Skip to content

1398281322-a11y/java-backend-guardrails / skills/java-backend-guardrails/mysql-index

mysql-index

Verified

Use when diagnosing slow SQL, creating or changing secondary indexes, EXPLAIN, 最左前缀, covering index, or implicit conversion. Do not use for sharding or table status-machine design.

MySQL 索引

When to Invoke

慢查询、EXPLAIN、加索引、分页深翻、订单列表按用户+时间查。

When NOT

分库分表、选分片键 → sharding。建表字段/唯一键/归档 → table-design。

风险(面试考点)

InnoDB 二级索引是 B+ 树。联合索引最左前缀:(user_id, created_at) 能用 user_id,不能单独用 created_at。回表:二级索引找到主键再回聚簇。覆盖索引:SELECT 列都在索引里可避免回表。隐式转换(varchar 列用数字比)会让索引失效。函数包列(DATE(created_at)=)同样失效。

面试常说「RR 靠间隙锁防幻读」——那是锁,不是索引本身;本 skill 不展开。当前读/间隙锁见业务里 SELECT FOR UPDATE 时再单独处理。

适用场景

  • WHERE user_id=? ORDER BY created_at DESC LIMIT 20
  • 核销列表:merchant_id + status + created_at
  • 回调查重:应靠 UNIQUE,不是普通二级索引

方案选型(轻量优先)

场景默认索引
等值 + 时间排序(user_id, created_at)
等值 + 过滤 + 时间(merchant_id, status, created_at)
唯一业务键UNIQUE,不当成普通 INDEX
低区分度列(status 单独)不要单建;放到联合索引第二列
深分页延迟关联 / 记上次 ID,不 LIMIT 100000,20

先 EXPLAIN,再加索引。禁止凭感觉给每个查询字段都建单列索引。

默认方案

EXPLAIN SELECT id, order_no, created_at
FROM t_order
WHERE user_id = ? AND created_at >= ?
ORDER BY created_at DESC
LIMIT 20;
-- 期望:type=range, key=idx_user_created, Extra 尽量 Using index 或至少非 filesort 全表
ALTER TABLE t_order ADD KEY idx_user_created (user_id, created_at);

分页:

-- 用上一页最小 id,不要大 offset
WHERE user_id = ? AND id < #{lastId}
ORDER BY id DESC LIMIT 20;

隐式转换:

-- 列是 VARCHAR 单号
WHERE order_no = 123        -- 可能失效
WHERE order_no = '123'      -- 正确

反例

错误:WHERE DATE(created_at) = '2026-09-01'。 正确:created_at >= '2026-09-01' AND created_at < '2026-09-02'。

错误:OR 两边不同列导致全表;或 LIKE '%abc'。 正确:改写 UNION;搜索走专门检索而不是硬上索引。

错误:一张表 12 个单列索引,写入变慢。 正确:按查询形状建联合索引,定期用 unused index 思路删。

错误:把「分片键」写进索引优化建议。 正确:分片是另一套问题。

验证

  • EXPLAIN 走预期索引,rows 数量级合理。
  • 慢日志里该 SQL 消失或耗时下降。
  • 写入 QPS 未因索引过多明显变差。

评审清单

  • 联合索引列顺序匹配 WHERE/ORDER
  • 无函数包列、无隐式转换
  • 深分页不是巨大 OFFSET
  • 没有用分库分表冒充索引优化

deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall