資料庫表結構示例如下：

CREATE TABLE follow_fans_[0-255]  (    id bigint(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',    biz_content   VARCHAR(50) DEFAULT NULL COMMENT '業務物件ID',    source        VARCHAR(50) DEFAULT NULL COMMENT '來源',    pin           VARCHAR(50) DEFAULT NULL COMMENT '使用者pin',    ext           VARCHAR(5000) DEFAULT NULL COMMENT '擴充套件資訊',    status        TINYINT(2) DEFAULT 1 COMMENT '狀態，0是失效，1是正常',    created_time  DATETIME DEFAULT NULL COMMENT '建立時間',    modified_time DATETIME DEFAULT NULL COMMENT '修改時間',    PRIMARY KEY(id),    UNIQUE INDEX uniq_biz_content_pin (biz_content, pin)  )  ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT = '關注粉絲表';

由於同一個業務物件的所有粉絲都儲存到一張資料庫表中，對於分頁查詢列表介面，首先想到的就是用limit實現，對於粉絲數量很少的關注物件，查詢介面效能還不錯。但是隨著關注物件的粉絲數量越來越多，介面查詢效能就會越來越慢。後來經過介面壓測，當業務物件粉絲列表數量達到幾十萬級別的時候，查詢頁碼數量越大，查詢耗時越多。limit深分頁為什麼會變慢？這就和sql的執行計劃有關了，limit語句會先掃描offset+n行，然後再丟棄掉前offset行，返回後n行資料。也就是說limit 100000,10，就會掃描100010行，而limit 0,10，只掃描10行。查詢 sql 示例如下：

select  id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} order by id desc limit 10, 10;

• 方案優點：實現簡單，支援跳頁查詢。

• 方案缺點：資料量變大時，隨著查詢頁碼的深入，查詢效能越來越差。

Limit深分頁問題的本質原因就是：偏移量（offset）越大，mysql就會掃描越多的行，然後再拋棄掉，這樣就導致查詢效能的下降。所以可以採用標籤記錄法，就是標記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。具體做法方式是，查詢粉絲列表中按照自增主鍵ID倒序查詢，查詢結果中返回主鍵ID，然後查詢入參中增加maxId引數，該引數需要透傳上一次請求粉絲列表中最後一條記錄主鍵ID，第一次查詢時可以為空，但是需要查詢下一頁時就必傳。最後根據查詢時返回的行數是否等於 10 來判斷整個查詢是否可以結束。最佳化後的查詢sql參考如下：

select id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} and id < #{lastId} order by id desc limit 10;

• 方案優點：避免了資料量變大時，頁碼查詢深入的效能下降問題；經過介面壓測，千萬級資料量時，前 N-1頁查詢耗時可以控制在幾十毫秒內。
• 方案缺點：只能支援按照頁碼順序查詢，不支援跳頁，而且僅能保證前 N-1 頁的查詢效能；如果最後一頁的表中行數量不滿 10 條時，引擎不知道何時終止查詢，只能遍歷全表，所以當表中資料量很大時，還是會出現超時情況。

select id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} and id < #{lastId} and id >={minId} order by id desc limit 10;

select min(id) from follow_fans_1 where biz_content = #{bizContent}

由於表中資料量太大，每個表中總資料量都是上億級別，導致第一步查詢 minId就直接超時了，根本沒有機會去執行第二步。但是考慮到上一個查詢方案只有最後一頁才會查詢超時，前N-1頁查詢根本用不到 minId 作為區間限制。所以當表中資料量很大時，通常從第一頁到最後一頁查詢之間會存在一定的時間差。就可以正好去利用這個時間差去非同步查詢minId，然後將查詢出來的minId儲存到快取中，考慮到這個 minId 可能會被刪除，可以設定一定的過期時間。最後最佳化後的查詢流程如下：

1.呼叫查詢粉絲列表方法時首先查詢快取minId；

2.如果快取minId 為空，則建立非同步任務去執行select min(id) 查詢表中的 minId,然後回寫快取，該非同步任務執行時間可能會很長，可以單獨設定超時時間;

• 方案優點：避免了資料量變大時，頁碼查詢深入的效能下降問題；經過介面壓測，千萬級資料量時，從第一頁到最後一頁都控制在幾十毫秒內。

• 方案缺點：只能支援按照頁碼順序和主鍵ID倒序查詢，不支援跳頁查詢，並且還需要依賴大資料平臺離線計算和額外的快取來儲存 minId。

聚簇索引 (clustered index)：聚簇索引的葉子節點儲存行記錄，InnoDB必須要有且只有一個聚簇索引：

1.如果表定義了主鍵，則主鍵索引就是聚簇索引；

2.如果沒有定義主鍵，則第一個非空的唯一索引列是聚簇索引；

可以參考以下幾點索引原則：

1.最左字首匹配原則，mysql會一直向右匹配直到遇到範圍查詢（>、<、between、like）就停止匹配，比如 a=1 and b=2 and c>3 and d=4 ，如果建立了（a,b,c,d)順序的索引，d是用不到索引的，如果建立（a,b,d,c）的索引則都可以用到，a、b、d的順序可以任意調整。

2.=和in可以亂序，比如 a=1 and b=2 and c=3 建立（a,b,c)索引可以任意順序，mysql的查詢最佳化器會幫助最佳化成索引可以識別的形式。

3.儘量選擇區分度高德列作為索引，區分度公式count(distinct col)/count(*)，表示欄位不重複的比例。

4.索引列不能使用函式或參與計算，不能進行型別轉換，否則索引會失效。

回表查詢就是先定位主鍵值，在根據主鍵值定位行記錄，需要掃描兩遍索引。解決方案：只需要在一顆索引樹上能夠獲取SQL所需要的所有列資料，則無需回表查詢，速度更快。可以將要查詢的欄位，建立到聯合索引裡去，這就是索引覆蓋。查詢sql在進行explain解析時，Extra欄位為Using Index時，則觸發索引覆蓋。沒有觸發索引覆蓋，發生了回表查詢時，Extra欄位為Using Index condition。

本文透過結合實際系統案例，詳細介紹了分頁查詢的最佳化歷程，一步步從最簡單的limit分頁實現，到最後滿足千萬級表資料的分頁查詢探索實現，並介紹每種技術方案的優缺點，希望可以幫助讀者去選擇適合自己的技術方案。