mysql-有解無憂

日常開發中我們經常會遇到大表的情況，所謂的大表是指存盤了百萬級乃至千萬級條記錄的表。這樣的表過于龐大，導致資料庫在查詢和插入的時候耗時太長，性能低下，如果涉及聯合查詢的情況，性能會更加糟糕。分表的目的就是減少資料庫的負擔，提高資料庫的效率，通常點來講就是提高表的增刪改查效率，本文將介紹我的一次利用存盤程序給一張2600萬資料的地址庫大表（資料網上下載也可以自己造）進行水平分表處理。對于MySQL存盤程序大家都了解，我就不介紹概念了，本文主要介紹分表的程序。我的上一篇文章也簡單介紹了存盤程序的一些語法、創建陳述句等，參考文章：https://blog.csdn.net/caiqing116/article/details/84843908 開門見山，進入正文。

1.創建IP地址庫總資料表

CREATE TABLE `tb_data_ipaddrlib_free` (
`id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
`minip` INT (11) UNSIGNED DEFAULT NULL COMMENT 'IP 塊最小端 IP，整數形式',
`maxip` INT (11) UNSIGNED DEFAULT NULL COMMENT 'IP 塊最大端 IP，整數形式',
`continent` VARCHAR (16) DEFAULT NULL COMMENT '大洲',
`areacode` VARCHAR (4) DEFAULT NULL COMMENT 'IP 塊所在國家的國家編碼',
`country` VARCHAR (50) DEFAULT NULL COMMENT 'IP 塊所在國家',
`multiarea` text COMMENT 'IP 塊定位資訊，是單或多區域',
`user` VARCHAR (200) DEFAULT NULL COMMENT 'IP 使用者名稱',
PRIMARY KEY (`id`)
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.創建IP地址區間和分表后綴關系表
在后面我將IP地址庫總表每100萬條IP地址記錄作為一條關聯記錄存盤，id值在這里從1001開始累加（后綴都4位，這個可以自己定義2位3位都行）,作為分表的區分后綴，minip為這100萬條資料的開始ip，maxip為這100萬條資料的結束ip。以上這些定義是可以靈活變動的，根據個人需要定義，后續根據存盤程序插入記錄結果形如：

1000，minip1,maxip2
1001，minip3,maxip4
1002，minip5,maxip6
1
2
3
建表陳述句如下

DROP TABLE IF EXISTS tb_data_ipaddrlib_tables;
CREATE TABLE `tb_data_ipaddrlib_tables` (
`id` INT (11) UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT 'id',
`minip` INT (11) UNSIGNED  DEFAULT NULL COMMENT 'IP 塊最小端 IP，整數形式',
`maxip` INT (11) UNSIGNED  DEFAULT NULL COMMENT 'IP 塊最大端 IP，整數形式',
PRIMARY KEY (`id`)
) DEFAULT CHARSET = utf8;
1
2
3
4
5
6
7
3.匯入IP地址庫總資料作為分表依據
Navicat for MySQL提供了匯入資料的功能，我們也可以根據tb_data_ipaddrlib_free表結構自己手動造資料，這里就不詳細介紹怎么導資料了。如果需要可以找我(評論區)。匯入結果如下，可以看到我這里匯入了26407540條資料

資料格式如下

4.定義存盤程序為IP地址區間和動分表后綴關系表插入資料
每張表100萬資料，冗余的插入在最后一張表，分表后綴從1001開始累加

DELIMITER //
create PROCEDURE proc_ip_split_tables()
begin
#定義變數 i 回圈起始值，init分表后綴起始值，datanum每張表最大資料量， count分表個數
declare i int default  0;
declare init int default  1001;
declare datanum int default 1000000;
declare count int ;
#計算出分表個數并賦值給count
select FLOOR(count(id) / datanum) into 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,
#查詢開始ip賦值
minip = (select minip from tb_data_ipaddrlib_free where id = (1+datanum*i) ),
#查詢結束ip賦值，最后一條記錄
maxip = (select maxip from tb_data_ipaddrlib_free ORDER BY id desc limit 1 );
    ELSE
insert into  tb_data_ipaddrlib_tables set
id = init + i,
#查詢開始ip賦值
minip = (select minip from tb_data_ipaddrlib_free where id = (1+datanum*i) ),
#查詢結束ip賦值
maxip = (select maxip from tb_data_ipaddrlib_free where id = (datanum+datanum*i) );
    END IF;
set i = i+1;
end 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
執行此存盤程序 call proc_ip_split_tables();
根據2600萬+的資料量，每張表100萬，我們可以斷定會生成27張分表。結果如下：

5.定義存盤程序創建所有的分表

DELIMITER //
create PROCEDURE proc_ip_tables_create()
begin
#定義變數 i 回圈起始值，init分表后綴起始值，datanum每張表最大資料量， count分表個數
declare i int default  0;
declare init int default  1001;
declare datanum int default 1000000;
declare count int;
#計算出分表個數并賦值給count
select FLOOR(count(id) / datanum) into count from tb_data_ipaddrlib_free;
#開始創建表
while i<= count do
set @sql_create_table = concat(
'CREATE TABLE IF NOT EXISTS tb_data_ipaddrlib_free_',  init+i,
"(
`id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
`minip` INT (11) UNSIGNED DEFAULT NULL COMMENT 'IP 塊最小端 IP，整數形式',
`maxip` INT (11) UNSIGNED DEFAULT NULL COMMENT 'IP 塊最大端 IP，整數形式',
`continent` VARCHAR (16) DEFAULT NULL COMMENT '大洲',
`areacode` VARCHAR (4) DEFAULT NULL COMMENT 'IP 塊所在國家的國家編碼',
`country` VARCHAR (50) DEFAULT NULL COMMENT 'IP 塊所在國家',
`multiarea` text COMMENT 'IP 塊定位資訊，是單或多區域',
`user` VARCHAR (200) DEFAULT NULL COMMENT 'IP 使用者名稱',
PRIMARY KEY (`id`),
KEY `index_minip_maxip` (`minip`, `maxip`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8" );
PREPARE sql_create_table FROM @sql_create_table;
EXECUTE sql_create_table;
set i = i+1;
end 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
32
執行此存盤程序 call proc_ip_tables_create();
根據2600萬+的資料量，每張表100萬，我們可以斷定會生成27張分表。結果如下：

6.定義存盤程序為所有的分表插入資料

DELIMITER //
create PROCEDURE proc_ip_inserttotables()
BEGIN
#定義變數 i 回圈起始值，init分表后綴起始值，datanum每張表最大資料量， count分表個數
declare i int default  0;
declare datanum int default 1000000;
declare init int default 1001;
declare count int ;
#計算出分表個數并賦值給count
select FLOOR(count(id) / datanum) into count from tb_data_ipaddrlib_free;
#開始插入資料
WHILE i<= count DO
    set @sql_insert_ip = CONCAT(
'insert into  tb_data_ipaddrlib_free_',
init+i,
' select * from tb_data_ipaddrlib_free where minip BETWEEN (select minip from tb_data_ipaddrlib_tables where id =',
init+i,
' ) AND (select minip from tb_data_ipaddrlib_free where maxip in (select  maxip from tb_data_ipaddrlib_tables where id =',
init+i,
' )limit 1 )');
    set @sql_truncate_ip = CONCAT("truncate tb_data_ipaddrlib_free_", 1001+1);
PREPARE sql_truncate_ip FROM @sql_truncate_ip;
PREPARE sql_insert_ip FROM @sql_insert_ip;

EXECUTE sql_truncate_ip;
EXECUTE sql_insert_ip;
set i = i+1;
end 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
執行此存盤程序 call proc_ip_inserttotables();
驗證步驟1，我們可以查詢任意分表然后查看是否是100萬條資料，查看最后一張表1027是否是407504條資料
查詢分表1008驗證

查詢分表1027驗證

uj5u.com熱心網友回復：

感謝分享

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/76739.html

標籤：MySQL

上一篇：這個功能的sql如何寫

下一篇：安裝MongoDB遇到了問題！