Article Source:

Database middleware is a database and table

Congratulations, your company has finally grown to a certain size, and it is necessary to consider high availability and even sub-database and sub-tables. But do you know what you need to triage a database? The splitting process is complex, plan ahead, don’t wait for the real start to start, all kinds of unexpected work ensues, and even get out of hand.

This article is intended to open up the breadth of database middleware without considering the depth of implementation, and will not explain the concept and reasons for the vertical and horizontal division of library tables. Therefore, this article is aimed at professionals who have some research and development experience and are looking for a selection and splitting process.

Cut to the level

Below, the scope is defined in JAVA and MySQL. Let’s first look at the level of database sub-table cutting.

(1) The coding layer

Create multiple data sources in the same project, using if else, to route directly in code based on conditions. There are abstract classes in Spring that dynamically switch data sources, see AbstractRoutingDataSource.

If the project is not very large, you can use this method to quickly break the library. But the disadvantages are also obvious, requiring a lot of code to be written to take care of each branch. When it comes to cross-database queries, aggregations, and scenarios that require cyclic calculation results and mergers, the workload is huge.

If the project is fissioned, most of this code cannot be shared, mostly by copy. In the long run, the code will not be coded.

(2) Frame layer

This scenario is appropriate for a company where the ORM framework is unified, but in many cases it is less realistic. The main thing is to modify or enhance the functions of the existing ORM framework, and add some custom primitives or hints to the SQL to implement.

By implementing some interceptors (such as Mybatis’s Interceptor interface), adding some custom parsing to control the flow of data, although the effect is better, it will change some of the existing programming experience.

In many cases, it is necessary to modify the framework source code and is not recommended.

(3) Driving layer

Based on the various drawbacks of cutting in at the coding and framework layers, true database middleware starts at least at the driver layer. What does that mean? In fact, it is to rewrite a JDBC driver, maintain a list of routes in memory, and then forward requests to the real database connection.

Things like TDDL, ShardingJDBC, etc., are cut in at this layer.

Includes the Mysql Connector/J’s Failover protocol
(specifically “load balancing”, “replication”, “farbic”, etc.),
It is also modified directly on the driver.

The request flow direction is generally like this:

(4) Proxy layer

The database middleware of the proxy layer disguises itself as a database and accepts the link on the business side. The request on the load business side is then parsed or forwarded to the real database.

Like MySQL Router, MyCat, etc., all cut in at this layer.

The request flow direction is generally like this:

(5) Implementation layer

SQL special version support, such as Mysql cluster itself supports various features, mariadb galera cluster supports peer-to-peer dual primary, Greenplum supports sharding, etc.

The need to change storage, generally the solution, is not on the list of discussions.

Technology will eventually converge, and choosing either one is feasible. However, the final selection is affected by many factors such as developer familiarity, community activity, company relevance, official maintenance, scalability, and the company’s existing database products. Choose or develop a suitable one, and the friends will be much happier.

Comparison of the driver and proxy layers

From the above level description, it is clear that we choose or develop middleware, focusing on the driver layer and the agent layer. At both levels, you can have greater control over database connections and routing and more granular management. But the difference is also obvious.

Driving layer features

Driver layer middleware maintains many database connections. For example, a table divided into 10 libraries, each Java connection to maintain 10 database connections. If there are too many projects, there will be a connection explosion (let’s calculate that if there are 6 instances per project, minIdle in the connection pool is equal to 5, and the total number of connections for 3 projects is 10*6*5*3 = 900). A database like Postgres, where each connection corresponds to one process, can be stressful.

Proxy layer features

Heterogeneous support, limited DB support

Broker-layer middleware is just the opposite. Only one back-end relational database is supported, but multiple development languages are supported. This scenario is recommended if your system is heterogeneous and has the same SLA requirements.


Space is limited and will not be discussed too much. Visit the middleware promotion page and see a long list of features, that is, a whitelist; You can also see a long list of restrictions, also known as blacklists. Limiting how you play, after enhancing the ability to distribute, the database sub-table itself is a castrated database.

Restrictions on Use

Ensure data balance Split the data of the database as evenly as possible, such as the user database is not uniform by province, and the user id module will be more uniform without deep paging Deep paging without the slicing key will take out all the data before the number of pages taken by all the libraries in memory sorting calculation. It is easy to cause memory overflow. Reduce subqueries Subqueries can cause SQL parsing disorders, parsing errors, and minimize SQL subqueries. The principle of transaction minimumity Minimize the scope of libraries involved in stand-alone transactions, that is, reduce the number of quark operations as much as possible, and divide the libraries/tables of the same kind of operations together The data of the split database is as uniform as possible, such as the uneven user library by province, and the modulus by userid will be more uniform Special functions distinct, having, union, in, or, etc., are generally not supported. Or it is supported, and after use, it will increase the risk and need to be retrofitted.


It is recommended to focus on MyCat and ShardingJDBC. In addition, there are a lot of other middleware, unfamiliar with the advice not to be rash. Database middleware is not easy to maintain, and you will find a large number of half-dead projects.

Atlas, Kingshard, DBProxy, mysql router, MaxScale, 58 Oceanus, ArkProxy, Ctrip DAL, Tsharding, Youtube vitess, NetEase DDB, Heisenberg, proxysql, Mango, DDAL, Datahekr, MTAtlas, MTDDL, Zebra, Cobar、Cobar

Khan, almost every big factory has its own database middleware (and found a few like to take open source components plus company prefixes as products), but not for us to use.

Process solutions

No matter which level of cutting is used to sub-database and table sub-tables, it faces the following work process.

Information Collection

Statistical impact on businesses and projects


Data collation

Implementation phase

Data migration

But in general, it is still necessary to simulate this state, such as using the Canal component.


If you don’t have support, you can’t do it.

The database sub-table is a strategic technical solution, and many cases cannot be rolled back or the rollback plan is complex. If the library table to be split involves multiple business parties, the company’s technical staff is complex, and the CTO must personally take charge of the coordination and have a professional and careful architect to supervise. Unlicensed coordinators can be in an awkward position, resulting in run-out projects that are out of control of the process.

Anyone who has really experienced it will know how it hurts!

Welcome to scan the code to join the Ru ape technical exchange group, every night at 20:00 there are Java interviews, Redis, MySQL, RocketMQ, SpringCloudAlibaba, Java architecture and other technical Q&A sharing, more with the small partners to exchange technology

In addition, we recommend the 1 yuan series of courses of the Confucian Ape Classroom to you, welcome to join and learn together