MySQL 核心原理与实战：从基础到高可用架构

2.2 DDL：数据库与表的管理

创建数据库时建议指定字符集为 utf8mb4，以支持 Emoji 等特殊字符。

-- 创建数据库
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.3 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.4 DQL：核心查询（SELECT）

查询是开发中最频繁的操作。注意分页查询中的 LIMIT 用法以及聚合函数的使用。

-- 基础查询
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;

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

3.1 MySQL 逻辑架构与存储引擎

MySQL 的逻辑架构大致分为四层：连接层、服务层、引擎层、存储层。其中引擎层是可插拔的，决定了数据的存储方式。

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

3.2 索引优化与性能分析

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

• B+Tree 特性：非叶子节点只存储键值，不存储数据，使得树的高度较低；叶子节点存储数据，且形成双向链表，支持范围查询。
• 为什么不使用二叉树？ 二叉树在极端情况下会退化成链表；B 树数据分布在所有节点，范围查询需要多次回溯。

索引类型：

• 主键索引：叶子节点存整行数据。
• 二级索引（辅助索引）：叶子节点存主键值。查询时若未覆盖索引，需'回表'。
• 联合索引：(a, b, c) 遵循最左前缀原则。
• 全文索引：FULLTEXT，用于文本搜索。

最左前缀法则：

-- 假设创建了联合索引 (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;
-- ❌ 失效：跳过中间列，只能用到 name
SELECT * FROM user WHERE name = 'A' AND city = 'BJ';

常见索引失效情况：

1. 隐式类型转换：WHERE phone = 13800000000 (phone 是 varchar，缺少引号)。
2. 对索引列进行计算/函数：WHERE LEFT(name,3) = 'abc' 或 WHERE age + 1 = 20。
3. 使用 != 或 <>：通常导致全表扫描。
4. LIKE 以 % 开头：LIKE '%abc' 失效，LIKE 'abc%' 有效。

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

开启慢查询日志可以捕捉耗时 SQL：

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

使用 EXPLAIN 分析 SQL 执行路径：

EXPLAIN SELECT * FROM user WHERE name = 'zhangsan';

关键字段解读：

• type：访问类型（性能从好到差）：system > const > eq_ref > ref > range > index > ALL（全表扫描，需优化）。
• rows：预估扫描的行数，越少越好。
• Extra：
  • Using index：覆盖索引，无需回表，好！
  • Using filesort：外部排序，需优化。
  • Using temporary：使用了临时表，需优化。

3.3 事务、锁与 MVCC

事务的四大特性（ACID）：

• 原子性（Atomicity）：通过 undo log 实现。
• 一致性（Consistency）：事务前后数据完整性保持一致。
• 隔离性（Isolation）：并发事务之间互不干扰。
• 持久性（Durability）：通过 redo log 实现。

并发事务带来的问题：脏读、不可重复读、幻读。

事务隔离级别：

• MySQL 默认：REPEATABLE READ (RR)。
• MySQL 在 RR 级别下通过 Next-Key Lock（间隙锁 + 行锁）解决了幻读问题。

锁机制：

• 粒度分类：全局锁、表级锁、页级锁、行级锁。
• 行锁算法：Record Lock、Gap Lock、Next-Key Lock。
• 意向锁：表级锁，用于快速判断表中是否有行锁。

MVCC（多版本并发控制）：通过在每行记录后维护两个隐藏列：DB_TRX_ID 和 DB_ROLL_PTR，利用 Read View 实现非阻塞读。

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

4.1 复杂查询与 SQL 高级技巧

连接查询：

-- INNER JOIN: 返回匹配的行
SELECT u.name, o.order_no FROM user u INNER JOIN orders o ON u.id = o.user_id;
-- LEFT JOIN: 返回左表所有行，右表无匹配则为 NULL
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;

子查询与派生表：

-- 标量子查询
SELECT name, (SELECT MAX(salary) FROM employee) FROM employee;
-- IN 子查询
SELECT * FROM user WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
-- EXISTS 子查询 (通常用于判断存在性)
SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

窗口函数（MySQL 8.0+）：

-- 排名：ROW_NUMBER, RANK, DENSE_RANK
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK() OVER (ORDER BY salary DESC) AS rank_num,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employee;

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

-- 聚合窗口函数：计算累计和
SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS cumulative_amount FROM sales;

4.2 SQL 优化实战

海量数据分页优化： 传统 LIMIT 1000000, 10 会扫描前 100 万条数据，性能极差。

优化方案 1：延迟关联

-- 先利用覆盖索引快速定位主键，再回表取数据
SELECT * FROM user INNER JOIN (
  SELECT id FROM user ORDER BY id LIMIT 1000000, 10
) AS tmp ON user.id = tmp.id;

优化方案 2：书签记录法

-- 记住上一页的最大 ID
SELECT * FROM user WHERE id > 1000000 ORDER BY id LIMIT 10;

COUNT(*) 优化：

• COUNT(*) ≈ COUNT(1) > COUNT(主键) > COUNT(非主键列)。
• 在 InnoDB 中，COUNT(*) 需要实时扫描，建议使用额外汇总表或缓存（Redis）。

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

4.3 用户权限与安全管理

-- 创建用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPwd123!';
-- 授权
GRANT SELECT, INSERT, UPDATE ON shop.* TO 'app_user'@'192.168.1.%';
-- 刷新权限
FLUSH PRIVILEGES;
-- 查看权限
SHOW GRANTS FOR 'app_user'@'192.168.1.%';

安全最佳实践：生产环境禁用 root 远程登录，应用程序使用独立的低权限账号，定期备份并校验备份可恢复性。

4.4 备份与恢复策略

逻辑备份（mysqldump）：

# 备份所有库
mysqldump -u root -p --all-databases > all_backup.sql
# 备份指定库，并开启事务保证一致性
mysqldump -u root -p --single-transaction --quick shop > shop_backup.sql
# 恢复
mysql -u root -p shop < shop_backup.sql

物理备份（XtraBackup）：Percona XtraBackup 支持在线热备份，不锁表，适合大数据库。

二进制日志（Binlog）：记录了所有修改数据的操作，主要用于主从复制和基于时间点的恢复。

-- 查看日志状态
SHOW MASTER STATUS;
-- 查看 Binlog 内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

4.5 监控与日志分析

SHOW STATUS LIKE 'Threads_connected'; -- 当前连接数
SHOW STATUS LIKE 'Innodb_rows_read'; -- InnoDB 读取行数
SHOW PROCESSLIST; -- 查看当前执行中的 SQL

错误日志配置通常在 my.cnf 中：

[mysqld]
log-error=/var/log/mysqld.log
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow.log
long_query_time=2

4.6 高可用架构（集群）

主从复制（Master-Slave Replication）：基于 Binlog 的异步复制。确保 server-id 唯一，Master 开启 Binlog，Slave 配置 relay-log。

读写分离：通过中间件（如 MyCat、ShardingSphere-JDBC）或应用层路由，将写操作发往 Master，读操作发往 Slave。

分库分表（Sharding）：当单表数据量过大时考虑。

• 垂直拆分：按业务模块拆分库；按字段拆分表。
• 水平拆分：按主键或业务键取模（hash）分片。
• 分片键选择：需兼顾查询频率，避免跨分片查询。

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

5.1 典型业务场景实战

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

方案 1：乐观锁（CAS）

UPDATE product SET stock = stock - 1 WHERE id = 1 AND stock > 0;

利用行锁和原子性，返回受影响行数判断是否成功。适合并发不太高的场景。

方案 2：悲观锁（SELECT FOR UPDATE）

BEGIN;
SELECT stock FROM product WHERE id = 1 FOR UPDATE;
-- 业务判断逻辑，如果库存足够则更新
UPDATE product SET stock = stock - 1 WHERE id = 1;
COMMIT;

会阻塞其他事务，需注意事务时长。

排行榜实时更新（Redis + MySQL）：写入时同时写入 MySQL 和 Redis Sorted Set，查询直接从 Redis 读取，定时任务同步回 MySQL。

树形结构存储：邻接表（递归 CTE）、闭包表、枚举路径（Path Enumeration）。

5.2 常见面试题与高频问题解析

1. 为什么用 B+ 树不用 B 树？ B+ 树叶子节点存数据，非叶子节点存指针，相同高度下能容纳更多索引项，IO 次数更少；叶子节点链表结构适合范围扫描。
2. MySQL 如何保证 ACID？ 原子性（undo log）、持久性（redo log）、隔离性（锁 + MVCC）、一致性（约束 + 逻辑）。
3. 主从延迟如何处理？ 读写分离强制主库读、开启并行复制、优化从库硬件、避免大事务。
4. varchar(50) 和 varchar(200) 在存储上有什么区别？ 存储字符串占用相同，但在内存操作（如排序、临时表）中，定义长度大的消耗更多内存。

总结与学习路径

1. 初级阶段：熟练掌握 SQL 语法，理解 DDL、DML、DQL，能写出正确的增删改查。
2. 中级阶段：掌握索引原理，能够使用 Explain 分析慢 SQL，理解事务隔离级别和锁机制。
3. 高级阶段：能够进行数据库架构设计（分库分表）、高可用架构部署（主从、MHA/MGR）、性能调优（参数调优、操作系统调优）。
4. 持续学习：阅读官方文档，关注新特性（MySQL 8.0 的窗口函数、CTE、Hash Join 等），实践出真知。

推荐资源：

• 书籍：《高性能 MySQL》、《MySQL 技术内幕：InnoDB 存储引擎》。
• 工具：Navicat/DBeaver（客户端）、Sysbench（压测工具）、pt-query-digest（慢日志分析）。