SQL 优化专题
2024-05-30 11:09:58 0 举报AI智能生成
SQL 优化专题是一篇深入探讨如何提高数据库性能和效率的技术文章。本文针对各种SQL查询进行了全面分析，提供了一系列优化技巧和最佳实践。通过实例演示，读者可以学习到如何避免常见的性能瓶颈，提高查询速度。此外，文章还涵盖了索引优化、查询优化、数据库设计优化等关键领域。无论是数据库管理员还是开发人员，都可以从本文中受益匪浅，提升自身技能水平。
作者其他创作
大纲/内容
基于规则的优化 & 子查询优化
优化器如何处理子查询是重点
条件化简
MySQL 会根据一些规则将烂 SQL 重写为可以高效执行的 SQL，这个过程叫做查询重写
移除不必要的括号
常量传递
移除没用的条件
表达式计算
HAVING 子句和 WHERE 子句的合并
常量表检测
通过这两种方式查询的表叫做常量表，查询优化器在分析一个 SQL 时，首先执行常量表查询，然后将 SQL 中涉及这个常量表的条件全部替换成常数，再去分析其余表的查询成本
查询的表中最多只有一条记录，这种查询不能使用 InnoDB 表
主键或者唯一二级索引的等值匹配查询
外连接消除
在外连接查询中，指定的 WHERE 子句中包含被驱动表中的列不为 NULL 值的条件叫做空值拒绝，在被驱动表的 WHERE 子句符合空值拒绝的条件后，外连接与内连接就可以相互转换了，这种转换带来的好处就是优化器可以通过评估表的不同连接顺序的成本，选出成本最低的连接顺序来执行查询
子查询优化
子查询语法
在 SELECT 子句中
SELECT (SELECT m1 FROM t1 LIMIT 1)
在 FROM 子句中
SELECT m, n FROM (SELECT m2+1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t
放在 FROM 子句中的子查询在逻辑上相当于一个表，这个子查询叫做派生表
在 WHERE 或者 ON 子句中
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2)
这是最常使用子查询的方式，将子查询放到外层查询的 WHERE 子句或者 ON 子句的表达式中
在 ORDER BY 子句中
虽然语法支持，但没啥意义
在 GROUP BY 子句中
虽然语法支持，但没啥意义
子查询分类维度
按照返回的结果集区分子查询
标量子查询
只返回一个单一值的子查询
SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2)
标量子查询可以作为一个单一值或者表达式的一部分出现在查询语句的各个地方
行子查询
只返回一条记录的子查询，不过这条记录需要包含多个列，如果只包含一个列，就是标量子查询
SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1)
列子查询
只返回一个列的子查询，不过这个列需要包含多条记录，如果只包含一条记录，就是标量子查询
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2)
表子查询
返回多记录多列的子查询
SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2)
按照与外层查询的关系区分子查询
不相关子查询
如果子查询可以单独运行出结果，而不依赖于外层查询的值，那这个子查询就是不相关子查询
相关子查询
如果子查询的执行需要依赖于外层查询的值，那这个子查询就是相关子查询
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2)
这个子查询有一个搜索条件是 n1 = n2，由于 n1 是外层查询的列，也就是说这个子查询的执行需要依赖于外层查询的值，所以它就是一个相关子查询
子查询在布尔表达式中的使用场景
= > < >= <= <> != <=> (SELECT)
SELECT * FROM t1 WHERE m1 < (SELECT MIN(m2) FROM t2)
这里的子查询只能标量子查询或者行子查询
[NOT] IN (SELECT)
SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2)
判断某个操作数是否存在于由子查询结果集组成的集合中
> ANY(SELECT)
SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2)
只要在子查询的结果集中存在一个值，某个指定的操作数与该值通过比较操作符进行比较时，结果为 TRUE，那么整个表达式的结果就为 TRUE
ANY 与 SOME 表达的意思相同
=ANY 等价于 IN 操作
> ALL(SELECT)
SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2)
某个指定的操作数与该子查询结果集中的所有值通过比较操作符进行比较时，结果都是 TRUE，那么整个表达式的结果就是 TRUE
[NOT] EXISTS (SELECT)
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2)
我们并不关心这个子查询最后查询出的结果到底是什么，我们真正关心的是子查询的结果集中是否存在记录，如果子查询结果集中有记录，那么整个 EXISTS 表达式的结果就是 TRUE
子查询语法注意事项
子查询必须用小括号括起来
不用小括号括起来的子查询是非法的
在 SELECT 子句中的子查询必须是标量子查询
要想得到标量子查询或者行子查询，但是又不能保证子查询的结果集只有一条记录时，应该使用 LIMIT 1 语句来限制记录数量
对于 IN/ANY/SOME/ALL 子查询来说，子查询中不允许有 LIMIT 语句
别问为什么不能在这类子查询中使用 LIMIT，MySQL 就是这么规定的
子查询的结果集其实相当于一个集合，集合里的值是否排序一点儿都不重要，记住不要在 IN 子查询中使用 ORDER BY，这就是画蛇添足
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 ORDER BY m2)
集合中的值是否去重也没啥意义
SELECT * FROM t1 WHERE m1 IN (SELECT DISTINCT m2 FROM t2)
在没有聚集函数以及 HAVING 子句时，GROUP BY 子句就是个摆设
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 GROUP BY m2)
对于这些无用的子句，优化器在一开始就将它们干掉了
不允许在一条语句中增删改某个表的记录时，同时还对该表进行子查询
DELETE FROM t1 WHERE m1 < (SELECT MAX(m1) FROM t1)
子查询在 MySQL 中是怎么执行的
小白眼中的子查询执行方式
不相关子查询执行方式
先单独执行子查询，获取子查询的结果集
再将得到的子查询结果集作为外层查询的参数，再执行外层查询
相关子查询执行方式
先从外层查询中获取一条记录
然后从获取的这条记录中拎出子查询中涉及的值，然后执行子查询
最后根据子查询的查询结果集来检测外层查询 WHERE 子句的条件是否成立，如果成立，就将外层查询的那条记录加入到结果集中，否则就丢弃
重复执行步骤1，获取第二条外层查询中的记录，以此类推
标量子查询、行子查询的执行方式
不相关标量子查询或者行子查询执行方式
SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)
首先单独执行子查询，获取子查询查询结果集
将子查询结果集作为外层查询的参数
执行外层查询
总结：包含不相关的标量子查询或者行子查询的 SQL 来说，MySQL 会分别独立执行子查询和外层查询，很简单就是两个单表查询
相关标量子查询或者行子查询执行方式
SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1)
先从外层查询中获取一条记录
然后从这条记录中拎出子查询中涉及的值，然后执行子查询
最后根据子查询的结果集来检测外层查询 WHERE 子句的条件是否成立，如果成立就将外层查询的这条记录加入到结果集中，否则就丢弃
再回到步骤1获取外层查询的下一条记录，直到外层查询中获取不到记录为止
在使用标量子查询或者行子查询的场景中，MySQL 的执行方式并没有什么新鲜的，与小白的理解是一致的
IN 子查询优化
IN 子查询是最常用的子查询
物化表的提出
将 IN 子查询的结果集写入到一个临时表中
该临时表的列就是 IN 子查询结果集中的列
写入临时表的记录会被去重，这里去重的目的是让临时表尽量小，节省空间
由于 IN 子查询结果集一般不会很大，所以会建立基于内存的 MEMORY 临时表，而且会为该内存临时表建立哈希索引，判断匹配过程会非常快
如果子查询结果集很大，临时表类型会转为基于磁盘的存储引擎来保存记录，索引类型也相应地转为 B+ 树索引
物化：将子查询结果集中的记录保存到临时表的过程就叫物化
物化表：那个存储子查询结果集的临时表就叫物化表
物化表也是有索引的：基于内存就有哈希索引，基于磁盘就有 B+ 树索引
注意，相关子查询不是一个独立的查询，所以不能进行物化操作
物化表转连接
SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a')
SELECT s1.* FROM s1 INNER JOIN 物化表 ON s1.key1 = 物化表.m_val
这两个 SQL 在语义上是一样的，所以 IN 子查询可以重写为内连接形式
查询优化器会分别分析不同连接顺序的执行成本
物化子查询时需要的成本
扫描驱动表的成本：只访问一次
被驱动表访问成本：访问多次
选择成本最低的连接顺序执行
将子查询转换为半连接
半连接概念解释
半连接 semi-join 的定义背景：在语义上理解 IN 子查询表达式的作用：外层查询的一条记录在结果集中要么不出现，要么就是只出现一次，这个与转为正常连接的 SQL 是有差异的地方，如果我们将 IN 子查询直接粗暴地转为连接查询，那么结果集中外层表的记录可能会重复出现，所以这时就引出来半连接的概念
将 s1 表与 s2 表进行半连接的语义为：对于 s1 表中的某条记录来说，我们只关心 s2 表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集只保留 s1 表的记录
半连接是 MySQL 内部采用的一种执行子查询的方式，我们用户是不能显式操作的
半连接实现方案
消除半连接结果集中重复值的方案
Table Pullout 子查询中的表上拉
SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a')
SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2 WHERE s2.key3 = 'a'
当子查询的查询列表中只有主键或者唯一索引列时，可以直接将子查询中的表上拉到外层查询的 FROM 子句中，并将子查询中的搜索条件合并到外层查询的搜索条件中，表上拉的核心原因是 IN 子查询的结果集数据没有重复值
Duplicate Weedout 重复值消除
SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a')
s1 表的单条记录在 s2 表中可能存在多条匹配的记录，所以就有可能被多次添加到最后的结果集中
Duplicate Weedout 方案是建一个临时表，这个临时表只存 s1 表的主键，这样每当 s1 表中的某条记录加入结果集之前，先将这条记录的主键加到临时表中，如果临时表能够添加成功，说明这个记录之前没被加过结果集，那么现在就加到最后的结果集中，反之，不加直接丢弃
LooseScan 松散扫描
SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b')
当 IN 子查询走了某个普通二级索引，并且这个子查询的查询列表中只有这个索引的索引列，那么再将 IN 子查询转为半连接查询后，就可以将这个子查询中的表作为驱动表，注意这里虽然也是扫描索引，但是只取键值相同的第一条记录去执行匹配操作
Semi-join Materialization 半连接物化
先将外层查询的 IN 子句中的不相关子查询进行物化，然后再将外层查询的表与物化表进行连接，这种执行方式在本质上也算是一种实现半连接的方案，只不过由于物化表中没有重复的记录，所以可以直接将子查询转为连接查询
FirstMatch 首次匹配
先取一条外层查询的记录
然后到子查询的表中寻找符合匹配条件的记录
如果能找到一条，就将该外层查询记录加到最终的结果集中并且停止查找更多匹配的记录，如果找不到，就将该外层查询的记录丢弃，然后再开始取下一条外层查询的记录，重复这个过程，直到外层查询获取不到记录为止
半连接的适用条件
SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...
SELECT ... FROM outer_table WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ...
该子查询必须是与 IN 组成的布尔表达式，并且在外层查询的 WHERE 或者 ON 子句中出现
外层查询也可以有其他的搜索条件，只不过必须使用 AND 与 IN 子查询的搜索条件连接起来
该子查询必须是一个单一的查询，不能是由 UNION 连接起来的若干个查询
该子查询不能包含 GROUP BY、HAVING 语句或者聚集函数
不适用于半连接的情况
在外层查询的 WHERE 子句中，存在其他搜索条件使用 OR 与 IN 子查询组成的布尔表达式连接起来的情况
SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a') OR key2 > 100
使用 NOT IN 而不是 IN 的情况
SELECT * FROM s1 WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a')
IN 子查询在 SELECT 子句中的情况
SELECT key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a') FROM s1
IN 子查询中包含 GROUP BY、HAVING 或者聚集函数的情况
SELECT * FROM s1 WHERE key2 IN (SELECT COUNT(*) FROM s2 GROUP BY key1)
IN 子查询中包含 UNION 的情况
SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a' UNION SELECT common_field FROM s2 WHERE key3 = 'b')
无法转为半连接的 IN 子查询的执行方式
对于不相关子查询，先将 IN 子查询物化之后再参与查询
SELECT * FROM s1 WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a')
注意，这里将子查询物化之后，不能转为与外层查询的表连接，只能是先扫描 s1 表，然后针对 s1 表的某条记录来判断该记录的 key1 值是否在物化表中
无论 IN 子查询是相关的还是不相关的，都可以将 IN 子查询转为 EXISTS 子查询
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND outer_expr = inner_expr)
只要 IN 子查询放在 WHERE 或者 ON 子句中，那么 IN 就可以转为 EXISTS，否则 IN 是不能转为 EXISTS 的
IN 转为 EXISTS 的好处就是转换之前可能不走索引，但是转换之后可能就走索引了
如果 IN 子查询不满足转为半连接的条件，又不能转为物化表，或者转为物化表的成本太高，那么它就会被转为 EXISTS 子查询
IN 子查询执行方案优先级总结
如果 IN 子查询符合转换为半连接的条件，查询优化器就会将该子查询转为半连接，从五种半连接执行策略中选择执行成本最低的方案执行
如果 IN 子查询不符合转换为半连接的条件，查询优化器会从下边两种策略中找成本最低的方案执行
先将子查询物化，再执行查询
执行 IN 到 EXISTS 的转换
ANY/ALL 子查询优化
如果 ANY/ALL 子查询是不相关子查询，那么子查询就会转换成我们熟悉的方式来执行
< ANY (SELECT inner_expr ...) 转换为 < (SELECT MAX(inner_expr) ...)
> ANY (SELECT inner_expr ...) 转换为 > (SELECT MIN(inner_expr) ...)
< ALL (SELECT inner_expr ...) 转换为 < (SELECT MIN(inner_expr) ...)
> ALL (SELECT inner_expr ...) 转换为 > (SELECT MAX(inner_expr) ...)
EXISTS 子查询的执行
如果 EXISTS 子查询是不相关子查询，那么查询优化器会首先执行该子查询，然后对查询进行 TRUE 或者 FALSE 的改写
如果 EXISTS 子查询是相关子查询，就只能按照小白认为的相关子查询的执行方式来执行，要是可以走到索引，效率就会相应提高
对于派生表的优化
将派生表和外层查询合并，也就是将查询重写为没有派生表的形式
将派生表与外层查询合并，然后将派生表中的搜索条件放到外层查询的搜索条件中，通过将外层查询与派生表合并的方式可以消除派生表，就意味着我们不用再付出创建和访问临时表的成本了
派生表不能与外层查询合并的场景，当派生表中有下面这些函数或者语句时，就不可以与外层查询合并
聚集函数
DISTINCT
GROUP BY
HAVING
LIMIT
UNION 或者 UNION ALL
派生表对应的子查询的 SELECT 子句中含有另一个子查询
把派生表物化
将派生表的结果集写到一个临时表中，然后把这个物化表当作普通表一样来参与查询
在物化派生表时，会采用延迟物化的策略，也就是在查询中会真正使用到派生表时才会去物化它，而不是在执行查询之前就先将派生表物化，如果另外一个参与连接的表都没有可用于连接的记录，结果集就是空的，那就根本用不上这个派生表，所以就没有必要去物化它
分析子查询，从语义分析入手，这样去理解语法的转换就更加形象了
将 IN 子查询转换为连接查询可以充分利用查询优化器的作用
0 条评论
下一页
