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

清風艾艾發表於2018-04-20
原文網址 : http://blog.itpub.net/29357786/viewspace-2153133/
MySql
    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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章