SQL索引是数据库中用于快速查找数据的数据结构，合理的索引设计能让查询效率提升数倍甚至数十倍，而不合理的索引则会导致写入变慢、存储空间浪费等问题。做好索引优化和遵循设计最佳实践，是数据库性能调优的关键环节。
索引设计的核心原则
索引设计需要结合业务查询场景，遵循以下核心原则才能发挥最大价值：
- 覆盖高频查询字段：优先为查询条件、排序字段、分组字段创建索引，避免全表扫描。
- 控制索引数量：单表索引数量不宜过多，一般建议不超过5个，过多索引会影响插入、更新、删除操作的性能。
- 选择区分度高的字段：字段的区分度越高，索引的过滤效果越好，比如用户表的手机号字段区分度远高于性别字段。
- 避免冗余索引：如果已经存在复合索引(a,b)，就不需要再单独创建a字段的索引，前者已经能覆盖后者的查询场景。
复合索引的设计技巧
复合索引是多个字段组合的索引，设计时需要遵循最左前缀原则，即查询条件需要匹配索引的最左字段才能生效。比如创建复合索引idx_user_age_name(age, name)，以下查询可以使用该索引：
- 查询条件包含age字段
- 查询条件同时包含age和name字段
而仅包含name字段的查询无法使用该索引。因此设计复合索引时，要把最常用的查询字段放在最左侧。
以下是创建复合索引的SQL示例：
-- 为用户表创建年龄和姓名的复合索引 CREATE INDEX idx_user_age_name ON user_table (age, name); -- 查询可以使用该索引的场景 SELECT * FROM user_table WHERE age = 25; SELECT * FROM user_table WHERE age = 25 AND name = '张三';
常见索引优化方法
1. 避免索引失效的场景
很多写法会导致索引失效，需要特别注意：
- 对索引字段使用函数或运算，比如
WHERE YEAR(create_time) = 2023会导致create_time索引失效，可改为WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'。 - 查询条件使用不等于、IS NULL、IS NOT NULL，可能导致索引失效。
- 模糊查询以通配符开头，比如
WHERE name LIKE '%三'无法使用name索引，而WHERE name LIKE '张%'可以使用。
2. 定期清理无用索引
随着业务迭代，很多早期创建的索引可能不再被使用，定期分析索引使用情况可以清理冗余索引。MySQL中可以通过查询sys.schema_unused_indexes表查看未被使用的索引：
-- 查看MySQL中未被使用的索引 SELECT * FROM sys.schema_unused_indexes;
3. 合理使用覆盖索引
覆盖索引是指索引包含了查询需要的所有字段，不需要回表查询数据行，能大幅提升查询性能。比如查询只需要返回id和name，而索引已经包含这两个字段，就可以使用覆盖索引：
-- 创建包含id和name的复合索引，查询时不需要回表 CREATE INDEX idx_user_cover ON user_table (age, id, name); -- 该查询会使用覆盖索引，直接从索引中获取数据 SELECT id, name FROM user_table WHERE age = 25;
不同业务场景的索引设计实践
| 业务场景 | 索引设计方案 |
|---|---|
| 用户登录查询，根据手机号查询用户信息 | 为手机号字段创建唯一索引，区分度高且保证数据唯一性 |
| 订单列表查询，根据用户ID和创建时间排序查询 | 创建复合索引(user_id, create_time)，匹配查询条件和排序字段 |
| 商品搜索，根据分类ID和价格区间筛选 | 创建复合索引(category_id, price)，覆盖筛选条件 |
索引维护注意事项
索引不是创建后就一劳永逸，需要定期维护：
- 数据量大的表，创建索引尽量选择业务低峰期，避免影响线上服务。
- 定期分析索引的使用效率，对于区分度低、使用频率低的索引及时删除。
- 对于频繁更新的字段，谨慎创建索引，因为更新字段会同时更新索引，增加写入开销。
索引设计没有通用的最优方案，需要结合具体的查询场景、数据量、写入频率综合判断，通过实际测试验证索引效果才是可靠的方法。