MySQL调优之EXPLAIN分析详解_mysql调优explain-CSDN博客

本文链接：https://blog.csdn.net/weixin_41290976/article/details/105180450

EXPLAIN分析详解

建表语句用实际SQL把所有类型显示出来 SQL需要从上往下执行有些依赖于上面的SQL
DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher (
id bigint NOT NULL AUTO_INCREMENT,
Tid varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
Tname varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

BEGIN;
INSERT INTO teacher VALUES (1, ‘01’, ‘张三’);
INSERT INTO teacher VALUES (2, ‘02’, ‘李四’);
INSERT INTO teacher VALUES (3, ‘03’, ‘王五’);
COMMIT;

DROP TABLE IF EXISTS course;
CREATE TABLE course (
id bigint NOT NULL AUTO_INCREMENT,
Cid varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
Cname varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
Tid varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

BEGIN;
INSERT INTO course VALUES (1, ‘01’, ‘语文’, ‘02’);
INSERT INTO course VALUES (2, ‘02’, ‘数学’, ‘01’);
INSERT INTO course VALUES (3, ‘03’, ‘英语’, ‘03’);
COMMIT;

DROP TABLE IF EXISTS sc;
CREATE TABLE sc (
id bigint NOT NULL AUTO_INCREMENT,
Sid varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
Cid varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
score decimal(18,1) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

BEGIN;
INSERT INTO sc VALUES (1, ‘01’, ‘01’, 80.0);
INSERT INTO sc VALUES (2, ‘01’, ‘02’, 90.0);
INSERT INTO sc VALUES (3, ‘01’, ‘03’, 99.0);
INSERT INTO sc VALUES (4, ‘02’, ‘01’, 70.0);
INSERT INTO sc VALUES (5, ‘02’, ‘02’, 60.0);
INSERT INTO sc VALUES (6, ‘02’, ‘03’, 80.0);
INSERT INTO sc VALUES (7, ‘03’, ‘01’, 80.0);
INSERT INTO sc VALUES (8, ‘03’, ‘02’, 80.0);
INSERT INTO sc VALUES (9, ‘03’, ‘03’, 80.0);
INSERT INTO sc VALUES (10, ‘04’, ‘01’, 50.0);
INSERT INTO sc VALUES (11, ‘04’, ‘02’, 30.0);
INSERT INTO sc VALUES (12, ‘04’, ‘03’, 20.0);
INSERT INTO sc VALUES (13, ‘05’, ‘01’, 76.0);
INSERT INTO sc VALUES (14, ‘05’, ‘02’, 87.0);
INSERT INTO sc VALUES (15, ‘06’, ‘01’, 31.0);
INSERT INTO sc VALUES (16, ‘06’, ‘03’, 34.0);
INSERT INTO sc VALUES (17, ‘07’, ‘02’, 89.0);
INSERT INTO sc VALUES (18, ‘07’, ‘03’, 98.0);
COMMIT;

DROP TABLE IF EXISTS student;
CREATE TABLE student (
id bigint NOT NULL AUTO_INCREMENT,
Sid varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
Sname varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
Sage datetime DEFAULT NULL,
Ssex varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

BEGIN;
INSERT INTO student VALUES (1, ‘01’, ‘赵雷’, ‘1990-01-01 00:00:00’, ‘男’);
INSERT INTO student VALUES (2, ‘02’, ‘钱电’, ‘1990-12-21 00:00:00’, ‘男’);
INSERT INTO student VALUES (3, ‘02’, ‘钱电’, ‘1990-12-21 00:00:00’, ‘男’);
INSERT INTO student VALUES (4, ‘03’, ‘孙风’, ‘1990-05-20 00:00:00’, ‘男’);
INSERT INTO student VALUES (5, ‘04’, ‘李云’, ‘1990-08-06 00:00:00’, ‘男’);
INSERT INTO student VALUES (6, ‘05’, ‘周梅’, ‘1991-12-01 00:00:00’, ‘女’);
INSERT INTO student VALUES (7, ‘06’, ‘吴兰’, ‘1992-03-01 00:00:00’, ‘女’);
INSERT INTO student VALUES (8, ‘07’, ‘郑竹’, ‘1989-07-01 00:00:00’, ‘女’);
INSERT INTO student VALUES (9, ‘08’, ‘王菊’, ‘1990-01-20 00:00:00’, ‘女’);
COMMIT;

1. id 列（SQL 执行顺序，id相同从上到下执行，id不同id大的先执行)

2. select_type 列（查询顺序，区别于普通查询，联合查询，子查询等的复杂查询）

a. SIMPLE: 简单的 SELECT (没有使用UNION 或者子查询（PS：单表查询）)

EXPLAIN SELECT * from student；

b. PRIMARY: 最外层的Select 作为primary 查询。(PS:含有子查询的情况,但是并不复杂)

//获取学生id值最大的学生
EXPLAIN SELECT * from student a where Sid = (select MAX(Sid) from student b);

c. UNION: 从第二个或者在union 之后的select 作为 union 查询

EXPLAIN SELECT * from student WHERE id > 8
UNION
SELECT * from student WHERE id < 2;

d. DEPENDENT UNION: 从第二个或者在union 之后的select 作为 union 查询，依赖于外部查询（内层的SELECT语句与外层的SELECT语句有依赖关系）

EXPLAIN
SELECT * from sc WHERE Sid in(
SELECT a.Sid from student a WHERE id > 8
UNION
SELECT b.Sid from student b WHERE id < 2);

e. UNION RESULT: 结果集是通过union 而来的

EXPLAIN SELECT * from student WHERE id > 8
UNION
SELECT * from student WHERE id < 2;

f. SUBQUERY: 第一个查询是子查询

EXPLAIN SELECT * from student a where Sid = (select MAX(Sid) from student b);

g. DEPENDENT SUBQUERY: 第一个查询是子查询，依赖于外部查询

特别关注 DEPENDENT SUBQUERY
1 会严重消耗性能
2 不会进行子查询，会先进行外部查询,生成结果集,再在内部进行关联查询
3 子查询的执行效率受制于外层查询的记录数
4 可以尝试改成join查询

EXPLAIN
SELECT * from sc WHERE Sid in(
SELECT a.Sid from student a WHERE id > 8
UNION
SELECT b.Sid from student b WHERE id < 2);

h. DERIVED: 在from 查询语句中的（派生，嵌套很多）子查询.(PS:递归操作这些子查询)

EXPLAIN
SELECT * from student s JOIN (SELECT Sid, max(score) from sc GROUP BY Sid) b ON s.Sid = b.Sid;

i. MATERIALIZED: （雾化）子查询（PS：子查询是个视图？）

EXPLAIN SELECT * from student a where Sid in (select Sid from sc b where score > 80);

j. UNCACHEABLE SUBQUERY: 子查询结果不能被缓存，而且必须重写（分析）外部查询的每一行

k. UNCACHEABLE UNION: 第二个或者在UNION 查询之后的select ,属于不可缓存的查询

3. table 列（访问的是哪个表）

4. type 列（MySQL以哪种方式查找数据）

效率从低到高排列 最低查询要保证在range级别之上

a. ALL(全表扫描)

效率最低无使用到任何索引，必须要优化

b. index(全索引树扫描)

create index Sid on student(Sid);
explain
SELECT * from student force index(Sid) ORDER BY Sid;

c. range(索引下的范围查询)

explain
SELECT * from student WHERE id > 4 and id < 8;

d. ref(索引查找或索引访问)

只发生在非唯一索引或者是唯一索引的非唯一查找
1.create index Sname on student(Sname);
explain
SELECT * from student where Sname = ‘赵雷’;
2.create unique index Tid on teacher(Tid);
#这里不建议在索引列使用null 只为测试用
insert into teacher(Tid,Tname) VALUES(null,‘老徐’);
insert into teacher(Tid,Tname) VALUES(null,‘栩栩’);
explain
SELECT * from teacher where Tid is null;