Read-Write Separation Analysis

Overview

Read-write separation is a common database architecture optimization technique that separates database write and read operations to different database instances through master-slave configuration.

Main Advantages:

  1. Improve overall system processing capability
  2. Increase system availability
  3. Optimize resource utilization

Use Cases:

  1. Moderate data volume (below TB level)
  2. Read-heavy (80% read + 20% write)
  3. High-concurrency queries

Read-Write Separation Solutions

Specific Implementation Solutions

  1. Master-slave replication: Master synchronizes data changes to slaves through binlog
  2. Middleware solution: Use proxy middleware (such as MyCat, ShardingSphere) to automatically route requests
  3. Application layer implementation: Explicitly distinguish read/write data sources in code

Scaling Capabilities

  • Horizontal scaling of slaves: Can dynamically increase slave count based on query pressure
  • Load balancing: Use strategies like round-robin, weighted distribution for read requests

Table Sharding + Read-Write Separation

Sharding Strategy

When single table data volume reaches 5 million rows, table sharding is recommended:

  • Horizontal table sharding: Distribute data to multiple tables based on a field
  • Vertical table sharding: Split table by business fields

Implementation Suggestions

  1. Data migration: Need to design smooth migration solution
  2. Monitoring: Establish comprehensive monitoring system
  3. Transaction processing: Pay special attention to distributed transaction handling

Database Sharding + Read-Write Separation

Database Sharding Solutions

  1. Horizontal table sharding: Distribute to multiple tables by ID modulo, time range
  2. Vertical table sharding: Store infrequently used fields separately in extended table
  3. Database sharding: Split by region or business line

Notes

  1. Distributed transaction issues: Need to introduce XA protocol or TCC compensation mechanism
  2. Cross-database JOIN issues: Recommended to handle at application layer
  3. Global ID generation: Use Snowflake algorithm or database sequence

Master-Slave Architecture & Read-Write Separation Technical Details

Core Feature System

1. Multi-Slave Read-Write Separation Configuration

master-slave:
  name: ds_master_slave
  master-data-source-name: master_ds
  slave-data-source-names:
    - slave_ds_1
    - slave_ds_2
  load-balance-algorithm-type: ROUND_ROBIN

2. Native SQL Passthrough Support

  • Keep SQL statements executing as-is
  • Zero intrusion, doesn’t change application’s original SQL statements

3. Thread-Level Data Consistency Guarantee

  • Automatically maintain read-write consistency within the same thread
  • Write-then-read automatically routes to master database

4. Hint Force Master Routing

/* SHARDINGSPHERE_HINT: MASTER_ONLY */
SELECT account_balance FROM user_accounts WHERE user_id=12345;

Unsupported Features

  1. Data synchronization mechanism: Does not include master-slave data synchronization protocol implementation
  2. Synchronization delay handling: Business layer needs to handle master-slave delay issues
  3. Multi-master limitation: Strict single-master write mode
  4. Cross-master-slave transactions: Does not support cross-database transaction isolation guarantee

Performance Optimization Suggestions

  1. Read-write separation weight configuration: Set different traffic weights based on slave hardware configuration
  2. Consistency level selection: Eventual consistency goes to slave, strong consistency goes to master
  3. Monitoring metrics: Master-slave delay time, each slave query response time