MySQL分区表介绍及如何创建和查看分区表信息-CSDN博客

本文链接：https://blog.csdn.net/weixin_30340269/article/details/113556464

什么是分区表

为什么要用分区表

如何查看分区信息

分区的类型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语句来查看具体的分区信息和每一个分区中存储的数据量是多少。SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS, SUBPARTITION_NAME, SUBPARTITION_METHOD, SUBPARTITION_EXPRESSION FROM information_schema.PARTITIONS WHERE TABLE_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');

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