深入理解MySQL索引(下)

2022-11-24 08:21:54

先创建一个T表。



mysql> create table T (

ID int primary key,

k int NOT NULL DEFAULT 0,

s varchar(16) NOT NULL DEFAULT '',

index k(k))

engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

然后执行

select * from T where k between 3 and 5

思考：
需要执行几次树的搜索操作，会扫描多少行？

深入理解MySQL索引(下)

这条SQL的执行流程

  1. 在k索引树上找到k=3的记录，取得ID=300；
  2. 再到ID索引树查到ID=300对应的R3；
  3. 在k索引树取下一个值k=5，取得ID=500;
  4. 再回到ID索引树查到ID=500对应的R4
  5. 在k索引树取下一个值k=6，不满足条件，循环结束。

这个过程中，回到主键索引树搜索的过程叫做 回表。这个过程回表了2次，（步骤2和4）

在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，从而避免回表的过程呢？

覆盖索引

如果执行的语句是
select ID from T where k between 3 and 5
这时候只需要查询ID的值，而ID的值已经在K索引树上了，因此可以直接获取结果并返回，不需要再回表。也就说，在这个查询里面索引k已经覆盖了我们的查询需求，我们称为覆盖索引。

因为覆盖索引可以减少回表的次数，索引使用覆盖索引是一个常用的性能优化手段。

需要注意的是，在引擎内部使用覆盖索引在索引k上其实读取了3条记录，第一次是k=3,第二次是k=5,第三次是k=6；但是对于MySQL的server层来说，他就是找引擎拿到了两条数据（3,5）因此MySQL认为扫描的行数是2.

最左前缀匹配原则

如果为每一种查询都设计一个索引，索引是不是太多了，如果要按照市民的身份证号去查他们家的家庭地址该怎么办呢？虽然这种情况出现的概率也许不高但是总不能直接全表扫描吧。

B+树这种索引结构可以利用索引的最左前缀匹配来定位记录

深入理解MySQL索引(下)

上图中是建立了一个联合索引 (name,age),主键还是ID。

可以发现索引项是按照name从小到大排序的。

如果是查name='张三’的ID可以直接出结果。

如果要查的是性张的ID 这时候也能用到索引。where name like '张%``查到第一个符合条件的记录是ID3，然后依次向后遍历，直到遇到name的第一个字不是张的为止。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

那到底如何安排索引内的字段顺序呢？

这里需要考虑的是索引 的复用能力。因为可以支持最左前缀，所以当已经有了(a,b)这个联合索引后，一般不需要单独在a上建立索引，（a,b）实际上 建立的索引包含a,同样建立一个联合索引(a,b,c)就相当于建立了三个索引，分别是 (a,b) （a,c）,(a,b,c); 因此通过调整索引顺序可以少维护一个索引，那么这个顺序是需要优先考虑的。

那如果使用联合查询有基于a,b各自的查询呢？

select * from T where a = 1 and b = 2

select * from T where b = 2 -- 不会走索引，而是全表扫描

select * from T where b = 2 and a = 1

select * from T where a =1 -- 使用索引

第一条SQL和第三条SQL查询的结果是一样，为什么第三条SQL也会走索引，他难道不是遵从最左前缀匹配吗？

其实，我们可以这样想，因为这两条SQL的执行结果最后是一样的，对吧，既然结果是一样的，那么位于Server层的优化器就该出场了，他来决定这条SQL用哪种方式执行起来效率最高，那当然是用索引的效率高些，所以最后第三条SQL会以第一条SQL的形式执行。

这里在说一下第一条SQL和第四条SQL走索引的不同。

用

explain select * from T where a = 1 and b = 2 --type 是 ref

explain select * from T where b = 2 --type 是 index

index：这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个复合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。

ref：这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一 一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

索引下推

建一张市民表



CREATE TABLE `tuser` (

`id` int(11) NOT NULL,

`id_card` varchar(32) DEFAULT NULL,

`name` varchar(32) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`ismale` tinyint(1) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `id_card` (`id_card`),

KEY `name_age` (`name`,`age`)

) ENGINE=InnoDB

现在要查姓张的男孩年龄还必须是10岁的，SQL可以这么写



mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

这个语句在搜索 索引树的时候，只能用"张",找到第一个满足条件的记录ID3，当然这种笔全表扫描要好。然后再判断剩下的条件是否满足，在MySQL5.6版本之前只能根据name查到ID3，剩下的条件需要一次一次回表判断才能最终找出结果。但是在MySQL5.6引入下推优化，可以减少回表的次数。

5.6之前，不会向后判断age，而是直接回表查询
深入理解MySQL索引(下)

5.6之后，会向后判断age=10的人，然后再回表，这样就减少了回表的次数

深入理解MySQL索引(下)
两张图中虚线代表回表。

参考 知乎大佬 https://www.zhihu.com/question/36996520

上一篇：PHP可变参数


下一篇：深入MySQL索引