保藏自：?tid=340&page=e
通过度区（Partition）晋升MySQL机能
——MySQL5.1新特性翻译系列
几年前，俺写过一篇题为“The Foundation of Excellent Performance”的文章(此刻仍然可以在看到)，俺对SQL语句是影响数据库驱动系统机能的第一要素的概念有点质疑。其实在当时我在文章中就坚信数据库的物理设计在对高级数据库的机能影响上远比其他因素重要。同时俺还给各人看了Oracle的研究，他们表明白为什么拙劣的物理设计是数据库停机（无论是有打算的照旧没打算的）背后的主要原因。这么多年都过来啦（幸好没几多人朝俺扔砖头），俺的概念是改变了一些，但在这点上俺照旧僵持DBA假如想要高机能的数据库就必需在数据库的物理设计上多思考的概念，这样才气淘汰响应时间使终端用户满足而不是引来骂声一片。（陈朋奕语：不要那么严肃，嘿嘿）
俺本日这么感动又想写文章的原因是MySQL5.1的宣布带来了设计超强动力数据库的强有力的兵器，任何MySQL的DBA都应该尽快进修并利用它。俺以为假如能很好滴利用这个5.1版带来的新特性，DBA可以使本身打点的VLDB（不知道什么是VLDB？汇报你，是好大好大的数据库的意思，Very Large DB）或数据客栈古迹般的得到庞大的机能晋升。
什么是数据库分区？
数据库分区是一种物理数据库设计技能，DBA和数据库建模人员对其相当熟悉。固然分区技能可以实现许多结果，但其主要目标是为了在特定的SQL操纵中淘汰数据读写的总量以缩减响应时间。
分区主要有两种形式：//这里必然要留意行和列的观念（row是行，column是列）
程度分区（Horizontal Partitioning） 这种形式分区是对表的行举办分区，通过这样的方法差异分组内里的物理列支解的数据集得以组合，从而举办个别支解（单分区）或集团支解（1个或多个分区）。所有在表中界说的列在每个数据会合都能找到，所以表的特性依然得以保持。
举个简朴例子：一个包括十年发票记录的表可以被分区为十个差异的分区，每个分区包括的是个中一年的记录。（朋奕注：这里详细利用的分区方法我们后头再说，可以先说一点，必然要通过某个属性列来支解，譬如这里利用的列就是年份）
垂直分区（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范畴分区模式，通过年份举办分区：
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)
留意到了这里的最后一行吗？这里把不属于前面年度分另外年份范畴都包括了，这样才气担保数据不会堕落，各人今后要记着啊，否则数据库无缘无故堕落你就爽了。那下面我们成立没有分区的表（表名为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措施也汇报我们在对已分区的表的查询进程中仅对第一个分区举办了扫描，其他都跳过了。
哔厉吧拉，说阿说……横竖就是这个分区成果对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 5.1的分区成果能给数据机能带来庞大的晋升的同时减轻DBA的打点承担，假如分区公道的话。假如需要更多的资料可以去或 ?106得到相关资料。