对于负载均衡，常见的方案有：F5、HAProxy、JDBC中的loadbalance。F5不是开发者想玩就能玩的，HAProxy需要独立部署，如果部署在数据库集群之外的机器，会有额外的网络开销。JDBC的loadbalance内置于程序中，直接配置url即可，配置简单，也省去了额外的网络开销。使用方法：

jdbc:mysql:loadbalance://[host1][:port],[host2][:port][,[host3][:port]]...[/[database]]
[?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

Connector/J has long provided an effective means to distribute read/write load across multiple MySQL server instances for Cluster or source-source replication deployments. You can dynamically configure load-balanced connections, with no service outage. In-process transactions are not lost, and no application exceptions are generated if any application is trying to use that particular server instance.

试用

在TiDB中使用JDBC的LoadBalance，写一个简单的程序，设定线程数为40访问数据库。用while true的方式插入，所以应当是40个长连接。但在监控中发现，在拥有4个TiDB节点的集群，每个实例都有40个connection，这个情况有点反直觉。

试用HAProxy，虽然连接数不是很均衡，但数目是符合预期的。

创建链路

尝试看了下JDBC源码，loadbalance的逻辑大致在LoadBalancedConnectionProxy.java中，

A proxy for a dynamic com.mysql.cj.jdbc.JdbcConnection implementation that load balances requests across a series of MySQL JDBC connections

JDBC中有3种负载均衡模式，需要在url中配置loadBalanceStrategy，可选参数有 3个，使用分别是random、bestResponseTime、serverAffinity，它们都实现了BalanceStrategy接口，里面只有一个抽象方法pickConnection。默认是random模式，这里也只看这块的逻辑了。

abstract JdbcConnection pickConnection(InvocationHandler proxy, List<String> configuredHosts, Map<String, JdbcConnection> liveConnections,long[] responseTimes, int numRetries) throws SQLException;

顾名思义是实现负载均衡该选择哪个host开启连接的意思，分别对应的均衡器也就是：RandomBalanceStrategy、BestResponseTimeBalanceStrategy、ServerAffinityStrategy。

在LoadBalancedConnectionProxy@LoadBalancedConnectionProxy.java中，会case不同的负载均衡模式

switch (strategy) {
    case "random":
        this.balancer = new RandomBalanceStrategy();
        break;
    case "bestResponseTime":
        this.balancer = new BestResponseTimeBalanceStrategy();
        break;
    case "serverAffinity":
        this.balancer = new ServerAffinityStrategy(props.getProperty(PropertyKey.serverAffinityOrder.getKeyName(), null));
        break;
    default:
        this.balancer = (BalanceStrategy) Class.forName(strategy).newInstance();
}

最后会执行pickConnection()，如果this.currentConnection == null说明这个connection是刚初始化的，需要根据选定机制获取host连接。

this.currentConnection = this.balancer.pickConnection(this, hostPortList, Collections.unmodifiableMap(this.liveConnections),this.responseTimes.clone(), this.retriesAllDown);

来到RandomBalanceStrategy.java，可以看到JDBC会获取一个随机数，随机的范围是现在可以使用的hostList的size大小，之后根据随机的下标获取到要创建connection的host，之后去liveConnections这个map中获取现有已经创建的connection，有则复用，无则新建。

int random = (int) Math.floor((Math.random() * whiteList.size()));
String hostPortSpec = whiteList.get(random);
ConnectionImpl conn = (ConnectionImpl) liveConnections.get(hostPortSpec);
if (conn == null) {
    conn = ((LoadBalancedConnectionProxy) proxy).createConnectionForHost(hostPortSpec);
}
return conn;

看起来这个逻辑挺傻瓜的，没有考虑选择出来的节点上是否已经有很多connection，选到谁就是谁了。其实到这里，loadbalance的逻辑还没结束。

释放链路

在autocommit=false的情况下，在每次commit或rollback之后，JDBC都会使当前客户端线程重新选择connection。如果autocommit=true，则会一直使用最初的connection，不会重新选择。

if ("commit".equals(methodName) || "rollback".equals(methodName)) {
    this.inTransaction = false;
    pickNewConnection();
}

所以不停的执行pickNewConnection()，还是上面说的挑选connection逻辑，随机挑host创建或复用conneciton。这也就是为什么会有connection数成倍膨胀的问题，假设有host1和host2，因为每次commit结束都重新选择host，所以不论有多少个host，只要程序运行时间足够长，总会都至少被选择到一次，所以connection count = thread * host，这样来看那大部分connection平时都处于idle状态。 这里的疑惑就是，其实这也不是完全的负载均衡，毕竟是random，只有在thread足够多的情况，才会趋于均衡。再有一个问题是每次都重新选择connection可能会带来额外的开销。
在Connector / J 的官方slack提了一下这个问题，官方给的回答是

这个机制很合理，如果有3个节点，只有一个thread访问，也应该让其在3个节点中不断的随机选择host进而访问数据库，不应该绑死在某个节点上进行访问。

改造尝试

创建的connection成倍膨胀可能会造成TiDB节点资源使用率的成倍提升，在某种场景下也许对性能也有损耗。决定尝试改造一下。
于是fork了JDBC的repo，简单地实现了新的均衡模式lasting，根据每个host上的connection数量进行选择，即每次挑选connection最少的host进行创建。在commit之后不再重新pickConneciton。

测试效果

并发1000的情况下访问数据库： Random模式在前，Lasting模式在后。

可以看到，在lasting模式下，TiDB节点的运行的goroutine数量，内存使用情况等均有非常大比例的下降。Connection Idle Duration也下降了非常多。看起来还是有一些效果的。

附录 – 编译 Connector / J 8

安装 Ant

brew install ant

第三方库

下载如下第三方库，并将其放入一个文件中，

search.maven.org/artifact/or… search.maven.org/artifact/or… search.maven.org/artifact/or… search.maven.org/artifact/or… search.maven.org/artifact/or… search.maven.org/artifact/or… search.maven.org/artifact/or… search.maven.org/artifact/or… search.maven.org/artifact/or… search.maven.org/artifact/co… search.maven.org/artifact/co… search.maven.org/artifact/or… search.maven.org/artifact/or…

Clone jdbc

git clone --branch release/8.0 https://github.com/mysql/mysql-connector-j.git

配置 build.properties

这里注意，官网的说法如下，但其实不需要加 path_to，写成下面的就好

In the directory, create a file named build.properties to indicate to Ant the locations of the root directories for your JDK 1.8.x installation, as well as the location of the extra libraries. The file should contain the following property settings, with the “path_to_*” parts replaced by the appropriate file paths:

JDK在MAC系统的的默认路径为/usr/libexec/java_home -V。

com.mysql.cj.build.jdk=jdk_1.8
com.mysql.cj.extra.libs=folder_for_extra_libraries

Build

Buildfile: /Users/yuyang/IdeaProjects/mysql-connector-j/build.xml

-extra-libs-check:
  [taskdef] Could not load definitions from resource org/jacoco/ant/antlib.xml. It could not be found.

-jdk-check:

-compiler-check:

clean:
   [delete] Deleting directory /Users/yuyang/IdeaProjects/mysql-connector-j/build

-load-info-properties:

-init-copy-common:
    [mkdir] Created dir: /Users/yuyang/IdeaProjects/mysql-connector-j/build
     [copy] Copying 532 files to /Users/yuyang/IdeaProjects/mysql-connector-j/build/mysql-connector-java-8.0.22-SNAPSHOT

-init-filter-license:

-init-no-crypto:

-init-license-headers:

-init-copy:

-init-notices-commercial:

-init-notices-gpl:
      [get] Getting: file:./LICENSE
      [get] To: /Users/yuyang/IdeaProjects/mysql-connector-j/build/mysql-connector-java-8.0.22-SNAPSHOT/LICENSE
      [get] ..
     [copy] Copying 1 file to /Users/yuyang/IdeaProjects/mysql-connector-j/build/mysql-connector-java-8.0.22-SNAPSHOT

-init-info-files:
     [echo] ## INFO_BIN ##
     [echo] build-date: 2021-04-26 16:29:27 +0800
     [echo] os-info: Mac OS X x86_64 11.2.3
     [echo] compiler: javac 1.8.0_251
     [echo] build-tool: Apache Ant(TM) version 1.10.10 compiled on April 12 2021
     [echo] ## INFO_SRC ##
     [echo] version: 8.0.22-SNAPSHOT
     [echo] branch: release/8.0
     [echo] date: 2020-08-07 22:42:18 +0100
     [echo] commit: d64b664fa93e81296a377de031b8123a67e6def2
     [echo] short: d64b664f

init:

-clean-output:

compile-driver:
     [echo] Compiling MySQL Connector/J JDBC implementation with '/Library/Java/JavaVirtualMachines/jdk1.8.0_251.jdk/Contents/Home' to 'build/mysql-connector-java-8.0.22-SNAPSHOT'
    [javac] Compiling 510 source files to /Users/yuyang/IdeaProjects/mysql-connector-j/build/mysql-connector-java-8.0.22-SNAPSHOT
    [javac] Creating empty /Users/yuyang/IdeaProjects/mysql-connector-j/build/mysql-connector-java-8.0.22-SNAPSHOT/com/mysql/cj/xdevapi/package-info.class
    [javac] Creating empty /Users/yuyang/IdeaProjects/mysql-connector-j/build/mysql-connector-java-8.0.22-SNAPSHOT/com/mysql/cj/x/protobuf/package-info.class
     [java] Applying CommonChecks.
     [java] Applying TranslateExceptions.
     [java] Applying AddMethods.

-compile-integration-c3p0:
     [echo] Compiling MySQL Connector/J-c3p0 integration with '/Library/Java/JavaVirtualMachines/jdk1.8.0_251.jdk/Contents/Home' to 'build/mysql-connector-java-8.0.22-SNAPSHOT'
    [javac] Compiling 1 source file to /Users/yuyang/IdeaProjects/mysql-connector-j/build/mysql-connector-java-8.0.22-SNAPSHOT

compile-integration:

compile:

dist:
    [mkdir] Created dir: /Users/yuyang/IdeaProjects/mysql-connector-j/build/mysql-connector-java-8.0.22-SNAPSHOT/META-INF/services
     [copy] Copying 4 files to /Users/yuyang/IdeaProjects/mysql-connector-j/build/mysql-connector-java-8.0.22-SNAPSHOT/META-INF
      [jar] Building jar: /Users/yuyang/IdeaProjects/mysql-connector-j/build/mysql-connector-java-8.0.22-SNAPSHOT/mysql-connector-java-8.0.22-SNAPSHOT.jar

BUILD SUCCESSFUL
Total time: 17 seconds