POSTGRESQL AUTO_EXPLAIN 记录慢语句的执行计划

本文介绍如何使用PostgreSQL的auto_explain模块自动记录慢查询的执行计划,包括配置参数详解及注意事项。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

b505a7eb0f6f69ca9a250946b955e0f8.png

在数据库的执行SQL的语句中,有很多语句在执行中,执行计划会变化,而执行计划的变化会导致SQL 语句执行时间的变化,如何对在POSTGRESQL 中执行较慢的语句。

auto_explain 模块提供一种自动记录慢语句的执行计划的功能,使用这个功能的同时需要注意,任何功能的开启都需要负担一定的性能损耗,在损耗的情况下,我们应该判断是否开启这个功能。

我们需要在下面的这个位置,将auto_explain 填入到 local_preload_libraries ,并且重启数据库服务器。

0b7598bd65fb4a321e48434f81ba45e7.png

除此以外如果要auto_explain 能良好的完成工作,POSTGRESQL 针对auto_explain 有几个参数需要修改,下面我们一个一个说

cd8219d8da0f295936348923482f5ee0.png

1   auto_explain.log_min_duration 

这个参数是启动auto_explain的核心参数,除了在local_preload_libraries 中需要加载auto_explain, 启用这个功能的另一个核心参数就是 auto_explain.log_min_duration. 这个参数代表了决定了超过多长时间的慢SQL 语句需要对执行计划进行记录,默认是-1 ,表名不记录任何慢语句。

2   auto_explain.log_analyze

这个参数主要是在输出的内容上进行调整,如果不写这个参数则输出的内容仅仅是 explain 的内容,而如果将这个值设置为 ON 则可以打开 explain analyze 的值

3  auto_explain.log_buffers 

这个值是在开启了 log_analyze 后产生的在analzye后打印 buffers的值,默认也是关闭的,在打开 auto_explain.log_analyz 后这个参数的修改才有效。

4  auto_explain.log_timing 

这个值主要控制在打印这个语句执行的时间有多长,这个参数也是 auto_explain.log_analzye 的后置项。

5  auto_explain.log_verbose

这个值主要控制 explain 中的verbose 信息的信息输出。

03eecfea51a48484cf4507c818ce76d1.png

我们执行一个SQL 查看在调整参数并重启后,日志中开始出现慢查询的执行计划的信息。

30f946aab73ee6e205e3af931b5ca500.png

实际上我们也可以修改打印的信息的格式,修改日志输出格式后,整体

47e3b1552878349c98ec0543d8f39455.png


相关 auto_explain功能就直接启用了。那么 auto_explain有什么劣势或者在使用中需要考虑的点

1  PG 中的慢查询和auto_explain 中的log_min_duration 应该设置成一个值吗?

这个问题实际上,个人人为不应该设置成一个值,而是应该设置为一个比 log_duration 更大的值。主要原因是,功能的不同,查看慢查询的执行计划本身并不应该针对每一个慢查询,而是应该对应与一些较长时间执行的SQL 如我们默认 1秒的SQL 就要记录在慢查询记录中,但是我们不应该将慢查询执行计划也设置成一秒,而应该讲时间拉长,例如 10秒,超过10秒的语句我们才需要查看他的实际执行计划,如果将每个超过1秒的语句都记录慢查询分析的话,浪费的日志空间,和消耗的系统IO等资源会比较大。

2  auto_explain 在什么情况下,需要开启 ?

auto_explain本身不适合一些已经运行一段时间的数据库并且数据量较大,平时数据库并没有太大的问题,而是预防一些由于数据量突然加大,导致执行计划和数据表analyze 不匹配导致的临时性的执行计划变化导致的问题,一般这样的语句都有执行时间突然变长的情况,在这样的情况下,适当的查看这个语句的执行计划,并记录当时的执行计划,有助于分析问题。

同时这个功能对于高频的,系统性能已经出现问题的数据库,在上线这个功能时候,需要考虑这个系统是否能承受这个功能带来的一些问题的考虑和前摄

1  系统消耗增多

2  磁盘是否能承载更多的日志

3  这个功能能实际解决问题的场景与你的期望是否契合

4  及时调整截取的SQL 的时间,避免大量的不需要的SQL 的分析被记录的日志。

120550ea94a9ad40ec0ba45a51c16b9e.png

### 如何使用 SQL `EXPLAIN` 分析查询执行计划 #### 基本概念 `EXPLAIN` 是一种用于分析 SQL 查询性能的重要工具,它能够提供关于查询执行方式的详细信息。通过解读这些信息,可以识别潜在的性能瓶颈并优化查询逻辑[^1]。 #### 基本语法 在大多数关系型数据库系统中(如 MySQLPostgreSQL),可以通过在查询前加上 `EXPLAIN` 关键字来获取其执行计划。以下是通用语法: ```sql EXPLAIN SELECT * FROM table_name WHERE condition; ``` 对于更复杂的查询场景,还可以附加选项以获得额外的信息。例如,在 MySQL 中支持扩展模式: ```sql EXPLAIN EXTENDED SELECT * FROM table_name WHERE condition; ``` 而在 PostgreSQL 中,则可通过设置参数启用更多细节: ```sql SET explain_perf_mode = 'verbose'; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM table_name WHERE condition; ``` #### 输出列详解 不同数据库系统的 `EXPLAIN` 结果格式略有差异,但核心字段通常包括以下几个方面: - **id**: 表示查询的选择顺序编号,数值越小优先级越高。 - **select_type**: 描述当前查询的部分类型及其角色,常见值如下: - `SIMPLE`: 不含子查询或联合操作的基础单表查询[^2]; - `PRIMARY`: 外部主查询部分; - `UNION`: 联合查询中的后续片段; - `SUBQUERY`: 子查询内部的第一个选择语句。 - **table**: 显示所访问的数据表名称;当存在别名定义时会反映出来;特殊情况下也可能标记为 `<derived>` 或者 `<union>` 形式的虚拟中间结果集[^3]^。 - **type**: 访问策略类别,从最优到最差依次排列为 system -> const -> eq_ref -> ref -> range -> index -> ALL 。其中,“ALL”意味着全表扫描,通常是低效的表现形式之一。 - **possible_keys**, **key**, **key_len**, **ref**, **rows**, **filtered**, **Extra** : 这些项进一步细化了索引利用情况以及实际读取记录数估计等方面的内容。 #### 实战案例 假设有一个名为 employees 的员工档案表格结构如下所示: | Field | Type | |-------------|--------------| | id | INT PRIMARY KEY AUTO_INCREMENT | | first_name | VARCHAR(50) NOT NULL | | last_name | VARCHAR(75) DEFAULT '' | | hire_date | DATE | 现在我们想找出姓氏以字母 A 开头的所有雇员名单,并对其应用 EXPLAIN 查看具体实现过程: ```sql EXPLAIN SELECT * FROM employees WHERE last_name LIKE 'A%'; ``` 根据返回结果评估是否存在合适的索引覆盖范围等问题进而调整设计达到最佳效果。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值