定位：本文档定位 “从入门到精通”**，循序渐进——先带你建表 + 灌入真实数据 + 增删改查完整走一遍（「实战入门」章，标 ★），再逐步深入到数据类型选型、索引、事务锁、性能优化与真实业务 SQL。既覆盖语法，更强调设计取舍、生产陷阱与高性能写法**。即使从零起步，目标也是”能写出可在生产环境长期运行、可维护、高性能的 SQL”。

环境约定：除非特别说明，本文基于 MySQL 8.0（默认存储引擎 InnoDB，默认字符集 utf8mb4）。涉及 5.7 差异时会标注。

学习路线（入门 → 精通，建议按顺序学，先动手再理论）：标记 ★ 的章节强烈建议照着敲一遍。
| 章节 | 难度 | 你学到什么 |
| — | — | — |
| 一、定位与架构 | 入门 | MySQL 是什么、存储引擎怎么选 |
| 二、安装连接 | 入门 | 连上数据库、基础元命令 |
| ★ 实战入门：建表 + 真实数据 + CRUD | 入门 | 第一个完整例子（必做） |
| 三、数据类型 | 入门 | 字段怎么选类型（最易踩坑） |
| 四、约束与表设计 | 入门→进阶 | 主键/外键/范式与反范式 |
| 五、DDL | 入门→进阶 | 建表/改表（生产高风险） |
| 六、DML | 入门 | 增删改（配合实战章数据练） |
| 七、DQL 查询 | 入门→进阶 | 查（核心能力） |
| 八、索引 | 进阶 | 性能命脉（重中之重） |
| 九、事务与锁 | 进阶→精通 | 并发正确性 |
| 十、函数 | 入门 | 常用函数 |
| 十一、视图/存储过程 | 进阶 | 现代实践克制使用 |
| 十二、业务实战 | 进阶→精通 | 真实业务 SQL 长什么样 |
| 十三、性能优化 | 精通 | 排障 SOP |
| 十四、字符集 | 入门 | 不乱码的根因 |
| 十五、安全与规范 | 入门 | 防 SQL 注入 |
| 十六、备份恢复 | 进阶 | DBA 底线 |

一、MySQL 定位与整体架构

1.1 MySQL 是什么

MySQL 是关系型、客户端/服务器（C/S）架构的 OLTP（联机事务处理）数据库。所谓”关系型”，指数据以二维表（行/列）组织，表与表之间通过关系（外键/逻辑关联）建模。OLTP 强调高并发、短事务、强一致（与 OLAP 大数据分析相对）。

核心特点：

插件式存储引擎：SQL 解析/优化统一，但数据怎么存、怎么加锁由引擎决定（InnoDB / MyISAM / Memory…）。这是 MySQL 区别于 Oracle/PostgreSQL 的关键架构。
ACID 事务（InnoDB）：靠 redo log（持久性）、undo log（原子性/回滚）、锁+MVCC（隔离性）共同实现。
B+Tree 索引为主：绝大多数查询性能问题，本质都是”索引有没有用对”。

1.2 逻辑架构（面试/排错必懂）

客户端(JDBC/CLI)
   ↓
① 连接层：连接管理、鉴权、线程池（每个连接一个线程，或 thread pool 插件）
   ↓
② 服务层：SQL 接口 → 解析器(Parser) → 预处理器 → 查询优化器(Optimizer) → 执行器
          （查询缓存：8.0 已移除）
   ↓
③ 存储引擎层：InnoDB / MyISAM（真正读写数据、加锁、维护索引）
   ↓
④ 文件系统层：数据文件(.ibd)、redo/undo log、binlog

要点：优化器决定”怎么执行”（选哪个索引、谁做驱动表），它基于统计信息（Cardinality）估算，不是绝对最优——这也是为什么有时要用 FORCE INDEX 或改写 SQL 来引导它。

1.3 存储引擎对比（选型基础）

引擎	事务	锁粒度	外键	聚簇索引	适用场景
InnoDB	✅	行锁 + 表锁	✅	✅	默认，99% 业务（订单、账户、用户）
MyISAM	❌	表锁	❌	❌	只读/读多写极少、全文检索老方案、统计计数表
Memory	❌	表锁	❌	❌	临时结果、缓存表（重启即丢）
Archive	❌	行锁(insert)	❌	❌	归档日志、只插不删改

结论：新项目无脑 InnoDB。它支持事务、崩溃恢复（redo/undo）、行锁高并发、MVCC 无锁读，几乎全面优于 MyISAM。

1.4 MySQL 5.7 vs 8.0 关键差异

特性	5.7	8.0
默认字符集	`latin1`	`utf8mb4`
窗口函数	❌	✅（ROW_NUMBER/RANK…）
CTE（WITH 子句）	❌	✅
降序索引	❌	✅
隐藏索引（INVISIBLE）	❌	✅（可线上验证删索引是否安全）
原子 DDL	❌	✅（CREATE/DROP 要么全成要么全败）
持久化自增计数器	❌（重启可能重用）	✅
`CHECK` 约束	解析但忽略	✅ 真正生效
默认认证插件	`mysql_native_password`	`caching_sha2_password`

二、安装、连接与基础命令

-- 连接（命令行）
mysql -h 127.0.0.1 -P 3306 -u root -p

-- 查看版本（确认 8.0）
SELECT VERSION();          -- 8.0.xx
SELECT @@version_comment;  -- 发行说明

-- 常用元命令
STATUS;        -- 连接/字符集/运行时长
SHOW PROCESSLIST;  -- 当前连接与正在执行的 SQL（排查慢查询/锁等待）
SHOW VARIABLES LIKE '%innodb%';  -- 看系统变量
SHOW GLOBAL STATUS LIKE 'Threads_%';  -- 运行时状态

-- 库/表/列等元信息查询（比 SHOW 更适合程序化查询）
SELECT table_name FROM information_schema.tables
  WHERE table_schema = 'your_db';
SELECT column_name, data_type, column_comment
  FROM information_schema.columns
  WHERE table_schema='your_db' AND table_name='user';

实战入门：建库建表 + 真实数据 + CRUD 全流程（★ 入门第一练，建议照着敲一遍）

难度：入门。这一章不堆概念，带你从建库到增删改查完整走一遍，每一步都给出”你敲的 SQL”和”MySQL 实际返回 / 查出来的数据”。强烈建议照着在本地 MySQL 敲一遍——比看十遍都管用。后面所有进阶章节（数据类型、索引、事务…）都会基于这里打下的手感。

第 1 步：建库建表（先有”容器”）

-- 1) 建一个练习库（IF NOT EXISTS 防止重复建库报错）
CREATE DATABASE IF NOT EXISTS school
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;

-- 2) 进入这个库（后续所有 SQL 都在这个库里执行）
USE school;

-- 3) 建一张学生表
CREATE TABLE student (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '学号(自增)',
  name       VARCHAR(20) NOT NULL COMMENT '姓名',
  gender     CHAR(1)     NOT NULL DEFAULT 'M' COMMENT 'M=男 F=女',
  age        TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
  score      DECIMAL(5,2) DEFAULT 0.00 COMMENT '成绩(满分100)',
  city       VARCHAR(20) DEFAULT '' COMMENT '城市',
  created_at DATETIME    DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

说明：id 设了 AUTO_INCREMENT + PRIMARY KEY，所以插入时**不用写 id**，MySQL 自动从 1 开始递增。DEFAULT ... 表示不写该列时自动填的默认值。COMMENT 是给字段/表写的”备注”，方便团队协作。

第 2 步：插入数据（INSERT，先有”表数据”）

-- 一次插入多行（推荐，比循环单条 INSERT 快很多）
INSERT INTO student (name, gender, age, score, city) VALUES
  ('张三', 'M', 18, 88.50, '北京'),
  ('李四', 'F', 19, 92.00, '上海'),
  ('王五', 'M', 20, 75.50, '北京'),
  ('赵六', 'F', 18, 60.00, '广州'),
  ('钱七', 'M', 21, 99.00, '上海');

插入成功后 MySQL 返回：Query OK, 5 rows affected（表示成功插入 5 行）。

现在表里就有了 5 行真实数据，我们用 SELECT * 看一眼整张表：

1	SELECT * FROM student;

返回（这就是你后续所有查询的”原始数据”，请记住它）：

id	name	gender	age	score	city	created_at
1	张三	M	18	88.50	北京	2026-07-09 10:00:01
2	李四	F	19	92.00	上海	2026-07-09 10:00:01
3	王五	M	20	75.50	北京	2026-07-09 10:00:01
4	赵六	F	18	60.00	广州	2026-07-09 10:00:01
5	钱七	M	21	99.00	上海	2026-07-09 10:00:01

小提示：你看到的 created_at 时间会和我不同（= 你执行插入那一刻的时间），这是正常的。id 从 1 开始、连续递增也是正常的。

第 3 步：查询数据（SELECT，最常用的操作）

查全部 / 查指定列

1 2	SELECT * FROM student; -- 查所有列 SELECT name, score, city FROM student; -- 只查这三列

后一条返回：

name	score	city
张三	88.50	北京
李四	92.00	上海
王五	75.50	北京
赵六	60.00	广州
钱七	99.00	上海

带条件查询（WHERE）

1 2	-- 年龄大于 18 的学生 SELECT name, age FROM student WHERE age > 18;

name	age
李四	19
王五	20
钱七	21

1 2	-- 城市是北京，且成绩 >= 80 的 SELECT name, score FROM student WHERE city = '北京' AND score >= 80;

name	score
张三	88.50

💡 前端视角：WHERE 就像 JS 数组的 .filter() 条件：
arr.filter(s => s.age > 18 && s.city === '北京' && s.score >= 80)
只不过 SQL 是在数据库里过滤，能利用索引，比在代码里遍历快得多。

排序（ORDER BY）

1	SELECT name, score FROM student ORDER BY score DESC; -- 成绩从高到低

name	score
钱七	99.00
李四	92.00
张三	88.50
王五	75.50
赵六	60.00

分组统计（GROUP BY）

1
2
3

-- 每个城市分别有多少人、平均分多少
SELECT city, COUNT(*) AS 人数, ROUND(AVG(score), 2) AS 平均分
FROM student GROUP BY city;

city	人数	平均分
北京	2	82.00
上海	2	95.50
广州	1	60.00

AVG(score) 默认返回很多小数位，用 ROUND(..., 2) 保留两位更直观。

第 4 步：修改数据（UPDATE）

⚠️ 新手最容易犯的错：UPDATE 忘了写 WHERE，会把整张表每一行都改掉！动手前先想清楚”到底改哪几行”。

1
2
3

-- 把"王五"的成绩改成 80 分
UPDATE student SET score = 80.00 WHERE name = '王五';
-- 返回：Query OK, 1 row affected（只改了 1 行）

改完立刻查一下，确认改对了：

1	SELECT name, score FROM student WHERE name = '王五';

name	score
王五	80.00

第 5 步：删除数据（DELETE）

⚠️ 同理：DELETE 不写 WHERE = 删光整张表（生产环境重大事故！）。

1
2
3

-- 删除"赵六"这条记录
DELETE FROM student WHERE name = '赵六';
-- 返回：Query OK, 1 row affected

删完再看整表（赵六没了，只剩 4 行）：

1	SELECT * FROM student;

id	name	gender	age	score	city	created_at
1	张三	M	18	88.50	北京	…
2	李四	F	19	92.00	上海	…
3	王五	M	20	80.00	北京	…
5	钱七	M	21	99.00	上海	…

注意 id = 4 这一行不会再出现了——自增 id 用掉就不会复用，这是正常现象，不用担心。

第 6 步：一句话记住 CRUD

操作	SQL 关键字	前端类比
增 Create	`INSERT INTO ... VALUES ...`	`arr.push(obj)`
查 Read	`SELECT ... FROM ... WHERE`	`arr.filter()` / `arr.find()`
改 Update	`UPDATE ... SET ... WHERE`	`arr.map(x => x.id===3 ? {...x, score:80} : x)`
删 Delete	`DELETE FROM ... WHERE`	`arr.filter(x => x.id !== 4)`

✅ 到这里，你已经走完了建表 → 灌数据 → 增删改查的完整闭环。接下来的章节会在这个手感之上，讲解：

为什么这么设计：数据类型怎么选（§三）、约束与主键（§四）；

怎么写得更快更对：索引（§八）、查询优化（§十三）；

并发下怎么不出错：事务与锁（§九）；

真实业务 SQL 长什么样：§十二业务实战。

三、数据类型（最易被低估、最影响性能与正确性）

为什么数据类型这么重要？ 选错类型不只是”不优雅”，而是真实的生产事故：用 FLOAT 存金额，对账时发现少了 1 分钱（浮点误差）；用 VARCHAR(255) 存手机号，索引比 CHAR(11) 大好几倍，百万级表查询慢 3 倍；用 DATETIME 存跨国用户时间，结果不同时区用户看到的时间全是错的。下面每种类型都会告诉你：在什么业务场景用、为什么这么选、选错了会怎样。

💡 学习建议：看到一个类型时，先想”我的项目里哪个字段该用它”——把它和你正在开发的真实业务对应起来，而不是死记字节数。

3.1 数值类型

整数（精确，无误差）

类型	字节	有符号范围	无符号范围 (UNSIGNED)
`TINYINT`	1	-128 ~ 127	0 ~ 255
`SMALLINT`	2	-32768 ~ 32767	0 ~ 65535
`MEDIUMINT`	3	-8388608 ~ 8388607	0 ~ 16777215
`INT` / `INTEGER`	4	-2147483648 ~ 2147483647	0 ~ 4294967295
`BIGINT`	8	-2^63 ~ 2^63-1	0 ~ 2^64-1

UNSIGNED：非负且范围翻倍（如自增主键、年龄、数量用 UNSIGNED 更省）。
显示宽度（如 INT(11)）：仅影响 zerofill 显示，不影响存储与取值范围，8.0 已不推荐使用 INT(11)，直接写 INT。
自增主键：INT UNSIGNED AUTO_INCREMENT 约 42 亿上限，BIGINT UNSIGNED 几乎无限。

💡 真实案例：整数类型怎么选？

-- 一个电商用户表，每个字段选最合适的整数类型
CREATE TABLE user (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 主键用BIGINT：用户量可能超42亿（INT上限）
  age        TINYINT UNSIGNED DEFAULT 0,                -- 年龄0~255足够，只占1字节
  status     TINYINT NOT NULL DEFAULT 1,                -- 状态：1启用 2禁用 3注销，值很少
  login_count INT UNSIGNED DEFAULT 0,                   -- 登录次数：不会超过42亿
  balance    DECIMAL(12,2) NOT NULL DEFAULT '0.00'      -- ⚠️ 余额不能用整数！用DECIMAL（见下节）
);

选型口诀：能确定范围的就选最小类型（TINYINT < SMALLINT < INT < BIGINT），每少一个级别省 1~4 字节 × 数据量 = 百万级省出几 MB 索引空间。

定点数（金额必须用这个）

类型	说明	精度
`DECIMAL(M,D)`	精确小数，以字符串形式存储	M 总位数(1~~65)，D 小数位(0~~30)
`NUMERIC`	`DECIMAL` 的同义词	同

1
2
3

-- 金额/汇率/比例：永远用 DECIMAL，不要用 FLOAT/DOUBLE
price DECIMAL(10,2)   -- 最大 99999999.99，2 位小数
amount DECIMAL(18,6)  -- 大额/高精度

FLOAT/DOUBLE 是二进制浮点，有误差：0.1 + 0.2 ≠ 0.3（0.30000000000000004）。金融、计费、对账场景一律 DECIMAL。

💡 真实案例：FLOAT vs DECIMAL 的差距
1
2
3
4
5
6
7
8
9
10
11
-- 模拟：用 FLOAT 存金额，结果"丢钱"
CREATE TABLE test_float (amount FLOAT);
INSERT INTO test_float VALUES (0.1), (0.2);
SELECT SUM(amount) FROM test_float;
-- 返回：0.30000001192092896（不是 0.3！对账直接差了零点几分）

-- 改用 DECIMAL：精确到分
CREATE TABLE test_decimal (amount DECIMAL(10,2));
INSERT INTO test_decimal VALUES (0.10), (0.20);
SELECT SUM(amount) FROM test_decimal;
-- 返回：0.30（精确！）
使用场景：订单金额、会员余额、佣金、税费、汇率——凡是涉及”钱”的，全部用 DECIMAL。FLOAT/DOUBLE 只用在科学计算（如传感器温度、GPS 经纬度等不要求精确到分的场景）。

浮点（仅用于科学/非精确度量）

类型	字节	有效位数
`FLOAT`	4	~7 位十进制
`DOUBLE`	8	~15 位十进制

位类型

BIT(M)：存储位串（0~64 位），适合权限位标记；用 b'101' 字面量。查询返回二进制，需用 BIN()/CAST() 转换。

3.2 字符串类型

类型	说明	长度上限
`CHAR(N)`	定长，N 0~255，不足右补空格	固定 N 字符
`VARCHAR(N)`	变长，N 0~~65535(受行 65535 字节限制)，1~~2 字节记长度	变长
`TINYTEXT`/`TEXT`/`MEDIUMTEXT`/`LONGTEXT`	大文本	255 / 64KB / 16MB / 4GB
`TINYBLOB`/`BLOB`/…	二进制大对象	同上
`ENUM('a','b',...)`	枚举，存 1/2/3 整数，省空间	最多 65535 个值
`SET('a','b',...)`	集合，位掩码	最多 64 个
`JSON` (8.0)	JSON 文档，校验格式、可路径查询	——

CHAR vs VARCHAR 选择原则：

CHAR：长度固定且短（如手机号 CHAR(11)、UUID 去横杠 CHAR(32)、国家码 CHAR(2)、MD5 CHAR(32)），且对比更快（定长无碎片）。
VARCHAR：绝大多数业务字段（用户名、地址、标题），按需分配、省空间。
TEXT：不进行、单独存、不能建普通索引但可建前缀索引；大字段建议拆表，避免拖慢主表回表。

💡 真实案例：一个用户表的字符串字段选型

CREATE TABLE user_profile (
  id         BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  phone      CHAR(11) NOT NULL COMMENT '手机号，固定11位',     -- ✅ CHAR：定长，索引紧凑
  nickname   VARCHAR(32) NOT NULL DEFAULT '' COMMENT '昵称',   -- ✅ VARCHAR：长度不确定
  id_card    CHAR(18) DEFAULT NULL COMMENT '身份证号',         -- ✅ CHAR：固定18位
  email      VARCHAR(128) DEFAULT '' COMMENT '邮箱',           -- ✅ VARCHAR
  bio        VARCHAR(500) DEFAULT '' COMMENT '个人简介',       -- ✅ VARCHAR：不超过500字
  avatar_url VARCHAR(256) DEFAULT '' COMMENT '头像URL',        -- ✅ VARCHAR
  -- ❌ 错误示范：用 TEXT 存短字段（如 nickname TEXT）→ 每次查询额外IO，且不能直接加索引
  -- ❌ 错误示范：用 VARCHAR(255) 存手机号 → 浪费(255-11)*变长头=字节，索引也变大
  content    TEXT COMMENT '文章正文（很长，单独拆表更好）'      -- TEXT 适合不常检索的大文本
);

使用场景总结：手机号/身份证/MD5/UUID → CHAR；姓名/标题/URL → VARCHAR；文章正文/评论详情 → TEXT（但建议拆到副表）。

陷阱：VARCHAR 的 N 是字符数不是字节数。utf8mb4 下每个字符最多 4 字节，所以 VARCHAR(255) 最多占 255×4+2 ≈ 1022 字节。一个 InnoDB 行所有列总字节数上限 65535，所以一个表不能有太多长 VARCHAR。

ENUM/SET 注意：

优点：存整数省空间、自带校验。
缺点：枚举值修改需 ALTER TABLE（DDL，8.0 原子但仍有成本）、排序按存储序号而非字母序、部分 ORM 不友好。现代实践更推荐用 TINYINT + 代码字典，或 VARCHAR + 应用层校验。

3.3 日期时间类型

类型	字节	范围	时区敏感	说明
`DATE`	3	1000-01-01 ~ 9999-12-31	否	仅日期
`TIME`	3	-838:59:59 ~ 838:59:59	否	可表示时长
`DATETIME`	8 (5.6+)	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	否	与时区无关，原样存原样取
`TIMESTAMP`	4	1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 (UTC)	是	存UTC，按会话时区转换；自动更新
`YEAR`	1	1901 ~ 2155	否	年份

DATETIME vs TIMESTAMP 选型：

需要跨时区一致（如全球用户活动时间、审计时间）→ 用 TIMESTAMP（随 session time_zone 转换，存 UTC）。
需要大范围/不随时区漂移（如生日、历史归档、未来超过 2038 的时间）→ 用 DATETIME。
2038 年问题：TIMESTAMP 上限 2038-01-19，长期系统慎用。

-- 8.0 支持小数秒（最多 6 位，即微秒）
create_time DATETIME(3)   -- 毫秒精度
-- 自动填充（创建/更新时间经典写法）
created_at DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3)
            ON UPDATE CURRENT_TIMESTAMP(3),

💡 真实案例：DATETIME vs TIMESTAMP 选型场景

-- 场景1：国内单机房项目（时区固定），用DATETIME即可，范围大且不怕2038
CREATE TABLE article (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(128) NOT NULL,
  publish_time DATETIME NOT NULL COMMENT '发布时间（不跟时区走）',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 场景2：全球化SaaS，用户遍布各时区 → 用TIMESTAMP
-- 存UTC，读取时MySQL按当前session时区自动转换（美国用户看到美国时间，中国用户看到北京时间）
CREATE TABLE global_event (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  event_name VARCHAR(64) NOT NULL,
  event_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '自动按时区转换'
);

-- 场景3：用户生日 → DATE（不需要时分秒、不跟时区走）
ALTER TABLE user ADD COLUMN birthday DATE DEFAULT NULL COMMENT '生日';

选型口诀：全球化跨时区 → TIMESTAMP（但注意 2038 年问题！）；国内项目/不需时区转换 → DATETIME；只要日期不要时间 → DATE。

3.4 类型选择总原则（资深经验）

越小越好：能用 TINYINT 别用 INT，能用 INT 别用 BIGINT（索引更小、内存更少、缓存更多行）。
越简单越好：整型比字符串快；能用整型表示（如状态码）就不用字符串。
避免 NULL 列：NULL 使索引/统计/比较更复杂（需要额外位标记）；可用 NOT NULL + 默认值替代（如字符串 ''、数值 0）。主键必须 NOT NULL。
**IP 地址用 INT UNSIGNED**（而非 VARCHAR(15)）：INET_ATON('127.0.0.1') ↔ INET_NTOA(ip)，省 75% 空间且可范围查询。
JSON（8.0）：适合结构不稳定、无需频繁按内部字段聚合的属性（如商品扩展属性）；但频繁查询/过滤的字段应独立成列 + 索引，别全塞 JSON。

四、约束、主键与表设计

4.1 六种约束

约束	关键字	作用
主键	`PRIMARY KEY`	唯一标识一行，非空且唯一，InnoDB 用它组织数据（聚簇索引）
非空	`NOT NULL`	列不可为空
唯一	`UNIQUE [KEY]`	值唯一（允许 NULL，多个 NULL 不冲突）
默认值	`DEFAULT`	插入未指定时填充
外键	`FOREIGN KEY ... REFERENCES`	参照完整性（子表值须存在于父表）
检查	`CHECK (expr)`	8.0 真正生效，如 `CHECK (age >= 0)`

CREATE TABLE user (
  id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  username  VARCHAR(64)  NOT NULL COMMENT '登录名',
  age       TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
  status    TINYINT NOT NULL DEFAULT 1 COMMENT '1启用 2禁用',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_username (username),
  CHECK (age >= 0 AND age <= 150)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

4.2 主键设计（重要）

**自增 BIGINT**：最经典，插入顺序追加、聚簇索引无碎片、性能好。缺点：分库分表时易冲突、可被遍历猜测 ID。
UUID：全局唯一，但无序 → 聚簇索引频繁页分裂、写入性能差；若用请用 CHAR(32) 去横杠或 8.0 UUID_TO_BIN(uuid, 1)（交换时间低位，提升有序性）。
雪花 ID（Snowflake）：64 位趋势递增、分布式唯一，兼具自增的有序性与分布式的唯一性，生产首选。
业务联合主键（如订单明细 (order_id, item_id)）：仅在确属自然键且无性能顾虑时使用，否则用代理主键 + 唯一索引。

外键的取舍：外键保证一致性但带来写锁耦合、跨表级联成本、分库分表不可用。互联网高并发系统多在应用层维护参照完整性，而非数据库外键。

💡 真实案例：外键用不用？两种做法对比

-- 做法1：用外键（适合：数据一致性要求极高的内部管理系统、不需要分库分表的传统项目）
CREATE TABLE order_item (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES `order`(id) ON DELETE CASCADE
  -- CASCADE=删订单自动删明细；但代价：每次INSERT都要检查父表是否存在、加锁
);

-- 做法2：不用外键，应用层保证（适合：高并发互联网项目、微服务、分库分表）
CREATE TABLE order_item (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,          -- 只建索引，不建外键
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  KEY idx_order (order_id)            -- 查询用
);
-- 一致性由代码保证：删订单时，代码里同时删明细（或软删除）

使用场景：银行/ERP/政务等对数据一致性”零容忍”的系统 → 考虑外键；电商/社交/内容平台等高并发互联网系统 → 不用外键，靠代码+索引+定时数据校验保证一致性。

4.3 范式与反范式（冗余设计）

三范式：消除冗余、避免更新异常（适合写多、一致性要求高的核心表）。
反范式/冗余列：把频繁 JOIN 的字段冗余到主表（如订单表冗余 user_name），以空间换 JOIN 性能（适合读多、报表、宽表）。
实践：核心交易表遵循范式；统计/展示宽表适度冗余。本质是读写比的权衡。

💡 真实案例：范式 vs 反范式——订单表要不要冗余用户名？
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 范式做法（不冗余）：每次查订单列表都要 JOIN 用户表拿 username
SELECT o.order_no, o.amount, u.username
FROM `order` o JOIN user u ON o.user_id = u.id
WHERE o.status = 1 ORDER BY o.created_at DESC LIMIT 20;
-- 问题：订单表 500 万行、用户表 100 万行，高峰期 JOIN 很慢

-- 反范式做法（冗余 user_name 到订单表）：
ALTER TABLE `order` ADD COLUMN user_name VARCHAR(64) DEFAULT '' COMMENT '冗余用户名';
-- 插入订单时同时写入 user_name
-- 查询时不再需要 JOIN：
SELECT order_no, amount, user_name FROM `order`
WHERE status = 1 ORDER BY created_at DESC LIMIT 20;
-- 代价：用户改名时需要同步更新订单表的 user_name（写成本增加）
使用场景：

遵守范式：核心交易表（用户表、账户余额表）—— 数据不重复，改一处即全局生效。

适度冗余：订单列表页、商品详情页等”读多写少、经常需要关联展示”的场景 —— 用冗余字段省掉 JOIN，接受”改名时多更新一张表”的写代价。

经验法则：如果一个 JOIN 在高频查询中反复出现，且被 JOIN 的字段几乎不变（如用户名、商品名），就值得冗余。

五、DDL（数据定义语言）

5.1 建库

1
2
3

CREATE DATABASE IF NOT EXISTS shop
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;  -- 8.0 默认，ai=口音不敏感，ci=大小写不敏感

字符集铁律：全程 utf8mb4（真正的 4 字节 UTF-8，支持 emoji 与所有中文）。MySQL 的 utf8 是伪 UTF-8（仅 3 字节），存不了 emoji 和部分生僻字。排序规则 _ci 大小写不敏感、_bin 二进制精确（需区分大小写如验证码/令牌用 _bin）。

5.2 建表（完整要素）

CREATE TABLE `order` (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no    VARCHAR(32) NOT NULL,
  user_id     BIGINT UNSIGNED NOT NULL,
  amount      DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  status      TINYINT NOT NULL DEFAULT 0 COMMENT '0待支付 1已支付 2已取消',
  created_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_created (user_id, created_at)   -- 组合索引，支撑"某用户按时间查订单"
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

5.3 改表（ALTER，生产高风险）

-- 加列（8.0 多数场景可 INSTANT，秒级；5.7 可能锁表重建）
ALTER TABLE user ADD COLUMN email VARCHAR(128) NOT NULL DEFAULT '' COMMENT '邮箱';

-- 改列类型（可能锁表 + 重建，大表务必低峰 + 用 pt-online-schema-change/gh-ost）
ALTER TABLE user MODIFY COLUMN age SMALLINT UNSIGNED NOT NULL DEFAULT 0;

-- 加索引（8.0 支持 ALGORITHM=INPLACE 并发写；加唯一索引仍要校验）
ALTER TABLE `order` ADD INDEX idx_status (status);

-- 删除/重命名
ALTER TABLE user DROP COLUMN email;
ALTER TABLE user RENAME COLUMN name TO full_name;

生产铁律：大表 DDL 绝不可直接在业务高峰 ALTER！用 pt-online-schema-change（Percona） 或 gh-ost（GitHub） 做在线无锁变更；或 8.0 的 ALGORITHM=INSTANT（仅支持加列/删列等有限操作）。变更前务必在影子库验证并评估锁时长。

5.4 TRUNCATE vs DELETE

操作	是否回滚	是否触发触发器	速度	自增计数器
`DELETE FROM t`	✅（事务内）	✅	逐行慢	不变
`TRUNCATE TABLE t`	❌（DDL，隐式提交）	❌	秒级（重建表）	重置为初始值

1 2	DELETE FROM log WHERE created_at < '2025-01-01'; -- 大批量删除要分批+LIMIT，避免长事务/锁表 TRUNCATE TABLE temp_table; -- 清空整表且不在乎回滚

六、DML（数据操纵语言）

💡 配合练习：下面每条 INSERT/UPDATE/DELETE 都建议结合「实战入门」章的 student 表亲手敲一遍——先 SELECT 看改之前的数据，执行后再 SELECT 看改之后的结果，对比着学印象最深。

6.1 INSERT

-- 单行
INSERT INTO user (username, age) VALUES ('tom', 20);

-- 批量（一次插入多行，比循环单条快几个数量级，减少网络/事务开销）
INSERT INTO user (username, age) VALUES
  ('a', 21), ('b', 22), ('c', 23);

-- 从查询插入（数据迁移/初始化）
INSERT INTO user_stat (user_id, order_cnt)
SELECT user_id, COUNT(*) FROM `order` GROUP BY user_id;

-- 主键/唯一冲突时更新（幂等写入经典写法，"存在则更新，不存在则插入"）
INSERT INTO user (id, username, age) VALUES (1, 'tom', 21)
  ON DUPLICATE KEY UPDATE age = VALUES(age), updated_at = NOW();

-- 冲突则忽略（不报错，跳过）
INSERT IGNORE INTO user (username, age) VALUES ('tom', 20);

** VALUES() 引用**：在 ON DUPLICATE KEY UPDATE 中用 VALUES(col) 引用本次待插入的值，避免重复写字面量。

💡 真实案例：ON DUPLICATE KEY UPDATE 的 3 大使用场景

-- 场景1：用户每日签到（今天签过就不再插入，只更新签到次数）
-- sign_record 表有 UNIQUE KEY uk_user_date (user_id, sign_date)
INSERT INTO sign_record (user_id, sign_date, count)
  VALUES (123, '2026-07-09', 1)
  ON DUPLICATE KEY UPDATE count = count + 1;
-- 第一次签到：插入新行；重复签到：count+1（幂等，不报错）

-- 场景2：第三方数据同步（有则更新、无则新增）
-- 同步微信用户信息到本地表，openid 是唯一键
INSERT INTO wx_user (openid, nickname, avatar, updated_at)
  VALUES ('oXyz123', '张三', 'https://...', NOW())
  ON DUPLICATE KEY UPDATE nickname=VALUES(nickname), avatar=VALUES(avatar), updated_at=NOW();

-- 场景3：实时统计计数表（每次请求累加）
INSERT INTO api_stat (api_path, call_date, count)
  VALUES ('/user/info', CURDATE(), 1)
  ON DUPLICATE KEY UPDATE count = count + 1;

一句话：凡是”存在就更新、不存在就新增”的需求，就用 ON DUPLICATE KEY UPDATE（需要有唯一键/主键冲突触发）。

6.2 UPDATE

UPDATE user SET age = 21, updated_at = NOW() WHERE id = 1;

-- 带 LIMIT + ORDER BY（控制影响行数，避免误全表更新）
UPDATE `order` SET status = 2 WHERE status = 1 ORDER BY id LIMIT 1000;

-- 关联更新
UPDATE `order` o
  JOIN user u ON o.user_id = u.id
  SET o.user_name = u.username
  WHERE o.user_name IS NULL;

UPDATE 铁律：必须带 WHERE 且 WHERE 命中索引；写前先 SELECT 确认影响行数；大批量更新分批（LIMIT）+ 低峰执行，避免长事务锁表拖垮主库。

6.3 DELETE

1 2	DELETE FROM user WHERE id = 1; DELETE FROM `order` WHERE status = 2 AND created_at < '2025-01-01' LIMIT 1000;

删全表用 TRUNCATE（更快且重置自增）；带外键约束时删除顺序要注意（先删子表再删父表，或设 ON DELETE CASCADE）。生产禁止无 WHERE 的 DELETE。

6.4 REPLACE

REPLACE INTO = 若存在主键/唯一冲突则先删后插（DELETE+INSERT，自增主键会变化、触发器会被触发两次）。多数场景不如 INSERT ... ON DUPLICATE KEY UPDATE 可控，谨慎使用。

七、DQL 查询（核心能力）

💡 配合练习：本章所有查询示例都可套用到「实战入门」章的 student 表（5 行真实数据）。建议把 SQL 复制到本地 MySQL 跑一下，对照返回结果理解每个子句的作用。

7.1 SELECT 完整语法与执行顺序

SELECT [DISTINCT] 列/聚合
FROM 表
[JOIN 表 ON 条件]
[WHERE 行过滤]
[GROUP BY 分组列]
[HAVING 分组后过滤]
[ORDER BY 排序列]
[LIMIT 偏移, 条数];

SQL 逻辑执行顺序（理解优化关键）：

1	FROM → JOIN/ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

这意味着：WHERE 在分组前过滤（不能用聚合函数），HAVING 在分组后过滤（可用 COUNT/SUM 等）。SELECT 里起的别名不能在 WHERE 用，但可在 ORDER BY/HAVING 用（MySQL 扩展允许）。

7.2 WHERE 条件与 NULL 陷阱

1
2
3

SELECT * FROM user WHERE age >= 18 AND city = 'SZ';
SELECT * FROM user WHERE status IN (1, 2);
SELECT * FROM user WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

NULL 三大铁律：

NULL 不等于任何值，包括它自己：NULL = NULL 结果为 NULL（非 true）。判断空用 **IS NULL / IS NOT NULL**。

WHERE col != 'x' 不会返回 col 为 NULL 的行（NULL 参与比较结果未知，被当作 false 过滤掉）——这是最隐蔽的漏数据 bug。

NOT IN (子查询) 若子查询含 NULL，整个结果可能为 NULL（返回空集）。用 NOT EXISTS 替代更安全。

7.3 LIKE 模糊查询

1
2
3

SELECT * FROM user WHERE username LIKE 'tom%';   -- 前缀匹配，可用索引 ✅
SELECT * FROM user WHERE username LIKE '%tom';    -- 后缀，索引失效 ❌
SELECT * FROM user WHERE username LIKE '%tom%';   -- 包含，索引失效 ❌

前缀 % 在最左才走索引；前后都有 % 必全表扫描。需要”包含/后缀”检索请用全文索引（FULLTEXT）或 ES。

7.4 排序 ORDER BY

1
2
3

SELECT * FROM `order` ORDER BY created_at DESC;                 -- 单字段
SELECT * FROM `order` ORDER BY status ASC, created_at DESC;     -- 多字段
SELECT * FROM `order` ORDER BY FIELD(status, 3,1,2);            -- 自定义顺序

排序字段有索引且顺序一致 → 索引有序，免排序（Extra 无 Using filesort）。

无索引或 ORDER BY 与索引顺序不符 → Using filesort（内存/磁盘排序，大数据量慢）。LIMIT 常配合 ORDER BY 主键做深度分页优化（见 §12.1）。

7.5 分组聚合 GROUP BY + HAVING

-- 各城市用户数
SELECT city, COUNT(*) AS cnt FROM user GROUP BY city;

-- 聚合函数
SELECT
  COUNT(*)        AS total,        -- 总行数（含 NULL 行）
  COUNT(email)    AS has_email,    -- email 非 NULL 的行数
  AVG(age), SUM(age), MIN(age), MAX(age)
FROM user;

-- HAVING 过滤分组结果
SELECT city, COUNT(*) cnt FROM user
  GROUP BY city HAVING COUNT(*) > 100;

COUNT 三兄弟（高频考点）：

写法	含义	是否计 NULL 行	InnoDB 实现
`COUNT(*)`	行数	计（NULL 整行也算）	全表/索引扫描计数
`COUNT(1)`	行数	计	等价于 `COUNT(*)`
`COUNT(col)`	col 非 NULL 的行数	不计 col 为 NULL 的行	扫描 col

结论：COUNT(*) 与 COUNT(1) 在 InnoDB 性能基本无差，语义最清晰，**统计行数用 COUNT(*)**。COUNT(col) 是”非空计数”，语义不同不要混用。MyISAM 的 COUNT(*) 有缓存总数（无 WHERE 时 O(1)），InnoDB 无，需扫表或靠二级索引。

7.6 多表 JOIN

-- INNER JOIN：两表都匹配的行
SELECT o.order_no, u.username
  FROM `order` o
  JOIN user u ON o.user_id = u.id;

-- LEFT JOIN：左表全保留，右表无匹配补 NULL
SELECT u.username, o.order_no
  FROM user u
  LEFT JOIN `order` o ON o.user_id = u.id;

-- 多表
SELECT ... FROM a
  JOIN b ON a.id = b.a_id
  JOIN c ON b.id = c.b_id;

JOIN 核心认知（资深必懂）：

驱动表：MySQL 用嵌套循环（Nested-Loop）：先取驱动表（外表）的行，再用关联列去被驱动表查。
优化器选驱动表依据预估行数，通常小表驱动大表更优；确保被驱动表的关联列有索引（否则退化成”全表 × 全表”灾难）。
ON vs WHERE：
- INNER JOIN 中 ON 与 WHERE 过滤效果等价（优化器会下推）。
- LEFT JOIN 中把右表过滤条件放 ON**（保留左表所有行）还是 WHERE（过滤后只剩匹配行，等价于 INNER）语义完全不同**，务必分清。

💡 真实案例：JOIN 在什么业务场景下用？（4 种典型）

-- 场景1：订单详情页（JOIN 拿用户名 + 商品名）
SELECT o.order_no, o.amount, u.username, p.product_name
FROM `order` o
  JOIN user u ON o.user_id = u.id
  JOIN order_item oi ON oi.order_id = o.id
  JOIN product p ON oi.product_id = p.id
WHERE o.id = 100;

-- 场景2：统计每个用户的订单总金额（LEFT JOIN 保留没下过单的用户）
SELECT u.username, COALESCE(SUM(o.amount), 0) AS total
FROM user u
  LEFT JOIN `order` o ON o.user_id = u.id
GROUP BY u.id, u.username;
-- LEFT JOIN 确保没下过单的用户也显示出来（total=0），INNER JOIN 会丢掉他们

-- 场景3：查"从未下过单的用户"（LEFT JOIN + IS NULL）
SELECT u.id, u.username FROM user u
  LEFT JOIN `order` o ON o.user_id = u.id
  WHERE o.id IS NULL;
-- 使用场景：运营想找"注册了但从未消费的用户"做促活推送

-- 场景4：关联更新（把用户最新手机号同步到订单表的冗余字段）
UPDATE `order` o
  JOIN user u ON o.user_id = u.id
  SET o.user_phone = u.phone
  WHERE o.user_phone = '' OR o.user_phone IS NULL;

-- 经典：查"从未下过单的用户"（LEFT JOIN + IS NULL 反模式，改用 NOT EXISTS 更优）
SELECT u.* FROM user u
  LEFT JOIN `order` o ON o.user_id = u.id
  WHERE o.id IS NULL;

7.7 子查询

-- IN 子查询（8.0 优化为 Semi-Join，通常没问题）
SELECT * FROM user WHERE id IN (SELECT user_id FROM `order`);

-- EXISTS（相关子查询，常用于"存在即返回"，可走索引）
SELECT * FROM user u
  WHERE EXISTS (SELECT 1 FROM `order` o WHERE o.user_id = u.id);

-- 派生表（FROM 子查询，8.0 可合并/物化）
SELECT t.city, t.cnt FROM (
  SELECT city, COUNT(*) cnt FROM user GROUP BY city
) t WHERE t.cnt > 100;

IN vs EXISTS 经验：

子查询结果小、外表大 → IN（MySQL 8.0 自动转 semi-join，效率高）。

子查询大、外表小且关联列有索引 → EXISTS。

NOT IN 遇 NULL 返回空集的坑（见 §7.2），”不存在”语义一律用 NOT EXISTS 或 LEFT JOIN ... IS NULL。

7.8 UNION / UNION ALL

SELECT id, name FROM a
UNION ALL          -- 不去重，保留全部（更快，推荐默认用 ALL）
SELECT id, name FROM b;
-- UNION 会去重（隐含 DISTINCT + 排序，开销大），仅在确需去重时用

7.9 窗口函数（8.0，分析利器）

窗口函数不折叠行，在”行间”做计算（排名、累计、同比），是报表 SQL 的核心。

-- 基本语法
函数() OVER (
  [PARTITION BY 分组列]      -- 类似 GROUP BY，但不聚合行
  [ORDER BY 排序列]
  [ROWS|RANGE 窗口帧]
)

排名三兄弟：

SELECT
  user_id, score,
  ROW_NUMBER() OVER (ORDER BY score DESC)        AS rn,   -- 1,2,3,4 连续不重
  RANK()       OVER (ORDER BY score DESC)        AS rk,   -- 1,2,2,4 并列跳号
  DENSE_RANK() OVER (ORDER BY score DESC)        AS drk   -- 1,2,2,3 并列不跳
FROM exam;

聚合窗口（累计/占比/移动平均）：

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date)                       AS running_total,  -- 累计
  SUM(amount) OVER ()                                          AS grand_total,    -- 全体总和
  SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_3, -- 近3日移动和
  100.0 * amount / SUM(amount) OVER ()                         AS pct             -- 占比%
FROM daily_sales;

偏移（同比/环比/前后比较）：

SELECT
  order_month,
  amount,
  LAG(amount, 1)  OVER (ORDER BY order_month) AS prev_month,   -- 上一期
  LEAD(amount, 1) OVER (ORDER BY order_month) AS next_month,  -- 下一期
  amount - LAG(amount,1) OVER (ORDER BY order_month) AS mom_diff -- 环比差值
FROM monthly_sales;

每组的 TopN（经典：每用户最新一笔订单）：

SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM `order`
) t WHERE rn = 1;   -- rn<=3 即每用户前3笔

7.10 CTE（WITH，8.0，可读性 + 递归）

WITH city_stat AS (
  SELECT city, COUNT(*) cnt FROM user GROUP BY city
)
SELECT * FROM city_stat WHERE cnt > 100;

-- 递归 CTE（遍历树形结构，见 §12.2）
WITH RECURSIVE cte AS (
  SELECT id, parent_id, name FROM category WHERE parent_id IS NULL  -- 锚点
  UNION ALL
  SELECT c.id, c.parent_id, c.name FROM category c JOIN cte ON c.parent_id = cte.id  -- 递归
)
SELECT * FROM cte;

八、索引（性能命脉，重中之重）

一句话：大多数 MySQL 性能问题 = 索引没建对 / 没用上。资深工程师的核心竞争力之一，就是”看一眼 SQL 和表结构，能预判它会不会慢、慢在哪”。

8.1 为什么索引快（B+Tree）

InnoDB 默认索引是 B+Tree（多路平衡树）：

数据按主键顺序存在叶子节点（聚簇索引，Clustered Index），叶子间用双向链表串联 → 范围查询/排序极快。
树高通常 3~4 层即可存千万~~亿级数据，一次查询只需 3~~4 次磁盘 IO。
二级索引（Secondary）叶子存 (索引列, 主键值)，查到主键后再回表取整行（这就是”回表”开销）。

1 2	聚簇索引（主键）: [叶子节点 = 完整数据行，按主键排序] 二级索引: [叶子节点 = (索引列值, 主键值)] → 回表查聚簇索引

💡 真实案例：没有索引 vs 有索引——百万数据查询对比
1
2
3
4
5
6
7
8
9
10
-- 假设订单表有 100 万行，user_id 上没有索引
EXPLAIN SELECT * FROM `order` WHERE user_id = 12345;
-- 结果：type=ALL, rows=1000000（全表扫描100万行！耗时约2秒）

-- 给 user_id 加索引
ALTER TABLE `order` ADD INDEX idx_user_id (user_id);

-- 再看执行计划
EXPLAIN SELECT * FROM `order` WHERE user_id = 12345;
-- 结果：type=ref, key=idx_user_id, rows=15（只扫描15行！耗时<1ms）
使用场景：只要你的 WHERE/JOIN ON/ORDER BY 用到了某个列，且表数据超过几千行，就应该考虑给它建索引。不建索引 = 每次查询都要扫全表（想象从一本 1000 页的书里找一个词，没有目录只能一页页翻）。

8.2 索引类型

类型	说明	关键字
普通索引	无唯一约束	`INDEX` / `KEY`
唯一索引	值唯一（允许 NULL）	`UNIQUE KEY`
主键索引	特殊的唯一非空聚簇索引	`PRIMARY KEY`
组合索引	多列联合	`INDEX idx(a,b,c)`
前缀索引	对长字符串前 N 字符建索引	`INDEX idx(col(20))`
全文索引	分词检索	`FULLTEXT`
空间索引	地理	`SPATIAL`
覆盖索引	查询列全在索引中，无需回表	查询利用组合索引

8.3 创建/查看/删除

CREATE INDEX idx_user_created ON `order` (user_id, created_at);
ALTER TABLE `order` ADD INDEX idx_status (status);
DROP INDEX idx_status ON `order`;
-- 8.0 隐藏索引：先隐藏观察，确认无影响再删（安全删索引）
ALTER TABLE `order` ALTER INDEX idx_status INVISIBLE;  -- 优化器不再用
ALTER TABLE `order` ALTER INDEX idx_status VISIBLE;

-- 查看索引（Cardinality 越接近总行数，选择性越好，越该建索引）
SHOW INDEX FROM `order`;

8.4 最左前缀原则（组合索引的灵魂）

组合索引 (a, b, c) 相当于建立了 (a)、(a,b)、(a,b,c) 三个有效前缀。查询必须从最左列开始且连续才能用上索引：

查询条件	是否用索引	说明
`WHERE a=1`	✅ `(a)`	用最左列
`WHERE a=1 AND b=2`	✅ `(a,b)`	连续前缀
`WHERE a=1 AND b=2 AND c=3`	✅ `(a,b,c)`	全用
`WHERE b=2`	❌	跳过最左列 a
`WHERE a=1 AND c=3`	✅ 仅 `(a)`	a 用索引，c 在索引内过滤但非前缀
`WHERE a>1 AND b=2`	✅ 仅 `a`	范围查询(a)后的列(b)失效

组合索引列顺序设计原则：区分度高的列放前面（如 user_id 放前，status 放后），且把最常用于等值过滤的列放最左。

💡 真实案例：组合索引的最左前缀——“查某用户某时间段的订单”

-- 业务需求：后台管理页面查"某用户最近30天的订单列表，按时间倒序"
-- SQL：
SELECT * FROM `order`
  WHERE user_id = 12345 AND created_at >= '2026-06-09'
  ORDER BY created_at DESC;

-- 正确索引：(user_id, created_at)
ALTER TABLE `order` ADD INDEX idx_user_created (user_id, created_at);
-- EXPLAIN: type=range, key=idx_user_created, Extra无filesort ✅
-- 解释：先按user_id等值定位（最左），再在这批数据内按created_at范围扫描+排序（索引本身有序）

-- ❌ 错误索引顺序：(created_at, user_id)
-- 如果查询是 WHERE user_id=12345（跳过了最左列created_at），这个索引完全用不上！

-- ❌ 单独给 created_at 建索引？
-- 能用，但要扫描30天内所有用户的订单再过滤user_id，效率远不如组合索引

使用场景：只要你的查询是”A=某值 AND B 范围/排序”的模式，就建 (A, B) 组合索引。常见于：查某用户的订单/消息/日志（按时间排序）、查某商户的交易（按金额/时间排序）。

8.5 索引失效（必背场景）

以下情况会导致优化器放弃索引、全表扫描：

对索引列用函数/运算：WHERE DATE(created_at)='2026-07-09'（应改为范围 >= AND <）；WHERE amount*1.1 > 100。
隐式类型转换：列是 VARCHAR，却 WHERE phone = 13800000000（传数字）→ MySQL 对列做 CAST，索引失效。务必类型匹配。
前导 % 模糊：LIKE '%abc'。
OR 连接非索引列：WHERE a=1 OR b=2（b 无索引则全表）。可改为 UNION 或给 b 加索引。
不等于 / NOT IN / IS NOT NULL（通常）：选择性差时优化器认为全表更划算。
优化器认为全表更快：当命中数据超过约 20%~30% 时，优化器往往选全表扫描（回表成本高）。此时覆盖索引或 FORCE INDEX 可引导。

💡 真实案例：索引失效的”坑”——函数包裹索引列
1
2
3
4
5
6
7
8
9
10
-- 需求：查今天注册的用户
-- ❌ 错误写法（索引失效！DATE() 函数包裹了 created_at 列）
SELECT * FROM user WHERE DATE(created_at) = '2026-07-09';
-- EXPLAIN: type=ALL（全表扫描），即使 created_at 有索引也用不上

-- ✅ 正确写法（范围查询，索引生效）
SELECT * FROM user
  WHERE created_at >= '2026-07-09 00:00:00'
    AND created_at <  '2026-07-10 00:00:00';
-- EXPLAIN: type=range, key=idx_created ✅
记忆口诀：索引列上不能”加工”（函数、运算、类型转换），否则索引失效。查日期段永远用 >= AND <，不用 DATE()/YEAR() 包裹。

8.6 覆盖索引（性能优化杀手锏）

“覆盖索引”= 查询所需的所有列都包含在某个索引中，无需回表。

1
2
3

-- idx_user_created = (user_id, created_at)
-- 只需要这两列 → 索引已覆盖，Extra 显示 Using index，极快
SELECT user_id, created_at FROM `order` WHERE user_id = 123;

实战：高频只读查询可建宽组合索引覆盖常用列（以空间换免回表的性能）；SELECT * 几乎不可能被覆盖，尽量只查需要的列。

8.7 解读执行计划 EXPLAIN（排错核心）

1	EXPLAIN SELECT * FROM `order` WHERE user_id = 123 ORDER BY created_at DESC;

字段	含义 / 关注点
`id`	执行顺序（大→先，相同则自上而下）
`select_type`	SIMPLE / PRIMARY / SUBQUERY / DERIVED 等
`type`	访问类型，越靠前越好：`system` > `const` > `eq_ref` > `ref` > `range` > `index` > `ALL`。`ALL`=全表扫描（需警惕）
`possible_keys`	可能用的索引
`key`	实际用的索引（NULL=没用上，要查为什么）
`key_len`	索引用到的字节数（判断组合索引用了几列）
`rows`	预估扫描行数（越小越好）
`Extra`	关键提示：`Using index`(覆盖) / `Using where`(回表后过滤) / `Using index condition`(ICP) / `Using filesort`(需排序，慢) / `Using temporary`(用临时表，慢)

排错套路：type=ALL 或 rows 巨大或 Extra 出现 Using filesort/Using temporary → 基本就是慢查询，回去检查 WHERE/JOIN/ORDER BY 是否命中索引。

8.8 索引设计 checklist（资深经验）

✅ WHERE / JOIN ON / ORDER BY / GROUP BY 用到的列优先建索引。
✅ 高基数（Cardinality）列放组合索引前面。
✅ 用覆盖索引消除回表；少 SELECT *。
✅ 长字符串用前缀索引 col(20)，并权衡前缀长度与选择性。
❌ 不过度索引：每个索引拖慢写入、占空间、增加优化器选择成本。写多读少的表索引从简。
❌ 不在低基数列（如 gender 只有 2 值）单独建索引（选择性差，优化器多半不用）。
❌ 不建”永远用不上”的索引（用 sys.schema_unused_indexes / 8.0 隐藏索引验证后清理）。

九、事务与锁（并发正确性）

9.1 ACID

A 原子性：事务内操作要么全成功要么全失败（undo log 支持回滚）。
C 一致性：数据从一个一致状态到另一个（业务约束由应用+DB 共同保证）。
I 隔离性：并发事务互不干扰（靠锁 + MVCC）。
D 持久性：提交后数据永久保存（redo log + 刷盘）。

9.2 事务控制

BEGIN;  -- 或 START TRANSACTION
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- 提交，持久化
-- ROLLBACK;  -- 出错时回滚

-- 保存点（部分回滚）
SAVEPOINT sp1;
DELETE FROM tmp;
ROLLBACK TO sp1;  -- 只回滚到 sp1，之前的操作保留

事务内注意：DDL（CREATE/ALTER/DROP/TRUNCATE）会隐式提交当前事务；事务中混 DDL 是常见坑。

💡 真实案例1：转账——事务保证”钱不会凭空消失”
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 场景：用户A给用户B转100元。必须"扣A + 加B"同时成功，否则全部回滚
BEGIN;
-- 1) 先查A余额（加排他锁防并发，确保读到的余额是最新的）
SELECT balance FROM account WHERE id = 1 FOR UPDATE;
-- 假设返回 balance=500，够转

-- 2) 扣A的钱
UPDATE account SET balance = balance - 100 WHERE id = 1;

-- 3) 加B的钱
UPDATE account SET balance = balance + 100 WHERE id = 2;

COMMIT; -- 两步都成功，一起持久化
-- 如果中间任何一步报错，ROLLBACK → A的钱不会少、B的钱不会多
为什么需要事务：如果不开事务，扣了A的钱后系统崩溃，B的钱没加上 → A少了100、B没收到 = 钱”消失”了。事务保证要么全成功、要么全失败（原子性）。

💡 真实案例2：秒杀扣库存——防超卖

-- 场景：商品库存100件，1000人同时抢。不加锁 → 可能卖出200件（超卖！）
BEGIN;
-- 用 FOR UPDATE 加排他锁：同一时刻只有一个事务能锁住这行
SELECT stock FROM product WHERE id = 1001 FOR UPDATE;
-- 假设 stock=1（还剩1件）

-- 判断库存够不够（在代码里判断）
-- 如果 stock > 0：
UPDATE product SET stock = stock - 1 WHERE id = 1001;
INSERT INTO order_item (product_id, user_id) VALUES (1001, 999);
COMMIT;
-- 如果 stock <= 0：ROLLBACK（已卖完，不扣）

更高效的写法（乐观锁/CAS）：

1
2
3

-- 不需要 FOR UPDATE，靠 WHERE stock > 0 保证不会扣到负数
UPDATE product SET stock = stock - 1 WHERE id = 1001 AND stock > 0;
-- affected_rows = 1 → 扣成功；= 0 → 库存不足，不创建订单

使用场景：任何”先查后改”的并发场景（转账、扣库存、抢红包、抢优惠券），都需要事务+锁来防止数据不一致。

9.3 四种隔离级别与并发异常

隔离级别	脏读	不可重复读	幻读	说明
READ UNCOMMITTED（读未提交）	❌可能	❌可能	❌可能	能看到别人未提交的数据，几乎不用
READ COMMITTED（读已提交）	✅避免	❌可能	❌可能	Oracle 默认，每次读最新已提交
REPEATABLE READ（可重复读）	✅	✅	✅(靠间隙锁)	MySQL InnoDB 默认
SERIALIZABLE（串行化）	✅	✅	✅	加读锁，性能差，并发低

脏读：读到别的事务未提交的数据（可能回滚）。
不可重复读：同一事务内两次读同一行，结果不同（被别的事务UPDATE了）。
幻读：同一事务内两次范围查询，行数不同（被别的事务 INSERT/DELETE 了）。

InnoDB 如何解决可重复读 + 幻读：靠 MVCC（多版本并发控制） 让普通读（快照读）看到事务开始时的快照（解决不可重复读）；靠 Next-Key Lock（记录锁 + 间隙锁） 在范围查询时锁住间隙，防止别的事务插入（解决幻读）。所以 InnoDB 在 RR 下不会发生幻读。

9.4 锁机制（InnoDB）

锁	说明
共享锁 S（`LOCK IN SHARE MODE` / `FOR SHARE`）	读锁，多个事务可同时持有，互斥写
排他锁 X（`FOR UPDATE` / UPDATE/DELETE/INSERT）	写锁，独占
意向锁 IS/IX	表级，表明”事务将要在某些行加 S/X 锁”，避免行锁与表锁冲突
记录锁 Record Lock	锁单行
间隙锁 Gap Lock	锁索引记录间的”间隙”，防插入
Next-Key Lock	记录锁 + 间隙锁（RR 默认，解决幻读）

1 2	-- 当前读（加锁读），常用于"先查后改"防并发覆盖 SELECT * FROM `order` WHERE id = 1 FOR UPDATE; -- 排他锁

死锁：两个事务互相等待对方持有的锁。InnoDB 会检测并回滚其中一个（代价小者）。避免方法：事务尽量短、按固定顺序访问多张表/多行、降低隔离级别/锁粒度、及时提交。

💡 真实案例：死锁是怎么发生的？

-- 事务A：先锁用户1、再锁用户2
BEGIN;
UPDATE account SET balance = balance - 50 WHERE id = 1;  -- 锁住 id=1
-- 此时还没执行到 id=2...

-- 事务B（同时）：先锁用户2、再锁用户1
BEGIN;
UPDATE account SET balance = balance - 30 WHERE id = 2;  -- 锁住 id=2
UPDATE account SET balance = balance + 30 WHERE id = 1;  -- 等待 id=1（被A持有）💀

-- 事务A继续：
UPDATE account SET balance = balance + 50 WHERE id = 2;  -- 等待 id=2（被B持有）💀
-- 此时A等B、B等A → 死锁！InnoDB检测到后回滚其中一个事务并报错：
-- ERROR 1213 (40001): Deadlock found when trying to get lock

解决办法：让所有事务按 id 从小到大的顺序更新（如都先锁 id=1 再锁 id=2），就不会互相等待。代码里捕获死锁异常后重试即可（InnoDB 只回滚一个）。

长事务危害（生产大忌）：持有锁和 undo 版本时间长 → 阻塞他人、undo 膨胀、主从延迟、甚至 OOM。务必缩小事务边界，别在事务里调外部接口/睡大觉。

十、常用函数

10.1 字符串

CONCAT('a','-','b')              -- 'a-b'
CONCAT_WS('-','a','b','c')       -- 'a-b-c'（带分隔符）
SUBSTRING(str, pos, len)         -- 截取（pos 从1起，可负）
LEFT(str,n) / RIGHT(str,n)       -- 左/右 n 字符
LENGTH(str)                      -- 字节数（utf8mb4 中文=3~4）
CHAR_LENGTH(str)                 -- 字符数
TRIM / LTRIM / RTRIM(str)        -- 去首尾空白
REPLACE(str, from, to)           -- 替换
UPPER / LOWER(str)
LOCATE(sub, str)                 -- 子串位置（0=无）
LPAD / RPAD(str, len, pad)       -- 补位（如订单号补零）

10.2 数值

ROUND(x, d)      -- 四舍五入保留 d 位
TRUNCATE(x, d)   -- 截断（不四舍五入）
CEIL(x)/FLOOR(x) -- 向上/下取整
ABS / MOD(a,b)   -- 绝对值 / 取模
RAND()           -- 随机 0~1（ORDER BY RAND() 大表极慢，禁用）

10.3 日期

NOW()                       -- 当前 datetime（语句开始时刻）
SYSDATE()                   -- 实时（每调用一次取当前）
CURDATE() / CURTIME()
DATE_ADD(created_at, INTERVAL 7 DAY)   -- 加 7 天
DATEDIFF(d1, d2)            -- 天数差
TIMESTAMPDIFF(SECOND, a, b) -- 精确差（可选 SECOND/MINUTE/HOUR/DAY/MONTH/YEAR）
DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s')  -- 格式化
YEAR/MONTH/DAY(created_at)  -- 取年/月/日
LAST_DAY(created_at)        -- 当月最后一天

避免 DATE(col) 包裹索引列（见 §8.5）。统计”今日”应写：
WHERE created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY

10.4 聚合

COUNT / SUM / AVG / MAX / MIN / GROUP_CONCAT（将分组内多行拼成一个串）。

10.5 流程控制

-- IF(条件, 真值, 假值)
SELECT IF(status=1, '启用', '禁用') FROM user;

-- CASE WHEN（最常用，行列转换核心）
SELECT
  user_id,
  MAX(CASE WHEN subject='语文' THEN score END) AS 语文,
  MAX(CASE WHEN subject='数学' THEN score END) AS 数学
FROM score GROUP BY user_id;

-- COALESCE(取第一个非 NULL)
SELECT COALESCE(email, phone, '未知') FROM user;
-- IFNULL(expr, default)

10.6 JSON（8.0）

-- 提取
SELECT info->>'$.city' FROM user;          -- 提取并去引号
SELECT JSON_EXTRACT(info, '$.age') FROM user;

-- 查询条件
SELECT * FROM user WHERE info->>'$.city' = 'SZ';

-- 生成/修改
SELECT JSON_OBJECT('a',1,'b',2);
SELECT JSON_SET(info, '$.age', 30) FROM user;

十一、视图 / 存储过程 / 触发器 / 事件

资深建议：现代应用层（Java/Go）足够强大，存储过程/触发器应克制使用——它们难以调试、难以版本管理、不利于水平扩展、把逻辑藏进数据库。视图（简化复杂查询）可适度用；触发器/存储过程仅用于 DBA 维护类场景。

-- 视图（虚拟表，不存数据，查询时实时计算）
CREATE VIEW v_active_user AS
  SELECT id, username FROM user WHERE status = 1;

-- 存储过程（带流程，较少用）
DELIMITER $$
CREATE PROCEDURE batch_update(IN batch INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  -- ... 业务逻辑
END$$
DELIMITER ;

-- 触发器（慎用，隐藏副作用）
CREATE TRIGGER trg_after_insert AFTER INSERT ON `order`
  FOR EACH ROW UPDATE user_stat SET order_cnt = order_cnt + 1 WHERE user_id = NEW.user_id;

-- 事件（定时任务，需开启 event_scheduler）
CREATE EVENT ev_clean_log
  ON SCHEDULE EVERY 1 DAY
  DO DELETE FROM log WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

十二、常见业务场景 SQL 实战（精华）

以下示例用统一测试表：

-- 用户
CREATE TABLE user (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(64) NOT NULL,
  city VARCHAR(32), age TINYINT UNSIGNED,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 订单
CREATE TABLE `order` (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status TINYINT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user (user_id),
  KEY idx_created (created_at)
);

12.1 分页查询（含深度分页优化）

使用场景：几乎所有列表页面都需要分页——商品列表、订单列表、用户管理后台。前端传 page=1&pageSize=20，后端用 LIMIT offset, size 返回对应页数据。问题在于翻到越后面越慢（如第 5000 页），因为 MySQL 要先扫描前 10 万行再丢掉。

1
2
3

-- 常规分页（offset 大时越来越慢：要扫描 offset+limit 行再丢弃前 offset 行）
SELECT * FROM `order` ORDER BY id DESC LIMIT 0, 20;     -- 第1页
SELECT * FROM `order` ORDER BY id DESC LIMIT 100000, 20; -- 第5000页，极慢

深度分页优化（游标分页 / 延迟关联）：

-- 方案A：游标（上一页最后一条的 id），前提 id 有序且连续查询
SELECT * FROM `order` WHERE id < :last_id ORDER BY id DESC LIMIT 20;

-- 方案B：延迟关联（先查索引拿 id，再回表，避免大 offset 回表开销）
SELECT * FROM `order` a
  JOIN (SELECT id FROM `order` ORDER BY id DESC LIMIT 100000, 20) b ON a.id = b.id;

前端”上拉加载更多”用游标分页（无限滚动）；后台管理”跳页”才用 offset，但要限制最大页数。

12.2 树形结构（分类/组织架构）

使用场景：电商的商品分类（一级分类→二级→三级）、公司组织架构（集团→事业部→部门→组）、评论的回复树、文件夹目录——任何”有父子层级关系”的数据都是树形结构。核心问题：怎么在关系型表里存树、怎么高效查某节点的所有子孙？

方案：邻接表（最常见）——每行带 parent_id。

CREATE TABLE category (
  id BIGINT PRIMARY KEY,
  parent_id BIGINT,          -- 顶级为 NULL
  name VARCHAR(64),
  INDEX idx_parent (parent_id)
);

-- 查询某节点的所有子孙（递归 CTE，8.0）
WITH RECURSIVE subtree AS (
  SELECT id, parent_id, name FROM category WHERE id = 1   -- 起点
  UNION ALL
  SELECT c.id, c.parent_id, c.name
    FROM category c JOIN subtree s ON c.parent_id = s.id  -- 递归子节点
)
SELECT * FROM subtree;

-- 查询某节点的所有祖先路径（向上递归）
WITH RECURSIVE path AS (
  SELECT id, parent_id, name FROM category WHERE id = 10
  UNION ALL
  SELECT c.id, c.parent_id, c.name
    FROM category c JOIN path p ON c.id = p.parent_id
)
SELECT * FROM path;

其它方案：路径枚举（存 '/1/3/10' 全路径，LIKE 查子孙）、闭包表（另建关系表，查子树最快但写入复杂）。层级浅/变更少选邻接表+递归 CTE 最简洁。

12.3 行转列 / 列转行

使用场景：报表展示。数据库里成绩是”一行一条记录”（张三-语文-90、张三-数学-85），但报表/前端需要一行展示一个学生的所有科目（张三 | 90 | 85 | 78）。这就是”行转列”。反过来，有时宽表需要转成长表做分析/导入，就是”列转行”。

-- 行转列（学生成绩：每个学生的语文/数学分作为列）
SELECT
  user_id,
  MAX(CASE WHEN subject='语文' THEN score END) AS 语文,
  MAX(CASE WHEN subject='数学' THEN score END) AS 数学,
  MAX(CASE WHEN subject='英语' THEN score END) AS 英语
FROM score GROUP BY user_id;

-- 列转行（宽表转长表，用 UNION ALL）
SELECT user_id, '语文' AS subject, 语文 AS score FROM wide
UNION ALL SELECT user_id, '数学', 数学 FROM wide
UNION ALL SELECT user_id, '英语', 英语 FROM wide;

12.4 连续登录天数 / 留存

使用场景：运营分析。产品经理想知道”用户连续登录了多少天”来做签到奖励/活跃度分析；或计算”次日/7日/30日留存率”判断产品粘性。这是游戏、社交、教育类 App 最常见的数据需求。

-- 计算用户最长连续登录天数（经典窗口函数解法）
SELECT user_id, MAX(streak) AS max_streak FROM (
  SELECT
    user_id,
    DATE_SUB(login_date, INTERVAL ROW_NUMBER()
             OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp,  -- 连续日期 grp 相同
    COUNT(*) AS streak
  FROM (SELECT DISTINCT user_id, DATE(login_time) login_date FROM login_log) d
  GROUP BY user_id, grp
) t GROUP BY user_id;

思路：连续日期的”日期 - 行号”是常数，按该常数分组即得每段连续区间的长度。

12.5 排名 / 每组 TopN

使用场景：排行榜（成绩排名、销售额排名、热搜排名）；以及”每个分组里取前 N 个”（如每个用户最新 3 条消息、每个城市 TOP3 商家、每个部门工资最高的人）——这是后台管理系统和 BI 报表最高频的需求之一。

-- 每个城市销售额 TOP 3 的商品
SELECT * FROM (
  SELECT city, product, sales,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY sales DESC) AS rn
  FROM product_sales
) t WHERE rn <= 3;

-- 分数排名（并列处理：RANK 跳号 / DENSE_RANK 不跳号）
SELECT name, score,
  RANK() OVER (ORDER BY score DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS drk
FROM exam;

12.6 累计求和 / 同比环比

-- 每日销售额 + 累计 + 环比（窗口函数）
SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS cum_amount,                 -- 累计
  LAG(amount, 1) OVER (ORDER BY order_date) AS prev_day,                -- 前一日
  ROUND(100.0*(amount - LAG(amount,1) OVER (ORDER BY order_date))
        / LAG(amount,1) OVER (ORDER BY order_date), 2) AS mom_pct       -- 环比%
FROM daily_sales;

-- 同环比（按月，与去年同期比）
SELECT
  DATE_FORMAT(order_month,'%Y-%m') ym,
  amount,
  LAG(amount, 12) OVER (ORDER BY order_month) AS yoy_last_year  -- 去年同期
FROM monthly_sales;

12.7 去重保留最新一条

使用场景：数据清洗。业务 bug 或重复提交导致同一用户有多条重复记录（如重复的登录日志、重复的消息推送记录），需要只保留最新/最大的那一条，删掉其余脏数据。也用于”每个用户只保留最后一次操作”的业务逻辑。

-- 场景：日志表重复，只保留每个 user_id 最新一条
-- 方法1：窗口函数（推荐，8.0）
DELETE t FROM log t
JOIN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM log
  ) s WHERE rn > 1
) d ON t.id = d.id;

-- 方法2：GROUP BY 取最大 id（适合小表/一次性清理）
DELETE FROM log WHERE id NOT IN (
  SELECT max_id FROM (SELECT MAX(id) max_id FROM log GROUP BY user_id) t
);

注意：含子查询的 DELETE 在 MySQL 中不能直接 DELETE FROM t WHERE id NOT IN (子查询同表)，需套一层派生表（如方法2的内层 t）规避 “不能先 SELECT 再 UPDATE 同表” 限制。

12.8 缺失日期补 0（报表常用）

使用场景：做报表/图表时，如果某天没有数据（如周末没订单），直接查会”跳过”那天，导致前端折线图断裂。解决方案：先生成一组连续日期，再 LEFT JOIN 实际数据，无数据的天自动补 0。运营日报、销售趋势图、活跃用户曲线都需要。

-- 生成最近 30 天连续日期，LEFT JOIN 实际数据补 0
WITH RECURSIVE dates AS (
  SELECT CURDATE() - INTERVAL 29 DAY AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM dates WHERE d < CURDATE()
)
SELECT dates.d, COALESCE(SUM(o.amount), 0) AS amount
FROM dates
LEFT JOIN `order` o ON DATE(o.created_at) = dates.d
GROUP BY dates.d ORDER BY dates.d;

12.9 随机取样 / 高效随机

-- ❌ 禁用：ORDER BY RAND() 会物化全表再排序，千万级表灾难
SELECT * FROM user ORDER BY RAND() LIMIT 10;

-- ✅ 高效随机：先随机一个 id 范围再取（近似）
SELECT * FROM user
WHERE id >= (SELECT FLOOR(RAND()*(SELECT MAX(id) FROM user)))
ORDER BY id LIMIT 10;

12.10 分桶 / 取模分组

-- 按 user_id 取模分 10 桶（灰度、分批处理、分库分表路由）
SELECT user_id, user_id % 10 AS bucket FROM user;

-- IN 拆分大查询做分批（避免长事务）
SELECT * FROM `order` WHERE user_id % 10 = 0 AND status = 1;

十三、性能优化清单（排障 SOP）

开启并分析慢查询日志：

1 2	SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; -- 超过1秒记日志

用 pt-query-digest 聚合慢日志，找出高频/高耗时 SQL。

EXPLAIN 每一类慢 SQL（见 §8.7），看 type/key/rows/Extra。
索引先行：按 §8.4~8.8 原则补/调索引，优先覆盖索引。
消灭全表扫描与 filesort/temporary：改写 SQL、加合适索引、SELECT 只取所需列。
SQL 改写：
- 避免 SELECT *；避免 ORDER BY RAND()；
- IN 元素过多改 JOIN 临时表；
- COUNT(*) 大表用估算/EXPLAIN 的 rows，或冗余计数表；
- 深分页用游标/延迟关联（§12.1）。
JOIN 优化：确保被驱动表关联列有索引；小表驱动大表。
批量操作：INSERT 多值、批量 UPDATE 用 CASE WHEN 一次性改，减少交互。
连接池：合理设置最大连接数（如 HikariCP maximumPoolSize ≈ DB 核数*2），避免连接耗尽。
表结构：类型最小化、避免大字段在主表、定期 OPTIMIZE/ANALYZE TABLE 更新统计信息。
架构层：读写分离（主写从读）、分库分表（ShardingSphere）、缓存（Redis 扛读热点）、ES 扛检索。

十四、字符集与排序规则（乱码根因）

1
2
3

-- 查看
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

utf8mb4 是真正 UTF-8（4 字节），支持 emoji 与全部中文；**utf8(utf8mb3) 仅 3 字节**，存 emoji 报错。
三级一致：库、表、列、连接字符集都要一致，否则乱码。连接串加 ?useUnicode=true&characterEncoding=utf8mb4。
排序规则：utf8mb4_general_ci（快、精度略低）、utf8mb4_0900_ai_ci（8.0 默认，ai=口音不敏感、ci=大小写不敏感）、utf8mb4_bin（二进制精确，区分大小写，适合令牌/校验码）。
常见乱码排查：客户端编码 ≠ 服务端编码、列单独指定了不同字符集、emoj‍i 用 utf8 存。

十五、安全与规范

15.1 SQL 注入（必防）

// ❌ 字符串拼接：用户输入 ' OR '1'='1 直接破坏语义
String sql = "SELECT * FROM user WHERE name = '" + name + "'";

// ✅ 预编译（PreparedStatement）：参数化，输入被当作数据而非 SQL
String sql = "SELECT * FROM user WHERE name = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, name);   // 安全转义

铁律：任何用户输入必须参数化（? 占位），禁止拼接。ORM（MyBatis #{} 是预编译，${} 是拼接，禁用 ${} 接用户输入）。最小权限账号、关闭 FILE 等危险权限。

15.2 命名与开发规范（团队共识）

表/字段用小写蛇形 user_order / created_at；表名单数或复数统一。
必备：id 主键、created_at、updated_at；逻辑删除用 deleted/is_deleted 而非物理删。
每个字段加 COMMENT；表加 COMMENT。
索引命名：idx_字段 / uk_字段 / pk_。
SQL 关键字大写，便于阅读与区分。

十六、备份与恢复（DBA 底线）

# 逻辑备份（mysqldump，适合中小库/单表恢复）
mysqldump -u root -p --single-transaction --routines --triggers shop > shop.sql
# 恢复
mysql -u root -p shop < shop.sql

# binlog（增量、点-in-time 恢复）
mysqlbinlog --start-datetime="2026-07-09 10:00:00" binlog.000123 | mysql -u root -p

# 物理备份（大库推荐 Percona XtraBackup，热备、快）

生产必须：定期全量 + binlog 增量 + 异地/云存储 + 定期恢复演练。没演练过的备份 = 没有备份。

附：高频面试题 & 易错点速记

COUNT(*)、COUNT(1)、COUNT(col) 区别？→ 见 §7.5。
CHAR vs VARCHAR？→ §3.2。
DATETIME vs TIMESTAMP？→ §3.3。
为什么 FLOAT 不能存金额？→ §3.1。
最左前缀原则？→ §8.4。
索引失效场景？→ §8.5。
覆盖索引？→ §8.6。
事务隔离级别 & 幻读如何解决？→ §9.3。
WHERE col != x 为何漏数据？→ §7.2（NULL 陷阱）。
深度分页怎么优化？→ §12.1。
为什么 ORDER BY RAND() 慢？→ §12.9。
死锁怎么办？→ §9.4（InnoDB 自动回滚代价小者，应用重试）。

本文档持续沉淀自真实生产实践。遇到具体慢 SQL 或表结构问题，先用 EXPLAIN 定位，再对症下药。