Java 业务开发常见错误 100 例
朱晔
贝壳金服资深架构师
52944 人已学习
新⼈⾸单¥59
登录后,你可以任选4讲全文学习
课程目录
已完结/共 48 讲
代码篇 (23讲)
Java 业务开发常见错误 100 例
15
15
1.0x
00:00/00:00
登录|注册

07 | 数据库索引:索引并不是万能药

二级索引
聚簇索引
页目录
MySQL的索引选择
函数计算
后缀匹配查询
回表代价
空间代价
索引维护代价
索引在排序中的作用
优化器 trace 分析
optimizer trace
MySQL的索引选择
全表扫描的成本
IO成本和CPU成本
创建二级索引的代价
二级索引
聚簇索引
B+树
InnoDB存储数据结构
存储引擎
误区2:建了索引就一定有效
误区1:索引越多越好
思考与讨论
数据库基于成本决定是否走索引
聚簇索引和二级索引
InnoDB存储数据原理
数据库索引并不是万能药
数据库索引

该思维导图由 AI 生成,仅供参考

你好,我是朱晔。今天,我要和你分享的主题是,数据库的索引并不是万能药。
几乎所有的业务项目都会涉及数据存储,虽然当前各种 NoSQL 和文件系统大行其道,但 MySQL 等关系型数据库因为满足 ACID、可靠性高、对开发友好等特点,仍然最常被用于存储重要数据。在关系型数据库中,索引是优化查询性能的重要手段。
为此,我经常看到一些同学一遇到查询性能问题,就盲目要求运维或 DBA 给数据表相关字段创建大量索引。显然,这种想法是错误的。今天,我们就以 MySQL 为例来深入理解下索引的原理,以及相关误区。

InnoDB 是如何存储数据的?

MySQL 把数据存储和查询操作抽象成了存储引擎,不同的存储引擎,对数据的存储和读取方式各不相同。MySQL 支持多种存储引擎,并且可以以表为粒度设置存储引擎。因为支持事务,我们最常使用的是 InnoDB。为方便理解下面的内容,我先和你简单说说 InnoDB 是如何存储数据的。
虽然数据保存在磁盘中,但其处理是在内存中进行的。为了减少磁盘随机读取次数,InnoDB 采用页而不是行的粒度来保存数据,即数据被分成若干页,以页为单位保存在磁盘中。InnoDB 的页大小,一般是 16KB。
各个数据页组成一个双向链表,每个数据页中的记录按照主键顺序组成单向链表;每一个数据页中有一个页目录,方便按照主键查询记录。数据页的结构如下:
确认放弃笔记?
放弃后所记笔记将不保留。
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
公开
同步至部落
取消
完成
0/2000
荧光笔
直线
曲线
笔记
复制
AI
  • 深入了解
  • 翻译
    • 英语
    • 中文简体
    • 中文繁体
    • 法语
    • 德语
    • 日语
    • 韩语
    • 俄语
    • 西班牙语
    • 阿拉伯语
  • 解释
  • 总结

数据库索引并非解决所有查询性能问题的“灵丹妙药”。本文深入探讨了MySQL中InnoDB存储数据的原理,以及聚簇索引和二级索引的概念。首先,介绍了InnoDB存储数据的方式,采用页的概念来减少磁盘随机读取次数。然后,详细解释了聚簇索引和二级索引的作用和实现原理,以及如何利用B+树来实现快速查找和回表操作。通过本文,读者可以深入了解数据库索引的原理和误区,以及如何合理地利用索引来优化查询性能。 在讨论索引开销的最佳实践时,文章提到了创建索引的代价,包括维护代价、空间代价和回表代价。通过实验测试,作者展示了创建索引对性能的影响,并提出了合理设置合并阈值的建议。此外,文章还强调了避免在SQL语句中使用SELECT *,并提出了索引轻量级字段和索引覆盖的建议。 另外,文章还探讨了不是所有针对索引列的查询都能用上索引的情况,包括索引失效的几种情况和如何选择建立联合索引还是多个独立索引的建议。 总的来说,本文通过深入探讨数据库索引的原理和实践,为读者提供了全面的索引优化知识,帮助他们更好地理解索引的作用和使用方法。文章还介绍了MySQL基于成本决定是否走索引的原理,以及如何通过optimizer trace功能查看优化器生成执行计划的整个过程,为读者提供了更深入的了解和优化查询的方法。 通过本文,读者可以了解到索引的作用和误区,以及如何通过实践和优化器功能来更好地利用索引来提高数据库查询性能。

仅可试看部分内容,如需阅读全部内容,请付费购买文章所属专栏
《Java 业务开发常见错误 100 例》
新⼈⾸单¥59
立即购买
登录 后留言

全部留言(42)

  • 最新
  • 精选
  • Darren
    置顶
    第一个问题: 覆盖索引的cost是1.21而回表的是2.21 覆盖索引: analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "name_score", "ranges": [ "name1 <= name <= name1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 1, "cost": 1.21, "chosen": true } ] 回表: "range_scan_alternatives": [ { "index": "name_score", "ranges": [ "name1 <= name <= name1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 1, "cost": 2.21, "chosen": true } ] 第二个问题: SQL中带order by且执行计划中Extra 这个字段中有"Using index"或者"Using index condition"表示用到索引,并且不用专门排序,因为索引本身就是有序的; 如果Extra有“Using filesort”表示的就是需要排序; 排序时:MySQL 会给每个线程分配一块内存用于排序,称为 sort_buffer。sort_buffer_size(sort_buffer)的大小。如果要排序的数据量小于 sort_buffer_size,排序就在内存中完成。但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序。 上述排序中,只对原表的数据读了一遍,剩下的操作都是在 sort_buffer 和临时文件中执行的。但这个算法有一个问题,就是如果查询要返回的字段很多的话,那么 sort_buffer 里面要放的字段数太多,这样内存里能够同时放下的行数很少,要分成很多个临时文件,排序的性能会很差。所以如果单行很大,这个方法效率不够好。max_length_for_sort_data,是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是,如果单行的长度超过这个值,MySQL 就认为单行太大,要换一个算法。称为 rowid 排序; rowid排序简单的描述就是先取出ID和排序字段进行排序,排序结束后,用ID回表去查询select中出现的其他字段,多了一次回表操作, 对于 InnoDB 表来说,rowid 排序会要求回表多造成磁盘读,因此不会被优先选择。

    作者回复: 👍🏻

    2020-03-24
    7
    92
  • Darren
    另外分享下之前在公司分享的MySQL相关的PPT,主要从MySQL整体架构,引擎、索引、锁和MVCC这及部分分享的,感兴趣的同学可以看看,然后大家交流交流。 https://github.com/y645194203/geektime-java-100/blob/master/MySQL-%E5%88%86%E4%BA%AB%E4%BA%A4%E6%B5%81%E7%9A%84%E5%89%AF%E6%9C%AC.pptx

    作者回复: 感谢分享

    2020-03-24
    3
    47
  • 袁帅
    老师的文章真的是太用心了,讲的太好了,赞👍

    作者回复: 觉得好可以多分享

    2020-03-25
    14
  • 👽
    过早的优化,是万恶之源。不需要提前考虑建立索引。等性能出现问题,真正的需求降临的时候再考虑优化。 建立索引之前,需要考虑索引带来的副作用:维护成本,空间成本,回表成本。 更重要的是还要考虑,你的查询是否能用到索引。如果花费大量成本建立的索引,最后还用不上。那就赔了夫人又折兵了。 索引又牵扯到了很多注意事项,例如:尽量使用前缀匹配,而避免使用后缀匹配。因为后缀匹配会使得索引失效,走全表匹配。

    作者回复: 总结的不错

    2020-03-24
    14
  • 马以
    老师这里讲的查询优化器选择流程详细一些,可否加个餐,说一说普通索引和唯一索引在什么业务场景下使用?

    作者回复: 可以进一步阅读MySQL实战45讲专栏来了解相关内容,比如: 09 | 普通索引和唯一索引,应该怎么选择? 10 | MySQL为什么有时候会选错索引?

    2020-06-02
    3
    7
  • 汝林外史
    如果没有主键,就选择第一个不包含 NULL 值的唯一列。 没有主键的话,mysql不是会为每行数据默认生成一个rowid字段作为主键吗?? 排序索引失效应该也是对索引做了一些函数操作。 研读过mysql的专栏,所以这一节感觉的知识感觉都了解,不过这种感觉还是很爽的,期待老师更多的干货。

    作者回复: 聚簇索引的规则是: When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically. If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index. If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order. 因为这些太细节的东西和坑点没有太多关系,为了节省篇幅就不展开说了。 是的,当你读一些文章的时候发现99%的知识点自己都知道,完全是复习一遍的感觉,这种契合的感觉很舒服

    2020-03-24
    6
  • pedro
    第二个问题,我使用 EXPLAIN SELECT create_time from person ORDER BY create_time LIMIT 10; 语句,分析该 SQL,结果果然是 type = index,Extra = using index,证明排序走了索引并且是索引覆盖没有回表;我本以为加上函数就可以使索引失效,于是分别使用了 ORDER BY YEAR(create_time) 和 ORDER BY substring(create_time,1,3),发现 type 还是 index,我的数据库版本是8.0,请问老师,有什么方式可以让索引失效呢?

    作者回复: select *,你select仅仅是索引列,那么走索引更划算了

    2020-03-24
    4
  • 海战
    利用jpa 完成查询,是不是都会发生回表呀,不知道会不会产生影响

    作者回复: 文中回表是指二级索引查询后回聚簇索引,这个和jpa没什么关系

    2020-03-25
    2
    3
  • LiG❄️
    老师好优秀,讲的非常透彻~自己还要要实操,加深理解!

    作者回复: :)

    2020-04-27
    1
  • hellojd
    有时候一个表索引太多,也会带来成本

    作者回复: 是的

    2020-03-28
    1
收起评论
显示
设置
留言
42
收藏
沉浸
阅读
分享
手机端
快捷键
回顶部