mysql优化--explain

最新推荐文章于 2024-06-18 23:03:55 发布

ylx814056815

最新推荐文章于 2024-06-18 23:03:55 发布

阅读量219

点赞数

分类专栏： mysql 文章标签： mysql

原文链接：https://blog.csdn.net/zhou920786312/article/details/117405281

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

mysql优化–explain

一、介绍

查看SQL语句执行顺序以及每一步都做了什么

二、作用

分析SQL执行语句的时候有没有完全利用好索引

三、怎么使用

explain  sql

四、数据准备

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');

4.1、ID

代表执行select子句或操作表的顺序
1.id值越大,优先级越高,越先被执行，如果是子查询,id的序号会递增
2.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;

在这里插入图片描述

4.2、select_type

1. 表示查询中每个select子句的类型,SQL语句查询的位置

2. 主要用于区别普通查询,联合查询,子查询等复杂查询

4.2.1、simple

1. 简单的select查询，不包含子查询或union查询

案例

EXPLAIN 
SELECT c1.* 
	
FROM
	course c1,
	score s1,
	teacher t1 
WHERE
	c1.id = s1.course_id
	AND c1.teacher_id = t1.id;

在这里插入图片描述

4.2.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 ));

在这里插入图片描述

4.2.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 ));

在这里插入图片描述

4.2.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 
		)

在这里插入图片描述

4.2.5、derived

在FROM列表中包含的子查询被标记为DERIVED（衍生）
mysql会递归这些子查询,把结果放在临时表中

4.2.6、union

union的第二个或后面的SELECT语句：若union包含在from子句的子查询中,外层select将被标记为derived

在这里插入图片描述

4.2.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
)

在这里插入图片描述

4.2.8、 union result

union的结果

在这里插入图片描述

4.3、table

显示一行的数据时关于哪张表的

4.4、type（连接类型）

描述了找到所需数据使用的扫描方式。
性能从好到坏，依次如下(NULL>system>const>eq_ref>ref>range>index>All)
一般情况下,得至少保证达到range级别,最好能达到ref
单独查询—性能从好到坏

4.4.1、 NULL

MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

例如：

从一个索引列里选取最小值可以通过单独索引查找完成。

explain select min(id) from course;

在这里插入图片描述

4.4.2、system

表只有一行记录

4.4.3、const

const扫描的条件：

1. 命中主键(primary key)或者唯一(unique)索引
2. 被连接的部分是一个常量(const)值
3. 表示通过索引一次就找到了目标
	1. 因为只匹配一行数据,所以效率很快
		1.如将主键置于where条件中,mysql就能将该查询转换为一个常量

案例：

explain select * from teacher where teacher_no = 'T2010001';

在这里插入图片描述

4.4.4、eq_ref

1. 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,
	1.可以这样理解：对于前表的每一行(row)，后表只有一行被扫描。


2. 常见于主键或唯一索引扫描
	1.简单来说，就是多表连接中使用primary key或者 unique key作为关联条件


3. 发生的环境
	1.join查询
	2.等值连接
	3.命中主键(primary key)或者非空唯一(unique not null)索引

案例：

EXPLAIN 

SELECT c1.*  
FROM course c1
LEFT JOIN teacher t1 ON c1.teacher_id = t1.id;

在这里插入图片描述

4.4.5、ref

1. 非唯一性索引扫描
2. 简单来说：
	1.表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
	2.对于前表的每一行(row)，后表可能有多于一行的数据被扫描。
3.可能出现在join里，也可能出现在单表普通索引里，每一次匹配可能有多行数据返回，虽然它比eq_ref要慢，但它仍然是一个很快的join类型。

案例：

EXPLAIN 

SELECT c1.*  
FROM
	course c1,
	score s1 
WHERE
	c1.id = s1.course_id

course_id为普通非唯一(non unique)索引。

对于前表的每一行(row)，后表可能有多于一行的数据被扫描。

在这里插入图片描述

4.4.6、fulltext

全文索引

4.4.7、ref_or_null

如同ref,但可以搜索值是NULL的行。

案例：

EXPLAIN 
SELECT * 
FROM teacher 
WHERE
	NAME = 'xxx' 
	OR NAME IS NULL;

XXX 数据不存在

在这里插入图片描述

4.4.8、index_merge

索引合并优化

案例：

EXPLAIN 

SELECT *  
FROM teacher  
WHERE
	id = 1 
	OR teacher_no = 'T2010001'

在这里插入图片描述

4.4.9、unique_subquery

1. 该类型替换了下面形式的IN子查询的ref:

	value IN (SELECT primary_key FROM single_table WHERE some_expr)


2. unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。

4.4.10、index_subquery

1. 该联接类型类似于unique_subquery。
2. 可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:
	1.value IN (SELECT key_column FROM single_table WHERE some_expr)

4.4.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;

注意：必须是索引，否则不能批量"跳过"。

在这里插入图片描述

4.4.12、index

全索引扫描
index类型只遍历索引树,这通常比All快,因为索引文件通常比数据文件小
index是从索引中读取,all从硬盘中读取

案例：

explain select id from course;

在这里插入图片描述

4.4.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;

filtered = 查询结果条数/总查询条数 * 100

例如：查询结果只有3条数据，总共查询了 3*5 条数据 , 所以 filtered = 20

如果id上不建索引，对于前表的每一行(row)，后表都要被全表扫描。

在这里插入图片描述

4.5、possible_keys

可能使用的索引

4.6、key

1. 实际使用的索引，必然包含在possible_keys中
2. 如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）
3. 很少的情况下，MYSQL会选择优化不足的索引。这种情况下，要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

4.6.1、覆盖索引

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行

查询中若使用了覆盖索引,则该索引仅出现在key列表中,possible_keys不会出现

4.7、key_len

表示索引字段的最大可能长度，并非实际使用长度
key_len是根据表定义计算而得，不是通过表内检索出的。
可通过该列计算查询中使用的索引的长度
在不损失精确性的情况下，长度越短越好。
如果键是key 是 NULL,则长度为NULL。

4.8、ref

1. 显示索引的哪一列被使用了，
	1. 简单理解：就是表的连接过滤条件，即哪些列或常量被用于查找索引列上的值
2. 如果可能的话，最好是一个常数。
3. 显示使用哪个列或常数与key一起从表中选择行。

4.9、rows

预估出找到目标所需要读取的行数，也就是说，用的越少越好.

4.10、Extra

4.10.1、Using filesort

mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为 “Using filesort”
性能低，需要被优化

4.10.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;

在这里插入图片描述

4.10.3、Using index

1. 表示相应的select操作中使用了覆盖索引
	1. 仅仅使用了索引中的信息，而没有读取表的数据行，效率不错。
2. 如果同时出现using where，表明索引被用来执行索引键值的查找；
3. 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

4.10.4、Using where

mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。

在这里插入图片描述

4.10.5、Using join buffer

1. 强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。
	1. 如果出现了这个值，那应该注意，需要添加索引来改进能。
2. 如果多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

案例：

EXPLAIN 
SELECT
	s1.*,
	t1.* 
FROM
	student s1,
	teacher t1

在这里插入图片描述

4.10.6、impossible where

无法查到结果：where子句的值总是false，不能用来获取任何元组

案例：

EXPLAIN 

SELECT *  
FROM teacher  
WHERE id = 1 and id = 2

4.10.7、select tables optimized away

在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

4.10.8、distinct

一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

案例：
EXPLAIN

SELECT 
DISTINCT t1.name  
FROM teacher t1
LEFT JOIN course c1 ON t1.id = c1.teacher_id;

在这里插入图片描述

4.10.9、not exists

MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再继续往下搜索

4.10.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

在这里插入图片描述

4.10.11、 Using sort_union(…), Using union(…), Using intersect(…)

1. 这些函数说明如何为index_merge联接类型合并索引扫描。
2. 简单来说：
	1. 对多个索引分别进行条件扫描，然后将各自的结果进行合并（intersect/union）

4.10.12、Using index for group-by

表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。

4.10.13、Select tables optimized away

1. SELECT操作已经优化到不能再优化了
	1. MySQL根本没有遍历表或索引就返回数据了

4.10.14、No tables used

Query语句中使用from dual 或不含任何from子句

案例：

explain select now() from dual;

在这里插入图片描述

4.11、filtered

1.通过条件过滤出的行数/查找范围的总行数*100
2.单位是百分比
3.越小说明要优化

ylx814056815

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql优化--explain

mysql优化–explain一、介绍查看SQL语句执行顺序以及每一步都做了什么二、作用分析SQL执行语句的时候有没有完全利用好索引三、怎么使用explain sql四、数据准备DROP TABLE IF EXISTS `course`;CREATE TABLE `course` ( `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键id,自增涨', `name` varchar(20) DEFAULT NULL COMMENT
复制链接

扫一扫

专栏目录