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

场景:我们在做大量数据导出的时候,单条sql导出几千万级别以上数据容易造成内存溢出,采用limit有时候也不方便,而且如果在数据仓库中做这种操作的时候,复杂sql可能会很慢,这样分成limit后,每条sql都执行几分钟,那效率可也太低了。

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

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

2.对于postgresql,这个需要设置连接的session 的autoCommit = false,指定fetchSize=500; 这个可能没那么简单,如果你使用mybaits或jdbctemplate,你需要对持久化框架session相关源码有一定的了解,而且这个session的autoCommit和fetchsize属性会受到开源数据库连接池的影响。

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

1.设置autocommit和fetchsize

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配置中心也是类似这样的原理。

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<ContextRefreshedEvent> {

    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<DataSourceCfg> 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;
    }
}

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

druid的config:

{
	"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遍历即可!

  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
不同的数据库管理系统对于命名的支持不同,其是否支持使用`-`作为命名的一部分也有所不同。 - Presto: Presto支持使用`-`作为命名的一部分,但是建议遵循以下规则:表名和列名只能包含字母(a-z,A-Z)、数字(0-9)和下划线(_),第一个字符必须是字母或下划线。 - ClickHouse: ClickHouse支持使用`-`作为命名的一部分,但是建议遵循以下规则:表名和列名只能包含字母(a-z,A-Z)、数字(0-9)和下划线(_),第一个字符必须是字母或下划线。 - HBase: HBase不支持使用`-`作为命名的一部分,建议使用字母、数字和下划线来命名表名和列名。 - Apache Hudi: Apache Hudi支持使用`-`作为命名的一部分,但是建议遵循以下规则:表名和列名只能包含字母(a-z,A-Z)、数字(0-9)和下划线(_),第一个字符必须是字母或下划线。 - MySQL: MySQL支持使用`-`作为命名的一部分,但是建议遵循以下规则:表名和列名只能包含字母(a-z,A-Z)、数字(0-9)和下划线(_),第一个字符必须是字母或下划线。 - Oracle: Oracle支持使用`-`作为命名的一部分,但是建议遵循以下规则:表名和列名只能包含字母(a-z,A-Z)、数字(0-9)和下划线(_),第一个字符必须是字母。 - SQL Server: SQL Server支持使用`-`作为命名的一部分,但是建议遵循以下规则:表名和列名只能包含字母(a-z,A-Z)、数字(0-9)和下划线(_),第一个字符必须是字母或下划线。 - PostgreSQL: PostgreSQL支持使用`-`作为命名的一部分,但是建议遵循以下规则:表名和列名只能包含字母(a-z,A-Z)、数字(0-9)和下划线(_),第一个字符必须是字母或下划线。 总的来说,建议使用字母、数字和下划线来命名表名和列名,以避免在不同的数据库管理系统出现命名问题。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值