17.Mysql分区
分区是指根据一定的规则把一个表分解成多个部分，逻辑上仍是一张表，实际上由多个物理分区对象组成。分区对于应用是完全透明的，不影响业务逻辑和SQL编写。分区的优点： 可以存储更多的数据； 优化查询；当where条件包含分区键时只在特定的分区查询；当涉及聚合函数时每个分区可以并行查询。 可以通过分区快速删除过期数据； 可以将不同的分区分散在多个磁盘上，获取更高的磁盘IO。17.1 分区概述
分区使用分区键对某个列的取值进行范围、列表、HASH值划分，从而将表中的数据划分到为不同的分区。查看当前Mysql是否支持分区：show variables like '%partition%';Mysql分区由具体的存储引擎实现，只有部分存储引擎(Innodb)支持分区。同一个表的所有分区必须使用相同的存储引擎；不同的分区表可以使用不同的存储引擎。例子：create table temp (empid int,salary decimal(7,2),birth_date date)engine=innodbpartition by hash(month(birth_date)) partitions 4;17.2 分区类型
17.2.1 Range分区 Range分区：基于一个给定列的取值的连续范围进行分区。分区键必须是INT类型。 在有主键列的表中，主键列必须作为分区键； 在没有主键列的表中，INT类型列可以作为分区键，非INT类型列经过表达式处理后转换为INT类型也可以作为分区键。语法：create table 表名 (列名 类型(长度) 约束,...)partition by range (列表达式) ( partition 分区名1 values less than (列值1), ... [partition 分区名n values less than MAXVALUE]);说明： 列值区间必须连续，且不能重叠； 分区名不区分大小写，且不能重复； 列值区间为前闭后开[)，即包含左侧，不包含右侧； 插入值不在所有区间内，插入将报错； MAXVALUE为默认最大值，设置MAXVALUE分区后将不能再扩展分区； 如果列是非INT类型，可使用函数、表达式将列转为INT类型，也可以使用range columns分区。 range columns分区支持对日期列和字符列进行分区。 如果分区键列为null，将被当作最小值处理。例子1：create table temp (empid int,salary decimal(7,2),birth_date date)partition by range(year(birth_date)) ( partition p1 values less than (1990), partition p2 values less than (2000), partition p3 values less than (2010));例子2：create table temp (empid int,salary decimal(7,2),birth_date date)partition by range columns(birth_date) ( partition p1 values less than ('1990-01-01'), partition p2 values less than ('2000-01-01'), partition p3 values less than ('2010-01-01'));查询SQL：explain parttions select count(1) from temp where birth_date>='2000-01-01';17.2.2 List分区
List分区：基于一个给定列的取值的枚举值进行分区。分区键必须是INT类型。 在有主键列的表中，主键列必须作为分区键； 在没有主键列的表中，INT类型列可以作为分区键，非INT类型列经过表达式处理后转换为INT类型也可以作为分区键。语法：create table 表名 (列名 类型(长度) 约束,...)partition by list (列表达式) ( partition 分区名1 values in (整数列表), ...);说明： 整数列表是列的一个或多个取值，多个值时用逗号分隔； 整数列表取值与顺序无关； 插入值不在所有区间内，插入将报错； 如果列是非INT类型，可使用函数、表达式将列转为INT类型，也可以使用list columns分区。 list columns分区支持对日期列和字符列进行分区。例子1：create table temp (empid int,category int,birth_date date)partition by list(category) ( partition p1 values in (1,3), partition p2 values in (5,2), partition p3 values in (4));例子2：create table temp (empid int,category varchar(10),birth_date date)partition by list columns(category) ( partition p1 values in ('高中','大专'), partition p2 values in ('本科'), partition p3 values in ('硕士','博士'));17.2.3 Range/List Columns分区
Range/List Columns分区是Range分区和List分区的增强，区别在于分区键可以是整数、日期、字符串类型。 Range/List Columns分区的分区键支持的数据类型包括： 整数类型(tinyint、smallint、mediumint、int、bigint),日期时间类型(date、datetime),字符类型(char、varchar、binary、varbinary)。 Range/List Columns分区的分区键不支持的数据类型包括：浮点类型(decimal、float)、大对象类型(blob、text)。 Range/List Columns分区不支持列表达式分区，只能使用原始列。 Range/List Columns分区支持多个列组合分区，多列组合分区时将基于元组进行比较，即进行多列排序根据排序结果来选择分区。例子1：create table test(a int,b int)partition by range columns(a,b) ( partition p01 values less than (0,10), partition p02 values less than (10,10), partition p03 values less than (10,20), partition p04 values less than (20,20)); 17.2.4 Hash分区 Hash分区：基于一个给定列的取值的HASH函数进行分区。分区键必须是INT类型。 Hash分区可以用来分散热点读，并确保数据在预先确定个数的分区中尽可能的均匀分布。 Hash分区分为常规Hash分区和线性Hash分区(Linear hash),常规Hash分区采用取模算法，线性Hash分区采用2的幂运算法则。例子1：create table test(a int,b int)partition by hash(a) partitions 3;对a列的数值取除以3的余数，余数为0/1/2，分别对应在一个分区中。常规Hash分区不支持新增分区、合并分区等分区管理操作。例子2：create table test(a int,b int)partition by linear hash(a) partitions 4;线性Hash分区建议分区个数是2的幂次个，否则将导致数据分布不均匀。首先找到大于等于分区个数的2的幂次，V=power(2,ceiling(log(2,分区个数)))；其次将列值和(V-1)进行按位与操作。线性Hash分区支持分区管理操作，包括：增加分区、删除分区、合并分区、拆分分区。17.2.5 Key分区
Key分区是Hash分区的增强，区别在于分区键可以任何类型(除大对象类型BLOB和TEXT)。 Key分区是支持整数、日期、字符类型的Hash分区，但不支持表达式。例子1：create table test(a int,b int)partition by key(a) partitions 4;注意： 不指定分区键列时默认使用主键列，没有主键时默认非空的唯一键列； 不指定分区键列且没有主键时，唯一键列可空将报错； 没有主键、唯一键时，必须指定分区键列。例子2：create table test(a int,b int)partition by linear key(a) partitions 4;注意：Key分区同样支持常规Key分区和线性key分区。17.2.6 子分区
子分区(subpartitioning)是对分区表的再次分区，又称复合分区。 可以对Range分区和List分区再次进行分区，子分区可以是hash分区或key分区。例子：create table temp (empid int,salary decimal(7,2),birth_date date)partition by range columns(birth_date) subpartition by hash(empid) subpartitions 2 ( partition p1 values less than ('1990-01-01'), partition p2 values less than ('2000-01-01'), partition p3 values less than ('2010-01-01'));17.2.7 mysql分区处理null值的方式
mysql允许可空列作为分区键。 Range分区中null值被当作最小值处理，始终在第一个分区中； List分区中null值必须出现在枚举列表中(否则报错)，可以自行定义null值在哪个分区中； hash分区中null值被当作0处理，始终在第一个分区中。 建议分区键设置为非空。 分区信息在information_schema.partitions表中记录。17.3 分区管理
17.3.1 Range和List分区管理 增加分区： alter table 表名 add partition (partition 分区名 values less than (列值)); alter table 表名 add partition (partition 分区名 values in (列值列表)); 注意： Range分区添加分区时必须保证列值与已有列值连续且有序； List分区添加分区时必须保证列值列表与已有列值列表不重复。 删除分区:删除分区的同时会删除分区内的数据。 alter table 表名 drop partition 分区名; 重定义分区：可以将一个分区拆分为多个分区；也可以将相邻多个分区合并为一个分区。 重定义分区只能重新定义相邻的分区，且分区区间必须能够覆盖原来的分区区间，且不能改变分区类型。 拆分分区： alter table 表名 reorganize partition 原分区名 into ( partition 现分区名1 values less than (新区间列值), partition 现分区名2 values less than (等于原列值) ); 合并分区： alter table 表名 reorganize partition 原分区名1,原分区名2 into ( partition 现分区名1 values less than (等于原分区名2列值) ); alter table 表名 reorganize partition 原分区名1,原分区名2 into ( partition 现分区名1 values in (新列值列表1), partition 现分区名2 values in (新列值列表2) ); 注意：重组前后总的列值列表要相等，每个分区的列值列表可以调整。17.3.2 Hash和Key分区管理
增加分区： alter table 表名 add partition partitions 新增的分区个数; 减少分区： alter table 表名 coalesce partition 减少到几个分区;17.4 小结