mysql创建索引时应该选择哪些字段_mysql索引选择原则

where高频字段优先建索引，联合查询用复合索引，避免低选择性字段单独建索引，order by/group by字段需匹配索引顺序，join关联字段必须有索引，建前必查执行计划。

WHERE 条件中频繁出现的字段必须优先考虑

索引最直接的作用是加速查询，而查询加速效果最明显的，就是 WHERE 子句里反复用到的字段。如果一个字段在多数查询中都作为过滤条件（比如 user_id、status、created_at），它大概率值得建索引。

注意：单列高频 ≠ 一定单独建索引。例如 WHERE status = ? AND created_at > ?，这时更合适建联合索引 (status, created_at)，而非两个单列索引——mysql 通常只能用上其中一个。

• 避免为低选择性字段单独建索引，比如 gender（只有 ‘M’/’F’）或 is_deleted（大量为 0）；B+ 树索引对这类字段加速有限，还增加写开销
• 字符串字段建索引要谨慎：长文本如 TEXT 类型不能全字段索引，需指定前缀长度，例如 INDEX idx_title (title(100))
• 时间字段若常用于范围查询（BETWEEN、>=），放在联合索引的右侧更合理，因为 MySQL 索引最左匹配原则下，范围查询之后的列无法被利用

ORDER BY 和 GROUP BY 字段要纳入索引设计

排序和分组本身不走索引，但如果能被索引“覆盖”，就能避免额外的 filesort 或 temporary table。关键看是否与 WHERE 条件字段组合成有序结构。

例如查询 select * FROM orders WHERE user_id = 123 ORDER BY created_at DESC，索引 (user_id, created_at) 可同时满足过滤和排序；但如果写成 (created_at, user_id)，就无法高效过滤 user_id，排序也未必生效。

• ORDER BY 字段顺序必须和索引列顺序一致，且方向（ASC/DESC）尽量统一；MySQL 8.0+ 支持混合方向索引，但老版本只认全部 ASC 或全部 DESC
• GROUP BY 同理，理想情况是索引能直接提供分组所需的有序性，否则会触发临时表
• 如果 SELECT 中只查索引列（含主键），还能触发“覆盖索引”，避免回表——这是性能加成点，不是建索引的首要理由

外键字段和 JOIN 关联字段别漏掉

表关联时，被驱动表（即 JOIN 右侧或 LEFT JOIN 的右表）的关联字段如果没有索引，很容易触发全表扫描。执行计划里看到 type: ALL 或 Extra: using join buffer 就是明显信号。

例如 SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id，orders.user_id 必须有索引；反过来，users.id 是主键，天然有索引，无需额外处理。

• 外键约束本身不要求索引，但 InnoDB 强烈建议手动添加，否则 DELETE/UPDATE CASCADE 性能极差
• 多表 JOIN 时，优先确保每个被驱动表的 ON 条件字段都有索引，而不是堆砌所有关联字段到一个联合索引里
• 注意索引区分度：如果 user_id 在 orders 表中重复极高（比如某用户下了 10 万单），单靠它做索引效率仍受限，需结合其他字段优化

别盲目建索引，先看执行计划和慢日志

没有实际查询负载支撑的索引是无效索引。很多团队凭经验“觉得这里该加”，结果索引从不被用到，反而拖慢 INSERT/UPDATE/DELETE 速度，并占用磁盘和内存。

验证方式很简单：EXPLAIN format=TREE（MySQL 8.0+）或 EXPLAIN 查看 key、rows、Extra 字段；配合慢查询日志定位真实瓶颈 SQL。

• 新增索引后，务必用 SHOW INDEX FROM table_name 确认是否创建成功，注意 Cardinality 值是否合理（太低说明选择性差）
• 避免冗余索引：已有 (a, b)，再建 (a) 就是浪费；可用 sys.schema_redundant_indexes 视图辅助识别
• 复合索引列数不宜过多（一般 ≤ 3），否则维护成本高、命中率下降；优先保证高频查询路径的最左前缀能覆盖

真正难的不是“哪些字段能建索引”，而是“这个业务查询模式下，索引怎么排列、要不要截断、会不会被优化器忽略”。每次加索引前，至少跑一遍真实参数的 EXPLAIN。