SQL优化 之 索引优化

索引是数据库性能优化的关键手段之一，合理的索引设计可以显著提高查询效率。以下是索引优化的核心要点：

索引基础优化

1. 选择合适的列建立索引

   • 高选择性的列（区分度高）

   • 常用于WHERE、JOIN、ORDER BY、GROUP BY的列

   • 外键列通常需要索引

2. 避免过度索引

   • 每个索引都会占用存储空间并影响写性能

   • 监控索引使用率，删除冗余索引

3. 复合索引设计原则

   • 遵循最左前缀原则

   • 将选择性高的列放在前面

   • 考虑查询的列顺序和排序方向

高级索引策略

1. 覆盖索引优化

   -- 如果索引包含所有查询字段，可以避免回表
   CREATE INDEX idx_cover ON users(name, age);
   SELECT name, age FROM users WHERE name = 'John';

2. 索引条件下推(ICP)

   • MySQL 5.6+特性，能在存储引擎层提前过滤数据

3. 索引合并优化

   • 当WHERE条件中有多个单列索引时，MySQL可能合并使用

4. 函数索引

   -- MySQL 8.0+支持函数索引
   CREATE INDEX idx_func ON users(UPPER(name));

常见索引失效场景

1. 隐式类型转换

   -- 假设user_id是字符串类型，但用数字查询
   SELECT * FROM users WHERE user_id = 123; -- 索引失效

2. 使用函数或运算

   SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 索引失效

3. 前导模糊查询

   SELECT * FROM users WHERE name LIKE '%ohn'; -- 索引失效

4. OR条件不当使用

   -- 当OR两边的列只有一边有索引时，索引可能失效
   SELECT * FROM users WHERE name = 'John' OR age = 30;

索引优化实践

1. 使用EXPLAIN分析

   • 检查type列：至少达到range级别

   • 检查key列：确认使用了预期索引

   • 检查Extra列：避免Using filesort、Using temporary

2. 索引选择性计算

   -- 选择性 = 不重复的索引值数量 / 表记录总数
   SELECT COUNT(DISTINCT name)/COUNT(*) FROM users;

3. 定期维护索引

   • 重建碎片化严重的索引

   • 更新统计信息(ANALYZE TABLE)

4. 特殊场景优化

   • 大数据量分页：使用延迟关联

   SELECT * FROM users INNER JOIN (
     SELECT id FROM users ORDER BY name LIMIT 100000, 10
   ) AS t USING(id);

合理使用索引可以提升查询性能几个数量级，但需要结合实际查询模式和数据特点进行设计，并持续监控和调整。