MySQL 核心技术与实战全攻略

2.2 DDL：数据库与表的管理

创建数据库时建议指定字符集，避免乱码问题。建表时注意字段类型选择，比如密码字段用定长 CHAR 更合适。

-- 创建数据库
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：数据的增删改

写 DML 语句时要格外小心，尤其是 UPDATE 和 DELETE，务必带上 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）

查询是日常开发中最频繁的操作，掌握分页、聚合和排序是关键。

-- 基础查询
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 特性：非叶子节点只存键值，树的高度低；叶子节点存数据且形成双向链表，利于范围查询。
• 索引类型：
  • 主键索引：叶子节点存整行数据。
  • 二级索引：叶子节点存主键值，查询可能需要回表。
  • 联合索引：(a, b, c) 遵循最左前缀原则。

最左前缀法则示例：

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

常见索引失效情况：

1. 隐式类型转换：字符串字段没加引号。
2. 对索引列进行计算/函数：如 WHERE LEFT(name,3) = 'abc'。
3. LIKE 以 % 开头：LIKE '%abc' 会导致全表扫描。

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

开启慢查询日志是定位性能问题的第一步。

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

使用 EXPLAIN 分析 SQL 执行计划，重点关注 type（访问类型）和 rows（预估扫描行数）。Using index 表示覆盖索引，无需回表，性能最佳。

3.4 事务、锁与 MVCC

• ACID 特性：原子性（undo log）、一致性、隔离性、持久性（redo log）。
• 隔离级别：MySQL 默认为 REPEATABLE READ (RR)。RC 级别每次查询生成新 Read View，RR 级别首次查询生成一次。
• 锁机制：包括 Record Lock（记录锁）、Gap Lock（间隙锁）和 Next-Key Lock（组合锁），用于解决脏读、不可重复读和幻读问题。

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

4.1 用户权限与安全管理

生产环境严禁使用 root 远程登录，应为应用程序创建独立账号并授予最小权限。

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

4.2 备份与恢复策略

• 逻辑备份：使用 mysqldump，适合小库或跨版本迁移。
• 物理备份：Percona XtraBackup 支持在线热备，不锁表，适合大库。
• Binlog：记录所有修改操作，用于主从复制和基于时间点恢复。

4.3 监控与日志分析

常用命令查看当前状态：

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

4.4 高可用架构（集群）

• 主从复制：基于 Binlog 的异步复制，Master 写，Slave 读。
• 读写分离：通过中间件（如 ShardingSphere）将写请求发往 Master，读请求发往 Slave。
• 分库分表：单表过大（千万级）时考虑水平拆分，需关注分片键选择和跨库 Join 问题。

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

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

高并发下扣减库存，需保证不超卖。

• 乐观锁（CAS）：利用行锁和原子性，返回受影响行数判断。

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

• 悲观锁：SELECT ... FOR UPDATE，会阻塞其他事务，需注意事务时长。

5.2 排行榜实时更新

结合 Redis Sorted Set 实现实时排名，定时任务同步回 MySQL 持久化。

5.3 树形结构存储

• 邻接表：parent_id，查询子树需递归。
• 闭包表：维护路径表，查询方便但维护成本高。
• 枚举路径：存储 1/2/3/ 路径字符串。

5.4 常见面试题解析

1. 为什么用 B+ 树？ 叶子节点存数据，非叶子节点存指针，IO 次数少；链表结构适合范围扫描。
2. 如何保证 ACID？ undo log（原子性）、redo log（持久性）、锁 + MVCC（隔离性）。
3. 主从延迟处理？ 强制重要数据走主库读，开启并行复制，优化从库硬件。
4. varchar 长度区别？ 磁盘占用相同，但内存操作中按定义长度分配，影响性能。

总结与学习路径

1. 初级阶段：熟练掌握 SQL 语法，理解 DDL、DML、DQL。
2. 中级阶段：掌握索引原理，能使用 Explain 分析慢 SQL，理解事务隔离级别。
3. 高级阶段：能够进行数据库架构设计（分库分表）、高可用部署（主从、MHA/MGR）、性能调优。
4. 持续学习：阅读官方文档，关注新特性（如 MySQL 8.0 窗口函数），多动手实践搭建环境。

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