mysql创建索引有哪些注意点？

在 MySQL 中创建索引是优化查询性能的关键手段，但不当使用反而会降低系统效率。以下是创建索引时需要注意的核心要点及实践建议：

一、索引创建前的评估

1. 选择高选择性字段

2. 优先考虑：区分度高（不同值多）的列（如 user_id、phone），避免低区分度列（如性别、状态标志）。

3. SELECT COUNT(DISTINCT column)/COUNT(*) FROM table;
   -- 结果 > 0.2 通常适合建索引
   

4. 分析查询模式

5. 在频繁用于 WHERE、JOIN、ORDER BY、GROUP BY 的列上创建索引。

6. 避免为极少被查询的列建索引。

二、索引创建的核心原则

1. 联合索引的最左前缀原则

2. 索引 (a, b, c) 生效场景：

3. WHERE a=? AND b=? AND c=?  -- 完全匹配 
   WHERE a=? AND b=?          -- 使用前两列  
   WHERE a=?                  -- 使用最左列  
   WHERE b=? AND c=?          -- 不生效（跳过a）  
   WHERE a=? AND c=?          -- 仅a生效（c无法利用）
   

4. 避免冗余索引

5. 例如已存在 (a, b)，再创建 (a) 是冗余的（最左前缀可覆盖）。

6. 控制索引数量

7. 写代价：每次 INSERT/UPDATE/DELETE 需更新所有相关索引，索引过多会显著降低写性能。

8. 建议：单表索引不超过 5~8 个。

三、特定场景优化策略

1. 前缀索引节约空间

2. 对长文本（TEXT/VARCHAR(255)）使用前缀索引：

3. CREATE INDEX idx_name ON table(column(10)); -- 取前10字
   

4. 平衡点：保证前缀长度下的选择性接近完整列（避免重复值过多）。

5. 覆盖索引避免回表

6. 若查询只需索引字段，直接使用索引返回数据（无需查主表）：

7. SELECT name, age FROM users WHERE name='Alice'; -- 覆盖索引生效

8. 排序/分组优化

9. ORDER BY 或 GROUP BY 字段与索引顺序一致可避免 filesort

10. CREATE INDEX idx_age_name ON users(age, name); SELECT * FROM users ORDER BY age, name;  -- 直接利用索引排序

四、语法与使用陷阱

1. 表达式/函数导致索引失效

2. SELECT * FROM users WHERE YEAR(create_time)=2023; -- 索引失效 -- 改为范围查询： SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

3. 隐式类型转换陷阱

4. -- phone 是 VARCHAR 但查询用数字： SELECT * FROM users WHERE phone = 13800138000; --触发隐式转换，索引失效 -- 改为字符串匹配： SELECT * FROM users WHERE phone = '13800138000';

5. OR 条件索引失效

6. SELECT * FROM users WHERE a=1 OR b=2; -- 单列索引可能失效 -- 解决方案： CREATE INDEX idx_a_b ON users(a, b);  -- 联合索引（需结合查询优化） -- 或拆分查询： SELECT * FROM users WHERE a=1 UNION SELECT * FROM users WHERE b=2;

五、索引维护与管理

1. 监控索引使用率

2. -- 查看未使用的索引（Schema为数据库名） SELECT * FROM sys.schema_unused_indexes WHERE object_schema='your_db';

3. 定期重建碎片化索引

4. ALTER TABLE table_name ENGINE=InnoDB; -- 重建表（包括索引） ANALYZE TABLE table_name;             -- 更新统计信息

5. 命名规范与文档化

6. 使用可读性强的命名（如 idx_user_id、uk_email）。

7. 记录索引创建原因（如 优化订单时间范围查询）。

六、应避免创建索引的场景

场景

原因

小表（<1000行）

全表扫描可能比索引更快

写频繁 & 读极少

索引维护成本 > 查询收益

长文本（不设前缀）

索引体积过大（除非使用全文索引）

频繁更新的字段

更新后需重排序B+树，代价高

注意：

1. 不要迷信规则，也要以实际情况为准

2. 用 EXPLAIN 验证索引是否生效（看 key 字段）。

3. 测试 INSERT/UPDATE 性能是否可接受。

4. 避免“凭感觉创建索引”——用数据驱动决策（慢查询日志 + 性能监控）。

5. 超大表要注意创建索引需要考虑对服务器io消耗，别在业务繁忙时候操作