MySQL Logical Architecture Design and Performance Optimization

1. MySQL Architecture Logical Design

1.1 MySQL Logical Architecture Diagram

MySQL's architecture is divided into four layers:

Client : Various languages provide connectors (e.g., JDBC, PHP, Go) to connect to MySQL.

Server layer : Includes connection manager, query cache, parser, optimizer, executor, and built‑in functions (date, math, encryption, etc.). All cross‑engine features such as stored procedures, triggers, and views reside here.

Storage engine layer : Handles actual data storage and retrieval. It is plugin‑based and supports engines like InnoDB, MyISAM, Memory. InnoDB has been the default since MySQL 5.5.5.

Physical file layer : Stores real table data and logs (redo log, undo log, binlog, error log, query log, slow log, data files, index files). Binlog is generated by the server layer; redo log is produced only by InnoDB.

1.2 What Is High‑Performance MySQL?

High performance can be measured by query response time rather than raw CPU usage. The book *High Performance MySQL* defines performance as low response latency; a low CPU utilization often indicates poor performance.

MySQL performance mainly concerns read and write. Write performance is addressed by master‑slave replication and careful management of redo/undo logs. This article focuses on achieving high‑performance reads.

1.3 Basic Concepts

Understanding MySQL optimization requires grasping several fundamental concepts.

(1) Data Page

InnoDB uses 16 KB pages as the basic unit of interaction between disk and memory. When a page is first created it contains no user records. Inserting a row allocates space from the free‑space area and moves it to the user‑records area. When free space is exhausted, a new page is allocated.

(2) Index Lookup (Back‑Table)

InnoDB stores data in B+ trees, which consist of clustered (primary) and non‑clustered (secondary) indexes. Clustered indexes store the actual row data; secondary indexes store only the primary key values.

1.4 Cache Hit and LRU Algorithm

1.4.1 Buffer Pool

The buffer pool caches disk pages in memory. It stores the actual data rows, and the database engine reads and writes through this cache.

The buffer pool holds cached pages of 16 KB each.

Data is loaded from disk into the pool using an LRU (Least Recently Used) algorithm.

Performance depends heavily on the memory hit rate.

The pool uses a hash table keyed by "tablespace + page number" to locate cached pages.

When the pool becomes full, the LRU algorithm evicts pages based on usage.

1.4.2 LRU Algorithm

The LRU strategy is "least recently used". In InnoDB the LRU list is split 5:3 into a young region and an old region. New pages are inserted at the head of the old region; pages that stay in the old region for more than one second are promoted to the young region. Accesses to pages in the young region move the page to the front of the list.

During a full‑table scan, new pages are placed in the old region. Because a scanned page is accessed repeatedly within a short interval, it remains in the old region. After the scan moves on, the page is not accessed again and eventually gets evicted.

2. Index Optimization

2.1 Index Structures

2.1.1 Common Index Models

Typical index structures include hash tables (good for equality queries), ordered arrays (fast reads, slow writes, suited for static engines), search trees (B+ trees used by InnoDB with O(log N) complexity), and newer structures such as skip‑lists and LSM trees.

2.1.2 B+ Tree

Only leaf nodes store actual row data; internal nodes store only directory entries. All nodes are sorted by key and linked as a doubly‑linked list to support range queries. The search complexity is O(log N).

2.2 Index Optimization

2.2.1 Index Types

Indexes can be clustered (primary key) or non‑clustered (secondary). Clustered index leaves contain full rows; secondary index leaves contain only primary key values, requiring a back‑table lookup for non‑indexed columns.

2.2.2 Advantages and Disadvantages

Covering index : All required columns are present in the index, eliminating the need for a back‑table lookup. Advantages include avoiding back‑table reads and relaxing the left‑most prefix rule. Disadvantages include the overhead of maintaining many composite indexes; it is recommended to keep the number of indexes per table below five.

The left‑most prefix principle applies to composite indexes (the first N columns) and to character‑based indexes (the first N characters).

Note: A query like WHERE name LIKE '张%' can use an index because the pattern matches the leftmost characters.

When creating composite indexes, order matters; a (a,b) index makes a separate index on a unnecessary.

Index push‑down (MySQL 5.6)

Example: (name, age) SELECT * FROM t WHERE name LIKE 'zhan%' AND age = 10 AND male = 1 uses the composite index for filtering, even though the age part does not satisfy the left‑most rule; this is called index push‑down.

Full‑text index (MySQL 5.7.6)

Full‑text indexes (FTS) work on CHAR, VARCHAR, and TEXT columns in InnoDB or MyISAM tables and support Chinese characters from MySQL 5.7.6 onward.

The optimizer ultimately chooses the best index based on estimated rows scanned, temporary tables, and sorting cost.

3. Summary

By analyzing MySQL's query path architecture and storage logic, we obtain the diagram above. In everyday MySQL queries, creating appropriate indexes based on this understanding can significantly improve performance.