mysql分表分區(qū)的示例分析

這篇文章給大家介紹mysql分表分區(qū)的示例分析，內(nèi)容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

面對當今大數(shù)據(jù)存儲，設想當mysql中一個表的總記錄超過1000W，會出現(xiàn)性能的大幅度下降嗎？

答案是肯定的，一個表的總記錄超過1000W，在操作系統(tǒng)層面檢索也是效率非常低的

解決方案：

目前針對海量數(shù)據(jù)的優(yōu)化有兩種方法：

1、大表拆小表的方式（主要有分表和分區(qū)兩者技術）

（1）分表技術

垂直分割

優(yōu)勢：降低高并發(fā)情況下，對于表的鎖定。

不足：對于單表來說，隨著數(shù)據(jù)庫的記錄增多，讀寫壓力將進一步增大。

水平分割

如果單表的IO壓力大，可以考慮用水平分割，其原理就是通過hash算法，將一張表分為N多頁，并通過一個新的表（總表），記錄著每個頁的的位置。假如一 個門戶網(wǎng)站，它的數(shù)據(jù)庫表已經(jīng)達到了1000萬條記錄，那么此時如果通過select去查詢，必定會效率低下（不做索引的前提下）。為了降低單表的讀寫 IO壓力，通過水平分割，將這個表分成10個頁，同時生成一個總表，記錄各個頁的信息，那么假如我查詢一條id=100的記錄，它不再需要全表掃描，而是 通過總表找到該記錄在哪個對應的頁上，然后再去相應的頁做檢索，這樣就降低了IO壓力。

水平分表技術就是將一個表拆成多個表，比較常見的方式就是將表中的記錄按照某種HASH算法進行拆分，同時，這種分區(qū)方法也必須對前端的應用程序中的 SQL進行修改方能使用，而且對于一個SQL語句，可能會修改兩個表，那么你必須要修改兩個SQL語句來完成你這個邏輯的事務，會使得邏輯判斷越來越復 雜，這樣會增加程序的維護代價，所以我們要避免這樣的情況出現(xiàn)。

2、SQL語句的優(yōu)化(索引)

SQL語句優(yōu)化：可以通過增加索引等來調整，但同時數(shù)據(jù)量的增大會導致索引的維護代價增大。

分區(qū)優(yōu)點：

1、減少IO

2、提高讀寫

3、方便數(shù)據(jù)管理

分區(qū)與分表的區(qū)別：

分區(qū)是邏輯層面進行了水平分割，對于應用程序來說，它仍是一張表。

分區(qū)就是把一張表的數(shù)據(jù)分成N多個區(qū)塊，這些區(qū)塊可以在同一個磁盤上，也可以在不同的磁盤上

1. 實現(xiàn)方式上

（1）mysql的分表是真正的分表，一張表分成很多表后，每一個小表都是完整的一張表，都對應三個文件，一個.MYD數(shù)據(jù)文件，.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。他們二個都是獨立 的表，取數(shù)據(jù)的時候，我們可以通過總表來取。這里總表是沒有.MYD,.MYI這二個文件的，也就是說，總表他不是一張表，沒有數(shù)據(jù)，數(shù)據(jù)都放在分表里 面。我們來看看.MRG到底是什么東西

[root@BlackGhost test]# cat alluser.MRG |more

user1

user2

#INSERT_METHOD=LAST

從上面我們可以看出，alluser.MRG里面就存了一些分表的關系，以及插入數(shù)據(jù)的方式?？梢园芽偙砝斫獬梢粋€外殼，或者是連接池。

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

[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個區(qū)。我們都知道一張表對應三個文件.MYD,.MYI,.frm。分區(qū)根據(jù)一定的規(guī)則把數(shù)據(jù)文件和索引文件進行 了分割，還多出了一個.par文件，打開.par文件后你可以看出他記錄了，這張表的分區(qū)信息，跟分表中的.MRG有點像。分區(qū)后，還是一張，而不是多張 表。

2. 數(shù)據(jù)處理上

（1）分表后，數(shù)據(jù)都是存放在分表里，總表只是一個外殼，存取數(shù)據(jù)發(fā)生在一個一個的分表里面?？聪旅娴睦樱?/p>

select * from user1 user2 where id='12'表面上看，是對表alluser進行操作的，其實不是的。是對alluser里面的分表進行了操作。

（2）分區(qū)，不存在分表的概念，分區(qū)只不過把存放數(shù)據(jù)的文件分成了許多小塊，分區(qū)后的表，還是一張表。數(shù)據(jù)處理還是由自己來完成。

select * from alluser where id='12'

3. 提高性能上

（1） 分表后，單表的并發(fā)能力提高了，磁盤I/O性能也提高了。因為查詢一次所花的時間變短了，如果出現(xiàn)高并發(fā)的話，總表可以根據(jù)不同的查詢，將并發(fā)壓力分到不 同的小表里面。本來一個非常大的.MYD文件現(xiàn)在也分攤到各個小表的.MYD中去了，因此對于磁盤IO壓力也降低了。

（2）mysql提出了分區(qū)的概念，我覺得就想突破磁盤I/O瓶頸，想提高磁盤的讀寫能力，來增加mysql性能。

在這一點上，分區(qū)和分表的側重點不同，分表重點是存取數(shù)據(jù)時，如何提高mysql并發(fā)能力上；而分區(qū)呢，則是如何突破磁盤的讀寫能力，從而達到提高mysql性能的目的。

4. 實現(xiàn)的難易度上

（1）分表的方法有很多，用merge來分表，是最簡單的一種方式。這種方式根分區(qū)難易度差不多，并且對程序代碼來說可以做到透明的。如果是用其他分表方式就比分區(qū)麻煩了。

（2）分區(qū)實現(xiàn)是比較簡單的，建立分區(qū)表，跟建平常的表沒什么區(qū)別，并且對開代碼端來說是透明的。

分區(qū)類型

hash、range、list、key

• RANGE分區(qū)：基于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

• LIST分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進行選擇。

• HASH分區(qū)：基于用戶定義的表達式的返回值來進行選擇的分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負整數(shù)值的任何表達式。

hash用在數(shù)據(jù)相對比較隨機的情況下。它是根據(jù)表中的內(nèi)容進行hash運算后隨機平均分配，假設這個列是性別，則不適合用hash分區(qū)，因為內(nèi)容要么是男，要么是女，沒有隨機性。

• KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL 服務器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。 ----很少用到

如何查看數(shù)據(jù)庫是否支持分區(qū)技術？

創(chuàng)建分區(qū)：

mysql> create table t1(id int)partition by hash(id)partitions 3;

Query OK, 0 rows affected (0.03 sec)

【實驗】

分別創(chuàng)建一個分區(qū)的表和非分區(qū)的表，進行性能測試

創(chuàng)建分區(qū)表

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)

創(chuàng)建非分區(qū)表
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 ; // 執(zhí)行完這個存儲過程后，需要將結束符修改回去

上面的存儲過程實際上是為了創(chuàng)建大量的數(shù)據(jù)（800萬條）

mysql> call load_part_tab(); // 調用load_part_tab這個存儲過程
Query OK, 1 row affected (9 min 18.95 sec)

快速將part_tab里面的數(shù)據(jù)插入到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

測試一：

實驗之前確保兩個表里面的數(shù)據(jù)是一致的！保證實驗的可比性

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

結論：可以看到，做了分區(qū)之后，只需要掃描79萬條語句，而不做分區(qū)的，則需要進行全表掃描，故可以看出，做了分區(qū)技術后，可以提高讀寫效率。

測試2：
創(chuàng)建索引，查看語句執(zhí)行情況

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

結論：為未分區(qū)的表創(chuàng)建了索引之后，再次執(zhí)行相同的語句，可以看到該SQL語句是根據(jù)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

測試未創(chuàng)建索引字段

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)
可以看到，在刪除方面，有分區(qū)的還是比沒分區(qū)的快一點。從而體現(xiàn)了其便于數(shù)據(jù)管理的特點。

關于mysql分表分區(qū)的示例分析就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。