mysql 创建索引注意事项-优快云博客

在 MySQL 中创建索引是提升查询性能的关键手段，但不当的创建会带来资源浪费、写入性能下降甚至优化器选错索引等问题。以下是创建索引时必须注意的核心事项：

明确目标查询 (WHERE, JOIN, ORDER BY, GROUP BY)
- 只为高频、关键、性能瓶颈的查询创建索引。
- 使用 EXPLAIN 分析查询执行计划，确认是否真正需要新索引。
遵循最左前缀匹配原则 (Leftmost Prefixing)
- 对于 组合索引 (col1, col2, col3)：
  - 有效查询：col1、col1 AND col2、col1 AND col2 AND col3。
  - 无效查询：col2、col3、col2 AND col3（无法使用索引）。
- 将区分度高（唯一值多）的列放在组合索引左侧。
优先考虑覆盖索引 (Covering Index)
- 让索引包含查询所需的所有字段（SELECT、WHERE、JOIN 列），避免回表查询。
- 示例：查询 SELECT name, age FROM users WHERE city='北京' → 创建 INDEX (city, name, age)。
控制索引数量，避免冗余
- 单表索引不宜过多（通常 ≤5个）。每个索引占用磁盘空间，降低 INSERT/UPDATE/DELETE 速度。
- 检查是否有功能重叠的索引：
  - 已有 INDEX(a,b)，再建 INDEX(a) 是冗余的（前缀索引可复用）。
  - 用工具如 pt-duplicate-key-checker 检测冗余索引。
选择区分度高的列
- 区分度 = COUNT(DISTINCT col) / COUNT(*)。值越接近 1，索引效果越好（如唯一ID）。
- 避免为区分度极低的列建索引（如性别、状态标志），优化器可能直接忽略索引。

前缀索引优化长文本

对 VARCHAR/TEXT 列，使用前缀索引节省空间：

CREATE INDEX idx_name ON table (column(10)); -- 只索引前10字符

关键：计算合适长度，确保区分度：

SELECT 
  COUNT(DISTINCT LEFT(column, 10)) / COUNT(*) AS selectivity 
FROM table;

为排序/分组优化索引
- ORDER BY 或 GROUP BY 子句的列顺序需与索引一致，且方向相同（全 ASC 或全 DESC）。
- 示例：SELECT ... ORDER BY a DESC, b DESC → 建 INDEX(a DESC, b DESC)。
谨慎处理 NULL 值
- 索引通常不存储 NULL（除非是唯一索引）。查询 WHERE col IS NULL 可能无法使用索引。
- 考虑用默认值（如 '' 或 0）替代 NULL，或使用 FULLTEXT/SPATIAL 索引。
函数和表达式导致索引失效
- 索引无法用于函数计算的列。
  失效示例：WHERE YEAR(create_time) = 2023
  优化方案：WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'
隐式类型转换陷阱
- 列与查询值类型不一致时，索引失效。
  失效示例：user_id 是字符串类型，但查询 WHERE user_id = 100（整数）。

使用 Online DDL (MySQL 5.6+)
- 加 ALGORITHM=INPLACE, LOCK=NONE 减少锁表时间：
```
CREATE INDEX idx_name ON big_table (column) 
  ALGORITHM=INPLACE, LOCK=NONE;
```
- 注意：某些操作仍需锁表（如修改列类型、删除主键）。
避开业务高峰期
- 大表建索引可能耗时分钟级甚至小时级，阻塞写入操作。
监控资源消耗
- 创建过程中监控 CPU、I/O、内存使用，避免拖垮数据库。
优先在从库创建测试
- 在从库创建索引验证效果，确认无误再操作主库。

分析索引使用情况
- 查询 sys.schema_index_statistics 或 INFORMATION_SCHEMA.STATISTICS：
```
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'your_db' AND table_name='your_table';
```
- 删除长期未使用的索引 (index_reads=0)。
定期更新统计信息
- 优化器依赖统计信息选择索引。大表更新后执行：
```
ANALYZE TABLE your_table;
```

注意索引碎片化

频繁更新的表会导致索引碎片，定期优化：

OPTIMIZE TABLE your_table;  -- 重建表及索引
ALTER TABLE your_table ENGINE=InnoDB; -- 仅InnoDB有效