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

Limit語(yǔ)法：

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

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

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

支持 limit # offset # 語(yǔ)法：

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

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

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

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

1.offset比較小的時(shí)候：

select * from table limit 5,10

多次運(yùn)行，時(shí)間保持在0.0004-0.0005之間

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

多次運(yùn)行，時(shí)間保持在0.0005-0.0006之間。所有，在offset較小的時(shí)候，直接使用limit效率會(huì)高點(diǎn)！

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

select * from table limit 10000,10

多次運(yùn)行，時(shí)間保持在0.0187秒左右。

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

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

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

select id from table limit 10000,10

查詢花費(fèi)時(shí)間大概在0.04秒，這是因?yàn)橛胕d主鍵作為索引的結(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句會(huì)快點(diǎn)。因?yàn)榈?句是先取前90001條記錄，取其中最大一個(gè)ID值作為起始標(biāo)識(shí)，然后利用它快速定位下100條數(shù)據(jù)；而第2句是只取了最后一條記錄，然后取ID值作為起始標(biāo)識(shí)定位100條記錄。第2句可簡(jiǎn)寫(xiě)成：

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

省去Max運(yùn)算，一般ID都是遞增。

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

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

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

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

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

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

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

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

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

總結(jié)

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

最新評(píng)論