MySQL 優化器引數derived_merge導致多表關聯SQL效能及其低下
最近,MySQL維護中,遇到一個問題,通條SQL語句,在mysql 5.6的測試環境中執行速度不到1秒,但是在
mysql 5.7生產環境中執行卻要近5分鐘,mysql 5.7中同樣的資料庫同樣的資料量,更新完表的統計資訊後執行
速度還是要2分鐘。本次問題的處理與sql語句本身沒有關係,只跟mysql資料庫自身的優化器引數相關,下面是
問題的分析排查過程,問題分析中的sql語句不需要顯示,可以明確sql是多表join連線並且業務不允許更改。
1、首先,檢視測試環境和生產環境中,mysql的sql語句的執行計劃
--測試環境,執行計劃只需顯示區域性能說明問題即可
--生產環境,執行計劃只需顯示區域性能說明問題即可
2、從測試與生產環境sql語句的對比可以明顯發現,sql的執行計劃不一致,後續排查中發現
a、測試環境中sql涉及的表和索引的統計資訊都是當天最新的,而生產環境中的相關表和索引的統計資訊比較陳舊
b、測試環境 mysql大版本為5.6,生產環境mysql大版本為5.7
3、問題處理
a、由於sql執行計劃不一致,且生產環境統計資訊比較舊,重新收集生產環境表的統計資訊,收集後sql的執行速度沒有
提高,相比測試依然很慢。
b、關注測試環境執行計劃derived和<auto_key>,該特性與mysql 的引數derived_merge相關,檢視該引數的設定情況
--測試環境
MySQL [(none)]> show global variables like '%switch%';
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,....... |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [(none)]>
--生產環境,為了方便說明問題,省略了多餘的引數顯示
mysql> show variables like '%switch%';
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | ......, derived_merge=on |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
c、根據官方說明引數derived_merge是在mysql5.7版本中引入的,作用就是對join (select)表連線合並,本次問題sql有
大量的join (select),導致sql執行結果集合並,sql執行速度及其緩慢。
d、通過session級別臨時取消該引數,觀察sql執行計劃,發現sql執行計劃正常,且sql執行速度不到1秒與測試環境相近
--取消優化器derived_merge引數
mysql> set optimizer_switch="derived_merge=off";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8980
Current database: mysql
Query OK, 0 rows affected (0.02 sec)
mysql>
--觀察問題sql執行計劃,此時生產環境執行計劃與測試環境相同
--問題sql執行速度由原先的近5分鐘到現在的1秒左右
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29357786/viewspace-2646294/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL exists關聯子查詢SQL效能及其低下最佳化之等值子查詢轉換MySql
- SQL優化之多表關聯查詢-案例一SQL優化
- SQL Server SQL效能優化之引數化SQLServer優化
- 【SQL 效能優化】引數設定SQL優化
- Mysql 效能優化--基礎引數MySql優化
- MySQL 效能優化之快取引數優化MySql優化快取
- 【SQL優化器】初始化引數SQL優化
- 【MySQL】 效能優化之 延遲關聯MySql優化
- MySQL 效能優化之SQL優化MySql優化
- MySQL 資料庫效能優化之快取引數優化MySql資料庫優化快取
- MySQL多表關聯查詢MySql
- MySQL 多表關聯刪除MySql
- MySQL效能相關引數MySql
- 優化由直方圖資訊導致的sql效能問題優化直方圖SQL
- MySQL資料庫效能優化之快取引數優化(轉)MySql資料庫優化快取
- MySQL引數配置優化MySql優化
- MySQL配置檔案mysql.ini引數詳解、MySQL效能優化MySql優化
- MySQL關聯多表更新的操作MySql
- 多表關聯更新(mysql,oracle,postgreSQL)MySqlOracle
- SQL優化--多表連線和走索引的關係SQL優化索引
- mysql關聯查詢優化MySql優化
- mysql中的多表關聯查詢MySql
- MySQL為什麼不要多表關聯?MySql
- Oracle查詢優化器的相關引數Oracle優化
- Elasticsearch效能優化引數註解Elasticsearch優化
- MySQL的SQL效能優化總結MySql優化
- MYSQL order by排序導致效率低小優化MySql排序優化
- Mysql優化系列(1)--Innodb重要引數優化MySql優化
- Oracle\MS SQL Server Update多表關聯更新OracleSQLServer
- sql 多表關聯刪除表資料SQL
- mysqldump缺失-q引數導致mysql被oom幹掉MySqlOOM
- Oracle優化案例-join列索引缺失導致的sql效能問題(二十六)Oracle優化索引SQL
- Mysql 效能最佳化--基礎引數MySql
- Mysql表關聯欄位未建索引導致查詢慢,優化後查詢效率顯著提升MySql索引優化
- 【調優篇基本原理】優化器相關引數配置優化
- 多表連線SQL優化如何處理SQL優化
- SQL效能第1篇:關係優化SQL優化
- 資料庫效能優化-索引與sql相關優化資料庫優化索引SQL