35 | MySQL调优之索引：索引的失效与优化
该思维导图由 AI 生成，仅供参考
荧光笔
直线
曲线- 深入了解
- 翻译
- 解释
- 总结
本文从MySQL索引存储结构、覆盖索引优化查询、自增字段作主键优化查询以及前缀索引优化等方面展开讨论。首先介绍了B+Tree索引和Hash索引的存储结构，以及InnoDB和MyISAM存储引擎对索引的不同处理方式。其次，强调了覆盖索引的优势，通过建立组合索引来避免回表，提高查询性能。此外，指出了使用自增字段作为主键可以提高数据插入效率，避免页分裂的问题。最后，介绍了前缀索引的优化方式，减小索引字段大小以提高查询速度。通过本文的总结，读者可以快速了解MySQL索引的存储结构和优化方法，为数据库查询性能的提升提供了有益的参考。文章还提到了一些索引失效的情况，以及如何强制使用期望的索引。这篇文章对于想要深入了解MySQL索引优化的读者来说是一份有价值的参考资料。
《Java 性能调优实战》，新⼈⾸单¥59
全部留言(39)
- 最新
- 精选
QQ怪回答老师问题： 按照老师的操作了一遍，实验小部分区间是会走order_id索引的，但是查询范围继续增大 ，反而不走索引而是全表扫描，大概我估摸着的是小于全表5分之一区间能够走索引，超过5分之一会全表扫描，可以使用force index（索引名）强制使用该索引，这就是有些sql表开始跑的挺快的，后面越来越慢的原因吧。。但不清楚mysql优化器为啥要这样选择，希望老师解惑？作者回复: 因为order_id索引不能覆盖我们要查询的信息，所以在对order_id查询之后还需要一次回表来查找到整行数据，虽然order_id索引是顺序存放的，但是相对于主键id存放的数据顺序是不一致的，所以存在每次回表都是随机获取整行数据，如果在获取大量数据时，通过这种方式获取数据性能肯定是不理想的。 所以mysql一般判断在查询超过整个表20%的数据时，就会考虑使用聚族索引来查找数据，这种方式顺序读取数据的可能性要大于使用辅助索引的随机读。 在查询少量数据的情况下，使用辅助索引性能更加，而查询大量数据时，就未必了。 如果我们发现在查询一定量数据使用辅助索引要比主键索引快，而数据库又没有按照我们期望的去使用辅助索引，则我们可以通过子查询或force index来强制使用辅助索引。
2019-08-10674
CCC对索引进行函数操作或者表达式计算也会导致索引的失效作者回复: 对的，点赞补充
2019-08-1023
Loubobooo我的想法是，可以利用子查询去减少回表操作，既然有主键自增id，便可以利用聚簇索引的优势来强制走索引。代码方法如下：select * from order_detail where id in (select id from order_detail where order_id between 5000 and 10000)作者回复: 思路是对的，这种方式可以解决。
2019-08-11317
张三丰”如果不存在辅助索引，此时会通过查询聚簇索引来统计行数，如果此时正好存在一个辅助索引，则会通过查询辅助索引来统计行数，减少 I/O 操作。“ 这有什么区别吗？都是通过索引统计行数作者回复: 区别在于聚簇索引存储了其他数据，而辅助索引只保存了索引列和主键，所以通过查询辅助索引统计行检索的数据量会更少，I/O操作会更少
2019-10-04213- 张学磊由于是select *操作，所以每条记录都需进行回表，当server层分析器发现between的范围太大时，使用辅助索引存在大量回表操作，所以觉得得不偿失，故而直接使用主键索引。如果想使用我们期望的索引，需要给server层分析器一个hint，force index(idx_order_id)
作者回复: 分析到位，答案正确。
2019-08-1013 
man1s走主键索引，优化器认为5000数据+回表5000次性能消耗要大于全表扫描 force index作者回复: 👍
2019-09-167
考休为什么innodb存储引擎没有支持哈希索引？作者回复: innodb不能手动设置哈希索引，但有自适应哈希索引的功能，也就是如果发现树的查询路径太长时，会记录到一个哈希缓存中，这个时候会用到哈希索引。 哈希索引在关键字查询时非常快，但也有很多缺点，例如无法进行范围查询、无法排序等。
2020-03-1026
某、 使用某个字段中字符串的前几个字符建立索引？这句不是很明白，能否细讲一下，能否以身份证这个字段作个栗子？作者回复: 身份证前几位一般在相同城市或省都是一样的，所以作为前缀索引区分度不大，如果倒序存储，以原来后六位作为首位区分度更大，alert table xxx add index index_idno(idno(6));
2019-12-0525
Charles想问下老师为什么回表查询的速度会慢于直接用主键查询，因为回表也是使用主键ID去查询的，就算查询的数据量大，用不用子查询都是使用主键ID去回表或是查询，速度应该一样吧作者回复: 回表就相当于两次索引树扫描操作了，而主键查询只有一次。
2019-08-1435
张振宇老师，没明白开头那段，为什么要添加 create_time 这列进行组合索引啊作者回复: 为了避免文件排序的发生。因为查询时我们只能用到status索引，如果要对create_time进行排序，则需要使用文件排序filesort。 filesort是通过相应的排序算法将取得的数据在内存中进行排序，如果内存不够则会使用磁盘文件作为辅助。虽然在一些场景中，filesort并不是特别消耗性能，但是我们可以避免filesort就尽量避免。
2020-03-1024
