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

一、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;--查询所有引擎

引擎使用示例
MYSQL一种分表实现方案及InnoDB、MyISAM、MRG_MYISAM等各种引擎应用场景介绍
英文说明 引擎适用场景 建表语句 数据存储文件
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

        mysql数据存储路径

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

上一篇:Windows驱动开发学习记录-Windbg打印Shadow SSDT 脚本


下一篇:伤害 等待互斥锁