Mysql索引篇（四） 覆盖索引(Using index)、文件排序(Using filesort)和临时表(Using temporary)

本系列文章目录

展开/收起

• Mysql索引篇（一） 索引的数据结构B+树
• Mysql索引篇（二） Myisam和Innodb的索引
• Mysql索引篇（三） 善于explain分析sql语句
• Mysql索引篇（四） 覆盖索引(Using index)、文件排序(Using filesort)和临时表(Using temporary)
• Mysql索引篇（五） Sql优化建议和例子

Using index 覆盖索引

概念：一个索引（B+树）中包含所有需要查询的字段的值，称为覆盖索引。覆盖索引的一个特点是无需回表。
覆盖索引不是一种索引类型，不是一个名词，而是一个动词。

下面举一个渐进的例子来描述覆盖索引在底层上是怎么做的：
比如，我给 col1 字段设置了一个普通索引，给id设置了主键索引，使用的是innodb的表。

1.Select * from t;
全表扫描，在底层表现为在聚集索引这棵树中把所有叶子节点一个个的读取到内存中，获取每个叶节点内的行数据。

2.Select col1 from t;
Mysql检查到 col1 字段是索引，所以会直接去索引获取col1字段，因为col1索引B+树中就包含col1字段的值，无需拿到叶子节点中存储的主键id值再跳到聚集索引获取col1的数据。此时直接找到二级索引的B+树将所有叶子节点的col1的值获取到。这个过程只查了col1二级索引的B+树，没有去查主键索引的B+树，因此这个例子就用到了覆盖索引。

3.假设我现在把col1的索引删了，再去执行 select col1from t;请问会发生什么变化？

我们还是要在脑袋里构建B+树的图：
有col1索引的时候，会直接去col1的B+树中去找，并且把所有叶子节点读取到内存中（假设有n个叶子节点，3层，就进行了3-1 + n-1=n+1次io操作），并在内存中找出每个叶子节点的col1值。

没有col1索引的时候，会去主键索引的B+树中去找，并且也把所有的叶子节点读取到内存，也是发生了 n+1 次io操作。

好像没什么区别，都是n+1次io操作，但真的没区别吗？

主键索引的叶子节点存了所有表字段的数据，每一个节点从磁盘读取到内存的时候都会把一行中所有表字段的数据读到内存。然后在内存中，mysql程序再从多个行的多个表字段中获取其中的col1字段。
col1索引的叶子节点只存了col1和id这两个字段的数据。每一个节点从磁盘读取到内存的时候只把col1和id的值加载到内存。然后只要col1，不要id。

也就是说虽然两者之间的io操作次数相同，但是前者每次io操作的速度比后者快，因为二级索引每个节点的数据比主键索引的每个节点的数据少，io读写的数据量不同会影响io的速度的。

为了读取col1字段而把其他全部字段都读取到内存，这就没有用到覆盖索引，还是一个全表扫描。

我有试过在一个100万的文章表中执行 select title from article 查询所有title。
加title索引的时候耗时0.03秒，不加title索引的时候耗时56秒。

所以不要小看 覆盖索引 的作用，它在特定情境下可以带来极大的优化。

4.Select id from t;
分别在innodb引擎和myisam引擎下执行这个语句，请问有没有用到覆盖索引？
答案是有，因为id建立了主键索引，所以直接会去主键索引的树中读取所有叶节点的key但是不会去读取value（主键索引中叶节点的key就是id）。两种引擎下都没有回表。

5.Select id from t where col1>100;
假设我给col1加了普通索引，分别在innodb引擎和myisam引擎下执行这个语句，请问有没有用到覆盖索引？

答案是，innodb和myisam都用到了二级索引查col1（type是range），但是innodb引擎用到了覆盖索引，myisam没用到覆盖索引。

首先，加了条件 col1>100后，就会去col1这个二级索引的树中查（而不会直接在主键索引中查）满足 col1>100的叶子节点，innodb和myisam都需要做这一步。

但是innodb的二级索引的叶子节点存储着col1的值和对应的id值。因此只需访问二级索引这一棵B+树就能获取到全部id，无需回表到主键索引去拿id字段。
而myisam的二级索引的叶子节点中，叶子节点只有col1，没有id，所以需要通过叶子节点存储的行地址到MYD文件中找对应的行，再从这些行中提取id字段。

也就是说，innodb在这个过程中没有回表，而myisam发生了回表。

我有试过在一个100万的文章表中执行 select id from article where create_time > 1586421023 。
Create_time加了索引，在innodb的表中查只花了0.7秒，在myisam的表中查花了31.67秒

6.Select id from t where col2>100
现在我添加了联合索引 index col1_col2 (col1,col2)。
请问在innodb引擎下，是否用到了覆盖索引？

答案是用到了联合索引的覆盖索引，只是条件查找没有用到联合索引而已（意思是 Extra中有Using index，但是type中没有出现range而是All）。
底层发生了什么？首先 mysql 会思考说主人想搜索id，id在主键索引和二级索引这两棵树中都有，可是如果在主键索引中搜，为了判断col2>100这个条件，mysql会把所有叶子节点从磁盘读到内存，主键索引的叶子节点是包含很多字段的，这会很慢。如果在联合索引中搜索，由于col2>100不符合联合索引的最左前缀原则，所以mysql也会把全部叶子节点从磁盘读到内存，在内存中筛选 col2>100的节点，并获取id字段的值。
所以这就是为什么用到了覆盖索引，但是范围查询没用到联合索引的原因。

7.Select col1,col2,col3 from t where col1 > 100;
现在我添加了联合索引 index col1_col2 (col1,col2)。
请问在innodb引擎下，是否用到了覆盖索引？

答案是范围查询用到了二级索引（因为where col1>100遵循了最左前缀原则），但是查字段的时候由于col3不在二级索引的叶子节点中，所以需要回表到主键索引的叶子节点中找col3字段。所以没有用到覆盖索引。
不过因为在二级索引中用到了range,所以mysql不会加载所有主键索引的叶子节点，而是加载对应id的叶子节点。

8.Select col1,col2,col3 from t where col2 > 100;
这句完全没用到联合索引，单纯的一个全表扫表，直接在主键索引读取所有的叶节点。Col2的条件判断和col1,col2,col3的字段提取全在内存中计算完成。

总结：无论判断一条sql有没有用到索引，有没有用到覆盖索引，都可以通过画一个B+树的图来分析。知道底层原理，sql优化变得有理有据，不知道底层原理，sql优化就只能凭感觉。

 

 

Using FileSort 文件排序

在Sql优化中，我们希望尽可能不要出现文件排序，因为出现了文件排序意味着没有使用到索引构建好的排序，而是需要在内存中对字段进行重新排序，排序的过程是计算的过程比较消耗cpu。

多字段排序要尽量遵循最左前缀原则，而且不要对一个字段升序对另一个字段降序，否则也会使用到Using filesort

如果一定会发生 Using filesort，那么我们要了解的文件排序有两种方式：双路排序和单路排序。

举个例子：

一个表建立的联合索引 index age_salary (age, salary)

Select * from t where id>500 and id<1000 order by  salary, age;

上面的例子中：

双路排序会在二级索引的B+树取出满足where条件的行(501~999行)的salary和age字段（不会取其他字段），和501~999行的地址指针，然后在sort buffer内存中排序。如果sort buffer不够（要排序的salary和age太多了），此时会创建一个 temporary table 存储结果（临时表的出现意味着更多次的io）。排完序之后再根据行指针（这是的行指针也是排好序的）回表（回到主键索引）取记录（排序的时候只取了要排序的字段，现在回表是要取整行的所有字段）。

 

单路排序会取出满足where条件的行(501~999行)的所有字段（不过这样更容易生成临时表，这样的话io反而会比双路排序高），然后再sort buffer中根据salary 和 age字段排序，然后直接输出结果。

 

由于双路排序发生了回表，所以大大增加了io次数（是单路排序的两倍，如果单路排序不生成临时表的话）， 但是单路排序的内存开销更大，更容易在排序过程中生成临时表，从而增加io次数。

Mysql会根据情况选择其中当然一种算法来进行文件排序filesort。但无论是哪种排序我们都可以通过提高 sort_buffer_size 和 max_length_for_sort_data来增大排序缓冲区的大小，减小创建临时表的可能。

 

结论：在非要出现文件排序不可的情况下，可以通过增大排序缓冲区的大小来优化

 

Using Temporary  使用临时表

临时表可以有我们用户手动创建，也可能是在执行sql是mysql在内部创建。我们只讨论后者。
MySQL临时表分为“内存临时表”和“磁盘临时表”，其中内存临时表使用MySQL的MEMORY存储引擎，磁盘临时表使用MySQL的MyISAM存储引擎；一般情况下，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表；

mysql会在什么时候创建内部临时表？（一般都是内存不够用的时候）
A.在排序或者分组过程中由于内存不足而导致mysql创建临时表进行额外存储。
B.在JOIN查询中，ORDER BY或者GROUP BY使用了不是第一个表的列
C.排序或分组时，表包含TEXT或者BLOB列（这样对于单路排序而言sort buffer肯定不足）；
D.GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列；
E.使用UNION或者UNION ALL时，SELECT子句中包含大于512字节的列；
最后3个是直接使用磁盘临时表。

为了避免使用到临时表，我们可以在排序和分组的时候尽量是去对索引的字段来排序分组，而且不能让索引失效。再者拆分长度很长的列，例如将Text或者Blob类型的字段垂直分表到另一张表中。

临时表的危害是大大增加io次数，严重时导致磁盘读写压力过大。

本文转载自： 张柏沛IT技术博客 > Mysql索引篇（四） 覆盖索引(Using index)、文件排序(Using filesort)和临时表(Using temporary)