MySQL中表分区技术详细解析

mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+-------+
|Variable_name     | Value |
+-----------------------+-------+
| have_partition_engine | YES  |
+-----------------------+-------+

mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+---------+---------+
| Name            | Status  | Type        | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog           | ACTIVE  | STORAGE ENGINE   | NULL  | GPL   |
| mysql_native_password   | ACTIVE  | AUTHENTICATION   | NULL  | GPL   |
..................................................................................
| INNODB_LOCKS        | ACTIVE  | INFORMATION SCHEMA | NULL  | GPL   |
| INNODB_LOCK_WAITS     | ACTIVE  | INFORMATION SCHEMA | NULL  | GPL   |
| partition         | ACTIVE  | STORAGE ENGINE   | NULL  | GPL   |
+----------------------------+----------+--------------------+---------+---------+

mysql> use mytest;
Database changed
mysql> create table range_p( 
  -> perid int(11), 
  -> pername char(12) not null, 
  -> monsalary DECIMAL(10,2),
  -> credate datetime 
  -> ) partition by range(year(credate))( 
  -> partition p2011 values less than (2011), 
  -> partition p2012 values less than (2012), 
  -> partition p2013 values less than (2013), 
  -> partition p2014 values less than (2014),
  -> partition p2015 values less than maxvalue 
  -> );
Query OK, 0 rows affected (0.12 sec)

mysql> create table list_p( "color: #800000">HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL中有效的、产生非负整数值的任何表达式。

--以int字段hash分区


create table hash_p( 
perid int(11), 
pername char(12) not null,
sex int(1) not null,
monsalary DECIMAL(10,2),
credate datetime 
) partition by hash (perid) 
partitions 8;



--以时间函数hash分区


mysql> create table hash_p( 
  -> perid int(11), 
  -> pername char(12) not null,
  -> sex int(1) not null,
  -> monsalary DECIMAL(10,2),
  -> credate datetime 
  -> ) partition by hash (year(credate)) 
  -> partitions 8;
Query OK, 0 rows affected (0.11 sec)



3.4键值分区
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。 其分区方法与hash很相似：


mysql> create table key_p( 
  -> perid int(11), 
  -> pername char(12) not null,
  -> sex int(1) not null,
  -> monsalary DECIMAL(10,2),
  -> credate datetime 
  -> ) partition by key (perid) 
  -> partitions 8;
Query OK, 0 rows affected (0.12 sec)



3.5其它说明
mysql-5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的进化，COLUMNS分区可以直接使用非整形数据进行分区。COLUMNS分区支持以下数据类型： 所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL则不支持。 日期类型，如DATE和DATETIME。其余日期类型不支持。 字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。 COLUMNS可以使用多个列进行分区。


mysql> create table range_p( 
  -> perid int(11), 
  -> pername char(12) not null, 
  -> monsalary DECIMAL(10,2),
  -> credate datetime 
  -> ) PARTITION BY RANGE COLUMNS (credate)( 
  -> partition p20151 values less than ('2015-04-01'), 
  -> partition p20152 values less than ('2015-07-01'), 
  -> partition p20153 values less than ('2015-10-01'), 
  -> partition p20154 values less than ('2016-01-01'),
  -> partition p20161 values less than ('2016-04-01'),
  -> partition partlog values less than maxvalue 
  -> );
Query OK, 0 rows affected (0.12 sec)



总结：


分区表是在MySQL5.1中新增的的功能，截止到MySQL5.1.22-rc,分区技术并不很成熟，很多分区的维护和管理功能未实现。如，分区内数据存储空间的回收、分区的修复、分区的优化等，MySQL的分区可以用在可以按分区删除的表中，且对数据库的修改操作不大，且频繁按照分区字段进行查询的表中（如恶意代码中的统计表按天分区，经常按照时间进行查询、分组等，且可以按天删除分区）。此外，由于MySQL无全局索引只有分区索引，当一张有2个唯一索引[z5] 的时候，不能将此表分区，分区列中必须包含主键。否则MySQL会报错。


总之，MySQL对于分区的限制很多，且个人认为hash和key的分区实际意义不是太大。


分区引入了一种新的优化查询的方式（当然，也有相应的缺点）。优化器可以使用分区函数修整分区，或者把分区从查询中完全移除掉。它通过推断是否可以在特定的分区上找到数据来达成这种优化。因此在最好的情况下，修整可以让查询访问更少的数据。重要的是要在WHERE子句中定义分区键，即使它看上去像是多余的。通过分区键，优化器就可以去掉不用的分区，否则的话，执行引擎就会像合并表那样访问表的所有分区，这在大表上会非常慢。分区数据比非分区数据更好维护，并且可以通过删除分区来移除老的数据。分区数据可以被分布到不同的物理位置，这样服务器可以更有效地使用多个硬盘驱动器。


[z1]分区函数的返回值必须是整数，新增分区的分区函数返回值应大于任何一个现有分区的分区函数的返回值。

 [z2]对于有主键的表错误提示：#1503

 A PRIMARY KEY MUST INCLUDE ALL COLUMNS INTHE TABLE'S PARTITIONING FUNCTION，没有主键的则无此约束

 [z3]注意：对于通过RANGE分区的表，只可以使用ADD PARTITION添加新的分区到分区列表的高端。即不能添加比这个分区的范围小的分区。

 [z4] 对于按照RANGE分区的表，只能重新组织相邻的分区；不能跳过RANGE分区。不能使用REORGANIZEPARTITION来改变表的分区类型；也就是说，例如，不能把RANGE分区变为HASH分区，反之亦然。也不能使用该命令来改变分区表达式或列。

 [z5]注意主键和唯一索引的区别
官方资料：https://dev.mysql.com/doc/refman/5.5/en/partitioning.html