MySQL分区

头像
双子城
    阅读 14 分钟
    1

    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
    mysql分区
    双子城
    46 声望6 粉丝

    just do it！

    « 上一篇
    sh jmeter 报配置错误
    下一篇 »
    2-基础知识

    引用和评论

    推荐阅读
    头像
    Go-内建变量类型

    双子城阅读 1.6k

    0 条评论
    得票最新