說實話,這個問題可以涉及到 MySQL 的很多核心知識,可以扯出一大堆,就像要考你計算機網(wǎng)絡(luò)的知識時,問你“輸入URL回車之后,究竟發(fā)生了什么”一樣,看看你能說出多少了。
之前騰訊面試的實話,也問到這個問題了,不過答的很不好,之前沒去想過相關(guān)原因,導致一時之間扯不出來。所以今天,我?guī)Т蠹襾碓敿毘兑幌掠心男┰颍嘈拍憧赐曛笠欢〞兴斋@,不然你打我。
開始裝逼:分類討論
一條 SQL 語句執(zhí)行的很慢,那是每次執(zhí)行都很慢呢?還是大多數(shù)情況下是正常的,偶爾出現(xiàn)很慢呢?所以我覺得,我們還得分以下兩種情況來討論。
1、大多數(shù)情況是正常的,只是偶爾會出現(xiàn)很慢的情況。
2、在數(shù)據(jù)量不變的情況下,這條SQL語句一直以來都執(zhí)行的很慢。
針對這兩種情況,我們來分析下可能是哪些原因?qū)е碌摹?/p>
針對偶爾很慢的情況
一條 SQL 大多數(shù)情況正常,偶爾才能出現(xiàn)很慢的情況,針對這種情況,我覺得這條SQL語句的書寫本身是沒什么問題的,而是其他原因?qū)е碌?,那會是什么原因呢?/p>
數(shù)據(jù)庫在刷新臟頁我也無奈啊
當我們要往數(shù)據(jù)庫插入一條數(shù)據(jù)、或者要更新一條數(shù)據(jù)的時候,我們知道數(shù)據(jù)庫會在內(nèi)存中把對應(yīng)字段的數(shù)據(jù)更新了,但是更新之后,這些更新的字段并不會馬上同步持久化到磁盤中去,而是把這些更新的記錄寫入到 redo log 日記中去,等到空閑的時候,在通過 redo log 里的日記把最新的數(shù)據(jù)同步到磁盤中去。
不過,redo log 里的容量是有限的,如果數(shù)據(jù)庫一直很忙,更新又很頻繁,這個時候 redo log 很快就會被寫滿了,這個時候就沒辦法等到空閑的時候再把數(shù)據(jù)同步到磁盤的,只能暫停其他操作,全身心來把數(shù)據(jù)同步到磁盤中去的,而這個時候,就會導致我們平時正常的SQL語句突然執(zhí)行的很慢,所以說,數(shù)據(jù)庫在在同步數(shù)據(jù)到磁盤的時候,就有可能導致我們的SQL語句執(zhí)行的很慢了。
拿不到鎖我能怎么辦
這個就比較容易想到了,我們要執(zhí)行的這條語句,剛好這條語句涉及到的表,別人在用,并且加鎖了,我們拿不到鎖,只能慢慢等待別人釋放鎖了?;蛘?,表沒有加鎖,但要使用到的某個一行被加鎖了,這個時候,我也沒辦法啊。
如果要判斷是否真的在等待鎖,我們可以用 show processlist這個命令來查看當前的狀態(tài)哦,這里我要提醒一下,有些命令最好記錄一下,反正,我被問了好幾個命令,都不知道怎么寫,呵呵。
下來我們來訪分析下第二種情況,我覺得第二種情況的分析才是最重要的
針對一直都這么慢的情況
如果在數(shù)據(jù)量一樣大的情況下,這條 SQL 語句每次都執(zhí)行的這么慢,那就就要好好考慮下你的 SQL 書寫了,下面我們來分析下哪些原因會導致我們的 SQL 語句執(zhí)行的很不理想。
我們先來假設(shè)我們有一個表,表里有下面兩個字段,分別是主鍵 id,和兩個普通字段 c 和 d。
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
扎心了,沒用到索引
沒有用上索引,我覺得這個原因是很多人都能想到的,例如你要查詢這條語句
select * from t where 100 c and c 100000;
字段沒有索引
剛好你的 c 字段上沒有索引,那么抱歉,只能走全表掃描了,你就體驗不會索引帶來的樂趣了,所以,這回導致這條查詢語句很慢。
字段有索引,但卻沒有用索引
好吧,這個時候你給 c 這個字段加上了索引,然后又查詢了一條語句
select * from t where c - 1 = 1000;
我想問大家一個問題,這樣子在查詢的時候會用索引查詢嗎?
答是不會,如果我們在字段的左邊做了運算,那么很抱歉,在查詢的時候,就不會用上索引了,所以呢,大家要注意這種字段上有索引,但由于自己的疏忽,導致系統(tǒng)沒有使用索引的情況了。
正確的查詢應(yīng)該如下
select * from t where c = 1000 + 1;
有人可能會說,右邊有運算就能用上索引?難道數(shù)據(jù)庫就不會自動幫我們優(yōu)化一下,自動把 c - 1=1000 自動轉(zhuǎn)換為 c = 1000+1。
不好意思,確實不會幫你,所以,你要注意了。
函數(shù)操作導致沒有用上索引
如果我們在查詢的時候,對字段進行了函數(shù)操作,也是會導致沒有用上索引的,例如
select * from t where pow(c,2) = 1000;
這里我只是做一個例子,假設(shè)函數(shù) pow 是求 c 的 n 次方,實際上可能并沒有 pow(c,2)這個函數(shù)。其實這個和上面在左邊做運算也是很類似的。
所以呢,一條語句執(zhí)行都很慢的時候,可能是該語句沒有用上索引了,不過具體是啥原因?qū)е聸]有用上索引的呢,你就要會分析了,我上面列舉的三個原因,應(yīng)該是出現(xiàn)的比較多的吧。
呵呵,數(shù)據(jù)庫自己選錯索引了
我們在進行查詢操作的時候,例如
select * from t where 100 c and c 100000;
我們知道,主鍵索引和非主鍵索引是有區(qū)別的,主鍵索引存放的值是整行字段的數(shù)據(jù),而非主鍵索引上存放的值不是整行字段的數(shù)據(jù),而且存放主鍵字段的值。不大懂的可以看我這篇文章:面試小知識:MySQL索引相關(guān) 里面有說到主鍵索引和非主鍵索引的區(qū)別
也就是說,我們?nèi)绻?c 這個字段的索引的話,最后會查詢到對應(yīng)主鍵的值,然后,再根據(jù)主鍵的值走主鍵索引,查詢到整行數(shù)據(jù)返回。
好吧扯了這么多,其實我就是想告訴你,就算你在 c 字段上有索引,系統(tǒng)也并不一定會走 c 這個字段上的索引,而是有可能會直接掃描掃描全表,找出所有符合 100 c and c 100000 的數(shù)據(jù)。
為什么會這樣呢?
其實是這樣的,系統(tǒng)在執(zhí)行這條語句的時候,會進行預(yù)測:究竟是走 c 索引掃描的行數(shù)少,還是直接掃描全表掃描的行數(shù)少呢?顯然,掃描行數(shù)越少當然越好了,因為掃描行數(shù)越少,意味著I/O操作的次數(shù)越少。
如果是掃描全表的話,那么掃描的次數(shù)就是這個表的總行數(shù)了,假設(shè)為 n;而如果走索引 c 的話,我們通過索引 c 找到主鍵之后,還得再通過主鍵索引來找我們整行的數(shù)據(jù),也就是說,需要走兩次索引。而且,我們也不知道符合 100 c and c 10000 這個條件的數(shù)據(jù)有多少行,萬一這個表是全部數(shù)據(jù)都符合呢?這個時候意味著,走 c 索引不僅掃描的行數(shù)是 n,同時還得每行數(shù)據(jù)走兩次索引。
所以呢,系統(tǒng)是有可能走全表掃描而不走索引的。那系統(tǒng)是怎么判斷呢?
判斷來源于系統(tǒng)的預(yù)測,也就是說,如果要走 c 字段索引的話,系統(tǒng)會預(yù)測走 c 字段索引大概需要掃描多少行。如果預(yù)測到要掃描的行數(shù)很多,它可能就不走索引而直接掃描全表了。
那么問題來了,系統(tǒng)是怎么預(yù)測判斷的呢?這里我給你講下系統(tǒng)是怎么判斷的吧,雖然這個時候我已經(jīng)寫到脖子有點酸了。
系統(tǒng)是通過索引的區(qū)分度來判斷的,一個索引上不同的值越多,意味著出現(xiàn)相同數(shù)值的索引越少,意味著索引的區(qū)分度越高。我們也把區(qū)分度稱之為基數(shù),即區(qū)分度越高,基數(shù)越大。所以呢,基數(shù)越大,意味著符合 100 c and c 10000 這個條件的行數(shù)越少。
所以呢,一個索引的基數(shù)越大,意味著走索引查詢越有優(yōu)勢。
那么問題來了,怎么知道這個索引的基數(shù)呢?
系統(tǒng)當然是不會遍歷全部來獲得一個索引的基數(shù)的,代價太大了,索引系統(tǒng)是通過遍歷部分數(shù)據(jù),也就是通過采樣的方式,來預(yù)測索引的基數(shù)的。
扯了這么多,重點的來了,居然是采樣,那就有可能出現(xiàn)失誤的情況,也就是說,c 這個索引的基數(shù)實際上是很大的,但是采樣的時候,卻很不幸,把這個索引的基數(shù)預(yù)測成很小。例如你采樣的那一部分數(shù)據(jù)剛好基數(shù)很小,然后就誤以為索引的基數(shù)很小。然后就呵呵,系統(tǒng)就不走 c 索引了,直接走全部掃描了。
所以呢,說了這么多,得出結(jié)論:由于統(tǒng)計的失誤,導致系統(tǒng)沒有走索引,而是走了全表掃描,而這,也是導致我們 SQL 語句執(zhí)行的很慢的原因。
這里我聲明一下,系統(tǒng)判斷是否走索引,掃描行數(shù)的預(yù)測其實只是原因之一,這條查詢語句是否需要使用使用臨時表、是否需要排序等也是會影響系統(tǒng)的選擇的。
不過呢,我們有時候也可以通過強制走索引的方式來查詢,例如
select * from t force index(a) where c 100 and c 100000;
我們也可以通過
來查詢索引的基數(shù)和實際是否符
合,如果和實際很不符合的話,我們可以重新來統(tǒng)計索引的基數(shù),可以用這條命令
來重新統(tǒng)計分析。
既然會預(yù)測錯索引的基數(shù),這也意味著,當我們的查詢語句有多個索引的時候,系統(tǒng)有可能也會選錯索引哦,這也可能是 SQL 執(zhí)行的很慢的一個原因。
好吧,就先扯這么多了,你到時候能扯出這么多,我覺得已經(jīng)很棒了,下面做一個總結(jié)。
### 總結(jié)
以上是我的總結(jié)與理解,最后一個部分,我怕很多人不大懂數(shù)據(jù)庫居然會選錯索引,所以我詳細解釋了一下,下面我對以上做一個總結(jié)。
一個 SQL 執(zhí)行的很慢,我們要分兩種情況討論:
1、大多數(shù)情況下很正常,偶爾很慢,則有如下原因
(1)、數(shù)據(jù)庫在刷新臟頁,例如 redo log 寫滿了需要同步到磁盤。
(2)、執(zhí)行的時候,遇到鎖,如表鎖、行鎖。
2、這條 SQL 語句一直執(zhí)行的很慢,則有如下原因。
(1)、沒有用上索引:例如該字段沒有索引;由于對字段進行運算、函數(shù)操作導致無法用索引。
(2)、數(shù)據(jù)庫選錯了索引。
大家如果有補充的,也是可以留言區(qū)補充一波哦。
以上所述是小編給大家介紹的SQL語句執(zhí)行慢的原因詳解整合,希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會及時回復大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持!
您可能感興趣的文章:- 批量執(zhí)行sql語句的方法
- 在SQL Server啟動時自動執(zhí)行存儲過程。
- 提高SQL的執(zhí)行效率的ASP的五種做法
- asp在線執(zhí)行sql語句的函數(shù)
- 為什么ASP中執(zhí)行動態(tài)SQL總報錯誤信息?提示語句語法錯誤
- mysql 顯示SQL語句執(zhí)行時間的代碼
- php執(zhí)行sql語句的寫法
- 高級MySQL數(shù)據(jù)庫面試問題 附答案
- sql面試題(查看數(shù)據(jù)中指定幾行記錄)
- 一道sql面試題附答案