MySQL分区分表

分区

  • 一般情况下我们创建的表对应一组存储文件,使用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,按热度拆分,高点击率的词条生成各自的一张表,低热度的词条都放在一张大表里,待低热度的词条达到一定的贴数后,再把低热度的表单独拆分成一张表;
  • 垂直分割:将经常一起使用的字段放在一个单独的表中,分割后的表记录之间是一一对应关系。
posted @ 2021-04-30 14:44  惊风破浪的博客  阅读(6)  评论(0编辑  收藏