使用jOOQ的DiagnosticsConnection来检测N+1查询（附实例）在许多运行SQL查询的应用程序中，N+

在许多运行SQL查询的应用程序中，N+1查询是一个流行的问题。这个问题可以简单描述如下:

运行1个获取父值的查询
运行N个获取每个子值的查询。

这个问题并不局限于SQL，它可以发生在任何不允许批处理和/或批量处理的设计不良的API（甚至是存储过程）。但是对于SQL来说，这尤其令人痛苦，因为在很多情况下，在一个查询中运行大量的逻辑是完全可能的，特别是在jOOQ的MULTISET 和SQL/XML或SQL/JSON支持下。

在最坏的情况下，N+1的问题是由第三方ORM造成的--或者说，它的实现/配置不好，但有些ORM让人很容易在N+1的问题上射中自己的脚......

一个例子

现在让我们坚持使用JDBC来说明N+1查询是如何发生的:

try (Statement stmt = conn.createStatement()) {

    // The parent query, fetching actors
    try (ResultSet r1 = stmt.executeQuery(
        """
        SELECT actor_id, first_name, last_name
        FROM actor
        LIMIT 5
        """
    )) {
        while (r1.next()) {
            System.out.println();
            System.out.println(
                "Actor: " + r1.getString(2) + " " + r1.getString(2));

            // The child query, fetching films per actor
            try (PreparedStatement pstmt = conn.prepareStatement(
                """
                SELECT count(*) FROM film_actor WHERE actor_id = ?
                """
            )) {
                pstmt.setInt(1, r1.getInt(1));

                try (ResultSet r2 = pstmt.executeQuery()) {
                    while (r2.next()) {
                        System.out.println("Films: " + r2.getInt(1));
                    }
                }
            }
        }
    }
}

当针对sakila数据库运行时，上面的打印结果:

Actor: PENELOPE PENELOPE
Films: 19

Actor: NICK NICK
Films: 25

Actor: ED ED
Films: 22

Actor: JENNIFER JENNIFER
Films: 22

Actor: JOHNNY JOHNNY
Films: 29

显然是正确的，但是我们可以很容易地在一个查询中运行这个:

SELECT
  a.first_name,
  a.last_name,
  count(fa.film_id)
FROM actor AS a
LEFT JOIN film_actor AS fa USING (actor_id)
GROUP BY a.actor_id

鉴于我们总共有200个演员，你更喜欢哪个？运行1+200个查询还是只运行1个查询？如果你能控制你的SQL，这种错误就不太可能发生，但如果你不能（完全）控制，因为SQL是根据急迫/缓慢的加载配置和复杂的实体图注释生成的，那么你就会很高兴你能把它插入到你的数据库中。你会很高兴，因为你可以把jOOQ的DiagnosticsConnection的重复语句诊断插入到你的集成测试环境中*（不一定在生产中，因为解析和规范所有的SQL字符串需要一些开销*）。

应用于上述JDBC的例子:

DSLContext ctx = DSL.using(connection);
ctx.configuration().set(new DefaultDiagnosticsListener() {
    @Override
    public void repeatedStatements(DiagnosticsContext c) {

        // Custom callback, may also throw exceptions, etc.
        System.out.println(
            "Repeated statement: " + c.normalisedStatement());
    }
});

Connection conn = ctx.diagnosticsConnection();

你现在会得到以下输出:

Actor: PENELOPE PENELOPE
Films: 19

Actor: NICK NICK
Repeated statement: select count(*) from film_actor where actor_id = ?;
Films: 25

Actor: ED ED
Repeated statement: select count(*) from film_actor where actor_id = ?;
Films: 22

Actor: JENNIFER JENNIFER
Repeated statement: select count(*) from film_actor where actor_id = ?;
Films: 22

Actor: JOHNNY JOHNNY
Repeated statement: select count(*) from film_actor where actor_id = ?;
Films: 29

正如你所看到的，诊断连接在语句的第一次重复后开始记录，假设在一个事务中，通常没有必要重复一个语句超过一次，因为几乎总是有一个更好的方法。

在JPA/Hibernate中使用这个方法

你可能不会像这样手动写JDBC语句，但谁调用JDBC并不重要（你、jOOQ、JdbcTemplate、Hibernate等）。如果你用jOOQ的DiagnosticsConnection 或DiagnosticsDataSource 来代理你的连接（或DataSource ），那么你就可以轻松地拦截这类事件，不管是什么原因。

jOOQ的未来版本将通过github.com/jOOQ/jOOQ/i… 添加更多的诊断功能。

要看jOOQ中已经有哪些可用的功能，请参考手册。