MySQL Middleware Deep Dive: Sharding vs Middleware, Distributed Transactions, and DBLE Insights

The live program "MySQL冲冲冲" is jointly organized by the IMG community and the Aikesheng open‑source community, focusing on MySQL technical topics. Episode 5’s theme is “Sharding vs Middleware – which architecture is better?” and features a guest expert from the DBLE project.

The host introduces the concept of distributed databases, illustrating the need for sharding with examples such as Alibaba’s 11.11 sales peak, where transaction per second (TPS) can reach 300 000 and query per second (QPS) up to 1.5 million, far beyond a single MySQL instance’s capacity.

Key discussion points include:

When to consider sharding: only after evaluating whether the problem is truly throughput‑related or can be solved with indexing or vertical splitting.

How to choose a sharding key: select columns that reflect core business access patterns (e.g., order ID, time, or logical primary keys).

Sharding algorithms: hash‑based for point queries, range‑based for batch operations, aiming to minimize the number of shards a query touches.

Post‑sharding business handling: the need to adapt application logic or rely on middleware to abstract routing.

The conversation then shifts to the pros and cons of implementing sharding at the business layer versus using a middleware layer. Factors such as team size, operational overhead, ease of maintenance, and the ability to “get off the bus” (i.e., revert to a single database) are examined.

Guest speaker Yan Huqing (DBLE lead) explains that DBLE uses the XA two‑phase‑commit protocol (MySQL 5.7+), adds robust failure‑handling mechanisms, and is stateless, allowing horizontal scaling of DBLE instances. He also notes the limitations of distributed transactions, especially under extreme TPS loads.

When asked about other middleware options, Yan mentions that DBLE is the only actively maintained open‑source solution with real‑world banking deployments, while MyCat, ProxySQL, and ShardingSphere each have different trade‑offs (e.g., MyCat’s parsing limitations, ShardingSphere’s JDBC‑level approach).

The host wraps up by previewing the next episode, which will compare MySQL middleware products (ProxySQL, Spider, MyCAT, DBLE) and announce live‑stream details: every Thursday 20:30‑21:30 on Bilibili (room 22230895), with gifts for participants.

Related links are provided for deeper analysis of MyCat vs DBLE performance tuning, a case study of Industrial and Commercial Bank of China’s MySQL architecture, and DBLE public courses.