MySQL分区

因为在工作中没有使用分区表，更多的是使用mycat作为中间件做分库分表操作，但是作为MySQL的基本属性，这里只是简单介绍分区的操作，分区的类型。

博客中部分内容摘自： http://www.ywnds.com/?p=7226

查看当前数据库是否支持分区。（以下两种方式）

mysql> show plugins;.......| partition                  | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     || ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     || FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL              | GPL     || ngram                      | ACTIVE   | FTPARSER           | NULL              | GPL     || rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so | GPL     |+----------------------------+----------+--------------------+-------------------+---------+45 rows in set (0.00 sec)mysql>  #partition的status状态为ACTIVE，表示当前服务器支持分区表。USE information_schema;SELECT    plugin_name AS NAME,    plugin_version AS version,    plugin_status AS STATUSFROM    `PLUGINS`WHERE    PLUGIN_TYPE = "STORAGE ENGINE";      +--------------------+---------+----------+| NAME               | version | STATUS   |+--------------------+---------+----------+| binlog             | 1.0     | ACTIVE   || InnoDB             | 5.7     | ACTIVE   || MyISAM             | 1.0     | ACTIVE   || MRG_MYISAM         | 1.0     | ACTIVE   || MEMORY             | 1.0     | ACTIVE   || CSV                | 1.0     | ACTIVE   || PERFORMANCE_SCHEMA | 0.1     | ACTIVE   || BLACKHOLE          | 1.0     | ACTIVE   || partition          | 1.0     | ACTIVE   || ARCHIVE            | 3.0     | ACTIVE   || FEDERATED          | 1.0     | DISABLED |+--------------------+---------+----------+11 rows in set (0.00 sec)mysql>

一般来说我们安装MySQL都是从rpm包安装或者是免编译的二进制包安装的，这样的话安装之后的MySQL就是直接支持分区的。若是是用源码包安装，则需要添加参数-DWITH_PARTITION_STORAGE_ENGINE 才能支持分区。

若是想禁止分区，可以在启动MySQL的时候使用--skip-partition选项。

摘自：http://www.ywnds.com/?p=7226

分区是一种表的设计模式，正确的分区可以极大地提升数据库的查询效率，完成更高质量的SQL编程。但是如果错误地使用分区，那么分区可能带来毁灭性的的结果。

分区功能并不是在存储引擎层完成的，因此不只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持分区。但是并不是所有的存储引擎都支持，如CSV、FEDORATED、MERGE等就不支持分区。在使用此分区功能前，应该对选择的存储引擎对分区的支持有所了解。

MySQL数据库在5.1版本时添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

MySQL数据库支持的分区类型为水平分区（指将同一个表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（指将同一表中不同列的记录分配到不同的物理文件中）。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。

MySQL分区类型

数据库实例基于MySQL5.7

mysql> select @@version;+------------+| @@version  |+------------+| 5.7.22-log |+------------+1 row in set (0.00 sec)

RANGE分区

range分区是每个分区包含的值是分区表达式给定的值，分区应该是连续的，但是不重叠的，使用VALUES LESS THAN操作符定义。

通过一个实例来说明range分区：

  tb6 (id ) PARTITION

测试分区，插入3条数据。

mysql   tb6     tb6     tb6      information_schema.partitions  table_schema()  table_name . row  :: :: . row  :: :: . row  :: :: rows   (

插入的最大值小于18，那么插入大于18的值呢？

mysql   tb6   (HY000):  has no partition  value

基于时间间隔的分区方案有两种形式：

第一种：使用基于range的形式，但是借助于datetime中某一列的返回值。例如根据年来分区。

create table tb7(content date) partition by range(year(content))(partition p1 VALUES less  than (2010),partition p2 VALUES less  than (2020))#插入两条数据mysql> insert into tb7 select "2002-12-23";Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> insert into tb7 select "2012-12-23";Query OK, 1 row affected (0.02 sec)Records: 1  Duplicates: 0  Warnings: 0#查看数据分布mysql> select PARTITION_NAME, TABLE_ROWS  from information_schema.partitions where table_schema=database() and table_name='tb7'\G*************************** 1. row ***************************PARTITION_NAME: p1    TABLE_ROWS: 1*************************** 2. row ***************************PARTITION_NAME: p2    TABLE_ROWS: 12 rows in set (0.01 sec)mysql>

第二种：使用range column把列作为分区列队表进行分区。

CREATE TABLE tb8 (join_date date) PARTITION BY RANGE COLUMNS (join_date)(    PARTITION p1    VALUES        less than ("2010-1-1"),        PARTITION p2    VALUES        less than ("2020-1-1"))#插入数据mysql> insert into tb8 select "2008-08-08";Query OK, 1 row affected (0.02 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> insert into tb8 select "2018-08-08";Query OK, 1 row affected (0.02 sec)Records: 1  Duplicates: 0  Warnings: 0#查看数据分布mysql> select PARTITION_NAME, TABLE_ROWS  from information_schema.partitions where table_schema=database() and table_name='tb8'\G*************************** 1. row ***************************PARTITION_NAME: p1    TABLE_ROWS: 1*************************** 2. row ***************************PARTITION_NAME: p2    TABLE_ROWS: 12 rows in set (0.00 sec)

列表分区

LIST分区和RANGE分区类似，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择，而非连续的。

LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

官方文档中给出了一个实例，来看一下：

CREATE TABLE employees (  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);

假定有20个音像店，分布在4个有经销权的地区，如下表所示：

==========================地区  商店ID号--------------------------北区  3, 5, 6, 9, 17东区  1, 2, 10, 11, 19, 20西区  4, 12, 13, 14, 18中区  7, 8, 15, 16==========================

要以属于同一区域的存储的行存储在同一分区中的方式对此表进行分区。

CREATE TABLE employees (  id INT NOT NULL,  name VARCHAR(30),  hired DATE NOT NULL DEFAULT '1970-01-01',  separated DATE NOT NULL DEFAULT '9999-12-31',  store_id INT)PARTITION BY LIST(store_id)  PARTITION pNorth VALUES IN (3,5,6,9,17),  PARTITION pEast VALUES IN (1,2,10,11,19,20),  PARTITION pWest VALUES IN (4,12,13,14,18),  PARTITION pCentral VALUES IN (7,8,15,16));

在RANGE分区时提到过maxvalue参数，但是list分区没有对应的参数，因此如果试图插入的数值不再对应的list分区中，那么将会报错。

列分区

列分区分为range列分区和list列分区。列分区允许分区键中使用多个列，为确定记录被放入对应的分区中，所有的这些列都会被考虑的。

In addition, both RANGE COLUMNS partitioning and LIST COLUMNS partitioning support the use of non-integer columns for defining value ranges or list members.

此外，两种列分区都支持使用非整数来决定值范围和列成员。

  types: , , MEDIUMINT,  (),  . (This  the same   partitioning  RANGE    ) are  supported   dates  times are  supported , , ,    BLOB columns are  supported  partitioning columns.

range columns的分区情况上面已经提到过一个实例，这里不再说明，但是给出官方文档的地址：https://dev.mysql.com/doc/refman/5.7/en/partitioning-columns-range.html

list columns分区：MySQL5.7提供了对list columns分区的支持，是list分区的一种变种，使用多个列作为分区键，列的类型除了整型之外，还可以使用string，date，datetime类型。

官方文档有个实例，假设您的业务在下面的三个地区，14各城市中，

区域   城市
1      Oskarshamn，Högsby，Mönsterås
2      Vimmerby，Hultsfred，Västervik
3      Nässjö，Eksjö，Vetlanda
4      Uppvidinge，Alvesta，Växjo

通过LIST COLUMNS分区，您可以为客户数据创建一个表，根据客户所在城市的名称将行分配给与这些区域对应的4个分区中的任何一个

CREATE TABLE customers_1 (    first_name VARCHAR(25),    last_name VARCHAR(25),    street_1 VARCHAR(30),    street_2 VARCHAR(30),    city VARCHAR(15),    renewal DATE)PARTITION BY LIST COLUMNS(city) (    PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),    PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo'));

哈希分区

想想一下上面两种分区的问题，假设以时间分区的话，那么问题就是现在及之后的数据都会写入到一个分区中，数据量比较大的读与写都会引起一些性能问题。

HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据量大致都是一样的。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL自动完成这些工作，用户所要做的只是基于将要进行哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分隔成的分区数量。

要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量，如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1

CREATE TABLE tb6 (id INT) PARTITION BY HASH (id) PARTITIONS 3;         #若是没有PARTITIONS 3子句，#随便插入数据，查看数据的分布信息mysql> select PARTITION_NAME, TABLE_ROWS  from information_schema.partitions where table_schema=database() and table_name='tb6'\G*************************** 1. row ***************************PARTITION_NAME: p0    TABLE_ROWS: 3*************************** 2. row ***************************PARTITION_NAME: p1    TABLE_ROWS: 2*************************** 3. row ***************************PARTITION_NAME: p2    TABLE_ROWS: 03 rows in set (0.00 sec)

MySQL是如何计算分区的号的？这里有三个分区，记为n，我们插入的值是整型记为m，那么计算分区号p=mod(m,n);取这两个的余数。

mysql> insert into tb6 select 5;              #插入数值5，5除以3得到的余数是2，因此插入的是最后一个分区。分区的排序从0开始。Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> select PARTITION_NAME, TABLE_ROWS  from information_schema.partitions where table_schema=database() and table_name='tb6'\G*************************** 1. row ***************************PARTITION_NAME: p0    TABLE_ROWS: 3*************************** 2. row ***************************PARTITION_NAME: p1    TABLE_ROWS: 2*************************** 3. row ***************************PARTITION_NAME: p2    TABLE_ROWS: 13 rows in set (0.00 sec)

上面的计算分区号的方法是取模，这样的哈希算法称为常规hash，MySQL还支持线性hash（LINEAR HASH分区），其使用的算法是二次幂方法。

创建分区表的时候需要加上关键字“linear”。

CREATE TABLE tb7 (id INT) PARTITION BY LINEAR HASH (id) PARTITIONS 3;

按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000吉）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

key分区

KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，支持字符串HASH分区，KEY分区使用MySQL数据库提供的函数进行分区，这些函数基于与PASSWORD()一样的运算法则。

CREATE TABLE tk (  col1 INT NOT NULL,  col2 CHAR(5),  col3 DATE)  PARTITION BY LINEAR KEY (col1)  PARTITIONS 3;

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。

MySQL子分区

子分区（subparttitioning）是在分区的基础上再进行分区，有时也称这种分区为复合分区。MySQL数据库允许在RANGE和LIST的分区上再进行HASH或KEY的子分区，如：

create table ts(a int,b date) engine=innodb  partition by range(year(b))  subpartition by hash(to_days(b))  subpartitions 2 (  partition p0 values less than(1990),  partition p1 values less than(2000),  partition p2 values less than maxvalue);[root@mgt01 mytest]# ll ts*-rw-r----- 1 mysql mysql  8578 Jan 11 15:37 ts.frm-rw-r----- 1 mysql mysql 98304 Jan 11 15:37 ts#P#p0#SP#p0sp0.ibd-rw-r----- 1 mysql mysql 98304 Jan 11 15:37 ts#P#p0#SP#p0sp1.ibd-rw-r----- 1 mysql mysql 98304 Jan 11 15:37 ts#P#p1#SP#p1sp0.ibd-rw-r----- 1 mysql mysql 98304 Jan 11 15:37 ts#P#p1#SP#p1sp1.ibd-rw-r----- 1 mysql mysql 98304 Jan 11 15:37 ts#P#p2#SP#p2sp0.ibd-rw-r----- 1 mysql mysql 98304 Jan 11 15:37 ts#P#p2#SP#p2sp1.ibd[root@mgt01 mytest]#

表ts先根据b列进行了RANGE分区，然后又进行了一次HASH分区，所以分区的数量应该为（3×2=）6个，这通过查看物理磁盘上的文件也可以得到证实。我们也可以通过使用subpartition语法来显示地指出各个子分区的名字，例如对上述的ts表同样可以这样：

create table ts(a int,b date) engine=innodb  partition by range(year(b))  subpartition by hash(to_days(b)) (    partition p0 values less than(1990) (    subpartition s0,    subpartition s1    ),    partition p1 values less than(2000) (    subpartition s2,    subpartition s3    ),    partition p2 values less than maxvalue (    subpartition s4,    subpartition s5    )  );

子分区的建立需要注意以下几个问题：

1、每个子分区的数量必须相同。

2、要在一个分区表的任何分区上使用subpartition来明确定义任何子分区，就必须定义所有的子分区。

3、每个subpartition子句必须包括子分区的一个名字。

4、子分区的名字必须是唯一的。

分区中对NULL值的处理

MySQL数据库允许对NULL值做分区，但是处理的方法与其他数据库可能完全不同。MySQL数据库的分区总是视NULL值小于任何的一个非NULL值，这和MySQL数据库中处理NULL值的ORDER BY操作是一样的。因此对于不同的分区类型，MySQL数据库对于NULL值的处理也是各不相同。

对于RANGE分区，如果向分区列插入了NULL值，则MySQL数据库会将该值放入最左边的分区。

create table t_range(  a int,  b int) engine=innodb   partition by range(b)(  partition p0 values less than (10),  partition p1 values less than (20),  partition p2 values less than maxvalue);#插入数据    mysql> insert into t_range select 1,1;Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0 mysql> insert into t_range select 1,NULL;Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0#查看分区信息mysql> select TABLE_ROWS from information_schema.partitions where table_schema=database() and table_name='t_range'\G*************************** 1. row ***************************TABLE_ROWS: 2*************************** 2. row ***************************TABLE_ROWS: 0*************************** 3. row ***************************TABLE_ROWS: 03 rows in set (0.00 sec)

可以看到两条数据都放入了p0分区，也就是说range分区下，NULL值会放入最左边的分区中。另外需要注意的是，如果删除p0这个分区，删除的将是小于10的记录，并且还有NULL值的记录，这点非常重要。

对于LIST分区，如果向分区列插入了NULL值，则必须显示地指出哪个分区放入NULL值，否则会报错。

create table t_list(  a int,  b int) engine=innodb   partition by list(b)(  partition p0 values in (1,3,5,7,9,NULL),  partition p1 values in (2,4,6,8,10));#插入数据mysql> insert into t_list select 1,null;Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0 mysql> insert into t_list select 1,null;Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0#查看分区信息mysql> select TABLE_ROWS from information_schema.partitions where table_schema=database() and table_name='t_list'\G*************************** 1. row ***************************TABLE_ROWS: 2*************************** 2. row ***************************TABLE_ROWS: 02 rows in set (0.00 sec)

对于HASH和KEY分区，对于NULL值的处理方法和RANGE分区、LIST分区不一样。任何分区函数都会将含有NULL值的记录返回为0。

create table t_hash(  a int,  b int) engine=innodb   partition by hash(b)  partitions 2;#插入数据到分区mysql> insert into t_hash select 1,0;Query OK, 1 row affected (0.01 sec)Records: 1  Duplicates: 0  Warnings: 0 mysql> insert into t_hash select 1,NULL;Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0#查看分区信息mysql> select TABLE_ROWS from information_schema.partitions where table_schema=database() and table_name='t_hash'\G*************************** 1. row ***************************TABLE_ROWS: 2*************************** 2. row ***************************TABLE_ROWS: 02 rows in set (0.01 sec)

分区和性能

分区真的会加快数据库的查询吗？实际上可能根本感觉不到查询速度的提升，甚至会发现查询速度急剧下降，因此在合理使用分区之前，必须了解分区的使用环境。

数据库的应用分为两类：一类是OLTP（在线事务处理），如Blog、电子商务、网络游戏等；另一类是OLAP（在线分析处理），如数据仓库、数据集市。对于OLAP的应用，分区的确是可以很好地提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一张很大的表。假设有一张1亿行的表，其中有一个时间戳属性列。用户的查询需要从这张表中获取一年的数据。如果按时间戳进行分区，则只需要扫描相应的分区即可。这就是前面介绍的分区修剪技术。

对于OLTP的应用，分区应该非常小心。在这种应用下，通常不可能会获取一张大表10%的数据，大部分都是通过索引返回几条记录即可。而根据B+树索引的原理可知，对于一张大表，一般的B+树需要2~3次的磁盘IO。因此B+树可以很好地完成操作，不需要分区的帮助，并且设计不好的分区会带来严重的性能问题。

如很多开发团队会认为含有1000w行的表是一张非常巨大的表，所以他们往往会选择采用分区，如对主键做10个HASH的分区，这样每个分区就只有100w的数据了，因此查询应该变得更快了。如select * from table where pk=@pk。但是有没有考虑过这样一种情况：100w和1000w行的数据本身构成的B+树的层次都是一样的，可能都是2~3层。那么上述走主键分区的索引并不会带来性能的提高。好的，如果1000w的B+树高度是3,100w的B+树高度是2，那么上述按主键分区的索引可以避免1次IO，从而提高查询的效率。这没问题，但是这张表只有主键索引，没有任何其他的列需要查询的。如果还有类似如下的SQL：select * from table where key=@key，这时对于key的查询需要扫描所有的10个分区，即使每个分区的查询开销为2次IO，则一共需要20次IO。而对于原来单表的设计，对于KEY的查询只需要2~3次IO。

由以上结论可以看出，对于在OLTP场景中使用分区一定要特别小心了。

分区键必须包含在主键字段

MySQL的分区字段，必须包含在主键字段内。在对表进行分区时，如果分区字段没有包含在主键字段内，会报错！如表user的主键为id，分区字段为crtTime ，要想根据时间按月分区，代码如下：

 ()  ()       InnoDB  CHARSET less than (to_days( less than (to_days( less than (to_days( less than (to_days( less than (to_days( less than (to_days( less than (to_days( less than (to_days( less than (to_days( less than maxvalue);

错误提示：#1503

A PRIMARY KEY MUST INCLUDE ALL COLUMNS IN THE TABLE’S PARTITIONING FUNCTIONMySQL

主键的限制，每一个分区表中的公式中的列，必须在primary key/unique key中包括。分区字段必须包含在主键字段内，至于为什么MySQL会这样考虑，CSDN的斑竹是这么解释的：为了确保主键的效率，否则同一主键区的东西一个在Ａ分区，一个在Ｂ分区，显然会比较麻烦。

下面讨论解决办法，毕竟在一张表里，日期做主键的还是不常见。顺应MySQL的要求，就把分区字段加入到主键中，组成复合主键即可。

CREATE TABLE `TradeOrderDetails` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `tradeNo` varchar(20) NOT NULL,  `crtTime` datetime NOT NULL,  `updTime` datetime DEFAULT NULL,  PRIMARY KEY (`id`,`crtTime`)) ENGINE=InnoDB DEFAULT CHARSET=utf8   partition by range(to_days(crtTime)) (  partition p201705 values less than (to_days('2017-06-01')),  partition p201706 values less than (to_days('2017-07-01')),  partition p201707 values less than (to_days('2017-08-01')),  partition p201708 values less than (to_days('2017-09-01')),  partition p201709 values less than (to_days('2017-10-01')),  partition p201710 values less than (to_days('2017-11-01')),  partition p201711 values less than (to_days('2017-12-01')),  partition p201712 values less than (to_days('2018-01-01')),  partition p201801 values less than (to_days('2018-02-01')),  partition p2018 values less than maxvalue);

MySQL5.7对分区的引进

http://mysqlserverteam.com/innodb-native-partitioning-early-access

上面是MySQL开发团队写的关于InnoDB Native Partitioning的文章。文章中大概讲的内容是，在MySQL 5.6里面，分区的信息是在MySQL Server层维护的（在.par文件里面），InnoDB引擎层是不知道有分区这个概念的，InnoDB引擎层把每一个分区都当成一张普通的InnoDB表。在打开一个分区表时，会打开很多个分区，打开这些分区表就相当于打开了同等数量的InnoDB表，这需要更多内存存放InnoDB表的元数据和各种与ibd文件打开相关的各种cache与handler的信息。在MySQL 5.7里面，InnoDB引入了Native Partitioning，它把分区的信息从Server层移到了InnoDB层，打开一个分区表和打开一个InnoDB表的内存开销基本是一样的。