MySQL数据库分区的概念与2大好处(1)(2)

分区带来的两点好处：

性能的提升（Increased performance） - 在扫描操作中，如果MySQL的优化器知道哪个分区中才包含特定查询中需要的数据，它就能直接去扫描那些分区的数据，而不用浪费很多时间扫描不需要的地方了。需要举个例子？好啊，百万行的表划分为10个分区，每个分区就包含十万行数据，那么查询分区需要的时间仅仅是全表扫描的十分之一了，很明显的对比。

同时对十万行的表建立索引的速度也会比百万行的快得多得多。如果你能把这些分区建立在不同的磁盘上，这时候的I/O读写速度就“不堪设想”（没用错词，真的太快了，理论上100倍的速度提升啊，这是多么快的响应速度啊，所以有点不堪设想了）了。

对数据管理的简化（Simplified data management） - 分区技术可以让DBA对数据的管理能力提升。通过优良的MySQL数据库分区，DBA可以简化特定数据操作的执行方式。例如：DBA在对某些分区的内容进行删除的同时能保证余下的分区的数据完整性(这是跟对表的数据删除这种大动作做比较的)。 此外分区是由MySQL系统直接管理的，DBA不需要手工的去划分和维护。例如：这个例如没意思，不讲了，如果你是DBA，只要你划分了分区，以后你就不用管了就是了。

站在性能设计的观点上，俺们对以上的内容也是相当感兴趣滴。通过使用分区和对不同的SQL操作的匹配设计，数据库的性能一定能获得巨大提升。下面咱们一起用用这个MySQL 5.1的新功能看看。

下面所有的测试都在Dell Optiplex box with a Pentium 4 3.00GHz processor, 1GB of RAM机器上（炫耀啊……），Fedora Core 4和MySQL 5.1.6 alpha上运行通过。

如何进行实际分区 看看分区的实际效果吧。我们建立几个同样的MyISAM引擎的表，包含日期敏感的数据，但只对其中一个分区。分区的表（表名为part_tab）我们采用Range范围分区模式，通过年份进行MySQL数据库分区：

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

注意到了这里的最后一行吗？这里把不属于前面年度划分的年份范围都包含了，这样才能保证数据不会出错，大家以后要记住啊，不然数据库无缘无故出错你就爽了。

那下面我们建立没有MySQL数据库分区的表（表名为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)  

下面咱写一个存储过程它能向咱刚才建立的已分区的表中平均的向每个分区插入共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 vv = 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数据库分区的，跟着有执行过程解析（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   
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   
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程序也告诉我们在对已分区的表的查询过程中仅对第一个分区进行了扫描，其他都跳过了。

哔厉吧拉，说阿说……反正就是这个分区功能对DBA很有用拉，特别对VLDB和需要快速反应的系统。

对Vertical Partitioning的一些看法 虽然MySQL 5.1自动实现了水平分区，但在设计数据库的时候不要轻视垂直MySQL数据库分区。虽然要手工去实现垂直分区，但在特定场合下你会收益不少的。例如在前面建立的表中，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 命令支持返回MySQL数据库分区表以及元数据的索引。

对其操作的命令和实现的维护功能有（比对全表的操作还多）：

ADD PARTITION   
DROP PARTITION   
COALESCE PARTITION   
REORGANIZE PARTITION   
ANALYZE PARTITION   
CHECK PARTITION   
OPTIMIZE PARTITION   
REBUILD PARTITION   
REPAIR PARTITION  

以上的相关内容就是对MySQL数据库分区的介绍，望你能有所收获。