4. 索引创建与维护：CREATE INDEX、ALTER INDEX、DROP INDEX
索引就像书的目录——建得好，翻书飞快；建得烂，翻书比没目录还慢。我自己这些年折腾过的数据库调优里，索引操作占了起码一半的活儿。别小看这三个命令：CREATE INDEX、ALTER INDEX、DROP INDEX。用对了，存储成本可控，查询效率翻倍；用错了，硬盘报警，DBA 半夜被叫醒。
4.1 CREATE INDEX——从无到有，讲究策略
创建索引不是什么难事，难的是该不该建、建什么类型、用什么字段。我见过最极端的情况：一张只有 10 万行的小表，愣是建了 12 个索引，插入一条记录要等 3 秒。你想想看，索引不是越多越好。
核心原则：索引的创建要基于查询模式，不是基于“感觉”。每多一个索引，写入性能就降一分。
最基础的语法长这样：
-- 单列索引
CREATE INDEX idx_user_email ON users(email);
-- 复合索引（最左前缀原则）
CREATE INDEX idx_order_user_date ON orders(user_id, order_date);
-- 唯一索引
CREATE UNIQUE INDEX idx_employee_no ON employees(employee_no);
-- 部分索引（PostgreSQL 专享）
CREATE INDEX idx_active_orders ON orders(order_id) WHERE status = 'active';这里面我特别想提部分索引。之前做电商项目，订单表几千万行，绝大多数已关单。我就只在 status = 'active' 的行上建索引，结果索引体积只有全表的 1/20。查询活跃订单几乎秒出，写入性能也没受影响。这个经验后来被我用到多张历史表上——省硬盘省得很爽。
| 索引类型 | 适用场景 | 存储成本 |
|---|---|---|
| 单列索引 | 等值查询、范围查询（单条件） | 低 |
| 复合索引 | 多条件组合查询、排序 | 中等（取决于列数） |
| 唯一索引 | 保证列唯一性、加速等值查询 | 中等 |
| 部分索引 | 只对部分行建立索引 | 低（约等于筛选后行数的索） |
注意：复合索引的字段顺序非常关键。WHERE user_id = ? AND order_date > ? 这种查询，要用 (user_id, order_date) 而非 (order_date, user_id)。我曾接手过一个系统，把高频查询的复合索引字段顺序搞反了，索引完全没用上，全表扫描。调换顺序后，查询快了 50 倍。
4.2 ALTER INDEX——温柔地改，别弄崩系统
索引创建之后不是一劳永逸。业务变了，查询变了，索引也要跟着变。ALTER INDEX 能做的事比很多人想象的多。
最常用的操作：
- 重命名索引：规范命名，方便维护。
- 重建索引：整理碎片，刷新统计信息。
- 设置填充因子：控制页填充率，减少页分裂。
- 禁用/启用索引：大批量导入时临时关掉。
-- 重命名（PostgreSQL / SQL Server）
ALTER INDEX idx_old_name RENAME TO idx_new_name;
-- 重建索引（SQL Server）
ALTER INDEX idx_order_date ON orders REBUILD;
-- PostgreSQL 重建
REINDEX INDEX idx_order_date;
-- 设置填充因子（SQL Server）
ALTER INDEX idx_product_code ON products REBUILD WITH (FILLFACTOR = 80);填充因子这件事，很多人忽略。默认 0（满页写入），对于随机插入的表，会导致大量页分裂。我个人习惯给高频插入表设 FILLFACTOR = 70~80，留一点空间给新行。代价是索引稍微变大一点，但页分裂少了，维护成本直降。
小技巧：重建索引时可以同时更新统计信息。SQL Server 的 REBUILD 默认就会更新。PostgreSQL 的 REINDEX 不会自动收集统计，要记得单独 ANALYZE。
我曾经做过一个生产案例：某张表的索引碎片率达到 60%，查询响应时间从 20ms 飙到 800ms。在线重建索引后，回到 30ms。但要注意——重建索引是锁表操作。SQL Server 企业版支持 ONLINE = ON，但会占更多资源。我建议在业务低峰期做，或者用 REORGANIZE（轻量碎片整理）代替。
4.3 DROP INDEX——删索引，比建索引更需要三思
删索引看起来简单：DROP INDEX idx_name ON table_name;。但删之前，必须确认一件事——这个索引现在还有用吗？
我见过太多人：看着服务器硬盘报警，二话不说把几个大索引删了。结果第二天核心查询全表扫描，把 CPU 打满。删索引前，至少要做这几步：
- 检查最近一个月的查询计划，看索引是否被使用。
- 检查索引是否为唯一约束或主键的支撑索引——删了会报错。
- 看索引大小，估算释放的空间到底值不值。
- 先在测试环境模拟删除，压测查询性能。
-- 检查索引使用情况（ PostgreSQL ）
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
-- 删除索引（通用）
DROP INDEX IF EXISTS idx_unused ON legacy_table;警告：别在生产库上直接 DROP INDEX 然后等结果。我建议采用软删除策略：先把索引重命名（加前缀 _unused），观察一个月。如果没有报错，再删掉。给自己留条后路。
关于存储成本：一个 500MB 的索引，删掉能立刻释放空间。但如果你使用的是 InnoDB（MySQL），删索引释放的空间会归还给表空间，但文件大小不会缩小——需要 OPTIMIZE TABLE 才能真正回收磁盘。PostgreSQL 则可以通过 VACUUM FULL 回收，但同样会锁表。我这里建议：大索引删除后，如果表允许，做一次批量收缩。
4.4 索引维护的三把刀
不管建、改、删，日常维护才是降低长期存储成本的关键。我把经验总结成三件事：
| 维护操作 | 周期建议 | 效果 |
|---|---|---|
| 索引碎片整理 | 周/月（取决于写入频率） | 减少 I/O，提升扫描效率 |
| 未使用索引清理 | 每月 | 释放存储空间，降低写入负担 |
| 统计信息更新 | 每周或大变更后 | 优化器能选对索引 |
说实话，很多 DBA 只关注查询优化，忽略索引维护。我见过一个极端案例：某个核心表的碎片率达到 85%，索引大小是实际数据的 3 倍。重建后，索引缩小了 60%，查询直接快了一个数量级。
-- 查看索引碎片（SQL Server）
SELECT i.name, ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('orders'), NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30;嗯，这里要提一个我踩过的坑：在线重建索引时，不要同时做大量修改操作。有一次我边在线重建边跑批量更新，结果重建超时回滚，事务日志差点撑爆硬盘。从那以后，不管什么在线操作，我都把并发控制好。
4.5 知识体系：索引创建与维护的核心流程
整个索引生命周期其实就三个动作：创建→维护→删除。我把它们画在一张图里，你可以照着这条思路走：
这张图浓缩了我的日常操作流程。从创建前的分析，到长期维护监控，再到安全删除——每一步都有对应的 SQL 和策略。记住：索引是工具，不是装饰。维护工作不比建索引少，但回报也在那里摆着。