MySQL 分区

MYSQL分区

分区的优点

  1. 存储更多数据
  2. 优化查询,只扫描必要的一个或者多个分区,针对count()和sum()只要对分区统计再汇总
  3. 对于过期或不需要保存的数据,操作分区更快
  4. 跨多个磁盘来分散数据查询,以获得更大的查询吞吐量

分区概述

分区键的引入。

查询是否支持分区

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)

分区类型

  1. RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。
  2. LIST 分区:类似于RANGE分区,区别在于LIST分区是基于枚举出的值列表分区,RANGE是基于给定的连续区间范围分区
  3. HASH分区:基于给定的分区个数,把数据分配到不同的分区
  4. KEY**分区**:类似于RANGE分区

在mysql5.1中:range、list、hash分区键必须是int类型,key还可以使用blob、text。在mysql5.5中已经支持非整数类型做分区键

分区时注意

  1. 要么分区表上没有主键/唯一键,要么分区表主键/唯一键必须包含分区键。(否则会报错)
  2. 分区的名字不区分大小写

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分区功能特别适用哪些情况?

  1. 当需要删除过期的数据,比如某个分区的数据已经完全没有意义了,请执行alter table emp_date drop partition p0删除分区。对动辄成千上万的数据,比运行delete要高效的多!
  2. 经常运行包含分区键的查询,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分区分两种

  1. 常规分区(HASH分区)—>取模算法
  2. 线性分区(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分区类型。不同点:

  1. HASH分区允许使用用户自定义的表达式,KEY分区不允许使用用户自定义的表达式,需要使用HASH函数
  2. HASH分区只支持整数分区,KEY分区支持使用除BLOB 、TEXT类型外的其他类型作为分区键
  3. 创建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值

  1. RANGE分区,null会被当成最小值
  2. LIST分区,null值必须出现在枚举列表中,否则不会被接受(报错)
  3. 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)
)
总结:

重定义分区的时候:

  1. 不能通过重定义分区改表原有分区类型

  2. 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
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!