SQL执行过程
在进入流程之前，先明确 MySQL 的分层：Server 层和存储引擎层。Server 层负责连接管理、语法解析、优化、执行调度，所有存储引擎共用这一套逻辑；存储引擎层（默认 InnoDB）负责数据的实际存取、索引、事务、锁和 MVCC。你写的 SQL 绝大多数“逻辑”发生在 Server 层，而“物理”读写和并发控制发生在引擎层。
第一阶段：连接建立——TCP、认证与会话初始化
1. TCP 三次握手与协议握手
JDBC 驱动（或任何客户端）首先与 MySQL 服务端的监听端口（默认 3306）完成 TCP 三次握手。但这只是建立了网络通道，MySQL 协议本身还有一次协议层面的握手：服务端主动发送Initial Handshake Packet，告知客户端协议版本、服务端版本以及默认的认证方式；客户端据此回复认证信息。这与很多“客户端先发请求”的协议不同，MySQL 是服务端先发起握手的。
2. 身份认证（Authentication）
TCP 通道建立后，连接器开始校验身份。客户端将用户名、密码（加密后的挑战响应）和来源主机信息发给服务端，服务端对照mysql.user等系统表进行验证。Host + User + 密码三者匹配才会放行；任何一项不符，直接返回Access denied for user，连接终止。
3. 权限获取与会话快照
认证通过后，连接器会从权限表中查出该用户拥有的全部权限，并将其缓存在当前连接对象上。后续这条连接上的所有操作，权限判断都基于这份缓存，而不是每次都回查系统表。
这带来一个在 Java 生产环境中经常被忽略的行为：权限变更不会影响已存在的连接。如果你在数据库里给某个账号加了权限，但应用使用的是连接池中的长连接，这些老连接仍然沿用旧权限快照，新权限只有在新建立的连接上才会生效。遇到“权限改了但应用还是报错”时，先考虑连接池是否需要主动回收或重建。
4. 连接状态与长连接的内存代价
连接空闲后状态变为Sleep，由wait_timeout控制空闲断开时间，默认 8 小时。Java 应用通常通过连接池（HikariCP、Druid）维持长连接，以避免频繁 TCP 握手和认证的开销。但长连接并非没有成本：MySQL 在执行过程中分配的部分内存（如临时表、排序缓冲区）是挂在连接对象上的，只有连接断开才会释放。长连接持续累积，可能导致内存上涨甚至 OOM。
MySQL 5.7+ 提供了mysql_reset_connection，可以在不断开 TCP 连接的前提下重置会话状态、释放连接级内存，同时不重新做权限认证。在连接池中配置合理的maxLifetime和空闲检测，本质上就是在管理这个权衡。
第二阶段：SQL 执行——从文本到数据的完整路径
连接建立后，一条 SQL 进入 Server 层，依次经过以下环节。
1. 查询缓存（已基本退出历史舞台）
在 MySQL 8.0 之前，Server 层会先检查查询缓存。但它有一个致命的设计缺陷：只要对某张表发生任何更新，该表上所有查询缓存都会被清空。在读写混合的业务中，缓存命中率极低，维护成本反而成为负担。因此 MySQL 8.0.4 正式移除了查询缓存模块。在 5.7 中，生产环境通常也将query_cache_type设为DEMAND，默认不走缓存。对于 Java 开发者而言，查询缓存基本可以不作为考虑因素——应用层的缓存（Redis、Caffeine）才是主要手段。
2. 解析器：词法分析与语法分析
Server 层拿到 SQL 文本后，解析器做两件事：
词法分析：将字符串拆解为 token，识别关键字、表名、列名、操作符等，例如识别出
SELECT、FROM、users、WHERE、id。语法分析：按照 MySQL 的语法规则，将 token 流组合成一棵语法树（Parse Tree / AST）。如果语法不合法，在这一步就会报错（如
You have an error in your SQL syntax），根本不会进入后续环节。
这一步只关心“SQL 写得对不对”，不关心表是否存在、列是否有效、索引是否可用。
3. 优化器：逻辑优化 + 物理优化，生成执行计划
语法树确认合法后，进入优化器。这是决定 SQL 跑得快还是慢的核心环节，也是 Java 开发者排查慢查询时最需要理解的模块。
MySQL 优化器采用CBO（Cost-Based Optimizer，基于代价的优化器），分两个层次工作：
逻辑优化：对查询做逻辑等价变换。比如把外连接转为内连接（当条件允许时）、子查询展开、常量折叠、
OR改写为UNION等。目标是在不改变结果的前提下，把查询结构改写成更容易高效执行的形式。物理优化：在逻辑等价方案中，选择“代价最低”的物理执行路径。核心决策包括：单表用哪个索引（或全表扫描）、多表连接的顺序、连接算法（Nested Loop、Hash Join 等）。
代价估算依赖统计信息（表的行数、索引基数、数据分布等）。这意味着优化器的选择本质上是一种基于估算的决策，统计信息不准确时，它可能选出一个“估算代价低但实际很慢”的执行计划。Java 侧看到的“明明有索引却走了全表扫描”或“选错了索引”，根因往往就在这里。
外连接转内连接：“空值拒绝”
外连接（LEFT JOIN）的语义是：即使右表没有匹配，左表的行也要保留，右表列补NULL。优化器在某些条件下会把它等价地重写为内连接，从而释放连接顺序的自由度。
触发条件是：WHERE子句中的条件对于外连接产生的NULL补全行，永远不可能为TRUE（即求值结果为FALSE或UNKNOWN）。这种条件被称为“空值拒绝”（null-rejected）的。
最典型的例子是对右表列做IS NOT NULL或非等值比较：
sql
-- 原始外连接 SELECT * FROM orders o LEFT JOIN users u ON o.user_id = u.id WHERE u.status = 'ACTIVE';
对于orders中那些没有匹配到users的行，u.status会被补成NULL。而WHERE NULL = 'ACTIVE'的结果是UNKNOWN，不是TRUE，所以这些行会被WHERE过滤掉。外连接“保留左表所有行”的语义已经被WHERE条件消解了，优化器因此可以安全地将其重写为：
sql
SELECT * FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE u.status = 'ACTIVE';
改写为内连接后，优化器不再受“左表必须先于右表访问”的约束（外连接的嵌套循环执行要求外表先访问），它可以根据过滤后的结果集大小自由选择orders还是users作为驱动表。
开发中的常见误解：写了LEFT JOIN就以为一定是左表驱动。如果WHERE里对右表有过滤条件，优化器很可能已经悄悄转成了内连接，并重新选择了驱动表。用EXPLAIN看执行计划时，id相同的行中排在前面的是驱动表，这比凭 SQL 字面判断可靠得多。
子查询展开：半连接与物化
WHERE ... IN (SELECT ...)或EXISTS (SELECT ...)这类子查询，优化器通常不会“逐行执行子查询”。它会在准备阶段做一种叫半连接转换的改写：把子查询展开成与外部表的连接操作，但语义上只关心“是否存在匹配”，而不是匹配了几次。
以经典的课程与选课为例：
sql
SELECT class_num, class_name FROM class WHERE class_num IN (SELECT class_num FROM roster);
IN的语义是“只要roster中存在至少一条对应记录即可”。如果直接做普通内连接，一个班级有 50 个学生，结果就会出现 50 行重复。优化器会把IN转换为半连接，并选择以下几种策略之一来执行：
策略一：表提取（Table Pullout）。如果子查询的关联列在子查询表中是唯一索引或主键，优化器可以直接把子查询表“拉”进外部连接，因为每个值最多匹配一次，不存在重复问题。执行计划会变成普通的ref或eq_ref连接。
策略二：物化（Materialization）。如果子查询结果集较小，优化器会先独立执行子查询，把结果存入一张内存临时表，并自动在临时表上建立索引，然后用外部表去与这张临时表做连接。物化的关键收益是：子查询只需要执行一次，而不是对外部查询的每一行都执行一遍。在EXPLAIN中，select_type为SUBQUERY且出现materialized相关标记时，就是走了这条路。
策略三：首匹配（FirstMatch）。对于EXISTS或IN，一旦在子查询表中找到第一条匹配记录，就可以立即停止对该值的扫描，不需要找全所有匹配。当子查询表在关联列上有合适索引时，这种“找到即停”的短路逻辑可以显著减少扫描量。
查询无法被半连接展开的场景：如果子查询的关联列可为NULL，且外层使用的是NOT IN，反连接转换通常会受到限制——因为NOT IN (包含 NULL 的集合)的三值逻辑结果不可预期。这是NOT IN子查询难以优化的一个根本原因，业务上如果逻辑允许，用NOT EXISTS或LEFT JOIN ... IS NULL往往更可控。
常量折叠：在优化阶段就把能算的算掉
常量折叠是在 SQL 进入执行之前，优化器把只涉及常量的表达式直接计算出结果，避免在执行阶段逐行重复计算。
最直接的例子来自WHERE条件中的常量运算：
sql
-- 优化前 WHERE (a < b AND b = c) AND a = 5 -- 常量折叠后 WHERE b > 5 AND b = c AND a = 5
因为a = 5是已知常量，a < b可以替换为5 < b，即b > 5。这个计算在优化阶段只做一次，而不是对结果集的每一行都做一次。
另一个非常实用的折叠场景是类型范围折叠。假设有一列：
sql
CREATE TABLE t (c TINYINT UNSIGNED NOT NULL); SELECT * FROM t WHERE c < 256;
TINYINT UNSIGNED的取值范围是 0 到 255，c < 256对所有非空行恒为真。优化器在 8.0.16 之后会把条件折叠为WHERE 1，然后直接走全表扫描（或利用覆盖索引），不再对每一行做c < 256的比较。
与常量折叠配套的还有常量条件移除：
sql
WHERE (b >= 5 AND b = 5) OR (b = 6 AND 5 = 5) OR (b = 7 AND 5 = 6)
5 = 5恒真，5 = 6恒假。折叠后简化为：
sql
WHERE b = 5 OR b = 6
第三个分支b = 7 AND FALSE被整体消除，b >= 5 AND b = 5被化简为b = 5。
代码侧需要注意：如果你的 SQL 里写了WHERE status = 1 + 1或者WHERE create_time > NOW() - INTERVAL 1 DAY，其中常量部分会被折叠或预计算，通常不需要为了“性能”去手工算好再拼字符串。优化器自动完成的工作，往往比你在 Java 代码里手工拼更不容易出错。
OR 改写成 UNION
这里的背景需要先澄清：MySQL 官方优化器默认并不总是主动把OR改写成UNION。社区版中，OR涉及不同列时，优化器有时会使用index merge来分别利用两个索引再合并结果；但 index merge 在不少场景下代价较高，容易退化为全表扫描。而一些商业发行版（如 PolarDB for MySQL）会在基于代价判断的前提下，自动将符合条件的OR重写为UNION ALL结构。在社区版中，这更多是开发者手动改写的一种优化手段。
手动改写之所以有效，是因为UNION ALL的每个分支是独立的SELECT，各自可以走各自最优的索引，然后由 MySQL 在结果层面做合并。而单个OR条件如果涉及不同列，优化器很难用同一个索引同时覆盖两个分支。
可改写的典型形态是每个OR分支都是column = constant且该列各自有可用索引：
sql
-- 原始写法：email 和 phone 各有索引，但 OR 可能导致无法同时利用 SELECT id, name FROM users WHERE email = 'alice@example.com' OR phone = '555-1234'; -- 改写为 UNION ALL（如果业务允许重复行） SELECT id, name FROM users WHERE email = 'alice@example.com' UNION ALL SELECT id, name FROM users WHERE phone = '555-1234';
每个分支单独执行时，EXPLAIN显示都走ref或range，合并后的总代价可能远低于对OR做全表扫描。如果业务要求去重，用UNION（而不是UNION ALL），代价会多一次排序去重，但如果结果集本身不大，这个开销通常是可控的。
硬性前提：拆分前必须用EXPLAIN分别验证每个分支确实能走上索引。如果某个分支本身就会全表扫描，拆成UNION ALL只会让全表扫描执行两次，反而更慢。另外，OR的条件如果是a = 1 OR a = 2（同一列），优化器通常可以直接用IN或范围扫描处理，不需要也不应该拆成UNION。
多表连接顺序与算法选择
连接顺序：优化器看的是“过滤后的结果集”，不是表的总行数
“小表驱动大表”这句话需要修正一个关键点：“小表”指的是经过WHERE条件过滤之后，参与连接的有效行数最小的表，而不是SHOW TABLE STATUS里行数最少的表。
考虑这个例子：
sql
SELECT o.order_id, u.username FROM orders o JOIN users u ON o.user_id = u.id WHERE o.create_time >= '2026-09-01' AND u.status = 'ACTIVE';
orders有 200 万行，但create_time >= '2026-09-01'过滤后只剩 3000 行；users有 50 万行，status = 'ACTIVE'过滤后还有 40 万行。优化器会选择orders作为驱动表，因为驱动表是外层循环，它的有效行数决定了内层循环需要执行多少次。从 3000 行出发去users上匹配，远优于从 40 万行出发去orders上匹配。
对于多表（三表及以上）的连接顺序，优化器的搜索空间是阶乘级的。它并不会穷举所有排列，而是使用贪心搜索或有限深度的穷举来控制搜索成本。optimizer_search_depth参数控制着评估的深度，默认值会根据表数量动态调整；如果连接表很多，优化器只会对每个候选顺序的前若干个表做代价估算，而不是完整展开所有组合。这意味着多表连接的顺序选择本质上是启发式的，不保证全局最优。如果明确知道某两张表的过滤性极强、应该先连接，可以用STRAIGHT_JOIN或优化器 hint 来干预。
连接算法：从 NLJ 到 Hash Join 的代际演进
MySQL 的连接算法以嵌套循环为基本框架，但有多个变体，选择取决于被驱动表的关联列是否有索引以及MySQL 版本。
（1）Index Nested-Loop Join（INLJ）：最理想的情况。被驱动表的连接列上有索引，驱动表每取一行，就去被驱动表的 B+ 树上做一次索引查找。伪代码逻辑是：
text
for each row in 驱动表: 在被驱动表的索引上查找匹配行
代价是驱动表行数 × 索引查找代价，在驱动表过滤后较小的情况下非常高效。
（2）Block Nested-Loop Join（BNL）：当被驱动表的连接列没有索引时使用。如果逐行去全表扫描被驱动表，代价是驱动表行数 × 被驱动表行数。BNL 的优化是：把驱动表的一批行先放入join_buffer（默认 256KB），然后一次性拿这批行去扫描被驱动表，把被驱动表的全表扫描次数从“驱动表行数”降低为“驱动表行数 / buffer 能容纳的行数”。在EXPLAIN的Extra中看到Using join buffer (Block Nested Loop)，就说明走了 BNL。
（3）Hash Join（MySQL 8.0.18+）：当连接条件是等值条件，且没有可用索引时，8.0.18 及之后会使用 Hash Join 替代 BNL。它的工作方式是用驱动表的连接列在内存中构建哈希表，然后扫描被驱动表，用哈希查找完成匹配。复杂度接近O(N+M)，远优于 BNL 的块扫描。从 8.0.20 开始，Hash Join 也支持非等值条件，但非等值部分是在哈希匹配之后作为过滤条件应用的，不是哈希键的一部分。这正是为什么“升级版本后同样的无索引 JOIN 从 30 秒变成 2 秒”是真实可能发生的——算法代际差异带来的收益有时远大于微调索引。
（4）Batched Key Access（BKA）：在 INLJ 的基础上，把驱动表的一批连接键收集起来，通过MRR（Multi-Range Read）批量、有序地向被驱动表的索引发起回表请求。BKA 需要显式开启：optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'。在EXPLAIN中表现为Using join buffer (Batched Key Access)，且type通常是ref或eq_ref。它适合被驱动表有索引但回表是随机 I/O 的场景。
选择逻辑的简化总结：
| 被驱动表连接列 | 算法 | 关键EXPLAIN特征 |
|---|---|---|
| 有索引 | INLJ（或 BKA） | type=ref/eq_ref，无 join buffer 提示 |
| 无索引，等值连接，8.0.18+ | Hash Join | Extra中通常不再出现 BNL，可能显示 hash 相关 |
| 无索引，8.0.18 之前 | BNL | Using join buffer (Block Nested Loop) |
优化器的选择顺序是：优先尝试用索引（INLJ/BKA）；没有索引时，8.0.18+ 优先 Hash Join，旧版本退到 BNL。
4. 执行器：按计划调用存储引擎
执行器拿到优化器生成的执行计划，开始真正“干活”。但在动手之前，执行器会再确认一次当前连接对目标表是否有相应权限（连接器阶段缓存的是全局权限，执行器阶段做的是表级/列级的权限校验）。
确认权限后，执行器进入核心工作模式：逐行调用存储引擎的 Handler API，按照执行计划规定的路径读取、过滤、处理数据。它不关心数据存在 B+ 树还是别的结构里，只通过统一的接口和引擎交互。
第三阶段：执行器与 InnoDB 的接口交互（以查询为例）
Server 层的执行器和 InnoDB 之间通过一套标准的Handler API协作。以一条走二级索引的查询为例，调用链大致如下：
handler::open()：打开目标表，获取访问句柄；index_init()：初始化索引扫描上下文（确定从哪个索引、哪个范围开始遍历 B+ 树）；index_read()：在索引上定位并读取记录。如果走的是二级索引且需要回表，这里会先拿到主键，再通过rnd_pos()根据主键回聚簇索引读取完整行数据；返回行数据：InnoDB 将读取到的行拷贝到执行器的缓冲区，执行器在此基础上进行后续的
WHERE条件过滤（对于索引无法覆盖的条件）、表达式计算等；循环往复，直到扫描完所有满足范围条件的记录或满足
LIMIT提前终止。
对于UPDATE语句，执行器同样先通过index_read()定位目标行，然后调用 InnoDB 的update_row()，由引擎层完成加锁（X 锁）、写入 undo log（支持回滚和 MVCC）、修改 Buffer Pool 中的数据页、写入 redo log 等动作，最后提交时走两阶段提交（redo prepare → binlog → redo commit）。
结果返回：流式返回
执行器在逐行获取数据的同时，会边处理边返回给客户端，而不是等全部结果集在内存中拼好再一次性发送。这种流式返回机制是 MySQL 协议层的设计，对于大结果集查询，Java 侧设置setFetchSize()并配合游标，就是在利用这一机制避免客户端内存被撑爆。
索引与存储引擎
1. InnoDB 默认用 B+ 树，而不是 B 树或哈希
核心原因是磁盘 I/O 的效率。B+ 树的非叶子节点只存键值和指针，不存数据行，因此一个磁盘页能容纳更多的索引项，树高通常只有 3—4 层，查一条记录只需要几次磁盘 I/O。同时，所有数据都集中在叶子节点，且叶子节点之间用双向链表串联，这让范围查询（BETWEEN、> <）可以顺序扫描，而不是像 B 树那样需要在树中反复回溯。
哈希索引虽然等值查询是 O(1)，但它不支持范围查询、排序和分组，也无法利用最左前缀，因此在 OLTP 场景中无法作为默认索引结构。
2. 聚簇索引与二级索引：回表与覆盖索引的本质
InnoDB 的表数据本身就是按主键组织的一棵 B+ 树，这棵树的叶子节点存放完整数据行，这就是聚簇索引。一张表只能有一个聚簇索引。
二级索引（非聚簇索引）的叶子节点不存数据行，只存“索引列的值 + 主键值”。因此，通过二级索引查到主键后，还需要再回到聚簇索引中取完整行——这个过程就是回表。回表意味着多一次 B+ 树查找，在二级索引扫描行数较多时，代价会显著放大。
覆盖索引的意义就在于消除这次回表：如果查询需要的所有列（SELECT、WHERE、ORDER BY、GROUP BY涉及的列）都恰好包含在某个二级索引中，MySQL 可以直接从该索引返回结果，不需要回表。在EXPLAIN的Extra列中看到Using index，通常就是覆盖索引生效的标志。
3. 最左前缀原则：不是“规定”，是 B+ 树排序方式的必然结果
联合索引idx_a_b_c (a, b, c)的 B+ 树，排序方式是：先按 a 排，a 相同再按 b 排，b 相同再按 c 排。
这意味着：
WHERE a=1 AND b=2 AND c=3：三列都能利用有序性，完整使用索引；WHERE a=1 AND c=3：跳过 b，在 a=1 的范围内，c 是无序的，所以只能用上 a；WHERE b=2：b 在全局范围内无序（只有 a 确定后 b 才有序），完全用不上该索引。
范围查询会“截断”后面的列，也是同一原理：一旦在 b 上使用了>、<、BETWEEN，b 得到的是一个区间，在这个区间内 c 不再有序，索引无法继续向下定位。
一个 Java 开发中容易踩的细节：优化器会自动调整等值条件的顺序。WHERE b=2 AND a=1会被优化成等效于a=1 AND b=2，仍然能用上 a、b。但范围条件的顺序无法调整，b>2 AND a=1和a=1 AND b>2的索引使用情况是一样的（都只用 a 和 b 的范围部分，c 无法使用）。
事务与并发：MVCC 与锁的配合
1. MVCC 解决的核心矛盾：让“读”不阻塞“写”
在纯锁机制下，读要加 S 锁、写要加 X 锁，读写之间会互相阻塞。MVCC 的思路是：写操作不覆盖旧数据，而是生成新版本；读操作根据可见性规则，去读一个“合适的旧版本”。这样普通SELECT不需要加锁，读写可以并行。
2. 版本链的形成
InnoDB 的每一行记录有两个 MVCC 关键隐藏字段：
DB_TRX_ID（6 字节）：最后修改这行数据的事务 ID；DB_ROLL_PTR（7 字节）：指向 undo log 中这行的上一个版本。
每次UPDATE时，旧值被写入 undo log，当前行的DB_TRX_ID更新为新事务 ID，DB_ROLL_PTR指向刚写入 undo log 的旧版本。多次修改就串成一条单向链表，即版本链。
3. ReadView：可见性判断的“规则引擎”
ReadView 包含四个核心字段：m_ids（生成时活跃未提交的事务 ID 列表）、min_trx_id、max_trx_id、creator_trx_id。
判断版本链上某个版本的DB_TRX_ID（记为trx_id）是否对当前事务可见，按顺序走：
trx_id == creator_trx_id：可见（自己改的）；trx_id < min_trx_id：可见（在 ReadView 生成前已提交）；trx_id >= max_trx_id：不可见（在 ReadView 生成后才开启的事务）；trx_id在m_ids中：不可见（生成 ReadView 时尚未提交）；否则：可见。
RC（Read Committed） 与 RR（Repeatable Read）在 MVCC 上的唯一区别，就是 ReadView 的生成时机：RC 每次SELECT都新建 ReadView，所以能看到别人新提交的数据；RR 只在事务第一次快照读时创建一次，后续复用，因此整个事务看到的是同一个快照。
4. 快照读与当前读
普通SELECT走 MVCC，读的是历史版本，叫快照读。而UPDATE、DELETE、SELECT ... FOR UPDATE、SELECT ... FOR SHARE必须读最新已提交版本，并加锁，这叫当前读。Java 中如果用乐观锁（版本号字段），本质就是在应用层模拟“当前读”的语义；如果用FOR UPDATE，就是直接使用数据库的当前读加锁机制。
锁机制：行锁
1. InnoDB 的行锁，锁的是索引记录，不是“逻辑行”
这是理解 InnoDB 锁行为的起点。如果一个UPDATE的WHERE条件没有走索引，InnoDB 无法定位到具体的索引记录，就可能退化为表级扫描并锁住大量记录，表现为“我只改一行，却锁了很多”。线上锁冲突排查时，首先要确认WHERE是否命中了合适的索引。
2. 三种行锁算法：Record Lock、Gap Lock、Next-Key Lock
Record Lock（记录锁）：锁住某一条具体的索引记录。
Gap Lock（间隙锁）：锁住两个索引记录之间的“空隙”，不包含记录本身，用于阻止其他事务在间隙中插入新记录。
Next-Key Lock（临键锁）：Record Lock + Gap Lock 的组合，锁住“左开右闭”的区间
(prev, current]，是 RR 级别下解决幻读的关键机制。
关键区别：RC 级别下间隙锁基本关闭，只保留记录锁（且语句执行完后会释放不匹配行的锁），锁范围更小、并发更高；RR 级别通过 Next-Key Lock 来防止幻读，代价是更多的锁冲突和死锁风险。这也是很多互联网业务在读写一致性允许的前提下倾向于将隔离级别设为 RC 的原因。
RC 下间隙锁“基本关闭”的两层含义：
第一层：语义上不需要加。RC 下 InnoDB 对扫描到的索引记录只加Record Lock（记录锁），不加 Gap Lock。UPDATE ... WHERE c = 20只锁住c=20那一条索引记录，(10,20)和(20,30)两个间隙不设防，其他事务可以自由插入。
第二层：semi-consistent read 让不匹配行的锁尽快释放。MySQL 5.6 起，RC 下引入 semi-consistent read：UPDATE扫描过程中遇到不满足WHERE条件的行，InnoDB 会先释放该行的锁，再交给 Server 层判断条件。这意味着 RC 下UPDATE即使扫描了很宽的索引范围，锁的持有时间和持锁行数也被大幅压缩。而 RR 下这个优化默认不启用（RR 需要完整加锁来维持快照一致性）。
“基本”而非“完全”关闭的例外：唯一性约束检查和外键约束检查在 RC 下仍然涉及间隙层面的协调（插入意向锁与唯一键冲突检测），否则并发插入相同唯一键会出问题。但业务 SQL 的普通范围扫描和等值扫描，RC 下确实不设间隙锁。
RR 下 Next-Key Lock 的加锁规则（简要）：
InnoDB 在 RR 下对索引记录的默认加锁单位是Next-Key Lock = Record Lock + Gap Lock，锁住的是“左开右闭”区间(prev_key, current_key]。几条关键规则：
唯一索引等值查询且记录存在：Next-Key Lock退化为 Record Lock（只锁记录，不锁间隙）。例如
WHERE id = 5，id 是主键且存在，只锁 id=5 这一行。唯一索引等值查询且记录不存在：加Gap Lock，锁住该值应该存在的那个间隙。
非唯一索引等值查询：加 Next-Key Lock，并向右扫描到第一条不满足条件的记录，对该记录加 Gap Lock（不锁记录本身）。
范围查询：对扫描到的每条索引记录加 Next-Key Lock，对范围结束后的第一条记录加 Gap Lock。
下面用具体数据演示这些规则在并发修改中的实际表现。
RR 下 Next-Key Lock 的加锁规则（简要）：
InnoDB 在 RR 下对索引记录的默认加锁单位是Next-Key Lock = Record Lock + Gap Lock，锁住的是“左开右闭”区间(prev_key, current_key]。几条关键规则：
唯一索引等值查询且记录存在：Next-Key Lock退化为 Record Lock（只锁记录，不锁间隙）。例如
WHERE id = 5，id 是主键且存在，只锁 id=5 这一行。唯一索引等值查询且记录不存在：加Gap Lock，锁住该值应该存在的那个间隙。
非唯一索引等值查询：加 Next-Key Lock，并向右扫描到第一条不满足条件的记录，对该记录加 Gap Lock（不锁记录本身）。
范围查询：对扫描到的每条索引记录加 Next-Key Lock，对范围结束后的第一条记录加 Gap Lock。
下面用具体数据演示这些规则在并发修改中的实际表现。
3. 死锁的本质与 Java 开发的关联
死锁的本质是循环等待。InnoDB 的锁加在索引记录上，当两个事务以不同顺序触碰同一组索引记录时，就可能形成环路。例如事务 A 先锁记录 1 再申请记录 2，事务 B 先锁记录 2 再申请记录 1。
Java 侧可以做的预防：统一加锁顺序（比如按主键升序批量更新）、缩短事务范围（不要在事务中做远程调用），以及合理利用SELECT ... FOR UPDATE的粒度，避免不必要的 Next-Key Lock 扩散。
日志与持久化：数据安全落盘
1. 三大日志的分工，不能混淆
| Redo Log | Undo Log | Binlog | |
|---|---|---|---|
| 层级 | InnoDB 引擎层 | InnoDB 引擎层 | Server 层 |
| 类型 | 物理日志（页修改） | 逻辑日志（反向操作） | 逻辑日志（事件） |
| 核心作用 | 崩溃恢复、保证持久性 | 事务回滚、MVCC | 主从复制、数据恢复 |
| 写入 | 循环写，大小固定 | 追加写，purge 清理 | 追加写，生成新文件 |
Redo Log 让 InnoDB 具备crash-safe能力；Undo Log 保证原子性并支撑 MVCC；Binlog 是 Server 层的逻辑日志，与存储引擎无关，用于复制和 PITR（时间点恢复）。
2. 两阶段提交（2PC）：为什么必须存在
Redo Log 和 Binlog 是两个独立的日志系统。如果没有 2PC，可能出现两种不一致：
Redo Log 写入成功但 Binlog 失败：崩溃恢复后主库有数据，但从库没有对应 Binlog，主从不一致；
Binlog 写入成功但 Redo Log 失败：从库重放后会有数据，但主库回滚，数据丢失。
2PC 用Prepare和Commit两个阶段，让两个日志“要么都生效，要么都失效”。崩溃恢复时的判断逻辑是：扫描 Redo Log 中处于PREPARE状态的事务，用其 XID 去 Binlog 中查找——Binlog 存在完整记录则提交，不存在则回滚。最终裁决权交给 Binlog 的完整性。
3. 与 Java 开发直接相关的刷盘参数
innodb_flush_log_at_trx_commit：控制 Redo Log 刷盘。1最安全（每次提交刷盘并fsync），0性能最好但可能丢 1 秒数据，2是折中（刷到 OS 缓存，每秒fsync）。sync_binlog：控制 Binlog 刷盘。1最安全，0由 OS 决定，N每 N 个事务刷一次。
两者都设为1是“双 1”配置，数据安全性最高，但写入吞吐会受影响。Java 应用中的批量写入场景，需要根据业务对数据丢失的容忍度来权衡这两个参数。
优化实战
1. 先定位，再看执行计划，最后才改 SQL
优化的顺序不能颠倒。慢查询日志负责发现：long_query_time建议设为 1 秒（核心场景 0.5 秒），min_examined_row_limit可以过滤掉扫描行数很少的小表查询，避免日志被无效信息淹没。log_queries_not_using_indexes不要长期开启，否则所有未走索引的毫秒级查询都会涌入日志。
拿到慢 SQL 后，第一步是EXPLAIN（MySQL 8.0.18+ 可用EXPLAIN ANALYZE看实际执行耗时和真实行数）。重点看：
type：性能排序system > const > eq_ref > ref > range > index > ALL，出现ALL意味着全表扫描；key：实际使用了哪个索引，是否符合预期；Extra：Using filesort和Using temporary通常意味着排序或临时表代价较高；Using index表示覆盖索引生效。
2. 索引失效的高频场景
索引失效不是一堆需要死记的规则，而是 B+ 树有序性被破坏的结果：
在索引列上使用函数（如
DATE(created_at)）：函数结果在 B+ 树中没有预排序，无法利用有序性定位；隐式类型转换：字符串列用数字查询，或数字列用字符串查询，可能导致索引失效；
LIKE '%xxx'：左侧通配符使前缀无序，B+ 树无法定位起点；OR连接非索引列：优化器可能选择全表扫描。
代码侧常见的“意外触发”场景：JPA/Hibernate 生成的 SQL 中，WHERE DATE(create_time) = ...或WHERE order_no = 123（order_no是varchar但传入了数字），都需要在抓取真实 SQL 后用EXPLAIN验证，而不是只看 Java 代码里的方法名。
3. 深度分页改写思路
LIMIT offset, size在offset很大时，MySQL 需要先扫描并丢弃前offset行，代价随页码线性增长。如果排序字段有索引、且业务允许“游标式”翻页，可以改写为基于上一页最后一条记录的值来定位：
sql
-- 慢：扫描并丢弃前 100000 行 SELECT * FROM orders ORDER BY id LIMIT 100000, 20; -- 快：从 id > 上一页最后一条 id 的位置开始 SELECT * FROM orders WHERE id > ? ORDER BY id LIMIT 20;
这条改写的前提是id上有索引（主键即可）且排序稳定。如果业务确实需要跳页，且必须用OFFSET，则要考虑是否通过覆盖索引先取出主键、再关联回表，以减小每次扫描的数据宽度。
4. 经验总结：安排查优先级
第一优先级：让连接列有可用的索引。这是消除 BNL、避免 Hash Join 之前那种“块扫描”的最直接手段。EXPLAIN中如果在被驱动表上看到Using join buffer，第一反应应该是检查连接列是否缺索引，而不是先调join_buffer_size。
第二优先级：用EXPLAIN确认“过滤后的驱动表”是谁。不要凭“小表驱动大表”的字面去猜。看EXPLAIN中id相同的行，排在上面的是驱动表。如果驱动表的选择与预期不符，先检查WHERE条件是否已经触发了外连接转内连接的改写。
第三优先级：对OR保持警惕，但不盲目拆分。只有当OR的每个分支各自单独EXPLAIN都能走索引时，拆成UNION ALL才是有收益的。同一列的多个等值条件，优先考虑IN；如果IN的列表很大，再评估拆分。
第四优先级：子查询优先写成EXISTS或JOIN的可优化形态。IN (SELECT ...)在 8.0 通常能被半连接展开，但NOT IN受NULL值限制容易退化。业务逻辑允许时，NOT EXISTS或LEFT JOIN ... WHERE 右表主键 IS NULL的优化路径更透明。
第五优先级：不要为了“性能”手工折叠常量或预计算。WHERE create_time > NOW() - INTERVAL 7 DAY中的常量部分，优化器会在准备阶段处理，不需要在 Java 层拼成字面量。手工干预反而可能破坏 SQL 的可读性和 PreparedStatement 的缓存命中。
最后一条，版本本身就是优化手段。如果线上仍然运行 MySQL 5.7，且存在无索引等值 JOIN 的慢查询，评估升级到 8.0 的收益可能比在旧版本上反复调参更大。Hash Join 在 8.0.18 之后的引入，本质上是让“无索引等值连接”从块扫描时代进入了哈希时代，这是算法层面的代差，不是参数微调能弥补的。
