Appearance
MySQL 必知必会
使用 Docker 启动 MariaDB
MariaDB 被广泛用于替代 MySQL，主要因其开源更彻底、功能更新更快、限制更少且在性能上更具优势
bash
docker run -d \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=root \
-v $HOME/mysql-data:/var/lib/mysql \
--name mariadb \
mariadb:10准备训练数据
Sakila 数据集 是 MySQL 官方为练习 join、聚合和索引而打造的 DVD 租赁示例库，包含 actor、film、customer、rental 等完整业务表，非常适合练习实战
打开 DataGrip，创建数据库 create database sakila;，将表结构和数据导入，先执行 sakila-schema.sql 文件，再执行 sakila-data 文件
MySQL 架构
在 MySQL 中，架构可以分为服务器层和存储引擎层两部分。服务器层负责 SQL 的解析、优化和执行，同时管理连接、权限以及缓存。
当执行 SELECT 查询时，服务器层会解析 SQL 并生成执行计划，然后交给存储引擎访问数据。SELECT 查询只读取数据，不修改，因此不会产生 binlog，也不会写 redo 或 undo log，只涉及从存储引擎读取 B+ 树、页和缓存中的数据。
对于 INSERT、UPDATE 和 DELETE 等修改操作，流程会稍微复杂一些。SQL 首先在服务器层解析和优化，之后在提交事务前会将操作记录到 binlog，这样可以支持主从复制和增量恢复。随后存储引擎层会先写 redo log 和 undo log 来保证事务的原子性和可回滚性，再修改数据页。事务提交后，redo log 确保数据持久化，而 undo log 用于回滚或支持 MVCC 快照。
可以理解为，binlog 属于服务器层的逻辑日志，记录的是事务提交后的变更，而 redo 和 undo log 属于存储引擎层的物理日志，用于保证事务安全和崩溃恢复。SELECT 查询主要走读取路径，不产生这些日志，而 INSERT/UPDATE/DELETE 则需要走写路径，通过 binlog、redo 和 undo log 协同保证数据一致性和可恢复性。整个架构将 SQL 执行、事务安全和数据持久性清晰分层，同时为性能优化提供了基础。
2PC（两阶段提交）解决的是：binlog 与 redo log 必须保持一致，否则主从会乱套。
MVCC 解决的是：并发读写时，每个事务看到的“数据版本”要一致。（undo log）
日志涉及：
- redo log（崩溃恢复）
- binlog（复制）
- undo log（MVCC + 回滚）
InnoDB 的索引机制
大纲：
- 页机制与磁盘 I/O：数据按页读取到内存，减少小块 I/O，提高访问效率
- InnoDB 如何存储数据：B+ 树、聚集索引和非聚集索引
- SELECT 查询流程
- SQL 查询优化手段：联合索引、最左前缀原则、覆盖索引和 ICP
数据库的数据最终存储在磁盘上，而磁盘的访问速度远比内存慢，数据在两者间来回移动的过程就是我们常说的 I/O（输入输出）操作
InnoDB 读取数据时，并不会一条一条地从磁盘上取，而是以 页（Page） 为单位进行读取。页是数据的最小读写单元，InnoDB 默认每页 16KB。当查询触发磁盘访问时，整页数据会被一次性加载到内存缓存（buffer pool）里，这样可以一次获取多条记录，避免频繁的小 I/O 请求，提高数据访问效率
理解页是关键，因为无论是聚集索引还是非聚集索引，B+ 树都是以页为单位组织的
B+ 树是 InnoDB 索引的核心结构，用于快速定位数据。内节点存储导航信息，叶子节点存储实际数据或主键指针。叶子节点通过链表顺序连接，这使得范围查询可以连续扫描多个页，而不是频繁跳转，从而提高 I/O 效率
这里需要理解随机读取与顺序读取的区别：随机读取意味着磁盘需要频繁调整磁头位置（寻道），延迟高；顺序读取意味着连续读取多页，磁头移动少，效率高。在聚集索引中，由于数据按主键顺序存储，范围查询或回表可以连续顺序读取磁盘页，从而充分利用顺序读取优势，减少随机 I/O 的开销
在 InnoDB 里，数据存储方式直接影响查询速度。聚集索引（主键索引）将数据行直接存储在 B+ 树的叶子节点中，所以按主键查询时，数据库可以直接定位到数据所在页，一步获取整行数据，无需额外跳转
非聚集索引则只在叶子节点存储主键，用于快速定位数据行。当查询条件是非主键列时，比如 SELECT id, name FROM user WHERE email='a@b.com'，InnoDB 会先在 email 索引里找到这条记录，但它拿到的只是一个主键值。例如找到 email 对应的是主键 id=42，它必须再跳回聚集索引里，把整行数据取出来。这个过程叫回表
理解这些概念后，再看 SELECT 的执行流程就非常清楚：如果按主键查询，直接命中聚集索引，不需要回表；如果按非主键查询，先通过非聚集索引找到主键，再回表获取完整数据
聚集索引保证数据顺序存储，非聚集索引提供快速定位，页机制减少小块读取开销，而顺序读取则让回表 I/O 高效，从而整体提升数据库性能
回表虽然多了一跳，但设计合理时，顺序读取可以显著降低 I/O 消耗，从而保持高性能。这就引出了 SQL 查询优化的手段：
联合索引可以把多列组合在一起加速查询条件，例如 (email, name) 索引可以快速定位满足 email=? AND name=? 的记录，而联合索引只有遵循最左前缀原则才能生效，例如可以用于 WHERE email=? 或 WHERE email=? AND name=?，但单独查询 name=? 则无法利用索引
当查询涉及的列全部包含在索引中时，例如只查询 email 和 name，该索引又成为覆盖索引，数据库无需回表即可直接从索引页返回结果，从而进一步减少 I/O 消耗
在非聚集索引回表的过程中，如果匹配的索引行很多，但最终只有少部分满足查询条件，就会产生大量不必要的 I/O，这时 Index Condition Pushdown（ICP） 就派上用场。ICP 会在索引扫描阶段，把能够在索引列上判断的 WHERE 条件提前过滤，只有真正匹配的行才会回表获取整行数据。这样不仅减少了回表 I/O，也降低了缓冲池压力，同时避免对无效数据的 CPU 计算，从而进一步优化查询性能
SQL 优化
SQL 优化核心目标：少扫行、少回表、少排序、少临时表、少死锁
- 优化索引结构：让查询尽量走索引，减少扫描
- 建立正确字段的 B+Tree 索引（WHERE / JOIN / ORDER BY 要一致）
- 让条件“最左匹配”，把高区分度字段放前面
- 用覆盖索引减少回表
- 减少范围扫描和 filesort
- 避免 * 全表扫描
- 减少临时表（尤其 GROUP BY / DISTINCT）
- 避免低效子查询（EXISTS/IN 的优化套路）
- 合理使用 Limit + 索引分页
- 尽量避免 OR（会让索引失效）
- 减少大事务（锁太多，死锁概率高）
- 字段类型优化（很容易忽略）
如何优化 filesort
TODO
如何优化 temporary
TODO
如何优化 join 查询
- 小表驱动大表
- JOIN 字段必须有索引
- JOIN 字段类型必须完全一致，否则索引会失效
- 尽量避免 JOIN 过多表
- 使用覆盖索引减少回表
- 利用 EXISTS 替代 IN / JOIN
- 避免
SELECT *，选择必要字段
Explain 语句
1、访问类型
| 类型 | 说明 | 性能等级 |
|---|---|---|
| system | 表只有一行记录（const 的特例） | ⭐⭐⭐⭐⭐ |
| const | 主键或唯一索引等值查询，最多返回一行 | ⭐⭐⭐⭐⭐ |
| eq_ref | 连接时使用主键或唯一索引，每张表只读取一行 | ⭐⭐⭐⭐ |
| ref | 非唯一索引等值查询 | ⭐⭐⭐ |
| range | 索引范围扫描 | ⭐⭐⭐ |
| index | 索引全扫描（索引树扫描） | ⭐⭐ |
| ALL | 全表扫描 | ⭐ |
性能目标：至少达到 range 级别，最好达到 ref 级别。
2、eq_ref 表示 JOIN 条件使用主键或唯一索引，每次匹配一行。
JOIN 时，对被驱动表进行等值连接，如果使用的是主键或完整唯一索引，且一次查找在逻辑上只能返回一行，就是 eq_ref
3、理解常见 Extra 的类型
| 值 | 说明 | 优先级 |
|---|---|---|
| Using index | 覆盖索引，无需回表 | ✅ 最好 |
| Using index condition | 索引条件下推（ICP） | ✅ 好 |
| Using where | 使用 WHERE 过滤 | ✅ 正常 |
| Using filesort | 需要文件排序 | ⚠️ 需优化 |
| Using temporary | 使用临时表 | ⚠️ 需优化 |
| Using join buffer | 使用连接缓冲 | ⚠️ 需优化 |
- Using filesort -> 排序无法走索引
- Using index condition -> ICP -> 减少回表行数，节省 I/O
事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 锁机制 |
|---|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 | 几乎不加锁 |
| READ COMMITTED | 避免 | 可能 | 可能 | 每次 SELECT 都创建新 Read View |
| REPEATABLE READ | 避免 | 避免 | 可能 | 事务开始时创建 Read View |
| SERIALIZABLE | 避免 | 避免 | 避免 | 全表串行化 |
- MySQL 默认隔离级别：REPEATABLE READ；Oracle 默认隔离级别：READ COMMITTED
- Repeatable Read 是事务隔离的协议，MVCC 是 InnoDB 用来实现这套协议的核心机制之一
- MVCC 主要解决“读一致性”，幻读靠的不是 MVCC，而是锁（Next-Key Lock）
READ UNCOMMITTED - 脏读演示
脏读：读到其他事务未提交的数据
sql
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
-- 第一次读取
SELECT balance FROM accounts WHERE id = 1;
-- 读到: 1000
-- 保持事务开启，切换到会话 Bsql
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
-- 修改数据，但不要提交！
UPDATE accounts SET balance = 500 WHERE id = 1;
-- 此时数据已修改，但事务未提交
-- 不要执行 COMMIT，保持事务开启sql
-- 第二次读取
SELECT balance FROM accounts WHERE id = 1;
-- 读到: 500！
-- 这就是"脏读" —— 读到了会话 B 未提交的修改sql
-- 回滚事务
ROLLBACK;
-- 此时数据恢复为 1000sql
-- 第三次读取
SELECT balance FROM accounts WHERE id = 1;
-- 读到: 1000
-- 但如果会话 A 之前基于 500 做了业务决策，就出问题了！
COMMIT;- 为什么会读到未提交的数据？因为 READ UNCOMMITTED 不使用 MVCC，不加锁，直接读取缓冲池中的最新值
- 如果会话 B 最终回滚了，会话 A 基于脏数据做的操作可能导致数据不一致、业务决策错误
READ COMMITTED - 不可重复读演示
sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- 第一次读取
SELECT balance FROM accounts WHERE id = 1;
-- 记录这个值，假设是 1000
-- 切换到会话 Bsql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE accounts SET balance = 500 WHERE id = 1;sql
-- 第二次读取
SELECT balance FROM accounts WHERE id = 1;
-- 值变成 500 了！这就是"不可重复读"
COMMIT;REPEATABLE READ - 可重复读演示
sql
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- 第一次读取
SELECT balance FROM accounts WHERE id = 1;
-- 记录这个值
-- 切换到会话 Bsql
UPDATE accounts SET balance = 2000 WHERE id = 1;
-- 验证
SELECT * FROM accounts WHERE id = 1;sql
-- 第二次读取
SELECT balance FROM accounts WHERE id = 1;
-- 值还是旧的！这就是"可重复读"
COMMIT;
-- 提交后再读，就是新值了
SELECT balance FROM accounts WHERE id = 1;幻读演示
幻读：同一事务内，前后两次查询，结果集数量不同
sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- 查询余额 < 1000 的用户
SELECT id, username, balance FROM accounts WHERE balance < 1000;
-- 假设只有 1 条记录
-- 切换到会话 Bsql
-- 插入一条新记录
INSERT INTO accounts (username, balance) VALUES ('user_new', 500);
COMMIT;sql
-- 再次查询
SELECT id, username, balance FROM accounts WHERE balance < 1000;
-- 现在有 2 条记录了！这就是"幻读"
COMMIT;MySQL 的 REPEATABLE READ 通过 Next-Key Lock 解决幻读：
sql
-- ===== 设置隔离级别为 REPEATABLE READ =====
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;重复上述步骤，你会发现幻读被避免了！
锁机制
InnoDB 的 RR 是靠 MVCC + 锁实现的
InnoDB 锁机制 = 行锁（S/X）+ 间隙锁/Gap Lock + Next-Key + 意向锁
| 当前锁 | MVCC 快照读 | S 锁读 | X 锁读 | 写 |
|---|---|---|---|---|
| 无锁 | ✅ | ✅ | ✅ | ✅ |
| S 锁 | ✅ | ✅ | ❌ | ❌ |
| X 锁 | ✅ | ❌ | ❌ | ❌ |
MVCC 快照读
场景：两个事务同时读取同一条记录，一个事务修改数据，观察读到的值
sql
BEGIN;
-- 查看 MySQL 事务 ID
SELECT CONNECTION_ID();
-- 读取用户余额（快照读）
SELECT id, username, balance FROM accounts WHERE id = 1;
-- 此时不要提交，保持事务开启
-- 然后切换到会话 Bsql
-- 更新数据
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 立即提交
COMMIT;
-- 验证更新
SELECT * FROM accounts WHERE id = 1;sql
-- 再次读取，观察是否读到新数据
SELECT id, username, balance FROM accounts WHERE id = 1;
-- 为什么还是旧值？因为 MVCC 的 Read View
COMMIT;- 会话 A 第二次读取的结果与会话 B 更新后的值一致吗？不一致。会话 A 读到的是旧值（快照）
- 为什么会话 A 读不到会话 B 的修改？因为 MVCC 机制。Read View 在事务开始时创建，后续读取的是这个快照，而不是最新数据
当前读和快照读的区别
- 快照读：普通的
SELECT，读取 Read View 中的历史版本 - 当前读：读取最新版本，加锁保证读到的最新数据
sql
BEGIN;
-- 这是快照读，不加锁
SELECT balance FROM accounts WHERE id = 1;
-- 这是当前读，加 S 锁
SELECT balance FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- 这也是当前读，加 X 锁
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;锁机制：S 锁（共享锁）
S 锁特性：多个事务可以同时持有 S 锁
sql
BEGIN;
-- 加 S 锁（LOCK IN SHARE MODE）
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- 保持事务开启，切换到会话 Bsql
BEGIN;
-- 也可以加 S 锁，成功！
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
COMMIT;
-- 尝试加 X 锁（FOR UPDATE），会阻塞！
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 此时应该被阻塞...会话 B 的 FOR UPDATE 会被阻塞，因为 S 锁和 X 锁不兼容
锁机制：X 锁（排他锁）
X 锁特性：只有持有锁的事务可以修改数据
sql
BEGIN;
-- 加 X 锁
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 或者直接 UPDATE 也会加 X 锁
-- UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- 保持事务开启sql
BEGIN;
-- 尝试加 S 锁，被阻塞
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- 尝试加 X 锁，也被阻塞
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 尝试更新，同样被阻塞
UPDATE accounts SET balance = balance + 50 WHERE id = 1;数据库范式
解决数据冗余、数据不一致
应用层连接池
应用层连接池参数
| 参数 | 含义 | 配置原则 / 说明 | 实战参考值 |
|---|---|---|---|
| MaxOpenConns | 最大连接数（池中最大活跃连接） | 控制客户端并发不要超过 MySQL max_connections；高并发适当放大 | 小型 20~50 / 中型 200~300 / 大型 800+ |
| MaxIdleConns | 最大空闲连接数 | 空闲连接池大小，保证突发请求快速拿到连接 | MaxOpenConns 的 50% 左右 |
| ConnMaxIdleTime / MaxConnectionIdleTime | 单连接最大空闲时间 | 防止连接被 MySQL 服务端断开或占用资源太久 | 2~5 分钟 |
| ConnMaxLifetime / MaxLifetime | 单连接最大生存时间 | 定期替换连接，避免死链或累积状态异常 | 略小于 MySQL wait_timeout（30~60 分钟） |
MySQL 服务端参数
| 参数 | 含义 | 调优原则 | 实战参考值 |
|---|---|---|---|
| innodb_buffer_pool_size | InnoDB 缓冲池大小 | 占可用内存 60~75%，热点数据放内存，减少磁盘 IO | 小型 2~4G / 中型 22~40G / 大型 96G |
| innodb_buffer_pool_instances | 缓冲池实例数 | 大缓冲池分多个实例减少锁争用 | 根据 buffer_pool_size 决定：>1G → 4~16 个 |
| max_connections | 最大允许连接数 | ≥ 客户端总池连接数 + 预留余量 | 小型 200 / 中型 1000 / 大型 3000 |
| wait_timeout / interactive_timeout | 空闲连接超时 | > 池中 MaxConnIdleTime | 300~1200 秒 |
TODO
- 树越高，上面的节点数据越少，是不是浪费 16K 页？
- B+ 树上层节点看似“空间利用率低”，但它们常驻内存、极大降低树高和磁盘 IO，这是数据库里最划算的空间换时间。
- 不同页之间读取数据，IO 次数怎么算？
- 数据库计算 IO 的核心单位是“页是否命中内存”，而不是“跨了多少节点”或“走了多少指针”。
- Buffer Pool/根节点常驻内存 0 IO
- IO 发生在“页不在内存”时
- 磁盘 https://linux.vbird.org/linux_basic/centos7/0130designlinux.php#partition_name
- 页不是磁盘的物理单位，而是数据库为了控制 IO 成本而设计的逻辑抽象；数据库用页思考性能，操作系统用块操作磁盘，磁盘最终按扇区读写。
- 顺序 IO/随机 IO/寻道
- B 树和 B+树的区别：B 树的节点存储数据行；B+树的节点只存储索引，只在叶子节点存储数据行
- 平衡二叉树/自旋慢；红黑树/自旋快
- 同级节点的左右指针：顺序访问/范围查询/顺序 IO/随机 IO
- 自旋/spin
- CRUD 时 B+树如何工作？增删麻烦，查改效率高
- MySQL 通过 B+ 树索引精确定位页号；是否随机或顺序 IO，取决于访问的页在磁盘上的物理连续性；顺序 IO 极大减少寻轨，是数据库性能的生命线。
- 缓存机制：MySQL Buffer Pool/OS Page Cache/预读
- 关联字段/排序字段/分组字段是如何使用索引的
- optimizer_trace 和 EXPLAIN 的关系
工具 作用 EXPLAIN 我选了谁 optimizer_trace 我为什么这么选 SHOW PROFILE 执行时花在哪 - optimizer_trace 是用来解释“为什么优化器不用某个索引”的唯一官方证据，它展示了候选执行计划、成本估算和最终决策过程。
- 联合索引/最左前缀匹配
- 覆盖索引
- 唯一索引
- 唯一索引允许多个 NULL 值
- 唯一索引影响性能的本质，是写入时必须进行额外的存在性检查和并发一致性保护，这在高并发写场景下会显著放大成本
- 索引失效的情况
- 使用函数
- 使用 LIKE 以
%开头（除非覆盖索引） - 使用不等于/
!= is not null有可能不走索引- 违反最左前缀匹配
- 索引下推/ICP/Using index condition