1. 从一次压测事故说起并发总在索引上翻车前阵子帮朋友排查一个线上问题现象很典型。他们的订单查询接口在低并发时一切正常响应时间稳定在 30 毫秒左右。压测一上到 200 并发数据库这边立刻开始报警线程堆积、CPU 飙到 80% 以上慢查询日志刷屏。我第一反应是看索引结果发现核心查询条件里有一个字段根本没建索引走了全表扫描。这个场景太常见了。很多团队把索引当成查询优化工具只在 SQL 变慢时才考虑加索引。但索引从来不只是查询性能的问题——在并发场景下索引设计的好坏直接决定了锁的粒度、事务的冲突概率、甚至死锁会不会频繁出现。这就是我为什么想认真写一篇关于索引并发控制的内容索引和并发控制从来都是同一枚硬币的两面。这篇内容会从 InnoDB 的索引结构出发讲清楚为什么索引会深度参与并发控制再展开索引锁、MVCC、DDL 并发、索引设计等几个层面的实操方法最后给出排查并发问题的完整链路。适合 DBA、后端开发、架构师以及所有正在被连接数打满死锁频繁锁等待超时折磨的同行。我先说一个反直觉的结论索引并不是越多越好错误的索引在并发场景下会主动制造锁竞争。所以这篇文章的核心不是教你怎么建索引而是教你怎么在并发和索引之间找到那个平衡点。2. 索引结构与并发控制的底层纠缠B 树、行锁与 MVCC2.1 为什么 B 树结构天然影响锁的竞争范围要理解索引和并发的关系先得回到索引的物理结构。InnoDB 的索引是 B 树数据都挂在叶子节点上每个叶子节点就是一个数据页默认 16KB。树的高度决定了查询需要访问多少层节点但真正影响并发的是当多个事务同时访问同一棵 B 树时它们会在哪些节点上产生竞争读操作之间不互斥读和写互斥写和写互斥。如果两个事务要更新同一个数据页里不同行的数据InnoDB 通过行锁Record Lock把冲突范围控制在行级别。但行锁能生效的前提是查询必须能通过索引精确定位到行。如果查询不走索引InnoDB 就只能退而求其次锁住整个表的记录——这就是全表扫描在并发场景下特别致命的原因它不是慢那么简单它把锁的粒度从一行放大到了整个表。举个例子一张订单表有 100 万行数据事务 A 要更新状态WHERE order_id 12345如果 order_id 有主键索引这个更新只需要锁一行如果没有合适的索引InnoDB 会扫描全部 100 万行去找这一行过程中所有被扫描到的记录都加上了锁。并发一上来事务 B、C、D 想更新其他订单全部被堵在锁上——你以为是业务并发太高其实是索引缺失把锁粒度放大了几十万倍。2.2 行锁、间隙锁与 Next-Key Lock索引定位失败时的连锁反应InnoDB 的默认隔离级别是 REPEATABLE READ很多人以为这个级别下只有行锁。实际上 InnoDB 为了解决幻读问题引入了间隙锁Gap Lock和 Next-Key Lock。这些锁都是基于索引定位的——没有索引连间隙锁都没法准确界定范围。什么情况容易出问题比如一个查询条件WHERE status PENDINGstatus 上有普通二级索引事务在范围扫描时除了锁住匹配的行还会锁住索引叶子节点之间的间隙。另一个事务想在间隙里插入一条新记录时会被阻塞。这里我要强调一个平时不容易注意到的点间隙锁只在通过索引扫描记录时才会加。如果查询条件没索引走了全表扫描那不仅是全表行锁连间隙都变成了全表锁——所有可能插入新记录的位置都被锁住。这在高并发插入场景下会直接表现为两个事务互相等对方的间隙释放然后死锁。有个经典的死锁场景两个事务分别执行UPDATE ... WHERE status A和UPDATE ... WHERE status B如果两边条件都不走索引它们会先各自锁住全表的部分记录然后在间隙上互相等待。实际排查时你会发现死锁的根源根本不在于业务逻辑冲突就是索引没建对。2.3 MVCC 的快照读与当前读索引在副本选择中的作用MVCC多版本并发控制是 InnoDB 实现高并发读的核心机制。它维护了每行数据的多个版本通过 undo log 记录变更历史。读操作分为快照读和当前读普通SELECT是快照读不加锁走 MVCC 版本链SELECT ... FOR UPDATE、UPDATE、DELETE是当前读必须读最新版本并加锁。这里有一个经常被忽略的事实MVCC 的版本链是挂在主键索引的每条记录上的。二级索引需要通过回表通过二级索引找到主键再通过主键找到完整行才能访问版本链。回表在低并发下只是个性能问题但在高并发下会变成一个一致性隐患二级索引读取到的数据版本信息不完整二级索引页只存储部分字段和主键值必须在回表后通过主键索引获取最新的事务版本信息。如果二级索引设计不合理导致大量回表每次回表都要走一遍主键 B 树的查找主键索引页的访问压力剧增缓冲池命中率下降锁等待的概率也随之上升。这也是为什么覆盖索引在并发场景下意义重大——它把回表省掉了既减少了主键索引页的争用又缩短了每条 SQL 的持有锁时间。锁时间越短并发度越高这是贯穿整篇内容的底层逻辑。3. 并发场景下的索引构建DDL 锁与 Online DDL 的实战选择3.1 老版本 DDL 为什么会让数据库卡死很多人建索引的习惯是ALTER TABLE t ADD INDEX idx_name (name);一把梭。在低峰期可能没什么感觉但在并发业务下这个操作可能直接让服务雪崩。MySQL 5.5 及之前的版本创建索引有两种方式COPY 算法拷贝整个表和 INPLACE 算法。但即使是 INPLACE早期实现里也需要全程持有表的 MDL 锁元数据锁整个建索引过程中所有对该表的读写操作都会被阻塞。如果表有几十 GB 数据建索引可能要跑十几分钟期间业务对这张表的任何请求全部排队——这在线上是不可接受的。MySQL 5.6 之后引入了 Online DDL支持了更多 INPLACE 操作包括在绝大多数情况下的在线加索引。在线并不意味着完全不加锁只是锁的持有时间大幅缩短——主要在 DDL 开始和结束的瞬间短暂获取 MDL 锁中间阶段允许并发 DML。我在实际运维中见过太多次因为不理解这个机制导致的故障有人在业务高峰期跑了一个ALTER TABLE加索引结果 MDL 锁排队把读请求全部堵死连接数瞬间打满整个库都响应不了了。3.2 Online DDL 的阶段性锁策略与 ALGORITHM 参数Online DDL 加索引的过程大致分为三个阶段准备阶段获取 MDL 锁检查权限和执行条件短暂阻塞 DML。执行阶段真正构建索引数据结构允许 DML 并发DML 操作会被记录到日志中。提交阶段应用执行阶段记录的 DML 日志与索引数据合并再次短暂获取 MDL 锁然后释放。手动关注点在于第 2 阶段的允许 DML 并发是有代价的系统需要额外记录和回放 DML 日志如果并发很高日志回放跟不上新索引的构建速度DDL 可能会失败回滚。更关键的是 ALGORITHM 参数ALGORITHMINPLACE是首选它允许在索引创建过程中执行并发 DMLALGORITHMCOPY则是完全不能接受的选择——全程锁表。有些人习惯性写成ALGORITHMDEFAULT让 MySQL 自己选但某些索引类型比如全文索引、空间索引仍然会退回 COPY 模式。3.3 一个实用的在线加索引流程基于我在生产环境的经验一个相对安全的加索引流程应该是这样的第一步评估表大小和索引构建时间SELECT COUNT(*)看数据量参考同规格表的历史 DDL 耗时评估窗口期。第二步检查当前是否有长事务长事务会阻塞 MDL 锁的获取。可以查information_schema.innodb_trx看看有没有跑了几百秒的事务有的话等它结束或者找业务方处理。第三步显示指定参数执行ALTER TABLE t ADD INDEX idx_name (name), ALGORITHMINPLACE, LOCKNONE;LOCKNONE表示要求不允许任何锁冲突如果加索引的过程中无法保证无锁比如索引需要覆盖已有数据导致短暂的排他空间操作MySQL 会直接报错而不是降级阻塞。这个设计是好的报错总比线上挂掉强。第四步用SHOW PROCESSLIST盯着 DDL 进度并观察有没有 MDL 等待。如果卡在 waiting for table metadata lock大概率是有长事务卡住了 MDL 释放。另外社区里有一个常用的做法先用CREATE INDEX ... ALGORITHMINPLACE在从库上测试一遍拿到准确的执行时间和锁策略行为再上主库操作。主干上出了问题可以立刻切从库顶上这个保险值得花。3.4 为什么索引命名规范在并发环境里也很重要你可能觉得索引命名和并发八竿子打不着但实际上混乱的索引命名会直接导致 DDL 加错索引、删错索引甚至重复建索引。重复索引意味着每次 DML 都要维护多棵 B 树写放大非常明显在并发写场景下直接拖慢所有事务。我有次接手一个项目发现同一张表上有idx_name、idx_name_2、idx_name_3三个几乎完全相同的索引。问了才知道是历任开发各自为政一个加一次。每当有写请求进来InnoDB 要同步维护三棵索引树本来 1 次插入变成 3 次索引更新——这在高并发写入下是实打实的性能损耗。所以命名规范不只是洁癖问题它也是并发控制的一部分。建议统一格式为idx_字段名_字段名二级索引、uniq_字段名唯一索引。4. 索引设计如何在根源上决定锁粒度与并发上限4.1 联合索引的最左前缀原则并发下最容易埋雷的地方联合索引是并发场景里最值得花时间设计的一类索引。原因在于联合索引决定了你的查询能不能精准定位到行也决定了锁定范围的准确性。MySQL 里联合索引遵循最左前缀原则只有当查询条件的第一个字段命中最左列时索引才会生效后面的字段才会参与索引定位。热搜词里有个mysql where条件a and b应该怎么建索引这正是典型问题。假设一张线下活动报名表常用查询是WHERE activity_id ? AND user_id ?。联合索引应该建(activity_id, user_id)还是(user_id, activity_id)答案不取决于哪个查询多而取决于哪个字段的区分度更高以及哪个字段更常作为独立的过滤条件。如果 activity_id 的区分度高活动很多并且经常单独查询某个活动的所有报名者那么(activity_id, user_id)是正解如果 user_id 区分度更高且经常单独查询某用户的所有报名例如用户中心则反过来的顺序更合适。最怕的是建了一个(a, b)联合索引然后业务查询全是WHERE b ?——这个索引完全用不上每次全表扫描锁的粒度直接回到表级。这不是并发控制的失败是最基础的索引设计失误但在高并发下会被无限放大。4.2 覆盖索引在并发读写中的作用覆盖索引是指查询的字段全部包含在索引树中不需要回表。在并发场景下覆盖索引不仅仅是少一次 IO 那么简单它意味着读操作可以完全不触碰主键索引上的行数据锁也不触碰聚簇索引的叶子节点页。这非常关键。InnoDB 的二级索引叶子上存储的是索引字段加主键值没有完整行数据。读操作只访问二级索引页时普通快照读连锁都不用加即使当前读如UPDATE的前置查询步骤二级索引上的锁也只是索引记录锁不与主键索引的其他行产生冲突。举个例子订单表有order_no唯一索引、status普通索引业务里高频查询是SELECT order_no, status FROM orders WHERE status PAID。这个时候建一个(status, order_no)的联合索引就可以覆盖这个查询读操作全程不触碰聚簇索引对主键索引的热点页压力会小很多。尤其是在并发极高的 ORDER 状态扫描场景下这个小优化往往能拉开一倍以上的吞吐差距。4.3 索引基数、区分度与一锁一大片的隐性问题下面这个问题我几乎每次培训都要讲。有些索引设计的误区是字段有索引但还是慢。典型例子是性别字段。性别只有两个值男、女建了索引后查询WHERE gender M依然会扫描接近一半的数据行。虽然走的是索引但因为命中区间过大锁定的记录数也极其庞大。此时行锁的竞争已经相当于表锁了。这类低区分度字段在并发环境下的危害超过很多人的想象。更新操作按性别条件扫出来的 50 万行记录每条都要加行锁和间隙锁任何其他事务只要更新这 50 万行范围内的任何一行都会产生锁等待。低区分度字段的正确做法一般是不单独建索引。如果一定有过滤需求考虑和其他高区分度字段组合成联合索引确保前缀是高区分度列。实在需要单独过滤也可以考虑引入冗余字段比如状态枚举拆分成多个状态位或者走分区表策略。我见过一个真实的案例某商品表把is_deleted字段建了索引值为 0 和 1。删除操作UPDATE ... WHERE is_deleted 0一来全表 99% 的记录都命中锁覆盖了整个表线上瞬间不可用。这再次印证了一个点索引不是存在就安全锁的范围由索引的区分度决定区分度太低时有索引比没索引问题更大至少没索引时 DBA 会立刻注意到。4.4 主键设计对并发写入的热点影响主键索引是聚簇索引所有二级索引最终都依赖主键定位。主键设计不合理在并发写场景下会形成一个严重的热点页问题。自增主键是最稳妥的选择插入顺序递增新的叶子节点总是追加在 B 树最右侧几乎不产生页分裂和页重组。如果你用 UUID 或者随机字符串作为主键每次插入都要把新记录随机分配到 B 树的不同位置造成大量页分裂触发频繁的平衡操作叶子节点的写锁竞争会异常激烈。唯一索引冲突检测也会拖慢每个 INSERT。顺带提醒一个细节主键长度不宜过长。二级索引的每个条目都要存储主键值主键越长二级索引树越大缓存命中率越低读写都受影响。这也是为什么我一直建议业务主键用自增 BIGINT而不是 36 位 UUID 的实打实原因。并发和索引的关系在这里体现得特别深刻主键一天设计天天受影响。5. 排查并发问题的实战链路慢查询、死锁分析与索引失效5.1 第一步用慢查询日志定位索引没走对的 SQL并发问题暴露时先从慢查询日志入手。线上 MySQL 建议把long_query_time设置为 1 秒或更低默认 10 秒太长了等你能看到慢 SQL 时业务已经挂了。开启慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;拿到慢 SQL 后用EXPLAIN看执行计划重点看这几个字段type如果是 ALL说明全表扫描没有走索引锁粒度直接是表级这是并发问题的第一元凶。key实际使用到的索引如果为 NULL也是全表扫描。rows预估扫描行数。注意这个数值同时约等于锁定的记录行数如果 rows 是几十万那你的行锁实际上已经等于表锁了。Extra如果出现 Using filesort 或 Using temporary说明索引设计没有覆盖排序或分组需求这本身不会直接造成锁竞争但会导致 SQL 执行时间变长间接延长锁持有时间。排查的原则是任何一条可能在并发高峰执行的 SQL都必须有 type 达到 range 级别或更高的索引支持。如果是 ref 甚至 const那恭喜锁的粒度已经是最优了。5.2 第二步从 information_schema 看当前锁等待当系统已经出现锁等待报警时立刻执行这样一组查询-- 查看当前所有事务及其状态 SELECT * FROM information_schema.innodb_trx; -- 查看锁等待关系 SELECT * FROM sys.innodb_lock_waits;重点观察trx_state为 RUNNING 却迟迟不提交的事务trx_query里有没有走索引。锁等待的本质是事务 A 持有一批索引记录的锁事务 B 需要同一范围内的锁。如果事务 A 的查询条件没有索引它拼命扫完全表持有上万条记录锁B 只能等。此时看innodb_lock_waits能直接告诉你阻塞链是哪条 SQL 导致的。这个排查手段非常快。有一次我定位到某报表查询事务跑了几十分钟不提交它查询条件里的时间字段没有索引导致全表扫描并锁住了整个表导致订单表的所有写操作全部排队。把报表改成走时间索引后锁等待立刻消失了。5.3 第三步通过 show engine innodb status 分析死锁死锁日志通常被很多人视为看不懂的天书但其实结构非常清晰。执行SHOW ENGINE INNODB STATUS\G找到 LATEST DETECTED DEADLOCK 部分它会打印两个事务各自的锁信息。核心看两件事事务持有哪些锁每条 Lock 后面会标注锁的类型RECORD LOCKS、GAP、NEXT-KEY如果看到锁记录的范围特别大大概率索引区分度不行。等待的锁在哪里死锁的成因是两个事务循环等待对方持有的锁。如果日志里显示涉及的是相同的索引范围和相同字段值说明索引设计没有把两个事务的访问路径隔离。下面这种死锁日志模式非常经典Transaction A: UPDATE orders SET amount 100 WHERE status PAID; Transaction B: UPDATE orders SET amount 200 WHERE status PAID;两个事务都通过 status 索引扫描status 区分度低扫描范围高度重合互相持有了对方需要的间隙锁或行锁然后各自等待死锁发生。解决方式有两类业务侧固定事务访问顺序比如总是按某种规则排序后再更新打破循环等待。索引侧提高索引区分度把锁的粒度收窄减少互相覆盖的区域。如果 status 这个字段本身区分度低就考虑建联合索引比如(status, order_id)让每个事务锁定的行更精准。5.4 第四步索引失效的常见场景复盘排查并发问题时还有一种情况让你怀疑人生明明建了索引执行计划显示不走。常见原因无非几种对索引列使用函数例如WHERE DATE(create_time) 2024-01-01这样索引就失效了。应该改成WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02。隐式类型转换字段是字符串类型查询条件传的是数字MySQL 会隐式把字段转成数字比较索引失效。这也是热搜里那个mysql索引话题下最常见的提问。前导模糊查询LIKE %abc无法用索引因为 B 树是从左到右匹配的。联合索引违反最左前缀WHERE b ?但索引是(a, b)直接失效。这些失效场景本身是老生常谈但在并发环境下的连锁反应值得重新强调一旦大量 SQL 因为隐式转换或函数导致全表扫描锁的粒度会在某个时刻突然从行级放大到表级。这个突然放大往往就是故障的临界点。6. 并发场景下的索引优化经验几个实测有效的落地技巧6.1 用锁持有时间来评估索引质量大多数人在评估索引时只看查询快不快我会额外看一个指标这条 SQL 在事务里平均持有锁的时间。持有锁的时间 执行时间 事务中后续操作的剩余时间。索引能压缩的是执行时间而事务设计尽早提交、减少事务内无关操作压缩的是剩余时间。两者结合才决定并发上限。我做过一次实测对比同一张 500 万行订单表不加索引的事务平均锁持有时间约 120 毫秒加覆盖索引后降到约 8 毫秒。在 200 并发下前者系统直接不可用线程全部在等锁后者吞吐稳定在每秒 1000 事务。锁持有时间的缩短对并发的影响比大多数优化都显著。6.2 唯一索引与幂等设计对并发写入的隐性帮助并发写入场景下唯一索引有两面性一方面它保证数据唯一性避免重复插入另一方面每次 INSERT 都要做一次唯一性冲突检测如果冲突频繁会引发大量的锁竞争。实际业务中常见同一个订单号重复提交这种场景如果用唯一索引当最后一道防线INSERT 会触发 duplicate key 错误但更重要的是锁竞争——InnoDB 在检查唯一约束时会对索引记录加锁并发冲突越多锁竞争越严重。有一个实践技巧业务侧可以先做幂等判断把必然重复的请求挡在数据库之外降低唯一索引冲突检测的频率。数据库层的唯一索引是兜底不是替代业务判断的。6.3 索引下推少回表就是少锁MySQL 5.6 引入了索引条件下推ICPIndex Condition Pushdown。在没有 ICP 时二级索引查到的每一条记录都要回表拿完整行数据再在 server 层判断其他条件是否满足。有了 ICPMySQL 会把部分 WHERE 条件直接下推到存储引擎层用二级索引里的字段先过滤一遍过滤不掉的再回表。对并发控制来说ICP 最直接的好处是减少了回表数量减少了主键索引页的访问两件事都能缩短 SQL 执行时间和锁持有时间。实际工作中我建议多利用联合索引来实现 ICP 过滤让更多过滤条件在索引层完成。6.4 分区表与索引的取舍分区表也是个和并发控制强相关的方案。当一张表过大时分区的目的是减少单次查询涉及的数据量从而缩小锁定的索引范围。但要注意分区表上的索引必须包含分区键否则查询无法进行分区裁剪还是会扫描所有分区。比如订单表按 create_date 分区查询如果只用 order_no 做条件而 order_no 不是分区键MySQL 会全分区扫描。这比普通全表扫描还糟。所以分区表设计时必须考虑每个常用查询条件必须带上分区键或者把分区键作为联合索引前缀。我一般建议在表超过 2000 万行、且有明确时间维度的数据保留需求时再考虑分区否则优先通过合理的索引设计解决问题。分区不是万能的它在并发场景下也可能制造新的问题比如分区之间的 DDL 进度不同步。7. 关于索引并发控制我想再补充的几个实操心得写到最后分享几个我自己从实战中总结的、很难在官方文档里看到的小心得。第一监控里一定要有锁等待次数和锁等待时长这两个指标。很多团队的监控面板只放 QPS、CPU、慢查询唯独漏了锁相关指标。等到出现大量锁等待时往往已经是雪崩的临界点。可以在 performance_schema 里配置对wait/io/table/sql/handler和lock/wait相关事件的采集并设置告警阈值。第二索引新增和删除要放在同一个变更流程里评估。我有一个很笨但很有效的做法每次线上变更索引都做一轮并发压测对比——同样 200 并发、同样的增删改查混合比例压测前和后分别跑一次比较延迟分布和吞吐。索引变更不是 DDL 那么简单它对并发的影响只有压测才能暴露。第三半连接Semijoin和派生表条件在外层时索引利用率波动很大。复杂 SQL 的索引选择和单独查询时可能完全不同这也是为什么我在排查时很少只看 SQL 本身而会结合完整的事务、连接池配置、隔离级别来看。比如SELECT ... WHERE id IN (SELECT order_id FROM xx)这类 SQL如果子查询条件本身区分度低外层怎么建索引都可能失效要注意改写。第四线上永远记得把事务隔离级别和锁等待超时时间配好。例如innodb_lock_wait_timeout默认是 50 秒这个时间太长了一旦锁等待发生50 秒内所有请求全部堆积连接池迅速耗尽。生产环境我一般设置成 3 到 5 秒宁可让业务快速报错失败重试也不让线程一直挂在锁上。第五关于索引并发控制还有一个常被忽略的维度查询结果集大小本身。即使走的是完美索引如果单次查询返回 10 万行传输和加锁的规模也注定了并发不会高。高并发场景下能用分页、条件收窄来控制每次查询返回的行数这比任何索引优化都更重要。这不只是性能设计也是并发设计的一部分。最后再分享一个小技巧吧。每次我在一个新项目里优化索引并发都会先画一张表-索引-事务的映射表把每张表的主力索引、高频事务、锁范围列出来。这个表不一定发给别人看但它能让我在五分钟内定位到并发瓶颈是锁竞争还是索引失效。这个习惯救过我很多次现在也推荐给你。 索引并发控制：从B+树到锁竞争，一次讲透索引与数据库稳定性
1. 从一次压测事故说起并发总在索引上翻车前阵子帮朋友排查一个线上问题现象很典型。他们的订单查询接口在低并发时一切正常响应时间稳定在 30 毫秒左右。压测一上到 200 并发数据库这边立刻开始报警线程堆积、CPU 飙到 80% 以上慢查询日志刷屏。我第一反应是看索引结果发现核心查询条件里有一个字段根本没建索引走了全表扫描。这个场景太常见了。很多团队把索引当成查询优化工具只在 SQL 变慢时才考虑加索引。但索引从来不只是查询性能的问题——在并发场景下索引设计的好坏直接决定了锁的粒度、事务的冲突概率、甚至死锁会不会频繁出现。这就是我为什么想认真写一篇关于索引并发控制的内容索引和并发控制从来都是同一枚硬币的两面。这篇内容会从 InnoDB 的索引结构出发讲清楚为什么索引会深度参与并发控制再展开索引锁、MVCC、DDL 并发、索引设计等几个层面的实操方法最后给出排查并发问题的完整链路。适合 DBA、后端开发、架构师以及所有正在被连接数打满死锁频繁锁等待超时折磨的同行。我先说一个反直觉的结论索引并不是越多越好错误的索引在并发场景下会主动制造锁竞争。所以这篇文章的核心不是教你怎么建索引而是教你怎么在并发和索引之间找到那个平衡点。2. 索引结构与并发控制的底层纠缠B 树、行锁与 MVCC2.1 为什么 B 树结构天然影响锁的竞争范围要理解索引和并发的关系先得回到索引的物理结构。InnoDB 的索引是 B 树数据都挂在叶子节点上每个叶子节点就是一个数据页默认 16KB。树的高度决定了查询需要访问多少层节点但真正影响并发的是当多个事务同时访问同一棵 B 树时它们会在哪些节点上产生竞争读操作之间不互斥读和写互斥写和写互斥。如果两个事务要更新同一个数据页里不同行的数据InnoDB 通过行锁Record Lock把冲突范围控制在行级别。但行锁能生效的前提是查询必须能通过索引精确定位到行。如果查询不走索引InnoDB 就只能退而求其次锁住整个表的记录——这就是全表扫描在并发场景下特别致命的原因它不是慢那么简单它把锁的粒度从一行放大到了整个表。举个例子一张订单表有 100 万行数据事务 A 要更新状态WHERE order_id 12345如果 order_id 有主键索引这个更新只需要锁一行如果没有合适的索引InnoDB 会扫描全部 100 万行去找这一行过程中所有被扫描到的记录都加上了锁。并发一上来事务 B、C、D 想更新其他订单全部被堵在锁上——你以为是业务并发太高其实是索引缺失把锁粒度放大了几十万倍。2.2 行锁、间隙锁与 Next-Key Lock索引定位失败时的连锁反应InnoDB 的默认隔离级别是 REPEATABLE READ很多人以为这个级别下只有行锁。实际上 InnoDB 为了解决幻读问题引入了间隙锁Gap Lock和 Next-Key Lock。这些锁都是基于索引定位的——没有索引连间隙锁都没法准确界定范围。什么情况容易出问题比如一个查询条件WHERE status PENDINGstatus 上有普通二级索引事务在范围扫描时除了锁住匹配的行还会锁住索引叶子节点之间的间隙。另一个事务想在间隙里插入一条新记录时会被阻塞。这里我要强调一个平时不容易注意到的点间隙锁只在通过索引扫描记录时才会加。如果查询条件没索引走了全表扫描那不仅是全表行锁连间隙都变成了全表锁——所有可能插入新记录的位置都被锁住。这在高并发插入场景下会直接表现为两个事务互相等对方的间隙释放然后死锁。有个经典的死锁场景两个事务分别执行UPDATE ... WHERE status A和UPDATE ... WHERE status B如果两边条件都不走索引它们会先各自锁住全表的部分记录然后在间隙上互相等待。实际排查时你会发现死锁的根源根本不在于业务逻辑冲突就是索引没建对。2.3 MVCC 的快照读与当前读索引在副本选择中的作用MVCC多版本并发控制是 InnoDB 实现高并发读的核心机制。它维护了每行数据的多个版本通过 undo log 记录变更历史。读操作分为快照读和当前读普通SELECT是快照读不加锁走 MVCC 版本链SELECT ... FOR UPDATE、UPDATE、DELETE是当前读必须读最新版本并加锁。这里有一个经常被忽略的事实MVCC 的版本链是挂在主键索引的每条记录上的。二级索引需要通过回表通过二级索引找到主键再通过主键找到完整行才能访问版本链。回表在低并发下只是个性能问题但在高并发下会变成一个一致性隐患二级索引读取到的数据版本信息不完整二级索引页只存储部分字段和主键值必须在回表后通过主键索引获取最新的事务版本信息。如果二级索引设计不合理导致大量回表每次回表都要走一遍主键 B 树的查找主键索引页的访问压力剧增缓冲池命中率下降锁等待的概率也随之上升。这也是为什么覆盖索引在并发场景下意义重大——它把回表省掉了既减少了主键索引页的争用又缩短了每条 SQL 的持有锁时间。锁时间越短并发度越高这是贯穿整篇内容的底层逻辑。3. 并发场景下的索引构建DDL 锁与 Online DDL 的实战选择3.1 老版本 DDL 为什么会让数据库卡死很多人建索引的习惯是ALTER TABLE t ADD INDEX idx_name (name);一把梭。在低峰期可能没什么感觉但在并发业务下这个操作可能直接让服务雪崩。MySQL 5.5 及之前的版本创建索引有两种方式COPY 算法拷贝整个表和 INPLACE 算法。但即使是 INPLACE早期实现里也需要全程持有表的 MDL 锁元数据锁整个建索引过程中所有对该表的读写操作都会被阻塞。如果表有几十 GB 数据建索引可能要跑十几分钟期间业务对这张表的任何请求全部排队——这在线上是不可接受的。MySQL 5.6 之后引入了 Online DDL支持了更多 INPLACE 操作包括在绝大多数情况下的在线加索引。在线并不意味着完全不加锁只是锁的持有时间大幅缩短——主要在 DDL 开始和结束的瞬间短暂获取 MDL 锁中间阶段允许并发 DML。我在实际运维中见过太多次因为不理解这个机制导致的故障有人在业务高峰期跑了一个ALTER TABLE加索引结果 MDL 锁排队把读请求全部堵死连接数瞬间打满整个库都响应不了了。3.2 Online DDL 的阶段性锁策略与 ALGORITHM 参数Online DDL 加索引的过程大致分为三个阶段准备阶段获取 MDL 锁检查权限和执行条件短暂阻塞 DML。执行阶段真正构建索引数据结构允许 DML 并发DML 操作会被记录到日志中。提交阶段应用执行阶段记录的 DML 日志与索引数据合并再次短暂获取 MDL 锁然后释放。手动关注点在于第 2 阶段的允许 DML 并发是有代价的系统需要额外记录和回放 DML 日志如果并发很高日志回放跟不上新索引的构建速度DDL 可能会失败回滚。更关键的是 ALGORITHM 参数ALGORITHMINPLACE是首选它允许在索引创建过程中执行并发 DMLALGORITHMCOPY则是完全不能接受的选择——全程锁表。有些人习惯性写成ALGORITHMDEFAULT让 MySQL 自己选但某些索引类型比如全文索引、空间索引仍然会退回 COPY 模式。3.3 一个实用的在线加索引流程基于我在生产环境的经验一个相对安全的加索引流程应该是这样的第一步评估表大小和索引构建时间SELECT COUNT(*)看数据量参考同规格表的历史 DDL 耗时评估窗口期。第二步检查当前是否有长事务长事务会阻塞 MDL 锁的获取。可以查information_schema.innodb_trx看看有没有跑了几百秒的事务有的话等它结束或者找业务方处理。第三步显示指定参数执行ALTER TABLE t ADD INDEX idx_name (name), ALGORITHMINPLACE, LOCKNONE;LOCKNONE表示要求不允许任何锁冲突如果加索引的过程中无法保证无锁比如索引需要覆盖已有数据导致短暂的排他空间操作MySQL 会直接报错而不是降级阻塞。这个设计是好的报错总比线上挂掉强。第四步用SHOW PROCESSLIST盯着 DDL 进度并观察有没有 MDL 等待。如果卡在 waiting for table metadata lock大概率是有长事务卡住了 MDL 释放。另外社区里有一个常用的做法先用CREATE INDEX ... ALGORITHMINPLACE在从库上测试一遍拿到准确的执行时间和锁策略行为再上主库操作。主干上出了问题可以立刻切从库顶上这个保险值得花。3.4 为什么索引命名规范在并发环境里也很重要你可能觉得索引命名和并发八竿子打不着但实际上混乱的索引命名会直接导致 DDL 加错索引、删错索引甚至重复建索引。重复索引意味着每次 DML 都要维护多棵 B 树写放大非常明显在并发写场景下直接拖慢所有事务。我有次接手一个项目发现同一张表上有idx_name、idx_name_2、idx_name_3三个几乎完全相同的索引。问了才知道是历任开发各自为政一个加一次。每当有写请求进来InnoDB 要同步维护三棵索引树本来 1 次插入变成 3 次索引更新——这在高并发写入下是实打实的性能损耗。所以命名规范不只是洁癖问题它也是并发控制的一部分。建议统一格式为idx_字段名_字段名二级索引、uniq_字段名唯一索引。4. 索引设计如何在根源上决定锁粒度与并发上限4.1 联合索引的最左前缀原则并发下最容易埋雷的地方联合索引是并发场景里最值得花时间设计的一类索引。原因在于联合索引决定了你的查询能不能精准定位到行也决定了锁定范围的准确性。MySQL 里联合索引遵循最左前缀原则只有当查询条件的第一个字段命中最左列时索引才会生效后面的字段才会参与索引定位。热搜词里有个mysql where条件a and b应该怎么建索引这正是典型问题。假设一张线下活动报名表常用查询是WHERE activity_id ? AND user_id ?。联合索引应该建(activity_id, user_id)还是(user_id, activity_id)答案不取决于哪个查询多而取决于哪个字段的区分度更高以及哪个字段更常作为独立的过滤条件。如果 activity_id 的区分度高活动很多并且经常单独查询某个活动的所有报名者那么(activity_id, user_id)是正解如果 user_id 区分度更高且经常单独查询某用户的所有报名例如用户中心则反过来的顺序更合适。最怕的是建了一个(a, b)联合索引然后业务查询全是WHERE b ?——这个索引完全用不上每次全表扫描锁的粒度直接回到表级。这不是并发控制的失败是最基础的索引设计失误但在高并发下会被无限放大。4.2 覆盖索引在并发读写中的作用覆盖索引是指查询的字段全部包含在索引树中不需要回表。在并发场景下覆盖索引不仅仅是少一次 IO 那么简单它意味着读操作可以完全不触碰主键索引上的行数据锁也不触碰聚簇索引的叶子节点页。这非常关键。InnoDB 的二级索引叶子上存储的是索引字段加主键值没有完整行数据。读操作只访问二级索引页时普通快照读连锁都不用加即使当前读如UPDATE的前置查询步骤二级索引上的锁也只是索引记录锁不与主键索引的其他行产生冲突。举个例子订单表有order_no唯一索引、status普通索引业务里高频查询是SELECT order_no, status FROM orders WHERE status PAID。这个时候建一个(status, order_no)的联合索引就可以覆盖这个查询读操作全程不触碰聚簇索引对主键索引的热点页压力会小很多。尤其是在并发极高的 ORDER 状态扫描场景下这个小优化往往能拉开一倍以上的吞吐差距。4.3 索引基数、区分度与一锁一大片的隐性问题下面这个问题我几乎每次培训都要讲。有些索引设计的误区是字段有索引但还是慢。典型例子是性别字段。性别只有两个值男、女建了索引后查询WHERE gender M依然会扫描接近一半的数据行。虽然走的是索引但因为命中区间过大锁定的记录数也极其庞大。此时行锁的竞争已经相当于表锁了。这类低区分度字段在并发环境下的危害超过很多人的想象。更新操作按性别条件扫出来的 50 万行记录每条都要加行锁和间隙锁任何其他事务只要更新这 50 万行范围内的任何一行都会产生锁等待。低区分度字段的正确做法一般是不单独建索引。如果一定有过滤需求考虑和其他高区分度字段组合成联合索引确保前缀是高区分度列。实在需要单独过滤也可以考虑引入冗余字段比如状态枚举拆分成多个状态位或者走分区表策略。我见过一个真实的案例某商品表把is_deleted字段建了索引值为 0 和 1。删除操作UPDATE ... WHERE is_deleted 0一来全表 99% 的记录都命中锁覆盖了整个表线上瞬间不可用。这再次印证了一个点索引不是存在就安全锁的范围由索引的区分度决定区分度太低时有索引比没索引问题更大至少没索引时 DBA 会立刻注意到。4.4 主键设计对并发写入的热点影响主键索引是聚簇索引所有二级索引最终都依赖主键定位。主键设计不合理在并发写场景下会形成一个严重的热点页问题。自增主键是最稳妥的选择插入顺序递增新的叶子节点总是追加在 B 树最右侧几乎不产生页分裂和页重组。如果你用 UUID 或者随机字符串作为主键每次插入都要把新记录随机分配到 B 树的不同位置造成大量页分裂触发频繁的平衡操作叶子节点的写锁竞争会异常激烈。唯一索引冲突检测也会拖慢每个 INSERT。顺带提醒一个细节主键长度不宜过长。二级索引的每个条目都要存储主键值主键越长二级索引树越大缓存命中率越低读写都受影响。这也是为什么我一直建议业务主键用自增 BIGINT而不是 36 位 UUID 的实打实原因。并发和索引的关系在这里体现得特别深刻主键一天设计天天受影响。5. 排查并发问题的实战链路慢查询、死锁分析与索引失效5.1 第一步用慢查询日志定位索引没走对的 SQL并发问题暴露时先从慢查询日志入手。线上 MySQL 建议把long_query_time设置为 1 秒或更低默认 10 秒太长了等你能看到慢 SQL 时业务已经挂了。开启慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;拿到慢 SQL 后用EXPLAIN看执行计划重点看这几个字段type如果是 ALL说明全表扫描没有走索引锁粒度直接是表级这是并发问题的第一元凶。key实际使用到的索引如果为 NULL也是全表扫描。rows预估扫描行数。注意这个数值同时约等于锁定的记录行数如果 rows 是几十万那你的行锁实际上已经等于表锁了。Extra如果出现 Using filesort 或 Using temporary说明索引设计没有覆盖排序或分组需求这本身不会直接造成锁竞争但会导致 SQL 执行时间变长间接延长锁持有时间。排查的原则是任何一条可能在并发高峰执行的 SQL都必须有 type 达到 range 级别或更高的索引支持。如果是 ref 甚至 const那恭喜锁的粒度已经是最优了。5.2 第二步从 information_schema 看当前锁等待当系统已经出现锁等待报警时立刻执行这样一组查询-- 查看当前所有事务及其状态 SELECT * FROM information_schema.innodb_trx; -- 查看锁等待关系 SELECT * FROM sys.innodb_lock_waits;重点观察trx_state为 RUNNING 却迟迟不提交的事务trx_query里有没有走索引。锁等待的本质是事务 A 持有一批索引记录的锁事务 B 需要同一范围内的锁。如果事务 A 的查询条件没有索引它拼命扫完全表持有上万条记录锁B 只能等。此时看innodb_lock_waits能直接告诉你阻塞链是哪条 SQL 导致的。这个排查手段非常快。有一次我定位到某报表查询事务跑了几十分钟不提交它查询条件里的时间字段没有索引导致全表扫描并锁住了整个表导致订单表的所有写操作全部排队。把报表改成走时间索引后锁等待立刻消失了。5.3 第三步通过 show engine innodb status 分析死锁死锁日志通常被很多人视为看不懂的天书但其实结构非常清晰。执行SHOW ENGINE INNODB STATUS\G找到 LATEST DETECTED DEADLOCK 部分它会打印两个事务各自的锁信息。核心看两件事事务持有哪些锁每条 Lock 后面会标注锁的类型RECORD LOCKS、GAP、NEXT-KEY如果看到锁记录的范围特别大大概率索引区分度不行。等待的锁在哪里死锁的成因是两个事务循环等待对方持有的锁。如果日志里显示涉及的是相同的索引范围和相同字段值说明索引设计没有把两个事务的访问路径隔离。下面这种死锁日志模式非常经典Transaction A: UPDATE orders SET amount 100 WHERE status PAID; Transaction B: UPDATE orders SET amount 200 WHERE status PAID;两个事务都通过 status 索引扫描status 区分度低扫描范围高度重合互相持有了对方需要的间隙锁或行锁然后各自等待死锁发生。解决方式有两类业务侧固定事务访问顺序比如总是按某种规则排序后再更新打破循环等待。索引侧提高索引区分度把锁的粒度收窄减少互相覆盖的区域。如果 status 这个字段本身区分度低就考虑建联合索引比如(status, order_id)让每个事务锁定的行更精准。5.4 第四步索引失效的常见场景复盘排查并发问题时还有一种情况让你怀疑人生明明建了索引执行计划显示不走。常见原因无非几种对索引列使用函数例如WHERE DATE(create_time) 2024-01-01这样索引就失效了。应该改成WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02。隐式类型转换字段是字符串类型查询条件传的是数字MySQL 会隐式把字段转成数字比较索引失效。这也是热搜里那个mysql索引话题下最常见的提问。前导模糊查询LIKE %abc无法用索引因为 B 树是从左到右匹配的。联合索引违反最左前缀WHERE b ?但索引是(a, b)直接失效。这些失效场景本身是老生常谈但在并发环境下的连锁反应值得重新强调一旦大量 SQL 因为隐式转换或函数导致全表扫描锁的粒度会在某个时刻突然从行级放大到表级。这个突然放大往往就是故障的临界点。6. 并发场景下的索引优化经验几个实测有效的落地技巧6.1 用锁持有时间来评估索引质量大多数人在评估索引时只看查询快不快我会额外看一个指标这条 SQL 在事务里平均持有锁的时间。持有锁的时间 执行时间 事务中后续操作的剩余时间。索引能压缩的是执行时间而事务设计尽早提交、减少事务内无关操作压缩的是剩余时间。两者结合才决定并发上限。我做过一次实测对比同一张 500 万行订单表不加索引的事务平均锁持有时间约 120 毫秒加覆盖索引后降到约 8 毫秒。在 200 并发下前者系统直接不可用线程全部在等锁后者吞吐稳定在每秒 1000 事务。锁持有时间的缩短对并发的影响比大多数优化都显著。6.2 唯一索引与幂等设计对并发写入的隐性帮助并发写入场景下唯一索引有两面性一方面它保证数据唯一性避免重复插入另一方面每次 INSERT 都要做一次唯一性冲突检测如果冲突频繁会引发大量的锁竞争。实际业务中常见同一个订单号重复提交这种场景如果用唯一索引当最后一道防线INSERT 会触发 duplicate key 错误但更重要的是锁竞争——InnoDB 在检查唯一约束时会对索引记录加锁并发冲突越多锁竞争越严重。有一个实践技巧业务侧可以先做幂等判断把必然重复的请求挡在数据库之外降低唯一索引冲突检测的频率。数据库层的唯一索引是兜底不是替代业务判断的。6.3 索引下推少回表就是少锁MySQL 5.6 引入了索引条件下推ICPIndex Condition Pushdown。在没有 ICP 时二级索引查到的每一条记录都要回表拿完整行数据再在 server 层判断其他条件是否满足。有了 ICPMySQL 会把部分 WHERE 条件直接下推到存储引擎层用二级索引里的字段先过滤一遍过滤不掉的再回表。对并发控制来说ICP 最直接的好处是减少了回表数量减少了主键索引页的访问两件事都能缩短 SQL 执行时间和锁持有时间。实际工作中我建议多利用联合索引来实现 ICP 过滤让更多过滤条件在索引层完成。6.4 分区表与索引的取舍分区表也是个和并发控制强相关的方案。当一张表过大时分区的目的是减少单次查询涉及的数据量从而缩小锁定的索引范围。但要注意分区表上的索引必须包含分区键否则查询无法进行分区裁剪还是会扫描所有分区。比如订单表按 create_date 分区查询如果只用 order_no 做条件而 order_no 不是分区键MySQL 会全分区扫描。这比普通全表扫描还糟。所以分区表设计时必须考虑每个常用查询条件必须带上分区键或者把分区键作为联合索引前缀。我一般建议在表超过 2000 万行、且有明确时间维度的数据保留需求时再考虑分区否则优先通过合理的索引设计解决问题。分区不是万能的它在并发场景下也可能制造新的问题比如分区之间的 DDL 进度不同步。7. 关于索引并发控制我想再补充的几个实操心得写到最后分享几个我自己从实战中总结的、很难在官方文档里看到的小心得。第一监控里一定要有锁等待次数和锁等待时长这两个指标。很多团队的监控面板只放 QPS、CPU、慢查询唯独漏了锁相关指标。等到出现大量锁等待时往往已经是雪崩的临界点。可以在 performance_schema 里配置对wait/io/table/sql/handler和lock/wait相关事件的采集并设置告警阈值。第二索引新增和删除要放在同一个变更流程里评估。我有一个很笨但很有效的做法每次线上变更索引都做一轮并发压测对比——同样 200 并发、同样的增删改查混合比例压测前和后分别跑一次比较延迟分布和吞吐。索引变更不是 DDL 那么简单它对并发的影响只有压测才能暴露。第三半连接Semijoin和派生表条件在外层时索引利用率波动很大。复杂 SQL 的索引选择和单独查询时可能完全不同这也是为什么我在排查时很少只看 SQL 本身而会结合完整的事务、连接池配置、隔离级别来看。比如SELECT ... WHERE id IN (SELECT order_id FROM xx)这类 SQL如果子查询条件本身区分度低外层怎么建索引都可能失效要注意改写。第四线上永远记得把事务隔离级别和锁等待超时时间配好。例如innodb_lock_wait_timeout默认是 50 秒这个时间太长了一旦锁等待发生50 秒内所有请求全部堆积连接池迅速耗尽。生产环境我一般设置成 3 到 5 秒宁可让业务快速报错失败重试也不让线程一直挂在锁上。第五关于索引并发控制还有一个常被忽略的维度查询结果集大小本身。即使走的是完美索引如果单次查询返回 10 万行传输和加锁的规模也注定了并发不会高。高并发场景下能用分页、条件收窄来控制每次查询返回的行数这比任何索引优化都更重要。这不只是性能设计也是并发设计的一部分。最后再分享一个小技巧吧。每次我在一个新项目里优化索引并发都会先画一张表-索引-事务的映射表把每张表的主力索引、高频事务、锁范围列出来。这个表不一定发给别人看但它能让我在五分钟内定位到并发瓶颈是锁竞争还是索引失效。这个习惯救过我很多次现在也推荐给你。