作者：馬佩
鏈接：https://juejin.cn/post/7146016771936354312

場景

當我們業務資料庫表中的資料越來越多，如果你也和我遇到了以下類似場景，那讓我們一起來解決這個問題

• 資料的插入,查詢時長較長
• 后續業務需求的擴展 在表中新增欄位 影響較大
• 表中的資料并不是所有的都為有效資料 需求只查詢時間區間內的

評估表資料體量

我們可以從表容量/磁盤空間/實體容量三方面評估資料體量，接下來讓我們分別展開來看看，

推薦一個開源免費的 Spring Boot 最全教程：

https://github.com/javastacks/spring-boot-best-practice

表容量：

表容量主要從表的記錄數、平均長度、增長量、讀寫量、總大小量進行評估，一般對于OLTP的表，建議單表不要超過2000W行資料量，總大小15G以內，訪問量：單表讀寫量在1600/s以內

查詢行資料的方式： 我們一般查詢表資料有多少資料時用到的經典sql陳述句如下：

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

但是當資料量過大的時候，這樣的查詢就可能會超時，所以我們要換一種查詢方式：

use 庫名;
show table status like '表名' ; 
show table status like '表名'\G ;

上述方法不僅可以查詢表的資料，還可以輸出表的詳細資訊 , 加 \G 可以格式化輸出，包括表名 存盤引擎 版本 行數 每行的位元組數等等，大家可以自行試一下哈

磁盤空間

查看指定資料庫容量大小

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

查詢單個庫中所有表磁盤占用大小

select
table_schema as '資料庫',
table_name as '表名',
table_rows as '記錄數',
truncate(data_length/1024/1024, 2) as '資料容量(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;

查詢出的結果如下：

建議資料量占磁盤使用率的70%以內，同時，對于一些資料增長較快，可以考慮使用大的慢盤進行資料歸檔（歸檔可以參考方案三）

實體容量

MySQL是基于執行緒的服務模型，因此在一些并發較高的場景下，單實體并不能充分利用服務器的CPU資源，吞吐量反而會卡在mysql層，可以根據業務考慮自己的實體模式

出現問題的原因

上面我們已經查到我們資料表的體量了 那么為什么單表資料量越大 業務的執行效率就越慢 根本原因是什么呢？

一個表的資料量達到好幾千萬或者上億時，加索引的效果沒那么明顯啦，性能之所以會變差，是因為維護索引的B+樹結構層級變得更高了，查詢一條資料時，需要經歷的磁盤IO變多，因此查詢性能變慢，

大家是否還記得，一個B+樹大概可以存放多少資料量呢？

InnoDB存盤引擎最小儲存單元是頁，一頁大小就是16k，

B+樹葉子存的是資料，內部節點存的是鍵值+指標，索引組織表通過非葉子節點的二分查找法以及指標確定資料在哪個頁中，進而再去資料頁中找到需要的資料；

假設B+樹的高度為2的話，即有一個根結點和若干個葉子結點，這棵B+樹的存放總記錄數為=根結點指標數*單個葉子節點記錄行數，

• 如果一行記錄的資料大小為1k，那么單個葉子節點可以存的記錄數 =16k/1k =16.
• 非葉子節點記憶體放多少指標呢？我們假設主鍵ID為bigint型別，長度為8位元組(面試官問你int型別，一個int就是32位，4位元組)，而指標大小在InnoDB原始碼中設定為6位元組，所以就是8+6=14位元組，16k/14B =16*1024B/14B = 1170

因此，一棵高度為2的B+樹，能存放1170 * 16=18720條這樣的資料記錄，同理一棵高度為3的B+樹，能存放1170 *1170 *16 =21902400，也就是說，可以存放兩千萬左右的記錄，B+樹高度一般為1-3層，已經滿足千萬級別的資料存盤，

如果B+樹想存盤更多的資料，那樹結構層級就會更高，查詢一條資料時，需要經歷的磁盤IO變多，因此查詢性能變慢，

如何解決單表資料量太大，查詢變慢的問題

知道了根本原因之后，我們就需要考慮如何優化資料庫來解決問題了

這里提供了三種解決方案，包括資料表磁區，分庫分表，冷熱資料歸檔 了解完這些方案之后大家可以選取適合自己業務的方案

方案一：資料表磁區

為什么要磁區：表磁區可以在區間內查詢對應的資料，降低查詢范圍 并且索引磁區 也可以進一步提高命中率，提升查詢效率

磁區是指將一個表的資料按照條件分布到不同的檔案上面，未磁區前都是存放在一個檔案上面的，但是它還是指向的同一張表，只是把資料分散到了不同檔案而已，

我們首先看一下磁區有什么優缺點：

表磁區有什么好處？

1. 與單個磁盤或檔案系統磁區相比，可以存盤更多的資料，
2. 對于那些已經失去保存意義的資料，通常可以通過洗掉與那些資料有關的磁區，很容易地洗掉那些資料，相反地，在某些情況下，添加新資料的程序又可以通過為那些新資料專門增加一個新的磁區，來很方便地實作，
3. 一些查詢可以得到極大的優化，這主要是借助于滿足一個給定WHERE陳述句的資料可以只保存在一個或多個磁區內，這樣在查找時就不用查找其他剩余的磁區，因為磁區可以在創建了磁區表后進行修改，所以在第一次配置磁區方案時還不曾這么做時，可以重新組織資料，來提高那些常用查詢的效率，
4. 涉及到例如SUM()和COUNT()這樣聚合函式的查詢，可以很容易地進行并行處理，這種查詢的一個簡單例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”，通過“并行”，這意味著該查詢可以在每個磁區上同時進行，最終結果只需通過總計所有磁區得到的結果，
5. 通過跨多個磁盤來分散資料查詢，來獲得更大的查詢吞吐量，

表磁區的限制因素

1. 一個表最多只能有1024個磁區，
2. MySQL5.1中，磁區運算式必須是整數，或者回傳整數的運算式，在MySQL5.5中提供了非整數運算式磁區的支持，
3. 如果磁區欄位中有主鍵或者唯一索引的列，那么多有主鍵列和唯一索引列都必須包含進來，即：磁區欄位要么不包含主鍵或者索引列，要么包含全部主鍵和索引列，
4. 磁區表中無法使用外鍵約束，
5. MySQL的磁區適用于一個表的所有資料和索引，不能只對表資料磁區而不對索引磁區，也不能只對索引磁區而不對表磁區，也不能只對表的一部分資料磁區，

在進行磁區之前可以用如下方法 看下資料庫表是否支持磁區哈

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

方案二：資料庫分表

為什么要分表：分表后，顯而易見，單表資料量降低，樹的高度變低，查詢經歷的磁盤io變少，則可以提高效率

mysql 分表分為兩種 水平分表和垂直分表

分庫分表就是為了解決由于資料量過大而導致資料庫性能降低的問題，將原來獨立的資料庫拆分成若干資料庫組成 ，將資料大表拆分成若干資料表組成，使得單一資料庫、單一資料表的資料量變小，從而達到提升資料庫性能的目的，

水平分表

定義：資料表行的拆分，通俗點就是把資料按照某些規則拆分成多張表或者多個庫來存放，分為庫內分表和分庫， 比如一個表有4000萬資料，查詢很慢，可以分到四個表，每個表有1000萬資料

垂直分表

定義：列的拆分，根據表之間的相關性進行拆分，常見的就是一個表把不常用的欄位和常用的欄位就行拆分，然后利用主鍵關聯，或者一個資料庫里面有訂單表和用戶表，資料量都很大，進行垂直拆分，用戶庫存用戶表的資料，訂單庫存訂單表的資料

缺點：垂直分隔的缺點比較明顯，資料不在一張表中，會增加join 或 union之類的操作

知道了兩個知識后，我們來看一下分庫分表的方案

1.取模方案：

拆分之前，先預估一下資料量，比如用戶表有4000w資料，現在要把這些資料分到4個表user1 user2 uesr3 user4， 比如id = 17，17對4取模為1，加上 ，所以這條資料存到user2表，

注意：進行水平拆分后的表要去掉auto_increment自增長，這時候的id可以用一個id 自增長臨時表獲得，或者使用 redis incr的方法，

優點： 資料均勻的分到各個表中，出現熱點問題的概率很低，

缺點：以后的資料擴容遷移比較困難難，當資料量變大之后，以前分到4個表現在要分到8個表，取模的值就變了，需要重新進行資料遷移，

2.range 范圍方案

以范圍進行拆分資料，就是在某個范圍內的訂單，存放到某個表中，比如id=12存放到user1表，id=1300萬的存放到user2 表，

優點：有利于將來對資料的擴容

缺點：如果熱點資料都存在一個表中，則壓力都在一個表中，其他表沒有壓力，

我們看到以上兩種方案 都存在缺點 但是卻又是互補的，那么我們將這兩個方案結合會怎樣呢？

3.hash取模和range方案結合

如下圖 我們可以看到 group 組存放id 為0~4000萬的資料，然后有三個資料庫 DB0 DB1 DB2，DB0里面有四個資料庫，DB1 和DB2 有三個資料庫

假如id為15000 然后對10取模（為啥對10 取模 因為有10個表），取0 然后 落在DB_0,然后在根據range 范圍，落在Table_0 里面，

總結：采用hash取模和range方案結合 既可以避免熱點資料的問題，也有利于將來對資料的擴容

我們已經了解了 mysql磁區和分表的知識 那我們看一下這兩個技術有何不同以及適用場景

磁區分表的區別：

1、實作方式上

• mysql的分表是真正的分表，一張表分成很多表后，每一個小表都是完整的一張表，都對應三個檔案，一個.MYD資料檔案，.MYI索引檔案，.frm表結構
• 磁區不一樣，一張大表進行磁區后，他還是一張表，不會變成二張表，但是他存放資料的區塊變多了，

2、提高性能上

• 分表重點是存取資料時，如何提高mysql并發能力上；
• 而磁區呢，如何突破磁盤的讀寫能力，從而達到提高mysql性能的目的，

3、實作的難易度上

1、分表的方法有很多，用merge來分表，是最簡單的一種方式，這種方式根磁區難易度差不多，并且對程式代碼來說可以做到透明的，如果是用其他分表方式就比磁區麻煩了， 2、磁區實作是比較簡單的，建立磁區表，根建平常的表沒什么區別，并且對開代碼端來說是透明的

磁區分表的聯系

1、都能提高mysql的性高，在高并發狀態下都有一個良好的表現，

2、分表和磁區不矛盾，可以相互配合的，對于那些大訪問量，并且表資料比較多的表，我們可以采取分表和磁區結合的方式，訪問量不大，但是表資料很多的表，我們可以采取磁區的方式等，

分庫分表存在的問題

1、事務問題

在執行分庫分表之后，由于資料存盤到了不同的庫上，資料庫事務管理出現了困難，如果依賴資料庫本身的分布式事務管理功能去執行事務，將付出高昂的性能代價；如果由應用程式去協助控制，形成程式邏輯上的事務，又會造成編程方面的負擔，

2、跨庫跨表的join問題

在執行了分庫分表之后，難以避免會將原本邏輯關聯性很強的資料劃分到不同的表、不同的庫上，這時，表的關聯操作將受到限制，我們無法join位于不同分庫的表，也無法join分表粒度不同的表，結果原本一次查詢能夠完成的業務，可能需要多次查詢才能完成，

3、額外的資料管理負擔和資料運算壓力

額外的資料管理負擔，最顯而易見的就是資料的定位問題和資料的增刪改查的重復執行問題，這些都可以通過應用程式解決，但必然引起額外的邏輯運算，例如，對于一個記錄用戶成績的用戶資料表userTable，業務要求查出成績最好的100位，在進行分表之前，只需一個order by陳述句就可以搞定，但是在進行分表之后，將需要n個order by陳述句，分別查出每一個分表的前100名用戶資料，然后再對這些資料進行合并計算，才能得出結果，

方案三：冷熱歸檔

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

接下來講一下歸檔的程序

1. 創建歸檔表 創建的歸檔表 原則上要與原表保持一致
2. 歸檔表資料的初始化

1. 業務增量資料處理程序

1. 資料的獲取程序

以上三種方案我們如何選型

大家可以根據自己的業務場景，去選擇合適自己業務的方案，我這邊就給大家提供一下思路～

那么到了這里，我要講的內容就差不多結束了，如果有什么不對的，或者有什么疑惑，歡迎大家指點！

參考文章：https://blog.csdn.net/qq_36770474/article/details/119537186

近期熱文推薦：

1.1,000+ 道 Java面試題及答案整理(2022最新版)

2.勁爆！Java 協程要來了，，，

3.Spring Boot 2.x 教程，太全了！

4.別再寫滿屏的爆爆爆炸類了，試試裝飾器模式，這才是優雅的方式！！

5.《Java開發手冊（嵩山版）》最新發布，速速下載！

覺得不錯，別忘了隨手點贊+轉發哦！

標籤：其他

上一篇：我的編程學習小圈子

下一篇：Disruptor-原始碼解讀