聚集索引和辅助索引

聚集索引（主键索引）
—innodb存储引擎是索引组织表，即表中的数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据
—聚集索引的叶子节点称为数据页，数据页，数据页！重要的事说三遍。聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

辅助索引（二级索引）
—非主键索引
—叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值

辅助索引检索数据图示如下

什么是覆盖索引

覆盖索引（covering index）指一个查询语句的执行只需要从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。也可以称之为实现了索引覆盖。
那么，优点显而易见。辅助索引不包含一整行的记录，因此可以大大减少IO操作。覆盖索引是mysql dba常用的一种SQL优化手段

几种优化场景

1、无where条件的查询优化

explain select sql_no_cache count(staff_id) from t1\G

优化: 
alter table t1 add key(staff_id);

执行计划解读如下：
Possible_keys为null，说明没有where条件时优化器无法通过索引检索数据；
但是这里使用了索引的另外一个优点，即从索引中获取数据，减少了读取的数据块的数量

无where条件的查询，可以通过索引来实现索引覆盖查询，但前提条件是，查询返回的字段数足够少，更不用说select *之类的了。毕竟，建立key length过长的索引，始终不是一件好事情。

2、二次检索优化

select sql_no_cache rental_date from t1 where inventory_id<80000;
优化:
alter table t1 add key(inventory_id,rental_date);

3、分页查询优化
分页查询的优化，相信大部分的DBA同学都碰到过，通常比较常规的优化手段就是查询改写，这里主要介绍一下新的思路，就是通过索引覆盖来优化

select tid,return_date from t1 order by inventory_id limit 50000,10;
优化
alter table t1 add index liu(inventory_id,return_date);

我们对比一下，索引覆盖与常规优化手段的效果差异

改写后的sql如下，思想是通过索引消除排序 (常规优化手段)

select tid,return_date from t1 order by inventory_id limit 800000,10;
常规:
select a.tid,a.return_date from t1 a inner join (select tid from t1 order by inventory_id limit 800000,10) b on a.tid=b.tid;

可以看到，这种优化手段较前者时间消耗多了大约140ms。
这种优化手段虽然使用索引消除了排序，但是还是要通过主键值回表查询。因此，在select返回列较少或列宽较小的时候，我们可以通过建立复合索引的方式优化分页查询，效果更佳，因为它不需要回表！

总体建议
索引具有以下两大用处：
1、通过索引检索仅需要数据
2、从索引中直接获取查询结果

覆盖索引的优势，就是利用到索引的第二大用处，在某些场景下，具有意想不到的优化效果。
个人总结如下：
Select查询的返回列包含在索引列中
有where条件时，where条件中要包含索引列或复合索引的前导列
查询结果的总字段长度可以接受

 

来源(带案例):

https://yq.aliyun.com/articles/62419