概述
使用MySQL数据库时,单表数据量过大,导致每次使用Navicat右键该表导出数据或使用MySQLdump导出数据花费时间过长(几小时或几天),现探索如何将大数据表快速导出。
后续了解到:mysqldump适合迁移十万或百万级以下数据。
而:若为十万或百万级,则直接查询导出与使用mysqldump区别不大。
另:load data从文件导入比执行sql文件insert语句效率高,适合百万、千万级数据迁移。
单表数据量
方法一:利用索引导出部分数据
根据索引最左原则,直接查询时,应直接指定第一索引,其他索引可按范围指定或直接指定皆可。
本次测试表MV_HIS_DATA1索引为mv_no和data_time,直接指定第一索引,第二索引按范围指定,测试如下:
1、查询数据
可以看到,110万条记录查询耗时1.5秒;
2、导出数据
一路下一步,可以看到,110万条数据写入文件耗时12秒;
3、导入数据
选择刚才导出的文件,一直“下一步”即可。
4、查询数据改良
使用嵌套查询:
select * from MV_HIS_DATA1
where mv_no IN ( select mv_no from MV where rtu_no=1 )
and data_time>'2020-01-01' and data_time<'2022-01-01';
方法一改良
MySQL支持直接将查询结果导出、导入数据文件的命令:into outfile和load data infile。
1、导出数据
100万条记录耗时1.76秒。
select * from MV_HIS_DATA1
where mv_no IN ( select mv_no from MV where rtu_no=1 )
and data_time>'2020-01-01' and data_time<'2022-01-01'
into outfile 'D:/MV_HIS_DATA1_export.txt';
2、导入数据
导入100万条记录数据耗时9.4秒。
load data infile 'D:/MV_HIS_DATA1_export.txt'
into table MV_HIS_DATA1(mv_no,data_time,mv_raw_value,mv_value);
备注
若命令执行失败,则需修改一下配置,windows修改my.ini文件,linux修改/etc/my.cnf文件,最后加一行:
secure_file_priv=''