在mysql中建立索引时需要注意哪些事项？

在mysql中建立索引时需要注意哪些事项？

🧠 一、是否需要创建索引（是否值得加索引）

1. 字段是否参与频繁查询

  • ✅ 适合加索引：出现在 WHERE、JOIN、ORDER BY、GROUP BY 等子句中的字段。

  • ❌ 不建议加索引：仅偶尔查询的字段。

  • 例：

    1
    2
    sql复制编辑SELECT * FROM orders WHERE customer_id = 123;
    -- 应该对 customer_id 建索引

2. 字段的选择性（离散度）是否高

  • 选择性 = 不同值的数量 / 总行数。
  • 高选择性：身份证号、邮箱 等 → 适合建索引。
  • 低选择性：性别（只有 M、F）→ 不适合建单列索引。
  • 例：
    • status 字段只有三个值：不建议单独建索引。
    • 可以作为复合索引的一部分。

3. 字段是否频繁更新

  • 每次更新表中的数据时，MySQL 也要同步更新索引 → 带来性能负担。
  • 对于频繁更新的字段，慎重考虑是否要建索引。

🧱 二、索引的类型和适用场景

1. 普通索引（INDEX）

  • 最基础的索引类型，无唯一性要求。
  • 适用场景：频繁查询，但不要求唯一性的字段。

2. 唯一索引（UNIQUE）

  • 强制字段值唯一，可用于手机号、邮箱等。
  • 插入重复值时会报错。

3. 主键索引（PRIMARY KEY）

  • 主键自动建立唯一索引。
  • 一个表只能有一个主键。
  • 通常用于 id 或 uuid。

4. 复合索引（多列索引）

  • 适用于多字段联合查询的场景。

  • 遵循“最左前缀”原则。

  • 例：

    1
    CREATE INDEX idx_user_name_age ON users(name, age);
    • 可用于：WHERE name = 'Tom'、WHERE name = 'Tom' AND age = 20
    • 不可用于：WHERE age = 20（索引失效）

5. 前缀索引（Prefix Index）

  • 对字符串类型字段，使用前几个字符建立索引（节省空间）。

  • 例：

    1
    CREATE INDEX idx_email_prefix ON users(email(10));

6. 全文索引（FULLTEXT）

  • 适用于长文本字段的模糊匹配（如文章内容、评论）。
  • 只能用于 MyISAM 或 InnoDB（MySQL 5.6+）且字段类型为 CHAR/VARCHAR/TEXT。

📌 三、索引设计注意事项

1. 最左前缀原则

  • 复合索引要从左往右使用，不能跳过字段。

  • 例：

    1
    INDEX(a, b, c) → 支持 (a)、(a, b)、(a, b, c)，但不支持 (b, c)

2. 索引列避免使用函数或计算

  • 索引字段参与计算或函数，会导致索引失效。

  • ❌：

    1
    WHERE YEAR(create_time) = 2023

  • ✅：

    1
    WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'

3. 避免对 NULL 或布尔字段建立单列索引

  • NULL 值无法充分利用索引。
  • 可考虑组合索引，或用其它方式优化。

4. 避免在小表上建索引

  • 表数据量很小时，MySQL 使用全表扫描反而更快。

5. 使用覆盖索引（Covering Index）优化查询

  • 如果索引包含查询所需的所有列，MySQL 不用回表，查询效率高。

  • 例：

    1
    2
    sql复制编辑SELECT name, age FROM users WHERE name = 'Tom';
    -- 如果有索引 (name, age)，这就是覆盖索引

🔧 四、优化与调试工具

1. 使用 EXPLAIN 分析查询是否命中索引

1
EXPLAIN SELECT * FROM users WHERE name = 'Tom';

2. 查看索引信息

1
SHOW INDEX FROM users;

3. 慢查询日志分析慢 SQL

  • 启用 MySQL 慢查询日志，可以找出没有使用索引的慢语句。

✅ 最佳实践总结

场景 是否适合加索引 备注
经常用于 WHERE 查询的字段 ✅ 高选择性优先
用于 JOIN 的连接键 ✅ 建立相同类型索引可提升性能
排序字段（ORDER BY） ✅ 尤其是在 LIMIT 结合 ORDER BY 时
频繁更新的字段 ❌ 更新时索引维护开销大
低选择性字段（如性别） ❌ 可考虑复合索引中包含
小表（几十条数据） ❌ 全表扫描更快
模糊匹配 LIKE ‘%xxx%’ ❌ 普通索引无法使用，可考虑 FULLTEXT