場(chǎng)景

當(dāng)我們業(yè)務(wù)數(shù)據(jù)庫(kù)表中的數(shù)據(jù)越來(lái)越多，如果你也和我遇到了以下類似場(chǎng)景，那讓我們一起來(lái)解決這個(gè)問(wèn)題。

• 數(shù)據(jù)的插入,查詢時(shí)長(zhǎng)較長(zhǎng)
• 后續(xù)業(yè)務(wù)需求的擴(kuò)展 在表中新增字段 影響較大
• 表中的數(shù)據(jù)并不是所有的都為有效數(shù)據(jù)  需求只查詢時(shí)間區(qū)間內(nèi)的

評(píng)估表數(shù)據(jù)體量

我們可以從表容量/磁盤空間/實(shí)例容量三方面評(píng)估數(shù)據(jù)體量，接下來(lái)讓我們分別展開(kāi)來(lái)看看。

表容量

表容量主要從表的記錄數(shù)、平均長(zhǎng)度、增長(zhǎng)量、讀寫量、總大小量進(jìn)行評(píng)估。一般對(duì)于OLTP的表，建議單表不要超過(guò)2000W行數(shù)據(jù)量，總大小15G以內(nèi)。訪問(wèn)量：?jiǎn)伪碜x寫量在1600/s以內(nèi)。

查詢行數(shù)據(jù)的方式：我們一般查詢表數(shù)據(jù)有多少數(shù)據(jù)時(shí)用到的經(jīng)典sql語(yǔ)句如下：

select count(*) from table
select count(1) from table

但是當(dāng)數(shù)據(jù)量過(guò)大的時(shí)候，這樣的查詢就可能會(huì)超時(shí)，所以我們要換一種查詢方式

use 庫(kù)名
show table status like '表名' ; 
或：show table status like '表名'\G ;

上述方法不僅可以查詢表的數(shù)據(jù)，還可以輸出表的詳細(xì)信息 , 加 \G 可以格式化輸出。包括表名 存儲(chǔ)引擎 版本 行數(shù) 每行的字節(jié)數(shù)等等，大家可以自行試一下哈。

磁盤空間

查看指定數(shù)據(jù)庫(kù)容量大小。

select
table_schema as '數(shù)據(jù)庫(kù)',
table_name as '表名',
table_rows as '記錄數(shù)',
truncate(data_length/1024/1024, 2) as '數(shù)據(jù)容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

查詢單個(gè)庫(kù)中所有表磁盤占用大小。

select
table_schema as '數(shù)據(jù)庫(kù)',
table_name as '表名',
table_rows as '記錄數(shù)',
truncate(data_length/1024/1024, 2) as '數(shù)據(jù)容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

查詢出的結(jié)果如下：

圖片

建議數(shù)據(jù)量占磁盤使用率的70%以內(nèi)。同時(shí)，對(duì)于一些數(shù)據(jù)增長(zhǎng)較快，可以考慮使用大的慢盤進(jìn)行數(shù)據(jù)歸檔（歸檔可以參考方案三）。

實(shí)例容量

MySQL是基于線程的服務(wù)模型，因此在一些并發(fā)較高的場(chǎng)景下，單實(shí)例并不能充分利用服務(wù)器的CPU資源，吞吐量反而會(huì)卡在mysql層，可以根據(jù)業(yè)務(wù)考慮自己的實(shí)例模式。

出現(xiàn)問(wèn)題的原因

上面我們已經(jīng)查到我們數(shù)據(jù)表的體量了 那么為什么單表數(shù)據(jù)量越大 業(yè)務(wù)的執(zhí)行效率就越慢 根本原因是什么呢？

一個(gè)表的數(shù)據(jù)量達(dá)到好幾千萬(wàn)或者上億時(shí)，加索引的效果沒(méi)那么明顯啦。性能之所以會(huì)變差，是因?yàn)榫S護(hù)索引的B+樹(shù)結(jié)構(gòu)層級(jí)變得更高了，查詢一條數(shù)據(jù)時(shí)，需要經(jīng)歷的磁盤IO變多，因此查詢性能變慢。

大家是否還記得，一個(gè)B+樹(shù)大概可以存放多少數(shù)據(jù)量呢？

InnoDB存儲(chǔ)引擎最小儲(chǔ)存單元是頁(yè)，一頁(yè)大小就是16k。

B+樹(shù)葉子存的是數(shù)據(jù)，內(nèi)部節(jié)點(diǎn)存的是鍵值+指針。索引組織表通過(guò)非葉子節(jié)點(diǎn)的二分查找法以及指針確定數(shù)據(jù)在哪個(gè)頁(yè)中，進(jìn)而再去數(shù)據(jù)頁(yè)中找到需要的數(shù)據(jù)；

圖片

假設(shè)B+樹(shù)的高度為2的話，即有一個(gè)根結(jié)點(diǎn)和若干個(gè)葉子結(jié)點(diǎn)。這棵B+樹(shù)的存放總記錄數(shù)為=根結(jié)點(diǎn)指針數(shù)*單個(gè)葉子節(jié)點(diǎn)記錄行數(shù)。

• 如果一行記錄的數(shù)據(jù)大小為1k，那么單個(gè)葉子節(jié)點(diǎn)可以存的記錄數(shù) =16k/1k =16.
• 非葉子節(jié)點(diǎn)內(nèi)存放多少指針呢？我們假設(shè)主鍵ID為bigint類型，長(zhǎng)度為8字節(jié)(面試官問(wèn)你int類型，一個(gè)int就是32位，4字節(jié))，而指針大小在InnoDB源碼中設(shè)置為6字節(jié)，所以就是8+6=14字節(jié)，16k/14B =16*1024B/14B = 1170

因此，一棵高度為2的B+樹(shù)，能存放1170 * 16=18720條這樣的數(shù)據(jù)記錄。同理一棵高度為3的B+樹(shù)，能存放1170 *1170 *16 =21902400，也就是說(shuō)，可以存放兩千萬(wàn)左右的記錄。B+樹(shù)高度一般為1-3層，已經(jīng)滿足千萬(wàn)級(jí)別的數(shù)據(jù)存儲(chǔ)。

如果B+樹(shù)想存儲(chǔ)更多的數(shù)據(jù)，那樹(shù)結(jié)構(gòu)層級(jí)就會(huì)更高，查詢一條數(shù)據(jù)時(shí)，需要經(jīng)歷的磁盤IO變多，因此查詢性能變慢。

如何解決單表數(shù)據(jù)量太大，查詢變慢的問(wèn)題

知道了根本原因之后，我們就需要考慮如何優(yōu)化數(shù)據(jù)庫(kù)來(lái)解決問(wèn)題了。

這里提供了三種解決方案，包括數(shù)據(jù)表分區(qū)，分庫(kù)分表，冷熱數(shù)據(jù)歸檔 了解完這些方案之后大家可以選取適合自己業(yè)務(wù)的方案。

方案一：數(shù)據(jù)表分區(qū)

為什么要分區(qū)：表分區(qū)可以在區(qū)間內(nèi)查詢對(duì)應(yīng)的數(shù)據(jù)，降低查詢范圍 并且索引分區(qū) 也可以進(jìn)一步提高命中率，提升查詢效率 分區(qū)是指將一個(gè)表的數(shù)據(jù)按照條件分布到不同的文件上面，未分區(qū)前都是存放在一個(gè)文件上面的，但是它還是指向的同一張表，只是把數(shù)據(jù)分散到了不同文件而已。

我們首先看一下分區(qū)有什么優(yōu)缺點(diǎn)：

表分區(qū)有什么好處？

• 與單個(gè)磁盤或文件系統(tǒng)分區(qū)相比，可以存儲(chǔ)更多的數(shù)據(jù)。
• 對(duì)于那些已經(jīng)失去保存意義的數(shù)據(jù)，通常可以通過(guò)刪除與那些數(shù)據(jù)有關(guān)的分區(qū)，很容易地刪除那些數(shù)據(jù)。相反地，在某些情況下，添加新數(shù)據(jù)的過(guò)程又可以通過(guò)為那些新數(shù)據(jù)專門增加一個(gè)新的分區(qū)，來(lái)很方便地實(shí)現(xiàn)。
• 一些查詢可以得到極大的優(yōu)化，這主要是借助于滿足一個(gè)給定WHERE語(yǔ)句的數(shù)據(jù)可以只保存在一個(gè)或多個(gè)分區(qū)內(nèi)，這樣在查找時(shí)就不用查找其他剩余的分區(qū)。因?yàn)榉謪^(qū)可以在創(chuàng)建了分區(qū)表后進(jìn)行修改，所以在第一次配置分區(qū)方案時(shí)還不曾這么做時(shí)，可以重新組織數(shù)據(jù)，來(lái)提高那些常用查詢的效率。
• 涉及到例如SUM()和COUNT()這樣聚合函數(shù)的查詢，可以很容易地進(jìn)行并行處理。這種查詢的一個(gè)簡(jiǎn)單例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通過(guò)“并行”，這意味著該查詢可以在每個(gè)分區(qū)上同時(shí)進(jìn)行，最終結(jié)果只需通過(guò)總計(jì)所有分區(qū)得到的結(jié)果。
• 通過(guò)跨多個(gè)磁盤來(lái)分散數(shù)據(jù)查詢，來(lái)獲得更大的查詢吞吐量。

表分區(qū)的限制因素

• 一個(gè)表最多只能有1024個(gè)分區(qū)。
• MySQL5.1中，分區(qū)表達(dá)式必須是整數(shù)，或者返回整數(shù)的表達(dá)式。在MySQL5.5中提供了非整數(shù)表達(dá)式分區(qū)的支持。
• 如果分區(qū)字段中有主鍵或者唯一索引的列，那么多有主鍵列和唯一索引列都必須包含進(jìn)來(lái)。即：分區(qū)字段要么不包含主鍵或者索引列，要么包含全部主鍵和索引列。
• 分區(qū)表中無(wú)法使用外鍵約束。
• MySQL的分區(qū)適用于一個(gè)表的所有數(shù)據(jù)和索引，不能只對(duì)表數(shù)據(jù)分區(qū)而不對(duì)索引分區(qū)，也不能只對(duì)索引分區(qū)而不對(duì)表分區(qū)，也不能只對(duì)表的一部分?jǐn)?shù)據(jù)分區(qū)。

在進(jìn)行分區(qū)之前可以用如下方法 看下數(shù)據(jù)庫(kù)表是否支持分區(qū)哈

mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

方案二：數(shù)據(jù)庫(kù)分表

為什么要分表：分表后，顯而易見(jiàn)，單表數(shù)據(jù)量降低，樹(shù)的高度變低，查詢經(jīng)歷的磁盤io變少，則可以提高效率 mysql 分表分為兩種 水平分表和垂直分表。

分庫(kù)分表就是為了解決由于數(shù)據(jù)量過(guò)大而導(dǎo)致數(shù)據(jù)庫(kù)性能降低的問(wèn)題，將原來(lái)獨(dú)立的數(shù)據(jù)庫(kù)拆分成若干數(shù)據(jù)庫(kù)組成 ，將數(shù)據(jù)大表拆分成若干數(shù)據(jù)表組成，使得單一數(shù)據(jù)庫(kù)、單一數(shù)據(jù)表的數(shù)據(jù)量變小，從而達(dá)到提升數(shù)據(jù)庫(kù)性能的目的。

水平分表

定義：數(shù)據(jù)表行的拆分，通俗點(diǎn)就是把數(shù)據(jù)按照某些規(guī)則拆分成多張表或者多個(gè)庫(kù)來(lái)存放。分為庫(kù)內(nèi)分表和分庫(kù)。

比如一個(gè)表有4000萬(wàn)數(shù)據(jù)，查詢很慢，可以分到四個(gè)表，每個(gè)表有1000萬(wàn)數(shù)據(jù)。

圖片

垂直分表

定義：列的拆分，根據(jù)表之間的相關(guān)性進(jìn)行拆分。常見(jiàn)的就是一個(gè)表把不常用的字段和常用的字段就行拆分，然后利用主鍵關(guān)聯(lián)。或者一個(gè)數(shù)據(jù)庫(kù)里面有訂單表和用戶表，數(shù)據(jù)量都很大，進(jìn)行垂直拆分，用戶庫(kù)存用戶表的數(shù)據(jù)，訂單庫(kù)存訂單表的數(shù)據(jù)。

圖片

缺點(diǎn)：垂直分隔的缺點(diǎn)比較明顯，數(shù)據(jù)不在一張表中，會(huì)增加join 或 union之類的操作。

知道了兩個(gè)知識(shí)后，我們來(lái)看一下分庫(kù)分表的方案。

1.取模方案：

拆分之前，先預(yù)估一下數(shù)據(jù)量。比如用戶表有4000w數(shù)據(jù)，現(xiàn)在要把這些數(shù)據(jù)分到4個(gè)表user1 user2  uesr3 user4。

比如id = 17，17對(duì)4取模為1，加上 ，所以這條數(shù)據(jù)存到user2表。

注意：進(jìn)行水平拆分后的表要去掉auto_increment自增長(zhǎng)。這時(shí)候的id可以用一個(gè)id 自增長(zhǎng)臨時(shí)表獲得，或者使用 redis incr的方法。

圖片

優(yōu)點(diǎn)：數(shù)據(jù)均勻的分到各個(gè)表中，出現(xiàn)熱點(diǎn)問(wèn)題的概率很低。

缺點(diǎn)：以后的數(shù)據(jù)擴(kuò)容遷移比較困難難，當(dāng)數(shù)據(jù)量變大之后，以前分到4個(gè)表現(xiàn)在要分到8個(gè)表，取模的值就變了，需要重新進(jìn)行數(shù)據(jù)遷移。

2.range 范圍方案

以范圍進(jìn)行拆分?jǐn)?shù)據(jù)，就是在某個(gè)范圍內(nèi)的訂單，存放到某個(gè)表中。比如id=12存放到user1表，id=1300萬(wàn)的存放到user2 表。

圖片

優(yōu)點(diǎn)：有利于將來(lái)對(duì)數(shù)據(jù)的擴(kuò)容。

缺點(diǎn)：如果熱點(diǎn)數(shù)據(jù)都存在一個(gè)表中，則壓力都在一個(gè)表中，其他表沒(méi)有壓力。

我們看到以上兩種方案 都存在缺點(diǎn) 但是卻又是互補(bǔ)的，那么我們將這兩個(gè)方案結(jié)合會(huì)怎樣呢？

3.hash取模和range方案結(jié)合

如下圖 我們可以看到 group 組存放id 為0~4000萬(wàn)的數(shù)據(jù)，然后有三個(gè)數(shù)據(jù)庫(kù) DB0 DB1 DB2，DB0里面有四個(gè)數(shù)據(jù)庫(kù)，DB1 和DB2 有三個(gè)數(shù)據(jù)庫(kù)。

假如id為15000 然后對(duì)10取模（為啥對(duì)10 取模 因?yàn)橛?0個(gè)表），取0 然后 落在DB_0,然后在根據(jù)range 范圍，落在Table_0 里面。

圖片

總結(jié)：采用hash取模和range方案結(jié)合 既可以避免熱點(diǎn)數(shù)據(jù)的問(wèn)題，也有利于將來(lái)對(duì)數(shù)據(jù)的擴(kuò)容。

我們已經(jīng)了解了 mysql分區(qū)和分表的知識(shí) 那我們看一下這兩個(gè)技術(shù)有何不同以及適用場(chǎng)景。

分區(qū)分表的區(qū)別

1、實(shí)現(xiàn)方式上

• mysql的分表是真正的分表，一張表分成很多表后，每一個(gè)小表都是完整的一張表，都對(duì)應(yīng)三個(gè)文件，一個(gè).MYD數(shù)據(jù)文件，.MYI索引文件，.frm表結(jié)構(gòu)。
• 分區(qū)不一樣，一張大表進(jìn)行分區(qū)后，他還是一張表，不會(huì)變成二張表，但是他存放數(shù)據(jù)的區(qū)塊變多了。

2、提高性能上

• 分表重點(diǎn)是存取數(shù)據(jù)時(shí)，如何提高mysql并發(fā)能力上；
• 而分區(qū)呢，如何突破磁盤的讀寫能力，從而達(dá)到提高mysql性能的目的。

3、實(shí)現(xiàn)的難易度上

• 分表的方法有很多，用merge來(lái)分表，是最簡(jiǎn)單的一種方式。這種方式根分區(qū)難易度差不多，并且對(duì)程序代碼來(lái)說(shuō)可以做到透明的。如果是用其他分表方式就比分區(qū)麻煩了。
• 分區(qū)實(shí)現(xiàn)是比較簡(jiǎn)單的，建立分區(qū)表，根建平常的表沒(méi)什么區(qū)別，并且對(duì)開(kāi)代碼端來(lái)說(shuō)是透明的。

分區(qū)分表的聯(lián)系

• 都能提高mysql的性高，在高并發(fā)狀態(tài)下都有一個(gè)良好的表現(xiàn)。
• 分表和分區(qū)不矛盾，可以相互配合的，對(duì)于那些大訪問(wèn)量，并且表數(shù)據(jù)比較多的表，我們可以采取分表和分區(qū)結(jié)合的方式，訪問(wèn)量不大，但是表數(shù)據(jù)很多的表，我們可以采取分區(qū)的方式等。

分庫(kù)分表存在的問(wèn)題

1、事務(wù)問(wèn)題

在執(zhí)行分庫(kù)分表之后，由于數(shù)據(jù)存儲(chǔ)到了不同的庫(kù)上，數(shù)據(jù)庫(kù)事務(wù)管理出現(xiàn)了困難。如果依賴數(shù)據(jù)庫(kù)本身的分布式事務(wù)管理功能去執(zhí)行事務(wù)，將付出高昂的性能代價(jià)；如果由應(yīng)用程序去協(xié)助控制，形成程序邏輯上的事務(wù)，又會(huì)造成編程方面的負(fù)擔(dān)。

2、跨庫(kù)跨表的join問(wèn)題

在執(zhí)行了分庫(kù)分表之后，難以避免會(huì)將原本邏輯關(guān)聯(lián)性很強(qiáng)的數(shù)據(jù)劃分到不同的表、不同的庫(kù)上，這時(shí)，表的關(guān)聯(lián)操作將受到限制，我們無(wú)法join位于不同分庫(kù)的表，也無(wú)法join分表粒度不同的表，結(jié)果原本一次查詢能夠完成的業(yè)務(wù)，可能需要多次查詢才能完成。

3、額外的數(shù)據(jù)管理負(fù)擔(dān)和數(shù)據(jù)運(yùn)算壓力

額外的數(shù)據(jù)管理負(fù)擔(dān)，最顯而易見(jiàn)的就是數(shù)據(jù)的定位問(wèn)題和數(shù)據(jù)的增刪改查的重復(fù)執(zhí)行問(wèn)題，這些都可以通過(guò)應(yīng)用程序解決，但必然引起額外的邏輯運(yùn)算。

例如，對(duì)于一個(gè)記錄用戶成績(jī)的用戶數(shù)據(jù)表userTable，業(yè)務(wù)要求查出成績(jī)最好的100位，在進(jìn)行分表之前，只需一個(gè)order by語(yǔ)句就可以搞定，但是在進(jìn)行分表之后，將需要n個(gè)order by語(yǔ)句，分別查出每一個(gè)分表的前100名用戶數(shù)據(jù)，然后再對(duì)這些數(shù)據(jù)進(jìn)行合并計(jì)算，才能得出結(jié)果。

方案三：冷熱歸檔

為什么要冷熱歸檔：其實(shí)原因和方案二類似，都是降低單表數(shù)據(jù)量，樹(shù)的高度變低，查詢經(jīng)歷的磁盤io變少，則可以提高效率 如果大家的業(yè)務(wù)數(shù)據(jù)，有明顯的冷熱區(qū)分，比如：只需要展示近一周或一個(gè)月的數(shù)據(jù)。那么這種情況這一周喝一個(gè)月的數(shù)據(jù)我們稱之為熱數(shù)據(jù)，其余數(shù)據(jù)為冷數(shù)據(jù)。那么我們可以將冷數(shù)據(jù)歸檔在其他的庫(kù)表中，提高我們熱數(shù)據(jù)的操作效率。

接下來(lái)講一下歸檔的過(guò)程

• 創(chuàng)建歸檔表  創(chuàng)建的歸檔表 原則上要與原表保持一致
• 歸檔表數(shù)據(jù)的初始化

圖片

• 業(yè)務(wù)增量數(shù)據(jù)處理過(guò)程

圖片

• 數(shù)據(jù)的獲取過(guò)程

圖片

以上三種方案我們?nèi)绾芜x型

圖片