mysql的分区（Partitioning）

得过且过

　　partitioning 是mysql5.1 以后出现的特性，一般来说，分区主要是两种方式：
1.水平切分（Horizontal partitioning） 
一个系统的用户表（user）,可以把userid<1w 的用户信息放入到user1表，把1w<=userid<2w的放入user2表， 而user1 和user2这两个表的表结构（schema）是完全一样的.
比较常用的用到水平切分的技术有：
归档文件 ， 旧的放入到一个表中，新的可以放入到另一个表中。
分片（sharding）, 把 user1表放到server1上，user2表放到server2上。
2. 垂直切分 （Vertical partitioning）
用户表有很多列（column）,我们可以把用户的一部分信息（比如：address信息）存入到一个表，而把另一部分信息 存入到另外一个表中。这里这两个表当然要有一个相同的主键userid连接两个表。这种方式也称为：row splitting。
我们可以把一些很少变动的列放在一个表（table1）， 然后把一些经常变动的列放入到另外一个表中(table2)，这样我们就可以利用table1的query cache,来提升这部分数据的查询速度。若是改造现有的表的话，我们可以用视图（view）来对应用隐藏这些变动，当然这样会损失一部分性能。

mysql中水平切分的算法有以下几种：
RANGE：比如,id<5000放入一个表， id>=5000的放入另一个表。
LIST：根据一些 整数值来选择数据的分区，例如：gender列中用1表示男，2表示女，那么gender 为 1的数据放入user1,gender为2的放入user2。
HASH：根据hash值来选择分区。
KEY：mysql的一个内部算法，mysql根据key来选择分区。
还有一种composite ：RANGE 和LIST 可以是 HASH 和 KEY的子分区。
总的来说就是根据某个partition key（必须是整数） 来决定数据所在的分区。

如果一个表有primary key,那么这个primary key 必须包含所有的partition key 的列, 如果这个表没有primary key,而有 unique key ，那么同样的，这个unique key必须包含所有的partition key 的列。当然没有primary key 和 unique key也可以用partition.

下面就来看看具体到底怎么来partition吧。
RANGE  partitioning
如果是新建一个表则可以用如下语句：
CREATE TABLE `test_partition` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL DEFAULT '',
`birthday` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (3) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION plast VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

假若已经用以下语句创建了表：
CREATE TABLE `test_partition` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL DEFAULT '',
`birthday` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
那么 就需要用到alter语句来 修改表 ：
alter table test_partition 
partition by range(id)(
partition p0 values less than (3) ENGINE = MyISAM ,
partition p1 values less than (6) ENGINE = MyISAM, 
PARTITION plast VALUES LESS THAN MAXVALUE ENGINE = MyISAM); 
这里 我们 如果 需要用到 DATE, DATETIME or TIMESTAMP类型来 分区的话可以 用  YEAR() 和  TO_DAYS()来 把日期转换为整型。

LIST partitioning
CREATE TABLE employees_by_region (
emp_id INT NOT NULL,
f_name VARCHAR(30) NOT NULL,
l_name VARCHAR(30) NOT NULL,
store_id INT NOT NULL
)
PARTITION BY LIST (store_id) (
PARTITION Southeast VALUES IN (1,2,3,4,5,6,7,8,9,10,15,17,18,20,21,24),
PARTITION Midatlantic VALUES IN (11,12,13,14,16,19,22,23,25,26,27,28),
PARTITION Northeast VALUES IN (29,30,33,38,40,41,50,56,64,65,75)
);

HASH partitioning
CREATE TABLE employees_by_region (
emp_id INT NOT NULL,
f_name VARCHAR(30) NOT NULL,
l_name VARCHAR(30) NOT NULL,
store_id INT NOT NULL
)
PARTITION BY HASH (store_id)
PARTITIONS 16;
(这里其实就是  store_id % 16 ， 可以指定用  LINEAR HASH )

KEY partitioning
CREATE TABLE employees_by_region (
emp_id INT NOT NULL,
f_name VARCHAR(30) NOT NULL,
l_name VARCHAR(30) NOT NULL,
store_id INT NOT NULL
)
PARTITION BY KEY(store_id)
PARTITIONS 16;
key partitioning 用的 是一种类似PASSWORD() 的 hash算法 。
NDB 用  MD5() 来作为key partitioning 的hash算法.
可以指定用  LINEAR KEY

Composite partitioning
...待续...


注：
1 可以用 show variables like '%partition%'; 来查看你的mysql 是否支持partition
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   | 
+-------------------+-------+
2 /*!50100   */ 表示mysql只有高于5.1的版本才有效，对低于5.1版本的就相当于注释一样。
3 我们可以 用  EXPLAIN PARTITIONS 来查看 包含partition信息的 查询计划。

参考
mysql administartor's bible
http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitions.html
http://dev.mysql.com/tech-resources/articles/performance-partitioning.html