Mysql千万级别水平分表优化

需求：随着数据量的增加单表已经不能很好的支持业务，千万级别数据查询缓慢

Mysql数据优化方案：

1. 方案一：使用myisam进行水平分表优化
2. 方案二：使用mysql分区优化

一：Myisam水平分区

1、创建水平分表 user_1:

--  创建水平分表
create table user_1(
	id varchar(50) PRIMARY key COMMENT '主键id',
	user_name varchar(50) DEFAULT null COMMENT '用户名称',
	creator varchar(50) DEFAULT null COMMENT '创建者',
	create_date datetime DEFAULT null COMMENT '创建时间',
	updater varchar(50) DEFAULT null COMMENT '更新者',
	update_date datetime DEFAULT null COMMENT '更新时间'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户1表';

2、创建水平分表 user_2:

create table user_2(
	id varchar(50) PRIMARY key COMMENT '主键id',
	user_name varchar(50) DEFAULT null COMMENT '用户名称',
	creator varchar(50) DEFAULT null COMMENT '创建者',
	create_date datetime DEFAULT null COMMENT '创建时间',
	updater varchar(50) DEFAULT null COMMENT '更新者',
	update_date datetime DEFAULT null COMMENT '更新时间'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户2表';

3、创建水平分表 user

 --  创建总表
create table user(
	id varchar(50) PRIMARY key COMMENT '主键id',
	user_name varchar(50) DEFAULT null COMMENT '用户名称',
	creator varchar(50) DEFAULT null COMMENT '创建者',
	create_date datetime DEFAULT null COMMENT '创建时间',
	updater varchar(50) DEFAULT null COMMENT '更新者',
	update_date datetime DEFAULT null COMMENT '更新时间'
)ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(
`user_1`,`user_2`);

4、新增添加分区 user_3

create table user_3(
	id varchar(50) PRIMARY key COMMENT '主键id',
	user_name varchar(50) DEFAULT null COMMENT '用户名称',
	creator varchar(50) DEFAULT null COMMENT '创建者',
	create_date datetime DEFAULT null COMMENT '创建时间',
	updater varchar(50) DEFAULT null COMMENT '更新者',
	update_date datetime DEFAULT null COMMENT '更新时间'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户3表';

初始化数据 

--  插入数据
insert into user_1(`id`,`user_name`,`create_date`,`update_date`) values ('1','张三',now(),now());
insert into user_1(`id`,`user_name`,`create_date`,`update_date`) values ('2','李四',now(),now());
insert into user_2(`id`,`user_name`,`create_date`,`update_date`) values ('1','王五',now(),now());
insert into user_3(`id`,`user_name`,`create_date`,`update_date`) values ('3','赵六3',now(),now());
insert into user(`id`,`user_name`,`create_date`,`update_date`) values ('4','老赵1',now(),now());

验证数据结果：通过

 

5、重新添加分区

alter table user UNION=(`user_1`,`user_2`,`user_3`);

参数说明 

1：分表ENGINE必须为：MyISAM，总表必须为MRG_MyISAM
2：INSERT_METHOD：向总表插入数据默认到哪个位置取值：
       0：不允许插入
       1：允许插入
       FIRST：merge的第一张表
       LAST：merge的最后一张表

出现异常错误

1168 - Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

 问题定位：

•  分表MERGE引擎仅适用于MYISAM表
• union了不存在的表或者不在同一个数据库中的表
• 表的结构不一致

官网地址：https://dev.mysql.com/doc/refman/8.0/en/merge-storage-engine.html