Principles and Implementation of Database Middleware

Principles and Implementation of Database Middleware

Problem Description

Database middleware (such as MyCat, ShardingSphere, etc.) are core components that solve problems like SQL routing, transaction management, and data aggregation after database and table sharding. Interviewers often require candidates to explain their core principles and design a simple middleware logic.

I. Why is Database Middleware Needed?

  1. Challenges After Database and Table Sharding:

    • Data is scattered across multiple databases or tables, making it impossible for the application layer to directly write cross-node SQL (e.g., JOIN, ORDER BY).
    • Transactions need to be managed across nodes (e.g., distributed transactions).
    • Application code should not directly couple with sharding logic.

  2. The Role of Middleware:

    • SQL Parsing and Routing: Parses SQL and determines the target node based on the shard key.
    • Result Merging: Aggregates, sorts, and paginates results from multiple nodes.
    • Transaction Coordination: Manages distributed transactions through protocols like XA or SAGA.
    • Connection Pool Management: Reduces the overhead of directly connecting to multiple databases.

II. Core Principle: SQL Routing and Execution

Step 1: SQL Parsing

The middleware first performs lexical and syntactic analysis on the SQL to generate an Abstract Syntax Tree (AST).

  • Example: 
    SELECT * FROM orders WHERE user_id = 123 AND create_time > '2023-01-01';
    After parsing, it identifies:
    • Table name: orders
    • Condition: user_id = 123 (if user_id is the shard key, route directly)
    • Non-shard condition: create_time > '2023-01-01' (needs filtering after execution on all shards)

Step 2: Shard Routing

Calculates the target shard based on the sharding strategy (e.g., modulo, range, hash).

  • Assuming the sharding rule: 
    shard_index = user_id % 4;  // 4 shards
    If user_id=123, route to shard 3 (123 % 4 = 3).

Step 3: SQL Rewriting

Changes the logical table name orders to the physical table name orders_3 and optimizes the query conditions:

-- SQL rewritten and sent to shard 3  
SELECT * FROM orders_3 WHERE user_id = 123 AND create_time > '2023-01-01';

If the query condition does not contain the shard key (e.g., only a create_time condition), a broadcast query to all shards is required.

Step 4: Result Merging

  • Simple Merging: Directly combines query results from multiple shards (e.g., UNION ALL).
  • Sort Merging: If the SQL includes ORDER BY, retrieves sorted data from each shard and then performs a global merge sort (similar to merge sort).
  • Pagination Merging: If the SQL includes LIMIT 10, 5, retrieves the top 15 rows from each shard, then globally sorts and extracts rows 10-15.

III. Transaction Management: The Challenge of Distributed Transactions

  1. Scenario:

    BEGIN;  
UPDATE orders SET amount = 100 WHERE user_id = 123;  -- shard 3  
UPDATE account SET balance = 200 WHERE user_id = 123; -- shard 1  
COMMIT;

    The two SQL statements may be in different databases, requiring atomicity.

  2. Solutions:

    • XA Protocol: Coordinates transactions across multiple databases through two-phase commit (2PC), but has performance bottlenecks.
    • SAGA Pattern: Splits the transaction into multiple local transactions, achieving eventual consistency through compensation mechanisms (e.g., reverse SQL).

IV. Designing a Simple Middleware Logic

Taking a query in a sharded database/table scenario as an example:

// 1. Parse SQL  
SQLParserResult result = SQLParser.parse("SELECT * FROM orders WHERE user_id = 123");  
String tableName = result.getTableName();  
Condition condition = result.getCondition("user_id");  

// 2. Shard Routing  
int shardKey = condition.getValue();  
int shardIndex = shardKey % shardCount;  
String physicalTable = tableName + "_" + shardIndex;  

// 3. SQL Rewriting  
String targetSQL = rewriteSQL(result, physicalTable);  

// 4. Execute and Merge  
List<Result> shardResults = executeOnShard(shardIndex, targetSQL);  
return mergeResults(shardResults);

V. Extension: Common Interview Questions

  1. How to Avoid Cross-Shard Queries?

    • When designing the shard key, prioritize high-frequency query conditions (e.g., user_id).
    • Use gene-based sharding (e.g., associating related tables through the hash value of user_id).

  2. How Does Middleware Ensure Data Consistency?

    • Read operations: Achieve read-write separation through master-slave synchronization + reading from replicas.
    • Write operations: Use XA or flexible transactions (SAGA, TCC).

  3. Difference Between Client-Side Sharding (e.g., ShardingJDBC) and Proxy Mode (e.g., MyCat)?

    • Client-Side Sharding: Lightweight but requires integration at the application layer.
    • Proxy Mode: Decouples the application from the database but introduces a single point of failure/bottleneck.

Summary

The core of database middleware is to shield the complexity of underlying data distribution. Through steps like SQL parsing, routing, rewriting, and merging, it allows applications to use sharded databases and tables as if they were a single database and table. Design requires balancing performance, consistency, and development costs.