本周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}
這一點的優化相對比較雞肋，都在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.
完。
