共计 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 行业资讯频道。
