当前位置: 代码迷 >> SQL >> SQL优化-逻辑优化-非SPJ优化
  详细解决方案

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

热度:75   发布时间:2016-05-05 10:46:44.0
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)当表的数据量较少时 , 全表扫描可能优于其它方式 ( 如利用索引的方式 )。


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

  相关解决方案