原文towardsdatascience.com/query-optimization-for-mere-humans-in-postgresql-875ab864390a?sourcecollection_archive---------4-----------------------#2024-12-03理解 PostgreSQL 执行计划并通过实际示例讲解https://medium.com/Eyaltra?sourcepost_page---byline--875ab864390a--------------------------------https://towardsdatascience.com/?sourcepost_page---byline--875ab864390a-------------------------------- Eyal Trabelsi·发布于 Towards Data Science ·阅读时间 8 分钟 ·2024 年 12 月 3 日–https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/4f384f53a80ad0fe12427dcc5b83524a.png图片来源Greg Rakozy 于 Unsplash如今用户对所使用的程序有很高的期望。用户希望程序具备惊人的功能速度快并且合理地消耗资源。作为开发人员我们应该努力为用户提供最佳的体验。数据库成为瓶颈是很常见的情况优化查询并消除瓶颈并非易事。不幸的是随着程序变得越来越复杂数据量也越来越大编写完美的 SQL 查询变得愈加困难。今天我将重点介绍一种通过使用Explain子句来找到瓶颈的技术。今天的目标是向你展示找到并消除这些瓶颈并非难事任何人都可以轻松发现瓶颈所在。本文的代码可以在 GitHub 上找到。注所有图片除非另有说明均由作者提供。让我们解释一下 Explain 与数据库的交互是通过声明式语言进行的其中 SQL 是最常用的语言。数据库决定了背后如何以及做什么唯一提供的视角是执行计划。这种限制使得在实践中实现合适的调试工具和性能分析器几乎不可能。所以我们在执行计划上有点困住了。行业术语警告 我的目标是让执行计划大众化。在 PostgreSQL 中要获取执行计划应该使用Explain/Explain analyze子句EXPLAIN显示了计划者打算做什么。EXPLAIN ANALYZE显示计划执行者的计划执行查询并展示它是如何执行的。专家提示 #1在你的职业生涯中至少看一次执行计划。这在不同的数据库中类似而且在公司中是一个稀缺的技能。专家提示 #2更倾向于使用EXPLAIN ANALYZE因为它在大多数情况下包含更多信息。警告 #1⚠️ 不要在像DELETE/UPDATE这样的破坏性操作上使用EXPLAIN ANALYZEEXPLAIN就足够了它不会执行查询。警告 #2⚠️ 当资源紧张时例如生产环境监控不要使用EXPLAIN ANALYZE如果查询永远不会完成EXPLAIN就足够了它不会执行查询。Explain是一个很棒的工具因为它可以解释查询慢的原因包括缺失/过度使用的索引/分区。未优化的数据库配置。冗余操作。过时的统计信息。过多的 I/O 操作。对于更细致的人你可以在下图中看到Explain子句的语法https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/61acb4497b1c5ad8f1db4fcd7a3597b8.pngPostgreSQL Explain 子句语法。理解 Explain 解剖学我们将用它作为一个简单查询的示例我们想计算没有 Twitter 句柄的用户数量。EXPLAIN ANALYZE SELECT COUNT(*)FROM users WHERE twitter!;https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/9a8f28ed38a9fe982ff8f92ce75497f5.png我们可以看到从 EXPLAIN ANALYZE 子句返回的执行计划。起初它看起来很神秘而且比我们的查询还长甚至在一个小的实际执行计划示例中如果不集中注意力它会让人感到压倒。但它确实提供了有用的信息。我们可以看到查询执行花费了 1.27 秒而查询计划只花费了 0.4 毫秒可忽略的时间。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/d5e57f45b5c3501eb7d865b1efe9c8d4.png我们可以看到查询计划和执行所花费的时间。执行计划的结构是一个反向树。在下图中你可以看到执行计划被分成不同的节点每个节点代表不同的操作不管是聚合还是扫描。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/6a7ccef3d946eef093a5bca9a3c0c39d.png我们可以看到查询计划和执行所花费的时间。有许多种节点操作从与扫描相关的‘Seq Scan’‘Index Only Scan’ 等等与连接相关的‘Hash Join’‘Nested Loop’ 等等与聚合相关的‘GroupAggregate’‘Aggregate’ 等等以及其他的‘Limit’‘Sort’‘materialize’ 等等。幸运的是你不需要记住这些。专家提示 #3集中注意力专注于有问题的节点。专家提示 #4作弊在有问题的节点上搜索它们在 explain 词汇表 中的含义。现在让我们深入探讨如何识别出问题节点。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/5765a24e17bb9ae9361b2481951954f0.png我们可以在每个节点中看到大量信息。让我们深入了解这些度量指标的实际含义。实际循环数相同节点执行的循环次数为 1。为了获得总时间和行数实际时间和行数需要乘以循环次数的值。实际行数聚合节点生成的实际行数为 1每次循环的平均值循环次数为 1。计划行数聚合节点的估算生成行数为 1。行数的估算值可能会根据统计数据有所偏差。实际启动时间返回聚合节点第一行所花费的时间单位为毫秒为 1271.157已聚合并包含之前的操作。启动成本表示返回聚合节点第一行的估算时间的任意单位为 845110已聚合并包含之前的操作。实际总时间返回所有行所花费的时间单位毫秒在聚合节点中为 1271.158每次循环的平均值循环次数为 1已聚合并包含之前的操作。总成本表示返回聚合节点所有行的估算时间的任意单位为 845110已聚合。计划宽度聚合节点的估算行大小为 8 字节。专业小贴士 #5注意循环记住当关心实际行数和实际总时间时要乘以循环次数。我们将在下一节中讲解一个实际的例子。示例性能优化我们将使用与之前相同的查询。EXPLAIN ANALYZE SELECT COUNT(*)FROM users WHERE twitter!;https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/c9d940c226ab0b75dc4f477da4b96027.png我们将聚焦于实际时间最长的 Seq Scan 节点。我们聚焦于最耗时的操作即对用户表的顺序扫描。该扫描筛选出了 2,487,813 行占用了 1.27 秒总时间为 1.271 秒。**但我们毕竟只是人类这并没有给我们带来任何线索。**让我们用 Google 搜索一下你也可以使用 ChatGPT!!!。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/e202c10e9f78a7914c4ec6e1224da7c4.png在 Google 上搜索如何加速 PostgreSQL 中的顺序扫描。CREATE INDEX twitter_test ON users(twitter)https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/2dbebcdb3d427e75d385fb1194604613.png我们的表现已经好多了但“扫描”节点在实际时间上仍然是最慢的。我们可以看到现在我们在用户表上执行的是仅索引扫描。它只用了 0.29 秒而不是 1.27 秒这非常棒但对我们来说仍然不够。专业小贴士 #6一步一步地优化你的查询。要理解传递给扫描的数据量我们可以使用下方的缓存参数。专业小贴士 #7在比较执行计划时关注多个度量指标。EXPLAIN(ANALYZE,BUFFERS)SELECT COUNT(*)FROM users WHERE twitter!https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/93d19daed446727c8e7c2a33d3a8c6c9.png我们可以看到很多关于数据命中缓存或磁盘的信息。我们有 51,854 页需要从缓存中读取400 MB因此改进配置可能不会带来剧烈变化。但是我们并非没有选择。由于扫描排除了 2,487,813 行数据我们可以将索引更改为部分索引但这并不是免费的。这会导致写入时间变长并且需要额外的存储这对垂直扩展的系统影响较大。专业提示 #8世上没有免费的午餐。良好的优化选项我不会深入讲解太多细节因为这篇博客已经相当长了。这些是当你遇到慢查询时可能首先需要解决的问题选择正确的扫描方法。选择正确的连接方法。选择正确的连接顺序。尽早推送过滤器。在需要时减少磁盘 IO 操作。为了手动检查特定的优化可以启用/禁用一些设置。SET enable_seqscan TO off;EXPLAIN(ANALYZE)SELECT*FROM foo WHERE c1500;SET enable_seqscan TO on;警告 #3⚠️只有在你尝试过最基本的优化之后才可以启用/禁用设置因为大多数情况下PostgreSQL 知道自己在做什么。难道没有更简单的方法吗不幸的是Explain并不完美且有一些原因导致它并不在每个开发者的工具箱中你没有生产环境中所有执行计划的历史记录。我们可以通过使用像auto_explain和pg_stat_plans这样的工具来记录在特定条件下的执行计划从而弥补缺乏历史记录的问题且它们不会对生产环境造成重大影响。另一种方法是记录哪些查询在何时运行并尝试重现它但这比看起来更复杂。调优复杂查询并非一项简单任务尤其是使用“裸执行计划”因为它们通常很长且难以阅读。此外它们往往无法提供为何某个特定优化未被利用的洞察也不能提供如何有效重写查询以提升性能的指导。然而这一挑战可以通过采用高度特定结构和元数据并使用专业工具来解决。这些工具帮助你/LLM 专注于重要事项无论是架构、瓶颈等。一些最著名的工具包括eversql — 一款成熟的解决方案旨在为你的PostgreSQL查询提供变更建议。metis — 旨在为你的PostgreSQL数据库提供变更建议作为开发和 CI/CD 过程中的一项防护机制。QueryFlow — 一款开源工具允许识别多个查询的错误和性能调优因为最难调试的查询在孤立时表现得很平稳。专业提示 #9使用工具让你的生活更轻松。我将给你展示使用像 QueryFlow 这样的工具有多方便更多细节可以阅读下面的内容。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/8b7368c085df0fdc6607ce2201a3a0b9.pngQueryFlow 执行计划的可视化专注于持续时间应该很容易看出仅索引扫描的宽度远大于聚合宽度这表明我们应该将重点放在这里。在多个复杂查询中其他工具往往缺乏这种能力。最后的话在本文中我们回顾了一些最常见的原因这些原因可能导致本来完全正常的 SQL 在任何对时间敏感的应用中变得过于缓慢并介绍了一种神话般的方法来识别并避免这些问题。由于话题的广泛性有许多优化措施我没有涵盖。因此如果你想更深入了解我在最后添加了额外的资源。我对未来充满信心。我相信这些工具将变得和在 Python 中打开文件一样简单无论是通过集成到 IDE 和客户端还是提供 SAS 解决方案。这将使我们能够变得更加主动而不是被动应对。我希望我能够传达我对这个迷人话题的热情并且你觉得这些内容对你有帮助像往常一样我愿意接受任何建设性的反馈。附加资源 PostgreSQL 执行计划的更深入理解视频EXPLAIN 解析视频理解 EXPLAIN