共計 6497 個字符，預計需要花費 17 分鐘才能閱讀完成。
這篇文章給大家介紹 mysql 分表分區的示例分析，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。
面對當今大數據存儲，設想當 mysql 中一個表的總記錄超過 1000W，會出現性能的大幅度下降嗎？
答案是肯定的，一個表的總記錄超過 1000W，在操作系統層面檢索也是效率非常低的
解決方案：
目前針對海量數據的優化有兩種方法：
1、大表拆小表的方式（主要有分表和分區兩者技術）
（1）分表技術
垂直分割
優勢：降低高并發情況下，對于表的鎖定。
不足：對于單表來說，隨著數據庫的記錄增多，讀寫壓力將進一步增大。
水平分割
如果單表的 IO 壓力大，可以考慮用水平分割，其原理就是通過 hash 算法，將一張表分為 N 多頁，并通過一個新的表（總表），記錄著每個頁的的位置。假如一
個門戶網站，它的數據庫表已經達到了 1000 萬條記錄，那么此時如果通過 select 去查詢，必定會效率低下（不做索引的前提下）。為了降低單表的讀寫
IO 壓力，通過水平分割，將這個表分成 10 個頁，同時生成一個總表，記錄各個頁的信息，那么假如我查詢一條 id=100 的記錄，它不再需要全表掃描，而是
通過總表找到該記錄在哪個對應的頁上，然后再去相應的頁做檢索，這樣就降低了 IO 壓力。
水平分表技術就是將一個表拆成多個表，比較常見的方式就是將表中的記錄按照某種 HASH 算法進行拆分，同時，這種分區方法也必須對前端的應用程序中的
SQL 進行修改方能使用，而且對于一個 SQL 語句，可能會修改兩個表，那么你必須要修改兩個 SQL 語句來完成你這個邏輯的事務，會使得邏輯判斷越來越復
雜，這樣會增加程序的維護代價，所以我們要避免這樣的情況出現。
2、SQL 語句的優化 (索引)
SQL 語句優化：可以通過增加索引等來調整，但同時數據量的增大會導致索引的維護代價增大。
分區優點：
1、減少 IO
2、提高讀寫
3、方便數據管理
分區與分表的區別：
分區是邏輯層面進行了水平分割，對于應用程序來說，它仍是一張表。
分區就是把一張表的數據分成 N 多個區塊，這些區塊可以在同一個磁盤上，也可以在不同的磁盤上
1. 實現方式上
（1）mysql 的分表是真正的分表，一張表分成很多表后，每一個小表都是完整的一張表，都對應三個文件，一個.MYD 數據文件，.MYI 索引文件，.frm 表結構文件。
[root@BlackGhost test]# ls |grep user
alluser.MRG
alluser.frm
user1.MYD
user1.MYI
user1.frm
user2.MYD
user2.MYI
user2.frm
簡單說明一下，上面的分表是利用了 merge 存儲引擎（分表的一種），alluser 是總表，下面有二個分表，user1，user2。他們二個都是獨立
的表，取數據的時候，我們可以通過總表來取。這里總表是沒有.MYD,.MYI 這二個文件的，也就是說，總表他不是一張表，沒有數據，數據都放在分表里
面。我們來看看.MRG 到底是什么東西
[root@BlackGhost test]# cat alluser.MRG |more
user1
user2
#INSERT_METHOD=LAST
從上面我們可以看出，alluser.MRG 里面就存了一些分表的關系，以及插入數據的方式?？梢园芽偙砝斫獬梢粋€外殼，或者是連接池。
（2）分區不一樣，一張大表進行分區后，他還是一張表，不會變成二張表，但是他存放數據的區塊變多了。
[root@BlackGhost test]# ls |grep aa
aa#P#p1.MYD
aa#P#p1.MYI
aa#P#p2.MYD
aa#P#p2.MYI
aa#P#p3.MYD
aa#P#p3.MYI
aa.frm
aa.par
從上面我們可以看出，aa 這張表，分為 3 個區。我們都知道一張表對應三個文件.MYD,.MYI,.frm。分區根據一定的規則把數據文件和索引文件進行
了分割，還多出了一個.par 文件，打開.par 文件后你可以看出他記錄了，這張表的分區信息，跟分表中的.MRG 有點像。分區后，還是一張，而不是多張
表。
2. 數據處理上
（1）分表后，數據都是存放在分表里，總表只是一個外殼，存取數據發生在一個一個的分表里面?？聪旅娴睦樱?/p>
select * from user1 user2 where id= 12 表面上看，是對表 alluser 進行操作的，其實不是的。是對 alluser 里面的分表進行了操作。
（2）分區，不存在分表的概念，分區只不過把存放數據的文件分成了許多小塊，分區后的表，還是一張表。數據處理還是由自己來完成。
select * from alluser where id= 12
3. 提高性能上
（1）
分表后，單表的并發能力提高了，磁盤 I / O 性能也提高了。因為查詢一次所花的時間變短了，如果出現高并發的話，總表可以根據不同的查詢，將并發壓力分到不
同的小表里面。本來一個非常大的.MYD 文件現在也分攤到各個小表的.MYD 中去了，因此對于磁盤 IO 壓力也降低了。
（2）mysql 提出了分區的概念，我覺得就想突破磁盤 I / O 瓶頸，想提高磁盤的讀寫能力，來增加 mysql 性能。
在這一點上，分區和分表的側重點不同，分表重點是存取數據時，如何提高 mysql 并發能力上；而分區呢，則是如何突破磁盤的讀寫能力，從而達到提高 mysql 性能的目的。
4. 實現的難易度上
（1）分表的方法有很多，用 merge 來分表，是最簡單的一種方式。這種方式根分區難易度差不多，并且對程序代碼來說可以做到透明的。如果是用其他分表方式就比分區麻煩了。
（2）分區實現是比較簡單的，建立分區表，跟建平常的表沒什么區別，并且對開代碼端來說是透明的。
分區類型
hash、range、list、key
RANGE 分區：基于一個給定連續區間的列值，把多行分配給分區。
LIST 分區：類似于按 RANGE 分區，區別在于 LIST 分區是基于列值匹配一個離散值集合中的某個值來進行選擇。
HASH 分區：基于用戶定義的表達式的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含 MySQL 中有效的、產生非負整數值的任何表達式。
hash 用在數據相對比較隨機的情況下。它是根據表中的內容進行 hash 運算后隨機平均分配，假設這個列是性別，則不適合用 hash 分區，因為內容要么是男，要么是女，沒有隨機性。
KEY 分區：類似于按 HASH 分區，區別在于 KEY 分區只支持計算一列或多列，且 MySQL 服務器提供其自身的哈希函數。必須有一列或多列包含整數值。—- 很少用到
如何查看數據庫是否支持分區技術？
創建分區：
mysql create table t1(id int)partition by hash(id)partitions 3;
Query OK, 0 rows affected (0.03 sec)
【實驗】
分別創建一個分區的表和非分區的表，進行性能測試
創建分區表
mysql create table part_tab (c1 int default NULL, c2 varchar(30) default null, c3 date default null) engine=myisam
– partition by range(year(c3))(
– partition p0 values less than (1995),
– partition p1 values less than (1996),
– partition p2 values less than (1997),
– partition p3 values less than (1998),
– partition p4 values less than (1999),
– partition p5 values less than (2000),
– partition p6 values less than (2001),
– partition p7 values less than (2002),
– partition p8 values less than (2003),
– partition p9 values less than (2004),
– partition p10 values less than (2010),
– partition p11 values less than MAXVALUE);
Query OK, 0 rows affected (0.14 sec)
創建非分區表
mysql create table no_part_tab (c1 int default NULL, c2 varchar(30) default null, c3 date default null) engine=myisam;
Query OK, 0 rows affected (0.11 sec)
mysql \d // #由于下面要用到存儲過程，這里需要修改結束符為“//”。所謂的存儲過程其實也就是眾多 sql 語句的集合。
mysql create procedure load_part_tab()
– begin
– declare v int default 0;
– while v 8000000
– do
– insert into part_tab
– values (v, testing partitions ,adddate( 1995-01-01 ,(rand(v)*36520)mod 3652));
– set v = v+1;
– end while;
– end
– //
Query OK, 0 rows affected (0.04 sec)
mysql \d ; // 執行完這個存儲過程后，需要將結束符修改回去
上面的存儲過程實際上是為了創建大量的數據（800 萬條）
mysql call load_part_tab(); // 調用 load_part_tab 這個存儲過程
Query OK, 1 row affected (9 min 18.95 sec)
快速將 part_tab 里面的數據插入到 no_part_tab 里面
mysql insert no_part_tab select * from part_tab;
Query OK, 8000000 rows affected (8.97 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
測試一：
實驗之前確保兩個表里面的數據是一致的！保證實驗的可比性
mysql select count(*) from part_tab where c3 date 1995-01-01 and c3 date 1995-12-31
+———-+
| count(*) |
+———-+
| 795181 |
+———-+
1 row in set (0.49 sec)
mysql select count(*) from no_part_tab where c3 date 1995-01-01 and c3 date 1995-12-31
+———-+
| count(*) |
+———-+
| 795181 |
+———-+
1 row in set (3.94 sec)
mysql desc select count(*) from part_tab where c3 date 1995-01-01 and c3 date 1995-12-31
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: part_tab
type: ALL // 全表掃描
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 798458
Extra: Using where
1 row in set (0.09 sec)
ERROR:
No query specified
mysql desc select count(*) from no_part_tab where c3 date 1995-01-01 and c3 date 1995-12-31
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: no_part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8000000
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
結論：可以看到，做了分區之后，只需要掃描 79 萬條語句，而不做分區的，則需要進行全表掃描，故可以看出，做了分區技術后，可以提高讀寫效率。
測試 2：
創建索引，查看語句執行情況
mysql create index idx_c3 on no_part_tab(c3)；
Query OK, 8000000 rows affected (32.68 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
結果分析：
mysql desc select count(*) from no_part_tab where c3 date 1995-01-01 and c3 date 1995-12-31
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NO_part_tab
type: range
possible_keys: idx_c3
key: idx_c3
key_len: 4
ref: NULL
rows: 785678
Extra: Using where; Using index
1 row in set (0.16 sec)
ERROR:
No query specified
結論：為未分區的表創建了索引之后，再次執行相同的語句，可以看到該 SQL 語句是根據 range 索引進行檢索，而不是全表掃描了。明顯效率也提高了。
測試 3：
測試做索引與未作索引的讀寫效率。
mysql create index idx_c3 on part_tab(c3);
Query OK, 8000000 rows affected (31.85 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
mysql desc select count(*) from part_tab where c3 date 1995-01-01 and c3 date 1995-12-31
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: part_tab
type: index
possible_keys: idx_c3
key: idx_c3
key_len: 4
ref: NULL
rows: 798458
Extra: Using where; Using index
1 row in set (0.14 sec)
ERROR:
No query specified
測試未創建索引字段
mysql select count(*) from no_part_tab where c3 date 1995-01-01 and c3 date 1995-12-31 and c2= hello
+———-+
| count(*) |
+———-+
| 0 |
+———-+
1 row in set (4.90 sec)
結論：可以看到如果沒通過索引進行檢索所耗費的時間將長于通過索引進行檢索。
測試 4：刪除
mysql delete from part_tab where c3 date 1995-01-01 and c3 date 1995-12-31
Query OK, 795181 rows affected (14.02 sec)
mysql delete from no_part_tab where c3 date 1995-01-01 and c3 date 1995-12-31
Query OK, 795181 rows affected (15.21 sec)
可以看到，在刪除方面，有分區的還是比沒分區的快一點。從而體現了其便于數據管理的特點。
關于 mysql 分表分區的示例分析就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
