Lesson 31 of the SQL Optimization Course: Mastering Horizontal Sharding in MySQL

Introduction

For relational databases, the design of tables and SQL is written are particularly crucial. It wouldn’t be an exaggeration to say that they account for 90% of performance. So this time, specifically targeting these two major knowledge areas, we’ll conduct a detailed analysis for you, peeling back the layers.

This Series uses plain and understandable language and selects a large number of examples to elaborate on the subtleties for you.

🧑‍💻 Target audience:

• DBA
• Database developers
• Students

We will use MySQL as the demonstration database.

Introduction

In the previous chapter, we explored vertical data sharding. Today, we delve into another critical database optimization technique: horizontal sharding. Unlike vertical sharding, which splits data by fields, horizontal sharding divides datasets at the row level. This method enhances scalability without altering application logic significantly.

Core Concepts of Horizontal Sharding

1. Table Partitioning

Partitioning involves splitting a large table into smaller, manageable segments based on specific criteria (e.g., ID). Each partition operates independently but remains part of the logical whole.

2. Horizontal Partitioning vs. Sharding

While both split data horizontally, native partitioning (database-built-in) simplifies operations but lacks cross-instance scalability. Sharding, however, allows distributed deployments—ideal for large-scale systems.

MySQL Native Sharding Solutions

The MERGE Table Approach

MERGE tables aggregate multiple MyISAM tables into a single virtual table. Key features:

• Simplified Management: Query 100+ partitions with a single statement.
• Load Balancing: Distribute writes across disks or databases.
• Zero Maintenance: Metadata-only structure for quick setup/deletion.

Example Setup

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- Child tables (m1-m10)
CREATE TABLE m1 (
  id INT PRIMARY KEY,
  r1 INT,
  INDEX (r1)
) ENGINE=MyISAM;

-- Merge table definition
CREATE TABLE m_global (
  id INT PRIMARY KEY,
  r1 INT,
  INDEX (r1)
) ENGINE=MRG_MYISAM
INSERT_METHOD=LAST
UNION=(m1,m2,...,m10);

Insert Behavior

• INSERT_METHOD=LAST: Appends data to the last child table (e.g., m10).
• INSERT_METHOD=NO: Read-only mode; manual distribution required.

Limitations & Considerations

Drawbacks of MERGE Tables

• Engine Restriction: Only works with MyISAM (not InnoDB).
• Performance Overhead: Index scans across multiple tables can degrade speed.
• No Full-Text Index Support: Limits advanced querying capabilities.

Use Cases

• Archival Data: Consolidate historical logs (e.g., monthly tables).
• Read-Heavy Workloads: Simplify complex unions for frequent queries.
• Non-Critical Systems: Where data integrity is secondary.

👋 See you in the next lesson.

Recommended reading

• SQL Optimization Course
• SQL optimization knowledge
• Database industry knowledge

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.

How to use SQLFlash in a database?

• SQLFlash + MySQL
• SQLFlash + PostgreSQL