Mysql的explain之Extra字段讲解

认真阅读了一下explain之Extra字段，前2篇关于一道sql （select * from t where a=? and b>? order by c limit 0,100 ） 如何加索引的问题答案终于浮出水面

Using index

表示使用了覆盖索引，性能上会快很多
覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

Using index condition

与 Using index 的区别在于，用上了索引（利用索引完成字段的筛选过滤），但是索引列不能够包含查询要求的所有字段，需要回表补全字段
回表是指，根据索引找到满足条件的id后，利用id回到主键索引上取出整行或者取出需要的字段

Using filesort

表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。
"排序"这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。
sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

• 全字段排序
  可以通过 OPTIMIZER_TRACE结果 查看
  "sort_mode": "<fixed_sort_key, additional_fields>"

• rowid排序
  如果sort_buffer中存放的字段数太多，会造成内存里能够同时放下的的行数很少，就会使用临时文件来进行排序，那么排序的效率就会很差
  控制排序的单行长度设置(就表t来说，max_length_for_sort_data值大于等于22时，均采用全字段排序) 具体语法为 SET max_length_for_sort_data = 21;
  可以通过 OPTIMIZER_TRACE结果 查看
  "sort_mode": "<fixed_sort_key, rowid>"

Using where

首先你需要知道的是Mysql数据库包含sever层(连接器、查询缓存、分析器、优化器、执行器)与引擎层(innodb,myisam,memory)
Using where表示sever层在收到引擎层返回的行后会进行过滤(即应用WHERE过滤条件)。即会根据查询条件过滤结果集。

结论

上面3者的性能开销是依次放大的，也就是说尽可能让Extra中出现情况依次为

Using index > Using index condition > Using filesort

这里的排序没有加上Using where，因为如果一个sql有where条件的话都会有这个阶段的

有了这个理论之后，我们再来解答前面2篇文章的结论（前2篇文章测试样例一致，这里只以第1篇结论进行解析）

idx_cab (c,a,b) / Using where; Using index

效果最好,不用回表，不用排序

idx_ac (a,c) / Using where

需要回表，不需要排序 (回表后完成b>5000的过滤条件)

idx_abc (a,b,c) / Using where; Using index; Using filesort

不需要回表，需要排序

idx_ab (a,b) / Using index condition; Using filesort

需要回表，需要排序 (回表前完成b>5000的过滤条件)

idx_a (a) / Using where; Using filesort

需要回表，需要排序 (回表后完成b>5000的过滤条件)

PRIMARY KEY (id) / Using where; Using filesort

全表扫描，且需要排序

结论更新（2019-09-14）

前面就之前的测试得出的结论是 idx_cab (c,a,b) 最优，idx_ac (a,c)次之。
其实还有一种测试没有做，idx_acb (a,c,b)，这种方式才是真正最优的。
分析：
题目上的sql（select * from t where a=? and b>? order by c limit 0,100 ）
a为等值比较，所以idx_acb (a,c,b)，将a放在第1位，在比较满足a的条件后，c依旧是有序的，不用专门去排序，接下来只需要依次取出来b的值进行（b>?）比较，满足条件的直接返回，当返回条数达到100条时结束遍历。

参考知识

前面提到的"全字段排序" “rowid排序” 一个sql到底使用的是哪种方式可以通过如下方式来查看：

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select * from t where a=10 and b>50000 order by c limit 0,100 ;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算 Innodb_rows_read 差值 */
select @b-@a;