前言
本章学习sharding-proxy业务处理流程：
- PacketCodec：针对数据库协议的编解码实现。
- FrontendChannelInboundHandler：针对Netty的ChannelInboundHandler实现。
- ShardingProxy实际的业务执行。
一、PacketCodec编解码
PacketCodec编解码Handler，委托DatabasePacketCodecEngine编解码引擎实现具体的编解码逻辑。这里主要关注MySQL的解码实现类MySQLPacketCodecEngine。
public final class PacketCodec extends ByteToMessageCodec<DatabasePacket> {
private final DatabasePacketCodecEngine databasePacketCodecEngine;
@Override
protected void decode(final ChannelHandlerContext context, final ByteBuf in, final List<Object> out) {
int readableBytes = in.readableBytes();
if (!databasePacketCodecEngine.isValidHeader(readableBytes)) {
return;
}
databasePacketCodecEngine.decode(context, in, out, readableBytes);
}
@Override
protected void encode(final ChannelHandlerContext context, final DatabasePacket message, final ByteBuf out) {
databasePacketCodecEngine.encode(context, message, out);
}
}
1、解码
对于MySQL来说，通过自定义协议解决粘包拆包，报文的4个字节属于协议头。
解码之前，首先需要校验报文可读字节数大于4（协议头长度），否则不处理。
@Override
public boolean isValidHeader(final int readableBytes) {
// 可读字节数 > 3（Payload长度） + 1（sequenceID）
return readableBytes > MySQLPacket.PAYLOAD_LENGTH + MySQLPacket.SEQUENCE_LENGTH;
}
接着进入MySQLPacketCodecEngine的decode方法解码。
public final class MySQLPacketCodecEngine implements DatabasePacketCodecEngine<MySQLPacket> {
@Override
public void decode(final ChannelHandlerContext context, final ByteBuf in, final List<Object> out, final int readableBytes) {
// markReaderIndex标记读index，readMediumLE读出3个字节
int payloadLength = in.markReaderIndex().readMediumLE();
// 计算实际报文总长度 = payload内容长度 + 协议头长度
int realPacketLength = payloadLength + MySQLPacket.PAYLOAD_LENGTH + MySQLPacket.SEQUENCE_LENGTH;
// 如果可读字节不足，表示遇到半包，重置读index返回
if (readableBytes < realPacketLength) {
in.resetReaderIndex();
return;
}
// 读payload和sequenceId到一个新的ByteBuf
ByteBuf byteBuf = in.readRetainedSlice(payloadLength + MySQLPacket.SEQUENCE_LENGTH);
out.add(byteBuf);
}
}
可以看到解码逻辑很简单，主要是处理粘包拆包问题，保证读入的ByteBuf和实际业务一致。
2、编码
PacketPayload
PacketPayload：ByteBuf的包装类，提供不同数据库的读写ByteBuf的方式，辅助业务数据写入ByteBuf。
public interface PacketPayload extends AutoCloseable {
ByteBuf getByteBuf();
}
比如writeInt2写入2字节长度的int到ByteBuf中，MySQLPacketPayload的实现如下：
@RequiredArgsConstructor
@Getter
public final class MySQLPacketPayload implements PacketPayload {
private final ByteBuf byteBuf;
public void writeInt2(final int value) {
byteBuf.writeShortLE(value);
}
}
PostgreSQLPacketPayload的实现如下：
@RequiredArgsConstructor
@Getter
public final class PostgreSQLPacketPayload implements PacketPayload {
private final ByteBuf byteBuf;
public void writeInt2(final int value) {
byteBuf.writeShort(value);
}
}
DatabasePacket
DatabasePacket：负责写入PacketPayload，一般本身持有业务相关数据。
public interface DatabasePacket<T extends PacketPayload> {
void write(T payload);
}
MySQLPacket：负责写入MySQLPacketPayload，同时需要子类实现获取SequenceId方法。针对于不同类型的SQL语句会有不同的MySQLPacket实现。
public interface MySQLPacket extends DatabasePacket<MySQLPacketPayload> {
// payload长度
int PAYLOAD_LENGTH = 3;
// sequenceId长度
int SEQUENCE_LENGTH = 1;
// 获取sequenceId
int getSequenceId();
}
编码
回到MySQLPacketCodecEngine的encode方法，按照Payload长度->SequenceId->Payload的顺序写入ByteBuf。其中Payload数据是通过MySQLPacket写入MySQLPacketPayload持有的ByteBuf，然后再最终写入out的。
public void encode(final ChannelHandlerContext context, final MySQLPacket message, final ByteBuf out) {
// 开辟一个MySQLPacketPayload（一个ByteBuf的包装）
try (MySQLPacketPayload payload = new MySQLPacketPayload(context.alloc().buffer())) {
// 业务数据写入MySQLPacketPayload
message.write(payload);
// 写入Payload长度
out.writeMediumLE(payload.getByteBuf().readableBytes());
// 写入SequenceId
out.writeByte(message.getSequenceId());
// 写入Payload
out.writeBytes(payload.getByteBuf());
}
}
二、FrontendChannelInboundHandler
FrontendChannelInboundHandler负责实际业务处理。
1、成员变量
FrontendChannelInboundHandler在构造ChannelPipeline时被创建。
@RequiredArgsConstructor
public final class FrontendChannelInboundHandler extends ChannelInboundHandlerAdapter {
// 数据库协议引擎
private final DatabaseProtocolFrontendEngine databaseProtocolFrontendEngine;
// 是否授权（用户名密码校验通过）
private volatile boolean authorized;
// BackendConnection
private final BackendConnection backendConnection = new BackendConnection(
TransactionType.valueOf(ShardingProxyContext.getInstance().getProperties().getValue(ConfigurationPropertyKey.PROXY_TRANSACTION_TYPE)),
ShardingProxyContext.getInstance().getProperties().<Boolean>getValue(ConfigurationPropertyKey.PROXY_HINT_ENABLED));
}
重点关注BackendConnection成员变量，可以类比sharding-jdbc的AbstractConnectionAdapter，主要负责获取连接。一个客户端连接对应一个FrontendChannelInboundHandler，所以一个客户端持有一个BackendConnection。
@Getter
public final class BackendConnection implements AutoCloseable {
// 逻辑schema
private volatile String schemaName;
// 逻辑schema
private LogicSchema logicSchema;
// 全局事务类型
private TransactionType transactionType;
// 是否支持Hint
private boolean supportHint;
// 针对单个客户端连接(channel)的唯一标识
@Setter
private int connectionId;
// 用户名
@Setter
private String userName;
// 缓存连接
private final Multimap<String, Connection> cachedConnections = LinkedHashMultimap.create();
// 缓存Statement
private final Collection<Statement> cachedStatements = new CopyOnWriteArrayList<>();
// 缓存ResultSet
private final Collection<ResultSet> cachedResultSets = new CopyOnWriteArrayList<>();
// 方法调用记录 用于后期反射调用
private final Collection<MethodInvocation> methodInvocations = new ArrayList<>();
// 自定义的同步器
@Getter
private final ResourceSynchronizer resourceSynchronizer = new ResourceSynchronizer();
// 连接状态管理
private final ConnectionStateHandler stateHandler = new ConnectionStateHandler(resourceSynchronizer);
}
2、channelActive
当channel首次注册到selector上时触发channelActive。
public final class FrontendChannelInboundHandler extends ChannelInboundHandlerAdapter {
private final DatabaseProtocolFrontendEngine databaseProtocolFrontendEngine;
private final BackendConnection backendConnection
public void channelActive(final ChannelHandlerContext context) {
// ChannelThreadExecutorGroup创建与channel一一对应的线程处理业务
ChannelThreadExecutorGroup.getInstance().register(context.channel().id());
// MySQLAuthenticationEngine回复握手消息给客户端
databaseProtocolFrontendEngine.getAuthEngine().handshake(context, backendConnection);
}
}
ChannelThreadExecutorGroup全局单例，维护channel和业务线程的一一对应关系。
public final class ChannelThreadExecutorGroup {
private static final ChannelThreadExecutorGroup INSTANCE = new ChannelThreadExecutorGroup();
private final Map<ChannelId, ExecutorService> executorServices = new ConcurrentHashMap<>();
public static ChannelThreadExecutorGroup getInstance() {
return INSTANCE;
}
public void register(final ChannelId channelId) {
executorServices.put(channelId, Executors.newSingleThreadExecutor());
}
public ExecutorService get(final ChannelId channelId) {
return executorServices.get(channelId);
}
public void unregister(final ChannelId channelId) {
executorServices.remove(channelId).shutdown();
}
}
MySQLAuthenticationEngine通过写入MySQLHandshakePacket，回复握手消息。注意这里为BackendConnection创建了唯一id标识。
public final class MySQLAuthenticationEngine implements AuthenticationEngine {
private final MySQLAuthenticationHandler authenticationHandler = new MySQLAuthenticationHandler();
private MySQLConnectionPhase connectionPhase = MySQLConnectionPhase.INITIAL_HANDSHAKE;
@Override
public void handshake(final ChannelHandlerContext context, final BackendConnection backendConnection) {
// 为BackendConnection分配id
int connectionId = ConnectionIdGenerator.getInstance().nextId();
backendConnection.setConnectionId(connectionId);
// 修改连接阶段
connectionPhase = MySQLConnectionPhase.AUTH_PHASE_FAST_PATH;
// 写入握手消息（如果关注握手消息的写入协议，可以进入MySQLHandshakePacket的write方法）
context.writeAndFlush(new MySQLHandshakePacket(connectionId, authenticationHandler.getAuthPluginData()));
}
}
3、channelRead
public void channelRead(final ChannelHandlerContext context, final Object message) {
// 如果当前channel还没授权，执行授权（校验用户名密码）
if (!authorized) {
authorized = auth(context, (ByteBuf) message);
return;
}
CommandExecutorSelector
// CommandExecutorSelector根据条件，选择合适的线程池执行业务
.getExecutor(
databaseProtocolFrontendEngine.getFrontendContext().isOccupyThreadForPerConnection()
, backendConnection.isSupportHint()
, backendConnection.getTransactionType()
, context.channel().id()
)
// CommandExecutorTask执行业务
.execute(new CommandExecutorTask(databaseProtocolFrontendEngine, backendConnection, context, message));
}
当首次收到数据时，会先进行授权校验。一方面需要校验用户名密码，另一方面还需要校验schema权限，这都是由MySQLAuthenticationEngine处理的。
private boolean auth(final ChannelHandlerContext context, final ByteBuf message) {
// 创建PacketPayload
try (PacketPayload payload = databaseProtocolFrontendEngine.getCodecEngine().createPacketPayload(message)) {
// MySQLAuthenticationEngine做用户名密码及schema权限校验
return databaseProtocolFrontendEngine.getAuthEngine().auth(context, payload, backendConnection);
} catch (final Exception ex) {
context.write(databaseProtocolFrontendEngine.getCommandExecuteEngine().getErrorPacket(ex));
}
return false;
}
授权通过后，CommandExecutorSelector会根据当前的情况选择业务线程池提交任务CommandExecutorTask。
public final class CommandExecutorSelector {
public static ExecutorService getExecutor(final boolean isOccupyThreadForPerConnection, final boolean supportHint, final TransactionType transactionType, final ChannelId channelId) {
return (isOccupyThreadForPerConnection || supportHint || TransactionType.XA == transactionType || TransactionType.BASE == transactionType)
? ChannelThreadExecutorGroup.getInstance().get(channelId) : UserExecutorGroup.getInstance().getExecutorService();
}
}
满足下面任一条件，使用ChannelThreadExecutorGroup中channel对应的线程池处理
- isOccupyThreadForPerConnection：是否连接独占线程，由数据库类型决定，MySQL为false，PostgreSQL为true。
- supportHint：是否支持Hint。由proxy.hint.enabled决定，默认false。
- transactionType：事务类型，如果是XA或BASE事务（非本地事务），则用ChannelThreadExecutorGroup中的线程。
默认情况下MySQL都是用UserExecutorGroup中维护的公共线程池执行CommandExecutorTask业务任务。核心线程数=acceptor.size，默认为2倍核数。ShardingSphereExecutorService底层是通过Executors.newFixedThreadPool(executorSize)创建的线程池，等待队列是LinkedBlockingQueue，可认为无界。
public final class UserExecutorGroup implements AutoCloseable {
private static final ShardingProxyContext SHARDING_PROXY_CONTEXT = ShardingProxyContext.getInstance();
private static final String NAME_FORMAT = "Command-%d";
private static final UserExecutorGroup INSTANCE = new UserExecutorGroup();
private final ShardingSphereExecutorService shardingSphereExecutorService;
@Getter
private final ListeningExecutorService executorService;
private UserExecutorGroup() {
// 核心线程数 = acceptor.size 默认为2*核数
shardingSphereExecutorService = new ShardingSphereExecutorService(SHARDING_PROXY_CONTEXT.getProperties().<Integer>getValue(ConfigurationPropertyKey.ACCEPTOR_SIZE), NAME_FORMAT);
executorService = shardingSphereExecutorService.getExecutorService();
}
public static UserExecutorGroup getInstance() {
return INSTANCE;
}
@Override
public void close() {
shardingSphereExecutorService.close();
}
}
4、channelInactive
客户端关闭时，释放所有资源。
@Override
public void channelInactive(final ChannelHandlerContext context) {
context.fireChannelInactive();
// MySQL空实现
databaseProtocolFrontendEngine.release(backendConnection);
// 关闭backendConnection持有的资源，如Connection、ResultSet、Statement
backendConnection.close(true);
// 关闭ChannelThreadExecutorGroup中channel对应的线程池
ChannelThreadExecutorGroup.getInstance().unregister(context.channel().id());
}
5、channelWritabilityChanged
当channel无法写入数据时，会调用ResourceSynchronizer的doAwait方法阻塞等待直至被唤醒。当channel可写时，此处会唤醒所有阻塞线程。
@Override
public void channelWritabilityChanged(final ChannelHandlerContext context) {
if (context.channel().isWritable()) {
backendConnection.getResourceSynchronizer().doNotify();
}
}
ResourceSynchronizer：轻量级的自定义同步器，支持无限等待，超时等待。使用ReentrantLock和Condition实现。
public final class ResourceSynchronizer {
private static final long DEFAULT_TIMEOUT_MILLISECONDS = 200;
private final Lock lock = new ReentrantLock();
private final Condition condition = lock.newCondition();
public void doAwait() {
lock.lock();
try {
condition.await();
} catch (final InterruptedException ignore) {
} finally {
lock.unlock();
}
}
void doAwaitUntil() throws InterruptedException {
lock.lock();
try {
condition.await(DEFAULT_TIMEOUT_MILLISECONDS, TimeUnit.MILLISECONDS);
} finally {
lock.unlock();
}
}
public void doNotify() {
lock.lock();
try {
condition.signalAll();
} finally {
lock.unlock();
}
}
}
三、执行
1、连接状态
BackendConnection的连接状态枚举如下：
public enum ConnectionStatus {
INIT, RUNNING, RELEASE, TRANSACTION, TERMINATED
}
- INIT：BackendConnection刚创建时（客户端channel注册到selector上时，ChannelPipeline加入FrontendChannelInboundHandler）的状态。
- RUNNING：CommandExecutorTask业务处理任务执行时。
- RELEASE：CommandExecutorTask业务处理任务完成时。
- TRANSACTION：开启全局事务时。
- TERMINATED：全局事务提交或回滚时。
整个状态流转如下：
无事务的情况下，除了首次创建时的INIT，只有业务任务运行时（RUNNING）和结束时（RELEASE）的状态：
INIT -> RUNNING -> RELEASE -> RUNNING -> RELEASE -> ...
存在事务的情况下，多了TRANSACTION和TERMINATED状态，前者表示处于全局事务中，后者表示事务已经提交或回滚：
INIT -> RUNNING （Task开始）-> TRANSACTION （开启事务）-> ... 执行n次业务Task -> TERMINATED（提交或回滚事务）-> RELEASE -> ...
2、连接状态管理
ConnectionStateHandler作为BackendConnection的成员变量，负责管理BackendConnection的状态流转。
@RequiredArgsConstructor
public final class ConnectionStateHandler {
// 当前状态
private final AtomicReference<ConnectionStatus> status = new AtomicReference<>(ConnectionStatus.INIT);
// 同步器
private final ResourceSynchronizer resourceSynchronizer;
}
设置RUNNING
仅当执行中(RUNNING)或事务结束(TERMINATED)时，循环等待当前BackendConnection变为RELEASE状态，并更新为RUNNING执行中状态。
public void waitUntilConnectionReleasedIfNecessary() throws InterruptedException {
if (ConnectionStatus.RUNNING == status.get() || ConnectionStatus.TERMINATED == status.get()) {
while (!status.compareAndSet(ConnectionStatus.RELEASE, ConnectionStatus.RUNNING)) {
resourceSynchronizer.doAwaitUntil();
}
}
}
仅当状态为INIT或RELEASE或TERMINATED时，才能设置为RUNNING状态。（业务上只有INIT时才会设置为RUNNING）
public void setRunningStatusIfNecessary() {
if (ConnectionStatus.TRANSACTION != status.get() && ConnectionStatus.RUNNING != status.get()) {
status.getAndSet(ConnectionStatus.RUNNING);
}
}
设置RELEASE
仅当普通Task（非事务）执行结束后（RUNNING），或事务提交后（TERMINATED），设置状态为RELEASE，并通知所有等待线程。代表BackendConnection不再被使用，可以用于执行下一个Task。
void doNotifyIfNecessary() {
if (status.compareAndSet(ConnectionStatus.RUNNING, ConnectionStatus.RELEASE) || status.compareAndSet(ConnectionStatus.TERMINATED, ConnectionStatus.RELEASE)) {
resourceSynchronizer.doNotify();
}
}
事务相关
业务上只有事务相关状态，会调用这个方法。事务开启，设置为TRANSACTION，事务结束，设置为TERMINATED，如果当前状态为TERMINATED会通知所有等待线程。
public void setStatus(final ConnectionStatus update) {
status.getAndSet(update);
if (ConnectionStatus.TERMINATED == status.get()) {
resourceSynchronizer.doNotify();
}
}
3、CommandExecutorTask
CommandExecutorTask实际的业务处理任务。
public final class CommandExecutorTask implements Runnable {
// 数据库协议引擎
private final DatabaseProtocolFrontendEngine databaseProtocolFrontendEngine;
// channel对应BackendConnection
private final BackendConnection backendConnection;
// 上下文
private final ChannelHandlerContext context;
// PacketCodec解码之后的ByteBuf实例
private final Object message;
@Override
public void run() {
boolean isNeedFlush = false;
// BackendConnection实现AutoCloseable，结束后如果是RUNNING或TERMINATED会设置为RELEASE状态
try (BackendConnection backendConnection = this.backendConnection;
PacketPayload payload = databaseProtocolFrontendEngine.getCodecEngine().createPacketPayload((ByteBuf) message)) {
// 如果有任务在执行，等待BackendConnection变为RELEASE状态，更新为RUNNING状态
backendConnection.getStateHandler().waitUntilConnectionReleasedIfNecessary();
// 如果当前任务是INIT状态，设置为RUNNING状态
backendConnection.getStateHandler().setRunningStatusIfNecessary();
// 执行
isNeedFlush = executeCommand(context, payload, backendConnection);
} catch (final Exception ex) {
// ...
} finally {
if (isNeedFlush) {
context.flush();
}
}
}
}
可以看到run方法主要是控制BackendConnection的状态（RELEASE->RUNNING->RELEASE），必须从RELEASE（或INIT）变为RUNNING之后，才能执行业务方法，保证BackendConnection同时只能被一个Task执行。
接下来看一下CommandExecutorTask的executeCommand方法。
private boolean executeCommand(final ChannelHandlerContext context, final PacketPayload payload, final BackendConnection backendConnection) throws SQLException {
// MySQLCommandExecuteEngine
CommandExecuteEngine commandExecuteEngine = databaseProtocolFrontendEngine.getCommandExecuteEngine();
// 根据payload获取命令类型
CommandPacketType type = commandExecuteEngine.getCommandPacketType(payload);
// 工厂 根据命令类型构造CommandPacket - MySQLPacket的实现类
CommandPacket commandPacket = commandExecuteEngine.getCommandPacket(payload, type, backendConnection);
// 工厂 根据命令类型构造CommandExecutor
CommandExecutor commandExecutor = commandExecuteEngine.getCommandExecutor(type, commandPacket, backendConnection);
// 执行（业务逻辑）
Collection<DatabasePacket> responsePackets = commandExecutor.execute();
if (responsePackets.isEmpty()) {
return false;
}
// 提交到channel对应EventLoop写入ChannelOutboundBuffer
// 并没有flush，没写给客户端，是否flush通过协议的isFlushForPerCommandPacket返回给外部
for (DatabasePacket each : responsePackets) {
context.write(each);
}
// 如果是查询，需要将查询结果写回客户端，执行CommandExecuteEngine的writeQueryData方法
if (commandExecutor instanceof QueryCommandExecutor) {
commandExecuteEngine.writeQueryData(context, backendConnection, (QueryCommandExecutor) commandExecutor, responsePackets.size());
return true;
}
// 返回这个数据库协议 最终是否需要flush
return databaseProtocolFrontendEngine.getFrontendContext().isFlushForPerCommandPacket();
}
整个sharding业务都是在CommandExecutor中执行的，而CommandExecutor是由CommandExecuteEngine构造的。
CommandExecuteEngine创建CommandExecutor
commandExecuteEngine.getCommandPacketType首先根据payload解析出命令类型CommandPacketType，定位到MySQLCommandPacketTypeLoader。
public final class MySQLCommandPacketTypeLoader {
public static MySQLCommandPacketType getCommandPacketType(final MySQLPacketPayload payload) {
// 第一个字节是SequenceID，必须是0
Preconditions.checkArgument(0 == payload.readInt1(), "Sequence ID of MySQL command packet must be `0`.");
// 根据第二个字节决定命令类型
return MySQLCommandPacketType.valueOf(payload.readInt1());
}
}
MySQLPacketPayload里封装了一个ByteBuf，在第一部分PacketCodec编解码时看到这个ByteBuf里装的就是SequenceID和Payload。此处getCommandPacketType先读取一个字节，要求SequenceID必须是0，接着读取Payload中第一个字节决定了MySQL的命令类型。MySQLCommandPacketType是一个枚举。
public enum MySQLCommandPacketType implements CommandPacketType {
COM_SLEEP(0x00),
COM_QUIT(0x01),
COM_INIT_DB(0x02),
COM_QUERY(0x03),
// ... 其他命令类型
;
}
commandExecuteEngine.getCommandPacket创建MySQLCommandPacket，定位到MySQLCommandPacketFactory。可以看到这里就是一个简单工厂，根据命令类型，构造不同的MySQLCommandPacket返回。
public final class MySQLCommandPacketFactory {
public static MySQLCommandPacket newInstance(final MySQLCommandPacketType commandPacketType, final MySQLPacketPayload payload) throws SQLException {
switch (commandPacketType) {
case COM_QUIT:
return new MySQLComQuitPacket();
case COM_INIT_DB:
return new MySQLComInitDbPacket(payload);
case COM_FIELD_LIST:
return new MySQLComFieldListPacket(payload);
case COM_QUERY:
return new MySQLComQueryPacket(payload);
case COM_STMT_PREPARE:
return new MySQLComStmtPreparePacket(payload);
case COM_STMT_EXECUTE:
return new MySQLComStmtExecutePacket(payload);
case COM_STMT_RESET:
return new MySQLComStmtResetPacket(payload);
case COM_STMT_CLOSE:
return new MySQLComStmtClosePacket(payload);
case COM_PING:
return new MySQLComPingPacket();
default:
return new MySQLUnsupportedCommandPacket(commandPacketType);
}
}
}
commandExecuteEngine.getCommandExecutor最终创建了CommandExecutor，定位到MySQLCommandExecutorFactory。仍然是个简单工厂，根据命令类型，构造不同的CommandExecutor。
public final class MySQLCommandExecutorFactory {
public static CommandExecutor newInstance(final MySQLCommandPacketType commandPacketType, final CommandPacket commandPacket, final BackendConnection backendConnection) {
switch (commandPacketType) {
case COM_QUERY:
return new MySQLComQueryPacketExecutor((MySQLComQueryPacket) commandPacket, backendConnection);
case COM_STMT_PREPARE:
return new MySQLComStmtPrepareExecutor((MySQLComStmtPreparePacket) commandPacket, backendConnection);
case COM_STMT_EXECUTE:
return new MySQLComStmtExecuteExecutor((MySQLComStmtExecutePacket) commandPacket, backendConnection);
// ...
default:
return new MySQLUnsupportedCommandExecutor(commandPacketType);
}
}
}
CommandExecutor执行
这里重点关注查询语句的执行，对于Statement执行，属于COM_QUERY命令，由MySQLComQueryPacketExecutor处理；对于PrepareStatement执行，属于COM_STMT_EXECUTE命令，由MySQLComStmtExecuteExecutor处理。这里重点看后者。
CommandExecutor命令执行能力。
public interface CommandExecutor {
Collection<DatabasePacket> execute() throws SQLException;
}
QueryCommandExecutor查询能力。
public interface QueryCommandExecutor extends CommandExecutor {
// 是否时错误响应
boolean isErrorResponse();
// 是否是查询
boolean isQuery();
// 移动结果集游标
boolean next() throws SQLException;
// 获取结果集当前行
DatabasePacket getQueryData() throws SQLException;
}
MySQLComStmtExecuteExecutor实现COM_STMT_EXECUTE命令执行和查询能力。不过基本是委托DatabaseCommunicationEngine数据库交互引擎实现的。
public final class MySQLComStmtExecuteExecutor implements QueryCommandExecutor {
// 数据库交互引擎
private final DatabaseCommunicationEngine databaseCommunicationEngine;
// 是否是查询（实现isQuery方法）
private volatile boolean isQuery;
// 是否是错误响应（实现isErrorResponse方法）
private volatile boolean isErrorResponse;
// 当前SequenceID
private int currentSequenceId;
// 构造方法创建DatabaseCommunicationEngine
public MySQLComStmtExecuteExecutor(final MySQLComStmtExecutePacket comStmtExecutePacket, final BackendConnection backendConnection) {
databaseCommunicationEngine = DatabaseCommunicationEngineFactory.getInstance().newBinaryProtocolInstance(
backendConnection.getLogicSchema(), comStmtExecutePacket.getSql(), comStmtExecutePacket.getParameters(), backendConnection);
}
@Override
public Collection<DatabasePacket> execute() {
// 断路器是否打开（忽略）
if (ShardingProxyContext.getInstance().isCircuitBreak()) {
return Collections.singletonList(new MySQLErrPacket(1, CommonErrorCode.CIRCUIT_BREAK_MODE));
}
// 执行
BackendResponse backendResponse = databaseCommunicationEngine.execute();
// 是错误响应
if (backendResponse instanceof ErrorResponse) {
isErrorResponse = true;
return Collections.singletonList(createErrorPacket(((ErrorResponse) backendResponse).getCause()));
}
// 是更新响应
if (backendResponse instanceof UpdateResponse) {
return Collections.singletonList(createUpdatePacket((UpdateResponse) backendResponse));
}
// 是查询响应
isQuery = true;
return createQueryPacket((QueryResponse) backendResponse);
}
@Override
public boolean next() throws SQLException {
return databaseCommunicationEngine.next();
}
@Override
public MySQLPacket getQueryData() throws SQLException {
QueryData queryData = databaseCommunicationEngine.getQueryData();
return new MySQLBinaryResultSetRowPacket(++currentSequenceId, queryData.getData(), getMySQLColumnTypes(queryData));
}
}
先看一下createQueryPacket((QueryResponse) backendResponse)这个方法，如何返回查询报文。首先看一下QueryResponse的结构，这也是DatabaseCommunicationEngine返回的结果。
QueryResponse封装了查询头QueryHeader和查询结果QueryResult。其中QueryResult和sharding-jdbc是一样的，分为流式归并StreamQueryResult和内存归并MemoryQueryResult。
@RequiredArgsConstructor
@Getter
public final class QueryResponse implements BackendResponse {
// 查询头
private final List<QueryHeader> queryHeaders;
// 查询结果（分为内存归并MemoryQueryResult和流式归并StreamQueryResult）
private final List<QueryResult> queryResults = new LinkedList<>();
}
QueryHeader封装了字段的元数据信息，部分成员变量如下。
public final class QueryHeader {
private final String schema;
private final String table;
private final String columnLabel;
private final String columnName;
private final int columnLength;
private final Integer columnType;
// ... 省略其他
}
MySQLComStmtExecuteExecutor构建QueryPacket，先写一个packet表示有几个字段，再写所有字段定义packet，最后写一个eof结束packet，发现这里并没有写实际的QueryResult。写QueryResult是在执行CommandExecutor的execute方法之后，在CommandExecutorTask#executeCommand里做的，数据来源也是委托DatabaseCommunicationEngine。
private Collection<DatabasePacket> createQueryPacket(final QueryResponse backendResponse) {
Collection<DatabasePacket> result = new LinkedList<>();
// QueryHeader 字段定义列表
List<QueryHeader> queryHeader = backendResponse.getQueryHeaders();
// 第一个Packet表示查询结果有几个字段
result.add(new MySQLFieldCountPacket(++currentSequenceId, queryHeader.size()));
// 接下来几个Packet给出字段定义
for (QueryHeader each : queryHeader) {
result.add(new MySQLColumnDefinition41Packet(++currentSequenceId, each.getSchema(), each.getTable(), each.getTable(),
each.getColumnLabel(), each.getColumnName(), each.getColumnLength(), MySQLColumnType.valueOfJDBCType(each.getColumnType()), each.getDecimals()));
}
// 结束Packet
result.add(new MySQLEofPacket(++currentSequenceId));
return result;
}
4、DatabaseCommunicationEngine
DatabaseCommunicationEngine：实际实现解析、路由、重写、执行、合并流程的引擎。
public interface DatabaseCommunicationEngine {
// 执行
BackendResponse execute();
// 移动结果集游标
boolean next() throws SQLException;
// 获取结果集当前行
QueryData getQueryData() throws SQLException;
}
JDBCDatabaseCommunicationEngine：JDBC是唯一的实现引擎。
public final class JDBCDatabaseCommunicationEngine implements DatabaseCommunicationEngine {
// LogicSchema
private final LogicSchema logicSchema;
// 实际SQL
private final String sql;
// JDBC执行引擎
private final JDBCExecuteEngine executeEngine;
// 响应
private BackendResponse response;
// 查询归并结果
private MergedResult mergedResult;
}
看一下execute方法，这里我把所有的私有方法都合并到这一个方法里了，并且省略了部分代码。可以看到sharding-jdbc的MergeEngine负责将QueryResult转换为MergedResult。其余的四个关键步骤委托JDBCExecuteEngine执行。
public BackendResponse execute() {
// 解析 路由 重写
ExecutionContext executionContext = executeEngine.getJdbcExecutorWrapper().route(sql);
SQLStatementContext sqlStatementContext = executionContext.getSqlStatementContext();
// 执行
response = executeEngine.execute(executionContext);
// 合并
MergeEngine mergeEngine = new MergeEngine(logicSchema.getShardingRule().toRules(), ShardingProxyContext.getInstance().getProperties(), LogicSchemas.getInstance().getDatabaseType(), logicSchema.getMetaData().getSchema());
mergedResult = mergeEngine.merge(((QueryResponse) response).getQueryResults(), sqlStatementContext);
return response;
}
5、JDBCExecuteEngine
public final class JDBCExecuteEngine implements SQLExecuteEngine {
private final BackendConnection backendConnection;
private final JDBCExecutorWrapper jdbcExecutorWrapper;
private final SQLExecutePrepareTemplate sqlExecutePrepareTemplate;
private final SQLExecuteTemplate sqlExecuteTemplate;
}
JDBCExecuteEngine组合各种执行SQL必要的组件，适配sharding-jdbc。其中SQLExecutePrepareTemplate和SQLExecuteTemplate都是属于原来ShardingJDBC执行SQL的组件，前者负责分组、执行创建连接callback、执行创建语句callback，后者负责执行SQL。
解析&路由&重写
首先解析&路由&重写都是由JDBCExecutorWrapper的route方法实现的。这里判断配置类型，执行不同的逻辑，重点看分片配置的执行逻辑。
public final class PreparedStatementExecutorWrapper implements JDBCExecutorWrapper {
// 持有kv配置 授权信息
private static final ShardingProxyContext SHARDING_PROXY_CONTEXT = ShardingProxyContext.getInstance();
// 类比ShardingRuntimeContext 啥都有 第一章讲过
private final LogicSchema logicSchema;
// 参数列表
private final List<Object> parameters;
@Override
public ExecutionContext route(final String sql) {
if (logicSchema instanceof ShardingSchema) {
return doShardingRoute(sql);
}
if (logicSchema instanceof MasterSlaveSchema) {
return doMasterSlaveRoute(sql);
}
if (logicSchema instanceof EncryptSchema) {
return doEncryptRoute(sql);
}
if (logicSchema instanceof ShadowSchema) {
return doShadowRoute(sql);
}
return doTransparentRoute(sql);
}
}
JDBCExecutorWrapper的doShardingRoute方法，委托sharding-jdbc的PreparedQueryPrepareEngine实现解析&路由&重写。
private ExecutionContext doShardingRoute(final String sql) {
PreparedQueryPrepareEngine prepareEngine = new PreparedQueryPrepareEngine(
logicSchema.getShardingRule().toRules(),
ShardingProxyContext.getInstance().getProperties(),
logicSchema.getMetaData(),
logicSchema.getSqlParserEngine());
return prepareEngine.prepare(sql, parameters);
}
执行
重写完成以后进入JDBCExecuteEngine的execute方法，做SQL执行。
public BackendResponse execute(final ExecutionContext executionContext) throws SQLException {
SQLStatementContext sqlStatementContext = executionContext.getSqlStatementContext();
boolean isReturnGeneratedKeys = sqlStatementContext.getSqlStatement() instanceof InsertStatement;
boolean isExceptionThrown = ExecutorExceptionHandler.isExceptionThrown();
// 分组回调 backendConnection获取连接 jdbcExecutorWrapper创建Statement
ProxyJDBCExecutePrepareCallback callback01 = new ProxyJDBCExecutePrepareCallback(backendConnection, jdbcExecutorWrapper, isReturnGeneratedKeys);
// 分组
Collection<InputGroup<StatementExecuteUnit>> inputGroups = sqlExecutePrepareTemplate.getExecuteUnitGroups(executionContext.getExecutionUnits(), callback01);
// 首次执行回调 需要返回字段metadata用于组装QueryHeader jdbcExecutorWrapper负责执行
ProxySQLExecuteCallback callback02 = new ProxySQLExecuteCallback(sqlStatementContext, backendConnection, jdbcExecutorWrapper, isExceptionThrown, isReturnGeneratedKeys, true);
// 后续执行回调 不需要返回字段metadata jdbcExecutorWrapper负责执行
ProxySQLExecuteCallback callback03 = new ProxySQLExecuteCallback(sqlStatementContext, backendConnection, jdbcExecutorWrapper, isExceptionThrown, isReturnGeneratedKeys, false);
// 执行
Collection<ExecuteResponse> executeResponses = sqlExecuteTemplate.execute((Collection) inputGroups, callback02, callback03);
// 组装返回
ExecuteResponse executeResponse = executeResponses.iterator().next();
return executeResponse instanceof ExecuteQueryResponse
? getExecuteQueryResponse(((ExecuteQueryResponse) executeResponse).getQueryHeaders(), executeResponses)
: new UpdateResponse(executeResponses);
}
分组、创建连接、创建语句
JDBCExecuteEngine委托SQLExecutePrepareTemplate（sharding-jdbc），实现分组、创建连接、创建语句。重点关注传入template的callback方法（callback01），callback以外的逻辑与sharding-jdbc是一套逻辑（如何分组、创建多少连接、ConnectionMode如何决定等）。
ProxyJDBCExecutePrepareCallback
public final class ProxyJDBCExecutePrepareCallback implements SQLExecutePrepareCallback {
private final BackendConnection backendConnection;
private final JDBCExecutorWrapper jdbcExecutorWrapper;
private final boolean isReturnGeneratedKeys;
@Override
public List<Connection> getConnections(final ConnectionMode connectionMode, final String dataSourceName, final int connectionSize) throws SQLException {
return backendConnection.getConnections(connectionMode, dataSourceName, connectionSize);
}
@Override
public StatementExecuteUnit createStatementExecuteUnit(final Connection connection, final ExecutionUnit executionUnit, final ConnectionMode connectionMode) throws SQLException {
// connection创建PrepareStatement，填充params
Statement statement = jdbcExecutorWrapper.createStatement(connection, executionUnit.getSqlUnit(), isReturnGeneratedKeys);
// 如果内存限制模式，设置fetchSize防止OOM
if (connectionMode.equals(ConnectionMode.MEMORY_STRICTLY)) {
if (LogicSchemas.getInstance().getDatabaseType() instanceof MySQLDatabaseType) {
statement.setFetchSize(Integer.MIN_VALUE);
} else if (LogicSchemas.getInstance().getDatabaseType() instanceof PostgreSQLDatabaseType) {
statement.setFetchSize(1);
}
}
return new StatementExecuteUnit(executionUnit, statement, connectionMode);
}
}
ProxyJDBCExecutePrepareCallback操作BackendConnection获取数据库连接，分为事务中和非事务中两种处理方案。
// BackendConnection
private final Multimap<String, Connection> cachedConnections = LinkedHashMultimap.create();
public List<Connection> getConnections(final ConnectionMode connectionMode, final String dataSourceName, final int connectionSize) throws SQLException {
if (stateHandler.isInTransaction()) {
// BackendConnection事务中，使用缓存连接
return getConnectionsWithTransaction(connectionMode, dataSourceName, connectionSize);
} else {
// BackendConnection非事务，获取新连接
return getConnectionsWithoutTransaction(connectionMode, dataSourceName, connectionSize);
}
}
// 事务中获取连接
private List<Connection> getConnectionsWithTransaction(final ConnectionMode connectionMode, final String dataSourceName, final int connectionSize) throws SQLException {
Collection<Connection> connections;
synchronized (cachedConnections) {
connections = cachedConnections.get(dataSourceName);
}
List<Connection> result;
if (connections.size() >= connectionSize) {
result = new ArrayList<>(connections).subList(0, connectionSize);
} else if (!connections.isEmpty()) {
result = new ArrayList<>(connectionSize);
result.addAll(connections);
List<Connection> newConnections = createNewConnections(connectionMode, dataSourceName, connectionSize - connections.size());
result.addAll(newConnections);
synchronized (cachedConnections) {
cachedConnections.putAll(dataSourceName, newConnections);
}
} else {
result = createNewConnections(connectionMode, dataSourceName, connectionSize);
synchronized (cachedConnections) {
cachedConnections.putAll(dataSourceName, result);
}
}
return result;
}
// 非事务中获取连接
private List<Connection> getConnectionsWithoutTransaction(final ConnectionMode connectionMode, final String dataSourceName, final int connectionSize) throws SQLException {
List<Connection> result = getConnectionFromUnderlying(connectionMode, dataSourceName, connectionSize);
synchronized (cachedConnections) {
cachedConnections.putAll(dataSourceName, result);
}
return result;
}
private List<Connection> createNewConnections(final ConnectionMode connectionMode, final String dataSourceName, final int connectionSize) throws SQLException {
Preconditions.checkNotNull(logicSchema, "current logic schema is null");
List<Connection> result = getConnectionFromUnderlying(connectionMode, dataSourceName, connectionSize);
for (Connection each : result) {
replayMethodsInvocation(each);
}
return result;
}
private List<Connection> getConnectionFromUnderlying(final ConnectionMode connectionMode, final String dataSourceName, final int connectionSize) throws SQLException {
return logicSchema.getBackendDataSource().getConnections(connectionMode, dataSourceName, connectionSize, transactionType);
}
无论是事务中，还是非事务中，获取新连接都是通过JDBCBackendDataSource的getConnections方法获取。和AbstractConnectionAdapter#createConnections基本类似，根据连接模式，决定是否需要加锁。参照ShardingJDBC源码阅读（七）执行。
# JDBCBackendDataSource
public List<Connection> getConnections(final ConnectionMode connectionMode, final String dataSourceName, final int connectionSize, final TransactionType transactionType) throws SQLException {
DataSource dataSource = dataSources.get(dataSourceName);
if (1 == connectionSize) {
return Collections.singletonList(createConnection(transactionType, dataSourceName, dataSource));
}
// 连接限制模式，因为是一次性读取数据到内存，很快会释放资源，不需要加锁
if (ConnectionMode.CONNECTION_STRICTLY == connectionMode) {
return createConnections(transactionType, dataSourceName, dataSource, connectionSize);
}
// 内存限制模式 需要加锁
synchronized (dataSource) {
return createConnections(transactionType, dataSourceName, dataSource, connectionSize);
}
}
为什么BackendConnection获取连接要分为两种方案？
定位到Github #1516 optimize backendConnection for OLAP，在这个版本中，提供了本地事务的支持，这里是对于查询的性能优化。因为使用缓存连接需要加synchronized锁，直接获取新的连接不需要加锁。
连接创建完成后，ProxyJDBCExecutePrepareCallbackProxy执行createStatementExecuteUnit创建语句，委托JDBCExecutorWrapper实现，这里不看了，简单的通过connection创建statement。
执行
JDBCExecuteEngine委托SQLExecuteTemplate（sharding-jdbc），实现执行SQL。重点关注传入template的ProxySQLExecuteCallback（callback02、callback03），callback以外的逻辑与sharding-jdbc是一套逻辑（串行执行和并行执行的策略逻辑）。
JDBCExecuteEngine构造SQLExecuteTemplate时，决定是并行还是串行执行，和sharding-jdbc一样，LOCAL或XA事务中，串行执行；否则并行执行。
// BackendConnection
public boolean isSerialExecute() {
return stateHandler.isInTransaction()
&& (TransactionType.LOCAL == transactionType || TransactionType.XA == transactionType);
}
ProxySQLExecuteCallback执行SQL，callback02作为第一个执行callback，会获取QueryHeader（表头信息），然后同样是根据ConnectionMode选择QueryResult的实现是流式归并StreamQueryResult还是内存归并MemoryQueryResult。
private ExecuteResponse executeSQL(final Statement statement, final String sql, final ConnectionMode connectionMode, final boolean withMetadata) throws SQLException {
// ...
// 执行SQL
if (jdbcExecutorWrapper.executeSQL(statement, sql, isReturnGeneratedKeys)) {
ResultSet resultSet = statement.getResultSet();
List<QueryHeader> queryHeaders = null;
if (withMetadata) {
// 如果是第一个sql 这里就需要返回QueryHeader表头
queryHeaders = getQueryHeaders(sqlStatementContext, resultSet.getMetaData());
}
// 根据ConnectionMode选择流式归并还是内存归并
QueryResult queryResult = createQueryResult(resultSet, connectionMode);
return new ExecuteQueryResponse(queryHeaders, queryResult);
}
// ...
}
6、写客户端
回到CommandExecutorTask#executeCommand，此时SQL执行完毕，并且结果集也已经归并为MergedResult了。
private boolean executeCommand(final ChannelHandlerContext context, final PacketPayload payload, final BackendConnection backendConnection) throws SQLException {
// ...
// 执行（业务逻辑）
Collection<DatabasePacket> responsePackets = commandExecutor.execute();
// 提交到channel对应EventLoop写入ChannelOutboundBuffer
// 并没有flush，还不会给客户端，是否flush通过协议的isFlushForPerCommandPacket返回给外部
for (DatabasePacket each : responsePackets) {
context.write(each);
}
// 如果是查询，需要将查询结果写回客户端，执行CommandExecuteEngine的writeQueryData方法
if (commandExecutor instanceof QueryCommandExecutor) {
commandExecuteEngine.writeQueryData(context, backendConnection, (QueryCommandExecutor) commandExecutor, responsePackets.size());
return true;
}
// 返回这个数据库协议 最终是否需要flush
return databaseProtocolFrontendEngine.getFrontendContext().isFlushForPerCommandPacket();
}
首先把执行结果DatabasePacket通过ChannelHandlerContext提交到channel对应EventLoop写入ChannelOutboundBuffer。接着如果是查询，需要把查询结果集写回客户端，进入MySQLCommandExecuteEngine#writeQueryData。
public void writeQueryData(final ChannelHandlerContext context,
final BackendConnection backendConnection, final QueryCommandExecutor queryCommandExecutor, final int headerPackagesCount) throws SQLException {
int count = 0;
// proxy.frontend.flush.threshold 默认 128
int flushThreshold = ShardingProxyContext.getInstance().getProperties().<Integer>getValue(ConfigurationPropertyKey.PROXY_FRONTEND_FLUSH_THRESHOLD);
int currentSequenceId = 0;
while (queryCommandExecutor.next()) {
count++;
// channel不可写时，flush一次，然后阻塞等待channel可写
while (!context.channel().isWritable() && context.channel().isActive()) {
context.flush();
backendConnection.getResourceSynchronizer().doAwait();
}
// DatabaseCommunicationEngine缓存的MergedResult一行一行取出来写入ChannelOutboundBuffer
DatabasePacket dataValue = queryCommandExecutor.getQueryData();
context.write(dataValue);
// 满128，flush一次，真正写回客户端
if (flushThreshold == count) {
context.flush();
count = 0;
}
currentSequenceId++;
}
// 最后写eof包
context.write(new MySQLEofPacket(++currentSequenceId + headerPackagesCount));
}
重点关注一个参数proxy.frontend.flush.threshold，默认128，决定了查询结果集每次最大发送给客户端的行数，到了128会flush一次写给客户端。如果没满128，这里并不会实际写到客户端，仅仅保存在ChannelOutboundBuffer内存中。
MySQLComStmtExecuteExecutor#getQueryData方法获取结果集packet。
public MySQLPacket getQueryData() throws SQLException {
QueryData queryData = databaseCommunicationEngine.getQueryData();
return new MySQLBinaryResultSetRowPacket(++currentSequenceId, queryData.getData(), getMySQLColumnTypes(queryData));
}
JDBCDatabaseCommunicationEngine#getQueryData返回QueryData。可以看到在这一步，通过已经持有的MergedResult组装结果，并且在这一步实现了数据脱敏。
// 响应
private BackendResponse response;
// 查询归并结果
private MergedResult mergedResult;
public QueryData getQueryData() throws SQLException {
List<QueryHeader> queryHeaders = ((QueryResponse) response).getQueryHeaders();
List<Object> row = new ArrayList<>(queryHeaders.size());
for (int columnIndex = 1; columnIndex <= queryHeaders.size(); columnIndex++) {
Object value = mergedResult.getValue(columnIndex, Object.class);
if (isQueryWithCipherColumn && encryptRule.isPresent()) {
// ... 省略数据脱敏
}
row.add(value);
}
return new QueryData(getColumnTypes(queryHeaders), row);
}
最后executeCommand返回当前数据库协议是否需要最终执行一次flush操作，对于MySQL来说，很明显查询结果集不满128行数据的时候，还没执行flush操作，需要外部执行flush，所以返回true。
public void run() {
try (BackendConnection backendConnection = this.backendConnection;
PacketPayload payload = databaseProtocolFrontendEngine.getCodecEngine().createPacketPayload((ByteBuf) message)) {
// ...
// 对于MySQL来说这里会返回true
isNeedFlush = executeCommand(context, payload, backendConnection);
} catch (final Exception ex) {
// ...
} finally {
// 最终将剩余数据刷写回客户端
if (isNeedFlush) {
context.flush();
}
}
}
