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

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

那下面我们建立没有MySQL数据库分区的表（表名为no_part_tab）：

1. mysql> create table no_part_tab   
2. -> (c1 int(11) default NULL,   
3. -> c2 varchar(30) default NULL,   
4. -> c3 date default NULL)   
5. engine=myisam;   
6. Query OK, 0 rows affected (0.02 sec)  

下面咱写一个存储过程它能向咱刚才建立的已分区的表中平均的向每个分区插入共8百万条不同的数据。填满后，咱就给没分区的克隆表中插入相同的数据：

1. mysql> delimiter //   
2. mysql> CREATE PROCEDURE load_part_tab()   
3. -> begin   
4. -> declare v int default 0;   
5. -> while v < 8000000   
6. -> do   
7. -> insert into part_tab   
8. -> values (v,‘testing partitions‘,adddate(‘1995-01-01‘,(rand(v)*36520) mod 3652));   
9. -> set vv = v + 1;   
10. -> end while;   
11. -> end   
12. -> //   
13. Query OK, 0 rows affected (0.00 sec)   
14. mysql> delimiter ;   
15. mysql> call load_part_tab();   
16. Query OK, 1 row affected (8 min 17.75 sec)   
17. mysql> insert into no_part_tab select * from part_tab;   
18. Query OK, 8000000 rows affected (51.59 sec) Records: 8000000 Duplicates: 0 Warnings: 0   

表都准备好了。咱开始对这两表中的数据进行简单的范围查询吧。先分区了的，后没MySQL数据库分区的，跟着有执行过程解析（MySQL Explain命令解析器），可以看到MySQL做了什么：

1. mysql> select count(*) from no_part_tab where   
2. -> c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘;   
3. +----------+ | count(*) | +----------+ | 795181 | +----------+ 1 row in set (38.30 sec)   
4. mysql> select count(*) from part_tab where   
5. -> c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘;  
6. +----------+ | count(*) | +----------+ | 795181 | +----------+ 1 row in set (3.88 sec)   
7. mysql> explain select count(*) from no_part_tab where   
8. -> c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘\G   
9. id: 1 select_type:   
10. SIMPLE table: no_part_tab   
11. type: ALL   
12. possible_keys: NULL   
13. key: NULL   
14. key_len: NULL   
15. ref: NULL   
16. rows: 8000000   
17. Extra: Using where 1 row in set (0.00 sec)   
18. mysql> explain partitions select count(*) from part_tab where   
19. -> c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘\G   
20. id: 1   
21. select_type: SIMPLE   
22. table: part_tab   
23. partitions: p1   
24. type: ALL   
25. possible_keys: NULL   
26. key: NULL   
27. key_len: NULL   
28. ref: NULL   
29. rows: 798458   
30. Extra: Using where 1 row in set (0.00 sec)   

从上面结果可以容易看出，设计恰当表分区能比非分区的减少90％的响应时间。而命令解析Explain程序也告诉我们在对已分区的表的查询过程中仅对第一个分区进行了扫描，其他都跳过了。

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

对Vertical Partitioning的一些看法 虽然MySQL 5.1自动实现了水平分区，但在设计数据库的时候不要轻视垂直MySQL数据库分区。虽然要手工去实现垂直分区，但在特定场合下你会收益不少的。例如在前 面建立的表中，VARCHAR字段是你平常很少引用的，那么对它进行垂直分区会不会提升速度呢？咱们看看测试结果：

1. mysql> desc part_tab; +-------+-------------+------+-----+---------+-------+   
2. | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+   
3. | c1 | int(11) | YES | | NULL | |  
4. | c2 | varchar(30) | YES | | NULL | |  
5. | c3 | date | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.03 sec)   
6. mysql> alter table part_tab drop column c2;   
7. Query OK, 8000000 rows affected (42.20 sec) Records: 8000000 Duplicates: 0 Warnings: 0   
8. mysql> desc part_tab; +-------+---------+------+-----+---------+-------+   
9. | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+   
10. | c1 | int(11) | YES | | NULL | |   
11. | c3 | date | YES | | NULL | | +-------+---------+------+-----+---------+-------+  
12. 2 rows in set (0.00 sec)   
13. mysql> select count(*) from part_tab where   
14. -> c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘; +----------+   
15. | count(*) | +----------+   
16. | 795181 | +----------+   
17. 1 row in set (0.34 sec)   

在设计上去掉了VARCHAR字段后，不止是你，俺也发现查询响应速度上获得了另一个90％的时间节省。所以大家在设计表的时候，一定要考虑，表中的字段是否真正关联，又是否在你的查询中有用？

补充说明

这么简单的文章肯定不能说全MySQL 5.1 分区机制的所有好处和要点（虽然对自己写文章水平很有信心），下面就说几个感兴趣的：

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

对分区的表支持索引，包括本地索引local indexes，对其进行的是一对一的视图镜像，假设一个表有十个分区，那么其本地索引也包含十个分区。

关于分区的元数据Metadata的表可以在INFORMATION_SCHEMA数据库中找到，表名为PARTITIONS。

All SHOW 命令支持返回MySQL数据库分区表以及元数据的索引。

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

1. ADD PARTITION   
2. DROP PARTITION   
3. COALESCE PARTITION   
4. REORGANIZE PARTITION   
5. ANALYZE PARTITION   
6. CHECK PARTITION   
7. OPTIMIZE PARTITION   
8. REBUILD PARTITION   
9. REPAIR PARTITION  

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

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

标签：