mysql分表分區的示例分析

這篇文章給大家介紹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）分表后，數據都是存放在分表里，總表只是一個外殼，存取數據發生在一個一個的分表里面。看下面的例子：

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'\G;
*************************** 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'\G;
*************************** 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'\G;
*************************** 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'\G;
*************************** 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分表分區的示例分析就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。