MySQL 通過 limit 實現(xiàn)分頁查詢熟尉。limit 接收一個或兩個整數(shù)型參數(shù)。如果是兩個參數(shù)膀钠，第一個指定返回記錄行的偏移量，第二個指定返回記錄行的最大數(shù)目训枢。初始記錄行的偏移量是 0托修。為了與 PostgreSQL 兼容，limit 也支持limit a offset b【a：取的記錄數(shù)恒界；b：索引】睦刃。

一、分頁查詢

客戶端通過傳遞 start(頁碼)十酣，pageSize(每頁顯示的條數(shù))兩個參數(shù)去分頁查詢數(shù)據(jù)庫表中的數(shù)據(jù)涩拙。MySql 數(shù)據(jù)庫提供的分頁函數(shù) limit m,n 用法和實際需求不切合，所以就需要根據(jù)實際情況去改寫適合分頁的語句耸采。

1??查詢第1條到第10條的數(shù)據(jù)select * from table limit 0,10;
--->對應需求就是查詢第一頁的數(shù)據(jù)：select * from table limit (1-1)*10,10;

2??查詢第11條到第20條的數(shù)據(jù)select * from table limit 10,10;
--->對應需求就是查詢第二頁的數(shù)據(jù)：select * from table limit (2-1)*10,10;

3??查詢第21條到第30條的數(shù)據(jù)select * from table limit 20,10;
--->對應需求就是查詢第三頁的數(shù)據(jù)：select * from table limit (3-1)*10,10;

由此兴泥，得出符合需求的分頁 sql 格式是：select * from table limit (start-1)*pageSize,pageSize;其中 start 是頁碼，pageSize 是每頁顯示的條數(shù)虾宇。

二搓彻、性能問題

對于小的偏移量，直接用 limit 查詢沒有什么問題嘱朽。隨著數(shù)據(jù)量的增大旭贬，越往后分頁，limit 語句的偏移量越大搪泳，速度也會明顯變慢稀轨。

優(yōu)化思想：
避免數(shù)據(jù)量大時掃描過多的記錄

解決：
子查詢的分頁方式或者 JOIN 分頁方式。JOIN 分頁和子查詢分頁的效率基本在一個等級上岸军，消耗的時間也基本一致奋刽。

一般 MySQL 的主鍵是自增的數(shù)字類型，這種情況下可以使用下面的方式進行優(yōu)化艰赞。以真實的生產(chǎn)環(huán)境的6萬條數(shù)據(jù)的一張表為例佣谐，比較一下優(yōu)化前后的查詢耗時：

-- 傳統(tǒng) limit，文件掃描
select * from table order by id limit 50000,2;
受影響的行: 0
時間:  0.171s

-- 子查詢方式方妖，索引掃描
select * from table
where id >= (select id from table order by id limit 50000 , 1)
limit 2;
受影響的行: 0
時間: 0.035s

-- JOIN 分頁方式
select * from table as t1
join (select id from table order by id limit 50000, 1) as t2
where t1.id <= t2.id order by t1.id limit 2;
受影響的行: 0
時間: 0.036s

可以看到經(jīng)過優(yōu)化性能提高了很多倍台谍。

優(yōu)化原理：
子查詢是在索引上完成的，而普通的查詢是在數(shù)據(jù)文件上完成的。通常來說趁蕊，索引文件要比數(shù)據(jù)文件小得多，所以操作起來也會更有效率仔役。因為要取出所有字段內容掷伙，普通查詢需要跨越大量數(shù)據(jù)塊并取出，而另一種方式直接根據(jù)索引字段定位后又兵，才取出相應內容任柜，效率自然大大提升。因此沛厨，對 limit 的優(yōu)化宙地，是避免直接使用 limit，而是首先獲取到 offset 的 id逆皮，然后直接使用 limit size 來獲取數(shù)據(jù)宅粥。

在實際項目使用，可以利用類似策略模式的方式去處理分頁电谣。例如秽梅，每頁 100 條數(shù)據(jù)，判斷如果是 100 頁以內剿牺，就使用最基本的分頁方式企垦；如果大于 100，則使用子查詢的分頁方式晒来。

三钞诡、limit 優(yōu)化。使用合理的分頁方式以提高分頁的效率

使用 limit 實現(xiàn)分頁邏輯湃崩。不僅提高了性能荧降，同時減少了不必要的數(shù)據(jù)庫和應用間的網(wǎng)絡傳輸。
查詢結果只有一條或者只要最大/最小一條記錄竹习，建議用 limit 1誊抛。這是為了使 explain 中 type 列達到 const 類型≌埃“l(fā)imit 1”可以避免全表掃面拗窃，只要找到了對應的一條記錄，就不會繼續(xù)向下掃描了泌辫，效率將會大大提高随夸。當然，如果查詢字段是唯一索引的話震放，沒必要加 limit 1宾毒，因為 limit 的存在主要就是為了防止全表掃描，從而提高性能殿遂，如果一個語句本身可以預知不用全表掃描诈铛，有沒有 limit 乙各，性能的差別并不大。
使用下面 SQL 語句做分頁的時候幢竹，隨著表數(shù)據(jù)量的增加耳峦，直接使用 limit 分頁查詢會越來越慢。

select id,name from product limit 89757, 20

優(yōu)化如下：可以取前一頁的最大行數(shù)的 id焕毫，然后根據(jù)這個最大的 id 來限制下一頁的起點蹲坷。此列中，上一頁最大的 id 是 89756邑飒。SQL 可以采用如下的寫法：

//方案一 ：返回上次查詢的最大記錄(偏移量)
select id,name from product where id> 89756 limit 20
//方案二：order by + 索引
select id,name from product order by id  limit 10000循签，10
//方案三：在業(yè)務允許的情況下限制頁數(shù)

理由如下：

當偏移量最大的時候，查詢效率就會越低疙咸，因為 MySQL 并非是跳過偏移量直接去取后面的數(shù)據(jù)县匠，而是先把偏移量+要取的條數(shù)，然后再把前面偏移量這一段的數(shù)據(jù)拋棄掉再返回的罕扎。
如果使用優(yōu)化方案一聚唐，返回上次最大查詢記錄(偏移量)，這樣可以跳過偏移量腔召，效率提升不少杆查。
方案二使用 order by+索引，也是可以提高查詢效率的臀蛛。
方案三的話亲桦，建議跟業(yè)務討論，有沒有必要查這么多的分頁浊仆。因為絕大多數(shù)用戶都不會往后翻太多頁客峭。
【強制】在代碼中寫分頁查詢邏輯時，若 count 為 0 應直接返回抡柿，避免執(zhí)行后面的分頁語句舔琅。

MySQL的limit分頁查詢及性能問題

MySQL的limit分頁查詢及性能問題

一、分頁查詢

二搓彻、性能問題

三钞诡、limit 優(yōu)化。使用合理的分頁方式以提高分頁的效率