最近在写一个 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 程序去拼接, 如果数据量较大时, 可以考虑使用 StringBuffer 代替 String, 如果考虑线程安全可以考虑 StringBuilder(或者其他安全的字符串处理类)拼接.
3, 批查询的时候获取的集合数据建议排序, 获取有序数据, 这样便于后续 java 程序的处理.
4, 一般的 ORM 框架都是用的 sql 语句, 而一些 sql 语句的小的细节都能优化, 使用时需要日积月累, 平时应该时刻有优化意识.
5, 使用过 hibernate 应该都知道, hibernate 有缓存功能, 一级二级缓存, 这个思路符合我这篇博客优化思路, 可以提一下, 然后提供继续优化的思路, 对于一些经常操作的数据可以设置高速缓存.
6, 在使用 sql 语句的时候对于经常需要进行 order by 和 group by 的字段 (列) 建立索引, sql 查询避免进行全表扫描, 这些在写 sql 语句时需要注意.
来源: https://www.cnblogs.com/xiaotiaosi/p/8868406.html