mysql优化–explain–结果参数–02
1、数据准备
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键id,自增涨',
`name` varchar(20) DEFAULT NULL COMMENT '课程名称',
`teacher_id` int(0) NULL DEFAULT NULL COMMENT '教师ID',
PRIMARY KEY (`id`) USING BTREE,
INDEX `index_teacher_id`(`teacher_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 COMMENT = '课程表';
INSERT INTO `course` VALUES (1, '语文', 1);
INSERT INTO `course` VALUES (2, '数学', 2);
INSERT INTO `course` VALUES (3, '英语', 3);
INSERT INTO `course` VALUES (4, '历史', 4);
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键id,自增涨',
`student_id` int(0) NULL DEFAULT NULL COMMENT '学生ID',
`course_id` int(0) NULL DEFAULT NULL COMMENT '课程ID',
`score` int(0) NULL DEFAULT NULL COMMENT '分数',
PRIMARY KEY (`id`) USING BTREE,
INDEX `index_student_id`(`student_id`) USING BTREE,
INDEX `index_course_id`(`course_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 COMMENT = '分数表';
INSERT INTO `score` VALUES (1, 1, 1, 90);
INSERT INTO `score` VALUES (2, 1, 2, 60);
INSERT INTO `score` VALUES (3, 1, 3, 80);
INSERT INTO `score` VALUES (4, 1, 4, 100);
INSERT INTO `score` VALUES (5, 2, 4, 60);
INSERT INTO `score` VALUES (6, 2, 3, 50);
INSERT INTO `score` VALUES (7, 2, 2, 80);
INSERT INTO `score` VALUES (8, 2, 1, 90);
INSERT INTO `score` VALUES (9, 3, 1, 90);
INSERT INTO `score` VALUES (10, 3, 4, 100);
INSERT INTO `score` VALUES (11, 4, 1, 40);
INSERT INTO `score` VALUES (12, 4, 2, 80);
INSERT INTO `score` VALUES (13, 4, 3, 80);
INSERT INTO `score` VALUES (14, 4, 5, 100);
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键id,自增涨',
`name` varchar(20) DEFAULT NULL COMMENT '名称',
`student_no` varchar(20) DEFAULT NULL COMMENT '学生编号',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `index_student_no`(`student_no`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 COMMENT = '学生表';
INSERT INTO `student` VALUES (1, '小明', '20200001');
INSERT INTO `student` VALUES (2, '小红', '20200002');
INSERT INTO `student` VALUES (3, '小张', '20200003');
INSERT INTO `student` VALUES (4, '小李', '20200004');
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键id,自增涨',
`name` varchar(20) DEFAULT NULL COMMENT '教师名称',
`teacher_no` varchar(20) DEFAULT NULL COMMENT '教师编号',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `index_teacher_no`(`teacher_no`) USING BTREE,
INDEX `index_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 COMMENT = '教师表';
INSERT INTO `teacher` VALUES (1, '王老师', 'T2010001');
INSERT INTO `teacher` VALUES (2, '张老师', 'T2010002');
INSERT INTO `teacher` VALUES (3, '全老师', 'T2010003');
INSERT INTO `teacher` VALUES (4, '赵老师', 'T2010004');
2、id
- 代表执行select子句或操作表的顺序
- id值越大,优先级越高,越先被执行,如果是子查询,id的序号会递增
- id相同,执行顺序由上至下
案例
explain
select c1.* from course c1 left join teacher t1 on c1.teacher_id = t1.id
UNION
select c2.* from course c2 right join teacher t2 on c2.teacher_id = t2.id;
![在这里插入图片描述](https://img-blog.csdnimg.cn/2021053020394393.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
3、select_type
- 表示查询中每个select子句的类型
- 主要用于区别普通查询,联合查询,子查询等复杂查询
3.1、simple
- 简单的select查询
- 查询中不包含子查询或union查询
案例
EXPLAIN
SELECT c1.*
FROM
course c1,
score s1,
teacher t1
WHERE
c1.id = s1.course_id
AND c1.teacher_id = t1.id;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530203951853.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
3.2、primary
- 最外层的SELECT
- 查询中若包含任何复杂的子部分,最外层查询则被标记为primary
案例
EXPLAIN
SELECT s1.*
FROM
score s1
WHERE
course_id = (
SELECT c1.id
FROM course c1
WHERE teacher_id =(
SELECT id
FROM teacher t
WHERE id = 2 ));
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204000160.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
3.3、subquery
- 子查询中的第一个SELECT,结果不依赖于外部查询
案例
EXPLAIN
SELECT s1.*
FROM score s1
WHERE
s1.course_id = (
SELECT c1.id
FROM course c1
WHERE
c1.teacher_id = (
SELECT t1.id
FROM teacher t1
WHERE t1.id = 2 ));
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204007864.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
3.4、dependent subquery
子查询中的第一个SELECT,结果依赖于外部查询
案例
EXPLAIN
SELECT s1.*
FROM score s1
WHERE
s1.course_id = (
SELECT c1.id
FROM course c1
WHERE
c1.teacher_id = 2
and s1.course_id>c1.id
)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204014899.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
3.5、derived
- 在FROM列表中包含的子查询被标记为DERIVED(衍生)
- mysql会递归这些子查询,把结果放在临时表中
3.6、union
- union的第二个或后面的SELECT语句
- 若union包含在from子句的子查询中,外层select将被标记为derived
案例
EXPLAIN
SELECT id FROM teacher
union
select id FROM course
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204021174.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
3.7、dependent union
- union的第二个或后面的SELECT语句,取决于外面的查询
案例
EXPLAIN
SELECT c1.*
FROM course c1
WHERE c1.id IN (
SELECT c2.id FROM course c2 WHERE c2.id = 1
UNION
SELECT c3.id FROM course c3 WHERE c3.id = 2
)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204030486.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
3.8、union result
union的结果
![在这里插入图片描述](https://img-blog.csdnimg.cn/2021053020403883.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
4、table
- 显示一行的数据时关于哪张表的
5、type(连接类型)
- 描述了找到所需数据使用的扫描方式。
- 性能从好到坏,依次如下(NULL>system>const>eq_ref>ref>range>index>All)
- 一般情况下,得至少保证达到range级别,最好能达到ref
- 单独查询—性能从好到坏
5.1、NULL
- MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,举例如下
- 从一个索引列里选取最小值可以通过单独索引查找完成。
查看执行计划
explain select min(id) from course;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204045548.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
5.2、system
- 表只有一行记录
- 这是const类型的特例,非常难验证
5.3、const
const扫描的条件为:
- 命中主键(primary key)或者唯一(unique)索引
- 被连接的部分是一个常量(const)值
- 表示通过索引一次就找到了目标
- 因为只匹配一行数据,所以效率很快
- 如将主键置于where条件中,mysql就能将该查询转换为一个常量
查看执行计划
explain select * from teacher where teacher_no = 'T2010001';
teacher_no是唯一索引,连接部分是常量1
这类扫描效率极高,返回数据量少,速度非常快。
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204051975.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
5.4、eq_ref
- 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,
- 可以这样理解:对于前表的每一行(row),后表只有一行被扫描。
- 常见于主键或唯一索引扫描
- 简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
- 发生的环境
- join查询
- 等值连接
- 命中主键(primary key)或者非空唯一(unique not null)索引
查看执行计划
EXPLAIN
SELECT c1.*
FROM course c1
LEFT JOIN teacher t1 ON c1.teacher_id = t1.id;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204057843.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
5.5、ref
- 非唯一性索引扫描
- 简单来说:
- 表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
- 对于前表的每一行(row),后表可能有多于一行的数据被扫描。
- 可能出现在join里,也可能出现在单表普通索引里,每一次匹配可能有多行数据返回,虽然它比eq_ref要慢,但它仍然是一个很快的join类型。
查看执行计划
EXPLAIN
SELECT c1.*
FROM
course c1,
score s1
WHERE
c1.id = s1.course_id
course_id为普通非唯一(non unique)索引。
对于前表的每一行(row),后表可能有多于一行的数据被扫描。
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204103776.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
5.6、fulltext
5.7、ref_or_null
如同ref,但可以搜索值是NULL的行。
查看执行计划
EXPLAIN
SELECT *
FROM teacher
WHERE
NAME = 'xxx'
OR NAME IS NULL;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204109696.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
5.8、index_merge
用了索引合并的优化方法
查看执行计划
EXPLAIN
SELECT *
FROM teacher
WHERE
id = 1
OR teacher_no = 'T2010001'
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204116166.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
5.9、unique_subquery
-
该类型替换了下面形式的IN子查询的ref:
- value IN (SELECT primary_key FROM single_table WHERE some_expr)
-
unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
5.10、index_subquery
- 该联接类型类似于unique_subquery。
- 可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:
- value IN (SELECT key_column FROM single_table WHERE some_expr)
5.11、range
- 只检索给定范围的行
- 如where语句中出现了between,<,>,in等查询
- 这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。
查看执行计划
explain select * from course where id between 1 and 3;
explain select * from course where id in(1,2,3);
explain select * from course where id>3;
像上面中的between,in,>都是典型的范围(range)查询。
注意:必须是索引,否则不能批量"跳过"。
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204125189.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204132319.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204139663.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
5.12、index
- 全索引扫描
- index类型只遍历索引树,这通常比All快,因为索引文件通常比数据文件小
- index是从索引中读取,all从硬盘中读取
查看执行计划
explain select id from course;
![在这里插入图片描述](https://img-blog.csdnimg.cn/2021053020414629.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
5.13、all
- 全表扫描
- 最差的一种查询类型
初始化数据
create table t8 (id int ,name varchar(20))engine=innodb;
insert into t8 values(1,'hwb');
insert into t8 values(2,'zhangsan');
insert into t8 values(3,'xiaoming');
create table t9 (id int,age int)engine=innodb;
insert into t9 values(1,18);
insert into t9 values(2,20);
insert into t9 values(3,30);
insert into t9 values(4,40);
insert into t9 values(5,50);
查看执行计划
explain select * from t8,t9 where t8.id=t9.id;
如果id上不建索引,对于前表的每一行(row),后表都要被全表扫描。
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204157510.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
6、possible_keys
- 指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
- 如果该列是NULL,则没有相关的索引,那应该怎么优化了?
- 可以用有索引的列来替换 where 的条件列。
- where 的条件列 创造一个适当的索引
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204208608.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
7、key
- 实际使用的索引,必然包含在possible_keys中
- 如果为NULL,则没有使用索引。(可能原因包括没有建立索引或索引失效)
- 很少的情况下,MYSQL会选择优化不足的索引。这种情况下,要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204208608.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
7.1、覆盖索引
- 覆盖索引是select的数据列只用从索引中就能够取得,不必读取数据行
- 换句话说:
- select 要查询的字段刚好 在 创建的索引字段的范围内。
- 比如:select a1,a2字段,索引的字段[a1,a2,…]
7.2、查询中若使用了覆盖索引,则该索引仅出现在key列表中,possible_keys不会出现
![在这里插入图片描述](https://img-blog.csdnimg.cn/2021053020424120.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
![在这里插入图片描述](https://img-blog.csdnimg.cn/202105302042419.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
8、key_len
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204312232.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
- 表示索引字段的最大可能长度,并非实际使用长度
- key_len是根据表定义计算而得,不是通过表内检索出的。
- 可通过该列计算查询中使用的索引的长度
- 在不损失精确性的情况下,长度越短越好。
- 如果键是key 是 NULL,则长度为NULL。
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204328531.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204334653.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204340862.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
9、ref
- 显示索引的哪一列被使用了
- 简单理解:就是表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
- 如果可能的话,最好是一个常数。
- 显示使用哪个列或常数与key一起从表中选择行。
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204354644.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
10、rows
- 预估出找到目标所需要读取的行数,也就是说,用的越少越好.
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204403323.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
11、Extra
查看执行计划
explain select * from course order by name;
额外信息
11.1、Using filesort(九死一生)
- mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。
- MySQL中无法利用索引完成的排序操作称为 “Using filesort”
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204412772.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
11.2、 Using temporary(十死无生)
- 使用了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。
- 常见于排序order by和分组查询group by
查看执行计划
EXPLAIN
SELECT course.*
FROM course LEFT JOIN teacher ON course.teacher_id = teacher.id
UNION
SELECT course.*
FROM course RIGHT JOIN teacher ON course.teacher_id = teacher.id;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204431630.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
11.3、 Using index(发财了)
- 表示相应的select操作中使用了覆盖索引
- 仅仅使用了索引中的信息,而没有读取表的数据行,效率不错。
- 如果同时出现using where,表明索引被用来执行索引键值的查找;
- 如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
查看执行计划
EXPLAIN
SELECT c1.*
FROM
course c1,
score s1,
teacher t1
WHERE
c1.id = s1.course_id
AND c1.teacher_id = t1.id;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204440161.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
11.4、 Using where
- mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据,再按 where 条件进行检查,符合就留下,不符合就丢弃。
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204440161.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
11.5、 Using join buffer
- 强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。
- 如果出现了这个值,那应该注意,需要添加索引来改进能。
- 如果多表join的次数非常多,那么将配置文件中的缓冲区的join buffer调大一些。
查看执行计划
EXPLAIN
SELECT
s1.*,
t1.*
FROM
student s1,
teacher t1
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204459805.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
11.6、 impossible where
where子句的值总是false,不能用来获取任何元组
查看执行计划
EXPLAIN
SELECT *
FROM teacher
WHERE id = 1 and id = 2
![在这里插入图片描述](https://img-blog.csdnimg.cn/2021053020450812.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
11.7、 select tables optimized away
在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
11.8、 distinct
一旦MYSQL找到了与行相联合匹配的行,就不再搜索了
查看执行计划
EXPLAIN
SELECT
DISTINCT t1.name
FROM teacher t1
LEFT JOIN course c1 ON t1.id = c1.teacher_id;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204516100.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
11.9、 not exists
MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了
查看执行计划
EXPLAIN
SELECT count( 1 )
FROM course c1
LEFT JOIN course c2 ON c1.id = c2.id
WHERE
c2.id IS NULL;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204523723.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
解释
- course表中的id是主键,不可能为NULL,当mysql在用c1表的id扫描course表,并查找c2表的行时,如果在c2表发现一个匹配的行就不再继续扫描c2了,因为c2表中的id字段不可能为NULL。
这样避免了对c2表的多次扫描。
11.10、 range checked for each Record
- MySQL没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。
- 这是使用索引的最慢的连接之一,但是速度比没有索引要快得多
查看执行计划
EXPLAIN
SELECT c1.*
FROM course c1, course c2
WHERE
c1.id >= c2.id
AND c1.id <= c2.id
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204531152.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
11.11、 Using sort_union(…), Using union(…), Using intersect(…)
- 这些函数说明如何为index_merge联接类型合并索引扫描。
- 简单来说:
- 对多个索引分别进行条件扫描,然后将各自的结果进行合并(intersect/union)
查看执行计划1
下面的例子中teacher_id是一个检索范围,此时mysql会使用sort_union函数来进行索引的合并。
EXPLAIN
SELECT c1.*
FROM course c1
WHERE
c1.id = 1
OR c1.teacher_id > 2;
![在这里插入图片描述](https://img-blog.csdnimg.cn/2021053020453840.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
查看执行计划2
下面的例子中teacher_id是固定值,此时mysql会使用union函数来进行索引的合并。
EXPLAIN
SELECT c1.*
FROM course c1
WHERE
c1.id = 1
OR c1.teacher_id = 2;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204545211.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
查看执行计划3
下面的例子中teacher_id是固定值,此时mysql会使用union函数来进行索引的合并。
EXPLAIN
SELECT c1.*
FROM course c1
WHERE
c1.name = '语文'
AND c1.teacher_id = 1;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204600440.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204607146.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
11.12、 Using index for group-by
- 表明可以在索引中找到分组所需的所有数据,不需要查询实际的表。
11.13、 Select tables optimized away
- SELECT操作已经优化到不能再优化了
- MySQL根本没有遍历表或索引就返回数据了
查看执行计划
explain select min(id) from course;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204617911.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
11.14、 No tables used
Query语句中使用from dual 或不含任何from子句
查看执行计划
explain select now() from dual;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204625526.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)
12、filtered
- 通过条件过滤出的行数/查找范围的总行数*100
- 单位是百分比
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210530204635262.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3U5MjA3ODYzMTI=,size_16,color_FFFFFF,t_70#pic_center)