关系型数据库工作原理-SQL重写讲解

本文翻译了如下章节, 介绍数据库的SQL重写器组件：

Query rewriter -查询重写器<喎? f/ware/vc/”=”” target=”_blank” class=”keylink”>vc3Ryb25nPjwvaDI+DQo8cD7U2tXiuPa0psDtu7e92qOsv6q3osjL1LHQtLXEU1FM0+++5LvhsbvXqru7zqrSu9bWREJNU8Tasr+1xLHtyr63vcq9oaPW2NC0U1FM09DI/bj2xL+1xKO6PC9wPg0KPHA+ttRTUUzT777k1/bM4cew08W7ryjS69Xfo7rP4LWx09pDKyvA78PmtcTUpLHg0uspoaMgyKWz/VNRTNbQyN/T4LXEstnX96GjILDv1vrTxbuvxvfV0rW91+66w7XE08W7r7e9sLihozwvcD4NCjxwPtbY0LTG97vhttRTUUzT777ksLTSu7aotcS55tTy1/a0psDtoaPI57n7U1FM0+++5Lf7us/Es8z1uebU8rXEzPW8/qOsuebU8r7Nu+HWtNDQo6zIu7rzU1FMsbvW2NC0oaPPwsPmysfSu9CpuebU8rXEwP3X06O6PC9wPg0KPHA+VmlldyBtZXJnaW5nKMrTzbzM5ru7KaO6yOe5+9TaU1FM1tDKudPDwcvK0828o6zK0828vauxu9PDytPNvFNRTLT6wuvM5ru7KNLr1d+jus/gtbHT2kMrK9bQtcS66szmu7spoaM8L3A+DQo8cD5TdWJxdWVyeSBmbGF0dGVuaW5nKNfTsunRr9W5v6opOtPQ19Oy6dGvtcRTUUzT777kyse63MTR1/bTxbuvtcSjrLjE0LTG972rytTNvNDeuMSy6dGv0+++5NLUyKWz/dfTsunRr6GjPC9wPg0KPHA+wP3I58/Cw+a1xFNRTNPvvuSjujwvcD4NCjxwcmUgY2xhc3M9″brush:sql;”> SELECT PERSON.* FROM PERSON WHERE PERSON.person_key IN (SELECT MAILS.person_key FROM MAILS WHERE MAILS.mail LIKE ‘christophe%’);

将被重写成这样：

SELECT PERSON.*
FROM PERSON, MAILS
WHERE PERSON.person_key = MAILS.person_key
and MAILS.mail LIKE 'christophe%';

Removal of unnecessary operators(去除无用的操作):例如将DISTINCT应用到一个已定义为UNIQUE的字段上，DISTINCT将被去掉。

Redundant join elimination(去除多余的连接条件):如果在SQL中有两个相同的连接条件(可能是视图已经隐含了连接条件)，那么多余的连接条件将去掉。

Constant arithmetic evaluation(常量运算): 如果在SQL中使用了算术运算，重写器将提前计算好结果。例如：WHERE AGE > 10+2将被转换为AGE > 12；TODATE(“some date”)将被转换为对应Date的时间戳格式。

(Advanced) Partition Pruning（分区裁决）：如果使用了分区表，重写器将提前找出将使用哪些分区。

(Advanced) Materialized view rewrite(物化视图重写):

如果有一个物化视图与查询语句匹配，且该物化视图数据是最新的。重写器会修改SQL以访问物化视图来代替原始表。

(Advanced) Custom rules(自定义规则): 如果你有定义规则来重写查询SQL,重写器将会执行这些规则。

(Advanced) Olap transformations（多维分析转化）: analytical/windowing functions, star joins, rollup … are also transformed (but I’m not sure if it’s done by the rewriter or the optimizer, since both processes are very close it must depends on the database).

重写后的SQL语句将被送到查询优化器做进一步处理。