当前位置: fuhua-pet->PostgreSQL > PostgreSQL技术大讲堂 - 第31讲:SQL调优技巧

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

2023-11-20作者:fuhua-pet来源:www.fuhua-pet.com

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


第31讲:SQL调优技巧


第31讲:10月28日(周六)19:30-20:30,往期文档及视频,联系CUUG

内容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

  • 大连财经学院 - 国产数据库工作室揭牌仪式圆满成功(CUUG)
  • 河北工程技术学院 - 国产数据库工作室揭牌仪式圆满成功
  • postgresql从小白到高手 - 第38讲:数据库备份
  • 11月18日直播!杭州峰会大咖晚宴煮酒论英雄+PG技术大讲堂(34)
  • 2023年12月,PostgreSQL认证培训红头文件【工信部人才交流中心】
  • 温州大学 - 开源国产数据库工作室成立揭牌仪式圆满结束
  • 1月17日阿里云PolarDB开发者大会PolarDB DevCon
  • 2024-1-12,恭喜CUUG 王同学获得Oracle OCP证书
  • 2024年第13届PostgreSQL中国技术大会来啦!
  • DB-Engines:PostgreSQL is the DBMS of the Year 2023
  • PostgreSQL技术大讲堂 - 第44讲:pg流复制部署
  • PostgreSQL技术大讲堂 - 第45讲:poc-tpcc测试
  • PostgreSQL技术大讲堂 - 第47讲:JMETER工具使用
  • PostgreSQL技术大讲堂 - 第48讲:PG高可用实现keepalived
  • PostgreSQL技术大讲堂 - 第50讲:PG分区表管理
  • 2024-02-02,恭喜CUUG 刘同学通过Oracle考试获得OCP 19c证书
  • OCP认证能不能在家中考试,不去VUE考点考试吗?
  • 恭喜CUUG Guo同学以较高分数通过19c OCM认证考试!
  • 3月30日,工信部人才交流中心PostgreSQL认证考试顺利结束
  • 2024年4月8日,工信人才发布红头文件:PostgreSQL数据库管理人才研修与评测班
  • 恭喜CUUG入选2024年工业和信息化重点领域人才能力评价支撑机构
  • 天津职业技术师范大学《PolarDB开源数据库工作室》授牌仪式顺利完成
  • 温州大学国产开源数据库工作室成功举办PostgreSQL技能培训活动
  • Oracle数据库加入AI功能,Database 23c改名为Database 23ai
  • PostgreSQL技术大讲堂 - 第31讲:SQL调优技巧
  • PostgreSQL PGCP是什么级别的认证?
  • PGCP中级认证考试的三个维度考核
  • postgresql技术大讲堂 - 第40讲:数据库不完全恢复
  • PostgreSQL技术大讲堂 - 第46讲:poc-tpch测试
  • postgresql从小白到高手 - 第37讲:postgres物理备份和恢复概述
  • postgresql从小白到高手 - 第36讲:postgresql逻辑备份
  • postgresql从小白到高手 - 第35讲:中间件PgBouncer部署
  • PostgreSQL PGCP是什么级别的认证?
  • PGCP中级认证考试的三个维度考核
  • 国内PostgreSQL认证,工信部人才交流中心PG技术能力提升培训认证
  • 11月27日,CUUG新鲜出炉的Oracle DB 19C OCP证书
  • Oracle 19c OCM认证好考吗?CUUG OCM成绩公布
  • Oracle 19c OCM认证考试成绩出炉!- CUUG WDP培训中心
  • 菏泽学院 - 国产数据库工作室揭牌仪式圆满成功
  • PostgreSQL技术大讲堂 - 第41讲:表空间备份与恢复
  • PostgreSQL技术大讲堂 - 第42讲:pg_rman部署与使用
  • PostgreSQL技术大讲堂 - 第43讲:流复制原理
  • 阿里云PolarDB开发者大会圆满结束,CUUG两次获奖
  • 2024年首张Oracle OCP证书-CUUG胡同学
  • 北京培黎职业学院 - PolarDB开源国产数据库工作室成立揭牌 - CUUG
  • 今天(5月6日),CUUG 赵同学收到19c OCM认证考试证书!
  • PolarDB-X高可用与容灾(WIP)
  • PolarDB-X体系架构architecture
  • 什么是 PolarDB-X 云原生分布式数据库系统
  • postgresql技术大讲堂 - 第39讲:数据库完全恢复