MySQL Database Performance Optimization: Principles, Architecture, Indexing, and Real‑World Cases

The article introduces the importance of monitoring and optimizing MySQL performance as data volume grows, describing typical symptoms such as latency spikes and CPU overload that can affect user experience and revenue.

Common optimization measures are listed, including adding indexes, sharding tables, and tuning connection pools. The underlying storage engine (InnoDB) is explained, showing how data is stored in 16 KB pages with a page header, directory, and user data area.

Index construction is demonstrated with a simple table creation example:

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;

Sample data insertion is shown:

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');

The article explains how InnoDB pages form a B‑Tree structure, with leaf nodes storing the clustered index (data = index) and internal nodes facilitating fast look‑ups. Visual diagrams illustrate page splitting, double‑linked leaf lists, and the evolution of a B‑Tree as data grows.

Index usage guidelines are provided, such as creating indexes only on columns used for search, sorting, or grouping; preferring high‑cardinality columns; keeping index columns small; using prefix indexes, covering indexes, auto‑increment primary keys, and removing redundant or duplicate indexes.

Four practical cases from an internet‑hospital system are described:

Inconsistent query performance caused by a string‑typed business_id column that sometimes receives numeric values, preventing index usage.

A sudden traffic surge during a promotion leading to CPU 100 % and slow queries; the root cause was missing conditional indexes in dynamic SQL.

A slow query on rx_create_time that, after adding an index, caused the optimizer to choose a less efficient plan during peak hours, resulting in CPU spikes.

Recommendations of reference books such as “How MySQL Works” and “SQL Optimization Based on Oracle”.

The execution plan fields possible_keys, key, and rows are explained, showing how the optimizer selects indexes.

Finally, the article stresses that database performance optimization is a complex topic requiring a holistic view from hardware to code, and that sharing real cases helps developers avoid costly mistakes.