34讲MySQL调优之索引：索引的失效与优化

你好，我是刘超。

不知道你是否跟我有过同样的经历，那就是作为⼀个开发⼯程师，经常被DBA叫过去“批评”，⽽最常⻅的就是申请创建新的索引或发现慢SQL⽇志了。

记得之前有⼀次迭代⼀个业务模块的开发，涉及到了⼀个新的查询业务，需要根据商品类型、订单状态筛选出需要的订单，并以订单时间进⾏排序。由于sku的索引已经存在了，我在完成业务开发之后，提交了⼀个创建status的索引的需求，理由是
SQL查询需要使⽤到这两个索引：

select * from order where status =1 and sku=10001 order by create_time asc

然⽽，DBA很快就将这个需求驳回了，并给出了重建⼀个sku、status以及create_time组合索引的建议，查询顺序也改成了
sku=10001 and status=1。当时我是知道为什么要重建组合索引，但却⽆法理解为什么要添加create_time这列进⾏组合。

从执⾏计划中，我们可以发现使⽤到了索引，那为什么DBA还要求将create_time这⼀列加⼊到组合索引中呢？这个问题我们在第32讲中提到过，相信你也已经知道答案了。通过故事我们可以发现索引知识在平时开发时的重要性，然⽽它⼜很容易被我们忽略，所以今天我们就来详细聊⼀聊索引。

MySQL索引存储结构

索引是优化数据库查询最重要的⽅式之⼀，它是在MySQL的存储引擎层中实现的，所以每⼀种存储引擎对应的索引不⼀定相同。我们可以通过下⾯这张表格，看看不同的存储引擎分别⽀持哪种索引类型：

B+Tree索引和Hash索引是我们⽐较常⽤的两个索引数据存储结构，B+Tree索引是通过B+树实现的，是有序排列存储，所以在
排序和范围查找⽅⾯都⽐较有优势。如果你对B+Tree索引不够了解，可以通过该链接了解下它的数据结构原理。

Hash索引相对简单些，只有Memory存储引擎⽀持Hash索引。Hash索引适合key-value键值对查询，⽆论表数据多⼤，查询数据的复杂度都是O(1)，且直接通过Hash索引查询的性能⽐其它索引都要优越。

在创建表时，⽆论使⽤InnoDB还是MyISAM存储引擎，默认都会创建⼀个主键索引，⽽创建的主键索引默认使⽤的是B+Tree 索引。不过虽然这两个存储引擎都⽀持B+Tree索引，但它们在具体的数据存储结构⽅⾯却有所不同。

InnoDB默认创建的主键索引是聚簇索引（Clustered Index），其它索引都属于辅助索引（Secondary Index），也被称为⼆级索引或⾮聚簇索引。接下来我们通过⼀个简单的例⼦，说明下这两种索引在存储数据中的具体实现。

⾸先创建⼀张商品表，如下：

CREATE TABLE merchandise (
id int(11) NOT NULL,
serial_no varchar(20) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
unit_price decimal(10, 2) DEFAULT NULL, PRIMARY KEY (id) USING BTREE
) CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
然后新增了以下⼏⾏数据，如下：

如果我们使⽤的是MyISAM存储引擎，由于MyISAM使⽤的是辅助索引，索引中每⼀个叶⼦节点仅仅记录的是每⾏数据的物理

地址，即⾏指针，如下图所示：

如果我们使⽤的是InnoDB存储引擎，由于InnoDB使⽤的是聚簇索引，聚簇索引中的叶⼦节点则记录了主键值、事务id、⽤于事务和MVCC的回流指针以及所有的剩余列，如下图所示：

基于上⾯的图示，如果我们需要根据商品编码查询商品，我们就需要将商品编码serial_no列作为⼀个索引列。此时创建的索引是⼀个辅助索引，与MyISAM存储引擎的主键索引的存储⽅式是⼀致的，但叶⼦节点存储的就不是⾏指针了，⽽是主键值，并以此来作为指向⾏的指针。这样的好处就是当⾏发⽣移动或者数据分裂时，不⽤再维护索引的变更。

如果我们使⽤主键索引查询商品，则会按照B+树的索引找到对应的叶⼦节点，直接获取到⾏数据：

select * from merchandise where id=7

如果我们使⽤商品编码查询商品，即使⽤辅助索引进⾏查询，则会先检索辅助索引中的B+树的serial_no，找到对应的叶⼦节
点，获取主键值，然后再通过聚簇索引中的B+树检索到对应的叶⼦节点，然后获取整⾏数据。这个过程叫做回表。在了解了索引的实现原理后，我们再来详细了解下平时建⽴和使⽤索引时，都有哪些调优⽅法呢？

1.覆盖索引优化查询

假设我们只需要查询商品的名称、价格信息，我们有什么⽅式来避免回表呢？我们可以建⽴⼀个组合索引，即商品编码、名
称、价格作为⼀个组合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从⽽避免回表。

从辅助索引中查询得到记录，⽽不需要通过聚簇索引查询获得，MySQL中将其称为覆盖索引。使⽤覆盖索引的好处很明显， 我们不需要查询出包含整⾏记录的所有信息，因此可以减少⼤量的I/O操作。

通常在InnoDB中，除了查询部分字段可以使⽤覆盖索引来优化查询性能之外，统计数量也会⽤到。例如，在第32讲我们讲SELECT COUNT(*)时，如果不存在辅助索引，此时会通过查询聚簇索引来统计⾏数，如果此时正好存在⼀个辅助索引，则会通过查询辅助索引来统计⾏数，减少I/O操作。

通过EXPLAIN，我们可以看到 InnoDB 存储引擎使⽤了idx_order索引列来统计⾏数，如下图所示：

2.⾃增字段作主键优化查询

上⾯我们讲了 InnoDB 创建主键索引默认为聚簇索引，数据被存放在了B+树的叶⼦节点上。也就是说，同⼀个叶⼦节点内的各个数据是按主键顺序存放的，因此，每当有⼀条新的数据插⼊时，数据库会根据主键将其插⼊到对应的叶⼦节点中。

如果我们使⽤⾃增主键，那么每次插⼊的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当⻚⾯写满，就会⾃动开辟⼀个新⻚⾯。因为不需要重新移动数据，因此这种插⼊数据的⽅法效率⾮常⾼。

如果我们使⽤⾮⾃增主键，由于每次插⼊主键的索引值都是随机的，因此每次插⼊新的数据时，就可能会插⼊到现有数据⻚中间的某个位置，这将不得不移动其它数据来满⾜新数据的插⼊，甚⾄需要从⼀个⻚⾯复制数据到另外⼀个⻚⾯，我们通常将这种情况称为⻚分裂。⻚分裂还有可能会造成⼤量的内存碎⽚，导致索引结构不紧凑，从⽽影响查询效率。

因此，在使⽤InnoDB存储引擎时，如果没有特别的业务需求，建议使⽤⾃增字段作为主键。

3.前缀索引优化

前缀索引顾名思义就是使⽤某个字段中字符串的前⼏个字符建⽴索引，那我们为什么需要使⽤前缀来建⽴索引呢？

我们知道，索引⽂件是存储在磁盘中的，⽽磁盘中最⼩分配单元是⻚，通常⼀个⻚的默认⼤⼩为16KB，假设我们建⽴的索引 的每个索引值⼤⼩为2KB，则在⼀个⻚中，我们能记录8个索引值，假设我们有8000⾏记录，则需要1000个⻚来存储索引。如果我们使⽤该索引查询数据，可能需要遍历⼤量⻚，这显然会降低查询效率。

减⼩索引字段⼤⼩，可以增加⼀个⻚中存储的索引项，有效提⾼索引的查询速度。在⼀些⼤字符串的字段作为索引时，使⽤前缀索引可以帮助我们减⼩索引项的⼤⼩。

不过，前缀索引是有⼀定的局限性的，例如order by就⽆法使⽤前缀索引，⽆法把前缀索引⽤作覆盖索引。

4.防⽌索引失效

当我们习惯建⽴索引来实现查询SQL的性能优化后，是不是就万事⼤吉了呢？当然不是，有时候我们看似使⽤到了索引，但实际上并没有被优化器选择使⽤。

对于Hash索引实现的列，如果使⽤到范围查询，那么该索引将⽆法被优化器使⽤到。也就是说Memory引擎实现的Hash索引只有在“=”的查询条件下，索引才会⽣效。我们将order表设置为Memory存储引擎，分析查询条件为id<10的SQL，可以发现没

有使⽤到索引。

如果是以%开头的LIKE查询将⽆法利⽤节点查询数据：

当我们在使⽤复合索引时，需要使⽤索引中的最左边的列进⾏查询，才能使⽤到复合索引。例如我们在order表中建⽴⼀个复合索引idx_user_order_status(order_no, status, user_id)，如果我们使⽤order_no、order_no+status、
order_no+status+user_id以及order_no+user_id组合查询，则能利⽤到索引；⽽如果我们⽤status、status+user_id查询，将
⽆法使⽤到索引，这也是我们经常听过的最左匹配原则。

如果查询条件中使⽤or，且or的前后条件中有⼀个列没有索引，那么涉及的索引都不会被使⽤到。

所以，你懂了吗？作为⼀名开发⼈员，如果没有熟悉MySQL，特别是MySQL索引的基础知识，很多时候都将被DBA批评到怀疑⼈⽣。

总结

在⼤多数情况下，我们习惯使⽤默认的 InnoDB 作为表存储引擎。在使⽤InnoDB作为存储引擎时，创建的索引默认为B+树数据结构，如果是主键索引，则属于聚簇索引，⾮主键索引则属于辅助索引。基于主键查询可以直接获取到⾏信息，⽽基于辅助索引作为查询条件，则需要进⾏回表，然后再通过主键索引获取到数据。

如果只是查询⼀列或少部分列的信息，我们可以基于覆盖索引来避免回表。覆盖索引只需要读取索引，且由于索引是顺序存
储，对于范围或排序查询来说，可以极⼤地极少磁盘I/O操作。

除了了解索引的具体实现和⼀些特性，我们还需要注意索引失效的情况发⽣。如果觉得这些规则太多，难以记住，我们就要养成经常检查SQL执⾏计划的习惯。

思考题

假设我们有⼀个订单表order_detail，其中有主键id、主订单order_id、商品sku等字段，其中该表有主键索引、主订单id索引。

现在有⼀个查询订单详情的SQL如下，查询订单号范围在5000~10000，请问该查询选择的索引是什么？有什么⽅式可以强制使⽤我们期望的索引呢？

select * from order_detail where order_id between 5000 and 10000；
期待在留⾔区看到你的答案。也欢迎你点击“请朋友读”，把今天的内容分享给身边的朋友，邀请他⼀起讨论。

    精选留⾔         <br />![](https://cdn.nlark.com/yuque/0/2022/png/1852637/1646315714815-c14a1ad9-2bec-4839-8a59-39f7135fec66.png#)QQ怪<br />回答⽼师问题：<br />按照⽼师的操作了⼀遍，实验⼩部分区间是会⾛order_id索引的，但是查询范围继续增⼤ ，反⽽不⾛索引⽽是全表扫描，⼤概我估摸着的是⼩于全表5分之⼀区间能够⾛索引，超过5分之⼀会全表扫描，可以使⽤force index（索引名）强制使⽤该索引， 这就是有些sql表开始跑的挺快的，后⾯越来越慢的原因吧。。但不清楚mysql优化器为啥要这样选择，希望⽼师解惑？<br />2019-08-10 14:23<br />作者回复<br />因为order_id索引不能覆盖我们要查询的信息，所以在对order_id查询之后还需要⼀次回表来查找到整⾏数据，虽然order_id索引是顺序存放的，但是相对于主键id存放的数据顺序是不⼀致的，所以存在每次回表都是随机获取整⾏数据，如果在获取⼤量<br />数据时，通过这种⽅式获取数据性能肯定是不理想的。

所以mysql⼀般判断在查询超过整个表20%的数据时，就会考虑使⽤聚族索引来查找数据，这种⽅式顺序读取数据的可能性要
⼤于使⽤辅助索引的随机读。

在查询少量数据的情况下，使⽤辅助索引性能更加，⽽查询⼤量数据时，就未必了。

如果我们发现在查询⼀定量数据使⽤辅助索引要⽐主键索引快，⽽数据库⼜没有按照我们期望的去使⽤辅助索引，则我们可以通过⼦查询或force index来强制使⽤辅助索引。
2019-08-12 09:47

Charles
想问下⽼师为什么回表查询的速度会慢于直接⽤主键查询，因为回表也是使⽤主键ID去查询的，就算查询的数据量⼤，⽤不⽤
⼦查询都是使⽤主键ID去回表或是查询，速度应该⼀样吧
2019-08-14 08:52

CCC
对索引进⾏函数操作或者表达式计算也会导致索引的失效
2019-08-10 00:44
作者回复
对的，点赞补充
2019-08-12 09:48

我⾏我素
⽼师，想请问下InnoDB引擎下使⽤HASH索引也可以啊，但是⽂中的图InnoDB索引Hash是no
2019-08-13 16:51
作者回复
官⽹给出的是不⽀持⾃创建hash数据结构的索引，但是它是⾃适应的，也就是我们不能⼈为的⼲预使⽤hash索引。具体的可以参考官⽹：https://dev.mysql.com/doc/refman/8.0/en/create-index.html
2019-08-13 19:08

胡峣
好⼏个都同学都提到 select * from order_detail where id in (select id from order_detail where order_id between 5000 and
10000)，⾄少mysql 57⾥这样写
2019-08-13 00:31
密码123456
我这⼀直⽤的都是oracle，看到mysql就想，快快跳过。后来发现，和数据库关系不⼤，很多都是通⽤的。
2019-08-12 08:19
作者回复
多熟悉⼀⻔数据库也是好的，知⼰知彼
2019-08-12 09:22

Loubobooo
我的想法是，可以利⽤⼦查询去减少回表操作，既然有主键⾃增id，便可以利⽤聚簇索引的优势来强制⾛索引。代码⽅法如下
：select * from order_detail where id in (select id from order_detail where order_id between 5000 and 10000)
2019-08-11 15:53
作者回复
思路是对的，这种⽅式可以解决。
2019-08-12 09:35

张学磊
由于是select *操作，所以每条记录都需进⾏回表，当server层分析器发现between的范围太⼤时，使⽤辅助索引存在⼤量回表操作，所以觉得得不偿失，故⽽直接使⽤主键索引。如果想使⽤我们期望的索引，需要给server层分析器⼀个hint，force index (idx_order_id)
2019-08-10 18:26
作者回复
分析到位，答案正确。
2019-08-12 09:37
新世界
查询⽤order_id索引，然后进⾏回表查询
2019-08-10 14:49
作者回复
对的
2019-08-12 09:37

许童童
请问该查询选择的索引是什么？有什么⽅式可以强制使⽤我们期望的索引呢？
会直接查询主键索引进⾏全表扫描，因为数据库优化器在判断SQL语句执⾏使⽤哪个索引时会计算代价，如果使⽤主订单 id 索引回表太多，代价太⼤，还不如⽤主键索引进⾏全表扫描。
我觉得SQL改为以下⽅式可以使⽤主订单 id 索引，并提⾼查询效率。
select * from order_detail where id in (select id from order_detail where order_id between 5000 and 10000)
2019-08-10 14:36
作者回复
正解！
2019-08-12 09:39

undifined
可以⽤⼦查询实现，因为order_id 的索引中有主键id，先使⽤order_id 的索引查询到主键id，然后通过主键再从表⾥查询
SELECT *
FROM order_detail
WHERE id IN (SELECT id FROM order_detail WHERE order_id BETWEEN 5000 AND 10000);

2019-08-10 10:57
作者回复
对的，思路就是强制使⽤辅助索引
2019-08-12 09:51

-W.LI-
索引失效:这个操作在索引树上满⾜不了需求。就会导致索引失效，b+树的特性就是有序(最左可以理解为⾼位有效)。课后习题，感觉⽼师这么问肯定不会⾛主订单id的索引了，原因待⽼师解答。
如果有分布式id⽣成系统能保证⽣成的id有序递增的话。可以不⽤⾃增的id做主键，直接⽤⽣成的oreder_id做主键，可少⼀次 回表。愚⻅期待⽼师的答案
2019-08-10 10:21
作者回复
已经给出答案，请转⾄QQ怪的问答题
2019-08-12 09:52

⻔窗⼩⼆
看查询优化器计算出来的代价！不知道数据量⼩的时候会不会直接使⽤id索引这样相⽐order_id⽽⾔是少了回表的代价！正常情况应该还是使⽤主订单id的索引！最终还是需要结合执⾏计划看下是不是可以优化或者说指定需要查询出来的列⽽不是*。强制使⽤某个索引是force index吧
2019-08-10 09:44
作者回复
正解！
2019-08-12 09:50