邯城往事
来自邯郸社畜的呐喊
Mysql执行计划
运行 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
分析执行计划的返回结果
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 练习题