記一次MySQL遷移並從MySQL5.6升級到5.7後查詢慢了幾十倍的問題

起因

因為生產環境數據量越來越大，客戶越來越多，項目功能也越來越多，項目本身也越來越多，導致之前的服務器內存、硬盤都已經漸漸的不夠用了，當時出現了2種解決方案，增加服務器配置和新購服務器，但是就算是新增硬盤，也需要對數據庫進行遷移，所以就採用了新購服務器的方案，並且因為之前是高效雲盤，出現過IO佔滿的情況，所以對於新購的服務器採用了SSD硬盤，理論上速度會飛起來了，實際上我在新服務器上安裝MySQL5.7，因為聽說MySQL5.7性能提升了N倍，還支持json（雖然對我們沒什麼用），但是畢竟MySQL8已經出來了，說明MySQL5.7也肯定穩定好了。so，就找了個夜深人靜的晚上偷偷的吧數據庫遷移過來了，然後開啟慢查詢日誌，限制為5秒，於是開始各種測試，然後查看了一下慢查詢日誌，一堆慢查詢日誌，所以有了這篇文章，為什麼會出現這麼多慢查詢，以及如何解決

開始排查

將慢查詢SQL拿出來，發現主要的慢查詢SQL都是鏈表查詢的語句，也就是說查詢語句本身非常複雜，所以就把SQL語句放回之前的數據庫執行，發現之前的數據庫都是不到1秒就查詢出來了，而在新的數據庫上最慢能達到140多秒，這明顯就不正常了，畢竟新的MySQL服務器無論是CPU、內存、還是硬盤相對於以前的MySQL服務器來說，都是好了不止一星半點，如果說性能差不多還能接受，但是一下子慢了這麼多，就明顯是有異常了，於是開始挨個排查

排查第一步：配置問題

首先2邊服務器的配置文件是一模一樣的，因為就是從舊服務器吧配置文件複製過來的，但是因為MySQL的版本不一致，所以懷疑是因為新版有些配置不一樣，所以導致的，於是百度了MySQL5.7的優化配置，同時根據機器的內存CPU等硬件情況調整了部分配置，重啟MySQL，執行語句，效果不明顯，平均下來能快1秒左右，但是這1秒本身也可以認為是查詢波動，所以不是配置的問題

排查第二步：硬件問題

首先CPU和內存應該不會有什麼問題，唯一可能性就是SSD硬盤的問題，因為之前看到過因為SSD硬盤導致K,V鍵值存儲性能低下，跟機械硬盤完全不在同一個等級上，所以懷疑SSD是否需要開啟什麼特別的配置才行，於是百度，發現雖然有針對SSD的優化配置，但是也沒有因為用了SSD導致速度非常慢的情況，於是針對SSD進行了優化配置，重啟MySQL，執行語句，效果微乎其微

排查第三步：語句問題

也是實在沒辦法了，才想到這個問題，但是我自己都覺得不大可能，而且語句本身也優化的差不多了，小結果集驅動大結果集，索引根據where條件創建等。畢竟就算MySQL升級也不會說改變SQL語法之類的，最多就是在優化SQL的進行了一些特殊處理，所以先查看一下SQL語句的索引執行情況於是desc sql語句查看，跟舊庫上面差別

舊庫（MySQL5.6）

新庫（MySQL5.7）

在2個庫數據，索引相同的情況下，居然會出現索引引用和命中不一樣的情況，所以懷疑是否是遷移數據庫的時候導致索引數據被破壞，於是百度去找，還真的發現了一個例子，也是遷移數據庫後查詢非常慢，後面重建索引之後恢復了，於是準備重建索引，由於表非常多，所以寫了一個工具類來重建索引（唯一索引和普通索引，不包含主鍵索引），核心代碼如下：

List<HashMap> list = mapper.select1(); 
 HashMap<String,HashMap<String,Object>> temp = new HashMap<>();
 for(HashMap map : list){
 String tableName = map.get("TABLE_NAME").toString();
 String indexName = map.get("INDEX_NAME").toString();
 String nonUnique = map.get("NON_UNIQUE").toString();
 String columnName = map.get("COLUMN_NAME").toString();
 if(temp.containsKey(tableName+"|"+indexName)){
 HashMap<String,Object> value = temp.get(tableName+"|"+indexName);
 List<String> columns = (List<String>) value.get("columns");
 columns.add(columnName);
 }else{
 HashMap<String,Object> value = new HashMap<>();
 value.put("nonUnique",nonUnique);
 List<String> columns = new ArrayList<>();
 columns.add(columnName);
 value.put("columns",columns);
 value.put("indexName",indexName);
 value.put("tableName",tableName);
 temp.put(tableName+"|"+indexName,value);
 }
 }
 List<String> creates = new ArrayList<>();
 List<String> drops = new ArrayList<>();
 for(Map.Entry<String,HashMap<String,Object>> entry:temp.entrySet()){
 String create = null;
 String tableName = entry.getValue().get("tableName").toString();
 String indexName = entry.getValue().get("indexName").toString();
 String nonUnique = entry.getValue().get("nonUnique").toString();
 List<String> columns = (List<String>) entry.getValue().get("columns");
 drops.add("DROP INDEX "+indexName+" ON "+tableName+";");
 if("0".equals(nonUnique)){
 //唯一鍵索引
 create = "CREATE UNIQUE INDEX "+indexName+" ON "+tableName+" (";
 }else{
 //創建普通索引
 create = "CREATE INDEX "+indexName+" ON "+tableName+" (";
 }
 for(int i = 0;i < columns.size();i++){
 if(i == columns.size() - 1){
 create += columns.get(i)+");";
 }else{
 create += columns.get(i)+",";
 }
 }
 creates.add(create);
 }
 for(String str : drops){
 System.out.println(str);
 }
 for(String str : creates){
 System.out.println(str);
 }

查詢所有索引SQL代碼如下：

select * from information_schema.statistics WHERE INDEX_SCHEMA='xxxx' AND INDEX_NAME<>'PRIMARY' 

其中xxxx是數據庫實例名，代碼執行完成後將打印出來的SQL語句放進SQL裡面執行即可，當然也可以在使用Java調用SQL執行，不過我為了隨時觀察狀況，所以把SQL複製出來執行

重建索引完成後執行SQL語句，發現速度還是沒有明顯變化，說明不是因為索引數據異常的問題。

檢查MySQL5.7新特性

百度查看MySQL5.7有沒有更新什麼新特性，看到了derived_merge特性，因為derived_merge是MySQL5.7的新的SQL優化方式，所以試著將derived_merge關閉，執行SQL

set GLOBAL optimizer_switch='derived_merge=off' 

執行SQL，發現速度比舊服務器還快，然後用desc查看SQL索引使用情況，跟舊服務器也一樣，於是問題解決

關閉derived_merge後的新問題

本來以為關閉derived_merge後就萬事大吉了，但是服務器的CPU佔滿卻說明事情沒有那麼簡單，top命令查看服務器CPU佔滿的原因發現是因為MySQL（肯定是MySQL，畢竟服務器就這一個軟件），執行命令：

show full processlist; 

查看卡住的鏈接信息，發現有大量的視圖查詢卡住，於是把SQL語句複製出來，發現只是查詢單條數據，理論上不會這樣慢，為了找出原因，停止測試，重啟MySQL，執行視圖SQL語句，發現完全卡住幾分鐘都不能執行完成，強行停止，檢查視圖的SQL是否有異常，發現視圖的SQL也是普通的SQL（4個表的關聯查詢），理論上來說不會耗費這麼久的時間，把創建視圖的SQL語句拿出來跟執行的視圖的SQL條件拼接起來，用desc查看，發現索引正常命中，於是試著執行一次SQL，結果非常意外，速度非常快，所以以為是服務器發瘋，但是為了測試好，就又執行一下視圖的SQL，結果為卡死。也就是說視圖本身的SQL執行沒有任何問題，但是使用視圖查詢，就會進入卡死狀態。於是使用desc 查看視圖SQL索引命中情況，發現結果跟直接的SQL不同，下面是對比圖：

視圖

視圖SQL

視圖的索引命中情況明顯比視圖SQL索引命中多了一個索引，但是為什麼會造成卡死呢，原因就在多的那個索引身上，仔細看可以看到，索引命中的行有83141272975行，11位數，上百億，難怪會卡死，索引命中了上百億的數據，那跟沒有命中索引也沒有區別了，而且最為關鍵的是，我們整個庫所有表加起來應該也沒有上百億的數據啊，畢竟目前最大的表數據量也才近千萬，所以這個索引肯定是有問題，數據也有問題，但是具體什麼問題，就不是我瞭解的了，畢竟不是專業搞數據庫的，所以也希望瞭解的能幫我解答一下。

知道問題後，感覺解決就簡單了，百度搜索了一下MySQL5.7對視圖是否進行了優化，但是不管是百度還是谷歌都沒有找到合適的答案，畢竟視圖本身也只是存儲了一個SQL語句而已，並沒有保存實際數據，也就是說就算優化也是針對SQL語句本身進行優化，但是SQL語句本身執行又沒有任何問題，而且心想MySQL不可能將這麼大個bug放出來吧，於是回想之前調過的參數，是否是因為修改了配置導致的，因為之前主要修改特性的配置就derived_merge，所以懷疑是因為derived_merge導致的，於是又打開derived_merge

set GLOBAL optimizer_switch='derived_merge=on' 

執行視圖，一切正常

排查第四步：索引命中問題

由於關閉了derived_merge會導致視圖查詢問題，而系統中用到了很多視圖，所以如果不用視圖的話需要對系統進行大的改動導致關閉derived_merge不現實，也就只能另想他法了，查詢之所以慢的原因主要還是因為索引沒有命中導致的，也就是說解決了索引命中的問題，就能解決查詢慢的問題，先對比2個庫命中的索引，發現主要是鏈表查詢的時候ON後面跟的條件在新庫上面沒有命中索引，ON後面的條件在主表是跟其他列有組成聯合索引的，而被鏈接的表有部分表是跟其他列組合成聯合索引，有些表的列則沒有任何索引，於是嘗試著在被鏈接的表創建ON後面的字段單獨的索引，創建之後，速度明顯快了一倍，但是還是有部分索引沒有命中，所以又在主表對ON後面的字段單獨創建索引（如果ON後面有幾個條件，就創建聯合索引），創建完成後，執行語句，秒查詢出來，問題解決

嘗試在舊庫上優化索引

由於新庫創建了索引後速度上明細比舊庫快了很多，當然跟配置本身也有關係，於是相到再舊庫上也創建同樣的索引會不會更快了，於是在舊庫上創建了跟新庫相同的索引，執行SQL語句，比未創建索引之前慢了一倍，查看索引命中情況，雖然命中了更多的索引，但是也導致了命中的索引的行數增加

感悟

MySQL不同的版本有不同的SQL優化器，而且不同的版本可能會出現索引命中規則不同，另外索引並不是越多查詢就真的能更快，不合理的索引創建不僅會導致插入慢，還會導致查詢變慢，所以瞭解MySQL索引命中規則和了解所用的MySQL的SQL優化器是有必要的，以及不要輕易更新版本，天知道會出現什麼莫名其妙的問題。。。。。