mysql分頁查詢實例講解

limit子句可以被用于強制 select 語句返回指定的記錄數。limit 接受一個或兩個數字參數。參數必須是一個整數常量。如果給定兩個參數,第一個參數指定第一個返回記錄行的偏移量,第二個參數指定返回記錄行的最大數目。初始記錄行的偏移量是 0(而不是 1)。下面,我們針對特例對mysql分頁查詢進行總結。

mysql提供分頁的功能:

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

最簡單的用法就是:

select * from table limit ?,?

這種是最簡單的limit分頁查詢。配合where條件使用:

select * from table where column > ? order by id limit ?,?

上邊這種情況,建議在column和id建立復合索引比較好。 ?
以上兩種情況,對于小數據量分頁查詢時,這樣的sql就足夠用了。但是對于百萬級以上的數據表,如果使用上邊的sql 的話,越往后limit語句的偏移量越來越大,查詢就會變得越來越慢。類似于:

select * from `user` where `cate`='陜西' order by id limit 100000,10

為了避免這種查詢,我們可以通過子查詢的方式來提高查詢效率。

select * from `user` where id >=(select * from `user` where `cate`='陜西' order by id limit 100000,1) and `cate`='陜西' limit 10

通過explain我們可以看出直接limit查詢和通過子查詢的差距:

直接limit查詢:

type possible_keys key key_len ref rows Extra
ALL (NULL) (NULL) (NULL) (NULL) 4076607

子查詢分頁查詢:

type possible_keys key key_len ref rows Extra
PRIMARY range PRIMARY PRIMARY 4 2038331 Using where
SUBQUERY index (NULL) PRIMARY 4 4076663 Using index

可以看出,通過子查詢的方式,子查詢是在索引上進行的,而普通的查詢是在數據文件上進行的。 通常來說,索引文件要比數據文件小的多,所以操作索引文件更直接高效。

此外,還可以通過join分頁方式

SELECT * FROM `user` AS t1   JOIN (SELECT id FROM `user` ORDER BY id LIMIT 100000, 1) AS t2   WHERE t1.id <= t2.id ORDER BY t1.id LIMIT 10;

join分頁和子查詢分頁的效率基本在一個等級上。(但是,子查詢需要在內存中建立臨時表,查詢完畢后,MySQL需要撤銷這些臨時表。通過join可以避免這種情況)在分頁查詢前,可以進行判斷,如果是在限定頁數內,就使用基本分頁查詢,大于則使用子查詢分頁處理。

相關推薦:

mysql分頁性能探索

mysql分頁性能探索

mysql分頁性能探索

? 版權聲明
THE END
喜歡就支持一下吧
點贊10 分享