MySQL優(yōu)化器的SQL重寫規(guī)則

MySQL優(yōu)化器會(huì)根據(jù)一定的規(guī)則對(duì)輸入的SQL在保證含義不變的情況下進(jìn)行SQL的優(yōu)化重寫。

條件簡(jiǎn)化

1.移除不必要的括號(hào)

例如：

((a = 5 AND b =c) OR ((a > c) AND (c < 5)));
--優(yōu)化后
(a = 5 and b =c) OR (a > c AND c < 5)

2.常量傳遞

例如：

a = 5 AND b >a;
--優(yōu)化后
a = 5 AND b >5;  

3.等值傳遞

例如：

a = b and b = c and c = 5;
--優(yōu)化后
a = 5 and b = 5 and c = 5;

4.移除沒(méi)用的條件

例如：

a < 1 and b= b;
-- 優(yōu)化后
a < 1;

5.表達(dá)式計(jì)算

例如：

a = 1 + 1;
--優(yōu)化后
a = 2;

但是對(duì)于復(fù)雜的無(wú)法優(yōu)化，例如：

-a < -8;
max(a) > 8;

6.常量表檢測(cè)

在使用主鍵索引或則唯一性的二級(jí)索引進(jìn)行等值匹配時(shí)候，MySql認(rèn)為查詢耗時(shí)很少，可以忽略。因此MySQL將這種條件的查詢作為一個(gè)常量表來(lái)處理。

優(yōu)化器在分析一個(gè)查詢語(yǔ)句時(shí)，先首先執(zhí)行常量表查詢，然后把查詢中涉及到該表的條件全部替換成常數(shù)，最后再分析其余表的查詢成本。

例如：

select *
from table1 a
         left join table2 b
                   on a.id = b.id
where
  and a.id = 1;

這個(gè)查詢可以使用主鍵和常量值的等值匹配來(lái)查詢table1表，也就是在這個(gè)查詢中table1表相當(dāng)于常量表，在分析對(duì)table2表的查詢成本之前，就會(huì)執(zhí)行對(duì)table1表的查詢，并把查詢中涉及table1表的條件都替換掉：

SELECT table1表記錄的各個(gè)字段的常量值,
       table2.*
FROM table1
         INNER JOIN table2 ON table2.a = 1;

7.外連接消除

內(nèi)連接的驅(qū)動(dòng)表和被驅(qū)動(dòng)表的位置可以相互轉(zhuǎn)換，而外連接的驅(qū)動(dòng)表和被驅(qū)動(dòng)表是固定的。

這就導(dǎo)致內(nèi)連接可能通過(guò)優(yōu)化表的連接順序來(lái)降低整體的查詢成本，而外連接卻無(wú)法優(yōu)化表的連接順序。

如果外連接查詢的列行數(shù)和內(nèi)連接查詢的行數(shù)相同，即查詢內(nèi)容相同，也就是說(shuō)外連接中驅(qū)動(dòng)表沒(méi)有多余的列，那么MySQL就會(huì)將外連接轉(zhuǎn)換為內(nèi)連接來(lái)執(zhí)行SQL，這就是外連接消除。

8.子查詢優(yōu)化

8.1子查詢類型

按返回的結(jié)果集區(qū)分子查詢，子查詢分為以下幾種：

• 標(biāo)量子查詢：那些只返回一個(gè)單一值的子查詢稱之為標(biāo)量子查詢。
• 行子查詢：就是返回一條記錄的子查詢，不過(guò)這條記錄需要包含多個(gè)列（只包含一個(gè)列就成了標(biāo)量子查詢了）。
• 列子查詢：列子查詢自然就是查詢出一個(gè)列的數(shù)據(jù)，不過(guò)這個(gè)列的數(shù)據(jù)需要包含多條記錄（只包含一條記錄就成了標(biāo)量子查詢了）。
• 表子查詢：就是子查詢的結(jié)果既包含很多條記錄，又包含很多個(gè)列。

按與外層查詢關(guān)系來(lái)區(qū)分子查詢,可分為：

• 不相關(guān)子查詢：如果子查詢可以單獨(dú)運(yùn)行出結(jié)果，而不依賴于外層查詢的值，我們就可以把這個(gè)子查詢稱之為不相關(guān)子查詢。
• 相關(guān)子查詢：如果子查詢的執(zhí)行需要依賴于外層查詢的值，我們就可以把這個(gè)子查詢稱之為相關(guān)子查詢，比如：SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2 WHERE n1 = n2);

[NOT] IN/ANY/SOME/ALL子查詢

對(duì)于列子查詢和表子查詢來(lái)說(shuō)，它們的結(jié)果集中包含很多條記錄，這些記錄相當(dāng)于是一個(gè)集合，所以就不能單純的和另外一個(gè)操作數(shù)使用操作符來(lái)組成布爾表達(dá)式了，MySQL通過(guò)下面的語(yǔ)法來(lái)支持某個(gè)操作數(shù)和一個(gè)集合組成一個(gè)布爾表達(dá)式。

• IN或者NOT IN:例如：SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);
• ANY/SOME:例如：SELECT * FROM e1 WHERE m1 > ANY(SELECT m2 FROM e2);等價(jià)于SELECT * FROM e1 WHERE m1 > (SELECT MIN(m2) FROM e2);
• ALL:例如：SELECT * FROM e1 WHERE m1 > ALL(SELECT m2 FROM e2);等價(jià)于SELECT * FROM e1 WHERE m1 > (SELECT MAX(m2) FROM e2);
• EXISTS子查詢：例如SELECT * FROM e1 WHERE EXISTS (SELECT 1 FROM e2);

8.2子查詢優(yōu)化

標(biāo)量子查詢、行子查詢的執(zhí)行方式

• 對(duì)于不相關(guān)標(biāo)量子查詢或者行子查詢來(lái)說(shuō),先單獨(dú)執(zhí)行子查詢，然后將子查詢結(jié)果作為條件執(zhí)行外出查詢，也就是說(shuō)，分別執(zhí)行外層查詢和子查詢，兩個(gè)單表操作。
• 對(duì)于相關(guān)的標(biāo)量子查詢或者行子查詢來(lái)說(shuō)，先從外層查詢?nèi)〕鲆粭l數(shù)據(jù)，然后將某列作為條件去匹配子查詢，如果成立放入結(jié)果集，如果不成立，舍棄。

物化表

• 對(duì)于行子查詢或表子查詢來(lái)說(shuō)，子查詢返回的結(jié)果集不止一條，外層去匹配子查詢結(jié)果集效率極低。那么MySQL采用臨時(shí)表的解決方法，該臨時(shí)表的列就是子查詢結(jié)果集中的列，也就是物化表。
• 物化表建立方式：寫入臨時(shí)表的記錄會(huì)被去重，臨時(shí)表也是個(gè)表，只要為表中記錄的所有列建立主鍵或者唯一索引。一般情況下子查詢結(jié)果集不會(huì)大的離譜，所以會(huì)為它建立基于內(nèi)存的使用Memory存儲(chǔ)引擎的臨時(shí)表，而且會(huì)為該表建立哈希索引。如果子查詢的結(jié)果集非常大，超過(guò)了系統(tǒng)變量tmp_table_size或者max_heap_table_size，臨時(shí)表會(huì)轉(zhuǎn)而使用基于磁盤的存儲(chǔ)引擎來(lái)保存結(jié)果集中的記錄，索引類型也對(duì)應(yīng)轉(zhuǎn)變?yōu)锽+樹索引。

物化表轉(zhuǎn)連接

所謂物化表轉(zhuǎn)連接，就是外層表和物化表做連接查詢，MySQL通過(guò)計(jì)算外層表作為驅(qū)動(dòng)表和物化表作為驅(qū)動(dòng)表進(jìn)行連接查詢的查詢成本，然后使用成本較低的方式進(jìn)行查詢。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。