一般情况下我们创建的表对应一组存储文件,使用MyISAM
存储引擎时是一个.MYI
和.MYD
文件,使用Innodb
存储引擎时是一个.ibd
和.frm
(表结构)文件。当数据量较大时(一般千万条记录级别以上),MySQL的性能就会开始下降,这时我们就需要将数据分散到多组存储文件,保证其单个文件的执行效率。读写分离分散数据库读写操作压力,分库分表分散存储压力。
目录
最常见的分区方案是按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)
);
6. 分区的使用
- 当数据表中的数据量很大时,分区带来的效率提升才会显现出来
- 只有检索字段为分区字段时,分区带来的效率提升才会比较明显
- 分区字段的选择很重要(业务逻辑要尽可能地使用分区字段作为查询条件)
分区有利于管理非常大的表,它采用分而治之的逻辑。