共計 6301 個字符，預計需要花費 16 分鐘才能閱讀完成。

本篇文章為大家展示了 mysql 中怎么實現水平分表和垂直分表，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

什么是數據庫分區？

數據庫分區是一種物理數據庫設計技術，DBA 和數據庫建模人員對其相當熟悉。雖然分區技術可以實現很多效果，但其主要目的是為了在特定的 SQL 操作中減少數據讀寫的總量以縮減響應時間。

分區主要有兩種形式：// 這里一定要注意行和列的概念（row 是行，column 是列）

水平分區（Horizontal Partitioning）這種形式分區是對表的行進行分區，通過這樣的方式不同分組里面的物理列分割的數據集得以組合，從而進行個體分割（單分區）或集體分割（1 個或多個分區）。所有在表中定義的列在每個數據集中都能找到，所以表的特性依然得以保持。

舉個簡單例子：一個包含十年發票記錄的表可以被分區為十個不同的分區，每個分區包含的是其中一年的記錄。（注：這里具體使用的分區方式我們后面再說，可以先說一點，一定要通過某個屬性列來分割，譬如這里使用的列就是年份）

垂直分區（Vertical Partitioning）這種分區方式一般來說是通過對表的垂直劃分來減少目標表的寬度，使某些特定的列被劃分到特定的分區，每個分區都包含了其中的列所對應的行。

舉個簡單例子：一個包含了大 text 和 BLOB 列的表，這些 text 和 BLOB 列又不經常被訪問，這時候就要把這些不經常使用的 text 和 BLOB 了劃分到另一個分區，在保證它們數據相關性的同時還能提高訪問速度。

在數據庫供應商開始在他們的數據庫引擎中建立分區（主要是水平分區）時，DBA 和建模者必須設計好表的物理分區結構，不要保存冗余的數據（不同表中同時都包含父表中的數據）或相互聯結成一個邏輯父對象（通常是視圖）。這種做法會使水平分區的大部分功能失效，有時候也會對垂直分區產生影響。

在 MySQL 5.1 中進行分區

  MySQL5.1 中最激動人心的新特性應該就是對水平分區的支持了。這對 MySQL 的使用者來說確實是個好消息，而且她已經支持分區大部分模式：

  Range（范圍）C 這種模式允許 DBA 將數據劃分不同范圍。例如 DBA 可以將一個表通過年份劃分成三個分區，80 年代（1980 s）的數據，90 年代（1990 s）的數據以及任何在 2000 年（包括 2000 年）后的數據。

  Hash（哈希）C 這中模式允許 DBA 通過對表的一個或多個列的 Hash Key 進行計算，最后通過這個 Hash 碼不同數值對應的數據區域進行分區，。例如 DBA 可以建立一個對表主鍵進行分區的表。

  Key（鍵值）C 上面 Hash 模式的一種延伸，這里的 Hash Key 是 MySQL 系統產生的。

  List（預定義列表）C 這種模式允許系統通過 DBA 定義的列表的值所對應的行數據進行分割。例如：DBA 建立了一個橫跨三個分區的表，分別根據 2004 年 2005 年和 2006 年值所對應的數據。

  Composite（復合模式）- 很神秘吧，哈哈，其實是以上模式的組合使用而已，就不解釋了。舉例：在初始化已經進行了 Range 范圍分區的表上，我們可以對其中一個分區再進行 hash 哈希分區。

  分區帶來的好處太多太多了，有多少？俺也不知道，自己猜去吧，要是覺得沒有多少就別用，反正俺也不求你用。不過在這里俺強調兩點好處：

性能的提升（Increased performance）- 在掃描操作中，如果 MySQL 的優化器知道哪個分區中才包含特定查詢中需要的數據，它就能直接去掃描那些分區的數據，而不用浪費很多時間掃描不需要的地方了。需要舉個例子？好啊，百萬行的表劃分為 10 個分區，每個分區就包含十萬行數據，那么查詢分區需要的時間僅僅是全表掃描的十分之一了，很明顯的對比。同時對十萬行的表建立索引的速度也會比百萬行的快得多得多。如果你能把這些分區建立在不同的磁盤上，這時候的 I / O 讀寫速度就“不堪設想”（沒用錯詞，真的太快了，理論上 100 倍的速度提升啊，這是多么快的響應速度啊，所以有點不堪設想了）了。

對數據管理的簡化（Simplified data management）- 分區技術可以讓 DBA 對數據的管理能力提升。通過優良的分區，DBA 可以簡化特定數據操作的執行方式。例如：DBA 在對某些分區的內容進行刪除的同時能保證余下的分區的數據完整性 (這是跟對表的數據刪除這種大動作做比較的)。

此外分區是由 MySQL 系統直接管理的，DBA 不需要手工的去劃分和維護。例如：這個例如沒意思，不講了，如果你是 DBA，只要你劃分了分區，以后你就不用管了就是了。

站在性能設計的觀點上，俺們對以上的內容也是相當感興趣滴。通過使用分區和對不同的 SQL 操作的匹配設計，數據庫的性能一定能獲得巨大提升。下面咱們一起用用這個 MySQL 5.1 的新功能看看。

下面所有的測試都在 Dell Optiplex box with a Pentium 4 3.00GHz processor, 1GB of RAM 機器上（炫耀啊 hellip; hellip;），Fedora Core 4 和 MySQL 5.1.6 alpha 上運行通過。

如何進行實際分區

看看分區的實際效果吧。我們建立幾個同樣的 MyISAM 引擎的表，包含日期敏感的數據，但只對其中一個分區。分區的表（表名為 part_tab）我們采用 Range 范圍分區模式，通過年份進行分區：

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.00 sec)

注意到了這里的最后一行嗎？這里把不屬于前面年度劃分的年份范圍都包含了，這樣才能保證數據不會出錯，大家以后要記住啊，不然數據庫無緣無故出錯你就爽了。那下面我們建立沒有分區的表（表名為 no_part_tab）：

mysql create table no_part_tab

  – (c1 int(11) default NULL,

  – c2 varchar(30) default NULL,

  – c3 date default NULL) engine=myisam;

Query OK, 0 rows affected (0.02 sec)

下面咱寫一個存儲過程（感謝 Peter Gulutzan 給的代碼，如果大家需要 Peter Gulutzan 的存儲過程教程的中文翻譯也可以跟我要，chenpengyi◎gmail.com），它能向咱剛才建立的已分區的表中平均的向每個分區插入共 8 百萬條不同的數據。填滿后，咱就給沒分區的克隆表中插入相同的數據：

mysql delimiter //

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.00 sec)

mysql delimiter ;

mysql call load_part_tab();

Query OK, 1 row affected (8 min 17.75 sec)

mysql insert into no_part_tab select * from part_tab;

Query OK, 8000000 rows affected (51.59 sec)

Records: 8000000 Duplicates: 0 Warnings: 0

表都準備好了。咱開始對這兩表中的數據進行簡單的范圍查詢吧。先分區了的，后沒分區的，跟著有執行過程解析（MySQL Explain 命令解析器），可以看到 MySQL 做了什么：

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 (38.30 sec)

mysql select count(*) from part_tab where

  – c3 date 1995-01-01 and c3 date 1995-12-31

+———-+

| count(*) |

+———-+

|  795181 |

+———-+

1 row in set (3.88 sec)

mysql explain 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)

mysql explain partitions 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

  partitions: p1

  type: ALL

possible_keys: NULL

  key: NULL

  key_len: NULL

  ref: NULL

  rows: 798458

  Extra: Using where

1 row in set (0.00 sec)

從上面結果可以容易看出，設計恰當表分區能比非分區的減少 90%的響應時間。而命令解析 Explain 程序也告訴我們在對已分區的表的查詢過程中僅對第一個分區進行了掃描，其他都跳過了。

嗶厲吧拉，說阿說 hellip; hellip; 反正就是這個分區功能對 DBA 很有用拉，特別對 VLDB 和需要快速反應的系統。

對 Vertical Partitioning 的一些看法

雖然 MySQL 5.1 自動實現了水平分區，但在設計數據庫的時候不要輕視垂直分區。雖然要手工去實現垂直分區，但在特定場合下你會收益不少的。例如在前面建立的表中，VARCHAR 字段是你平常很少引用的，那么對它進行垂直分區會不會提升速度呢？咱們看看測試結果：

mysql desc part_tab;

+——-+————-+——+—–+———+——-+

| Field | Type  | Null | Key | Default | Extra |

+——-+————-+——+—–+———+——-+

| c1  | int(11)  | YES |  | NULL  |  |

| c2  | varchar(30) | YES |  | NULL  |  |

| c3  | date  | YES |  | NULL  |  |

+——-+————-+——+—–+———+——-+

3 rows in set (0.03 sec)

mysql alter table part_tab drop column c2;

Query OK, 8000000 rows affected (42.20 sec)

Records: 8000000 Duplicates: 0 Warnings: 0

mysql desc part_tab;

+——-+———+——+—–+———+——-+

| Field | Type  | Null | Key | Default | Extra |

+——-+———+——+—–+———+——-+

| c1  | int(11) | YES |  | NULL  |  |

| c3  | date  | YES |  | NULL  |  |

+——-+———+——+—–+———+——-+

2 rows in set (0.00 sec)

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.34 sec)

在設計上去掉了 VARCHAR 字段后，不止是你，俺也發現查詢響應速度上獲得了另一個 90%的時間節省。所以大家在設計表的時候，一定要考慮，表中的字段是否真正關聯，又是否在你的查詢中有用？

補充說明

這么簡單的文章肯定不能說全 MySQL 5.1 分區機制的所有好處和要點（雖然對自己寫文章水平很有信心），下面就說幾個感興趣的：

支持所有存儲引擎 (MyISAM, Archive, InnoDB, 等等)

對分區的表支持索引，包括本地索引 local indexes，對其進行的是一對一的視圖鏡像，假設一個表有十個分區，那么其本地索引也包含十個分區。

關于分區的元數據 Metadata 的表可以在 INFORMATION_SCHEMA 數據庫中找到，表名為 PARTITIONS。

All SHOW 命令支持返回分區表以及元數據的索引。

對其操作的命令和實現的維護功能有（比對全表的操作還多）：

ADD PARTITION

DROP PARTITION

COALESCE PARTITION

REORGANIZE PARTITION

ANALYZE PARTITION

CHECK PARTITION

OPTIMIZE PARTITION

REBUILD PARTITION

REPAIR PARTITION

上述內容就是 mysql 中怎么實現水平分表和垂直分表，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注丸趣 TV 行業資訊頻道。