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

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 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数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
5天前
|
关系型数据库 分布式数据库 数据库
阿里云PolarDB登顶2024中国数据库流行榜:技术实力与开发者影响力
近日,阿里云旗下的自研云原生数据库PolarDB在2024年中国数据库流行度排行榜中夺冠,并刷新了榜单总分纪录,这一成就引起了技术圈的广泛关注。这一成就源于PolarDB在数据库技术上的突破与创新,以及对开发者和用户的实际需求的深入了解体会。那么本文就来分享一下关于数据库流行度排行榜的影响力以及对数据库选型的影响,讨论PolarDB登顶的关键因素,以及PolarDB“三层分离”新版本对开发者使用数据库的影响。
24 3
阿里云PolarDB登顶2024中国数据库流行榜:技术实力与开发者影响力
|
3月前
|
存储 SQL 关系型数据库
PolarDB这个sql行存和列存性能差别好大 ,为什么?
PolarDB这个sql行存和列存性能差别好大 ,为什么?
24 0
|
3月前
|
关系型数据库 MySQL 分布式数据库
PolarDB MySQL版并行查询技术探索与实践
PolarDB MySQL版并行查询技术探索与实践 PolarDB MySQL版在企业级查询加速特性上进行了深度技术探索,其中并行查询作为其重要组成部分,已经在线稳定运行多年,持续演进。本文将详细介绍并行查询的背景、挑战、方案、特性以及实践。
93 2
|
11天前
|
SQL
14、sql注入绕过技术
14、sql注入绕过技术
14 0
|
11天前
|
SQL
小课堂 -- sql注入绕过技术
小课堂 -- sql注入绕过技术
16 0
|
1月前
|
SQL 算法 JavaScript
【数据库SQL server】关系型数据库的基本知识
【数据库SQL server】关系型数据库的基本知识
132 0
|
2月前
|
SQL 缓存 Java
JAVAEE框架技术之8-myBatis ORM框架技术参数和动态SQL语句
JAVAEE框架技术之8-myBatis ORM框架技术参数和动态SQL语句
58 0
JAVAEE框架技术之8-myBatis ORM框架技术参数和动态SQL语句
|
2月前
|
关系型数据库 分布式数据库 数据库
业界声音|PolarDB最值得关注的技术创新有哪些?
"PolarDB一路走来,见证了国产数据库发展的不平凡之路。"
业界声音|PolarDB最值得关注的技术创新有哪些?
|
2月前
|
缓存 运维 关系型数据库
PostgreSQL技术大讲堂 - 第43讲:流复制原理
PostgreSQL技术大讲堂 - 第43讲:流复制原理
35 2

相关产品

  • 云原生数据库 PolarDB