在生产过程中,有的 SQL 查询往往会变得越来越慢,这时候,我们该怎么办呢?首当其冲的,我们可以通过查询计划来定位问题,今天就来谈谈如何在查询计划中定位这些慢查询产生的原因。
当我们分析查询计划时,是否有一个异常操作消耗了大部分的查询时间?比如,在执行索引扫描时,时间比预期的要长很多,这时候我们基本可以判断此索引可能已经超期了,需要重建。
我们通过运行 EXPLAIN ANALYZE ,查看执行计划预估的返回行数与实际返回的行数是否接近,如果出入很大,说明统计信息是有问题的,我们需要对相关表 / 列收集更多的统计信息。
在执行计划中,选择性限定条件应该更早的应用,目的是让更少的数据返回到上层操作中。如果查询在选择性限定条件应用后表现并不好,返回的消耗依然很大,我们可以收集相关列的统计信息再看看是否会提高性能;另外,还可以通过调整 SQL 语句中不合理的 WHERE 条件来提高性能。
当我们的查询里面有很多连接操作(JOIN)时,要确保执行计划选择了一个最优连接顺序。拥有大量返回数据的连接应该尽早完成,以保证我们为上层操作返回更少的行。如果执行计划没有选择最佳的连接顺序,我们可以设置参数 join_collapse_limit=1 ,然后在 SQL 语句中使用明确的 JOIN 语法强迫执行计划按照特定的执行顺序执行。另外,我们可以收集相关列的统计信息再看看是否会提高性能。
如果我们使用查询中涉及到了分区表数据查询,那么查询计划是否直接定位到扫描满足条件的分区,而不是扫描整张表。
Hash 操作比其他类型的聚合或者连接操作要快很多,行数据的比较和分类操作是在内存中进行,而不是通过读写磁盘完成。为了能够使用 Hash 操作,我们必须保证有足够的 work memory 可以容纳查询计划返回的行数据,所以我们可以通过尝试增加 work memory 来提高查询性能。通过运行 EXPLAIN ANALYZE 命令,这样可以看出哪些计划会有数据使用到磁盘,需要多少额外的 work memory 等,为 work memory 的调整提供参考。例如:
- Work_mem used: 23430K bytes avg,
- 23430K bytes max(seg0).Work_mem wanted: 33649K bytes avg,
- 33649K bytes max(seg0) to lessen workfile I / O affecting 2 workers.
来源: https://yq.aliyun.com/articles/141108