MySQL之SQL語句優化

大隊長11發表於2022-05-25
MySql優化

語句優化

即優化器利用自身的優化器來對我們寫的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(￣▽￣)ｄ。

這個算是下下策了。

相關文章