-- 查看慢查询的信息
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<200;
-- 至少要在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 <'a' AND key1 LIKE '%a';
-- 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<500;
-- filtered,索引后的满足其他搜索条件的比率，易混淆，可以看其他列来优化
EXPLAIN SELECT * FROM s1 WHERE key2<500 AND common_field = 'a';
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
