mysql —— 分表分割槽（1）

weixin_34377065發表於2013-09-26
MySql
面對當今大資料儲存，設想當mysql中一個表的總記錄超過1000W，會出現效能的大幅度下降嗎？
答案是肯定的，一個表的總記錄超過1000W，在作業系統層面檢索也是效率非常低的

解決方案：
目前針對海量資料的優化有兩種方法：
1、大表拆小表的方式（主要有分表和分割槽兩者技術）
（1）分表技術
垂直分割
094806715.png
優勢：降低高併發情況下，對於表的鎖定。
不足：對於單表來說，隨著資料庫的記錄增多，讀寫壓力將進一步增大。


水平分割
094821338.png

如果單表的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 伺服器提供其自身的雜湊函式。必須有一列或多列包含整數值。 ----很少用到


如何檢視資料庫是否支援分割槽技術？
094848116.png

建立分割槽：
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)

結論：可以看到，在刪除方面，有分割槽的還是比沒分割槽的快一點。從而體現了其便於資料管理的特點
方便資料管理這點，我通過下面的例子來說明：比如資料庫的表t1記錄的是今年一整年（12個月）公司的營業額，在未分割槽的情況下，也就是說資料檔案都存放在同一個檔案裡面，那麼假如現在要刪除第一個季度的記錄，那麼需要全表掃描才能得出結果。但如果t1這個表事先做了分割槽，那麼我只需要分別刪除1,2,3這三個檔案即可。所以從一定程度上，還是方便了管理。


相關文章