mysql partition分区分表

一般情况下我们创建的表对应一组存储文件，使用MyISAM存储引擎时是一个.MYI和.MYD文件，使用Innodb存储引擎时是一个.ibd和.frm（表结构）文件。当数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率。读写分离分散数据库读写操作压力，分库分表分散存储压力。

目录

1. hash(field)

2. key(field)

3. range(field)

4. list(field)

5. 分区管理

5.1 key/hash

5.2 range/list

6. 分区的使用

最常见的分区方案是按id分区，如下将id的哈希值对10取模将数据均匀分散到10个.ibd存储文件中：

create table article(
id int auto_increment PRIMARY KEY,
title varchar(64),
content text
)PARTITION by HASH(id) PARTITIONS 10

物理存储data目录：

MySQL提供的分区算法

1. hash(field)

• 相同的输入得到相同的输出
• 适用于整型字段。

2. key(field)

和hash(field)的性质一样，只不过key是处理字符串的，比hash()多了一步从字符串中计算出一个整型在做取模操作。

create table article_key(
id int auto_increment,
title varchar(64),
content text,
PRIMARY KEY (id,title) -- 要求分区依据字段必须是主键的一部分
)PARTITION by KEY(title) PARTITIONS 10

3. range(field)

• 条件分区算法
• 按照数据大小范围分区（将数据使用某种条件，分散到不同的分区中）
• 条件运算符（less than）

create table article_range(
id int auto_increment,
title varchar(64),
content text,
created_time int, -- 发布时间到1970-1-1的毫秒数
PRIMARY KEY (id,created_time) -- 要求分区依据字段必须是主键的一部分
)charset=utf8
PARTITION BY RANGE(created_time)(-- 注意：分区的定义顺序依照created_time数值范围从小到大，不能颠倒
PARTITION p201808 VALUES less than (1535731199), -- select UNIX_TIMESTAMP('2018-8-31 23:59:59')
PARTITION p201809 VALUES less than (1538323199), -- 2018-9-30 23:59:59
PARTITION p201810 VALUES less than (1541001599) -- 2018-10-31 23:59:59);

4. list(field)

• 条件分区算法
• 按照列表值分区（in (值列表)）

create table article_list(
id int auto_increment,
title varchar(64),
content text,
status TINYINT(1), -- 文章状态：0-草稿，1-完成但未发布，2-已发布
PRIMARY KEY (id,status) -- 要求分区依据字段必须是主键的一部分
)charset=utf8
PARTITION BY list(status)(
PARTITION writing values in(0,1), -- 未发布的放在一个分区
PARTITION published values in (2) -- 已发布的放在一个分区
);

5. 分区管理

如何查看某个表的所有分区？

show create table stat

5.1 key/hash

-- 增加分区
-- 尝试使用range对文章按照月份归档，随着时间的增加，我们需要增加一个月份
alter table article_range add partition(
partition p201811 values less than (1543593599) -- select UNIX_TIMESTAMP('2018-11-30 23:59:59')
-- more
);
-- 删除分区
alter table article_range drop PARTITION p201808

注意：删除分区后，分区中原有的数据也会随之删除！

5.2 range/list

key/hash分区的管理不会删除数据，但是每一次调整（新增或删除分区）都会将所有的数据重写分配到新的分区上。效率极低，最好在设计阶段就考虑好分区策略。

-- 新增分区（在原理的基础上新增）
alter table article_key add partition partitions 4
-- 删除分区
alter table article_key coalesce partition 6
 
-- 也可以按日期
ALTER TABLE stat
    PARTITION BY RANGE(TO_DAYS(dt)) (
        PARTITION p0 VALUES LESS THAN(0),
        PARTITION p190214 VALUES LESS THAN(TO_DAYS('2019-02-14')),
        PARTITION pm VALUES LESS THAN(MAXVALUE)
    );
-- 如何进行动态扩容？
-- 增加分区需要用到 REORGANIZE 命令，它的作用是对某个分区重新分配。 比如明天是 15 号，那我们要给 15 号也增加个分区，实际上就是把 pm 分区拆分成2个分区：
ALTER TABLE stat
    REORGANIZE PARTITION pm INTO (
        PARTITION p190215 VALUES LESS THAN(TO_DAYS('2019-02-15')),
        PARTITION pm VALUES LESS THAN(MAXVALUE)
    );ALTER TABLE stat
REORGANIZE PARTITION pm INTO (
PARTITION p190215 VALUES LESS THAN(TO_DAYS('2019-02-15')),
PARTITION pm VALUES LESS THAN(MAXVALUE)
);

6. 分区的使用

• 当数据表中的数据量很大时，分区带来的效率提升才会显现出来
• 只有检索字段为分区字段时，分区带来的效率提升才会比较明显
• 分区字段的选择很重要（业务逻辑要尽可能地使用分区字段作为查询条件）

分区有利于管理非常大的表，它采用分而治之的逻辑。