PostgreSQL技术大讲堂 - 第31讲:SQL调优技巧

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: PostgreSQL从小白到专家,系列技术大讲堂 - 第31讲:SQL调优技巧

PostgreSQL从小白到专家,是从入门逐渐能力提升的一个系列教程,内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容,希望对热爱PG、学习PG的同学们有帮助,欢迎持续关注CUUG PG技术大讲堂。


第31讲:SQL调优技巧


第31讲预告:10月28日(周六)19:30-20:30,钉钉群直播,群号:35822460

内容1 : SQL调优范式

内容2 : 多表查询调优技巧

内容3 : 多表查询应用案例


开发范式一

· 不要轻易把字段嵌入到表达式

在sal列上有索引,但是条件语句中把sal列放在了表达式当中,导致索引被压抑,因为索引里面储存的是sal列的值,而不是sal加上100以后的值。

testdb=# explain select * from emp2 where sal + 100 = 2000;

QUERY PLAN

-------------------------------------------------------------------------

Gather (cost=1000.00..7796.60 rows=2294 width=36)

Workers Planned: 2

-> Parallel Seq Scan on emp2 (cost=0.00..6567.20 rows=956 width=36)

Filter: ((sal + 100) = 2000)

(4 rows)

· 改写成

通过等式等换,把sal列从表达式中剥离出来,就会用到索引。

testdb=# explain select * from emp2 where sal = 2000 - 100;

QUERY PLAN

--------------------------------------------------------------------------

Index Scan using emp2_sal_ind on emp2 (cost=0.42..8.44 rows=1 width=36)

Index Cond: (sal = 1900)

(2 rows)


开发范式二


· 不要轻易把字段嵌入到函数中

在hiredate列上有索引,但是条件语句中把该列放在了函数当中,导致索引被压抑,因为索引里面储存的是该列的值,而不是函数处理以后的值。

testdb=# explain select * from emp2 where to_char(hiredate,'dd-mm-yyyy')='22-05-2022';

QUERY PLAN

----------------------------------------------------------------------------------------------------

Seq Scan on emp2 (cost=0.00..289.32 rows=50 width=62)

Filter: (to_char((hiredate)::timestamp with time zone, 'dd-mm-yyyy'::text) = '22-05-2022'::text)

· 改写成

通过等式转换,把列从函数中剥离出来,就会用到索引,比较成本,差别很大。

testdb=# explain select * from emp2 where hiredate=to_date('22-05-2022','dd-mm-yyyy');

QUERY PLAN

----------------------------------------------------------------------------

Index Scan using emp2_hiredate on emp2 (cost=0.29..8.30 rows=1 width=62)

Index Cond: (hiredate = to_date('22-05-2022'::text, 'dd-mm-yyyy'::text))


开发范式三


· 如果查询中比较固定查询某些列,可以基于这几个列建复合索引,直接查询索引,避开回表扫描。

create index emp2_empno on emp2 (empno,sal);

testdb=# explain select empno,sal from emp2 where empno=7788;

QUERY PLAN

-----------------------------------------------------------------------------

Index Only Scan using emp2_empno on emp2 (cost=0.29..10.09 rows=2 width=8)

Index Cond: (empno = 7788)


多表查询指导方针


· OLTP应用SQL调优指导方针

-- 驱动表上有很好的条件限制,同时,驱动表上的限制性条件字段上应该有索引,包括主键、唯一索引或其它索引、复合索引等。

-- 在每次连接操作之后尽量保证返回记录数最少,传递给下一个连接操作。

-- 根据返回的行的数量对应正确的连接方式。

-- 尽量通过在被驱动表的连接字段上的索引,访问被驱动表。

-- 单表扫描应该有效率,如果被驱动表上还有其它限制条件,可以遵循复合索引创建原则,创建合适的复合索引(连接字段与条件字段)。

-- 全表扫描也许是合理的,例如若干小表、代码表的访问。

-- 依次类推,顺序完成所有表的连接操作。


· 多表连接调优总体思路

>> 如果是OLTP应用,则优化的思路是由小到大,即从限制性最强,返回记录最少的连接开始,依次完成其它表的连接,并在访问每张表时,合理使用索引,特别是复合索引技术。

>> 如果是OLAP应用,则优化思路基本是hash连接加并行处理,表连接顺序不是最主要的。


· 多表连接优化案例一

testdb=# explain select e.*,d.*

from emp e,dept d

where d.deptno=e.deptno

and e.empno=7499;

QUERY PLAN

-----------------------------------------------------------------------------

Nested Loop (cost=0.30..16.36 rows=1 width=192)

-> Index Scan using pk_emp on emp e (cost=0.15..8.17 rows=1 width=98)

Index Cond: (empno = 7499)

-> Index Scan using pk_dept on dept d (cost=0.15..8.17 rows=1 width=94)

Index Cond: (deptno = e.deptno)

执行计划解读:

1、先按照建立在empno字段上的索引去emp表查询empno为7499的员工信息。

2、再根据7499所在的部门号(deptno)去dept表查询该部门的详细信息,而且dept表的deptno字段上应该有索引。

3、最后使用嵌套循环连接方式处理数据。

建议:

“如果是多表连接sql语句,注意驱动表的连接字段是否需要创建索引”。

在上例中,被驱动表是dept,dept表的连接字段是deptno,而emp的deptno字段是可以不需要建索引的,因为已经根据条件字段上列访问驱动表。


· 多表连接优化案例二

testdb=# explain select e.*,d.*

from emp e,dept d

where d.deptno=e.deptno

and e.empno=7499

and d.dname='DALLAS';

QUERY PLAN

-----------------------------------------------------------------------------

Nested Loop (cost=0.30..20.35 rows=1 width=192)

-> Index Scan using pk_emp on emp e (cost=0.15..8.17 rows=1 width=98)

Index Cond: (empno = 7499)

-> Index Scan using pk_dept on dept d (cost=0.15..8.17 rows=1 width=94)

Index Cond: (deptno = e.deptno)

Filter: ((dname)::text = 'DALLAS'::text)

执行计划解读:

1、先按照建立在empno字段上的索引去emp表查询empno为7499的员工信息。

2、再根据7499所在的部门号(deptno)去dept表查询该部门的详细信息。此时dept表还有一个条件字段loc=‘DALLAS’,因此可考虑按(deptno,loc)复合索引方式去查询dept表,效率更高,即可建立(deptno,loc)字段上的复合索引(idx_dept_2)。

3、最后以嵌套循环的连接方式处理数据。

建议:

“如果是多表连接sql语句,注意是否可以在被驱动表的连接字段与该表的其它约束条件字段上创建复合索引”。索引可以在dept表上创建(deptno与dname)字段的复合索引。

执行计划解读(续)

应该遵循关于复合索引创建时的建议:

“如果单个字段是主键或者唯一字段,或者可选性非常高的字段,尽管约束条件字段比较固定,也不一定要建成复合索引,可建成单字段索引,降低复合索引开销”。

*而且通过比较发现这种情况创建单列索引比创建复合索引查询的时候代价要低的多。所以在本例中,不应该创建复合索引。


多表查询应用案例


· 5张查询应用案例

SELECT emp.last_name,emp.first_name,j.job_title,d.department_name,l.city,l.state_province,l.postal_code,l.street_address,emp.email,emp.phone_number,emp.hire_date,emp.salary,mgr.last_name

from hr.employees emp,hr.employees mgr,hr.departments d,hr.locations l,hr.jobs j

where l.city='South San Francisco'

and emp.manager_id=mgr.employee_id

and emp.department_id=d.department_id

and d.location_id=l.location_id

and emp.job_id=j.job_id;


· 第一种情况:无索引

在没有任何索引的情况下查看其执行计划 ,由于没有索引,所以所有扫描方式均为全表扫描,连接方式为hash join。


· 第二种情况:创建单列索引

在locations的city、location_id列上创建索引。

在departments的location_id上创建索引

在departments的department_id上创建主键约束

在employees的employee_id上创建主键约束

在jobs的job_id上创建主键约束。


· 第三种情况:创建复合索引

在locations的city、location_id列上创建复合索引。

在departments的department_id 、location_id上创建复合索引

在employees的employee_id、 department_id、manager_id、job_id上创建复合索引(或者单列索引)

在jobs的job_id上创建主键约束。


· 三种执行计划成本对比

经过分析发现,如果连接方式能够走嵌套循环,那么其成本比其它连接方式都低,当然我们要提供条件让优化器自动选择成本最低的连接方式,只要有一张表的访问方式是索引扫描,那么连接方式一般会选择嵌套循环。

Employees表的复合索引在执行计划中起到了作用,或者选择在连接条件列上( employee_id,department_id,manager_id )创建单列索引。

Departments和locations表的记录比较少,即使创建了单列或者多列索引,都不会使用索引。

连接顺序是L->D->EMP-MGR-J



相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
打赏
0
1
3
0
119
分享
相关文章
SQL查询太慢?实战讲解YashanDB SQL调优思路
本文是Meetup第十期“调优实战专场”的第二篇技术文章,上一篇《高效查询秘诀,解码YashanDB优化器分组查询优化手段》中,我们揭秘了YashanDB分组查询优化秘诀,本文将通过一个案例,助你快速上手YashanDB慢日志功能,精准定位“慢SQL”后进行优化。
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
81 1
【SQL技术】不同数据库引擎 SQL 优化方案剖析
不同数据库系统(MySQL、PostgreSQL、Doris、Hive)的SQL优化策略。存储引擎特点、SQL执行流程及常见操作(如条件查询、排序、聚合函数)的优化方法。针对各数据库,索引使用、分区裁剪、谓词下推等技术,并提供了具体的SQL示例。通用的SQL调优技巧,如避免使用`COUNT(DISTINCT)`、减少小文件问题、慎重使用`SELECT *`等。通过合理选择和应用这些优化策略,可以显著提升数据库查询性能和系统稳定性。
114 9
比 SQL 快出数量级的大数据计算技术
SQL 是大数据计算中最常用的工具,但在实际应用中,SQL 经常跑得很慢,浪费大量硬件资源。例如,某银行的反洗钱计算在 11 节点的 Vertica 集群上跑了 1.5 小时,而用 SPL 重写后,单机只需 26 秒。类似地,电商漏斗运算和时空碰撞任务在使用 SPL 后,性能也大幅提升。这是因为 SQL 无法写出低复杂度的算法,而 SPL 提供了更强大的数据类型和基础运算,能够实现高效计算。
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
1038 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
Oracle SQL:了解执行计划和性能调优
Oracle SQL:了解执行计划和性能调优
168 1
Text-to-SQL技术演进 - 阿里云OpenSearch-SQL在BIRD榜单夺冠方法剖析
本文主要介绍了阿里云OpenSearch在Text-to-SQL任务中的最新进展和技术细节。

相关产品

  • 云原生数据库 PolarDB
  • AI助理

    你好,我是AI助理

    可以解答问题、推荐解决方案等