MySQL exists關聯子查詢SQL效能及其低下最佳化之等值子查詢轉換

清風艾艾發表於2018-04-20
    2018年4月20日,移動某平臺江蘇某業務系統發起SQL調優請求,在本次MySQL除錯過程中遇到了exists關聯子查詢導致的sql
效能及其低下,並且相關的SQL語句執行速度及其不穩定忽快忽慢;透過重寫相關SQL的exists部分修改成等值子查詢,相關sql的
效能得到極大提高,並且執行速度比較穩定。本次SQL最佳化過程記錄如下:
    1、環境資訊:
    作業系統版本:CENTOS 7.2.1151
    資料庫版本:5.7.17-log
    2、檢視MySQL慢日誌相關資訊
mysql>show variables like '%slow_query_%';
-+-------------------------------------------+--------------------------------------------------------------------------------+
|        Variable_name                        |                                    Value                                                   |
-+-------------------------------------------+--------------------------------------------------------------------------------+
|slow_query_log                               | ON                                                                                        |
|slow_query_log_file                         |/data/mysql/db/elog/slow.log                                                    |
-+-------------------------------------------+--------------------------------------------------------------------------------+
    2、使用pt工具分析MySQL的慢日誌
cd  /data/mysql/db/elog
pt-query-digest slow.log  --since '2018-04-13 00:00:00'  --until '2018-04-19 00:00:00' >>slow_report_log
    3、檢視慢日誌分析報告slow_report_log

透過慢日誌分析報告發現兩條執行緩慢的SQL語句Query_ID分別是0xEA33702BDD78E0BA、0x56B6B418ADAAB135,其中
0xEA33702BDD78E0BA的響應時間佔據了整個資料庫時間的97.1%,是重點最佳化物件。
    4、明確需要最佳化的物件
--第一條需要最佳化的重點物件0xEA33702BDD78E0BA

--SQL執行計劃

--SQL執行統計資訊概要
--第二條要最佳化的SQL物件
0x56B6B418ADAAB135

--SQL執行計劃

--SQL執行統計資訊概要

    5、首先看第一條SQL語句,自己的最佳化思路,確定SQL語句中慢的具體位置
--對第一條SQL語句拆分,以union為界分兩部分
--0xEA33702BDD78E0BA第一部分:0xEA33702BDD78E0BA_part1

0xEA33702BDD78E0BA_part1部分SQL語句查詢只有13條記錄,執行速度非常快0.00秒完成

--0xEA33702BDD78E0BA第二部分:0xEA33702BDD78E0BA_part2

0xEA33702BDD78E0BA_part2部分SQL(該部分查詢正常出結果是103條資料)語句執行超過10s,超時退出

    6、接下來著重關注0xEA33702BDD78E0BA第二部分:0xEA33702BDD78E0BA_part2

觀察SQL語句發現有EXISTS關聯子查詢,我的理解是,原先sql被最佳化器改寫,主查詢執行多次,每次主查詢sql都不同,
都會發起物理讀盤掃表。於是考慮將其改為等值連線子查詢:

--SQL改寫後(查詢結果集與修改前一致):兩個等值查詢表結果集在記憶體,接下來是最終結果集匹配和過濾,執行速度只有0.01秒

7、修改後的完整SQL如下

--修改後的sql執行速度,有原先的超過10s超時中斷退出降低到0.00秒以下。

--SQL改寫後的執行計劃


8、第二條SQL語句採用第一條SQL語句最佳化方法,將exists關聯子查詢改寫為表關聯的等值子查詢
--改寫前的執行效率

--改寫後的執行效率

--改寫後的SQL執行計劃


9、總結
透過觀察SQL改寫前後的執行計劃,可以發現改寫後的SQL 執行中間結果集均已快取到記憶體(Using join buffer(Block Nested Loop)),記憶體結果集匹配過濾降低了表的物理IO,從而提高了SQL的執行效能。



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29357786/viewspace-2153133/,如需轉載,請註明出處,否則將追究法律責任。

相關文章