本文内容是整理极客时间的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层来说，它就是找引擎拿了两条记录。
在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？
市民表的定义：
如果有一个高频请求，根据市民的身份证号查询他的姓名，这个联合索引就有意义。在这个高频请求上用到覆盖索引，不需要回表查找整行记录，减少语句的执行时间。
索引字段的维护总是有代价的，在建立冗余索引来支持覆盖索引时需要权衡。
最左前缀原则，在建立联合索引的时候，如何安排索引内的字段顺序。
第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
对于一个不频繁的请求需不需要覆盖索引呢，如果为每一种查询都设计一个索引，是不是太浪费了？
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里面各个判断调换顺序没关系的
