MySQL高效分頁(yè)解決方案集分享

2024-07-24 12:47:53

字體：大中小

供稿：網(wǎng)友

一，最常見MYSQL最基本的分頁(yè)方式：
復(fù)制代碼代碼如下:

select * from content order by id desc limit 0, 10

在中小數(shù)據(jù)量的情況下，這樣的SQL足夠用了，唯一需要注意的問題就是確保使用了索引。隨著數(shù)據(jù)量的增加，頁(yè)數(shù)會(huì)越來越多，查看后幾頁(yè)的SQL就可能類似：
復(fù)制代碼代碼如下:

select * from content order by id desc limit 10000, 10

一言以蔽之，就是越往后分頁(yè)，LIMIT語(yǔ)句的偏移量就會(huì)越大，速度也會(huì)明顯變慢。
此時(shí)，我們可以通過2種方式：
一，子查詢的分頁(yè)方式來提高分頁(yè)效率，飄易用的SQL語(yǔ)句如下：
復(fù)制代碼代碼如下:

SELECT * FROM `content` WHERE id (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) ORDER BY id desc LIMIT $pagesize

為什么會(huì)這樣呢？因?yàn)樽硬樵兪窃谒饕贤瓿傻模胀ǖ牟樵儠r(shí)在數(shù)據(jù)文件上完成的，通常來說，索引文件要比數(shù)據(jù)文件小得多，所以操作起來也會(huì)更有效率。（via）通過explain SQL語(yǔ)句發(fā)現(xiàn)：子查詢使用了索引！
復(fù)制代碼代碼如下:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY content range PRIMARY PRIMARY 4 NULL 6264 Using where
2 SUBQUERY content index NULL PRIMARY 4 NULL 27085 Using index

經(jīng)過飄易的實(shí)測(cè)，使用子查詢的分頁(yè)方式的效率比純LIMIT提高了14-20倍！
二，JOIN分頁(yè)方式
復(fù)制代碼代碼如下:

select * FROM `content` AS t1
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2
WHERE t1.id

經(jīng)過我的測(cè)試，join分頁(yè)和子查詢分頁(yè)的效率基本在一個(gè)等級(jí)上，消耗的時(shí)間也基本一致。explain SQL語(yǔ)句：
復(fù)制代碼代碼如下:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY system NULL NULL NULL NULL 1
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index

三，使用MYSQL的FOUND_ROWS()函數(shù)
Mysql FOUND_ROWS() 函數(shù)結(jié)合SQL_CALC_FOUND_ROWS在SELECT中可以得到兩個(gè)結(jié)果：
1. 得到Limit的內(nèi)容
2. 得到去除Limit以后所有行數(shù)
SELECT語(yǔ)句中經(jīng)常可能用LIMIT限制返回行數(shù)。有時(shí)候可能想要知道如果沒有LIMIT會(huì)返回多少行，但又不想再執(zhí)行一次相同語(yǔ)句。那么，在SELECT查詢中包含SQL_CALC_FOUND_ROWS選項(xiàng)，然后執(zhí)行FOUND_ROWS()就可以了：
復(fù)制代碼代碼如下:

select SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

其中SQL_CALC_FOUND_ROWS 告訴Mysql將sql所處理的行數(shù)記錄下來，F(xiàn)OUND_ROWS() 則取到了這個(gè)紀(jì)錄。雖然也是兩個(gè)語(yǔ)句，但是只執(zhí)行了一次主查詢，所以效率比原來要高很多。

上一篇：使用Linux的Shell腳本定時(shí)處理MySQL超時(shí)

下一篇：mysql SELECT FOR UPDATE語(yǔ)句使用示例