MySQL Limit性能優化及分頁數據性能優化詳解

MySQL Limit可以分段查詢數據庫數據，主要應用在分頁上。雖然現在寫的網站數據都是千條級別，一些小的的優化起的作用不大，但是開發就要做到極致，追求完美性能。下面記錄一些limit性能優化方法。

Limit語法：

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

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

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

支持 limit # offset # 語法：

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

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

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

不同數據量讀取數據效率比較：

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數據比較大的時候：

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做索引的結果。

如果用id作為數據表的主鍵：

select id from table limit 10000,10

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

Limit性能優化：

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值作為起始標識，然后利用它快速定位下100條數據；而第2句是只取了最后一條記錄，然后取ID值作為起始標識定位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都是遞增。

分頁數據性能優化：

1、對于數據量較大數據表，可以建立主鍵和索引字段建立索引表，通過索引表查詢相應的主鍵，在通過主鍵查詢數據量的數據表；

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

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

使用游標(cursor)分頁：

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

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

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

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

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對億速云的支持。如果你想了解更多相關內容請查看下面相關鏈接