本週SQL優化實戰分享

分享一下本週SQL優化的兩個場景。
如果能對讀者有一定的啟發，共同探討，不勝榮幸。

版本資訊：mysql,5.7.19
引擎: innodb

場景1

我們有一張常口表，裡面的資料由各種資料來源合併而來，所以人員可能有多個手機號其中還包括座機號。這點在這篇文章裡也分享過。https://juejin.cn/post/7234355976458518586
現在人員詳情頁面需展示同手機號的人員列表，同手機號是包含，而非等同關係。

在人員列表裡手機號頁面有做展示，那麼點選跳轉人員詳情的時候，是可以把手機號通過URL帶過來的，但前端說引數過多，不好控制，所以只傳遞了人員ID引數。

所以後端查詢的時候先得通過主鍵ID把手機號查出來。之所以不一次性通過join帶出手機號再關聯同手機號人員，是關聯與被關聯人員手機號都可能存在多個。

select * 還是select 指定欄位

原通過主鍵查詢手機號的SQL，是直接用的mybatis生成器自動生成的SQL。

<select id="selectPhoneByPrimaryKey" parameterType="java.lang.String" resultMap="BaseResultMap">
    select
    phone
    from t_person_info
    where ID = #{id,jdbcType=VARCHAR}
  </select>

Base_Column_List可想而知是全部欄位，類似於select *,這本身沒什麼，但其中有一部份欄位長度在幾百，全部加起來也算是個大欄位，全部提取對效率還是有一定的影響，所以改為select phone 查詢手機一個欄位。

select
    phone
    from t_person_info
    where ID = #{id,jdbcType=VARCHAR}

更追求極致一點，可以新增一個id和phone的覆蓋索引，避免回表。

這一點的優化相對比較雞肋，都在1-2ms之間看不出明顯差別，但把limit放大的時候，還是能看出差距。

表資料70萬左右。

select * form table limit 10000

select phone form table limit 10000

174ms vs 7ms

確實是聊勝於無。
但是到底是select * 還是select 指定欄位，確實還是存在著一些爭議。

一般情況下，表欄位少，且不存在大欄位，用select * 確實能減少許多麻煩，加減欄位不用改sql，多個查詢子功能可以共用等。
而且，頁面查詢多是分頁，不太可能一下子查詢10000條這種情況。

佔用記憶體，不必要的IO，增加網路負擔，拒絕覆蓋索引，確實也是select *的問題。

我覺得需要根據具體情況，自行判斷，沒必要太過教條。

全文檢索

拿到手機號以後，根據手機號去查詢關聯人員。
因為是包含關係，所以同事一開始用的是like模糊匹配。

 select p.id, p.id as pid,p.name,p.idcard,p.phone,count( w.EVENT_NO ) AS count 
      from t_person_info p 
      left join t_other w on w.pid = p.ID
        where
            <foreach collection="phones" item="phone" separator="or" open="(" close=")">
                p.phone like concat("%",#{phone},"%")
            </foreach>
         and p.id != #{id}
        group by p.id

這裡的!=有可能會導致索引失效，這時候可以在sql去掉，然後在程式碼中過濾掉當前人員。

因為where條件中有 p.id != #{id},執行計劃倒是從從ALL上升到了range。耗時1.5秒。

將phone加上全文索引。 where 條件改為

match(p.phone) against (#{phones} IN boolean MODE) and p.id != #{id}

每個手機號需要全匹配，所以這裡使用布林模式，
因為手機號有多個，需要做到or，
又因為涉及到座機號，其中帶的-可能會被mysql識別為邏輯運運算元。

具體參照我寫的這篇文章 https://juejin.cn/post/7234355976458518586

布林模式的邏輯運運算元

+
select * from t_user where match(phone) AGAINST('a +b' in boolean mode)
其中 + 會被識別成邏輯運運算元，而不是將a +b作為一個整體，以下同理。
'a +b' 指'a'和'b'必須同時出現才滿足搜尋條件。
-
select * from t_user where match(phone) AGAINST('0797 -12345' in boolean mode)
0797 -12345指0797必須包含，但不包含12345才能滿足搜尋條件。
以下查詢排除了包含0797-12345的記錄。

注意-前後空格 0797 -12345才表示包含0797 同時不包含12345.
0797-12345等於0797 - 12345，它並不等於0797 -12345。
有圖為證：
> <
提高/降低該條匹配資料的權重值。不管使用>還是 <，其權重值均大於沒使用其中任何一個的。
select * from t_user where match(phone) AGAINST('0797(>94649 <12345)' in boolean mode)
表示匹配0797,同時包含94649的列往前排，包含12345的往後排
select * from t_user where match(phone) AGAINST('a > b' in NATURAL LANGUAGE mode)
()
相當於表示式分組，參考上一個例子。
*
萬用字元，只能在字串後面使用
"
完全匹配，被雙引號包起來的單詞必須整個被匹配。
select * from t_user where match(phone) AGAINST('"0797-1789"' in boolean mode)
"0797-1789"中不可再分。其它包含0797-1234等記錄就不再匹配。
空格表示 or

這裡使用6，7來解決上述的兩種問題。
如下SQL，與以下4個手機號其中一個全區配的人員都將被篩選出來。

#{phone}引數應為"135****6" "136****9" "1387****2" "0791-123"格式。

耗時從1.5秒降到了2毫秒。

場景2

還是常口表，列表查詢。

排序

每個使用者呢會關聯一些事件，無需理會什麼是事件，反正這張表中的每條記錄與事件表形成一對多的關聯關係。
事件實時進入。然後再使用者列表展示的時候需要根據關聯的事件數來進行排序。

實時join關聯事件表，耗時4.9秒。
sql執行計劃 extra為 Using temporary; Using filesort 產生了臨時表和IO檔案排序。當然快不起來。

這還是在沒有查詢條件，以及沒有深度分頁的情況下。

那麼很明顯，需要在使用者表建一個冗餘欄位，儲存使用者所關聯的事件數，再對這個欄位建立索引。

但這會犧牲一定的實時性。
以及需要定時任務去統計使用者的關聯事件數。

然後需要跟產品溝通，因為我們的產品是2B的，還需要跟客戶進行溝通。

結合我們的業務場景，經過我們的努力溝通，客戶認為犧牲適當的實時性，換來頁面的響應效率，是值得的。

然後耗時降到了3毫秒。

一旦 where having order by 裡的欄位是通過max,min,count等計算出來的虛擬欄位，那麼肯定會產生 Using temporary; Using filesort 臨時表和IO檔案排序。
要想辦法消滅，不管從業務還是技術上。

適當的建立冗餘欄位，或者寬表。

但阿里巴巴java開發手冊，禁止3張表以上的關聯，畢竟只是比較理想的狀態。

幸福的公司都是相似的；不幸的公司我看也有相似不幸。
不外乎難搞的產品，多變的客戶，睿(s)智(13)的老闆。

深度分頁

上面小節同樣的sql,首頁查詢只需耗時2ms，但是到了700000以後，耗時達到了2.6秒。

這就是著名的mysql深度分頁的問題。
通過執行計劃，可以明顯的看出，mysql會將前 700015條資料取出來，然後丟掉前700000條，只取後15條資料。
前面讀取的700000條資料是不必要耗時操作。

解決深度分頁的方式有幾種。看具體情況，沒有通用的辦法。

利用覆蓋索引

或者叫利用不回表。
這裡為了便利，用主鍵索引id來演示，innodb下，主鍵索引為聚簇索引，本身就是回表啦，相當於普通索引省掉了回表操作。

如此查詢只需200毫秒左右。

但是，這裡不合適把需要展示的欄位全部建成一個覆蓋索引。

利用覆蓋索引延遲關聯

先通過覆蓋索引把id拿到，再把這15條資料去關聯一次拿到其它欄位不就好了嗎？

select p.id ,p.name,p.idcard,p.phone
from t_person_info p
inner join (select id from t_person_info order by EVENTCOUNT desc limit 700000,15) p2 on p.id = p2.id

如此同樣只需要200毫秒左右。

其它方式

其它方式，通過記錄上次的位置，通過子查詢，都只適用於id為自增主鍵的情況。

不適用我的這個業務場景。

類似於這樣的SQL

select id ,name,idcard,phone,EVENTCOUNT from t_person_info where id <=(select id from t_person_info order by EVENTCOUNT limit 700000, 1) limit 15;

由於歷史友商等原因，我們的資料ID有部份是UUID，它是不連續的，且人員關聯事件數EVENTCOUNT也不連續，大量的人員集中在某一個數量上，這都使得此種方式不可取。

分頁外掛

在做列表展示時肯定需要分頁，分頁就需要查詢總數。
分頁外掛pagehelper預設會生成一個查詢總數的方法。

假如mapper查詢方法為selectList(),那麼查詢總數的方法名為selectList_COUNT()。
對應的SQL為SELECT count(0) FROM 原sql

在一些比較比較簡單的SQL的時候，分頁的SQL還是會進行重寫，比較去掉多餘的select欄位，不必要的排序等。

但當SQL比較複雜的時候，那就是直接在原SQL上包一層select count(0)。

這個時候我們就可以自已去實現這個selectList_COUNT()這個方法，讓它執行效率更高的自定義SQL.

完。