目录
4.2.3 SUBQUERY(在select 或 where中的子查询)
4.10.5 Select tables optimized away
explain 其实就是mysql的一个关键字,使用方法就是放在select 前使用。explain关键字可以模拟MySQL优化器执行SQL语句,可以很好的分析SQL语句活表结构的性能瓶颈。
1、Explain 的用途
- 表的读取顺序如何
- 数据读取操作有哪些操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间是如何引用
- 每张表有多少行被优化器查询
2、explain 用法以及包含的字段
explain SELECT * FROM a_user WHERE id = 1;
执行结果有12个字段,12个字段分别是以下字段和解释:
id:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
select_type:查询类型
table:正在访问哪个表
partitions:匹配的分区
type:访问的类型
possible_keys:显示可能应用在这张表中的索引,一个或多个,但不一定实际使用到
key:实际使用到的索引,如果为NULL,则没有使用索引
key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数,哪些列或常量被用于查找索引列上的值
rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需读取的行数
filtered:查询的表行占表的百分比
Extra:包含不适合在其它列中显示但十分重要的额外信息
3、Explain 一图详解
4、Explain 详解(带案例-案例SQL在最后)
4.1 id 列
4.1.1 id相同
--执行顺序从上至下 explain SELECT * FROM a_user,a_user_role WHERE a_user.id = a_user_role.user_id; -- 读取顺序:a_user > a_user_role
4.1.2 id不相同
-- 如果是子查询,id的序号会递增,id的值越大优先级越高,越先被执行 explain SELECT a_user.* FROM a_user WHERE a_user.id = (SELECT a_user_role.user_id FROM a_user_role WHERE a_user_role.role_id = 3); -- 读取顺序:a_user_role > a_user
4.1.3 id有相同,有不相同的
-- id如果相同,可以认为是一组,在一组中的顺序是从上往下顺序执行
-- 以不同组为单位,组id值越大,优先级越高,越先执行
explain SELECT * FROM a_user,a_user_role WHERE a_user.id = a_user_role.user_id union SELECT * FROM a_user,a_user_role WHERE a_user.id = a_user_role.user_id; -- 读取顺序:2.a_user > 2.user_role > 1.a_user > 1.user_role
4.2 select_type列(查询类型)
4.2.1 SIMPLE (简单查询)
简单查询:不包含子查询或者Union查询
EXPLAIN SELECT * FROM a_user,a_user_role WHERE a_user.id = a_user_role.user_id;
4.2.2 PRIMARY (子查询中最外层的查询)
查询中若包含任何复杂的子部分,最外层查询则被标记为主查询
explain SELECT a_user.* FROM a_user WHERE a_user.id = (SELECT a_user_role.user_id FROM a_user_role WHERE a_user_role.role_id = 3);
4.2.3 SUBQUERY(在select 或 where中的子查询)
在select或where中包含子查询
explain SELECT a_user.* FROM a_user WHERE a_user.id = (SELECT a_user_role.user_id FROM a_user_role WHERE a_user_role.role_id = 3);
4.2.4 derived (衍生表)
在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中
备注:MySQL5.7+ 进行优化了,增加了derived_merge(派生合并),默认开启,可加快查询效率
-- 先关闭优化 set session optimizer_switch = 'derived_merge=off'; explain select (select 1 from a_user where id = 1) from (select * from a_user_role where id = 1) der; -- 执行完之后再将优化开启 set session optimizer_switch = 'derived_merge=on';
4.2.5 union
在union中的第二个和随后的select
explain SELECT * FROM a_user as a_user_1 union SELECT * FROM a_user as a_user_2 union SELECT * FROM a_user as a_user_3;
4.2.6 union result
从 union 临时表检索结果的 select
explain SELECT * FROM a_user as a_user_1 union SELECT * FROM a_user as a_user_2 union SELECT * FROM a_user as a_user_3;
4.3 table 列
数据来自哪张表
当 from 子句中有子查询时,table列是 <derivenN> 格式,表示当前查询依赖 id=N 的查 询,于是先执行 id=N 的查询。
当有 union 时,UNION RESULT 的 table 列的值为<union1,2>,1和2表示参与 union 的 select 行id。
explain SELECT * FROM a_user as a_user_1 union SELECT * FROM a_user as a_user_2 union SELECT * FROM a_user as a_user_3;
4.4 type (访问类型)
这一列表示关联类型或访问类型,即MySQL决定如何查找表中的行,查找数据行记录的大概范围。
依次从最优到最差分别为:
NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL
一般来说,得保证查询达到range级别,最好达到ref
4.4.1 NULL
mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。
explain select min(id) from a_user;
4.4.2 system
表只有一行记录(等于系统表),这是const类型的特列,平时不大会出现,可以忽略
4.4.3 const
表示通过索引一次就找到了,const用于比较primary key或uique索引,因为只匹配一行数据,所以很快,如主键置于where列表中,MySQL就能将该查询转换为一个常量。
explain select * from a_user WHERE id = 1;
4.4.4 eq_ref
primary key(主键索引)或unique key(联合索引)索引的所有部分被连接使用,最多只会返回一条符合条件的记录。这可能是在const之外最好的联接类型了,简单的 select 查询不会出现这种type。
-- 被关联的表使用主键关联 EXPLAIN SELECT a_user_role.* FROM a_user_role left join a_user on a_user.id = a_user_role.user_id;
4.4.5 ref
不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行。
情况1:简单的select查询,username是非唯一索引
-- 创建一个普通的索引 CREATE INDEX inx_username ON a_user (username); explain select * from a_user where username = '王五';
情况2:联合索引
-- 创建一个联合索引 CREATE INDEX idx_user_role_id ON a_user_role (user_id,role_id); -- 这两个sql都是ref级别,只不过使用索引的长度不同(后面详解),但是要遵守最左原则,就是 user_id一定要有 EXPLAIN select * from a_user_role WHERE user_id = 2; EXPLAIN select * from a_user_role WHERE user_id = 2 and role_id = 2;
4.4.6 ref_or_null
类似ref,不仅想找到某个索引的某个值,还想把该列的null值也找出来;
SQL后面跟着 OR 列 is null
-- username 已有索引 -- CREATE INDEX inx_username ON a_user (username); EXPLAIN select * from a_user where username = '王五' or username is null;
4.4.7 index_merge(索引合并)
使用了组合索引(也就是多个索引的结果集合并)
-- 下面这个sql 会被优化成走两个索引 -- select * from a_user where id = 1 和 select * from a_user where username = '王五' EXPLAIN select * from a_user where id = 1 or username = '王五';
4.4.8 range(范围索引)
范围扫描通常出现在 in(), between ,> ,= 等操作中。使用一个索引来检索给定范围的行。
EXPLAIN select * from a_user where id > 1;
4.4.9 index
扫描全表索引(某个索引树),这通常比ALL快一些
EXPLAIN select id from a_user;
4.4.10 all
即全表扫描,意味着mysql需要从头到尾去查找所需要的行。通常大部分情况下这需要增加索引来进行优化了。
EXPLAIN select * from a_user;
4.5 possible_keys 列
这一列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有列,而 key 显示 NULL 的情况,这种情况是因为表中数据不多,mysql认为索引对此查询帮助不大,选择了全表查询。
如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能,然后用 explain 查看效果
4.6 key 列
这一列显示mysql实际采用哪个索引来优化对该表的访问。
如果没有使用索引,则该列是 NULL。
4.7 key_len 列
这一列显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。多在复合索引中使用的比较多。
key_len计算规则如下:
- 字符串
- char(n):n字节长度
- varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n+2
- 数值类型
- tinyint:1字节
- smallint:2字节
- int:4字节
- bigint:8字节
- 时间类型
- date:3字节
- timestamp:4字节
- datetime:8字节
- 如果字段允许为 NULL,需要1字节记录是否为NULL。索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引
举例说明:
a_user_role 的联合索引idx_user_role_id 由 user_id 和 role_id两个bigint组成的,一个bigint占8个字节。
EXPLAIN select * from a_user_role WHERE user_id = 2; EXPLAIN select * from a_user_role WHERE user_id = 2 and role_id = 2;
4.8 ref列
这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名(例:id)
4.9 rows 列
这一列是mysql估计要读取并检测的行数,注意这个不是结果集里的行数。
4.10 extra 列
展示额外信息,但是十分重要
4.10.1 Using index
使用覆盖索引,查询结果就是索引所在列,避免访问了表的数据行。
EXPLAIN select id from a_user WHERE id = 2;
4.10.2 Using where
使用where语句来处理结果,本人理解为,查询条件没有走索引或者是没有索引。
DROP INDEX inx_username ON a_user; EXPLAIN select * from a_user WHERE username = '123'; CREATE INDEX inx_username ON a_user (username);
4.10.3 Using temporary
mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的,首先是想到用索引来优化。
-- 先删除表中的索引 -- DROP INDEX <索引名> ON <表名> DROP INDEX inx_username ON a_user; explain SELECT distinct username from a_user;
-- 再将索引添加回来 CREATE INDEX inx_username ON a_user (username); explain SELECT distinct username from a_user;
4.10.4 Using filesort
将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
a_user.username未创建索引,会浏览a_user整个表,保存排序关键字username和对应的id,然后排序username并检索行记录
-- 删除索引 DROP INDEX inx_username ON a_user; -- 根据 username排序 explain SELECT id,username from a_user order by username; -- 将索引添加回来 CREATE INDEX inx_username ON a_user (username);
4.10.5 Select tables optimized away
使用某些聚合函数(比如 max、min)来访问存在索引的某个字段
explain select min(id) from a_user;
4.10.6 impossible where
where子句的值总是false,不能用来获取任何数据。
explain select * from a_user WHERE username = '张三' and username = '李四';
5、 演示数据SQL
CREATE TABLE `a_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`username` varchar(50) DEFAULT NULL COMMENT '用户名',
`password` varchar(100) DEFAULT NULL COMMENT '密码',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户表';
CREATE TABLE `a_role` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(20) NOT NULL COMMENT '角色名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='角色';
CREATE TABLE `a_user_role` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`user_id` bigint(20) NOT NULL COMMENT '用户id',
`role_id` bigint(20) NOT NULL COMMENT '角色id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户权限表';
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (1, '张三', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (2, '李四', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (3, '王五', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (4, '王五', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (5, '15010480559', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (6, 'liulongying', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (7, 'dutianyu', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (8, 'zhuyanlin', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (9, NULL, '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (10, 'admin', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (11, 'guodianwei', '$2a$10$VwWK1zPdf6asro8rhEGKK.JCHYixjNi500sXQJQb15eRGF9xilxmW');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (12, 'zhaozhengxing1111', '$2a$10$EZCI.Ysb7hWhUQ.aYZqpK.Mg5VWg/99eU1ldheTR/seGRolXoqudO');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (13, 'jianghongyu', '$2a$10$XGfOgLOUN9urcbVmK4XuXuv.hipxfmaEhEGibZkZXhmbh4r/eFAdO');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (14, 'cuiqingqing', '$2a$10$DEZPRouyJqZAVG2qHnPMMeH6ztfPAF7nhIQTaTg63hZ5.5//F/cXO');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (15, 'hepengcong', '$2a$10$a/DWtXdHFoMdSm1V2LCPvuTH3qBe0tfTOdFa26EVl1whghnCpgzFy');
INSERT INTO `a_user`(`id`, `username`, `password`) VALUES (16, 'jianghongyu1', '$2a$10$XG');
INSERT INTO `a_role`(`id`, `name`) VALUES (1, '管理员');
INSERT INTO `a_role`(`id`, `name`) VALUES (2, '子管理员');
INSERT INTO `a_role`(`id`, `name`) VALUES (3, '部长');
INSERT INTO `a_role`(`id`, `name`) VALUES (4, '组长');
INSERT INTO `a_user_role`(`id`, `user_id`, `role_id`) VALUES (1, 1, 1);
INSERT INTO `a_user_role`(`id`, `user_id`, `role_id`) VALUES (2, 1, 2);
INSERT INTO `a_user_role`(`id`, `user_id`, `role_id`) VALUES (3, 2, 3);
INSERT INTO `a_user_role`(`id`, `user_id`, `role_id`) VALUES (5, 2, 4);
INSERT INTO `a_user_role`(`id`, `user_id`, `role_id`) VALUES (4, 3, 4);