MySQL索引-下

本文内容是整理极客时间的mysql课程。

执行select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

表的初始化操作：

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

InnoDB的索引组织结构-图

InnoDB的索引语句执行流程：

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，不满足条件，循环结束。
解析

回到主键索引树搜索的过程，我们称为回表。在这个例子中，查询过程读了k索引树的3条记录（步骤1、3、5），回表了两次（步骤2和4）。

覆盖索引，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引

如何经过索引优化，避免回表过程？
覆盖索引，如果执行的语句是select ID from T where k between 3 and 5

如果执行的语句是select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。

在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称之为覆盖索引。
覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

注意，在引擎内部使用覆盖索引在索引k上其实读了三个记录，R3~R5（对应的索引k上的记录项），但对MySQL的Server层来说，它就是找引擎拿了两条记录。

在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

市民表的定义：

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

如果有一个高频请求，根据市民的身份证号查询他的姓名，这个联合索引就有意义。在这个高频请求上用到覆盖索引，不需要回表查找整行记录，减少语句的执行时间。
索引字段的维护总是有代价的，在建立冗余索引来支持覆盖索引时需要权衡。

最左前缀原则，在建立联合索引的时候，如何安排索引内的字段顺序。

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
对于一个不频繁的请求需不需要覆盖索引呢，如果为每一种查询都设计一个索引，是不是太浪费了？
B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

用（name，age）这个联合索引来分析。

图（name，age）索引示意图

索引下推

最左前缀可以用于在索引种定位记录，但是那些不符合最左前缀的部分怎么办？

比如现在有一个需求，检索出表种“名字第一个字是张，而且年龄是10岁的所有男孩”，SQL语句是这么写：

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

MySQL5.6引入的索引下推优化（index condition pushdown），可以在索引遍历过程种，对索引种包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的次数。
无索引下推执行流程
索引下推执行流程

小结

在满足语句需求的情况下，尽量少的访问资源是数据库设计的重要原则之一。我们在使用数据库的时候，尤其实在设计表结构时，也要减少资源消耗作为目标。
思考

有这样一个数据表，表结构是：

CREATE TABLE `geek` (

`a` int(11) NOT NULL,

`b` int(11) NOT NULL,

`c` int(11) NOT NULL,

`d` int(11) NOT NULL,

PRIMARY KEY (`a`,`b`),

KEY `c` (`c`),

KEY `ca` (`c`,`a`),

KEY `cb` (`c`,`b`)

) ENGINE=InnoDB;

由于历史原因，这个表需要 a、b 做联合主键。既然主键包含了 a、b 这两个字段，那意味着单独在字段 c上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？同事告诉他，是因为他们的业务里面有这样的两种语句：

select * from geek where c=N order by a limit 1;

select * from geek where c=N order by b limit 1;

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

作者回答：

网友回答

表记录

--a--|--b--|--c--

  1 2 3

  1 3 2

  1 4 3

  2 1 3

  2 2 2

  2 3 4

主键 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需要保留

对于下面的语句

select ... from geek where c=N order by a

走ca,cb索引都能定位到满足c=N主键

而且主键的聚簇索引本身就是按order by a,b排序，无序重新排序。所以ca可以去掉

select ... from geek where c=N order by b

这条sql如果只有 c单个字段的索引，定位记录可以走索引，但是order by b的顺序与主键顺序不一致，需要额外排序

cb索引可以把排序优化调
网友回答

给表创建索引时，应该创建哪些索引，每个索引应该包含哪些字段，字段的顺序怎么排列，这个问题没有标准答案，需要根据具体的业务来做权衡。不过有些思路还是可供参考的：
1.既然是一个权衡问题，没有办法保证所有的查询都高效，那就要优先保证高频的查询高效，较低频次的查询也尽可能的使用到尽可能长的最左前缀索引。可以借助pt-query-digest来采样统计业务查询语句的访问频度，可能需要迭代几次才能确定联合索引的最终字段及其排序。
2.业务是在演进的，所以索引也是要随着业务演进的，并不是索引建好了就万事大吉了，业务发生变化时，我们需要重新审视当初建的索引是不是还依然高效，依然能满足业务需求。
3.业内流传的有一些mysql 军规，其实这些并不是真正的军规，只是典型场景下的最佳实践。真正的军规其实就一条：高效的效满足业务需求。比如有个军规规定一个表上的索引数不超过5个，但如果我们现在有一些历史数据表、历史日志表，我们很明确的知道这些表上不会再有数据写入了，但我们的查询需求很多也很多样化，那我们在这些表上的索引数能不能超过5个？当然是没有任何问题的。当然关于这份军规还是要认真看一下的，但看的重点不是去记住它，而是要弄明白每一条军规它为什么这么规定，它这样规定是基于什么考虑，适用的场景和前提是什么，这些都弄明白了，你记不记得住这些军规都无所谓了，因为你已经把它溶化到了你的血液中，具体到自己的具体业务时游刃有余将是必然。

网友回答

看过某些文章上面写：如果查询顺序和联合索引的顺序不一致，优化器会自动做优化，是这样的吗老师？
作者回复: 是的，查询语句的where里面各个判断调换顺序没关系的