SQL优化--逻辑优化--非SPJ优化

1）GROUP BY分组转换（MySQL不支持）

①分组操作下移

GROUPBY 操作可能较大幅度减小关系元组的个数，如果能够对某个关系先进行分组操作，然后再进行表之间的连接，很可能提高连接效率。这种优化方式是把分组操作提前执行。下移的含义，是在查询树上，让分组操作尽量靠近叶子结点，使得分组操作的结点低于一些选择操作。

②分组操作上移

如果连接操作能够过滤掉大部分元组，则先进行连接后进行GROUPBY 操作，可能提高分组操作的效率。这种优化方式是把分组操作置后执行。上移的含义，和下移正好相反。

对于带有 GROUPBY 等操作的非 SPJ 格式的 SQL 语句，在本节之前提及的技术都适用，只是结合了 GROUPBY 操作的语义进行分组操作。因为 GROUPBY 操作下移或上移均不能保证重写后的查询效率一定更好，所以，要在查询优化器中采用基于代价的方式来估算某几种路径的优劣。

③MySQL的GROUP BY优化

MySQL 对于 GROUPBY 的处理，通常采用的方式是扫描整个表、创建一个临时表用以执行分组操作。查询执行计划中出现“ Usingtemporary” 字样表示 MySQL 采用了常规的处理方式。对于 GROUPBY 的优化，则尽量利用索引。

2）ORDER BY 优化

①排序消除（ Order By Elimination ， OBYE ）

优化器在生成执行计划前，将语句中没有必要的排序操作消除（如利用索引），避免在执行计划中出现排序操作或由排序导致的操作（如在索引列上排序，可以利用索引消除排序操作）。

②排序下推（ Sort push down ）

把排序操作尽量下推到基表中，有序的基表进行连接后的结果符合排序的语义，这样能避免在最终的大的连接结果集上执行排序操作。

③MySQL的GROUP BY优化

利用索引的条件是：分组子句中的列对象源自同一个 btree 索引（不支持利用Hash 索引进行优化）的全部或前缀部分的部分有序的键（分组使用的索引列与索引建立的顺序不匹配则不能使用索引）。

主要的方式有 :

a）Loose IndexScan 

直接用索引完成分组操作中对分组列的检索，不必考虑索引的全部键满足 WHERE 子句，只要有部分匹配 WHERE 中的列对象即可（ loose ，利用索引中部分列为“松散”）。

b）Tight IndexScan 

索引中的全部键与 WHERE 子句中的列对象匹配（ tight ，利用索引中的全部列为“严密”）。

3）DISTINCT 优化

①DISTINCT 消除（ Distinct Elimination ）（MySQL支持）

如果表中存在主键、唯一约束、索引等，则可以消除查询语句中的 DISTINCT （这种优化方式，在语义优化中也涉及，本质上是语义优化研究的范畴）。

②DISTINCT 推入（ Distinct Push Down ）（MySQL不支持）

生成含 DISTINCT 的反半连接查询执行计划时，先进行反半连接再进行 DISTICT 操作；也许先执行 DISTICT 操作然后再执行反半连接，可能更优；这是利用连接语义上确保唯一功能特性进行 DISTINCT 的优化。

③DISTINCT 迁移（ Distinct Placement ）（MySQL不支持）

对连接操作的结果执行 DISTINCT ，可能把 DISTINCT 移到一个子查询中优先进行（有的书籍把这项技术称为“ DISTINCT 配置”）。

4）LIMIT优化

①MySQL的LIMIT优化

a）LIMIT对单表扫描的影响：如果索引扫描可用且花费低于全表扫描，则用索引扫描实现 LIMIT （ LIMIT 取很少量的行，否则优化器更倾向于使用全表扫描）。

b）LIMIT对排序的影响：如果 LIMIT 和 ORDERBY子句协同使用，当取到 LIMIT设定个数的有序元组数后，后续的排序操作将不再进行。

c）LIMIT对去重的影响：如果 LIMIT 和 DISTINCT子句协同使用，当取到 LIMIT设定个数的唯一的元组数后，后续的去重操作将不再进行。

d）LIMIT受分组的影响：如果 LIMIT 和 GROUPBY子句协同使用， GROUPBY 按索引有序计算每个组的总数的过程中，LIMIT 操作不必计数直到下一个分组开始计算。

e）LIMIT 0 ：直接返回空结果集。

f）MySQL 支持对不带 HAVING 子句的 LIMIT 进行优化。

5）集合操作优化

①MySQL 的集合操作优化

a）MySQL 语法：

SELECT …

UNION [ALL |DISTINCT]SELECT …

[UNION [ALL |DISTINCT]SELECT …]

b）查询重写规则： OR 重写并集规则  ---MySQL 不支持。

c）需要引入代价估算的去评估重写后的代价，比较复杂。

6）总结

①常见的启发式规则

a）嵌套连接消除 : 如果都是内连接 , 则可以把表示嵌套关系的括号去掉。

A join (B join C) == A join B join C

b）选择操作下推。

c）投影操作下推 。

②常见的经验规则

a）在索引键上执行排序操作 , 通常利用索引的有序性按序读取数据而不进行排序。

b）选择率低于 10% 时 , 利用索引的效果通常比读表数据的效果好。

c）当表的数据量较少时 , 全表扫描可能优于其它方式 ( 如利用索引的方式 )。

摘自《数据库查询优化器的艺术》一书