SQL优化--逻辑优化--视图重写与等价谓词重写

1）视图重写

视图的类型：

a）用SPJ格式构造的视图，称为简单视图。

CREATE VIEW v1 AS SELECT x, y, z FROM t;

b）用非SPJ格式构造的视图（带有GROUPBY等操作），称为复杂视图。

CREATE VIEW v2 AS SELECT x, y, z FROM t ORDER BY x;

视图重写：

a）查询语句中出现视图对象

b）查询优化后,视图对象消失

c）消失的视图对象的查询语句, 融合到初始查询语句中

MySQL视图重写准则：

a）MySQL支持对视图进行优化。

b）优化方法是把视图转为对基表的查询，然后进行类似子查询的优化。

c）MySQL通常只能重写简单视图，复杂视图不能重写。

2）等价谓词重写：把逻辑表达式重写成等价的且效率更高的形式。

a）LIKE规则

LIKE谓词，是SQL标准支持的一种模式匹配比较操作；LIKE规则，是对LIKE谓词的等价重写，即改写LIKE谓词为其他等价的谓词，以更好地利用索引进行优化。如：

name LIKE ‘Abc%‘  重写为：  name >=‘Abc‘ AND name <‘Abd‘

应用LIKE规则的好处：转换前针对LIKE谓词，只能进行全表扫描，如果name列上存在索引，则转换后可以进行索引扫描。

LIKE匹配的表达式中，没有通配符（%或_），则与“=”等价，如：

name LIKE ‘Abc‘  重写为：  name =‘Abc‘

如果name列上存在索引，则可以利用索引提高查询效率

b）BETWEEN-AND规则

BETWEEN-AND谓词，是SQL标准支持的一种范围比较操作；

BETWEEN-AND规则，是BETWEEN-AND谓词的等价重写，即改写BETWEEN-AND谓词为其他等价的谓词，以更好地利用索引进行优化。如：

sno BETWEEN 10 AND 20  重写为：  sno>=10 AND sno <=20

BETWEEN-AND规则的好处是：如果sno上建立了索引，则可以用索引扫描代替原来BETWEEN-AND谓词限定的全表扫描，从而提高了查询的效率。

c）IN转换OR规则

IN是只IN操作符操作，不是IN子查询。IN转换OR规则，就是IN谓词的OR等价重写，即改写IN谓词为等价的OR谓词，以更好地利用索引进行优化。将IN谓词等价重写为若干个OR谓词，可能会提高执行效率。如：

age IN (8，12，21)  重写为：  age=8 OR age=12 OR age=21

应用IN转换OR规则后效率是否能够提高，需要看数据库对IN谓词是否只支持全表扫描。如果数据库对IN谓词只支持全表扫描且OR谓词中表的age列上存在索引，则转换后查询效率会提高。

d）IN转换ANY规则

IN转换ANY规则，就是IN谓词的ANY等价重写，即改写IN谓词为等价的ANY谓词。IN可以转换为OR，OR可以转为ANY，所以可以直接把IN转换为ANY。将IN谓词等价重写为ANY谓词，可能会提高执行效率。如：

age IN (8，12，21)  重写为：  age ANY(8, 12, 21)

应用IN转换ANY规则后效率是否能够提高，依赖于数据库对于ANY操作的支持情况。

e）OR转换ANY规则

OR转换ANY规则，就是OR谓词的ANY等价重写，即改写OR谓词为等价的ANY谓词，以更好地利用MIN/MAX操作进行优化。如：

sal>1000 OR 

dno=3 AND (sal>1100 OR sal>base_sal+100) OR

sal>base_sal+200 OR 

sal>base_sal×2

重写为：

dno=3 AND (sal>1100 OR sal>base_sal+100) OR 

sal> ANY (1000,base_sal+200,base_sal×2)

OR转换ANY规则，依赖于数据库对于ANY操作的支持情况。（PostgreSQL V9.2.3和MySQL V5.6.10目前都不支持本条规则。）

f）ALL/ANY转换集函数规则

ALL/ANY转换集函数规则，就是ALL/ANY谓词改写为等价的聚集函数MIN/MAX谓词操作，以更好地利用MIN/MAX操作进行优化。如：

sno>ANY(10, 2*5+3,sqrt(9))  重写为：  sno>sqrt(9)

上面这个ALL/ANY转换集函数规则的示例，有两点需要注意：

①示例中存在“>”和“ANY”，其意是在找出“(10, 2*5+3,sqrt(9))”中的最小值，所以可以重写为“sno>sqrt(9)”。通常，聚集函数MAX()、MIN()等的执行效率一般都比ANY、ALL谓词的执行效率高，因此在这种情况下对其进行重写，可以起到比较好的效果。

②如果有索引存在，求解MAX/MIN的效率更高。

g）NOT规则

NOT谓词的等价重写，如下：

NOT (col_1 !=2)    重写为  col_1=2

NOT (col_1 !=col_2)重写为  col_1=col_2

NOT (col_1 =col_2) 重写为  col_1!=col_2

NOT (col_1 <col_2) 重写为  col_1>=col_2

NOT (col_1 >col_2) 重写为  col_1<=col_2

NOT规则重写的好处：如果col_1上建立了索引，则可以用索引扫描代替原来的全表扫描，从而提高查询的效率。

h）OR重写并集规则

OR条件重写为并集操作，形如下SQL示例：

SELECT * FROM student

WHERE(sex=’f’ AND age>15) OR age>18；

假设所有条件表达式的列上都有索引（即sex列和age列上都存在索引），数据库可能对于示例中的WHERE语句强迫查询优化器使用顺序扫描，因为这个语句要检索的是OR操作的集合。为了能利用索引处理上面的查询，可以将语句改成如下形式：

SELECT * FROM student

WHERE sex=’f’ and age>15

UNION

SELECT * FROM student

WHERE age>18;

改写后的形式，可以分别利用列sex和age上的索引，进行索引扫描，然后再提供执行UNION操作获得最终结果。

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

A