InnoDB 内部原理

InnoDB 是 MariaDB 默认存储引擎，理解它的内部 ≈ 理解 80% 的 MariaDB 调优。

整体架构

+----------------------------------------------------------+
|                   Server Layer                           |
|  parser / optimizer / executor / connection mgmt          |
+----------------------------------------------------------+
                       |
+----------------------------------------------------------+
|                   InnoDB Engine                          |
|                                                          |
|   +-------------------+    +---------------------+       |
|   |  Buffer Pool      |    |  Change Buffer      |       |
|   |  (data + index)   |    |  (secondary idx)    |       |
|   +-------------------+    +---------------------+       |
|                                                          |
|   +-------------------+    +---------------------+       |
|   |  Redo Log Buffer  |    |  Undo Log Buffer    |       |
|   +-------------------+    +---------------------+       |
+-------|--------------------------|----------------------+
        |                          |
        v                          v
+----------------+     +----------------------+
|  ib_logfile*   |     |  *.ibd / ibdata1     |
|  (redo)        |     |  (data + undo)       |
+----------------+     +----------------------+

1. 数据组织：聚簇索引 + B+ 树

InnoDB 表的数据本身按主键排序存储——这叫聚簇索引（clustered index）。

              +-----------------+
              |  Root (page)    |
              |  [PK=100, ptr]  |
              |  [PK=500, ptr]  |
              +--------+--------+
                  /         \
        +---------+         +---------+
        | Node    |         | Node    |
        | 1-99    |         | 500-...|
        +----+----+         +----+----+
             |                   |
    +--------+------+      +-----+------+
    | Leaf (page)   |      | Leaf       |
    | [PK=1,  row]  |      | [PK=500,…] |
    | [PK=2,  row]  |      |            |
    | ...           |      |            |
    +---------------+      +------------+

一个 page 默认 16 KB
叶子节点存整行数据
叶子节点之间双向链表——支持范围扫描

二级索引（Secondary Index）

CREATE INDEX idx_email ON users(email);

二级索引也是 B+ 树，但叶子存的是主键值而非整行：

[email=alice@x.com → PK=42]
[email=bob@x.com   → PK=17]

查 WHERE email = ?：

在二级索引找到 PK
用 PK 回到聚簇索引拿整行（回表）

覆盖索引就是"二级索引里已经够了不用回表"——所以 SELECT * 是性能杀手。

为什么主键不能太长

二级索引的每个叶子都存了一份主键。主键 16 字节 ≠ 4 字节——索引可能膨胀 4 倍。所以主键推荐 BIGINT AUTO_INCREMENT，不要用 UUID v4（除非真有需要）。

UUID v7（时间有序）勉强可用，因为新插入接近末尾，碎片更少。

2. Buffer Pool（内存里的数据）

+-------------------------------------------+
|         Buffer Pool (innodb_buffer_pool_size) |
|                                           |
|   +-------+  +-------+  +-------+         |
|   | page  |  | page  |  | page  |  ...    |
|   | 16KB  |  | 16KB  |  | 16KB  |         |
|   +-------+  +-------+  +-------+         |
|       LRU list (分代)                     |
+-------------------------------------------+

默认 128 MB——生产必须调大到 RAM 的 60–70%
用分代 LRU：新读入的 page 进"年轻代"中间，被多次访问才进"老年代"
这样全表扫描不会冲掉热数据

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';

命中率

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- hit_rate = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests

< 99% 通常说明 buffer pool 太小。

3. Redo Log（持久性保证）

事务提交时，不需要把脏页立刻写磁盘——太慢。InnoDB 用 WAL（Write-Ahead Log）：

1. 修改 page，标记 dirty
2. 写 redo log entry 到 redo log buffer
3. COMMIT 时：
   - 把 redo log buffer flush 到 ib_logfile
   - 此时事务"持久"了，即使断电
4. 后台慢慢把 dirty page flush 到 *.ibd

崩溃恢复时，按 redo log 重放未应用到磁盘的变更。

关键参数

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- 1 (默认): 每事务提交都 fsync redo log → 强持久
-- 2: 每次写到 OS 缓存，每秒 fsync → OS 崩可能丢 1 秒
-- 0: 每秒写 + fsync → MariaDB 崩可能丢 1 秒

SHOW VARIABLES LIKE 'innodb_redo_log_capacity';   -- MariaDB 10.11+
-- 总大小，建议 1-4 GB

不要为了性能把 innodb_flush_log_at_trx_commit 设 0。MariaDB 进程崩溃就丢数据，连断电都不用。

4. Undo Log（回滚 + MVCC）

每次写操作前，InnoDB 把修改前的行版本写到 undo log。两个用途：

ROLLBACK：撤销修改时按 undo 还原
MVCC：其他事务读时根据 undo 拼出"应该看到的旧版本"

MVCC（多版本并发控制）

Time
  |
  |  T1: SELECT * FROM accounts WHERE id = 1;  -- 看到 balance=100
  |
  |  T2: UPDATE accounts SET balance = 50 WHERE id = 1; COMMIT;
  |
  |  T1: SELECT * FROM accounts WHERE id = 1;  -- 在 REPEATABLE READ 下仍看到 100
  v

T1 的第二次 SELECT 不会被 T2 影响——InnoDB 通过 undo log 给 T1 重建出"T1 开启事务那一刻"的数据快照。

代价：undo log 累积。长事务会让 undo 越来越大（"history list length"），影响性能。

-- 看 undo 历史长度
SHOW ENGINE INNODB STATUS\G  -- 找 "History list length"

一般 < 数万正常；几十万+ 说明有长事务卡着，需要排查。

5. 锁

行锁（Record Lock）

START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 锁住这一行
-- 其他事务对这一行的 UPDATE/DELETE 都会等

间隙锁（Gap Lock）

在 REPEATABLE READ 下，**范围扫描会锁住"中间的空隙"**防止幻读：

-- 表里有 id=10, 20, 30
SELECT * FROM tbl WHERE id BETWEEN 11 AND 19 FOR UPDATE;
-- 锁住 (10, 20) 的区间，其他事务不能 INSERT id=15

间隙锁是死锁的常见来源——业务模型尽量避免范围更新。

Next-Key Lock

= Record Lock + 它前面的 Gap Lock。InnoDB 默认的锁模式。

锁排查 SQL

SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

SHOW ENGINE INNODB STATUS\G  -- 死锁信息在 "LATEST DETECTED DEADLOCK" 节

6. Change Buffer

对二级索引的写入先缓存在 change buffer，合并后再写到 page。优化随机 IO。

仅对非唯一二级索引有效（唯一索引必须立即校验唯一性）
写密集表受益巨大
innodb_change_buffering / innodb_change_buffer_max_size 控制

7. Doublewrite Buffer

为了防止"页损坏"（写一半时崩），InnoDB 先写到 doublewrite buffer 再写到目标 page。

性能开销 ~5%
强烈建议保留开（生产绝对不要关）
SSD 上开销已经很小

SHOW VARIABLES LIKE 'innodb_doublewrite';

8. 实际调优 checklist

参数	推荐	影响
`innodb_buffer_pool_size`	RAM × 60–70%	命中率，决定性能上限
`innodb_buffer_pool_instances`	RAM/4G	减少锁竞争
`innodb_redo_log_capacity`	2–4 GB	减少 checkpoint 频率
`innodb_flush_log_at_trx_commit`	1	持久性（别动）
`innodb_flush_method`	O_DIRECT	Linux 上避免双缓存
`innodb_io_capacity`	SSD: 2000+	后台 IO 速率
`innodb_io_capacity_max`	2 × io_capacity	突发 IO 上限
`innodb_lock_wait_timeout`	50 (默认)	锁等待超时
`transaction_isolation`	REPEATABLE-READ	默认
`sync_binlog`	1	binlog 持久性

9. 监控关键指标

-- 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';                  -- 物理读
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';          -- 逻辑读

-- 脏页比例
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

-- 慢的 page flush
SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';   -- > 0 说明 IO 不够

-- 长事务
SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS sec
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30;