InnoDB调优-分区表

分区表是MySQL数据库在 5.1 版本时引入的新特性，主要用于数据库高可用性的管理。

分区表是一个独立的逻辑表，底层由多个物理子表组成；而分区的过程实际上就是将一个表分解为更小、更可管理的部分，对分区表的请求会被转化为对存储引擎的接口调用。当然，这对于应用来说是完全透明的。

MySQL在创建表时使用 PARTITION BY 语句指定分区类型和定义分区存放数据。因为分区不需要精确定位到每条数据的位置，也就无须额外的数据结构去记录每个分区有哪些数据，所以其代价非常低。

在执行查询的时候，优化器只需要根据一个简单的表达式就可以知道每个分区存放的是什么数据，然后过滤那些没有我们需要数据的分区。

use db_test;create table t_p_test(create_time datetime)engine=innodb-- 定义分区信息partition by range ( year( create_time))(partition p2016 values less than (2017),partition p2017 values less than (2018));

创建分区表后，我们可以通过 INFORMATION_SCHEMA.PARTITIONS 查询到分区明细，很明显能够看到这是分成了2个区，分区的表达式为 year(create_time)

select PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA = schema() and TABLE_NAME = 't_p_test';

进入数据库数据文件存储路径，能够看到这个表实际上是被划分成了多个物理子表的。普通表是由一个 ibd文件 组成，而分区后就变成了由建立分区时的各个分区 ibd文件 组成。分区文件名遵循 “ 表名#P#分区名.ibd ” 格式

分区表底层由多个物理子表组成，而MySQL数据库目前仅支持局部分区（局部分区是指同一个分区中即存放了数据又存放了索引；全局分区则是数据放在各分区中，但是所有数据的索引放在一个对象中），这也就意味着索引也是按照分区子表定义的，没有全局索引。这一点在创建和使用分区时需要特别注意。

Partiton Pruning（分区修剪）

分区的本意是将数据按照一个较粗粒度分在不同的表中，便于聚拢数据做批量操作（例如一次批量删除整个分区的数据就变得十分的方便了）。

但是由于在对分区表进行查询时优化器能够根据分区表达式过滤一些分区，减少了需要扫描的数据量，这对于需要频繁地扫描一张很大的表的应用来说，无疑是一个可以很好地提高查询性能的方法。

相较与普通表，分区表在使用上并没有什么明显不同的地方

-- 写入insert into t_p_test( create_time) values ('2016-01-01'), ('2016-12-04'), ('2017-02-01'), ('2017-07-10');-- 查询select * from t_p_test where create_time = '2017-07-10';

在执行了写入语句之后，数据其实是被写入了不同的分区的

而在进行查询时，也并没有扫描所有的数据，仅仅扫描了查询数据所在的分区。查看对分区表进行操作的执行计划明细，可以使用 EXPLAINS PARTITIONS 命令

explain partitionsselect * from t_p_test where create_time = '2017-07-10';

我们能够看到在进行查询的时候，SQL优化器通过过滤函数直接扫描了数据所在的分区，并不会去扫描所有的分区，因而提升了查询的速度。这被称为“分区修剪”（Partition Pruning）。

分区类型

分区功能并不是在存储引擎层完成的，因此并非只有 InnoDB存储引擎 支持分区。当然，因为不同存储引擎之间在对数据处理上存在差异，所以不同的存储引擎底层对分区数据的操作也会有细微的差别。

MySQL数据库支持 RANGE、LIST、HASH、KEY 这四种分区类型，在MySQL 5.5 版本后又新增 COLUMNS分区（可视为 RANGE分区 和 LIST分区 的一种进化）。在介绍这几种分区之前，我们需要牢记一点：MySQL数据库支持的分区类型为水平分区，也就是说不论创建何种类型的分区，都是根据 “行” 将数据分配到不同的物理文件中的。

1、RANGE分区

RANGE分区 是比较常用的一种分区类型，行数据基于属于一个给定连续区间的列值被放入分区，简单的说就是根据给定范围进行划分。

例如先前定义的 t_p_test 表使用的便是 RANGE分区 类型，根据时间列 create_time 列进行分区。这也是 RANGE分区 较为常用的场景。

定义RANGE分区语句为 PARTITION BY RANGE( expr) ，这里的表达式 expr 的返回值要是一个确定的整数类型（integer），且不能是常数。如果不是整型，那么应该通过函数将其转化为整型， t_p_test 表便是通过 year() 函数将 create_time 转化为了整型。

定义了分区后，写入表的数据应该严格遵守分区的定义，如果写入的数据不在分区中定义的范围时，MySQL数据库将会抛出异常。

例如对 t_p_test 表写入 2020 年的数据。该表当前仅定义了小于2017、小于2018 这2个分区，而 2020 不满足任一个分区定义，所以无法插入

insert into t_p_test select '2020-10-01';--> Error Code: 1526. Table has no partition for value 2020

对于类似问题，我们可以对分区添加一个 MAXVALUE 值的分区。MAXVALUE 可以理解为正无穷，这样写入的数据总能找到应该存放的分区。

alter table t_p_test add partition (partition Pmax values less than ( MAXVALUE));

2、LIST分区

LIST分区与RANGE分区十分类似，只是分区列的值是离散的，而非连续的。

LIST分区的定义语句为 PARTITION BY LIST( expr) ，表达式 expr 约束与 RANGE分区 一致，必须为整型。

create table t_p_test2(create_time datetime)partition by list( year(create_time))(partition p0 values in ( 2011, 2013, 2015, 2017),partition p1 values in ( 2012, 2014, 2016, 2018));

对LIST分区表写入数据，只能写入定义值。例如分区列的值包含 2016 ，那么可以成功写入2016 年的数据

insert into t_p_test2 select '2016-01-02';

而如果写入分区未定义的值时，将会操作失败，例如写入 2019 年的数据

insert into t_p_test2 select '2019-04-06';--> Error Code: 1526. Table has no partition for value 2019

需要注意的是，在 INSERT 多行数据的过程中遇到分区未定义的值时，不同的存储引擎的处理可能会存在差异。

以 MyISAM存储引擎 和 InnoDB存储引擎 为例，InnoDB存储引擎 会将该操作作为一个完整事务进行处理，当遇到分区未定义的值无法写入时便会抛出异常并进行回滚，结果是满足分区定义的正常值也没有写入表。

MyISAM存储引擎 则不同，由于 MyISAM存储引擎 不支持事务操作，所以在遇到分区未定义的值无法正常写入时会抛出异常，但是在此之前插入的值会保留下来。

例如我们定义2张表： t_p_list_innodb 和 t_plist_myisam ，分别使用InnoDB存储引擎和MyISAM存储引擎

-- 使用InnoDB存储引擎的List分区表create table t_p_list_innodb(num int)engine=innodbpartition by list(num)(partition list1 values in ( 1, 3, 5, 7, 9),partition list2 values in ( 2, 4, 6, 8, 10));-- 使用MyISAM存储引擎的List分区表create table t_p_list_myisam(num int)engine=myisampartition by list(num)(partition list1 values in ( 1, 3, 5, 7, 9),partition list2 values in ( 2, 4, 6, 8, 10));

接着对表写入数据 20 ，因为这个 20 不在分区定义内，所以是没法成功写入的

insert into t_p_list_innodb( num) values( 1),( 3),( 6),( 20),( 8);--> Error Code: 1526. Table has no partition for value 20insert into t_p_list_myisam( num) values( 1),( 3),( 6),( 20),( 8);--> Error Code: 1526. Table has no partition for value 20

对2张表的写入都失败了，但不同的是，InnoDB存储引擎分区表操作失败后并没有写入任何数据，而MyISAM存储引擎分区表则将写入 20 失败之前写入成功的值保留了下来

select * from t_p_list_innodb;select * from t_p_list_myisam;

此时 t_p_list_myisam 表中的数据为

3、HASH分区

HASH分区 的目的将数据按照某列进行hash计算后更加均匀的分散到各个分区。相比，RANGE分区 和 LIST分区 来说，HASH分区不用明确指定一个给定的列值或者列值集合，只需要基于将要进行HASH分区的列指定一个列值或者表达式，以及指定分区表将要被分割成的分区数量。

定义HASH分区的语句为 PARTITION BY HASH( expr) ，其中 expr 是一个整型列（类型为MySQL整型的列）的列名或者返回一个整数的表达式。

如果没有显式添加 PARTITIONS 子句声明需要分割的分区数量，那么默认只会创建一个分区。

create table t_p_hash(`name` varchar(50),age int)engine=innodbpartition by hash( age)partitions 4;

因为分区是按照整型列或者整数表达式进行的，这个值本身是离散的，如果对于连续的值进行HASH分区，则可以较好地将数据进行平均分布，例如自增长的主键。

但是需要注意的是，根据自增长主键作HASH分区并不能保证数据就一定会分布均匀。因为在数据插入失败进行了回滚时，当前的自增长主键值依然是增加了的，并且下次插入数据时的主键将会在当前失败了的主键基础上递增。

MySQL数据库还支持 LINEAR HASH分区 ，这可以看做 HASH分区 的一个变种。LINEAR HASH 分区的语法与 HASH分区 的语法大体一致，但是其内部使用的是一个更加复杂的算法来确定新行写入到分区中的位置。

相对于 HASH分区 来说，LINEAR HASH分区 在增加、删除、合并、拆分分区方面更加快捷，有利于处理含有大量数据的表，但是各个分区间数据的分布可能不大均衡。

create table t_p_linearhash(`name` varchar(50),age int)engine=innodbpartition by linear hash( age)partitions 4;

4、KEY分区

KEY分区 和 HASH分区 十分相似，不同之处在于 HASH分区 使用用户定义的函数进行分区，KEY分区 使用MySQL数据库提供的函数进行分区。例如 InnoDB存储引擎 就是使用内部的 哈希函数 来进行分区。

create table t_p_key(`name` varchar( 50),create_time datetime) engine=innodbpartition by key( create_time)partitions 4;

KEY分区 也有类似于HASH分区那样的的 LINEAR KEY分区 ，所带来的效果也是一致的。

5、COLUMNS分区

COLUMNS分区 可以看作是对 RANGE分区 和 LIST分区 的一种加强，可以直接使用非整型的数据进行分区；分区根据类型直接比较而得，不需要转化为整数。

使用的语法为 RANGE COLUMNS ( expr) 和 LIST COLUMNS ( expr) ，表达式 expr 不再必须为整型。值得一提的是， RANGE COLUMNS分区还可以对多个列的值进行分区。

例如直接根据 字符串类型 的列作分区

create table t_p_rangecolumns(`name` VARCHAR(50))engine=innodbpartition by list columns ( `name`)(partition r1 values in ( '张三', '王五'),partition r2 values in ( '李四', '赵六'));

创建分区表后，查看明细能够看到分区的表达式为字符串类型的字段 name

再来看下 RANGE COLUMNS 对多列进行分区

create table t_p_rangecolumns(`name` varchar(50),age int)engine=innodbpartition by range columns ( `name`, age)(partition r1 values less than ( '张三', 21),partition r2 values less than ( '李四', 23),partition r3 values less than ( MAXVALUE, MAXVALUE))

COLUMNS分区支持的数据类型有以下几种

• 所有的整型类型，如 INT、SMALLINT、TINYINT、BIGINT。浮点型的FLOAT和DECIMAL则不予支持

• 日期类型，仅包含 DATE 和 DATETIME

• 字符串类型，如 CHAR、VARCHAR、BINARY和VARBINARY。不支持BLOB和TEXT类型

  ​

常用策略

1、全表扫描，不要任何索引

可以使用简单的分区方式存放表，不要任何索引，根据分区的规则大致定位需要的数据位置。只要能够使用 WHERE条件 ，将需要的数据限制在少数分区中，则效率是很高的。

2、索引数据，并分离热点

如果数据有明显的”热点“，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会缓存在内存中。这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效的缓存。

需要注意的问题

1、分区数量并非越多越好

分区表本身有一定的限制，单张表最多是只能有1024个分区的。另一方面，在对分区表进行查询时服务器需要扫描所有分区定义的列表来找到正确的分区，类似这样的线性搜索的效率不高，所以随着分区数的增长，成本会越来越高。

2、查询不能根据表达式来过滤分区

MySQL只能在使用分区函数列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即便这个表达式就是分区函数也不行。

例如使用 year( create_time) 进行查询，实际上便扫描了所有的分区

explain partitionsselect * from t_p_test where year( create_time) = '2017';

3、NULL值会使分区过滤无效

MySQL数据库允许对NULL值做分区，但是MySQL数据库的分区总是视NULL值小于任何一个非NULL值，不同分区对NULL值的处理也各不相同。

RANGE分区 会将NULL值放入最左边边的分区中， LIST分区 下使用NULL值则必须显式地指出在哪个分区存放，而 HASH分区 和 KEY分区 会将含有NULL值的记录返回为0。

4、分区列必须是唯一索引的一个组成部分

不论创建何种类型的分区，如果建表时没有指定主键、唯一索引，那么可以指定任何一个列为分区列。但是当表中有主键或者唯一索引时，分区列就必须包含所有主键列和唯一索引列的一个组成部分。另外，分区表中无法使用外键约束。

例如创建表 t_p_test ，指定字段 id 为主键，但是定义分区时使用的是普通的整型列 age

create table t_p_test(id bigint primary key not null auto_increment,`name` varchar(50),age int)engine=innodbpartition by range( age)(partition p1 values less than ( 20),partition p2 values less than ( 30),partition p3 values less than ( MAXVALUE));

此时显然是无法成功进行分区的，执行语句后将会得到错误提示

Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function

如果将分区列改为主键列，则能正常进行分区

create table t_p_test(id bigint primary key not null auto_increment,`name` varchar(50),age int)engine=innodbpartition by range( id)(partition p1 values less than ( 20),partition p2 values less than ( 30),partition p3 values less than ( MAXVALUE));

需要注意的是，在包含唯一索引的表中进行分区，分区列只要求是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列。

例如在表 t_p_test 中存在一个由id、age组成的唯一索引（该表中没有主键），那么在进行分区时可以只使用 id 或者 age 作分区列

create table t_p_test(id bigint not null auto_increment,`name` varchar(50),age int,unique index iname( id, age))engine=innodbpartition by range( age)(partition p1 values less than ( 20),partition p2 values less than ( 30),partition p3 values less than ( MAXVALUE));

5、索引列和分区列不匹配会导致查询无法过滤分区

正如先前所说，MySQL数据库支持的分区为局部分区，索引是按照分区子表定义的，也就是说每个分区的索引相互独立。当索引列并非分区列时，对索引列进行扫描势必也就需要扫描全部分区。

这里创建分区表，在 name 列上建立索引，同时使用 age 列进行分区

create table t_p_test(name varchar(50) not null,age int,index i_uname( name))partition by range columns( age)(partition p1 values less than ( 18),partition p2 values less than (30),partition p3 values less than (50),partition p4 values less than (maxvalue));insert into t_p_test( name, age) values( '张三', 19), ( '李四', 29), ( '王五', 22), ( '赵六', 14), ( '田七', 71);

分析查询执行计划，能够看到使用 name 字段进行等值查询时使用了索引，但是却扫描了所有的分区

explain partitionsselect * from t_p_test where name = '张三'；

应该尽量避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件，这一点在多表关联时尤为重要。

6、维护分区的成本可能会很高

新增或者删除分区很快，但并不代表着对分区的维护操作总是很快。在对分区进重组时（更新分区操作），需要创建临时表，然后将数据复制到临时表中，最后再删除原分区；这个操作可能会带来很高的维护成本，特别是在有较为庞大的数据量的情况下。