【MySQL技术内幕】23-分区表

作者简介：大家好，我是码炫码哥，前中兴通讯、美团架构师，现任某互联网公司CTO，兼职码炫课堂主讲源码系列专题

代表作：《jdk源码&多线程&高并发》，《深入tomcat源码解析》，《深入netty源码解析》，《深入dubbo源码解析》，《深入springboot源码解析》，《深入spring源码解析》，《深入redis源码解析》等

联系qq：184480602，加我进群，大家一起学习，一起进步，一起对抗互联网寒冬

1、分区概述

分区功能并不是在存储引擎层完成的,因此不是只有 InnoDB存储引擎支持分区,常见的存储引擎 MyISAM、NDB等都支持。但也并不是所有的存储引擎都支持,如CSV、 FEDORATED、 MERGE等就不支持。在使用分区功能前,应该对选择的存储引擎对分区的支持有所了解。
MySQL数据库在5.1版本时添加了对分区的支持。分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言,从逻辑上讲,只有一个表或一个索引,但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象,可以独自处理,也可以作为一个更大对象的一部分进行处理。
MySQL数据库支持的分区类型为水平分区,并不支持垂直分区。

水平分区,指将同一表中不同行的记录分配到不同的物理文件中。
垂直分区,指将同一表中不同列的记录分配到不同的物理文件中。

此外, MySQL数据库的分区是局部分区索引,一个分区中既存放了数据又存放了索引。而全局分区是指,数据存放在各个分区中,但是所有数据的索引放在一个对象中。目前, MySQL数据库还不支持全局分区。

查询支持分区命令如下：

    mysql> show plugins like '%partiotion%';
    mysql> show variables like '%partition%';
    Empty set (0.00 sec)

如果查询结果显示Empty，表示不支持分区。但是上面的查询方法只是针对mysql5.6以下版本。如果mysql5.6以及以上版本，需要使用下面的查询命令：

    mysql> show plugins;

大多数DBA会有这样一个误区:只要启用了分区,数据库就会运行得更快。这个结论是存在很多问题的。就我的经验看来,分区可能会给某些SQL语句性能带来提高,但是分区主要用于数据库高可用性的管理。在OLTP应用中,对于分区的使用应该非常小心。总之,如果只是一味地使用分区,而不理解分区是如何工作的,也不清楚你的应用如何使用分区,那么分区极有可能会对性能产生负面的影响。
当前 MySQL数据库支持以下几种类型的分区。

• RANGE分区:行数据基于属于一个给定连续区间的列值被放入分区。 MySQL5.5开始支持 RANGE COLUMNS的分区。
• LIST分区:和 RANGE分区类型,只是LST分区面向的是离散的值。 MySQL5.5开始支持 LIST COLUMNS的分区。
• HASH分区:根据用户自定义的表达式的返回值来进行分区,返回值不能为负数。
• KEY分区:根据 MySQL数据库提供的哈希函数来进行分区。

不论创建何种类型的分区,如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分,因此下面创建分区的SQL语句会产生错误。

    mysql> create table t1(
        -> col1 int not null,
        -> col2 date not null,
        -> col3 int not null,
        -> col4 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,
        -> col4 int not null,
        -> unique key (col1,col2,col3,col4)
        -> ) partition by hash(col3) partitions 4;
    Query OK, 0 rows affected (0.04 sec)

如果建表时没有指定主键,唯一索引,可以指定任何一个列为分区列。

2、分区类型

2.1、RANGE分区

我们介绍的第一种分区类型是 RANGE分区,也是最常用的一种分区类型。下面的CREATE TABLE语句创建了一个id列的区间分区表。当id小于10时,数据插入p0分区。当id大于等于10小于20时,数据插入p1分区。

    mysql> create table t(
        -> id int
        -> )engine=innoDB
        -> partition by range (id)(
        -> partition p0 values less than (10),
        -> partition p1 values less than (20));
    Query OK, 0 rows affected (0.03 sec)

查看表在磁盘上的物理文件,启用分区之后,表不再由一个id文件组成了,而是由建立分区时的各个分区ibd文件组成,如下面的t#P#p0.ibd,t#P#p1.ibd:

    mysql>  system sudo ls -lh /usr/local/mysql/data/test2;
    total 416
    -rw-r-----  1 _mysql  _mysql    65B 10 17 17:36 db.opt
    -rw-r-----  1 _mysql  _mysql    96K 10 17 17:38 t#P#p0.ibd
    -rw-r-----  1 _mysql  _mysql    96K 10 17 17:38 t#P#p1.ibd
    -rw-r-----  1 _mysql  _mysql   8.4K 10 17 17:38 t.frm

接着插入如下数据:

    mysql> insert into t select 9;
    Query OK, 1 row affected (0.01 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    mysql> insert into t select 10;
    Query OK, 1 row affected (0.00 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    mysql> insert into t select 15;
    Query OK, 1 row affected (0.01 sec)
    Records: 1  Duplicates: 0  Warnings: 0

因为表t根据列i进行分区,所以数据是根据列id的值的范围存放在不同的物理文件中的,可以通过查询 information_schema架构下的 PARTITIONS表来查看每个分区的具体信息:

    mysql> select * from information_schema.PARTITIONS
        -> where table_schema=database() and table_name='t'\G
    *************************** 1. row ***************************
                    TABLE_CATALOG: def
                     TABLE_SCHEMA: test2
                       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: 1
                   AVG_ROW_LENGTH: 16384
                      DATA_LENGTH: 16384
                  MAX_DATA_LENGTH: NULL
                     INDEX_LENGTH: 0
                        DATA_FREE: 0
                      CREATE_TIME: 2018-10-17 17:38:13
                      UPDATE_TIME: 2018-10-17 17:43:36
                       CHECK_TIME: NULL
                         CHECKSUM: NULL
                PARTITION_COMMENT:
                        NODEGROUP: default
                  TABLESPACE_NAME: NULL
    *************************** 2. row ***************************
                    TABLE_CATALOG: def
                     TABLE_SCHEMA: test2
                       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: 2
                   AVG_ROW_LENGTH: 8192
                      DATA