Comprehensive MySQL Knowledge Points and Optimization Guide

Introduction

Hello, I am Lao Tian. After previously writing about JVM and concurrent programming, I received many requests to compile a comprehensive MySQL guide, which I finally publish here.

Core MySQL Knowledge

The article lists 25+ essential MySQL questions covering normal forms, DDL/DML/DCL/TCL, architecture, storage engines, logs, transactions, isolation levels, MVCC, indexing, and optimization.

SQL Statement Summary

DDL

Data Definition Language includes CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME.

DML

Data Manipulation Language includes SELECT, INSERT, UPDATE, DELETE, GROUP BY, HAVING.

DCL

Data Control Language includes GRANT and REVOKE.

TCL

Transaction Control Language includes COMMIT and ROLLBACK.

Architecture Overview

MySQL consists of four layers: Connection layer (client authentication), Service layer (SQL parsing, optimization, caching), Engine layer (storage and transaction handling, primarily InnoDB), and Storage layer (file system storage).

Storage Engines

Show Engines Command

show engines;<br/>show variables like '%storage_engine%';

InnoDB vs MyISAM

InnoDB supports row‑level locking and transactions, while MyISAM uses table‑level locking and lacks transaction support.

Common Engines

InnoDB – default transactional engine.

MyISAM – fast reads, no transactions.

Memory – stores data in RAM, uses HASH indexes.

Merge – combines multiple MyISAM tables.

Engine Selection Guidance

Choose InnoDB for high concurrency and referential integrity; MyISAM for read‑heavy, write‑light workloads; MEMORY for small, frequently accessed tables; MERGE to bypass single‑table size limits.

Log Files

MySQL maintains eight log types: redo, undo, binlog, error, slow‑query, general, relay, and metadata logs, each serving distinct purposes such as durability, crash recovery, replication, and diagnostics.

Redo Log

Ensures transaction durability by persisting changes before commit; default files are ib_logfile1 and ib_logfile2.

Undo Log

Stores pre‑transaction images for rollback and MVCC; configurable via innodb_undo_directory, innodb_undo_logs, innodb_undo_tablespaces.

Binary Log

Used for replication and point‑in‑time recovery; records logical statements and their inverse operations.

Error Log

Records server start/stop events and diagnostic messages.

Slow Query Log

Captures queries exceeding the long_query_time threshold for later analysis.

Performance Optimization

Hardware Layer

Use high‑performance CPU modes and disable power‑saving features.

Prefer SSD/PCIe SSD for storage I/O.

Choose RAID‑10 over RAID‑5.

System Layer

Use deadline/noop I/O schedulers; avoid CFQ.

Prefer XFS over ext3/ext4 for large workloads.

Mount with noatime, nodiratime, nobarrier.

Tune vm.swappiness, vm.dirty_* and TCP parameters.

MySQL Layer

Enable thread pool (Percona/MariaDB).

Set default-storage-engine=InnoDB.

Adjust innodb_buffer_pool_size to 50‑70% of RAM.

Configure innodb_flush_log_at_trx_commit and sync_binlog based on durability needs.

Set innodb_file_per_table=1, innodb_log_file_size≈256M, etc.

Set long_query_time to 0.05 s for detailed slow‑query capture.

Schema Design & SQL Practices

Use an auto‑increment surrogate primary key.

Prefer NOT NULL columns and appropriate length.

Avoid TEXT/BLOB in frequently queried tables.

Use covering indexes and prefix indexes where suitable.

Prefer JOIN over subqueries; keep join columns indexed and type‑matched.

Transactions

A transaction is an atomic unit of work that must either fully succeed or fully fail. MySQL supports the four ACID properties and provides COMMIT and ROLLBACK operations.

Isolation Levels

Read Uncommitted – allows dirty reads.

Read Committed – default for many DBMS, permits non‑repeatable reads.

Repeatable Read – MySQL default, prevents non‑repeatable reads but may allow phantom reads, mitigated by MVCC.

Serializable – highest isolation, uses shared locks on read.

MVCC

Multi‑Version Concurrency Control is implemented in InnoDB using hidden columns DATA_TRX_ID, DATA_ROLL_PTR, and optionally DATA_ROW_ID, together with undo logs and read views. It provides snapshot reads without locking and current reads with locking.

SELECT * FROM table WHERE ?;<br/>SELECT * FROM table WHERE ? LOCK IN SHARE MODE; -- read lock<br/>SELECT * FROM table WHERE ? FOR UPDATE; -- write lock<br/>INSERT INTO table VALUES (...);<br/>UPDATE table SET ? WHERE ?;<br/>DELETE FROM table WHERE ?;

Index Details

Indexes accelerate data retrieval and are stored on disk. MySQL supports B‑Tree, Hash, Full‑Text, and R‑Tree indexes. B‑Tree is suitable for range queries, while Hash excels at equality lookups.

Index Operations

CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));<br/>ALTER mytable ADD [UNIQUE] INDEX indexName;<br/>DROP INDEX indexName ON mytable;<br/>SHOW INDEX FROM mytable;

Index Maintenance

Remove unused or duplicate indexes (pt‑duplicate‑key‑checker, pt‑index‑usage).

Monitor slow queries and adjust indexes accordingly.

SQL Optimization

EXPLAIN

Prepend EXPLAIN to a SELECT to view the execution plan, possible_keys, key used, rows estimated, and extra information such as Using filesort or Using temporary.

EXPLAIN SELECT * FROM user WHERE id = 1001;<br>EXPLAIN SELECT * FROM user WHERE id = 1001 \G

Slow Query Analysis

Enable slow_query_log and set long_query_time.

Use mysqldumpslow or pt‑query‑digest to analyze logs.

Query information_schema.processlist for long‑running statements.

Lock Optimization

Prefer row‑level locks over table‑level locks for higher concurrency; understand gap locks, next‑key locks, and lock wait timeouts to reduce deadlocks.

Common Tuning Strategies

Regularly use Percona Toolkit (pt‑online‑schema‑change, pt‑table‑checksum, pt‑table‑sync) and monitor system metrics to keep the database performant.

Conclusion

This is the fourth article in a planned series of 20 MySQL “linked‑cannon” tutorials covering fundamentals, performance, and advanced topics.