mysql在高并发场景中的索引优化策略

根本原因是非唯一二级索引导致间隙锁或临键锁，即使有索引也会锁住大范围；应优先用UNIQUE索引、避免高频字段单独建索引、用联合索引优化，并确保INSERT...ON DUPLICATE KEY UPDATE仅依赖单一唯一索引。

为什么 SELECT ... FOR UPDATE 会卡住，而加了索引也不行？

根本原因不是没加索引，而是加了「非唯一二级索引」却没覆盖查询条件，导致 MySQL 退化为间隙锁（Gap Lock）或临键锁（Next-Key Lock），锁住一大片范围。比如 WHERE status = 1，即使 status 有索引，若该值重复率高，InnoDB 仍可能锁住多个索引项及其间隙。

• 优先用 UNIQUE 索引替代普通二级索引，让 FOR UPDATE 尽可能走唯一查找，只锁单行
• 避免在高频更新字段（如 status、version）上建单独索引，改用联合索引前置该字段 + 主键或高频过滤字段
• 执行 EXPLAIN 确认是否走了索引，特别注意 key 和 rows 列；若 rows 远大于实际匹配数，说明索引选择性差

INSERT ... ON DUPLICATE KEY UPDATE 的索引依赖和死锁风险

这个语句本质是先按唯一约束（主键或 UNIQUE 索引）查找，再决定插入或更新。如果唯一约束不明确、或存在多个 UNIQUE 索引，MySQL 可能加锁顺序不一致，引发死锁。

• 必须确保冲突检测只依赖**一个明确的唯一索引**，不要同时定义多个 UNIQUE 约束（如 email 和 phone 都设 UNIQUE）
• 联合唯一索引要严格按查询顺序定义，例如常用 INSERT ... ON DUPLICATE KEY UPDATE 基于 (tenant_id, biz_id)，就建 UNIQUE KEY uk_tenant_biz (tenant_id, biz_id)，别反过来
• 高并发下，该语句仍可能因锁等待超时报 Deadlock found when trying to get lock，需在应用层重试，但重试前建议加随机微小延迟（如 1–10ms）

联合索引的最左匹配失效：为什么 WHERE a = ? AND c = ? 没走索引？

当联合索引是 (a, b, c)，而查询跳过中间列 b，MySQL 无法使用 c 部分做索引查找，只能用到 a，c 变成回表后过滤。

CREATE INDEX idx_abc ON orders (user_id, status, created_at);

• ✅ WHERE user_id = 123 AND status = 1 → 走索引，且 status 可用于范围裁剪
• ❌ WHERE user_id = 123 AND created_at > '2025-01-01' → created_at 不生效，需回表后过滤
• 对策：把高频等值查询字段放最左，范围查询字段放最右；必要时拆成两个索引，比如补一个 (user_id, created_at)

高并发写入时，自增主键为何比 UUID 更稳？

UUID（尤其无序版本如 UUID_SHORT() 或字符串 UUID）会导致聚簇索引频繁页分裂，B+ 树节点反复重排，产生大量磁盘随机写和锁竞争。而 BIGINT AUTO_INCREMENT 是严格递增的，新记录总追加到 B+ 树最右叶子页，写放大最小。

• 不要用 CHAR(36) 存 UUID 作主键，哪怕加了索引，写入吞吐也会掉 30%+（实测 5k QPS 场景下）
• 若业务强制需要分布式唯一 ID，优先选有序类方案：Twitter Snowflake、Leaf-segment 或 MySQL 8.0+ 的 UUID_TO_BIN(UUID(), true)（true 表示 compact mode）
• 自增主键记得设足够大的类型，BIGINT UNSIGNED 比 INT 更安全，避免某天凌晨突然主键溢出

索引不是越多越好，真正关键的是让每条慢查背后的 WHERE、ORDER BY、GROUP BY 都落在一个高效索引的最左前缀上；而高并发下，锁粒度、写入顺序、唯一性保障，往往比“能不能查得快”更致命。