当前位置：首页 > news >正文

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

news 2025/7/3 16:38:54

在 MySQL 中建立索引是优化查询性能的关键操作，但若使用不当可能导致存储开销增加、写入效率降低甚至性能反降。以下是建索引时需要重点注意的事项及对应分析：

选择高选择性列
- 原理：选择性 = 不同值数量 / 总行数，值越接近1（如主键、唯一ID）越适合建索引。
- 反例：对性别（男/女）建索引，选择性仅0.5，优化效果极低，不如全表扫描。
- 建议：优先为WHERE、JOIN、ORDER BY中频繁出现的列建索引。
避免过度索引
- 代价：每个索引需占用存储空间（B+树结构），且写入（INSERT/UPDATE/DELETE）时需额外维护索引。
- 案例：一张10万行、10个索引的表，写入速度可能比无索引表慢3倍以上。
- 工具：通过SHOW INDEX FROM 表名监控索引使用频率，删除长期未使用的索引。
复合索引的列顺序
- 原则：遵循“最左前缀”规则，将选择性高、过滤性强的列放在左侧。
- 示例：索引(a, b, c)可优化a=1、a=1 AND b=2、a=1 AND b=2 AND c=3，但无法优化b=2或c=3单独查询。
- 反例：将(user_id, create_time)改为(create_time, user_id)后，按时间范围查询的效率可能大幅下降。

普通索引 vs 唯一索引
- 普通索引：允许重复值，适用于频繁更新的列。
- 唯一索引：强制唯一性，适合主键或业务唯一键（如手机号），但写入时需检查重复。
前缀索引
- 适用场景：对长字符串（如URL、邮箱）建索引时，可截取前N个字符。
- 语法：ALTER TABLE 表名 ADD INDEX idx_name (column_name(10))。
- 限制：无法用于ORDER BY或GROUP BY，需权衡选择性。
全文索引（FULLTEXT）
- 适用场景：对文本内容（如文章、商品描述）进行模糊搜索。
- 注意：仅支持MyISAM（5.6前）和InnoDB（5.6+），且默认不支持中文分词（需使用ngram插件）。
空间索引（SPATIAL）
- 适用场景：地理空间数据（如Point、Polygon）的查询。
- 限制：仅支持MyISAM（5.7前）和InnoDB（5.7+），且列必须为NOT NULL。

避免索引失效
- 常见陷阱：
  - 对索引列使用函数（如WHERE YEAR(create_time) = 2023）或隐式转换（如字符串与数字比较）。
  - 使用NOT IN、!=、<>、OR（除非所有条件列均有索引）。
  - 查询条件范围过大（如age > 80且数据分布不均）。
- 解决方案：改用等值查询、拆分OR为UNION ALL、调整查询逻辑。
覆盖索引（Covering Index）
- 原理：索引包含查询所需的所有列（如SELECT a, b FROM t WHERE a=1，索引(a, b)），避免回表。
- 优势：减少I/O操作，提升查询速度。
索引下推（ICP）
- 适用场景：MySQL 5.6+对复合索引的优化，将WHERE条件过滤下推到存储引擎层。
- 示例：SELECT * FROM t WHERE a=1 AND b>10，索引(a, b)下可先过滤a=1再检查b>10。

外键与索引
- 建议：对外键列自动建索引（InnoDB默认行为），避免JOIN时全表扫描。
自增主键 vs 业务主键
- 自增主键：适合InnoDB（聚簇索引），写入性能高。
- 业务主键：如UUID，可能导致索引碎片化，需定期OPTIMIZE TABLE。
索引监控与维护
- 工具：
  - EXPLAIN：分析查询是否使用索引。
  - SHOW STATUS LIKE 'Handler_read%'：监控索引读取次数。
- 定期任务：对大表重建索引（ALTER TABLE ... ENGINE=InnoDB）。