MySQL exists關聯子查詢SQL效能及其低下最佳化之等值子查詢轉換
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
效能及其低下,並且相關的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的執行效能。
透過慢日誌分析報告發現兩條執行緩慢的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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 區分關聯子查詢和非關聯子查詢
- exist-in和關聯子查詢-非關聯子查詢
- MySQL聯結查詢和子查詢MySql
- sql語法相關子查詢與非相關子查詢SQL
- 在關聯子查詢中in與exists的區別
- MySQL 相關子查詢MySql
- MySQL之連線查詢和子查詢MySql
- 關聯查詢子查詢效率簡單比照
- sql子查詢SQL
- Oracle查詢轉換(五)子查詢展開Oracle
- exists與in子查詢優化優化
- 關聯子查詢 Correlated Subqueries
- MySQL子查詢MySql
- MYsql 子查詢MySql
- SQL查詢的:子查詢和多表查詢SQL
- 相關子查詢&非相關子查詢概念
- 子查詢-表子查詢
- mysql求交集:UNION ALL合併查詢,inner join內連線查詢,IN/EXISTS子查詢MySql
- [not] in/exists 與 帶TOP的子查詢
- 關聯子查詢的用處
- SQL--子查詢SQL
- 什麼是SQL 語句中相關子查詢與非相關子查詢SQL
- sql-server不相關子查詢SQLServer
- sql-server相關子查詢SQLServer
- mysql的子查詢MySql
- NOT IN、NOT EXISTS的相關子查詢改用LEFT JOIN--sql2000效能優化SQL優化
- 子查詢中的IN與EXISTS的區別(轉)
- SQL -- 使用聯結還是子查詢?SQL
- StoneDB 子查詢最佳化
- Sql Server系列:子查詢SQLServer
- mysql-分組查詢-子查詢-連線查詢-組合查詢MySql
- 【MySQL】檢視&子查詢MySql
- [MYSQL -14]使用子查詢MySql
- 【MySQL】子查詢之一MySql
- ORACLE_OCP之SQL_子查詢OracleSQL
- MySQL 優化五(關聯查詢子查詢以及 in 的效率問題)(高階篇)MySql優化
- 關於mysql 子查詢中 使用 limitMySqlMIT
- 複雜查詢—子查詢