mysql explain语句_mysql explain语句实践解析

— 查看慢查询的信息

show variables like ‘slow_query%’;

show variables like ‘long_query_time%’;

— 查询有截至目前已经产生了多少条慢查询记录。

show global status like ‘%Slow_queries%’;

数据准备

CREATE TABLE `s1` (

`id` int NOT NULL AUTO_INCREMENT,

`key1` varchar(100) DEFAULT NULL,

`key2` int DEFAULT NULL,

`key3` varchar(100) DEFAULT NULL,

`key_part1` varchar(100) DEFAULT NULL,

`key_part2` varchar(100) DEFAULT NULL,

`key_part3` varchar(100) DEFAULT NULL,

`common_field` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `idx_key2` (`key2`),

KEY `idx_key1` (`key1`),

KEY `idx_key3` (`key3`),

KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)

) ENGINE=InnoDB AUTO_INCREMENT=120001 DEFAULT CHARSET=utf8;

CREATE TABLE `s2` (

`id` int NOT NULL AUTO_INCREMENT,

`key1` varchar(100) DEFAULT NULL,

`key2` int DEFAULT NULL,

`key3` varchar(100) DEFAULT NULL,

`key_part1` varchar(100) DEFAULT NULL,

`key_part2` varchar(100) DEFAULT NULL,

`key_part3` varchar(100) DEFAULT NULL,

`common_field` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `idx_key2` (`key2`),

KEY `idx_key1` (`key1`),

KEY `idx_key3` (`key3`),

KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)

) ENGINE=InnoDB AUTO_INCREMENT=110001 DEFAULT CHARSET=utf8;

各插入10万数据，以后提供批量插入脚本

索引相关

show processlist;

— 隐式类型转换

— 索引列运算导致索引失效

EXPLAIN SELECT * from s1 WHERE id=10

EXPLAIN SELECT * from s1 WHERE id-1=9

— 函数导致索引失效

EXPLAIN SELECT * from s1 WHERE ROUND(id)=10

— 用到or,导致索引失效

EXPLAIN SELECT * from s1 WHERE id=10 or common_field=’a’

— 多列索引需要用到索引第一部分

— 未用到

EXPLAIN SELECT * from s1 WHERE key_part3=’a’

— 可以用到

EXPLAIN SELECT * from s1 WHERE key_part3=’a’ AND key_part1=’a’

select type

— select type simple

explain SELECT * FROM s1 WHERE key1 = ‘key1-0’;

— select type–primary,union,union result(union联表,去重中间表)

EXPLAIN SELECT * FROM s1  UNION SELECT * FROM s2;

— select type –PRIMARY,simple,子查询可以转为连接的形式

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1=’key1-0′);

— select type –PRIMARY,subquery 子查询只执行一次,子查询无法转为连接的形式

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1=’key1-0′) OR key3 = ‘a’;

— select type PRIMARY DEPENDENT SUBQUERY 子查询执行多次

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = ‘a’;

— select type– primary,DEPENDENT SUBQUERY,DEPENDENT UNION,UNION RESULT(子查询包括union)

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = ‘a’ UNION SELECT key1 FROM s1 WHERE key1 = ‘b’);

— select type–primary,DERIVED(from是子查询生成的表)

EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;

type

— type-system

— 表中只有一条记录，且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，则查询是system

DROP TABLE t

CREATE TABLE t(i int) Engine=MyISAM;

INSERT INTO t VALUES(1);

EXPLAIN SELECT * FROM t;

— type-const

— 根据主键或者唯一二级索引与常数进行等值匹配

EXPLAIN SELECT * FROM s1 WHERE id = 5;

— type-eq_ref

— 连接查询,被驱动表通过主键或者唯一二级索引列等值匹配的方式进行访问的,则被驱动表的type是eq_ref

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

— type-ref

— 通过普通的二级索引列与常量进行等值匹配时来查询表，那么对该表的访问方法就可能是ref

EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’;

— type-ref_or_null

— 通过普通的二级索引列进行等值匹配时来查询表，该索引列可以是null时，那么对该表的访问方法就可能是ref_or_null

EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ OR key1 IS NULL;

— type-index_merge

— 索引合并

EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ OR key3 = ‘a’;

— type-unique_subquery

— 类似于eq_ref,在in子查询中，子查询使用到主键进行等值匹配，子查询的type就是unique_subquery

EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM s2 where s2.key1 = s1.key1) OR key3= ‘a’;

— tyep-index_subquery

— 类似于eq_ref,在in子查询中，子查询使用到普通索引进行等值匹配，子查询的type就是index_subquery

EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3= ‘a’;

— type-range

— 使用索引获取某些范围记录

EXPLAIN SELECT * FROM s1 WHERE key1 IN (‘a’, ‘b’, ‘c’);

EXPLAIN SELECT * FROM s1 WHERE id IN (1,2,3);

EXPLAIN SELECT * FROM s1 WHERE id > 100 AND id

— 至少要在range以上

— type-INDEX

— 当使用了索引覆盖，但是需要扫描全部索引记录时，会出现index,存在  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)索引

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = ‘a’;

— key_part1是ref

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part1 = ‘a’;

— type-ALL

— 全表扫描

EXPLAIN SELECT * FROM s1;

Extra

— extra -No tables used

EXPLAIN SELECT 1;

— extra-Impossible WHERE条件不成立

EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

— extra-No matching min/max row，where条件不存在记录

EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = ‘notexist’;

— extra- Using index,用到索引

EXPLAIN SELECT key1 FROM s1 WHERE key1 = ‘a’;

— extra- Using index condition,

explain SELECT * FROM s1 WHERE key1

— extra Using where 全表扫描，用到where索引

EXPLAIN SELECT * FROM s1 WHERE common_field = ‘a’;

— extra Using where,

EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ AND common_field = ‘a’;

— extra Using join buffer ，用到中间表

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

— extra Not exists

EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key2 = s2.key2 WHERE s2.id IS NULL;

— extra Using intersect(…)、Using union(…)和Using sort_union(…)，索引合并

EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ or key3 = ‘a’;

— extra limit 参数为0

EXPLAIN SELECT * FROM s1 LIMIT 0;

— extra Using filesort 排序出现文件排序，排序无法用到索引，性能差

EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

— 排序正常用到索引

EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

— extra Using temporary

—  查询过程中，mysql借助临时表来完成一些功能，会损失性能，比如去重，排序，DISTINCT，GROUP BY，UNION

EXPLAIN SELECT DISTINCT common_field FROM s1;

EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;

其他列

— ref-const

— 等值匹配的对象是常数

EXPLAIN SELECT * from s1 WHERE id=189;

— ref

— 等值匹配的对象是具体的列

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

— ref func

— 等值匹配的对象是一个函数

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

— rows 预计扫描行数

EXPLAIN SELECT * FROM s1 WHERE key2

— filtered,索引后的满足其他搜索条件的比率，易混淆，可以看其他列来优化

EXPLAIN SELECT * FROM s1 WHERE key2

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = ‘a’;

EXPLAIN SELECT * from s1 ORDER BY id LIMIT 20