[[407944]]

前言

有人說(shuō)單表超千萬(wàn)數(shù)據(jù)就應(yīng)該分庫(kù)分表了，這么玩不合理啊。但是對(duì)于創(chuàng)新業(yè)務(wù)來(lái)講，業(yè)務(wù)系統(tǒng)的設(shè)計(jì)不可能一上來(lái)就預(yù)估這么大的容量，成本和工期都不足矣完成系統(tǒng)的開(kāi)發(fā)工作。我覺(jué)得對(duì)于創(chuàng)新型業(yè)務(wù)系統(tǒng)的設(shè)計(jì)，首先滿(mǎn)足需求，其次考慮到萬(wàn)一業(yè)務(wù)井噴發(fā)展所要考慮到的臨時(shí)解決方案，為系統(tǒng)升級(jí)預(yù)留時(shí)間。

誰(shuí)都希望業(yè)務(wù)井噴，那么它來(lái)了!

具體時(shí)間點(diǎn)就不說(shuō)了，開(kāi)始做了一個(gè)新業(yè)務(wù)，見(jiàn)了一個(gè)表，該表累計(jì)數(shù)據(jù)條不超過(guò)100萬(wàn)，提供查詢(xún)功能。后來(lái)業(yè)務(wù)量持續(xù)上漲，mysql 磁盤(pán)開(kāi)始報(bào)警，查詢(xún)超時(shí)報(bào)警。而且，客戶(hù)需要實(shí)時(shí)查詢(xún)?cè)摌I(yè)務(wù)表的數(shù)據(jù)并下載。頭大，臨時(shí)改存儲(chǔ)方案已經(jīng)來(lái)不及了，不能耽誤KPI。

先解決眼下問(wèn)題，先擴(kuò)充磁盤(pán)。停止雙機(jī)房同步，減少不必要的報(bào)警。

但是1000G 估計(jì)也扛不了多久，和業(yè)務(wù)同學(xué)討論后，業(yè)務(wù)接受的范圍T-7范圍內(nèi)的數(shù)據(jù)實(shí)時(shí)查詢(xún)下載。按這個(gè)增長(zhǎng)量，7天也是過(guò)億的記錄條數(shù)。但是7天的數(shù)據(jù)磁盤(pán)肯定是夠用的，那就要先把歷史數(shù)據(jù)離線(xiàn)存儲(chǔ)。

這個(gè)也簡(jiǎn)單，幾行代碼的事兒。當(dāng)然這樣依靠完善的基建。

容量的問(wèn)題解決了，那么改對(duì)數(shù)據(jù)分頁(yè)查詢(xún)的進(jìn)行優(yōu)化。為了說(shuō)明問(wèn)題，去掉敏感的業(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í)，我們用下面的分頁(yè)是沒(méi)有問(wèn)題的：

SELECT id,a,b… FROM t LIMIT n,m 

例如：

pagesize ：每頁(yè)顯示條數(shù)。

pageno:頁(yè)碼

那么 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é)約了很多。

上面是每頁(yè)請(qǐng)求的RT,可見(jiàn)隨著頁(yè)數(shù)的增加，RT 逐漸上升。

Qps 逐漸下降。

那么如果數(shù)據(jù)太多的話(huà)，最后一頁(yè)超時(shí)的概率會(huì)非常大。

優(yōu)化后

先賣(mài)個(gè)關(guān)子，先看看優(yōu)化后的表現(xiàn)，這個(gè)接口的性能明顯提升。如圖所示：

RT 平均在10ms 左右，因?yàn)榉祷刈隽藬?shù)據(jù)處理，RT最終在15ms左右

qps 也很平穩(wěn)，應(yīng)該可以再高一些，取決于客戶(hù)的調(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)。

這里，沒(méi)有必要實(shí)現(xiàn)一個(gè)LSM 樹(shù)，只是參考了其稀疏索引的思想，能夠準(zhǔn)確定位數(shù)據(jù)。這樣就簡(jiǎn)單了。步驟如下：

1.根據(jù)分析業(yè)務(wù)，構(gòu)建一個(gè) 字段 a,b的聯(lián)合索引。因?yàn)閍,b 是數(shù)據(jù)的查詢(xún)條件，且能分離出1/7的數(shù)據(jù)。

ALTER table  ADD INDEX index_a_b('a','b') 

2.因?yàn)檫@個(gè)表的數(shù)據(jù) 都是通過(guò) insert ... on duplicate key update ... 來(lái)更新的，【這也是線(xiàn)上死鎖分析的那篇文章留下的伏筆】，而且 id 是自增主鍵，所以，所有的數(shù)據(jù)都是按照入庫(kù)時(shí)的順序來(lái)的，且后面遇到?jīng)_突時(shí)修改也是update 的，所以主鍵id 是不會(huì)變的。

在redis 中設(shè)計(jì) 稀疏索引。

在redis 中設(shè)計(jì) 稀疏索引。 
key = a+b+頁(yè)面 
value = 這頁(yè)的起始id 
比如 以每頁(yè)2條數(shù)據(jù)為例 
key1 = ab1 value =0; 
key2 = ab1 value =4; 
key3 = ab1 value =8; 
..... 
那么第一頁(yè)： 
select * from t where id>0 and a='a' b='b'  limit 2; 
第二頁(yè)： 
select * from t where id>4 and a='a' b='b'   limit 2; 
第三頁(yè)： 
select * from t where id>8 and a='a' b='b'  limit 2; 
.... 

那么這樣就能很快定位到每頁(yè)的起始id，少了大量的掃描操作，同時(shí)使用了索引，雖然 ab 聯(lián)合索引 在ab 值都是一樣的時(shí)候 區(qū)分度不高，但是這樣也保證了id的順序，不用order by。因?yàn)橹麈I索引的id 本來(lái)就是有序的。

稀疏索引的計(jì)算時(shí)機(jī)：

在一批數(shù)據(jù)入庫(kù)完成后開(kāi)始稀疏索引的計(jì)算。

計(jì)算方法：

第一頁(yè) ：id = 0

第一頁(yè)數(shù)據(jù) 
select * from t where id>0 and a='a' b='b'  limit 2; 

 第二頁(yè)：id計(jì)算方法;

select max(t.id) from (select * from t where id>0 and a='a' b='b'  limit 2) t;  

第三頁(yè)：id計(jì)算方法;

select max(t.id) from (select * from t where id>【第二頁(yè)id】 and a='a' b='b'  limit 2) t;  

..........

依次類(lèi)推.....

然后寫(xiě)入redis ,更新也是同樣的道理。

為什么不用覆蓋索引呢?

有人肯定會(huì)說(shuō)為什么不用覆蓋索引呢，這樣就不用回表了啊!

答案是不能;

假如我們返回的 字段 是 a,b ,c d,e,f,那么我們建一個(gè) 覆蓋索引 x。x的B+樹(shù)如下：

那如果這個(gè)時(shí)候 我改了id=5 的值a=4 改為a =1

那現(xiàn)在id 就是不是順序的了!!!!!!

那用覆蓋索引+order by id 呢?

數(shù)據(jù)量不大的話(huà)也是可以的，但是這又是何必呢。我們看看order by 的原理。

首先 MySQL 會(huì)為每個(gè)查詢(xún)線(xiàn)程分配一塊內(nèi)存，叫做 sort_buffer，這塊內(nèi)存的作用就是用來(lái)排序的。這塊內(nèi)存有多大呢?由參數(shù) **sort_buffer_size** 控制，可以通過(guò)如下命令來(lái)查看。

# 查看sort_buffer的大小 
show variables like 'sort_buffer_size'; 

這樣有兩個(gè)問(wèn)題：

每次都是按照篩選條件全量排序

如果數(shù)據(jù)量太大內(nèi)存不夠會(huì)觸發(fā)文件排序，比較慢。

所以還是老老實(shí)實(shí)用了剛剛的方案。效果也還不錯(cuò)，也是僅僅加了幾行代碼而已

這個(gè)臨時(shí)方案也是平穩(wěn)運(yùn)行了1年多。(>‿◠)