Mysql系列之分区

最新推荐文章于 2021-02-23 22:09:37 发布

置顶小阳爱技术

最新推荐文章于 2021-02-23 22:09:37 发布

阅读量280

点赞数

分类专栏： Mysql 文章标签：分区

本文链接：https://blog.csdn.net/qq_33261700/article/details/80883840

版权

Mysql 专栏收录该内容

3 篇文章 1 订阅

订阅专栏

分区功能并不是存储引擎来完成的，因此不是只有InnoDB才有分区功能，MyISAM,NDB等都支持分区功能，但也并不是所有的引擎都支持，如：CSV就不支持。在使用分区功能之前，应对存储引擎有所了解。

分区表

概述

MySQL数据库在5.1版本时就添加了对分区的支持，Mysql表分区类型的水平分区，不是垂直分区，此外，MySQL数据库的分区是局部索引，一个分区中及存放的数据又存放了索引。而全局分区是指数据存放在各个分区中，但是所有数据放在一个对象中。目前，Mysql并不支持全局分区。

为什么要进行分区

为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。

分区的一些优点包括：
- 与单个磁盘或文件系统分区相比，可以存储更多的数据。
- 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。
- 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
- 涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
- 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

分区的类型介绍

RANGE分区
- 行数据基于属于一个给定连续区间的列值被放入分区。
LIST分区
- LIST分区是面向离散的值。
HASH分区
- 根据用户自定义的表达式的返回值来进行分区，返回值不能是负数
KEY分区
- 根据Mysql数据库提供的哈希函数来进行分区。

无论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分，否则会报如下错误：

mysql> create  table t1(
    -> col1 int not null,
    -> col2 date not null ,
    -> col3 int not null,
    -> unique key (col1, col2)
    -> )partition by hash(col3)
    -> partitions 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

唯一索引是允许为NULL的，并且分区列只要是唯一索引的组成部分，不需要整个唯一索引都是分区列：

mysql> create  table t1(
    -> col1 int not null,
    -> col2 date not null ,
    -> col3 int not null,
    -> unique key (col1, col2, col3)
    -> )partition by hash(col3)
    -> partitions 4;
Query OK, 0 rows affected (0.07 sec)

注：如果在创建分区表的时候，没有指定唯一字段或主键时，指定任何一个字段为分区字段都是被允许的。

RANGE分区

RANGE分区是最常见的一种分区类型。

mysql> create table t(
    -> id int
    -> )engine=innodb
    -> partition by range(id) -- 给谁设置分区
    -> (
    -> partition p0 values less than (10), -- 存入该分区的条件（id值0-10）
    -> partition p1 values less than (20)，-- 存入该分区的条件（id值10-20）
    -> )
    -> ;
Query OK, 0 rows affected (0.03 sec)

查看一下RANGE分区创建的系统分区

mysql> show variables like "datadir";
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| datadir       | /var/lib/mysql/                      |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> system ls -lh  /var/lib/mysql/odp/t*
-rw-rw----  1 chenyang chenyang 8.5K Jul  2 14:30 /var/lib/mysql/odp/t1.frm
-rw-rw----  1 chenyang chenyang   32 Jul  2 14:30 /var/lib/mysql/odp/t1.par
-rw-rw----  1 chenyang chenyang  96K Jul  2 14:30 /var/lib/mysql/odp/t1#P#p0.ibd
-rw-rw----  1 chenyang chenyang  96K Jul  2 14:30 /var/lib/mysql/odp/t1#P#p1.ibd
-rw-rw----  1 chenyang chenyang 8.4K Jul  2 14:40 /var/lib/mysql/odp/t.frm
-rw-rw----  1 chenyang chenyang   28 Jul  2 14:40 /var/lib/mysql/odp/t.par
-rw-rw----  1 chenyang chenyang  96K Jul  2 14:40 /var/lib/mysql/odp/t#P#p0.ibd
-rw-rw----  1 chenyang chenyang  96K Jul  2 14:40 /var/lib/mysql/odp/t#P#p1.ibd

我们可以看到，表的文件不再是ibd文件组成，而是有建立分区时各个分区的ibd文件组成，如：t1#P#p0.ibd

查看RANGE分区情况

mysql> select * from information_schema.PARTITIONS where table_schema=database() and table_name='t'\G;
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: odp -- 数据库名
                   TABLE_NAME: t   -- 表名
               PARTITION_NAME: p1  -- 分区名
            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: 0   -- 数据条数
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2018-07-02 14:40:27
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: odp -- 数据库名
                   TABLE_NAME: t   -- 表名
               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: 0   -- 数据条数
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2018-07-02 14:40:27
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
2 rows in set (0.00 sec)

ERROR: 
No query specified

我们可以看到，有两个分区。注：database()

那么向RANGE分区表中插入数据结果回事怎么样的呢？


mysql> insert into t values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t values (10),(12),(13);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.PARTITIONS where table_schema=database() and table_name='t'\G;
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: odp
                   TABLE_NAME: t
               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: 3
               AVG_ROW_LENGTH: 5461
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2018-07-02 14:40:27
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: odp
                   TABLE_NAME: t
               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: 2018-07-02 14:40:27
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
2 rows in set (0.00 sec)

ERROR: 
No query specified

我们可以看到插入每个分区表中分别是3条数据。so 当插入数据的值小于10就会写入分区p0，插入数据大于等于10，小于20的数据插入了p1分区（分区范围是左闭右合区间）

这个时候我们会发现一个BUG，当我们插入大于20的数据的时候，会怎么样呢？一起来测试一下

mysql> insert into t values (21),(22),(30);
ERROR 1526 (HY000): Table has no partition for value 21

没错！Mysql报错，说是这个表没有存放21的分区，这可怎么办呢？
没错！这个时候，我们可以添加一个maxvalue值得分区，详细如下：

mysql> alter table t add partition (partition p2 values less than maxvalue);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t values (21),(22),(30);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

我们可以看到，当我们添加一个maxvalue分区之后，在添加比20大的值得时候就不会报错了。
来我们看看分区情况：

mysql> select * from information_schema.PARTITIONS where table_schema=database() and table_name='t'\G;
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: odp
                   TABLE_NAME: t
               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: 6
               AVG_ROW_LENGTH: 2730
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2018-07-02 15:28:28
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: odp
                   TABLE_NAME: t
               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: 2018-07-02 15:28:28
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 3. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: odp
                   TABLE_NAME: t
               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: 3
               AVG_ROW_LENGTH: 5461
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2018-07-02 15:28:28
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
3 rows in set (0.00 sec)

ERROR: 
No query specified

我看可以看到，我们成功的添加了一个值为maxvalue的分区。

RANGE分主要用于日期列的分区，简单实例：

-- 创建表
mysql> create table sales(
    -> money int unsigned not null,
    -> data datetime
    -> )engine=innodb
    -> partition by range (YEAR(data)) (
    ->   partition p2016 values less than (2016),
    ->   partition p2017 values less than (2017),
    ->   partition p2018 values less than (2018)
    -> );
Query OK, 0 rows affected (0.06 sec)

-- 插入数据

mysql> insert into sales values (100, '2015-08-09');
Query OK, 1 row affected (0.02 sec)

mysql> insert into sales values (100, '2016-08-09');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sales values (100, '2017-08-09');
Query OK, 1 row affected (0.00 sec)

如果现在我们想删除2015年的数据，我们就不用用：delete from sales where data>="2015-01-01" and data <= "2016-01-01"而是直接删除2016分区即可：

mysql> alter table sales drop partition p2016;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

另外还有一个好处，比喻我们想查询2017年的所有数据，我们可以这样操作：

mysql> explain partitions select * from sales where data>="2017-01-01" and "2017-12-31"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
   partitions: p2018
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

经过了explain partitions命令我们可以发现，Mysql只扫描了p2018这一个分区的数据，这样在应对海量数据的时候会大幅提升查询速度。但是我们需要注意的是，在写where条件的时候，尽量不要跨区，比喻上面的2017-12-31要比2018-01-01的性能更优。

LIST分区

LIST分区跟RANGE分区非常相似，不过RANGE分区值是连续的，而LIST分区的值是离散的。

不多说，上SQL：

mysql> 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)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t_list values (1,1),(1,2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

我们看看分区情况

mysql> select TABLE_SCHEMA,PARTITION_NAME,TABLE_NAME,PARTITION_DESCRIPTION,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.PARTITIONS where table_schema=database() and table_name='t_list'\G;
*************************** 1. row ***************************
         TABLE_SCHEMA: odp
       PARTITION_NAME: p0
           TABLE_NAME: t_list
PARTITION_DESCRIPTION: 1,3,5,7,9
 PARTITION_EXPRESSION: b
           TABLE_ROWS: 1
*************************** 2. row ***************************
         TABLE_SCHEMA: odp
       PARTITION_NAME: p1
           TABLE_NAME: t_list
PARTITION_DESCRIPTION: 0,2,4,6,8
 PARTITION_EXPRESSION: b
           TABLE_ROWS: 1
2 rows in set (0.00 sec)

ERROR: 
No query specified

我们可以看到每个分区都插入了一条数据。
写到这里，难免有一个疑问，如果我们插入的数据在我们规定的范围之外，这个时候，LIST分区会怎么处理呢？
其实在这个一点MyISAM存储引擎和INNODB存储引擎的处理方式是不一样的，MyISAM会将之前符合要求的数据插入，之后的数据则不会插入。Innodb存储引擎将其视为一个事务，因此没有任何数据插入。
下面我们来演示一下：

-- 创建表
mysql> create table t_list_m(
    -> a int,
    -> b int
    -> )engine=MyISAM
    -> partition by list(b)(
    ->   partition p0 values in (1,3,5,7,9),
    ->   partition p1 values in (0,2,4,6,8)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_list_i(
    -> 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)
    -> );
Query OK, 0 rows affected (0.02 sec)

-- 插入数据
mysql> insert into t_list_m values (1,2),(1,20);
ERROR 1526 (HY000): Table has no partition for value 20

mysql> insert into t_list_i values (1,2), (1,20);
ERROR 1526 (HY000): Table has no partition for value 20

-- 我们可以看到，都报错，那我们来查看一下存入的内容
mysql> select * from t_list_m;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from t_list_i;
Empty set (0.00 sec)

这个结果也就印证了上面所说的结果！
因此我们在设置分区的时候，一定要考虑清楚使用哪种！

HASH分区

HASH分区的目的是将数据均匀的分布到预先定义的各个分区中，保证各个分区的内容大体一致。在RANGE和LIST分区中必须给出指定列的列值或列值的集合，而HASH分区则是自动完成这些工作，用户所要做的只是基于将要进行的哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

上SQL：

mysql> create table t_hash(
    ->   a int ,
    ->   b datetime
    -> )engine=INNODB
    -> partition by hash(year(b))
    -> partitions 4; -- 创建几个分区（非负数，默认为1）
Query OK, 0 rows affected (0.06 sec)

查看一下分区情况

mysql> select TABLE_SCHEMA,PARTITION_NAME,TABLE_NAME,PARTITION_DESCRIPTION,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.PARTITIONS where table_schema=database() and table_name='t_hash'\G;
*************************** 1. row ***************************
         TABLE_SCHEMA: odp
       PARTITION_NAME: p0
           TABLE_NAME: t_hash
PARTITION_DESCRIPTION: NULL
 PARTITION_EXPRESSION: year(b)
           TABLE_ROWS: 0
*************************** 2. row ***************************
         TABLE_SCHEMA: odp
       PARTITION_NAME: p1
           TABLE_NAME: t_hash
PARTITION_DESCRIPTION: NULL
 PARTITION_EXPRESSION: year(b)
           TABLE_ROWS: 0
*************************** 3. row ***************************
         TABLE_SCHEMA: odp
       PARTITION_NAME: p2
           TABLE_NAME: t_hash
PARTITION_DESCRIPTION: NULL
 PARTITION_EXPRESSION: year(b)
           TABLE_ROWS: 0
*************************** 4. row ***************************
         TABLE_SCHEMA: odp
       PARTITION_NAME: p3
           TABLE_NAME: t_hash
PARTITION_DESCRIPTION: NULL
 PARTITION_EXPRESSION: year(b)
           TABLE_ROWS: 0
4 rows in set (0.00 sec)

ERROR: 
No query specified

那么现在我有一个日期：2017-01-09应该被存入那个分区呢？
我们可以这样计算，2017%4=1所以应该会被存入p1这个分区，我们来测试一下。

mysql> insert into t_hash values (1, "2017-01-09");
Query OK, 1 row affected (0.01 sec)

mysql> select TABLE_SCHEMA,PARTITION_NAME,TABLE_NAME,PARTITION_DESCRIPTION,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.PARTITIONS where table_schema=database() and table_name='t_hash'\G;
*************************** 1. row ***************************
         TABLE_SCHEMA: odp
       PARTITION_NAME: p0
           TABLE_NAME: t_hash
PARTITION_DESCRIPTION: NULL
 PARTITION_EXPRESSION: year(b)
           TABLE_ROWS: 0
*************************** 2. row ***************************
         TABLE_SCHEMA: odp
       PARTITION_NAME: p1
           TABLE_NAME: t_hash
PARTITION_DESCRIPTION: NULL
 PARTITION_EXPRESSION: year(b)
           TABLE_ROWS: 1
*************************** 3. row ***************************
         TABLE_SCHEMA: odp
       PARTITION_NAME: p2
           TABLE_NAME: t_hash
PARTITION_DESCRIPTION: NULL
 PARTITION_EXPRESSION: year(b)
           TABLE_ROWS: 0
*************************** 4. row ***************************
         TABLE_SCHEMA: odp
       PARTITION_NAME: p3
           TABLE_NAME: t_hash
PARTITION_DESCRIPTION: NULL
 PARTITION_EXPRESSION: year(b)
           TABLE_ROWS: 0
4 rows in set (0.00 sec)

ERROR: 
No query specified

我们可以看到只有p1这个分区添加了一条数据。简单的说明HASH是按照取余的方式均匀的插入到表中。

KEY分区

KEY分区与HASH分区相似，不过HASH是通过用户自定义的函数进行分区，KEY则是通过数据库函数来分区的，对于MYSQL来说，NDB引擎是通过MD5来分区，其他则是通过哈希函数，这些函数是基于与PASSWORD()一样的运算法则：

废话不多说，上SQL：

mysql> create table t_key(
    ->   a int ,
    ->   b datetime
    -> )engine=INNODB
    -> partition by key(b)
    -> partitions 4;
Query OK, 0 rows affected (0.04 sec)

COLUMNS分区

前面介绍的RANGE、HASH、KEY和LIST这四个分区的条件都是：数据必须是整型，不是整型也要通过内置函数转化成整型，比喻：YEAR()，TO_DAYS()，MONTH()等！从MYSQL5.5版本之后就可以使用COLUMNS分区，可视为RANGE和LIST分区的一种进化。

COLUMNS支持的分区：

所有整型：INT、SMALLINT、TINYINT、BIGINT。FLOAT和DECIMAL则不予支持。
日期类型：DATE和DATETIME。其他日期类型不予支持。
字符串类型：CHAR、VARCHAR、BINARY和VARBIARY。BLOB和TEXT则不予支持。
上SQL：


mysql> create table t_columns_range(
    -> a int,
    -> b datetime
    -> )engine=INNODB
    -> partition by RANGE COLUMNS(b)
    -> (
    -> partition p0 values less than ("2018-01-01"),
    -> partition p1 values less than ("2019-01-01")
    -> );

Query OK, 0 rows affected (0.03 sec)


mysql> create table t_columns_list(
    ->   first_name varchar (25),
    ->   last_name varchar (25),
    ->   city varchar (30)
    -> )engine=INNODB
    -> partition by LIST COLUMNS (city)(
    ->   partition p0 values in ("beijing"),
    ->   partition p1 values in ("shanghai"),
    ->   partition p2 values in ("shenzhen")
    -> );
Query OK, 0 rows affected (0.03 sec)

-- RANGE COLUMNS支持多列分区
mysql> create table t_columns_ranges(
    ->   a int,
    ->   b smallint ,
    ->   c char (3)
    -> )engine=INNODB
    -> partition by RANGE COLUMNS(a,b,c)
    -> (
    -> partition p0 values less than (5, 5, "man"),
    -> partition p1 values less than (10, 20, "woman")
    -> );

Query OK, 0 rows affected (0.03 sec

通过以上SQL，我们可以非常清楚的看出，之前的RANGE和LIST分区可以被RANGE COLUMNS和LIST COLUMNS所代替。

子分区

子分区就是在分区的基础上在分区，有时也称这中分区为复合分区。

创建一个复合分区：

mysql> create table t_sun(
    ->   a int,
    ->   b datetime
    -> )engine=INNODB
    -> partition by RANGE (YEAR(b))
    -> SUBPARTITION by HASH (TO_DAYS(b))
    -> SUBPARTITIONS 2(
    ->   PARTITION p0 VALUES LESS THAN (2017),
    ->   PARTITION p1 VALUES LESS THAN (2018),
    ->   PARTITION p2 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.08 sec)

查看一下生成的文件：


mysql> system ls -lh  /var/lib/mysql/odp/t_sun*
-rw-rw----  1 chenyang chenyang 8.4K Jul  3 18:04 /home/chenyang/.jumbo/var/lib/mysql/odp/t_sun.frm
-rw-rw----  1 chenyang chenyang   96 Jul  3 18:04 /home/chenyang/.jumbo/var/lib/mysql/odp/t_sun.par
-rw-rw----  1 chenyang chenyang  96K Jul  3 18:04 /home/chenyang/.jumbo/var/lib/mysql/odp/t_sun#P#p0#SP#p0sp0.ibd
-rw-rw----  1 chenyang chenyang  96K Jul  3 18:04 /home/chenyang/.jumbo/var/lib/mysql/odp/t_sun#P#p0#SP#p0sp1.ibd
-rw-rw----  1 chenyang chenyang  96K Jul  3 18:04 /home/chenyang/.jumbo/var/lib/mysql/odp/t_sun#P#p1#SP#p1sp0.ibd
-rw-rw----  1 chenyang chenyang  96K Jul  3 18:04 /home/chenyang/.jumbo/var/lib/mysql/odp/t_sun#P#p1#SP#p1sp1.ibd
-rw-rw----  1 chenyang chenyang  96K Jul  3 18:04 /home/chenyang/.jumbo/var/lib/mysql/odp/t_sun#P#p2#SP#p2sp0.ibd
-rw-rw----  1 chenyang chenyang  96K Jul  3 18:04 /home/chenyang/.jumbo/var/lib/mysql/odp/t_sun#P#p2#SP#p2sp1.ibd

小阳爱技术

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql系列之分区

分区功能并不是存储引擎来完成的，因此不是只有InnoDB才有分区功能，MyISAM,NDB等都支持分区功能，但也并不是所有的引擎都支持，如：CSV就不支持。在使用分区功能之前，应对存储引擎有所了解。分区表概述MySQL数据库在5.1版本时就添加了对分区的支持，Mysql表分区类型的水平分区，不是垂直分区，此外，MySQL数据库的分区是局部索引，一个分区中及存放的数据又...
复制链接

扫一扫