A table splitting implementation scheme of MySQL and InnoDB, MyISAM and MRG_ Introduction to MyISAM and other engine application scenarios

One 、MYSQL Complete example of implementing sub table （5.7 edition ）

1、 Build three identical sub tables 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、 Create another main table , The table structure is the same, but engine ENGINE=MRG_MyISAM; Addition, deletion, modification and query are all operations on this table , But the real data change is in each sub table

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=' A sub table implementation method ';

3、 verification ： perform insert sentence , The query found that the data was stored in user3 in （ Why does data exist user3 Not user1 Well ？ To look down ）

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

The article quoted ：https://www.cnblogs.com/lucky-man/p/6207873.html          If I have a user list user Yes 1100W Data , Now it's going to be broken into three tables user1 and user2 and user3,user1 Table storage 500w,user2 save 500w,user3 save 100w, All new user information will exist in the future user3 in .

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;

In this way, I will succeed in putting one user surface , Divided into three tables , There is a problem at this time , In code sql What to do with the statement , It used to be a watch , Now it's three tables , The code has changed a lot , This brings a lot of workload to programmers , Is there a good way to solve this ？ The way is to put the previous user Back up the table , Then delete , I created one of the above operations alluser surface , Just put this alluser Change the table name of the table to user That's it

Two 、INSERT_METHOD There are three different ways

INSERT_METHOD=FIRST	Insert into the first table
INSERT_METHOD=LAST	Insert into the last table
INSERT_METHOD=NO Or default	Cannot perform insert operation

3、 ... and 、MYSQL Brief description of various engines

        show engines;-- Query all engines

Examples of engine usage

English description	Engine applicable scenario	Create table statement	Data storage files
InnoDB	5.5 After version Mysql The default database for , The preferred engine for transactional databases , Support ACID Business , Support row level locking	ENGINE=InnoDB	.frm  .ibd
MyISAM	Has a higher insert , Query speed , But does not support Business	ENGINE=MyISAM	.frm .MYD .MYI
MRG_MYISAM	There will be a certain number of MyISAM The watch is united into a whole , It's useful for very large-scale data storage	ENGINE=MRG_MYISAM	.frm .MRG
MEMORY	All the data is put in the memory of the storage engine , Has extremely high insertion , Update and query efficiency . But it will occupy the memory space which is proportional to the amount of data . And its content will be in Mysql Lost on reboot	ENGINE=MEMORY	.frm
ARCHIVE	Ideal for storing large amounts of independent , Data as a historical record . Because they are not often read .Archive With efficient insertion speed , But its support for query is relatively poor	ENGINE=ARCHIVE	.frm .ARZ

        mysql Data storage path