高效的mysql分頁(yè)方法及原理
首先看一下分頁(yè)的基本原理：
mysql> explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20G ***************** 1. row ************** id: 1 select_type: SIMPLE table: message type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 10020 Extra: 1 row in set (0.00 sec)
limit 10000,20的意思掃描滿足條件的10020行，扔掉前面的10000行，返回最后的20行，問(wèn)題就在這里，如果是limit 100000,100，需要掃描100100行，在一個(gè)高并發(fā)的應(yīng)用里，每次查詢需要掃描超過(guò)10W行，性能肯定大打折扣。文中還提到limit n性能是沒(méi)問(wèn)題的，因?yàn)橹粧呙鑞行。
文中提到一種”clue”的做法，給翻頁(yè)提供一些”線索”，比如還是SELECT * FROM message ORDER BY id DESC，按id降序分頁(yè)，每頁(yè)20條，當(dāng)前是第10頁(yè)，當(dāng)前頁(yè)條目id最大的是9527，最小的是9500，如果我們只提供”上一頁(yè)”、”下一頁(yè)”這樣的跳轉(zhuǎn)（不提供到第N頁(yè)的跳轉(zhuǎn)），那么在處理”上一頁(yè)”的時(shí)候SQL語(yǔ)句可以是：
SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;
處理”下一頁(yè)”的時(shí)候SQL語(yǔ)句可以是：
SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20;
不管翻多少頁(yè)，每次查詢只掃描20行。
缺點(diǎn)是只能提供”上一頁(yè)”、”下一頁(yè)”的鏈接形式，但是我們的產(chǎn)品經(jīng)理非常喜歡”<上一頁(yè) 1 2 3 4 5 6 7 8 9 下一頁(yè)>”這樣的鏈接方式，怎么辦呢？
如果LIMIT m,n不可避免的話，要優(yōu)化效率，只有盡可能的讓m小一下，我們擴(kuò)展前面的”clue”做法，還是SELECT * FROM message ORDER BY id DESC，按id降序分頁(yè)，每頁(yè)20條，當(dāng)前是第10頁(yè)，當(dāng)前頁(yè)條目id最大的是9527，最小的是9500，比如要跳到第8頁(yè)，我看的SQL語(yǔ)句可以這樣寫：
SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20,20;
跳轉(zhuǎn)到第13頁(yè)：
SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 40,20;
原理還是一樣，記錄住當(dāng)前頁(yè)id的最大值和最小值，計(jì)算跳轉(zhuǎn)頁(yè)面和當(dāng)前頁(yè)相對(duì)偏移，由于頁(yè)面相近，這個(gè)偏移量不會(huì)很大，這樣的話m值相對(duì)較小，大大減少掃描的行數(shù)。其實(shí)傳統(tǒng)的limit m,n，相對(duì)的偏移一直是第一頁(yè)，這樣的話越翻到后面，效率越差，而上面給出的方法就沒(méi)有這樣的問(wèn)題。
注意SQL語(yǔ)句里面的ASC和DESC，如果是ASC取出來(lái)的結(jié)果，顯示的時(shí)候記得倒置一下。
已在60W數(shù)據(jù)總量的表中測(cè)試，效果非常明顯。
- 默默小談PHP&MYSQL分頁(yè)原理及實(shí)現(xiàn)
- 數(shù)據(jù)庫(kù)分頁(yè)查詢方法
- php 分頁(yè)原理詳解
- PHP 分頁(yè)原理分析，大家可以看看
- php入門學(xué)習(xí)知識(shí)點(diǎn)二 PHP簡(jiǎn)單的分頁(yè)過(guò)程與原理
- sqlserver2005使用row_number() over分頁(yè)的實(shí)現(xiàn)方法
- SQL SERVER 2008 中三種分頁(yè)方法與比較
- mysql分頁(yè)原理和高效率的mysql分頁(yè)查詢語(yǔ)句
- jquery實(shí)現(xiàn)html頁(yè)面 div 假分頁(yè)有原理有代碼
- 分頁(yè)技術(shù)原理與實(shí)現(xiàn)之分頁(yè)的意義及方法（一）
相關(guān)文章
window10中mysql8.0修改端口port不生效的解決方法
mysql配置文件默認(rèn)位置,端口號(hào)等信息需要在my.ini文件中修改,若修改安裝位置的my-default文件文件或新建my.ini文件是不生效的,本文主要介紹了window10中mysql8.0修改端口port不生效的解決方法,感興趣的可以了解一下2023-11-11
JOIN是一種非常常見(jiàn)的操作，用于將兩個(gè)或多個(gè)表中的數(shù)據(jù)合并到一個(gè)結(jié)果集中。MySQL支持多種JOIN類型，本文通過(guò)代碼示例詳細(xì)介紹了Join的使用優(yōu)化，有需要的小伙伴可以參考閱讀2023-04-04
這篇文章主要介紹了Mysql索引性能優(yōu)化問(wèn)題解決方案,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-09-09
clickhouse復(fù)雜時(shí)間格式的轉(zhuǎn)換方式
這篇文章主要介紹了clickhouse復(fù)雜時(shí)間格式的轉(zhuǎn)換方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12
使用mysql記錄從url返回的http GET請(qǐng)求數(shù)據(jù)操作
這篇文章主要介紹了使用mysql記錄從url返回的http GET請(qǐng)求數(shù)據(jù)操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
MySQL 5.7及8.0版本數(shù)據(jù)庫(kù)的root密碼遺忘的解決方法
這篇文章主要介紹了MySQL 5.7及8.0版本數(shù)據(jù)庫(kù)的root密碼遺忘的解決辦法,本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友參考下吧2019-12-12
這篇文章主要介紹了MySQL中replace into語(yǔ)句的用法詳解,本文講解了replace into語(yǔ)句的多種寫法,replace into語(yǔ)句的作用等內(nèi)容,需要的朋友可以參考下2014-08-08
在使用MYSQL時(shí)遇到中文亂碼的問(wèn)題,表現(xiàn)為插入數(shù)據(jù)后查詢時(shí)輸出為??(亂碼),下面這篇文章主要給大家介紹了關(guān)于mysql5.5中文亂碼問(wèn)題解決的有用方法,文中通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2022-05-05
mysql報(bào)錯(cuò)RSA?private?key?file?not?found的解決方法
當(dāng)MySQL報(bào)錯(cuò)RSA?private?key?file?not?found時(shí)，可能是由于MySQL的RSA私鑰文件丟失或者損壞導(dǎo)致的，此時(shí)可以重新生成RSA私鑰文件，以解決這個(gè)問(wèn)題2023-06-06
MySQL按天分組統(tǒng)計(jì)一定時(shí)間內(nèi)的數(shù)據(jù)實(shí)例(沒(méi)有數(shù)據(jù)補(bǔ)0)
我們?cè)谟肕ysql制作數(shù)據(jù)可視化圖表時(shí)候,經(jīng)常需要按照天對(duì)數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì),下面這篇文章主要給大家介紹了關(guān)于MySQL按天分組統(tǒng)計(jì)一定時(shí)間內(nèi)的數(shù)據(jù),沒(méi)有數(shù)據(jù)補(bǔ)0的相關(guān)資料,需要的朋友可以參考下2023-03-03