MySQL Performance Optimization: Design, Indexing, Partitioning, and Migration Strategies

Problem Overview

Using Alibaba Cloud RDS for MySQL 5.6, a user‑access log table accumulated nearly 20 million rows over six months and 40 million rows for the past year, resulting in extremely slow queries and frequent system hangs that severely impacted business.

The original schema and SQL were poorly designed, and the original developers had left, leaving the maintenance team with a difficult situation.

Solution Overview

Three approaches are proposed in order of preference:

Optimize the existing MySQL database (no code changes, lowest cost).

Upgrade to a 100 % MySQL‑compatible database (minimal code changes, higher cost).

Adopt a big‑data solution with NewSQL/NoSQL (high scalability, requires code changes).

Detailed Explanation of Solution 1: Optimize Existing MySQL

Key recommendations include:

Design tables with performance in mind.

Write optimized SQL.

Use partitioning.

Implement sharding (horizontal splitting).

Consider database sharding (multiple databases).

Database Design and Table Creation

Best practices:

Avoid NULL columns; use default numeric values.

Prefer INT over BIGINT; use UNSIGNED when possible; consider TINYINT, SMALLINT, MEDIUMINT.

Replace strings with enums or integers.

Prefer TIMESTAMP over DATETIME.

Limit the number of columns per table (ideally ≤20).

Store IP addresses as integers.

Indexing

Guidelines:

Create indexes only on columns used in WHERE or ORDER BY clauses; verify with EXPLAIN.

Avoid NULL checks in indexed columns.

Do not index low‑cardinality columns (e.g., gender).

Use prefix indexes for character columns.

Avoid using character columns as primary keys.

Prefer application‑enforced constraints over foreign keys and UNIQUE indexes.

When using composite indexes, match the column order with query conditions and remove unnecessary single‑column indexes.

In short: choose appropriate data types and indexes.

SQL Optimization

Tips:

Use LIMIT to restrict result sets.

Avoid SELECT *; list required columns.

Prefer JOIN over subqueries.

Break large DELETE/INSERT statements into smaller batches.

Enable slow‑query logs to identify bottlenecks.

Avoid column operations in WHERE clauses (e.g., avoid id + 1 = 10).

Keep SQL statements simple; one statement per CPU core.

Replace OR with IN where possible.

Avoid functions and triggers in SQL; handle logic in application code.

Do not use leading wildcards (%xxx).

Minimize JOIN usage.

Compare values of the same type.

Prefer BETWEEN over IN for continuous numeric ranges.

Original developer has left; the table cannot be altered, so some recommendations are not applicable.

Engine Choice

MyISAM vs. InnoDB:

MyISAM: table‑level locking, no transactions, no foreign keys, fast reads, supports full‑text indexes.

InnoDB: row‑level locking with MVCC, supports transactions and foreign keys, safe crash recovery, but no full‑text indexes.

Generally, MyISAM suits read‑heavy workloads, while InnoDB is better for write‑intensive scenarios.

Partitioning

MySQL 5.1+ supports horizontal partitioning, which is transparent to applications but requires partition columns in queries for optimal performance. Types include RANGE, LIST, HASH, and KEY. Benefits: larger tables, easier maintenance, faster queries when partitions are pruned, and the ability to place partitions on different storage devices.

Limitations: max 1024 partitions, primary/unique keys must include partition columns, no foreign keys, NULL values break partition pruning, and all partitions must use the same storage engine.

Example: HASH partitioning by id into 64 partitions improved query speed dramatically.

Sharding (Table Splitting)

When optimization fails, split a large table into multiple tables (vertical or horizontal). Horizontal splitting can be done by modulo on an ID (e.g., tableName_id%100). This requires code changes and increases development effort, so it is not recommended for already deployed systems.

Database Sharding (Multiple Databases)

Splitting a database into multiple instances with read/write separation can improve scalability but adds significant development and operational overhead; not recommended unless justified.

Detailed Explanation of Solution 2: Upgrade to a Compatible Database

Switch to a 100 % MySQL‑compatible database to avoid code changes while improving performance.

Open‑Source Options

TiDB – https://github.com/pingcap/tidb

CUBRID – https://www.cubrid.org/

Open‑source solutions may incur higher operational costs and may lack enterprise‑grade stability.

Cloud Options

Alibaba Cloud POLARDB – high performance, MySQL compatible, up to 100 TB storage, up to 6× MySQL speed, cost ~1/10 of commercial databases.

Alibaba Cloud OceanBase – supports both OLTP and OLAP (HTAP) workloads.

Tencent Cloud DCDB – MySQL‑compatible, automatic horizontal sharding, suitable for TB–PB scale.

POLARDB offers free MySQL data migration with ~10× performance boost at comparable price to RDS.

Detailed Explanation of Solution 3: Adopt Big‑Data Technologies

When data exceeds billions of rows, consider big‑data platforms.

Open‑Source Stack

Hadoop ecosystem (HBase, Hive) provides OLAP/OLTP capabilities but requires substantial investment (≈¥100k) for operations.

Cloud Stack

Alibaba Cloud MaxCompute + DataWorks offers a serverless, pay‑as‑you‑go big‑data solution with low cost (≈¥100) for processing hundreds of millions of rows using SQL, MapReduce, or Python scripts.

Other cloud options include Alibaba Cloud HBase, but MaxCompute was chosen for its ease of use.

Conclusion

For tables under a billion rows, optimizing the existing MySQL instance (design, indexing, partitioning) is usually sufficient. For larger scales, migrating to a high‑performance compatible cloud database or a big‑data platform provides the necessary scalability.

Original developer has left; some recommendations cannot be applied directly.