当前位置：网站首页>MYSQL一种分表实现方案及InnoDB、MyISAM、MRG_MYISAM等各种引擎应用场景介绍

MYSQL一种分表实现方案及InnoDB、MyISAM、MRG_MYISAM等各种引擎应用场景介绍

2022-04-23 14:07:00 【pureluckyfish】

一、MYSQL实现分表完整示例（5.7版本）

1、建三张一样的分表user1、user2、user3

CREATE TABLE user1 (
	id INT auto_increment NOT NULL,
	name varchar(50) NULL,
	sex INT DEFAULT 0 NOT NULL,
	CONSTRAINT user1_pk PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE user2 (
	id INT auto_increment NOT NULL,
	name varchar(50) NULL,
	sex INT DEFAULT 0 NOT NULL,
	CONSTRAINT user1_pk PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE user3 (
	id INT auto_increment NOT NULL,
	name varchar(50) NULL,
	sex INT DEFAULT 0 NOT NULL,
	CONSTRAINT user1_pk PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

2、另建一张主表，表结构一样但引擎ENGINE=MRG_MyISAM；增删改查都是操作这张表，但是真实的数据变化是在各个分表中

CREATE TABLE alluser (
	id INT auto_increment NOT NULL,
	name varchar(50) NULL,
	sex INT DEFAULT 0 NOT null,
	index(id)
	)
ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 
INSERT_METHOD=LAST UNION=(`user1`,`user2`,`user3`) 
auto_increment=1 COMMENT='一种分表实现方法';

3、验证：执行insert语句，查询发现数据存储在user3中（为啥数据是存在user3中而不是user1呢？往下看）

insert into alluser(name,sex)values('童童',1);
select * from alluser;
delete from alluser where id=1;
select * from user1 ;
select * from user2 ;
select * from user3 ;

文章引用：https://www.cnblogs.com/lucky-man/p/6207873.html
假如我有一张用户表user有1100W条数据，现在要拆成三张表user1和user2和user3，user1表存500w，user2存500w，user3存100w，以后新增的用户信息都存在user3中。

INSERT INTO user1(user1.id,user1.name,user1.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id <= 5000000;
INSERT INTO user2(user2.id,user2.name,user2.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 5000000 and user.id <= 10000000;
INSERT INTO user3(user3.id,user3.name,user3.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 10000000;

这样我就成功的将一张user表，分成了三个表，这个时候有一个问题，代码中的sql语句怎么办，以前是一张表，现在变成三张表了，代码改动很大，这样给程序员带来了很大的工作量，有没有好的办法解决这一点呢？办法是把以前的user表备份一下，然后删除掉，上面的操作中我建立了一个alluser表，只把这个alluser表的表名改成user就行了

二、INSERT_METHOD三种方式区别

INSERT_METHOD=FIRST	插入到第一个表
INSERT_METHOD=LAST	插入到最后一个表
INSERT_METHOD=NO或者缺省	不能执行insert操作

三、MYSQL各种引擎简单说明

show engines;--查询所有引擎

引擎使用示例

英文说明	引擎适用场景	建表语句	数据存储文件
InnoDB	5.5版本后Mysql的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定	ENGINE=InnoDB	.frm .ibd
MyISAM	拥有较高的插入，查询速度，但不支持事务	ENGINE=MyISAM	.frm .MYD .MYI
MRG_MYISAM	将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用	ENGINE=MRG_MYISAM	.frm .MRG
MEMORY	所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失	ENGINE=MEMORY	.frm
ARCHIVE	非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差	ENGINE=ARCHIVE	.frm .ARZ