登录
首页 >  数据库 >  MySQL

MySQL 分区表

来源：SegmentFault

时间：2023-01-22 14:58:19 389浏览 收藏

亲爱的编程学习爱好者，如果你点开了这篇文章，说明你对《MySQL 分区表》很感兴趣。本篇文章就来给大家详细解析一下，主要介绍一下MySQL、分区，希望所有认真读完的童鞋们，都有实质性的提高。

  MySQL分区就是将一个表分解为多个更小的表。从逻辑上讲，只有一个表或一个索引，但在物理上这个表或者索引可能由多个物理分区组成。每个分区在物理上都是独立的。MySQL数据库分区类型：

  1. Range分区：行数据基于属于一个给定连续区间的列值放入分区。
  2. List分区：和Range分区类似，只是List分区面向的是离散的值。
  3. Hash分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。
  4. Key分区：根据MySQL数据库提供的哈希函数来进行分区。
  5. Columns分区：Range分区和List分区的扩展，支持非整型的分区条件。
  6. 子分区（复合分区）：MySQL数据库允许在Range和List的分区上再进行Hash或者Key的子分区。

create table t_range(
id int) engine=innodb
partition by range(id) (
partition p0 values less than (10),
partition p1 values less than (20),
partition p2 values less than maxvalue);

  上面例子表示创建了一个id列的区间分区表，当id=20时，数据会插入p2分区。

maxvalue
表示正无穷大。分区表会在磁盘上为每个分区创建一个文件，如下：

# ls -lh t_range*
-rw-r----- 1 mysql mysql 8.4K 8月  17 19:25 t_range.frm
-rw-r----- 1 mysql mysql  96K 8月  17 19:25 t_range#P#p0.ibd
-rw-r----- 1 mysql mysql  96K 8月  17 19:25 t_range#P#p1.ibd
-rw-r----- 1 mysql mysql  96K 8月  17 19:25 t_range#P#p2.ibd

插入几条数据，查看数据在各分区的分布情况：

mysql> insert into t_range values (1), (2), (10), (15), (16), (20), (22), (24), (25);
Query OK, 9 rows affected (0.10 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.partitions where table_schema=database() and table_name='t_range'\G
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: mytest
                   TABLE_NAME: t_range
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 10
                   TABLE_ROWS: 2
               AVG_ROW_LENGTH: 8192
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-08-17 19:25:02
                  UPDATE_TIME: 2020-08-17 19:40:16
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: mytest
                   TABLE_NAME: t_range
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 20
                   TABLE_ROWS: 3
               AVG_ROW_LENGTH: 5461
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-08-17 19:25:02
                  UPDATE_TIME: 2020-08-17 19:40:16
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 3. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: mytest
                   TABLE_NAME: t_range
               PARTITION_NAME: p2
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: MAXVALUE
                   TABLE_ROWS: 4
               AVG_ROW_LENGTH: 4096
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2020-08-17 19:25:02
                  UPDATE_TIME: 2020-08-17 19:40:16
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
3 rows in set (0.00 sec)

PARTITION_METHOD表示分区类型，TABLE_ROWS列表示分区中记录的数量，可以看到p0分区有2条记录，p1分区有3条记录，p2分区有4条记录。

2. List分区

List分区和Range分区相似，只是分区列是离散的。用法如下：

create table t_list (
a int,
b int) engine=innodb
partition by list(b)(
partition p0 values in (1,3,5,7,9),
partition p1 values in (0,2,4,6,8));

创建List分区表是，同一个数值不能出现两次，否则会报错

ERROR 1495 (HY000): Multiple definition of same constant in list partitioning
。

3. Hash分区

  Hash分区的目的是将数据均匀地分布到预先定义地各个分区中，保证各分区地数据数量大致都是一样的。在Range和List分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区，而在Hash分区中，MySQL自动完成这些工作，我们所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
Hash分区表用法如下：以YEAR(b)做hash，分区数据量是4。

create table t_hash (
a int,
b datetime) engine=innodb
partition by hash (YEAR(b))
partitions 4;

  MySQL还支持一种称为Linear Hash的分区，它的用法跟Hash分区一样，只是将关键字Hash改为Linear Hash。Linear Hash分区使用了一个更加复杂的算法来做hash函数。Linear Hash分区的优点在于增加、删除、合并、拆分分区效率更高，有利于处理数据量大的表；缺点是数据分布不大均衡。

4. Key分区

  Key分区和Hash分区相似，不同在于，Hash分区使用用户指定的函数进行分区，Key分区无需明确指定函数，会自动使用MySQL数据库提供的函数进行分区。Key分区也可以使用关键字Linear。用法如下：

create table t_key (
a int,
b datetime) engine=innodb
partition by key (b)
partitions 4;

5. Columns分区

  Range、List、Hash、Key这四种分区中，分区的条件必须是整型，如果不是整型，需要通过Year()、Month()等函数将其转化为整型。Columns分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。Range Columns分区还可以对多个列的值进行分区。Columns分区支持：所有整型、日期类型（Date、Datetime）、字符串类型（Blob、Text不支持）。用法如下：

create table t_columus_range(
a int,
b datetime) engine=innodb
partition by range columns (b) (
partition p0 values less than ('2019-01-01'),
partition p1 values less than ('2020-01-01'));

# 使用多个列进行分区
create table t_range_columns_mult (
a int,
b int,
c char(3),
d int) engine=innodb
partition by range columns (a,b,c)(
partition p0 values less than (5, 10, 'sdd'),
partition p1 values less than (10, 20, 'xxx'),
partition p2 values less than (maxvalue, maxvalue, maxvalue));

6. 子分区

  子分区是在分区的基础上再进行分区，也称复合分区。MySQL允许在Range和List的分区上再进行Hash或Key的子分区。用法如下：

# 一下只指定了每个分区的子分区数量为2，没有指定每个分区的名称。
create table t_sub (
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);

# 看下物理上的分布
ls -lh t_sub*
-rw-r----- 1 mysql mysql 8.4K 8月  18 11:46 t_sub.frm
-rw-r----- 1 mysql mysql  96K 8月  18 11:46 t_sub#P#p0#SP#p0sp0.ibd
-rw-r----- 1 mysql mysql  96K 8月  18 11:46 t_sub#P#p0#SP#p0sp1.ibd
-rw-r----- 1 mysql mysql  96K 8月  18 11:46 t_sub#P#p1#SP#p1sp0.ibd
-rw-r----- 1 mysql mysql  96K 8月  18 11:46 t_sub#P#p1#SP#p1sp1.ibd
-rw-r----- 1 mysql mysql  96K 8月  18 11:46 t_sub#P#p2#SP#p2sp0.ibd
-rw-r----- 1 mysql mysql  96K 8月  18 11:46 t_sub#P#p2#SP#p2sp1.ibd

# 也可以显示指定每个子分区的名称
create table t_sub_named (
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));

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

  • 每个分区下的子分区的数量必须相同。
  • 如果在分区表的任何分区上使用
    subpartition
    来明确定义任何子分区，那么就必须定义所有的子分区。
  • 可以使用
    subpartitions
    只指定子分区的数量，MySQL会自动为每个子分区分配名称。如果使用了
    subpartition
    ，则每个subpartition字句都必须包括子分区的名称，且每个子分区的名称必须唯一。

7. 分区中的NULL值

MySQL允许对NULL值做分区，并且会把NULL值视为小于任何一个非NULL值。

  • 对于Range分区，NULL值会被插入最左边的分区。
  • 对于List分区，必须显示指出NULL值放入哪个分区。
  • 对于Hash和Key分区，任何分区函数都会将含有NULL值的记录返回为0。

以上就是《MySQL 分区表》的详细内容，更多关于mysql的资料请关注golang学习网公众号！

mysql MySQL 分区
声明：本文转载于：SegmentFault 如有侵犯，请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>