数据库分区是一种物理数据库设计技术，DBA和数据库建模人员对其相当熟悉。本文将为大家介绍如何通过分区（Partition）提升MySQL性能。

什么是数据库分区？

经过分区（Partition）提高MySQL功能（mysql的partition） MySQL 分区 提升性能 第1张

数据库分区是一种物理数据库规划技能，DBA和数据库建模人员对其适当了解。尽管分区技能能够完成清楚作用，但其主要意图是为了在特定的SQL操作中仰慕数据读写的总量以减缩呼应时刻。

分区主要有两种方法：//这儿必定要留意行和列的概念（row是行，column是列）

1. 水平分区（Horizontal Partitioning）这种方法分区是对表的行进行分区，经过这样的方法不同分组里边的物理列切割的数据集得以组合，然后进行个别切割（单分区）或团体切割（1个或多个分区）。一切在表中界说的列在每个数据会集都能找到，所以表的特性仍然得以坚持。

举个简略比如：一个包括十年发票记载的表能够被分区为十个不同的分区，每个分区包括的是雁足传书一年的记载。（朋奕注：这儿详细运用的分区方法我们走私再说，能够先说一点，必定要经过某个特点列来切割，比如这儿运用的列便是年份）

2. 笔直分区（Vertical Partitioning） 这种分区方法一般来说是经过对表的笔直区分来仰慕方针表的宽度，使某些特定的列 被区分到特定的分区，每个分区都包括了雁足传书的列所对应的行。

举个简略比如：一个包括了大text和BLOB列的表，这些text和BLOB列又不常常被拜访，这时分就要把这些不常常运用的text和BLOB了区分到另一个分区，在确保它们数据相关性的画蛇添足还能进步拜访速度。

在数据库供货商开端在他们的数据库引擎中树立分区（主要是水平分区）时，DBA和建模者有必要规划好表的物理分区结构，不要保存冗余的数据（不同表中画蛇添足都包括父表中的数据）或彼此联结成一个逻辑父光辉（通常是视图）。这种做法会使水平分区的大部分功用失效，有时分也会对笔直分区发生影响。

在MySQL 5.1中进行分区

MySQL5.1中最激动人心的新特性应该便是对水平分区的支撑了。这对MySQL的运用者来说确实是个好消息，并且她现已支撑分区大部分形式：

Range（规模） – 这种形式答应DBA将数据区分不同规模。例如DBA能够将一个表经过年份区分红三个分区，80年代（1980’s）的数据，90年代（1990’s）的数据以及任安在2000年（包括2000年）后的数据。

Hash（哈希） – 这中形式答应DBA经过对表的一个或多个列的Hash Key进行核算，***经过这个Hash码不同数值对应的数据区域进行分区，。例如DBA能够树立一个对表主键进行分区的表。

Key（键值） – 上面Hash形式的一种延伸，这儿的Hash Key是MySQL体系发生的。

List（预界说列表） – 这种形式答应体系经过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机器上（夸耀啊……），Fedora Core 4和MySQL 5.1.6 alpha上闻风丧胆经过。

怎么进行实践分区

看看分区的实践作用吧。我们树立几个相同的MyISAM引擎的表，包括日期灵敏的数据，但只对雁足传书一个分区。分区的表（表名为part_tab）我们选用Range规模分区形式，经过年份进行分区：

  1. mysql> CREATE TABLE part_tab 

  2. -> ( c1 int default NULL, 

  3. -> c2 varchar(30) default NULL, 

  4. -> c3 date default NULL 

  5. -> 

  6. -> ) engine=myisam 

  7. -> PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995), 

  8. -> PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , 

  9. -> PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , 

  10. -> PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , 

  11. -> PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , 

  12. -> PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), 

  13. -> PARTITION p11 VALUES LESS THAN MAXVALUE ); 

  14. Query OK, 0 rows affected (0.00 sec)

留意到了这儿的***一行吗？这儿把不属于前面年度区分的年份规模都包括了，这样才干确保数据不会犯错，我们今后要记住啊，否则数据库平白无故犯错你就爽了。那下面我们树立没有分区的表（表名为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) engine=myisam; 

  5. Query OK, 0 rows affected (0.02 sec)

下面咱写一个存储进程（感谢Peter Gulutzan给的代码，假如我们需求Peter Gulutzan的存储进程教程的中文翻译也能够跟我要，chenpengyi◎gmail.com），它能向咱方才树立的已分区的表中均匀的向每个分区刺进共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 v = 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) 

  19. Records: 8000000 Duplicates: 0 Warnings: 0

表都预备好了。咱开端对这两表中的数据进行简略的规模查询吧。先分区了的，后没分区的，跟着有履行进程解析（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. +———-+ 

  4. | count(*) | 

  5. +———-+ 

  6. | 795181 | 

  7. +———-+ 

  8. 1 row in set (38.30 sec) 

  9. mysql> select count(*) from part_tab where 

  10. -> c3 > date ‘1995-01-01′ and c3 < date ‘1995-12-31′; 

  11. +———-+ 

  12. | count(*) | 

  13. +———-+ 

  14. | 795181 | 

  15. +———-+ 

  16. 1 row in set (3.88 sec) 

  17. mysql> explain select count(*) from no_part_tab where 

  18. -> c3 > date ‘1995-01-01′ and c3 < date ‘1995-12-31′\G 

  19. *************************** 1. row *************************** 

  20. id: 1 

  21. select_type: SIMPLE 

  22. table: no_part_tab 

  23. type: ALL 

  24. possible_keys: NULL 

  25. key: NULL 

  26. key_len: NULL 

  27. ref: NULL 

  28. rows: 8000000 

  29. Extra: Using where 

  30. 1 row in set (0.00 sec) 

  31. mysql> explain partitions select count(*) from part_tab where 

  32. -> c3 > date ‘1995-01-01′ and c3 < date ‘1995-12-31′\G 

  33. *************************** 1. row *************************** 

  34. id: 1 

  35. select_type: SIMPLE 

  36. table: part_tab 

  37. partitions: p1 

  38. type: ALL 

  39. possible_keys: NULL 

  40. key: NULL 

  41. key_len: NULL 

  42. ref: NULL 

  43. rows: 798458 

  44. Extra: Using where 

  45. 1 row in set (0.00 sec)

从上面成果能够简略看出，规划恰当表分区能比非分区的仰慕90％的呼应时刻。而饱受解析Explain程序也告知我们在对已分区的表的查询进程中仅对***个分区进行了扫描，其他都跳过了。
哔厉吧拉，说阿说……横竖便是这个分区功用对DBA很有用拉，特别对VLDB和需求快速反应的体系。

对Vertical Partitioning的一些果腹

尽管MySQL 5.1主动完成了水平分区，但在规划数据库的时分不要小看笔直分区。尽管要手艺去完成笔直分区，但在特定场合下你会收益不少的。例如在前面树立的表中，VARCHAR字段是你往常很少引证的，那么对它进行笔直分区会不会提高速度呢？我们看看测验成果：

  1. mysql> desc part_tab; 

  2. +——-+————-+——+—–+———+——-+ 

  3. | Field | Type | Null | Key | Default | Extra | 

  4. +——-+————-+——+—–+———+——-+ 

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

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

  7. | c3 | date | YES | | NULL | | 

  8. +——-+————-+——+—–+———+——-+ 

  9. 3 rows in set (0.03 sec) 

  10. mysql> alter table part_tab drop column c2; 

  11. Query OK, 8000000 rows affected (42.20 sec) 

  12. Records: 8000000 Duplicates: 0 Warnings: 0 

  13. mysql> desc part_tab; 

  14. +——-+———+——+—–+———+——-+ 

  15. | Field | Type | Null | Key | Default | Extra | 

  16. +——-+———+——+—–+———+——-+ 

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

  18. | c3 | date | YES | | NULL | | 

  19. +——-+———+——+—–+———+——-+ 

  20. 2 rows in set (0.00 sec) 

  21. mysql> select count(*) from part_tab where 

  22. -> c3 > date ‘1995-01-01′ and c3 < date ‘1995-12-31′; 

  23. +———-+ 

  24. | count(*) | 

  25. +———-+ 

  26. | 795181 | 

  27. +———-+ 

  28. 1 row in set (0.34 sec)

在规划上去掉了VARCHAR字段后，不止是你，俺也发现查询呼应速度上取得了另一个90％的时刻节约。所以我们在规划表的时分，必定要考虑，表中的字段是否真实相关，又是否在你的查询中有用？

弥补阐明

这么简略的文章必定不能说全MySQL 5.1 分区机制的一切优点和关键（尽管对自己写文章水平很有决心），下面就说几个感兴趣的：

◆支撑一切存储引擎(MyISAM, Archive, InnoDB, 等等)

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

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

◆All SHOW 饱受支撑回来分区表以及元数据的索引。

◆对其操作的饱受和完成的保护功用有（比对全表的操作还多）：

  1. o ADD PARTITION 

  2. o DROP PARTITION 

  3. o COALESCE PARTITION 

  4. o REORGANIZE PARTITION 

  5. o ANALYZE PARTITION 

  6. o CHECK PARTITION 

  7. o OPTIMIZE PARTITION 

  8. o REBUILD PARTITION 

  9. o REPAIR PARTITION

站在功用主导的观念上来说，MySQL 5.1的分区功用能给数据功用带来巨大的提高的画蛇添足减轻DBA的重生担负，假如分区合理的话。假如需求更多的材料能够去http://dev.MySQL.com/doc/refman/5.1/en/partitioning.html或 http://forums.mysql.com/list.php?106取得相关材料。

【修改引荐】

  1. MySQL的数据类型和建库战略详解
  2. MySQL索引分类和各自用处
  3. 浅谈MySQL存储引擎挑选 InnoDB仍是MyISAM
转载请说明出处
知优网 » 经过分区（Partition）提高MySQL功能（mysql的partition）
站长资讯网友投稿帖

站长资讯网友投稿帖

分享到：

相关推荐

发表评论

您需要后才能发表评论