MySQL 核心原理与实战进阶指南

2.1 DDL：数据库与表的管理

创建表时注意字符集和引擎的选择，InnoDB 是默认推荐引擎。

-- 创建数据库
CREATE DATABASE IF NOT EXISTS shop CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE shop;

-- 创建表
CREATE TABLE user (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户 ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password CHAR(32) NOT NULL COMMENT '密码 (MD5)',
    email VARCHAR(100) COMMENT '邮箱',
    status TINYINT DEFAULT 1 COMMENT '状态：0 禁用，1 启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id),
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 修改表结构
ALTER TABLE user ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE user MODIFY status TINYINT DEFAULT 0;
ALTER TABLE user DROP COLUMN phone;

2.2 DML：数据的增删改

操作数据时务必小心，尤其是更新和删除语句，必须带上 WHERE 条件。

-- 插入
INSERT INTO user (username, password, email) VALUES ('zhangsan', MD5('123456'), '[email protected]');
-- 批量插入
INSERT INTO user (username, password) VALUES ('lisi', MD5('123')), ('wangwu', MD5('456'));

-- 更新 (务必带 WHERE 条件)
UPDATE user SET status = 0 WHERE username = 'zhangsan';

-- 删除 (物理删除慎用，推荐逻辑删除)
DELETE FROM user WHERE id = 10;

2.3 DQL：核心查询

SELECT 是最常用的命令，掌握分页、聚合和排序是关键。

-- 基础查询
SELECT id, username, email FROM user WHERE status = 1;

-- 条件查询与排序
SELECT * FROM user 
WHERE created_at >= '2023-01-01' AND status IN (1, 2) 
ORDER BY created_at DESC, id ASC LIMIT 10 OFFSET 0;

-- 聚合查询
SELECT COUNT(*) AS total_count, MAX(id) AS max_id, AVG(id) AS avg_id FROM user;

第二部分：核心篇 —— 深入理解 MySQL 架构与原理

第三章：MySQL 逻辑架构与存储引擎

3.1 逻辑架构分层

理解架构有助于排查问题。MySQL 大致分为四层：

1. 连接层：处理客户端连接、认证、权限校验。
2. 服务层：负责 SQL 解析、优化、缓存及内置函数。
3. 引擎层：负责数据存储和提取，支持插件式架构。
4. 存储层：管理数据文件和日志文件。

3.2 核心存储引擎对比

• InnoDB（默认）：支持事务（ACID）、行级锁、外键、聚簇索引和 MVCC。适合高并发写场景。
• MyISAM：不支持事务和外键，使用表级锁。读多写少场景适用，现已较少使用。

第四章：索引优化与性能分析

4.1 索引的本质与数据结构

索引是帮助 MySQL 高效获取数据的排好序的数据结构。InnoDB 默认使用 B+Tree。

• B+Tree 特性：非叶子节点只存键值，树高度低；叶子节点存数据且形成链表，支持范围查询。
• 为什么不选二叉树？ 极端情况下会退化成链表；B 树数据分散，范围查询需回溯。

4.2 索引类型与使用场景

• 主键索引：叶子节点存整行数据。
• 二级索引：叶子节点存主键值，查询可能需回表。
• 联合索引：遵循最左前缀原则。
• 全文索引：用于文本搜索。

4.3 索引优化策略与失效场景

最左前缀法则：

-- 假设创建了联合索引 (name, age, city)
-- ✅ 有效：命中 name 和 age
SELECT * FROM user WHERE name = 'A' AND age = 20;
-- ✅ 有效：命中 name
SELECT * FROM user WHERE name = 'A';
-- ❌ 失效：未使用最左列
SELECT * FROM user WHERE age = 20;
-- ❌ 失效：跳过中间列
SELECT * FROM user WHERE name = 'A' AND city = 'BJ';

常见失效情况：隐式类型转换、对索引列进行计算/函数、使用 != 或 <>、LIKE 以 % 开头。

4.4 慢查询日志与 Explain 执行计划

开启慢查询日志定位瓶颈：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 记录超过 1 秒的 SQL

使用 EXPLAIN 分析 SQL 执行计划，重点关注 type（访问类型）、rows（扫描行数）和 Extra（如 Using filesort 需优化）。

第五章：事务、锁与 MVCC

5.1 事务的四大特性（ACID）

• 原子性：通过 undo log 实现。
• 一致性：数据完整性保持。
• 隔离性：并发互不干扰。
• 持久性：通过 redo log 实现。

5.2 并发事务问题

包括脏读、不可重复读和幻读。MySQL 默认隔离级别为 REPEATABLE READ (RR)。

5.3 锁机制

• 行锁算法：Record Lock（记录锁）、Gap Lock（间隙锁）、Next-Key Lock（组合锁）。
• 意向锁：表级锁，用于快速判断是否有行锁。

5.4 MVCC（多版本并发控制）

利用隐藏列 DB_TRX_ID 和 DB_ROLL_PTR 配合 Read View 实现非阻塞读。RC 级别每次生成新 Read View，RR 级别首次生成后复用。

第三部分：进阶篇 —— 高性能 SQL 与复杂场景应用

第六章：复杂查询与 SQL 高级技巧

6.1 连接查询

-- INNER JOIN: 返回匹配的行
SELECT u.name, o.order_no FROM user u INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: 返回左表所有行
SELECT u.name, o.order_no FROM user u LEFT JOIN orders o ON u.id = o.user_id;

-- 自连接：查询员工及其上级
SELECT e.name, m.name AS manager FROM employee e LEFT JOIN employee m ON e.manager_id = m.id;

6.2 子查询与派生表

-- 标量子查询
SELECT name, (SELECT MAX(salary) FROM employee) FROM employee;

-- EXISTS 子查询：通常比 IN 性能更好
SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

6.3 窗口函数（MySQL 8.0+）

窗口函数在不改变行数的情况下进行聚合，非常适合排名和累积计算。

-- 排名
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num FROM employee;

-- 分组排名
SELECT name, dept_id, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank FROM employee;

第七章：SQL 优化实战

7.1 海量数据分页优化

传统 LIMIT 1000000, 10 效率极低。可采用延迟关联或书签法。

-- 延迟关联：先定位主键再回表
SELECT * FROM user INNER JOIN (
    SELECT id FROM user ORDER BY id LIMIT 1000000, 10
) AS tmp ON user.id = tmp.id;

-- 书签记录法：记住上一页最大 ID
SELECT * FROM user WHERE id > 1000000 ORDER BY id LIMIT 10;

7.2 避免 SELECT *

减少网络传输，有利于覆盖索引命中，避免回表。

第四部分：管理与优化篇 —— 运维与高可用

第八章：用户权限与安全管理

-- 创建用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPwd123!';
-- 授权
GRANT SELECT, INSERT, UPDATE ON shop.* TO 'app_user'@'192.168.1.%';
FLUSH PRIVILEGES;

生产环境建议禁用 root 远程登录，应用程序使用独立低权限账号。

第九章：备份与恢复策略

• 逻辑备份：mysqldump，适合小库，可指定事务保证一致性。
• 物理备份：Percona XtraBackup，支持在线热备份，适合大库。
• Binlog：记录修改操作，用于主从复制和时间点恢复。

第十章：监控与日志分析

常用命令查看连接数、读取行数及当前进程：

SHOW STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST;

第十一章：高可用架构（集群）

11.1 主从复制

基于 Binlog 的异步复制。Master 写入 Binlog，Slave 读取并重放。需注意 server-id 唯一性。

11.2 读写分离

通过中间件（如 ShardingSphere）将写操作发往 Master，读操作发往 Slave。

11.3 分库分表

单表过大时考虑拆分。垂直拆分按业务模块，水平拆分按主键取模。注意跨分片 Join 的处理。

第五部分：实战与架构篇 —— 案例驱动

第十二章：典型业务场景实战

12.1 电商库存扣减（防超卖）

• 乐观锁：UPDATE product SET stock = stock - 1 WHERE id = 1 AND stock > 0; 利用行锁原子性。
• 悲观锁：SELECT ... FOR UPDATE，会阻塞其他事务，需注意时长。

12.2 排行榜实时更新

结合 Redis Sorted Set 做实时查询，定时同步回 MySQL 持久化。

12.3 树形结构存储

邻接表（递归 CTE）、闭包表或枚举路径（Path Enumeration）均可，根据查询频率选择。

第十三章：常见面试题与高频问题解析

1. 为什么用 B+ 树不用 B 树？ B+ 树叶子节点存数据，范围查询无需回溯，IO 次数更少。
2. MySQL 如何保证 ACID？ undo log 保证原子性，redo log 保证持久性，锁 + MVCC 保证隔离性。
3. 主从延迟如何处理？ 强制重要数据走主库，开启并行复制，优化从库硬件。
4. varchar 长度区别？ 磁盘占用相同，但内存操作中按定义长度分配，过长影响性能。

总结与学习路径

1. 初级阶段：熟练掌握 SQL 语法，理解 DDL、DML、DQL。
2. 中级阶段：掌握索引原理，能使用 Explain 分析慢 SQL，理解事务隔离级别。
3. 高级阶段：进行架构设计（分库分表）、高可用部署及参数调优。
4. 持续学习：阅读官方文档，关注新特性，搭建虚拟机环境实践。

推荐资源：《高性能 MySQL》、《MySQL 技术内幕：InnoDB 存储引擎》；工具 Navicat/DBeaver、Sysbench、pt-query-digest。