数据库专题14索引与 EXPLAIN 实验——用证据决定索引而不是凭感觉索引像书的目录能减少扫描行但每次写入都要维护它还会占用磁盘和缓存。本篇在真实博客表中造十万行数据分别对作者列表、已发布时间线和评论查询建立索引用EXPLAIN (ANALYZE, BUFFERS)对比前后执行计划并解释为什么“有索引”不等于“一定走索引”。上一篇练习讲解报表练习要求用窗口函数比较三种排名并处理没有数据的 NULL。多对多表必须先聚合再 JOIN避免行数相乘时间分桶统一使用 UTC 存储、业务时区展示。报表索引要从 WHERE、JOIN、GROUP BY 和 ORDER BY 的实际组合推导。1. 造数据和基线insertintoarticles(author_id,title,content,status,created_at)select1,Python 数据库教程 ||g,repeat(content ,30),casewheng%50thendraftelsepublishedend,now()-(g|| minutes)::intervalfromgenerate_series(1,100000)g;analyzearticles;基线查询explain(analyze,buffers)selectid,title,created_atfromarticleswhereauthor_id1andstatuspublishedorderbycreated_atdesc,iddesclimit20;记录Execution Time、actual rows、shared read/hit和扫描类型。第一次执行可能把数据读进缓存至少重复三次取中位数并在报告中注明环境。2. 复合和部分索引createindexconcurrently idx_articles_author_publishedonarticles(author_id,created_atdesc,iddesc)wherestatuspublished;createindexconcurrently idx_articles_published_timeonarticles(created_atdesc,iddesc)wherestatuspublished;作者列表把等值过滤author_id放在前面时间排序放在后面全站列表不需要 author_id所以单独建立索引。部分索引排除 draft索引更小。不要把两者合并成一个“万能索引”那可能无法同时优化两种访问模式。3. 评论和覆盖索引createindexconcurrently idx_comments_article_visibleoncomments(article_id,created_atasc,idasc)include(user_id)wheredeleted_atisnull;include允许索引覆盖小字段减少回表正文 content 很大不应放进 include。索引创建使用concurrently可减少阻塞但会花费更长时间失败时留下 invalid 索引需要检查pg_indexes后清理。4. 读懂“没走索引”的原因PostgreSQL 可能选择顺序扫描因为表很小、条件选择性低、统计信息过期或随机 I/O 成本较高。不要用set enable_seqscanoff作为优化证明它只适合诊断。执行selectrelname,n_live_tup,last_analyzefrompg_stat_user_tableswhererelnamein(articles,comments);analyzearticles;如果估算行数和实际差异很大提高列统计目标altertablearticlesaltercolumnstatussetstatistics500;analyzearticles;统计目标越高分析成本和系统目录开销越大只对确实倾斜的列调整。5. OFFSET 和游标计划对比explain(analyze,buffers)selectid,titlefromarticleswherestatuspublishedorderbycreated_atdesc,iddesclimit20offset50000;explain(analyze,buffers)selectid,titlefromarticleswherestatuspublishedand(created_at,id)(now()-interval50000 minutes,50000)orderbycreated_atdesc,iddesclimit20;第二条可以从索引定位起点深页延迟不会随页码线性增加。实际游标值应来自上一页不要用时间估算代替真实 id。6. 索引清理和监控selectindexrelname,idx_scan,pg_size_pretty(pg_relation_size(indexrelid))frompg_stat_user_indexeswhererelnamearticlesorderbyidx_scan;长期idx_scan0的索引可能是无用索引但先确认是否刚创建、是否只在低频报表使用。删除前导出执行计划并观察一周生产删除也使用drop index concurrently。验收记录与排错无索引作者列表Seq ScanExecution Time 70ms 复合部分索引Index ScanExecution Time 5ms 深页 OFFSET扫描约50020行游标扫描约20~40行 统计信息过期估算与实际偏差 10 倍ANALYZE 后恢复不同机器的毫秒数会变化重要的是计划和趋势。若索引创建失败检查是否有重复同名索引或长事务阻塞若磁盘空间不足先取消实验数据和无用索引不要直接删除生产表。课后练习为标签文章查询设计索引提交EXPLAIN (ANALYZE, BUFFERS)前后对比找出一个低使用率索引并写出保留/删除理由。下一篇比较 SQLite、MySQL 和 PostgreSQL 的特性理解迁移时哪些行为不能想当然。