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 类型
select_type 可选值 |
含义 |
---|---|
SIMPLE |
简单的 select,没有使用 UNION 或者 子查询 |
PRIMARY |
最外一层的 select |
UNION |
UNION 中第二个或者后面的 select 语句 |
DEPENDENT UNION |
UNION 中第二个或者后面的 select 语句,依赖于外层的 select |
UNION RESULT |
UNION 的结果 |
SUBQUERY |
子查询的第一个 select |
DEPENDENT SUBQUERY |
子查询的第一个 select,取决于外层的 select |
DERIVED |
派生表 |
MATERIALIZED |
Materialized subquery |
UNCACHEABLE SUBQUERY |
无法缓存结果的子查询,必须为外部查询的每一行重新计算其结果 |
UNCACHEABLE UNION |
UNION 查询中不可缓存的子查询中的第二个或者后一个 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 |