MySQL深入学习（4）——分区表

1. 概述

    分区功能其实并不是在存储引擎层完成的，所以并不是只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持。但也并不是所有的存储引擎都支持。

    分区是将一个表的数据按照某种方式，比如按照时间上的月份，分成多个较小的，更容易管理的部分，但是逻辑上仍是一个表。所以，分区跟性能没有必然关系，分区更多的是从管理的角度出发的。

    MySQL数据库在5.1版本增加了对分区的支持。分区的过程是将一个表或者索引分解为多个更小、更易于管理的部分。以数据库查询来说，从逻辑上讲只有一个表或一个索引，但是在物理上这个表或者索引可能会有数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大的对象的一部分进行处理。

    MySQL数据库支持水平分区，而不支持垂直分区（和水平分表、垂直分表的概念差不多）。此外，MySQL支持的是局部分区索引，即一个分区既存放数据又存储索引。可以通过以下指令查看数据库是否启用了分区功能。

show variables like '%partition%';
或者
show pulgins;

    上面说过，分区与性能其实并没有必然联系，虽然说分区确实会为某些SQL语句带来性能提升，但是分区的主要目的是用于数据库高可用性的管理。在OLTP（线上事务处理）应用中，对于分区的使用应该非常小心。目前MySQL支持的分区类型如下：

（1）RANGE分区：行数据基本属于一个给定连续区间的列值被放入区间。

（2）LIST分区：和RANGE分区类型相似，但是LIST分区面向的是离散的值。

（3）HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。

（4）Key分区：根据MySQL数据库提供的哈希函数来进行分区。

   不论是何种分区类型，如果表中存在主键或唯一索引的列，则分区列必须是主键或唯一索引的一部分。唯一索引列可以是null值。在没有主键和唯一索引的表中可以指定任意列为索引列。表中只能最多有一个唯一索引，即primary key 和unique key不能同时存在，primary key是包含unique key特性的。而且以上几种分区类型的分区字段必须为int类型字段或者可以转换为int类型。

 

2. RANGE分区

    RANGE分区的分区字段必须是整型或者转换为整型，按照字段的区间划分数据的归属，典型的就是按照时间维度的月份分区。比如下表就是一个创建一句id列的区间分区表，当id的值小于10时，数据插入p1分区，大于等于10小于20时，则存储到p2分区。

CREATE TABLE test_range_partition(
    id INT auto_increment,
    createdate DATETIME,
    primary key (id,createdate)
) 
PARTITION BY RANGE (id ) (
   PARTITION p1 VALUES LESS THAN ( 10 ),
   PARTITION p2 VALUES LESS THAN ( 20 )
);

    表被分区处理后，表在磁盘上的物理文件就不再是一个ibd文件组成了，而是由建立分区时的各个分区ibd文件组成。比如上面的表会被分为 test_range_partition#P#p1.ibd和 test_range_partition#P#p2.ibd两个文件。

    上面定义了分区的区间有两种，一种是id小于10的，另一种是10到20之间的，那如果插入的行记录的id值大于20呢？MySQL对于插入一个不在分区中定义的值的时候，MySQL会抛出一个异常。

    实际上，对于分区依据字段并不是必须为int类型数据，我么你需要保证的是在RANGE() 这个括号内的数据是一个int类型数据即可，所以我们可以将一些非int类型的字段也作为分区依据，比如上表中的createdate字段，可以通过Year(createdate)作为分区依据。新分区规则如下所示

CREATE TABLE test_range_partition(
    id INT auto_increment,
    createdate DATETIME,
    primary key (id,createdate)
) 
PARTITION BY RANGE (YEAR(createdate) ) (
   PARTITION p2008 VALUES LESS THAN ( 2009 ),
   PARTITION p2009 VALUES LESS THAN ( 2010 )
);

    为表添加分区之后，确实会对于SQL语句有着很好的性能提升，比如在上面的依据时间分区的表中进行下面SQL查询

select * from test_range_partition where createdate = '2018-10-10';

在上面的SQL语句中，SQL执行时（可以通过explain 来查看MySQL中的SQL语句执行计划,在partitions这一栏就可以看到查询的分区）只会在p2008这个分区中进行查找，而不是在所有分区，或者说整张表中进行查询，查询速度自然大幅提升。

3. LIST分区

    LIST分区和RANGE分区非常类似，只是区别在于LIST分区中所依据的列（字段）的值是散列的，或者说不连续的，随机的。比如

create table t1 (
    id int,
    age int
)partition by list(id) (
    partition p1 values in (2,4,1,5,7),
    partition p2 values in (0,3,9,8,6)
)

    注意RANGE分区定义使用的是values less than语句，而LIST分区定义语句使用的是values in。如果插入的数据的值不在分区定义中，那么同样会抛出异常。

    LIST分区要求有确定的固定的散列值，所以通常都是用于哪些有明确的取值范围的数据字段，比如说性别字段，0代表女，1代表男。

4. HASH分区

    HASH分区的目的是将数据均匀的分布到各个预先定义的分区中，保证各个分区的数据量接近一致。在RANGE和LIST分区中，需要给定一个明确的列值或者范围，而在HASH分区中，MySQL自动完成这些工作，用户所需要进行的只是基于要进行哈希分区的列值指定一个列值或表达式，以及被分区的表将要被分割成的分区数量。如下所示

create table t2 (
    id int,
    date datetime,
    primary key(id,date)
) partition by hash(id)
partition 4;

create table t2 (
    id int,
    date datetime,
    primary key(id,date)
) partition by hash(year(date))
partition 4;

    同样的，HASH分区的所依据的内容并不是必须为int字段，只要by hash(expr)中的expr是整形数据即可，哪怕是一个表达式。而partition num就表示分为num个分区，如果不包含这个partition子句，那么分区数量默认为1。

5. KEY分区

    key分区和hash分区类似，但不同之处在于key分区不需要指定int类型的字段或者是将字段值处理为int型在进行分区，而是采用了MySQL的内置hash算法，对于大部分的字段类型都支持直接分区。比如说

create table t3 (
    id int,
    date datetime,
    primary key(id,date)
) partition by key(date)
partition 4;

KEY分区支持除text和BLOB之外的所有数据类型的分区，而HASH分区只支持数字分区，KEY分区不允许使用用户自定义的表达式进行分区，KEY分区使用系统提供的HASH函数进行分区。

6. COLUMNS分区

    在上面几种分区中，都对分区依赖的数据类型做了限制，必须是int型或者是能通过YEAR()等函数处理为int型数据。但从MySQL5.5开始支持COLUMNS分区，该分区可以视为RANGE和LIST分区的进化，分区一句类型直接比较而得，不需要转换为整形数据，此外Columns分区可以依据多个列的值进行分区。

    其支持的数据类型包括：

（1）所有整形类型，比如int、bigint等，但是float和decimal不支持。

（2）日期类型只支持date和datetime。

（3）字符串类型只支持char、varchar、binary、varbinary。blob和text不支持。

7. 子分区

    所谓子分区其实就是在一个分区的基础上在进行一次分区，或者叫复合分区。MySQL数据库允许在RANGE和LIST的分区上在进行HASH和KEY的子分区，比如：

create table t5 (
    id int,
    date datetime
    primary key(id,date)
)partition by range(year(date)) 
    subpartition by hash(to_days(date))
    subpartition 4
(partition p1 values less than (2010),
partition p2 values less than (2020)
);

8. 分区和性能

    上面说过分区确实可以为数据SQL执行带来性能提升，但是并不是一定会带来性能提升，甚至会带来性能下降（和数据库索引有些类似）。可以来做一个简单的分析，假如有一张1000万行数据的表，我们对其主键进行分区处理，分为10个分区，每个分区由100万行数据。

    如果执行select * from table where pk = '**'，我们都知道MySQL数据库内部由B+树索引组织数据，主要影响查询性能的是IO次数，如果树的高度为2，那么就需要进行两次IO。所以，如果100万行数据和1000万行数据在数据库中的树高度都为2，那么其实分区与不分区的性能差异并不大，如果说100万行数据是2层树结构，而1000万行数据是3层树结构，那么确实会多出一次IO操作的性能差异。

   那么，假如确实100万行数据是2层树结构，而1000万行数据是3层树结构，但是，我们对一个表的查询并不总是依据主键查询，如果我们执行另一条SQL语句不涉及主键查询的话，比如select * from table where key = '**'。该条SQL语句的执行必然会涉及到扫描10个分区的所有数据，那么一个分区2次IO，10个分区就是20次IO，而在原来的单表不分区的设计也只是3次IO而已。

    在OLTP（线上事务处理）类型的应用中，比如电商平台、博客网站、网络游戏等，通常都会对一张表中的每个字段都有可能进行查询操作，而且每次查询的数据量都很少；而对于OLAP（在线数据分析）类型的应用中，比如数据仓库等，通常就会依据一个固定的字段进行查询取数据，而且每次提取都是大量数据提取。

    所以，在设计分区时，尤其是对于OLTP的应用中，一定要慎重使用分区。