# 数据库与 SQL 核心考点速记与全景通关宝典（纯干货速记典藏版）
**【定位与使用原则】**
- **全面补全，绝不删减知识点**：系统化补齐从基础运维指令、DDL/DML/DQL、对象（视图/存储过程/触发器）、高级分析（窗口函数/CTE/递归）、底层机制（ACID/MVCC/锁/B+树）到 EXPLAIN 调优的全谱系知识。
- **零冗余废话**：全面剔除无意义的背景闲聊与冗长铺垫，直奔核心考点、底层原理与规范操作。
- **基础操作细致入微**：从服务连接、用户权限 DCL、库表底层 DDL、各类字段选型存储字节、到 10 大 ALTER TABLE 语法，全代码细化演示。
- **高密对比表与口诀**：全书配备 25+ 核心横向对比矩阵与防雷避坑口诀，便于考前与面试突击速记。
- **真题驱动即学即练**：每章紧扣经典笔面试真题，标准答案与踩分考点直接附于题下。
---
完整知识体系大纲导航
- [第 1 章：数据库系统基石、环境架构与精细化基础操作](#第-1-章数据库系统基石环境架构与精细化基础操作)
- [1.1 关系型数据库核心架构与基本概念速记](#11-关系型数据库核心架构与基本概念速记)
- [1.2 SQL 语言五大功能分类速查表](#12-sql-语言五大功能分类速查表)
- [1.3 MySQL 客户端连接与服务基础指令](#13-mysql-客户端连接与服务基础指令)
- [1.4 数据库（Database）完整基础操作（增删改查查）](#14-数据库database完整基础操作增删改查查)
- [1.5 字符集（utf8mb4）与校验规则（Collation）深度考点](#15-字符集utf8mb4与校验规则collation深度考点)
- [1.6 用户与权限管理体系（DCL 完整指令集实操）](#16-用户与权限管理体系dcl-完整指令集实操)
- [1.7 【必考精练】库操作与分类辨析经典真题](#17-必考精练库操作与分类辨析经典真题)
- [第 2 章：数据表（Table）精细化操作与数据类型全谱系](#第-2-章数据表table精细化操作与数据类型全谱系)
- [2.1 标准工业级建表模板（含引擎、字符集、注释、键约束）](#21-标准工业级建表模板含引擎字符集注释键约束)
- [2.2 查看表结构与元数据分析指令（DESC, SHOW 与 information_schema）](#22-查看表结构与元数据分析指令desc-show-与-informationschema)
- [2.3 表结构重构：ALTER TABLE 10 大核心语法细化实操](#23-表结构重构alter-table-10-大核心语法细化实操)
- [2.4 表清空与删除：DROP vs TRUNCATE vs DELETE 终极横向对比表](#24-表清空与删除drop-vs-truncate-vs-delete-终极横向对比表)
- [2.5 数值类型深度考点：整型存储字节与 DECIMAL 精度铁律](#25-数值类型深度考点整型存储字节与-decimal-精度铁律)
- [2.6 字符串类型深度考点：CHAR vs VARCHAR 存储机制与适用场景](#26-字符串类型深度考点char-vs-varchar-存储机制与适用场景)
- [2.7 日期时间类型深度考点：DATETIME vs TIMESTAMP 跨时区与 2038 溢出](#27-日期时间类型深度考点datetime-vs-timestamp-跨时区与-2038-溢出)
- [2.8 虚拟生成列（Generated Columns）与 JSON 数据类型实操](#28-虚拟生成列generated-columns与-json-数据类型实操)
- [2.9 【必考精练】字段选型与表结构修改经典真题](#29-必考精练字段选型与表结构修改经典真题)
- [第 3 章：数据完整性约束、范式化设计与键体系规范](#第-3-章数据完整性约束范式化设计与键体系规范)
- [3.1 五大完整性约束核心定义与语法（非空、默认、唯一、主键、检查）](#31-五大完整性约束核心定义与语法非空默认唯一主键检查)
- [3.2 主键选型大厂规范：自增主键 vs UUID vs 雪花算法与 B+ 树页分裂](#32-主键选型大厂规范自增主键-vs-uuid-vs-雪花算法与-b-树页分裂)
- [3.3 外键约束（FOREIGN KEY）4 种级联动作与高并发禁用原因](#33-外键约束foreign-key4-种级联动作与高并发禁用原因)
- [3.4 数据库规范化理论：第一范式至巴斯范式（1NF, 2NF, 3NF, BCNF）与反范式化](#34-数据库规范化理论第一范式至巴斯范式1nf-2nf-3nf-bcnf与反范式化)
- [3.5 【必考精练】主外键约束与范式设计经典真题](#35-必考精练主外键约束与范式设计经典真题)
- [第 4 章：数据操作语言 (DML) 细化操作与数据安全红线](#第-4-章数据操作语言-dml-细化操作与数据安全红线)
- [4.1 插入数据 (INSERT) 细化操作集（单行、全列、批量、查询插入与 LAST_INSERT_ID）](#41-插入数据-insert-细化操作集单行全列批量查询插入与-lastinsertid)
- [4.2 高级写入：INSERT IGNORE 与 INSERT ... ON DUPLICATE KEY UPDATE (Upsert)](#42-高级写入insert-ignore-与-insert--on-duplicate-key-update-upsert)
- [4.3 更新数据 (UPDATE) 细化操作与全表误更新安全防范 (sql_safe_updates)](#43-更新数据-update-细化操作与全表误更新安全防范-sql_safe_updates)
- [4.4 删除数据 (DELETE) 与企业级逻辑软删除 (is_deleted) 规范](#44-删除数据-delete-与企业级逻辑软删除-is_deleted-规范)
- [4.5 【必考精练】DML 核心操作与软删除业务真题](#45-必考精练dml-核心操作与软删除业务真题)
- [第 5 章：单表数据查询 (DQL)、9 步执行流水线与高频内置函数库](#第-5-章单表数据查询-dql9-步执行流水线与高频内置函数库)
- [5.1 投影、别名 (AS) 与去重 (DISTINCT) 核心规则](#51-投影别名-as-与去重-distinct-核心规则)
- [5.2 条件过滤运算符（比较、BETWEEN、IN、LIKE 通配符与正则匹配 REGEXP）](#52-条件过滤运算符比较betweeninlike-通配符与正则匹配-regexp)
- [5.3 【王牌核心考点 1】SQL 三值逻辑（Three-Valued Logic）与 NULL 陷阱](#53-王牌核心考点-1sql-三值逻辑three-valued-logic与-null-陷阱)
- [5.4 排序 (ORDER BY) 与分页 (LIMIT) 及深分页延迟关联优化](#54-排序-order-by-与分页-limit-及深分页延迟关联优化)
- [5.5 【王牌核心考点 2】MySQL 引擎底层 9 步物理执行顺序生命周期](#55-王牌核心考点-2mysql-引擎底层-9-步物理执行顺序生命周期)
- [5.6 必须背熟的 MySQL 常用内置函数全集分类速查](#56-必须背熟的-mysql-常用内置函数全集分类速查)
- [5.7 【必考精练】执行顺序与 DQL 基础检索经典真题](#57-必考精练执行顺序与-dql-基础检索经典真题)
- [第 6 章：分组聚合统计与多表关联 (JOIN) 高频考点大对决](#第-6-章分组聚合统计与多表关联-join-高频考点大对决)
- [6.1 5 大聚合函数忽略 NULL 机制剖析](#61-5-大聚合函数忽略-null-机制剖析)
- [6.2 【王牌核心考点 3】COUNT(*) vs COUNT(1) vs COUNT(列名) 深度辨析](#62-王牌核心考点-3count-vs-count1-vs-count列名-深度辨析)
- [6.3 【王牌核心考点 4】WHERE vs HAVING 本质区别对比表](#63-王牌核心考点-4where-vs-having-本质区别对比表)
- [6.4 关系代数与 7 种 JOIN 关系图谱及代码实现](#64-关系代数与-7-种-join-关系图谱及代码实现)
- [6.5 【王牌核心考点 5】外连接中 ON 条件 vs WHERE 条件的决定性语义差异](#65-王牌核心考点-5外连接中-on-条件-vs-where-条件的决定性语义差异)
- [6.6 集合运算：UNION vs UNION ALL 性能与去重区别](#66-集合运算union-vs-union-all-性能与去重区别)
- [6.7 【必考精练】聚合与多表连接经典真题](#67-必考精练聚合与多表连接经典真题)
- [第 7 章：高级子查询、CTE 通用表表达式与窗口函数（笔试大题压轴）](#第-7-章高级子查询cte-通用表表达式与窗口函数笔试大题压轴)
- [7.1 子查询四大形态（标量、列、行、表子查询）与 ANY / ALL / SOME 操作符](#71-子查询四大形态标量列行表子查询与-any--all--some-操作符)
- [7.2 【王牌核心考点 6】IN vs EXISTS 选型法则与小表驱动大表](#72-王牌核心考点-6in-vs-exists-选型法则与小表驱动大表)
- [7.3 CTE 通用表表达式与递归层级查询（WITH 与 WITH RECURSIVE）](#73-cte-通用表表达式与递归层级查询with-与-with-recursive)
- [7.4 窗口函数基本语法骨架（OVER, PARTITION BY, ORDER BY, ROWS/RANGE 窗口帧）](#74-窗口函数基本语法骨架over-partition-by-order-by-rowsrange-窗口帧)
- [7.5 【必考压轴 1】3 大排名函数核心辨析（ROW_NUMBER, RANK, DENSE_RANK）](#75-必考压轴-13-大排名函数核心辨析row_number-rank-dense_rank)
- [7.6 跨行位移函数（LEAD, LAG）与极值函数（FIRST_VALUE, LAST_VALUE, NTH_VALUE）](#76-跨行位移函数lead-lag与极值函数first_value-last_value-nth_value)
- [7.7 经典大厂 SQL 题型实战模板（TopN、连续签到、次日留存、行转列）](#77-经典大厂-sql-题型实战模板topn连续签到次日留存行转列)
- [7.8 【必考精练】窗口函数与复杂统计真题演练](#78-必考精练窗口函数与复杂统计真题演练)
- [第 8 章：数据库高级对象：视图、存储过程、函数与触发器](#第-8-章数据库高级对象视图存储过程函数与触发器)
- [8.1 视图 (View)：定义、WITH CHECK OPTION 机制与可更新视图限制](#81-视图-view定义with-check-option-机制与可更新视图限制)
- [8.2 存储过程 (Stored Procedure)：参数类型、变量、流程控制与游标 (Cursor) 实操](#82-存储过程-stored-procedure参数类型变量流程控制与游标-cursor-实操)
- [8.3 自定义函数 (Stored Function)：语法与存储过程的核心区别](#83-自定义函数-stored-function语法与存储过程的核心区别)
- [8.4 触发器 (Trigger)：BEFORE/AFTER INSERT/UPDATE/DELETE 与 NEW/OLD 伪记录](#84-触发器-triggerbeforeafter-insertupdatedelete-与-newold-伪记录)
- [8.5 【必考精练】数据库对象经典真题](#85-必考精练数据库对象经典真题)
- [第 9 章：事务 ACID、并发控制、锁机制与 MVCC 底层原理](#第-9-章事务-acid并发控制锁机制与-mvcc-底层原理)
- [9.1 事务四大特性 (ACID) 与底层保障日志对照表](#91-事务四大特性-acid-与底层保障日志对照表)
- [9.2 三大并发读异常（脏读、不可重复读、幻读）定义与示例](#92-三大并发读异常脏读不可重复读幻读定义与示例)
- [9.3 四大事务隔离级别对比表（RU, RC, RR, Serializable）](#93-四大事务隔离级别对比表ru-rc-rr-serializable)
- [9.4 【王牌底层核心 1】MVCC 多版本并发控制底层运行机制（隐藏列、版本链、ReadView 四步法则）](#94-王牌底层核心-1mvcc-多版本并发控制底层运行机制隐藏列版本链readview-四步法则)
- [9.5 锁机制全景：共享锁 (S) vs 排他锁 (X)、意向锁 (IS/IX) 与行锁三大算法（记录锁、间隙锁、临键锁）](#95-锁机制全景共享锁-s-vs-排他锁-x意向锁-isix-与行锁三大算法记录锁间隙锁临键锁)
- [9.6 死锁的产生原因、排查日志 (SHOW ENGINE INNODB STATUS) 与防范策略](#96-死锁的产生原因排查日志-show-engine-innodb-status-与防范策略)
- [9.7 【必考精练】事务隔离与锁机制考题](#97-必考精练事务隔离与锁机制考题)
- [第 10 章：MySQL 存储引擎、B+ 树索引底层与 EXPLAIN 调优全解析](#第-10-章mysql-存储引擎b-树索引底层与-explain-调优全解析)
- [10.1 存储引擎对比：InnoDB vs MyISAM 核心特性矩阵](#101-存储引擎对比innodb-vs-myisam-核心特性矩阵)
- [10.2 索引数据结构选型推演：为什么是 B+ 树？（树高计算与扇出比数学推导）](#102-索引数据结构选型推演为什么是-b-树树高计算与扇出比数学推导)
- [10.3 聚簇索引 vs 二级索引、回表查询与覆盖索引优化](#103-聚簇索引-vs-二级索引回表查询与覆盖索引优化)
- [10.4 联合索引最左前缀匹配法则（Leftmost Prefix Rule）与索引下推 (ICP)](#104-联合索引最左前缀匹配法则leftmost-prefix-rule与索引下推-icp)
- [10.5 【必背考点 1】索引失效 7 大经典场景与防雷口诀](#105-必背考点-1索引失效-7-大经典场景与防雷口诀)
- [10.6 【必背考点 2】EXPLAIN 执行计划关键字段深度判读（type, key_len, Extra）](#106-必背考点-2explain-执行计划关键字段深度判读type-key_len-extra)
- [10.7 慢查询日志 (Slow Query Log) 与分析工具 mysqldumpslow 常用命令](#107-慢查询日志-slow-query-log-与分析工具-mysqldumpslow-常用命令)
- [10.8 【必考精练】索引调优与慢 SQL 分析真题演练](#108-必考精练索引调优与慢-sql-分析真题演练)
---
第 1 章：数据库系统基石、环境架构与精细化基础操作
1.1 关系型数据库核心架构与基本概念速记
| 核心概念 | 英文术语 | 概念本质与考试必记要点 |
|---|
| **数据库** | Database (DB) | 长期存储在计算机内、有组织、可共享的大量数据集合。 |
| **数据库管理系统** | DBMS | 位于用户与操作系统之间的数据管理控制软件（如 MySQL, PostgreSQL, Oracle）。 |
| **数据库系统** | DBS | 由数据库、DBMS、应用程序和数据库管理员 (DBA) 组成的完整系统体系。 |
| **二维表** | Table | 关系模型的基本存储结构，由行与列组成，数学本质上是笛卡尔积的有限子集。 |
| **行 / 记录 / 元组** | Row / Record / Tuple | 表中水平方向的一行，代表**一个具体的客观业务实体对象**。 |
| **列 / 字段 / 属性** | Column / Field / Attribute | 表中垂直方向的一列，代表实体拥有的**某一项特性指标与数据类型**。 |
| **候选码 / 候选键** | Candidate Key | 表中能够**唯一标识一个元组的属性或属性组**，其任意真子集都无法唯一标识该元组。 |
| **主键 / 主码** | Primary Key (PK) | 从候选键中人为选定作为行唯一标识的字段，具备 `NOT NULL + UNIQUE` 绝对铁律。 |
| **外键 / 外码** | Foreign Key (FK) | 当前表的一个属性组，虽然不是当前表的主键，但引用了另一张表的主键，维系实体间参照完整性。 |
---
1.2 SQL 语言五大功能分类速查表
**【高频考点】**：考试常以选择题形式考查“某关键字属于哪种 SQL 分类”，务必牢记以下归属！
| 语言分类 | 英文全称 | 核心职能 | 核心代表指令 |
|---|
| **DDL** | Data Definition Language（数据定义语言） | 定义、修改、删除数据库或数据表的物理结构（元数据管理） | `CREATE`, `DROP`, `ALTER`, `TRUNCATE` |
| **DML** | Data Manipulation Language（数据操作语言） | 对表内具体数据记录进行增加、删除、修改（不改变表结构） | `INSERT`, `UPDATE`, `DELETE` |
| **DQL** | Data Query Language（数据查询语言） | 对数据进行多维度检索、筛选、排序、分组聚合与展示 | `SELECT` |
| **TCL** | Transaction Control Language（事务控制语言） | 控制事务开启、安全提交、保存点与失败回滚 | `START TRANSACTION`, `COMMIT`, `ROLLBACK`, `SAVEPOINT` |
| **DCL** | Data Control Language（数据控制语言） | 管理数据库连接账号、安全策略与数据读写权限授权 | `GRANT`, `REVOKE`, `CREATE USER` |
---
1.3 MySQL 客户端连接与服务基础指令
1. 命令行登录与退出
# 标准格式：-h主机 -P端口 -u用户名 -p密码
mysql -h 127.0.0.1 -P 3306 -u root -p
# 回车后密文输入密码（生产环境严禁直接在 -p 后面写明文密码，防止 bash 历史记录泄露）
# 退出客户端连接
exit;
# 或
quit;
2. 查看服务状态与系统核心变量
-- 查看当前连接的 MySQL 版本号
SELECT VERSION();
-- 查看当前登录用户及连接客户端来源主机
SELECT USER();
-- 查看当前服务器完整运行状态
STATUS;
-- 查看服务器全局最大并发连接数与当前已建立连接数
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
-- 查看客户端连接空闲超时自动断开时间（默认 28800 秒 = 8 小时）
SHOW VARIABLES LIKE 'wait_timeout';
---
1.4 数据库（Database）完整基础操作（增删改查查）
1. 查看已有数据库
SHOW DATABASES;
2. 创建数据库（标准规范语法）
-- 语法：CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARACTER SET 字符集] [COLLATE 排序规则];
CREATE DATABASE IF NOT EXISTS mall_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
3. 查看数据库建库语句与编码
SHOW CREATE DATABASE mall_db;
4. 切换/使用指定数据库
USE mall_db;
-- 验证当前正处于哪个数据库
SELECT DATABASE();
5. 修改数据库字符集与校验集
ALTER DATABASE mall_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
6. 删除数据库（危险操作）
-- 语法：DROP DATABASE [IF EXISTS] 数据库名;
DROP DATABASE IF EXISTS mall_db;
---
1.5 字符集（utf8mb4）与校验规则（Collation）深度考点
**【核心要点】**：
1. **为什么在 MySQL 中严禁使用 `utf8` 字符集？**
- MySQL 历史遗留的 `utf8` 实际上是 `utf8mb3`，最多只支持 3 个字节存储一个字符；
- 真正的 UTF-8 标准支持 1~4 字节。移动端的 Emoji 表情符号（如 😄、🎉）以及部分生僻汉字占用 4 个字节；
- 若使用 `utf8`，插入 Emoji 会直接抛出 `java.sql.SQLException: Incorrect string value` 异常，导致写入崩溃；
- **生产环境建库建表必须强制指定为 `utf8mb4`**！
2. **校验规则（Collation）后缀含义辨析**：
- `_ci`（Case Insensitive）：**大小写不敏感**。`'admin'` 与 `'ADMIN'` 被视为相同；
- `_bin`（Binary）：**二进制大小写敏感**。按字符的 ASCII/二进制编码直接对比，`'a'` 与 `'A'` 不相等；
- `_cs`（Case Sensitive）：**大小写敏感**。
---
1.6 用户与权限管理体系（DCL 完整指令集实操）
-- 1. 创建新用户账号 (用户名@允许登录的主机，% 代表任意远程主机)
CREATE USER 'dev_user'@'%' IDENTIFIED BY 'Password123!';
-- 2. 修改用户密码 (MySQL 8.0 规范写法)
ALTER USER 'dev_user'@'%' IDENTIFIED BY 'NewPass456!';
-- 3. 查看指定用户的权限列表
SHOW GRANTS FOR 'dev_user'@'%';
-- 4. 赋予指定数据库全部表的增删改查权限 (GRANT 权限 ON 库.表 TO 用户)
GRANT SELECT, INSERT, UPDATE, DELETE ON mall_db.* TO 'dev_user'@'%';
-- 5. 撤销指定权限 (REVOKE 权限 ON 库.表 FROM 用户)
REVOKE DELETE ON mall_db.* FROM 'dev_user'@'%';
-- 6. 刷新权限系统表使修改立刻生效
FLUSH PRIVILEGES;
-- 7. 删除用户账号
DROP USER 'dev_user'@'%';
---
1.7 【必考精练】库操作与分类辨析经典真题
题 1-1（考点：SQL 语言分类辨析）
下列 SQL 语句中，全部属于数据定义语言 (DDL) 的一组是：
A. `CREATE DATABASE`, `INSERT INTO`, `DROP TABLE`
B. `ALTER TABLE`, `TRUNCATE TABLE`, `DROP DATABASE`
C. `UPDATE`, `DELETE FROM`, `GRANT`
D. `SELECT`, `COMMIT`, `REVOKE`
**【参考答案】**：B
**【抓分解析】**：
- `ALTER`, `TRUNCATE`, `DROP`, `CREATE` 改变的是库或表的物理架构，属 DDL；
- A 中 `INSERT` 属 DML；C 中 `UPDATE/DELETE` 属 DML，`GRANT` 属 DCL；D 中 `SELECT` 属 DQL，`COMMIT` 属 TCL，`REVOKE` 属 DCL。
题 1-2（考点：MySQL 字符集与 Emoji 崩溃防御）
某移动端社交 APP 在用户注册填写个性签名时输入了表情符号 `😄`，后台报错无法写入。导致该问题的最直接原因及推荐解决方案是：
A. 数据库主键未设置自增，改为 AUTO_INCREMENT
B. 数据库编码为 utf8 (utf8mb3) 无法存放 4 字节字符，应将库表编码升级为 utf8mb4
C. 客户端未开启自动提交事务，应执行 COMMIT
D. 用户名违反唯一约束冲突
**【参考答案】**：B
**【抓分解析】**：标准 UTF-8 Emoji 占 4 字节，MySQL 旧版 `utf8` 仅支持 3 字节，必须变更为 `utf8mb4`。
---
第 2 章：数据表（Table）精细化操作与数据类型全谱系
2.1 标准工业级建表模板（含引擎、字符集、注释、键约束）
CREATE TABLE IF NOT EXISTS tb_users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户主键ID',
username VARCHAR(32) NOT NULL COMMENT '登录账号(唯一)',
password_hash CHAR(64) NOT NULL COMMENT 'SHA-256密码散列值(定长64字符)',
email VARCHAR(128) DEFAULT NULL COMMENT '用户电子邮箱',
balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '账户可用余额(定点数,精确到分)',
status TINYINT NOT NULL DEFAULT 1 COMMENT '账户状态: 0-禁用, 1-正常',
is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑软删除标识: 0-未删除, 1-已删除',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
-- 约束与索引声明
PRIMARY KEY (id),
UNIQUE KEY uk_username (username),
KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户中心核心主表';
---
2.2 查看表结构与元数据分析指令（DESC, SHOW 与 information_schema）
-- 1. 查看表的所有列名、类型、是否允许NULL、键属性、默认值
DESC tb_users;
-- 或完整形式
DESCRIBE tb_users;
-- 2. 查看创建该表时的完整底层 DDL 语句（包括自增当前游标与存储引擎）
SHOW CREATE TABLE tb_users\G;
-- 3. 查看表的物理存储状态（行数、平均行长、数据大小、索引大小）
SHOW TABLE STATUS LIKE 'tb_users'\G;
-- 4. 深入系统字典库查询所有字段元数据
SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT, IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mall_db' AND TABLE_NAME = 'tb_users';
---
2.3 表结构重构：ALTER TABLE 10 大核心语法细化实操
**【高频考点】**：考试常要求手写修改表结构的 ALTER 语句，必须熟练掌握！
-- 1. 增加新列 (ADD)
-- 语法：ALTER TABLE 表名 ADD [COLUMN] 字段名 类型 [约束] [FIRST | AFTER 已有字段];
ALTER TABLE tb_users ADD COLUMN phone CHAR(11) NOT NULL DEFAULT '' AFTER username;
-- 2. 删除已有列 (DROP)
-- 语法：ALTER TABLE 表名 DROP [COLUMN] 字段名;
ALTER TABLE tb_users DROP COLUMN email;
-- 3. 修改字段类型与约束 (MODIFY) —— 不改变列名
-- 语法：ALTER TABLE 表名 MODIFY [COLUMN] 字段名 新类型 [新约束];
ALTER TABLE tb_users MODIFY COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';
-- 4. 字段重命名并可同时修改类型与约束 (CHANGE)
-- 语法：ALTER TABLE 表名 CHANGE [COLUMN] 旧字段名 新字段名 新类型 [新约束];
ALTER TABLE tb_users CHANGE COLUMN phone mobile_phone CHAR(11) NOT NULL DEFAULT '';
-- 5. 修改字段默认值 (ALTER COLUMN ... SET/DROP DEFAULT)
ALTER TABLE tb_users ALTER COLUMN status SET DEFAULT 0;
-- 6. 修改数据表名称 (RENAME)
ALTER TABLE tb_users RENAME TO tb_user_account;
-- 或使用专用指令：RENAME TABLE tb_users TO tb_user_account;
-- 7. 添加主键与索引
ALTER TABLE tb_user_account ADD PRIMARY KEY (id);
ALTER TABLE tb_user_account ADD UNIQUE KEY uk_mobile (mobile_phone);
ALTER TABLE tb_user_account ADD INDEX idx_created (created_at);
-- 8. 删除主键与索引
ALTER TABLE tb_user_account DROP PRIMARY KEY;
ALTER TABLE tb_user_account DROP INDEX uk_mobile;
-- 9. 修改表的默认字符集与校验集
ALTER TABLE tb_user_account CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 10. 重置表的自增计数器起点
ALTER TABLE tb_user_account AUTO_INCREMENT = 1000;
---
2.4 表清空与删除：DROP vs TRUNCATE vs DELETE 终极横向对比表
| 对比维度 | `DELETE FROM table;` | `TRUNCATE TABLE table;` | `DROP TABLE table;` |
|---|
| **SQL 门类** | **DML**（数据操作语言） | **DDL**（数据定义语言） | **DDL**（数据定义语言） |
| **操作对象** | 逐行扫描并删除数据行 | 清空整张表的所有数据内容 | 彻底摧毁数据行及表结构骨架 |
| **事务日志** | 记录详细 Undo/Redo 日志，开销大 | 不逐行记录，仅记录数据页释放 | 仅记录元数据变更 |
| **能否事务回滚** | **可以回滚** (`ROLLBACK`) | **绝不能回滚** | **绝不能回滚** |
| **自增计数器** | **不重置**！下一条继续自增累加 | **重置为 1** (初始起点) | 表物理消失，计数器不复存在 |
| **物理空间释放** | 仅标记物理页为空闲，**不释放磁盘空间** | **立刻释放数据物理存储页** | **彻底释放全部表空间** |
| **执行速度** | 极慢（行级逐条锁与打标记） | 极快（重建表物理空间） | 瞬间完成 |
| **触发器支持** | 会触发 `ON DELETE` 触发器 | **不会激活** DELETE 触发器 | 无触发器执行 |
---
2.5 数值类型深度考点：整型存储字节与 DECIMAL 精度铁律
1. 整数类型存储容量与范围
| 类型 | 存储字节 | 有符号 (Signed) 取值范围 | 无符号 (UNSIGNED) 取值范围 |
|---|
| **TINYINT** | 1 字节 | -128 ~ 127 | 0 ~ 255 |
| **SMALLINT** | 2 字节 | -32,768 ~ 32,767 | 0 ~ 65,535 |
| **MEDIUMINT** | 3 字节 | -8,388,608 ~ 8,388,607 | 0 ~ 16,777,215 |
| **INT** | 4 字节 | 约 -21.47 亿 ~ 21.47 亿 | 0 ~ 约 42.94 亿 |
| **BIGINT** | 8 字节 | -2^63 ~ 2^63 - 1 | 0 ~ 2^64 - 1 |
⚠️ **【高频考点陷阱：INT(11) 的括号数字代表什么？】**
- **错误理解**：代表只能存 11 位数字，或者占用 11 字节。
- **正确真相**：括号内的数字代表**显示宽度（Display Width）**，仅当字段配置了 `ZEROFILL`（前导补零）属性时才在客户端展示时生效（如 `1` 显示为 `00000000001`）。
- **无论是 `INT(1)` 还是 `INT(11)`，其物理存储始终固定占用 4 个字节，可存储的数值大小上限完全一致（-2147483648 ~ 2147483647）！**
2. 浮点数 (FLOAT/DOUBLE) vs 定点数 (DECIMAL)
- `FLOAT`（4 字节）与 `DOUBLE`（8 字节）：二进制近似存储，存在**精度截断与舍入误差**。`0.1 + 0.2` 计算可能得出 `0.30000000000000004`。
- `DECIMAL(M, D)`：**定点数精准存储**。内部以字符串数组形式无损编码存储。
- `M`：**精度（Precision）**，总有效数字位数（1~65）；
- `D`：**标度（Scale）**，小数点后保留位数（0~30）。
- **铁律：金融账目、电商金额、余额计算必须强制使用 `DECIMAL(M, D)`，绝对禁止使用 FLOAT/DOUBLE！**
---
2.6 字符串类型深度考点：CHAR vs VARCHAR 存储机制与适用场景
| 对比维度 | `CHAR(M)` 定长字符串 | `VARCHAR(M)` 变长字符串 |
|---|
| **存储长度** | **固定长度**，恒定占用 M 个字符空间 | **动态长度**，实际字符长度 L + 1~2 字节长度前缀 |
| **空格处理** | 存入时右侧填充空格补齐 M，**读取时自动丢弃尾随空格** | **如实保存并保留尾随空格** |
| **存储空间开销** | 若实际内容短，产生存储碎片（空间浪费） | 按需分配，空间利用率极高 |
| **执行与查找性能** | 极高（寻道位置固定，无额外偏移计算） | 略逊于 CHAR（需解析长度字节，动态寻址） |
| **行内碎片与修改** | 原地更新，不会发生行迁移（Page Overflow） | 变长更新变大时可能导致页分裂（Page Split） |
| **推荐适用场景** | 手机号 `CHAR(11)`、MD5/SHA 哈希 `CHAR(64)`、身份证号 `CHAR(18)`、状态码 `CHAR(1)` | 用户名 `VARCHAR(32)`、文章标题 `VARCHAR(128)`、家庭地址 `VARCHAR(255)` |
---
2.7 日期时间类型深度考点：DATETIME vs TIMESTAMP 跨时区与 2038 溢出
| 对比维度 | `DATETIME` | `TIMESTAMP` |
|---|
| **存储字节** | 8 字节（MySQL 5.6.4+ 优化后为 5 字节） | 4 字节 |
| **支持时间范围** | **`1000-01-01 00:00:00` ~ `9999-12-31 23:59:59`** | **`1970-01-01 00:00:01` ~ `2038-01-19 03:14:07` UTC** |
| **时区敏感性** | **时区无关**。存什么时间读出来就是什么时间 | **强时区依赖**。存入时转为 UTC 时间戳，读取时按当前会话时区转换 |
| **2038年问题** | **无风险**，可从容使用到公元 9999 年 | **存在 2038 年 32 位整型溢出灾难（Year 2038 Problem）** |
| **默认自动更新** | MySQL 5.6+ 支持 `DEFAULT CURRENT_TIMESTAMP` | 天然支持 `DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP` |
---
2.8 虚拟生成列（Generated Columns）与 JSON 数据类型实操
-- 1. 虚拟生成列：由其他列计算派生得来 (VIRTUAL-读时计算不占磁盘; STORED-写时计算落盘)
CREATE TABLE tb_order_items (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
price DECIMAL(10,2) NOT NULL,
quantity INT NOT NULL,
-- 自动计算总金额并建立索引
total_price DECIMAL(10,2) GENERATED ALWAYS AS (price * quantity) STORED,
KEY idx_total (total_price)
);
-- 2. 原生 JSON 类型与检索函数
CREATE TABLE tb_user_profiles (
user_id BIGINT PRIMARY KEY,
extra_info JSON COMMENT '扩展动态属性'
);
-- 插入 JSON
INSERT INTO tb_user_profiles VALUES (1, '{"age": 25, "tags": ["it", "sports"]}');
-- 查询 JSON 内部字段 (->> 提取为纯文本)
SELECT user_id, extra_info->>'$.age' AS age FROM tb_user_profiles;
---
2.9 【必考精练】字段选型与表结构修改经典真题
题 2-1（考点：INT(10) 与存储大小概念陷阱）
关于 MySQL 中 `id INT(10)` 与 `id INT(4)` 字段的对比，正确的是：
A. `INT(10)` 比 `INT(4)` 能存储更大的数字
B. `INT(10)` 占用 10 字节存储空间，`INT(4)` 占用 4 字节存储空间
C. 两者物理存储大小均为 4 字节，可容纳的数值范围完全相同，括号数字仅为配置 ZEROFILL 时的显示宽度
D. `INT(4)` 最多只能存 4 位数字（即 9999）
**【参考答案】**：C
**【抓分解析】**：整型存储字节与取值范围仅由类型本身（TINYINT/INT/BIGINT）决定，括号内数字为显示宽度，配合 ZEROFILL 补零生效，绝不改变存储范围。
题 2-2（考点：清空表数据的指令选择）
在事务内对一张含有 500 万行测试数据的表执行清空操作，要求在发生异常时能通过 `ROLLBACK` 恢复数据，必须使用的语句是：
A. `TRUNCATE TABLE test_table;`
B. `DROP TABLE test_table;`
C. `DELETE FROM test_table;`
D. `ALTER TABLE test_table DROP ALL;`
**【参考答案】**：C
**【抓分解析】**：`TRUNCATE` 和 `DROP` 属于 DDL 语言，执行后隐式提交，不可回滚；只有 DML 语言的 `DELETE FROM` 可以被事务回滚。
---
第 3 章：数据完整性约束、范式化设计与键体系规范
3.1 五大完整性约束核心定义与语法
| 约束类型 | 关键字 | 约束职能与规则 | 代码示例（列级 / 表级） |
|---|
| **主键约束** | `PRIMARY KEY` | 唯一标识记录，强制 `NOT NULL + UNIQUE`，单表只能有 1 个 | `id BIGINT PRIMARY KEY AUTO_INCREMENT` |
| **唯一约束** | `UNIQUE` | 保证该列数值全表不重复，但**允许存在多个 NULL 值** | `username VARCHAR(32) UNIQUE` |
| **非空约束** | `NOT NULL` | 强制该字段不能插入或修改为 NULL 值 | `status TINYINT NOT NULL` |
| **默认值约束** | `DEFAULT` | 未显式赋值时自动填入预设默认值 | `balance DECIMAL(10,2) DEFAULT 0.00` |
| **检查约束** | `CHECK` | 约束字段数值必须满足特定的布尔逻辑表达式 | `CHECK (age >= 0 AND age <= 150)` |
---
3.2 主键选型大厂规范：自增主键 vs UUID vs 雪花算法与 B+ 树页分裂
**【核心要点】**：大厂开发规范强制建议单表主键采用**自增整型主键（BIGINT UNSIGNED AUTO_INCREMENT）**，慎用或禁用无序 UUID。
【自增主键：单调递增顺次追加】
Page 1: [1, 2, 3] ──> Page 2: [4, 5, 6] ──> Page 3: [7, 8, 9] (直接在最末尾数据页追加，无页分裂，磁盘顺序写)
【UUID：完全无序随机散列】
新插入 UUID="a7f8..." ──> 强行挤入已经写满的 Page 1 中间 ──> 触发【B+ 树页分裂(Page Split)】！
导致内存与磁盘大量数据搬迁移动，产生巨量磁盘碎片，导致索引体积暴涨与写入性能崩塌！
| 选型对比 | 自增主键 (`BIGINT AUTO_INCREMENT`) | 随机 UUID (`CHAR(36)`) | 分布式雪花算法 (Snowflake ID) |
|---|
| **物理存储占用** | 仅占用 8 个字节 | 占用 36 字符（即使去掉中划线也占 32 字节） | 占用 8 字节（转为 BIGINT 存储） |
| **B+树写入行为** | **顺序递增追加写入**，零页分裂，速度极快 | **随机离散插入**，频繁引起 B+ 树页分裂与重平衡 | **趋势递增**，较少引发页分裂 |
| **聚簇索引碎片** | 页面填充率极高（通常接近 100%），紧凑 | 产生大量物理内部碎片，查询缓存命中率骤降 | 页面紧凑度极高 |
| **二级索引膨胀** | 二级索引叶子节点仅存 8 字节的主键 ID | 每个二级索引叶子都要存 36 字节 UUID，索引空间翻数倍 | 二级索引开销仅 8 字节 |
| **业务保密性** | 易被爬虫按连续数字遍历猜测业务总量 | 具备全局唯一性与不可推导性，天然防抓取 | 难以直接猜测总量，支持分库分表 |
---
3.3 外键约束（FOREIGN KEY）4 种级联动作与高并发禁用原因
1. 外键级联操作语法与 4 种动作
ALTER TABLE tb_orders
ADD CONSTRAINT fk_order_user
FOREIGN KEY (user_id) REFERENCES tb_users (id)
ON DELETE CASCADE
ON UPDATE RESTRICT;
| 级联动作 | 行为触发规则 | 业务含义 |
|---|
| **CASCADE** | 父表记录被删除/更新时，**子表匹配的外键记录全自动同步级联删除/更新** | 联动抹除（如删除部门，级联删除所有下属员工，风险极高） |
| **RESTRICT / NO ACTION** | 若子表中存在关联的明细记录，**严禁删除或更新父表主键** | 强制防御（报错阻止删除） |
| **SET NULL** | 父表记录被删除/更新时，子表对应外键字段自动更新为 `NULL` | 留存孤儿记录（子表列必须允许为 NULL） |
2. 为什么阿里开发手册强制规定：“不得使用外键与级联，一切外键概念必须在应用层解决”？
1. **性能损耗与并发锁放大**：每次对子表插入、更新，都必须反查父表加锁校验；级联删除会引发跨表串行锁定，产生大量锁等待甚至死锁。
2. **分库分表与分布式架构死穴**：当用户表和订单表被拆分到不同物理数据库实例时，底层物理外键彻底失效。
3. **运维与导入导出灾难**：做历史冷热数据迁移、批量脚本灌入时，外键依赖导致清空表与导入数据异常繁琐。
---
3.4 数据库规范化理论：第一范式至巴斯范式（1NF, 2NF, 3NF, BCNF）与反范式化
**【必背核心：三大范式通俗化记忆口诀】**：
- **1NF（属性不可分）**：单列必须是原子属性，不能再拆分（如把地址拆分为省、市、区）；
- **2NF（消除非主属性对主键的部分依赖）**：要求表必须有主键，非主键列必须**完全依赖于整个主键**，不能只依赖复合主键的一部分；
- **3NF（消除非主属性对主键的传递依赖）**：非主键列必须直接依赖主键，不能产生 `A(主键) -> B -> C` 的传递依赖链条；
- **BCNF（巴斯-科德范式）**：在 3NF 基础上，消除**主属性**对主键的部分与传递依赖。
反范式化（Denormalization）的工业级权衡
- **过度规范化的恶果**：表拆得过细，一次普通业务查询需要 JOIN 7~8 张表，产生恐怖的笛卡尔积中间临时表与磁盘 I/O 崩塌。
- **反范式化策略**：为了“以空间换时间，减少高频 JOIN”，故意在订单明细表中直接冗余保存 `商品名称`、`店铺名称` 等字段，既降低了读取延迟，又固化了历史下单快照（防止后续商品改名导致历史账单失真）。
---
3.5 【必考精练】主外键约束与范式设计经典真题
题 3-1（考点：外键级联动作判读）
设存在父表 `Department(dept_id, dept_name)` 与子表 `Employee(emp_id, emp_name, dept_id)`。外键定义包含 `ON DELETE SET NULL`。若执行 `DELETE FROM Department WHERE dept_id = 1;`，则子表中原属于 1 号部门的员工记录将发生什么变化？
A. 员工记录被整行同步删除
B. 操作直接被系统拦截报错，拒绝执行
C. 员工记录仍然存在，但其 `dept_id` 字段的值被自动修改为 `NULL`
D. 员工记录被转移到默认部门
**【参考答案】**：C
**【抓分解析】**：`SET NULL` 动作指父行删除后，子表中关联该父行外键值的字段自动置空保活。
题 3-2（考点：数据库第二范式 2NF 判定）
存在关系模式 `R(学号, 课程号, 姓名, 成绩)`，其中主键为复合主键 `(学号, 课程号)`。该关系模式最高满足第几范式？
A. 1NF
B. 2NF
C. 3NF
D. BCNF
**【参考答案】**：A
**【抓分解析】**：`姓名` 仅依赖于 `学号`，即存在非主属性对复合主键的部分依赖，破坏了 2NF 的要求，因此最高仅满足 1NF。必须拆分为 `学生表(学号, 姓名)` 与 `选课表(学号, 课程号, 成绩)` 才能达到 2NF。
---
第 4 章：数据操作语言 (DML) 细化操作与数据安全红线
4.1 插入数据 (INSERT) 细化操作集（单行、全列、批量、查询插入与 LAST_INSERT_ID）
-- 1. 指定字段单行插入（推荐写法）
INSERT INTO tb_users (username, password_hash, balance, status)
VALUES ('zhangsan', 'e10adc3949ba59abbe56e057f20f883e', 100.00, 1);
-- 2. 全字段按序插入（必须严格与表中列顺序一一对应）
INSERT INTO tb_users
VALUES (NULL, 'lisi', 'e10adc3949ba59abbe56e057f20f883e', 'lisi@qq.com', 500.00, 1, 0, NOW(), NOW());
-- 3. 工业级高性能批量插入（单条 SQL 批量插入数百条，比循环单条快百倍）
INSERT INTO tb_users (username, password_hash, balance) VALUES
('wangwu', 'hash_w5', 200.00),
('zhaoliu', 'hash_z6', 300.00),
('sunqi', 'hash_s7', 400.00);
-- 4. 查询结果直接插入（常用于历史数据归档或报表宽表生成）
INSERT INTO tb_users_archive (id, username, balance)
SELECT id, username, balance FROM tb_users WHERE created_at < '2025-01-01';
-- 5. 获取刚刚插入成功记录的自增主键值
SELECT LAST_INSERT_ID();
---
4.2 高级写入：INSERT IGNORE 与 INSERT ... ON DUPLICATE KEY UPDATE (Upsert)
1. `INSERT IGNORE INTO`（忽略重复）
当遇到主键（PRIMARY KEY）或唯一键（UNIQUE）冲突时，不抛出 Duplicate entry 错误，而是静默忽略当前行插入，保留旧数据。
INSERT IGNORE INTO tb_users (id, username) VALUES (1, 'zhangsan');
2. `INSERT ... ON DUPLICATE KEY UPDATE`（存在则更新，不存在则插入）
高并发业务（如点赞数累计、阅读量上报、库存扣减）下的幂等神器！
-- 若 user_id=101 记录已存在，则自动执行 UPDATE 将 views 加 1；若不存在则插入新行
INSERT INTO tb_user_stats (user_id, views, likes)
VALUES (101, 1, 0)
ON DUPLICATE KEY UPDATE
views = views + 1;
---
4.3 更新数据 (UPDATE) 细化操作与全表误更新安全防范 (sql_safe_updates)
-- 1. 单列与多列条件更新
UPDATE tb_users
SET balance = balance + 50.00, status = 1
WHERE id = 10;
-- 2. 生产红线防灾机制：安全更新模式
-- 开启后，UPDATE 和 DELETE 语句若没有加 WHERE 条件，或 WHERE 条件中未使用主键/索引字段，MySQL 直接报错拒绝执行！
SET sql_safe_updates = 1;
---
4.4 删除数据 (DELETE) 与企业级逻辑软删除 (is_deleted) 规范
-- 1. 物理硬删除（数据彻底从磁盘物理页抹除，难以审计回溯）
DELETE FROM tb_users WHERE id = 100;
-- 2. 工业级逻辑软删除（通过标志位标记，数据保留在库）
UPDATE tb_users SET is_deleted = 1 WHERE id = 100;
-- 后续所有业务查询均自动追加软删除过滤条件
SELECT * FROM tb_users WHERE is_deleted = 0;
**【架构师必考：软删除与唯一索引冲突如何破解？】**
- **痛点**：若设置 `username` 为唯一索引，用户张三注销（`is_deleted=1`）后，新用户再次注册同名“张三”，会因数据库中已存在张三旧记录而抛出唯一键冲突错误！
- **经典优雅解法**：联合唯一索引设为 `UNIQUE (username, is_deleted)`。未删除时 `is_deleted = 0`；被软删除时，将 `is_deleted` 的值更新为其主键 `id`（例如 `is_deleted = 100`）。这样既保证了正常数据 `username + 0` 唯一，又允许任意多个历史注销数据并存！
---
4.5 【必考精练】DML 核心操作与软删除业务真题
题 4-1（考点：INSERT ... ON DUPLICATE KEY UPDATE 行为）
某商品销量表定义了 `product_id INT PRIMARY KEY, sold_count INT`。当前表中有一行 `(101, 50)`。执行语句：
`INSERT INTO tb_sales (product_id, sold_count) VALUES (101, 10) ON DUPLICATE KEY UPDATE sold_count = sold_count + 10;`
执行后该行记录的 `sold_count` 值为：
A. 10
B. 50
C. 60
D. 报错主键冲突
**【参考答案】**：C
**【抓分解析】**：主键 101 已存在，触发 `ON DUPLICATE KEY UPDATE` 分支，原值 50 + 10 = 60。
---
第 5 章：单表数据查询 (DQL)、9 步执行流水线与高频内置函数库
5.1 投影、别名 (AS) 与去重 (DISTINCT) 核心规则
-- 1. 投影与别名
SELECT
product_name AS "商品名称",
price * 0.8 AS "折后价"
FROM tb_products;
-- 2. 单列去重 vs 多列组合去重
SELECT DISTINCT city FROM tb_users;
-- 注意：DISTINCT 作用于其后所有的列！下面语句表示【城市 + 性别】完全相同的一整组才去重
SELECT DISTINCT city, gender FROM tb_users;
---
5.2 条件过滤运算符（比较、BETWEEN、IN、LIKE 通配符与正则匹配 REGEXP）
| 运算符 | 语义与考点规则 | 示例 |
|---|
| `=`, `!=` (`<>`), `>`, `<` | 基础数学比较。注意：**比较 NULL 必须用 `IS NULL`，不能用 `=`** | `price > 100` |
| `BETWEEN a AND b` | **闭区间过滤**，等价于 `>= a AND <= b`（两端边界均包含！） | `age BETWEEN 18 AND 35` |
| `IN (v1, v2, ...)` | 离散枚举，等价于多个 `OR` | `city IN ('北京', '上海', '广州')` |
| `LIKE '%str%'` | **模糊查询**：`%` 匹配 0 到任意多个字符；`_` 精确匹配恰好 1 个字符 | `name LIKE '张_'`（找姓张且名字为两字的） |
| `ESCAPE` | **转义特殊通配符**：查找包含 `%` 或 `_` 的真实字符 | `code LIKE '%/_%' ESCAPE '/'` |
| `REGEXP` / `RLIKE` | 正则表达式模式匹配 | `email REGEXP '^[a-zA-Z0-9]+@[a-zA-Z0-9]+' ` |
---
5.3 【王牌核心考点 1】SQL 三值逻辑（Three-Valued Logic）与 NULL 陷阱
⚠️ **【高频命题重灾区】**：在 SQL 规范中，布尔判断结果不是二维（真/假），而是三维：`TRUE`, `FALSE`, `UNKNOWN`（未知）！
【三值真值表核心准则】
1. NULL 代表“未知的值”，因此：NULL = NULL 的判断结果不是 TRUE，而是 UNKNOWN！
2. 凡是用 = NULL 或 != NULL 过滤行记录，判断结果全为 UNKNOWN，WHERE 子句只放行 TRUE，因此结果永远为空！
3. 必须使用专用的 IS NULL 与 IS NOT NULL 谓词进行判断！
【必背致命陷阱】：`NOT IN` 列表中只要混入一个 `NULL`，整句查询永远返回 0 行！
-- 陷阱重现：
SELECT * FROM tb_users WHERE id NOT IN (1, 2, NULL);
-- 引擎底层将其展开为：WHERE id != 1 AND id != 2 AND id != NULL
-- 任何值与 NULL 进行 != 比较，结果恒为 UNKNOWN！
-- TRUE AND TRUE AND UNKNOWN 结果为 UNKNOWN，导致整条查询全盘崩塌，返回空集！
-- 避坑黄金法则：使用 NOT IN 时必须排除 NULL，或改用 NOT EXISTS！
---
5.4 排序 (ORDER BY) 与分页 (LIMIT) 及深分页延迟关联优化
-- 1. 多列升降序排列（前列相等时，才按后列比较）
SELECT * FROM tb_products ORDER BY category_id ASC, price DESC;
-- 2. NULL 值在 ORDER BY 中的默认位置：
-- ASC (升序)：NULL 被排在最前面；
-- DESC (降序)：NULL 被排在最后面。
-- 3. 分页语法：LIMIT [偏移量 offset,] 记录条数 count
SELECT * FROM tb_orders LIMIT 0, 10; -- 第 1 页 (跳过 0 条，查 10 条)
SELECT * FROM tb_orders LIMIT 10, 10; -- 第 2 页 (跳过 10 条，查 10 条)
-- 4. 千万级深分页性能调优（延迟关联 Deferred Join）：
-- 慢查询原因：LIMIT 1000000, 10 需要扫描 1000010 行并回表，最后抛弃前 1000000 行，I/O 爆炸！
-- 调优：先在覆盖索引上完成千万级轻量游标定位，再 JOIN 回表取出 10 行完整数据
SELECT t.* FROM tb_orders t
JOIN (
SELECT id FROM tb_orders ORDER BY id LIMIT 1000000, 10
) sub ON t.id = sub.id;
---
5.5 【王牌核心考点 2】MySQL 引擎底层 9 步物理执行顺序生命周期
**【必背核心铁律】**：书写顺序不等于执行顺序！
书写顺序：`SELECT -> DISTINCT -> FROM -> JOIN -> ON -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT`
【MySQL 底层物理执行 9 大阶段】
Step 1: FROM (加载主表与数据源笛卡尔积)
Step 2: ON (根据关联条件筛选连接行)
Step 3: JOIN (保留或补全外连接主表行)
Step 4: WHERE (进行行级数据初筛)
Step 5: GROUP BY (将数据按照指定列聚合折叠分箱)
Step 6: HAVING (对分组折叠后的整组进行聚合指标二次过滤)
Step 7: SELECT (投影提取指定字段，计算表达式与别名生成)
Step 8: DISTINCT (针对投影结果执行哈希去重)
Step 9: ORDER BY (全量数据按规则排序) -> LIMIT (截取指定区间返回客户端)
【两大经典考点解析】：
1. **为什么 `WHERE` 条件中不能使用 `SELECT` 定义的别名？**
- 答：因为 `WHERE`（Step 4）执行时，`SELECT`（Step 7）尚未开始，别名根本没有生成！
2. **为什么 `WHERE` 条件中不能直接写聚合函数（如 `WHERE SUM(price) > 500`）？**
- 答：因为聚合计算发生于 `GROUP BY`（Step 5）阶段，`WHERE` 执行时数据尚未分组，根本没有聚合值！
---
5.6 必须背熟的 MySQL 常用内置函数全集分类速查
1. 字符串函数库
- `CONCAT(s1, s2, ...)`：无缝拼接字符串。
- `CONCAT_WS(sep, s1, s2, ...)`：用指定分隔符连接字符串。
- `SUBSTRING(str, pos, len)`：截取字符串。**注意：SQL 字符下标从 1 开始！**
- `LEFT(str, len)` / `RIGHT(str, len)`：从左/右截取指定长度。
- `LENGTH(str)`（所占字节数） vs `CHAR_LENGTH(str)`（字符个数，中文算 1 个）。
- `LOWER(str)` / `UPPER(str)`：大小写转换。
- `REPLACE(str, from, to)`：字符串查找替换。
- `TRIM(str)` / `LTRIM(str)` / `RTRIM(str)`：去除首尾/左/右空格。
- `LPAD(str, len, pad)` / `RPAD(str, len, pad)`：左/右填充字符至指定长度。
- `INSTR(str, substr)`：返回子串第一次出现的位置（从 1 开始，未找到返回 0）。
2. 数值函数库
- `ROUND(x, d)`：四舍五入到小数点后 `d` 位。
- `TRUNCATE(x, d)`：截断数字保留 d 位小数（不进行四舍五入）。
- `CEIL(x)` / `FLOOR(x)`：向上取整 / 向下取整。
- `MOD(n, m)`：取模运算（求余数）。
- `ABS(x)`：求绝对值。
- `POW(x, y)`：计算 x 的 y 次幂。
- `RAND()`：生成 0 到 1 之间的随机浮点数。
3. 日期时间函数库
- `NOW()`：当前完整日期和时间 `YYYY-MM-DD HH:MM:SS`。
- `CURDATE()` / `CURTIME()`：当前日期 / 当前时间。
- `YEAR(date)` / `MONTH(date)` / `DAY(date)`：提取年、月、日数值。
- `DATE_ADD(date, INTERVAL expr unit)` / `DATE_SUB(...)`：时间加减推移。
- `DATEDIFF(date1, date2)`：计算天数差（`date1 - date2`）。
- `TIMESTAMPDIFF(unit, datetime1, datetime2)`：按指定单位（HOUR, DAY, MONTH）计算时间差（`datetime2 - datetime1`）。
- `DATE_FORMAT(date, '%Y-%m-%d %H:%i:%s')`：格式化时间为指定格式。
- `STR_TO_DATE(str, format)`：将格式化字符串解析为日期时间类型。
4. 条件流程与空值函数
- `IF(expr, v1, v2)`：三元运算符，expr 为真返回 v1，否则返回 v2。
- `IFNULL(val, default_val)`：若 val 为 NULL 则返回 default_val，否则返回 val。
- `NULLIF(expr1, expr2)`：若 expr1 == expr2 则返回 NULL，否则返回 expr1。
- `COALESCE(v1, v2, v3, ...)`：返回参数列表中**第一个非 NULL 的值**。
- `GROUP_CONCAT([DISTINCT] col [ORDER BY ...] [SEPARATOR '...'])`：将多行数据合并为一个字符串（以指定分隔符隔开）。
- `CASE WHEN` 搜索式条件判断（报表分类统计与行转列神器）：
SELECT
product_name,
CASE
WHEN price >= 1000 THEN '高客单'
WHEN price >= 200 THEN '平价款'
ELSE '引流款'
END AS price_level
FROM tb_products;
---
5.7 【必考精练】执行顺序与 DQL 基础检索经典真题
题 5-1（考点：SQL 执行时序与别名可见性）
下列 SQL 语句中，执行时会抛出语法错误的是：
A. `SELECT user_id, balance * 0.9 AS discount_bal FROM tb_users WHERE status = 1 ORDER BY discount_bal DESC;`
B. `SELECT user_id, balance * 0.9 AS discount_bal FROM tb_users WHERE discount_bal > 100;`
C. `SELECT city, COUNT(*) AS cnt FROM tb_users GROUP BY city HAVING cnt > 5;`
D. `SELECT city, AVG(balance) FROM tb_users WHERE status = 1 GROUP BY city;`
**【参考答案】**：B
**【抓分解析】**：`WHERE` 阶段早于 `SELECT` 执行，此时别名 `discount_bal` 尚未生成，在 WHERE 中引用别名必报 `Unknown column` 错误！而在 `ORDER BY`（Step 9）和部分支持的 `HAVING` 中可以使用 SELECT 别名。
---
第 6 章：分组聚合统计与多表关联 (JOIN) 高频考点大对决
6.1 5 大聚合函数忽略 NULL 机制剖析
| 聚合函数 | 语法与职能 | NULL 值特殊处理机制 |
|---|
| `COUNT()` | 统计记录行数或某列非空值个数 | **`COUNT(列名)` 自动跳过 NULL，只统计有值的行！** |
| `SUM()` | 计算指定数值列的总和 | **自动忽略 NULL**。若全列均为 NULL 则返回 NULL |
| `AVG()` | 计算指定数值列的平均值 | **自动忽略 NULL**（分母是实际非空行数，不是总行数！） |
| `MAX()` / `MIN()` | 找出指定列的最大值 / 最小值 | **自动忽略 NULL** |
---
6.2 【王牌核心考点 3】COUNT(*) vs COUNT(1) vs COUNT(列名) 深度辨析
| 对比维度 | `COUNT(*)` | `COUNT(1)` | `COUNT(列名)` |
|---|
| **是否统计 NULL** | **包含 NULL**（统计行数总额） | **包含 NULL**（统计行数总额） | **严格过滤跳过 NULL**！ |
| **语义与规范** | SQL92 标准定义的统计全表行数标准写法 | 每一行映射一个常量 1 并计数 | 统计该字段不为 NULL 的有效记录数 |
| **InnoDB底层优化** | 引擎专门优化，直接找最小二级索引树遍历行指针 | 与 `COUNT(*)` 底层机制完全一致 | 需逐行读取该列数据并解包判断非空 |
| **执行性能对比** | **性能最高，大厂规范推荐写法** | 性能与 `COUNT(*)` 完全等价 | 性能略低于前两者 |
---
6.3 【王牌核心考点 4】WHERE vs HAVING 本质区别对比表
| 对比维度 | `WHERE` 子句 | `HAVING` 子句 |
|---|
| **执行时机** | **在 GROUP BY 分组前执行**（Step 4） | **在 GROUP BY 分组后执行**（Step 6） |
| **作用对象** | 对底层物理表进行**逐行初筛** | 对分组折叠后的**每个聚合组进行组级过滤** |
| **聚合函数支持** | **绝对禁止直接使用聚合函数**（如 `WHERE AVG(amt) > 50` 报错） | **天然支持聚合函数**（如 `HAVING AVG(amt) > 50`） |
| **字段引用限制** | 只能引用表中存在的物理字段 | 可以引用聚合函数、分组列及 SELECT 别名 |
| **执行性能考量** | 先行初筛大幅减少参与分组的行，性能高 | 先全量分组再淘汰整组，开销大于 WHERE |
**【高频经典报错】`ONLY_FULL_GROUP_BY` 规则**：
当 SQL 开启了该模式时，`SELECT` 后出现的字段，必须要么出现在 `GROUP BY` 列表中，要么被聚合函数（`SUM/AVG/MAX/MIN/COUNT`）包裹，绝不允许将细分列裸露投影！
---
6.4 关系代数与 7 种 JOIN 关系图谱及代码实现
1. 内连接 (INNER JOIN): A ∩ B (仅保留两表关联匹配成功的交集数据)
2. 左外连接 (LEFT JOIN): A 全部保留 + B 匹配行 (B 不匹配项自动补填 NULL)
3. 右外连接 (RIGHT JOIN): B 全部保留 + A 匹配行 (A 不匹配项自动补填 NULL)
4. 左反连接 (差集): LEFT JOIN ... WHERE B.key IS NULL (只存在于 A 表、在 B 表没有匹配项的数据)
5. 全外连接 (FULL OUTER JOIN): MySQL 不支持该关键字，通过 LEFT JOIN ... UNION ... RIGHT JOIN 模拟
6. 交叉连接 (CROSS JOIN): A × B 笛卡尔积 (A表 M 行 × B表 N 行 = M*N 行)
-- 1. 内连接 (INNER JOIN)
SELECT u.username, o.order_id, o.total_amount
FROM tb_users u
INNER JOIN tb_orders o ON u.id = o.user_id;
-- 2. 左外连接 (LEFT JOIN) - 保障左表用户全员列出
SELECT u.username, o.order_id, o.total_amount
FROM tb_users u
LEFT JOIN tb_orders o ON u.id = o.user_id;
-- 3. 左反连接 (经典题型：查询注册后“从未下过单”的沉睡用户)
SELECT u.username
FROM tb_users u
LEFT JOIN tb_orders o ON u.id = o.user_id
WHERE o.order_id IS NULL;
---
6.5 【王牌核心考点 5】外连接中 ON 条件 vs WHERE 条件的决定性语义差异
⚠️ **【大厂面试经典压轴题】**：`LEFT JOIN` 中，把过滤条件写在 `ON` 和写在 `WHERE` 里有什么本质区别？
-- 场景 A：条件放在 ON 中
SELECT u.username, o.total_amount
FROM tb_users u
LEFT JOIN tb_orders o ON u.id = o.user_id AND o.total_amount > 500;
-- 场景 B：条件放在 WHERE 中
SELECT u.username, o.total_amount
FROM tb_users u
LEFT JOIN tb_orders o ON u.id = o.user_id
WHERE o.total_amount > 500;
【底层机制剖析】：
- **场景 A（在 `ON` 中）**：
`ON` 是连接建立阶段的匹配条件。即便订单金额不大于 500，**主表 `tb_users` 的所有用户依然会被 100% 强制保留列出**，只是右表订单列显示为 `NULL`！
- **场景 B（在 `WHERE` 中）**：
`WHERE` 是在整个外连接完成后对结果集进行整体行过滤。没有大单的用户对应的订单列为 `NULL`，而在 WHERE 中 `NULL > 500` 判定为 `UNKNOWN` 直接被淘汰，**导致 LEFT JOIN 彻底退化为 INNER JOIN，左表保活机制彻底失效！**
---
6.6 集合运算：UNION vs UNION ALL 性能与去重区别
| 对比维度 | `UNION` | `UNION ALL` |
|---|
| **重复数据处理** | **自动去重**（整行所有列一致的合并为 1 条） | **不去重**，全部无条件直接合并罗列 |
| **内部排序开销** | 引入临时表（Temporary Table）并强制内存/磁盘排序去重，**性能开销大** | 直接向客户端输出数据流，**性能极高** |
| **开发推荐法则** | 仅当业务上明确要求结果去重时使用 | **只要业务允许或结果集天然无交集，优先使用 UNION ALL！** |
---
6.7 【必考精练】聚合与多表连接经典真题
题 6-1（考点：COUNT 聚合与 NULL 过滤）
表 `score_table` 中共有 5 条记录，`score` 列的值分别为：`100, 90, 80, NULL, NULL`。执行语句：
`SELECT COUNT(*), COUNT(score), AVG(score) FROM score_table;`
查询结果依次为：
A. 5, 5, 54
B. 5, 3, 90
C. 3, 3, 90
D. 5, 3, 54
**【参考答案】**：B
**【抓分解析】**：
- `COUNT(*)` 统计物理总行数 = 5；
- `COUNT(score)` 自动跳过 2 个 NULL，统计非空行数 = 3；
- `AVG(score)` 自动跳过 NULL，只以 3 作为分母：`(100 + 90 + 80) / 3 = 90`（绝对不是除以 5！）。
---
第 7 章：高级子查询、CTE 通用表表达式与窗口函数（笔试大题压轴）
7.1 子查询四大形态（标量、列、行、表子查询）与 ANY / ALL / SOME 操作符
| 子查询分类 | 返回结果集维度 | 常见搭配操作符 | 经典示例 |
|---|
| **标量子查询** | 单行单列（一个确定的标量数值） | `=`, `>`, `<`, `!=` | `WHERE price > (SELECT AVG(price) FROM tb_products)` |
| **列子查询** | 多行单列（一个一维数据数组） | `IN`, `NOT IN`, `ANY`, `ALL` | `WHERE category_id IN (SELECT id FROM tb_cat WHERE name='数码')` |
| **行子查询** | 单行多列（一条记录的多个字段） | `=`, `IN` | `WHERE (gender, city) = (SELECT gender, city FROM tb_users WHERE id=1)` |
| **表子查询** | 多行多列（临时派生表） | `FROM` 中作虚拟数据源 | `SELECT * FROM (SELECT id, balance FROM tb_users) AS tmp` (必须起别名) |
**【ANY 与 ALL 操作符辨析】**：
- `> ANY(子查询)`：大于子查询结果中的**任意一个（即大于最小值即可）**；
- `> ALL(子查询)`：大于子查询结果中的**全部数值（即必须大于最大值）**；
- `= ANY(子查询)` 等价于 `IN`。
---
7.2 【王牌核心考点 6】IN vs EXISTS 选型法则与小表驱动大表
- `IN` 逻辑：先执行子查询得到一个内部列表，再拿着外表的每一行去列表中匹配；
- `EXISTS` 逻辑：先遍历外表的每一行，把当前行作为参数代入子查询判断是否能返回至少 1 行数据。
**【必背黄金法则：小表驱动大表】**：
- **当外表大、子查询内表较小时**：优先使用 **`IN`**（子查询只查一次并建立哈希集合）；
- **当外表小、子查询内表极大时**：优先使用 **`EXISTS`**（外表循环次数极少，子查询结合索引秒级命中返回）；
- **若两表规模相仿**：两者性能基本无差异。
---
7.3 CTE 通用表表达式与递归层级查询（WITH 与 WITH RECURSIVE）
1. 普通 CTE (Common Table Expression)
使复杂 SQL 结构模块化，避免层层多重嵌套子查询，提高可读性。
WITH HighValueUsers AS (
SELECT id, username, balance FROM tb_users WHERE balance > 10000
),
RecentOrders AS (
SELECT order_id, user_id, total_amount FROM tb_orders WHERE created_at >= '2025-01-01'
)
SELECT u.username, o.order_id, o.total_amount
FROM HighValueUsers u
JOIN RecentOrders o ON u.id = o.user_id;
2. 递归 CTE 查询分类树（WITH RECURSIVE）
遍历组织架构、商品分类等无限级树形结构：
-- 查询指定分类(id=1)及其所有递归下级子分类
WITH RECURSIVE CategoryPath AS (
-- 初始基础成员 (Anchor Member)
SELECT id, name, parent_id, 1 AS level FROM tb_categories WHERE id = 1
UNION ALL
-- 递归成员 (Recursive Member)
SELECT c.id, c.name, c.parent_id, cp.level + 1
FROM tb_categories c
JOIN CategoryPath cp ON c.parent_id = cp.id
)
SELECT * FROM CategoryPath;
---
7.4 窗口函数基本语法骨架（OVER, PARTITION BY, ORDER BY, ROWS/RANGE 窗口帧）
SELECT
字段1, 字段2,
<窗口函数>() OVER (
[PARTITION BY 分组列]
[ORDER BY 排序列 [ASC | DESC]]
[ROWS BETWEEN <起点> AND <终点>]
) AS 窗口计算结果
FROM 数据表;
窗口帧 (Window Frame) 范围定义：
- `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`：从分组第一行累计计算到当前行；
- `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`：计算当前行及其前一行和后一行（共 3 行移动平均）。
---
7.5 【必考压轴 1】3 大排名函数核心辨析（ROW_NUMBER, RANK, DENSE_RANK）
⚠️ **【全国考试与大厂面试 100% 必考对比矩阵】**：
| 数据成绩 | `ROW_NUMBER()` (行号) | `RANK()` (跳跃并列) | `DENSE_RANK()` (连续并列) |
|---|
| 100 | **1** | **1** | **1** |
| 90 | **2** | **2** | **2** |
| 90 | **3** | **2** | **2** |
| 80 | **4** | **4** (跳过 3) | **3** (紧凑递增) |
| 70 | **5** | **5** | **4** |
- `ROW_NUMBER()`：不管数值是否相等，序号绝对严格自增递增（常用于组内取第一名、唯一去重）；
- `RANK()`：并列同分共享序号，但后续名次会**跳过占位**（如两人并列第 2，下一人为第 4 名）；
- `DENSE_RANK()`：并列同分共享序号，但后续名次**不跳过**（如两人并列第 2，下一人仍为第 3 名）。
---
7.6 跨行位移函数（LEAD, LAG）与极值函数（FIRST_VALUE, LAST_VALUE, NTH_VALUE）
- `LAG(col, n, default_val) OVER(...)`：向上获取当前行**前面第 n 行**的数值（常用于求同比、环比上期数据）；
- `LEAD(col, n, default_val) OVER(...)`：向下获取当前行**后面第 n 行**的数值；
- `FIRST_VALUE(col) OVER(...)`：获取当前窗口帧范围内的第一条记录值；
- `LAST_VALUE(col) OVER(...)`：获取当前窗口帧范围内的最后一条记录值；
- `NTILE(n)`：将整组数据均分为 n 个桶，返回当前行所在的桶号（常用于求前 20% 高净值人群分箱）。
---
7.7 经典大厂 SQL 题型实战模板（TopN、连续签到、次日留存、行转列）
题型 1：分组求各部门薪资前 2 名（TopN 分组排名）
SELECT dept_id, emp_name, salary
FROM (
SELECT
dept_id, emp_name, salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
FROM tb_employees
) tmp
WHERE rk <= 2;
题型 2：连续 3 天及以上登录的用户（经典“日期 - 连续排名 = 恒定基准日”等差法）
SELECT DISTINCT user_id
FROM (
SELECT
user_id, login_date,
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS base_date
FROM (SELECT DISTINCT user_id, login_date FROM tb_user_login) t
) sub
GROUP BY user_id, base_date
HAVING COUNT(*) >= 3;
题型 3：次日留存率计算实战
-- 当日新登用户次日依然登录的比例
SELECT
a.login_date,
COUNT(DISTINCT a.user_id) AS new_users,
COUNT(DISTINCT b.user_id) AS next_day_retention_users,
ROUND(COUNT(DISTINCT b.user_id) / COUNT(DISTINCT a.user_id) * 100, 2) AS retention_rate
FROM (
SELECT user_id, MIN(login_date) AS login_date FROM tb_user_login GROUP BY user_id
) a
LEFT JOIN tb_user_login b
ON a.user_id = b.user_id AND b.login_date = DATE_ADD(a.login_date, INTERVAL 1 DAY)
GROUP BY a.login_date;
题型 4：学生成绩多门科目【行转列】透视
SELECT
student_id,
MAX(CASE WHEN subject = '语文' THEN score ELSE 0 END) AS '语文',
MAX(CASE WHEN subject = '数学' THEN score ELSE 0 END) AS '数学',
MAX(CASE WHEN subject = '英语' THEN score ELSE 0 END) AS '英语'
FROM tb_scores
GROUP BY student_id;
---
7.8 【必考精练】窗口函数与复杂统计真题演练
题 7-1（考点：3 大排名函数输出判读）
对成绩序列 `95, 90, 90, 85` 使用 `DENSE_RANK() OVER(ORDER BY score DESC)` 进行计算，第四名对应的排名值是：
A. 4
B. 3
C. 2
D. 1
**【参考答案】**：B
**【抓分解析】**：第一名 95(1)，第二、三名 90 并列(2)，第四名 85 紧凑不跳号，因此名次为 3。若用 `RANK()` 则为 4。
---
第 8 章：数据库高级对象：视图、存储过程、函数与触发器
8.1 视图 (View)：定义、WITH CHECK OPTION 机制与可更新视图限制
1. 视图的本质与标准创建语法
- **本质**：视图是一张**虚拟表**（自身不存储物理数据，仅在数据字典中保存一段 `SELECT` 查询定义逻辑）。
-- 创建或替换视图
CREATE OR REPLACE VIEW view_active_users AS
SELECT id, username, balance FROM tb_users WHERE status = 1 AND is_deleted = 0;
-- 查询视图如同查询普通表
SELECT * FROM view_active_users WHERE balance > 500;
2. `WITH CHECK OPTION` 防越界更新保护机制
当通过视图执行 `INSERT` 或 `UPDATE` 时，强制写入的数据必须满足该视图自身的 `WHERE` 条件！
CREATE OR REPLACE VIEW view_vip_users AS
SELECT id, username, balance FROM tb_users WHERE balance >= 1000
WITH CASCADED CHECK OPTION;
-- 若执行以下 UPDATE 会直接报错拦截：[Err] 1369 - CHECK OPTION failed 'view_vip_users'
-- UPDATE view_vip_users SET balance = 500 WHERE id = 1;
- `CASCADED`（默认）：级联向上检查所有依赖父视图的条件；
- `LOCAL`：仅检查当前视图自身的条件。
3. 可更新视图（Updatable View）限制条件
如果视图定义中包含以下任意一项，该视图**绝对不可更新**（只能只读，禁止 INSERT/UPDATE/DELETE）：
- 包含聚合函数（`SUM, AVG, MIN, MAX, COUNT`）；
- 包含 `DISTINCT` 关键字；
- 包含 `GROUP BY` 或 `HAVING` 子句；
- 包含 `UNION` 或 `UNION ALL`；
- 包含不可更新的子查询或多表连接更新。
---
8.2 存储过程 (Stored Procedure)：参数类型、变量、流程控制与游标 (Cursor) 实操
DELIMITER $$ -- 临时将语句结束符修改为 $$
CREATE PROCEDURE sp_process_salary(
IN in_dept_id INT, -- 输入参数
OUT out_total_amt DECIMAL(10,2), -- 输出参数
INOUT inout_bonus_rate DECIMAL(4,2) -- 输入输出参数
)
BEGIN
-- 1. 声明局部变量
DECLARE done INT DEFAULT 0;
DECLARE emp_salary DECIMAL(10,2);
-- 2. 声明游标 (必须在变量之后声明)
DECLARE cur_emp CURSOR FOR
SELECT salary FROM tb_employees WHERE dept_id = in_dept_id;
-- 3. 声明异常处理器 (当游标遍历到底无数据时设置 done=1)
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET out_total_amt = 0.00;
-- 4. 打开游标
OPEN cur_emp;
-- 5. 循环遍历游标
read_loop: LOOP
FETCH cur_emp INTO emp_salary;
IF done = 1 THEN
LEAVE read_loop; -- 退出循环
END IF;
-- 计算总工资与奖金
SET out_total_amt = out_total_amt + emp_salary * (1 + inout_bonus_rate);
END LOOP;
-- 6. 关闭游标
CLOSE cur_emp;
-- 调整输出系数
SET inout_bonus_rate = inout_bonus_rate * 1.1;
END $$
DELIMITER ; -- 还原语句结束符为分号
调用存储过程指令：
SET @bonus = 0.10;
CALL sp_process_salary(1, @total, @bonus);
SELECT @total AS 总薪资支出, @bonus AS 递增后奖金系数;
---
8.3 自定义函数 (Stored Function)：语法与存储过程的核心区别
DELIMITER $$
CREATE FUNCTION fn_get_grade(score INT)
RETURNS VARCHAR(10)
DETERMINISTIC -- 声明确定性函数(输入相同输出必相同)
BEGIN
DECLARE res VARCHAR(10);
IF score >= 90 THEN
SET res = '优秀';
ELSEIF score >= 60 THEN
SET res = '及格';
ELSE
SET res = '不及格';
END IF;
RETURN res;
END $$
DELIMITER ;
-- 在 SELECT 中直接调用函数
SELECT username, score, fn_get_grade(score) FROM tb_student_exam;
| 对比维度 | 存储过程 (Procedure) | 自定义函数 (Function) |
|---|
| **返回值** | 没有强制返回值，通过 `OUT/INOUT` 参数传出多个值 | **必须且只能有 1 个明确的 `RETURNS` 返回值** |
| **调用方式** | 必须使用 `CALL 过程名()` 单独调用 | 可以**直接嵌入在 SQL 表达式或 SELECT 语句中**使用 |
| **事务控制** | 允许包含 `COMMIT`、`ROLLBACK` 等事务指令 | **严禁在函数内执行事务提交或回滚** |
---
8.4 触发器 (Trigger)：BEFORE/AFTER INSERT/UPDATE/DELETE 与 NEW/OLD 伪记录
- **触发时机**：`BEFORE`（数据写入前，常用于合法性校验或自动填充）与 `AFTER`（数据写入成功后，常用于写操作审计日志或级联同步）；
- **虚拟行记录辨析**：
* `NEW`：代表即将存入或刚刚更新完成的**新行数据**（仅在 INSERT 和 UPDATE 时可用）；
* `OLD`：代表被修改或被删除前的**旧行原始数据**（仅在 UPDATE 和 DELETE 时可用）。
-- 创建审计触发器：当用户余额被修改时，自动向审计表写入修改前后的数值流水
CREATE TRIGGER trg_after_user_balance_update
AFTER UPDATE ON tb_users
FOR EACH ROW
BEGIN
IF OLD.balance != NEW.balance THEN
INSERT INTO tb_balance_audit_log (user_id, old_balance, new_balance, change_time)
VALUES (OLD.id, OLD.balance, NEW.balance, NOW());
END IF;
END;
---
8.5 【必考精练】数据库对象经典真题
题 8-1（考点：视图可更新性判定）
关于 MySQL 视图 (View) 的描述，下列说法正确的是：
A. 视图在磁盘上存储完整的物理数据表，数据发生变更时定期从基表同步
B. 若视图的 SELECT 语句中包含了 `SUM()` 聚合函数或 `GROUP BY` 子句，则该视图禁止执行 UPDATE 和 INSERT
C. 修改视图的结构会自动级联修改基表的物理结构
D. `WITH CHECK OPTION` 的主要作用是防止基表被 DROP
**【参考答案】**：B
**【抓分解析】**：视图是虚拟逻辑窗口不存物理数据；含有聚合函数、GROUP BY、DISTINCT、UNION 的视图属于不可更新视图。
---
第 9 章：事务 ACID、并发控制、锁机制与 MVCC 底层原理
9.1 事务四大特性 (ACID) 与底层保障日志对照表
| ACID 特性 | 英文全称 | 核心本质 | MySQL 底层主要保障支撑技术 |
|---|
| **A (原子性)** | Atomicity | 事务内全部操作不可分割，要么全成，要么全败回滚 | **Undo Log（回滚日志）** |
| **C (一致性)** | Consistency | 事务前后系统必须处于合法状态，数据完整性不被破坏 | **核心最终追求**（由 A、I、D 三者协同以及业务代码保障） |
| **I (隔离性)** | Isolation | 并发执行的事务互不干扰，相互隔离 | **锁机制 (Locks) + MVCC (多版本并发控制)** |
| **D (持久性)** | Durability | 事务一旦提交，修改永久落盘保存在物理介质 | **Redo Log（重做日志） + WAL 原则** |
**【必背核心机制：什么是 WAL 原则？】**
- **WAL（Write-Ahead Logging - 预写式日志）**：在事务提交前，先将数据变更顺序写入磁盘上的 Redo Log 日志文件，此时内存中真正的数据页（Buffer Pool）可以稍后再异步刷盘（Dirty Page Flush）。
- **收益**：将高开销的随机 I/O 转化为极速的磁盘顺序追加写，并在服务器意外断电重启时，能根据 Redo Log 实现崩溃恢复（Crash-Safe）。
---
9.2 三大并发读异常（脏读、不可重复读、幻读）定义与示例
| 并发异常 | 通俗定义 | 实际业务场景案例 |
|---|
| **脏读** (Dirty Read) | 事务 A 读取到了事务 B **尚未提交的脏数据**；随后事务 B 执行了 `ROLLBACK`，事务 A 读到的数据变为虚假无效数据。 | 银行转账事务 B 暂扣 1000 元未提交，事务 A 查到余额变少；随后事务 B 失败回滚，事务 A 依据脏数据做出错误决策。 |
| **不可重复读** (Non-repeatable Read) | 事务 A 内**读取同一条记录两次，两次读出的内容不一致**（中途被另一个已提交事务 B 执行了 `UPDATE` 或 `DELETE`）。 | 财务事务 A 第一次查某员工工资是 8000；期间人事事务 B 将工资修改为 9000 并提交；事务 A 再次读取变成 9000。 |
| **幻读** (Phantom Read) | 事务 A 内**读取同一条件范围的记录数两次，第二次读出了多出的“新数据行”**（中途被另一个已提交事务 B 执行了 `INSERT`）。 | 事务 A 统计工资>8000 的员工有 5 人；事务 B 插入了一名新员工（工资 9000）并提交；事务 A 再次统计发现变成了 6 人。 |
---
9.3 四大事务隔离级别对比表（RU, RC, RR, Serializable）
**【高频考点】**：MySQL InnoDB 存储引擎的默认隔离级别是 **可重复读 (Repeatable Read)**！
| 隔离级别 | 英文名称 | 脏读 | 不可重复读 | 幻读 | 默认采用引擎 / 性能开销 |
|---|
| **读未提交** | Read Uncommitted | ❌ 发生 | ❌ 发生 | ❌ 发生 | 性能极高，但几乎无企业使用 |
| **读已提交** | Read Committed (RC) | ✅ 解决 | ❌ 发生 | ❌ 发生 | Oracle 默认，互联网大厂高并发推荐 |
| **可重复读** | Repeatable Read (RR) | ✅ 解决 | ✅ 解决 | **✅ 解决** | **MySQL InnoDB 默认级别** (借由 MVCC+间隙锁解决幻读) |
| **串行化** | Serializable | ✅ 解决 | ✅ 解决 | ✅ 解决 | 全表串行加锁，并发吞吐量归零 |
---
9.4 【王牌底层核心 1】MVCC 多版本并发控制底层运行机制（隐藏列、版本链、ReadView 四步法则）
**【面试必杀技】**：MVCC（Multi-Version Concurrency Control）的核心追求是——**读写不冲突，普通 SELECT 读不加任何锁，极大提高系统并发吞吐量！**
1. 行记录三大隐藏字段
每行记录底层均包含 3 个系统预留隐藏列：
- `DB_TRX_ID`（6 字节）：最后一次插入或修改该行记录的**事务 ID**。
- `DB_ROLL_PTR`（7 字节）：**回滚指针**，指向写入 Undo Log 的上一版本历史快照。
- `DB_ROW_ID`（6 字节）：隐藏单调自增行 ID（仅当表未指定主键且无非空唯一索引时自动生成）。
2. Undo Log 版本链
每次更新数据时，旧版本数据被写入 Undo Log 回滚段中，`DB_ROLL_PTR` 回滚指针串联起一条单向链表（从最新快照指针直通最老版本）。
3. 一致性读视图（ReadView）判定算法
当事务执行快照读（普通 SELECT）时，系统为其生成一个 **ReadView**，内部包含 4 大核心参数：
- `m_ids`：生成 ReadView 时，当前系统中**活跃且尚未提交的事务 ID 列表**；
- `min_trx_id`：当前系统中活跃事务中**最小的事务 ID**；
- `max_trx_id`：系统即将分配给下一个新事务的事务 ID（即当前最大事务 ID + 1）；
- `creator_trx_id`：创建该 ReadView 的当前事务自身的事务 ID。
【版本链可见性比对四部曲判定规则】：
设待比对记录的 DB_TRX_ID 为 trx_id：
1. 若 trx_id == creator_trx_id：说明该版本是当前事务自己修改的，【可见】！
2. 若 trx_id < min_trx_id：说明修改该版本的事务在生成 ReadView 之前已经成功提交，【可见】！
3. 若 trx_id >= max_trx_id：说明修改该版本的事务在生成 ReadView 之后才开启，【不可见】，顺版本链往上找旧版！
4. 若 min_trx_id <= trx_id < max_trx_id：
- 若 trx_id 在 m_ids 活跃列表中：说明该事务尚未提交，【不可见】；
- 若 trx_id 不在 m_ids 活跃列表中：说明该事务在 ReadView 创建前已经提交完成，【可见】！
**【RC 与 RR 在 MVCC 上的本质差异】**：
- **RC 读已提交**：事务中**每次执行 SELECT 查询都会重新生成一个新的 ReadView**（因此别的事务提交后，新生成的 ReadView 能读到新内容，产生不可重复读）。
- **RR 可重复读**：事务仅在**第一次执行 SELECT 时生成 ReadView**，后续所有 SELECT 均沿用第一次生成的同一个 ReadView（因此读到的快照永久恒定，彻底杜绝不可重复读与快照读幻读！）。
---
9.5 锁机制全景：共享锁 (S) vs 排他锁 (X)、意向锁 (IS/IX) 与行锁三大算法（记录锁、间隙锁、临键锁）
1. 共享锁 (S) vs 排他锁 (X)
- **共享锁 (S Lock / 读锁)**：允许其他事务并发读取，但阻止其他事务获取 X 锁写入。语法：`SELECT ... LOCK IN SHARE MODE;` (MySQL 8+ 写法: `FOR SHARE`)。
- **排他锁 (X Lock / 写锁)**：排斥其他任何锁。获得 X 锁后唯独当前事务可读写。语法：`SELECT ... FOR UPDATE;` 以及所有的 `UPDATE / DELETE / INSERT`。
2. 意向锁（Intention Locks: IS / IX）
- 意向锁是**表级锁**，由存储引擎自动维护，无需手动声明；
- **作用**：当事务 A 锁定了表中某几行排他锁 (X) 时，引擎会自动在表级别加一个意向排他锁 (IX)；当事务 B 试图对整张表加表锁 (Table Lock) 时，无需逐行扫描全表行锁，只需直接检查表上是否存在 IX 锁即可秒级判定互斥，大幅提升加锁性能！
3. InnoDB 3 大行级锁算法
1. **Record Lock（记录锁）**：直接锁定单个物理索引记录行（如 `WHERE id = 1 FOR UPDATE` 锁住行 id=1）。
2. **Gap Lock（间隙锁）**：锁定一个开区间，**不包含物理记录本身，专门阻断其他事务在该区间内插入新数据**（如锁住 `(5, 10)`）。
3. **Next-Key Lock（临键锁 = 记录锁 + 间隙锁）**：锁定左开右闭区间（如 `(5, 10]`）。**MySQL InnoDB 在 RR 隔离级别下默认采用 Next-Key Lock，成功在写锁层面杜绝了“幻读”现象！**
---
9.6 死锁的产生原因、排查日志 (SHOW ENGINE INNODB STATUS) 与防范策略
1. 死锁产生的经典场景
- 事务 1 锁住记录 A，试图申请记录 B 的锁；
- 事务 2 锁住记录 B，试图申请记录 A 的锁；
- 双方相互等待对方释放资源，形成环路死锁。
2. 死锁排查与防御铁律
-- 查看 InnoDB 引擎内部最近一次死锁详细日志
SHOW ENGINE INNODB STATUS\G;
- **开发防御防死锁四铁律**：
1. **约定固定访问顺序**：所有事务必须按相同的顺序访问表和记录行（如统一先更新 id 小的，再更新 id 大的）；
2. **避免大事务长事务**：事务尽量短小，操作完立刻提交，减少锁持有时间；
3. **必须走索引更新**：WHERE 条件若没有走索引，行锁会直接升级为锁定全表的表锁！
4. **合理设置锁等待超时时间**：`innodb_lock_wait_timeout = 5`（默认 50 秒，高并发建议设为 3~5 秒）。
---
9.7 【必考精练】事务隔离与锁机制考题
题 9-1（考点：四大隔离级别防灾能力）
MySQL 数据库中，当事务隔离级别设置为可重复读 (Repeatable Read) 时，能杜绝的并发异常是：
A. 仅能杜绝脏读
B. 能杜绝脏读和不可重复读，并通过 MVCC + 间隙锁在很大程度上避免幻读
C. 只能杜绝幻读，不能杜绝脏读
D. 无法杜绝任何异常，除非升级到串行化
**【参考答案】**：B
**【抓分解析】**：标准 SQL 规范中 RR 级别不要求防幻读，但 MySQL InnoDB 通过 MVCC（快照读）与 Next-Key Lock（当前读）组合拳，在 RR 级别下全面攻克了幻读问题。
---
第 10 章：MySQL 存储引擎、B+ 树索引底层与 EXPLAIN 调优全解析
10.1 存储引擎对比：InnoDB vs MyISAM 核心特性矩阵
| 对比维度 | **InnoDB 存储引擎** | **MyISAM 存储引擎** |
|---|
| **事务支持** | **支持完整 ACID 事务** | **完全不支持事务** |
| **锁粒度** | **行级锁 (Row-level Lock)** + 表级锁 | **仅支持表级锁 (Table-level Lock)** |
| **外键支持** | **支持物理外键约束** | **不支持外键** |
| **崩溃自动恢复** | **支持**（基于 Redo Log 崩溃自愈 Crash-Safe） | **不支持**（掉电极易导致索引损坏） |
| **数据与索引物理存储** | `.ibd`（表空间文件，数据与索引合一聚簇存储） | `.sdi` (结构), `.MYD` (数据), `.MYI` (索引分离) |
| **默认存储引擎** | **MySQL 5.5+ 默认全局引擎** | MySQL 5.1 以前的旧默认引擎 |
---
10.2 索引数据结构选型推演：为什么是 B+ 树？（树高计算与扇出比数学推导）
**【必背核心：为什么不用 Hash、二叉平衡树、B 树？】**
1. **为什么不用 Hash 表？**
Hash 表只支持等值精准匹配 `O(1)`，**根本不支持范围查询 (`>`, `<`, `BETWEEN`)、模糊匹配和排序 (`ORDER BY`)**。
2. **为什么不用二叉搜索树 / AVL / 红黑树？**
二叉树每个节点最多只有 2 个孩子节点。面对千万级数据时，**树高度高达 20~30 层**，定位一条数据需做 20~30 次高开销磁盘 I/O，极慢！
3. **为什么选 B+ 树而不选 B 树（B-Tree）？**
- **B 树**：每一个节点（无论根、枝、叶）都存放完整的业务数据行。导致单个 16KB 数据页容纳的索引键极少，**扇出比低，树高度增加，I/O 次数增多**；且范围查询需跨层做中序回溯遍历。
- **B+ 树（天选之子）**：
* **非叶子节点只存“键值 + 指针”，不存真实数据** $\rightarrow$ 一个 16KB 页可存上千个键，**扇出比极高，树高仅 2~4 层**（千万级数据仅需 2~3 次 I/O）！
* **所有真实数据强制保存在底层叶子节点**；
* **叶子节点之间由双向链表相连** $\rightarrow$ 范围查询只要在叶子链表上顺序扫描即可，无需跨层回溯！
【经典面试数学推导：3 层 B+ 树能存多少数据？】
- InnoDB 数据页默认大小为 `16 KB = 16384 字节`；
- 设主键为 `BIGINT`（8 字节），指针占用 `6 字节`，非叶子节点一个索引项占用 `14 字节`。
- 则一个非叶子节点页可容纳索引项：`16384 / 14 ≈ 1170` 个；
- 设叶子节点单条数据行大小为 `1 KB`，则一个叶子节点页可存储 `16` 条行记录。
- **3 层 B+ 树最大容纳记录数** = `1170 (根节点) × 1170 (枝节点) × 16 (叶子节点) ≈ 2190 万条数据！`
- **结论：只需 3 次磁盘 I/O，即可在 2000 多万条数据中精准定位目标行！**
---
10.3 聚簇索引 vs 二级索引、回表查询与覆盖索引优化
[聚簇索引 (主键 B+ 树)] [二级索引 idx_name (普通非主键 B+ 树)]
[Root Page] [Root Page]
/ \ / \
[Leaf Page] [Leaf Page] [Leaf Page] [Leaf Page]
[Key:1, Data: 张三完整整行数据] [Key:'张三', Data: 主键 ID=1]
│ (若需要查年龄，需拿着 ID=1 回头查主键树)
▼
【回表查询 (Back to Table)】
- **聚簇索引（Clustered Index）**：叶子节点存放**完整的整行数据记录**。一张表有且仅有 1 个聚簇索引（主键）。
- **二级索引 / 非聚簇索引（Secondary Index）**：叶子节点存放的是**主键 ID 的值**。
- **回表查询**：先在二级索引树查出主键 ID，再拿着 ID 去主键聚簇索引树查出整行完整数据的过程（增加了 1 次 B+ 树 I/O 检索）。
- **覆盖索引（Covering Index - 神级调优）**：如果查询所需要的字段在二级索引树上已经全额存在（例如：`SELECT id, name FROM users WHERE name = '张三'`），**引擎直接从二级索引叶子节点提取返回，无需回表！EXPLAIN 会显示 `Using index`！**
---
10.4 联合索引最左前缀匹配法则（Leftmost Prefix Rule）与索引下推 (ICP)
1. 最左前缀原则
对于联合索引 `KEY idx_a_b_c (a, b, c)`：
- 索引在物理底层是先按 `a` 严格排序；只有在 `a` 相等时才按 `b` 排序；在 `b` 相等时才按 `c` 排序。
- **匹配规则**：查询必须从最左列 `a` 开始匹配！
- `WHERE a=1` $\rightarrow$ 走索引（走 a）
- `WHERE a=1 AND b=2` $\rightarrow$ 走索引（走 a, b）
- `WHERE a=1 AND b=2 AND c=3` $\rightarrow$ 走索引（走 a, b, c）
- `WHERE a=1 AND c=3` $\rightarrow$ 走索引（只走 a，c 无法命中）
- `WHERE b=2 AND c=3` $\rightarrow$ **完全不走索引（违反最左匹配，全表扫描！）**
- **范围查询截断法则**：遇到范围条件（`>`, `<`, `BETWEEN`），后续字段索引中断！如 `WHERE a=1 AND b > 2 AND c=3`，只有 `a` 和 `b` 走索引，`c` 无法走索引。
2. 索引下推 (Index Condition Pushdown - ICP)
- MySQL 5.6+ 默认开启的神器；
- **原理**：在联合索引遍历时，先在存储引擎层依据索引包含的字段做过滤，只有过滤通过的行才回表，大幅减少回表次数！EXPLAIN 会显示 `Using index condition`。
---
10.5 【必背考点 1】索引失效 7 大经典场景与防雷口诀
⚠️ **【笔试面试 100% 高频必考题】**：
【索引失效七字避坑口诀】
1. 模型做运算：索引字段参与数学运算或函数调用（WHERE SUBSTRING(name,1,3)='abc', WHERE id+1=10）
2. 类型隐式换：字符列查询未加单引号，触发底层隐式类型转换（WHERE phone = 13800138000）
3. 前导模糊窜：LIKE 模糊匹配以 % 开头（WHERE name LIKE '%三'，但 '张%' 可走索引）
4. 违背最左端：联合索引跳过最左引导列
5. OR 两侧不全单：OR 连接的条件中只要有一侧字段未建立索引，全盘退化为全表扫描
6. 范围之后断：联合索引中范围查询（>、<）导致其右侧的后续列索引失效
7. 负向不肯转：!=、<>、NOT IN、NOT EXISTS 很难利用索引
---
10.6 【必背考点 2】EXPLAIN 执行计划关键字段深度判读（type, key_len, Extra）
1. `type` 访问类型性能从优到劣排行榜（必背！）
【性能最优】 system > const > eq_ref > ref > range > index > ALL 【性能最差】
- `const`：通过主键或唯一索引等值查询，瞬间定位 1 行；
- `eq_ref`：多表关联中，连接条件引用被驱动表的主键或唯一索引；
- `ref`：使用普通二级非唯一索引进行等值查询；
- `range`：使用索引进行**范围查询**（`BETWEEN`, `>`, `<`, `IN`）；
- `index`：**全索引扫描**（虽然扫描了全表行，但遍历的是体积更小的二级索引树，无需读物理数据页）；
- `ALL`：**全表物理扫描（Performance Killer）**，必须重点优化！
2. `key_len` 索引长度精确计算公式
用于推导联合索引中究竟命中了前几个字段：
- `INT`：4 字节（若为 NOT NULL 占 4，允许 NULL 额外占 1 字节）
- `BIGINT`：8 字节
- `CHAR(N)`：`N * 字符集单字符最大字节 + (1 字节 if NULL)`
- `VARCHAR(N)`：`N * 字符集单字符最大字节 + 2字节长度前缀 + (1 字节 if NULL)`
* 在 `utf8mb4` 字符集下：`VARCHAR(20) NOT NULL` 的 key_len = `20 * 4 + 2 = 82 字节`。
3. `Extra` 核心性能标记判读
- `Using index`（**极优**）：使用了**覆盖索引**，所需字段在索引树上全覆盖，完全不回表！
- `Using where`（**正常**）：引擎层过滤后，Server 层继续依据条件做行筛选。
- `Using index condition`（**优**）：开启了 **ICP（索引下推）**，在引擎层遍历索引时直接过滤，大幅减少回表次数。
- `Using filesort`（**极差**）：无法利用索引完成排序，必须在内存或临时磁盘进行文件排序，必须优化索引！
- `Using temporary`（**极差**）：使用了临时表（常见于 GROUP BY 或 DISTINCT 未走索引），开销巨大！
---
10.7 慢查询日志 (Slow Query Log) 与分析工具 mysqldumpslow 常用命令
-- 1. 查看慢查询日志开启状态与耗时阈值 (默认10秒)
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
-- 2. 临时开启慢查询日志并将阈值设置为 1 秒
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
# 3. 使用系统自带 mysqldumpslow 分析日志
# 按照查询耗时降序排列前 10 条慢 SQL
mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log
# 按照锁定时间降序排列前 10 条 SQL
mysqldumpslow -s l -t 10 /var/lib/mysql/mysql-slow.log
---
10.8 【必考精练】索引调优与慢 SQL 分析真题演练
题 10-1（考点：联合索引最左匹配与失效）
在数据表 `user_table` 上建立了联合索引 `idx_a_b_c (a, b, c)`。下列查询语句中，能完全命中全部 3 个索引字段的是：
A. `SELECT * FROM user_table WHERE a = 1 AND c = 3;`
B. `SELECT * FROM user_table WHERE b = 2 AND c = 3;`
C. `SELECT * FROM user_table WHERE a = 1 AND b > 2 AND c = 3;`
D. `SELECT * FROM user_table WHERE a = 1 AND b = 2 AND c = 3;`
**【参考答案】**：D
**【抓分解析】**：
- A 缺少 b 列，只有 a 走索引，c 无法命中；
- B 缺失最左前缀 a，完全不走索引（ALL 全表扫描）；
- C 中 b 进行了范围查询 `b > 2`，导致后续 c 字段索引失效（仅 a, b 走索引）；
- 只有 D 三个字段均为等值匹配，完整利用 3 个索引列。
---