mysql code 关键字_MySQL中的分区表（上）

最新推荐文章于 2024-01-05 20:16:39 发布

六爷阿旦

最新推荐文章于 2024-01-05 20:16:39 发布

阅读量478

点赞数

文章标签： mysql code 关键字

本文链接：https://blog.csdn.net/weixin_32349577/article/details/113537947

版权

关注我「程序猿集锦」，获取更多分享。

什么是分区表
为什么要用分区表
如何查看分区信息
分区的类型
- range范围分区
- list列表分区
- columns多列分区
  - range columns范围多列分区
  - list columns列表多列分区
- hash哈希分区
  - linear hash线性哈希分区
- key按键分区
  - linear key线性按键分区
- 复合分区
  - range-hash复合分区
  - range-key复合分区
  - list-hash复合分区
  - list-key复合分区
  - 复合分区综合示例

什么是分区表

分区表示把一个表中的数据在物理上进行分开存储，对应到磁盘上是多个数据文件。但是这些数据文件在逻辑上又是于同一个表，它们共用一个数据表结构的元数据，但是在存放的时候，他们分别独立使用自己的数据文件。在某些情况下可以并行地读取各个分区中的数据，这样有利于提高数据存储和查询的效率。

为什么要用分区表

分区表，在一定的程度上可以提高MySQL数据库的性能。因为每一个分区对应着磁盘上面的一个单独的数据文件，这样可以提高磁盘的I/O，有助于提高查询和存储的时候的效率。但是这个是有一个前提：每一个分区都位于不同的存储设备上。如果是所有的分区都在一个磁盘上存放，那么对性能的提示是有限的，效率不会提高很多。如下图所示：

涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量，这个是分区表的最大优点。

如何查看分区信息

对于一个分区表，如何查看这个表的分区信息？我们可以使用show create table xxx;查看某一个表的分区信息。

那么一个分区表，如何查看每一个分区中，都存储了多少数据呢？在MySQL的information_schema下面的PARTITIONS表中存储着所有的分区表的详细元数据信息，我们可以使用下面的这个SQL语句来查看具体的分区信息和每一个分区中存储的数据量是多少。

SELECTPARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROMinformation_schema.PARTITIONS WHERETABLE_SCHEMA = SCHEMA() AND TABLE_NAME = 'xxxx';

分区的类型

根据分区类型的不同，分区表可以分为如下几种分区类型。

range分区：根据某一个列所存储值的范围对数据进行分区存储，这种分区经常使用在数据随着时间的增加在不断的增加，为了提高数据存储和处理的效率，按照时间段对数据进行分区存储，例如按照每一个月一个分区来存放数据，或者每一年一个分区来存放数据，这样就保证了同一个月内的数据，或同一年内的数据是处于同一个range范围分区中的。范围分区分区字段：integer、数值表达式、日期列，日期函数表达式(如year(),to_days(),to_seconds(),unix_timestamp())。
list分区：根据某一个列去重后的值的列表对数据进行分区存储，这样的分区能够保证在分区列上的值相同的数据行会被分到同一个分区中，便于一次性的把在分区列上有相同值的数据全部取出。常见的分区字段有类别、区号、性别、国家等字段。
columns分区：多列分区是指，在分区的时候，不是根据表中的某一个列进行分区，而是使用2个或2个以上的列作为分区键。前面的range范围分区、list列表分区在选择分区键的时候，只能选择表中的某一个列作为分区键，而columns多列分区可以选择表中的多个列作为分区键。不过这种分区的方式使用的比较少。
hash分区：根据指定的分区数，根据分区字段的哈希值来将数据分散到不同的分区中。它会拿每行数据中分区字段的值，与分区的数目取模，得到的余数是多少这行数据就落在对应的分区编号中。这种分区通常是为了把数据均匀的分布在各个分区中，避免在各个分区中所存储的数据量不同，这样就没有数据倾斜的现象发生。常见的分区字段有年龄、编号等。它只能基于interger类型的列作为分区键。
linear hash分区：线性哈希分区，这个分区方式和普通的hash分区差不多，只不过是在计算每一行数据到底该划分到哪个分区的时候使用的算法和普通的hash算法有些区别。和普通的hash分区相比，它的优点是可以更快地进行添加、删除、合并、拆分分区，在数据量特别大的时候这种效果尤其明显，它的缺点是数据在各个分区中的分布可能不均匀的，有数据倾斜的可能。
key分区：按键分区和hash分区类似。但是它和hash分区不同的是，它支持使用除了blob、text类型之外的所有类型的字段作为分区键，不像hash分区那样，仅仅支持使用integer类型的字段作为分区键。与此同时，key分区不允许使用自定义的表达式进行分区，它只能使用MySQL系统提供的hash函数作为表达式。在使用key分区的时候，如果没有指定分区键，则会使用表的主键作为分区键，如果没有主键，则使用唯一索引列作为分区键，这个唯一索引列不包含null类型的值。
linear key分区：和key按键分区类似，只不过在底层实现觉得数据应该存储到哪个分区的时候使用的算法不一样。它和key按键分区的区别与linear hash分区 vs hash分区的区别一样。在对分区的添加、修改、删除、合并等操作上，linear key分区的效率要高于普通的key按键分区。
复合分区：这种分区是基于前面的几种(range、list、hash、key)分区进行组合而产生的一种分区。就是在某一个分区中的数据，再根据另外一个分区规则进行再次分区，而再次分区所产生的分区称之为子分区。

下面我们针对每一种类型的分区，进行详细的示例说明。

range范围分区

下面我们创建一个range范围分区表，来真实地感受一下MySQL中的range分区表。

创建range范围分区表的示例语句如下，其中create_date创建日期字段是我们进行范围分区的字段。其中注意less than关键字后面跟着的边界值，它是一个右侧开区间，当前分区不包含less than后面的边界值。

drop table if exists range_partition_table;create table range_partition_table(id int auto_increment,  code varchar(16),  create_date date,  primary key(id, create_date)) partition by range columns(create_date) (partition p1 values less than('2020-02-01'), /*p1分区不包含2020-02-01这一天的数据*/  partition p2 values less than('2020-03-01'),  partition p3 values less than('2020-04-01'),  partition p4 values less than('2020-05-01'),  partition px values less than maxvalue);

注意：范围分区的时候，不支持使用date或datetime以外的日期或时间类型的列作为分区列。

插入测试数据：

insert into range_partition_table(id, code, create_date) values (null, 'A', '2020-01-04');insert into range_partition_table(id, code, create_date) values (null, 'B', '2020-01-31');insert into range_partition_table(id, code, create_date) values (null, 'C', '2020-02-01');insert into range_partition_table(id, code, create_date) values (null, 'D', '2020-02-29');insert into range_partition_table(id, code, create_date) values (null, 'E', '2020-03-01');insert into range_partition_table(id, code, create_date) values (null, 'F', '2020-03-31');insert into range_partition_table(id, code, create_date) values (null, 'G', '2020-04-01');insert into range_partition_table(id, code, create_date) values (null, 'H', '2020-04-30');insert into range_partition_table(id, code, create_date) values (null, 'I', '2020-05-01');insert into range_partition_table(id, code, create_date) values (null, 'J', '2020-05-31');insert into range_partition_table(id, code, create_date) values (null, 'K', '2020-06-01');insert into range_partition_table(id, code, create_date) values (null, 'L', '2020-06-30');insert into range_partition_table(id, code, create_date) values (null, 'M', '2020-07-01');

查看插入测试数据后的分区表的详细信息：

分区表在MySQL数据库服务器上，每一个分区对应着一个数据文件，他们共用一个.frm表结构文件，然后每一个分区是一个单独的数据文件。如下图所示：

list列表分区

列表分区比较适合哪种已经确定某个字段存储值的取值范围的情况下，根据这个字段来分区存储数据。下面我们举例说明一下这种分区的使用方式。创建list列表分区表的结构如下：

drop table if exists list_partition_table;create table list_partition_table(id int auto_increment,  type int,  create_date date,  primary key(id, type)) partition by list columns(type) (partition p1 values in (1, 2),  partition p2 values in (3, 4),  partition p3 values in (5, 6));

创建好表之后，插入下面的测试数据：

insert into list_partition_table(id, type, create_date) values(null,1,now());insert into list_partition_table(id, type, create_date) values(null,2,now());insert into list_partition_table(id, type, create_date) values(null,3,now());insert into list_partition_table(id, type, create_date) values(null,4,now());insert into list_partition_table(id, type, create_date) values(null,5,now());insert into list_partition_table(id, type, create_date) values(null,6,now());

使用前面我们提到的查看分区表详细分区信息的SQL语句，查询该分区表的详细信息如下：

columns多列分区

多列分区在某种意义上属于范围分区和列表分区的一种变形，它解决了range范围分区和list列表分区中只能指定一个列作为分区键的限制，这种分区方式可以一次性的选择多个列放在一起作为一个分区键来对数据进行分区。它是基于range和list两种分区之上来创建的，所以可以分为range columns范围多列分区和list columns列表多列分区两种。

在使用多列分区的时候，设置的分区列和设置的分区列的值要顺序和数目都对应上才可以。比如你选择了3个列作为分区键，那么你设置各个分区的边界值的时候，也要指定3个列的值，并且字段类型的顺序也需要对应起来。

针对这两种分区分别演示一下。

range columns范围多列分区

创建范围多列分区的示例如下：

drop table if exists range_columns_partiton_tab;create table range_columns_partiton_tab(id int auto_increment,  a int,  b int,  c varchar(16),  d int,  primary key(id,a,d,c)) partition by range columns(a,d,c) (partition p1 values less than (10,15,'d'),partition p2 values less than (20,20,'g'),partition p3 values less than (35,45,'k'),partition p4 values less than (40,60,'p'),partition px values less than (maxvalue,maxvalue,maxvalue));

创建好分区之后，插入如下测试数据

insert into range_columns_partiton_tab(id,a,b,c,d) values(null,9,1,'b',14);/*p1的数据*/insert into range_columns_partiton_tab(id,a,b,c,d) values(null,10,1,'d',15);/*p2的数据*/insert into range_columns_partiton_tab(id,a,b,c,d) values(null,22,1,'h',22);/*p3的数据*/insert into range_columns_partiton_tab(id,a,b,c,d) values(null,37,1,'m',50);/*p4的数据*/insert into range_columns_partiton_tab(id,a,b,c,d) values(null,40,1,'p',60);/*px的数据*/

插入数据后，使用前面我们提到的查询分区表详细信息的SQL语句，可以得到如下的结果：

从上面的例子可以看出，分区键排列的顺序和表中字段定义的顺序可以不一致，上面的例子中表字段的顺序是a,c,d，但是我们在定义分区字段的时候使用的顺序是a,d,c。但是我们在给分区字段进行定义边界值的时候，需要按照a,d,c的顺序来定义边界值。

list columns列表多列分区

列表多列分区和list分区差不多，只是此时选择分区键的时候，不仅仅可以支持int类型的列作为分区键，还可以选择varchar、date、timestamp类型的列作为分区字段。所以，列表多列中的多列并不是像范围多列分区中的多列那样指多个列一起作为分区字段，而是指支持多种字段类型来作为分区字段。

下面给出一个使用字符串类型的字段作为分区字段的分区表：

drop table if exists list_columns_partition_tab;create table list_columns_partition_tab(id int auto_increment,  area_code varchar(16),  create_date date,  primary key(id, area_code)) partition by list columns(area_code) (partition p1 values in ('010','020'),  partition p2 values in ('021','022'),  partition p3 values in ('023','024'));

创建好表之后，插入下面的测试数据：

insert into list_columns_partition_tab(id, area_code, create_date) values(null,'010',now());insert into list_columns_partition_tab(id, area_code, create_date) values(null,'020',now());insert into list_columns_partition_tab(id, area_code, create_date) values(null,'021',now());insert into list_columns_partition_tab(id, area_code, create_date) values(null,'022',now());insert into list_columns_partition_tab(id, area_code, create_date) values(null,'023',now());insert into list_columns_partition_tab(id, area_code, create_date) values(null,'024',now());

使用前面我们提到的inoformation.PARTITIONS表去查看list列表分区表的详细分区信息如下所示：

当我们向列表分区表中插入一个不存在的列表值的时候，会出现如下的错误，所以，我们要保证我们的分区已经全部以后分区键可能出现的值，否则插入数据的时候会失败，如下所示：

mysql> insert into list_columns_partition_tab(id, area_code, create_date) values(null,'999',now());ERROR 1526 (HY000): Table has no partition for value from column_listmysql>

hash哈希分区

哈希分区是根据分区字段的值，进行hash然后与分区的数目取余，得到的余数就是这个数据所在分区的编号。下面举例说明。

drop table if exists hash_partition_table;create table hash_partition_table(id int auto_increment,  store_code int,  create_date date,  primary key(id, store_code)) partition by hash(store_code)partitions 4;

对hash分区表，插入测试数据如下所示：

insert into hash_partition_table(id, store_code, create_date) values(null, 10, now());insert into hash_partition_table(id, store_code, create_date) values(null, 11, now());insert into hash_partition_table(id, store_code, create_date) values(null, 12, now());insert into hash_partition_table(id, store_code, create_date) values(null, 13, now());

查询hash分区后的详细信息如下：

注意：hash分区在选择分区键的时候，只能选择integer类型的列才可以作为分区键，如果我们使用一个非integer类型的列作为hash分区键的时候，如有如下错误：

mysql> create table hash_partition_table(    -> id int auto_increment,    ->   store_code varchar(16),    ->   create_date date,    ->   primary key(id, store_code)    -> ) partition by hash(store_code)    -> partitions 4;ERROR 1659 (HY000): Field 'store_code' is of a not allowed type for this type of partitioningmysql>

当然我们也可以选择一个非integer类型的字段作为hash分区的分区键，但是需要对这个字段进行一个函数的转换，将这个非integer类型的字段转换为integer类型的字段后再作为hash分区的分区键，如下所示，获取create_date的年作为一个integer类型值，然后基于这个值做hash分区。这就是在hash分区中使用了自定义表达式的方式来将一个可以转回为integer类型的字段转换为interger类型之后再做为hash分区的分区键。

drop table if exists hash_partition_table;create table hash_partition_table(id int auto_increment,  store_code int,  create_date date,  primary key(id, create_date)) partition by hash(year(create_date))/*这里使用了year(xxx)自定义表达式，来满足hash分区的分区键必须为interger类型的条件。*/partitions 4;

linear hash线性哈希分区

线性哈希分区在创建是使用的时候，和普通的哈希分区相比，没有什么区别，唯一的区别就是它在创建的时候比普通的哈希多了一个关键字linear。它的创建如下所示：

drop table if exists linear_hash_partition_table;create table linear_hash_partition_table(id int auto_increment,  store_code int,  create_date date,  primary key(id, store_code)) partition by linear hash(store_code)partitions 4;

插入如下测试数据：

insert into linear_hash_partition_table(id, store_code, create_date) values(null, 10, now());insert into linear_hash_partition_table(id, store_code, create_date) values(null, 11, now());insert into linear_hash_partition_table(id, store_code, create_date) values(null, 12, now());insert into linear_hash_partition_table(id, store_code, create_date) values(null, 13, now());

查询详细的分区信息如下，从中可以看出里面的分区方式是LINEAR HASH，而不是普通的HASH。

key按键分区

key分区和hash分区类似，但是它不支持自定义分区的表达式，只能使用MySQL自带的hash函数，它不仅仅支持interger类型的列作为分区键，支持除了blob、text之外的所有类型的字段作为分区键。在创建key按键分区表的时候，可以显示的指定分区键，也可以显示的指定分区键。下面看一个示例：

drop table if exists key_partition_table;create table key_partition_table(id int auto_increment,  code varchar(16),  create_date date,  primary key(id)) partition by key()/*这里没有指定使用哪个列作为分区键，则会使用上面表中的主键id作为分区键*/partitions 4;/*下面的SQL和上面的SQL是等价的，都是使用id作为key分区的分区键，只不过下面的SQL显示的指定的分区键的列*/drop table if exists key_partition_table;create table key_partition_table(id int auto_increment,  code varchar(16),  create_date date,  primary key(id)) partition by key(id)/*这里指定使用表中的主键id作为分区键*/partitions 4;

上述两种方式在创建完成分区表之后，查看分区表详细信息的时候，有一点区别如下，如果不指定分区键，则在相信分区信息中是看不到分区的表表达式的，但是这并不影响分区的功能，所以说不要以为这样的key分区表示有问题的。

如下的建表语句就会出现错误，疑问它没有主键，也没有唯一索引列，所以在按键分区的时候，它不知道该使用哪个列作为分区键。

drop table if exists key_partition_table;create table key_partition_table(id int,  code varchar(16),  create_date date) partition by key()/*没有主键、也没有非空的唯一索引，所以创建按键分区失败*/partitions 4;/*错误如下*/ERROR 1488 (HY000): Field in list of fields for partition function not found in table

如下的SQL是可以成功了，因为里面虽然没有主键，但是有一个唯一索引列，并且唯一索引指定了not null非空约束。

drop table if exists key_partition_table;create table key_partition_table(id int,  code varchar(16) not null,/*这个唯一索引增加了not null非空的约束，所以才可以使用这个唯一索引列作为分区键*/  create_date date,  unique key(code)) partition by key()/*这里没有指定使用哪个列作为分区键，则会使用上面表中的唯一索引列code作为分区键*/partitions 4;

如果我们使用下面的SQL语句创建表就会失败，因为虽然有唯一索引，但是这个唯一索引，没有增加not null非空的约束，按键分区可以使用唯一约束作为分区键，但是这个唯一索引不能包含null值。注意这里有一个隐藏的知识点：主键和唯一键(唯一索引)的区别是什么？主键中不能有null空值，而唯一键(唯一索引)中是可以包含null空值的。而此时在使用key按键分区的时候，如果不指定主键，那么使用表中非空的唯一索引列，而这个列不就是表中的一个主键列吗？只是没有显示的使用primary key关键字指定主键列是谁。

drop table if exists key_partition_table;create table key_partition_table(id int,  code varchar(16),/*唯一约索引，没有增加not null约束，所以创建按键分区失败*/  create_date date,  unique key(code)) partition by key()partitions 4;/*错误如下*/ERROR 1488 (HY000): Field in list of fields for partition function not found in table

当然，我们也是可以在创建key按键分区的时候，指定分区键的列是哪一个，如下所示。但是要求这个列必须是主键中的某一个列，或者是一个非空的唯一索引列。如果满足这两个条件，则有如下错误：

drop table if exists key_partition_table;create table key_partition_table(id int,  code varchar(16),  create_date date,  unique key(code)/*指定code为唯一索引列*/) partition by key(create_date)/*指定了使用create_date作为按键分区的分区键，但是这个列不是主键，也不是非空的唯一索引列*/partitions 4;/*错误信息如下：*/ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

drop table if exists key_partition_table;create table key_partition_table(id int,  code varchar(16),  create_date date,  primary key(id)/*指定id为主键列*/) partition by key(create_date)/*指定了使用create_date作为按键分区的分区键，但是这个列不是主键，也不是非空的唯一索引列*/partitions 4;/*错误信息如下：*/ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

如下才是指定key按键分区键的正确方式：

drop table if exists key_partition_table;create table key_partition_table(id int,  code varchar(16),  create_date date,  primary key(id)/*指定id为主键列*/) partition by key(id)/*使用主键id作为按键分区的分区键*/partitions 4;

drop table if exists key_partition_table;create table key_partition_table(id int,  code varchar(16),  create_date date,  unique key(code)/*指定code为唯一索引列*/) partition by key(code)/*使用唯一索引列code作为按键分区的分区键*/partitions 4;

向key按键分区表中，插入测试数据如下：

insert into key_partition_table(id, code, create_date) values(1,'A',now());insert into key_partition_table(id, code, create_date) values(2,'B',now());insert into key_partition_table(id, code, create_date) values(3,'C',now());insert into key_partition_table(id, code, create_date) values(4,'D',now());

查看详细的分区信息和数据如下：

key分区的时候，不仅仅只支持一个列作为分区键，还可以选择多个列作为分区键，如下所示，也是可以的。

drop table if exists key_partition_table;create table key_partition_table(id int,  code varchar(16),  create_date date,  unique key(id,code)/*指定id, code为唯一索引列*/) partition by key(id,code)/*使用唯一索引列id, code作为按键分区的分区键*/partitions 4;

linear key线性按键分区

linear key分区和linear hash分区类似。它是key按键分区的一种变形。在创建语法上只不过增加了linear关键字而已。它的优点是合并、删除、修改、拆分分区的时候比普通的key按键分区效率要高。就像linear hash分区和普通的hash分区的区别一样。

具体创建linear key分区的示例如下：

drop table if exists linear_key_partition_table;create table linear_key_partition_table(id int,  code varchar(16),  create_date date,  unique key(code)/*指定code为唯一索引列*/) partition by linear key(code)/*使用唯一索引列code作为按键分区的分区键*/partitions 4;

复合分区

复合分区又称为子分区，虽然是前面的几种分区组合之后产生的，但是它们的组合并不是任意组合都可以的，而是由规律个限制的。目前的复合分区仅仅支持在range范围分区和list列表分区下面创建子分区，并且这个子分区的类型也只能是hash分区或者key分区。详细的组合方式下面分别展开说明。

range范围分区下面支持的子分区有如下两种：hash哈希分区key按键分区
list列表分区下面支持的子分区有如下两种：hash哈希分区Key按键分区

range-hash复合分区

range-hash复合分区是指先根据某个列进行范围分区，然后再基于每一个分区进行hash分区。示例如下：

drop table if exists range_hash_partition_tab;create table range_hash_partition_tab(id int auto_increment,  type int,  create_date date,  col1 varchar(16),  col2 int,  primary key(id,create_date,type))partition by range columns(create_date) /*根据create_date拆分为4个范围分区*/subpartition by hash(type) subpartitions 2 /*每个分区再根据type拆分为2个子分区*/(  partition p1 values less than ('2020-02-01'),  partition p2 values less than ('2020-03-01'),  partition p3 values less than ('2020-04-01'),  partition px values less than maxvalue);

创建完成分区表之后，在MySQL服务器上面的数据库目录下面，存储的文件如下所示，可以从下面的图中看出，这个分区表有4个分区，分别为p1、p2、p3、px四个分区，然后每一个分区下面又有两个子分区，分别为sp0、sp1。所以这个表一共有4*2=8个分区。

插入测试数据

insert into range_hash_partition_tab(id, type, create_date, col1, col2) values(null, 4, '2020-01-11','p1分区中sp0子分区的数据',1);insert into range_hash_partition_tab(id, type, create_date, col1, col2) values(null, 7, '2020-01-14','p1分区中sp1子分区的数据',1);insert into range_hash_partition_tab(id, type, create_date, col1, col2) values(null, 8, '2020-02-22','p2分区中sp0子分区的数据',1);insert into range_hash_partition_tab(id, type, create_date, col1, col2) values(null, 5, '2020-02-24','p2分区中sp1子分区的数据',1);insert into range_hash_partition_tab(id, type, create_date, col1, col2) values(null, 2, '2020-03-07','p3分区中sp0子分区的数据',1);insert into range_hash_partition_tab(id, type, create_date, col1, col2) values(null, 3, '2020-03-13','p3分区中sp1子分区的数据',1);insert into range_hash_partition_tab(id, type, create_date, col1, col2) values(null, 6, '2021-01-22','px分区中sp0子分区的数据',1);insert into range_hash_partition_tab(id, type, create_date, col1, col2) values(null, 1, '2021-01-11','px分区中sp1子分区的数据',1);

分区的详细信息如下：

range-key复合分区

范围按键复合分区的示例如下，需要注意的时候，在key按键分区作为子分区的时候，需要指定key分区的分区键，不能向单独创建一个按键分区表那样，可以不指定分区键，在key分区作为子分区的时候，需要显示的声明key分区的分区键。

drop table if exists range_key_partition_tab;create table range_key_partition_tab(id int auto_increment,  type int,  create_date date,  col1 varchar(16),  col2 int,  primary key(id,create_date,type))partition by range columns(create_date) /*根据create_date拆分为4个范围分区*/subpartition by key(type) subpartitions 2 /*每个分区再根据type拆分为2个子分区*/(  partition p1 values less than ('2020-02-01'),  partition p2 values less than ('2020-03-01'),  partition p3 values less than ('2020-04-01'),  partition px values less than maxvalue);

插入初始化数据如下：

insert into range_key_partition_tab(id, type, create_date, col1, col2) values(null, 4, '2020-01-11','p1分区',1);insert into range_key_partition_tab(id, type, create_date, col1, col2) values(null, 7, '2020-01-14','p1分区',1);insert into range_key_partition_tab(id, type, create_date, col1, col2) values(null, 8, '2020-02-22','p2分区',1);insert into range_key_partition_tab(id, type, create_date, col1, col2) values(null, 5, '2020-02-24','p2分区',1);insert into range_key_partition_tab(id, type, create_date, col1, col2) values(null, 2, '2020-03-07','p3分区',1);insert into range_key_partition_tab(id, type, create_date, col1, col2) values(null, 3, '2020-03-13','p3分区',1);insert into range_key_partition_tab(id, type, create_date, col1, col2) values(null, 6, '2021-01-22','px分区',1);insert into range_key_partition_tab(id, type, create_date, col1, col2) values(null, 1, '2021-01-11','px分区',1);

分区表的详细信息如下：

list-hash复合分区

list-hash复合分区是先根据list分区，然后在每一个list分区中，再按照hash来分区。示例如下：

drop table if exists list_hash_partition_tab;create table list_hash_partition_tab(id int auto_increment,  type int,  create_date date,  col1 varchar(16),  col2 int,  primary key(id,create_date,type))partition by list columns(type) /*根据type的值拆分为2个列表分区*/subpartition by hash(year(create_date)) /*根据create_date的year()表达式，进行hash分区*/subpartitions 2 /*每个分区再根据year(create_date)拆分为2个子分区*/(  partition p1 values in (1, 3, 5, 7, 9),  partition p2 values in (2, 4, 6, 8, 10));

创建好复合分区后的表，在物理磁盘上存储如下所示。从中可以看出共有4个分区。先安装type进行列表分区，分成了两个分区，分别为p1、p2两个分区。然后再每一个分区上，又根据create_date进行了hash分区，分成了sp0、sp1两个子分区。

插入如下初始化数据，在区分是插入到p1还是p2的时候，是根据type的值来区分的，奇数值全部插入到p1分区中，偶数值全部插入到p2分区中。然后在p1和p2的分区中，再根据year(create_date)的值与我们的子分区的数目2进行取余数，再决定到底该分区到sp0还是sp1中。

insert into list_hash_partition_tab(id, type, create_date, col1, col2) values(null, 1, '2020-01-11','p1分区',1);insert into list_hash_partition_tab(id, type, create_date, col1, col2) values(null, 3, '2021-01-14','p1分区',1);insert into list_hash_partition_tab(id, type, create_date, col1, col2) values(null, 5, '2020-02-22','p1分区',1);insert into list_hash_partition_tab(id, type, create_date, col1, col2) values(null, 7, '2021-02-24','p1分区',1);insert into list_hash_partition_tab(id, type, create_date, col1, col2) values(null, 9, '2020-03-07','p1分区',1);insert into list_hash_partition_tab(id, type, create_date, col1, col2) values(null, 2, '2021-03-13','p2分区',1);insert into list_hash_partition_tab(id, type, create_date, col1, col2) values(null, 4, '2020-01-22','p2分区',1);insert into list_hash_partition_tab(id, type, create_date, col1, col2) values(null, 6, '2021-01-11','p2分区',1);

详细的分区信息如下图所示：

list-key复合分区

list-key列表按键复合分区是先进行list列表分区，然后再每一个分区的基础上，再按照key进行分区。示例如下：

drop table if exists list_key_partition_tab;create table list_key_partition_tab(id int auto_increment,  type int,  create_date date,  col1 varchar(16),  col2 int,  primary key(id,create_date,type))partition by list columns(type) /*根据type的值拆分为2个列表分区*/subpartition by key(create_date) /*根据create_date进行按键分区*/subpartitions 2 /*每个分区再根据create_date拆分为2个按键子分区*/(  partition p1 values in (1, 3, 5, 7, 9),  partition p2 values in (2, 4, 6, 8, 10));

创建好表之后，可以在磁盘上看到如下数据文件：

插入如下测试数据：

insert into list_key_partition_tab(id, type, create_date, col1, col2) values(null, 1, '2020-01-11','p1分区',1);insert into list_key_partition_tab(id, type, create_date, col1, col2) values(null, 3, '2021-01-14','p1分区',1);insert into list_key_partition_tab(id, type, create_date, col1, col2) values(null, 5, '2020-02-22','p1分区',1);insert into list_key_partition_tab(id, type, create_date, col1, col2) values(null, 7, '2021-02-24','p1分区',1);insert into list_key_partition_tab(id, type, create_date, col1, col2) values(null, 9, '2020-03-07','p1分区',1);insert into list_key_partition_tab(id, type, create_date, col1, col2) values(null, 2, '2021-03-13','p2分区',1);insert into list_key_partition_tab(id, type, create_date, col1, col2) values(null, 4, '2020-01-22','p2分区',1);insert into list_key_partition_tab(id, type, create_date, col1, col2) values(null, 6, '2021-01-11','p2分区',1);

详细的分区信息如下所示：

复合分区综合示例

复合分区在设置子分区的时候，同时可以设置子分区的名称，并可以指定每一个分区的存储目录。看下面的这个示例：

drop table if exists list_key_partition_tab;create table list_key_partition_tab(id int auto_increment,  type int,  create_date date,  col1 varchar(16),  col2 int,  primary key(id,create_date,type))partition by list columns(type) /*根据type的值拆分为2个列表分区*/subpartition by key(create_date) /*根据create_date进行按键分区*/(  partition p1 values in (1, 3, 5, 7, 9) (  subpartition s1,/*指定子分区的名称*/    subpartition s2 /*指定子分区的名称*/  ),  partition p2 values in (2, 4, 6, 8, 10) (  subpartition s3,/*指定子分区的名称*/    subpartition s4 /*指定子分区的名称*/  ));

上面的复合分区表创建完成之后，在磁盘上看下分区的效果，指定了子分区的名称，从下图中可以看出，子分区的名称确实如我们设置的规则生成的，分别为s1、s2、s3、s4四个子分区。当我们不设置子分区的名称的时候，子分区的名称是sp0、sp1这样的规则。

注意：上面的例子中，我们指定子分区的名称，需要为每一个分区都指定子分区的名称，假如我们有4个分区，不能只为其中的某一个或多个分区指定其分区内的子分区名称，需要为每一个分区都指定子分区的名称。要么全部指定，要么全都不指定。

下面我们再看另外一个例子，指定每一个分区的磁盘目录，这样对于分区表提高磁盘的I/O尤为重要。

drop table if exists list_key_partition_tab;create table list_key_partition_tab(id int auto_increment,  type int,  create_date date,  col1 varchar(16),  col2 int,  primary key(id,create_date,type))partition by list columns(type) /*根据type的值拆分为2个列表分区*/subpartition by key(create_date) /*根据create_date进行按键分区*/(  partition p1 values in (1, 3, 5, 7, 9) (  subpartition s1    data directory = '/disk1/data',    subpartition s2    data directory = '/disk2/data'  ),  partition p2 values in (2, 4, 6, 8, 10) (  subpartition s3    data directory = '/disk3/data',    subpartition s4    data directory = '/disk4/data'  ));

上面的语句执行成功的前提是，指定的挂载的磁盘目录需要对mysql这个操作系统用户有对应的权限。如下，磁盘的目录是有owner的权限的，即执行了：chwon -R mysql:mysql /disk*/命令，否则在创建分区表的时候，可能会有ERROR 1030 (HY000): Got error 168 from storage engine的错误。

指定每一个分区存储目录的分区表，在磁盘上分布如下，只有数据文件在指定的磁盘目录上，表结构元数据信息文件还是在数据库feng的数据目录下面，但是在数据库目录下面会对应的.isl结尾的数据文件，.isl文件中只是存储的数据文件的真实路径是什么。

不仅仅是复合分区支持指定磁盘目录，非复合分区也支持指定磁盘目录，如下所示：

drop table if exists list_partition_tab;create table list_partition_tab(id int auto_increment,  type int,  create_date date,  col1 varchar(16),  col2 int,  primary key(id,create_date,type))partition by list columns(type) /*根据type的值拆分为2个列表分区*/(  partition p1 values in (1, 3, 5, 7, 9)   data directory = '/disk1/data',  partition p2 values in (2, 4, 6, 8, 10)    data directory = '/disk2/data');

创建完成之后，分区表在磁盘上的目录结果如下所示：

由于篇幅长度的限制，关于分区表的管理相关的内容，请看下篇文章。