Mysql如何處理大數據表？處理方案分享

Mysql如何處理大數據表？以下這篇文章跟大家介紹Mysql大數據表處理方案，希望對大家有幫助。

場景：

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

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

#評估資料表資料量

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

表容量：

##表容量主要從表格的記錄數、平均長度、增長量、讀寫量、總大小量進行評估。一般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變多，因此查詢效能變慢。

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

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

这里提供了三种解决方案，包括数据表分区，分库分表，冷热数据归档 了解完这些方案之后大家可以选取适合自己业务的方案

方案一：数据表分区

为什么要分区：表分区可以在区间内查询对应的数据，降低查询范围 并且索引分区 也可以进一步提高命中率，提升查询效率

分区是指将一个表的数据按照条件分布到不同的文件上面，未分区前都是存放在一个文件上面的，但是它还是指向的同一张表，只是把数据分散到了不同文件而已。

我们首先看一下分区有什么优缺点：

表分区有什么好处？

• 与单个磁盘或文件系统分区相比，可以存储更多的数据。

• 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

• 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

• 涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。

• 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

表分区的限制因素

• 一个表最多只能有1024个分区。

• MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.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 .查詢場景只在某個區3.沒有聯合查詢的場景	區分錶是在物理上對資料表所對應的文件進行拆分，對應的表名是不變的，所以不會影響到先前業務邏輯的sql	分錶後的查詢等業務會建立對應的對象，也會造成一定的開銷分區資料若要聚合的話耗費時間也較長；使用範圍不適合數據量千萬級以上的
資料表分錶	資料量較大,無法區分明顯冷熱區  且資料可以完整依照區間分割	適用於對冷熱分區的界限不是很明顯的數據，對後續類似的數據可以採用該方式，將大表拆分成小表提高查詢插入等效率	若大數據表逐漸增加那麼對應的資料庫表越來越多每個表都需要分錶；區間的劃分較為固定若後續單表的資料量大起來也會對效能造成影響；實現複雜度相對方案三比較複雜  需要測試整個實作過程在編碼層處理對原有業務有影響；
冷熱歸檔分庫	資料量較大；資料冷熱分區明顯；冷資料使用頻率極低；	資料遷移的流程對業務的影響較小開發量也較少減少成本	#需要確認分錶規則

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

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

【相關推薦：mysql影片教學】

#

以上是Mysql如何處理大數據表？處理方案分享的詳細內容。更多資訊請關注PHP中文網其他相關文章！