数据库索引策略
索引类型
| 类型 | 最适合 | 支持的操作 | 说明 |
|---|---|---|---|
| B-tree（默认） | 等值、范围、排序 | =、<、>、BETWEEN、LIKE 'foo%' | 适合 90%+ 场景 |
| Hash | 仅等值查询 | = 只支持等值 | 等值更快，不支持范围 |
| GIN | 多值（数组、JSONB、tsvector） | @>、?、@@ 全文搜索 | 索引大、构建慢、查询快 |
| BRIN | 超大顺序写入表 | 自然有序数据的范围查询 | 索引极小，适合时序数据 |
| Partial（部分索引） | 行子集（含 WHERE 条件） | 任意类型，更小更快 | 只索引满足条件的行 |
| 表达式索引 | 函数结果，如 LOWER(email) | 查询必须使用相同表达式 | 表达式必须完全匹配 |
| 覆盖索引（INCLUDE） | 包含额外列 | 索引覆盖扫描 | 完全避免堆查询 |
组合索引列顺序
-- 规则：等值列在前，范围列居中，ORDER BY 列最后
-- 场景：WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY user_id
-- 最优索引：
CREATE INDEX idx_status_created_user ON orders(status, created_at, user_id);
-- 最左前缀原则：
-- 可使用索引：
-- WHERE status = 'active' ✓（前缀匹配）
-- WHERE status = 'active' AND created_at > X ✓（前缀+范围）
-- 无法使用索引：
-- WHERE created_at > '2024-01-01' ✗（跳过第一列）
-- WHERE user_id = 42 ✗（跳过前两列）
-- 覆盖索引（INCLUDE 非过滤列）
CREATE INDEX idx_orders_cover
ON orders(user_id, status)
INCLUDE (amount, created_at);
-- 部分索引（只索引活跃订单）
CREATE INDEX idx_active_orders ON orders(user_id, created_at)
WHERE status = 'active';
-- 表达式索引
CREATE INDEX idx_email_lower ON users(LOWER(email));
-- 查询：WHERE LOWER(email) = '[email protected]'
索引选择性与维护
-- 查找未使用的索引（PostgreSQL）
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexname NOT LIKE '%pkey';
-- 重建索引（不阻塞写入）
REINDEX INDEX CONCURRENTLY idx_orders_user_id;
-- MySQL：检查索引使用情况
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;
-- 何时不加索引：
-- • 小表（全表扫描更快）
-- • 低基数列（boolean、性别等）
-- • 很少出现在 WHERE/JOIN/ORDER BY 中的列
-- • 写多读少的表（每个索引都会降低写入速度）