数据库 | Mysql - [SQL 执行顺序 & 优化]

问仙长何方蓬莱

已于 2022-10-22 23:03:09 修改

阅读量458

点赞数

分类专栏：数据库文章标签：数据库 sql mysql

于 2022-10-22 21:12:49 首次发布

本文链接：https://blog.csdn.net/ZEUS00456/article/details/127460196

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

INDEX

§1 SQL 执行顺序

SQL 通用完整模板

SELECT [DISTINCT] <columns>
FROM <table> 
[ [<INNER>][<LEFT>][<RIGHT>] JOIN <table>
  ON <join-condition>
]
WHERE <condition>
GROUP BY <group-expression>
HAVING <having-conditions>
ORDER BY <order-expression>
LIMIT <limit-expression>

SQL 通用解析顺序
优先生成最终的表，所有要先关联、筛选、分组

FROM <table> 
[ ON <join-condition>
  [<INNER>][<LEFT>][<RIGHT>] JOIN <table>
]
WHERE <condition>
GROUP BY <group-expression>
HAVING <having-conditions>
SELECT 
DISTINCT <columns>
ORDER BY <order-expression>
LIMIT <limit-expression>

§2 表关联

在这里插入图片描述
PS：
Mysql 语法上不支持 full join
若需要使用全连接可以使用 left join union right join 实现

§3 执行计划

执行计划所有字段详解

id
执行序列
多条目具有相同 id 时视为处于同一个序列，顺序执行
多条目具有不同 id 时视为处于不同序列
- id 会递增
- id 越大，优先级越高，越先执行

select_type
查询级别

SIMPLE：简单查询，没有 UNION 或子查询
PRIMARY：主查询，最外层的 select
UNION：结果集连接，仅次于主查询
DEPENDENT UNION：依赖于外部结果的子查询 UNION
UNION RESULT：即 UNION 的结果
SUBQUERY：子查询
DEPENDENT SUBQUERY：依赖于外部结果的子查询，外部数据量很多时比为慢查询
UNCACHABLE SUBQUERY：不能缓存的子查询，常因使用了函数等影响结果的操作导致
DERIVED：派生查询，会生成临时表

table
被操作的表
被 <> 描述时，通常指对应 id 的 table
如 <union 1,2>，指当前操作的是 id 是 1 和 2 的操作对应的表

type
访问类型，可以理解为性能级别

system：系统
仅在表中只有一条记录时出现，常出现于结果集大小为 1 的子查询
const：常量
通过索引直接找到，常出现与使用主键或唯一索引的场景
在 where 后，Mysql 可以将之转化成一个常量
eq_ref：唯一引用
命中唯一索引单个值，并返回了唯一一行结果
也可能是命中主键，但此时主键的单个值通常是其他查询的结果，不是立即数
ref：引用
推荐级别
命中普通索引单个值，但此时可能返回唯一一行，也可能返回多行数据
full_text
ref_or_null
出现于在索引字段上进行 null 或唯一值匹配
通常优化成 union
index_merge
unique_subquery：唯一索引子查询
出现于子查询的所有信息可以通过子查询的唯一索引获取
index_subquery：索引子查询
出现于子查询的所有信息可以通过子查询的索引获取
range：范围
底线级别
命中索引上的多个值，常见于 between、<、>、like、in 等
index
索引覆盖，索引中的列就能满足所有查询要求，因此虽然理论上要查全表，但只扫描全索引即可
比 ALL 快，但依然不理想
ALL

possible_keys
可能使用的索引
通常 possible_key 命中的索引，才会被使用
但使用覆盖索引时，possible_keys = null，而 key 可以使用

key
实际使用的索引
若出现 possible_key 命中索引，但 key 中未使用，说明索引失效

key_len
索引中被使用的字节数，使用复合索引时此参数非常重要
数字 4 字节
字符 latin 1 字节，gbk 2 字节，utf 3 字节
可变长度 +2 字节，比如 VARCHAR
允许空值 +1 字节

假设在下面两个字段上创建复合索引
col1 varchar(10) utf null
col2 char(10) utf not-null
完全使用时，key_len = (103 + 2 + 1) + (103) = 63
若某次查询使用此索引但 key_len = 33，说明只使用了 col1 部分

ref
索引上的哪些列被使用了
可能有多个值，每个值按 <库名.表名.列名> 显示
如果某一列被常量使用，直接显示 const

rows
Mysql 认为执行当前查询时必须检查的行数，越小越好

filtered
当次操作前后数据保留的百分比，通常越大越好
但在表关联场景的驱动表上，越小越好，说明被过滤掉更多数据，驱动表还剩更少的循环

Extra
额外信息

Using filesort
排序未能使用表内索引进行，使用外部索引进行的排序被称为文件排序
表示排序未能复用表维护的索引
Using temporary
使用临时表，通常在堆查询结果的排序时使用
常见于 order by 和 group by
Using index
使用了覆盖索引，可能伴随 Using where，说明同时在索引中通过 where 进行了筛选
Using where
表明使用了 where 过滤

§4 SQL 优化

避免索引失效
使用复合索引时
- 尽量全值匹配，即精确匹配复合索引中所有列
- 务必满足最左原则
表关联时优化
- 尽量使用小关联，能 inner 就不 left 或 right，inner 会自动选择小表作为驱动表
- 尽量使用小表驱动大表，inner 会自动选择小表作为驱动表
- 使用外连接时，尽量使用被驱动表的索引
  A left join B：B 是被驱动表
  A right join B：A 是被驱动表
- 尽量避免在被驱动表上使用子查询，可能索引失效
- 尽量使用表关联代替子查询
排序优化
- 尽量使用已存在的索引，即哪个列上有索引，哪个列适合排序
- 避免 filesort
  Mysql 具有两种排序方式：index、filesort，前者效率远比后者高
  以下两种方式可以避免 filesort
  - 满足复合索引的最左匹配
  - where + order by 满足复合索引的最左匹配
- 避免乱序使用索引的排序规则进行排序
  可以按索引排序规则正向、逆向进行排序，但避免乱序
  如 index (a ASC,b DESC) 时，使用 a ASC,b ASC 排序
分组优化
- 适配排序优化
  分组前会自带一次排序，因此排序优化都适用于分组优化
- 尽量通过 where 而不是 having 进行筛选
避免在索引列上做任何操作
包括但不限于计算、函数、类型转换
尽量使用覆盖索引，严禁使用 select *
null、not null 可能对索引有影响
但不同版本会有不同表现
使用 like 时必须左侧匹配
使用字符串时，必须使用单引号包围
优化不等于
!= 和 <> 可能造成索引失效
可以通过 union 或覆盖索引优化
优化 or
or 可能造成索引失效
可以通过 in 或覆盖索引优化
大偏移量分页优化
- 索引可以计算时，直接使用计算的 id 跳过偏移量
  where id>=1000000 order by id limit 30
- 索引不可计算时，先查询起始点 id，然后在分页
  又可能需要单独维护索引表
  如 where id>=( select id from table order by id limit 1000000,1 ) order by id limit 30
  或 select t1.* from table as t1, (select id from table where condition limit 1000000,30) as t2 wher t1.id=t2.id
update 优化
update 时，需要按索引列进行筛选，否则行锁可能升级为表锁
这是因为 update 时，行锁锁住的就是对应的索引