（一）前言

這幾天供應(yīng)商在測(cè)試環(huán)境上使用MYSQL數(shù)據(jù)庫(kù)做開(kāi)發(fā)時(shí)遇到一個(gè)SQL性能問(wèn)題，即在他開(kāi)發(fā)環(huán)境本地跑SQL速度很快就一兩秒時(shí)間，但是同樣的SQL放在測(cè)試環(huán)境上死活跑了很久一直出不了結(jié)果。最后求助到我這邊，以下正文是我解決這次問(wèn)題的一個(gè)過(guò)程淺談，供大家參考。

（二）正文

本文使用NAVICAT試用版作為基礎(chǔ)工具來(lái)說(shuō)明，需要永久激活的可以在網(wǎng)上找到相關(guān)介紹走正式途徑。

其次附上一篇文章，解釋說(shuō)明如果在NAVICAT中運(yùn)行一條長(zhǎng)時(shí)間的SQL想關(guān)閉終止它，圖形化點(diǎn)擊失敗時(shí)候所應(yīng)該采取的方式，文章鏈接如下：

在Navicat上如何停止正在運(yùn)行的MYSQL語(yǔ)句

1. 表結(jié)構(gòu)/索引展示

以下將大致描述下本次遇到性能問(wèn)題涉及的兩張表（rep_consultant_first和rep_newcomer_consultant）的表結(jié)構(gòu)和索引。

（1）表結(jié)構(gòu)

a. rep_consultant_first

b. rep_newcomer_consultant

（2）各表索引情況

 a. rep_consultant_first

b. rep_newcomer_consultant

2. 存在性能問(wèn)題的SQL語(yǔ)句

這條SQL語(yǔ)句的意圖在于找出rep_newcomer_consultant表中缺失的存在于rep_consultant_first表中的數(shù)據(jù)，即可求這兩張表的差集(rep_consultant_first - rep_newcomer_consultant)，簡(jiǎn)單說(shuō)就是下圖里的紅色填充區(qū)域。

select consultantNumber,
       customerId,userName,
			 telephoneNumber,
			 sponsorConsultantNumber,
			 signUpDate 
from rep_consultant_first rcf left join rep_newcomer_consultant rnco
 on rnco.rncoConsultantNumber=rcf.consultantNumber
where rnco.rncoConsultantNumber is null;

 直接運(yùn)行后我們發(fā)現(xiàn)這條語(yǔ)句運(yùn)行了好幾十分鐘依舊沒(méi)有結(jié)果，假如只是稍微慢一點(diǎn)那可能勉強(qiáng)說(shuō)得過(guò)去，但是目前這種情況實(shí)在是到無(wú)法接受的情況了?。?！

3. 解決思路

（1）執(zhí)行計(jì)劃思路調(diào)優(yōu)

一般SQL慢了，第一個(gè)想的一定是查一下執(zhí)行計(jì)劃是不是哪個(gè)環(huán)節(jié)沒(méi)有走索引，走了全表掃描，讓我們選中SQL部分，點(diǎn)擊“解釋已選擇的”來(lái)看下這條SQL的執(zhí)行計(jì)劃詳情：

 從執(zhí)行計(jì)劃中我們看到別名為rcf(即rep_consultant_first表)的type方式走了ALL，即全表掃描，那自然而然我們會(huì)先想從這里去優(yōu)化。

回到rep_consultant_first表的索引位置，我們看到在select后篩出來(lái)的字段里只有consultant number和sponsorConsultantNumber字段上有索引而其他并沒(méi)有，所以不可避免走了全表掃描

那我們先嘗試下給未加索引的字段加上一組索引，大致流程如下：

a. 找到所要加索引字段所在的表（rep_consultant_first），右鍵點(diǎn)擊后選擇"設(shè)計(jì)表"

b. 找到索引選項(xiàng)卡，添加索引TestIndex，最后點(diǎn)擊保存。

讓我們重新回到一開(kāi)始的SQL，看一下加完索引后是否執(zhí)行計(jì)劃有優(yōu)化：

可以看出，執(zhí)行計(jì)劃的TYPE從ALL變成了INDEX且EXTRA列明確說(shuō)明了Using index了，那說(shuō)明執(zhí)行計(jì)劃確實(shí)改變了，沒(méi)有掃全表，不過(guò)遺憾的是。。。SQL依舊跑不出來(lái)。。。

（2）字符集匹配調(diào)優(yōu)

此時(shí)真的黔驢技窮了么？？還真沒(méi)有 ！足球籃球世界里我們經(jīng)?？吹阶詈髸r(shí)刻逆襲的致命一擊取得勝利，在SQL優(yōu)化里我們同樣有這樣的機(jī)會(huì)！ 仔細(xì)回想了下，似乎在MYSQL相關(guān)手冊(cè)資料中的優(yōu)化TIPS里除了添加相關(guān)字段索引之外，那left join中關(guān)聯(lián)兩表的字段，字符集是否需要統(tǒng)一？？？

有了這個(gè)思路，我們立馬著手再看下這條問(wèn)題SQL語(yǔ)句，我們重點(diǎn)關(guān)注rep_consultant_first表上的consultantNumber字段以及rep_newcomer_consultant表上的rncoConsultantNumber字段：

對(duì)比之下，立馬看出了區(qū)別！在rep_consultant_first上字段consultantNumber的字符集為utf8mb4，而rep_newcomer_consultant上字段rncoConsultantNumber的字符集為gbk。在官方相關(guān)文檔中提到過(guò)關(guān)聯(lián)字段除了需要有索引外，擁有相同的字符集以及數(shù)據(jù)類(lèi)型相當(dāng)重用，這會(huì)極大影響查詢(xún)速度！

接下來(lái)我們來(lái)具體操作下，可以將rep_newcomer_consultant上字段rncoConsultantNumber的字符集改從gbk改為utf8mb4，排序方式也改為和rep_consultant_first表一樣的utf8mb4_0900_ai_ci試一試，點(diǎn)擊保存按鈕：

保存成功后，我們立馬再運(yùn)行下慢SQL：

一下子只有0.885秒了??！速度飆升到無(wú)法言喻的速度！至此我們基本算優(yōu)化成功了。

（三）總結(jié)

經(jīng)過(guò)這個(gè)案例后，我搜羅總結(jié)了下本例涉及到一些優(yōu)化注意點(diǎn)：

1. 關(guān)于執(zhí)行計(jì)劃中TYPE的性能比較

2. 關(guān)于left join優(yōu)化

1、left join選擇小表作為驅(qū)動(dòng)表（這部分基本是大家的共識(shí)）

2、如果左表比較大，并且業(yè)務(wù)要求驅(qū)動(dòng)表必須是左表，那么我們可以通過(guò)where條件語(yǔ)句，使得左表被過(guò)濾的小一些，主要原理和第一條類(lèi)似

3、關(guān)聯(lián)字段給索引，因?yàn)樵趍ysql的嵌套循環(huán)算法中，是通過(guò)關(guān)聯(lián)字段進(jìn)行關(guān)聯(lián)，并查詢(xún)的，所以給關(guān)聯(lián)字段索引很必要

4、如果sql里面有排序，請(qǐng)給排序字段加上索引，不然會(huì)造成排序使用全表掃描
        參考：https://www.oschina.net/question/930697_2190172      
5、如果where條件中含有右表的非空條件（除開(kāi)is null），則left join語(yǔ)句等同于join語(yǔ)句，可直接改寫(xiě)成join語(yǔ)句。 

6、根據(jù)文檔，MySQL能更高效地在聲明具有相同類(lèi)型和尺寸的列上使用索引。所以把表與表之間的關(guān)聯(lián)字段給上encoding和collation（決定字符比較的規(guī)則）全部改成統(tǒng)一的類(lèi)型

7、右表的條件列一定要加上索引（主鍵、唯一索引、前綴索引等），最好能夠使type達(dá)到range及以上（ref,eq_ref,const,system） 

3. 其他注意點(diǎn)

總結(jié)

到此這篇關(guān)于Mysql使用left join寫(xiě)查詢(xún)語(yǔ)句執(zhí)行很慢問(wèn)題解決的文章就介紹到這了,更多相關(guān)Mysql left join查詢(xún)語(yǔ)句執(zhí)行很慢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論