MySQL
MySQL
BufferPool的缓存清除策略
MySQL InnoDB 缓冲池淘汰机制采用改进的 LRU（最近最少使用）算法，核心是将缓存页分为“新生代”（热数据）和“老生代”（冷数据）两个区域，并设置一个 时间阈值。
它解决了标准 LRU 的问题： 全表扫描或预读加载的大量可能只访问一次的“冷”页会被直接加入老生代尾部；即使这些页在短时间内被再次访问，只要间隔小于设定的时间阈值，就不会被提升到新生代。这样能有效防止一次性访问的“冷”数据冲刷掉真正频繁访问的“热”数据。
淘汰时优先从老生代尾部移除冷数据，尤其是干净页。
什么是DoubleWrite
- 解决什么问题？
- InnoDB的数据操作单元是“页”（默认为16KB）。
- 操作系统（OS）和磁盘硬件（如HDD/SSD）的写操作通常以更小的“块”（如4KB）为单位进行。
- 当InnoDB将修改后的脏页刷回磁盘（如
.ibd数据文件）时，如果系统崩溃或断电发生在写这16KB页的中间过程（例如只写了前4KB或前8KB），就会导致该页部分数据更新成功，部分未更新，造成页撕裂/部分写。这种页在逻辑上是损坏的，无法通过redo log（重做日志）恢复，因为redo log的前提是磁盘上的原始页是完整的。
- 如何工作？
- 两步写入： 在将脏页写入其最终数据文件位置之前，InnoDB会先将脏页的副本写入一个专门的、连续分配的磁盘区域，叫做doublewrite buffer（双写缓冲区）。这个缓冲区通常位于系统表空间 (
ibdata1) 中。 - 顺序写入： 多个脏页的副本会被连续地、顺序地写入到doublewrite buffer区域。
- 刷回实际位置： 只有在doublewrite buffer成功写完后，InnoDB才会将这些脏页实际写入（可能是随机写入）到它们各自在表空间数据文件 (
*.ibd) 中的最终位置。
- 两步写入： 在将脏页写入其最终数据文件位置之前，InnoDB会先将脏页的副本写入一个专门的、连续分配的磁盘区域，叫做doublewrite buffer（双写缓冲区）。这个缓冲区通常位于系统表空间 (
- 如何保障数据？
- 如果在第二步（写入实际位置）之前发生崩溃：doublewrite buffer里的副本是完整的（因为是原子写入较小的块或由存储保证），最终位置的数据页未更新或仍是旧的完整页。重启恢复时，InnoDB用doublewrite buffer里的完整副本来覆盖最终位置的数据页。
- 如果在第二步（写入实际位置）期间发生崩溃（部分写）：最终位置的数据页是损坏的，但doublewrite buffer里的副本是完整的。重启恢复时，InnoDB发现最终位置数据页损坏，于是用doublewrite buffer里的完整副本来修复它。
简单说：doublewrite是先写一份完整的“备份”到一个安全区域，再写正式位置。如果写正式位置时出错了，就用安全区的“备份”来修复。它牺牲了一点写性能（两次写），换取了数据页物理完整性的关键保障，使得数据库在崩溃后能安全恢复。 对于使用保证原子写入（如大多数现代SSD的16K原子写入）的系统，可以关闭doublewrite以提升性能，但通常默认启用以保证最大可靠性。
什么是索引下推
- 核心原理（一句话概括）：
将
WHERE子句中与索引列相关的部分过滤条件下推给存储引擎（如InnoDB），在扫描索引过程中提前过滤无效数据，避免将所有索引记录都返回给MySQL服务器层再过滤。
- 具体工作流程对比：
- 未使用索引下推（传统方式）：
- 存储引擎：根据索引定位到满足最左匹配原则的条件的记录。
- 存储引擎→服务器层：将所有满足最左条件的记录对应的主键（或行ID）返回。
- 服务器层：根据主键回表查询完整行数据 → 再应用
WHERE子句剩余的过滤条件进行二次过滤。 - 问题：如果索引初步筛选的结果集很大，但实际匹配的记录很少，会产生大量无效回表操作。
- 使用索引下推（ICP）：
存储引擎：根据索引定位到满足最左匹配条件的记录。
存储引擎内部过滤：直接在索引结构中检查
WHERE子句剩余的其他索引列条件（即使不满足最左匹配）。存储引擎→服务器层：只返回同时满足所有索引列条件的记录的主键。
服务器层：仅对少量主键回表查询完整数据 → 执行最终过滤（如非索引列条件）。
SQL语句的执行过程
①使用连接器，通过客户端/服务器通信协议与 MySQL 建立连接。并查询是否有权限
②Mysql8.0之前检查是否开启缓存，开启了 Query Cache 且命中完全相同的 SQL 语句，则将查询结果直接返回给客户端；
③由解析器(分析器) 进行语法分析和语义分析，并生成解析树。如查询是select、表名users、条件是age='18' and name='Hollis'，预处理器则会根据 MySQL 规则进一步检查解析树是否合法。比如检查要查询的数据表或数据列是否存在等。
④由优化器生成执行计划。根据索引看看是否可以优化
⑤执行器来执行SQL语句，这里具体的执行会操作MySQL的存储引擎来执行 SQL 语句，根据存储引擎类型，得到查询结果。若开启了 Query Cache，则缓存，否则直接返回。
不可重复读怎么解决幻读问题?它解决不了哪些幻读？
MySQL的InnoDB引擎利用间隙锁和临键锁，在其可重复读隔离级别下有效地阻止了其他事务向当前事务查询范围插入新行，从而防止了幻读的发生。
MySQL的可重复读隔离级别通过MVCC和Next-Key Lock解决了大部分幻读场景，但两类操作仍可能突破防线：
- 事务内混用快照读和当前读时，因数据可见性不一致导致逻辑幻读。
- 在表末尾插入数据时，因间隙锁无法覆盖无限区间而产生物理幻读。
锁
全局锁
- 锁的粒度：整个数据库实例。
- 特点：
- 加锁后，整个数据库处于只读状态，所有表的写操作（如
INSERT、UPDATE、DELETE）和表结构修改（如ALTER TABLE）都会被阻塞。 - 典型使用场景：全库逻辑备份（如
mysqldump）。
- 加锁后，整个数据库处于只读状态，所有表的写操作（如
- 实现方式：
- 使用
FLUSH TABLES WITH READ LOCK (FTWRL)命令加锁。 - 使用
UNLOCK TABLES命令释放锁。
- 使用
表级锁
- 锁的粒度：整张表。
- 特点：
- 并发性能较低，但开销较小，适用于读多写少的场景。
- 支持存储引擎：MyISAM、InnoDB。
- 子类锁：
- 表锁：
- 共享读锁（
READ LOCK）：- 允许其他事务读取表，但阻塞写操作。
- 示例：
LOCK TABLES tb_user READ;
- 排他写锁（
WRITE LOCK）：- 阻塞其他事务的读和写。
- 示例：
LOCK TABLES tb_user WRITE;
- 共享读锁（
- 元数据锁（MDL）：
- 自动控制，无需显式加锁。
- 作用：防止 DML（增删改查）与 DDL（表结构修改）冲突。
- 锁类型：
- 共享锁（
SHARED_READ/SHARED_WRITE）：用于读/写操作。 - 排他锁（
EXCLUSIVE）：用于表结构修改（如ALTER TABLE）。
- 共享锁（
- 意向锁（Intent Lock）：
- 协调行锁与表锁，减少冲突检查。
- 类型：
- 意向共享锁（
IS）：事务计划对某些行加共享锁。 - 意向排他锁（
IX）：事务计划对某些行加排他锁。
- 意向共享锁（
- 表锁：
行级锁
- 锁的粒度：单个行。
- 特点：
- 并发性能高，但开销较大，适用于高并发写操作的场景。
- 依赖索引：只有通过索引条件检索数据时，InnoDB 才使用行级锁；否则退化为表级锁。
- 支持存储引擎：InnoDB。
- 锁类型：
- 记录锁（Record Lock）：
- 锁定索引记录。
- 示例：
SELECT * FROM table WHERE id = 1 FOR UPDATE;
- 间隙锁（Gap Lock）：
- 锁定索引之间的间隙，防止幻读。
- 示例：
SELECT * FROM table WHERE id BETWEEN 10 AND 20 FOR UPDATE;
- 临键锁（Next-Key Lock）：
- 记录锁 + 间隙锁，防止幻读。
- 是 InnoDB 默认的行锁模式。
- 记录锁（Record Lock）：
修改一条记录什么情况下会锁表？什么情况下会锁行？
| 情况 | 锁表还是锁行？ | 关键决定因素 |
|---|---|---|
| WHERE 无法利用索引 | 几乎总是锁表 | 数据库无法精确定位目标行，只能进行全表扫描 |
| 显式表锁操作 | 锁表 | 显式执行了 LOCK TABLES 等命令 |
| DDL 操作（ALTER等） | 锁表 | 修改表结构需要独占整个表 |
| 大量修改导致锁升级 | 锁表 | 修改行数过多，超出阈值 |
| 没有 WHERE 条件 | 锁表 | 操作涉及表中所有行 |
| WHERE 能利用索引 | 锁行 (包括索引记录和可能的间隙) | 数据库能通过索引精确定位一个或多个目标行 |
| 主键/唯一键冲突的插入 | 锁行 (锁冲突的具体索引记录) | 能精确定位到已存在的键值 |
| 意向锁 (IS, IX) | 不是锁表操作！ (协调锁的辅助机制，通常与其他行锁或表锁共存) | 表明事务的意图（稍后要在某些行加锁）。虽然作用在表级别，但本身非常轻量且兼容，不影响大部分并发操作 |