MySQL Limit性能優(yōu)化及分頁數(shù)據(jù)性能優(yōu)化詳解

MySQL Limit可以分段查詢數(shù)據(jù)庫數(shù)據(jù)，主要應(yīng)用在分頁上。雖然現(xiàn)在寫的網(wǎng)站數(shù)據(jù)都是千條級別，一些小的的優(yōu)化起的作用不大，但是開發(fā)就要做到極致，追求完美性能。下面記錄一些limit性能優(yōu)化方法。

Limit語法：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT子句可以被用于強(qiáng)制 SELECT 語句返回指定的記錄數(shù)。LIMIT接受一個或兩個數(shù)字參數(shù)。參數(shù)必須是一個整數(shù)常量。

如果給定兩個參數(shù)，第一個參數(shù)指定第一個返回記錄行的偏移量，第二個參數(shù)指定返回記錄行的最大數(shù)目。初始記錄行的偏移量是 0(而不是 1)。

支持 limit # offset # 語法：

mysql> SELECT * FROM table LIMIT 5,10; //檢索記錄行6-15
//為了檢索從某一個偏移量到記錄集的結(jié)束所有的記錄行，可以指定第二個參數(shù)為-1
mysql> SELECT * FROM table LIMIT 95,-1; //檢索記錄行96-last
//如果只給定一個參數(shù)，它表示返回最大的記錄行數(shù)目，換句話說，LIMIT n 等價于 LIMIT 0,n
mysql> SELECT * FROM table LIMIT 5; //檢索前5個記錄行

limit n,m 表示從第n條記錄開始選擇m條記錄。而大多數(shù)開發(fā)人員喜歡使用這類語句來解決Web中經(jīng)典的分頁問題。對于小規(guī)模的數(shù)據(jù)，這并不會有太大的問題。對于論壇這類可能具有非常大規(guī)模數(shù)據(jù)的應(yīng)用來說，limit n,m 的效率是十分低的。因為每次都需要對數(shù)據(jù)進(jìn)行選取。如果只是選取前5條記錄，則非常輕松和容易；但是對100萬條記錄，選取從80萬行記錄開始的5條記錄，則還需要掃描記錄到這個位置。

也就是說limit 10000,20意味著掃描滿足條件的10020行，扔掉前面的10000行，返回最后的20行；問題就在這里，如果是limit 100000,100，需要掃描100100行，在一個高并發(fā)的應(yīng)用里，每次查詢需要掃描超過10W行，性能肯定大打折扣。

不同數(shù)據(jù)量讀取數(shù)據(jù)效率比較：

1.offset比較小的時候：

select * from table limit 5,10

多次運行，時間保持在0.0004-0.0005之間

Select * From table Where id >=( 
Select id From table Order By id limit 10,1 
) limit 10

多次運行，時間保持在0.0005-0.0006之間。所有，在offset較小的時候，直接使用limit效率會高點！

2.offset數(shù)據(jù)比較大的時候：

select * from table limit 10000,10

多次運行，時間保持在0.0187秒左右。

Select * From table Where id >=( 
Select id From table Order By id limit 10000,1 
) limit 10

多次運行，時間保持在0.061秒左右，是前者的1/3左右。所以，offset較大時，使用后者效率會搞！這是用了id做索引的結(jié)果。

如果用id作為數(shù)據(jù)表的主鍵：

select id from table limit 10000,10

查詢花費時間大概在0.04秒，這是因為用id主鍵作為索引的結(jié)果。

Limit性能優(yōu)化：

Select * From cyclopedia Where ID>=( 
Select Max(ID) From ( 
Select ID From cyclopedia Order By ID limit 90001 
) As tmp 
) limit 100; 
Select * From cyclopedia Where ID>=( 
Select Max(ID) From ( 
Select ID From cyclopedia Order By ID limit 90000,1 
) As tmp 
) limit 100;

同樣是取90000條后100條記錄，第2句會快點。因為第1句是先取前90001條記錄，取其中最大一個ID值作為起始標(biāo)識，然后利用它快速定位下100條數(shù)據(jù)；而第2句是只取了最后一條記錄，然后取ID值作為起始標(biāo)識定位100條記錄。第2句可簡寫成：

Select * From cyclopedia Where ID>=( 
Select ID From ( 
Select ID From cyclopedia Order By ID limit 90000,1 
) As tmp 
) limit 100;

省去Max運算，一般ID都是遞增。

分頁數(shù)據(jù)性能優(yōu)化：

1、對于數(shù)據(jù)量較大數(shù)據(jù)表，可以建立主鍵和索引字段建立索引表，通過索引表查詢相應(yīng)的主鍵，在通過主鍵查詢數(shù)據(jù)量的數(shù)據(jù)表；

2、如果對于有where 條件，又想走索引用limit的，必須設(shè)計一個索引，將where 放第一位，limit用到的主鍵放第2位，而且只能select 主鍵！這樣能提高讀取速度

3、利用in：先通過where條件取得相應(yīng)的主鍵值，然后利用主鍵值查詢相應(yīng)的字段值。

使用游標(biāo)(cursor)分頁：

為讓mysql達(dá)到最佳查詢性能，我將分頁查詢改為cursor查詢方式：

select * from table where id > last_id limit 20 order by reply_id ASC;

上面的last_id為本頁最后一條記錄的id，這樣就能實現(xiàn)“下一頁”的查詢了，同理也可以實現(xiàn)“上一頁”的查詢。

cursor方式分頁只適合用于有順序的數(shù)據(jù)且不支持跳頁，我們可以建立自增ID或向數(shù)據(jù)表中增加有序字段：對于數(shù)據(jù)量大的項目，跳頁的作用也不大，完全可以使用篩選條件達(dá)到查找的目的。

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，謝謝大家對腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請查看下面相關(guān)鏈接