MySQL Fundamentals for Backend Developers
Storage engines, DDL and DML, joins and subqueries, indexes, transactions and EXPLAIN — the complete MySQL foundation.
InnoDB: the Engine that Matters
InnoDB is the default since MySQL 5.5 and the only sane choice for OLTP workloads: ACID transactions, row-level locks, crash-safe redo logs and a clustered index layout where secondary indexes point to the primary key. MyISAM and MEMORY are legacy niches.
-- 查看引擎与行数估算
SHOW TABLE STATUS LIKE 'orders'\G
-- Engine: InnoDB / Rows: 估计值（不精确，InnoDB 不维护计数）
-- 聚簇索引：数据按主键的 B+ 树物理有序存储
-- 选主键：自增整型最佳，二级索引叶子存的是主键值
-- 行格式与页：InnoDB 以 16KB 页为单位读写
-- 页内行越大，一页放的行越少，扫描越慢
SELECT page_size, table_name FROM information_schema.INNODB_TABLES;CRUD and Joins
-- DDL：建表
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status TINYINT NOT NULL DEFAULT 0,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
INDEX idx_user_time (user_id, created_at), -- 复合索引
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf88mb4mb4;
-- DML：写
INSERT INTO orders (user_id, amount) VALUES (1, 99.50), (2, 30.00);
UPDATE orders SET status = 1 WHERE id = 10; -- 必须带 WHERE！
DELETE FROM orders WHERE created_at < '2025-01-01';
-- 查询：INNER / LEFT / RIGHT JOIN
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active';
-- 聚合 + HAVING
SELECT user_id, COUNT(*) cnt, SUM(amount) total
FROM orders
WHERE status = 1
GROUP BY user_id
HAVING total > 100
ORDER BY total DESC
LIMIT 20;-- 金额存 DECIMAL，不要 FLOAT
-- DECIMAL(10,2) 精确到分，财务数据零误差
-- 字符集统一 utf8mb4（含 emoji 与生僻字）
-- DATETIME(3) / TIMESTAMP(3)：毫秒精度更利于定位问题
-- 子查询 vs JOIN：能用 JOIN 就别用相关子查询
SELECT name FROM users WHERE id IN (
SELECT user_id FROM orders WHERE amount > 1000
);
-- 上面 IN 子查询在某些版本会物化大表，JOIN 更稳UPDATE/DELETE without a WHERE in production is the number one self-inflicted incident. Always preview the WHERE with a SELECT first, and keep backups.
Indexes that Actually Help
-- 最左前缀原则
-- INDEX (a, b, c) 能命中：a / a,b / a,b,c
-- 只查 b 或 c 用不上（除非 index_merge 限场景）
SELECT * FROM t WHERE a = 1 AND b > 5; -- a 走索引，b 走范围
SELECT * FROM t WHERE b = 5; -- ✗ 全表扫描
-- 索引失效的典型写法
SELECT * FROM t WHERE DATE(created) = '2026-10-01'; -- 函数包裹列
SELECT * FROM t WHERE name LIKE '%abc'; -- 前导模糊
SELECT * FROM t WHERE a + 1 = 10; -- 运算包裹列
-- 隐式类型转换：a 是字符串，WHERE a = 10 也失效
-- 覆盖索引：查询字段都在索引里，不回表
SELECT user_id, created_at FROM orders
WHERE user_id = 7 ORDER BY created_at;
-- (user_id, created_at) 索引直接覆盖，Extra: Using index
-- 看执行计划：explain
EXPLAIN SELECT * FROM orders WHERE user_id = 7;-- EXPLAIN 关键列
-- type: const > ref > range > index > ALL（ALL = 全表）
-- key: 实际命中的索引
-- rows: 估算扫描行数
-- Extra: Using index（覆盖索引）/ Using filesort（额外排序）/ Using temporary（临时表）
-- 卡慢查询
SHOW VARIABLES LIKE 'slow_query_log%';
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒记日志
-- 长事务与锁等待
SHOW ENGINE INNODB STATUS\GIndex strategy in one line: index the columns in where + order by + group by following leftmost prefix, prefer covering indexes for hot queries, and drop unused indexes — every index costs write amplification.
Transactions and Isolation
-- ACID 在 InnoDB 的实现
-- A 原子性：undo log 回滚
-- C 一致性：业务约束 + 崩溃后的恢复
-- I 隔离性：锁 + MVCC
-- D 持久性：redo log（WAL）刷盘
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 或 ROLLBACK
-- 隔离级别（默认 REPEATABLE READ）
-- READ UNCOMMITTED 脏读
-- READ COMMITTED 不可重复读
-- REPEATABLE READ InnoDB 默认；间隙锁防幻读
-- SERIALIZABLE 最高，性能差
SET SESSION transaction_isolation = 'READ-COMMITTED';
-- 死锁日志定位
SHOW ENGINE INNODB STATUS; -- 搜 LATEST DETECTED DEADLOCKKeep transactions short: long transactions hold locks, bloat undo logs and stall replication. Wrap only the queries that must be atomic, do RPC and slow IO outside.
InnoDB：唯一值得关心的引擎
InnoDB 自 MySQL 5.5 成为默认引擎，也是 OLTP 负载唯一合理的选择：ACID 事务、行级锁、崩溃安全的 redo 日志、聚簇索引结构（二级索引指向主键）。MyISAM、MEMORY 都是历史遗留。
-- 查看引擎与行数估算
SHOW TABLE STATUS LIKE 'orders'\G
-- Engine: InnoDB / Rows: 估计值（不精确，InnoDB 不维护计数）
-- 聚簇索引：数据按主键的 B+ 树物理有序存储
-- 选主键：自增整型最佳，二级索引叶子存的是主键值
-- 行格式与页：InnoDB 以 16KB 页为单位读写
-- 页内行越大，一页放的行越少，扫描越慢
SELECT page_size, table_name FROM information_schema.INNODB_TABLES;增删改查与连接
-- DDL：建表
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status TINYINT NOT NULL DEFAULT 0,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
INDEX idx_user_time (user_id, created_at), -- 复合索引
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf88mb4mb4;
-- DML：写
INSERT INTO orders (user_id, amount) VALUES (1, 99.50), (2, 30.00);
UPDATE orders SET status = 1 WHERE id = 10; -- 必须带 WHERE！
DELETE FROM orders WHERE created_at < '2025-01-01';
-- 查询：INNER / LEFT / RIGHT JOIN
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active';
-- 聚合 + HAVING
SELECT user_id, COUNT(*) cnt, SUM(amount) total
FROM orders
WHERE status = 1
GROUP BY user_id
HAVING total > 100
ORDER BY total DESC
LIMIT 20;-- 金额存 DECIMAL，不要 FLOAT
-- DECIMAL(10,2) 精确到分，财务数据零误差
-- 字符集统一 utf8mb4（含 emoji 与生僻字）
-- DATETIME(3) / TIMESTAMP(3)：毫秒精度更利于定位问题
-- 子查询 vs JOIN：能用 JOIN 就别用相关子查询
SELECT name FROM users WHERE id IN (
SELECT user_id FROM orders WHERE amount > 1000
);
-- 上面 IN 子查询在某些版本会物化大表，JOIN 更稳线上 UPDATE/DELETE 不带 WHERE 是头号自残事故。先 SELECT 验证 WHERE 条件，并做好备份。
让索引真正生效
-- 最左前缀原则
-- INDEX (a, b, c) 能命中：a / a,b / a,b,c
-- 只查 b 或 c 用不上（除非 index_merge 限场景）
SELECT * FROM t WHERE a = 1 AND b > 5; -- a 走索引，b 走范围
SELECT * FROM t WHERE b = 5; -- ✗ 全表扫描
-- 索引失效的典型写法
SELECT * FROM t WHERE DATE(created) = '2026-10-01'; -- 函数包裹列
SELECT * FROM t WHERE name LIKE '%abc'; -- 前导模糊
SELECT * FROM t WHERE a + 1 = 10; -- 运算包裹列
-- 隐式类型转换：a 是字符串，WHERE a = 10 也失效
-- 覆盖索引：查询字段都在索引里，不回表
SELECT user_id, created_at FROM orders
WHERE user_id = 7 ORDER BY created_at;
-- (user_id, created_at) 索引直接覆盖，Extra: Using index
-- 看执行计划：explain
EXPLAIN SELECT * FROM orders WHERE user_id = 7;-- EXPLAIN 关键列
-- type: const > ref > range > index > ALL（ALL = 全表）
-- key: 实际命中的索引
-- rows: 估算扫描行数
-- Extra: Using index（覆盖索引）/ Using filesort（额外排序）/ Using temporary（临时表）
-- 卡慢查询
SHOW VARIABLES LIKE 'slow_query_log%';
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒记日志
-- 长事务与锁等待
SHOW ENGINE INNODB STATUS\G索引策略一句话：按 where + order by + group by 的字段建复合索引并遵循最左前缀，热查询尽量覆盖索引，不再使用的索引删掉——每个索引都有写入放大成本。
事务与隔离级别
-- ACID 在 InnoDB 的实现
-- A 原子性：undo log 回滚
-- C 一致性：业务约束 + 崩溃后的恢复
-- I 隔离性：锁 + MVCC
-- D 持久性：redo log（WAL）刷盘
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 或 ROLLBACK
-- 隔离级别（默认 REPEATABLE READ）
-- READ UNCOMMITTED 脏读
-- READ COMMITTED 不可重复读
-- REPEATABLE READ InnoDB 默认；间隙锁防幻读
-- SERIALIZABLE 最高，性能差
SET SESSION transaction_isolation = 'READ-COMMITTED';
-- 死锁日志定位
SHOW ENGINE INNODB STATUS; -- 搜 LATEST DETECTED DEADLOCK事务越短越好：长事务持锁、撑大 undo 日志、拖慢主从同步。只把必须原子的语句包进事务，RPC 与慢 IO 放外面。