java-db 进行 SQL 统计

tio-boot官网

SQL 统计

简介

Java db 内置了 LiteSqlStatementStat 用于进行 SQL 统计

使用

使用 LiteSqlStatementStat 进行 SQL 统计

本文介绍了如何在 Java 应用中使用 LiteSqlStatementStat 进行 SQL 统计，并展示了相关配置、测试和输出结果。

1. 配置类 (DbConfig)

首先，创建一个配置类 DbConfig，用于配置数据库连接和 ActiveRecordPlugin。以下是该类的关键步骤：

• 数据源配置：通过 EnvUtils.get("DATABASE_DSN") 获取数据库连接信息，并使用 HikariCP 进行数据源配置。数据源通过 DsContainer.setDataSource(hikariDataSource) 进行设置，并在应用关闭时调用 hikariDataSource::close 方法进行清理。
• ActiveRecordPlugin 配置：创建 ActiveRecordPlugin 实例，并根据环境配置开发模式和是否显示 SQL 语句。关键部分是配置 LiteSqlStatementStat，通过调用 arp.setSqlStatementStat(new LiteSqlStatementStat(), false)，启用 SQL 语句统计功能。

import javax.sql.DataSource;

import com.jfinal.template.Engine;
import com.jfinal.template.source.ClassPathSourceFactory;
import com.litongjava.db.activerecord.ActiveRecordPlugin;
import com.litongjava.db.activerecord.OrderedFieldContainerFactory;
import com.litongjava.db.activerecord.dialect.PostgreSqlDialect;
import com.litongjava.db.activerecord.stat.LiteSqlStatementStat;
import com.litongjava.db.hikaricp.DsContainer;
import com.litongjava.template.SqlTemplates;
import com.litongjava.tio.boot.server.TioBootServer;
import com.litongjava.tio.utils.dsn.DbDSNParser;
import com.litongjava.tio.utils.dsn.JdbcInfo;
import com.litongjava.tio.utils.environment.EnvUtils;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class DbConfig {

  public DataSource dataSource() {
    String dsn = EnvUtils.get("DATABASE_DSN");
    if (dsn == null) {
      return null;
    }

    JdbcInfo jdbc = new DbDSNParser().parse(dsn);
    int maximumPoolSize = EnvUtils.getInt("jdbc.MaximumPoolSize", 5);

    if (EnvUtils.isDev()) {
      maximumPoolSize = 2;
    }

    HikariConfig config = new HikariConfig();
    // config
    config.setJdbcUrl(jdbc.getUrl());
    config.setUsername(jdbc.getUser());
    config.setPassword(jdbc.getPswd());
    config.setMaximumPoolSize(maximumPoolSize);
    HikariDataSource hikariDataSource = null;
    try {
      hikariDataSource = new HikariDataSource(config);
    } catch (Exception e) {
      log.error("dsn:{}", dsn);
      return null;
    }

    // set datasource
    DsContainer.setDataSource(hikariDataSource);
    // add destroy
    TioBootServer.me().addDestroyMethod(hikariDataSource::close);
    return hikariDataSource;
  }
  /*
   *
   * config ActiveRecordPlugin
   */

  public void config() {
    // get dataSource
    DataSource dataSource = dataSource();
    if (dataSource == null) {
      return;
    }
    // create arp
    ActiveRecordPlugin arp = new ActiveRecordPlugin(dataSource);

    if (EnvUtils.isDev()) {
      arp.setDevMode(true);

    }

    boolean showSql = EnvUtils.getBoolean("jdbc.showSql", false);
    log.info("show sql:{}", showSql);
    arp.setShowSql(showSql);
    arp.setDialect(new PostgreSqlDialect());
    arp.setContainerFactory(new OrderedFieldContainerFactory());
    arp.setSqlStatementStat(new LiteSqlStatementStat(),false);

    // config engine
    Engine engine = arp.getEngine();
    engine.setSourceFactory(new ClassPathSourceFactory());
    engine.setCompressorOn(' ');
    engine.setCompressorOn('\n');
    // add sql file
    // arp.addSqlTemplate("/sql/all_sqls.sql");
    // start
    arp.start();
    // add stop
    SqlTemplates.load("sql-templates/main.sql");
    TioBootServer.me().addDestroyMethod(arp::stop);
  }
}

2. 测试类 (LiteSqlStatementStatTest)

为了验证 SQL 统计功能，创建一个简单的测试类 LiteSqlStatementStatTest，执行 SQL 查询并打印统计结果：

• 使用 Db.find(sql) 执行 SQL 查询。
• 调用 Lite.querySqlStatementStats() 获取 SQL 统计信息，并以 JSON 格式输出。

import java.util.List;
import java.util.Map;

import org.junit.Test;

import com.litongjava.db.activerecord.Db;
import com.litongjava.db.activerecord.Record;
import com.litongjava.lite.Lite;
import com.litongjava.open.chat.config.DbConfig;
import com.litongjava.tio.utils.environment.EnvUtils;
import com.litongjava.tio.utils.json.JsonUtils;

public class LiteSqlStatementStatTest {

  @Test
  public void test() {
    try {
      EnvUtils.load();
      new DbConfig().config();

      // Define the SQL query
      String sql = "select * from rumi_sjsu_class_schedule_2024_fall";

      // Execute the query and save the data asynchronously
      List<Record> find = Db.find(sql);
      System.out.println(find.size());

      // Now query the saved SQL statement statistics
      List<Map<String, Object>> querySqlStatementStats = Lite.querySqlStatementStats();
      System.out.println(JsonUtils.toJson(querySqlStatementStats));
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

3. LiteSqlStatementStat 内部实现

在上述配置完成后，LiteSqlStatementStat 将自动记录每次 SQL 执行的统计信息，并将其保存到内置的 SQLite 数据库中。SQL 执行信息包括查询类型、执行时间、返回行数等。

LiteSqlStatementStat 的实现如下：

• save() 方法负责保存 SQL 执行信息。如果 writeSync 为 true，则同步保存，否则异步保存。

public class LiteSqlStatementStat implements ISqlStatementStat {

  @Override
  public void save(String name, String sqlType, String sql, Object[] paras, int size, long startTimeMillis, long elapsed, boolean writeSync) {
    if (writeSync) {
      Lite.saveSqlStatementStat(name, sqlType, sql, paras, size, startTimeMillis, elapsed);
    } else {
      TioThreadUtils.submit(() -> {
        Lite.saveSqlStatementStat(name, sqlType, sql, paras, size, startTimeMillis, elapsed);
      });
    }
  }
}

4. 输出结果

运行测试类后，你会看到 SQL 查询的结果以及 SQL 统计信息，例如：

Sql: select * from rumi_sjsu_class_schedule_2024_fall
6446
[{"elapsed":"363","start_time":"1725102570770","name":"main","sql_type":"find","id":"419541847678750720","params":"[]","rows":6446,"sql":"select * from rumi_sjsu_class_schedule_2024_fall"}]

通过这些配置，你可以轻松记录和查看 SQL 的执行情况，为后续的性能优化和问题排查提供有力支持。

输出的 JSON 片段包含了 SQL 语句执行的统计信息，每个字段代表不同的统计数据。以下是对每个字段的解释：

1. id: "419541847678750720"
   这是该 SQL 语句统计记录的唯一标识符，用于唯一标识这条执行记录。通常是一个生成的唯一 ID，例如使用 Snowflake 算法生成的。
2. name: "main"
   这个字段表示执行该 SQL 查询的数据库名称。
3. start_time: "1725102570770"
   这是查询开始执行的时间戳，表示从 1970 年 1 月 1 日 00:00:00 UTC 开始到查询开始的时间点所经过的毫秒数。这是一个精确的时间标记，用于确定查询的开始时间。
4. elapsed: "363"
   这个字段表示 SQL 查询的执行时间，单位是毫秒（ms）。在这个例子中，查询执行时间为 363 毫秒。
5. sql_type: "find"
   这个字段表示 SQL 语句的类型。在这个例子中，"find" 通常指的是一个查询操作，即从数据库中检索数据的操作。
6. sql: "select * from rumi_sjsu_class_schedule_2024_fall"
   这个字段包含了实际执行的 SQL 语句。在这个例子中，执行的 SQL 查询是从 rumi_sjsu_class_schedule_2024_fall 表中选择所有列的查询。
7. params: "[]"
   这个字段包含了 SQL 语句的参数。在这个例子中，"[]" 表示这个查询没有使用任何参数。
8. rows: 6446
   这个字段表示 SQL 查询返回的行数。在这个例子中，查询返回了 6446 行数据。