确认过的眼神,简单的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语句时需要注意。

  • 发表于:
  • 原文链接http://kuaibao.qq.com/s/20180420A0TVRJ00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券