百萬級數據，分頁如何處理？
推薦閱讀
1. SpringBoot 整合篇
2. 手寫一套迷你版HTTP伺服器
3. 記住：永遠不要在MySQL中使用UTF-8
4. Springboot啟動原理解析
最近遇到了這麼一個情況，資料庫裡面的數據由於長期的堆積，導致數據量不斷的上升，而後臺的系統每次進行分頁查詢的時候，效率都會降低很多。後來查看了一下之後，發現此時的分頁原理主要是採用了傳統的物理分頁 limit n,m 的方式。
為了方便演示，我特意創建了以下幾張表進行實例演練：
表分別是商品表，用戶表，用戶選購商品記錄表：
goods user g_u
三張表的關係比較簡單，user的id和goods裡面的id合併生成關聯數據，存儲在了g_u裡面。三張資料庫表的設計如下所示：
CREATE TABLE `goods` (
`id` int(11) NOT NULL,
`name` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
`price` decimal(6,1) NOT NULL,
`des` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`age` tinyint(3) NOT NULL,
`sex` tinyint(1) NOT NULL COMMENT 年齡,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `g_u` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`g_id` int(11) NOT NULL COMMENT 商品id,
`u_id` int(11) NOT NULL COMMENT 用戶id,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2800001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
這個模擬的應用場景非常簡單，用戶和商品之間的關係維持在了一對多的關聯中。為了方便進行後續的測試，我用jmeter批量創建了1900000條測試數據，模擬一次百萬級的數據查詢場景。
相應的數據腳本也已經存在百度雲中了，需要的同學可以前往下載：
地址：鏈接: https://pan.baidu.com/s/1BfddJ8MBtnpeiV84gNmClA
提取碼: 4kmp
假設現在需求裡面有這樣的一個業務場景，需要我們對購買記錄表裡面的數據進行分頁查詢，那麼對於常規的分頁查詢操作，常人會想到的方式可能是通過下述的語句：
SELECT * from g_u as gu ORDER BY id limit 1850000,100
測試一下發現，查詢的時間為：
當我們搜索的數據越靠後邊的時候，搜索的速度就會越低下，因此這個時候，適當的創建索引就顯得比較重要了。首先我們來做一次explain的sql檢測，檢測結果為如下所示：
由於我們查詢的時候，使用的是根據主鍵索引id進行排序，因此查詢的時候key一項為PRIMARY。 SELECT * FROM g_u WHERE id >=(SELECT id FROM g_u LIMIT 1850000,1) ORDER BY id LIMIT 100
此時查詢有了一些許的提升，但是依舊查詢緩慢
通過explain執行計劃分析結果可見：
子查詢用到了索引，外部查詢用到了where的輔助索引這個時候我們不妨可以試下通過利用主鍵id來提升我們的查詢效率：
SELECT * FROM g_u as gu WHERE gu.id>($firstId+$pageSize*$pageSize) limit 100
查詢的時間一下子大大縮短了許多：
通過explain分析一下該sql：
這裡面，sql在運行的時候藉助了主鍵索引的幫助，因此效率大大提升了。但是這個時候，可能你會有這麼一個疑惑。如果說數據的索引不是連續的該如何處理分頁時候每頁數據的完整性和一致性？這裡不妨可以試試另外的一種思路，通過建立一張第三方的表g_u_index表，將原本亂序的id存儲在g_u_index中，在g_u_index一表中，我們可以通過該表有序的g_u_index.id來對應原本相應的無序的g_u.id。建表的sql語句如下所示：
CREATE TABLE `g_u_index` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`index` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_id_index` (`id`,`index`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1900024 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ps: 可以為id和index兩者建立一套複合索引，提升查詢的效率。
這裡我們需要保證一點就是，g_u表中插入的數據順序需要和g_u_index表中插入的順序是一致的。然後查詢分頁指定的index時候可以這麼來查：
SELECT g_u_index.index FROM g_u_index WHERE id=($firstId+$pageSize*$pageSize) limit 1
通過執行explain分析後，結果變成如下所示：
查詢時間為：0.001s
有了第三方表的幫助下，此時分頁的sql優化可以調整為以下這種方式：
SELECT * FROM g_u as gu where gu.id>(
SELECT g_u_index.index FROM g_u_index WHERE id=($firstId+$pageSize*$pageSize) limit 1
) limit 100
通過構建了第三方表之後，數據的查詢時間一下子大大縮減了：
查詢的時候為了更加人性化，通常不需要顯示這些無意義的id，需要的是商品名稱和用戶姓名，假設我們還是隻採用最原始的無第三方表的方式進行查詢的話，效率會比較底下：
SELECT gu.id,goods.`name`,`user`.username FROM g_u as gu ,goods ,`user`
where goods.id=gu.g_id AND `user`.id=gu.u_id
ORDER BY id limit 1500000,1000
結果：
因此如果藉助了第三方表查詢的話，sql可以調整成下方這種類型：
SELECT goods.`name`,`user`.username FROM g_u as gu ,goods ,`user`
where goods.id=gu.g_id AND `user`.id=gu.u_id
and
gu.id>=(
SELECT g_u_index.index FROM g_u_index WHERE id=(9+1000*1900) limit 1
) limit 100
查詢的時間會大大減少：
通過explain執行計劃分析之後，結果如下：
在實際的業務場景中，一張原來就有上百萬數據的表要做出這樣的id拆分，並且同步到第三方表的確實不太容易，這裡推薦一種思路，可以藉助阿里的中間件canal來實現對於資料庫日誌的訂閱，然後自定義進行數據的同步操作。
對於canal的講解在我的這篇文章中也有講述： 阿里Canal框架（數據同步中間件）初步實踐
對於sql的優化需要結合實際的業務需求來開展，總的來說，這部分還是需要有一定的實戰演練才能變強。
常用的sql優化技巧小結：
1.數據量大的時候，應盡量避免全表掃描，應考慮在 where及 order by 涉及的列上建立索引，建索引可以大大加快數據的檢索速度。
2.適當的使用Explain可以對sql進行相應的深入分析。
3.當只要一行數據時使用LIMIT 1。
4.在使用索引欄位作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應儘可能的讓欄位順序與索引順序相一致。
5.不要在 where子句中的「=」左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。
6.適當的時候採用覆蓋索引可以提高查詢的效率。
推薦閱讀：
