Master MySQL Performance: Indexing, Execution Plans, and Real-World Cases

1. Common Performance Optimization Measures

When the database reaches a certain scale, lack of alerts, monitoring, and handling can degrade user experience and even cause order loss. The following measures are frequently used:

Physical level: Upgrade hardware (CPU, memory, SSD) to achieve immediate gains.

Application level: Tune connection‑pool parameters such as timeout and pool size to avoid connection starvation.

Single‑table level: Use indexes wisely; avoid missing, duplicate, redundant, or out‑of‑control indexes.

Database‑table level: Apply sharding or partitioning (e.g., by user ID, order ID, date) to reduce scan ranges.

Monitoring level: Subscribe to slow‑SQL logs and use tools like Druid for real‑time monitoring.

2. MySQL Underlying Architecture

The query execution process consists of three parts. The second part, the Server layer, is responsible for query optimization, generating execution plans, and invoking storage‑engine APIs to retrieve data.

3. MySQL Index Construction Process

MySQL primarily uses the InnoDB engine. Indexes are built as B‑Tree structures that evolve into B+Tree with leaf nodes storing the full row data (clustered index). The storage page (default 16 KB) contains a header, page directory, and user‑data area.

Pages link together: a single‑direction linked list inside a page for fast lookup, and a double‑direction linked list between pages for efficient range queries.

As data grows, pages split and form a tree structure (B+Tree). Leaf nodes store the full row (clustered index), while internal nodes store only index keys.

Example table creation and data insertion:

CREATE TABLE `t1`(
  a INT NOT NULL,
  b INT DEFAULT NULL,
  c INT DEFAULT NULL,
  d INT DEFAULT NULL,
  e VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY(a)
) ENGINE=InnoDB;

INSERT INTO test.t1 VALUES(4,3,1,1,'d');
INSERT INTO test.t1 VALUES(1,1,1,1,'a');
INSERT INTO test.t1 VALUES(8,8,8,8,'h');
INSERT INTO test.t1 VALUES(2,2,2,2,'b');
INSERT INTO test.t1 VALUES(5,2,3,5,'e');
INSERT INTO test.t1 VALUES(3,3,2,2,'c');
INSERT INTO test.t1 VALUES(7,4,5,5,'g');
INSERT INTO test.t1 VALUES(6,6,4,4,'f');

4. MySQL Index Usage Guidelines

Create indexes only on columns used for search, sort, or grouping, focusing on the WHERE clause.

Index columns whose distinct‑value count is a large proportion of total rows (e.g., phone numbers, user IDs).

Keep indexed column types as small as possible to reduce index size.

Consider prefix indexes to save space, e.g., ALTER TABLE single_table ADD INDEX idx_key1(key1(10)); Prefer covering indexes to avoid costly table lookups.

Use auto‑increment primary keys to minimize page splits in clustered indexes.

Periodically remove redundant or duplicate indexes.

5. Execution Plan Basics

Key fields in EXPLAIN output:

possible_keys: indexes that could be used.

key: the actual index chosen.

rows: estimated number of rows to read.

6. Real‑World Cases

Case 1: String vs Numeric ID

A table with ~230k rows stored a third‑party order ID as a VARCHAR and added an index. Queries sometimes failed to use the index because MySQL could not implicitly convert numeric literals to strings, causing unstable performance.

Case 2: Traffic Spike During Promotion

During a large promotion, CPU hit 100 % due to a query that lacked an index on the conditional fields (patient_id, doctor_pin). The dynamic IF clauses prevented the optimizer from using existing indexes, leading to full table scans.

Case 3: Index on rx_create_time Caused CPU Spike

After adding an index on rx_create_time, the optimizer sometimes chose it over the more selective rx_status index. When many rows matched rx_status=5, using rx_create_time forced a costly table‑row lookup for each row, driving CPU to 99 %.

Final diagnosis: the new index changed the execution plan; the optimizer preferred it during high‑load periods, leading to severe performance degradation.

7. Takeaways

Database performance optimization is a complex topic that cannot be fully covered in a single post. Real‑world experience, thorough understanding of MySQL internals, and careful index design are essential to avoid costly incidents.