什么是数据库分区？

Mysql数据库中的数据是以文件的形式保存在磁盘上的，默认放在mysql/data路径，不同的引擎生成的文件后缀不同，像MyISAM生成.MYD和.MYI后缀文件，MYI存放索引（非聚簇索引）。Innodb生成.idb后缀文件（聚簇索引）。

当mysql表中数据越来越多，文件会变的越来越大，这个时候查询数据的速度会变慢。这个时候就可以用到分区这个功能，将一张表生成的数据文件拆分为多份，进行横向水平分区，提升查询效率。并且分区在清理历史数据的场景上非常高效

“随着MySQL越来越流行，Mysql里面的保存的数据也越来越大。在日常的工作中，我们经常遇到一张表里面保存了上亿甚至过十亿的记录。这些表里面保存了大量的历史记录。 对于这些历史数据的清理是一个非常头疼事情，由于所有的数据都一个普通的表里。所以只能是启用一个或多个带where条件的delete语句去删除（一般where条件是时间）。 这对数据库的造成了很大压力。即使我们把这些删除了，但底层的数据文件并没有变小。面对这类问题，最有效的方法就是在使用分区表。最常见的分区方法就是按照时间进行分区。 分区一个最大的优点就是可以非常高效的进行历史数据的清理。”

目前MySQL支持范围分区（RANGE），列表分区（LIST），哈希分区（HASH）以及KEY分区四种。下面我们逐一介绍每种分区：

RANGE分区：

基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。本例中使用to_days函数

如上图，以时间字段分区，把一个航班动态相关的表分成5个区先。

分完了以后可以看到生成了5个.ibd文件（Innodb）

INSERT INTO `nick`.`partition_table_name`(`id`, `flightno`, `flightdate`) VALUES (1, 'TG663', '2019-05-01 00:00:00');

INSERT INTO `nick`.`partition_table_name`(`id`, `flightno`, `flightdate`) VALUES (2, 'TG663', '2019-06-01 00:00:00');

INSERT INTO `nick`.`partition_table_name`(`id`, `flightno`, `flightdate`) VALUES (3, 'TG663', '2019-07-01 00:00:00');

INSERT INTO `nick`.`partition_table_name`(`id`, `flightno`, `flightdate`) VALUES (4, 'TG663', '2019-08-01 00:00:00');

INSERT INTO `nick`.`partition_table_name`(`id`, `flightno`, `flightdate`) VALUES (5, 'TG663', '2019-09-01 00:00:00');

我们插入五条数据。

然后我们用执行计划查看扫描分区情况

EXPLAIN select*from partition_table_name where flightdate = '2019-05-01 00:00:00';

EXPLAIN select*from partition_table_name where flightdate = '2019-06-01 00:00:00';

EXPLAIN select*from partition_table_name where flightdate = '2019-07-01 00:00:00';

EXPLAIN select*from partition_table_name where flightdate = '2019-08-01 00:00:00';

可以看到，针对航班起飞时间进行分区，执行计划显示根据查询条件不同，会去不同的分区下面扫描数据，达到了跟水平分表类似的效果。

上述例子是基于datetime类型的，如果是timestamp类型，mysql也提供了基于UNIX_TIMESTAMP函数的RANGE分区方案，只需要替换函数即可。把TO_DAYS替换为UNIX_TIMESTAMP，完成分区裁剪，5.7以后还提供了TO_SECONDS()函数，5.7之前还有YEAR()函数。

注意几点：

1.Mysql分区表如果有主键，必须包含分区字段，所以我create语句是一个复合主键，否则将会报创表sql错误。

2.对于原生分区，分区对象返回的只能是整数值。

3.分区字段不能为NULL，所以创表语句要NOT NULL。

其余的分区就不一个个实践了，我直接贴一些其他博客中的例子来说明

LIST 分区

LIST分区

LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。二者在语法方面非常的相似。同样建议LIST分区列是非null列，否则插入null值如果枚举列表里面不存在null值会插入失败，这点和其它的分区不一样，RANGE分区会将其作为最小分区值存储，HASH\KEY分为会将其转换成0存储，主要LIST分区只支持整形，非整形字段需要通过函数转换成整形.

CREATE TABLE my_member (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    created DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT,

    store_id INT)PARTITION BY HASH(id)

PARTITIONS 4;

Hash 分区

我们在实际工作中经常遇到像会员表的这种表。并没有明显可以分区的特征字段。但表数据有非常庞大。为了把这类的数据进行分区打散mysql 提供了hash分区。基于给定的分区个数，将数据分配到不同的分区，HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数。表达式可以是mysql中任意有效的函数或者表达式，对于非整形的HASH往表插入数据的过程中会多一步表达式的计算操作，所以不建议使用复杂的表达式这样会影响性能。

Hash分区表的基本语句如下：

CREATE TABLE my_member (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    created DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT,

    store_id INT)PARTITION BY HASH(id)

PARTITIONS 4;

注意：

HASH分区可以不用指定PARTITIONS子句，如上文中的PARTITIONS 4，则默认分区数为1。

不允许只写PARTITIONS，而不指定分区数。

同RANGE分区和LIST分区一样，PARTITION BY HASH (expr)子句中的expr返回的必须是整数值。

HASH分区的底层实现其实是基于MOD函数。譬如，对于下表

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4; 如果你要插入一个col3为“2017-09-15”的记录，则分区的选择是根据以下值决定的：

MOD(YEAR(‘2017-09-01’),4) = MOD(2017,4) = 1

LINEAR HASH分区

LINEAR HASH分区是HASH分区的一种特殊类型，与HASH分区是基于MOD函数不同的是，它基于的是另外一种算法。

格式如下：

CREATE TABLE my_members (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT,

    store_id INT)PARTITION BY LINEAR HASH( id )

PARTITIONS 4;

说明： 它的优点是在数据量大的场景，譬如TB级，增加、删除、合并和拆分分区会更快，缺点是，相对于HASH分区，它数据分布不均匀的概率更大。

KEY分区

KEY分区其实跟HASH分区差不多，不同点如下：

KEY分区允许多列，而HASH分区只允许一列。

如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。

KEY分区对象必须为列，而不能是基于列的表达式。

KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。

格式如下：

CREATE TABLE k1 (  

  id INT NOT NULL PRIMARY KEY,       

 name VARCHAR(20)) PARTITION BY KEY()

PARTITIONS 2;

在没有主键或者唯一键的情况下，格式如下：

CREATE TABLE tm1 (

    s1 CHAR(32)

)PARTITION BY KEY(s1)

PARTITIONS 10;

Tips：

贴一段知乎上关于mysql优化的文，后续将继续出分表、分库相关的blog

MySQL 对于千万级的大表要怎么优化？ - 互联网编程的回答 - 知乎

https://www.zhihu.com/question/19719997/answer/549041957