检查mysql数据库内存溢出_关于一条sql从mysql或postgresql等数据库中查出大量数据而不形成内存溢出的方法...

场景：咱们在作大量数据导出的时候，单条sql导出几千万级别以上数据容易形成内存溢出，采用limit有时候也不方便，并且若是在数据仓库中作这种操做的时候，复杂sql可能会很慢，这样分红limit后，每条sql都执行几分钟，那效率可也过低了。java

解决关键: jdbc驱动的fetchsize,而后查询使用resultset遍历mysql

1.对于mysql，咱们只须要在driverurl后面加上:useCursorFetch=true&defaultFetchSize=500,开启fetchsize便可，这个会在服务端维护一个游标，分批返回数据到客户端。经本人测试，对mysql服务端性能影响极小，固然你担忧能够从mysql从库上查询。spring

2.对于postgresql，这个须要设置链接的session 的autoCommit = false，指定fetchSize=500； 这个可能没那么简单，若是你使用mybaits或jdbctemplate，你须要对持久化框架session相关源码有必定的了解，并且这个session的autoCommit和fetchsize属性会受到开源数据库链接池的影响。sql

下面给到我在实际开发中使用postgresql，jdbctemplate，druid数据库链接池而构建的查询相关代码。数据库

1.设置autocommit和fetchsizeapache

import com.alibaba.druid.filter.config.ConfigTools;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import org.springframework.jdbc.core.JdbcTemplate;

import java.security.NoSuchAlgorithmException;

import java.security.NoSuchProviderException;

import java.sql.SQLException;

/**

* Created by tanlei on 2018/12/20

* postgresql使用pro数据源的时候指定 ： postgresql的fetchsize可用

* mysql使用pro数据源的时候在url上指定fetchsize可用：jdbc:mysql://localhost:3306/{dbName}?useCursorFetch=true&defaultFetchSize=5000

*/

public class JdbcTemplateUtil {

public static final Logger logger = LogManager.getLogger(JdbcTemplateUtil.class);

/** 若是使用这个数据源，必须关闭自动提交,能够不用恢复 */

public static String PG_DATASOURCE_PRO = "pgDcJdbcTemplatePro";

/** */

public static int DEFAULT_FETCHSIZE = 500;

public static void setPgDataSourceProInfo(JdbcTemplate jdbcTemplate, int fetchSize) {

if (fetchSize <= 0) {

fetchSize = DEFAULT_FETCHSIZE;

}

try {

jdbcTemplate.getDataSource().getConnection().setAutoCommit(false);//不起做用，在datasource加载处设置默认自动提交为false

jdbcTemplate.setFetchSize(fetchSize);

} catch (SQLException e) {

logger.error("关闭postgresql自动提交和设置fetchsize错误：{}", e);

}

}

}

2.spring容器启动加载构建本身特殊的jdbctemplate，这个可让你把链接信息配置到数据库等地方，所谓的spring-cloud配置中心也是相似这样的原理。json

import com.alibaba.druid.pool.DruidDataSource;

import com.alibaba.dubbo.common.json.JSONObject;

import com.alibaba.dubbo.common.json.ParseException;

import com.alibaba.fastjson.JSON;

import org.apache.commons.collections4.CollectionUtils;

import org.apache.commons.lang3.StringUtils;

import org.springframework.beans.BeansException;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.support.BeanDefinitionBuilder;

import org.springframework.beans.factory.support.DefaultListableBeanFactory;

import org.springframework.context.ApplicationContext;

import org.springframework.context.ApplicationContextAware;

import org.springframework.context.ApplicationListener;

import org.springframework.context.ConfigurableApplicationContext;

import org.springframework.context.event.ContextRefreshedEvent;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Component;

import javax.annotation.Resource;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

/**

* Created by tanlei-pc on 2018/5/29.

* 初始化数据中心数据源

*/

@Component

public class DcDataSourceInitEvent implements ApplicationContextAware, ApplicationListener {

private ConfigurableApplicationContext applicationContext;

@Resource

private DataSourceMapper dataSourceMapper;

@Override

public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {

this.applicationContext = (ConfigurableApplicationContext) applicationContext;

}

@Override

public void onApplicationEvent(ContextRefreshedEvent contextRefreshedEvent) {

DataSourceCfg searchParam = new DataSourceCfg();

searchParam.setDisabled(0);

List dsList = dataSourceMapper.getDataSourceCfgList(searchParam);

if (CollectionUtils.isNotEmpty(dsList)) {

DefaultListableBeanFactory beanFactory = (DefaultListableBeanFactory) applicationContext.getBeanFactory();//定义spring相关信息

for (DataSourceCfg dc : dsList) {

String configJson = dc.getConfig();

DruidDataSourceConfig config = JSON.parseObject(configJson, DruidDataSourceConfig.class);

DruidDataSource ds = new DruidDataSource();

ds.setDriverClassName(config.getDriverClassName());

ds.setUrl(config.getUrl());

ds.setUsername(config.getUsername());

ds.setPassword(config.getPassword());

ds.setInitialSize(config.getInitialSize());

ds.setMinIdle(config.getMinIdle());

ds.setMaxActive(config.getMaxActive());

ds.setMaxWait(config.getMaxWait());

ds.setTimeBetweenEvictionRunsMillis(config.getTimeBetweenEvictionRunsMillis());

ds.setMinEvictableIdleTimeMillis(config.getMinEvictableIdleTimeMillis());

ds.setMaxEvictableIdleTimeMillis(config.getMaxEvictableIdleTimeMillis());

ds.setValidationQuery(config.getValidationQuery());

ds.setTestWhileIdle(config.isTestWhileIdle());

ds.setTestOnBorrow(config.isTestOnBorrow());

ds.setTestOnReturn(config.isTestOnReturn());

if (StringUtils.equals(JdbcTemplateUtil.PG_DATASOURCE_PRO, dc.getDataSource())) {//数据中心的读数据源关闭自动提交

ds.setDefaultAutoCommit(false);//关闭自动提交

}

try {

ds.setFilters(config.getFilters());

} catch (SQLException e) {

e.printStackTrace();

}

ds.setConnectionProperties("config.decrypt=true;config.decrypt.key=" + config.getPublicKey());

ds.setConnectionInitSqls(config.getConnectionInitSqls());

JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);

if (StringUtils.equals(JdbcTemplateUtil.PG_DATASOURCE_PRO, dc.getDataSource())) {

JdbcTemplateUtil.setPgDataSourceProInfo(jdbcTemplate, JdbcTemplateUtil.DEFAULT_FETCHSIZE);

}

beanFactory.registerSingleton(dc.getDataSource(), jdbcTemplate);//注册bean

}

}

}

}

/**

* Created by tanlei-pc on 2018/5/29.

*/

public class DataSourceCfg extends Entity {

private String config;

private String dataSource;

private String type;

private Integer disabled;

public String getConfig() {

return config;

}

public void setConfig(String config) {

this.config = config;

}

public String getDataSource() {

return dataSource;

}

public void setDataSource(String dataSource) {

this.dataSource = dataSource;

}

public Integer getDisabled() {

return disabled;

}

public void setDisabled(Integer disabled) {

this.disabled = disabled;

}

public String getType() {

return type;

}

public void setType(String type) {

this.type = type;

}

}

好比我数据库有这样一段配置对应上面的实体类:session

druid的config：app

{

"driverClassName": "org.postgresql.Driver",

"filters": "config",

"initialSize": 1,

"maxActive": 20,

"maxEvictableIdleTimeMillis": 3000000,

"maxWait": 60000,

"minEvictableIdleTimeMillis": 300000,

"minIdle": 3,

"password": "LcVkmR8FwPDUbxGGZredWFOUeMq576214vg5A5Lganl9nOx7IXw8S3t3zNMAG4J7poZ15HqxAfhDPknYUDIkfA==",

"publicKey": "MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAIVMhWXIMXNsdKSGQ5d3AnbVSy3+Uv5HsMIp0lojhxBD9oAyDVOrNi+mqn6O1Rw1TjP7ASjdSHj9v3RBld/hud8CAwEAAQ==",

"testOnBorrow": false,

"testOnReturn": false,

"testWhileIdle": true,

"timeBetweenEvictionRunsMillis": 60000,

"url": "jdbc:postgresql://x.x.x.x:1921/datacenter?reWriteBatchedInserts=true",

"username": "postgres",

"validationQuery": "SELECT 1"

}

data_source：pgDcJdbcTemplate；框架

在你须要使用到这个类的地方:@Resource这个pgDcJdbcTemplate或者是applicationContext.getBean("pgDcJdbcTemplate")便可，灵活性较强。

而后查询相似于:

selectJdbcTemplate.query(selectCoreSql, new RowCallbackHandler() {

@Override

public void processRow(ResultSet resultSet) {

而后在里面resultSet.next遍历便可！