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

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

條件簡化

1.移除不必要的括號

例如：

((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.移除沒用的條件

例如：

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

5.表達(dá)式計算

例如：

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

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

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

6.常量表檢測

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

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

例如：

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

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

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

7.外連接消除

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

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

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

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

8.1子查詢類型

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

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

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

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

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

對于列子查詢和表子查詢來說，它們的結(jié)果集中包含很多條記錄，這些記錄相當(dāng)于是一個集合，所以就不能單純的和另外一個操作數(shù)使用操作符來組成布爾表達(dá)式了，MySQL通過下面的語法來支持某個操作數(shù)和一個集合組成一個布爾表達(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);等價于SELECT * FROM e1 WHERE m1 > (SELECT MIN(m2) FROM e2);
• ALL:例如：SELECT * FROM e1 WHERE m1 > ALL(SELECT m2 FROM e2);等價于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í)行方式

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

物化表

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

物化表轉(zhuǎn)連接

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

總結(jié)

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

最新評論