快捷導(dǎo)航

MySQL Order By索引優(yōu)化方法

更新時(shí)間：2012年07月30日 00:29:46 作者：

在一些情況下，MySQL可以直接使用索引來(lái)滿足一個(gè) ORDER BY 或 GROUP BY 子句而無(wú)需做額外的排序

盡管 ORDER BY 不是和索引的順序準(zhǔn)確匹配，索引還是可以被用到，只要不用的索引部分和所有的額外的 ORDER BY 字段在 WHERE 子句中都被包括了。

使用索引的MySQL Order By
下列的幾個(gè)查詢都會(huì)使用索引來(lái)解決 ORDER BY 或 GROUP BY 部分：

復(fù)制代碼代碼如下:

 
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; 
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2; 
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2; 
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC; 
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC; 

不使用索引的MySQL Order By
在另一些情況下，MySQL無(wú)法使用索引來(lái)滿足 ORDER BY，盡管它會(huì)使用索引來(lái)找到記錄來(lái)匹配 WHERE 子句。這些情況如下：
* 對(duì)不同的索引鍵做 ORDER BY ：
SELECT * FROM t1 ORDER BY key1, key2;
* 在非連續(xù)的索引鍵部分上做 ORDER BY：
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
* 同時(shí)使用了 ASC 和 DESC：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
* 用于搜索記錄的索引鍵和做 ORDER BY 的不是同一個(gè)：
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
* 有很多表一起做連接，而且讀取的記錄中在 ORDER BY 中的字段都不全是來(lái)自第一個(gè)非常數(shù)的表中（也就是說(shuō)，在 EXPLAIN 分析的結(jié)果中的第一個(gè)表的連接類型不是 const）。
* 使用了不同的 ORDER BY 和 GROUP BY 表達(dá)式。
* 表索引中的記錄不是按序存儲(chǔ)。例如，HASH 和 HEAP 表就是這樣。

通過(guò)執(zhí)行 EXPLAIN SELECT ... ORDER BY，就知道MySQL是否在查詢中使用了索引。如果 Extra 字段的值是 Using filesort，則說(shuō)明MySQL無(wú)法使用索引。詳情請(qǐng)看"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。當(dāng)必須對(duì)結(jié)果進(jìn)行排序時(shí)，MySQL 4.1以前它使用了以下 filesort 算法：

復(fù)制代碼代碼如下:

 
根據(jù)索引鍵讀取記錄，或者掃描數(shù)據(jù)表。那些無(wú)法匹配 WHERE 分句的記錄都會(huì)被略過(guò)。 
在緩沖中每條記錄都用一個(gè)‘對(duì)'存儲(chǔ)了2個(gè)值（索引鍵及記錄指針）。緩沖的大小依據(jù)系統(tǒng)變量 sort_buffer_size 的值而定。 
當(dāng)緩沖慢了時(shí)，就運(yùn)行 qsort（快速排序）并將結(jié)果存儲(chǔ)在臨時(shí)文件中。將存儲(chǔ)的塊指針保存起來(lái)（如果所有的‘對(duì)'值都能保存在緩沖中，就無(wú)需創(chuàng)建臨時(shí)文件了）。 
執(zhí)行上面的操作，直到所有的記錄都讀取出來(lái)了。 
做一次多重合并，將多達(dá) MERGEBUFF（7）個(gè)區(qū)域的塊保存在另一個(gè)臨時(shí)文件中。重復(fù)這個(gè)操作，直到所有在第一個(gè)文件的塊都放到第二個(gè)文件了。 
重復(fù)以上操作，直到剩余的塊數(shù)量小于 MERGEBUFF2 (15)。 
在最后一次多重合并時(shí)，只有記錄的指針（排序索引鍵的最后部分）寫到結(jié)果文件中去。 
通過(guò)讀取結(jié)果文件中的記錄指針來(lái)按序讀取記錄。想要優(yōu)化這個(gè)操作，MySQL將記錄指針讀取放到一個(gè)大的塊里，并且使用它來(lái)按序讀取記錄，將記錄放到緩沖中。緩沖的大小由系統(tǒng)變量 read_rnd_buffer_size 的值而定。這個(gè)步驟的代碼在源文件 `sql/records.cc' 中。 

這個(gè)逼近算法的一個(gè)問(wèn)題是，數(shù)據(jù)庫(kù)讀取了2次記錄：一次是估算 WHERE 分句時(shí)，第二次是排序時(shí)。盡管第一次都成功讀取記錄了（例如，做了一次全表掃描），第二次是隨機(jī)的讀?。ㄋ饕I已經(jīng)排好序了，但是記錄并沒(méi)有）。在MySQL 4.1 及更新版本中，filesort 優(yōu)化算法用于記錄中不只包括索引鍵值和記錄的位置，還包括查詢中要求的字段。這么做避免了需要2次讀取記錄。改進(jìn)的 filesort 算法做法大致如下：
1. 跟以前一樣，讀取匹配 WHERE 分句的記錄。
2. 相對(duì)于每個(gè)記錄，都記錄了一個(gè)對(duì)應(yīng)的；‘元組'信息信息，包括索引鍵值、記錄位置、以及查詢中所需要的所有字段。
3. 根據(jù)索引鍵對(duì)‘元組'信息進(jìn)行排序。
4. 按序讀取記錄，不過(guò)是從已經(jīng)排序過(guò)的‘元組'列表中讀取記錄，而非從數(shù)據(jù)表中再讀取一次。

使用改進(jìn)后的 filesort 算法相比原來(lái)的，‘元組'比‘對(duì)'需要占用更長(zhǎng)的空間，它們很少正好適合放在排序緩沖中（緩沖的大小是由 sort_buffer_size 的值決定的）。因此，這就可能需要有更多的I/O操作，導(dǎo)致改進(jìn)的算法更慢。為了避免使之變慢，這種優(yōu)化方法只用于排序‘元組'中額外的字段的大小總和超過(guò)系統(tǒng)變量 max_length_for_sort_data 的情況（這個(gè)變量的值設(shè)置太高的一個(gè)表象就是高磁盤負(fù)載低CPU負(fù)載）。想要提高 ORDER BY 的速度，首先要看MySQL能否使用索引而非額外的排序過(guò)程。如果不能使用索引，可以試著遵循以下策略：
* 增加 sort_buffer_size 的值。
* 增加 read_rnd_buffer_size 的值。
* 修改 tmpdir，讓它指向一個(gè)有很多剩余空間的專用文件系統(tǒng)。
如果使用MySQL 4.1或更新，這個(gè)選項(xiàng)允許有多個(gè)路徑用循環(huán)的格式。各個(gè)路徑之間在 Unix 上用冒號(hào)（':'）分隔開來(lái)，在 Windows，NetWare以及OS/2 上用分號(hào)（';'）?？梢岳眠@個(gè)特性將負(fù)載平均分?jǐn)偨o幾個(gè)目錄。注意：這些路徑必須是分布在不同物理磁盤上的目錄，而非在同一個(gè)物理磁盤上的不同目錄。

您可能感興趣的文章: