你会看MySQL的执行计划(EXPLAIN)吗?
SQL 执行太慢怎么办?我们通常会使用 EXPLAIN 命令来查看 SQL 的执行计划,然后根据执行计划找出问题所在并进行优化。 用法简介
EXPLAIN 的用法很简单,只需要在你的 SQL 前面加上 EXPLAIN 即可。例如: explain select * from t;
PS:insert、update、delete 同样可以通过 explain 查看执行计划,不过通常我们更关心 select 的执行情况
你会看到如下输出: +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
执行计划结果字段说明如下表:
字段
JSON Name
说明
id
select_id
查询标识符
select_type
/
查询类型
table
table_name
查询记录所在表
partitions
partitions
查询匹配的分区(没有进行表分区,则为 NULL)
type
access_type
连接类型
possible_keys
possible_keys
可以选择的索引
key
key
实际上用到的索引
key_len
key_length
被用到索引的长度,比如联合索引中有几个被用到
ref
ref
与索引相比较的列
rows
rows
要扫描的行数(估算值)
filtered
filtered
按表条件过滤的行百分比
Extra
/
附加信息
EXPLAIN 的用法非常简单,看一眼就会。但是要根据输出结果找到问题并解决,就没那么容易了。就好比操作拍 CT 的机器可能相对简单,但要从 CT 成像中看出问题并给出治疗方案就需要丰富的知识和大量的临床经验了。
因此,我们需要知道每个字段代表什么指标;什么样的取值是我们想要的,什么样是需要优化的;最后还要知道如何优化成我们想要的值。 字段详解id
标识符。查询操作的序列号。通常都是正整数,但当有 UNION 操作时,该值可以为 NULL。
id 相同 explain select * from t1 where t1.id in (select t2.id from t2); +----+-------------+-------+------------+--------+---------------+--------+ | id | select_type | table | partitions | type | possible_keys | ... | +----+-------------+-------+------------+--------+---------------+--------+ | 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | .... | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | .... | +----+-------------+-------+------------+--------+---------------+--------+ 2 rows in set, 1 warning (0.00 sec)
id 不同 explain select * from t1 where t1.id = (select t2.id from t2); +----+-------------+-------+------------+-------+---------------+--------+ | id | select_type | table | partitions | type | possible_keys | ... | +----+-------------+-------+------------+-------+---------------+--------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | .... | | 2 | SUBQUERY | t2 | NULL | index | NULL | .... | +----+-------------+-------+------------+-------+---------------+--------+ 2 rows in set, 1 warning (0.00 sec)
id 包含 NULL explain select id from t1 union (select id from t2); +----+--------------+------------+------------+-------+---------------+-----------+ | id | select_type | table | partitions | type | possible_keys | ... | +------+--------------+------------+------------+-------+---------------+---------+ | 1 | PRIMARY | t1 | NULL | index | NULL | ... | | 2 | UNION | t2 | NULL | index | NULL | ... | | NULL | UNION RESULT | | NULL | ALL | NULL | ... | +------+--------------+------------+------------+-------+---------------+---------+ 3 rows in set, 1 warning (0.00 sec)
id 为 NULL 时,table 列值为 < unionM,n > 格式,表示该行为 id 为 m 和 n 联合的结果
id 顺序的规则:如果 id 相同,执行顺序由上到下;如果不同,执行顺序由大到小。 select_type
SELECT 类型,常见的取值如下表:
查询类型
JSON Name
说明
SIMPLE
/
简单 SELECT(没有 UNION 或子查询)
PRIMARY
/
查询包含 UNION 或子查询,则最外层的查询被标识为 PRIMARY
UNION
/
UNION 中的第二个或更后面的 SELECT 语句
DEPENDENT UNION
dependent (true)
UNION 中的第二个或更后面的 SELECT 语句,依赖于外部查询
UNION RESULT
union_result
UNION 的结果
SUBQUERY
/
子查询中的第一个 SELECT 语句
DEPENDENT SUBQUERY
dependent (true)
子查询中的第一个 SELECT 语句,依赖于外部查询
DERIVED
/
派生表 SELECT
DEPENDENT DERIVED
dependent (true)
派生表依赖于另一个表
MATERIALIZED
materialized_from_subquery
将子查询的结果物化(生成临时表)
UNCACHEABLE SUBQUERY
cacheable (false)
结果无法缓存且必须为外部查询的每一行重新计算的子查询
UNCACHEABLE UNION
cacheable (false)
UNION 中的第二个或更后面的 SELECT 语句,属于不可缓存子查询(参考 UNCACHEABLE SUBQUERY) UNION 或者子查询 MySQL 会自动产生临时表。派生表可以简单理解为具有别名的临时表。生成临时表的这个动作称为物化(水变成蒸汽叫汽化)
临时表通常在内存里,当其 size 超过一定范围会被存入磁盘 # 临时表 select * from t1 join t2 on t1.id = t2.id where t1.id > 1; # 派生表,临时表取个别名 select * from (select * from t1) t;type
连接字段为主键或者唯一索引,此类型通常出现于多表的join查询,表示对于前表的每一个结果,都对应后表的唯一一条结果。并且查询的比较是=操作,查询效率比较高。
取值
说明
system
表中只有一条记录,const 类型的特例
const
表中最多有一条匹配数据,用于主键或唯一索引的等值匹配
eq_ref
出现在多表查询中,前表结果中的每一条记录,在后表中有唯一的对应。同样是主键或唯一索引等值匹配
ref
普通索引的等值匹配(= 或 <=>)
fulltext
全文索引
ref_or_null
跟 ref 类似,增加了对 NULL 的判断
index_merge
合并索引(用到了两个及以上的索引)
unique_subquery
子查询用到主键或唯一索引
index_subquery
子查询用到普通索引
range
范围匹配( = 、 < > 、 > 、 > = 、 < 、 < = 、 IS NULL、 < = > 、 BETWEEN、 LIKE 或 IN)
index
扫描索引树(在覆盖索引的情况下优于 ALL)
ALL
全表扫描
还有一种 NULL 的情况,比如 select min(id) from t1,但 MySQL 官方没有提及这种情况,所以我们不在此讨论
性能从优到劣依次为:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
优化原则:最好做到 const,至少做到 ref,避免 ALL ref
查询中用来和索引比较的类型,如:id = 1,值为 const;如果是联合查询或者子查询则为关联的字段;如果使用了函数,则为 func。 Extra
Extra 用来存放一些附加信息,通常用来配合 type 的输出来做 SQL 优化。 扩展desc
desc 与 explain 作用相同,可以互相代替,后面的例子中均使用 desc 来查看执行计划。 format
explain/desc 还支持一些参数,format 顾名思义,是用来格式化输出结果的。它包括两种格式化方式:tree 和 json。
比如: desc format = tree select * from t1 where t1.id in (select t2.id from t2 where t2.id > 1);
输出格式如下: +----------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------+ | -> Nested loop inner join (cost=0.70 rows=1) -> Filter: (t2.id > 1) (cost=0.35 rows=1) -> Index scan on t2 using a2_uidx (cost=0.35 rows=1) -> Single-row index lookup on t1 using PRIMARY (id=t2.id) (cost=0.35 rows=1) | +----------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
执行计划结果以树形结构展示,可以清晰的看出语句之间的嵌套关系,还有基本的执行成本(cost)。
使用 json 方式: desc format = json select * from t1;
输出结构为一个 JSON 结构: +---------------------------------------------------+ | EXPLAIN | +---------------------------------------------------+ | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "0.35" }, "table": { "table_name": "t1", "access_type": "ALL", "rows_examined_per_scan": 1, "rows_produced_per_join": 1, "filtered": "100.00", "cost_info": { "read_cost": "0.25", "eval_cost": "0.10", "prefix_cost": "0.35", "data_read_per_join": "56" }, "used_columns": [ "id", "a1", "b1" ] } } } | +---------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
简介表中的 JSON Name 指的就是这里 JSON 结果的 key
json 格式会展示出更加详细的信息,可以看到执行成本划分的更加细致了,方便定位到慢 SQL 的问题具体出现在哪个环节。 analyze
除了 format 以外,explain/desc 还可以使用 analyze 参数: desc analyze select * from t1 where t1.id in (select t2.id from t2 where t2.id > 1);
输出结果: +-------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------+ | -> Nested loop inner join (cost=0.70 rows=1) (actual time=0.018..0.018 rows=0 loops=1) -> Filter: (t2.id > 1) (cost=0.35 rows=1) (actual time=0.016..0.016 rows=0 loops=1) -> Index scan on t2 using a2_uidx (cost=0.35 rows=1) (actual time=0.015..0.015 rows=0 loops=1) -> Single-row index lookup on t1 using PRIMARY (id=t2.id) (cost=0.35 rows=1) (never executed) | +-------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
可以看出,analyze 的输出结果是基于 format = tree 的
上面执行计划中(format = json/tree)的执行成本(cost)都是估值,而 analyze 中的执行成本是真实值。actual time 代表对应 SQL 执行的真实时间,单位为毫秒。 最后
执行计划的结果中,我们最关心的是 type,它能够最直接的反映出 SQL 执行效率处在什么级别。然后再结合其他字段(例如 Extra)来做更细致的分析。还可以通过各种参数,来分解每个环节的执行情况。
今天的内容就到这里,有哪些想要了解的可以留言告诉我。
OPPO猜谜式爆料引网友疯狂互动,ColorOS12主要功能呼之欲出9月9日,OPPOColorOS官博发布了六张图片,让网友们来猜测ColorOS12系统的新功能。这种类似于你画我猜的新互动方式,瞬间就吸引了众多网友的关注。那么,ColorOS1
随心持久GoPro9泰迅ALLINBOX收纳充电盒体验序当前短视频越来越火,成为很多人在闲暇之余刷手机必做的一件事,对于那些短视频制作爱好者来说,拥有专业的视频拍摄工具是非常重要的,抛开普通人常用手机拍摄视频的情况,那些专业的视频制作
一锅搞定挑剔女王美食之路多菜谱实测大宇电饭煲高压锅两用锅序俗话说工欲善其事必先利其器,女王就是这样的人,呜呼,果然,古人诚不欺我也!说说家里的情况,目前我们是一家五口,一家三口加上2个老人,在家做饭的时候,女王老嘀咕说没有趁手的锅灶,我
布鲁金斯学会只有21的美国人愿意乘坐自动驾驶汽车资本实验室今日创新观察聚焦前沿科技创新与传统产业升级张珂自动驾驶是近几年最热门的创新与投资领域之一,政府汽车厂商互联网公司和风险资本都投入了无比的热情。那么,普通居民是否对自动驾驶
坐上飞行汽车前,我们还要先过多少坎?资本实验室今日创新观察聚焦前沿科技创新与传统产业升级王进近日,吉利控股集团宣布旗下飞行汽车公司Terrafugia将于今年10月开始接受预定,首批产品将于2019年问世,首批用户将
面对自动驾驶的到来,汽车保险行业的变革已悄然开启资本实验室今日创新观察聚焦前沿科技创新与传统产业升级张珂1897年,美国俄亥俄州代顿市的GilbertJ。Loomis从美国旅行者保险公司购买了首个汽车保险。可以说,汽车保险行业的
4万人投票希望贝佐斯留在太空,而我希望他平安回来今日热搜排行榜出现了比较有意思的话题美国4万人投票希望贝佐斯留在太空。这让我想到了仇富心理,在本世纪,人们的仇富心理空前高涨,富人坐拥亿万财产,生活无忧,甚至要去太空旅行,还有一个
中国将成为全球最大的自动驾驶汽车市场资本实验室今日创新观察聚焦前沿科技创新与传统产业升级李鑫自动驾驶是当前最热门的新兴领域之一。毫无疑问,自动驾驶正在彻底改变汽车行业,并将对整个人类社会产生深远的影响。目前,老牌车企
商业航天通往太空旅程的新门票资本实验室今日创新观察聚焦前沿科技创新与传统产业升级李鑫自1969年美国实现人类首次登月之后,很长一段时间里,人类对月球的探索计划因各种原因变得沉寂。近年来,随着各国航天科技的持续
大变局汽车产量超过美国的5个国家资本实验室今日创新观察聚焦前沿科技创新与传统产业升级车轮上的国家,高度工业化的象征,美国梦的标志之一,世界上曾经最大的汽车生产国汽车,对于美国的意义无与伦比。但是,几十年来,美国开
120Hz手机哪款值得买?手把手带你选手机已经成为现代人最重要的随身工具,成了必不可少的东西,如何提升使用者手机操控体验,是各手机品牌必然考虑的事情,在保持性能水准不相上下的时候,于是开始在提高手机屏幕刷新率上做文章,