MySQL分区总结

前言：分区是指根据一定的规则，数据库把一个表分解成多个更小的，更容易管理的部分。分区对应用来说是完全透明的，不影响应用的业务逻辑。

MySQL分区的优点：

　　1、和单个磁盘或者文件系统分区相比，可以存储更多数据；

　　2、优化查询。在Where字句中包含分区条件时，可以只扫描必要的一个或多个分区来提高查询效率；同时在涉及 SUM() 和 COUNT() 等聚合函数的查询时，可以容易地在每个分区上并行处理，最终只需要汇总所有分区得到的结果。

　　3、对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据。

　　4、跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。

一：概述

　　MySQL 支持使用大部分存储引擎（如：MyISAM、InnoDB、Memory等）创建分区表，不支持使用MERGE和CSV。 MySQL分区类型主要包括：range分区、list分区、hash分区、key分区；无论是那种MySQL分区类型，要么分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，也就是说不能使用主键/唯一键字段之外的其他字段分区。如：

mysql> create table emp(
    ->     id int not null,
    ->     ename varchar(30),
    ->     hired date not null default '1970-01-01',
    ->     separated date not null default '9999-12-31',
    ->     job varchar(30) not null,
    ->     store_id int not null,
    ->     primary key(id)
    -> )
    -> partition by range (store_id) (
    ->     partition p0 values less than(10),
    ->     partition p1 values less than(20),
    ->     partition p2 values less than(30)
    -> );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

　　去掉主键约束后，创建表会成功:

mysql> create table emp(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-01-01',
    -> separated date not null default '9999-12-31',
    -> job varchar(30) not null,
    -> store_id int not null
    -> )
    -> partition by range (store_id) (
    -> partition p0 values less than(10),
    -> partition p1 values less than(20),
    -> partition p2 values less than(30)
    -> );
Query OK, 0 rows affected (0.02 sec)

　　分区的名字遵循MySQL标识符的原则。分区的名字不区分大小写，如果分区名分别为 mypart 和 MyPart 将会被MySQL认为是同一个分区而报错。

 

二：RANGE分区

　　按照range分区的表是利用取值范围将数据分区，区间要连续并且不能互相重叠，使用 values less than 操作符进行分区定义。

mysql> create table emp(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-01-01',
    -> separated date not null default '9999-12-31',
    -> job varchar(30) not null,
    -> store_id int not null
    -> )
    -> partition by range (store_id) (
    -> partition p0 values less than(10),
    -> partition p1 values less than(20),
    -> partition p2 values less than(30)
    -> );
Query OK, 0 rows affected (0.02 sec)

　　如果增加商店ID大于等于30的行，超出分区范围，会出现错误。

mysql> insert into emp(id,ename,hired,job,store_id) values ('7934', 'MILLER', '1982-01-23', 'CLERK', 50);
ERROR 1526 (HY000): Table has no partition for value 50

　　可以使用 values less than maxvalue 设置分区，超出明确指定的分区值时，数据会存储在该分区，如：

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

mysql> insert into emp(id,ename,hired,job,store_id) values ('7934', 'MILLER', '1982-01-23', 'CLERK', 50);
Query OK, 1 row affected (0.05 sec)

　　MySQL支持在 values less than 字句中使用表达式 partition by range (year(separated))，可自行测试。类似的函数有 to_days()、to_seconds()；5.5版本后可直接使用日期字段作为分区键。

Range分区特别使用的两种情况：

　　1、当需要删除过期的数据时，只需要简单的 ALTER TABLE emp DROP PARTITION p0 来删除p0分区中的数据，对于具有上百万条记录的表来说，删除分区要比运行一个 DELETE 语句有效得多。

　　2、经常运行包含分区间的查询，MySQL 可以很快地确定只有一个或者某些分区需要扫描，因为其他分区不可能包含有该 WHERE 字句的任何记录。

 

三：List 分区

　　List 分区是建立离散的值列表告诉数据库特定的值属于哪个分区，List 分区在很多方面类似于 Range 分区，区别在于 List 分区是从属于一个枚举列表的值得集合，Range 分区从属于一个连续区间值得集合。

mysql> create table expenses(
    -> expense_date date not null,
    -> category int,
    -> amount decimal (10,3)
    -> )partition by list(category) (
    -> partition p0 values in (3, 5),
    -> partition p1 values in (1, 10),
    -> partition p2 values in (4, 9),
    -> partition p3 values in (2),
    -> partition p4 values in (6)
    -> );
Query OK, 0 rows affected (0.05 sec)

　　如果插入的列值不包含分区值得列表中，insert 操作会失败报错。注意：List 分区不存在类似 values less than maxvalue 这样包含其他值在内的定义方式。将要匹配的任何值都必须在值列表中。

 

四：Hash 分区

　　Hash 分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。MySQL 支持两种 Hash 分区：常规 Hash 分区、线性 Hash 分区（Linear Hash 分区）。常规 Hash 分区使用的是取模算法，线性 Hash 分区使用的是一个线性的2的幂的运算法则。

1、常规 Hash 分区

　　使用 PARTITION BY HASH(expr) PARTITIONS num 字句对分区类型、分区键和分区个数进行定义，其中 expr 是某列值或一个基于某列值返回的表达式。num 是一个非负整数，表示分区数量，默认为1。

mysql> create table emp_hash(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-01-01',
    -> separated date not null default '9999-12-31',
    -> job varchar(30) not null,
    -> store_id int not null
    -> )
    -> partition by hash (store_id) partitions 4;
Query OK, 0 rows affected (0.03 sec)

　　假设将要保存记录的分区编号为N， 那么 N=MOD(expr, num) 如，emp_hash表中有4个分区，插入一个 store_id列值为234的记录到表中：

mysql> insert into emp_hash values (1, 'Tom', '2010-10-10', '9999-12-31', 'Clerk', 234);
Query OK, 1 row affected (0.01 sec)

　　MOD(234, 4)=2 保存这条记录的分区应该是p2；从p0 开始，模为 0 时保存到 p0 依次类推。

mysql> explain partitions select * from emp_hash where store_id =234\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp_hash
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.05 sec)

　　不推荐使用涉及多列的哈希表达式，复杂的表达式可能会引起性能问题。常规 Hash 在分区管理上需要的代价高，不适合需要灵活变动分区的需求。

2、线性 Hash 分区（Linear Hash）

　　线性 Hash 分区和常规 Hash 分区 在语法上的唯一区别是在 partition by 字句中添加关键字 “Linear”，如：

mysql> create table emp_linear(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-01-01',
    -> separated date not null default '9999-12-31',
    -> job varchar(30) not null,
    -> store_id int not null
    -> )
    -> partition by linear hash (store_id) partitions 4;
Query OK, 0 rows affected (0.03 sec)

　　线性Hash分区的优点是：在分区维护（增加、删除、合并、拆分分区时），MySQL能够处理得更加迅速；缺点是：对比常规的Hash分区（取模）的时候，线性Hash各个分区之间数据的分布不太均衡。

 

五：Key 分区

　　Key 分区类似于 Hash 分区，不过 Key 分区不允许使用用户自定义的表达式，需要使用 MySQL 服务器提供的 Hash 函数；同时 Hash 分区只支持整数分区，而 Key 分区支持使用 Blob 或 Text 类型外其他类型的列作为分区键

mysql> create table emp_key(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-01-01',
    -> separated date not null default '9999-12-31',
    -> job varchar(30) not null,
    -> store_id int not null
    -> )
    -> partition by key (job) partitions 4;
Query OK, 0 rows affected (0.05 sec)

　　创建 Key 分区表的时候，可以不指定分区键，默认会首先选择使用主键作为分区键

mysql> create table emp_key1(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-01-01',
    -> separated date not null default '9999-12-31',
    -> job varchar(30) not null,
    -> store_id int not null
    -> )
    -> partition by key () partitions 4;
Query OK, 0 rows affected (0.03 sec)

　　在没有主键的情况，会选择非空唯一键作为分区键，分区键的唯一键必须是非空的，如果不是非空会报错：

mysql> create table emp_key2(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-01-01',
    -> separated date not null default '9999-12-31',
    -> job varchar(30) not null,
    -> store_id int not null,
	-> unique key(id)
    -> )
    -> partition by key () partitions 4;
Query OK, 0 rows affected (0.03 sec)

　　和 Hash 分区类似，在 Key 分区中使用关键字 Linear 具有同样的作用，Linear Key 分区时，分区的编号是通过2的幂算法得到的，不是通过取模得到的。

 

附：MySQL 分区的 null 值处理

　　1、MySQL 分区不禁止在分区键值上使用 null 

　　2、Range 分区中，null 值会被当做最小值来处理

　　3、List 分区中，null 值必须出现在枚举列表中，否则不被接受

　　4、Hash/Key 分区中，null 值会被当做零值来处理

　　5、为了避免在处理 null 值时出现误判，推荐通过设置字段非空和默认值来绕开 MySQL 对 null 值的默认处理