知识卡片

结构图卡

常见SQL调优经验法则:从索引到扫描次数

理解等级:未标注

除去前面已单独讨论的几条核心原理,书中还给出一组更具体的经验法则,共同的落脚点仍是”减少访问的块数量”与”减少不必要的处理开销”。外键列建索引:子表通常记录远多于父表,给外键列建索引既能加速父子表连接,也能加速对父表记录的删除/清理作业——没有这个索引,批量清理生产表的作业可能会变得极其缓慢。避免SELECT *:只声明真正需要的列,这样在所需列恰好都在索引中时,才可能享受到[[查询执行的四个组件,及计划器的三种数据访问方法|仅索引扫描]]的收益,用SELECT *会强制回表,白白多一次堆访问。谨慎使用ORDER BY:不需要排序时就不要加,因为对SELECT *的结果排序时,需要重排的是整个结果集而不仅仅是参与排序的那一列,一旦数据量超出内存就会溢出到磁盘,代价陡增;如果确实需要排序,对排序列建索引可以直接从索引里取出已排好序的数据,省去排序这一步。警惕用DISTINCT掩盖问题:当连接条件或过滤条件写得不够精确导致出现重复行时,加DISTINCT能让结果”看起来对了”,但这只是掩盖了连接逻辑本身的缺陷,且会给执行计划多加一个去重节点、增加开销——真正的做法是回头检查连接条件为什么会产生重复。用UNION ALL代替UNION:UNION会对结果做一次去重,UNION ALL不会;如果本来就不关心重复与否,用UNION ALL能省下这次去重的开销。合并多次扫描为一次:用CASE表达式把原本需要分别执行多条SQL语句(每条只是过滤条件不同)才能得到的分组统计,改写成一次扫描内用CASE分组累加,能把”扫描表N次”降为”扫描表1次”。部分索引:只针对数据的某个稳定子集(比如状态为”未处理”的一小部分记录)建索引,而不是对全表建索引,这类索引本身更小、扫描更快,尤其适合”大部分记录已经处理完毕、只有一小撮活跃记录会被频繁查询”这种数据分布场景。

结构图:

flowchart TB
  R["常见调优法则"]
  R --> A["外键列建索引<br/>加速连接与父表清理"]
  R --> B["避免SELECT*<br/>争取仅索引扫描"]
  R --> C["谨慎ORDER BY<br/>无需时不加,需要时对排序列建索引"]
  R --> D["警惕DISTINCT掩盖连接缺陷"]
  R --> E["UNION ALL替代UNION<br/>省去不必要的去重"]
  R --> F["CASE合并多次扫描为一次"]
  R --> G["部分索引<br/>只覆盖数据的活跃子集"]
这张卡片看不懂?

已有反馈 · 0 条

    还没有人提交反馈。

    原书出处

    参考来源

    - 位置:《数据架构师的PostgreSQL修炼:高效设计、开发与维护数据库应用》第7章《SQL调优》之"7.4 优化指南和捕捉"(7.4.1-7.4.5、7.4.7、7.4.10各小节,源文件:_epub-src/OEBPS/text00081.html-text00090.html) - 结论依据:原文分别说明"在子表中创建一个外键列索引总是不错的主意"、"除非我们真的需要表中的所有列,否则不应该使用SELECT*"、"我们不应该使用ORDER BY,除非我们不得不这样做"、"使用DISTINCT来避免重复仅仅隐藏了问题(连接不明确的问题)"、"UNION ALL不会执行去重操作……这样会增加一些开销"、"使用CASE结构的查询只扫描表一次,这样成本就下降了"、"在数据子集上创建索引的优势是可以比不用过滤条件创建的索引小", 分别支撑本卡汇总的七条经验法则。 - 原始内容:正确的方法是检查是否以后重复并确保连接形式的正确。