简单的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语句时需要注意。

 

posted on 2018-04-18 15:14  小调~  阅读(4361)  评论(3编辑  收藏  举报

导航