Master MySQL: In-Depth Guide to Storage Engines, Indexes, and Performance Optimization

MySQL Advanced Detailed Summary

1. Storage Engines

1.1 MySQL Architecture

1. Connection Layer

Handles client connection requests, including local socket and TCP/IP communication.

Performs client authentication.

Provides SSL/TLS encrypted communication.

Introduces a thread‑pool mechanism to allocate threads for authenticated clients, improving concurrency.

Validates client operation permissions.

2. Service Layer

Parses SQL queries and creates an internal parse tree for optimization.

Implements cross‑storage‑engine features such as stored procedures and functions.

For SELECT queries, it first checks the internal cache to improve performance under high read concurrency.

Handles the core SQL logic, providing the SQL interface, cache queries, analysis, and optimization.

3. Engine Layer

Implements database index structures.

Supports multiple storage engines; users can choose the appropriate engine.

Manages data storage and retrieval via APIs.

4. Storage Layer

Data is stored on the file system and interacts with the storage engine (e.g., Redo, Undo, Data, Index, Error, Binary, Query, Slow logs).

1.2 Storage Engine Introduction

Storage engines implement data storage, index creation, and data update/query. They are table‑level, not database‑level. The default engine is InnoDB if none is specified.

CREATE TABLE table_name(
  column1 type COMMENT 'column1 comment',
  column2 type COMMENT 'column2 comment',
  ...
) ENGINE=INNODB COMMENT='table comment';

Show supported engines:

SHOW ENGINES;

1.3 Storage Engine Characteristics

1.3.1 InnoDB

Logical storage structure diagram:

1.3.2 MyISAM

1.3.3 Memory

Differences between InnoDB and MyISAM

InnoDB supports transactions; MyISAM does not.

InnoDB supports foreign keys; MyISAM does not.

InnoDB supports row‑level and table‑level locks; MyISAM only supports table‑level locks.

2. Indexes

2.1 Index Overview

MySQL indexes are implemented at the storage‑engine layer. Different engines provide different index structures, mainly B‑Tree, B+Tree, and Hash.

2.2 Index Structures

B‑Tree diagram:

B+Tree diagram (standard MySQL index):

Hash diagram:

2.3 Index Types

MySQL indexes include primary key, unique, regular, and full‑text indexes.

Clustered Index (primary key) stores the full row data in leaf nodes; Secondary Index stores the primary key value in leaf nodes.

2.4 Index Usage Examples

Example query process for SELECT * FROM user WHERE name='Arm':

Use the secondary index on name to find the primary key value (e.g., 10).

Use the primary key to fetch the full row from the clustered index.

This is called a “covering index” or “back‑table lookup”.

2.5 Index Design Principles

Create indexes on large tables with frequent queries.

Index columns used in WHERE, ORDER BY, GROUP BY.

Prefer high‑selectivity columns; use unique indexes when possible.

For long VARCHAR columns, consider prefix indexes.

Prefer composite (multi‑column) indexes over many single‑column indexes.

Limit the total number of indexes to avoid high write overhead.

Define NOT NULL on indexed columns when appropriate.

3. SQL Optimization

3.1 Insert Optimization

Batch Insert :

INSERT INTO tb_test VALUES (1,'Tom'),(2,'Cat'),(3,'Jerry');

Manual Transaction Control :

START TRANSACTION;
INSERT INTO tb_test VALUES (1,'Tom'),(2,'Cat'),(3,'Jerry');
INSERT INTO tb_test VALUES (4,'Tom'),(5,'Cat'),(6,'Jerry');
COMMIT;

Inserting rows in primary‑key order yields better performance than random order.

3.1.2 Bulk Load

Use LOAD DATA LOCAL INFILE for massive data loads:

SET GLOBAL local_infile = 1;
LOAD DATA LOCAL INFILE '/root/sql1.log' INTO TABLE tb_user
FIELDS TERMINATED BY ',' LINES TERMINATED BY '
';

3.2 Primary Key Optimization

Discuss data organization, page splits, and merges for InnoDB primary keys. Visual diagrams illustrate these concepts.

3.3 ORDER BY Optimization

Use covering indexes that match the ORDER BY columns, and consider ASC/DESC order when creating composite indexes. If filesort is unavoidable, increase sort_buffer_size.

3.4 GROUP BY Optimization

Leverage indexes that satisfy the left‑most prefix rule for GROUP BY columns to avoid temporary tables and filesort.

3.5 LIMIT Optimization

For large offsets, use a sub‑query with an indexed ORDER BY to fetch the primary keys first, then join back to the table:

EXPLAIN SELECT * FROM tb_sku t, (
  SELECT id FROM tb_sku ORDER BY id LIMIT 2000000,10
) a WHERE t.id = a.id;

3.6 COUNT Optimization

Compare different counting methods (e.g., COUNT(*), COUNT(1), COUNT(DISTINCT column)) and show performance differences.

3.7 UPDATE Optimization

Emphasize using indexed WHERE clauses, batching updates in transactions, and minimizing row locking.

All optimizations combined lead to significant performance improvements.