SQL 后端面试知识点梳理（中高级）

面向 Java/Go 后端 3-5 年社招，默认数据库为 MySQL 8.0 + InnoDB。

---

〇、SQL 语句书写大全

按 SQL 的功能分类：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）、TCL（事务）。

0.1 DDL（数据定义语言）

0.1.1 数据库

-- 创建
CREATE DATABASE db_name
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_0900_ai_ci;

CREATE DATABASE IF NOT EXISTS db_name;

-- 查看
SHOW DATABASES;
SHOW CREATE DATABASE db_name;

-- 选择
USE db_name;

-- 修改
ALTER DATABASE db_name CHARACTER SET utf8mb4;

-- 删除
DROP DATABASE db_name;
DROP DATABASE IF EXISTS db_name;

0.1.2 表

-- 完整建表
CREATE TABLE `user` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    `name`        VARCHAR(64)     NOT NULL DEFAULT '' COMMENT '姓名',
    `age`         TINYINT UNSIGNED         DEFAULT NULL,
    `email`       VARCHAR(128)             DEFAULT NULL,
    `status`      TINYINT         NOT NULL DEFAULT 1 COMMENT '1有效 0无效',
    `balance`     DECIMAL(18,2)   NOT NULL DEFAULT 0.00,
    `create_time` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_email` (`email`),
    KEY `idx_name_status` (`name`, `status`),
    KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 复制表结构
CREATE TABLE t2 LIKE t1;                 -- 仅结构（含索引）
CREATE TABLE t2 AS SELECT * FROM t1;     -- 结构 + 数据，不含索引/主键
CREATE TABLE t2 AS SELECT * FROM t1 WHERE 1=0;  -- 仅结构，不含索引

-- 临时表（会话级，连接断开自动消失）
CREATE TEMPORARY TABLE tmp_t (...);

-- 查看
SHOW TABLES;
SHOW CREATE TABLE t;
DESC t;                  -- 等价 DESCRIBE t / SHOW COLUMNS FROM t
SHOW TABLE STATUS LIKE 't';

-- 重命名
RENAME TABLE t1 TO t2;
ALTER TABLE t1 RENAME TO t2;

-- 删除
DROP TABLE t;
DROP TABLE IF EXISTS t1, t2;

-- 清空（重置自增、不可回滚、比 DELETE 快）
TRUNCATE TABLE t;

0.1.3 ALTER TABLE 完整操作

-- 列操作
ALTER TABLE t ADD COLUMN age INT NOT NULL DEFAULT 0 AFTER name;
ALTER TABLE t ADD COLUMN age INT FIRST;
ALTER TABLE t DROP COLUMN age;
ALTER TABLE t MODIFY COLUMN age BIGINT NOT NULL;             -- 改类型/属性
ALTER TABLE t CHANGE COLUMN age user_age BIGINT NOT NULL;    -- 改名+类型
ALTER TABLE t RENAME COLUMN age TO user_age;                 -- 8.0+ 仅改名
ALTER TABLE t ALTER COLUMN status SET DEFAULT 1;
ALTER TABLE t ALTER COLUMN status DROP DEFAULT;

-- 主键/索引
ALTER TABLE t ADD PRIMARY KEY (id);
ALTER TABLE t DROP PRIMARY KEY;
ALTER TABLE t ADD UNIQUE KEY uk_email (email);
ALTER TABLE t ADD INDEX idx_name (name);
ALTER TABLE t ADD INDEX idx_name_status (name, status);
ALTER TABLE t ADD INDEX idx_name_prefix (name(10));          -- 前缀索引
ALTER TABLE t ADD FULLTEXT INDEX ft_content (content);       -- 全文索引
ALTER TABLE t DROP INDEX idx_name;
ALTER TABLE t RENAME INDEX idx_old TO idx_new;

-- 外键
ALTER TABLE order_item
    ADD CONSTRAINT fk_order
    FOREIGN KEY (order_id) REFERENCES `order`(id)
    ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE order_item DROP FOREIGN KEY fk_order;

-- 表选项
ALTER TABLE t ENGINE=InnoDB;
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE t COMMENT '新注释';
ALTER TABLE t AUTO_INCREMENT = 10000;

-- Online DDL 算法（8.0）
ALTER TABLE t ADD INDEX idx_x (x), ALGORITHM=INPLACE, LOCK=NONE;

0.1.4 索引

-- 创建（CREATE INDEX 与 ALTER TABLE ADD INDEX 等价）
CREATE INDEX idx_name ON t(name);
CREATE UNIQUE INDEX uk_email ON t(email);
CREATE INDEX idx_n_s ON t(name, status);                    -- 联合索引
CREATE INDEX idx_lower_name ON t((LOWER(name)));            -- 函数索引(8.0+)
CREATE INDEX idx_name_desc ON t(name DESC);                 -- 降序索引(8.0+)

-- 不可见索引（8.0+，排查索引是否还需要）
ALTER TABLE t ALTER INDEX idx_name INVISIBLE;
ALTER TABLE t ALTER INDEX idx_name VISIBLE;

-- 查看
SHOW INDEX FROM t;

-- 删除
DROP INDEX idx_name ON t;

0.1.5 视图

CREATE VIEW v_active_user AS
SELECT id, name, email FROM user WHERE status = 1;

CREATE OR REPLACE VIEW v_active_user AS
SELECT id, name FROM user WHERE status = 1;

DROP VIEW v_active_user;

0.2 DML（数据操作语言）

0.2.1 INSERT 全形式

-- 标准插入
INSERT INTO t (a, b, c) VALUES (1, 'x', NOW());

-- 批量插入（一次网络往返，远快于单条循环）
INSERT INTO t (a, b) VALUES (1,'x'), (2,'y'), (3,'z');

-- 插入所有列（不写列名，必须按表定义顺序，不推荐）
INSERT INTO t VALUES (1, 'x', NOW());

-- 从查询结果插入
INSERT INTO t_bak (a, b) SELECT a, b FROM t WHERE create_time < '2024-01-01';

-- 忽略冲突（主键/唯一键冲突不报错，跳过）
INSERT IGNORE INTO t (id, a) VALUES (1, 'x');

-- 冲突时更新（UPSERT）
INSERT INTO t (id, a, b) VALUES (1, 'x', 'y')
ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b);
-- 8.0.20+ 推荐别名形式，VALUES() 已废弃
INSERT INTO t (id, a, b) VALUES (1, 'x', 'y') AS new
ON DUPLICATE KEY UPDATE a = new.a, b = new.b;

-- 替换（先删后插，会触发自增、外键级联）
REPLACE INTO t (id, a) VALUES (1, 'x');

-- 获取自增 ID
SELECT 3309213;

0.2.2 UPDATE 全形式

-- 单表
UPDATE t SET a = 1, b = b + 1 WHERE id = 10;

-- 不带 WHERE 会全表更新（生产事故经典）
SET sql_safe_updates = 1;   -- 开启安全模式可阻止

-- 排序+限量更新
UPDATE t SET status = 0 ORDER BY create_time ASC LIMIT 100;

-- 多表 JOIN 更新
UPDATE order o
JOIN user u ON o.user_id = u.id
SET o.user_name = u.name
WHERE u.status = 1;

-- 用子查询的值更新
UPDATE t SET cnt = (SELECT COUNT(*) FROM sub WHERE sub.t_id = t.id);

-- CASE WHEN 批量按条件更新
UPDATE t SET level = CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 60 THEN 'B'
    ELSE 'C'
END;

0.2.3 DELETE 全形式

-- 单表
DELETE FROM t WHERE id = 1;

-- 排序+限量删除（清理历史数据时常用，分批避免大事务）
DELETE FROM t WHERE create_time < '2024-01-01' ORDER BY id LIMIT 1000;

-- 多表 JOIN 删除（指明删哪张表）
DELETE o FROM `order` o
JOIN user u ON o.user_id = u.id
WHERE u.status = 0;

-- 同时删多张表
DELETE o, oi FROM `order` o
JOIN order_item oi ON oi.order_id = o.id
WHERE o.status = -1;

-- 三种"清空"对比
DELETE FROM t;          -- DML，可回滚，逐行删，触发触发器，自增不重置
TRUNCATE TABLE t;       -- DDL，不可回滚，整表重建，自增重置
DROP TABLE t;           -- 表都没了

0.3 DQL（数据查询语言）

0.3.1 SELECT 完整骨架

SELECT [DISTINCT] <列 / 表达式 / 聚合函数 / 窗口函数>
FROM <表> [AS 别名]
    [INNER | LEFT | RIGHT | CROSS] JOIN <表2> ON <条件>
WHERE <过滤条件>
GROUP BY <分组列> [WITH ROLLUP]
HAVING <分组过滤>
ORDER BY <排序列> [ASC | DESC]
LIMIT [offset,] count
[FOR UPDATE | FOR SHARE | LOCK IN SHARE MODE];

0.3.2 WHERE 所有运算符

-- 比较
WHERE a = 1
WHERE a <> 1        -- 等价 !=
WHERE a > 1 AND a <= 10
WHERE a <=> NULL    -- 空安全等于，NULL <=> NULL 为 TRUE

-- 范围
WHERE a BETWEEN 1 AND 10              -- 闭区间
WHERE a NOT BETWEEN 1 AND 10

-- 集合
WHERE a IN (1, 2, 3)
WHERE a NOT IN (1, 2, 3)              -- 注意 NULL 陷阱
WHERE a IN (SELECT id FROM b)

-- NULL
WHERE a IS NULL
WHERE a IS NOT NULL

-- 模糊
WHERE name LIKE '张%'                  -- % 任意多字符
WHERE name LIKE '_明'                  -- _ 单字符
WHERE name LIKE '50\%' ESCAPE '\\'     -- 转义
WHERE name NOT LIKE '%test%'
WHERE name REGEXP '^[0-9]+$'           -- 正则

-- 逻辑
WHERE a = 1 AND b = 2
WHERE a = 1 OR b = 2
WHERE NOT (a = 1)

-- EXISTS（关联子查询，常用于"存在性"判断）
WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)
WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)

-- ANY / ALL / SOME
WHERE a > ANY (SELECT x FROM b)        -- > 任一个，即 > 最小值
WHERE a > ALL (SELECT x FROM b)        -- > 所有，即 > 最大值

0.3.3 JOIN 七种形态

-- 1. 内连接：交集
SELECT * FROM a INNER JOIN b ON a.id = b.a_id;

-- 2. 左连接：A 全部 + 匹配 B
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;

-- 3. 左连接 - 内连接：A 独有
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.a_id IS NULL;

-- 4. 右连接：B 全部 + 匹配 A
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;

-- 5. 右连接 - 内连接：B 独有
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id WHERE a.id IS NULL;

-- 6. 全外连接：MySQL 用 UNION 模拟
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;

-- 7. 全外连接 - 内连接：仅独有
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.a_id IS NULL
UNION
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id WHERE a.id IS NULL;

-- 笛卡尔积
SELECT * FROM a CROSS JOIN b;
SELECT * FROM a, b;                    -- 老语法

-- 自连接：查每个员工的上级姓名
SELECT e.name, m.name AS manager
FROM emp e LEFT JOIN emp m ON e.manager_id = m.id;

-- USING 简写：列名相同时
SELECT * FROM a JOIN b USING (user_id);   -- 等价 ON a.user_id = b.user_id

-- 自然连接：按所有同名列连接（不推荐，隐式行为不可控）
SELECT * FROM a NATURAL JOIN b;

0.3.4 GROUP BY 与聚合

-- 基础分组
SELECT dept_id, COUNT(*), AVG(salary), SUM(salary), MAX(salary), MIN(salary)
FROM emp
GROUP BY dept_id;

-- 多列分组
SELECT dept_id, job, COUNT(*) FROM emp GROUP BY dept_id, job;

-- HAVING 过滤分组结果
SELECT dept_id, COUNT(*) AS cnt FROM emp GROUP BY dept_id HAVING cnt > 5;

-- WITH ROLLUP 增加合计行
SELECT dept_id, job, SUM(salary) FROM emp GROUP BY dept_id, job WITH ROLLUP;

-- DISTINCT 去重
SELECT DISTINCT dept_id FROM emp;
SELECT COUNT(DISTINCT dept_id) FROM emp;

-- GROUP_CONCAT 分组拼接
SELECT dept_id, GROUP_CONCAT(name ORDER BY id SEPARATOR ',') FROM emp GROUP BY dept_id;

-- ONLY_FULL_GROUP_BY（5.7+ 默认开启）
-- SELECT 中非聚合字段必须出现在 GROUP BY 中

聚合函数完整列表：COUNT / SUM / AVG / MAX / MIN / GROUP_CONCAT / JSON_ARRAYAGG / JSON_OBJECTAGG / STD / VARIANCE / BIT_AND / BIT_OR / BIT_XOR。

0.3.5 ORDER BY 与 LIMIT

-- 排序
ORDER BY a ASC                         -- 升序，默认
ORDER BY a DESC
ORDER BY a, b DESC                     -- 多列：a 升，b 降
ORDER BY FIELD(status, 1, 0, -1)       -- 自定义顺序
ORDER BY RAND()                        -- 随机（数据量大时性能极差）

-- NULL 的位置：MySQL 中 NULL 视为最小值
ORDER BY a IS NULL, a;                 -- 让 NULL 排最后

-- 分页
LIMIT 10                               -- 取前 10 条
LIMIT 20, 10                           -- 跳过 20 取 10
LIMIT 10 OFFSET 20                     -- 标准 SQL 写法

-- 深分页优化
SELECT * FROM t WHERE id > 1000000 ORDER BY id LIMIT 10;     -- 游标式
SELECT * FROM t INNER JOIN (
    SELECT id FROM t ORDER BY id LIMIT 1000000, 10
) x USING (id);                                              -- 延迟关联

0.3.6 子查询全形式

-- 标量子查询：返回单值
SELECT name, (SELECT AVG(salary) FROM emp) AS avg_sal FROM emp;

-- 列子查询：返回一列多行，配合 IN/ANY/ALL
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE region='SH');

-- 行子查询：返回一行多列
SELECT * FROM emp WHERE (dept_id, job) = (SELECT dept_id, job FROM emp WHERE id=1);

-- 表子查询（派生表）：必须有别名
SELECT * FROM (SELECT dept_id, COUNT(*) c FROM emp GROUP BY dept_id) t WHERE c > 5;

-- 关联子查询：内层引用外层
SELECT * FROM emp e
WHERE salary > (SELECT AVG(salary) FROM emp WHERE dept_id = e.dept_id);

0.3.7 集合运算

-- UNION：去重合并
SELECT a FROM t1 UNION SELECT a FROM t2;

-- UNION ALL：不去重，性能更好
SELECT a FROM t1 UNION ALL SELECT a FROM t2;

-- INTERSECT 交集 / EXCEPT 差集（8.0.31+ 才支持）
SELECT a FROM t1 INTERSECT SELECT a FROM t2;
SELECT a FROM t1 EXCEPT SELECT a FROM t2;

-- 老版本用 IN/NOT IN 或 JOIN 模拟

要求：每个 SELECT 列数相同、对应位置类型兼容；ORDER BY/LIMIT 只能放在最后整体使用。

0.3.8 CTE（WITH 子句，8.0+）

-- 普通 CTE
WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_sal FROM emp GROUP BY dept_id
)
SELECT e.* FROM emp e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;

-- 多个 CTE
WITH a AS (...), b AS (...)
SELECT * FROM a JOIN b ON ...;

-- 递归 CTE：处理树形/层级
WITH RECURSIVE cte AS (
    SELECT id, parent_id, name, 1 AS lvl FROM dept WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.parent_id, d.name, c.lvl + 1
    FROM dept d JOIN cte c ON d.parent_id = c.id
)
SELECT * FROM cte;

-- 数列生成
WITH RECURSIVE nums AS (
    SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 100
)
SELECT * FROM nums;

0.3.9 窗口函数（8.0+）

SELECT
    user_id,
    order_time,
    amount,
    -- 排名
    ROW_NUMBER() OVER w  AS rn,        -- 1,2,3,4
    RANK()       OVER w  AS rk,        -- 1,2,2,4
    DENSE_RANK() OVER w  AS drk,       -- 1,2,2,3
    PERCENT_RANK() OVER w,
    NTILE(4)     OVER w,               -- 分桶
    -- 偏移
    LAG(amount, 1)  OVER w AS prev_amt,
    LEAD(amount, 1) OVER w AS next_amt,
    FIRST_VALUE(amount) OVER w,
    LAST_VALUE(amount)  OVER w,
    NTH_VALUE(amount, 2) OVER w,
    -- 聚合作为窗口
    SUM(amount) OVER w               AS running_sum,
    AVG(amount) OVER (PARTITION BY user_id) AS user_avg
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY order_time);

-- 帧（frame）控制
SUM(amt) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  -- 7日滑动
SUM(amt) OVER (ORDER BY dt RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

0.3.10 加锁读

SELECT ... FOR UPDATE;                         -- 排他锁
SELECT ... FOR SHARE;                          -- 共享锁（8.0+，老语法 LOCK IN SHARE MODE）
SELECT ... FOR UPDATE NOWAIT;                  -- 8.0+：拿不到锁立即报错
SELECT ... FOR UPDATE SKIP LOCKED;             -- 8.0+：跳过被锁行（队列消费场景）
SELECT ... FOR UPDATE OF t1;                   -- 多表中只锁指定表

0.4 常用函数速查

0.4.1 字符串函数

LENGTH('abc')           -- 字节长度（utf8mb4 一个汉字 3-4 字节）
CHAR_LENGTH('中文')      -- 字符长度
CONCAT('a','b','c')
CONCAT_WS(',', 'a','b','c')   -- 用分隔符
UPPER(s) / LOWER(s)
TRIM(s) / LTRIM / RTRIM
TRIM(BOTH 'x' FROM 'xxabcxx')
SUBSTRING(s, 1, 3)      -- 从 1 开始
LEFT(s, 3) / RIGHT(s, 3)
LOCATE('b', 'abc')      -- 等价 INSTR
REPLACE(s, 'a', 'b')
REPEAT('ab', 3)
REVERSE(s)
LPAD(s, 10, '0')        -- 左填充
FORMAT(12345.678, 2)    -- 千分位 '12,345.68'

0.4.2 数字函数

ABS(-1)
CEIL(1.2) / FLOOR(1.8) / ROUND(1.5) / ROUND(1.234, 2)
TRUNCATE(1.999, 2)      -- 截断不四舍五入
MOD(10, 3) / 10 % 3
POWER(2, 10) / SQRT(9)
RAND()                  -- [0,1)
SIGN(-5)                -- -1,0,1
GREATEST(1,5,3) / LEAST(1,5,3)

0.4.3 日期时间函数

NOW() / CURRENT_TIMESTAMP        -- 当前日期时间
CURDATE() / CURTIME()
UNIX_TIMESTAMP() / FROM_UNIXTIME(1700000000)
YEAR(dt) / MONTH / DAY / HOUR / MINUTE / SECOND
DAYOFWEEK(dt) / WEEK(dt) / QUARTER(dt)
DATE(dt) / TIME(dt)
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')
STR_TO_DATE('2025-01-01', '%Y-%m-%d')
DATE_ADD(NOW(), INTERVAL 1 DAY)
DATE_SUB(NOW(), INTERVAL 1 MONTH)
DATEDIFF('2025-01-10', '2025-01-01')           -- 天数差
TIMESTAMPDIFF(HOUR, t1, t2)                    -- 任意单位差
LAST_DAY(NOW())                                -- 当月最后一天

0.4.4 流程控制

-- IF
IF(a > 0, 'P', 'N')
IFNULL(a, 0)                            -- a 为 NULL 返回 0
NULLIF(a, b)                            -- a=b 返回 NULL，否则 a
COALESCE(a, b, c, 0)                    -- 返回首个非 NULL

-- CASE 两种形式
CASE WHEN score >= 90 THEN 'A' WHEN score >= 60 THEN 'B' ELSE 'C' END
CASE status WHEN 1 THEN '有效' WHEN 0 THEN '无效' ELSE '未知' END

0.4.5 类型转换

CAST('123' AS SIGNED)
CAST('1.23' AS DECIMAL(10,2))
CAST(NOW() AS DATE)
CONVERT('123', UNSIGNED)
CONVERT(s USING utf8mb4)               -- 字符集转换

0.4.6 JSON 函数（5.7+）

JSON_OBJECT('a', 1, 'b', 2)
JSON_ARRAY(1, 2, 3)
JSON_EXTRACT(doc, '$.a.b')             -- 等价 doc->'$.a.b'
doc->>'$.a'                            -- 解引用并去引号
JSON_SET(doc, '$.a', 1)
JSON_INSERT / JSON_REPLACE / JSON_REMOVE
JSON_CONTAINS(doc, '1', '$.list')
JSON_LENGTH(doc, '$.list')
JSON_KEYS(doc)
-- 配合函数索引：
ALTER TABLE t ADD INDEX idx_a ((CAST(doc->>'$.a' AS CHAR(64))));

0.5 DCL（数据控制语言）

-- 用户
CREATE USER 'u'@'%' IDENTIFIED BY 'pwd';
ALTER USER 'u'@'%' IDENTIFIED BY 'newpwd';
RENAME USER 'u'@'%' TO 'u2'@'%';
DROP USER 'u'@'%';

-- 授权
GRANT SELECT, INSERT, UPDATE ON db.* TO 'u'@'%';
GRANT ALL PRIVILEGES ON db.t TO 'u'@'%';
GRANT SELECT ON *.* TO 'u'@'%';
GRANT SELECT (col1, col2) ON db.t TO 'u'@'%';     -- 列级
GRANT PROXY ON 'src'@'%' TO 'dst'@'%';

-- 撤销
REVOKE INSERT ON db.* FROM 'u'@'%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'u'@'%';

-- 刷新（修改权限表后必要）
FLUSH PRIVILEGES;

-- 查看权限
SHOW GRANTS FOR 'u'@'%';
SHOW GRANTS FOR CURRENT_USER();

-- 角色（8.0+）
CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON db.* TO 'app_read';
GRANT 'app_read' TO 'u'@'%';
SET DEFAULT ROLE 'app_read' TO 'u'@'%';

0.6 TCL（事务控制）

-- 开启
START TRANSACTION;
BEGIN;                                  -- 等价
START TRANSACTION READ ONLY;            -- 只读事务，可优化
START TRANSACTION WITH CONSISTENT SNAPSHOT;   -- 立即建立快照

-- 自动提交
SET autocommit = 0;                     -- 关闭后必须显式 COMMIT
SET autocommit = 1;

-- 提交 / 回滚
COMMIT;
ROLLBACK;

-- 保存点
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;

-- 隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL  TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@transaction_isolation;

-- XA 分布式事务
XA START 'xid';
XA END   'xid';
XA PREPARE 'xid';
XA COMMIT  'xid';   -- 或 XA ROLLBACK

0.7 其他常用语句

0.7.1 EXPLAIN 与诊断

EXPLAIN SELECT ...;
EXPLAIN FORMAT=JSON SELECT ...;
EXPLAIN ANALYZE SELECT ...;             -- 8.0.18+ 实际执行并计时
SHOW WARNINGS;                          -- 看优化器改写后的 SQL（配合 EXPLAIN EXTENDED）

SHOW PROCESSLIST;                       -- 当前连接/会话
SHOW FULL PROCESSLIST;
KILL <id>;                              -- 杀掉某个会话/查询
KILL QUERY <id>;

SHOW ENGINE INNODB STATUS;              -- 死锁、锁等待详情
SHOW STATUS LIKE 'Threads%';
SHOW VARIABLES LIKE 'innodb%';

0.7.2 锁与会话

LOCK TABLES t READ;
LOCK TABLES t WRITE;
UNLOCK TABLES;

GET_LOCK('name', 10);                   -- 应用层命名锁
RELEASE_LOCK('name');

0.7.3 导入导出

-- 导出
SELECT * FROM t INTO OUTFILE '/tmp/t.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 导入
LOAD DATA [LOCAL] INFILE '/tmp/t.csv' INTO TABLE t
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(a, b, c);

0.7.4 预编译语句（PREPARE）

PREPARE stmt FROM 'SELECT * FROM t WHERE id = ?';
SET @id = 1;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

0.7.5 存储过程 / 函数 / 触发器（了解即可）

DELIMITER $$
CREATE PROCEDURE p_add(IN a INT, IN b INT, OUT c INT)
BEGIN
    SET c = a + b;
END$$
DELIMITER ;

CALL p_add(1, 2, @r);
SELECT @r;

CREATE FUNCTION f_add(a INT, b INT) RETURNS INT DETERMINISTIC
RETURN a + b;

CREATE TRIGGER trg_before_ins BEFORE INSERT ON t
FOR EACH ROW SET NEW.create_time = NOW();

实践中后端不推荐在 DB 写复杂逻辑：难维护、难版本管理、扩容困难，业务逻辑放应用层。

0.7.6 变量与注释

-- 用户变量（会话级）
SET @x = 1;
SELECT @x := COUNT(*) FROM t;

-- 系统变量
SET SESSION sql_mode = '...';
SET GLOBAL  max_connections = 1000;
SELECT @@session.sql_mode, @@global.max_connections;

-- 注释
-- 单行
# 单行（MySQL 扩展）
/* 多行 */
/*! 50700 ... */     -- 版本注释，>=5.7 才执行

0.8 易错点速查

写法	问题
WHERE col = NULL	永假，应用 IS NULL
col NOT IN (子查询含 NULL)	整体结果为空
SELECT *	阻碍覆盖索引、传输浪费
LIMIT 1000000, 10	深分页扫描大量数据，用游标或延迟关联
ORDER BY RAND()	全表排序，数据多时性能极差
WHERE DATE(create_time)='2025-01-01'	函数包列，索引失效
WHERE phone = 13800000000	字符串列被隐式转数字，索引失效
INSERT ... SELECT 长事务	加大量锁，分批更安全
UPDATE t SET ... 无 WHERE	全表更新，开 sql_safe_updates
REPLACE INTO	先删后插，自增暴涨、外键级联
COUNT(列) 当行数	会过滤 NULL，要行数用 COUNT(*)
UNION vs UNION ALL	不去重需求一定用 ALL，省排序
JOIN 没建被驱动表索引	笛卡尔级别开销

---

一、SQL 基础与执行顺序

1.1 SQL 语句的逻辑执行顺序

书写顺序与执行顺序不同，理解执行顺序是写出正确 SQL 的前提。

书写顺序：

SELECT → FROM → JOIN → ON → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

执行顺序：

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

关键推论：

• WHERE 在 GROUP BY 之前执行，因此 WHERE 中不能用聚合函数。
• HAVING 在 GROUP BY 之后执行，可以用聚合函数过滤分组。
• SELECT 中的列别名不能在 WHERE、GROUP BY、HAVING 中使用（MySQL 在 GROUP BY/HAVING/ORDER BY 中允许别名是个例外），但在 ORDER BY 中可以使用。

1.2 三值逻辑与 NULL

SQL 使用三值逻辑：TRUE / FALSE / UNKNOWN。NULL 参与的所有比较结果都是 UNKNOWN。

• NULL = NULL 结果为 UNKNOWN，不为 TRUE。
• 判断 NULL 必须用 IS NULL / IS NOT NULL。
• NOT IN (子查询) 子查询若包含 NULL，整体结果为空（经典坑）。
• COUNT(*) 统计行数包含 NULL；COUNT(列) 不统计 NULL。
• SUM/AVG 忽略 NULL；AVG 的分母是非 NULL 行数。

1.3 JOIN 类型

• INNER JOIN：取交集。
• LEFT/RIGHT JOIN：保留左/右表全部行，未匹配置 NULL。
• FULL OUTER JOIN：MySQL 不支持，可用 UNION 模拟。
• CROSS JOIN：笛卡尔积。
• SELF JOIN：自连接，常用于层级数据、对比同表行。

JOIN 的本质：先笛卡尔积，再用 ON 条件过滤；外连接还会补上不匹配的行。

ON 与 WHERE 的差别：在外连接中，ON 决定能否匹配（不匹配仍保留并补 NULL），WHERE 是匹配后的过滤（会把外连接补出来的 NULL 行干掉，效果退化为内连接）。

---

二、索引

2.1 B+ 树索引结构

InnoDB 使用 B+ 树作为索引结构。

特点：

• 非叶子节点只存索引键和指针，不存数据，单个节点能容纳更多键，树更矮。
• 所有数据都在叶子节点，叶子节点之间通过双向链表连接，范围查询效率高。
• 通常 3-4 层即可索引千万级数据，每次查询 IO 次数少。

为什么不用 B 树/Hash/红黑树：

• B 树非叶子节点存数据，单节点容纳键少，树更高。
• Hash 不支持范围查询、排序、最左前缀。
• 红黑树是二叉，深度太大，磁盘 IO 次数过多。

2.2 聚簇索引 vs 非聚簇索引

InnoDB 中：

• 聚簇索引（Clustered Index）：叶子节点直接存放整行数据。一张表有且仅有一个聚簇索引。
  • 主键存在 → 主键即聚簇索引。
  • 无主键 → 选第一个非空唯一索引。
  • 都没有 → InnoDB 隐式生成 6 字节 ROW_ID。
• 二级索引（Secondary Index）：叶子节点存索引列 + 主键值。

回表（Back to table）：通过二级索引查到主键，再到聚簇索引中查完整行的过程。

2.3 覆盖索引

如果查询的字段都被索引包含（不包括 SELECT *），可直接从二级索引返回结果，不需要回表，称为覆盖索引。EXPLAIN 中 Extra 显示 Using index。

实战中是减少回表的核心手段。常见组合：(a, b, c) 联合索引覆盖 SELECT a, b, c WHERE a = ?。

2.4 联合索引与最左前缀

联合索引 (a, b, c) 实际只能命中以下前缀：

• a
• a, b
• a, b, c

不能命中：

• b、c、b, c（不满足最左前缀）

注意点：

• WHERE a = ? AND c = ? 只能用到 a 部分，c 走不了索引。
• WHERE a = ? AND b > ? AND c = ? 走到 b 的范围条件后，c 用不上索引（范围之后字段失效）。
• WHERE b = ? AND a = ? 优化器会自动调整顺序，能命中。

2.5 索引失效场景

• 对索引列使用函数/运算：WHERE DATE(create_time) = ?、WHERE id + 1 = 10。
• 隐式类型转换：WHERE phone = 13800000000（phone 是 varchar，会转成数字导致索引失效）。
• 字符串隐式转换字符集。
• LIKE '%xxx' 前导模糊。
• OR 条件中存在未建索引的列。
• 优化器认为全表扫描更快（数据量小、命中率高）。
• !=、<>、NOT IN 在大多数情况下走不了索引（取决于选择性）。
• IS NULL 现代版本可以走索引。

2.6 索引下推（ICP, Index Condition Pushdown）

MySQL 5.6 引入。对于联合索引 (a, b)，查询 WHERE a = ? AND b LIKE '%xx'：

• 关闭 ICP：a 命中后回表，由 server 层过滤 b。
• 开启 ICP：在存储引擎层用索引中的 b 列直接过滤，减少回表次数。

EXPLAIN 的 Extra 显示 Using index condition。

2.7 索引设计原则

• 高频查询字段、高选择性（基数大）的字段优先。
• 联合索引顺序：等值在前、范围在后；高选择性在前。
• 区分度低的字段（性别、状态）单列索引意义不大，可放联合索引尾部。
• 合理使用覆盖索引，减少回表。
• 索引不是越多越好：每个索引都会增加写入成本和占用空间。
• 短索引：长字符串可用前缀索引 INDEX(name(10))。

---

三、事务

3.1 ACID

• 原子性（Atomicity）：事务内操作要么全成功，要么全失败 → undo log 实现。
• 一致性（Consistency）：事务前后数据满足业务约束 → 是目标，由其他三个特性保证。
• 隔离性（Isolation）：并发事务互不干扰 → 锁 + MVCC。
• 持久性（Durability）：事务提交后永久生效 → redo log 实现。

3.2 隔离级别

级别	脏读	不可重复读	幻读
READ UNCOMMITTED	可能	可能	可能
READ COMMITTED（RC）	否	可能	可能
REPEATABLE READ（RR，MySQL 默认）	否	否	基本解决
SERIALIZABLE	否	否	否

• 脏读：读到其他事务未提交的数据。
• 不可重复读：同一事务内两次读同一行结果不同（其他事务修改并提交了）。
• 幻读：同一事务内两次读同一范围结果集不同（其他事务插入并提交了）。

InnoDB 在 RR 下通过间隙锁（Gap Lock）+ Next-Key Lock 解决了幻读，这是 MySQL 与标准 SQL 的差异。

3.3 MVCC（多版本并发控制）

InnoDB 在 RC 和 RR 下用 MVCC 实现非锁定读（快照读），写不阻塞读、读不阻塞写。

核心组件：

• 隐藏字段：每行 DB_TRX_ID（最近修改事务 ID）、DB_ROLL_PTR（回滚指针）、DB_ROW_ID。
• undo log：维护历史版本链。
• ReadView：事务可见性判断的快照，包含活跃事务列表 m_ids、最小活跃事务 min_trx_id、下一个事务 ID max_trx_id、创建者 creator_trx_id。

可见性判断：

• 行的 trx_id < min_trx_id → 可见。
• 行的 trx_id >= max_trx_id → 不可见。
• 行的 trx_id 在活跃列表中 → 不可见，沿 undo 链找历史版本。
• 行的 trx_id 不在活跃列表，且 < max_trx_id → 可见。

RC 与 RR 的差别：

• RC：每次 SELECT 都生成新的 ReadView。
• RR：第一次 SELECT 生成 ReadView，整个事务复用。

快照读 vs 当前读：

• 快照读：普通 SELECT，读 ReadView 对应版本。
• 当前读：SELECT ... FOR UPDATE、LOCK IN SHARE MODE、UPDATE、DELETE、INSERT，读最新版本并加锁。

---

四、锁机制

4.1 锁的粒度

• 全局锁：FLUSH TABLES WITH READ LOCK，全库只读，用于全库逻辑备份。
• 表级锁：LOCK TABLES、元数据锁（MDL）、意向锁（IS/IX）。
• 行级锁（InnoDB）：粒度小，并发高。

4.2 行锁分类

InnoDB 行锁实际是加在索引上的：

• Record Lock（记录锁）：锁定单条索引记录。
• Gap Lock（间隙锁）：锁定一个范围（开区间），防止插入。
• Next-Key Lock：Record Lock + Gap Lock，锁定一个左开右闭的区间，是 RR 解决幻读的关键。
• Insert Intention Lock（插入意向锁）：是一种特殊的 Gap Lock，多个事务可以同时持有不冲突的插入意向锁。

按模式分：

• 共享锁（S 锁）：LOCK IN SHARE MODE，可共存。
• 排他锁（X 锁）：FOR UPDATE，互斥。

4.3 意向锁

为了让表锁和行锁共存：

• IS（意向共享锁）：事务打算给某些行加 S 锁，先给表加 IS。
• IX（意向排他锁）：事务打算给某些行加 X 锁，先给表加 IX。

意向锁之间互不冲突，只与表级 S/X 锁冲突。这样判断"表能否加 X 锁"时不必扫描每一行。

4.4 加锁规则（RR 隔离级别）

记忆口诀：原则上加 next-key lock，等值命中退化，范围向后扩展。

• 主键/唯一索引等值匹配，且记录存在 → 退化为 Record Lock。
• 主键/唯一索引等值匹配，记录不存在 → 退化为 Gap Lock。
• 普通索引等值匹配 → 命中行加 Next-Key Lock，下一行加 Gap Lock（直到第一个不满足等值的位置）。
• 范围查询 → 加 Next-Key Lock，向右遍历到第一个不满足条件的值。

4.5 死锁

发生条件：互斥、不可剥夺、请求与保持、循环等待。

InnoDB 的处理：

• 自动检测死锁（wait-for graph），回滚代价小的事务。
• 也可设置 innodb_lock_wait_timeout 超时退出。

排查工具：

• SHOW ENGINE INNODB STATUS：查看 LATEST DETECTED DEADLOCK 段。
• information_schema.INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS（8.0 改为 performance_schema.data_locks 等）。

避免方法：

• 固定加锁顺序。
• 减小事务粒度、缩短事务时间。
• 走索引避免锁升级到全表扫描。
• 业务上拆分大事务。

---

五、日志系统

5.1 redo log（重做日志）

• 作用：保证持久性，崩溃恢复（crash-safe）。
• 层级：InnoDB 引擎层。
• 特点：物理日志，记录"在某数据页上做了什么修改"，循环写、固定大小。
• 写入流程：先写 redo log buffer，再 fsync 到磁盘。
• 配合 WAL（Write-Ahead Logging）：先写日志再刷脏页，避免每次修改都随机写磁盘。

innodb_flush_log_at_trx_commit：

• 0：每秒写并 fsync，宕机丢 1 秒数据。
• 1（默认）：每次提交都 fsync，最安全。
• 2：每次提交写 OS 缓存，每秒 fsync。

5.2 undo log（回滚日志）

• 作用：事务回滚 + MVCC 历史版本。
• 层级：InnoDB 引擎层。
• 特点：逻辑日志，记录反向操作（INSERT 的 undo 是 DELETE）。

5.3 binlog（归档日志）

• 作用：主从复制、数据恢复（按时间点恢复）。
• 层级：MySQL Server 层，所有引擎都有。
• 特点：逻辑日志，追加写入，多个文件。

格式：

• STATEMENT：记录 SQL 语句，可能引起主从不一致（如 NOW()、UUID()）。
• ROW：记录每行变更，安全但日志量大。
• MIXED：折中。

5.4 两阶段提交（2PC）

binlog 与 redo log 必须保证一致，否则主从数据不一致。

流程：

1. Prepare：写 redo log，状态置为 prepare。
2. 写 binlog。
3. Commit：redo log 状态置为 commit。

崩溃恢复时：

• redo log 是 commit → 直接提交。
• redo log 是 prepare → 看 binlog 是否完整：完整则提交，否则回滚。

5.5 三大日志对比

日志	层级	作用	类型	写入方式
redo log	InnoDB	崩溃恢复	物理日志	循环写
undo log	InnoDB	回滚 + MVCC	逻辑日志	随事务
binlog	Server	主从、归档	逻辑日志	追加写

---

六、执行计划与查询优化

6.1 EXPLAIN 重点字段

• id：查询序号，相同从上往下执行，不同越大越先执行。
• select_type：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION 等。
• type（访问类型，从优到劣）：
  • system > const > eq_ref > ref > range > index > ALL
  • 至少要达到 range，理想是 ref。
• possible_keys / key：可能用到的索引 / 实际用到的索引。
• key_len：用到索引的字节数，可判断联合索引使用了几列。
• rows：预估扫描行数。
• filtered：过滤后剩余比例。
• Extra（重点）：
  • Using index：覆盖索引，好。
  • Using where：server 层用 WHERE 过滤。
  • Using index condition：索引下推。
  • Using filesort：额外排序，需优化。
  • Using temporary：用了临时表，常见于 GROUP BY、DISTINCT。

6.2 慢查询排查流程

1. 开慢查询日志：slow_query_log、long_query_time。
2. mysqldumpslow 或 pt-query-digest 聚合分析。
3. EXPLAIN 看执行计划。
4. EXPLAIN ANALYZE（8.0+）或 SHOW PROFILE 看实际耗时。
5. 检查索引、SQL 写法、表设计。

6.3 常见 SQL 优化手段

• 避免 SELECT *：增加 IO、阻碍覆盖索引。
• LIMIT 大偏移量优化：LIMIT 1000000, 10 改为基于游标 WHERE id > ? LIMIT 10，或延迟关联 JOIN (SELECT id FROM t LIMIT 1000000, 10) USING(id)。
• IN 子查询改 JOIN（老版本优化器对子查询支持差）。
• OR 改 UNION ALL（每个分支可以分别走索引）。
• COUNT(*) 与 COUNT(1) 性能等价，COUNT(列) 会过滤 NULL；MyISAM 缓存总行数所以快，InnoDB 必须扫描。
• 分页+排序，给排序字段建索引避免 filesort。
• JOIN 时驱动表选小表（小表驱动大表，被驱动表的关联字段必须有索引）。
• EXISTS vs IN：外大内小用 IN，外小内大用 EXISTS（8.0 后优化器差异变小）。
• 批量操作合并：INSERT ... VALUES (), (), () 比循环单条快得多。

---

七、表设计与范式

7.1 三大范式

• 1NF：列不可再分（原子性）。
• 2NF：在 1NF 基础上，非主属性完全依赖主键（消除部分依赖）。
• 3NF：在 2NF 基础上，非主属性不依赖于其他非主属性（消除传递依赖）。

实践中常反范式冗余字段以减少 JOIN，提升查询性能，但要注意一致性维护。

7.2 字段设计建议

• 主键：自增 BIGINT，避免 UUID（无序导致页分裂）。分布式场景用雪花算法保证趋势递增。
• 时间：用 DATETIME 而非 TIMESTAMP（2038 问题、时区问题）。
• 金额：用 DECIMAL，避免 FLOAT/DOUBLE 精度问题。
• 字符串：能用定长 CHAR 不用 VARCHAR（如 MD5、手机号），定长访问快。
• 状态字段：TINYINT 而非 VARCHAR。
• 尽量 NOT NULL + 默认值：NULL 占用额外空间、影响索引、聚合函数行为复杂。
• 字符集：utf8mb4，支持 emoji 和 4 字节字符。

7.3 单表数据量

阿里规约：单表行数超过 500 万行或单表容量超过 2GB 才推荐分库分表。

实际不是死指标，看 B+ 树高度：

• 一个 16KB 数据页大约 1170 个索引指针（假设主键 BIGINT + 指针约 14 字节）。
• 3 层 B+ 树：1170 × 1170 × 16（每页 16 行数据）≈ 2000 万。
• 4 层：≈ 230 亿。

层级越高，IO 越多，所以单表通常控制在千万级。

---

八、分库分表

8.1 拆分方式

• 垂直拆分：
  • 垂直分库：按业务划分（订单库、用户库）。
  • 垂直分表：把不常用或大字段拆出去。
• 水平拆分：
  • 水平分库：同一张表拆到多个库。
  • 水平分表：同一张表拆到多张物理表。

8.2 分片策略

• Range：按范围（时间、ID 区间）。优点：易扩容；缺点：热点。
• Hash：按 hash 取模。优点：均匀；缺点：扩容需要数据迁移。
• 一致性 Hash：缓解扩容迁移问题。
• 路由表：灵活但需要额外维护。

8.3 引发的问题

• 跨库 JOIN：通常在应用层组装，或冗余字段、ER 分片。
• 分布式事务：2PC、TCC、最大努力通知、本地消息表、Seata。
• 全局唯一 ID：UUID、雪花算法、号段模式（美团 Leaf）、Redis incr。
• 跨库分页：全局视野（所有库取 N 条再合并排序）、二次查询、禁止深翻页。
• 数据迁移与扩容：双写 + 校验 + 切流、一致性 Hash 减少迁移。

中间件：

• 客户端：Sharding-JDBC（ShardingSphere）、TDDL。
• Proxy：MyCat、ShardingSphere-Proxy、Vitess。

---

九、主从复制与高可用

9.1 主从复制原理

1. 主库写 binlog。
2. 从库 IO 线程拉取 binlog，写入 relay log。
3. 从库 SQL 线程重放 relay log。

9.2 复制方式

• 异步复制：默认，主库不等从库 ack，可能丢数据。
• 半同步复制：主库等至少一个从库收到 binlog 才返回。
• 组复制（MGR）：基于 Paxos，强一致。

9.3 主从延迟原因与处理

原因：

• 从库单线程重放（5.7 起支持并行复制 MTS）。
• 大事务、大表 DDL。
• 从库机器配置低、读压力大。

处理：

• 强一致读：走主库（写后读、关键链路）。
• 业务容忍：读从库即可。
• 升级到并行复制、降低主库写压力、拆分大事务。

9.4 读写分离

• 主库写、从库读。
• 写后立即读容易读不到（主从延迟）→ 强制走主库或缓存写入结果。
• 中间件：ShardingSphere、MyCat、ProxySQL。

---

十、其他高频考点

10.1 char vs varchar

• CHAR(n)：定长，不足空格填充，访问效率高，适合定长字段（手机号、身份证、MD5）。
• VARCHAR(n)：变长，占用 = 实际长度 + 1~2 字节长度前缀，节省空间。
• 行内变长字段过多可能导致行溢出（数据存到溢出页）。

10.2 TEXT/BLOB

• 不能有默认值。
• 不能整列建索引，只能前缀索引。
• 通常单独拆表存储。

10.3 InnoDB vs MyISAM

维度	InnoDB	MyISAM
事务	支持	不支持
外键	支持	不支持
锁	行锁	表锁
崩溃恢复	支持	不支持
全文索引	5.6+ 支持	支持
聚簇索引	是	否（数据与索引分开）
COUNT(*)	实时统计	缓存总行数，O(1)

MySQL 5.5+ 默认 InnoDB，MyISAM 基本退场。

10.4 字符集与排序规则

• utf8：MySQL 历史包袱，最多 3 字节，不支持 emoji。
• utf8mb4：真正的 UTF-8，4 字节，必选。
• collation：决定比较和排序规则，utf8mb4_general_ci 不区分大小写但不严格，utf8mb4_0900_ai_ci（8.0 默认）更准确。

10.5 SQL 注入与防御

• 用预编译（PreparedStatement）+ 参数绑定。
• 应用层不要拼接用户输入到 SQL。
• 最小权限原则，数据库账号按需授权。
• 使用 ORM 时也要小心动态 SQL（如 MyBatis 的 ${} vs #{}）。

10.6 软删除 vs 物理删除

• 软删除：加 is_deleted 字段，查询带条件，注意唯一索引要包含此字段或用空值技巧。
• 物理删除：彻底删除，配合归档表保留历史。

10.7 乐观锁与悲观锁

• 悲观锁：SELECT ... FOR UPDATE，假设并发冲突高。
• 乐观锁：版本号或 CAS，UPDATE t SET v = v+1, version = version+1 WHERE id = ? AND version = ?，假设冲突少，失败重试。
• 高并发下乐观锁更友好，但要处理 ABA 问题（版本号即可解决）。

10.8 大表 DDL

• 直接 ALTER 会锁表很久。
• 使用 Online DDL（5.6+ 支持，部分操作仍会拷表）。
• 工具：pt-online-schema-change、gh-ost（GitHub 的无触发器方案）。
• 原理：建影子表 → 增量同步 → 切换。

10.9 窗口函数（8.0+）

SELECT
    user_id,
    order_time,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn,
    SUM(amount) OVER (PARTITION BY user_id) AS total
FROM orders;

常见函数：ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD、SUM/AVG OVER。 典型场景：每组 TopN、累计求和、同比环比。

10.10 CTE（公用表表达式，8.0+）

WITH t AS (
    SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT * FROM t WHERE cnt > 10;

支持递归 CTE，处理树形结构。

---

十一、常用 ORM 框架语法

后端面试常被问"你用过什么 ORM？""手写一段 XX 框架的查询？"。下面分 Java 与 Go 两栈整理高频框架的核心语法。

11.1 Java：MyBatis

国内 Java 后端事实标准。SQL 写在 XML 或注解中，灵活可控。

11.1.1 Mapper 接口与 XML

public interface UserMapper {
    User selectById(Long id);
    List<User> selectByCondition(@Param("name") String name, @Param("status") Integer status);
    int insert(User user);
    int updateById(User user);
    int deleteById(Long id);
}

<mapper namespace="com.x.UserMapper">

    <resultMap id="UserMap" type="com.x.User">
        <id column="id" property="id"/>
        <result column="user_name" property="userName"/>
        <result column="create_time" property="createTime"/>
    </resultMap>

    <sql id="Base_Column_List">id, user_name, status, create_time</sql>

    <select id="selectById" resultMap="UserMap">
        SELECT <include refid="Base_Column_List"/>
        FROM user WHERE id = #{id}
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user(user_name, status) VALUES(#{userName}, #{status})
    </insert>
</mapper>

11.1.2 #{} vs ${}（必考）

• #{}：预编译占位符（PreparedStatement 的 ?），自动防 SQL 注入。默认用这个。
• ${}：字符串拼接，有注入风险。仅用于动态表名、列名、ORDER BY 字段等占位符无法表达的场景。

<!-- 安全：参数 -->
WHERE id = #{id}

<!-- 仅排序字段名要用 ${}，但要白名单校验 -->
ORDER BY ${sortColumn}

11.1.3 动态 SQL

<select id="selectByCondition" resultMap="UserMap">
    SELECT * FROM user
    <where>
        <if test="name != null and name != ''">
            AND user_name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
    <choose>
        <when test="orderBy == 'time'">ORDER BY create_time DESC</when>
        <otherwise>ORDER BY id DESC</otherwise>
    </choose>
</select>

<update id="updateById">
    UPDATE user
    <set>
        <if test="userName != null">user_name = #{userName},</if>
        <if test="status != null">status = #{status},</if>
        update_time = NOW()
    </set>
    WHERE id = #{id}
</update>

<!-- 批量插入：foreach -->
<insert id="batchInsert">
    INSERT INTO user(user_name, status) VALUES
    <foreach collection="list" item="u" separator=",">
        (#{u.userName}, #{u.status})
    </foreach>
</insert>

<!-- IN 查询 -->
<select id="selectByIds" resultType="User">
    SELECT * FROM user WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<!-- trim 万能拼接（替代 where/set） -->
<trim prefix="WHERE" prefixOverrides="AND |OR ">
    <if test="...">AND a = #{a}</if>
</trim>

11.1.4 注解写法

@Select("SELECT * FROM user WHERE id = #{id}")
User selectById(Long id);

@Insert("INSERT INTO user(name) VALUES(#{name})")
@Options(useGeneratedKeys = true, keyProperty = "id")
int insert(User user);

@Update("UPDATE user SET name=#{name} WHERE id=#{id}")
int update(@Param("id") Long id, @Param("name") String name);

复杂动态 SQL 用 @SelectProvider + Provider 类，但通常不如 XML 清晰。

11.1.5 一二级缓存

• 一级缓存：SqlSession 级别，默认开启。同一会话内相同查询走缓存。
• 二级缓存：namespace 级别，需 <cache/> 显式开启。分布式场景一般禁用，用 Redis 替代。

11.2 Java：MyBatis-Plus

在 MyBatis 之上提供 CRUD 模板和条件构造器，减少模板代码。

11.2.1 实体注解

@TableName("user")
public class User {
    @TableId(type = IdType.AUTO)            // AUTO/ASSIGN_ID(雪花)/INPUT
    private Long id;

    @TableField("user_name")
    private String userName;

    @TableLogic                              // 逻辑删除
    private Integer deleted;

    @Version                                 // 乐观锁
    private Integer version;

    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime createTime;

    @TableField(fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;

    @TableField(exist = false)               // 非数据库字段
    private String extra;
}

11.2.2 BaseMapper 自带方法

public interface UserMapper extends BaseMapper<User> {}

userMapper.insert(user);
userMapper.deleteById(id);
userMapper.updateById(user);
userMapper.selectById(id);
userMapper.selectBatchIds(Arrays.asList(1L, 2L));
userMapper.selectList(null);                 // null 查全部
userMapper.selectCount(null);

11.2.3 条件构造器 Wrapper

// QueryWrapper：链式
QueryWrapper<User> qw = new QueryWrapper<>();
qw.eq("status", 1)
  .like("user_name", "张")
  .gt("age", 18)
  .between("create_time", start, end)
  .in("id", Arrays.asList(1L, 2L, 3L))
  .isNotNull("email")
  .orderByDesc("create_time")
  .last("LIMIT 10");
List<User> list = userMapper.selectList(qw);

// LambdaQueryWrapper：避免硬编码字段名
LambdaQueryWrapper<User> lqw = Wrappers.<User>lambdaQuery()
    .eq(User::getStatus, 1)
    .like(StringUtils.isNotBlank(name), User::getUserName, name)   // 条件构造
    .ge(User::getAge, 18)
    .orderByDesc(User::getCreateTime);

// UpdateWrapper
new LambdaUpdateWrapper<User>()
    .set(User::getStatus, 0)
    .eq(User::getId, 1L);

// 链式调用（IService）
userService.lambdaQuery().eq(User::getStatus, 1).list();
userService.lambdaUpdate().set(User::getStatus, 0).eq(User::getId, 1L).update();

11.2.4 分页

// 1. 配置分页插件
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
    MybatisPlusInterceptor i = new MybatisPlusInterceptor();
    i.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
    return i;
}

// 2. 使用
Page<User> page = new Page<>(1, 10);     // 第1页，每页10条
Page<User> result = userMapper.selectPage(page,
    new LambdaQueryWrapper<User>().eq(User::getStatus, 1));
result.getRecords();
result.getTotal();
result.getPages();

11.3 Java：JPA / Hibernate

Spring Data JPA 是基于 Hibernate 的封装，强 ORM 风格。

11.3.1 实体映射

@Entity
@Table(name = "user", indexes = @Index(name="idx_name", columnList="name"))
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "user_name", length = 64, nullable = false)
    private String userName;

    @Enumerated(EnumType.STRING)
    private Status status;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "dept_id")
    private Dept dept;

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Order> orders;

    @CreatedDate
    private LocalDateTime createTime;
    @LastModifiedDate
    private LocalDateTime updateTime;
}

关系：@OneToOne / @OneToMany / @ManyToOne / @ManyToMany，注意 fetch=LAZY（默认 ManyToOne 是 EAGER，需手动改）和 N+1 问题。

11.3.2 Repository 接口

public interface UserRepository extends JpaRepository<User, Long> {

    // 1. 方法名解析（Spring Data 魔法）
    List<User> findByUserName(String name);
    List<User> findByUserNameLikeAndStatus(String name, Status status);
    List<User> findByAgeGreaterThanEqualOrderByCreateTimeDesc(Integer age);
    Optional<User> findFirstByEmailIgnoreCase(String email);
    long countByStatus(Status status);
    void deleteByStatus(Status status);
    boolean existsByEmail(String email);

    // 2. JPQL（面向实体）
    @Query("SELECT u FROM User u WHERE u.status = :status")
    List<User> findByStatus(@Param("status") Status status);

    // 3. 原生 SQL
    @Query(value = "SELECT * FROM user WHERE status = ?1", nativeQuery = true)
    List<User> findByStatusNative(Integer status);

    // 4. 修改/删除必须 @Modifying + @Transactional
    @Modifying
    @Query("UPDATE User u SET u.status = :s WHERE u.id = :id")
    int updateStatus(@Param("id") Long id, @Param("s") Status s);
}

方法名关键字：findBy / countBy / existsBy / deleteBy + And / Or / Between / LessThan / GreaterThan / Like / In / IsNull / OrderBy + Asc/Desc。

11.3.3 分页与排序

Page<User> p = userRepository.findAll(
    PageRequest.of(0, 10, Sort.by(Sort.Direction.DESC, "createTime"))
);
p.getContent(); p.getTotalElements(); p.getTotalPages();

11.3.4 Specification 动态查询

Specification<User> spec = (root, query, cb) -> {
    List<Predicate> ps = new ArrayList<>();
    if (name != null) ps.add(cb.like(root.get("userName"), "%"+name+"%"));
    if (status != null) ps.add(cb.equal(root.get("status"), status));
    return cb.and(ps.toArray(new Predicate[0]));
};
userRepository.findAll(spec, PageRequest.of(0, 10));

11.3.5 经典坑

• N+1：@OneToMany 默认 LAZY，循环访问时每个对象都触发一次 SQL。解法：JOIN FETCH、@EntityGraph、Fetch Join。
• 懒加载 LazyInitializationException：会话已关却访问关联属性。
• save vs saveAndFlush：前者只入持久化上下文，事务提交才 flush。
• 持久化上下文（一级缓存）：相同事务内 findById 同一 ID 只查一次。

11.4 Go：GORM

Go 生态最流行的 ORM。

11.4.1 模型定义

type User struct {
    ID        uint           `gorm:"primaryKey;autoIncrement"`
    Name      string         `gorm:"size:64;not null;index:idx_name"`
    Email     string         `gorm:"size:128;uniqueIndex"`
    Status    int8           `gorm:"default:1"`
    Balance   decimal.Decimal `gorm:"type:decimal(18,2)"`
    DeptID    uint
    Dept      Dept
    Orders    []Order
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt gorm.DeletedAt `gorm:"index"`        // 软删除
}

func (User) TableName() string { return "user" }

11.4.2 连接

dsn := "user:pwd@tcp(127.0.0.1:3306)/db?charset=utf8mb4&parseTime=true&loc=Local"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
    Logger: logger.Default.LogMode(logger.Info),
    NamingStrategy: schema.NamingStrategy{SingularTable: true},
})

sqlDB, _ := db.DB()
sqlDB.SetMaxOpenConns(100)
sqlDB.SetMaxIdleConns(10)
sqlDB.SetConnMaxLifetime(time.Hour)

11.4.3 CRUD

// 创建
db.Create(&user)
db.Create(&[]User{u1, u2, u3})              // 批量
db.CreateInBatches(users, 100)              // 分批

// 查询
var u User
db.First(&u, 1)                             // 主键
db.First(&u, "email = ?", "x@y.com")
db.Where("status = ? AND age > ?", 1, 18).Find(&users)
db.Where(&User{Status: 1}).Find(&users)     // 结构体条件
db.Where(map[string]interface{}{"status": 1}).Find(&users)
db.Select("id, name").Where("status=1").Find(&users)
db.Order("create_time desc").Limit(10).Offset(20).Find(&users)
db.Distinct("name").Find(&names)
db.Where("status = ?", 1).Count(&total)

// 更新
db.Model(&u).Update("name", "new")                          // 单字段
db.Model(&u).Updates(User{Name: "n", Status: 1})            // 结构体（零值忽略）
db.Model(&u).Updates(map[string]interface{}{"name":"n"})    // map（零值不忽略）
db.Model(&User{}).Where("status=?", 0).Update("status", 1)  // 批量
db.Model(&u).UpdateColumn("name", "n")                      // 不更新 UpdatedAt

// 删除（有 DeletedAt 字段则为软删，否则物理删）
db.Delete(&u, 1)
db.Where("status=0").Delete(&User{})
db.Unscoped().Delete(&u)                                    // 强制物理删
db.Unscoped().Where("deleted_at IS NOT NULL").Find(&users)  // 查含已删

11.4.4 链式与作用域

// 链式
db.Where("a=?", 1).Or("b=?", 2).Not("c=?", 3).Find(&users)

// 命名作用域
func ActiveUser(db *gorm.DB) *gorm.DB { return db.Where("status=?", 1) }
db.Scopes(ActiveUser).Find(&users)

11.4.5 关联与预加载

// 预加载（避免 N+1）
db.Preload("Dept").Preload("Orders").Find(&users)
db.Preload("Orders", "status = ?", 1).Find(&users)
db.Preload("Orders.Items").Find(&users)             // 嵌套
db.Joins("Dept").Find(&users)                       // 左连接，单条 SQL

// 关联操作
db.Model(&user).Association("Orders").Append(&order)
db.Model(&user).Association("Orders").Count()
db.Model(&user).Association("Orders").Replace(&newOrders)

11.4.6 原生 SQL 与事务

// 原生
db.Raw("SELECT * FROM user WHERE id=?", 1).Scan(&u)
db.Exec("UPDATE user SET status=? WHERE id=?", 0, 1)

// 事务
db.Transaction(func(tx *gorm.DB) error {
    if err := tx.Create(&u).Error; err != nil { return err }
    if err := tx.Model(&account).Update("balance", gorm.Expr("balance - ?", 100)).Error; err != nil {
        return err
    }
    return nil
})

// 手动事务
tx := db.Begin()
defer func() { if r := recover(); r != nil { tx.Rollback() } }()
if err := tx.Create(&u).Error; err != nil { tx.Rollback(); return }
tx.Commit()

11.4.7 钩子 Hooks

func (u *User) BeforeCreate(tx *gorm.DB) error { ... }
// AfterCreate / BeforeUpdate / AfterUpdate / BeforeDelete / AfterDelete / AfterFind

11.4.8 GORM 经典坑

• 零值更新：用结构体 Updates，零值字段（0、""、false）会被忽略；要更新到零值需用 map 或 Select 显式指定。
• Find 不报 ErrRecordNotFound：只有 First/Take/Last 会报错，Find 没结果不报错。
• AutoMigrate 不删列：列删除/类型缩窄需手动处理。
• DeletedAt 全局影响：定义了软删字段后，所有查询都默认带 deleted_at IS NULL。

11.5 Go：sqlx / ent / sqlc 简表

框架	风格	特点
database/sql	标准库	最底层，手写 SQL + Scan，灵活但啰嗦
sqlx	标准库增强	加 StructScan/Get/Select/Named 参数，仍是手写 SQL
GORM	ORM	模型映射、链式 API，适合 CRUD 密集业务
ent	代码生成	Facebook 出品，schema as code，强类型图模型
sqlc	SQL → 代码	写原生 SQL，生成类型安全的 Go 代码，性能/可读性最佳

sqlx 示例：

db, _ := sqlx.Connect("mysql", dsn)

var u User
db.Get(&u, "SELECT * FROM user WHERE id=?", 1)

var users []User
db.Select(&users, "SELECT * FROM user WHERE status=?", 1)

// 命名参数
db.NamedExec("INSERT INTO user(name,age) VALUES(:name,:age)",
    map[string]interface{}{"name":"x", "age":20})

sqlc 示例（query.sql 写好后生成代码）：

-- name: GetUser :one
SELECT * FROM user WHERE id = ? LIMIT 1;

-- name: ListActiveUsers :many
SELECT * FROM user WHERE status = 1 ORDER BY id DESC LIMIT ?;

11.6 ORM 共性问题（面试高频）

1. N+1 查询：循环里查关联数据。解法：预加载/JOIN/批量查后内存组装。
2. 大事务：ORM 习惯把整个流程放一个事务，注意拆小。
3. 隐式全表更新/删除：MyBatis-Plus、GORM 缺条件时可能误伤。GORM 默认禁止无条件 Delete/Update（AllowGlobalUpdate 才放开）。
4. ORM 还是手写 SQL：CRUD 用 ORM 提效；复杂报表、深度优化用原生 SQL；混用是常态。
5. 逻辑删除：业务上几乎都用，但要注意唯一索引冲突（deleted=0 可能多行存在 → 把 deleted 加进唯一索引或用时间戳替代）。
6. 乐观锁：MyBatis-Plus @Version、JPA @Version、GORM 手动 WHERE version=? + version=version+1。
7. 审计字段：MyBatis-Plus 自动填充、JPA @CreatedDate/@LastModifiedDate、GORM CreatedAt/UpdatedAt 自动维护。
8. SQL 注入：所有主流 ORM 默认参数化；只有动态拼字段名/表名时才有风险，必须白名单。

---

十二、高阶补强：场景题与排查决策树

高阶岗考的不是"知不知道"，是"出事时你能不能定位 + 决策"。下面是高频实战场景的标准答题骨架。

12.1 慢 SQL 排查决策树

第一步：先确认"慢"的边界

• 偶发慢 vs 持续慢？→ 偶发看锁等待/IO 抖动，持续看 SQL 本身。
• 单条慢 vs 整库慢？→ 整库慢先看主机指标（CPU/IO/连接数）。
• 何时开始慢？→ 对比变更：上线、数据量增长、统计信息过期。

第二步：定位 SQL

-- 当前正在执行的慢查询
SELECT * FROM information_schema.processlist WHERE TIME > 5 ORDER BY TIME DESC;

-- 8.0 推荐：performance_schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- 慢查询日志聚合
pt-query-digest /var/log/mysql/slow.log

第三步：拆解单条 SQL（核心决策树）

EXPLAIN 看 type
├── ALL（全表）→ 没用索引 / 索引失效
│   ├── WHERE 上有函数？           → 改写或建函数索引
│   ├── 隐式类型转换？             → 修字段类型或参数类型
│   ├── LIKE '%xx'？               → 反向索引 / 全文索引 / ES
│   └── 优化器估错？               → ANALYZE TABLE 更新统计、FORCE INDEX
├── index（全索引扫描）→ 没用上前缀
│   └── 检查最左前缀、ORDER BY 是否能走索引
├── range/ref → 看 rows 估算
│   ├── 扫描行数远大于返回行数？   → 索引选择性差，加联合索引覆盖
│   └── Extra 有 Using filesort？   → 排序字段加索引
└── eq_ref/const → SQL 没问题，看锁等待 / IO

EXPLAIN ANALYZE 看实际耗时（8.0.18+）

第四步：还慢就看锁和资源

-- 锁等待
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_locks;

-- 看是不是被长事务卡住
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 10;

-- buffer pool 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 命中率 = 1 - (reads / read_requests)，应 > 99%

典型场景速答

现象	多半原因	处理
突然变慢，SQL 没变	数据增长跨过临界点 / 统计信息过期 / 主从切换后冷缓存	ANALYZE TABLE / 预热 buffer pool
偶发尖刺	大事务 / 大查询挤占 IO / checkpoint 刷脏	拆事务、分批、调 innodb_io_capacity
全库变慢	连接数打满 / CPU 打满 / 主从延迟	限流、排查爆量来源
同样的 SQL 时快时慢	优化器选错索引	FORCE INDEX 或重建索引调整选择性
写入忽然变慢	redo log 满等 checkpoint / undo 膨胀	调大 redo log、kill 长事务

12.2 主从延迟应急

延迟 = 主库 binlog 写入速度 > 从库 SQL 线程消费速度。

定位

SHOW SLAVE STATUS\G
-- Seconds_Behind_Master：粗略指标，不准
-- 真实延迟 = master 当前 binlog pos - relay log 已执行 pos

常见原因 → 处理

1. 大事务（最常见）：批量 DELETE / UPDATE → 拆批、pt-archiver 归档。
2. 大表 DDL：用 gh-ost / pt-osc，避免一次性锁。
3. 从库单线程回放：开 slave_parallel_workers + slave_parallel_type=LOGICAL_CLOCK。
4. 从库压力大：读流量过大 → 加从库或拆读流量。
5. 从库无主键表：ROW 模式下回放是全表扫描，必须建主键。
6. 网络抖动：换专线 / 同机房。

写后读一致性方案

• 强一致：写后读走主库（按用户/请求标记）。
• 半同步复制：至少一个从库收到才返回。
• 等待 GTID：写完后从库等到该 GTID 再读。
• 缓存写入结果：直接读自己刚写的 cache。

12.3 死锁排查与防控

现场抓取

SHOW ENGINE INNODB STATUS\G   -- LATEST DETECTED DEADLOCK 段

-- 8.0 持久化
SET GLOBAL innodb_print_all_deadlocks = ON;
-- 错误日志会打印每次死锁

-- 实时锁
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

读懂死锁日志 关键三段：T1 持有什么锁、T1 等待什么锁、T2 持有什么锁。画出循环等待图就能找到根因。

典型死锁模式

1. 加锁顺序不一致：A 转账给 B、B 转账给 A 同时发生 → 按 ID 大小固定顺序加锁。
2. 间隙锁交叉：两事务在同一区间分别 INSERT → 改 RC 隔离级别 / 用唯一索引精确命中。
3. 唯一索引冲突 + 回滚：批量 INSERT 撞唯一键，事务回滚释放锁瞬间被别人抢插 → 改 INSERT IGNORE / UPSERT / 提前查重。
4. 二级索引 + 主键回表：UPDATE 走二级索引拿到主键，再加主键锁；另一事务直接走主键 → 加锁顺序相反。

防控原则

• 事务尽量短、小、快。
• 加锁顺序固定（按主键升序处理批量数据）。
• 索引到位，避免锁升级到全表。
• 业务层重试机制（死锁是 ERROR 1213，可自动重试）。

12.4 大表改造（亿级表加字段/加索引）

禁止：直接 ALTER TABLE，几小时锁表，业务全瘫。

Online DDL 路径

-- 8.0 大部分操作支持 INSTANT（秒级，只改元数据）
ALTER TABLE t ADD COLUMN x INT, ALGORITHM=INSTANT;

-- 不支持 INSTANT 的操作走 INPLACE
ALTER TABLE t ADD INDEX idx_x(x), ALGORITHM=INPLACE, LOCK=NONE;

8.0 INSTANT 支持：加列（末尾）、改默认值、改列名、加/删虚拟列。 INPLACE 不锁表的：加二级索引、列重命名。 仍需拷表的：改主键、改字符集、改列类型（部分）。

工具方案

• pt-online-schema-change：建影子表 + 触发器同步 + chunk 拷贝 + rename。三方写入靠触发器，写压力大时影响主库。
• gh-ost（推荐）：基于 binlog 同步，无触发器，对主库压力小，可暂停可限速。

操作清单

1. 评估表大小、写入 QPS、磁盘空间（要预留 1.2 倍表空间）。
2. 低峰期执行，避开备份窗口。
3. 限速参数：gh-ost --max-load、--throttle-control-replicas。
4. 完成前做切换演练，留 rollback 路径（保留 _old 表 24 小时）。
5. 监控：主从延迟、磁盘 IO、连接数。

12.5 深分页 / 千万级 COUNT / TopN

深分页：LIMIT 1000000, 10 扫 100 万行。

• 游标式：WHERE id > ${last_id} ORDER BY id LIMIT 10，最优。
• 延迟关联：JOIN (SELECT id FROM t LIMIT 1000000, 10) USING(id)，仍要扫但只回表 10 次。
• 业务限制：禁止跳页，只能"上一页/下一页"。

精确 COUNT 慢：InnoDB 没缓存总行数。

• 估算可接受 → SHOW TABLE STATUS 的 Rows（误差 30%+）或 EXPLAIN 的 rows。
• 必须精确 → 维护计数表（按业务分桶减少热点）。
• 条件 COUNT → 覆盖索引 + 二级索引范围。

TopN per group（每组取前 N）

• 8.0 窗口函数：ROW_NUMBER() OVER (PARTITION BY uid ORDER BY t DESC) 配合外层过滤。
• 老版本：JOIN 子查询，或应用层批量。

12.6 缓存与数据库一致性（高阶必考）

Cache Aside（旁路缓存）

• 读：先 cache，miss 再 DB，回填 cache。
• 写：先更新 DB，再删 cache（不是更新 cache）。

为什么删而不更新：

1. 并发更新会乱序覆盖。
2. cache 值常常是 DB 多字段加工后的产物，更新逻辑分散易错。
3. 懒加载，写多读少时省 CPU。

为什么先 DB 再删 cache（不是反过来）：

• 反过来：删 cache 后、写 DB 前，另一线程读到旧 DB 回填 cache → 长期脏数据。
• 正过来的极端 case：A 读 DB 旧值未回填 → B 写 DB 删 cache → A 才回填旧值。概率极低，且可被下次写覆盖。

延迟双删：写 DB → 删 cache → sleep 几百毫秒 → 再删 cache。兜底主从延迟期间的脏读。

强一致方案

• Canal + binlog：订阅 binlog 异步删 cache，业务无侵入，最终一致。生产首选。
• 分布式锁：写时锁 key，串行化，性能差，仅金融级强一致用。
• 2PC/事务消息：复杂，少用。

缓存三连击

问题	含义	方案
穿透	查不存在的数据，每次打 DB	布隆过滤器 / 缓存空值（短 TTL）
击穿	热点 key 过期，瞬间打 DB	互斥锁重建 / 永不过期 + 异步刷新
雪崩	大量 key 同时过期	TTL 加随机扰动 / 多级缓存 / 限流降级

---

十三、高阶补强：InnoDB 深层原理

13.1 行记录格式

InnoDB 行格式：REDUNDANT / COMPACT / DYNAMIC / COMPRESSED，5.7+ 默认 DYNAMIC。

COMPACT 格式结构

[变长字段长度列表] [NULL 值列表] [记录头信息(5 字节)] [列1] [列2] ... [事务ID 6B] [回滚指针 7B]

记录头里的 next_record 指向下一条，构成单向链表（page 内有序）。deleted_flag 标记删除（不立即清理，由 purge 处理）。

行溢出：单行 > 半页（8KB）时，VARCHAR/TEXT/BLOB 大字段存到溢出页（off-page）。

• DYNAMIC：完全存到溢出页，原行只留 20 字节指针 → 推荐。
• COMPACT：原行存 768 字节前缀 + 指针 → 浪费空间。

13.2 Buffer Pool 深入

结构：默认 128MB，生产配 物理内存 50-80%。由多个 chunk 组成（innodb_buffer_pool_chunk_size），多实例（innodb_buffer_pool_instances）减少锁争用。

改造的 LRU：标准 LRU 会被全表扫描污染。InnoDB 把链表分成两段：

• young 区（前 5/8）：热数据。
• old 区（后 3/8）：新加载页先放这。
• 页在 old 区停留超过 innodb_old_blocks_time（默认 1 秒）再被访问，才晋升 young。

效果：全表扫描的页只在 old 区流转，不污染 young 热数据。

关键状态

SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- pages_total / pages_free / pages_dirty
-- read_requests（逻辑读）/ reads（物理读）
-- 命中率 = 1 - reads/read_requests

13.3 Change Buffer

针对非唯一二级索引的写优化。

• 写一条二级索引时，目标页不在 buffer pool？
  • 唯一索引：必须读页判断唯一性 → 不能用 change buffer。
  • 非唯一索引：把"要做的修改"暂存到 change buffer，避免立即随机 IO 读页。
• 后续真要用这个页时再 merge。

适合写多读少的二级索引；读多写少不开。innodb_change_buffer_max_size 控制大小（默认 25%）。

13.4 自适应哈希索引（AHI）

InnoDB 监控热点查询，对频繁访问的索引页自动建内存哈希。等值查询从 B+ 树降级为 O(1)。

• 不等于"哈希索引"，是 B+ 树之上的加速层。
• 高并发等值查询有效，范围查询无收益。
• 内存争用大时可关：innodb_adaptive_hash_index = OFF。

13.5 Redo Log 与 WAL

WAL（Write-Ahead Logging）原则：先写日志再写数据页。

• 修改数据页 = 修改 buffer pool 中的页（变脏页）。
• 同时写 redo log buffer。
• 提交时 fsync redo log，数据页异步刷盘。
• 崩溃恢复：redo log 重放未刷盘的脏页。

redo log 文件：ib_logfile0/1，循环写。innodb_log_file_size 大点能减少 checkpoint 频率（生产 1-4GB 起）。

关键参数

innodb_flush_log_at_trx_commit
  0 = 每秒刷盘，宕机丢 1 秒
  1 = 每次 commit 刷盘（默认，最安全）
  2 = 每次 commit 写 OS 缓存，每秒 fsync

13.6 组提交（Group Commit）

每次 commit 都 fsync 太慢。InnoDB + binlog 的组提交流程：

binlog 三阶段：FLUSH → SYNC → COMMIT
多个事务在 SYNC 阶段合并 fsync，N 个事务 1 次 IO。

调优参数：

• binlog_group_commit_sync_delay：等待多少微秒收集更多事务一起 fsync。
• binlog_group_commit_sync_no_delay_count：满多少事务立即 fsync。

高并发写场景适当调大 delay 能显著降低 IOPS 压力。

13.7 Undo Log 与 Purge

undo log 两个用途：回滚 + MVCC 历史版本。

结构：undo log 存在 undo tablespace 中，按事务组织，构成 history list。

Purge 线程：异步清理"已无任何活跃事务能访问"的旧版本。

• 长事务会拉长 history list，旧版本无法清理 → undo 膨胀，磁盘爆。
• 监控：SELECT * FROM information_schema.innodb_metrics WHERE name='trx_rseg_history_len';
• 阈值经验：> 1000 万 要警惕，> 1 亿 必须处理。

长事务排查

SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS dur
FROM information_schema.innodb_trx
ORDER BY trx_started LIMIT 10;

13.8 Double Write Buffer

问题：MySQL 页 16KB，OS 页 4KB，写一个页要 4 次 OS 写。如果中途断电，部分页损坏（partial page write），redo log 救不回（redo 是物理逻辑日志，需要原始页）。

方案：每次刷脏页前，先把页顺序写入 double write buffer（共享表空间），再写到目标位置。崩溃恢复时若发现页损坏，从 double write 恢复。

代价：写放大 2 倍（但 double write 是顺序写，开销小）。SSD + 文件系统支持原子写时可关。

13.9 一条 UPDATE 的完整生命周期（高阶版）

Server 层:
1. 连接器：鉴权 / 维护连接
2. 分析器：词法 + 语法
3. 优化器：选索引、JOIN 顺序
4. 执行器：调用引擎接口

InnoDB 层:
5. 从 buffer pool 读页（不在则磁盘加载）
6. 写 undo log（生成历史版本）
7. 修改 buffer pool 中的页（变脏页）
8. 写 redo log buffer，状态 = prepare

Server 层:
9. 写 binlog cache
10. binlog fsync 到磁盘

InnoDB 层:
11. redo log 写 commit 标记
12. 后续：脏页异步刷盘、purge 清理 undo

每一步都可能成为面试追问点：buffer pool 怎么淘汰？redo log 满了怎么办？binlog 没写完崩溃怎么恢复？

---

十四、高阶补强：分布式事务与一致性

14.1 分布式事务方案对比

方案	一致性	性能	复杂度	适用场景
2PC / XA	强	差（同步阻塞）	低	跨库强一致，少用
TCC	强	中	高（每个服务三接口）	金融、账务
Saga	最终	好	中（补偿链）	长流程业务（订单履约）
本地消息表	最终	好	中	跨库异步通知
事务消息（RocketMQ）	最终	好	低	解耦的跨服务
Seata AT	强（伪）	中	极低（无侵入）	业务快速接入

14.2 TCC 详解

每个服务实现三个接口：

• Try：预留资源（冻结库存、冻结金额）。
• Confirm：真正提交（扣减冻结）。
• Cancel：回滚（解冻）。

三大坑

1. 空回滚：Try 失败后 Cancel 被调用，但 Try 根本没执行。要在 Cancel 里判断 Try 是否成功过。
2. 悬挂：Cancel 先到、Try 后到。要在 Try 里检查是否已 Cancel。
3. 幂等：Confirm/Cancel 都可能重试，必须幂等（事务记录表 + 状态机）。

14.3 本地消息表

最实用的最终一致性方案。

CREATE TABLE local_message (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    biz_id VARCHAR(64),
    payload JSON,
    status TINYINT,         -- 0:待发 1:已发 2:已确认
    retry_count INT,
    next_retry_time DATETIME,
    create_time DATETIME
);

流程：

1. 业务表 + 消息表在同一本地事务写入。
2. 异步任务扫描 status=0 的消息发 MQ。
3. 消费者处理完回调或 ACK，更新为 status=2。
4. 长时间未确认的重试，超阈值告警人工介入。

优势：依赖少、可靠、易理解。注意：消息表大了要分区或归档。

14.4 Seata AT 模式原理

Seata 自动接管 SQL，业务无感知：

1. 解析 SQL，生成 before image。
2. 执行 SQL，生成 after image。
3. 写 undo_log 表（业务库内）。
4. 注册分支事务到 TC（Seata Server）。
5. 全局提交：TC 通知所有分支异步删 undo_log。
6. 全局回滚：用 undo_log 反向补偿。

全局锁：Seata 用全局锁保证隔离性，写写冲突时排队 → 高并发下是瓶颈，热点账户场景慎用。

14.5 事务消息（RocketMQ）

1. 生产者发"半消息"到 Broker（消费者不可见）
2. 执行本地事务
3. 提交：Broker 把半消息变正式消息；回滚：丢弃
4. 长时未提交：Broker 回查生产者，生产者根据本地事务结果决定提交/回滚

适合：跨服务异步通知、需要可靠投递、业务方愿意实现回查接口。

14.6 幂等设计

分布式系统所有写操作都该幂等，因为重试不可避免。

手段

1. 唯一键：业务流水号入库唯一索引，重复插入失败即可。
2. Token / 防重表：客户端先取 token，操作时带上，服务端校验 + 删除。
3. 状态机：只允许特定状态转移，重复请求被拒。
4. 乐观锁：UPDATE ... WHERE version=?，重复请求 version 已变，update 0 行。
5. 幂等键 + 结果缓存：相同 key 直接返回上次结果。

14.7 分布式 ID

方案	优点	缺点
UUID	全局唯一、无中心	无序、长、索引差
雪花算法	趋势递增、64bit	时钟回拨、机器ID 分配
号段模式（Leaf）	DB 兜底、可批量	强依赖 DB
Redis incr	简单	持久化与可用性需保证
TiDB / DB 自增	简单	跨实例需协调

雪花算法时钟回拨处理：

• 拒绝服务（等到 NTP 校正）。
• 用上次最大 ID + 1（牺牲严格递增）。
• 美团 Leaf-snowflake 用 ZK 校验时钟。

14.8 CAP / BASE / 一致性级别

• CAP：一致性 / 可用性 / 分区容忍。分区下二选一（CP 或 AP）。
• BASE：基本可用、软状态、最终一致。多数互联网业务选 BASE。
• 一致性级别：强一致 > 顺序一致 > 因果一致 > 最终一致。
  • 写后读、单调读、单调写、读己之写——这四个一致性是工程上最常见的子需求，不一定要全局强一致。

---

十五、高阶补强：架构演进与真实 Case Study

高阶面试必问："讲一个你做过的最复杂的数据库优化"。下面提供 case 模板和高频题答题骨架。

15.1 Case Study 答题模板（STAR-R）

S（背景）：业务规模、QPS、数据量、痛点指标。 T（目标）：可量化的目标（P99 从 2s 降到 200ms / 容量支撑 10x 增长）。 A（方案）：候选方案对比 + 选择理由 + 取舍。 R（结果）：上线后数据 + 复盘。 R（反思）：如果重做会怎么改？

高阶面试官最看重最后一个 R。能讲清"我当时做错了什么"比"我有多牛"更值钱。

15.2 经典 case 1：订单表分库分表

背景：订单表 5 亿行 200GB，主键查询 P99 600ms，月增 5000 万。

思考过程

• 为什么不能先靠垂直拆？— 已经拆过订单详情。
• 分片键选 user_id 还是 order_id？
  • user_id：按用户聚合查询友好，但商家维度查询要扫所有分片。
  • order_id（含 user_id 后缀位）：兼顾两边。
• 几库几表？— 16 库 × 64 表 = 1024 表，按月增长可用 5 年。
• 历史数据迁移？— 双写 + 全量 + 校验 + 切流。
• 跨片查询？— 商家维度走 ES 异构索引；分页禁止深翻。
• 全局 ID？— 雪花算法，bit 位预留分片信息。

结果：P99 60ms，单表 < 500 万行，扩容预案文档化。 反思：商家维度查询低估了，应一开始就上 ES。

15.3 经典 case 2：缓存击穿 + DB 雪崩

背景：促销秒杀，热点商品 cache miss 瞬间打挂 DB，500 错误率 30%。

链路分析

1. 热 key TTL 到期 → 大量请求穿透到 DB。
2. DB 连接打满 → 慢 SQL 堆积 → 连接池耗尽 → 全站雪崩。

改造

1. 互斥锁重建：cache miss 时只允许一个线程查 DB 回填，其他等待。
2. 逻辑过期：cache 永不物理过期，过期时间存 value 内，过期则异步刷新。
3. 多级缓存：本地 caffeine + Redis，本地兜底 5 秒。
4. 熔断降级：DB 慢时返回兜底页。
5. 限流：Sentinel 按 user 维度限流。

15.4 经典 case 3：长事务导致 undo 膨胀

现象：磁盘空间一周从 50% 涨到 90%，DB 整体变慢。

定位

SELECT trx_id, trx_started, TIMESTAMPDIFF(MINUTE, trx_started, NOW()) AS dur_min
FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 5;

发现一个事务跑了 18 小时——某离线任务忘了 commit。

处理

1. 紧急 KILL 该事务。
2. 等 purge 追上，history list 降下来。
3. 整改：
   • 应用代码强制事务超时（Spring @Transactional(timeout=30)）。
   • 监控加报警：innodb_trx 中超过 5 分钟的事务直接告警。
   • 离线任务禁用大事务，强制分批 commit。

15.5 高阶高频问答骨架

Q: 你怎么设计一个支持百万 QPS 的读服务？

• 读写分离 + 多从库（解决并发）
• 多级缓存（解决吞吐）：本地 + Redis + DB
• 数据预热（避免冷启动）
• 异构存储：ES 做检索、HBase 做明细
• 限流降级（保护下游）
• 监控全链路 P99

Q: 千万级表加一列怎么操作？ 8.0 INSTANT 直接加；不支持就 gh-ost；评估磁盘空间、锁表风险、主从延迟、回滚路径。

Q: 主库挂了怎么办？ 高可用方案选型：MHA / Orchestrator / MGR / 云厂商。

• 选举新主 → 数据补齐 → VIP/DNS 切换 → 应用重连。
• 关注：脑裂、数据丢失（半同步缓解）、切换时长。

Q: 跨机房如何部署？

• 同城双活：双向同步 + 单元化分流。
• 异地多活：单元化 + 全局路由 + 数据按维度切分。
• 异地灾备：异步复制 + 演练切换。

Q: 用过哪些数据库？怎么选？

• OLTP：MySQL / PostgreSQL / TiDB（HTAP / 水平扩展）
• 文档：MongoDB（灵活 schema、地理位置）
• KV：Redis / TiKV
• 搜索：ES（全文、聚合）
• 分析：ClickHouse / Doris / Hive
• 时序：InfluxDB / TDengine
• 图：Neo4j / Nebula

选型四维：数据模型 / 一致性要求 / 读写比 / 数据规模。

15.6 视野：新一代数据库的差异化

• TiDB：MySQL 协议兼容，存算分离（TiKV + TiDB），Raft 多副本，HTAP（TiFlash 列存）。适合需要水平扩展的 MySQL 用户。
• OceanBase：阿里自研，分布式事务强一致，金融级。
• PolarDB：共享存储 + 计算节点池，读节点秒级扩展。
• Aurora（AWS）：redo log 下沉到存储层，6 副本 4/6 quorum。

共同趋势：存算分离、多副本一致性协议（Paxos/Raft）、Serverless、HTAP。面试时能聊一两个，体现技术敏感度。

---

十六、面试组织建议

16.1 回答框架

中级回答用 是什么 → 为什么 → 怎么实现 即可。 高阶回答必须再加 怎么用 + 踩过的坑 + 对比/取舍 + 反思：

1. 是什么：定义（30 秒）。
2. 为什么：解决什么问题（这步体现思考深度）。
3. 怎么实现：原理（B+ 树、MVCC、2PC、组提交…）。
4. 怎么用：自己项目里真实用过的场景。
5. 踩过的坑：具体故障 + 怎么定位 + 怎么修。
6. 对比 / 取舍：与相近方案的差异，为什么选 A 不选 B。
7. 反思：如果重做会改什么 / 这个方案的边界在哪。

中级 vs 高阶最大区别：高阶要有"边界感"——知道方案在什么场景下不成立。

16.2 高阶高频组合题（建议都能讲 5 分钟以上）

索引族

• 为什么 B+ 树而非 B 树/红黑树/Hash？→ 页结构、聚簇/二级、覆盖、最左前缀、ICP。
• 联合索引 (a,b,c) 怎么命中？查询优化器为什么有时不走索引？
• 千万级表加索引怎么不影响业务？→ Online DDL / gh-ost。

事务与隔离族

• RR 怎么解决幻读？→ MVCC（快照读）+ Next-Key Lock（当前读）。RC 为什么不行？
• ReadView 在 RR 和 RC 下的差异？长事务为什么是噩梦？→ history list 膨胀。
• 为什么需要两阶段提交？→ redo 与 binlog 一致性 → 崩溃恢复逻辑。

锁族

• 给我一个 SQL，分析它在 RR 下加什么锁。
• 死锁怎么排查？给一个真实死锁场景。
• 间隙锁怎么设计能避免？为什么生产不少人选 RC？

性能族

• 慢 SQL 排查决策树（讲 12.1）。
• 主从延迟从 0 涨到 30 分钟，怎么救？
• 一条 update 的完整生命周期（讲 13.9，对比基础版能多讲 buffer pool/change buffer/double write/group commit/purge）。
• 深分页 / 千万级 COUNT 怎么优化。

架构族

• 你怎么从单库演进到分库分表？（拿 case 15.2 讲）
• 缓存与 DB 一致性方案对比，你们用哪种？为什么？
• 分布式事务方案对比，金融场景选哪个？
• 库存扣减强一致 vs 高并发怎么平衡？

视野族

• TiDB 为什么能水平扩展？跟 MySQL 分库分表比优劣？
• 你最近关注的数据库领域新东西是什么？
• 你们 DB 监控关键指标有哪些？为什么是这些？

16.3 反向提问（高阶岗的隐藏加分项）

面试快结束时面试官说"你有什么问题问我吗"，别问待遇。问这些显示你懂行：

• 你们当前数据库架构？单库还是分布式？
• 主从延迟容忍度多少？怎么处理写后读？
• 慢 SQL 治理流程是什么样的？谁负责？
• 大表 DDL 走什么流程？有 SOP 吗？
• 当前最大的数据库技术债是什么？

这些问题直接展示你思考过 DB 治理而不只是会写 SQL。