explain是mysql中用于查看查询执行计划(QEP)的语法，语法：explain select语句;

Explain的具体查看功能

  1. 表的读取加载顺序
  2. 数据读取操作的操作类型
  3. 哪些索引可以被使用
  4. 哪些索引实际被使用
  5. 表之间的引用
  6. 每张表有多少行被查询优化器查询到

Explain的各种字段表示的含义

mysql> explain select * from test;
+——+——————-+———-+———+———————-+———+————-+———+———+———-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+——————-+———-+———+———————-+———+————-+———+———+———-+
其中每一行代表一个子查询
其中最重要的字段：id、type、key、rows、Extra

  1. id字段：表的读取加载顺序

id代表子查询执行或表(由table字段指明)加载的优先级。
当id值不同时，id值越大，优先级越高，越优先执行。
当id值相同时，表示从上往下顺序执行。

  1. select_type：数据读取操作的操作类型
    1. simple：简单子查询，查询中不包括子查询或union联合查询（复杂查询）
    2. primary：查询中包括任何复杂查询，最外层标记为primary
    3. subquery：出现在select 或 where 中的子查询
    4. derived：出现在from中的子查询
    5. union：出现在union联合查询的第二个select查询
    6. union result：union联合查询的临时结果表
  2. table：扫描哪张表
  3. type：访问类型，代表了查询优化的程度，是一个重要的指标

注意以下：唯一性索引、非唯一性索引、索引的区别。
以下选项效果依次变差，一般要求到达range，最好能达到ref。

  1. system：表中只有一条记录，最完美的访问类型
  2. const： 使用基于primary key 或unique key 的等值查询，并且右值是一个常量，并且最终匹配了一条记录

mysql> explain select from (select from stu where name = ‘Jack’) sub_stu;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+——————-+——————+————+———————-+———+————-+———+———+———-+
| 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | stu | const | name | name | 303 | | 1 | |
+——+——————-+——————+————+———————-+———+————-+———+———+———-+

  1. eq_ref：唯一性索引扫描，对于每一个唯一性索引，表中只有一条记录与之匹配。通常也是使用基于primary key 或unique key 的等值查询，并且最终匹配了一条记录，与const的区别在于：右值不是一个常量。

    explain select s., d. from stu s, department d where s.department_id = d.id;

  2. ref： 非唯一性索引扫描，返回匹配某个单独值的所有行，本质是索引查找，但会匹配到多行记录。属于（索引查找+扫描）。

mysql> explain select * from stu where age = 20; （对age字段建立了普通索引）
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+——————-+———-+———+———————-+——————-+————-+———-+———+——————-+
| 1 | SIMPLE | stu | ref | inx_stu_age | inx_stu_age | 5 | const | 2 | Using where |
+——+——————-+———-+———+———————-+——————-+————-+———-+———+——————-+

  1. range：基于索引并且where子句中使用了between、>、<、in等比较运算符
  2. index：索引表的全表扫描 explain select id from stu;
  3. all：数据表的全表扫描 explain select * from stu;
    1. possible_keys：代表哪些索引可以被使用，查询字段涉及到的可能使用的索引
    2. key：代表哪些索引实际被使用，若查询使用了覆盖索引，则索引列表仅出现在key列表中。

覆盖索引指：select子句中的查询字段的个数和顺序与复合索引的个数和顺序一致（或少于）。简单来说， 查询列被索引覆盖。
mysql> explain select name, age from stu;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+——————-+———-+———-+———————-+————————+————-+———+———+——————-+
| 1 | SIMPLE | stu | index | NULL | index_name_age | 308 | NULL | 2 | Using index |
+——+——————-+———-+———-+———————-+————————+————-+———+———+——————-+

  1. key_len：索引字段的最大可能长度（字节数），而不是实际使用的字节数。
  2. ref：表之间的引用，前面6字段中已经指出了实际使用了哪些索引，ref字段代表了这些索引的值引用的是哪个数据库中的哪张表的哪个字段，如果索引值是一个常数，则显示为const。只显示查询使用到的索引字段值，不显示排序或分组使用到的索引字段值。

mysql> explain select s., d. from stu s, department d
where s.department_id = d.id and d.name = ‘computer’;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+——————-+———-+———-+———————-+———————-+————-+———-+———+——————-+
| 1 | SIMPLE | d | const | PRIMARY,name | name | 303 | const | 1 | Using index |
| 1 | SIMPLE | s | const | department_id | department_id | 5 | const | 1 | |
+——+——————-+———-+———-+———————-+———————-+————-+———-+———+——————-+

  1. rows：根据表统计信息以及建立的索引，估算出每张表有多少行被查询优化器查询到
  2. Extra：一些额外的重要信息
    • Using filesort：使用了文件排序，发生在：当order by子句中排序字段的个数或顺序与复合索引的顺序不一致时，索引本身的排序无法利用，需要重新对记录进行排序。
      • 建立复合索引：create index idx_stu on stu(name,department_id,age);
      • 无法使用索引本身的顺序：

mysql> explain select * from stu where name = ‘jack’ order by ag
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
+——+——————-+———-+——————+———+———————-+————-+————-+———-+———+————
| 1 | SIMPLE | stu | NULL | ref | idx_stu | idx_stu | 303 | const | 1 | 100.00 | Using where; Using index; Using filesort |
+——+——————-+———-+——————+———+———————-+————-+————-+———-+———+————

  • Using temporary：使用了临时表存储临时结构，发生在：当order by子句中排序字段或group by子句中分组字段的个数或顺序与复合索引的顺序不一致时

mysql> explain select * from stu where name in (‘jack’, ‘Rose’) group by department_id, age;+——+——————-+———-+——————+———-+———————-+————-+————-+———+———+—————+—————-
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
+——+——————-+———-+——————+———-+———————-+————-+————-+———+———+————
| 1 | SIMPLE | stu | NULL | index | idx_stu | idx_stu | 313 | NULL | 2 | 100.00 | Using where; Using index; Using temporary; Using filesort |
+——+——————-+———-+——————+———-+———————-+————-+————-+———+———+————

  • Using index：使用了覆盖索引（covering index），避免了访问表的数据行，并且如果如果有Using where，表示覆盖索引用于数据行的查找，如果没有，表示用于数据行的读取。

mysql> explain select name, department_id, age from stu;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
+——+——————-+———-+——————+———-+———————-+————-+————-+———+———+————
| 1 | SIMPLE | stu | NULL | index | NULL | idx_stu | 313 | NULL | 2 | 100.00 | Using index |
+——+——————-+———-+——————+———-+———————-+————-+————-+———+———+————

  • Using join buffer：使用了连接缓存，缓存大小可以在配置文件中设置。
  • Using where：使用了where子句
  • Impossible where：where筛选条件始终为false
  • 其余不常用