mysql-connector-j SQL 请求执行过程梳理

本次源码分析基于 mysql-connector-8.0.20

一、SQL 请求调用链路跟踪

当上层应用或 ORM 框架调用 PreparedStatement#execute 方法时，会直接调用 mysql-connector-j 包中 ClientPreparedStatement#execute 方法，从此处开始正式进入 MySQL 驱动程序的逻辑。后续的源码我会进行相应的简化，突出重点，方便理解

1.1 ClientPreparedStatement：SQL 请求入口

// ClientPreparedStatement.class
@Override
public boolean execute() throws SQLException {
    synchronized (checkClosed().getConnectionMutex()) {
        JdbcConnection locallyScopedConn = this.connection;
        ResultSetInternalMethods rs = null;
        Message sendPacket = ((PreparedQuery<?>) this.query).fillSendPacket();
        rs = executeInternal(this.maxRows, sendPacket, createStreamingResultSet(),
                (((PreparedQuery<?>) this.query).getParseInfo().getFirstStmtChar() == 'S'), cachedMetadata, false);
        if (rs != null) {
            this.results = rs;
        }
        return ((rs != null) && rs.hasRows());
    }
}

我们可以看到获取 ResultSet 的详细过程在 ClientPreparedStatement#executeInternal 方法中

// ClientPreparedStatement.class
protected <M extends Message> ResultSetInternalMethods executeInternal(int maxRowsToRetrieve, M sendPacket, boolean createStreamingResultSet,
        boolean queryIsSelectOnly, ColumnDefinition metadata, boolean isBatch) throws SQLException {
    synchronized (checkClosed().getConnectionMutex()) {
        JdbcConnection locallyScopedConnection = this.connection;
        ((PreparedQuery<?>) this.query).getQueryBindings()
                .setNumberOfExecutions(((PreparedQuery<?>) this.query).getQueryBindings().getNumberOfExecutions() + 1);
        ResultSetInternalMethods rs = ((NativeSession) locallyScopedConnection.getSession()).execSQL(this, null, maxRowsToRetrieve, (NativePacketPayload) sendPacket, createStreamingResultSet, getResultSetFactory(), metadata, isBatch);
        return rs;
    }
}

核心过程在 NativeSession#execSQL 方法中

// NativeSession.class
public <T extends Resultset> T execSQL(Query callingQuery, String query, int maxRows, NativePacketPayload packet, boolean streamResults,
        ProtocolEntityFactory<T, NativePacketPayload> resultSetFactory, ColumnDefinition cachedMetadata, boolean isBatch) {
    return packet == null ? 
        ((NativeProtocol) this.protocol).sendQueryString(callingQuery, query, this.characterEncoding.getValue(), maxRows, streamResults, cachedMetadata, resultSetFactory) : 
    	((NativeProtocol) this.protocol).sendQueryPacket(callingQuery, packet, maxRows, streamResults, cachedMetadata, resultSetFactory);
}

可以看到在 execSQL 中，若命令包不为 null，则执行 NativeProtocol#sendQueryPacket 方法

1.2 NativeProtocol：执行 SQL 请求并返回结果

public final <T extends Resultset> T sendQueryPacket(Query callingQuery, NativePacketPayload queryPacket, int maxRows, boolean streamResults,
        ColumnDefinition cachedMetadata, ProtocolEntityFactory<T, NativePacketPayload> resultSetFactory) throws IOException {

    final long queryStartTime = getCurrentTimeNanosOrMillis();

    this.statementExecutionDepth++;
	// 1 获取 SQL 二进字符数组
    byte[] queryBuf = queryPacket.getByteBuffer();
    int oldPacketPosition = queryPacket.getPosition(); // save the packet position

    LazyString query = new LazyString(queryBuf, 1, (oldPacketPosition - 1));

    try {
	    // 2 若存在拦截器，则调用拦截器的前置处理方法 preProcess 对 SQL 进行处理，并返回结果
        if (this.queryInterceptors != null) {
            // 若拦截器 preProcess 方法返回非 null，则直接返回拦截处理后的 Resultset
            T interceptedResults = invokeQueryInterceptorsPre(query, callingQuery, false);
            if (interceptedResults != null) {
                return interceptedResults;
            }
        }

        // 3 Send query command and sql query string
        NativePacketPayload resultPacket = sendCommand(queryPacket, false, 0);

        // 4 读取 SQL 执行结果
        T rs = readAllResults(maxRows, streamResults, resultPacket, false, cachedMetadata, resultSetFactory);
        
	    // 5 若存在拦截器，则调用拦截器的后置处理方法 postProcess 对 SQL、 Resultset 等进行处理，并返回结果
        if (this.queryInterceptors != null) {
            rs = invokeQueryInterceptorsPost(query, callingQuery, rs, false);
        }

        return rs;

    } catch (CJException sqlEx) {
        if (this.queryInterceptors != null) {
            // TODO why doing this?
            // we don't do anything with the result set in this case
            invokeQueryInterceptorsPost(query, callingQuery, null, false); 
        }

        if (callingQuery != null) {
            callingQuery.checkCancelTimeout();
        }

        throw sqlEx;

    } finally {
        this.statementExecutionDepth--;
    }
}

NativeProtocol#sendQueryPacket 主要有 5 个流程：

1. 获取 SQL 二进字符数组
2. 若存在拦截器，则调用拦截器的前置处理方法 preProcess 对 SQL 进行处理，并返回结果
3. 发送查询命令并返回结果集包头（里面包含拦截器）
4. 读取结果集字段包和行数据包
5. 若存在拦截器，则调用拦截器的后置处理方法 postProcess 对 SQL、 结果集等进行拦截处理，并返回拦截处理后的结果

我们此处先重点分析不存在拦截器的情况，拦截器的部分将在后续章节中详述

1.2.1 发送 SQl 命令包，获取结果集包头

// NativeProtocol.class
@Override
public final NativePacketPayload sendCommand(Message queryPacket, boolean skipCheck, int timeoutMillis) {
    // 1 若存在拦截器，则调用拦截器的后置处理方法 preProcess(M queryPacket) 对 SQL 命令执行包进行处理并返回 SQL 命令执行包
    if (this.queryInterceptors != null) {
        NativePacketPayload interceptedPacketPayload = (NativePacketPayload) invokeQueryInterceptorsPre(queryPacket, false);
        if (interceptedPacketPayload != null) {
            return interceptedPacketPayload;
        }
    }
    // 2 发送 SQL 命令执行包
    send(queryPacket, queryPacket.getPosition());
    // 3 Checks for errors in the reply packet, and if none, returns the reply packet, ready for reading
    returnPacket = checkErrorMessage(command);
    // 4 若存在拦截器，则调用拦截器的后置处理方法 postProcess(M queryPacket, M originalResponsePacket) 进行拦截处理并返回结果集包头
    if (this.queryInterceptors != null) {
        returnPacket = (NativePacketPayload) invokeQueryInterceptorsPost(queryPacket, returnPacket, false);
    }
    return returnPacket;
}

1.2.2 读取结果集包的字段包和行数据包

// NativeProtocol.class
public <T extends Resultset> T readAllResults(int maxRows, boolean streamResults, NativePacketPayload resultPacket, boolean isBinaryEncoded,
        ColumnDefinition metadata, ProtocolEntityFactory<T, NativePacketPayload> resultSetFactory) throws IOException {

    resultPacket.setPosition(0);
    T topLevelResultSet = read(Resultset.class, maxRows, streamResults, resultPacket, isBinaryEncoded, metadata, resultSetFactory);

    if (this.serverSession.hasMoreResults()) {
        T currentResultSet = topLevelResultSet;
        if (streamResults) {
            currentResultSet = readNextResultset(currentResultSet, maxRows, true, isBinaryEncoded, resultSetFactory);
        } else {
            while (this.serverSession.hasMoreResults()) {
                currentResultSet = readNextResultset(currentResultSet, maxRows, false, isBinaryEncoded, resultSetFactory);
            }
            clearInputStream();
        }
    }

    if (this.hadWarnings) {
        scanForAndThrowDataTruncation();
    }

    reclaimLargeReusablePacket();
    return topLevelResultSet;
}

1.3 SQL 请求执行时序图（无拦截器）

二、QueryInterceptor

mysql-connector-j 提供了 QueryInterceptor 接口供我们对 SQL 请求进行拦截处理，接口定义如下：

public interface QueryInterceptor {

    /**
     * Called once per connection that wants to use the interceptor
     * 
     * The properties are the same ones passed in in the URL or arguments to
     * Driver.connect() or DriverManager.getConnection().
     * 
     * @param conn
     *            the connection for which this interceptor is being created
     * @param props
     *            configuration values as passed to the connection. Note that
     *            in order to support javax.sql.DataSources, configuration properties specific
     *            to an interceptor <strong>must</strong> be passed via setURL() on the
     *            DataSource. QueryInterceptor properties are not exposed via
     *            accessor/mutator methods on DataSources.
     * @param log
     *            logger
     * @return {@link QueryInterceptor}
     */
    QueryInterceptor init(MysqlConnection conn, Properties props, Log log);

    /**
     * Called before the given query is going to be sent to the server for processing.
     * 
     * Interceptors are free to return a result set (which must implement the
     * interface {@link Resultset}), and if so,
     * the server will not execute the query, and the given result set will be
     * returned to the application instead.
     * 
     * This method will be called while the connection-level mutex is held, so
     * it will only be called from one thread at a time.
     * 
     * @param sql
     *            the Supplier for SQL representation of the query
     * @param interceptedQuery
     *            the actual {@link Query} instance being intercepted
     * @param <T>
     *            {@link Resultset} object
     * 
     * @return a {@link Resultset} that should be returned to the application instead
     *         of results that are created from actual execution of the intercepted
     *         query.
     */
    <T extends Resultset> T preProcess(Supplier<String> sql, Query interceptedQuery);

    /**
     * Called before the given query packet is going to be sent to the server for processing.
     * 
     * Interceptors are free to return a PacketPayload, and if so,
     * the server will not execute the query, and the given PacketPayload will be
     * returned to the application instead.
     * 
     * This method will be called while the connection-level mutex is held, so
     * it will only be called from one thread at a time.
     * 
     * @param queryPacket
     *            original {@link Message}
     * @param <M>
     *            {@link Message} object
     * @return processed {@link Message}
     */
    default <M extends Message> M preProcess(M queryPacket) {
        return null;
    }

    /**
     * Should the driver execute this interceptor only for the
     * "original" top-level query, and not put it in the execution
     * path for queries that may be executed from other interceptors?
     * 
     * If an interceptor issues queries using the connection it was created for,
     * and does not return <code>true</code> for this method, it must ensure
     * that it does not cause infinite recursion.
     * 
     * @return true if the driver should ensure that this interceptor is only
     *         executed for the top-level "original" query.
     */
    boolean executeTopLevelOnly();

    /**
     * Called by the driver when this extension should release any resources
     * it is holding and cleanup internally before the connection is
     * closed.
     */
    void destroy();

    /**
     * Called after the given query has been sent to the server for processing.
     * 
     * Interceptors are free to inspect the "original" result set, and if a
     * different result set is returned by the interceptor, it is used in place
     * of the "original" result set.
     * 
     * This method will be called while the connection-level mutex is held, so
     * it will only be called from one thread at a time.
     * 
     * @param sql
     *            the Supplier for SQL representation of the query
     * @param interceptedQuery
     *            the actual {@link Query} instance being intercepted
     * @param originalResultSet
     *            a {@link Resultset} created from query execution
     * @param serverSession
     *            {@link ServerSession} object after the query execution
     * @param <T>
     *            {@link Resultset} object
     * 
     * @return a {@link Resultset} that should be returned to the application instead
     *         of results that are created from actual execution of the intercepted
     *         query.
     */
    <T extends Resultset> T postProcess(Supplier<String> sql, Query interceptedQuery, T originalResultSet, ServerSession serverSession);

    /**
     * Called after the given query packet has been sent to the server for processing.
     * 
     * Interceptors are free to return either a different PacketPayload than the originalResponsePacket or null.
     * 
     * This method will be called while the connection-level mutex is held, so
     * it will only be called from one thread at a time.
     * 
     * @param queryPacket
     *            query {@link Message}
     * @param originalResponsePacket
     *            response {@link Message}
     * @param <M>
     *            {@link Message} object
     * @return {@link Message}
     */
    default <M extends Message> M postProcess(M queryPacket, M originalResponsePacket) {
        return null;
    }
}

在自定义拦截器时，我们可以重写一下 4 个方法，这四个方法分为两组，分别处理前置拦截逻辑和后置拦截逻辑

<T extends Resultset> T preProcess(Supplier<String> sql, Query interceptedQuery);
<M extends Message> M preProcess(M queryPacket);
<T extends Resultset> T postProcess(Supplier<String> sql, Query interceptedQuery, T originalResultSet, ServerSession serverSession);
<M extends Message> M postProcess(M queryPacket, M originalResponsePacket);

由于 PreparedStatement#execute 调用时获取数据库连接级别的锁：synchronized (checkClosed().getConnectionMutex())，故这四个方法是线程安全的

sendQueryPacket

NativeProtocol#sendQueryPacket 方法中前置拦截作用于两处：

public final <T extends Resultset> T sendQueryPacket(Query callingQuery, NativePacketPayload queryPacket, int maxRows, boolean streamResults,
        ColumnDefinition cachedMetadata, ProtocolEntityFactory<T, NativePacketPayload> resultSetFactory) throws IOException {

    final long queryStartTime = getCurrentTimeNanosOrMillis();

    this.statementExecutionDepth++;
    // 1 获取 SQL 二进字符数组
    byte[] queryBuf = queryPacket.getByteBuffer();
    int oldPacketPosition = queryPacket.getPosition(); // save the packet position

    LazyString query = new LazyString(queryBuf, 1, (oldPacketPosition - 1));

    try {
        // 2 若存在拦截器，则调用拦截器的前置处理方法 preProcess 对 SQL 进行处理，并返回结果
        if (this.queryInterceptors != null) {
            // 若拦截器 preProcess 方法返回非 null，则直接返回拦截处理后的 Resultset
            T interceptedResults = invokeQueryInterceptorsPre(query, callingQuery, false);
            if (interceptedResults != null) {
                return interceptedResults;
            }
        }

        // 3 Send query command and sql query string
        NativePacketPayload resultPacket = sendCommand(queryPacket, false, 0);

        // 4 读取 SQL 执行结果
        T rs = readAllResults(maxRows, streamResults, resultPacket, false, cachedMetadata, resultSetFactory);

        // 5 若存在拦截器，则调用拦截器的后置处理方法 postProcess 对 SQL、 Resultset 等进行处理，并返回结果
        if (this.queryInterceptors != null) {
            rs = invokeQueryInterceptorsPost(query, callingQuery, rs, false);
        }

        return rs;

    } catch (CJException sqlEx) {
        if (this.queryInterceptors != null) {
            // TODO why doing this?
            // we don't do anything with the result set in this case
            invokeQueryInterceptorsPost(query, callingQuery, null, false);
        }

        if (callingQuery != null) {
            callingQuery.checkCancelTimeout();
        }

        throw sqlEx;

    } finally {
        this.statementExecutionDepth--;
    }
}

sendCommand

public final NativePacketPayload sendCommand(Message queryPacket, boolean skipCheck, int timeoutMillis) {
    // 1 若存在拦截器，则调用拦截器的后置处理方法 preProcess(M queryPacket) 对 SQL 命令执行包进行处理并返回 SQL 命令执行包
    if (this.queryInterceptors != null) {
        NativePacketPayload interceptedPacketPayload = (NativePacketPayload) invokeQueryInterceptorsPre(queryPacket, false);
        if (interceptedPacketPayload != null) {
            return interceptedPacketPayload;
        }
    }
    // 2 发送 SQL 命令执行包
    send(queryPacket, queryPacket.getPosition());
    // 3 Checks for errors in the reply packet, and if none, returns the reply packet, ready for reading
    returnPacket = checkErrorMessage(command);
    // 4 若存在拦截器，则调用拦截器的后置处理方法 postProcess(M queryPacket, M originalResponsePacket) 进行拦截处理并返回结果集包头
    if (this.queryInterceptors != null) {
        returnPacket = (NativePacketPayload) invokeQueryInterceptorsPost(queryPacket, returnPacket, false);
    }
    return returnPacket;
}

2.1 前置拦截

NativeProtocol#sendQueryPacket 方法中前置拦截作用于两处：

1. 第一处作用于 NativeProtocol#sendQueryPacket 方法标记为 ② 的地方
2. 第二处作用于 NativeProtocol#sendQueryPacket 方法标记为 ③ 的地方，内部作用于 sendCommand 方法中标记为 ① 的地方

2.1.1 第一处拦截

第一处拦截的核心逻辑于 NativeProtocol#invokeQueryInterceptorsPre 方法中

public <T extends Resultset> T invokeQueryInterceptorsPre(Supplier<String> sql, Query interceptedQuery, boolean forceExecute) {
    T previousResultSet = null;

    for (int i = 0, s = this.queryInterceptors.size(); i < s; i++) {
        QueryInterceptor interceptor = this.queryInterceptors.get(i);

        boolean executeTopLevelOnly = interceptor.executeTopLevelOnly();
        boolean shouldExecute = (executeTopLevelOnly && (this.statementExecutionDepth == 1 || forceExecute)) || (!executeTopLevelOnly);

        if (shouldExecute) {
            T interceptedResultSet = interceptor.preProcess(sql, interceptedQuery);

            if (interceptedResultSet != null) {
                previousResultSet = interceptedResultSet;
            }
        }
    }

    return previousResultSet;
}

此处前置拦截器可以拦截 SQL 请求，并直接生成结果集包返回。若 invokeQueryInterceptorsPre 方法返回 null，则接续执行后续 SQL 请求逻辑

2.1.2 第二处拦截

第二处拦截的核心逻辑于 NativeProtocol#invokeQueryInterceptorsPre 方法中

public <M extends Message> M invokeQueryInterceptorsPre(M queryPacket, boolean forceExecute) {
    M previousPacketPayload = null;

    for (int i = 0, s = this.queryInterceptors.size(); i < s; i++) {
        QueryInterceptor interceptor = this.queryInterceptors.get(i);
        M interceptedPacketPayload = interceptor.preProcess(queryPacket);
        if (interceptedPacketPayload != null) {
            previousPacketPayload = interceptedPacketPayload;
        }
    }

    return previousPacketPayload;
}

此处拦截 SQL 命令包，并调用 QueryInterceptor#preProcess 方法执行拦截逻辑，可自定义返回结果集包头。若 QueryInterceptor#preProcess 方法返回 null，则接续执行后续 SQL 请求逻辑，否则直接返回结果集包头

2.2 后置拦截

NativeProtocol#sendQueryPacket 方法中后置拦截作用于两处：

1. 第一处作用于 NativeProtocol#sendQueryPacket 方法标记为 ⑤ 的地方
2. 第二处作用于 NativeProtocol#sendQueryPacket 方法标记为 ③ 的地方，内部作用于 sendCommand 方法中标记为 ④ 的地方

2.2.1 第一处拦截

第一处拦截的核心逻辑于 NativeProtocol#invokeQueryInterceptorsPre 方法中

public <T extends Resultset> T invokeQueryInterceptorsPost(Supplier<String> sql, Query interceptedQuery, T originalResultSet, boolean forceExecute) {

    for (int i = 0, s = this.queryInterceptors.size(); i < s; i++) {
        QueryInterceptor interceptor = this.queryInterceptors.get(i);

        boolean executeTopLevelOnly = interceptor.executeTopLevelOnly();
        boolean shouldExecute = (executeTopLevelOnly && (this.statementExecutionDepth == 1 || forceExecute)) || (!executeTopLevelOnly);

        if (shouldExecute) {
            T interceptedResultSet = interceptor.postProcess(sql, interceptedQuery, originalResultSet, this.serverSession);

            if (interceptedResultSet != null) {
                originalResultSet = interceptedResultSet;
            }
        }
    }

    return originalResultSet;
}

此处 invokeQueryInterceptorsPost 方法调用 QueryInterceptor#postProcess 方法执行拦截操作，若 QueryInterceptor#postProcess 方法返回 null，则接续执行后续 SQL 请求逻辑，否则直接返回拦截处理后的结果集包

2.2.2 第二处拦截

第二处拦截的核心逻辑于 NativeProtocol#invokeQueryInterceptorsPre 方法中

public <M extends Message> M invokeQueryInterceptorsPost(M queryPacket, M originalResponsePacket, boolean forceExecute) {

    for (int i = 0, s = this.queryInterceptors.size(); i < s; i++) {
        QueryInterceptor interceptor = this.queryInterceptors.get(i);

        M interceptedPacketPayload = interceptor.postProcess(queryPacket, originalResponsePacket);
        if (interceptedPacketPayload != null) {
            originalResponsePacket = interceptedPacketPayload;
        }
    }

    return originalResponsePacket;
}

此处 invokeQueryInterceptorsPost 方法调用 QueryInterceptor#postProcess 方法执行拦截操作，可自定义返回结果集包头。若 QueryInterceptor#postProcess 方法返回 null，则接续执行后续 SQL 请求逻辑，否则直接返回结果集包头

2.3 拦截器总结