Mysql实战45讲学习详情----深入浅出索引（二）

新建表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

执行过程：

　　

　　①在 k 索引树上找到 k=3 的记录，取得 id = 300

　　②再到 id 索引树查到 id=300 对应的 r3

　　③在 k 索引树取下一个值 k=5，取得 id=500

　　④再回到 id 索引树查到 id=500 对应的 r4

　　⑤在 k 索引树取下一个值 k=6，不满足条件，循环结束

　　在这个过程中，回到主键索引树搜索的过程，称为回表（此查询回表两次，步骤2，4）

优化索引，避免回表：

　　覆盖索引：

　　　　在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

　　　　执行语句：

　　　　　　select id from t where k between 3 and 5

　　　　　　直接查询字段id的值，而id的值在k索引的树上，不需要回表。

 最左前缀原则：

　　不可能为每一个查询都设置一个索引，但是单独为一个不频繁的请求创建一个索引又有点浪费。

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

　　可以设置n个字段

 　　这就需要考虑到一个问题，在建立联合索引的时候，如何安排索引内的字段顺序？

　　　　索引的复用能力：因为已经有（a,b）联合索引，所以不需要在a上单独建索引，so第一原则就是：如果可以通过调整顺序少维护一个索引，那么调整顺序是需要优先考虑采用的。

　　　　空间原则：　　　如果查询条件里只有b，同时a字段比b字段大，那么就需要创建一个（a,b）联合索引和b单字段索引。

索引下推：

　　事例语句：

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

　　　　以前缀规则来执行（过程）：

　　　　　　搜索索引树--》用 ‘张’ 找到第一个满足条件的记录（id3）--》在mysql5.6之前（没有索引下推）：从id3开始一个一个回表，到主键索引上找出数据行，对比字段值

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　在mysql5.6之后（优化了索引下推）：在索引遍历的过程中对包含的字段做优先判断，过滤掉不符合要求的值，减少回表次数

【虚线代表回表次数】

没有索引下推：　　　　　　　　　　　　　　  索引下推：

　　

 

 【个人理解】

　　索引越多，维护成本越大

　　覆盖索引：查询结果是索引字段或主键

　　　　优--不用回表操作

　　最左前缀：联合索引最左边的n个字段或字符串索引的最左n个字段　

　　　　优--减少索引数量，降低维护成本

　　联合索引：两个字段创建的索引，例如：（a,b）查询a或a，b时使用索引，单查b不会使用索引

　　索引下推：先过滤掉不符合条件的数据再进行回表查询