MySQL快速导入千万条数据(1)

2022年2月25日 888点热度 1人点赞 0条评论

对于传统的关系数据库如oracle,在大量数据导入方面的效率,我们一般有一个大概的认知,即1分钟以内可以导入千万条数据,而对于MySQL数据库,普遍观点以为性能相对较差,尤其时对于千万级别的数据量,几十分钟、几个小时,都是可能的。是否如此,本文会给出答案
在普遍去IOE的今天,最难的去O也已经势在必行,所以探讨测试一下MySQL的大数据量导入非常有必要。事实上我们的各个新建项目由于采用了MySQL数据库,在备份恢复时,便会面临大量数据的逻辑导出与导入需求。
恰好笔者手头有一个3000多万行的数据记录,SQL文本格式如下:

DROP TABLE IF EXISTS `tablename`;
CREATE TABLE `tablename` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(100) DEFAULT NULL,
  `init_value` text,
  `master_id` int(11) DEFAULT NULL,
  `code_id` varchar(11) DEFAULT NULL,
  `end_value` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=137259138 DEFAULT CHARSET=utf8;
INSERT INTO `tablename` VALUES ('40601438', 'CF_0105', '121589425857.3000', '56814', null, '121589425857.3000');
INSERT INTO `tablename` VALUES ('40601439', 'CF_0105', '113776588.1400', '56815', null, '113776588.1400');
INSERT INTO `tablename` VALUES ('40601440', 'CF_0105', '357661339.7200', '56816', null, '357661339.7200');
...后面都是INSERT语句

一、命令行导入方式

首先使用最原始的命令行方式测试,如下:
mysql -uroot -ppasswd' --default-character-set=utf8 dbname < mysql.sql
为了提高插入效率,去掉索引,改为批量commit提交,此处特意编写了一个脚本,用于修改原SQL文件,如下:

cat > modify_file.sh << EOF
#!/bin/sh
filename=\$1 # 定义文件名字
var1="COMMIT;" # 定义变量
var2="START TRANSACTION;" # 定义变量
sed -i ''\$2' i\'"\$var2" \$filename # 第n行前添加"START TRANSACTION;"
cat \$filename|awk 'NR%1000==0{printf("%09d\n", NR)}'|while read line #每隔1000行获取行号 
do
  echo "\$line"
  sed -i ''\$line'a '"\$var1""\n""\$var2"'' \$filename # 向文件插入两行,分别是"COMMIT;"和"START TRANSACTION;"
done
sed -i '\$a\'"\$var1" \$filename # 最后1行添加"COMMIT;"
EOF

一个简单的导入脚本,用于记录时间:

vim dumpin.sh
echo "Start ...("`date "+%Y%m%d-%H:%M:%S"`")"
mysql -uroot -p'passwd' --default-character-set=utf8 dbname < $1
echo "Completed.("`date "+%Y%m%d-%H:%M:%S"`")"

chmod +x dumpin.sh

取出前50万行:
head -500000 mysql.sql > mysql2.sql
修改文件内容,加入批量提交语句:
./modify_file.sh mysql2.sql 35
执行导入:

logfile=dumpin.log_"`date "+%Y%m%d_%H-%M"`"
./dumpin.sh mysql2.sql > $logfile 2>&1 &

测试结果如下:
去索引,每1000条批量提交,50万行耗时9分钟

Start ...(20220224-21:49:58)
Completed.(20220224-21:58:17)

去索引,逐行提交,50万行耗时19分钟

Start ...(20220224-22:14:13)
Completed.(20220224-22:33:37)

可见这个命令行的导入方式,时间太长,几乎无法接受,也没有太大的提升空间。

二、LOAD DATA导入方式

关于LOAD DATA的详细介绍请网搜,此处不再赘述。
首先,修改原SQL文件格式为LOADDATA可用的csv文本格式,此处先用前500万行测试:

head -5000000 mysql.sql > mysql2.sql
sed -i "s/INSERT INTO \`tablename\` VALUES (//g" mysql2.sql
sed -i "s/);//g" mysql2.sql

经过以上自动编辑处理,原SQL文件内容成为如下格式:

'40601438', 'CF_0105', '121589425857.3000', '56814', null, '121589425857.3000'
'40601439', 'CF_0105', '113776588.1400', '56815', null, '113776588.1400'

然后,执行导入,如下所示:

mysql -uroot -p'passwd' dbname --local-infile
LOAD DATA LOCAL INFILE '/root/mysql2.sql'
INTO TABLE tablename
FIELDS TERMINATED BY ', '
ENCLOSED BY "'"
LINES TERMINATED BY '\n'
IGNORE 34 lines;

期间遭遇3948、2068错误,自行网搜修改配置即可解决,最终测试结果如下:
500万行,有2索引导入耗时:3 min 35.32 sec
500万行,无 索引导入耗时:3 min 5.99 sec

可见百万行级别load时少数索引影响不大,基本是数分钟内可以导入百万条记录
必须说明,由于这是在笔记本电脑虚机的测试结果,相信生产环境会快很多。

好,现在你还会说,MySQL数据库大批量数据导入性能较差吗?

下一步继续测试这3000万条数据全部导入的情况。

liking

我是雪人

文章评论