Reducing Sharding-JDBC Database Connection Count: Analysis and Custom Partitioning Optimization

The article focuses on lowering the number of database connections required by Sharding-JDBC, investigates its routing rules, compares four transformation schemes, and proposes a custom table‑partitioning algorithm to optimize the system.

Background : JD Logistics' delivery order fulfillment centers (cp‑eofc and jdl‑uep‑ofc) use ShardingSphere's Sharding‑JDBC as a sharding middleware. The cluster consists of 16 MySQL instances, each containing 32 databases (total 512 databases). Adding a client host forces each instance to open at least 32 connections, which quickly reaches the connection limit and hinders horizontal scaling.

Sharding concepts : Database sharding splits a logical database into multiple physical databases to distribute load and improve availability, while table sharding horizontally partitions a large table into smaller tables to maintain query performance as data volume grows.

Sharding‑JDBC overview : Sharding‑JDBC is a lightweight Java framework that provides data‑sharding, distributed transactions, and governance. It supports MySQL, Oracle, SQLServer, and PostgreSQL and can be used with any Java ORM or connection pool. A typical Spring XML configuration is shown below:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:sharding="http://shardingsphere.io/schema/shardingsphere/sharding"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://shardingsphere.io/schema/shardingsphere/sharding http://shardingsphere.io/schema/shardingsphere/sharding/sharding.xsd">
    <!-- Data source ds0 -->
    <bean id="ds0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/ds0"/>
        <property name="username" value="root"/>
        <property name="password" value=""/>
    </bean>
    <!-- Data source ds1 -->
    <bean id="ds1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/ds1"/>
        <property name="username" value="root"/>
        <property name="password" value=""/>
    </bean>
    <sharding:inline-strategy id="databaseStrategy" sharding-column="user_id" algorithm-expression="ds$->{user_id % 2}"/>
    <sharding:inline-strategy id="orderTableStrategy" sharding-column="order_id" algorithm-expression="t_order$->{order_id % 2}"/>
    <sharding:data-source id="shardingDataSource">
        <sharding:sharding-rule data-source-names="ds0,ds1">
            <sharding:table-rules>
                <sharding:table-rule logic-table="t_order" actual-data-nodes="ds$->{0..1}.t_order$->{0..1}" database-strategy-ref="databaseStrategy" table-strategy-ref="orderTableStrategy"/>
                <sharding:table-rule logic-table="t_order_item" actual-data-nodes="ds$->{0..1}.t_order_item$->{0..1}" database-strategy-ref="databaseStrategy" table-strategy-ref="orderItemTableStrategy"/>
            </sharding:table-rules>
        </sharding:sharding-rule>
    </sharding:data-source>
</beans>

Problem analysis : With the current setup, a single client can create up to 32 × 5 = 160 connections per MySQL instance (assuming a max pool size of 5). During large‑scale promotions, hundreds of client machines are added, quickly exhausting the connection limits.

Feasible solutions considered were: (1) single‑instance no‑database sharding but table sharding, (2) migrating to elastic databases such as TiDB, (3) deploying Sharding‑Proxy, and (4) modifying Sharding‑JDBC itself. The first three options required new clusters or additional services and carried high migration risk, so the team chose option 4.

Optimization steps :

Reduce the number of data sources from 512 to 16 (one per MySQL instance) and keep only the first database of each instance.

Rewrite the Groovy sharding expression to use integer division: (Math.abs(order_code.hashCode()) % 512).intdiv(32) , ensuring the result is an integer.

Implement a custom PreciseShardingAlgorithm for table sharding that returns a combined "database.table" identifier.

Adjust connection‑pool parameters because 32 databases now share a single pool per instance.

After the changes, the client‑to‑cluster connection model shifted from many per‑database connections to a single pool per instance, dramatically reducing the total number of active connections (see Figures 10‑11 in the original article).

Conclusion : The article demonstrates how to analyze sharding‑JDBC routing, design a custom sharding algorithm, and reconfigure data sources to solve a connection‑count bottleneck. It also stresses that sharding decisions must consider QPS, table size, hardware, data retention, and business growth to balance benefits and risks.