最近，幫同事重寫了一個(gè)MySQL SQL語句，該SQL語句涉及兩張表，其中一張表是字典表（需返回一個(gè)字段），另一張表是業(yè)務(wù)表（本身就有150個(gè)字段，需全部返回），當(dāng)然，字段的個(gè)數(shù)是否合理在這里不予評(píng)價(jià)。平時(shí)，返回的數(shù)據(jù)大概5w左右，系統(tǒng)尚能收到數(shù)據(jù)。但12月31日那天，數(shù)據(jù)量大概20w，導(dǎo)致SQL執(zhí)行時(shí)間過長，未能在規(guī)定的時(shí)間內(nèi)反饋結(jié)果，于是系統(tǒng)直接報(bào)錯(cuò)。
一般的思路是用MySQL的分頁功能，即直接在原SQL語句后面增加LIMIT子句。但請注意，雖然你看到的反饋結(jié)果只是LIMIT后面指定的數(shù)量，于是想當(dāng)然的以為MySQL只是檢索了指定數(shù)量的數(shù)據(jù)，然后給予返回。其實(shí)，MySQL內(nèi)部實(shí)現(xiàn)的原理是，檢索所有符合where條件的記錄，然后返回指定數(shù)量的記錄。從這個(gè)角度來看，直接在原SQL語句后面添加LIMIT子句只能說是一種可以實(shí)現(xiàn)功能的方案，但未必最優(yōu)。
具體在本例中，首先我們來看一下150個(gè)字段的表的統(tǒng)計(jì)信息：
一行大概就占2k，而Innodb默認(rèn)頁的大小為16k，這意味著，一個(gè)頁中最多可存儲(chǔ)8行的數(shù)據(jù)。隨機(jī)讀的可能性大大增加。而這無疑會(huì)對數(shù)據(jù)庫系統(tǒng)的IO造成極大的壓力。
優(yōu)化前
如果采用上述方案，即直接在原SQL語句后面增加LIMIT子句，下面，我們來看看它的執(zhí)行情況。
首先，直接添加LIMIT子句后的SQL語句如下（已省略a1表的150個(gè)字段和a2中的一個(gè)字段）：
其執(zhí)行時(shí)間如下：
大概執(zhí)行了32s，絕大部分都花費(fèi)到Sending data上了。Sending data指的是服務(wù)器檢索數(shù)據(jù)，讀取數(shù)據(jù)，并將數(shù)據(jù)返回給客戶端的時(shí)間。
關(guān)于上述執(zhí)行結(jié)果，有以下幾點(diǎn)需要說明：
1. 這是SQL語句多次執(zhí)行后的結(jié)果，這樣就可以排除結(jié)果緩存的影響，事實(shí)上，每次查詢的時(shí)長都是32s左右。
2. 為什么選用的是limit 50000,10000，而不是0，10000，這個(gè)主要是考慮到對于LIMIT子句來說，越到后面，分頁的成本越高。基于此，選擇了中間值來作為分頁的結(jié)果。
該語句的執(zhí)行計(jì)劃如下：
優(yōu)化后：
優(yōu)化的思路：
只對該表的主鍵進(jìn)行分頁，然后用返回的主鍵作為子查詢的結(jié)果，來檢索該表其它字段的值。
改寫后的SQL語句如下：
其執(zhí)行時(shí)間如下：
大概3s多，比第一種方案快了差不多10倍，效果顯著。
下面來看看其執(zhí)行計(jì)劃（explain extended）
總結(jié)：
1. 改寫后的語句原本如下：
但MySQL報(bào)以下錯(cuò)誤：
需再增加一個(gè)嵌套子查詢，
比如這樣的語句是不能正確執(zhí)行的。
但是，只要你再加一層就行。如：
這樣就可以繞開limit子查詢的問題。
問題解決。
2. 如果想查看MySQL查詢優(yōu)化器等價(jià)改寫后的SQL語句，可首先通過explain extended得到具體的執(zhí)行計(jì)劃，然后通過show warnings查看。
具體在本例中，等價(jià)改寫后的SQL語句如下：
與設(shè)想中的執(zhí)行順序一致~
3. 如何查看MySQL語句各步驟的執(zhí)行時(shí)間。
以上就是本文的全部內(nèi)容，希望對大家MySQL分頁優(yōu)化有所幫助。
