MySQL
SQL 语法
查询时关键字执行顺序
FROM 找到要查的表，如果有 JOIN 则建立联表
WHERE 过滤分组前的数据
GROUP BY 进行分组
HAVING 过滤分组后的数组
SELECT 选择查询的字段
DISTINCT 去重
ORDER BY 对查询结果作排序
LIMIT / OFFSET 限制行数
例：LIMIT 10 OFFSET 20，跳过前 20 行并往后查 10 行
慢 SQL 调优
从SQL 语句出发
避免使用
SELECT *读取表中所有字段，增加磁盘 I/O 和内存消耗
使用 JOIN 而非子查询
嵌套查询，时间复杂度为 O(N²)
用 IN 代替 != 或 <>
!= 或 <> 无法有效利用索引，特别当列允许 NULL 时，会遍历所有列
IN 的列表过长可能导致全表扫描，需结合索引
避免前导通配符
LIKE 利用的是 % 前的字段，使用 LIKE ‘%abc%’ 无法利用索引，改为 LIKE ‘abc%’
从索引出发
避免过多索引
每个索引都需要占用磁盘空间，增加写操作。优先为高频查询字段建索引
使用覆盖索引减少回表
索引包含查询所需字段，直接从索引树返回结果
其它
EXPLAIN 分析执行计划
- 连接类型 type：ALL 表示全表扫描，index 表示索引扫描，ref 表示索引查找
- 索引 key：实际使用的索引
- 行数 rows：预估扫描行数
合并批量插入
每条 INSERT 都需要多次事务提交、日志写入、网络开销
合并可减少事务开销和磁盘 I/O
硬件优化
- 内存：扩大 Buffer Pool，减少磁盘 I/O
- 磁盘：使用 SSD 替换 HDD，提升随机读写速度
- CPU：并行查询时，多核 CPU 可加速计算
笛卡尔积问题
定义
多表查询且无连接条件时返回的结果
假设有两个集合，A = {a, b}, B = {1, 2, 3}
则 A 和 B 的笛卡尔积 = {{a, 1}, {a, 2}, {a, 3}, {b, 1}, {b, 2}, {b, 3}}，共 6 条记录
MySQL 中的例子
假设有表 1、表 2 两张表，数据量分别为 100 和 1000。现在对这两张表作 JOIN，若未建立查询条件，则需要从数据库中将这 1100 条数据拉取到内存中，在内存中进行100 * 1000次的计算，并对这 10W 条数据全表扫描找到想要的数据
存在问题
- 内存占用高：如果两张表各有 10W 条数据，那么中间结果集就有 100 亿条，可能导致 OOM
- I/O 频繁：可能把中间结果集写入磁盘的临时表文件，造成磁盘频繁读写
- 耗时长：若中间结果集很大，且无过滤条件，只能全表扫描
如何避免
- 显式指定 JOIN，确保多表查询包含 ON 或 USING
- 单表查询、业务耦合
- 使用 EXPLAIN 排查，若输出中的 type 列未 ALL 且 rows 极大，很可能发生了笛卡尔积
架构
SQL 执行流程
MySQL分为 Server 层和存储引擎层
Server 层
- 连接器：管理客户端连接、身份认证、权限验证
- 查询缓存：缓存 SELECT 查询的完整结果，加速重复查询（8.0 版本移除）
- 解析器：进行词法分析（识别如 SELECT 的关键字）和语法分析，无误则建立语法树
- 预处理器：判断表和字段是否存在，将 ***** 扩展为表上的所有列
- 优化器：生成并选择最优执行计划（全表扫描 OR 索引扫描）
- 执行器：执行查询计划，与存储引擎交互并返回结果给客户端
存储引擎层
- InnoDB
- MyISAM
- Memory
- …
不同存储引擎共用一个 Server 层，5.5之后，InnoDB 作为默认存储引擎。索引数据结构就是由存储引擎层实现的，不同存储引擎支持的索引类型不同
存储引擎对比
事务
只有 InnoDB 支持 ACID 事务，高并发下的首选
锁机制
InnoDB 支持行级锁，粒度小，并发性能高，适合写操作频繁场景
MyISAM 和 Memory 只支持表级锁
索引机制
InnoDB：聚簇索引，主键索引和数据存储在一起
MyISAM：非聚簇索引，索引和数据分开
Memory：默认为 Hash 索引，也可用 B 树
数据存储位置
InnoDB 和 MyISAM 存储在磁盘中
Memory 存储在内存中，重启即丢失，适用于缓存和临时表
崩溃恢复
InnoDB：undo log、redo log
MyISAM：借助 myisamchk 工具
Memory：直接丢失，无法恢复
事务
ACID 四大特性
A：Atomicity，原子性。通过 Undo log 实现
C：Consistency，一致性。通过其它三个特性和应用层逻辑实现
I：Isolation，隔离性。通过 MVCC 和锁实现
D：Durability，持久性。通过 Redo log 实现
虽称为四大特性，但并不平级，AID 是为了达到 C 这一目标的手段
隔离级别
- RU，读未提交：一个事务还未提交时，其他事务就能看到它做的变更
- RC，读已提交：一个事务提交后，其他事务才能看到它做的变更
- RR，可重复读：一个事务执行过程中，看到的数据都是一致的，InnoDB 默认隔离级别
- Serializable，串行化：对记录加读写锁，当多个事务对同一记录做读写操作时，若发生冲突，则按顺序执行操作
隔离级别：RU < RC < RR < Serializable
并发性能：RU > RC > RR > Serializable
分别是如何实现的？
RU：
直接读取最新数据，包括其它数据未提交的修改；写操作对行加排他锁（X锁），读操作不加锁
RC：
每次查询生成一个 Read View 视图；写操作加 X 锁，事务提交后释放
RR：
事务的首次查询生成 Read View 视图，整个事务期间都使用该视图，保证一致性；写操作加行锁和临键锁（Next-Key Lock）
串行化：
读操作隐式转换为 SELECT … LOCK IN SHARE MODE，加共享锁（S锁），写操作加 X 锁
各自会造成什么问题？
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 加锁读 |
|---|---|---|---|---|
| RU | ✅ | ✅ | ✅ | |
| RC | ✅ | ✅ | ||
| RR | ✅（极少） | |||
| 串行化 | ✅ |
脏读：读到其他事务还未提交的数据
不可重复读：一个事务内多次查询同一字段，前后数据内容不一致
幻读：一个事务内多次查询同一字段，前后记录数量不一致
严重性：脏读 > 不可重复读 > 幻读
可重复读如何避免幻读？
- 快照读：通过 MVCC，保证事务期间的 Read View 视图一致
- 当前读：SELECT … FOR UPDATE，通过临键锁阻塞其他事务在此锁范围内插入记录
无法解决的幻读：
单纯的快照读或当前读都不会导致幻读现象，交叉使用时才有可能导致幻读
- 快照读时：事务A开启后，事务B执行了插入操作，且事务A使用了 UPDATE 进行更新，后续事务A可以读到这条记录，导致幻读
- 当前读时：事务开启后，先执行快照读，其他事务进行插入操作，后续执行 SELECT … FOR UPDATE 或 SELECT … IN SHARE MODE，发现前后记录数量不一样，导致幻读
共享锁、互斥锁兼容性
| X 锁 | S 锁 | |
|---|---|---|
| X | ❌ | ❌ |
| S | ❌ | ✅ |
为什么默认隔离级别为 RR，却有很多互联网公司用 RC ？
- 当前业务场景下，幻读的影响不大
- 提高并发性能
为什么不采用串行化，避免所有事务问题？
串行化的并发性能过低
MVCC
作用
- 特定隔离级别下，读写操作不会相互阻塞
- 读取的数据是某一时间点的一致性快照，而非最新数据
- 提高数据库在高并发环境下的性能和吞吐量
依赖机制
隐藏字段
InnoDB 在每行记录中维护两个隐藏字段
- trx_id：记录最近一次修改该行的事务 ID
- 回滚指针 roll_pointer：指向 undo log 中的旧版本，构建历史快照
undo log
当事务修改数据时，InnoDB 会将修改前的值记录在 undo log 中，快照读下，可以通过回滚指针查看旧版本的数据
快照读实现原理
执行快照读时创建 Read View 视图
Read View 的内容
- m_ids：创建视图时，活跃事务的事务 ID 列表，活跃事务即启动但未提交事务
- min_trx_id：m_ids 中的最小事务 ID，ID 比它小的一定是已提交事务
- max_trx_id：全局事务中最大事务 ID 值 +１
- creator_trx_id：创建该视图的 事务 ID
MVCC 版本链
创建视图后，可以将隐藏字段中的 trx_id 划分为三种情况
- 已提交事务：事务 ID 小于 min_trx_id，该版本记录对当前事务可见
- 启动但未提交事务：事务 ID 在 [min_trx_id, max_trx_id] 区间内，版本记录对当前事务不可见
- 还未开始的事务：事务 ID 大于 max_trx_id，版本记录对当前事务不可见
可通过 roll_pointer 回滚到该链上的旧版本
索引
作用
加快查询数据的速度，相当于书籍的目录
分类
按数据结构分类
B+ 树
多叉平衡树，叶子节点存放数据，非叶子节点只存放索引，并且每个节点里的数据按主键顺序存放。父节点的索引值都会出现在下层子节点的索引值中，因此叶子节点中包含所有索引值信息。
除此之外，每个叶子节点还有两个指针，分别指向前一个和后一个叶子节点，形成双向链表，可用于实现范围查询
主键索引和二级索引的 B+ 树区别
主键索引的叶子节点存放实际数据
二级索引的叶子节点存放主键值，再到主键索引中找到对应的数据（回表）
如何避免回表？
回表需要查两棵 B+ 树，效率较低。使用覆盖索引将查询需要的所有列都包含在二级索引的叶子节点，就无需到主键索引中查找行数据
全文索引 Full-Text
按关键字分词构建倒排索引，用于大文本字段的模糊查找
三者对比
| B+ 树索引 | Hash 索引 | 全文索引 | |
|---|---|---|---|
| 底层结构 | B+ 树 | 哈希表 | 倒排索引 |
| 优势 | 范围、有序查询快 | 等值查询最快 | 适合模糊匹配、大量文本搜索 |
| 劣势 | 等值略慢于 Hash | 不支持范围/排序查询 | 不支持结构化字段、更新开销大 |
| 示例场景 | 通用查询，支持排序/范围 | 用户 ID 精确查找 | 文本检索、搜索引擎类功能 |
InnoDB 为什么选 B+ 树作默认索引
对比 B 树
B 树的非叶子节点也存储数据，相较于 B+ 树，其每个节点存储的数据更多，导致单个节点更大，占用更多磁盘空间；B+ 树则把所有数据集中存储在叶子节点，非叶节点仅用于导航，节点结构更稳定，单个节点能容纳更多索引项，I/O 效率更高
B+ 树的叶子节点通过链表相连，天然支持范围查询，而 B 树不支持。
对比二叉树
B+ 树的搜索复杂度为 O(logdN)，其中 d 表示节点允许的最大子节点个数，实际应用中 d 大于 1000，可保证在千万级数据量下，仍然可以维持 3
4 的层高，即一次查询只需要 34 次磁盘 I/O 就可找到 目标数据二叉树的子节点个数固定为 2，搜索复杂度为 O(logN)，比 B+ 树高不少，查询时需要更多的磁盘 I/O
对比 Hash
虽然 Hash 的搜索复杂度为 O(1)，比 B+ 树快，但其只适用于等值查询而非范围查询
按物理存储分类
聚簇索引（主键索引）
叶子节点存放实际数据，完整的用户记录都存放在主键索引的 B+ 树叶子节点中
叶子节点存多少数据？
叶子节点相当于数据页，故每个叶子节点存储若干行数据
向叶子节点插入一条数据的过程？
从根节点开始，比较索引键，找到适合的叶子节点，查找复杂度为 O(logN)
如果叶子节点有足够空间，则直接插入
没有则发生页分裂，将数据对半分裂，一部分记录移到新页，此时要更新父节点的指针，使其指向这两个页
非聚簇索引（二级索引、辅助索引）
叶子节点存放主键而非实际数据
按字段特性分类
主键索引
每表最多一个，列的值不允许有空值
唯一索引
可以多个，索引列的值必须唯一，允许空值（多个 NULL 不会触发唯一性冲突）
普通索引
前缀索引
只为字符类型字段的前几个字符建立索引，减少索引占用的存储空间，提高查询效率
按字段个数分类
单列索引
建立在单列上，如主键索引
联合索引（复合索引）
假设存在字段 name 和 age，(name, age) 即为二者组成的联合索引
联合索引如何实现？
非叶子节点用两个字段的值作为 B+ 树的 key。当联合索引在查询数据时，先按 name 字段比较，name 相同情况下再比较 age 字段，存在最左匹配原则，即按照最左优先的方式进行索引匹配，若不遵循，会导致索引失效，转为全表扫描
例：联合索引 (a, b, c)，下列查询条件可以匹配联合索引
- where a = 1;
- where a = 1 and b = 2 and c = 3;
- where a = 1 and b = 2;
由于优化器的存在，所以 a 字段在 where 子句中的顺序不重要
即使写作 where b = 2 and c = 3 and a = 1; 仍会被重排为 where a = 1 and b = 2 and c = 3;
但如果没有 a 字段，就会失效
where a > 1 and b = 2 and c = 3;
只能用到索引 a，因为执行 a > 1 索引扫描会从 a = 2 开始顺序扫描，此时 b 是乱序的，MySQL 并不确定 b = 2 是否还排序在一起，触发索引截断
当只有最后一个字段使用范围查询时，才不会索引截断
什么时候需要创建索引
频繁作为 WHERE 查询条件的字段
显著减少扫描的行数
用于 ORDER BY 和 GROUP BY 的字段
按索引顺序直接输出，避免排序操作（因为 B+ 树是有序结构，按索引顺序输出即可保证有序）
用于 JOIN 条件的字段
外键字段可以加速关联查询
用于覆盖索引的字段
查询可以只从索引返回，无需回表
高基数字段
基数高 = 唯一值多（用户ID、手机号），适合建索引
索引的选择性高，扫描的数据页更少
索引失效
不遵循最左匹配原则
B+ 树查找是需要从左往右匹配字段，不遵循最左匹配原则就会退化为全表扫描
1
2
3
4
5
6-- 有联合索引： (a, b, c)
SELECT * FROM table WHERE b = 1;
# b 没有 a 的值，无法进入对应范围，只能全表扫描
# 正确写法
SELECT * FROM table WHERE a = 1 AND b = 1;使用左或左右模糊匹配
% 开头意味着以任意字符开头
1
2
3
4SELECT * FROM users WHERE name LIKE '%abc'
# 正确写法
SELECT * FROM users WHERE name LIKE 'abc%';WHERE 子句中使用 OR
1
2
3
4
5
6
7SELECT * FROM users WHERE phone = '13812345678' OR email = '[email protected]';
# 假设 phone 和 eamil 都有索引，MySQL 无法同时使用两个索引，会改为全表扫描
# 改为下面这种写法
SELECT * FROM users WHERE phone = '13812345678'
UNION
SELECT * FROM users WHERE eamil = '[email protected]';对索引使用函数
MySQL 无法直接利用索引，只能逐行计算函数值进行比较
1
2
3
4SELECT * FROM users WHERE LEFT(phone, 3) = '138';
# 正确写法
SELECT * FROM users WHERE phone LIKE '138%'; -- 能用索引对索引做表达式计算
该变了字段原本的值，而 B+ 树上存储的是原始值
1
2
3
4SELECT * FROM orders WHERE price + 10 > 100;
# 正确写法
SELECT * FROM orders WHERE price > 90; -- 可以用索引
多少层的 B+ 树能存下 2000W 行数据
基本参数
页大小：16KB = 16 × 1024 = 16,384 字节
假设每条索引项（键+指针）占：16 字节
- 键：8 字节
- 指针：8 字节
每个内部节点（非叶子节点）可容纳的键数：
16,384 / 16 ≈ 1024 个键（→ 1025 个子节点）
假设一条记录平均占用：128 字节
每个叶子节点能容纳的记录数：16384 / 128 = 128 条
推导层级结构
第1层：叶子节点
- 总数据量：20,000,000 行
- 每页记录数：128
- 需要的叶子页数：20,000,000 / 128 ≈ 156,250 页（即叶子节点）
第2层：第一层中间节点（指向叶子节点）
- 一个中间节点可容纳指针数 ≈ 1025
- 页数：156,250 / 1025 ≈ 153 页
第3层：第二层中间节点（指向上一层）
- 153 / 1025 ≈ 0.15，只需要 1 个节点（根节点）
所以总共需要 3 层
锁
锁类型
全局锁
在 MyISAM 中使用，加上之后整个表都是只读状态，用于备份数据库
表级锁
表锁
限制其他线程对该表的写入，但本线程也无法访问其他表
意向锁
用来判断表中是否有记录被加锁
InnoDB 下，加共享锁先前，要现在表级别上加一个意向共享锁
InnoDB 下，加独占锁先前，要现在表级别上加一个意向独占锁
也就是说，当执行插入、更新、删除操作时需要先对表加意向独占锁
表锁粒度大，InnoDB下一般不用，因为对并发性能影响大
行级锁
记录锁
锁住的是一条记录，有 S 锁和 X 锁之分
间隙锁
锁住的是一个范围，可兼容，两个事务可同时持有包含相同间隙范围的间隙锁
临键锁
同时锁住记录和间隙范围，有 S 和 X 之分
插入意向锁
事务在插入记录时，先判断插入位置是否已被其他事务加间隙锁。如果有，那么插入操作会阻塞并生成一个插入意向锁，表示有事务想在某个区间内插入记录
特殊的间隙锁，锁的是一个点
MySQL 为什么要设计这么多锁？
- 平衡性能与开销
- 防止并发事务下的问题
- 适配不同存储引擎
MySQL 死锁
如何产生
必备的四个条件
- 互斥条件：某些资源一次只能被一个事务占用
- 请求与保持条件：一个事务已经获得了某个资源，在等待另一个资源时不会释放自己已占有的资源
- 不剥夺条件：事务获取到的资源，在使用完之前，系统不能强制剥夺
- 循环等待条件：存在一个事务等待链，链中的每一个事务都在等待下一个事务所持有的资源
场景
不同顺序访问同一组表或行
1 | -- 事务1 |
若事务 1 先锁表 A，事务 2 先锁表 B，就会进入相互等待的死锁状态
如何避免
- 始终按照相同的顺序访问资源
- 尽量缩短事务执行时间，减少持锁时间
日志
1 | 底层实现待补充 |
undo log
保证事务的原子性
事务处理过程中出现错误或执行 ROLLBACK 语句，将利用 undo log 中的历史数据回滚版本
实现 MVCC 的关键因素
Read View + undo log 构成版本链，实现多版本并发控制
底层实现
存储结构：撤销段
redo log
当对数据页的修改提交后，都要先写入 redo log，最后以脏页形式刷盘，称作Write-Ahead Logging技术
为什么 redo log 和数据都要写入磁盘
写入方式
写入 redo log 为追加操作，故磁盘操作为顺序写
写入数据需要先找到写入位置，故磁盘操作为随机写
顺序写的效率更高，提高 MySQL 的写入性能
保证事务的持久性
MySQL 崩溃时，通过 redo log 可以保证之前提交的记录不丢失
底层实现
- log buffer：内存中，暂存 redo log 的写入
- redo log files：磁盘中，物理日志文件
binlog
Server 层实现的日志，所有存储引擎都可用，记录了所有数据库表结构变更和表数据修改
追加写，如果写满文件，就新建一个文件继续写，不会覆盖以前的日志，全量保存日志
Buffer Pool
1 | 底层实现待补充 |
作用
InnoDB 下的一片连续内存空间的缓冲池，用来缓存数据页和索引页，减少磁盘 I/O
默认大小为 128MB，可通过 innodb_buffer_pool_size 进行设置，建议为可用物理内存的 60~80%
怎么缓冲？
查询一条记录时，将这条记录所在页都缓冲到 buffer pool 中
如何管理
管理空闲页
Free List
通过 Free 链表来记录控制块，以及一个头节点，头节点包含链表头节点地址、尾节点地址，以及当前链表中节点的数据等信息
管理脏页
Flush List
更新数据时并非每次都刷盘，而是先将对应页标记为脏页，再由后台线程将其写入到磁盘中
什么时候将脏页刷盘
- redo log 满了，自动触发脏页刷新到磁盘
- Buffer Pool 空间不足，需将一部分数据页淘汰掉，若数据页为脏页，则将其刷盘
- MySQL 空闲时
- MySQL 正常关闭前
如果宕机了，脏页数据会不会丢？
不会，InnoDB 采用 WAL 预写日志机制，数据修改之前会先将修改操作写入 redo log，可通过它恢复
如何提高缓存命中率
LRU List
管理脏页 + 干净页，将最近经常查询的数据缓存，不常查询的就淘汰
InnoDB 对 LRU 的优化
- 将其分为 young 和 old 两个区域，加入缓冲池的页，优先插入到 old 区域；页被访问时，才进入 young 区域，用于解决预读失效问题
- 当页被访问或 old 区域停留时间超过阈值，将页插入到 young 区域，否则还是插入到 old 区，解决 批量数据访问，大量热数据淘汰问题