07 | 数据库索引：索引并不是万能药
该思维导图由 AI 生成，仅供参考
InnoDB 是如何存储数据的？
荧光笔
直线
曲线- 深入了解
- 翻译
- 解释
- 总结
数据库索引并非解决所有查询性能问题的“灵丹妙药”。本文深入探讨了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-24792- 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-24347 
袁帅老师的文章真的是太用心了，讲的太好了，赞👍作者回复: 觉得好可以多分享
2020-03-2514
👽过早的优化，是万恶之源。不需要提前考虑建立索引。等性能出现问题，真正的需求降临的时候再考虑优化。 建立索引之前，需要考虑索引带来的副作用：维护成本，空间成本，回表成本。 更重要的是还要考虑，你的查询是否能用到索引。如果花费大量成本建立的索引，最后还用不上。那就赔了夫人又折兵了。 索引又牵扯到了很多注意事项，例如：尽量使用前缀匹配，而避免使用后缀匹配。因为后缀匹配会使得索引失效，走全表匹配。作者回复: 总结的不错
2020-03-2414
马以老师这里讲的查询优化器选择流程详细一些，可否加个餐，说一说普通索引和唯一索引在什么业务场景下使用？作者回复: 可以进一步阅读MySQL实战45讲专栏来了解相关内容，比如： 09 | 普通索引和唯一索引，应该怎么选择？ 10 | MySQL为什么有时候会选错索引？
2020-06-0237
汝林外史如果没有主键，就选择第一个不包含 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-246
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-244
海战利用jpa 完成查询，是不是都会发生回表呀，不知道会不会产生影响作者回复: 文中回表是指二级索引查询后回聚簇索引，这个和jpa没什么关系
2020-03-2523
LiG❄️老师好优秀，讲的非常透彻~自己还要要实操，加深理解！作者回复: ：）
2020-04-271
hellojd有时候一个表索引太多，也会带来成本作者回复: 是的
2020-03-281
