简单的sql调优（批处理）
文章出处：小调~---原文地址
最近在写一个java的爬虫程序时，遇到了一个大量数据进行插入更新和大量数据循环查询的问题，所以查了一下一般的调优的方式，下面主要介绍我采取的调优措施。
一 、调优思路
先说说我采取方式的调优的思路，这样便于理解我的选取的调优策略。
思路分析
首先我们都知道计算机存储空间分为：寄存器、高速缓存、内存、交换区(外部存储虚拟化)、硬盘以及其他的外部存储。而且我们都知道从寄存器开始到硬盘读写速度是从快到慢依次递减。我们访问数据库，一般是通过运行的代码去访问数据库，运行起来的代码所需要的数据一般会放在内存或者是在高速缓存中，而数据库数据存放在哪？很多人会说应该存放在电脑硬盘中，但是这个只回答对了一半。个人开发，代码和数据库在同一个电脑上，但是如果是团队开发喃？明显存放在个人的电脑上不合适，一般会存放在团队开发的服务器上硬盘上。团队开发时，将服务器硬盘上的数据读到自己开发电脑的内存中（自己开发测试时）或者上线后从一个数据库服务器硬盘读到上线服务器内存（数据库和程序不在一个服务器上），加上数据表查询和查询交互的一些准备（包括一些初始化）所需要的时间将会很多。
最简朴的sql插入、更新和查询一般程序一条一条的链接数据库进行操作，这样耗费的时间非常恐怖。
由此引出我们调优的想法，减少与数据库交互的次数，将多条查询，多条插入，多条更新合并为交互一次，也就是批操作。这样会减少很多时间。多次处理的操作交给java程序在内存中进行处理，内存中处理的速度要快上很多。
二、插入的优化（批插入）
将插入语句进行拼接，多条插入语句拼接成一条插入语句，与数据库交互一次执行一次。
使用insert into tableName values(),(),(),()语句进行拼接然后再一次性插入。
如果字符串太长，则需要配置下MYSQL，在mysql 命令行中运行 ：set global max_allowed_packet = 2*1024*1024*10
我插入1000条的数据耗时为毫秒级别，效率提高很多。
1、下面是代码可以便于理解：
$sql= "insert into twenty_million (value) values";for($i=0;$i<2000000;$i++){
$sql.="('50'),";
};
$sql = substr($sql,0,strlen($sql)-1);
$connect_mysql->query($sql);
2、我是用java写的代码，用的是spring带的JdbcDaoSupport类写的dao层，所以粘一下代码
public void batchInsert(List<SpdrGoldEtfPostions> spdrGoldEtfPostionsList) { int size = spdrGoldEtfPostionsList.size();
String sql = "insert into " + TABLE_NAME + "(" + COLUMN_WITHOUT_ID + ") values";
StringBuffer sbf = new StringBuffer(sql); for (int i = 0; i < size - 1; i++) {
sbf.append("('").append(spdrGoldEtfPostionsList.get(i).getSpdrEftId()).append("','")
.append(spdrGoldEtfPostionsList.get(i).getSpdrEftDate())
.append("',");
sbf.append(spdrGoldEtfPostionsList.get(i).getTotalNetAssetValue()).append("),");
}
sbf.append("('").append(spdrGoldEtfPostionsList.get(size - 1).getSpdrEftId()).append("','")
.append(spdrGoldEtfPostionsList.get(size - 1).getSpdrEftDate())
.append("',");
sbf.append(spdrGoldEtfPostionsList.get(size - 1).getTotalNetAssetValue()).append(")");
sql = sbf.toString(); this.getJdbcTemplate().update(sql);
}
三、更新优化（批更新）
将更新语句进行拼接，多条更新语句拼接成一条更新语句，与数据库交互一次执行一次。
1、下面是sql语句的批更新语句，提供便于理解
UPDATE book SET Author = CASE id WHEN 1 THEN '黄飞鸿' WHEN 2 THEN '方世玉' WHEN 3 THEN '洪熙官' END WHERE id IN (1,2,3)
2、下面java写的spring带的JdbcDaoSupport类写的dao层的批更新语句
public void batchUpdateBySpdrEftDate(List<SpdrGoldEtfPostions> spdrGoldEtfList) { int size = spdrGoldEtfList.size();
String sql = "UPDATE " + TABLE_NAME + " set total_net_asset_value = CASE spdr_eft_date\n";
StringBuffer sbf = new StringBuffer(sql); for (int i = 0; i < size; i++) {
sbf.append("WHEN ").append(spdrGoldEtfList.get(i).getSpdrEftDate()).append(" THEN ")
.append(spdrGoldEtfList.get(i).getTotalNetAssetValue()).append("\n");
}
sbf.append("END\n").append("WHERE spdr_eft_date IN("); for (int i = 0; i < size - 1; i++) {
sbf.append(spdrGoldEtfList.get(i).getSpdrEftDate()).append(",");
}
sbf.append(spdrGoldEtfList.get(size - 1).getSpdrEftDate()).append(")");
sql = sbf.toString(); this.getJdbcTemplate().update(sql);
}
四、查询优化（批量查询）
将所有的查询都合并为一条查询语句，然后返回一个集合，然后处理集合（最好返回的集合是有序的，这样处理起来比较的方便，在sql语句中可以用order by 或者group by进行排序分类，顺便多说一句，使用order by 和group by 的字段最好建立索引，这样速度更快）
1、首先写一下sql语句，便于大家理解
select * from tableName where id in (1,2,3,4) order by id
2、下面java写的spring带的JdbcDaoSupport类写的dao层的批查询语句
public List<SpdrGoldEtfPostions> batchSelectBySpdrEtfDate(String[] spdrEtfDateArray) {
String sql = "select * from " + TABLE_NAME;
StringBuffer sbf = new StringBuffer(sql);
sbf.append(" where spdr_eft_date IN("); for (int i = 0; i < spdrEtfDateArray.length - 1; i++) {
sbf.append(spdrEtfDateArray[i]).append(",");
}
sbf.append(spdrEtfDateArray[spdrEtfDateArray.length - 1]).append(")").append(" ORDER BY spdr_eft_date");
sql = sbf.toString();
List<SpdrGoldEtfPostions> items = this.getJdbcTemplate().query(sql, rowMapper()); return items;
}
当然批量查询你可以改变where后面的限定语句，也可以实现批量查询，如where id <100 and id>10（这里id<100写在前面也是优化的思路，这天语句在执行时，会先将范围控制在100以内，然后在从99给数据中进行查询限定，这也是优化，所以说，很多小细节都能体现优化）,类似这类的也可以实现批量查询，根据需要改变限定条件实现批量查询。
五、删除的优化（批量删除）
其实看完了批量查询的话，就可以得到一些关于sql批量删除的想法了，无非是限定条件上动点手脚。
1、先给一下sql语句便于理解
delete from tableName where id in(1,2,3,4,5,6)
2、下面java写的spring带的JdbcDaoSupport类写的dao层的批删除语句
public void batchDeleteBySpdrEtfDate(String[] spdrEtfDateArray) {
String sql = "delete from " + TABLE_NAME;
StringBuffer sbf = new StringBuffer(sql);
sbf.append(" where spdr_eft_date IN("); for (int i = 0; i < spdrEtfDateArray.length - 1; i++) {
sbf.append(spdrEtfDateArray[i]).append(",");
}
sbf.append(spdrEtfDateArray[spdrEtfDateArray.length - 1]).append(")");
sql = sbf.toString(); this.getJdbcTemplate().update(sql);
}
和查询同样道理的，可以通过设定where后面的限定，来实现其他的类批删除。
六、总结
1、首先，数据量较大的sql优化，采取的是批处理操作，减少与数据库的交互次数。
2、批处理的sql语句交给java程序去拼接，如果数据量较大时，可以考虑使用StringBuilder代替String，如果考虑线程安全可以考虑StringBuffer（或者其他安全的字符串处理类）拼接。
3、批查询的时候获取的集合数据建议排序，获取有序数据，这样便于后续java程序的处理。
4、一般的ORM框架都是用的sql语句，而一些sql语句的小的细节都能优化，使用时需要日积月累，平时应该时刻有优化意识。
5、使用过hibernate应该都知道，hibernate有缓存功能，一级二级缓存，这个思路符合我这篇博客优化思路，可以提一下，然后提供继续优化的思路，对于一些经常操作的数据可以设置高速缓存。
6、在使用sql语句的时候对于经常需要进行order by和group by的字段(列)建立索引，sql查询避免进行全表扫描，这些在写sql语句时需要注意。
随时随地看视频
