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.

Optimizing MySQL Performance with Native Partitioning Techniques

URL: mysql-native-partitioning-techniques.md

Introduction

MySQL’s InnoDB engine, the default storage engine, lacks built-in horizontal sharding capabilities like MERGE tables. However, it offers native partitioning as a powerful alternative for managing large datasets. Partitioning enables horizontal segmentation of tables at the row level, improving scalability and query efficiency while remaining transparent to applications.

Key Advantages of MySQL Partitioned Tables

1. Enhanced Query Performance

Partitioning reduces I/O overhead by limiting data scans to specific partitions. For example:

• A non-partitioned table t1 (10 million rows) vs. a partitioned table p1 (same data, partitioned by id range).
• Range queries (e.g., id < 1,000,000) execute faster on p1 due to fewer scanned rows.

2. Simplified Operations

Partitioning streamlines tasks like data archiving, purging, and maintenance:

• Exchange partitions: Quickly swap data between tables without complex ETL.
• Truncate partitions: Remove outdated data from specific partitions (e.g., ALTER TABLE p1 TRUNCATE PARTITION p0).

Practical Use Cases and Benchmarks

Scenario 1: Optimizing Range Queries

Non-partitioned table t1:

1
2

SELECT COUNT(*) FROM t1 WHERE id < 1000000;
-- Cost: 407,495.19, Scanned rows: 2,030,006

Partitioned table p1:

1
2

SELECT COUNT(*) FROM p1 WHERE id < 1000000;
-- Cost: 99,980.09, Scanned rows: 499,369

Partitioning reduces query costs and scanned rows by ~75%.

Scenario 2: Efficient Data Updates

Update performance:

• Partitioned table p1 scans fewer rows (998,738 vs. 3,938,068 for t1) when updating a range of IDs.

Scenario 3: Data Swapping

Exchange partition with a non-partitioned table:

1
2
3
4
5

-- Create a staging table
CREATE TABLE t_p1 LIKE t1;

-- Swap partition `p1` with `t_p1` (0.07s)
ALTER TABLE p1 EXCHANGE PARTITION p1 WITH TABLE t_p1;

This operation is 10–100x faster than manual data migration for non-partitioned tables.

Scenario 4: Cleanup Efficiency

Truncate a single partition:

1

ALTER TABLE p1 TRUNCATE PARTITION p0; -- 0.07s

Deleting the same data from a non-partitioned table takes 26.8s (via DELETE).

👋 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