MySQL优化、回表、覆盖索引图文详解

MySQL优化

环境：MySQL5.7

一、总结

1、覆盖索引：

覆盖索引->减少回表次数-》MySQL5.6后增加了索引下推
Explain 的Extra字段， Using index condition
很多公司为了节约成本，存储介质使用的是机械硬盘，怕随机读取，随机寻址很消耗性能，
这时可以打开 MRR（multi range read），在回表前吧ID读取到buffer里面，进行排序，把随机操作变为顺序操作。
覆盖索引，因为是覆盖，所以可以避免排序用到的临时文件。
利用覆盖所以加最左原则，减少对索引的维护

2、普通索引：

适用于读多写少、唯一性不高或业务代码能保证唯一性的操作，能用到change buffer，把一些些操作缓存起来，提高写入的效率和内存命中率

3、SQL没有走索引：

1、索引字段用到了函数
2、2张表的编码不一样
3、字段类型不一样，如String赋给ID，会隐式调用cast方法
4、索引统计信息有问题 Analaze table重新统计所有信息

4、Explain可能会选错

涉及到回表操作和排序操作

5、索引慢的情况：

1、force index，强制走一个索引，不太好，业务应急方案，可能迁移到别的数据库就不行了还要做代码的重新发布
2、重复索引加最左原则，把选错的索引删了，较为常用的。

二、名词分析（回表、覆盖索引）

要想了解回表、覆盖索引，首先我们的得知道聚簇索引和普通索引

1、聚簇索引

创建规则

如果表设置了主键，则主键就是聚簇索引
如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引
以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引

注意事项

InnoDB的聚簇索引的叶子节点存储的是行记录（其实是页结构，一个页包含多行数据），InnoDB必须要有至少一个聚簇索引。
由此可见，使用聚簇索引查询会很快，因为可以直接定位到行记录。

2、普通索引

普通索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引。
InnoDB的普通索引叶子节点存储的是主键（聚簇索引）的值，而MyISAM的普通索引存储的是记录指针。

这里很关键，叶子节点存储的是主键（聚簇索引）的值，而不是行记录，下面我们会有例子详细说明。

3、建表语句和数据

create table user(
     id int(10) auto_increment,
     name varchar(30),
     age tinyint(4),
     primary key (id),
     index idx_age (age)
     )engine=innodb charset=utf8mb4;
insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('刘八',10);

4、聚簇索引（ClusteredIndex）

id 是主键，所以是聚簇索引，其叶子节点存储的是对应行记录的数据

5、普通索引（secondaryIndex）

age 是普通索引（二级索引），非聚簇索引，其叶子节点存储的是聚簇索引的的值

6、聚簇索引查找过程

如果查询条件为主键（聚簇索引），则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据。
如：select * from user where id = 1;

7、普通索引查找过程第一步

如果查询条件为普通索引（非聚簇索引），需要扫描两次B+树。
第一次扫描通过普通索引定位到聚簇索引的值。
第二次扫描通过聚簇索引的值定位到要查找的行记录数据。

如下面这个SQL：

1. 先通过普通索引 age=30 定位到主键值 id=1
2. 再通过聚集索引 id=1 定位到行记录数据

 select * from user where age = 30;

8、普通索引查找过程第二步

回表查询

 select * from user where age = 30;

先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。

索引覆盖

select id,age from user where age = 10;

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

如何实现覆盖索引

常见的方法是：将被查询的字段，建立到联合索引里去。

1、如实现：select id,age from user where age = 10;
explain分析：因为age是普通索引，使用到了age索引，通过一次扫描B+树即可查询到相应的结果，这样就实现了覆盖索引

2、实现：select id,age,name from user where age = 10;
explain分析：age是普通索引，但name列不在索引树上，所以通过age索引在查询到id和age的值后，需要进行回表再查询name的值。此时的Extra列的NULL表示进行了回表查询

为了实现索引覆盖，需要建组合索引idx_age_name(age,name)

drop index idx_age on user;
create index idx_age_name on user(`age`,`name`);

再次Explain，此时字段age和name是组合索引idx_age_name，查询的字段id、age、name的值刚刚都在索引树上，只需扫描一次组合索引B+树即可，这就是实现了索引覆盖，此时的Extra字段为Using index表示使用了索引覆盖。

哪些场景适合使用索引覆盖来优化SQL

分页查询
例如：select id,age,name from user order by age limit 100,2;
因为name字段不是索引，所以在分页查询需要进行回表查询，此时Extra为Using filesort文件排序，查询性能低下。

使用索引覆盖：建组合索引idx_age_name(age,name)

最左匹配原则：

索引可以简单如一个列 (a)，也可以复杂如多个列 (a,b,c,d)，即联合索引。

如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。
因此，列的排列顺序决定了可命中索引的列数。
例子：
如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)