An Overview of MyCat: Open‑Source Distributed Database Middleware and Its Technical Features

Author Wang Jinjian, a senior DBA at TianShi Group and core developer of the open‑source MyCat middleware, presents an in‑depth overview originally published in Programmer Magazine.

In the cloud era, traditional single‑node databases face inherent performance limits, while NoSQL solutions cannot fully replace relational features; therefore, scalable, sharded relational databases are needed.

MyCat aims to migrate existing single‑node databases and applications to the cloud at low cost, alleviating data‑storage bottlenecks as business scales; by April 2015 it was used in over 60 projects, mainly in telecom and internet transaction systems, with some tables reaching 30 billion rows per month.

MyCat is an open‑source distributed database system that implements the MySQL protocol, acting as a proxy that forwards client requests to multiple backend databases (MySQL, SQL Server, Oracle, DB2, PostgreSQL, MongoDB, etc.) via native or JDBC protocols, with its core function being horizontal table partitioning (sharding).

Beyond a simple MySQL proxy, MyCat now supports a wide range of back‑ends, presenting all storage types as regular tables that can be accessed with standard SQL, thereby reducing development complexity and accelerating delivery.

Key problems solved by MyCat include:

Connection overload – unified management of data sources makes backend clusters transparent to applications.

Innovative ER relationship sharding – parent‑child tables are routed to the same node, enabling efficient cross‑node JOINs.

Global sharding – each node can concurrently insert, update, and read data, improving read performance and cross‑node JOIN efficiency.

AI‑driven catlet support for complex cross‑shard SQL and stored procedures, expressed via special annotations.

Advanced read‑write splitting based on MySQL master‑slave replication status, automatically excluding lagging slaves.

Example annotations used in MyCat:

/*!MyCat:catlet=demo.catlets.ShareJoin / select bu., sg.* from base_user bu, sam_glucose sg where bu.id_=sg.user_id;*/

/*!MyCat: sql=select * from base_user where id_=1;*/ CALL proc_test();

/*!MyCat:catlet=demo.catlets.BatchInsertSequence */ insert into sam_test(name_) values('t1'),('t2');

/*!MyCat:catlet=demo.catlets.BatchGetSequence */ SELECT MyCat_get_seq('MyCat_TEST',100);

The technical principle of MyCat centers on "interception": incoming SQL is intercepted, analyzed for sharding, routing, read‑write splitting, and caching, then dispatched to the appropriate backend databases, with results merged and returned to the client.

For instance, the Orders table can be split into three datanodes across two MySQL servers; a query like select * from Orders where prov='wuhan' is routed to the node containing the wuhan shard, while an IN query spanning multiple provinces is sent to multiple nodes and the results are merged.

Future plans for MyCat include enriching middleware functionality with plugins, AI‑based optimization, comprehensive monitoring, and operational tools for online scaling and migration, as well as deep integration with big‑data stream engines such as Spark and Storm to provide fast OLAP capabilities and real‑time analytics.

Copyright statement: content sourced from the internet; original author and source are credited. Please notify us of any infringement.