JAVA MYSQL sql_calc_found_rows和found_rows()实践

一、背景  1.百万级数据库，数据量持续增加。每张数据表的字段数大于50（时间字段，分组字段，指标字段）  2.JDBCTemplate，java，mysql二、问题描述  通过分析接口返回数据响应时

一、背景

  1.百万级数据库，数据量持续增加。每张数据表的字段数大于50（时间字段，分组字段，指标字段）

  2.JDBCTemplate，java，mysql

二、问题描述

  通过分析接口返回数据响应时间过长（通过某个分组字段搜索数据，响应时间长达30s）。

三、检查问题

  检查代码，发现代码中运行了两句SQL语句，一句通过select查询数据，一句通过select count(1)来获取返回数据的总条数。

  通过navicat查询语句对应的执行时间。

SELECT eventtime,smart_card_id,uevt_1000 FROM analytics_vhsession_user_event_info_day_201901 WHERE eventtime>='2019-01-01 00:00:00' AND eventtime<'2019-01-31 23:59:59' AND smart_card_id = '0382205801' ORDER BY eventtime asc LIMIT 0,1000> OK> 时间: 10.603sSELECT count(1) FROM analytics_vhsession_user_event_info_day_201901 WHERE eventtime>='2019-01-01 00:00:00' AND eventtime<'2019-01-31 23:59:59' AND smart_card_id = '0382205801'> OK> 时间: 11.13s

  同样耗时10s+，所以想办法从select count(1)入手，减少SQL执行时间以达到减少响应时间的目的。

四、查询资料

  通过查询资料，可以通过使用sql_calc_found_rows和found_rows()替代select count(1)。

  通过navicat查询语句对应的执行时间。

SELECT sql_calc_found_rows eventtime,smart_card_id,uevt_1000 FROM analytics_vhsession_user_event_info_day_201901 WHERE eventtime>='2019-01-01 00:00:00' AND eventtime<'2019-01-31 23:59:59' AND smart_card_id = '0382205801' ORDER BY eventtime asc LIMIT 0,1000> OK> 时间: 11.606sSELECT FOUND_ROWS()> OK> 时间: 0.004s

  相较之前的方案，响应时间可以减少10s以上，是一个值得尝试的方案。

五、优化尝试

  根据之前的测试结果尝试进行代码优化，使用jdbcTemplate来调用两次query(),一次获取数据，一次获取对应的总条数。

//select sql_calc_found_rowsString selectSQL = "select sql_calc_found_rows ...";List<Map<String, Object>> data = jdbcTemplate.queryForList(selectSQL);//select found_rows()String selectTotalCountSQL = "select found_rows()";Long totalCount = jdbcTemplate.queryForObject(selectTotalCountSQL, Long.class);

  但是在实际测试中遇到了jdbcTemplate.query("select found_rows()")返回的总条数与实际的总条数不一致的情况。通过查询相应的资料，在一篇分享文档发现一点端倪，以下为资料原文：

we do this by opening a connection, running two SELECT queries, then closing the connection. This allows us to achieve the desired result that we need.

  sql_calc_found_rows和found_rows()需要两句SQL在同一会话中，才能保证select found_rows()返回的总条数是上一句select sql_calc_found_rows对应的总条数

  查看jdbcTemplate.query()底层代码实现。

public <T> T execute(StatementCallback<T> action) throws DataAccessException {	Assert.notNull(action, "Callback object must not be null");	Connection con = DataSourceUtils.getConnection(getDataSource());	Statement stmt = null;	try {		Connection conToUse = con;		if (this.nativeJdbcExtractor != null &&			this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativeStatements()) {			conToUse = this.nativeJdbcExtractor.getNativeConnection(con);		}		stmt = conToUse.createStatement();		applyStatementSettings(stmt);		Statement stmtToUse = stmt;		if (this.nativeJdbcExtractor != null) {			stmtToUse = this.nativeJdbcExtractor.getNativeStatement(stmt);		}		T result = action.doInStatement(stmtToUse);		handleWarnings(stmt);		return result;	}	catch (SQLException ex) {		// Release Connection early, to avoid potential connection pool deadlock		// in the case when the exception translator hasn't been initialized yet.		JdbcUtils.closeStatement(stmt);		stmt = null;		DataSourceUtils.releaseConnection(con, getDataSource());		con = null;		throw getExceptionTranslator().translate("StatementCallback", getSql(action), ex);	}	finally {		JdbcUtils.closeStatement(stmt);		DataSourceUtils.releaseConnection(con, getDataSource());	}}

  jdbcTemplate每次执行query()都会从连接池中获取连接

Connection con = DataSourceUtils.getConnection(getDataSource())

  执行完成后释放连接

DataSourceUtils.releaseConnection(con, getDataSource());

  不能保证两次query()在一个会话中(同一个Connection)。

六、优化实践

  优化方案：不使用JDBCTemplate中的query()方法，自己实现具体逻辑。通过DataSourceUtils.getConnection(jdbcTemplate.getDataSource())获取会话，使用Statement来执行两次SQL后，再通过DataSourceUtils.releaseConnection(conn, jdbcTemplate.getDataSource());释放会话，保证两句SQL在同一会话中。

public PagedArrayList getDataAndTotalCount(String sql){	Connection conn = null;	Statement statement = null;	ResultSet rs = null;	ResultSet rs1 = null;	long totalCount = 0L;	PagedArrayList data = new PagedArrayList();	try {		conn = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());		conn.setAutoCommit(true);		statement = conn.createStatement();		rs = statement.executeQuery(sql);		ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据		int columnCount = md.getColumnCount();   //获得列数		while (rs.next()) {			Map<String,Object> rowData = new HashMap<String,Object>();			for (int i = 1; i <= columnCount; i++) {				rowData.put(md.getColumnName(i), rs.getObject(i));			}			data.add(rowData);		}		String totalCountSQL = "select found_rows() AS total_count";		rs1 = statement.executeQuery(totalCountSQL);		while (rs1.next()){			totalCount = rs1.getLong("total_count");		}		data.totalCount = totalCount;	} catch (Exception e) {		slf4jLogger.error("getDataAndTotalCount() error:", e);	} finally {		//关闭资源		JdbcUtils.closeResultSet(rs);		JdbcUtils.closeResultSet(rs1);		JdbcUtils.closeStatement(statement);		//释放资源		DataSourceUtils.releaseConnection(conn, jdbcTemplate.getDataSource());	}	return data;}

七、参考文档

https://www.contradodigital.com/2018/01/06/how-to-use-sql_calc_found_rows-and-found_rows-with-limit-and-offset-in-a-mysql-query-using-java-and-jdbc/