淺談mysql使用limit分頁(yè)優(yōu)化方案的實(shí)現(xiàn)

Mysql limit分頁(yè)語(yǔ)句用法

與Oracle和MS SqlServer相比，mysql的分頁(yè)方法簡(jiǎn)單的讓人想哭。

--語(yǔ)法：

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

--舉例：

select * from table limit 5; --返回前5行

select * from table limit 0,5; --同上，返回前5行

select * from table limit 5,10; --返回6-15行

如何優(yōu)化limit

當(dāng)一個(gè)查詢語(yǔ)句偏移量offset很大的時(shí)候，如select * from table limit 10000,10 , 最好不要直接使用limit，而是先獲取到offset的id后，再直接使用limit size來(lái)獲取數(shù)據(jù)。效果會(huì)好很多。

如：

select * From customers Where customer_id >=(
select customer_id From customers Order By customer_id limit 10000,1
) limit 10;

一、測(cè)試實(shí)驗(yàn)

mysql分頁(yè)直接用limit start, count分頁(yè)語(yǔ)句：

select * from product limit start, count

當(dāng)起始頁(yè)較小時(shí)，查詢沒(méi)有性能問(wèn)題，我們分別看下從10， 100， 1000， 10000開(kāi)始分頁(yè)的執(zhí)行時(shí)間（每頁(yè)取20條），如下：

select * from product limit 10, 20 0.016秒
select * from product limit 100, 20 0.016秒
select * from product limit 1000, 20 0.047秒
select * from product limit 10000, 20 0.094秒

我們已經(jīng)看出隨著起始記錄的增加，時(shí)間也隨著增大， 這說(shuō)明分頁(yè)語(yǔ)句limit跟起始頁(yè)碼是有很大關(guān)系的，
那么我們把起始記錄改為40w看下（也就是記錄的一半左右）

select * from product limit 400000, 20 3.229秒

再看我們?nèi)∽詈笠豁?yè)記錄的時(shí)間

select * from product limit 866613, 20 37.44秒

像這種分頁(yè)最大的頁(yè)碼頁(yè)顯然這種時(shí)間是無(wú)法忍受的。

從中我們也能總結(jié)出兩件事情：

• limit語(yǔ)句的查詢時(shí)間與起始記錄的位置成正比。
• mysql的limit語(yǔ)句是很方便，但是對(duì)記錄很多的表并不適合直接使用。

二、 對(duì)limit分頁(yè)問(wèn)題的性能優(yōu)化方法

2.1 利用表的覆蓋索引來(lái)加速分頁(yè)查詢

我們都知道，利用了索引查詢的語(yǔ)句中如果只包含了那個(gè)索引列（覆蓋索引），那么這種情況會(huì)查詢很快。

因?yàn)槔盟饕檎矣袃?yōu)化算法，且數(shù)據(jù)就在查詢索引上面，不用再去找相關(guān)的數(shù)據(jù)地址了，這樣節(jié)省了很多時(shí)間。

另外Mysql中也有相關(guān)的索引緩存，在并發(fā)高的時(shí)候利用緩存就效果更好了。

在我們的例子中，我們知道id字段是主鍵，自然就包含了默認(rèn)的主鍵索引?，F(xiàn)在讓我們看看利用覆蓋索引的查詢效果如何：
這次我們之間查詢最后一頁(yè)的數(shù)據(jù)（利用覆蓋索引，只包含id列），如下：

select id from product limit 866613, 20 

查詢時(shí)間為0.2秒，相對(duì)于查詢了所有列的37.44秒，提升了大概100多倍的速度。

那么如果我們也要查詢所有列，有兩種方法，

id>=的形式：

SELECT * FROM product 
WHERE ID > =(select id from product limit 866613, 1) limit 20

查詢時(shí)間為0.2秒，簡(jiǎn)直是一個(gè)質(zhì)的飛躍啊。

利用join

SELECT * FROM product a 
JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

查詢時(shí)間也很短，贊！

其實(shí)兩者用的都是一個(gè)原理嘛，所以效果也差不多。

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。