合理设计索引需基于查询模式创建单列或复合索引，遵循最左前缀原则，避免函数导致失效；控制索引数量与长度以减少写开销，利用覆盖索引减少回表；结合事务隔离级别优化锁范围，通过EXPLAIN分析执行计划，精准匹配业务需求，平衡读写性能。
在 MySQL 中合理设计索引是提升事务查询效率的关键。索引能显著加快数据检索速度，但如果设计不当，不仅无法提升性能，还可能增加写入开销、占用更多存储空间，甚至影响事务并发性。以下从实际场景出发，介绍如何科学设计索引以优化事务查询。
理解查询模式，针对性创建索引
索引设计应基于实际的查询语句。分析频繁执行的 SELECT、UPDATE、DELETE 语句中的 WHERE、JOIN、ORDER BY 和 GROUP BY 条件，找出高频过滤字段。
- 对 WHERE 子句中经常使用的列建立单列索引，例如 user_id、status 等。
- 多个字段组合查询时，使用复合索引（联合索引），注意最左前缀原则。例如查询条件为 WHERE user_id = ? AND create_time > ?，应创建 (user_id, create_time) 的复合索引，而不是分别建两个单列索引。
- 避免在索引列上使用函数或表达式，如 WHERE YEAR(create_time) = 2024，会导致索引失效。
控制索引数量与长度，减少维护开销
虽然索引能加速查询，但每个索引都会在 INSERT、UPDATE、DELETE 时增加 B+ 树维护成本，尤其在高并发事务场景下，过多索引会拖慢写入性能。
- 避免为低选择性的列创建索引，如性别、状态码等枚举值较少的字段，除非与其他列组合使用。
- 尽量使用短字段做索引，例如用 INT 而不是 VARCHAR(255)。对于长字符串字段，可考虑前缀索引，如 INDEX(title(10))，但需评估区分度。
- 定期审查冗余或未使用的索引，可通过 performance_schema 或 sys.schema_unused_indexes 视图识别并清理。
利用覆盖索引减少回表操作
覆盖索引是指查询所需的所有字段都包含在索引中，无需回主键索引查找数据行，极大提升查询效率。
- 例如有索引 (user_id, status, create_time)，查询 SELECT user_id, status FROM table WHERE user_id = ? AND status = ? 可直接从索引获取结果。
- 在事务中频繁读取少量字段时，适当扩展复合索引的列顺序，使其能覆盖常见查询，减少 I/O 开销。
- 注意索引列顺序：将筛选性强的列放在前面，如 user_id 放在 status 前面。
结合事务隔离级别与锁机制优化索引
在可重复读（REPEATABLE READ）等隔离级别下，MySQL 使用间隙锁（Gap Lock）防止幻读，合理的索引设计有助于缩小锁定范围。
- 精确匹配的唯一索引查询（如主键或唯一键）通常只加记录锁，不会触发间隙锁，提升并发性。
- 范围查询若缺少合适索引，可能导致全表扫描并加大量锁，阻塞其他事务。确保范围条件字段有索引。
- 使用 EXPLAIN 分析执行计划，确认是否走了预期索引，避免全表扫描。
基本上就这些。关键是根据业务查询特征设计高效复合索引，平衡读写性能，同时配合执行计划持续调优。索引不是越多越好，精准才是王道。
