Replication, Read/Write Splitting, and Optimization
Master-Slave Replication Mechanics
Replication is the process of synchronizing data from a Master server to one or more Slaves.
- Master: Every write operation is recorded in the Binlog.
- Slave (I/O Thread): Connects to the Master and pulls the Binlog data, saving it locally into a Relay Log.
- Slave (SQL Thread): Reads the Relay Log and applies the changes to its own database.
Replication Lag
The Master can process writes in parallel (multi-threaded), but the Slave's SQL Thread traditionally applies changes sequentially (single-threaded). This creates Replication Lag—the time gap between data appearing on the Master and the Slave.
Mitigation:
- Multi-Threaded Replication (MTR): MySQL 5.7+ allows multiple SQL threads if transactions don't conflict (Group Commit based).
- Semi-Synchronous Replication: The Master waits for at least one Slave to acknowledge receipt of the Binlog before returning success to the user.
Read/Write Splitting
To scale, we send INSERT/UPDATE/DELETE to the Master and SELECT to the Slaves.
The "Stale Read" Problem: If a user updates their profile and immediately refreshes, they might see the old data if the Slave hasn't caught up. Solutions:
- Critical Reads: Code that must have the latest data can be forced to read from the Master.
- Cache-Aside: Update a cache (Redis) on write; readers check cache first.
SQL Performance Tuning
The EXPLAIN Executive Summary
The most important tool for any MySQL developer is the EXPLAIN keyword.
| Priority | Feature | Meaning |
|---|---|---|
| type | Access Method | Aim for const, eq_ref, or ref. Avoid ALL (Full Table Scan) or index (Full Index Scan). |
| key | Used Index | If this is NULL, your index is not being used. |
| rows | Scan Est. | The number of rows MySQL thinks it needs to inspect. Smaller is better. |
| Extra | Metadata | Using filesort or Using temporary usually indicates a missing index for sorting/grouping. |
Optimization Workflow
- Identify: Turn on the Slow Query Log with a threshold (e.g.,
long_query_time = 1). - Analyze: Use
EXPLAINto find the bottleneck (Missing index, wrong join type, etc.). - Refactor:
- Avoid
SELECT *: Don't waste I/O on columns you don't need. - Pagination Optimization: Avoid
OFFSET 1,000,000. Instead, join with a subquery that retrieves only the necessary IDs first (Deferred Join). - Prefix Rule: Ensure your
WHEREclause matches the order of your composite indexes. - Data Types: Don't compare strings to numbers; it disables indexing via implicit conversion.
- Avoid
Deep Technical Insights
Why OFFSET is slow
LIMIT 1000000, 10 is slow because MySQL must actually retrieve 1,000,010 rows from the engine, discard the first million, and return only 10.
The Fix: Use Keyset Pagination (e.g., WHERE id > last_seen_id LIMIT 10). This allows the index to "jump" directly to the starting point in $O(\log N)$ instead of $O(N)$ time.
Small Tables Drive Large Tables
In a JOIN, MySQL picks one table as the "Driver" (the base). Always aim to have the table with the most restrictive WHERE clause act as the driver to minimize the number of rows that need to be joined from the second table.