昨天下载了一个全国地名数据库，解压得到的.sql文件的文件大小达到200MB，即下图的position.sql。
![]()
总共有138万多行的语句，其中大部分都是insert语句，每个insert语句插入一行数据。导入SQLyog中运行后，发现迟迟没有结束。无奈只好让电脑继续运行，我先睡觉去了。今天醒来一看，我的乖乖，用了4个多小时！
![]()
我认为，每个SQL语句都是有固定开销的，比如使用select语句时，就算再简单的语句，需要的时间也是毫秒级，但是当获取的数据变大时，需要的时间并没有显著增加。这次执行这个SQL文件用了这么长时间，肯定是因为语句太多了，很多时间都花在了固定开销上。于是我觉得应该在执行SQL文件前对这个文件作一些处理，合并掉那些重复的insert，让一个insert插入多条数据，这样应该能让执行的时间缩短一点。
合并的方法并不难，比如下面这3个SQL语句：
INSERT INTO `j_position_village` VALUES ('668387', '659004003000', '659004003002', '东城社区居民委员会');
INSERT INTO `j_position_village` VALUES ('668388', '659004003000', '659004003003', '龙泉社区居民委员会');
INSERT INTO `j_position_village` VALUES ('668389', '659004003000', '659004003004', '北海东街社区居民委员会');
只要把末尾的分号去掉，把后面的insert语句的values和之前的内容去掉，加个分号接起来就行了，像这样：
INSERT INTO `j_position_village` VALUES ('668387', '659004003000', '659004003002', '东城社区居民委员会'),('668388', '659004003000', '659004003003', '龙泉社区居民委员会'),('668389', '659004003000', '659004003004', '北海东街社区居民委员会');
那么应该合并到多长呢？考虑到很多文本编辑器允许的最大行长度是65536，那么就用65536吧。写好程序后一运行，哇，200MB的文件，读取+处理+写入硬盘，居然只用了1.7秒不到！
![]()
这么长的文本的处理，我以为会花很长时间的，没想到会这么快！
好了，既然已经处理好SQL语句了，那么就运行看看吧。新建了一个数据库address，导入SQLyog，一切正常，行的长度并没有超标，心想“可以可以”。然后开始执行。在程序运行十几秒后，我觉得这一时半会的估计也没法结束，要不先买个菜去？正在准备的时候，SQLyog的“信息”窗口跳出了时间信息。嗯？怎么情况？我赶紧使用Ctrl+end跳到末尾看看，发现SQL文件末尾的id已经显示在上面了。难道已经好了？我不大相信，又打开idea，count一下行数看看，发现确实是执行完了。卧槽，卧槽，我忍不住直呼，怎么会那么快，1分02秒！
![]()
简直是不敢相信。之前的SQL文件需要4个多小时才执行完，这次只要1分钟！我又细看了一下每个语句的运行时间，发现确实如我想的，实际插入的实际其实很短，很多大部分时间都花在了固定开销上。
查询：INSERT INTO `j_position_village` VALUES ('664076', '653129209000', '653129209220', '苏巴斯提村委会'),('664077', '65312920...
共 1209 行受到影响
执行耗时 : 0.018 sec
传送时间 : 0.006 sec
总耗时 : 0.024 sec
--------------------------------------------------
如上，实际插入了1200多行语句，但是时间只比原来插入1条语句的时候多1倍！这效率真是太恐怖了！完了我一边感叹一边买菜去了。买菜的时候我一边想，如果每一行的内容再增加会怎样？我网上查了一下，MySQL中可以在配置文件中设置一个参数max_allowed_packet，设定每行允许接收的内容的大小，默认值是1024KB。我心想我只设了65536，也就是64KB，那么是绰绰有余的。于是我将容量改成1024KB后再运行了一下，发现SQL文件比原来的小了一点点。导入SQLyog后，居然还能正常显示！厉害了我的SQLyog！然后执行。然而这次似乎没有那么顺利，执行开始后，我发现时间显示不大正常，估计最后的总时间会少很多。于是赶紧掏出手机进行计时，这时大约10秒已经过去了。当手机计时到37秒时，SQLyog停了。我看了看执行记录，果然没能正常执行，出错了。但是看起来速度应该也不会再有显著提高了，我想就这样吧。今后对数据进行备份后的再恢复时，可以想这样预先处理一下，这样速度真的是快太多了。更深层次的内容，我现在应该没有必要考虑（即便考虑了也暂时用不到）。
以下为SQL文件处理程序的源代码，逻辑很简单，只对insert语句进行合并。为保险起见，对表名进行了判断，如果表名不相符，那么肯定不能合并。但是数据库dump出来的SQL应该也不会出现这种情况。这里唯一需要注意的就是，我没有对一行里包含多个SQL语句的情况进行判断，也没有对一个insert语句是否插入了多个记录作判断。默认的情况就是一行一条insert语句，一个insert语句插入一条记录。（如果一个insert已经插入多条记录了，那么也不大需要我这个程序了）
import java.io.*;
public class Test {
public static void main(String[] args) throws IOException {
long start = System.currentTimeMillis();
BufferedReader reader = new BufferedReader(new FileReader("C:\\Users\\Jien217\\Desktop\\全国地名数据库\\position.sql"));
BufferedWriter writer = new BufferedWriter(new FileWriter("C:\\Users\\Jien217\\Desktop\\全国地名数据库\\position-simple.sql"));
String tableName = null;
String lineSrc;
String line;
int maxLineLength = 65536;
StringBuilder builder = new StringBuilder(maxLineLength);
int count = 0;
while ((lineSrc = reader.readLine()) != null) {
count++;
if (count % 100000 == 0) {
System.out.println("已处理 " + count + " 行");
}
line = lineSrc.trim();
//判断是否为插入语句。如果不是，那么原样输出到文件
if (!line.startsWith("INSERT INTO")) {
if (builder.length() > 0) {
writer.write(builder.toString());
writer.write(";");
writer.newLine();
writer.write(line);
writer.newLine();
builder.delete(0, builder.length());
} else {
writer.write(line);
writer.newLine();
}
continue;
}
if (tableName == null) {
tableName = line.substring(11, line.indexOf("VALUES")).trim(); //获取表名，确保表名正确
}
//如果表名不一致，那么不能合并。输出先前的内容后输出当前行
if (!tableName.equals(line.substring(11, line.indexOf("VALUES")).trim())) {
if (builder.length() > 0) {
writer.write(builder.toString());
writer.write(";");
writer.newLine();
writer.write(line);
writer.newLine();
builder.delete(0, builder.length()); //输出后清空 builder
} else {
writer.write(line);
writer.newLine();
}
tableName = line.substring(11, line.indexOf("VALUES")).trim(); //表名为新行的表名
continue;
}
//到这里，就是表名一致的 insert 语句了，可以进行拼接
if (builder.length() + line.length() < maxLineLength) { //如果行过长，那么就不要拼接了，否则可能出错
if (builder.length() == 0) { //builder 长度为 0，那么就使用这行的开头
builder.append(line.substring(0, line.length() - 1));
} else {
String data = line.substring(line.indexOf("VALUES") + 7, line.length() - 1);//否则只获取数据
builder.append(",");
builder.append(data);
}
} else {
writer.write(builder.toString());
writer.write(";");
writer.newLine();
builder.delete(0, builder.length()); //清空 builder
builder.append(line.substring(0, line.length() - 1));
}
}
if (builder.length() > 0) {
writer.write(builder.toString());
writer.write(";");
writer.newLine();
}
writer.flush(); //最后需要 flush，否则文件末尾的内容可能不会被输出
System.out.println("数据处理完毕！用时：" + (System.currentTimeMillis() - start) / 1000.0 + " 秒");
}
}
|
