MYSQL的索引——深入浅出索引（下）

参考文章：https://time.geekbang.org/column/article/69236

目录

覆盖索引

最左前缀原则

索引下推

---

回到主键索引树搜索的过程，我们称为回表。那么，有没有可能经过索引优化，避免回表过程呢？

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');

InnoDB 的索引组织结构

覆盖索引

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

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

**我们来讨论一个问题：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

（name，age）索引示意图

**在建立联合索引的时候，如何安排索引内的字段顺序？

这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。那么，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。这时候，我们要考虑的原则就是空间了。比如上面这个市民表的情况，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

索引下推

那些不符合最左前缀的部分，会怎么样呢？

以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：
mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

无索引下推执行流程：

索引下推执行流程：

InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。

-----------------------------------我是快乐且好学的分割线-----------------------------------

思考：

实际上主键索引也是可以使用多个字段的。DBA 小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的：

公司的同事告诉他说，由于历史原因，这个表需要 a、b 做联合主键，这个小吕理解了。

但是，学过本章内容的小吕又纳闷了，既然主键包含了 a、b 这两个字段，那意味着单独在字段 c 上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？

同事告诉他，是因为他们的业务里面有这样的两种语句：

这位同事的解释对吗，为了这两个查询模式，这两个索引是否都是必须的？为什么呢？

答：

表记录

–a--|–b--|–c--|–d--

1 2 3 d

1 3 2 d

1 4 3 d

2 1 3 d

2 2 2 d

2 3 4 d

主键 a，b 的聚簇索引组织顺序相当于 order by a,b ，也就是先按 a 排序，再按 b 排序，c 无序。

索引 ca 的组织是先按 c 排序，再按 a 排序，同时记录主键

–c--|–a--|–主键部分b-- （注意，这里不是 ab，而是只有 b）

2 1 3

2 2 2

3 1 2

3 1 4

3 2 1

4 2 3

这个跟索引 c 的数据是一模一样的。

索引 cb 的组织是先按 c 排序，在按 b 排序，同时记录主键

–c--|–b--|–主键部分a-- （同上）

2 2 2

2 3 1

3 1 2

3 2 1

3 4 1

4 3 2

所以，结论是 ca 可以去掉，cb 需要保留。

-----------------------------------我是快乐且好学的分割线-----------------------------------

有价值的问题：

1、联合索引的技巧

（1）覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据
（2）最左前缀：联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
（3）联合索引：根据创建联合索引的顺序，以最左原则进行where检索，比如（age，name）以age=1 或 age= 1 and name=‘张三’可以使用索引，单以name=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。
（4）索引下推：like 'hello%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度

2、下面两条语句有什么区别，为什么都提倡使用（2）:
（1）.select * from T where k in(1,2,3,4,5) 
（2）.select * from T where k between 1 and 5

第一个要树搜素5次；第二个搜索一次

3、说下怎么让mysql的MyISAM引擎支持事务

面试官是魔鬼吗😄~~

用lock table 来实现，但是这样只能实现串行化隔离级别，其它隔离都实现不了。但是因为mysiam不支持崩溃恢复，所以即使用lock table硬实现，也是问题多多：ACID里面， 原子性和持久性做不到；隔离性只能实现基本用不上的串行化；一致性在正常运行的时候依赖于串行化，在异常崩溃的时候也不能保证。这样实现的事务不要也罢。

-----------------------------------我是快乐且好学的分割线-----------------------------------

总结：
- 高频查询，可以建立联合索引来使用覆盖索引，不用回表。
- 非高频查询，再已有的联合索引基础上，使用最左前缀原则来快速查询。
- 对于MySQL 5.6 引入索引下推，减少回表次数。