Java 后端 / 系统课时
设计高效索引
设计高效索引
本节解决查询速度慢时如何设计合适索引的问题。通过分析查询模式、过滤条件和数据分布,可以建立有效索引,减少无效数据扫描。
学习目标
- 理解索引设计与查询模式之间的关系
- 掌握单列索引、联合索引的设计方法
- 避免常见索引使用误区
从查询模式设计索引
索引不是越多越好,它需要服务于实际查询。设计索引前,应先确认业务中高频执行的 SQL,包括过滤条件、排序字段和关联字段。
例如经常按照用户邮箱查询用户信息:
sql
SELECT id, username
FROM users
WHERE email = 'user@example.com';可以为 email 建立索引:
sql
CREATE INDEX idx_users_email ON users(email);预期结果
text
CREATE INDEX创建后,数据库可以通过索引快速定位满足条件的数据。
理解联合索引设计
当查询包含多个条件时,可以使用联合索引。例如订单查询经常按照用户和创建时间筛选:
sql
CREATE INDEX idx_order_user_time
ON orders(user_id, created_at);预期结果
text
CREATE INDEX联合索引遵循最左匹配原则。以上索引可以支持 user_id 查询,也可以支持 user_id + created_at 查询,但通常不能直接支持只按 created_at 查询。
避免索引设计误区
索引设计需要平衡查询性能和写入成本。每增加一个索引,数据库在插入、更新和删除数据时都需要维护额外结构。
常见问题包括:
- 为低选择性字段建立索引,例如只有少量不同值的状态字段
- 对查询条件使用函数导致索引无法匹配,例如对索引列执行转换
- 创建大量重复或很少使用的索引
可以通过执行计划验证索引是否真正生效,而不是仅依赖索引是否存在。
验证索引效果
创建索引后,应重新查看执行计划,确认扫描方式是否变化。
sql
EXPLAIN ANALYZE
SELECT id, total_amount
FROM orders
WHERE user_id = 1001
ORDER BY created_at DESC
LIMIT 20;预期结果
text
Index Scan using idx_order_user_time on orders
(actual rows=20)如果仍然出现全表扫描,需要继续检查数据量、统计信息、查询条件和索引顺序。
高效索引设计的核心不是覆盖所有字段,而是在有限维护成本下,让高频查询能够快速定位目标数据。
按自己的节奏,确认真正理解。