概述

一个数据库（这里特指 MySQL）最基础的能力是对数据进行增删改查。在此基础上，MySQL 主要需要解决两个核心问题：数据如何组织，才能高效、稳定地访问；数据在并发读写时，如何保证正确性。

围绕第一个问题，MySQL 形成了以索引为核心的一套存储与查询机制，包括数据页、B+ Tree、聚簇索引、二级索引等；围绕第二个问题，MySQL 提供了事务机制，并进一步涉及 ACID、隔离级别、锁、MVCC、Undo Log、Redo Log 等。

因此，理解 MySQL 的核心原理，主要就是两块内容：索引解决数据如何高效访问的问题，事务解决数据如何正确读写的问题。

索引

InnoDB 的数据最终要持久化到磁盘，但磁盘并不是数据库每需要一条记录，就单独读取这一条。为了提高 I/O 效率，InnoDB 会把数据按照 Page（页） 进行组织，页是 InnoDB 磁盘管理和读取的重要基本单位，默认大小是 16KB。当我们需要查询数据时，如果任何额外的数据结构来告诉我们去那个page查，那么它就只能不断检查数据页、检查里面的记录。数据越多，需要检查的数据就越多。这样的话性能就是O(n)的，是比较慢的。能不能建立一种额外的数据结构，让 MySQL 不需要扫描大量数据，就能快速定位目标记录所在的位置？即根据某一个数据的key来快速找到这个数据所在的page。

数据结构

数据结构的选择都有哪些呢：

  • 有序数组查询很好，可以二分做到 O(log n)，范围查询也方便，但插入和删除时为了维持有序，可能需要移动大量元素，所以不适合频繁更新
  • Hash 表做等值查询非常快，平均可以接近 O(1)，但它不保序，因此 BETWEEN、ORDER BY、前缀范围这类查询很不友好；
  • 二叉搜索树既能保持有序，也支持动态插入删除，但一个节点只有两个分支，数据量一大树就会比较高，而数据库索引节点本身通常也在磁盘 Page 中，所以树高意味着可能需要更多次 Page 访问和磁盘 I/O；
  • 平衡二叉树或红黑树能解决普通二叉树退化的问题，但本质上仍然是二叉结构，分支数太少，树高依然不够理想。
  • B Tree 这类多叉树：B Tree 是一种多叉平衡树，一个节点中可以存放多个 key，因此相比二叉树能够显著降低树高，减少查询过程中需要访问的 Page 数量。不过，B Tree 的数据既可能存放在非叶子节点，也可能存放在叶子节点，因此不同查询的访问路径长度可能不同；同时，进行范围查询时，往往需要在树的多个节点之间反复遍历，连续扫描的效率不够理想。
  • B+ Tree 又进一步把真正的数据主要放在叶子节点，非叶子节点尽量只保存 key 和子页指针，这样一个 Page 能容纳更多索引项，树更矮，同时叶子节点之间按顺序连接，也特别适合范围查询。因此从数据库场景来看，B+ Tree 并不是单纯“查找复杂度最好”，而是它在等值查询、范围查询、动态增删和减少磁盘 I/O之间取得了非常好的平衡。

聚簇索引和二级索引

B+ 树解决了索引应该采用什么数据结构的问题，但还需要继续考虑：一张表中的真实数据，究竟应该怎样放进这棵 B+树中？ 在 InnoDB 中，表数据会按照主键组织成一棵 B+树，非叶子节点主要保存主键和子页指针，叶子节点则保存完整的行数据。由于数据本身就存储在这棵索引树的叶子节点中，因此这棵索引被称为聚簇索引（Clustered Index）。

也就是说，B+ 树里的节点在物理实现上通常对应一个 InnoDB Page。非叶子 Page 里主要放索引 key 和指向子 Page 的页号，叶子 Page 里放真正的索引记录；如果这是聚簇索引，那么叶子 Page 里的记录就是完整行数据。

聚簇索引解决了通过主键快速定位数据的问题，但实际查询并不总是根据主键进行。例如我们还可能经常根据 name、email、age 等其他字段进行查询。如果这些字段没有索引，那么即使已经有聚簇索引，MySQL 仍然可能需要扫描大量叶子节点中的数据。

因此，InnoDB 允许针对其他列再建立额外的 B+ Tree，这些索引称为二级索引（InnoDB中也可以叫非聚簇索引）。二级索引同样以 Page 作为树节点，但它的叶子节点不会保存完整行数据，而是保存索引列的值以及对应的主键值。当通过二级索引查询完整数据时，先在二级索引中找到对应的主键，再根据主键到聚簇索引中查找完整行，这个过程就是回表。由于回表意味着需要再次访问聚簇索引，会带来额外的查询开销，因此如果某个查询经常执行，可以考虑让二级索引本身就包含这次查询所需要的字段。这样查询结果可以直接从二级索引中获得，不再需要回到聚簇索引查找完整行，这种情况就称为覆盖索引。覆盖索引的本质，就是通过让索引覆盖查询所需的全部字段，来减少甚至避免回表。既然把更多字段放进索引可以减少回表，那为什么不干脆把所有字段都放进去？因为索引不是免费的。索引本身也要占用磁盘空间，而且字段越多，单个索引记录越大，一个 Page 能容纳的索引项就越少，B+树 可能变得更高，从而增加查询时的 Page 访问次数；同时，每次 INSERT、UPDATE、DELETE 时，不仅要修改表数据，还要维护相关索引，索引越多、越宽，写入成本就越高。除此之外，很多字段本身并不具备良好的区分度，例如性别、状态这类字段即使建索引，也未必能明显减少扫描范围。因此索引设计本质上是在查询性能、存储空间和写入维护成本之间做权衡，并不是越多越好，而是应该优先为高频查询条件、排序字段以及有实际覆盖价值的字段建立索引。

联合索引

但如果每个字段都单独建立索引，不仅会增加存储空间，也会提高写入时维护索引的成本。因此，MySQL 允许把多个字段按照一定顺序组织到同一棵 B+ Tree 中，这就是联合索引。例如建立 (name, age) 联合索引时，B+ Tree 中的 key 不再只是单个字段，而是由 name 和 age 共同组成。联合索引既可以用于提高多条件查询效率，也可以在索引包含查询所需字段时形成覆盖索引。不过，由于联合索引中的字段存在固定顺序，它能否被有效利用还取决于查询条件是否符合这个顺序，这就进一步引出了最左前缀原则。不过，即使已经建立了索引，也不意味着每次查询都一定能够有效利用它。除了联合索引需要遵循最左前缀原则之外，一些查询写法也可能让 MySQL 无法利用索引原本的有序性，例如对索引列进行函数计算、发生隐式类型转换，或者使用以 % 开头的模糊查询等。这些情况通常被统称为索引失效。 索引失效常见场景主要有这些：

  1. 对索引列做函数、计算或表达式操作，例如 WHERE YEAR(create_time)=2026、WHERE age + 1 = 20。因为 B+ 树里存的是原始列值，经过计算后通常不能直接按原有顺序定位。
  2. 发生隐式类型转换，例如字符串类型的 phone 列，却写成 WHERE phone = 13800138000。MySQL 可能需要对列做类型转换，从而影响索引使用。
  3. 联合索引不满足最左前缀原则。例如联合索引 (a,b,c)，直接查询 WHERE b=2，通常无法有效利用这棵联合索引进行定位。
  4. LIKE 以 % 开头，例如 LIKE '%abc' 或 LIKE '%abc%'。因为无法确定索引扫描的起始位置；而 LIKE 'abc%' 通常可以利用索引。
  5. 范围条件导致后续列无法继续用于定位。例如联合索引 (a,b,c)，查询 a=1 AND b>10 AND c=3，a 和 b 可以用于定位范围，但 c 通常不能继续缩小 B+ Tree 的扫描区间。不过 c 仍可能通过 Index Condition Pushdown 参与过滤，所以不要简单说“后面的索引完全失效”。
  6. 使用 !=、<>、NOT IN、NOT LIKE 等否定条件时，可能不走索引。不是绝对失效，而是因为符合条件的数据可能很多，优化器可能认为全表扫描成本更低。
  7. IS NULL / IS NOT NULL 是否走索引取决于数据分布。它们并不是天然导致索引失效。如果 NULL 很少，索引可能很有效；如果绝大多数数据都是 NULL，优化器可能放弃索引。
  8. OR 两边的条件无法都有效使用索引时，可能导致整体放弃索引。例如 WHERE indexed_col=1 OR non_indexed_col=2。但如果两边都有合适索引，MySQL 也可能使用 index_merge，所以这也不是绝对规则。
  9. 索引区分度太低或查询返回的数据太多。例如性别、状态这类字段，即使有索引，如果一次查询要返回表中 80% 的数据，随机回表的成本可能比全表扫描还高，于是优化器会主动不走索引。
  10. 字符集、排序规则或比较方式不匹配，有时会引入隐式转换，从而影响索引利用，例如 JOIN 两边字段类型或字符集不一致。

可以浓缩成一句：

常见的有：索引列参与函数或计算、隐式类型转换、联合索引不满足最左匹配、前缀 % 模糊查询、范围查询后续列无法继续定位，以及因为 OR、否定条件、低选择性或返回数据量过大导致优化器主动放弃索引。

Explain

了解了常见的索引失效场景之后，还需要进一步判断一条 SQL 在实际执行时到底有没有使用索引、使用了哪个索引，以及扫描了多少数据。仅靠 SQL 写法本身往往不能完全确定，因为 MySQL 最终会由优化器根据成本选择执行计划。因此，可以使用 EXPLAIN 查看 SQL 的执行计划，从而分析查询是否走索引、索引是否使用合理，以及是否存在全表扫描等性能问题。

在了解了索引失效的常见场景之后，还需要进一步确认一条 SQL 在实际执行时到底有没有使用索引、使用了哪个索引，以及大概需要扫描多少数据。因为 MySQL 最终是否使用索引，并不是只由 SQL 写法决定的，而是由优化器根据成本选择执行计划。因此，可以使用 EXPLAIN 来查看 MySQL 对一条 SQL 的执行计划。

例如：

1

EXPLAIN SELECT * FROM user WHERE name = 'Tom';

在 EXPLAIN 的结果中，最值得关注的字段主要有 type、possible_keys、key、rows 和 Extra。其中，possible_keys 表示这条 SQL 理论上可能使用哪些索引，key 表示优化器最终实际选择了哪个索引；rows 表示优化器估算需要扫描的行数，可以用来大致判断查询的数据访问量。

其中最重要的字段之一是 type，它表示 MySQL 访问数据的方式。常见的访问类型大致可以按照：

system / const → eq_ref → ref → range → index → ALL

来理解。通常越靠前，访问的数据范围越小。例如根据主键或唯一索引进行等值查询时，可能出现 const；通过普通索引进行等值查询时，常见 ref；范围查询可能出现 range；index 表示需要扫描整棵索引，而 ALL 则表示全表扫描。因此，如果看到 ALL，通常就需要进一步分析是不是缺少合适的索引，或者优化器认为使用索引的成本反而更高。

Extra 则会提供一些额外的执行信息。例如 Using index 通常表示查询所需要的数据可以直接从索引中获取，也就是使用了覆盖索引；Using filesort 表示排序无法直接利用索引顺序，需要进行额外排序；Using temporary 表示执行过程中需要使用临时表，这些情况都可以帮助判断 SQL 是否还有优化空间。

因此，在实际分析一条 SQL 时，可以按照一个比较简单的顺序来看 EXPLAIN：先通过 type 判断整体访问方式，再通过 key 确认实际使用了哪个索引，然后结合 rows 判断扫描数据量，最后查看 Extra 中是否出现覆盖索引、额外排序、临时表等信息。这样就可以把前面关于索引、索引失效和覆盖索引的知识真正落到 SQL 执行分析上。

事务

索引主要解决的是数据访问效率问题，但数据库不仅要查得快，还要保证数据在并发读写和异常情况下仍然是正确的。因此，数据库需要把一组相关操作看成一个不可分割的整体，要么全部成功，要么全部失败，这就是事务（Transaction）。

事务通常用 ACID 来描述它需要提供的保证，包括原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。而在 MySQL InnoDB 中，这些能力并不是凭空实现的，而是依赖 Undo Log、Redo Log、锁、MVCC、隔离级别等一系列具体机制共同完成。

ACID

ACID 就是事务希望保证的四个性质：

  • Atomicity（原子性）：一个事务里的操作要么全部成功，要么全部失败，不能只执行一半。比如转账时，扣款和加款必须一起成功。
  • Consistency（一致性）：事务执行前后，数据库都应该处于合法、正确的状态。比如账户余额不能因为一次转账凭空多出或少掉。
  • Isolation（隔离性）：多个事务同时执行时，彼此之间不能随意互相干扰。不同隔离级别，本质上就是规定“事务之间能看到对方多少中间状态”。
  • Durability（持久性）：事务一旦提交成功，即使数据库随后宕机，已经提交的数据也不应该丢失。

事务之所以需要隔离性，是因为多个事务并发执行时，一个事务可能会受到其他事务读写操作的影响，从而看到本不应该看到的数据，或者在同一个事务中前后读取到不同的结果。根据这种影响的不同，主要会出现三类典型问题：脏读、不可重复读和幻读。脏读是指读取到了其他事务尚未提交的数据；不可重复读是指同一个事务中多次读取同一条记录，得到的结果发生了变化；幻读则是指同一个事务中多次按照相同条件查询时，得到的记录集合发生了变化。为了控制这些并发问题，数据库才进一步定义了不同强度的事务隔离级别。

事务隔离级别

四种标准事务隔离级别，从弱到强分别是：

  • Read Uncommitted（读未提交）：一个事务可以读到另一个事务还没有提交的数据，所以可能出现脏读。
  • Read Committed（读已提交）：只能读取已经提交的数据，解决了脏读，但同一个事务中两次读取同一行，可能因为别的事务中途提交修改而得到不同结果，这就是不可重复读。
  • Repeatable Read（可重复读）：保证同一个事务中多次读取同一行结果一致，解决不可重复读。MySQL InnoDB 默认就是这个级别，并通过 MVCC 等机制进一步处理很多并发读问题。
  • Serializable（串行化）：隔离最强，事务效果接近串行执行，并发能力也最低。

MVCC

前面讲的事务隔离级别，本质上是在规定不同事务之间的数据可见性，但这些隔离效果还需要具体机制来实现。为了在保证隔离性的同时尽量减少读写之间的阻塞，InnoDB 引入了 MVCC（Multi-Version Concurrency Control，多版本并发控制）。它的核心思想是为同一条数据保留多个版本，让不同事务根据各自的可见性规则读取应该看到的版本，而不是所有事务都直接读取最新数据。

要实现“多个版本”，InnoDB 在每条记录中会维护一些隐藏信息，其中比较重要的是最后修改这条记录的事务 ID，以及一个指向旧版本的 roll pointer。当事务修改一条数据时，并不会简单地把旧数据彻底覆盖掉，而是会把修改前的信息记录到 Undo Log 中，同时让当前记录通过 roll pointer 指向对应的旧版本。这样一条记录经过多次修改后，就可以沿着 Undo Log 形成一条版本链。

有了版本链之后，还需要解决另一个问题：当前事务到底应该看到哪个版本？ 这就是 Read View 的作用。Read View 可以理解为事务在某个时刻对系统中活跃事务状态做的一份快照，其中比较重要的几个信息包括：m_ids，表示创建 Read View 时仍然活跃、尚未提交的事务 ID 集合；min_trx_id，表示这些活跃事务中最小的事务 ID；max_trx_id，表示创建 Read View 时下一次将要分配的事务 ID；以及 creator_trx_id，表示创建这个 Read View 的事务自身 ID。当读取某个数据版本时，InnoDB 会拿这个版本对应的 trx_id 和 Read View 进行比较。如果这个版本就是当前事务自己修改出来的，那么可以直接看到；如果 trx_id < min_trx_id，说明生成这个版本的事务在 Read View 创建之前就已经提交，因此通常可见；如果 trx_id >= max_trx_id，说明这个版本是在 Read View 创建之后才产生的，因此不可见；如果 trx_id 落在 min_trx_id 和 max_trx_id 之间，则还需要判断它是否存在于 m_ids 中，如果存在，说明对应事务当时还没有提交，因此不可见；如果不存在，则说明它在 Read View 创建之前已经提交，因此可见。

如果当前版本不可见，InnoDB 就会沿着 Undo Log 形成的版本链继续向前查找，直到找到一个符合可见性规则的版本。因此，Read View 本质上就是一套判断某个数据版本对于当前事务是否可见的规则。

因此，MVCC 实际上可以拆成两个核心部分：Undo Log 和版本链负责解决“历史版本在哪里”，Read View 负责解决“当前事务应该看到哪个版本”。 在这个基础上，InnoDB 再通过不同的 Read View 创建策略，实现读已提交和可重复读不同的数据可见性效果。

有了版本链和 Read View 之后，InnoDB 就可以通过控制 Read View 的创建时机，实现不同的事务隔离级别。

在读已提交级别下，事务中的每一次快照读都会重新生成一个新的 Read View。也就是说，如果其他事务在两次查询之间提交了修改，那么第二次查询生成的新 Read View 就可能认为这个新版本已经可见，因此同一个事务前后两次读取同一条数据，可能得到不同的结果。

在可重复读级别下，事务第一次进行快照读时会生成一个 Read View，之后的快照读通常继续复用这个 Read View。这样，即使其他事务在此期间提交了新的数据版本，当前事务仍然按照最初的可见性规则去选择版本，因此前后多次读取能够保持一致。

因此，两者的核心区别可以简单理解为：读已提交是“每次查询都重新判断一次当前哪些版本可见”，而可重复读是“事务内尽量按照第一次查询时的可见性规则持续读取”。

不过，可重复读主要解决的是同一条记录前后读取不一致的问题，幻读仍然需要额外处理。在 InnoDB 的可重复读隔离级别下，对于普通 SELECT 这类快照读，主要通过 MVCC 和 Read View 保证前后查询结果的一致性；而对于 SELECT ... FOR UPDATE、UPDATE、DELETE 这类当前读，则需要配合行锁、间隙锁和临键锁，阻止其他事务在相关范围内插入新的记录，从而处理幻读问题。

这里提到的行锁、间隙锁和临键锁，就是 InnoDB 在当前读场景下实现并发控制的具体锁机制。行锁用于锁住已经存在的某条索引记录，主要防止其他事务同时修改这条数据；间隙锁锁住的是索引记录之间的区间，本身并不锁具体记录，它的作用是阻止其他事务在这个范围内插入新的数据；**临键锁（Next-Key Lock）**则可以理解为行锁和间隙锁的组合，既锁住已有记录，也锁住对应的间隙。因此，在可重复读隔离级别下，InnoDB 可以通过这些锁限制其他事务对当前查询范围的修改和插入，从而保证当前读时的数据范围不会被随意改变。具体使用哪一种锁，一般由 InnoDB 根据 SQL 条件、索引以及查询范围自动决定。

Undo log(原子性)

前面我们讲事务的隔离性时，其实已经碰到过 Undo Log：MVCC 会利用它保存旧版本，形成版本链。但 Undo Log 还有一个更基础的作用，就是帮助事务实现原子性。

当一个事务修改数据时，InnoDB 会先记录相应的 Undo 信息。比如把 age 从 20 改成 30，Undo Log 会保留“修改前”的信息；如果事务后面执行失败或者主动 ROLLBACK，InnoDB 就可以根据 Undo Log 把数据恢复到修改之前的状态。因此，Undo Log 可以理解为数据库为事务准备的一份“反向操作记录”。

比如：

1

UPDATE user SET age = 30 WHERE id = 1;

如果原来 age = 20，那么 Undo Log 会保留能够把这次修改还原回 20 的信息。事务提交后，这些 Undo 记录也不会一定立刻删除，因为前面讲过，MVCC 可能仍然需要旧版本；只有当确认没有活跃事务再需要这些历史版本时，后台 Purge 才会逐步清理。

Redo log(持久性)

事务的持久性要求：一旦事务提交成功，即使数据库随后发生宕机，已经提交的数据也不能丢失。

InnoDB 在读写数据时，并不是每次都直接操作磁盘中的数据页，而是会先把磁盘中的 Page 加载到内存中的 Buffer Pool。当执行 UPDATE 时，通常也是先修改 Buffer Pool 中的数据页，此时这个页已经和磁盘中的内容不一致，被称为脏页。

如果每次修改数据后，都立刻把整个脏页写回磁盘，磁盘随机 I/O 的成本会非常高。因此 InnoDB 并不会要求事务提交时马上把所有修改过的数据页刷盘，而是先把这次修改记录到 Redo Log 中，并优先保证 Redo Log 持久化。Redo Log 是顺序写，写入成本比随机刷新大量数据页低得多。

因此整体过程可以简单理解为：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

磁盘数据页
   ↓
加载到 Buffer Pool
   ↓
在内存中修改数据
   ↓
写 Redo Log
   ↓
事务提交
   ↓
数据页之后再慢慢刷回磁盘

如果事务提交后、数据页还没有刷盘时数据库突然宕机，Buffer Pool 中的数据会丢失，但已经持久化的 Redo Log 仍然存在。数据库重启后，可以根据 Redo Log 把已经提交的修改重新恢复到数据页中，这就是 Crash Recovery（崩溃恢复）。

这种“先写日志，再写数据页”的思想就是 WAL（Write-Ahead Logging）。也就是说，不需要在事务提交时立刻把完整数据页写入磁盘，只要先保证描述这次修改的 Redo Log 已经可靠落盘即可。

不过 MySQL 中除了 InnoDB 的 Redo Log，还有 Server 层的 Binlog。Redo Log 主要用于 InnoDB 的崩溃恢复，而 Binlog 主要用于主从复制、数据恢复等场景。因此一个事务提交时，需要保证 Redo Log 和 Binlog 对同一个事务的状态保持一致，否则可能出现“Redo Log 认为事务提交了，但 Binlog 中没有这个事务”，或者反过来的情况。

为了解决这个问题，MySQL 在事务提交时会使用两阶段提交。可以简单理解为：

1
2
3
4
5
6
7
8
9

修改 Buffer Pool
      ↓
写 Redo Log
状态：prepare
      ↓
写 Binlog
      ↓
Redo Log
状态：commit

第一阶段，InnoDB 先把 Redo Log 写入并标记为 prepare；随后 MySQL Server 写入 Binlog；Binlog 成功后，再把 Redo Log 标记为 commit。这样即使在提交过程中发生宕机，MySQL 也可以通过 Redo Log 和 Binlog 的状态判断这个事务最终应该提交还是回滚，从而保证两份日志的一致性。

首先，通过“修改 Buffer Pool + 先写 Redo Log + 后续再刷数据页”的方式，在兼顾性能的同时保证事务持久性；其次，通过 Redo Log 与 Binlog 的两阶段提交，保证事务在存储引擎和 Server 层日志之间保持一致。