MySQL实战45讲学习笔记：第五讲
一、需要回表的案例
在下面表T中，执行下面语句，需要执行几次树的搜索操作？会扫描多少行？
select * from T where k between 3 and 5
1、初始化语句
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');
2、这条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，不满足条件，循环结束。
这个过程中回到主键索引树搜索的过程，我们称为回表，可以看到这个查询过程读了K索引树的3条记录
(步骤1、3和5)，回表了两次(步骤1、3和5)
二、如何避免回表
在上面的例子中，由于查询结果锁需要的数据只在主键索引上有，所以不得不回表，
那么。有没有可能经过索引优化，避免回表过程？
select ID from T where k between 3 and 5
1、覆盖索引
索引k已经"覆盖了"我们的查询需求。我们称为覆盖索引
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段
2、在一个市民信息表上，是否有必要将身份证号和名字建立联合索引
假设这个市民表的定义是这样的：
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
如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了，
它可以在这个高频请求上用覆盖索引、不再需要回表查整行记录，减少语句的执行时间
3、回表的缺点
当然、索引字段的维护总是有代价的，建立冗余索引来支持覆盖索引时就需要权衡考虑了，这正是业务DBA，或者称为业务数据架构师的工作
三、最左前缀原则
1、疑问
单独为一个不频繁的请求创建一个索引又感觉有点浪费，应该怎么做呢？
2、解决方案
B+树这种索引结构，可以利用索引的"最左前缀"，来定位记录
1、为了直观地说明这个概念、我们用(name、age)这个联合索引来分析
1、查到所有名字是“张三”的人
快速定位到ID4,然后向后遍历得到所有需要的结果
2、你要查的是所有名字第一个字是“张”的人
where name like '张%'
查找到第一个符合条件的记录是ID3，然后向后遍历，知道不满足条件为止。
你要查的是所有名字第一个字是“张”的人
不只是索引的全部定义，只要满足最左前缀，就可以可利用索引来加速检索、这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符
3、在建立联合索引的时候，如何安排索引内的字段顺序
1、评估标准
索引的复用能力，
因为可以支持最左前缀，所以已经有了(a,b)这两个联合所用后，一般就不需要单独在b上面建立索引了
2、第一原则是
如果通过调整顺序，可以减少维护一个索引。那么这个顺序往往就是需要优先考虑采用的
3、既有联合查询，又有基于 a、b 各自的查询呢？
同时维护(a,b)(b)这两个索引、这时候我们要考虑的原则就是空间了。
比如上面这个市民表的情况、name字段是比age字段大的，那我就建议你创建一个(name,age)的联合索引和一个(age)的字段索引
四、索引下推
我们还是以市民表的联合索引（name, age）为例。检索出表中："名字第一个字是长，而年龄是10岁的所有男孩"
1、名字第一个字是长，而年龄是10岁的所有男孩
1、SQL语句是这么写的
mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;
1、 找到第一个满足条件的记录ID3(这还不错，总比权标扫描要好)
2、判断其他是否满足条件
3、无索引执行流程
5.6之前只能从ID3开始一个一个回表，到主键索引上找出数据航，再对比字段值不去判断age
执行流程
每一个虚线箭头表示回表一次
1、InnoDB 并不会去看 age的值，
2、只是按顺序把name的第一个子是'张'的记录一条取出来回表，因此需要回表4次
4、索引下推执行流程
5.6之后引入的索引下推优化，可以在索引遍历过程中对索引中包含的字段先判断，直接过滤掉不满足条件的记录，减少回表次数
内部就判断了age是否等于10
执行流程
每一个虚线箭头表示回表一次
1、InnoDB 内部就哦按段了age是否等于10，
2、对不等于10的记录，直接判断跳过，在我们这个例子中只需要对ID4、ID4回表2次
五、联合索引的技巧
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的数据，再进行回表查询，减少回表率，提升检索速度
