MySQL 有哪些索引，项目如何设计索引？

核心回答

MySQL 的索引要按不同维度分类，再结合实际查询设计。以 MySQL 8.4 的 InnoDB 为例：

• 按约束：主键索引、唯一索引、普通索引。主键唯一且非空，一张表只有一个主键；唯一索引约束键值唯一，普通索引允许重复。可空列上的唯一索引允许多个 NULL，业务要求必填时还要配合 NOT NULL。
• 按列数：单列索引和联合索引。联合索引按定义的列顺序组织，通常从连续的最左前缀构造查找范围。
• 按数据组织：InnoDB 的聚簇索引保存行记录，通常由主键承担；普通二级索引的记录包含索引列和主键值，需要其他列时可再通过主键查找，也就是回表。
• 按结构与用途：InnoDB 常规索引采用 B+ 树；另外有文本检索用的 FULLTEXT、空间数据用的 SPATIAL。其他引擎还可能支持 Hash 索引，不能把它们都当作 InnoDB 常规索引的可选结构。

这些维度可以叠加：一个索引可以同时是“唯一、联合、二级索引”。覆盖索引描述的是某条查询所需数据能否由索引提供，也不是和主键、普通索引互斥的一种建索引类型。

项目设计时，我会按“查询模式与业务约束 → 候选索引 → 执行计划和实际负载验证 → 读写成本取舍”来说明。

以订单查询为例，怎样设计？

下面是一个设计示例：订单表中有 id、order_no、user_id、status、created_at 和金额等字段。主要请求包括按订单号查详情，以及查询某个用户、某种状态下最近的20笔订单。

1. 主键与业务唯一键分工：用较短、稳定的 id 作主键；订单号另建唯一索引，并按业务要求设为非空。订单号索引既用于查详情，也让数据库约束重复订单号，不能只靠应用先查再插。
2. 让联合索引对应主要列表请求：可考虑 (user_id,status,created_at,id)。前两列用于等值过滤，再按创建时间和 id 的同向顺序访问；id 用来区分相同时间的记录，使分页顺序明确。是否能按预期少扫描、减少排序，要看实际执行计划。
3. 检查查询变体：如果另一个高频请求不限制状态，只按用户和时间取订单，中间的 status 会影响后续排序利用。此时应评估增加或改用 (user_id,created_at,id)，而不是认为一个更长的索引天然覆盖所有查询。
4. 控制返回列与覆盖成本：列表只取必要字段；若需要金额等索引外字段，可能回表。是否增加字段来覆盖，要比较减少回表的收益与索引体积、更新频率，不能为了覆盖一个 SELECT * 把整张表塞进索引。

上面的索引列清单表达访问顺序。InnoDB 的二级索引本身也会携带主键列，实际定义时应结合已有索引和主键扩展情况，避免重复设计。

怎样确认索引设计有收益？

先收集主要 SQL、调用频率、表规模、数据分布、过滤条件、排序和返回列，再看 EXPLAIN 中选中的索引、访问方式、扫描行数估算及排序、过滤信息。需要实际执行信息时，再用代表性数据比较执行耗时、扫描量、返回结果和整体接口表现。

验证不能只看单条查询：增加索引后，还应观察写入耗时、索引空间、缓存占用和其他常用查询是否变差。对冗余索引的调整，也要保留唯一约束等业务语义。

不存在“每个条件列各建一个索引”或“区分度最高的一律放第一列”的通用答案。列顺序要服务常用查询前缀；低基数字段在合适的联合索引中仍可能有价值，优化器也可能因为返回行数多而选择扫描。

常见追问

没有显式主键，InnoDB 怎么组织数据？ 会选择第一个所有键列均为非空的唯一索引作聚簇索引；没有合适索引时，再生成隐藏的聚簇行标识。它不是自动给业务增加一个可直接使用的主键约束。

字符串前缀索引和最左前缀一样吗？ 含义不同。前者只索引字符串值的一部分，节省空间但会损失完整值信息；后者讨论联合索引从哪几列开始形成访问前缀。

参考资料