分区表

分区概述

分区功能并不是在存储引擎层完成的，因此不是只有InnoDB存储引擎支持分区，

常见的存储引擎MyISAM、NDB等都支持。

MySQL数据库在5.1版本时添加了对分区的支持。分区的过程是将一个表或索引

分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，

只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。

每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

MySQL的分区

MySQL数据库支持的分区类型为水平分区，不支持垂直分区。

水平分区：将同一表中不同行的记录分配到不同的物理文件中

垂直分区：将同一表中不同列的记录分配到不同的物理文件中

数据库的水平分区和用中间件或代码控制分区有啥区别？

MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。

而全局分区是只数据存放在各个分区中，但是所有数据的索引放在一个对象中。

可以通过 SHOW VARIABLES LIKE "%partition%"

所以全局分区是有什么好处吗？

MySQL支持的分区

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

MySQL 5.5开始支持 RANGE 和 LIST分区

RANGE 分区

CREATE TABLE t (id INT) ENGINE= INNODB 
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20));
-- 可以查看到磁盘上不再只有一个ibd文件，而是有 t#p#p0.ibd 和 t#p#p1.ibd

查看分区的信息

SELECT * FROM information_schema.PARTITIONS 
WHERE table_schema = DATABASE() AND TABLE_NAME = "t";
-- PARTITION_METHOD 列表示分区的类型
-- TABLE_ROWS 列反映了各个分区中记录的数量

对于表t，由于定义了分区，因此插入的值必须严格遵守定义，

当插入一个不在分区的值时，MySQL将会抛出异常。

INSERT INTO t SELECT 21;
-- SQL错误(1526)：Table has no partition for value 21

可以对分区添加一个MAXVALUE值的分区

ALTER TABLE t ADD PARTITION (PARTITION p2 VALUES LESS THAN MAXVALUE);
-- 又会多出一个 t#p#p2.ibd

分区可以修改吗，影响大吗

RANGE分区的主要用于日期列的分区，

例如对与销售类的表，可以根据年来分区存放销售记录。

CREATE TABLE t_sales(money INT UNSIGNED NOT NULL, date DATETIME) ENGINE = INNODB 
PARTITION BY RANGE (YEAR(date)) (
PARTITION p2008 VALUES LESS THAN (2009),
PARTITION p2009 VALUES LESS THAN (2010),
PARTITION p2010 VALUES LESS THAN (2011));

这样分区的好处

删除某一年的数据时，不需要用普通的删除语句删除，直接删除分区即可
```
ALTER TABLE t_sales DROP PARTITION p2008;
```
可以加快某些查询操作，查询只落在某个分区时，优化器会进行优化，不会搜索全部分区

—— Partition Pruning 分区修剪

注意！优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()、UNIX_TIMESTAMP()

这类函数进行优化选择

LIST 分区

List分区和Range分区非常相似，只是分区列的值是离散的，而非连续的。

CREATE TABLE t_list_partition(a INT, b INT) ENGINE=INNODB
PARTITION BY LIST(b) (
PARTITION p0 VALUES IN (0,2,4,6,8),
PARTITION p1 VALUES IN (1,3,5,7,9));

List分区可能在有固定值的列的情况下使用。

HASH 分区

CREATE TABLE t_hash_partition(a INT, b INT) ENGINE=INNODB
PARTITION BY HASH(b) 
PARTITIONS 4;
-- PARTITION BY HASH(b) 指定用hash的方式分区，还支持 LINEAR HASH 这种更复杂的
-- PARTITIONS 4 表示划分为4个分区，不指定默认为1

Hash分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据量大致都是一样的。

LINEAR HASH分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理大量数据的表。

缺点在于与使用HASH分区得到的数据分布相比会不大均衡。

KEY分区

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

KEY分区使用MySQL数据库提供的函数进行分区。

CREATE TABLE t_key_partition(a INT, b INT) ENGINE=INNODB
PARTITION BY KEY(b) 
PARTITIONS 4;

COLUMNS 分区

RANGE、LIST、HASH和KEY这四种分区中，分区的条件是：数据必须是整型，

如果不是整型需要使用函数转换为整形。

COLUMNS分区可视为RANGE分区和LIST分区的一种进化。可以直接使用非整型的数据

进行分区，分区根据类型直接比较而得，不需要转化为整型。

COLUMNS 分区支持的数据类型

所有的整型类型：如：INT、SMALLINT、TINYINT、BIGINT。FLOAT和DECIMAL不支持。
日期类型：如DATE和DATETIME。其余的日期类型不予支持。
字符串类型：如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT不支持。

对于日期类型的分区，不再需要使用YEAR()等函数转换了。

CREATE TABLE t_columns_partition_1(money INT UNSIGNED NOT NULL, date DATETIME) ENGINE = INNODB 
PARTITION BY RANGE COLUMNS (date) (
PARTITION p2008 VALUES LESS THAN ('2009-01-01'),
PARTITION p2009 VALUES LESS THAN ('2010-01-01'),
PARTITION p2010 VALUES LESS THAN ('2011-01-01'));
-- 直接根据日期分区
-- 注意，是 PARTITION BY RANGE COLUMNS (date) 


CREATE TABLE t_columns_partition_2(name VARCHAR(25), city VARCHAR(25)) ENGINE = INNODB 
PARTITION BY LIST COLUMNS (city) (
PARTITION pChaozhou VALUES IN ('潮州'),
PARTITION pGuangzhou VALUES IN ('广州'));
-- 直接根据字符串分区
-- 注意，是 PARTITION BY LIST COLUMNS (city)


CREATE TABLE t_columns_partition_3(a INT, b INT, c CHAR(3), d INT) ENGINE = INNODB 
PARTITION BY RANGE COLUMNS (a,d,c) (
PARTITION p0 VALUES LESS THAN (5, 10, 'aaa'),
PARTITION p1 VALUES LESS THAN (15, 20, 'zzz'),
PARTITION p2 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE));
-- 多列分区

子分区

子分区subpartitioning是在分区的基础上再进行分区，有时也称这种分区为复合分区

composite partitioning。MySQL数据库允许在RANGE和LIST的分区上再进行HASH或KEY的子分区。

CREATE TABLE t_subpartition(a INT, b DATE) ENGINE = INNODB 
PARTITION BY RANGE (YEAR(b)) 
SUBPARTITION BY HASH (TO_DAYS(b))
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
-- 先根据b列进行RANGE分区，然后在进行一个HASH分区，所以分区的数量是 3 * 2。

子分区的建立需要注意以下几个问题

每个子分区的数量必须相同
要在一个分区表任何分区上使用SUBPARTITION来明确定义任何子分区，就必须定义所有子分区。
每个SUBPARTITION子句必须包括子分区的一个名字，子分区的名字必须是唯一的。

分区中的NULL值

MySQL数据库允许对NULL值做分区，但是处理的方法与其他数据库可能完全不同。

MySQL数据库的分区总是将NULL值视为小于任何一个非NULL值。

LIST分区下，必须显示指定NULL放在哪个分区，否则插入NULL会报错。

HASH和KEY分区对于NULL的处理方式和RANGE分区、LIST分区不一样。

任何分区函数都会将含有NULL的记录返回为0。