一些重寫規(guī)則

• 移除沒用的括號
• 常量傳遞(確定值換成對應常數(shù))
• 移除明顯沒用的條件
• 表達式計算
• Having移到where
• 常量表

唯一索引的等值查詢,被任務查詢的時間少到可忽略,

這里table1就是常量表, 假設 table1.primary_key = 1 這行是 1,a語句就會被優(yōu)化為SELECT * FROM 1,a INNER JOIN table2 ON a= table2.column2

• 外連接和內(nèi)連接效果一樣轉(zhuǎn)內(nèi)連接

子查詢

類型按結果分:

• 標量子查詢 : 子查詢子返回一個值
• 行子查詢:
  
• 列子查詢:
  
• 表子查詢
  

按與外層關系分:

相關查詢

子查詢結果集的布爾表達式

• IN

• ANY/SOME

  
  
  
  

  等價于

  
  
• ALL (同意可換成極值)

• EXISTS

  
  
  如果子查詢結果集有記錄就是 ture

IN子查詢優(yōu)化

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

物化表

SELECT * FROM s1 WHERE key1
SELECT common_field FROM s2 WHERE key3 = 'a'
這2個查的是 s1 s2 2張表, 不相關的查詢
內(nèi)層查詢的結果集太多, 可能內(nèi)存發(fā)不下,而且 in(這里面太多) 效率太低,
所以, 內(nèi)層查詢的結果會被放到臨時表里面, 這個過程會去重(瘦身), 這個過程叫物化

• 一般是 基于內(nèi)存的 Memory表, hash索引
• 太大了(超過系統(tǒng)變量設置的大小) 只能用基于磁盤的存儲引擎, 索引的B+

semi-join 半連接

SELECT s1.* FROM s1 SEMI JOIN s2 ON s1.key1 = s2.common_field WHERE key3 = 'a';
這個語句不能執(zhí)行只做示意
半連接:只關心在s2表中是否存在與之匹配的記錄是否存在脓鹃，而不關心具體有多少條記錄與之匹配

執(zhí)行半連接如何消除重復值:

• Table pullout （子查詢中的表上拉）:
  比如
  SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a');因為key2在表2里面是唯一的, 可以變成
  SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2WHERE s2.key3 = 'a';
• DuplicateWeedout execution strategy （重復值消除）
  會建立一個臨時表, 臨時表只有一個列 id, 是主鍵, 把s1查詢出來的結果的id插入這個臨時表, 如果插成功的, 就是不重復的, 記入結果集
• LooseScan execution strategy （松散索引掃描）
  SELECT * FROM s1
  WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');
  可以轉(zhuǎn)換成
  SELECT * FROM s1 INNER JOIN s2 ON s1.key3 = s2.key1 WHERE s2.key1 > 'a' AND s2.key1 < 'b'
  以s2為驅(qū)動表, 先走s2的 key1上的索引 找到 s2.key1 > 'a' AND s2.key1 < 'b', 然后 相同的key1 只取第一個 比如
  

in 子查詢 轉(zhuǎn)成 EXISTS

SELECT
    *
FROM
    s1
WHERE
    key1 IN (
        SELECT
            key3
        FROM
            s2
        WHERE
            s1.common_field = s2.common_field
    )
OR key2 > 1000;

SELECT
    *
FROM
    s1
WHERE
    EXISTS (
        SELECT
            1
        FROM
            s2
        WHERE
            s1.common_field = s2.common_field
        AND s2.key3 = s1.key1// 可以用到s2.key3上的索引了
    )
OR key2 > 1000;

如果IN子查詢不滿足轉(zhuǎn)換為semi-join的條件把将，又不能轉(zhuǎn)換為物化表或者轉(zhuǎn)換為物化表的成本太大，那么它就會被轉(zhuǎn)換為EXISTS查詢。

派生表消除

SELECT
    *
FROM
    (SELECT * FROM s1 WHERE key1 = 'a') AS derived_s1// 派生表
INNER JOIN s2 ON derived_s1.key1 = s2.key1
WHERE
    s2.key2 = 1;

SELECT
    *
FROM
    s1
INNER JOIN s2 ON s1.key1 = s2.key1
WHERE
    s1.key1 = 'a'
AND s2.key2 = 1;