MySQL分区的概念分类用法

分区有range 分区、list 分区、hash 分区、key 分区。

分区的限制：
1.主键/唯一键必须包含分区键，且没有列前要求
2.分区名不区分大小写 /*一般情况下由于MySQL数据库和表对应系统中的文件和目录，所以大小写敏感问题取决于系统*/


range 分区：基于一个给定的连续范围进行分区
range分区的限制：
1.区间连续且不能重叠
2.range分区要求分区键是整数类型，如果需要使用其他类型，需要使用range columns分区，否则只能使用函数处理转化为整型


用法：
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `ttime` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
 PARTITION BY RANGE  COLUMNS(ttime)
(PARTITION p0 VALUES LESS THAN ('2017-01-01') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN ('2017-02-01') ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN maxvalues ENGINE = InnoDB) ;
（maxvalues 表示最大的可能的整数值）


range 分区的应用场景：
1.需要删除过期的数据时，alter table t drop partition p0。（效率比delete快）
2.运行包括分区键的范围查询。（可以快速确定要查询的分区，扫描少量分区即可得到查询结果）




list 分区：基于枚举出的值列表分区
list 分区的限制：
1.list分区要求分区键是整数类型，如果需要使用其他类型可以使用list columns分区


用法：
 CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
 PARTITION BY LIST (id)
(PARTITION p0 VALUES IN (1,3,10) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (2,5,12) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (9,6,4) ENGINE = InnoDB) ;




查看分区情况：（查看information_schema.partitions表）
mysql> select partition_name,partition_expression,partition_description from partitions where table_name='t';
+----------------+----------------------+-----------------------+
| partition_name | partition_expression | partition_description |
+----------------+----------------------+-----------------------+
| p0             | `name`               | '1q','3a','4l'        |
| p1             | `name`               | 'a2','5','6'          |
+----------------+----------------------+-----------------------+
2 rows in set (0.00 sec)




hash 分区：基于给定的分区的数目，将数据分布到不同的分区
hash分区根据分配的算法的不一样分为常规hash分区和线性hash分区
hash分区的限制:只支持分区键为整数的分区
hash 分区应用场景：用来分散热点读，使数据在预先设置的几个分区尽量平均分布


语法：
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
 PARTITION BY HASH (id)
 PARTITIONS 3 ;




key 分区：类似hash分区，但是key支持出test，blob之外的所有数据类型做分区键
语法：
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
 PARTITION BY KEY (`name`)
 PARTITIONS 3 ;




复合分区：分区表中对每一个分区的再次分割。
复合分区适用于保存非常大量的数据记录。


用法：
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
 PARTITION BY RANGE  COLUMNS(id)
SUBPARTITION BY HASH (id)
SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);

综上，range、list都使用range column、list column来代替。