Daily development situation, we often encounter the big table in the large table refers to the storage levels millions and tens of millions of records in the table, the table is too big, lead to the database a query and insert it takes too long, poor performance, if the case involving a federated query, performance will be worse, the purpose of the table is to reduce the burden of database, improve the efficiency of the database, usually point is to improve efficiency of table to add and delete, this article will introduce me of an use stored procedures to a address for 26 million data repository large table (also can make data download) level table processing, for MySQL stored procedures, everyone know, I will not introduce concept, this article mainly introduced the process of the table, my previous article also briefly introduces some of the stored procedure syntax, create statements, etc., refer to the article: https://blog.csdn.net/caiqing116/article/details/84843908 cut to the chase, enter text,

1. Create an IP address library according to the total table

The CREATE TABLE ` tb_data_ipaddrlib_free ` (
UNSIGNED ` id ` INT (11) the NOT NULL AUTO_INCREMENT COMMENT 'id',
` minip ` INT (11) UNSIGNED DEFAULT NULL COMMENT 'IP IP block minimum end, integer form,
` maxip ` INT (11) UNSIGNED DEFAULT NULL COMMENT 'IP block of the big end IP, integral form,
` continent ` VARCHAR (16) DEFAULT NULL COMMENT 'continents'
` areacode ` VARCHAR (4) the DEFAULT NULL COMMENT 'IP block country code for your country,
` country ` VARCHAR (50) DEFAULT NULL COMMENT 'IP block country',
` multiarea ` text COMMENT 'IP piece positioning information, it is single or multiple zones',
` user ` VARCHAR (200) the DEFAULT NULL COMMENT 'IP user name',
The PRIMARY KEY (` id `)
The KEY ` index_minip_maxip ` (` minip `, ` maxip `)
) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9
10
11.
12
2. The IP address range and table suffix relational tables to create
I will behind the IP address table showing the library every 1 million IP address record as a relational record storage, id value from 1001 began to accumulate here (suffix all four, this can define your own two three is ok), as to distinguish the suffix of table, minip that is the beginning of the 1 million data IP maxip article 1 million the end of the data for this IP, above these definitions can be flexibility, according to individual needs to be defined, the follow-up according to the result of the stored procedure insert record form, such as:

1000, minip1 maxip2
1001, minip3 maxip4
1002, minip5 maxip6
1
2
3
Build table statements below

DROP TABLE IF the EXISTS tb_data_ipaddrlib_tables;
The CREATE TABLE ` tb_data_ipaddrlib_tables ` (
UNSIGNED ` id ` INT (11) the NOT NULL AUTO_INCREMENT COMMENT 'id',
` minip ` INT (11) UNSIGNED DEFAULT NULL COMMENT 'IP IP block minimum end, integer form,
` maxip ` INT (11) UNSIGNED DEFAULT NULL COMMENT 'IP block of the big end IP, integral form,
The PRIMARY KEY (` id `)
) the DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
3. Import library according to the total IP address as the table on the basis of
Navicat for MySQL provides the function of the import data, we can also according to tb_data_ipaddrlib_free table structure data manually, here is not described in detail how to derivative according to, if you need can find me (comments), import the results are as follows, you can see here I have imported 26407540 data


The data format below

4. Define the stored procedure for the IP address range and dynamic table suffix relational table insert data
Each table of 1 million data, redundancy of insert in the last table, table suffix from 1001 began to accumulate

DELIMITER//
Create PROCEDURE proc_ip_split_tables ()
The begin
# define variables I loop starting value, init table suffix starting value, datanum each table the largest amount of data, count table number
Declare I int the default 0;
Declare the init int the default 1001;
Declare datanum int the default 1000000;
Declare the count int.
# and calculate the table number assigned to the count
The select FLOOR (count (id)/datanum) into the count from tb_data_ipaddrlib_free;
Truncate tb_data_ipaddrlib_tables;
While i<=count do
IF I=count THEN
Insert into tb_data_ipaddrlib_tables set
Id=init + I,
# query start IP assignment
Minip=(select minip from tb_data_ipaddrlib_free where id=(1 + datanum * I)),
# query over IP assignment, and finally a record
Maxip=(select maxip from tb_data_ipaddrlib_free ORDER BY id desc limit 1);
The ELSE
Insert into tb_data_ipaddrlib_tables set
Id=init + I,
# query start IP assignment
Minip=(select minip from tb_data_ipaddrlib_free where id=(1 + datanum * I)),
# query over IP assignment
Maxip=(select maxip from tb_data_ipaddrlib_free where id=(datanum + datanum * I));
END IF;
The set I=I + 1;
End the while;
End//
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Execute the stored procedure call proc_ip_split_tables ();
According to 26 million + the amount of data, each table 1 million, we can conclude that generates a table 27, the results are as follows:


5. Define the stored procedure to create all the table

DELIMITER//
Create PROCEDURE proc_ip_tables_create ()
The begin
# define variables I loop starting value, init table suffix starting value, datanum each table the largest amount of data, count table number
Declare I int the default 0;
Declare the init int the default 1001;
Declare datanum int the default 1000000;
Declare the count int.
# and calculate the table number assigned to the count
The select FLOOR (count (id)/datanum) into the count from tb_data_ipaddrlib_free;
# to create table
While i<=count do
The set @ sql_create_table=concat (
'the CREATE TABLE IF NOT EXISTS tb_data_ipaddrlib_free_', init + I,
"(
UNSIGNED ` id ` INT (11) the NOT NULL AUTO_INCREMENT COMMENT 'id',
` minip ` INT (11) UNSIGNED DEFAULT NULL COMMENT 'IP IP block minimum end, integer form,
` maxip ` INT (11) UNSIGNED DEFAULT NULL COMMENT 'IP block of the big end IP, integral form,
` continent ` VARCHAR (16) DEFAULT NULL COMMENT 'continents'
` areacode ` VARCHAR (4) the DEFAULT NULL COMMENT 'IP block country code for your country,
` country ` VARCHAR (50) DEFAULT NULL COMMENT 'IP block country',
` multiarea ` text COMMENT 'IP piece positioning information, it is single or multiple zones',
` user ` VARCHAR (200) the DEFAULT NULL COMMENT 'IP user name',
PRIMARY KEY (` id `),
The KEY ` index_minip_maxip ` (` minip `, ` maxip `)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ");
PREPARE sql_create_table FROM @ sql_create_table;
The EXECUTE sql_create_table;
The set I=I + 1;
End the while;
End//
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11.
12
13
14
15
16
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull