一。前言
通常，我們分頁時怎么實現呢？
SELECT?*?FROM?table?ORDER?BY?id?LIMIT?1000,?10;
但是，數據量猛增以后呢？
SELECT?*?FROM?table?ORDER?BY?id?LIMIT?1000000,?10;
如上第二條查詢時很慢的，直接拖死。
最關鍵的原因mysql查詢機制的問題：
不是先跳過，后查詢；
而是先查詢，后跳過。(解釋如下)
什么意思？比如limit 100000,10,在找到需要的那10條時，先會輪詢經過前10W條數據，先回行查詢出前100000條的字段數據，然后發現沒用舍棄掉，直到最后找到需要的10條。
二。分析
limit offset,N, 當offset非常大時,效率極低,
原因是mysql并不是跳過offset行,然后單取N行,
而是取offset+N行,返回放棄前offset行,返回N行【同前邊說的先查詢，后跳過】.
效率較低,當offset越大時,效率越低
三。3條優化建議
1:從業務上去解決
辦法:不允許翻過100頁
以百度為例,一般翻頁到70頁左右.
?
2:不用offset,用條件查詢.
例:
mysql>?select?id,?from?lx_com?limit?5000000,10; +---------+--------------------------------------------+ |?id??????|?name???????????????????????????????????????| +---------+--------------------------------------------+ |?5554609?|溫泉縣人民政府供暖中心??????????| .................. |?5554618?|溫泉縣郵政鴻盛公司????????????????| +---------+--------------------------------------------+ 10?rows?in?set?(5.33?sec) ? mysql>?select?id,name?from?lx_com?where?id>5000000?limit?10; +---------+--------------------------------------------------------+ |?id??????|?name???????????????????????????????????????????????????| +---------+--------------------------------------------------------+ |?5000001?|南寧市嘉氏百貨有限責任公司????????????????| ................. |?5000002?|南寧市友達電線電纜有限公司????????????????| +---------+--------------------------------------------------------+ 10?rows?in?set?(0.00?sec)
現象：從5.3秒到不到100毫秒，查詢速度大大加快；但是數據結果卻不一樣
優點：利用where條件來避免掉先查詢后跳過的問題，而是條件縮小范圍，從而直接跳過。
存在問題: 有時有會發現用此方法與limitM,N，兩次的結果不一致[如上邊實例所展示]
原因:數據被物理刪除過,有空洞.
解決:數據不進行物理刪除(可以邏輯刪除).
最終在頁面上顯示數據時,邏輯刪除的條目不顯示即可.
(一般來說,大網站的數據都是不物理刪除的,只做邏輯刪除 ,比如 is_delete=1)
3:延遲索引.
非要物理刪除,還要用offset精確查詢,還不限制用戶分頁,怎么辦?
優化思路：
利用索引覆蓋，快速查詢出滿足條件的主鍵id;然后憑借主鍵id作為where條件，達到快速查詢。
（速度快在哪里？利用索引覆蓋不需要回行就可以快速查詢出滿足條件的id，時間節約在這里了）
我們現在必須要查,則只查索引,不查數據,得到id.再用id去查具體條目.?這種技巧就是延遲索引.
慢原因：
查詢100W條數據的id,name,m每次查詢回行拋棄，跨過100W后取到真正要的數據。【就是我們剛剛說的，先查詢，后跳過】
優化后快原理：
a.利用索引覆蓋先查詢出主鍵id，在索引上就拿到信息了，避免回行
b.找到主鍵后，根據已知的目標主鍵在查詢，避免跨大數據行去尋找，而是直接定位哪幾條數據直接查詢。
本方法即延遲索引查詢。
mysql>?select?id,name?from?lx_com?inner?join?(select?id?from?lx_com?limit?5000000,10)?as?tmp?using(id); +---------+-----------------------------------------------+ |?id??????|?name??????????????????????????????????????????| +---------+-----------------------------------------------+ |?5050425?|?隴縣河北鄉大談灣小學????????????????| ........ |?5050434?|?隴縣堎底下鎮水管站???????????????????| +---------+-----------------------------------------------+ 10?rows?in?set?(1.35?sec)
?
四。總結：
從方案上來說，肯定是方法一優先，從業務上去滿足是否要翻那么多頁。
如果業務要求，則用id>n limit m的方式來代替limit n,m,但缺點是不能有物理刪除
如果非有物理刪除有空缺不能用方法二，則用延遲索引法，本質是利用索引覆蓋先快速取出索引值，根據鎖定的目標的索引值。一次性去回行取值，效果很明顯。
?以上就是Mysql優化-大數據量下的分頁策略的內容，更多相關內容請關注PHP中文網（www.php.cn）！
