在平时工作中我经常会找慢sql查询，然后我习惯的找出来慢sql,会使用explain命令去查看sql查询执行计划，看下这个sql是不是扫描的全表查询，还在通过索引查询。我就去记录一下我平常如何优化sql。

1
explain select * from test;

查询

使用expain出来的信息有12列，分别是id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra。

在mysql 5.7以前的版本中，想要显示partitions需要使用explain partitions命令；想要显示filtered需要使用explain extended命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。

####id含义

id为SELECT的标识符。它是在SELECT查询中的顺序编号。如果这一行表示其他行的union结果，这个值可以为空。在这种情况下，table列会显示为形如<union M,N>，表示它是id为M和N的查询行的联合结果。

  1. id不相同，执行顺序id值越大，越先被执行；
  2. id相同时，可以认为是一组，从上往下顺序执行；
  3. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；

select_type

查询中每个select子句的类型,下面是具体类型的分析

  1. SIMPLE: 简单SELECT或者连接查询时,不使用UNION或子查询等，外层的查询为SIMPLE，且只有一个;
  2. PRIMARY: 查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY,且只有一个;
  3. UNION:union连接的select查询，除了第一个表外，第二个及以后的表select_type都是union;
  4. DEPENDENT UNION:与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响;
  5. UNION RESULT: 包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null;
  6. SUBQUERY:除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery ;
  7. DEPENDENT SUBQUERY：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响; (5.5版本之前使用select in查询会出现subquery,之后版本优化，使用in查询不会出现DEPENDENT SUBQUERY，而是SIMPLE类型)
  8. DERIVED: from字句中出现的子查询;
  9. materialized: Materialized subquery
  10. UNCACHEABLE SUBQUERY:对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）
  11. UNCACHEABLE UNION：UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）

table

显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。

partitions

5.7版本之前版本是通过explain partitions 显示该列，5.7版本之后改成默认选项。该列显示的分区表命中分区情况，不是分区表该列为null。

type

表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： NULL，system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL。性能依次从好到差。

分类
  • 单独查询: NULL，system，index_merge，rangeindex，ALL;
  • 多表join查询: eq_ref,ref,ref_or_null;
  • 子查询:unique_subquery,index_subquery;
  • index_merge之外的用到了一个索引
类型分析

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

system:表中只有一行数据，且用于engine是myiasm和memory类型的表。这个类型是特殊const类型

const:使用唯一索引或者主键等值去查询扫描，返回的数据一定是一行数据并且等于where条件的值。const查询速度非常快，因为他只去读区一次就可以了。

1
2
3
4
SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref:此类型通常用多表join查询，前表每一个结果都能匹配到后表的一行结果，并且后表的连接字段主键和唯一索引，数据必须是not null。查询效率比较高。

1
2
3
4
5
6
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

ref: 此类型通常用多表join查询，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能会出现，常见的与辅助索引放入等值查找。或者在主键和唯一索引中，使用第一个列之外的列作为等值 查找也会出现，或者返回数据不唯一的等值查询也可能会出现。

1
2
3
4
5
6
7
8
SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

fulltext:全文索引检索。 * 全文检索全文检索的优先级很高，若全文检索索引和普通索引同时存在的时候，优先选择全文检索索引。

ref_or_null: 与ref方法类似，增加null值的比较。

1
2
SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

index_merge: 表示查询使用两个以上的索引，最后取交集或者并集，常见的and、or的条件使用了不通的索引，效率不是很高。

unique_subquery: 用于where中的in形式的子查询，子查询返回不重复值唯一值;

1
value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery:用于in形式子查询使用到了辅助索引或者in常数列表，可以使用查询去重。

1
value IN (SELECT key_column FROM single_table WHERE some_expr)

range:表示使用索引范围查询，通过索引字段范围获取表中部分数据记录，这个类通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() 操作中.

1
2
3
4
5
6
7
8
9
10
11
SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

index: 全索引扫描(Full Index Scan)，把索引全部扫描一遍。index与ALL区别为index类型只遍历索引树

all: 全表扫描数据文件(Full Table Scan),MySQL将遍历全表以找到匹配的行

possible_keys

查询可能使用到的索引都会在这里列出来

key

查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

key_len

用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

extra

如果你想要优化你的查询，那就要注意extra辅助信息中的using filesort和using temporary，这两项非常消耗性能，需要注意。

**Using filesort:**当 Extra 中有Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

**Using temporary:**查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

using index: “覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错

filtered

使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

参考文章:

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

mysql 查询优化 ~explain解读之select_type的解读

mysql 查询优化 ~explain解读之type的解读