mysql

s1358607060 2018-12-13 09:14:17
日常开发中我们经常会遇到大表的情况,所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大,导致数据库在查询和插入的时候耗时太长,性能低下,如果涉及联合查询的情况,性能会更加糟糕。分表的目的就是减少数据库的负担,提高数据库的效率,通常点来讲就是提高表的增删改查效率,本文将介绍我的一次利用存储过程给一张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验证
...全文
104 1 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
1 条回复
切换为时间正序
请发表友善的回复…
发表回复
二月十六 2018-12-13
  • 打赏
  • 举报
回复
感谢分享

56,866

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧