确认过的眼神，简单的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;$iquery($sql);

2、我是用java写的代码，用的是spring带的JdbcDaoSupport类写的dao层，所以粘一下代码

public void batchInsert(List 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

三、更新优化（批更新）

将更新语句进行拼接，多条更新语句拼接成一条更新语句，与数据库交互一次执行一次。

1、下面是sql语句的批更新语句，提供便于理解

UPDATE book SET Author = CASE id WHEN 1 THEN '黄飞鸿'WHEN 2 THEN '方世玉'WHEN 3 THEN '洪熙官'ENDWHERE id IN (1,2,3)

2、下面java写的spring带的JdbcDaoSupport类写的dao层的批更新语句

public void batchUpdateBySpdrEftDate(List 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

四、查询优化（批量查询）

将所有的查询都合并为一条查询语句，然后返回一个集合，然后处理集合（最好返回的集合是有序的，这样处理起来比较的方便，在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 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 items = this.getJdbcTemplate().query(sql, rowMapper()); return items; }

当然批量查询你可以改变where后面的限定语句，也可以实现批量查询，如where id 10（这里id

五、删除的优化（批量删除）

其实看完了批量查询的话，就可以得到一些关于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

和查询同样道理的，可以通过设定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语句时需要注意。