使用explain分析sql语句

mysql的explain语句能查看sql的执行计划,根据执行计划能分析写的sql的执行效率.是优化sql的常用工具之一.

模拟数据

首先模拟订单数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


drop table if exists tbl_order_info;
CREATE TABLE `tbl_order_info` (
                      `id` int(11) NOT NULL auto_increment,
                      `amount` decimal(12,2) DEFAULT NULL,
                      `user_no` varchar(32) DEFAULT NULL,
                      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
                      `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
                      PRIMARY KEY (`id`),
                      KEY `idx_user_no` (`user_no`),
                      KEY `idx_amount` (`amount`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

drop procedure if exists insert_tbl_order_info;
delimiter ;;
create procedure insert_tbl_order_info()
begin
    declare i int;
    set i=1;
    while(i<=1000)do
    insert into tbl_order_info(amount, user_no) values(i, i+1);
    set i=i+1;
        end while;
end;;
delimiter ;
call insert_tbl_order_info();

执行explain

查询user_no为999的订单信息.查看此sql的执行计划.

1
2


explain select * from tbl_order_info 
where user_no='999'; 

输出:

1
2
3
4
5
6


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

执行计划显示,select_type:是简单查询,key:查询使用idx_user_no索引,type:基于普通索引的等值查询,ref:和索引比较的是常量.

explain输出字段含义

列名	解释
id	查询编号
select_type	查询类型:显示本行是简单还是复杂查询
table	涉及到的表
partitions	匹配的分区:查询将匹配记录所在的分区.仅当使用 partition 关键字时才显示该列。对于非分区表,该值为 NULL。
type	本次查询的表连接类型
possible_keys	可能选择的索引
key	实际选择的索引
key_len	被选择的索引长度:一般用于判断联合索引有多少列被选择了
ref	与索引比较的列
filtered	按条件筛选的行的百分比
Extra	附加信息

explain中select_type

select的值	解释
SIMPLE	简单查询 (不使用关联查询或子查询)
PRIMARY	如果包含关联查询或者子查询,则最外层的查询部分标记为 primary
UNION	联合查询中第二个及后面的查询
DEPENDENT UNION	满足依赖外部的关联查询中第二个及以后的查询
UNION RESULT	联合查询的结果
SUBQUERY	子查询中的第一个查询
DEPENDENT SUBQUERY	子查询中的第一个查询,并且依赖外部查询
DERIVED	用到派生表的查询
MATERIALIZED	被物化的子查询
UNCACHEABLE SUBQUERY	一个子查询的结果不能被缓存,必须重新评估外层查询的每一行
UNCACHEABLE UNION	关联查询第二个或后面的语句属于不可缓存的子查询

explain中type

type的值	解释
system	查询对象表只有一行数据,且只能用于 MyISAM 和 Memory 引擎的表,这是最好的情况
const	基于主键或唯一索引查询,最多返回一条结果
eq_ref	表连接时基于主键或非 NULL 的唯一索引完成扫描
ref	基于普通索引的等值查询,或者表间等值连接
fulltext	全文检索
ref_or_null	表连接类型是 ref,但进行扫描的索引列中可能包含 NULL 值
index_merge	利用多个索引
unique_subquery	子查询中使用唯一索引
index_subquery	子查询中使用普通索引
range	利用索引进行范围查询
index	全索引扫描
ALL	全表扫描
上表的这些情况,查询性能从上到下依次是最好到最差.

explain中Extra

Extra常见的值	解释
Using filesort	将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序
Using temporary	需要创建一个临时表来存储结构，通常发生对没有索引的列进行 GROUP BY 时
Using index	使用覆盖索引
Using where	使用 where 语句来处理结果
Impossible WHERE	对 where 子句判断的结果总是 false 而不能选择任何数据
Using join buffer (Block Nested Loop)	关联查询中，被驱动表的关联字段没索引
Using index condition	先条件过滤索引，再查数据
Select tables optimized away	使用某些聚合函数（比如 max、min）来访问存在索引的某个字段

sql分析的其他方式

show profile,trace等工具也能分析我们的sql.explain是查看执行计划,show profile是查看sql执行过程中各部分的耗时. trace是查看sql执行过程中的详细信息,使用了哪些索引,为什么使用,为何选取什么样子的排序方式等.

文章目录