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

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

一、索引创建前的评估

1. 选择高选择性字段

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

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

2. 分析查询模式

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

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

二、索引创建的核心原则

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

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

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

2. 避免冗余索引    

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

3. 控制索引数量

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

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

三、特定场景优化策略

1. 前缀索引节约空间

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

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

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

2. 覆盖索引避免回表

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

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

3. 排序/分组优化

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

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

四、语法与使用陷阱

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

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

2. 隐式类型转换陷阱

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

3. OR 条件索引失效

1. 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. 监控索引使用率

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

2. 定期重建碎片化索引

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

3. 命名规范与文档化

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

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

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

注意：

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

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

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

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

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