MySQL explain详解
前言
MySQL提供explain关键字为我们提供语句SQL如何执行的信息,explain可用于 select,delete,insert,replace和update语句前。select
语句之前增加explain
关键字,MySQL会在查询上设置一个标记,使语句执行时只会返回select
语句的执行计划,但不执行select
语句,使开发者可以根据执行计划开发者可特定策略提高SQL语句性能。该文主要参考自MySQL官方文档EXPLAIN Output Format。
explain返回结果列解析
-
id:标识SQL中的select执行顺序
- id相同时,查询语句从上往下查询
- id不同时,id越大的执行优先级越高
-
select_type:查询类型
类型 说明 SIMPLE(常见) 简单查询,在当今数据量爆炸、单表操作常见的时代只需了解该类型即可 PRIMARY 包含union或者子查询,最外层查询语句类型标记为primary SUBQUERY 在SELECT或where列表中的子查询 UNION union关键字后的查询都被标记为UNION DERIVED 派生表,from后的子查询,如select * from (select …) MATERIALIZED 物化子查询,出现在 in (select … where)子查询条件没有索引的情况 … … -
table:执行的select查到是哪个表
-
partitions:查询将匹配记录的分区
-
type(access_type,重点):访问(连接)类型,表示获取所需数据行的方式
类型 说明 效率 system 该表只有一行(=系统表),这是const防伪标类型的特例。 S+ const 表中最多具有一个匹配行,当使用主键或唯一索引字段进行=判断时使用。 S eq_ref 表间连接使用等值比较的列索引为主键或唯一非空索引时(如user_base join user_detail on user_base.id=user_detail.user_id 或 where user_base.id = user_detail.user_id,a.id为主键索引)使用该类型访问数据 A ref 使用=或<=>(注:<=>其实就是=,不是<或>或=)运算符进行条件比较普通索引列时使用该类型,如where user_id=1,user_id为普通索引 B fulltext 使用FULLTEXT 索引执行联接 C ref_or_null 与ref类似,只是条件多出了is null判断,如col=1 or col is null D index_merge 索引合并优化,仅合并来自单个表的索引扫描,而不合并多个表的扫描,possible_keys列出现可能使用的索引数组。如explain select * from user_detail where (city = ‘火星’ or user_id = 21),city与user_id都有索引,Mysql将进行合并优化 E unique_subquery(少用可忽略) 一个索引查找函数,替代某些in子查询以提高效率。 F index_subquery(少用可忽略) 类似于 unique_subquery,用于某些形式下的in子查询中的非唯一索引 G range where条件范围查询索引列 H index 全索引扫描,只扫描索引树而不扫描数据树 I ALL 全表扫描,该考虑条件索引了 渣渣 -
possible_keys:查询中可能会被使用的索引
-
key:实际使用的索引
注:key会命名possible_keys中不存在的索引
-
key_len:实际使用的索引列长度(如int列索引长度为4)
若索引列允许NULL值,则该索引列长度比NOT NULL长度大1,如int类型索引列可空则使用该索引时key_len=5。varchar与char的实际长度会根据编码类型更改,在utf8编码环境下实际长度+2,可参考如下2图:
-
ref(不常用):将哪些列或常量与该key列中命名的索引进行比较,以从表中选择行。
当为索引列等值判断时该值为const;当为表间连接时则值为连接字段;当值为func,则使用的值是某些函数的结果,比较失败则为null。
-
rows:MySQL认为执行查询必须检查的行数
InnoDB引擎下,该数值只是估计值,不一定准确
-
filtered:将按表条件筛选的表行的估计百分比
最大值为100,意味着没有发生行过滤(即都符合条件),值从100减少表示过滤量增加。如列rows=3,filtered=66.7%,则最后查询出的列数约为2行,如(user_detail全量数据):
explain结果3 * 66.67约为2,如下图(实际并不一定准确): -
Extra:有关MySQL如何解析查询的额外信息
常见的如Using Where、Using Index、Distinct等