KEEL · 龙骨 · A CURRICULUM FOR THE AI ERA

索引与并发速查 Playbook — keel 龙骨

索引与并发实战的参考信息：索引与并发速查 Playbook

一页纸版本。定位 → 定量 → 改动 → 回滚。

一、慢 SQL 定位（5 分钟出结论）

EXPLAIN SELECT ...\G

列	危险信号	说明
`type`	`ALL` / `index`	全表或全索引扫描
`key`	NULL	未用索引
`key_len`	小于索引总长	联合索引只用了前几列（算出具体用了几列）
`rows`	远大于返回行数	过滤效率低
`filtered`	很低（如 1.00）	索引只做了粗筛，大量行在 server 层被丢弃
`Extra`	`Using filesort` / `Using temporary`	排序或分组未走索引

EXPLAIN ANALYZE SELECT ...;      -- 实际耗时（会真执行）
EXPLAIN FORMAT=JSON SELECT ...;  -- 看 cost_info
SET optimizer_trace='enabled=on'; -- 看优化器为什么选/不选某索引
ANALYZE TABLE t;                 -- 统计信息过期时先跑这个

定位顺序：慢日志 Top SQL → EXPLAIN 判层（索引选择 / 存储扫描 / server 过滤 / 排序 / JOIN 顺序）→ 对应改法。

二、索引设计三段法

① 等值条件列（选择性高在前）
② 排序列（ORDER BY / GROUP BY）
③ 范围条件列（放最后，其后的列只能索引下推）

选择性 = COUNT(DISTINCT col)/COUNT(*)，< 0.01 的列不要单建索引
覆盖索引：查询列全在索引里 → Using index（省回表），但索引变宽有写放大
前缀索引：COUNT(DISTINCT LEFT(col,N))/COUNT(*) 找最短可接受长度（不能用于覆盖/排序）
函数索引：CREATE INDEX idx ON t ((DATE(created_at)))，只匹配完全相同的表达式

三、索引失效清单（对照排查）

□ 索引列套函数 / 参与运算        → 运算移到常量侧
□ 隐式类型转换（字符串列传数字）  → 传同类型（注意：数字列传字符串不会失效）
□ 字符集/排序规则不匹配（JOIN）  → 统一 COLLATE，被驱动表会全表
□ LIKE '%x' / '%x%'             → 改用全文索引或外部检索
□ OR 连接不同列                  → 拆 UNION ALL
□ != / NOT IN（结果集占比大）    → 优化器弃索引是对的，改需求（加必填过滤）
□ 范围列之后的列                 → 等值列提到范围列前
□ ORDER BY 字段/方向不在索引中   → 8.0 可用降序索引
□ 统计信息过期 / 数据倾斜        → ANALYZE + 直方图
□ SELECT * 破坏覆盖索引          → 只查需要的列
□ FORCE INDEX                   → 只在确认误判 + 有监控时短期用（有失效条件与移除时间）

四、索引变更（线上）

① 检查长事务：SELECT * FROM information_schema.innodb_trx WHERE TIMEDIFF(NOW(),trx_started)>'00:00:10';
② 检查磁盘 > 表大小 × 1.5、主从延迟正常
③ 小表低峰 → Online DDL；大表/核心表 → gh-ost
④ 盯 Threads_running、主从延迟、慢日志、错误率
⑤ 回滚：gh-ost 中断即可；Online DDL 需要再删一次（也是 DDL）

gh-ost --alter="ADD INDEX idx_x (a,b)" --database=app --table=t \
  --max-load=Threads_running=50 --critical-load=Threads_running=100 --execute

权限受限（应用账号无 INDEX/ALTER）：索引内联在建表 DDL 的 KEY 里，DDL 走独立运维通道。

-- ✅ 不需要 INDEX 权限            -- ❌ 需要 INDEX 权限（1142）
CREATE TABLE t (..., KEY idx_x (a));   CREATE INDEX idx_x ON t (a);

五、并发定量

N = X × R      （并发度 = 吞吐 × 响应时间）

压测要出三条曲线（并发度 × QPS / P50 / P99），拐点 = 吞吐边际收益趋零 + P99 陡升处。
连接池、信号量、限流都按拐点设，不是按机器核数。

db_gate = Semaphore(120)                 # 背压闸门
async with asyncio.timeout(0.5):         # 排队也要有上限
    async with db_gate: ...

⚠️ async 不会增大数据库容量，pool_size 算法不变。过载会形成正反馈：
并发↑ → 耗时↑ → 持锁↑ → 冲突↑ → 耗时↑。限流是第一道防线。

六、锁方案选型

冲突率	方案
< 5%	条件更新（首选：`UPDATE ... WHERE stock>=1`，原子，无需先查）
5~20%	乐观锁 version CAS + 有限重试
20~50%	悲观锁，或先限流再乐观
> 50%	热点：子行拆分 → Redis 预扣+异步落库 → 串行队列

❌ SELECT 判断 → UPDATE        （有并发窗口，会超卖）
✅ UPDATE ... WHERE 约束条件    （当前读 + 原子写）
✅ version CAS + 影响行数判定 + 事务外退避 + 次数上限

七、死锁四步翻译

SHOW ENGINE INNODB STATUS\G   -- LATEST DETECTED DEADLOCK
SET GLOBAL innodb_print_all_deadlocks = ON;   -- 否则只保留最近一次

① 两个事务各自的 SQL → ② HOLDS / WAITING 画等待环 → ③ 看 index 与是否 gap lock → ④ 回代码找交叉顺序

预防四招（按性价比）：统一加锁顺序（排序后更新）→ 缩短事务（外部调用移出事务）→ 缩小锁范围 → 降到 RC 减少间隙锁。

重试纪律：整事务重试 + 指数退避抖动 + 上限 2~3 次 + 只对 1213 重试（1205 说明过载，重试会加剧）。

八、削峰与收口

幂等三件套：唯一键（uk_biz，1062 即已处理）> 状态机（WHERE status='PENDING'）> 去重表
对账：      基准（DB 流水为准）+ 频率（资产类分钟级）+ 处置（差异告警并切回同步）

Redis 预扣三件套：Lua 原子扣减 + MQ 异步落库（幂等消费）+ 对账与宕机恢复。

九、每次改动都要留下

SQL / 索引定义 / 优化前（type、rows、耗时、写入TPS）/ 优化后同四项 / 副作用 / 回滚方案

没有前后数据，就等于没有做优化。

进入 keel 阅读