Mysql执行计划

Published on with 0 views and 0 comments

运行 SQL 建表加数据

--1.学生表 Student(SId,Sname,Sage,Ssex) --SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--2.课程表 Course(CId,Cname,TId) --CId --课程编号,Cname 课程名称,TId 教师编号

--3.教师表 Teacher(TId,Tname) --TId 教师编号,Tname 教师姓名

--4.成绩表 SC(SId,CId,score) --SId 学生编号,CId 课程编号,score 分数

测试数据

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));  
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');  
insert into Student values('02' , '钱电' , '1990-12-21' , '男');  
insert into Student values('03' , '孙风' , '1990-05-20' , '男');  
insert into Student values('04' , '李云' , '1990-08-06' , '男');  
insert into Student values('05' , '周梅' , '1991-12-01' , '女');  
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');  
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');  
insert into Student values('09' , '张三' , '2017-12-20' , '女');  
insert into Student values('10' , '李四' , '2017-12-25' , '女');  
insert into Student values('11' , '李四' , '2017-12-30' , '女');  
insert into Student values('12' , '赵六' , '2017-01-01' , '女');  
insert into Student values('13' , '孙七' , '2018-01-01' , '女');
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10))  
insert into Course values('01' , '语文' , '02');  
insert into Course values('02' , '数学' , '01');  
insert into Course values('03' , '英语' , '03');
create table Teacher(TId varchar(10),Tname varchar(10))  
insert into Teacher values('01' , '张三');  
insert into Teacher values('02' , '李四');  
insert into Teacher values('03' , '王五');
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1))  
insert into SC values('01' , '01' , 80);  
insert into SC values('01' , '02' , 90);  
insert into SC values('01' , '03' , 99);  
insert into SC values('02' , '01' , 70);  
insert into SC values('02' , '02' , 60);  
insert into SC values('02' , '03' , 80);  
insert into SC values('03' , '01' , 80);  
insert into SC values('03' , '02' , 80);  
insert into SC values('03' , '03' , 80);  
insert into SC values('04' , '01' , 50);  
insert into SC values('04' , '02' , 30);  
insert into SC values('04' , '03' , 20);  
insert into SC values('05' , '01' , 76);  
insert into SC values('05' , '02' , 87);  
insert into SC values('06' , '01' , 31);  
insert into SC values('06' , '03' , 34);  
insert into SC values('07' , '02' , 89);  
insert into SC values('07' , '03' , 98);
  
提出一个查询的需求 A,并用 sqlA 来实现

需求 A:查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩

sqlA:

#查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT
	student.SId,
	student.Sname,
	tmp.avgscore
FROM
	student
	RIGHT JOIN (
	SELECT
		Sid,
		Count( Cid ),
		avg( score ) avgscore
	FROM
		sc
	WHERE
	sc.score < 60 GROUP BY Sid HAVING Count( Cid ) >= 2
	) tmp ON tmp.Sid = student.SId
用执行计划分析 sqlA

EXPLAIN sqlA
image.png

分析执行计划的返回结果

id id 是 select 的执行顺序,id 越大优先级越高,越先被执行,id 相同时下面的先执行.

select_type

select_type 说明
SIMPLE 简单查询,不包含 union 和子查询
PRIMARY 最外层查询,也就是 select 的主表
SUBQUERY 子查询中的第一个 select
DERIVED 导出表的 select(from 子句的子查询)
UNION 联合查询
UNION RESULT 使用联合的结果

table

table_type 说明
table_name 表明
衍生表
行数据是联合之后的数据 id 处于 m 和 n
子查询

partitions 在使用分区表的时候才能用到,暂时没用到过这种高级功能。

type 查询的类型

性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

由左至右,由最差到最好

type 说明
ALL 全数据表扫描
index 全索引表扫描
RANGE 对索引列进行范围查找
INDEX_MERGE 合并索引,使用多个单列索引搜索
REF 根据索引查找一个或多个值
EQ_REF 搜索时使用 primary key 或 unique 类型
CONST 常量,表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常熟,CONST 表很快,因为它们只读取一次。
SYSTEM 系统,表仅有一行(=系统表)

possible_keys

预计可能使用的索引,在不和其他表进行关联的时候,查询表时可能使用的索引

key

实际查询的过程中使用的索引,显示 MySQL 在查询中实际使用的索引,若没有使用索引,显示为 NULL

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度

ref

显示该表的索引字段关联了哪张表的哪个字段

rows

读取的行数,数值越小越好

filtered

返回结果的行数占读取行数的百分比,值越大越好

extra

常见的有下面几种

use filesort:MySQL 需要额外的一次传递,以找出如何按排序顺序检索行,如果是这个值,应该优化索引。

use temporary:为了解决查询,MySQL 需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的 GROUP BY 和 ORDERBY 子句时。

use index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时,可以使用该策略

use where:where 子句用于限制哪一行

Using join buffer (Block Nested Loop):连接查询的一种算法,如果慢可以通过添加索引解决

参考网址:

mysql Using join buffer (Block Nested Loop) join 连接查询优化 - 飞龙在生 - 博客园 (cnblogs.com)

MySQL :: MySQL 8.0 Reference Manual :: 8.8.1 Optimizing Queries with EXPLAIN

Mysql 经典练习题 50 题_original_recipe 的博客-CSDN 博客_mysql 练习题

MySQL 执行计划详解 - 云 + 社区 - 腾讯云 (tencent.com)

MySQL 索引创建及使用_程序人生-CSDN 博客_mysql 建立索引


标题:Mysql执行计划
作者:cuijianzhe
地址:https://cuijianzhe.github.io/articles/2021/12/01/1638348062390.html