共計 5995 個字符，預(yù)計需要花費 15 分鐘才能閱讀完成。

這篇文章主要介紹“Mysql 怎么處理大數(shù)據(jù)表”，在日常操作中，相信很多人在 Mysql 怎么處理大數(shù)據(jù)表問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Mysql 怎么處理大數(shù)據(jù)表”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學(xué)習(xí)吧！

場景：

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

數(shù)據(jù)的插入, 查詢時長較長

后續(xù)業(yè)務(wù)需求的擴(kuò)展 在表中新增字段 影響較大

表中的數(shù)據(jù)并不是所有的都為有效數(shù)據(jù)   需求只查詢時間區(qū)間內(nèi)的

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

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

表容量：

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

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

select count(*) from table

select count(1) from table
但是當(dāng)數(shù)據(jù)量過大的時候，這樣的查詢就可能會超時，所以我們要換一種查詢方式

use 庫名

show table status like 表名 或 show table status like 表名 \G ;

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

磁盤空間

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

select
table_schema as  數(shù)據(jù)庫 ,
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;

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

select
table_schema as  數(shù)據(jù)庫 ,
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ù)據(jù)增長較快，可以考慮使用大的慢盤進(jìn)行數(shù)據(jù)歸檔（歸檔可以參考方案三）

實例容量

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

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

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

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

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

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

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

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

如果一行記錄的數(shù)據(jù)大小為 1k，那么單個葉子節(jié)點可以存的記錄數(shù) =16k/1k =16.

非葉子節(jié)點內(nèi)存放多少指針呢？我們假設(shè)主鍵 ID 為 bigint 類型，長度為 8 字節(jié) (面試官問你 int 類型，一個 int 就是 32 位，4 字節(jié))，而指針大小在 InnoDB 源碼中設(shè)置為 6 字節(jié)，所以就是 8 +6=14 字節(jié)，16k/14B =16*1024B/14B = 1170

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

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

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

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

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

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

為什么要分區(qū)：表分區(qū)可以在區(qū)間內(nèi)查詢對應(yīng)的數(shù)據(jù)，降低查詢范圍 并且索引分區(qū) 也可以進(jìn)一步提高命中率，提升查詢效率

分區(qū)是指將一個表的數(shù)據(jù)按照條件分布到不同的文件上面，未分區(qū)前都是存放在一個文件上面的，但是它還是指向的同一張表，只是把數(shù)據(jù)分散到了不同文件而已。

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

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

與單個磁盤或文件系統(tǒng)分區(qū)相比，可以存儲更多的數(shù)據(jù)。

對于那些已經(jīng)失去保存意義的數(shù)據(jù)，通常可以通過刪除與那些數(shù)據(jù)有關(guān)的分區(qū)，很容易地刪除那些數(shù)據(jù)。相反地，在某些情況下，添加新數(shù)據(jù)的過程又可以通過為那些新數(shù)據(jù)專門增加一個新的分區(qū)，來很方便地實現(xiàn)。

一些查詢可以得到極大的優(yōu)化，這主要是借助于滿足一個給定 WHERE 語句的數(shù)據(jù)可以只保存在一個或多個分區(qū)內(nèi)，這樣在查找時就不用查找其他剩余的分區(qū)。因為分區(qū)可以在創(chuàng)建了分區(qū)表后進(jìn)行修改，所以在第一次配置分區(qū)方案時還不曾這么做時，可以重新組織數(shù)據(jù)，來提高那些常用查詢的效率。

涉及到例如 SUM() 和 COUNT() 這樣聚合函數(shù)的查詢，可以很容易地進(jìn)行并行處理。這種查詢的一個簡單例子如“SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通過“并行”，這意味著該查詢可以在每個分區(qū)上同時進(jìn)行，最終結(jié)果只需通過總計所有分區(qū)得到的結(jié)果。

通過跨多個磁盤來分散數(shù)據(jù)查詢，來獲得更大的查詢吞吐量。

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

一個表最多只能有 1024 個分區(qū)。

MySQL5.1 中，分區(qū)表達(dá)式必須是整數(shù)，或者返回整數(shù)的表達(dá)式。在 MySQL5.5 中提供了非整數(shù)表達(dá)式分區(qū)的支持。

如果分區(qū)字段中有主鍵或者唯一索引的列，那么多有主鍵列和唯一索引列都必須包含進(jìn)來。即：分區(qū)字段要么不包含主鍵或者索引列，要么包含全部主鍵和索引列。

分區(qū)表中無法使用外鍵約束。

MySQL 的分區(qū)適用于一個表的所有數(shù)據(jù)和索引，不能只對表數(shù)據(jù)分區(qū)而不對索引分區(qū)，也不能只對索引分區(qū)而不對表分區(qū)，也不能只對表的一部分?jǐn)?shù)據(jù)分區(qū)。

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

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

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

為什么要分表：分表后，顯而易見，單表數(shù)據(jù)量降低，樹的高度變低，查詢經(jīng)歷的磁盤 io 變少，則可以提高效率

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

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

水平分表

定義：數(shù)據(jù)表行的拆分，通俗點就是把數(shù)據(jù)按照某些規(guī)則拆分成多張表或者多個庫來存放。分為庫內(nèi)分表和分庫。
比如一個表有 4000 萬數(shù)據(jù)，查詢很慢，可以分到四個表，每個表有 1000 萬數(shù)據(jù)

垂直分表

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

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

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

1. 取模方案：

拆分之前，先預(yù)估一下數(shù)據(jù)量。比如用戶表有 4000w 數(shù)據(jù)，現(xiàn)在要把這些數(shù)據(jù)分到 4 個表 user1 user2  uesr3 user4。
比如 id = 17，17 對 4 取模為 1，加上，所以這條數(shù)據(jù)存到 user2 表。

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

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

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

2.range 范圍方案

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

優(yōu)點：有利于將來對數(shù)據(jù)的擴(kuò)容

缺點：如果熱點數(shù)據(jù)都存在一個表中，則壓力都在一個表中，其他表沒有壓力。

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

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

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

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

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

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

分區(qū)分表的區(qū)別：1、實現(xiàn)方式上

mysql 的分表是真正的分表，一張表分成很多表后，每一個小表都是完整的一張表，都對應(yīng)三個文件，一個.MYD 數(shù)據(jù)文件，.MYI 索引文件，.frm 表結(jié)構(gòu)

分區(qū)不一樣，一張大表進(jìn)行分區(qū)后，他還是一張表，不會變成二張表，但是他存放數(shù)據(jù)的區(qū)塊變多了。

2、提高性能上

分表重點是存取數(shù)據(jù)時，如何提高 mysql 并發(fā)能力上；

而分區(qū)呢，如何突破磁盤的讀寫能力，從而達(dá)到提高 mysql 性能的目的。

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

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

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

1、都能提高 mysql 的性高，在高并發(fā)狀態(tài)下都有一個良好的表現(xiàn)。

2、分表和分區(qū)不矛盾，可以相互配合的，對于那些大訪問量，并且表數(shù)據(jù)比較多的表，我們可以采取分表和分區(qū)結(jié)合的方式，訪問量不大，但是表數(shù)據(jù)很多的表，我們可以采取分區(qū)的方式等。

分庫分表存在的問題 1、事務(wù)問題

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

2、跨庫跨表的 join 問題

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

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

額外的數(shù)據(jù)管理負(fù)擔(dān)，最顯而易見的就是數(shù)據(jù)的定位問題和數(shù)據(jù)的增刪改查的重復(fù)執(zhí)行問題，這些都可以通過應(yīng)用程序解決，但必然引起額外的邏輯運算，例如，對于一個記錄用戶成績的用戶數(shù)據(jù)表 userTable，業(yè)務(wù)要求查出成績最好的 100 位，在進(jìn)行分表之前，只需一個 order by 語句就可以搞定，但是在進(jìn)行分表之后，將需要 n 個 order by 語句，分別查出每一個分表的前 100 名用戶數(shù)據(jù)，然后再對這些數(shù)據(jù)進(jìn)行合并計算，才能得出結(jié)果。

方案三：冷熱歸檔

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

接下來講一下歸檔的過程

創(chuàng)建歸檔表
創(chuàng)建的歸檔表 原則上要與原表保持一致

歸檔表數(shù)據(jù)的初始化

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

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

以上三種方案我們?nèi)绾芜x型方案試用場景優(yōu)點缺點數(shù)據(jù)表分區(qū) 1. 數(shù)據(jù)量較大 2. 查詢場景只在某個區(qū) 3. 沒有聯(lián)合查詢的場景分區(qū)分表是在物理上對數(shù)據(jù)表所對應(yīng)的文件進(jìn)行拆分，對應(yīng)的表名是不變的，所以不會影響到之前業(yè)務(wù)邏輯的 sql 分表后的查詢等業(yè)務(wù)會創(chuàng)建對應(yīng)的對象，也會造成一定的開銷分區(qū)數(shù)據(jù)若要聚合的話 耗費時間也較長；使用范圍不適合數(shù)據(jù)量千萬級以上的數(shù)據(jù)表分表數(shù)據(jù)量較大, 無法區(qū)分明顯冷熱區(qū)   且數(shù)據(jù)可以完整按照區(qū)間劃分適用于對冷熱分區(qū)的界限不是很明顯的數(shù)據(jù)，對后續(xù)類似的數(shù)據(jù)可以采用該方式，將大表拆分成小表 提高查詢插入等效率若大數(shù)據(jù)表逐漸增多 那么對應(yīng)的數(shù)據(jù)庫表越來越多 每個表都需要分表；區(qū)間的劃分較為固定 若后續(xù)單表的數(shù)據(jù)量大起來 也會對性能造成影響；實現(xiàn)復(fù)雜度相對方案三比較復(fù)雜   需要測試整個實現(xiàn)過程 在編碼層處理 對原有業(yè)務(wù)有影響；冷熱歸檔分庫數(shù)據(jù)量較大；數(shù)據(jù)冷熱分區(qū)明顯；冷數(shù)據(jù)使用頻率極低；數(shù)據(jù)遷移的過程對業(yè)務(wù)的影響較小 開發(fā)量也較少減少成本需要確認(rèn)分表規(guī)則

到此，關(guān)于“Mysql 怎么處理大數(shù)據(jù)表”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>