MYSQL分区
分区的优点
- 存储更多数据
- 优化查询,只扫描必要的一个或者多个分区,针对count()和sum()只要对分区统计再汇总
- 对于过期或不需要保存的数据,操作分区更快
- 跨多个磁盘来分散数据查询,以获得更大的查询吞吐量
分区概述
分区键的引入。
查询是否支持分区
mysql> show variables like '%partition%';
Empty set (0.01 sec)
mysql> show variables like '%partition%';
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| innodb_adaptive_hash_index_partitions | 1 |
+---------------------------------------+-------+
MySQL支持创建分区的引擎:MyISam、InnoDB、Memory,不支持分区:MERGE、CSV
在MySQL5.1中,同一个分区表的所有分区必须使用同一个存储引擎,但是在同一个MySQL服务器中或者同一个数据库中、对于不同的分区表可以使用不同的存储引擎。
MySQL的分区适用于一个表的所有数据和索引。
设置引擎ENGINE必须在CREATE TABLE语句中的其他任何部分之前
mysql> create table emp(empid int,salay decimal(7,2),birth_date date)
engine=innodb
partition by hash(month(birth_date))
partitions 6;
Query OK, 0 rows affected (0.06 sec)
分区类型
- RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。
- LIST 分区:类似于RANGE分区,区别在于LIST分区是基于枚举出的值列表分区,RANGE是基于给定的连续区间范围分区
- HASH分区:基于给定的分区个数,把数据分配到不同的分区
- KEY分区:类似于RANGE分区
在mysql5.1中:range、list、hash分区键必须是int类型,key还可以使用blob、text。在mysql5.5中已经支持非整数类型做分区键
分区时注意
- 要么分区表上没有主键/唯一键,要么分区表主键/唯一键必须包含分区键。(否则会报错)
- 分区的名字不区分大小写
RANGE分区
利用取值范围将数据分成分区,区间要连续且不能互相重叠。
RANGE分区中,分区键如果是NULL值会被当作一个最小值来处理。
mysql> create table emp_date(
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 (year(separated)) (
partition p0 values less than (1995),
partition p1 values less than (2000),
partition p2 values less than (2005)
);
Query OK, 0 rows affected (0.04 sec)
超出最大分区范围会报错,要是有个最大值maxvalue
兜底就好了!你想要的都给你!
mysql> alter table emp_date add partition (partition p3 values less than maxvalue);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
前面说了RANGE只支持int做分区键,太没有人性了,现实业务场景那么多,MySQL5.5起改进了这个问题,新增RANGE COLUMNS 分区支持非整型分区,这样创建日期分区就不用通过函数多此一举了。no code no bb!
mysql> drop table `emp_date`;
Query OK, 0 rows affected (0.04 sec)
mysql> create table emp_date(
-> 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 columns (separated) (
-> partition p0 values less than ('1995-01-01'),
-> partition p1 values less than ('2000-01-01'),
-> partition p2 values less than ('2005-01-01')
-> );
Query OK, 0 rows affected (0.04 sec)
这种操作还不够常用,经常要按天分区怎么搞?
MySQL5.1:分区日期处理函数只有year()
和to_days()
MySQL5.5:增加了to_seconds()
,把日期转换成秒。
说了那么多,RANGE分区功能特别适用哪些情况?
- 当需要删除过期的数据,比如某个分区的数据已经完全没有意义了,请执行
alter table emp_date drop partition p0
删除分区。对动辄成千上万的数据,比运行delete要高效的多! - 经常运行包含分区键的查询,MySQL很快能找到对应的分区,并且在对应的分区扫描。
mysql> insert into emp_date (id,ename,hired,separated,job,store_id) values('7934','miller','1995-01-01','1995-01-01','care',50);
Query OK, 1 row affected (0.01 sec)
mysql> explain partitions select count(1) from emp_date where store_id >=20\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp_date
partitions: p0,p1,p2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 2 warnings (0.00 sec)
ERROR:
No query specified
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, 11 ),
-> PARTITION p2 VALUES IN ( 4, 9 ),
-> PARTITION p3 VALUES IN ( 2 )
-> );
Query OK, 0 rows affected (0.07 sec)
前面有说过,LIST也是仅支持整型,如果你是MySQL5.1,还得单独建个表。
MYSQL5.5中支持非整型分区,真贴心!
mysql> CREATE TABLE expensess (
-> expense_date date NOT NULL,
-> category varchar (30),
-> amount DECIMAL ( 10, 3 )
-> )
-> PARTITION BY list columns ( category ) (
-> PARTITION p0 VALUES IN ('loading','food' ),
-> PARTITION p1 VALUES IN ( 'ear', 'frist' ),
-> PARTITION p2 VALUES IN ( 'hire','horse' ),
-> PARTITION p3 VALUES IN ( 'fees' )
-> );
Query OK, 0 rows affected (0.06 sec)
LIST分区,整型是list (expr) ,字符串是list columns (expr)
HASH分区
HASH分区主要用来分散热点读,确保数据在预先确定个数的分区中尽肯能平均分布。对一个表执行HASH分区时,MYQSL会对分区键应用一个散列函数,以此确定数据应当放在N个分区中的哪个分区
1、HASH分区分两种
- 常规分区(HASH分区)—>取模算法
- 线性分区(LINEAR HASH分区)——>一个线性的2的冥的运算法则
2、常规分区
语法:PARTITION BY HASH(expr) PARTITIONS num
expt:某列值或者一个基于某列值返回一个整数值的表达式
num:非负整数,分几个区
实例:
CREATE TABLE emp_date (
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;
根据expr算分区:
N=MOD(expr,num)
Store_id = 234;根据公式取模:N=MOD(234,4) = 2;分布在第二个分区
测试:
insert into emp_date values(1,'care','2010-10-10','9999-12-31','tos',234);
通过执行计划看看 :
mysql> EXPLAIN PARTITIONS SELECT * FROM emp_date WHERE store_id = 234\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp_date
partitions: p2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 2 warnings (0.00 sec)
ERROR:
No query specified
MySQL不推荐使用涉及多列的哈希表达式,expr可以是非随机非常数,每次增删改都需要计算一次,存在性能问题!
优点:数据平均的分布在每个分区、提高了效率
缺点:增加或合并分区,原来平均的数据需要重新通过取模再分配,不适合需要灵活变动分区的需求
3、线性HASH分区
实例:
CREATE TABLE emp_dates (
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;
记录将要保存到的分区是num 个分区中的分区N,其中N是根据下面的算法得到: 找到下一个大于num.的、2的幂,我们把这个值称为V
V = POWER(2, CEILING(LOG(2, num)))
N = F(column_list) & (V – 1)
当 N >= num: · 设置 V = CEIL(V / 2) · 设置 N = N & (V – 1)
是不是跟我一样懵逼中?实例走一波!
设定4个分区,expr=234
V = 4;
N = 234 & (4-1);
N = 2;
因为N<= 4;会被分配到第二个分区
线性HASH分区优缺点
优点:在分区维护(包含增加、删除、合并、拆分分区)时,MySQL能够处理的更迅速
缺点:相比线性分区,各个分区之间数据的分布不太均衡
KEY分区
与HASH分区类型。不同点:
- HASH分区允许使用用户自定义的表达式,KEY分区不允许使用用户自定义的表达式,需要使用HASH函数
- HASH分区只支持整数分区,KEY分区支持使用除BLOB 、TEXT类型外的其他类型作为分区键
- 创建KEY分区可以不指定分区键,默认使用主键
key分区的语法:partition by keys(expr)
;expr是零个或者多个字段名名的列表
mysql> CREATE TABLE `emp1` (
-> 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.04 sec)
试试看不指定分区键,前提得有主键!
mysql> CREATE TABLE `emp2` (
-> 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 key ( ) partitions 4;
Query OK, 0 rows affected (0.05 sec)
退一步,没有主键也可以,但是必须要有唯一键,unique key
,同时唯一键必须为非空,你搞个空鬼才知道你要存哪个分区!
既没有主键又没有唯一键,报错!
在按照key分区的分区表上,不能执行alter table drop primary key
与HASH分区类似,可以使用关键字LINEAR KEY分区时,分区的编号是通过2的冥算法得到而不是取模。在处理大量数据时,能够有效的分散热点!
子分区
子分区是分区表中对每个分区的再次分割。也称为复合分区。
MySQL5.1开始支持对已经通过RANGE或者LIST分区了的表再进行子分区。子分区既可以使用HASH分区,也可以使用KEY分区。
mysql> CREATE TABLE `ts` (
-> id int,
-> purchased date
-> )
->
-> PARTITION by range (year(purchased))
-> SUBPARTITION by hash (TO_DAYS(purchased))
-> SUBPARTITIONS 2
-> (
-> PARTITION p0 VALUES LESS THAN (1900 ),
-> PARTITION p1 VALUES LESS THAN (2000 ),
-> PARTITION p2 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.06 sec)
原先有三个分区,p0、p1、p2
每个分区又再被分为两个子分区,一共6个分区
子分区适合数据量非常大量的数据记录
MySQL分区处理null值的方式
MySQL不禁止在分区键上使用null值。
具体分区类型的null值
- RANGE分区,null会被当成最小值
- LIST分区,null值必须出现在枚举列表中,否则不会被接受(报错)
- HASH/KEY分区,null值当成0;
分区管理
MySQL5.1提供了添加、删除、重定义、合并、拆分分区的命令。都可以通过ALTER TABLE 来实现。
1、RANGE&LIST分区管理
在添加、删除、重新定义分区的处理上,RANGE和LIST分区很相似。
1-1、删除分区
删除分区:ALTER TABLE DROP PARTITION
删除分区同时也会删除该分区对应的数据
//新建分区
create table emp_date(
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 (YEAR(separated)) (
partition p0 values less than (1995),
partition p1 values less than (2000),
partition p2 values less than (2005),
partition p3 values less than (2015),
partition p4 values less than (2020)
);
//插入数据
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (1, 'care', '1970-01-01', '1991-12-31', 'a', 1);
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (2, 'tony', '1970-01-01', '1996-12-31', 'b', 2);
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (3, 'pony', '1970-01-01', '2001-12-31', 'c', 3);
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (4, 'foly', '1970-01-01', '2006-12-31', 'd', 4);
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (5, 'quly', '1970-01-01', '2016-12-31', 'e', 5);
//删除p2分区
ALTER TABLE emp_date DROP PARTITION p2;
//查看建表语句
mysql> SHOW CREATE TABLE emp_date\G;
*************************** 1. row ***************************
Table: emp_date
Create Table: CREATE TABLE `emp_date` (
`id` int(11) NOT NULL,
`ename` varchar(30) DEFAULT NULL,
`hired` date NOT NULL DEFAULT '1970-01-01',
`separated` date NOT NULL DEFAULT '9999-12-31',
`job` varchar(30) NOT NULL,
`store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (year(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2020) ENGINE = InnoDB) */
1 row in set (0.00 sec)
ERROR:
No query specified
查询表的分区对应的情况
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
information_schema.PARTITIONS
WHERE
table_schema = SCHEMA()
AND table_name = 'emp_date';
+------+-----------------+-------+------------+
| part | expr | descr | table_rows |
+------+-----------------+-------+------------+
| p0 | year(separated) | 1995 | 1 |
| p1 | year(separated) | 2000 | 1 |
| p3 | year(separated) | 2015 | 1 |
| p4 | year(separated) | 2020 | 1 |
+------+-----------------+-------+------------+
LIST分区因为是枚举型,删除分区之后如果不新建对应分区,直接插入原先被删除的数据,报错!
1-2、增加分区
增加:`ALTER TABLE ADD PARTITION
刚才不是删了一个p2分区吗?好!现在加上去
ALTER TABLE emp_date ADD PARTITION (PARTITION p2 VALUES less than (2005));
或者
ALTER TABLE emp_date ADD PARTITION (PARTITION p5 VALUES less than (2005));
//报错
1493 - VALUES LESS THAN value must be strictly increasing for each partition, Time: 0.001000s
只能从最末端添加
ALTER TABLE emp_date ADD PARTITION (PARTITION p2 VALUES less than (2025));
这样才是正确的!
==问题==
如果是想恢复此区间的分区,怎么弄呢?
比如删除:
ALTER TABLE emp_date DROP PARTITION p2;
此分区对应的范围是:less than (2005)
现在要恢复这个范围的分区怎么办?
实例:LIST增加分区
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, 11),
PARTITION p2 VALUES IN ( 4, 9),
PARTITION p3 VALUES IN ( 2 )
);
增加分区
ALTER TABLE expenses ADD PARTITION (PARTITION p4 values in (6,7,8));
LIST必须要注意的问题是枚举值必须唯一
ALTER TABLE expenses ADD PARTITION (PARTITION p5 values in (8));
(报错)
1495 - Multiple definition of same constant in list partitioning, Time: 0.000000s
1-3、重定义分区
刚开始定义分区发现分的不好,比如RANGE分区p4的范围(2000~2015),后来这个区的数据太大了,需要重新分区,分成p3(2000~2010),p4(2010~2020),前提之前没有p2,p3分区!
RANGE拆分分区
//原有分区
create table emp_date(
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 (YEAR(separated)) (
partition p0 values less than (1995),
partition p1 values less than (2000),
partition p4 values less than (2020)
);
//过一段时间发现卧槽,都集中在p4分区了,那怎么行,赶紧重定义分区
ALTER TABLE emp_date REORGANIZE PARTITION p4 INTO(
PARTITION p3 VALUES less than (2010),
PARTITION p4 VALUES less than (2020)
)
RANGE合并分区
//合并之前的分区情况
CREATE TABLE `emp_date` (
`id` int(11) NOT NULL,
`ename` varchar(30) DEFAULT NULL,
`hired` date NOT NULL DEFAULT '1970-01-01',
`separated` date NOT NULL DEFAULT '9999-12-31',
`job` varchar(30) NOT NULL,
`store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2020) ENGINE = InnoDB) */;
//操作合并,
p0\p1\p3合并成一个区p1
ALTER TABLE emp_date REORGANIZE PARTITION p0,p1,p3 INTO(
PARTITION p1 VALUES less than (2010)
)
LIST拆分分区
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, 11 ),
PARTITION p2 VALUES IN ( 4, 9 ),
PARTITION p3 VALUES IN ( 2 ),
PARTITION p4 VALUES IN ( 6),
PARTITION p5 VALUES IN ( 7,8 )
);
目标:将P4分区包含值(6,12)
方案一:和之前一样新增分区
ALTER TABLE expenses ADD PARTITION (PARTITION p6 VALUES IN ( 6,12 ));
//报错
1495 - Multiple definition of same constant in list partitioning, Time: 0.000000s
方案二:先增加分区,后重定义分区
ALTER TABLE expenses ADD PARTITION (PARTITION p6 VALUES IN ( 12));
//p4,p5,p6重定义到 p4 (6,12) p5(7,8)
ALTER TABLE expenses REORGANIZE PARTITION p4,p5,p6 INTO(
PARTITION p4 VALUES IN (6,12),
PARTITION p5 VALUES IN (7,8)
)
总结:
重定义分区的时候:
- 不能通过重定义分区改表原有分区类型
- RANGE和LIST只能重新定义相邻的分区、重新定义的区间必须和原分区区间覆盖相同的区间
2、HASH&KEY分区管理
HASH&KEY分区管理类似,以HASH举例
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 HASH ( store_id ) partitions 4;
//减少分区
ALTER TABLE emp COALESCE PARTITION 2;
//增加分区
coalesce不能用来增加分区的数量
ALTER TABLE emp COALESCE PARTITION 8;
报错
1508 - Cannot remove all partitions, use DROP TABLE instead, Time: 0.003000s
//注意是增加8个分区
ALTER TABLE emp add PARTITION PARTITIONS 8;
mysql> SHOW CREATE TABLE emp \G;
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`id` int(11) NOT NULL,
`ename` varchar(30) DEFAULT NULL,
`hired` date NOT NULL DEFAULT '1970-01-01',
`separated` date NOT NULL DEFAULT '9999-12-31',
`job` varchar(30) NOT NULL,
`store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH ( store_id)
PARTITIONS 10 */
1 row in set (0.00 sec)
ERROR:
No query specified
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。