分区
- 一般情况下我们创建的表对应一组存储文件,使用MyISAM存储引擎时是一个.MYI和.MYD文件,使用Innodb存储引擎时是一个.ibd和.frm(表结构)文件。
- 当数据量较大时(一般千万条记录级别以上),MySQL的性能就会开始下降,这时我们就需要将数据分散到多组存储文件,==保证其单个文件的执行效率
- 最常见的分区方案是按id分区,如下将id的哈希值对10取模将数据均匀分散到10个.ibd存储文件中:
- mysql的data目录会生成,p0-p9的MYD以及MYI文件
hash分区
//创建分区表
create table article(
id int auto_increment PRIMARY KEY,
title varchar(64),
content text
)PARTITION by HASH(id) PARTITIONS 10
//查询分区表
SELECT * FROM article PARTITION (p1);
key分区
- 与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
range算法分区
- 比如按文章的发布时间将数据按照2018年8月、9月、10月分区存放
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)(
PARTITION p201808 VALUES less than (1535731199),
PARTITION p201809 VALUES less than (1538323199),
PARTITION p201810 VALUES less than (1541001599)
);
list算法
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) -- 已发布的放在一个分区
);
//查询分区
SELECT * FROM article_list PARTITION(writing);
range/list 增加分区
ALTER TABLE article_range ADD PARTITION(
PARTITION p201811 VALUES LESS THAN(1543593599)
);
range/list 删除分区
ALTER TABLE article_range DROP PARTITION p201808 //删除分区后,分区中原有的数据也会随之删除
key和hash增加分区
ALTER TABLE article_key ADD PARTITION PARTITIONS 4;
range/list销毁分区
ALTER TABLE article_key COALESCE PARTITION 6
- key/hash分区的管理不会删除数据,但是每一次调整(新增或销毁分区)都会将所有的数据重写分配到新的分区上。效率极低,最好在设计阶段就考虑好分区策略
分区:当数据表中的数据量很大时,分区带来的效率提升才会显现出来。
- 只有检索字段为分区字段时,分区带来的效率提升才会比较明显。因此,==分区字段的选择很重要
thinkphp的分区查询
$res = Db::name('article')->partition(['p1', 'p2'])->select();
dd($res->toArray());
分表
- 数据库减压,分区算法局限,
- 水平拆分:通过建立结构相同的几张表分别存储数据
- 水平分割的几种方法:使用MD5哈希,做法是对UID进行md5加密,然后取前几位,还可根据时间放入不同的表,比如:article_201601,article_201602,按热度拆分,高点击率的词条生成各自的一张表,低热度的词条都放在一张大表里,待低热度的词条达到一定的贴数后,再把低热度的表单独拆分成一张表;
- 垂直分割:将经常一起使用的字段放在一个单独的表中,分割后的表记录之间是一一对应关系。