demand ： With the increase of data volume, a single table can no longer support business , Ten million level data query is slow

Mysql Data optimization scheme ：

1. Scheme 1 ： Use myisam Optimize the horizontal table
2. Option two ： Use mysql Partition optimization

One ：Myisam Horizontal zoning

1、 Create a horizontal sub table user_1:

--   Create a horizontal sub table 
create table user_1(
	id varchar(50) PRIMARY key COMMENT ' Primary key id',
	user_name varchar(50) DEFAULT null COMMENT ' User name ',
	creator varchar(50) DEFAULT null COMMENT ' The creator ',
	create_date datetime DEFAULT null COMMENT ' Creation time ',
	updater varchar(50) DEFAULT null COMMENT ' Update the ',
	update_date datetime DEFAULT null COMMENT ' Update time '
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=' user 1 surface ';

2、 Create a horizontal sub table user_2:

create table user_2(
	id varchar(50) PRIMARY key COMMENT ' Primary key id',
	user_name varchar(50) DEFAULT null COMMENT ' User name ',
	creator varchar(50) DEFAULT null COMMENT ' The creator ',
	create_date datetime DEFAULT null COMMENT ' Creation time ',
	updater varchar(50) DEFAULT null COMMENT ' Update the ',
	update_date datetime DEFAULT null COMMENT ' Update time '
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=' user 2 surface ';

3、 Create a horizontal sub table user

 --   Create a master table 
create table user(
	id varchar(50) PRIMARY key COMMENT ' Primary key id',
	user_name varchar(50) DEFAULT null COMMENT ' User name ',
	creator varchar(50) DEFAULT null COMMENT ' The creator ',
	create_date datetime DEFAULT null COMMENT ' Creation time ',
	updater varchar(50) DEFAULT null COMMENT ' Update the ',
	update_date datetime DEFAULT null COMMENT ' Update time '
)ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(
`user_1`,`user_2`);

4、 Add partition user_3

create table user_3(
	id varchar(50) PRIMARY key COMMENT ' Primary key id',
	user_name varchar(50) DEFAULT null COMMENT ' User name ',
	creator varchar(50) DEFAULT null COMMENT ' The creator ',
	create_date datetime DEFAULT null COMMENT ' Creation time ',
	updater varchar(50) DEFAULT null COMMENT ' Update the ',
	update_date datetime DEFAULT null COMMENT ' Update time '
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=' user 3 surface ';

Initialization data  

--   insert data 
insert into user_1(`id`,`user_name`,`create_date`,`update_date`) values ('1',' Zhang San ',now(),now());
insert into user_1(`id`,`user_name`,`create_date`,`update_date`) values ('2',' Li Si ',now(),now());
insert into user_2(`id`,`user_name`,`create_date`,`update_date`) values ('1',' Wang Wu ',now(),now());
insert into user_3(`id`,`user_name`,`create_date`,`update_date`) values ('3',' Zhao Liu 3',now(),now());
insert into user(`id`,`user_name`,`create_date`,`update_date`) values ('4',' laozhao 1',now(),now());

Verify the data results ： adopt

5、 Add the partition again

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

Parameter description  

1： table ENGINE It has to be for ：MyISAM, The total table must be MRG_MyISAM
2：INSERT_METHOD： The default location for inserting data into the master table ：
       0： Inserting... Is not allowed 
       1： Allow to insert 
       FIRST：merge First table of 
       LAST：merge The last table of 

An unexpected error occurred

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

  Problem location ：

•   table MERGE The engine is only applicable to MYISAM surface
• union A table that does not exist or is not in the same database
• The structure of the table is inconsistent

Official website address ：https://dev.mysql.com/doc/refman/8.0/en/merge-storage-engine.html