SQL执行过程在进入流程之前先明确 MySQL 的分层Server 层和存储引擎层。Server 层负责连接管理、语法解析、优化、执行调度所有存储引擎共用这一套逻辑存储引擎层默认 InnoDB负责数据的实际存取、索引、事务、锁和 MVCC。你写的 SQL 绝大多数“逻辑”发生在 Server 层而“物理”读写和并发控制发生在引擎层。第一阶段连接建立——TCP、认证与会话初始化1. TCP 三次握手与协议握手JDBC 驱动或任何客户端首先与 MySQL 服务端的监听端口默认 3306完成 TCP 三次握手。但这只是建立了网络通道MySQL 协议本身还有一次协议层面的握手服务端主动发送Initial Handshake Packet告知客户端协议版本、服务端版本以及默认的认证方式客户端据此回复认证信息。这与很多“客户端先发请求”的协议不同MySQL 是服务端先发起握手的。2. 身份认证AuthenticationTCP 通道建立后连接器开始校验身份。客户端将用户名、密码加密后的挑战响应和来源主机信息发给服务端服务端对照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 优化器采用CBOCost-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条件消解了优化器因此可以安全地将其重写为sqlSELECT * 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 ...)这类子查询优化器通常不会“逐行执行子查询”。它会在准备阶段做一种叫半连接转换的改写把子查询展开成与外部表的连接操作但语义上只关心“是否存在匹配”而不是匹配了几次。以经典的课程与选课为例sqlSELECT 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。这个计算在优化阶段只做一次而不是对结果集的每一行都做一次。另一个非常实用的折叠场景是类型范围折叠。假设有一列sqlCREATE TABLE t (c TINYINT UNSIGNED NOT NULL); SELECT * FROM t WHERE c 256;TINYINT UNSIGNED的取值范围是 0 到 255c 256对所有非空行恒为真。优化器在 8.0.16 之后会把条件折叠为WHERE 1然后直接走全表扫描或利用覆盖索引不再对每一行做c 256的比较。与常量折叠配套的还有常量条件移除sqlWHERE (b 5 AND b 5) OR (b 6 AND 5 5) OR (b 7 AND 5 6)5 5恒真5 6恒假。折叠后简化为sqlWHERE 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 aliceexample.com OR phone 555-1234; -- 改写为 UNION ALL如果业务允许重复行 SELECT id, name FROM users WHERE email aliceexample.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里行数最少的表。考虑这个例子sqlSELECT 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 版本。1Index Nested-Loop JoinINLJ最理想的情况。被驱动表的连接列上有索引驱动表每取一行就去被驱动表的 B 树上做一次索引查找。伪代码逻辑是textfor each row in 驱动表: 在被驱动表的索引上查找匹配行代价是驱动表行数 × 索引查找代价在驱动表过滤后较小的情况下非常高效。2Block Nested-Loop JoinBNL当被驱动表的连接列没有索引时使用。如果逐行去全表扫描被驱动表代价是驱动表行数 × 被驱动表行数。BNL 的优化是把驱动表的一批行先放入join_buffer默认 256KB然后一次性拿这批行去扫描被驱动表把被驱动表的全表扫描次数从“驱动表行数”降低为“驱动表行数 / buffer 能容纳的行数”。在EXPLAIN的Extra中看到Using join buffer (Block Nested Loop)就说明走了 BNL。3Hash JoinMySQL 8.0.18当连接条件是等值条件且没有可用索引时8.0.18 及之后会使用 Hash Join 替代 BNL。它的工作方式是用驱动表的连接列在内存中构建哈希表然后扫描被驱动表用哈希查找完成匹配。复杂度接近O(NM)远优于 BNL 的块扫描。从 8.0.20 开始Hash Join 也支持非等值条件但非等值部分是在哈希匹配之后作为过滤条件应用的不是哈希键的一部分。这正是为什么“升级版本后同样的无索引 JOIN 从 30 秒变成 2 秒”是真实可能发生的——算法代际差异带来的收益有时远大于微调索引。4Batched Key AccessBKA在 INLJ 的基础上把驱动表的一批连接键收集起来通过MRRMulti-Range Read批量、有序地向被驱动表的索引发起回表请求。BKA 需要显式开启optimizer_switchmrron,mrr_cost_basedoff,batched_key_accesson。在EXPLAIN中表现为Using join buffer (Batched Key Access)且type通常是ref或eq_ref。它适合被驱动表有索引但回表是随机 I/O 的场景。选择逻辑的简化总结被驱动表连接列算法关键EXPLAIN特征有索引INLJ或 BKAtyperef/eq_ref无 join buffer 提示无索引等值连接8.0.18Hash JoinExtra中通常不再出现 BNL可能显示 hash 相关无索引8.0.18 之前BNLUsing 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 a1 AND b2 AND c3三列都能利用有序性完整使用索引WHERE a1 AND c3跳过 b在 a1 的范围内c 是无序的所以只能用上 aWHERE b2b 在全局范围内无序只有 a 确定后 b 才有序完全用不上该索引。范围查询会“截断”后面的列也是同一原理一旦在 b 上使用了、、BETWEENb 得到的是一个区间在这个区间内 c 不再有序索引无法继续向下定位。一个 Java 开发中容易踩的细节优化器会自动调整等值条件的顺序。WHERE b2 AND a1会被优化成等效于a1 AND b2仍然能用上 a、b。但范围条件的顺序无法调整b2 AND a1和a1 AND b2的索引使用情况是一样的都只用 a 和 b 的范围部分c 无法使用。事务与并发MVCC 与锁的配合1. MVCC 解决的核心矛盾让“读”不阻塞“写”在纯锁机制下读要加 S 锁、写要加 X 锁读写之间会互相阻塞。MVCC 的思路是写操作不覆盖旧数据而是生成新版本读操作根据可见性规则去读一个“合适的旧版本”。这样普通SELECT不需要加锁读写可以并行。2. 版本链的形成InnoDB 的每一行记录有两个 MVCC 关键隐藏字段DB_TRX_ID6 字节最后修改这行数据的事务 IDDB_ROLL_PTR7 字节指向 undo log 中这行的上一个版本。每次UPDATE时旧值被写入 undo log当前行的DB_TRX_ID更新为新事务 IDDB_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 时尚未提交否则可见。RCRead Committed 与 RRRepeatable 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 LockRecord 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只锁住c20那一条索引记录(10,20)和(20,30)两个间隙不设防其他事务可以自由插入。第二层semi-consistent read 让不匹配行的锁尽快释放。MySQL 5.6 起RC 下引入 semi-consistent readUPDATE扫描过程中遇到不满足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 5id 是主键且存在只锁 id5 这一行。唯一索引等值查询且记录不存在加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 5id 是主键且存在只锁 id5 这一行。唯一索引等值查询且记录不存在加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 LogUndo LogBinlog层级InnoDB 引擎层InnoDB 引擎层Server 层类型物理日志页修改逻辑日志反向操作逻辑日志事件核心作用崩溃恢复、保证持久性事务回滚、MVCC主从复制、数据恢复写入循环写大小固定追加写purge 清理追加写生成新文件Redo Log 让 InnoDB 具备crash-safe能力Undo Log 保证原子性并支撑 MVCCBinlog 是 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最安全每次提交刷盘并fsync0性能最好但可能丢 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 后第一步是EXPLAINMySQL 8.0.18 可用EXPLAIN ANALYZE看实际执行耗时和真实行数。重点看type性能排序system const eq_ref ref range index ALL出现ALL意味着全表扫描key实际使用了哪个索引是否符合预期ExtraUsing 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 123order_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 之后的引入本质上是让“无索引等值连接”从块扫描时代进入了哈希时代这是算法层面的代差不是参数微调能弥补的。 
长河君 · 2026/11/2 11:15:52 · 阅读 1.2k JAVA进阶—MySql篇
SQL执行过程在进入流程之前先明确 MySQL 的分层Server 层和存储引擎层。Server 层负责连接管理、语法解析、优化、执行调度所有存储引擎共用这一套逻辑存储引擎层默认 InnoDB负责数据的实际存取、索引、事务、锁和 MVCC。你写的 SQL 绝大多数“逻辑”发生在 Server 层而“物理”读写和并发控制发生在引擎层。第一阶段连接建立——TCP、认证与会话初始化1. TCP 三次握手与协议握手JDBC 驱动或任何客户端首先与 MySQL 服务端的监听端口默认 3306完成 TCP 三次握手。但这只是建立了网络通道MySQL 协议本身还有一次协议层面的握手服务端主动发送Initial Handshake Packet告知客户端协议版本、服务端版本以及默认的认证方式客户端据此回复认证信息。这与很多“客户端先发请求”的协议不同MySQL 是服务端先发起握手的。2. 身份认证AuthenticationTCP 通道建立后连接器开始校验身份。客户端将用户名、密码加密后的挑战响应和来源主机信息发给服务端服务端对照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 优化器采用CBOCost-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条件消解了优化器因此可以安全地将其重写为sqlSELECT * 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 ...)这类子查询优化器通常不会“逐行执行子查询”。它会在准备阶段做一种叫半连接转换的改写把子查询展开成与外部表的连接操作但语义上只关心“是否存在匹配”而不是匹配了几次。以经典的课程与选课为例sqlSELECT 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。这个计算在优化阶段只做一次而不是对结果集的每一行都做一次。另一个非常实用的折叠场景是类型范围折叠。假设有一列sqlCREATE TABLE t (c TINYINT UNSIGNED NOT NULL); SELECT * FROM t WHERE c 256;TINYINT UNSIGNED的取值范围是 0 到 255c 256对所有非空行恒为真。优化器在 8.0.16 之后会把条件折叠为WHERE 1然后直接走全表扫描或利用覆盖索引不再对每一行做c 256的比较。与常量折叠配套的还有常量条件移除sqlWHERE (b 5 AND b 5) OR (b 6 AND 5 5) OR (b 7 AND 5 6)5 5恒真5 6恒假。折叠后简化为sqlWHERE 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 aliceexample.com OR phone 555-1234; -- 改写为 UNION ALL如果业务允许重复行 SELECT id, name FROM users WHERE email aliceexample.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里行数最少的表。考虑这个例子sqlSELECT 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 版本。1Index Nested-Loop JoinINLJ最理想的情况。被驱动表的连接列上有索引驱动表每取一行就去被驱动表的 B 树上做一次索引查找。伪代码逻辑是textfor each row in 驱动表: 在被驱动表的索引上查找匹配行代价是驱动表行数 × 索引查找代价在驱动表过滤后较小的情况下非常高效。2Block Nested-Loop JoinBNL当被驱动表的连接列没有索引时使用。如果逐行去全表扫描被驱动表代价是驱动表行数 × 被驱动表行数。BNL 的优化是把驱动表的一批行先放入join_buffer默认 256KB然后一次性拿这批行去扫描被驱动表把被驱动表的全表扫描次数从“驱动表行数”降低为“驱动表行数 / buffer 能容纳的行数”。在EXPLAIN的Extra中看到Using join buffer (Block Nested Loop)就说明走了 BNL。3Hash JoinMySQL 8.0.18当连接条件是等值条件且没有可用索引时8.0.18 及之后会使用 Hash Join 替代 BNL。它的工作方式是用驱动表的连接列在内存中构建哈希表然后扫描被驱动表用哈希查找完成匹配。复杂度接近O(NM)远优于 BNL 的块扫描。从 8.0.20 开始Hash Join 也支持非等值条件但非等值部分是在哈希匹配之后作为过滤条件应用的不是哈希键的一部分。这正是为什么“升级版本后同样的无索引 JOIN 从 30 秒变成 2 秒”是真实可能发生的——算法代际差异带来的收益有时远大于微调索引。4Batched Key AccessBKA在 INLJ 的基础上把驱动表的一批连接键收集起来通过MRRMulti-Range Read批量、有序地向被驱动表的索引发起回表请求。BKA 需要显式开启optimizer_switchmrron,mrr_cost_basedoff,batched_key_accesson。在EXPLAIN中表现为Using join buffer (Batched Key Access)且type通常是ref或eq_ref。它适合被驱动表有索引但回表是随机 I/O 的场景。选择逻辑的简化总结被驱动表连接列算法关键EXPLAIN特征有索引INLJ或 BKAtyperef/eq_ref无 join buffer 提示无索引等值连接8.0.18Hash JoinExtra中通常不再出现 BNL可能显示 hash 相关无索引8.0.18 之前BNLUsing 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 a1 AND b2 AND c3三列都能利用有序性完整使用索引WHERE a1 AND c3跳过 b在 a1 的范围内c 是无序的所以只能用上 aWHERE b2b 在全局范围内无序只有 a 确定后 b 才有序完全用不上该索引。范围查询会“截断”后面的列也是同一原理一旦在 b 上使用了、、BETWEENb 得到的是一个区间在这个区间内 c 不再有序索引无法继续向下定位。一个 Java 开发中容易踩的细节优化器会自动调整等值条件的顺序。WHERE b2 AND a1会被优化成等效于a1 AND b2仍然能用上 a、b。但范围条件的顺序无法调整b2 AND a1和a1 AND b2的索引使用情况是一样的都只用 a 和 b 的范围部分c 无法使用。事务与并发MVCC 与锁的配合1. MVCC 解决的核心矛盾让“读”不阻塞“写”在纯锁机制下读要加 S 锁、写要加 X 锁读写之间会互相阻塞。MVCC 的思路是写操作不覆盖旧数据而是生成新版本读操作根据可见性规则去读一个“合适的旧版本”。这样普通SELECT不需要加锁读写可以并行。2. 版本链的形成InnoDB 的每一行记录有两个 MVCC 关键隐藏字段DB_TRX_ID6 字节最后修改这行数据的事务 IDDB_ROLL_PTR7 字节指向 undo log 中这行的上一个版本。每次UPDATE时旧值被写入 undo log当前行的DB_TRX_ID更新为新事务 IDDB_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 时尚未提交否则可见。RCRead Committed 与 RRRepeatable 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 LockRecord 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只锁住c20那一条索引记录(10,20)和(20,30)两个间隙不设防其他事务可以自由插入。第二层semi-consistent read 让不匹配行的锁尽快释放。MySQL 5.6 起RC 下引入 semi-consistent readUPDATE扫描过程中遇到不满足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 5id 是主键且存在只锁 id5 这一行。唯一索引等值查询且记录不存在加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 5id 是主键且存在只锁 id5 这一行。唯一索引等值查询且记录不存在加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 LogUndo LogBinlog层级InnoDB 引擎层InnoDB 引擎层Server 层类型物理日志页修改逻辑日志反向操作逻辑日志事件核心作用崩溃恢复、保证持久性事务回滚、MVCC主从复制、数据恢复写入循环写大小固定追加写purge 清理追加写生成新文件Redo Log 让 InnoDB 具备crash-safe能力Undo Log 保证原子性并支撑 MVCCBinlog 是 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最安全每次提交刷盘并fsync0性能最好但可能丢 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 后第一步是EXPLAINMySQL 8.0.18 可用EXPLAIN ANALYZE看实际执行耗时和真实行数。重点看type性能排序system const eq_ref ref range index ALL出现ALL意味着全表扫描key实际使用了哪个索引是否符合预期ExtraUsing 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 123order_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 之后的引入本质上是让“无索引等值连接”从块扫描时代进入了哈希时代这是算法层面的代差不是参数微调能弥补的。