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

2022年2月25日 1515点热度 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万条数据全部导入的情况。