规则优化
条件优化
-
移除不必要的括号
-
常量传递:某个常量与列的等值匹配,且其他条件包含这个值,就直接进行替换
例如a=5 and b>a 则可以变成 a=5 and b>5
-
移除没用的条件
-
表达式计算
最好让索引列以单独的形式出现在搜索条件表达式中
-
HAVING子句和WHERE子句的合并
-
常量表检测
- 查询的表中一条记录都没有,或者只有一条记录
- 使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询这个表
外连接消除
如果where子句的搜索条件“指定被驱动表的列不为null",那么外连接和内连接就没区别了。
该条件称为空值拒绝。
子查询优化
放在from子句后面的子查询成为派生表。
只返回一个单一值得子查询称为标量子查询。
返回一条记录,且该记录包括多个列得子查询称为行子查询。
查询一个列的数据,列中需包含多条记录,称为列子查询。
子查询结果中包含多条记录,多个列称为表子查询。
子查询可以单独运行结果,不依赖于外层查询得值得查询称为不相关子查询。
子查询执行需要依赖于外层查询的值,称为相关子查询。
子查询注意事项
- 子查询必须用小括号括起来
- 在select子句中的子查询必须是表量子查询
- 要想得到标量子查询或者行子查询,但又不能保证子查询的结果集只有一条记录时,应该使用limit 1语句来限制记录数量
- 对于[NOT]IN/ANY/SOME/ALL子查询来说,子查询中不能有limit语句
- 不允许在一条语句中增删改查某个表的记录时,同时还对该表进行子查询
子查询执行方式
- 不相关子查询:
- 单独执行子查询
- 然后将子查询得到的结果当作外层查询的参数,再执行外层查询
- 相关子查询:
- 先从外层查询中获取一条记录
- 然后就从获取的记录中找出子查询中涉及的值
- 根据子查询的查询结果来检测外层查询where子句的条件是否成立
- 重复执行步骤1,获取第二条外层查询中的记录
(1)物化表
为了解决1.结果集太多,内存放不下,2.外层查询子查询结果集太多,in子句中参数特别多的问题。
提出不直接将不相关子查询的结果集当作外层查询的参数,而是将结果集写入临时表中,写入临时表注意:1.临时表的列就是子查询结果集的列,2.写入临时表的记录会被去重。该临时表称为物化表。
如果子查询结果集非常大,超过系统变量tmp_table_size或者max_heap_table_size的值,临时表会转而使用 基于磁盘的存储引擎来保存结果集中的记录,索引类型转变为B+树索引。
(2)物化表转连接
- 从表1的角度来看,就是表1中每条记录,如果该记录的值在子查询对应的物化表中,则该记录会被加入最终的结果集。
- 从子查询物化表看,对于子查询物化表的每个值,如果能在表1中找到对应的列的值与该值相等的记录,那么就把这些记录加入到最终的结果集中。
总查询成本
表作为驱动表:
- 物化子查询时需要的成本
- 扫描表时的成本
- 表中记录数量*通过条件对物化表进行单表访问的成本
物化表作为驱动表
- 物化子查询时需要的成本
- 扫描物化表时的成本
- 物化表中的记录数量*通过条件对表进行单表访问的成本
(3)将子查询转换为半连接
半连接:对于表1中某条记录来说,只关心表2中是否存在与之匹配的记录,而不关心具体由多少条记录与之匹配,最后结果集中只保留表1的记录。
半连接的实现:
- table pullout:当子查询的查询列表处只有主键或者唯一索引列,可以直接把子查询中的表上拉到外层查询的from子句中,并把子查询中的搜索条件合并到外层查询的搜索条件中。
- duplicate weedout:使用临时表消除半连接结果集中重复值。
- loosescan:取键值相同的第一条记录去执行匹配的操作方式。
- semi-join materialization:半连接物化
- firstmathch:先取一条外层查询记录,然后到子查询中 寻找符合匹配条件的记录。以此类推。转为半连接,用前面的几种方式。
(4)半连接适用条件
- 该子查询必须是与IN操作符组成的布尔表达式,并且在外层查询的WHERE或者ON子句中出现。
- 外层查询也可以有其他的搜索条件,必须用AND操作符与IN子查询的搜索条件连接。
- 子查询必须是一个单一的查询,不能由UNION连接起来的若干个查询。
- 子查询 不能包括GROUP BY,HAVING语句或者聚集函数。
(5)不适用半连接的情况
- 外层查询的WHERE子句中,存在其他搜索条件使用OR操作符与IN子查询组成的布尔表达式连接起来的情况。
- 使用NOT IN而不是IN的情况。
- 位于SELECT 子句中的IN子查询的情况。
- 子查询中包含GROUP BY,HAVING或者聚集函数的情况。
- 子查询中包含UNION的情况。
优化不能转为半连接查询的子查询。
- 可以先尝试物化后在参加查询
- 无论子查询是否相关,都可以将IN子查询尝试转为EXISTS子查询。
IN子查询不满足转换为半连接的条件,又不能转换为物化表,或者转换为物化表的成本太高,那么它就会被转换为EXISTS子查询。