数据库查询优化

逻辑优化

逻辑查询优化主要工作是：找到SQL语句的等价变换方式，使SQL执行更高效。关系代数等价变换对查询优化是有指导意义的

查询重写规则
传统的OLTP使用选择(From),投影(select, project),连接操作(join)相结合，成为SPJ查询

1. 选择操作

通过选择操作下推，目的是尽量减少连接操作前的元组数(减少行)

2. 投影操作

投影操作下推，尽量减少链接操作前的列数

3. 连接操作(涉及到两个子问题)
   1. 多表连接顺序决定效率
   2. 不同语义的多表连接不能随意更换顺序

因此，根据SQL语句的形式特点，可以分为基于SPJ查询优化，和针对非SPJ查询优化

子查询优化

子查询分类

从子查询涉及对象的和外层查询对象之间的关系看，可以分为以下两种：

1. 关联子查询

子查询执行依赖外层父查询的一些属性值。父查询参数改变时，子查询通常需要迭代重新查询(父查询先于子查询执行)

2. 非关联子查询

子查询不依赖外层父查询的值，子查询先于父查询执行

从特定谓词看，可以分为以下3种：

1. [NOT]IN/ALL/ANY/SOME子查询
2. [NOT]EXIST子查询
3. 其他子查询

从结果看，可以分为一下4类：

1. 标量子查询：子查询结果是一个值
2. 列子查询
3. 行子查询
4. 表子查询

优化思路

子查询合并

多个子查询合并为一个子查询

子查询上拉

把某些子查询重写为等价的多表连接操作，常见的IN/ANY/SOME/ALL/EXISTS依据情况转换为半连接。是最常用的技术

子查询展开有两种形式：

1. 子查询出现了聚集，GROUPBY，DISTINCT子句，则不能上拉
2. 子查询只是一个简单的SPJ格式，可以拉到上层

子查询优化的步骤

1. 将子查询和上层From子句连接为同一个From子句，并修改运行参数
2. 将投影列和上层相关列作join或许semi join
3. 将子查询的where条件作为一个整体和上层合并，并用AND条件连接。

聚集子查询消除

聚集函数上推，转换为不包含聚集函数的子查询

视图重写

视图重写为带子查询形式

等价谓词重写

通常就是将LIKE/IN/OR/ANY之类的谓词转换为常用的替代

1. LIKE规则：更改LIKE为'='，'>'子类的操作符。不用全表扫描
2. BETWEEN-AND：更改为'>','<',and。BETWEEN-AND是全表扫描语义
3. IN转换OR：这里的IN只是IN操作符，有的数据库IN是全表扫描
4. IN转换ANY：
5. OR转换ANY
6. ALL/ANY转换为聚合函数：转换为MAX/MIN
7. NOT规则：NOT是全表扫描，转化为非NOT，可以利用索引
8. OR重写并集：

条件化简

1. 把Having条件并入where条件：不存在group by或者聚集函数情况(一般都有吧)
2. 去除条件表达式中冗余的括号：便于后期化简
3. 常量化简：尽量把表达式两端某个值化简为常量
   • col_1=col_2 and col_1 = 3 等价于 col_1=3 and col_2=3
4. 消除死码：消除恒定为假的表达式
5. 表达式计算：简单计算直接得出结果
6. 等式变换：变得标准
   • where -a = 3 等价于 where a = -3
7. 不等式变换：去除重复的不等式

外连接消除

消除外连接，不是所有的外连接都能转换为内连接，只有基于"空值拒绝"的才可以。其实SQL还是外连接，只是这样可以通过调整多表连接顺序优化。

例如：left join，join后右边的列被非空条件排除

select * from x left join y on (x.x = y.y) where y.y is not null;

嵌套连接

多个连接有顺序指定，消除括号。只适用于内连接之间

连接消除

语义优化

针对非SPJ的优化

简单提一下，可以分为三种:

1. GROUPBY优化
   1. 分组下推：通过GROUPBY下推减少元祖数
   2. 分组上推：通过连接操作减少元祖数
2. ORDER优化
   1. 利用索引消除排序
   2. 排序下推
3. DISTINCT优化
   1. DISTINCT消除
   2. DISTINCT下推
   3. DISTINCT迁移

总结

总结一些比较有用的优化技术

1. 谓词下推(必用)
2. 子查询消除(必用)
3. 各种连接的消除
4. 等价谓词重写(可行)
5. 语义优化
6. 剪支优化

物理查询优化

物理优化阶段，主要解决的问题如下：

1. 如何单表扫描最优
2. 两表JOIN如何选择连接种类
3. 多表JOIN如何选择连接顺序

单机两表JOIN的种类

有loop join，hash join，merge join。
对于loop join，如果大表带索引可能会查得很快，好像GoldFish中使用loop join查询挺好的。