计算机科学

[toc]

最近需要做大数据开发，有多个百万级的数据库，原始文件是单条的insert语句组成的，每条语句执行大约需要0.06sec，如果等数据全部插入，估计电脑都已经开机几个月了。

如何快速的导入数据

分治思想（针对500M以下的数据文件）

使用工具将sql文件进行拆分，拆分成小文件以后，再将单条插入命令整合成一次执行多条插入的语句。初步测试约10W条数据，单条依次插入大概需要2个小时。整合为一次插入后，只需要8sec左右。

先斩后奏思想（针对超大数据文件500M以上）

数据导入

通过load data infile方法将包含insert命令的sql以文本的形式插入到数据库中再进行后续操作

load data infile 'data.sql' into table ${table_name} fields terminated by ',';

${table_name} 替换为tatget table

数据处理部分

  • 处理空白字符(2选1) 
    UPDATE ${table name} SET ${field name}=REPLACE(${field name},' ','');
UPDATE ${table name} SET ${field name}=LTRIM(${field name}) AND ${field name}=RTRIM(${field name});
  • 截取insert部分 
    UPDATE ${table name} SET ${field name}=substring_index(${field name},'(',-1);
  • 截取”);”部分 
    UPDATE ${table name} SET ${field name}=substring_index(${field name},')',1);
  • 去除单引号 
    UPDATE ${table name} SET ${field name}=REPLACE(${field name},'\'','');
  • 可以将所有的update操作合并为一条sql语句进行执行，以便提高效率*

预处理思想

处理数据

通过WINHEX对数据进行第一步处理

为什么选择WinHex?因为打开文件速度快没商量

导入数据

使用load data infile的方法进行数据导入

更换数据库引擎

直接更换Oracle数据库，通过数据泵进行导入。针对不同的开发平台，选择合适自己的就好。

错误及解决方案

  • ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

修改my.ini文件下的secure-file-priv字段为允许导入/导出的路径，重新启动mysql服务。在执行load data infile命令的时候，不论是在windows平台上还是linux平台上，一定要使用反斜杠“/”标识路径，否则同样会导致这个错误。

  • ERROR 1114 (HY000): The table ‘${table_name}’ is full 
    show variables like '%tmp_table%';
show variables like '%max_heap%';
set session tmp_table_size=1024*1024*1024;
set session max_heap_table_size=1024*1024*1024;

官方的错误说明ERROR 1114：B.5.2.11 The table is full

  • ERROR 1206 (HY000): The total number of locks exceeds the lock table size

查看参考资料

  • ERROR 1262 (01000): Row 6737 was truncated; it contained more data than there were input columns.

把 strict_trans_tables 从 sql_mode 中去掉，再次执行 MySQL Load data

  • mysql数据库版本的选择

mysql数据库应该选择使用64位数据库，如果使用32位的数据库将导致数据文件无法创建完成！

示例代码

参考资料