mysql调优之——执行计划explain

1.what is explain(explain 是个什么东东)

explain(解释)，在 Mysql 中 作为一个关键词，用来解释 Mysql 是如何执行语句，可以连接 select 、delete、insert、update 语句。

通常我们使用 explain 连接 一条 select 语句，查看运行状态，判断是否需要优化。

2.how to use explain（如何使用呢）

栗子：

explain select s.name,s.id,s.age,s.create_time from student s;   

输出：

  

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

  

官方：

EXPLAIN  [explain_type]  explainable_stmt


explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

输出的列名：

• id : select 标识符
• select_type:select 类型
• table：输出行对应的表
• partitions：匹配的分区
• type：join 类型
• possible_keys：可选的索引，可以通过 show index from tbl_name 查看表有哪些索引。
• key：实际选择的索引
• key_len：实际使用索引的长度
• ref：与索引比较的列
• rows：扫描行数的预估值
• filtered：按表条件筛选的行的百分比
• Extra：额外信息

3.重点关注的列

3.1 type 列

type 列描述了表的 join 类型，以下以 查询的最优到最差的排序列出了可能值：

• system ：当表只有一条数据（= system table）时，为 system 类型，是 const 类型的 特例。
• const：当表最多只有一条数据相匹配时，为 const 类型。因为只有一行，所以优化器的其余部分可以将此行列中的值视为常量（constant）。const表非常快，因为它们只读一次。在使用 主键 或者 唯一索引 和常量比较时，即为 const 类型。 如下的查询，tbl_name 可以被用作 const 表：

SELECT * FROM tbl_name WHERE primary_key=1;

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

栗子：

explain select s.* from student s where s.id = 1

输出：

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s     | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

• eq_ref：通常出现在多表 join 查询，并且 关联的字段是 主键 或者 唯一非空索引，即后表 只能匹配一条数据。 下面的示例，可以使用 eq_ref join 来处理 ref_table：

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 查询，关联使用了 最左前缀原则的索引 或者 关联的是非主键 或者 非 唯一索引（也就是说，join 不能根据索引选择 单行数据） 下面的示例，Mysql 可以使用 ref join 来处理 ref_table:

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：使用全文索引执行 join
• ref_or_null：在 ref 的基础上 ， 另外还搜索了包含空值的行 下面的示例，Mysql 可以使用 ref_or_null join 来处理 ref_table:：

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

• index_merge: 合并索引优化
• unique_subquery：子查询返回唯一主键。 形如下面的示例：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

• index_subquery：和 unique_subquery 类似，只不过在子查询中使用非唯一索引 形如下面的形式：

value IN (SELECT key_column FROM single_table WHERE some_expr)

• range：当 索引和常量 使用 诸如=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() 进行比较时 ，可以使用 range. 例如：

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：和 all 类似 ，只不过 扫描的是 索引树
• all：全表扫描，可以通过增加 索引避免 全表扫描

3.2 keys 列：真正使用的索引

3.3 rows 列：扫描的记录数

4.使用 explain 提升查询性能案例分析

假设有如下的 sql：根据订单日期 和 店员id 查询 订单信息（已创建了订单日期的索引），查询结果返回 18条记录。

  SELECT * FROM orders
  WHERE YEAR(o_orderdate) = 1992 AND MONTH(o_orderdate) = 4
  AND o_clerk LIKE '%0223';

Explain 输出执行计划：

问题所在：

• 根据 type 为 ALL ， 查询进行了全表扫描，被扫描的记录 rows 为 150万。
• possible_keys 和 ky 均为空 ，订单日期索引完全失效，原因在于被索引的字段使用了处理函数导致索引失效

4.1.修改sql 保证 订单日期索引正常

 SELECT * FROM orders
  WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
  AND o_clerk LIKE '%0223';

重新使用 Explain 查看 执行计划：

发现：type 由 ALL 变为 range ，订单日期索引得以利用，被扫描的记录由 15万 降为 3.3万左右。

4.2.另一个优化点在 店员字段的过滤

为 店员字段创建索引：

CREATE INDEX i_o_clerk ON orders(o_clerk);

再次输出执行计划：

发现：基本上并没有什么变化，新建的索引没有被利用，原因在于 该字段是 模糊查询，过滤指定后缀的 店员信息。但是索引对于后缀过滤会失效（尽管索引对于前缀有效果）。

修改sql，全量过滤店员字段：

SELECT * FROM orders
WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
AND o_clerk LIKE 'Clerk#000000223';

再次输出执行计划：

发现：可用索引增加，真正使用的索引变为 店员字段上的索引，被扫描的行由 3.3万降为 1546。

4.3.对于多条件查询，可以考虑使用组合索引

创建如下索引：

CREATE INDEX io_clerk_date ON orders(o_clerk, o_orderdate)

  ** ：这里将 o_clerk 放在 o_orderdate 之前，因为 o_orderdate 使用了 范围,最左前缀索引原则。

再次输出执行计划：

发现：使用了组合索引，被扫描记录即为输出的18条记录。效率已最优化。

多次优化的总结：

Type	Possible keys	Key	Rows Scanned	Duration (seconds)	Extra info	Rows returned
all	NULL	NULL	1.50M	1.201	Using where	18
range	i_o_orderdate	i_o_orderdate	32642	0.281	Using index condition; Using where	18
range	i_o_orderdate, i_o_clerk	i_o_clerk	1546	0.234	Using index condition; Using where	18
range	i_o_orderdate, i_o_clerk, i_o_clerk_date	i_o_clerk_date	18	0.234	Using index condition	18

欢迎关注 编程那点事儿，随时随地，想学就学~