語句優化

即優化器利用自身的優化器來對我們寫的SQL進行優化，然後再將其放入InnoDB引擎中執行。

條件簡化

移除不必要的括弧

select * from x where ((a = 5));

上面的括弧很沒必要，優化器就會直接去掉。

select * from x where a = 5;

等值傳遞

select * from x where b = a and a = 5;

同樣的，雖然是兩列比較，但是a的值只有一個，所以可以優化

select * from x where b = 5 and a = 5;

常量傳遞

select * from x where a = 5 and b > a;

可以優化為

select * from x where a = 5 and b > 5;

移除沒用的條件

select * from x where a < 5 and b > 10 and b > a;

當前兩個條件發生時，最後一個條件必然發生，所以可以優化

select * from x where a < 5 and b > 10;

表達式計算

select * from x where -a > -5;

優化器不會對其進行優化，而且這個壞處很多就是不能使用索引了，所以我們儘量讓列單獨出現，而不是在表達式計算中。

常量表檢測

當表中只有一兩條數據，或則使用主鍵或唯一列的索引等值查詢的話就會被MySQL優化器視為常量表，直接將SQL語句優化成常量。

select * from table1 join table2 on table1.col1 = table2.col2 where table1 = 'a';

select table1的列都作為常量，table2.* from table2 where table1的常量col1 = table2.col2;

外連接消除

外連接呢，首先連接的順序是固定的，故驅動表和被驅動表是固定不變的。所以是不能像內連接一樣交換驅動表的。

但是呢，有一種情況

select * from table1 left join table2 on table1.col1 = table2.col2 where table2.col2 is not null；	

我們設定了table2的列是非空的，這意味著什麼，當table1匹配不到時設置table2列為null，但是卻不滿足搜索條件被過濾掉，所以左連接匹配失敗null相當於是失效的。這個語句和內連接是沒有區別的，直接將其優化為內連接即可。

所以當在外連接出現時，但是被驅動表拒絕空值時，此時外連接和內連接是可以互相轉換的，而內連接可以通過交換驅動表來優化SQL查詢成本。

子查詢優化

子查詢分類

• 標量子查詢
• 列子查詢
• 行子查詢
• 表子查詢

再分

• 相關子查詢
• 不相關子查詢

標量子查詢

不相關標量子查詢

select * from x where key1 = (select y.key1 from y where y.primarykey = 1);

對於不相關的標量子查詢來說，就是先執行子查詢，然後在對外部查詢進行查詢。

相關子查詢

select * from s1 where key1 = (select common_field from s2 where s1.key3 = s2.key3 limit 1);

對於相關的標量子查詢

1. 首先取出外部的每條滿足自身搜索條件的行，然後傳入子查詢對應列的值。
2. 計運算元查詢的結果
3. 在判斷外部key1對於這個子查詢給的結果是否滿足條件，滿足加入結果行。
4. 繼續迴圈回1，直到遍歷完所有外層表的行。

其實和連接的流程差不多。

優化器對於標量的子查詢並不需要什麼優化，因為對於標量的子查詢來說，數據量還算很小的了。

IN子查詢優化

select * from x where key1 in (select key3 from y);

對於上述不相關的IN查詢來說，如果IN子查詢的參數少的話，還可以試著載入到記憶體，然後讓外層查詢對很多的條件進行比較。

但是如果子查詢數據量一旦大了起來，記憶體無法全部載入完，或導致外層查詢需要比較的參數太多，外層記錄需要對於過多條件進行比較，導致索引無法使用，因為每一次都要使用索引，每次都要比較，還不如直接全表掃描。最後導致性能很低。

物化表優化

MySQL對這種in參數過多時，不會將子查詢在作為外部的參數，而是直接創建一個臨時表來存儲子查詢的結果。

1. 將臨時表的列為子查詢結果的列，並對其進行去重。
2. 臨時表經過去重通常不會太大，創建的是Memory的存儲引擎的臨時表，並對其創建哈希索引。

子查詢轉物化表materialized_table後，我們還能將物化表和外層查詢轉換為連接的方式。

select x.* from x inner join materialized_table m on key1 = m.key3;

然後我們就可以用之前計算成本的知識來計算那個作為驅動表更合適了。

只有不相關子查詢才能轉換為物化表

semi-join優化

像上述結果一樣，我們將查詢結果轉換為物化表，然後我們在把物化表轉換為連接的方式。

我們為什麼不能直接將子查詢轉換為連接的方式呢？這就是semi-join優化。

我們可以試試將其轉換為如下語句

select x.* from x join y on key1 = key3;

三種情況

• 被驅動表y的行不滿足連接條件的，不能加入結果集。
• 被驅動表y一個key3滿足和驅動表x的key1相等且y表key3有且僅有一條，有一條記錄加入結果集。
• 被驅動表y有key3滿足連接條件但是一個key3有很多條記錄，就會有多條記錄加入結果集。

能滿足的條件就是y表的key3是主鍵或唯一列，不然就會出現多條的情況，這條語句就不等於原語句了。

但是此時semi join半連接概念的出現，在半連接的情況下，對於驅動表x來說，我們只關心被驅動表y是否有記錄能夠滿足連接條件的，而不關心被驅動表y有幾條能匹配，最後結果集只保存驅動表x的記錄。

實現半連接semi join的方法。PS：semi join半連接只是一個概念。

• Table pullout （子查詢中表上拉）
  • 當子查詢的查詢列 ( 即select 的列 ) 是主鍵或唯一列，就是我們上面說的直接join 出來即可，因為不會出現多條的情況
• DuplicateWeedout execution strategy （重覆值消除策略）
  • 我們不是提到上述的我們自己改為join的方法會出現重覆的情況嗎，因為被驅動表的重覆導致驅動表的重覆。
  • 我們就直接創建一個臨時表，把s1連接的結果記錄id (是數據行的id可以這麼理解把) 放入臨時表中，當該數據行再次被加入時臨時表就會拋出主鍵重覆的異常，就不會加入重覆行了。
• LooseScan execution strategy （鬆散索引掃描）
  • 當子查詢列key1有子查詢表的索引，這樣我們就可以通過索引訪問，對於每個值，只訪問一行，重覆值不再訪問，這樣來防止出現多條記錄。
• Semi-join Materialization execution strategy (物化表半連接)
  • 將不相關子查詢通過物化表的方式物化為臨時表，沒有重覆行的情形，我們可以直接轉換為連接。
• FirstMatch execution strategy （首次匹配）
  • 取外連接的一條記錄，然後和子查詢進行一條一條的比較。最原始的方法

semi join使用條件：

• 該子查詢必須是和IN語句組成的布爾表達式，並且在外層的Where和on子句中出現。
• 外層的搜索條件必須是用and 和in子查詢連接的。
• 子查詢是單一的查詢，不能union
• 子查詢不能包含group by、having、聚集函數
• ...

EXISTS優化

如果不能使用semi join和物化表，我們還可以將in的語句改造成EXISTS語句。

將上述改造為如下語句。

select * from x where exists (select 1 from y where key3 = x.key1)

如果被驅動表key3有索引，就可以使用索引了啊 o(￣▽￣)ｄ。

這個算是下下策了。