MySQL Optimization Guide: Indexes, Slow Query Tuning, and Table Design

In the previous article we built a complete performance‑optimization framework; this continuation focuses on MySQL optimization, discussing common bottlenecks, index strategies, slow‑query handling, and table‑level improvements.

01 MySQL Optimization Overview

MySQL performance is usually limited by two bottlenecks: CPU and I/O. CPU saturation occurs when data is loaded into memory or read from disk. I/O bottlenecks appear when the data volume exceeds memory capacity, and in distributed deployments the network can become the limiting factor.

System‑level tools such as mpstat , iostat , sar and vmstat can be used to monitor these conditions. Beyond hardware, MySQL itself offers various tools for performance tuning.

02 MySQL Optimization Solutions

MySQL optimization can be divided into three major parts: index optimization, SQL statement optimization, and table optimization.

Index Optimization

1. Index Basics

In most applications the read/write ratio is about 10:1, and insert/update operations rarely cause performance issues. Complex queries are the main source of problems, so accelerating queries with indexes is the most effective approach.

An index is analogous to a book's table of contents; it helps the storage engine locate records quickly, dramatically improving query speed, especially for large datasets and multi‑table joins.

In MySQL an index is also called a “key”. It is a data structure used by the storage engine to find rows efficiently.

2. Index Types

Ordinary Index – the most basic index without any restrictions.

Unique Index – values must be unique (NULLs are allowed). For composite indexes, the combination of column values must be unique.

Composite (Multi‑Column) Index – created on multiple columns; it is used only when the leftmost column in the index is referenced in the query.

Primary Key Index – a special unique index; a table can have only one primary key and it cannot contain NULL values. It is usually defined when the table is created.

Full‑Text Index – used for keyword search within text columns (CHAR, VARCHAR, TEXT). It works like a search engine and is accessed via MATCH ... AGAINST . Creating a full‑text index on a large table after data insertion is faster than building it before loading data.

Ordinary indexes have no restrictions.

A unique index requires each indexed value to be distinct, though NULLs are permitted.

A primary key is a special unique index; a table can have only one and it cannot contain NULLs.

Full‑text indexes are designed for searching keywords within large text fields rather than simple equality comparisons.

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。

3. Index Optimization Tips

Avoid creating indexes on columns that contain NULL values; such indexes are not used.

Prefer short indexes or prefix lengths when possible.

Index columns that appear frequently in WHERE clauses.

Use composite indexes for queries that filter or sort on multiple columns.

Indexes are not used when a LIKE pattern starts with a wildcard (e.g., %foo ); a trailing wildcard ( foo% ) can use an index.

Avoid applying functions or expressions to indexed columns in queries.

Avoid NOT IN and <> operations on indexed columns.

03 Slow Query Optimization

1. Capturing Inefficient SQL

slow_query_log – enable to log queries that exceed a specified execution time.

long_query_time – threshold (seconds) for a query to be considered slow; commonly set to 1 s or less.

slow_query_log_file – file path where the slow‑query log is written.

log_queries_not_using_indexes – when ON, logs all queries that do not use an index.

2. Basic Steps for Slow‑Query Tuning

Run the query with SQL_NO_CACHE to verify its actual latency.

Identify the table that returns the smallest result set and start filtering there.

Use EXPLAIN to examine the execution plan and ensure it matches expectations.

For ORDER BY … LIMIT queries, let the sorting table be scanned first.

Understand the business scenario behind the query.

When adding indexes, follow the core indexing principles.

Observe the results; if they are unsatisfactory, repeat the analysis from step 1.

3. General Optimization Principles

Avoid SELECT * ; specify only required columns.

Prefer joins over sub‑queries for better performance.

Leverage EXPLAIN and PROFILE to analyze queries.

Review slow‑query logs to pinpoint long‑running statements.

When joining multiple tables, drive the join with the smaller table.

Use LIMIT for pagination on large result sets.

Enable query caching for frequently executed statements.

04 Database Table Optimization

Key recommendations for table design:

Define columns as NOT NULL whenever possible.

Use fixed‑length column types for faster queries.

Split large tables by time or other logical markers into smaller tables.

Consider vertical or horizontal partitioning: Horizontal partitioning – distribute rows across multiple tables with identical schema. Vertical partitioning – move large or rarely used columns to a separate table, establishing a one‑to‑one relationship.

In summary, MySQL performance improvement revolves around three pillars: index tuning, SQL statement refinement, and table structure optimization. In high‑concurrency environments, additional techniques such as distributed caching, CDN, and read/write splitting may also be required.

--- For more architecture articles, follow the "mikechen的互联网架构" public account.