起因

因?yàn)樯a(chǎn)環(huán)境數(shù)據(jù)量越來越大，客戶越來越多，項(xiàng)目功能也越來越多，項(xiàng)目本身也越來越多，導(dǎo)致之前的服務(wù)器內(nèi)存、硬盤都已經(jīng)漸漸的不夠用了，當(dāng)時(shí)出現(xiàn)了2種解決方案，增加服務(wù)器配置和新購服務(wù)器，但是就算是新增硬盤，也需要對數(shù)據(jù)庫進(jìn)行遷移，所以就采用了新購服務(wù)器的方案，并且因?yàn)橹笆歉咝г票P，出現(xiàn)過IO占滿的情況，所以對于新購的服務(wù)器采用了SSD硬盤，理論上速度會飛起來了，實(shí)際上我在新服務(wù)器上安裝MySQL5.7，因?yàn)槁犝fMySQL5.7性能提升了N倍，還支持json（雖然對我們沒什么用），但是畢竟MySQL8已經(jīng)出來了，說明MySQL5.7也肯定穩(wěn)定好了。so，就找了個(gè)夜深人靜的晚上偷偷的吧數(shù)據(jù)庫遷移過來了，然后開啟慢查詢?nèi)罩荆拗茷?秒，于是開始各種測試，然后查看了一下慢查詢?nèi)罩荆欢崖樵內(nèi)罩?，所以有了這篇文章，為什么會出現(xiàn)這么多慢查詢，以及如何解決

開始排查

將慢查詢SQL拿出來，發(fā)現(xiàn)主要的慢查詢SQL都是鏈表查詢的語句，也就是說查詢語句本身非常復(fù)雜，所以就把SQL語句放回之前的數(shù)據(jù)庫執(zhí)行，發(fā)現(xiàn)之前的數(shù)據(jù)庫都是不到1秒就查詢出來了，而在新的數(shù)據(jù)庫上最慢能達(dá)到140多秒，這明顯就不正常了，畢竟新的MySQL服務(wù)器無論是CPU、內(nèi)存、還是硬盤相對于以前的MySQL服務(wù)器來說，都是好了不止一星半點(diǎn)，如果說性能差不多還能接受，但是一下子慢了這么多，就明顯是有異常了，于是開始挨個(gè)排查

排查第一步：配置問題

首先2邊服務(wù)器的配置文件是一模一樣的，因?yàn)榫褪菑呐f服務(wù)器吧配置文件復(fù)制過來的，但是因?yàn)镸ySQL的版本不一致，所以懷疑是因?yàn)樾掳嬗行┡渲貌灰粯樱詫?dǎo)致的，于是百度了MySQL5.7的優(yōu)化配置，同時(shí)根據(jù)機(jī)器的內(nèi)存CPU等硬件情況調(diào)整了部分配置，重啟MySQL，執(zhí)行語句，效果不明顯，平均下來能快1秒左右，但是這1秒本身也可以認(rèn)為是查詢波動(dòng)，所以不是配置的問題

排查第二步：硬件問題

首先CPU和內(nèi)存應(yīng)該不會有什么問題，唯一可能性就是SSD硬盤的問題，因?yàn)橹翱吹竭^因?yàn)镾SD硬盤導(dǎo)致K,V鍵值存儲性能低下，跟機(jī)械硬盤完全不在同一個(gè)等級上，所以懷疑SSD是否需要開啟什么特別的配置才行，于是百度，發(fā)現(xiàn)雖然有針對SSD的優(yōu)化配置，但是也沒有因?yàn)橛昧薙SD導(dǎo)致速度非常慢的情況，于是針對SSD進(jìn)行了優(yōu)化配置，重啟MySQL，執(zhí)行語句，效果微乎其微

排查第三步：語句問題

也是實(shí)在沒辦法了，才想到這個(gè)問題，但是我自己都覺得不大可能，而且語句本身也優(yōu)化的差不多了，小結(jié)果集驅(qū)動(dòng)大結(jié)果集，索引根據(jù)where條件創(chuàng)建等。畢竟就算MySQL升級也不會說改變SQL語法之類的，最多就是在優(yōu)化SQL的進(jìn)行了一些特殊處理，所以先查看一下SQL語句的索引執(zhí)行情況于是desc sql語句查看，跟舊庫上面差別

舊庫（MySQL5.6）

新庫（MySQL5.7）

在2個(gè)庫數(shù)據(jù)，索引相同的情況下，居然會出現(xiàn)索引引用和命中不一樣的情況，所以懷疑是否是遷移數(shù)據(jù)庫的時(shí)候?qū)е滤饕龜?shù)據(jù)被破壞，于是百度去找，還真的發(fā)現(xiàn)了一個(gè)例子，也是遷移數(shù)據(jù)庫后查詢非常慢，后面重建索引之后恢復(fù)了，于是準(zhǔn)備重建索引，由于表非常多，所以寫了一個(gè)工具類來重建索引（唯一索引和普通索引，不包含主鍵索引），核心代碼如下：

List list = mapper.select1();? ? ? ? ? HashMap> temp =newHashMap<>();for(HashMap map : list){StringtableName = map.get("TABLE_NAME").toString();StringindexName = map.get("INDEX_NAME").toString();StringnonUnique = map.get("NON_UNIQUE").toString();StringcolumnName = map.get("COLUMN_NAME").toString();if(temp.containsKey(tableName+"|"+indexName)){? ? ? ? ? ? ? ? HashMap value = temp.get(tableName+"|"+indexName);? ? ? ? ? ? ? ? List columns = (List) value.get("columns");? ? ? ? ? ? ? ? columns.add(columnName);? ? ? ? ? ? }else{? ? ? ? ? ? ? ? HashMap value =newHashMap<>();? ? ? ? ? ? ? ? value.put("nonUnique",nonUnique);? ? ? ? ? ? ? ? List columns =newArrayList<>();? ? ? ? ? ? ? ? columns.add(columnName);? ? ? ? ? ? ? ? value.put("columns",columns);? ? ? ? ? ? ? ? value.put("indexName",indexName);? ? ? ? ? ? ? ? value.put("tableName",tableName);? ? ? ? ? ? ? ? temp.put(tableName+"|"+indexName,value);? ? ? ? ? ? }? ? ? ? }? ? ? ? List creates =newArrayList<>();? ? ? ? List drops =newArrayList<>();for(Map.Entry> entry:temp.entrySet()){Stringcreate =null;StringtableName = entry.getValue().get("tableName").toString();StringindexName = entry.getValue().get("indexName").toString();StringnonUnique = entry.getValue().get("nonUnique").toString();? ? ? ? ? ? List columns = (List) entry.getValue().get("columns");? ? ? ? ? ? drops.add("DROP INDEX "+indexName+" ON "+tableName+";");if("0".equals(nonUnique)){//唯一鍵索引create ="CREATE UNIQUE INDEX "+indexName+" ON "+tableName+" (";? ? ? ? ? ? }else{//創(chuàng)建普通索引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(Stringstr : drops){? ? ? ? ? ? System.out.println(str);? ? ? ? }for(Stringstr : creates){? ? ? ? ? ? System.out.println(str);? ? ? ? }

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

select*frominformation_schema.statisticsWHEREINDEX_SCHEMA='xxxx'ANDINDEX_NAME<>'PRIMARY'

其中xxxx是數(shù)據(jù)庫實(shí)例名，代碼執(zhí)行完成后將打印出來的SQL語句放進(jìn)SQL里面執(zhí)行即可，當(dāng)然也可以在使用Java調(diào)用SQL執(zhí)行，不過我為了隨時(shí)觀察狀況，所以把SQL復(fù)制出來執(zhí)行

重建索引完成后執(zhí)行SQL語句，發(fā)現(xiàn)速度還是沒有明顯變化，說明不是因?yàn)樗饕龜?shù)據(jù)異常的問題。

檢查MySQL5.7新特性

百度查看MySQL5.7有沒有更新什么新特性，看到了derived_merge特性，因?yàn)閐erived_merge是MySQL5.7的新的SQL優(yōu)化方式，所以試著將derived_merge關(guān)閉，執(zhí)行SQL

setGLOBALoptimizer_switch='derived_merge=off'

執(zhí)行SQL，發(fā)現(xiàn)速度比舊服務(wù)器還快，然后用desc查看SQL索引使用情況，跟舊服務(wù)器也一樣，于是問題解決

關(guān)閉derived_merge后的新問題

本來以為關(guān)閉derived_merge后就萬事大吉了，但是服務(wù)器的CPU占滿卻說明事情沒有那么簡單，top命令查看服務(wù)器CPU占滿的原因發(fā)現(xiàn)是因?yàn)镸ySQL（肯定是MySQL，畢竟服務(wù)器就這一個(gè)軟件），執(zhí)行命令：

showfullprocesslist;

查看卡住的鏈接信息，發(fā)現(xiàn)有大量的視圖查詢卡住，于是把SQL語句復(fù)制出來，發(fā)現(xiàn)只是查詢單條數(shù)據(jù)，理論上不會這樣慢，為了找出原因，停止測試，重啟MySQL，執(zhí)行視圖SQL語句，發(fā)現(xiàn)完全卡住幾分鐘都不能執(zhí)行完成，強(qiáng)行停止，檢查視圖的SQL是否有異常，發(fā)現(xiàn)視圖的SQL也是普通的SQL（4個(gè)表的關(guān)聯(lián)查詢），理論上來說不會耗費(fèi)這么久的時(shí)間，把創(chuàng)建視圖的SQL語句拿出來跟執(zhí)行的視圖的SQL條件拼接起來，用desc查看，發(fā)現(xiàn)索引正常命中，于是試著執(zhí)行一次SQL，結(jié)果非常意外，速度非?？欤砸詾槭欠?wù)器發(fā)瘋，但是為了測試好，就又執(zhí)行一下視圖的SQL，結(jié)果為卡死。也就是說視圖本身的SQL執(zhí)行沒有任何問題，但是使用視圖查詢，就會進(jìn)入卡死狀態(tài)。于是使用desc 查看視圖SQL索引命中情況，發(fā)現(xiàn)結(jié)果跟直接的SQL不同，下面是對比圖：

視圖

視圖SQL

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

知道問題后，感覺解決就簡單了，百度搜索了一下MySQL5.7對視圖是否進(jìn)行了優(yōu)化，但是不管是百度還是谷歌都沒有找到合適的答案，畢竟視圖本身也只是存儲了一個(gè)SQL語句而已，并沒有保存實(shí)際數(shù)據(jù)，也就是說就算優(yōu)化也是針對SQL語句本身進(jìn)行優(yōu)化，但是SQL語句本身執(zhí)行又沒有任何問題，而且心想MySQL不可能將這么大個(gè)bug放出來吧，于是回想之前調(diào)過的參數(shù)，是否是因?yàn)樾薷牧伺渲脤?dǎo)致的，因?yàn)橹爸饕薷奶匦缘呐渲镁蚫erived_merge，所以懷疑是因?yàn)閐erived_merge導(dǎo)致的，于是又打開derived_merge

setGLOBALoptimizer_switch='derived_merge=on'

執(zhí)行視圖，一切正常

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

由于關(guān)閉了derived_merge會導(dǎo)致視圖查詢問題，而系統(tǒng)中用到了很多視圖，所以如果不用視圖的話需要對系統(tǒng)進(jìn)行大的改動(dòng)導(dǎo)致關(guān)閉derived_merge不現(xiàn)實(shí)，也就只能另想他法了，查詢之所以慢的原因主要還是因?yàn)樗饕龥]有命中導(dǎo)致的，也就是說解決了索引命中的問題，就能解決查詢慢的問題，先對比2個(gè)庫命中的索引，發(fā)現(xiàn)主要是鏈表查詢的時(shí)候ON后面跟的條件在新庫上面沒有命中索引，ON后面的條件在主表是跟其他列有組成聯(lián)合索引的，而被鏈接的表有部分表是跟其他列組合成聯(lián)合索引，有些表的列則沒有任何索引，于是嘗試著在被鏈接的表創(chuàng)建ON后面的字段單獨(dú)的索引，創(chuàng)建之后，速度明顯快了一倍，但是還是有部分索引沒有命中，所以又在主表對ON后面的字段單獨(dú)創(chuàng)建索引（如果ON后面有幾個(gè)條件，就創(chuàng)建聯(lián)合索引），創(chuàng)建完成后，執(zhí)行語句，秒查詢出來，問題解決

嘗試在舊庫上優(yōu)化索引

由于新庫創(chuàng)建了索引后速度上明細(xì)比舊庫快了很多，當(dāng)然跟配置本身也有關(guān)系，于是相到再舊庫上也創(chuàng)建同樣的索引會不會更快了，于是在舊庫上創(chuàng)建了跟新庫相同的索引，執(zhí)行SQL語句，比未創(chuàng)建索引之前慢了一倍，查看索引命中情況，雖然命中了更多的索引，但是也導(dǎo)致了命中的索引的行數(shù)增加

感悟

MySQL不同的版本有不同的SQL優(yōu)化器，而且不同的版本可能會出現(xiàn)索引命中規(guī)則不同，另外索引并不是越多查詢就真的能更快，不合理的索引創(chuàng)建不僅會導(dǎo)致插入慢，還會導(dǎo)致查詢變慢，所以了解MySQL索引命中規(guī)則和了解所用的MySQL的SQL優(yōu)化器是有必要的，以及不要輕易更新版本，天知道會出現(xiàn)什么莫名其妙的問題。。。。。