搞懂MySQL分区

 

一、InnoDB逻辑存储结构

 　　首先要先介绍一下InnoDB逻辑存储结构和区的概念，它的所有数据都被逻辑地存放在表空间，表空间又由段，区，页组成。
　　
段
　　段就是上图的segment区域，常见的段有数据段、索引段、回滚段等，在InnoDB存储引擎中，对段的管理都是由引擎自身所完成的。

区
　　区就是上图的extent区域，区是由连续的页组成的空间，无论页的大小怎么变，区的大小默认总是为1MB。为了保证区中的页的连续性，InnoDB存储引擎一次从磁盘申请4-5个区，InnoDB页的大小默认为16kb，即一个区一共有64（1MB/16kb=16）个连续的页。每个段开始，先用32页（page）大小的碎片页来存放数据，在使用完这些页之后才是64个连续页的申请。这样做的目的是，对于一些小表或者是undo类的段，可以开始申请较小的空间，节约磁盘开销。

页
　　页就是上图的page区域，也可以叫块。页是InnoDB磁盘管理的最小单位。默认大小为16KB，可以通过参数innodb_page_size来设置。常见的页类型有：数据页，undo页，系统页，事务数据页，插入缓冲位图页，插入缓冲空闲列表页，未压缩的二进制大对象页，压缩的二进制大对象页等。

二、分区概述

分区
　　这里讲的分区，此“区”非彼“区”，这里讲的分区的意思是指将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件，不是我们刚刚说的区。MySQL在5.1时添加了对水平分区的支持。分区是将一个表或索引分解成多个更小，更可管理的部分。每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。要知道MySQL是面向OLTP的数据，它不像TIDB等其他DB。那么对于分区的使用应该非常小心，如果不清楚如何使用分区可能会对性能产生负面的影响。
 　　MySQL数据库的分区是局部分区索引，一个分区中既存了数据，又放了索引。也就是说，每个区的聚集索引和非聚集索引都放在各自区的（不同的物理文件）。目前MySQL数据库还不支持全局分区。
 　　无论哪种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

三、分区类型

目前MySQL支持一下几种类型的分区，RANGE分区，LIST分区，HASH分区，KEY分区。如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。实战十有八九都是用RANGE分区。
　　　
RANGE分区
　　RANGE分区是实战最常用的一种分区类型，行数据基于属于一个给定的连续区间的列值被放入分区。但是记住，当插入的数据不在一个分区中定义的值的时候，会抛异常。RANGE分区主要用于日期列的分区，比如交易表啊，销售表啊等。可以根据年月来存放数据。如果你分区走的唯一索引中date类型的数据，那么注意了，优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择。实战中可以用int类型，那么只用存yyyyMM就好了。也不用关心函数了。

CREATE TABLE `m_test_db`.`Order` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `partition_key` INT NOT NULL,
  `amt` DECIMAL(5) NULL,
  PRIMARY KEY (`id`, `partition_key`)) PARTITION BY RANGE(partition_key) PARTITIONS 5( PARTITION part0 VALUES LESS THAN (201901),  PARTITION part1 VALUES LESS THAN (201902),  PARTITION part2 VALUES LESS THAN (201903),  PARTITION part3 VALUES LESS THAN (201904),  PARTITION part4 VALUES LESS THAN (201905)) ;

 　　这时候我们先插入一些数据

INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');

 　　现在我们查询一下，通过EXPLAIN PARTITION命令发现SQL优化器只需搜对应的区，不会搜索所有分区

 　　如果sql语句有问题，那么会走所有区。会很危险。所以分区表后，select语句必须走分区键。

以下3种不是太常用，就一笔带过了。

LIST分区
　　LIST分区和RANGE分区很相似，只是分区列的值是离散的，不是连续的。LIST分区使用VALUES IN，因为每个分区的值是离散的，因此只能定义值。

HASH分区
　　说到哈希，那么目的很明显了，将数据均匀的分布到预先定义的各个分区中，保证每个分区的数量大致相同。

KEY分区
　　KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用数据库提供的函数进行分区。

四、分区和性能

 　　一项技术，不是用了就一定带来益处。比如显式锁功能比内置锁强大，你没玩好可能导致很不好的情况。分区也是一样，不是启动了分区数据库就会运行的更快，分区可能会给某些sql语句性能提高，但是分区主要用于数据库高可用性的管理。数据库应用分为2类，一类是OLTP（在线事务处理），一类是OLAP（在线分析处理）。对于OLAP应用分区的确可以很好的提高查询性能，因为一般分析都需要返回大量的数据，如果按时间分区，比如一个月用户行为等数据，则只需扫描响应的分区即可。在OLTP应用中，分区更加要小心，通常不会获取一张大表的10%的数据，大部分是通过索引返回几条数据即可。

 　　比如一张表1000w数据量，如果一句select语句走辅助索引，但是没有走分区键。那么结果会很尴尬。如果1000w的B+树的高度是3，现在有10个分区。那么不是要(3+3)*10次的逻辑IO？（3次聚集索引，3次辅助索引，10个分区）。所以在OLTP应用中请小心使用分区表。

 　　在日常开发中，如果想查看sql语句的分区查询结果可以使用explain partitions + select sql来获取，partitions标识走了哪几个分区。

mysql> explain partitions select * from TxnList where startTime>'2016-08-25 00:00:00' and startTime<'2016-08-25 23:59:00';  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | Extra       |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
|  1 | SIMPLE      | ClientActionTrack | p20160825  | ALL  | NULL          | NULL | NULL    | NULL | 33868 | Using where |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
1 row in set (0.00 sec)

五、分区表常用操作示例

以部门员工表为例子：

1.创建range分区

1)以员工工资为依据做范围分区

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date,

salary int

)

partition by range(salary)

(

partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than maxvalue

);

2)以year(birthdate)表达式（计算员工的出生日期）作为范围分区依据。这里最值得注意的是表达式必须有返回值

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(year(birthdate))

(

partition p1 values less than (1980),

partition p2 values less than (1990),

partition p3 values less than maxvalue

);

2.创建list分区

以部门作为分区依据，每个部门做一分区

create table emp

(empno  varchar(20) not null ,

empname varchar(20),

deptno  int,

birthdate date not null,

salary int

)

partition by list(deptno)

(

partition p1 values in  (10),

partition p2 values in  (20),

partition p3 values  in  (30)

);

3.创建hash分区

  HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪 个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by hash(year(birthdate))

partitions 4;

4.创建key分区

  按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。“CREATE TABLE …PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个 列名的一个列表。

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by key(birthdate)

partitions 4;

5.创建复合分区

1）range - hash(范围哈希)复合分区

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(salary)

subpartition by hash(year(birthdate))

subpartitions 3

(

partition p1 values less than (2000),

partition p2 values less than maxvalue

);

2）range- key复合分区

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(salary)

subpartition by key(birthdate)

subpartitions 3

(

partition p1 values less than (2000),

partition p2 values less than maxvalue

);

3）list - hash复合分区

CREATE TABLE emp (

empno varchar(20) NOT NULL,

empname varchar(20) ,

deptno int,

birthdate date NOT NULL,

salary int

)

PARTITION BY list (deptno)

subpartition by hash(year(birthdate))

subpartitions 3

(

PARTITION p1 VALUES in  (10),

PARTITION p2 VALUES in  (20)

)

;

4）list - key 复合分区

CREATE TABLE empk (

empno varchar(20) NOT NULL,

empname varchar(20) ,

deptno int,

birthdate date NOT NULL,

salary int

)

PARTITION BY list (deptno)

subpartition by key(birthdate)

subpartitions 3

(

PARTITION p1 VALUES in  (10),

PARTITION p2 VALUES in  (20)

)

;

6.分区表的管理操作

1.删除分区

alter table emp drop partition p1;

不可以删除hash或者key分区。
一次性删除多个分区

alter table emp drop partition p1,p2;

2.增加分区

alter table emp add partition (partition p3 values less than (4000));

alter table empl add partition (partition p3 values in (40));

3.分解分区
  Reorganizepartition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。分解前后分区的整体范围应该一致。

alter table te

reorganize partition p1 into

(

partition p1 values less than (100),

partition p3 values less than (1000)

); ----不会丢失数据

4.合并分区
  Merge分区：把2个分区合并为一个。

alter table te

reorganize partition p1,p3 into

(partition p1 values less than (1000));

----不会丢失数据

5.重新定义hash分区表

Alter table emp partition by hash(salary)partitions 7;

----不会丢失数据

6.重新定义range分区表

Alter table emp partitionbyrange(salary)

(

partition p1 values less than (2000),

partition p2 values less than (4000)

); ----不会丢失数据

7.删除表的所有分区

Alter table emp removepartitioning;--不会丢失数据

8.重建分区

  这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。

ALTER TABLE emp rebuild partitionp1,p2;

9.优化分区

  如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使用“ALTER TABLE … OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。

ALTER TABLE emp optimize partition p1,p2;

10.分析分区

  读取并保存分区的键分布。

ALTER TABLE emp analyze partition p1,p2;

11.修补分区

  修补被破坏的分区。

在这里插入代码片

12.检查分区
  可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。

ALTER TABLE emp CHECK partition p1,p2;

  这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE … REPAIR PARTITION”来修补该分区。

六、分区表的局限性

1.在5.1版本中分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键（也包括主键约束）

CREATE TABLE emptt (

empno varchar(20) NOT NULL  ,

empname varchar(20)，

deptno int,

birthdate date NOT NULL,

salary int ,

primary key (empno)

)

PARTITION BY range (salary)

(

PARTITION p1 VALUES less than (100),

PARTITION p2 VALUES less than (200)

);

  这样的语句会报错。MySQL Database Error: A PRIMARY KEY must include allcolumns in the table’s partitioning function；

CREATE TABLE emptt (

empno varchar(20) NOT NULL  ,

empname varchar(20) ,

deptno int(11),

birthdate date NOT NULL,

salary int(11) ,

primary key (empno，salary)

)

PARTITION BY range (salary)

(

PARTITION p1 VALUES less than (100),

PARTITION p2 VALUES less than (200)

);

在主键中加入salary列就正常。

2.MySQL分区处理NULL值的方式

• 如果分区键所在列没有notnull约束。
• 如果是range分区表，那么null行将被保存在范围最小的分区。
• 如果是list分区表，那么null行将被保存到list为0的分区。
• 在按HASH和KEY分区的情况下，任何产生NULL值的表达式mysql都视同它的返回值为0。
  为了避免这种情况的产生，建议分区键设置成NOT NULL。

3.分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分
区类型为KEY分区的时候，可以使用其他类型的列作为分区键（ BLOB or TEXT 列除外）。

4.对分区表的分区键创建索引，那么这个索引也将被分区,分区键没有全局索引一说。

5.只有RANG和LIST分区能进行子分区，HASH和KEY分区不能进行子分区。

6.临时表不能被分区。

七、获取mysql分区表信息的几种方法

查看创建分区表的create语句

show create table 表名

查看表是不是分区表

show table status 

查看information_schema.partitions表查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息

select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='test';  

显示扫描哪些分区，及他们是如何使用的

explain partitions select语句

参考：

• MYSQL–表分区、查看分区
• 搞懂MySQL分区