⭐ 题目日期：

小红书 - 2024/11/11

📝 题解：

在数据库设计和优化中，合理创建索引是提升查询性能的关键手段，但需结合具体场景权衡利弊。以下是考虑建立索引的典型场景及决策依据：

---

一、建议创建索引的场景

1. 高频查询的过滤条件字段

• WHERE子句中的字段：如用户表的 user_id、订单表的 order_date。
• 示例：

SELECT * FROM orders WHERE user_id = 1001;  -- 为user_id建索引

2. 高频排序或分组的字段

• ORDER BY / GROUP BY 字段：如商品表的 price、日志表的 create_time。
• 示例：

SELECT category, COUNT(*) FROM products GROUP BY category;  -- 为category建索引

3. 多表连接的关联字段

• 外键字段：如订单表的 user_id（关联用户表主键）。
• 示例：

SELECT * FROM orders 
JOIN users ON orders.user_id = users.id;  -- 为orders.user_id建索引

4. 高基数字段

• 字段值唯一或接近唯一：如身份证号、手机号、邮箱。
• 示例：

SELECT * FROM employees WHERE employee_code = 'E1001';  -- employee_code唯一

5. 覆盖索引（Covering Index）

• 索引包含查询所需全部字段：避免回表（TABLE ACCESS BY INDEX ROWID）。
• 示例：

CREATE INDEX idx_user_info ON users(name, age, city);
SELECT name, age FROM users WHERE city = 'Beijing';  -- 直接使用索引

6. 复合查询条件

• 多字段组合查询：如同时筛选 status 和 create_time。
• 示例：

SELECT * FROM articles 
WHERE status = 'published' AND create_time > '2023-01-01';  -- 复合索引(status, create_time)

---

二、不建议创建索引的场景

1. 低基数字段

• 字段值重复率高：如性别（男/女）、状态标记（0/1）。
• 示例：

SELECT * FROM users WHERE gender = 'male';  -- 性别索引可能效果差

2. 写多读少的表

• 频繁写入的表：索引维护成本高，影响插入/更新性能。
• 示例： 日志表每秒插入千条数据，索引可能拖慢写入速度。

3. 小型表

• 数据量小（如 < 1000行）：全表扫描更快，索引无意义。
• 示例： 系统配置表，数据量固定且极小。

4. TEXT/BLOB大字段

• 长文本或二进制字段：索引体积大，效率低。
• 替代方案：使用全文检索引擎（如Elasticsearch）。

5. 频繁更新的字段

• 字段值频繁变更：索引维护成本高。
• 示例： 计数器字段（如点赞数），每次更新需调整索引。

---

三、索引优化技巧

1. 复合索引顺序

• 最左前缀原则：将高频等值查询字段放在复合索引左侧。

-- 复合索引顺序：country + city
CREATE INDEX idx_location ON users(country, city);
-- 有效查询：WHERE country='China' AND city='Beijing'
-- 无效查询：WHERE city='Beijing'（无法使用索引）

2. 避免冗余索引

• 删除重复或低效索引：如 (a, b) 和 (a) 同时存在，后者冗余。

3. 监控索引使用率

• 通过执行计划分析：

EXPLAIN PLAN FOR SELECT * FROM users WHERE email = 'user@example.com';

• 数据库统计信息： 使用 pg_stat_user_indexes（PostgreSQL）或 sys.dm_db_index_usage_stats（SQL Server）监控索引使用频率。

4. 定期维护索引

• 重建/重新组织索引：解决碎片化问题。

-- PostgreSQL
REINDEX INDEX idx_orders_user_id;
-- SQL Server
ALTER INDEX idx_orders_user_id ON orders REORGANIZE;

---

四、决策流程图

是否需要优化查询性能？ → 是 → 分析查询条件、排序、连接字段
                            ↓
                字段是否高频出现在WHERE/JOIN/ORDER BY？ → 是 → 检查字段基数
                            ↓                                  ↓
                数据量是否足够大？ → 是 → 是否存在写频繁问题？ → 否 → 创建索引
                            ↓              ↓
                           否             是 → 权衡读写性能，考虑延迟维护

---

五、总结

创建索引的核心目标是 以可控的存储和写入开销，换取显著的查询性能提升。需结合业务场景、数据分布和访问模式综合决策，避免盲目建索引导致资源浪费。通过持续监控和调优，确保索引策略始终适配实际需求。