MySQL單表億級(jí)數(shù)據(jù)分頁怎么優(yōu)化？
前言
有人說單表超千萬數(shù)據(jù)就應(yīng)該分庫分表了，這么玩不合理啊。但是對(duì)于創(chuàng)新業(yè)務(wù)來講，業(yè)務(wù)系統(tǒng)的設(shè)計(jì)不可能一上來就預(yù)估這么大的容量，成本和工期都不足矣完成系統(tǒng)的開發(fā)工作。我覺得對(duì)于創(chuàng)新型業(yè)務(wù)系統(tǒng)的設(shè)計(jì)，首先滿足需求，其次考慮到萬一業(yè)務(wù)井噴發(fā)展所要考慮到的臨時(shí)解決方案，為系統(tǒng)升級(jí)預(yù)留時(shí)間。
誰都希望業(yè)務(wù)井噴，那么它來了!
具體時(shí)間點(diǎn)就不說了，開始做了一個(gè)新業(yè)務(wù)，見了一個(gè)表，該表累計(jì)數(shù)據(jù)條不超過100萬，提供查詢功能。后來業(yè)務(wù)量持續(xù)上漲，mysql 磁盤開始報(bào)警，查詢超時(shí)報(bào)警。而且，客戶需要實(shí)時(shí)查詢?cè)摌I(yè)務(wù)表的數(shù)據(jù)并下載。頭大，臨時(shí)改存儲(chǔ)方案已經(jīng)來不及了，不能耽誤KPI。
先解決眼下問題，先擴(kuò)充磁盤。停止雙機(jī)房同步，減少不必要的報(bào)警。
但是1000G 估計(jì)也扛不了多久，和業(yè)務(wù)同學(xué)討論后，業(yè)務(wù)接受的范圍T-7范圍內(nèi)的數(shù)據(jù)實(shí)時(shí)查詢下載。按這個(gè)增長量，7天也是過億的記錄條數(shù)。但是7天的數(shù)據(jù)磁盤肯定是夠用的，那就要先把歷史數(shù)據(jù)離線存儲(chǔ)。
這個(gè)也簡單，幾行代碼的事兒。當(dāng)然這樣依靠完善的基建。
容量的問題解決了，那么改對(duì)數(shù)據(jù)分頁查詢的進(jìn)行優(yōu)化。為了說明問題，去掉敏感的業(yè)務(wù)數(shù)據(jù)，數(shù)據(jù)表結(jié)構(gòu)如下：
- CREATE TABLE `t` (
- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
- `a` char(32) DEFAULT '' COMMENT '',
- `b` varchar(64) DEFAULT NULL COMMENT '',
- `c` bigint(20) unsigned NOT NULL COMMENT '',
- `d` varchar(64) NOT NULL COMMENT '',
- `e` tinyint(4) DEFAULT NULL COMMENT '',
- `f` int(11) NOT NULL DEFAULT '0' COMMENT '',
- `g` varchar(32) NOT NULL COMMENT '',
- `h` char(32) DEFAULT NULL COMMENT '',
- `i` varchar(64) DEFAULT NULL COMMENT '',
- `j` varchar(64) DEFAULT NULL COMMENT '',
- `k` datetime DEFAULT NULL COMMENT '',
- `l` int(11) DEFAULT NULL COMMENT '',
- `m` timestamp NULL DEFAULT NULL COMMENT '',
- `n` timestamp NULL DEFAULT NULL COMMENT ''
- PRIMARY KEY (`id`),
- UNIQUE KEY `UK_b` (`b`),
- KEY `IDX_c` (`c`,) USING BTREE
- )
當(dāng)數(shù)據(jù)量少時(shí)，我們用下面的分頁是沒有問題的：
- SELECT id,a,b… FROM t LIMIT n,m
例如：
pagesize ：每頁顯示條數(shù)。
pageno:頁碼
那么 m=pagesize; n=(pageno-1)*pagesize.
MySQL的limit工作原理就是先讀取前面n條記錄，然后拋棄前n條，讀后面m條想要的，所以n越大，偏移量越大，性能就越差。
修改sql，減少io的消耗
- SELECT id,a,b… FROM t where id in(SELECT id FROM t LIMIT n,m)
其實(shí)這樣也避免不了掃描前n 條，但是時(shí)間已經(jīng)節(jié)約了很多。
上面是每頁請(qǐng)求的RT,可見隨著頁數(shù)的增加，RT 逐漸上升。
Qps 逐漸下降。
那么如果數(shù)據(jù)太多的話，最后一頁超時(shí)的概率會(huì)非常大。
優(yōu)化后
先賣個(gè)關(guān)子，先看看優(yōu)化后的表現(xiàn)，這個(gè)接口的性能明顯提升。如圖所示：
RT 平均在10ms 左右，因?yàn)榉祷刈隽藬?shù)據(jù)處理，RT最終在15ms左右
qps 也很平穩(wěn)，應(yīng)該可以再高一些，取決于客戶的調(diào)用。
優(yōu)化思路
全表掃描肯定不現(xiàn)實(shí)，這時(shí)我想到了LSM, Log Structured Merge Trees.這種數(shù)據(jù)結(jié)構(gòu)，被用在許多產(chǎn)品的文件結(jié)構(gòu)策略：HBase, Cassandra, LevelDB, SQLite,Kafka 等。是一種非常復(fù)雜的復(fù)合數(shù)據(jù)結(jié)構(gòu)，它包含了 WAL(Write Ahead Log)、跳表(SkipList)和一個(gè)分層的有序表(SSTable，Sorted String Table)。
這里，沒有必要實(shí)現(xiàn)一個(gè)LSM 樹，只是參考了其稀疏索引的思想，能夠準(zhǔn)確定位數(shù)據(jù)。這樣就簡單了。步驟如下：
1.根據(jù)分析業(yè)務(wù)，構(gòu)建一個(gè) 字段 a,b的聯(lián)合索引。因?yàn)閍,b 是數(shù)據(jù)的查詢條件，且能分離出1/7的數(shù)據(jù)。
- ALTER table ADD INDEX index_a_b('a','b')
2.因?yàn)檫@個(gè)表的數(shù)據(jù) 都是通過 insert ... on duplicate key update ... 來更新的，【這也是線上死鎖分析的那篇文章留下的伏筆】，而且 id 是自增主鍵，所以，所有的數(shù)據(jù)都是按照入庫時(shí)的順序來的，且后面遇到?jīng)_突時(shí)修改也是update 的，所以主鍵id 是不會(huì)變的。
在redis 中設(shè)計(jì) 稀疏索引。
- 在redis 中設(shè)計(jì) 稀疏索引。
- key = a+b+頁面
- value = 這頁的起始id
- 比如 以每頁2條數(shù)據(jù)為例
- key1 = ab1 value =0;
- key2 = ab1 value =4;
- key3 = ab1 value =8;
- .....
- 那么第一頁：
- select * from t where id>0 and a='a' b='b' limit 2;
- 第二頁：
- select * from t where id>4 and a='a' b='b' limit 2;
- 第三頁：
- select * from t where id>8 and a='a' b='b' limit 2;
- ....
那么這樣就能很快定位到每頁的起始id，少了大量的掃描操作，同時(shí)使用了索引，雖然 ab 聯(lián)合索引 在ab 值都是一樣的時(shí)候 區(qū)分度不高，但是這樣也保證了id的順序，不用order by。因?yàn)橹麈I索引的id 本來就是有序的。
稀疏索引的計(jì)算時(shí)機(jī)：
在一批數(shù)據(jù)入庫完成后開始稀疏索引的計(jì)算。
計(jì)算方法：
第一頁 ：id = 0
- 第一頁數(shù)據(jù)
- select * from t where id>0 and a='a' b='b' limit 2;
第二頁：id計(jì)算方法;
- select max(t.id) from (select * from t where id>0 and a='a' b='b' limit 2) t;
第三頁：id計(jì)算方法;
- select max(t.id) from (select * from t where id>【第二頁id】 and a='a' b='b' limit 2) t;
..........
依次類推.....
然后寫入redis ,更新也是同樣的道理。
為什么不用覆蓋索引呢?
有人肯定會(huì)說為什么不用覆蓋索引呢，這樣就不用回表了啊!
答案是不能;
假如我們返回的 字段 是 a,b ,c d,e,f,那么我們建一個(gè) 覆蓋索引 x。x的B+樹如下：
那如果這個(gè)時(shí)候 我改了id=5 的值a=4 改為a =1
那現(xiàn)在id 就是不是順序的了!!!!!!
那用覆蓋索引+order by id 呢?
數(shù)據(jù)量不大的話也是可以的，但是這又是何必呢。我們看看order by 的原理。
首先 MySQL 會(huì)為每個(gè)查詢線程分配一塊內(nèi)存，叫做 sort_buffer，這塊內(nèi)存的作用就是用來排序的。這塊內(nèi)存有多大呢?由參數(shù) **sort_buffer_size** 控制，可以通過如下命令來查看。
- # 查看sort_buffer的大小
- show variables like 'sort_buffer_size';
這樣有兩個(gè)問題：
每次都是按照篩選條件全量排序
如果數(shù)據(jù)量太大內(nèi)存不夠會(huì)觸發(fā)文件排序，比較慢。
所以還是老老實(shí)實(shí)用了剛剛的方案。效果也還不錯(cuò)，也是僅僅加了幾行代碼而已
這個(gè)臨時(shí)方案也是平穩(wěn)運(yùn)行了1年多。(>‿◠)
