化實戰(zhàn)：從LIMIT OFFSET到游標與延遲回表的完整方案)
去年我接手過一個訂單查詢接口單表數(shù)據(jù)已經(jīng)過億頁面還是最傳統(tǒng)的那種“下一頁”式分頁。用戶翻到第60頁之后接口響應時間直接從幾百毫秒跳到幾秒翻到100頁以后基本就是超時重試的節(jié)奏。我看數(shù)據(jù)庫監(jiān)控CPU不高IO不忙SQL執(zhí)行計劃也走了索引但就是慢得離譜。這套現(xiàn)象做后端的同學一定不陌生——這就是典型的深分頁問題。深分頁問題的本質(zhì)是LIMIT OFFSET這種訪問方式在數(shù)據(jù)量小的時候幾乎沒存在感一旦碰上億級數(shù)據(jù)越往后翻越災難。數(shù)據(jù)庫沒辦法“跳過”O(jiān)FFSET行只能把前面的行全部掃描出來再丟棄。這篇文章把我從慢SQL排查、方案選型到最終落地的一整套打法完整復盤一遍包括游標分頁、覆蓋索引延遲回表、基于緩存的有序ID分頁等主流方案以及一路踩出來的坑。適合后端開發(fā)、DBA以及所有被“數(shù)據(jù)量一大分頁就卡”折磨過的同學對照參考。1. 深分頁到底慢在哪里從執(zhí)行計劃到量化分析1.1 一條“看起來沒毛病”的SQL先看一條再常見不過的分頁SQLSELECT id, order_no, status, created_at FROM orders WHERE user_type 1 ORDER BY id DESC LIMIT 20 OFFSET 1000000;邏輯很清楚把用戶類型為1的訂單按主鍵倒序從第1000001行開始取20行。很多人以為OFFSET會讓數(shù)據(jù)庫直接跳到第1000001行去讀實際上MySQL根本沒有這個能力。它得先從滿足條件的起始位置開始一行一行掃描累計到1000000行之后把前面的全部丟掉只返回最后這20行。也就是說你最終只拿到20條數(shù)據(jù)數(shù)據(jù)庫實際觸碰的行數(shù)卻是一百萬級的。如果這張表只有一個主鍵索引執(zhí)行計劃里的rows基本都是百萬級估算值Extra里大概率出現(xiàn)Using where如果user_type上剛好建了二級索引回表次數(shù)更是被放大。深分頁慢的根本原因就在這里掃描范圍不會因為你只返回20行而縮小頁數(shù)越深數(shù)據(jù)庫做無用功越多。1.2 并不只是OFFSET的鍋三個隱藏成本深分頁耗時其實是三層成本疊加的結(jié)果。第一層是掃描行數(shù)放大OFFSET1000000時至少100萬條索引記錄要被遍歷這是無法繞開的基礎開銷。第二層是回表隨機IO如果SELECT需要取二級索引之外的字段比如order_no、status、created_at每掃到一行滿足條件的記錄都要回聚簇索引再查一次完整行。掃描行數(shù)一大回表次數(shù)跟著翻倍而索引葉子節(jié)點在磁盤上是離散分布的每一次回表都是一次隨機IO代價相當大。第三層是排序和臨時表。當ORDER BY字段和索引順序不一致或查詢里帶了GROUP BY、DISTINCTMySQL可能走filesort甚至生成臨時表。深分頁時臨時表會變得很大內(nèi)存裝不下就往磁盤寫整個查詢就徹底回到了“慢”的領域。可以類比成在圖書館里按編號找書管理員必須從第一本開始數(shù)數(shù)到100萬本才停下來如果每數(shù)到一本還要拆開封面看一眼自然更慢。幾個隱藏成本疊加之后OFFSET稍大就會產(chǎn)生比業(yè)務邏輯多幾個數(shù)量級的開銷。1.3 模擬數(shù)據(jù)從百萬到億級的變化曲線為了直觀我之前在一臺普通虛擬機上做過一組相對測試單表按自增主鍵寫入不重復數(shù)據(jù)模擬不同數(shù)據(jù)量級下的深分頁表現(xiàn)。大致的相對耗時趨勢如下表內(nèi)數(shù)據(jù)規(guī)模LIMIT 20 OFFSET 0LIMIT 20 OFFSET 10000LIMIT 20 OFFSET 1000000100萬行3ms12ms320ms5000萬行4ms85ms3700ms1億行6ms180ms6800ms這組數(shù)字受內(nèi)存緩存、磁盤類型、索引設計影響很大不看絕對值只看相對變化OFFSET在1萬以內(nèi)時哪怕億級表也能在百毫秒級完成一旦OFFSET到百萬級耗時直接跳到秒級甚至出現(xiàn)接口超時。并且這個變化并不是均勻的臨界點通常出現(xiàn)在數(shù)據(jù)量突破內(nèi)存熱數(shù)據(jù)窗口、或回表頻繁觸發(fā)磁盤IO的時候曲線會突然變陡。1.4 什么樣的業(yè)務最容易中招最容易踩深分頁的不是那些“看起來查詢很復雜”的系統(tǒng)恰恰是結(jié)構(gòu)簡單的流水型業(yè)務訂單列表、支付流水、行為日志、消息記錄、操作審計。這類表只增不改數(shù)據(jù)量天然大頁面又習慣按時間倒序展示。用戶只要愿意往后翻OFFSET漲到幾十萬只是時間問題。我見過一個運營后臺單表不到三千萬行用戶為了找一個月前的單據(jù)硬是點了七十多次“下一頁”最后把頁面點死了。這類行為一旦沒有約束深分頁就是遲早的事和SQL本身寫得多漂亮關系不大。2. 四類優(yōu)化方案全景思路與選型邏輯2.1 一張表看懂四類方案先給一個全景對比方便在動手前建立整體認知方案核心思路翻頁能力主要代價推薦場景游標分頁Keyset用WHERE id last_id代替 OFFSET僅上一頁/下一頁不能任意跳頁排序字段受限APP信息流、實時列表覆蓋索引延遲回表子查詢先在索引上拿主鍵再回表取完整行支持任意跳頁SQL改寫成本高OFFSET仍有索引掃描代價后管列表、報表頁ID區(qū)間分頁按自增主鍵或唯一序號分段支持任意跳頁主鍵空洞、條件組合時易失效數(shù)據(jù)單調(diào)寫入無刪除場景緩存有序ID預熱ID列表到Redis ZSet按score分頁支持任意跳頁一致性維護成本高內(nèi)存占用大消息中心、報表、活動頁先理解一個關鍵點所有方案的本質(zhì)都是盡量把“掃描丟棄”變成“直接定位”。只要你還在用LIMIT OFFSET數(shù)據(jù)庫就無法逃避“把前面所有頁重復讀一遍”這件事只要換成游標、區(qū)間或緩存你就是在主動告訴數(shù)據(jù)庫“從哪開始”而不是讓它自己數(shù)到那兒。2.2 游標分頁把“數(shù)頁碼”變成“記路標”游標分頁是最接近“物理正確”的方案。LIMIT OFFSET的痛點是讓數(shù)據(jù)庫從起點開始數(shù)頁碼游標分頁則讓客戶端每次翻頁帶上上一頁最后一條記錄的游標值通常是主鍵ID。服務端拿到游標后直接用WHERE id last_id去定位不再管前面有多少頁。相當于你爬山時在關鍵路口做了標記第二次不需要從山腳重新走直接去標記處繼續(xù)前進。-- 傳統(tǒng)深分頁第N頁 SELECT ... FROM orders WHERE user_type 1 ORDER BY id DESC LIMIT 20 OFFSET 10000; -- 游標分頁從上一頁最后一條 id80001 開始 SELECT ... FROM orders WHERE user_type 1 AND id 80001 ORDER BY id DESC LIMIT 20;后者執(zhí)行計劃里的rows基本是“滿足條件且停在80001之前”的數(shù)據(jù)量不再隨頁碼變大而膨脹。不過游標分頁有一票否決條件如果產(chǎn)品經(jīng)理要求回到頂部能直接點“第12345頁”這個方案基本只能作罷。這類交互常見于后管系統(tǒng)前端需要跳頁后端的游標就難以為繼。另外要注意游標分頁依賴排序字段穩(wěn)定且有索引不能隨便排。2.3 覆蓋索引延遲回表保住跳頁能力當業(yè)務必須支持跳頁覆蓋索引加延遲回表是我最常用的一招?！案采w索引”是指一個二級索引已經(jīng)把查詢要讀的列都包含了查詢只在索引頁里就能拿到結(jié)果Extra顯示為Using index不需要回表。“延遲回表”則是在此基礎上用子查詢先在索引里掃描出目標頁的主鍵ID集合再用這個集合去聚簇索引里取完整數(shù)據(jù)行。深分頁場景里收益特別好理解原查詢可能要回表100萬次改寫后無論OFFSET多大回表只發(fā)生在最終命中的那一頁數(shù)據(jù)上比如20次。但要注意這個方案不是讓數(shù)據(jù)庫不掃描了而是把掃描對象從“完整行回表”降級成“窄索引頁”整體成本低一個數(shù)量級。適合后臺管理列表這種必須支持跳頁、又不至于讓用戶無限制翻幾千頁的場景。2.4 三種補充思路與“選型三問”除了上面兩種還有三個方向也值得納入方案池。一是ID區(qū)間分頁利用自增主鍵或唯一有序編號做BETWEEN實現(xiàn)任意跳頁但它對主鍵空洞敏感一旦有刪除、批量導入產(chǎn)生了坑頁碼就會出現(xiàn)空白或錯位更適合單調(diào)寫入、基本不做物理刪除的表。二是冷熱分離/歸檔把N個月之前的歷史數(shù)據(jù)遷到歷史表線上主表只保留熱數(shù)據(jù)這不是SQL優(yōu)化而是數(shù)據(jù)治理但往往最能治本。三是引入檢索組件當查詢條件特別多、各種字段任意組合時SQL索引很難覆蓋所有場景可以把篩選維度同步到搜索引擎用倒排索引解決但代價是引入額外組件和同步鏈路。無論選哪條路動手前一定要回答三個問題業(yè)務能不能接受“只有上一頁/下一頁”排序字段是否穩(wěn)定能不能提煉出唯一的復合排序鍵數(shù)據(jù)能不能被業(yè)務維度先過濾一層比如只看自己的訂單、只看最近三十天這三個問題決定你該用游標、延遲回表還是緩存方案也決定這個優(yōu)化能不能活過需求評審。3. 實操落地從SQL改造到緩存方案的完整記錄3.1 落地前先摸清三張底牌直接改SQL前我習慣先花半天把三件事搞清楚。第一是表結(jié)構(gòu)和索引現(xiàn)狀主鍵是自增還是雪花ID排序字段上有沒有聯(lián)合索引哪些列經(jīng)常出現(xiàn)在SELECT里。第二是真實慢SQL打開慢日志把分頁接口在生產(chǎn)上的實際SQL撈出來別用開發(fā)環(huán)境的“假數(shù)據(jù)”推演。第三是業(yè)務約束確認前端翻頁交互、最大可接受的翻頁深度以及跳頁、上一頁這些能力是否必須保留。這三張底牌決定方案方向。比如一張訂單表主鍵是雪花ID、天然時間有序那么游標分頁幾乎首選但如果業(yè)務必須在列表頂部顯示“共多少頁”游標方案基本沒戲得回到延遲回表。業(yè)務約束不先對齊技術方案再漂亮也落不了地。另外排查出慢SQL后要把基線記下來優(yōu)化前典型耗時、rows_examined、rows_sent優(yōu)化后同樣記錄一遍后面驗證效果才有依據(jù)。3.2 改造一游標分頁的SQL與代碼實現(xiàn)這里給一個可運行的完整思路用Python偽代碼說明換Java、Go、C#都是一樣的邏輯# 約定每頁20條 def list_orders(user_id, last_idNone, limit20): cond user_id %s params [user_id] if last_id: cond AND id %s params.append(last_id) sql f SELECT id, order_no, status, created_at FROM orders WHERE {cond} ORDER BY id DESC LIMIT %s params.append(limit) rows db.query(sql, params) # 下一頁游標取當前頁最后一條記錄的id next_cursor rows[-1][id] if rows else None return rows, next_cursor接口返回體里帶上next_cursor前端點“加載更多”時把上一頁返回的next_cursor回傳。第一頁沒有游標傳空即可。這里有一個重要前提表上必須有(user_id, id)聯(lián)合索引否則WHERE user_id? AND id ?有可能走全表掃描優(yōu)化等于白做。執(zhí)行計劃檢查到key包含user_id和id時這條SQL的耗時才會隨數(shù)據(jù)增長保持平穩(wěn)。如果業(yè)務要求按創(chuàng)建時間倒序而不是按主鍵倒序游標要升級成二元組。因為同一毫秒內(nèi)可能會出現(xiàn)多條訂單created_at不唯一只用時間戳做游標必然漏數(shù)據(jù)。穩(wěn)妥寫法是用(created_at, id)作為復合游標SELECT id, order_no, status, created_at FROM orders WHERE user_id :uid AND (created_at :last_created_at OR (created_at :last_created_at AND id :last_id)) ORDER BY created_at DESC, id DESC LIMIT 20;聯(lián)合索引建為(created_at, id)。這個寫法的邊界判定更準確代價是SQL稍微復雜但這是值得的。3.3 改造二覆蓋索引延遲回表的SQL改造再來看必須支持跳頁的后臺列表場景。原SQL往往是這種SELECT id, order_no, status, created_at FROM orders WHERE status 1 ORDER BY id DESC LIMIT 1000000, 20;優(yōu)化后的寫法SELECT o.id, o.order_no, o.status, o.created_at FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY id DESC LIMIT 1000000, 20 ) t ON o.id t.id ORDER BY o.id DESC;前提是orders表上有(status, id)聯(lián)合索引。執(zhí)行流程拆開看子查詢在窄窄的二級索引頁里只拿id因為只需要id和status不需要回表即使掃描一百萬條索引記錄也遠比回表一百萬次來得快。INNER JOIN再去聚簇索引里取這20條完整數(shù)據(jù)回表次數(shù)降到20次。外層再排一次序保證最終順序穩(wěn)定。為了讓大家對收益有體感我在本機模擬數(shù)據(jù)上做過一次對比測試數(shù)據(jù)量壓在3000萬行左右同樣的OFFSET1000000兩種寫法的耗時和回表次數(shù)差異非常大大致整理如下表寫法索引使用回表次數(shù)模擬耗時SELECT * ... LIMIT 1000000, 20二級索引后逐行回表約100萬次5800ms覆蓋索引延遲回表子查詢使用覆蓋索引20次880ms需要說清楚這個方案不是銀彈。OFFSET仍然存在子查詢?nèi)匀灰獟呙璧揭话偃f行索引記錄只是避開了回表隨機IO。如果業(yè)務允許用戶翻到幾百萬頁索引掃描本身也會成為瓶頸。我的經(jīng)驗是后管系統(tǒng)加上最大OFFSET限制比如超過20000頁就提示縮小篩選范圍再配合延遲回表工程上既簡單又穩(wěn)妥。3.4 改造三基于Redis有序ID的分頁落地第三種方案適合讀多寫少、列表數(shù)據(jù)可以被預生成的場景比如報表、消息中心。核心思路是用一個后臺任務或異步回調(diào)把滿足條件的有序數(shù)據(jù)ID寫進Redis的ZSetmember是業(yè)務IDscore是排序值。前端分頁時直接在ZSet上按score區(qū)間取ID再拿著ID去數(shù)據(jù)庫批量查明細數(shù)據(jù)庫這邊就再也不會出現(xiàn)“深分頁慢查詢”的問題了。# 構(gòu)建score可以取毫秒時間戳member用業(yè)務ID ZADD orders:report:202501 1735689600000 xid_10000001 ZADD orders:report:202501 1735689601000 xid_10000002 # 第一頁按score從大到小取20條 ZREVRANGEBYSCORE orders:report:202501 inf 0 LIMIT 0 20 # 下一頁上一頁最后一條score為1735689601000取比它小的區(qū)間 ZREVRANGEBYSCORE orders:report:202501 1735689600999 0 LIMIT 0 20這里有一道細節(jié)題如果score只取時間戳同一毫秒內(nèi)可能有多條數(shù)據(jù)會出現(xiàn)邊界區(qū)間的重復或遺漏。更省心的做法是如果主鍵是雪花ID直接用ID做score因為雪花ID本身時間有序且唯一ZSet天然穩(wěn)定如果主鍵是自增ID但排序要按業(yè)務時間就要在score設計上保證唯一性或者在邊界判斷時處理同score成員。這個細節(jié)單獨抽出來講是因為我見過太多人在這里翻車。內(nèi)存方面也要提前估算。一個ZSet成員在Redis里的開銷并不低成員本身、score、跳表指針、字典對象加起來往往要幾十字節(jié)甚至上百字節(jié)。一個億級成員的ZSet內(nèi)存占用很容易到十幾GB甚至更高。所以我不建議把全量ID都塞進去更合理的是按用戶、月份、狀態(tài)拆key只緩存活躍窗口并給不活躍的key設置TTL自動淘汰。3.5 分頁參數(shù)與容量的實用建議最后給一組我自己常用的參考口徑。分頁大小列表頁單頁20到50條足夠后管報表可以放寬到100條但不建議更大因為單頁越大LIMIT之后的回表、序列化和傳輸成本全部被放大。游標字段類型優(yōu)先bigint或雪花IDvarchar主鍵也能用但比較和索引寬度都比整數(shù)差想用UUID作為游標基本不建議無序又寬索引性能和緩存友好度都差。容量估算可以用一個簡單公式單個分頁條目在Redis里按100字節(jié)粗算一個月5000萬條數(shù)據(jù)大約5GB內(nèi)存。如果你評估后覺得內(nèi)存成本過高就回到“按月拆key TTL”的思路讓活躍窗口固定在可控規(guī)模。不要試圖用一個ZSet扛住整張億級表的所有歷史那會把Redis也變成另一個“深分頁”。4. 高頻踩坑與排障實戰(zhàn)常見問題速查4.1 常見問題速查表把我在生產(chǎn)環(huán)境中遇到過的、以及朋友反饋過的高頻問題整理成一張速查表遇到類似現(xiàn)象可以直接對照現(xiàn)象可能原因排查動作游標分頁偶發(fā)少數(shù)據(jù)查詢期間有新數(shù)據(jù)插入游標邊界漂移接受最終一致性或改為快照分頁復合游標排序亂沒建(created_at, id)聯(lián)合索引EXPLAIN 看 key補索引延遲回表結(jié)果順序亂子查詢排序后外層未再排序外層加 ORDER BY覆蓋索引沒生效SELECT 里有回表字段或函數(shù)操作去掉冗余列避免函數(shù)包裹索引列加了 LIMIT 還是全表掃描條件字段沒走索引/隱式類型轉(zhuǎn)換檢查字段類型看 possible_keys緩存分頁出現(xiàn)幽靈ID源庫刪除后緩存未同步做異步刪除補償任務4.2 排查慢SQL的標準動作從慢日志到執(zhí)行計劃排查深分頁我有一套固定動作。先開慢日志把超過1秒的查詢抓出來SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SHOW VARIABLES LIKE slow_query_log_file;抓到慢SQL后用EXPLAIN看執(zhí)行計劃重點看type、rows、Extra這三列。type盡量是range或refrows要和實際數(shù)據(jù)量對得上Extra里出現(xiàn)Using filesort、Using temporary都要警惕。MySQL 8.0以上可以直接用EXPLAIN ANALYZE看每一步的耗時和讀取行數(shù)比全憑估算強太多。還有一個長期有效的手段記錄rows_examined和rows_sent的比值。如果一條分頁查詢讀了幾十萬行最終只返回20行rows_examined / rows_sent可能上萬這就是深分頁在“做無用功”的直接證據(jù)。這個比值可以作為分頁接口日常巡檢的指標數(shù)值超過一定閾值就自動告警。4.3 真實踩坑記錄四則實例第一個坑是游標方案被“總頁數(shù)”需求直接打死。原以為改完SQL就能上線結(jié)果產(chǎn)品要求在列表頂部顯示“共N頁”還說這是運營長期習慣。游標分頁根本不知道總頁數(shù)因為它從頭到尾沒有數(shù)過OFFSET。最后只能退回延遲回表并把最大翻頁深度壓到用戶可接受的范圍。第二個坑是排序字段不唯一導致重復數(shù)據(jù)。某個列表業(yè)務要求按created_at倒序我一開始只用created_at做游標結(jié)果同一秒內(nèi)創(chuàng)建的多條訂單下一頁用created_at last_created_at就把同秒、ID更大的那批漏掉了。改成(created_at, id)二元組游標用id做二級排序和邊界判等問題才真正解決。第三個坑是延遲回表優(yōu)化后結(jié)果依然很慢。排查發(fā)現(xiàn)優(yōu)化器把子查詢中的LIMIT改寫掉了或者因為沒有(status, id)聯(lián)合索引子查詢先走了全表掃描再排序。后來我堅持在子查詢的執(zhí)行計劃里確認出現(xiàn)Using index才把它壓下來。子查詢的ORDER BY和LIMIT必須落在索引覆蓋范圍內(nèi)優(yōu)化才有效。第四個坑是Redis緩存和源庫不一致。消息中心用了緩存ZSet分頁后用戶刪掉一條消息緩存里還留著這個ID翻頁時就會出現(xiàn)一條已經(jīng)刪掉的“幽靈消息”。后來補了一個異步刪除補償任務才把一致性穩(wěn)住。這個方案適合接受短時間不一致的業(yè)務不適合對數(shù)據(jù)強一致的場景。4.4 上線前要做的系統(tǒng)級防御優(yōu)化方案上線只是解決了“當前分頁為什么慢”。真正要讓系統(tǒng)長期不復發(fā)還得做幾道防御。第一給分頁接口設置最大OFFSET超過閾值直接拒絕查詢并提示用戶縮小篩選范圍而不是讓數(shù)據(jù)庫硬扛。第二前端交互上做約束比如只允許連續(xù)翻頁或滾動加載不讓用戶憑空跳到太深的頁數(shù)。第三對“上一頁/下一頁”這種交互后端主動緩存當前頁游標和結(jié)果減少重復計算。第四數(shù)據(jù)生命周期管理超過N個月的歷史數(shù)據(jù)遷入歷史表主表保持熱數(shù)據(jù)規(guī)模讓深分頁在源頭就沒有土壤。這四道防御不需要全是技術手段很多是對產(chǎn)品交互和數(shù)據(jù)治理的約束但往往比SQL優(yōu)化更治本。5. 多場景選型對照別讓業(yè)務為技術買單5.1 后管系統(tǒng)必須支持跳頁的深分頁后臺管理列表通常默認需要跳頁這種場景我排第一的是覆蓋索引延遲回表再疊加最大OFFSET限制。當OFFSET超過一定閾值與其讓用戶看到海量空白頁不如提示他增加篩選條件按時間、狀態(tài)或負責人把數(shù)據(jù)圈定到合理范圍。后管系統(tǒng)的用戶通常也接受“你要找的數(shù)據(jù)太舊了請加篩選條件”這類交互。5.2 APP信息流游標分頁是天然選擇APP里的“下拉加載更多”“上滑翻下一條”本質(zhì)就是連續(xù)翻頁沒有跳頁需求。對這種場景游標分頁的SQL最簡單、性能最穩(wěn)。需要注意排序字段足夠穩(wěn)定并用復合游標處理并發(fā)寫入。只要你不需要總頁數(shù)就別走回LIMIT OFFSET的老路這是性價比最高的做法。5.3 報表/消息中心用可接受的“延遲”換穩(wěn)定報表和消息中心的特點是數(shù)據(jù)量大、查詢頻繁、但允許一定的延遲。這種場景很適合預計算ID列表或直接生成結(jié)果集。報表可以把結(jié)果導出成文件分批下載消息中心可以用Redis ZSet維護活躍消息。用戶并不關心你是在查詢時算出來的還是提前備好的只要結(jié)果一致、響應快就是好方案。5.4 多條件復雜篩選讓專業(yè)組件做專業(yè)的事當一個列表同時支持時間、狀態(tài)、金額、渠道等十幾個篩選條件并且每個條件都可以任意組合時SQL索引幾乎無法覆蓋所有路徑。與其在深分頁和索引之間反復糾結(jié)不如把篩選維度同步到檢索組件里用倒排索引和內(nèi)存排序能力解決。代價是要維護數(shù)據(jù)同步鏈路、解決一致性問題團隊得有這個能力再上。如果團隊規(guī)模不大我會建議先用“固定篩選模板 索引 延遲回表”頂住等真正需要再投入這塊。這些方案我并不是一次性全用上而是踩了幾個月的坑之后才沉淀出來的。對我來說最深的體會是深分頁優(yōu)化第一個要解決的其實是產(chǎn)品交互問題——深分頁是用戶翻頁行為制造出來的如果產(chǎn)品設計把翻頁深度限制在合理范圍90%的系統(tǒng)根本不需要折騰。第二個才是技術選型我自己的決策順序是能游標先游標不能游標就用覆蓋索引延遲回表實在要跳頁再考慮緩存ID、冷熱分離和檢索組件。最后分享一個讓我長期受益的小習慣分頁接口上線后每周跑一次慢查詢巡檢把rows_examined和rows_sent比例超過一定閾值的SQL全部撈出來處理在深分頁還沒爆發(fā)前就把它按住比事后救火舒服得多。 MySQL深分頁優(yōu)化實戰(zhàn)：從LIMIT OFFSET到游標與延遲回表的完整方案
去年我接手過一個訂單查詢接口單表數(shù)據(jù)已經(jīng)過億頁面還是最傳統(tǒng)的那種“下一頁”式分頁。用戶翻到第60頁之后接口響應時間直接從幾百毫秒跳到幾秒翻到100頁以后基本就是超時重試的節(jié)奏。我看數(shù)據(jù)庫監(jiān)控CPU不高IO不忙SQL執(zhí)行計劃也走了索引但就是慢得離譜。這套現(xiàn)象做后端的同學一定不陌生——這就是典型的深分頁問題。深分頁問題的本質(zhì)是LIMIT OFFSET這種訪問方式在數(shù)據(jù)量小的時候幾乎沒存在感一旦碰上億級數(shù)據(jù)越往后翻越災難。數(shù)據(jù)庫沒辦法“跳過”O(jiān)FFSET行只能把前面的行全部掃描出來再丟棄。這篇文章把我從慢SQL排查、方案選型到最終落地的一整套打法完整復盤一遍包括游標分頁、覆蓋索引延遲回表、基于緩存的有序ID分頁等主流方案以及一路踩出來的坑。適合后端開發(fā)、DBA以及所有被“數(shù)據(jù)量一大分頁就卡”折磨過的同學對照參考。1. 深分頁到底慢在哪里從執(zhí)行計劃到量化分析1.1 一條“看起來沒毛病”的SQL先看一條再常見不過的分頁SQLSELECT id, order_no, status, created_at FROM orders WHERE user_type 1 ORDER BY id DESC LIMIT 20 OFFSET 1000000;邏輯很清楚把用戶類型為1的訂單按主鍵倒序從第1000001行開始取20行。很多人以為OFFSET會讓數(shù)據(jù)庫直接跳到第1000001行去讀實際上MySQL根本沒有這個能力。它得先從滿足條件的起始位置開始一行一行掃描累計到1000000行之后把前面的全部丟掉只返回最后這20行。也就是說你最終只拿到20條數(shù)據(jù)數(shù)據(jù)庫實際觸碰的行數(shù)卻是一百萬級的。如果這張表只有一個主鍵索引執(zhí)行計劃里的rows基本都是百萬級估算值Extra里大概率出現(xiàn)Using where如果user_type上剛好建了二級索引回表次數(shù)更是被放大。深分頁慢的根本原因就在這里掃描范圍不會因為你只返回20行而縮小頁數(shù)越深數(shù)據(jù)庫做無用功越多。1.2 并不只是OFFSET的鍋三個隱藏成本深分頁耗時其實是三層成本疊加的結(jié)果。第一層是掃描行數(shù)放大OFFSET1000000時至少100萬條索引記錄要被遍歷這是無法繞開的基礎開銷。第二層是回表隨機IO如果SELECT需要取二級索引之外的字段比如order_no、status、created_at每掃到一行滿足條件的記錄都要回聚簇索引再查一次完整行。掃描行數(shù)一大回表次數(shù)跟著翻倍而索引葉子節(jié)點在磁盤上是離散分布的每一次回表都是一次隨機IO代價相當大。第三層是排序和臨時表。當ORDER BY字段和索引順序不一致或查詢里帶了GROUP BY、DISTINCTMySQL可能走filesort甚至生成臨時表。深分頁時臨時表會變得很大內(nèi)存裝不下就往磁盤寫整個查詢就徹底回到了“慢”的領域。可以類比成在圖書館里按編號找書管理員必須從第一本開始數(shù)數(shù)到100萬本才停下來如果每數(shù)到一本還要拆開封面看一眼自然更慢。幾個隱藏成本疊加之后OFFSET稍大就會產(chǎn)生比業(yè)務邏輯多幾個數(shù)量級的開銷。1.3 模擬數(shù)據(jù)從百萬到億級的變化曲線為了直觀我之前在一臺普通虛擬機上做過一組相對測試單表按自增主鍵寫入不重復數(shù)據(jù)模擬不同數(shù)據(jù)量級下的深分頁表現(xiàn)。大致的相對耗時趨勢如下表內(nèi)數(shù)據(jù)規(guī)模LIMIT 20 OFFSET 0LIMIT 20 OFFSET 10000LIMIT 20 OFFSET 1000000100萬行3ms12ms320ms5000萬行4ms85ms3700ms1億行6ms180ms6800ms這組數(shù)字受內(nèi)存緩存、磁盤類型、索引設計影響很大不看絕對值只看相對變化OFFSET在1萬以內(nèi)時哪怕億級表也能在百毫秒級完成一旦OFFSET到百萬級耗時直接跳到秒級甚至出現(xiàn)接口超時。并且這個變化并不是均勻的臨界點通常出現(xiàn)在數(shù)據(jù)量突破內(nèi)存熱數(shù)據(jù)窗口、或回表頻繁觸發(fā)磁盤IO的時候曲線會突然變陡。1.4 什么樣的業(yè)務最容易中招最容易踩深分頁的不是那些“看起來查詢很復雜”的系統(tǒng)恰恰是結(jié)構(gòu)簡單的流水型業(yè)務訂單列表、支付流水、行為日志、消息記錄、操作審計。這類表只增不改數(shù)據(jù)量天然大頁面又習慣按時間倒序展示。用戶只要愿意往后翻OFFSET漲到幾十萬只是時間問題。我見過一個運營后臺單表不到三千萬行用戶為了找一個月前的單據(jù)硬是點了七十多次“下一頁”最后把頁面點死了。這類行為一旦沒有約束深分頁就是遲早的事和SQL本身寫得多漂亮關系不大。2. 四類優(yōu)化方案全景思路與選型邏輯2.1 一張表看懂四類方案先給一個全景對比方便在動手前建立整體認知方案核心思路翻頁能力主要代價推薦場景游標分頁Keyset用WHERE id last_id代替 OFFSET僅上一頁/下一頁不能任意跳頁排序字段受限APP信息流、實時列表覆蓋索引延遲回表子查詢先在索引上拿主鍵再回表取完整行支持任意跳頁SQL改寫成本高OFFSET仍有索引掃描代價后管列表、報表頁ID區(qū)間分頁按自增主鍵或唯一序號分段支持任意跳頁主鍵空洞、條件組合時易失效數(shù)據(jù)單調(diào)寫入無刪除場景緩存有序ID預熱ID列表到Redis ZSet按score分頁支持任意跳頁一致性維護成本高內(nèi)存占用大消息中心、報表、活動頁先理解一個關鍵點所有方案的本質(zhì)都是盡量把“掃描丟棄”變成“直接定位”。只要你還在用LIMIT OFFSET數(shù)據(jù)庫就無法逃避“把前面所有頁重復讀一遍”這件事只要換成游標、區(qū)間或緩存你就是在主動告訴數(shù)據(jù)庫“從哪開始”而不是讓它自己數(shù)到那兒。2.2 游標分頁把“數(shù)頁碼”變成“記路標”游標分頁是最接近“物理正確”的方案。LIMIT OFFSET的痛點是讓數(shù)據(jù)庫從起點開始數(shù)頁碼游標分頁則讓客戶端每次翻頁帶上上一頁最后一條記錄的游標值通常是主鍵ID。服務端拿到游標后直接用WHERE id last_id去定位不再管前面有多少頁。相當于你爬山時在關鍵路口做了標記第二次不需要從山腳重新走直接去標記處繼續(xù)前進。-- 傳統(tǒng)深分頁第N頁 SELECT ... FROM orders WHERE user_type 1 ORDER BY id DESC LIMIT 20 OFFSET 10000; -- 游標分頁從上一頁最后一條 id80001 開始 SELECT ... FROM orders WHERE user_type 1 AND id 80001 ORDER BY id DESC LIMIT 20;后者執(zhí)行計劃里的rows基本是“滿足條件且停在80001之前”的數(shù)據(jù)量不再隨頁碼變大而膨脹。不過游標分頁有一票否決條件如果產(chǎn)品經(jīng)理要求回到頂部能直接點“第12345頁”這個方案基本只能作罷。這類交互常見于后管系統(tǒng)前端需要跳頁后端的游標就難以為繼。另外要注意游標分頁依賴排序字段穩(wěn)定且有索引不能隨便排。2.3 覆蓋索引延遲回表保住跳頁能力當業(yè)務必須支持跳頁覆蓋索引加延遲回表是我最常用的一招?！案采w索引”是指一個二級索引已經(jīng)把查詢要讀的列都包含了查詢只在索引頁里就能拿到結(jié)果Extra顯示為Using index不需要回表。“延遲回表”則是在此基礎上用子查詢先在索引里掃描出目標頁的主鍵ID集合再用這個集合去聚簇索引里取完整數(shù)據(jù)行。深分頁場景里收益特別好理解原查詢可能要回表100萬次改寫后無論OFFSET多大回表只發(fā)生在最終命中的那一頁數(shù)據(jù)上比如20次。但要注意這個方案不是讓數(shù)據(jù)庫不掃描了而是把掃描對象從“完整行回表”降級成“窄索引頁”整體成本低一個數(shù)量級。適合后臺管理列表這種必須支持跳頁、又不至于讓用戶無限制翻幾千頁的場景。2.4 三種補充思路與“選型三問”除了上面兩種還有三個方向也值得納入方案池。一是ID區(qū)間分頁利用自增主鍵或唯一有序編號做BETWEEN實現(xiàn)任意跳頁但它對主鍵空洞敏感一旦有刪除、批量導入產(chǎn)生了坑頁碼就會出現(xiàn)空白或錯位更適合單調(diào)寫入、基本不做物理刪除的表。二是冷熱分離/歸檔把N個月之前的歷史數(shù)據(jù)遷到歷史表線上主表只保留熱數(shù)據(jù)這不是SQL優(yōu)化而是數(shù)據(jù)治理但往往最能治本。三是引入檢索組件當查詢條件特別多、各種字段任意組合時SQL索引很難覆蓋所有場景可以把篩選維度同步到搜索引擎用倒排索引解決但代價是引入額外組件和同步鏈路。無論選哪條路動手前一定要回答三個問題業(yè)務能不能接受“只有上一頁/下一頁”排序字段是否穩(wěn)定能不能提煉出唯一的復合排序鍵數(shù)據(jù)能不能被業(yè)務維度先過濾一層比如只看自己的訂單、只看最近三十天這三個問題決定你該用游標、延遲回表還是緩存方案也決定這個優(yōu)化能不能活過需求評審。3. 實操落地從SQL改造到緩存方案的完整記錄3.1 落地前先摸清三張底牌直接改SQL前我習慣先花半天把三件事搞清楚。第一是表結(jié)構(gòu)和索引現(xiàn)狀主鍵是自增還是雪花ID排序字段上有沒有聯(lián)合索引哪些列經(jīng)常出現(xiàn)在SELECT里。第二是真實慢SQL打開慢日志把分頁接口在生產(chǎn)上的實際SQL撈出來別用開發(fā)環(huán)境的“假數(shù)據(jù)”推演。第三是業(yè)務約束確認前端翻頁交互、最大可接受的翻頁深度以及跳頁、上一頁這些能力是否必須保留。這三張底牌決定方案方向。比如一張訂單表主鍵是雪花ID、天然時間有序那么游標分頁幾乎首選但如果業(yè)務必須在列表頂部顯示“共多少頁”游標方案基本沒戲得回到延遲回表。業(yè)務約束不先對齊技術方案再漂亮也落不了地。另外排查出慢SQL后要把基線記下來優(yōu)化前典型耗時、rows_examined、rows_sent優(yōu)化后同樣記錄一遍后面驗證效果才有依據(jù)。3.2 改造一游標分頁的SQL與代碼實現(xiàn)這里給一個可運行的完整思路用Python偽代碼說明換Java、Go、C#都是一樣的邏輯# 約定每頁20條 def list_orders(user_id, last_idNone, limit20): cond user_id %s params [user_id] if last_id: cond AND id %s params.append(last_id) sql f SELECT id, order_no, status, created_at FROM orders WHERE {cond} ORDER BY id DESC LIMIT %s params.append(limit) rows db.query(sql, params) # 下一頁游標取當前頁最后一條記錄的id next_cursor rows[-1][id] if rows else None return rows, next_cursor接口返回體里帶上next_cursor前端點“加載更多”時把上一頁返回的next_cursor回傳。第一頁沒有游標傳空即可。這里有一個重要前提表上必須有(user_id, id)聯(lián)合索引否則WHERE user_id? AND id ?有可能走全表掃描優(yōu)化等于白做。執(zhí)行計劃檢查到key包含user_id和id時這條SQL的耗時才會隨數(shù)據(jù)增長保持平穩(wěn)。如果業(yè)務要求按創(chuàng)建時間倒序而不是按主鍵倒序游標要升級成二元組。因為同一毫秒內(nèi)可能會出現(xiàn)多條訂單created_at不唯一只用時間戳做游標必然漏數(shù)據(jù)。穩(wěn)妥寫法是用(created_at, id)作為復合游標SELECT id, order_no, status, created_at FROM orders WHERE user_id :uid AND (created_at :last_created_at OR (created_at :last_created_at AND id :last_id)) ORDER BY created_at DESC, id DESC LIMIT 20;聯(lián)合索引建為(created_at, id)。這個寫法的邊界判定更準確代價是SQL稍微復雜但這是值得的。3.3 改造二覆蓋索引延遲回表的SQL改造再來看必須支持跳頁的后臺列表場景。原SQL往往是這種SELECT id, order_no, status, created_at FROM orders WHERE status 1 ORDER BY id DESC LIMIT 1000000, 20;優(yōu)化后的寫法SELECT o.id, o.order_no, o.status, o.created_at FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY id DESC LIMIT 1000000, 20 ) t ON o.id t.id ORDER BY o.id DESC;前提是orders表上有(status, id)聯(lián)合索引。執(zhí)行流程拆開看子查詢在窄窄的二級索引頁里只拿id因為只需要id和status不需要回表即使掃描一百萬條索引記錄也遠比回表一百萬次來得快。INNER JOIN再去聚簇索引里取這20條完整數(shù)據(jù)回表次數(shù)降到20次。外層再排一次序保證最終順序穩(wěn)定。為了讓大家對收益有體感我在本機模擬數(shù)據(jù)上做過一次對比測試數(shù)據(jù)量壓在3000萬行左右同樣的OFFSET1000000兩種寫法的耗時和回表次數(shù)差異非常大大致整理如下表寫法索引使用回表次數(shù)模擬耗時SELECT * ... LIMIT 1000000, 20二級索引后逐行回表約100萬次5800ms覆蓋索引延遲回表子查詢使用覆蓋索引20次880ms需要說清楚這個方案不是銀彈。OFFSET仍然存在子查詢?nèi)匀灰獟呙璧揭话偃f行索引記錄只是避開了回表隨機IO。如果業(yè)務允許用戶翻到幾百萬頁索引掃描本身也會成為瓶頸。我的經(jīng)驗是后管系統(tǒng)加上最大OFFSET限制比如超過20000頁就提示縮小篩選范圍再配合延遲回表工程上既簡單又穩(wěn)妥。3.4 改造三基于Redis有序ID的分頁落地第三種方案適合讀多寫少、列表數(shù)據(jù)可以被預生成的場景比如報表、消息中心。核心思路是用一個后臺任務或異步回調(diào)把滿足條件的有序數(shù)據(jù)ID寫進Redis的ZSetmember是業(yè)務IDscore是排序值。前端分頁時直接在ZSet上按score區(qū)間取ID再拿著ID去數(shù)據(jù)庫批量查明細數(shù)據(jù)庫這邊就再也不會出現(xiàn)“深分頁慢查詢”的問題了。# 構(gòu)建score可以取毫秒時間戳member用業(yè)務ID ZADD orders:report:202501 1735689600000 xid_10000001 ZADD orders:report:202501 1735689601000 xid_10000002 # 第一頁按score從大到小取20條 ZREVRANGEBYSCORE orders:report:202501 inf 0 LIMIT 0 20 # 下一頁上一頁最后一條score為1735689601000取比它小的區(qū)間 ZREVRANGEBYSCORE orders:report:202501 1735689600999 0 LIMIT 0 20這里有一道細節(jié)題如果score只取時間戳同一毫秒內(nèi)可能有多條數(shù)據(jù)會出現(xiàn)邊界區(qū)間的重復或遺漏。更省心的做法是如果主鍵是雪花ID直接用ID做score因為雪花ID本身時間有序且唯一ZSet天然穩(wěn)定如果主鍵是自增ID但排序要按業(yè)務時間就要在score設計上保證唯一性或者在邊界判斷時處理同score成員。這個細節(jié)單獨抽出來講是因為我見過太多人在這里翻車。內(nèi)存方面也要提前估算。一個ZSet成員在Redis里的開銷并不低成員本身、score、跳表指針、字典對象加起來往往要幾十字節(jié)甚至上百字節(jié)。一個億級成員的ZSet內(nèi)存占用很容易到十幾GB甚至更高。所以我不建議把全量ID都塞進去更合理的是按用戶、月份、狀態(tài)拆key只緩存活躍窗口并給不活躍的key設置TTL自動淘汰。3.5 分頁參數(shù)與容量的實用建議最后給一組我自己常用的參考口徑。分頁大小列表頁單頁20到50條足夠后管報表可以放寬到100條但不建議更大因為單頁越大LIMIT之后的回表、序列化和傳輸成本全部被放大。游標字段類型優(yōu)先bigint或雪花IDvarchar主鍵也能用但比較和索引寬度都比整數(shù)差想用UUID作為游標基本不建議無序又寬索引性能和緩存友好度都差。容量估算可以用一個簡單公式單個分頁條目在Redis里按100字節(jié)粗算一個月5000萬條數(shù)據(jù)大約5GB內(nèi)存。如果你評估后覺得內(nèi)存成本過高就回到“按月拆key TTL”的思路讓活躍窗口固定在可控規(guī)模。不要試圖用一個ZSet扛住整張億級表的所有歷史那會把Redis也變成另一個“深分頁”。4. 高頻踩坑與排障實戰(zhàn)常見問題速查4.1 常見問題速查表把我在生產(chǎn)環(huán)境中遇到過的、以及朋友反饋過的高頻問題整理成一張速查表遇到類似現(xiàn)象可以直接對照現(xiàn)象可能原因排查動作游標分頁偶發(fā)少數(shù)據(jù)查詢期間有新數(shù)據(jù)插入游標邊界漂移接受最終一致性或改為快照分頁復合游標排序亂沒建(created_at, id)聯(lián)合索引EXPLAIN 看 key補索引延遲回表結(jié)果順序亂子查詢排序后外層未再排序外層加 ORDER BY覆蓋索引沒生效SELECT 里有回表字段或函數(shù)操作去掉冗余列避免函數(shù)包裹索引列加了 LIMIT 還是全表掃描條件字段沒走索引/隱式類型轉(zhuǎn)換檢查字段類型看 possible_keys緩存分頁出現(xiàn)幽靈ID源庫刪除后緩存未同步做異步刪除補償任務4.2 排查慢SQL的標準動作從慢日志到執(zhí)行計劃排查深分頁我有一套固定動作。先開慢日志把超過1秒的查詢抓出來SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SHOW VARIABLES LIKE slow_query_log_file;抓到慢SQL后用EXPLAIN看執(zhí)行計劃重點看type、rows、Extra這三列。type盡量是range或refrows要和實際數(shù)據(jù)量對得上Extra里出現(xiàn)Using filesort、Using temporary都要警惕。MySQL 8.0以上可以直接用EXPLAIN ANALYZE看每一步的耗時和讀取行數(shù)比全憑估算強太多。還有一個長期有效的手段記錄rows_examined和rows_sent的比值。如果一條分頁查詢讀了幾十萬行最終只返回20行rows_examined / rows_sent可能上萬這就是深分頁在“做無用功”的直接證據(jù)。這個比值可以作為分頁接口日常巡檢的指標數(shù)值超過一定閾值就自動告警。4.3 真實踩坑記錄四則實例第一個坑是游標方案被“總頁數(shù)”需求直接打死。原以為改完SQL就能上線結(jié)果產(chǎn)品要求在列表頂部顯示“共N頁”還說這是運營長期習慣。游標分頁根本不知道總頁數(shù)因為它從頭到尾沒有數(shù)過OFFSET。最后只能退回延遲回表并把最大翻頁深度壓到用戶可接受的范圍。第二個坑是排序字段不唯一導致重復數(shù)據(jù)。某個列表業(yè)務要求按created_at倒序我一開始只用created_at做游標結(jié)果同一秒內(nèi)創(chuàng)建的多條訂單下一頁用created_at last_created_at就把同秒、ID更大的那批漏掉了。改成(created_at, id)二元組游標用id做二級排序和邊界判等問題才真正解決。第三個坑是延遲回表優(yōu)化后結(jié)果依然很慢。排查發(fā)現(xiàn)優(yōu)化器把子查詢中的LIMIT改寫掉了或者因為沒有(status, id)聯(lián)合索引子查詢先走了全表掃描再排序。后來我堅持在子查詢的執(zhí)行計劃里確認出現(xiàn)Using index才把它壓下來。子查詢的ORDER BY和LIMIT必須落在索引覆蓋范圍內(nèi)優(yōu)化才有效。第四個坑是Redis緩存和源庫不一致。消息中心用了緩存ZSet分頁后用戶刪掉一條消息緩存里還留著這個ID翻頁時就會出現(xiàn)一條已經(jīng)刪掉的“幽靈消息”。后來補了一個異步刪除補償任務才把一致性穩(wěn)住。這個方案適合接受短時間不一致的業(yè)務不適合對數(shù)據(jù)強一致的場景。4.4 上線前要做的系統(tǒng)級防御優(yōu)化方案上線只是解決了“當前分頁為什么慢”。真正要讓系統(tǒng)長期不復發(fā)還得做幾道防御。第一給分頁接口設置最大OFFSET超過閾值直接拒絕查詢并提示用戶縮小篩選范圍而不是讓數(shù)據(jù)庫硬扛。第二前端交互上做約束比如只允許連續(xù)翻頁或滾動加載不讓用戶憑空跳到太深的頁數(shù)。第三對“上一頁/下一頁”這種交互后端主動緩存當前頁游標和結(jié)果減少重復計算。第四數(shù)據(jù)生命周期管理超過N個月的歷史數(shù)據(jù)遷入歷史表主表保持熱數(shù)據(jù)規(guī)模讓深分頁在源頭就沒有土壤。這四道防御不需要全是技術手段很多是對產(chǎn)品交互和數(shù)據(jù)治理的約束但往往比SQL優(yōu)化更治本。5. 多場景選型對照別讓業(yè)務為技術買單5.1 后管系統(tǒng)必須支持跳頁的深分頁后臺管理列表通常默認需要跳頁這種場景我排第一的是覆蓋索引延遲回表再疊加最大OFFSET限制。當OFFSET超過一定閾值與其讓用戶看到海量空白頁不如提示他增加篩選條件按時間、狀態(tài)或負責人把數(shù)據(jù)圈定到合理范圍。后管系統(tǒng)的用戶通常也接受“你要找的數(shù)據(jù)太舊了請加篩選條件”這類交互。5.2 APP信息流游標分頁是天然選擇APP里的“下拉加載更多”“上滑翻下一條”本質(zhì)就是連續(xù)翻頁沒有跳頁需求。對這種場景游標分頁的SQL最簡單、性能最穩(wěn)。需要注意排序字段足夠穩(wěn)定并用復合游標處理并發(fā)寫入。只要你不需要總頁數(shù)就別走回LIMIT OFFSET的老路這是性價比最高的做法。5.3 報表/消息中心用可接受的“延遲”換穩(wěn)定報表和消息中心的特點是數(shù)據(jù)量大、查詢頻繁、但允許一定的延遲。這種場景很適合預計算ID列表或直接生成結(jié)果集。報表可以把結(jié)果導出成文件分批下載消息中心可以用Redis ZSet維護活躍消息。用戶并不關心你是在查詢時算出來的還是提前備好的只要結(jié)果一致、響應快就是好方案。5.4 多條件復雜篩選讓專業(yè)組件做專業(yè)的事當一個列表同時支持時間、狀態(tài)、金額、渠道等十幾個篩選條件并且每個條件都可以任意組合時SQL索引幾乎無法覆蓋所有路徑。與其在深分頁和索引之間反復糾結(jié)不如把篩選維度同步到檢索組件里用倒排索引和內(nèi)存排序能力解決。代價是要維護數(shù)據(jù)同步鏈路、解決一致性問題團隊得有這個能力再上。如果團隊規(guī)模不大我會建議先用“固定篩選模板 索引 延遲回表”頂住等真正需要再投入這塊。這些方案我并不是一次性全用上而是踩了幾個月的坑之后才沉淀出來的。對我來說最深的體會是深分頁優(yōu)化第一個要解決的其實是產(chǎn)品交互問題——深分頁是用戶翻頁行為制造出來的如果產(chǎn)品設計把翻頁深度限制在合理范圍90%的系統(tǒng)根本不需要折騰。第二個才是技術選型我自己的決策順序是能游標先游標不能游標就用覆蓋索引延遲回表實在要跳頁再考慮緩存ID、冷熱分離和檢索組件。最后分享一個讓我長期受益的小習慣分頁接口上線后每周跑一次慢查詢巡檢把rows_examined和rows_sent比例超過一定閾值的SQL全部撈出來處理在深分頁還沒爆發(fā)前就把它按住比事后救火舒服得多。