数据库三剑客 · 第4节 · MySQL 完整章节

MySQL 完整章节

MySQL 8.4 LTS · From Install to Replication

MySQL 是三家里最适合入门、也最常见的一家。互联网公司八成以上的业务库都是它。这一章我们按"装环境 → 写 SQL → 建索引 → 搞事务 → 写存储过程 → 备份 → 主从 → 面试题"的顺序，一路打穿。

2.1 MySQL 简介与安装

引子：1995 年一个瑞典公司做了个"轻量版 SQL"，本来只想当个配角，结果因为开源 + 好用 + 快，被互联网大潮一把推上神坛。它后来被 Sun 收购，Sun 又被 Oracle 收购，所以现在 MySQL 和 Oracle 是一家人——但脾气完全不同。

是什么（大白话）：MySQL 是一个"把数据按表存好、支持 SQL 查询"的服务。你在自己电脑上装一个，它就一直占着 3306 端口听你指挥；你的程序通过网络把 SQL 发给它，它把结果再吐回来。

Docker 一行起 MySQL 8.4（最推荐，干净可复现）

# 拉官方镜像，起一个容器，设置 root 密码，把 3306 端口映射出来
docker run -d --name mysql8 \
  -e MYSQL_ROOT_PASSWORD='YourPass_2026' \
  -p 3306:3306 \
  --restart unless-stopped \
  mysql:8.4

$ docker ps CONTAINER ID IMAGE STATUS PORTS NAMES a1b2c3d4e5f6 mysql:8.4 Up 10 seconds 0.0.0.0:3306->3306/tcp mysql8

Windows / macOS / Linux 原生安装

# macOS（用 Homebrew）
brew install mysql
brew services start mysql

# Ubuntu / Debian（apt）
sudo apt update && sudo apt install -y mysql-server
sudo systemctl start mysql

# Windows：去 dev.mysql.com 下 MSI 安装包，一路下一步，记得设 root 密码

my.cnf 关键配置（大小写不敏感、字符集 utf8mb4）

# /etc/mysql/my.cnf 或 macOS 的 /opt/homebrew/etc/my.cnf
[mysqld]
port = 3306
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci
default-storage-engine = InnoDB
max_connections = 500
slow_query_log = 1
long_query_time = 1   # 超过 1 秒的 SQL 就记到慢查询日志

为什么必须是 utf8mb4，不是 utf8

MySQL 里那个 utf8 是个残缺的假 utf8，最多存 3 个字节，存不下 emoji（比如 4 字节的表情符号字符），一插入就乱码或报错。真正的 UTF-8 全家桶叫 utf8mb4。建库建表永远写 utf8mb4，别再用 utf8。这是 MySQL 出了名的历史坑。

客户端工具：命令行 mysql / Workbench / DBeaver

# 命令行连上本机 MySQL
mysql -h 127.0.0.1 -P 3306 -u root -p

# 进去以后看看有哪些库
mysql> SHOW DATABASES;

图形客户端三个里挑一个：MySQL Workbench（官方）、DBeaver（开源、一家通吃三家）、Navicat（付费，好看）。初学推荐 DBeaver，后面学 Oracle、PostgreSQL 也用它，一份配置三家通用。

mysql 命令行常用命令

mysql> SHOW DATABASES; <-- 列库 mysql> USE shop; <-- 切库 mysql> SHOW TABLES; <-- 列表 mysql> DESCRIBE employees; <-- 看表结构 mysql> SHOW CREATE TABLE employees\G <-- 看建表语句 mysql> EXIT; <-- 退出

2.2 SQL 基础：DDL / DML / DQL 一把梭

引子：SQL 看着像英语，其实它是数据库的方言。三大门派：DDL（改结构）、DML（改数据）、DQL（查数据）。你日常 80% 的时间在写 DQL。

DDL：建库、建表、改结构

建库 + 建一张员工表

CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE shop;

CREATE TABLE employees (
  id          BIGINT       PRIMARY KEY AUTO_INCREMENT,
  name        VARCHAR(50)  NOT NULL,
  age         INT,
  salary      DECIMAL(10,2) DEFAULT 0.00,
  gender      ENUM('M','F','U') DEFAULT 'U',
  hire_date   DATE,
  resume      TEXT,
  ext_info    JSON,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

常用 MySQL 数据类型，照抄即可
类型	什么时候用
`INT / BIGINT`	整数。主键自增永远用 `BIGINT`（INT 上限约 21 亿，业务大了会爆）。
`VARCHAR(n)`	变长字符串，n 是字符数。姓名、标题这种。
`TEXT`	长文本，最多 64KB，文章正文。
`DECIMAL(10,2)`	精确小数，钱必须用它，别用 FLOAT/DOUBLE（有精度误差）。
`DATE / DATETIME`	DATE 只有年月日；DATETIME 到秒。MySQL 8 还有 `DATETIME(6)` 到微秒。
`ENUM`	枚举，性别、状态这种固定几个值。
`JSON`	存 JSON 文档，8.0 后可以对 JSON 字段建索引、查内部字段。

ALTER / DROP：改表结构

# 加一列
ALTER TABLE employees ADD COLUMN city VARCHAR(30);
# 改列类型
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2);
# 删一列（危险！先备份）
ALTER TABLE employees DROP COLUMN resume;
# 删表 / 删库（极度危险，三思）
DROP TABLE employees;

DML：增、改、删

INSERT / UPDATE / DELETE

# 插入一行
INSERT INTO employees (name, age, salary, gender, hire_date)
VALUES ('张三', 28, 18000.00, 'M', '2023-03-01');

# 一次插多行
INSERT INTO employees (name, age, salary, gender)
VALUES ('李四', 32, 25000, 'F'),
        ('王五', 45, 42000, 'M');

# 改：把张三涨薪 10%
UPDATE employees SET salary = salary * 1.1 WHERE name = '张三';

# 删：删王五（不带 WHERE 的 DELETE 会删全表！）
DELETE FROM employees WHERE name = '王五';

不带 WHERE 的 UPDATE / DELETE 是事故制造机

在线上库，UPDATE employees SET salary = 0; 这种没带 WHERE 的语句会把全表掀翻。养成肌肉记忆：写 UPDATE/DELETE 之前，先把同样条件写成 SELECT 查一遍，确认影响的就是那几行，再改成 UPDATE/DELETE。生产上永远先用事务包起来，ROLLBACK 兜底。

DQL：查询，SQL 的灵魂

WHERE / ORDER BY / GROUP BY / HAVING / LIMIT / DISTINCT

# 查月薪大于 2 万的男员工，按工资降序，取前 10 条
SELECT name, age, salary
FROM employees
WHERE salary > 20000 AND gender = 'M'
ORDER BY salary DESC
LIMIT 10;

# 分组：每个城市平均工资（HAVING 是分组后再过滤）
SELECT city, COUNT(*) AS cnt, ROUND(AVG(salary), 2) AS avg_sal
FROM employees
GROUP BY city
HAVING AVG(salary) > 20000;

# DISTINCT：去重
SELECT DISTINCT city FROM employees;

上面分组查询的实际输出

mysql> SELECT city, COUNT(*) cnt, ROUND(AVG(salary),2) avg_sal FROM employees GROUP BY city HAVING AVG(salary)>20000; +--------+-----+----------+ | city | cnt | avg_sal | +--------+-----+----------+ | 南京 | 12 | 23500.00 | | 上海 | 8 | 28750.50 | +--------+-----+----------+

问GROUP BY 后面的执行顺序是什么

SQL 书写顺序是 SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT，但执行顺序其实是：先 FROM 找表 → WHERE 过滤行 → GROUP BY 分组 → HAVING 过滤组 → SELECT 算列 → ORDER BY 排序 → LIMIT 截取。记住这个，就明白为什么 WHERE 里不能用聚合函数（那时还没分组），而 HAVING 里可以。

连接查询 JOIN：把多张表拼起来

业务数据从来不是一张表，而是拆成几十张表。JOIN 就是按某个共同字段把表"缝"起来。最常用的三种：

# 准备两张表：员工表 employees 有 dept_id，部门表 departments 有 id 和 name

# INNER JOIN：只保留两边都匹配的（最常用）
SELECT e.name, e.salary, d.name AS dept
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

# LEFT JOIN：左表全保留，右表没匹配的补 NULL
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

# 自连接：员工表里有 manager_id 指向同表的 id，查"谁的老板是谁"
SELECT w.name AS worker, m.name AS manager
FROM employees w
LEFT JOIN employees m ON w.manager_id = m.id;

三种 JOIN 一看就懂
JOIN 类型	含义（大白话）
`INNER JOIN`	两边都对上的才留。像"交集"。
`LEFT JOIN`	左表全留，右表对不上补 NULL。查"所有员工及其部门，没部门的员工也列出"。
`RIGHT JOIN`	和 LEFT 反过来。实际写得少，把表换个顺序用 LEFT 就行。

一条 LEFT JOIN 的实际返回（看 NULL 的位置就懂了）

mysql> SELECT e.name, d.name AS dept FROM employees e LEFT JOIN departments d ON e.dept_id = d.id; +--------+--------+ | name | dept | +--------+--------+ | 张三 | 研发部 | | 李四 | 研发部 | | 王五 | NULL | <-- 没部门，右表补 NULL +--------+--------+

子查询、UNION、常用函数

子查询：把一个查询的结果当条件

# 工资大于公司平均工资的人
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

# IN：在某个集合里
SELECT * FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE city = '南京');

# EXISTS：相关子查询，对外层每行跑一次
SELECT d.name FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

# UNION：把两个结果上下拼起来（自动去重；UNION ALL 不去重更快）
SELECT name FROM employees WHERE city='南京'
UNION
SELECT name FROM contractors WHERE city='南京';

UNION 和 UNION ALL 的性能差

UNION 会把两个结果合并后去重（要排序/哈希），UNION ALL 直接上下拼接，快得多。如果业务上知道不会重复，或者重复也无所谓，永远用 UNION ALL，别让数据库白跑去重。

常用函数速查

-- 字符串
CONCAT(first_name, ' ', last_name)   -- 拼接
UPPER(name), LOWER(name)              -- 大小写
SUBSTRING(name, 1, 3)               -- 截取
TRIM(name)                          -- 去空格

-- 日期
NOW(), CURDATE()
DATE_FORMAT(hire_date, '%Y-%m')
DATEDIFF(NOW(), hire_date)          -- 相差天数

-- 数学与聚合
COUNT(*), SUM(salary), AVG(salary), MAX(salary), MIN(salary)

-- 流程控制
IF(salary > 30000, '高', '普通')
CASE WHEN salary > 50000 THEN 'S'
     WHEN salary > 30000 THEN 'A'
     ELSE 'B' END
COALESCE(nickname, name)            -- 取第一个非 NULL

窗口函数：不折叠行的"高级分组"

普通的 GROUP BY 会把多行压成一行；窗口函数不一样——它给每一行旁边附上一个"分组统计值"，但保留每一行本身。这就是为什么它能轻松写出"部门内排名""和上一行比涨跌"这种查询。MySQL 8.0 才原生支持。

窗口函数三件套：分区 + 排序 + 函数

# 语法：函数() OVER (PARTITION BY 分组列 ORDER BY 排序列)
SELECT name, dept, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rk,
       LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_sal
FROM employees;

# 常用窗口函数：
# ROW_NUMBER()  每行一个连续序号（1,2,3）
# RANK()        并列同名次，后面跳号（1,1,3）
# DENSE_RANK()  并列同名次，不跳号（1,1,2）
# LAG(列,n)     取往上第 n 行的值（环比）
# LEAD(列,n)    取往下第 n 行的值
# SUM() OVER()  累计求和（累加余额那种）

排名三兄弟的区别（工资相同时）
函数	并列时的名次序列
`ROW_NUMBER()`	工资相同也强行排 1,2,3
`RANK()`	并列第 1，下一个是第 3（1,1,3）
`DENSE_RANK()`	并列第 1，下一个是第 2（1,1,2）

小练习（折叠看答案）

练习 1：写 SQL，查出每个部门工资最高的员工姓名和工资。

查看答案

SELECT dept_id, name, salary FROM employees e WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id); 或者用窗口函数 ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) 取 rn=1。

练习 2：WHERE salary != 0 和 WHERE salary <> 0 有区别吗？

查看答案

功能上没区别，!= 和 <> 都是"不等于"。但注意：如果 salary 可能是 NULL，这两个条件都不会把 NULL 算进来，因为 NULL 参与任何比较结果都是 NULL。要包含"工资未知"的行得写 salary != 0 OR salary IS NULL。

CTE（WITH 公用表表达式）：把复杂查询拆成积木

引子：一个 SQL 套了四五层子查询，SELECT ... FROM (SELECT ... FROM (SELECT ...))，看一眼就头大，根本不知道哪层在算什么。CTE（Common Table Expression，公用表表达式）就是用 WITH 先把每一步中间结果起个名字，再像搭积木一样拼起来。MySQL 8.0 才支持，和窗口函数是同期新特性。

论CTE 和子查询、临时表的区别

普通子查询是"写在里面"的，嵌套深了没法读；CTE 是"写在前面"的，一段一段列出来，从上往下读就像看剧本。它不是真的建了一张表，只是给一段 SELECT 起个临时名字，只在这条语句里有效，语句结束就消失。好处：① 可读性暴增；② 同一个 CTE 能在主查询里被引用多次，不用复制粘贴；③ 还能递归。

普通 CTE：用 WITH 把"部门平均工资"起个名字

# 查出每个部门里，工资高于本部门平均工资的员工
WITH dept_avg AS (
    # 第一步：先算出每个部门的平均工资，起名叫 dept_avg
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept_id
)
# 第二步：把员工表和刚算好的平均工资表 JOIN 起来
SELECT e.name, e.dept_id, e.salary, d.avg_sal
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal
ORDER BY e.dept_id;

# 不写 WITH 的话，这段 JOIN 条件里就得塞一坨子查询，又长又难读 # WITH 版本：第一步干嘛、第二步干嘛，一目了然

多个 CTE：逗号隔开，一步步搭

# 先算每个部门工资最高的人，再和公司总平均比
WITH
top_per_dept AS (
    SELECT dept_id, MAX(salary) AS top_sal
    FROM employees GROUP BY dept_id
),
company_avg AS (
    SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT t.dept_id, t.top_sal, c.avg_sal
FROM top_per_dept t CROSS JOIN company_avg c
WHERE t.top_sal > c.avg_sal;

递归 CTE：查树形结构（部门层级、菜单、评论楼中楼）

# 组织架构是一棵树：emp 表里 manager_id 指向自己的上级
# 从 CEO（id=1）出发，一层层往下找所有下属
WITH RECURSIVE org_tree AS (
    # 锚点成员：先找起点（CEO）
    SELECT id, name, manager_id, 1 AS lvl
    FROM employees WHERE id = 1
    UNION ALL
    # 递归成员：把上一层的结果再和员工表 JOIN，找到下一层
    SELECT e.id, e.name, e.manager_id, o.lvl + 1
    FROM employees e
    JOIN org_tree o ON e.manager_id = o.id
)
# 把整棵树拉出来（MySQL 里字符串拼接用 CONCAT，不是 ||）
SELECT lvl, CONCAT(REPEAT(' ', (lvl-1)*2), name) AS tree FROM org_tree;

# 输出像一棵树： lvl tree 1 CEO 2 技术总监 3 后端组长 3 前端组长 2 市场总监

递归 CTE 必须防死循环

递归 CTE 最容易踩的坑是环路。如果数据里出现 A 的上级是 B、B 的上级又是 A，递归就死循环了。生产上：① 用 RECURSIVE 关键字写清楚；② 数据要保证是真的树，别成环；③ 复杂树查询也可以考虑用 cte_max_recursion_depth 限制深度兜底。另外 UNION ALL 去不去重要想清楚：树状遍历用 UNION ALL，去重用 UNION。

CTE 小练习（折叠看答案）

练习 1：CTE 和临时表 CREATE TEMPORARY TABLE 有什么区别？

查看答案

CTE 是纯逻辑的，只在单条 SQL 里有效，不真正落盘；临时表会真建一张表，跨语句存在直到连接断开。复杂多步分析且要跨多条语句复用才用临时表；单条语句内分段就用 CTE，更轻。

练习 2：递归 CTE 想查"每个员工的所有下属（不限层级）"，锚点成员应该怎么写？

查看答案

锚点是起点员工（WHERE id = 某人），递归成员用 JOIN org_tree ON emp.manager_id = org_tree.id 不断往下找。每递归一层 lvl+1，最后整条 CTE 就是从这个人往下的整棵树。

2.3 索引原理与优化：为什么查询能从 10 秒变 10 毫秒

引子：一张表有 1000 万行数据，WHERE id = 9527 却只要 0.001 秒。它怎么做到的？答案就是索引。索引就是书的目录——没有目录你得一页页翻（全表扫描），有了目录直接翻到那一页。

论B+ 树索引，InnoDB 的命根子

MySQL InnoDB 默认用 B+ 树组织索引。想象一棵多层的家谱树：最上层是粗略的"目录的目录"，中间层不断分叉，最底层的叶子节点才真正存数据（或指向数据的指针），而且叶子之间用双向链表串起来。这带来两个好处：① 按主键精确查，树高三四层就到了，1000 万行也只查 3~4 次磁盘；② 叶子链表天然支持范围查询（BETWEEN、ORDER BY），顺着链表走就行。

聚簇索引 vs 非聚簇索引：聚簇索引的叶子节点直接存整行数据（一张表只有一个，就是主键）；非聚簇索引（二级索引）的叶子节点存的是主键值，查到后还要回表再去聚簇索引拿整行。如果查询需要的列二级索引里全有，就不用回表——这叫覆盖索引。

建索引的常用姿势

# 单列索引
CREATE INDEX idx_name ON employees(name);
# 联合索引（最左前缀！下面细讲）
CREATE INDEX idx_dept_age ON employees(dept_id, age);
# 唯一索引：值不能重复，且更快
CREATE UNIQUE INDEX uk_phone ON users(phone);
# 全文索引：对长文本做关键词搜索
CREATE FULLTEXT INDEX ft_content ON articles(content);

# 哈希索引：Memory 引擎才用，InnoDB 的自适应哈希是内部自动的，不用手动建

联合索引的"最左前缀原则"

建了 INDEX(a, b, c)，它是按 a 排、a 相同再按 b、b 相同再按 c 排的。所以查询条件必须从最左边开始连续命中才能用上这个索引：WHERE a=1、WHERE a=1 AND b=2、WHERE a=1 AND b=2 AND c=3 都行；但 WHERE b=2 或 WHERE c=3 直接废掉这个索引。这是面试必考、线上常踩的点。

索引失效的经典场景（背下来）

# 1. 对索引列用函数/运算，索引就废了
WHERE YEAR(hire_date) = 2023          -- 失效！改成范围：WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01'
WHERE id + 1 = 100                   -- 失效！改成 WHERE id = 99

# 2. 隐式类型转换：字符串列不小心当数字查
# phone 是 VARCHAR，下面写法会让 MySQL 把每一行都转成数字再比，索引失效
WHERE phone = 13800001111           -- 失效！应该 WHERE phone = '13800001111'

# 3. != / NOT IN / OR（OR 两边有一边没索引也可能废）
WHERE status != 'X'                    -- 经常全表扫

# 4. 前导模糊匹配 %xx
WHERE name LIKE '%张'                 -- 失效！'张%' 能用，'%张%' 基本废

EXPLAIN：SQL 的体检报告

一条 SQL 慢，先在前面加 EXPLAIN，看它到底怎么执行的。EXPLAIN 就是 SQL 的体检报告。重点看四列：

EXPLAIN 一条查询

EXPLAIN SELECT * FROM employees WHERE dept_id = 3 AND age > 30;

id select_type table type possible_keys key rows Extra 1 SIMPLE employees ref idx_dept_age idx_dept_age 120 Using where

EXPLAIN 关键列怎么看
列	怎么解读
`type`	访问类型，从好到差：`const > eq_ref > ref > range > index > ALL`。看到 `ALL` 就是全表扫描，要优化。至少得是 `range/ref`。
`key`	实际用上的索引。如果是 NULL，说明没走索引。
`rows`	预估要扫多少行，越大越慢。
`Extra`	`Using index` 是覆盖索引（好事）；`Using filesort`、`Using temporary` 是要警惕的坏味道。

慢查询日志 + 5 个真实优化案例

慢查询日志把超过某阈值（比如 1 秒）的 SQL 自动记下来，是线上找慢 SQL 的抓手。配合 mysqldumpslow 或 pt-query-digest 统计 Top N。

案例 1：SELECT * 改覆盖索引 → 减少回表

原来 SELECT * FROM orders WHERE user_id=? 走 user_id 索引但要回表拿全部列。如果业务只要 order_id 和 amount，就把这俩列做进联合索引，变覆盖索引，免回表。

案例 2：分页深翻页 LIMIT 1000000, 20 → 延迟关联

深分页会先扫描 100 万行再丢掉。改成先 WHERE id > 上次最大id LIMIT 20（游标分页），或用子查询先用覆盖索引找到 20 个主键再回表。

案例 3：JOIN 字段字符集不一致 → 建索引失效

两表 JOIN 的字段一个 utf8mb4、一个 utf8，MySQL 被迫每行做转换，索引全废。统一字符集即可。

案例 4：OR 拆 UNION ALL → 让两边都能用索引

WHERE a=1 OR b=2 若 a、b 各有索引却没合并好，拆成 (WHERE a=1) UNION ALL (WHERE b=2) often 更快。

案例 5：小表驱动大表 → EXISTS 替代 IN

外层结果集小、内层大时用 EXISTS；外层大内层小时用 IN。别机械背，用 EXPLAIN 看 rows 估算。

2.4 事务与隔离级别：要么全做，要么全不做

引子：你转账：A 扣 100，B 加 100。如果第一条成功、第二条数据库崩了，钱就凭空消失了。事务就是"要么两步都成，要么都别做"的承诺。银行、电商扣库存全靠它。

论ACID，事务的四根柱子

原子性 Atomicity：事务里的操作是一体的，要么全做，要么全不做（靠 undo log 回滚）。

一致性 Consistency：事务前后，数据库从一个合法状态变到另一个合法状态（转账前后总额不变）。

隔离性 Isolation：多个事务并发跑，互相不随便串味（下面隔离级别细说）。

持久性 Durability：事务一旦提交，就算断电也不能丢（靠 redo log 落盘）。

事务控制语句

START TRANSACTION;          -- 或 BEGIN; 开启事务

UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

-- 两条都成功就 COMMIT，任何一条出问题就 ROLLBACK
COMMIT;
-- ROLLBACK;

# SAVEPOINT：事务里打个点，可以只回滚到某个点
SAVEPOINT sp1;
-- ...出错了... 
ROLLBACK TO SAVEPOINT sp1;

事务在命令行里长什么样

mysql> START TRANSACTION; Query OK, 0 rows affected mysql> UPDATE account SET balance=balance-100 WHERE id=1; Query OK, 1 row affected mysql> COMMIT; <-- 成功才提交；出错就 ROLLBACK Query OK, 0 rows affected

四种隔离级别与三大并发问题

隔离级别越高越安全，但并发越差（MySQL 默认可重复读）
隔离级别	脏读	不可重复读	幻读
读未提交 READ UNCOMMITTED	会出现	会出现	会出现
读已提交 READ COMMITTED	不会	会出现	会出现
可重复读 REPEATABLE READ（MySQL 默认）	不会	不会	基本不会（InnoDB 用间隙锁解决）
串行化 SERIALIZABLE	不会	不会	不会

三个"读"的病：脏读——读到了别人还没提交、随时会回滚的数据；不可重复读——同一事务内两次读同一行，值被别人改了；幻读——同一事务内两次按条件查，行数变了（别人插/删了行）。

论MVCC：MySQL 并发读不堵写的秘诀

InnoDB 用 MVCC（多版本并发控制）实现"读不堵写、写不堵读"。每一行背后留着多个历史版本（存在 undo log 里），读的时候根据 read view（事务开始时拍的一张"快照"）决定你能看到哪个版本。这样普通 SELECT 是快照读，不加锁；只有 SELECT ... FOR UPDATE 这种才是当前读，要加锁。

间隙锁 / 临键锁：在可重复读级别下，为了防幻读，InnoDB 给查询范围两边的"间隙"也上锁。这就是为什么并发 insert 偶尔会莫名卡住——碰到间隙锁了。

死锁是怎么回事

两个事务互相等对方手里的锁：事务 A 锁了行 1 想锁行 2，事务 B 锁了行 2 想锁行 1，俩人僵住。InnoDB 会主动检测并回滚其中一个。减少死锁的办法：多个事务按相同顺序访问表和行；事务别太长；能走索引就走索引（不走索引会锁更多行）。

2.5 存储过程、触发器、视图、事件

是什么：存储过程就是把一段 SQL 打包存到数据库里，起个名字，以后一句 CALL 名字() 就能跑。适合固定、重复、计算密集的逻辑。

一个带循环的存储过程：给新员工批量建档

DELIMITER //
CREATE PROCEDURE batch_new_hires(IN p_dept_id BIGINT, IN p_cnt INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
    ROLLBACK;
  END;

  START TRANSACTION;
  WHILE i <= p_cnt DO
    INSERT INTO employees(name, dept_id, salary)
    VALUES(CONCAT('newbie_', i), p_dept_id, 8000);
    SET i = i + 1;
  END WHILE;
  COMMIT;
END //
DELIMITER ;

-- 调用
CALL batch_new_hires(3, 50);

过程式 SQL 里的控制结构
结构	用法
`IF ... THEN ... ELSEIF ... ELSE ... END IF`	分支判断。
`WHILE ... DO ... END WHILE`	先判断后执行的循环。
`LOOP ... LEAVE ... END LOOP`	死循环，用 LEAVE 跳出。
`REPEAT ... UNTIL ... END REPEAT`	先执行后判断，至少跑一次。
`DECLARE CURSOR`	游标：把查询结果集一行行捞出来处理。
`DECLARE ... HANDLER FOR SQLEXCEPTION`	异常处理：出错时执行指定动作（通常回滚）。

存储函数、触发器、视图、事件

# 存储函数：必须 RETURN 一个值，可以当表达式用
CREATE FUNCTION fmt_salary(p_salary DECIMAL(10,2))
RETURNS VARCHAR(20)
DETERMINISTIC
RETURN CONCAT('¥', p_salary);

# 触发器：往订单表插数据后，自动扣库存
CREATE TRIGGER trg_after_order
AFTER INSERT ON orders
FOR EACH ROW
UPDATE products SET stock = stock - NEW.qty WHERE id = NEW.product_id;

# 视图：把一段查询包装成"虚拟表"，对外只暴露必要列
CREATE VIEW v_emp_public AS
SELECT id, name, city FROM employees;   -- 隐藏 salary 等敏感字段

# 事件调度器：每天凌晨 2 点跑一次统计
CREATE EVENT evt_daily_stat
ON SCHEDULE EVERY 1 DAY
STARTS '2026-01-01 02:00:00'
DO INSERT INTO daily_stat SELECT NOW(), COUNT(*) FROM orders;

存储过程别滥用

存储过程把业务逻辑写死在数据库里，难调试、难版本管理、难跨数据库迁移、不好水平扩展。现代趋势是把逻辑放在应用层，数据库只老老实实存数据。除非是极固定的批量任务，否则别把核心业务塞进存储过程。

2.6 备份与恢复：数据是命根子

引子：没备份的 DBA 就像不带伞出门的人——不下雨时没事，一下雨就是灭顶之灾。备份不是选择题，是必答题。

mysqldump：逻辑备份（最常用，小中型库）

# 备份整个库
mysqldump -u root -p --databases shop --single-transaction > shop_full.sql

# 只备份一张表，并带上存储过程/事件/触发器
mysqldump -u root -p --single-transaction --routines --events shop employees > emp.sql

# 恢复：把 dump 文件喂回 mysql
mysql -u root -p shop < shop_full.sql
# 或在 mysql 命令行里 source
mysql> SOURCE /path/to/shop_full.sql;

备份命令的实际输出

$ mysqldump -u root -p --databases shop --single-transaction > shop_full.sql Enter password: $ ls -lh shop_full.sql -rw-r--r-- 1 user staff 48M 9 16 15:00 shop_full.sql

论--single-transaction 是干嘛的

对 InnoDB 表加 --single-transaction，导出时在一个事务里拍一致性快照，不锁表，业务照常读写。否则 mysqldump 会锁表，大库导导出期间业务全停。这是线上备份的必备参数。

xtrabackup：物理热备（大库用）+ binlog 时间点恢复

# Percona XtraBackup 物理热备，不锁库、跑得快
xtrabackup --backup --user=root --password=xxx --target-dir=/backup/full

# 开启 binlog（my.cnf），它是 PITR 的命根子
[mysqld]
log_bin = mysql-bin
binlog_format = ROW
server-id = 1

# 查看 binlog 内容
mysqlbinlog mysql-bin.000007 | less

# 时间点恢复：先恢复全量备份，再重放 binlog 到事故前一刻
mysqlbinlog --start-datetime="2026-09-16 10:00:00" \
           --stop-datetime="2026-09-16 10:30:00" \
           mysql-bin.000007 | mysql -u root -p

靠谱的备份策略：3-2-1
做法	说明
全量 + 增量 + binlog	每周一次全量，每天增量，binlog 持续归档。恢复时：全量 → 增量 → 重放 binlog 到事故前。
3-2-1 原则	至少 3 份副本、存在 2 种介质上、其中 1 份 offsite（异地/对象存储）。
定期演练恢复	备份不恢复 = 没备份。每季度找台机器真恢复一次，确认备份可用。

2.7 主从复制与集群：读得更快、挂得更慢

引子：一台机器再强也有极限，而且它总会坏。主从复制就是"主库负责写，从库负责读，主库挂了从库顶上"的那套机制。

论主从复制原理（三步）

① 主库把每次写操作记到 binlog；② 从库开一个 IO 线程，连上主库，让主库的 dump 线程把 binlog 推过来，从库把它写到本地的 relay log（中继日志）；③ 从库的 SQL 线程读 relay log，把变更重放一遍。于是从库数据和主库保持一致。

三种复制方式：异步复制（默认，主库不等从库，快但可能丢数据）；半同步复制（主库至少等一个从库收到 binlog 才返回，更稳）；组复制 MGR（多主、Paxos 投票，强一致但复杂）。

一主一从搭建关键步骤

-- 主库：开启 binlog，建复制账号
CREATE USER 'repl'@'从库IP' IDENTIFIED BY 'repl_pass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从库IP';
# 记下 SHOW MASTER STATUS; 里的 File 和 Position

-- 从库：告诉它主库在哪，起点在哪
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='主库IP', SOURCE_USER='repl',
  SOURCE_PASSWORD='repl_pass',
  SOURCE_LOG_FILE='mysql-bin.000007',
  SOURCE_LOG_POS=156;
START REPLICA;
SHOW REPLICA STATUS\G    # 看 IO/SQL 线程是不是 Yes

读写分离：写走主库、读走从库，靠中间件自动分流。常见有 ProxySQL、MyCat。高可用：主库挂了自动切到从库，老牌方案 MHA，官方方案 InnoDB Cluster（MGR + MySQL Router）。分库分表：单表太大、单库写不动时，按某个键把数据拆到多库多表，中间件用 ShardingSphere。

主从延迟是个老大难

主库写完立刻去从库读，可能读不到——因为 binlog 还没重放完。这叫主从延迟。业务上"刚下单查不到订单"就是它。对策：强一致的读走主库；或半同步复制；或业务上容忍短暂延迟并做兜底重试。

2.8 MySQL 面试重点 20 题

下面 20 题是 MySQL 面试的高频题，点开展开看答案。先自己想一遍再对。

MySQL 面试 20 题（折叠答案）

1. InnoDB 和 MyISAM 区别？

答案

InnoDB 支持事务、行锁、外键、MVCC、崩溃恢复；MyISAM 只支持表锁、不支持事务，5.5 后默认引擎已是 InnoDB，新项目别用 MyISAM。

2. 为什么用自增主键？用 UUID 行不行？

答案

InnoDB 是聚簇索引，按主键物理有序存储。自增主键顺序插入，页不分裂，写入快；UUID 随机，插入会导致页分裂、碎片多，且二级索引更大。主键尽量用 BIGINT 自增或雪花 ID。

3. 聚簇索引和非聚簇索引区别？

答案

聚簇索引叶子节点存整行，一张表一个（主键）；二级索引叶子存主键值，查到后要回表。覆盖索引可避免回表。

4. 什么是回表？什么是覆盖索引？

答案

二级索引查到主键后，再去主键索引拿整行数据叫回表。如果 SELECT 的列在二级索引里全有，不用回表，就是覆盖索引。

5. 最左前缀原则？

答案

联合索引 (a,b,c) 按 a→b→c 排序，查询条件必须从最左列开始连续命中才能用索引。跳过中间列（如只查 c）用不上。

6. 索引为什么用 B+ 树不用 B 树/哈希？

答案

B+ 树矮胖、叶子有序链表支持范围查询；B 树非叶子也存数据导致树更高、范围查询要中序遍历；哈希只支持等值、不支持范围和排序。

7. 哪些情况索引会失效？

答案

对索引列用函数/运算、隐式类型转换、前导模糊 %xx、!=/NOT IN、OR 两边有一边无索引、违反最左前缀。

8. MVCC 是什么？解决了什么？

答案

多版本并发控制，靠 undo log 历史版本 + read view 实现快照读，让读写不互相阻塞，提升并发。

9. MySQL 默认隔离级别？解决了哪些问题？

答案

可重复读（REPEATABLE READ）。解决脏读、不可重复读，并通过间隙锁/临键锁基本解决幻读。

10. 脏读、不可重复读、幻读区别？

答案

脏读=读到未提交数据；不可重复读=同行两次读值变了（被 update）；幻读=同条件两次查行数变了（被 insert/delete）。

11. redo log、undo log、binlog 分别干嘛？

答案

redo log 保证持久性（崩溃恢复）；undo log 保证原子性（回滚）+ MVCC；binlog 是 Server 层逻辑日志，用于主从复制和 PITR。

12. 间隙锁什么时候触发？

答案

可重复读级别下，范围查询加锁时会给记录之间的间隙加锁，防止别的事务在间隙插入导致幻读。

13. 怎么排查慢 SQL？

答案

开慢查询日志抓出来 → EXPLAIN 看 type/key/rows/Extra → 加合适索引/改写 SQL → 必要时 pt-query-digest 统计 Top N。

14. COUNT(*)、COUNT(1)、COUNT(列) 区别？

答案

MyISAM 下 COUNT(*) 有缓存快；InnoDB 下三者差不多，COUNT(*) 不取值、COUNT(列) 会跳过 NULL。写法上 COUNT(*) 最规范。

15. 大表加字段怎么不锁表？

答案

MySQL 8.0 支持 INSTANT ADD COLUMN（瞬间加列）；老版本用 pt-online-schema-change 或 gh-ost 在线变更，避免长 DDL 锁表。

16. 主从复制原理？

答案

主库写 binlog → 从库 IO 线程拉到 relay log → 从库 SQL 线程重放。

17. 主从延迟怎么办？

答案

强一致读走主库；用半同步复制；提升从库硬件；业务层做读重试兜底。

18. 分库分表什么时候用？怎么分？

答案

单表千万级、单库写瓶颈时。按范围/哈希/时间分片，用 ShardingSphere 等中间件。代价是分布式事务、跨片查询复杂。

19. 死锁怎么产生和排查？

答案

两事务交叉加锁。看 SHOW ENGINE INNODB STATUS 里 LATEST DETECTED DEADLOCK。预防：统一加锁顺序、缩短事务、走索引。

20. 为什么用 utf8mb4 不用 utf8？

答案

MySQL 的 utf8 最多 3 字节，存不下 emoji 和生僻字；utf8mb4 才是完整 UTF-8。建表一律 utf8mb4。

2.9 权限管理与安全：别让库裸奔

引子：很多人把 root 密码写进代码、给应用账号开全库权限——这就等于把家门钥匙挂在门外。数据库安全是 DBA 的基本功，也是面试常被追问的点。

建应用账号、最小授权

# 给应用建一个账号，只允许从应用服务器连，只给 shop 库的增删改查权限
CREATE USER 'shop_app'@'10.0.0.%' IDENTIFIED BY '强密码_xxx';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'10.0.0.%';
FLUSH PRIVILEGES;

# 回收权限 / 删除账号
REVOKE INSERT ON shop.* FROM 'shop_app'@'10.0.0.%';
DROP USER 'shop_app'@'10.0.0.%';

安SQL 注入：最经典也最危险的漏洞

如果你在应用里这样拼 SQL："SELECT * FROM users WHERE name='" + input + "'"，攻击者输入 ' OR '1'='1，整个 WHERE 就永真了，能拖走全表，甚至用 ; 拼接删表语句。

根治办法只有一个：参数化查询（预编译）。SQL 模板和参数分开传：WHERE name = ?，驱动负责安全转义。永远不要用字符串拼接用户输入进 SQL。ORM（如 Sequelize、Prisma、MyBatis）默认就是参数化的。

连接池：为什么应用不每次新建连接

# 数据库连接很贵（TCP + 认证 + 分配内存）。每次请求新建一个会把库压垮。
# 正确做法：应用启动时预建一批连接放池子里，用完还回去复用。
# MySQL 生态常用：HikariCP（Java）、sqlalchemy 的 pool（Python）、pg（Node）。
# max_connections（服务端）和池大小要匹配，别一边设 1000 一边池开 50。

安全 checklist

① 生产别用 root 跑应用，按"最小权限"建账号；② 密码强、定期轮换、别硬编码进代码（用配置中心/环境变量）；③ 数据库端口不对公网开放，只在内网/VPC；④ 全部用参数化查询，防注入；⑤ 定期备份 + 恢复演练。

2.10 索引设计清单与 8.4 新特性

学了一堆索引，落地时记住这份清单就行。

高频 WHERE / JOIN / ORDER BY 的列才建索引，别全建。
联合索引把等值列放前面、范围列放后面，遵守最左前缀。
选择性高的列（区分度大）放联合索引最左。
能用覆盖索引就别回表。
索引不是越多越好——写多、读少的表别乱建。
定期用 pt-query-digest 找慢 SQL，对症下药。

MySQL 8.4 LTS 值得知道的点
点	说明
LTS 长支持	官方维护到 2032 年，生产升级优先选它，别选半年就淘汰的 innovation 版。
JSON 增强	JSON 操作、路径更强，可建多值索引。
矢量类型	原生 VECTOR 类型，小规模向量检索可在 MySQL 里做。
INSTANT 加列	8.0.12+ 支持瞬间加列，8.4 进一步放宽，大表 DDL 不再怕锁。
认证插件默认 caching_sha2_password	老客户端连不上时要注意驱动版本。

← 上一节数据库三剑客 · 课程总览下一节 →