MySQL分区摘要

MySQL支持的分区类型为水平分区，并不支持垂直分区。

水平分区，以行为单位，划分到不同的物理文件中；垂直分区即针对列划分。

MySQL的分区，除了InnoDB支持，MyISAM也支持，所以分区并不是引擎级别的事儿。

分区并不一定会让访问变得更快，尤其是OLTP应用，OLAP应用使用分区的效果往往更好。

MySQL支持的如下几种分区类型。

• RANGE 行数据基于一个给定连续区间的列值划分。
• LIST 和RANGE类似，只是LIST面向的不是连续区间值，而是离散的集合。
• HASH 根据自定义的表达式返回值进行分区，不支持负数返回。
• KEY 根据MySQL提供的hash函数进行分区

不论按照哪种类型进行分区，如果当前表有主键或者唯一索引，分区列必须是唯一索引的组成部分。反过来，如果当前表没有定义主键也没有唯一索引，分区列可以是任何列。

分区操作示例：

create table sales(id int, price decimal(9,2), season int, finyear datetime) partition by range(season) (prtition s1 values less than (4),partition s2 values less than (7),partition s3 values less than (10),partition s4 values less than (13));

使用函数获取range值

create table sales(id int, price decimal(9,2), season int, finyear datetime) partition by range(year(finyear)) ( partition s1 values less than (2016), partition s2 values less than (2017), partition s3 values less than (2018),partition s4 values less than (2020));

create table sales(id int, price decimal(9,2), season int, finyear datetime) partition by range(year(finyear)*100+ month(finyear)) ( partition s1 values less than (201804), partition s2 values less than (201807), partition s3 values less than (201810),partition s4 values less than (201813));

LIST分区示例

create table sales(id int, price decimal(9,2), season int, finyear datetime) partition by LIST(id) ( partition s1 values in (1,3,5,7,9), partition s2 values in (2,4,6,8,10));

HASH分区示例

create table sales(id int, price decimal(9,2), season int, finyear datetime) engine=innodbpartition by hash(year(finyear));

KEY分区示例

create table sales(id int, price decimal(9,2), season int, finyear datetime) engine=innodbpartition by key(finyear);

分区后的数据存储结构及使用效果

以RANGE分区为例

插入数据

insert into sales select 1,10.05,1,'2018-01-01';insert into sales select 2,10.05,3,'2018-03-01';insert into sales select 3,10.05,4,'2018-04-01';

执行查询

explain partitions select * from sales where finyear < '2016-01-01';

可以看到，查询优化器，直接跳过第四个分区，在前三个分区中查询，这就是要的效果。

除了分区，还有分表、分库、分片操作，每一个都很重要，也都有各自的适用场景。