深入浅出mysql_分区

十七. mysql 分区


17.1分区概述


概念：


分区是指根据一定规则， 数据库把一个表分解成多个更小，更容易管理的部分。
就访问数据库而言，逻辑上只有一个表或者一个索引。但是实际上这个表可能有10个物理分区组成。每个分区都是独立的对象。可独立处理，可以作为表的一部分处理。


优点：




1。 和单个磁盘或者文件系统分区相比，可以存储更多的数据。
2. 优化查询，在where子句中包含分区条件时，可以只扫描必要的一个分区或多个分区，提高查询效率；同时 涉及sum 和count这类聚合查询时 可以并行的在每个分区上处理，最终只需要汇总所有分区 获得结果
3. 对于已经过期 或者不需要保存的数据，可以删除与这些数据有关的分区，来快速删除数据
4.跨多个磁盘来分散数据查询，已获得更大的查询吞吐量。


查看当前版本是否支持分区：


SHOW VARIABLES LIKE‘%partition%’


例子：
create table emp (
emp_id INT,
bir_day DATE
)ENGINE =InnoDB
PARTITION BY HASH(MONTH(bir_day))
PARTITIONS 6;


17.2分区类型


17.2.1RANGE:


是什么


基于一个给定连续区域范围，把数据分配到不同的分区。


语法


RANGE是利用取值范围分区，区间要连续，不能重叠，使用 VALUE LESS THAN 操作符进行分区定义。


范例


CREATE TABLE emp(
id INT NOT NULL，
ename VARCHAR(20),
store_id INT NOT NULL
) PARTITION BY RANGE(store_id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20)
);
注意： 每个分区要按照顺序定义，从最低到最高，这是PARTITION BY RANGE的语法要求。


上例 当插入大于20的数据时，将会报错，因为服务器不知道插入那个分区。
可是设置分区的时候使用VALURE LESS THAN MAXVALUE 。
ALTER TABLE emp ADD PARTITION(PARTITON p2 VALUES LESSTHAN MAXVALUE).


Mysql 支持在VALURE LESS THAN 字句中使用表达式


RANGE 分区适用场景


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


2，经常运行包含分区的查询，MySQL可以很快的确定只有一个或者某一个分区或者多个分区需要扫描，例如 SELECT * FROM emp WHERE store_id<10 改语句只会扫描p分区。


17.2.2LIST :


是什么


类似于RANGE分区，区别在于 LIST分区是基于枚举出的值列表分区，RANGE是基于给定的连续区域范围分区


语法


PARTITION BY LIST(expr)子句来实现，expr 是某个列或者基于某个列返回一个整数值的表达式，然后通过VALUES IN (values_list)来实现定义分区。与RANGE 分区不同，LIST不必声明任何特定的顺序。 （MySql 5.5后支持非整数）


范例


CREATE TABLE exp(
category INT,
exp_name VARCHAR(20),
....
)PARTITION BY LIST(category)(
PARTITON p0 VALUES IN(0,1),
PARTITON p1 VALUES IN(3,6),
PARTITON p2 VALUES IN(2,4,5),
)


17.2.3Columns 分区：


特点


支持更多的数据类型：
1 所有整数类型： TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。其他的数值类型都不支持。
2.日期时间类型： DATE 、 DATETIME
3.字符类型： CHAR、 VARCHAR、BINARY、VARBINARY、不支持TEXT和BLOB作为分区键。


注意：COLUMNS进支持一个或多个字段名作为分区键，不支持表达式作为分区键，区别与RANGE分区和LIST分区



例子：支持多列分区


CREATE TABLE rx3(
a INT，
b INT
)PARTITION BY RANGE COLUMNS（a，b）（
PARTITION p01 VALUES LESS THAN （0，10），
PARTITION p02 VALUES LESS THAN （10，20），
PARTITION p03 VALUES LESS THAN （10，30），
PARTITION p04 VALUES LESS THAN （10，MAXVALUE），
PARTITION p05 VALUES LESS THAN （MAXVALUE，MAXVALUE），
）；
RANGE 分区键的比较其实就是多列排序，先根据a字段排，再根据b字段排。


是什么


MySQL5.5后引入的分区，columns 分区解决了mysql RANGE 和LIST分区支持整数问题，


17.2.4HASH：


作用：
主要用来分散热点读，确保数据在预先确定分区个数的分区中尽可能的分散分布。


HAHS支持两种 分区：


1.常规分区 


是什么


常规分区采用取模计算 分区；


语法


PARTITION BY HASH(expr) PARTITIONS num 
expr 可以是mysql中任何有效的函数或者其他表达式，只要他们返回一个即非常常数也非随机数的整数；


范例


CREATE TABLE emp（
id INT NOT NULL,
ename VARCHAR(30),
......
store_id INT NOT NULL
）
PARTITION BY HASH(store_id) PARTITIONS 4;


缺点


缺点：当需要增加分区或者合并分区的时候，会出现问题。例如假设原来5个常规HASH分区，变成6个时取模算法 变成了MOD（exp,6）;原来的分区数据需要重新计算 变动分区。
常规分区不适合需要灵活变动的分区需求；


2.线性分区（LINER HASH）


是什么


为了降低分区管理的代价，MySQL提供了线性分区法则。分区法则是一个线性的2幂的运算法则；


语法


PARTITON BY LINER HASH(exp) PARTITONS num;


优缺点


优点：在分区维护（增加，删除，合并，拆分分区）时 处理速度更迅速，
缺点：相对于常规分区 数据分布不均匀；


是什么


基于给定的分区个数，把分区分配大不同的分区


17.2.5KEY：


与HASH 的区别：


类似于HASH分区
1.hash 支持用户自定义的表达式，key不允许用户自定义表达式，需要mysql提供的hash函数；
]2.hash 只支持整数分区，而key支持除text和BLOB类型外的其他类型作为分区；
3.与hash不同，创建key分区时 可以不指定分区键，默认会选择主键作为分区键，没有主键会选择非空唯一键；


相同
和HASH类似 使用LINEAR 具有相同的作用
处理大量数据时 有效的分散热点


17.2.6子分区


是什么


是对分区表中对每个分区的再次分区 又称复合分区；
从5.1后开始支持 通过RANGE和LIST分区的表再次进行子分区，子分区既可以使用HASH也可以使用KEY


语法：


CREATE TABLE ts(id INT, purchase DATE)
PARTITION BY RANGE(YEAR(purchase))
SUBPARTITION BY HASH(TO_DAYS(purchase)) SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p0 VALUES LESS THAN (2000),
PARTITION p0 VALUES LESS THAN MAXVALUE
)
该分区分成 了 2*3个分区


17.2.7分区处理NULL值的方式


RANGE 分区中 NULL当做最小值来处理
LIST 中分区中NULL 必须出现在枚举列表中 否则会出错
HASH/KEY 分区中NULL被当做零来处理


建议通过设置非空或者默认值来绕开MySQL对NULL的处理


17.3分区管理


17.3.1RANGE和LIST分区管理
(相似 统一说明)


删除：


语法


ALTER TABLE DROP PARTITON


范例


ALTER TABLE  table_name DROP PARTITON p0;
删除分区后 同时页删除了分区中的所有数据；


增加一个分区：


语法


ALTER TABLE ADD PARTITON


范例


alter table  exp add partition (partition p04 VALUES LESS THAN (3024)):
RANGE 分区只能从分区的最大端增加分区；


在不丢失数据的情况下 重新定义分区语句：


语法


ALTER TABLE REORANIZE PARTITION INTO


范例


例子： 将p3分区（2010-2020）查分为两个分区
ALTER TABLE exp REORGANZIE PARTITION p3 INTO （
PARTITION p2 VALUES LESS THAN (2017),
PARTITION p3 VALUES LESS THAN （2020）
）


例子：将分区合并为一个分区 
ALTER TABLE exp REORGANIZE p0, p1, p2 INTO (
PARTITION p0 VALUES LESS THAN(2017)
)


17.3.2KEY 和 HASH
（相似 一起讨论）


删除：


不能使用 drop方式来删除




语法：


ALTER TABLE tbname COALESCE PARTITION


例子：


将原有的四个分区改成两个
ALTER TABLE emp COALESCE PARTITION 2;
COALESCE 不能用来增加分区！！


增加分区：


语法：


ALTER TABLE tname ADD PARTITION 


范例


例子增加8个分区：
ALTER TABLE emp ADD PARTITON 8；//加上先有2个总有10个分区