PG_SQL优化

前言

　　上面两张图分别是SQL下发到数据库的简单流程图，以及数据库系统流程图（感兴趣的可以阅读《数据库系统实现》）；本章只涉及如何优化SQL，其他如服务端与客户端之间网络优化、缓冲区优化等不讨论。

优化根本原则

　　优化的根本原则在于---尽可能的减少IO；下面具体的优化方法，如减少排序还是使用索引，抑或是避免使用中间表等，其实目的都是尽可能的减少对硬盘的访问。

优化具体方法

1、SQL后面添加limit

　　目前的应用往往需要分页，SQL中添加limit可以避免扫描不必要的记录。另外一个需要注意的瓶颈点在于当偏移量特别大的时候，查询效率就变得低下。

理由如下：

当偏移量最大的时候，查询效率就会越低，因为并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。

解决方案：

首先需要跟业务确认是否真的需要这么后的分页。
使用 order by+索引，也是可以提高查询效率的。

select id，name from employee where id>10000 limit 10;

返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。

select id，name from employee where id>10000 limit 10;

　　对明知只返回一条记录的SQL加上limit 1

理由如下：

你知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动，避免扫描没必要的块。

2、禁用select *

　　取出全部列不仅会导致数据库无法使用index only scan；同时会带来额外的I/O、内存、CPU和网络开销。

3、优化like语句

　　一般的优化手段是将%匹配符放在关键字后面，这样PG能在无插件的情况下走索引。但是pg_trgm插件能够使like ‘%xxx%’走索引。

#安装好pg_trgm插件
create extension pg_trgm;
create index idx_t1_c1_gin on t1 using gin(c1 gin_trgm_ops);
注释：pg_trgm有限制，字段值需要三个字符及以上

4、避免在索引列上使用内置函数和表达式操作

　　在索引列使用内置函数，如：max(id)=1000、id+10=100;会导致索引失效。如果要使用函数，将函数放在等号右边。

5、对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引，尽量避免全表扫描

6、在适当的时候，使用only indexscan

　　查询的流程往往是先扫描对应索引块，寻址后扫描对应数据块；使用only indexscan能够使得你的 SQL 语句不需要回表，仅仅访问索引就能够得到所有需要的数据，大大提高了查询效率。

7、避免排序

　　避免排序并不意味着禁止排序，而是要知道哪些运算会进行排序操作，同时判断是否有排序的必要以及能否找到对应的替代方案，提高查询效率。

会进行排序的代表性的运算有下面这些：

　　● GROUP BY 子句

　　● ORDER BY 子句

　　● 聚合函数（ SUM、 COUNT、 AVG、 MAX、 MIN）

　　● DISTINCT

　　● 集合运算符（ UNION、 INTERSECT、 EXCEPT）

　　● 窗口函数（ RANK、 ROW_NUMBER 等）

　　例如：distinct 关键字一般用来过滤重复记录，以返回不重复的记录。在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果。但是在字段很多的时候使用，却会大大降低查询效率

理由如下：

　　带 distinct 的语句需要将字段进行排序所消耗的 CPU 时间和占用时间都高于不带 distinct 的语句。因为当查询很多字段时，如果使用 distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，CPU 时间。

可参考解决方案：

　　7.1、灵活使用集合运算符的 ALL 可选项

　　SQL 中有 UNION、 INTERSECT、 EXCEPT 三个集合运算符。

　　在默认的使用方式下，这些运算符会为了排除掉重复数据而进行排序。首先判断是否真的需要做union，如果真有必要且不在乎结果中是否有重复数据，可以使用union all 代替union。

例如之前遇到的一个例子，某项目现场写的SQL如下：

--表已经分区，但是时间跨度3年。

select count(*) from(
select id from tab1 where time1 between 1511745973 and 1606440373 and col1 = ‘xx1’
union
select id from tab1 where time1 between 1511745973 and 1606440373 and col2 = ‘xx2’
union
select id from tab1 where time1 between 1511745973 and 1606440373 and col3 = ‘xx3’) tmp;

　　很显然上述情况下时间跨度3年非常大建了（time1，col1）这类索引，效果也不好（实际耗时200s+走的全表扫描）；实际上看上述SQL其实根本没必要写的这么繁琐。

　　优化手段：

--SQL改写：id字段实际不存在重复数据，去掉没必要的union
select count(*) from tab1 where (col1 = ‘xx1’ or col2 = ‘xx2’ or col3 = ‘xx3’) and time1 between 1511745973 and 1606440373;

--新建索引(col1,col2,col3过滤性非常好，可以作为多列索引的前导列避免time1字段大范围扫描索引)
create index idx_tab1_col1_time1 on tab1(col1,time1);
create index idx_tab1_col2_time1 on tab1(col2,time1);
create index idx_tab1_col3_time1 on tab1(col3,time1);

优化后响应时间在毫秒级
FAQ：
有人会问为什么or还能走索引，实际上pg可以利用bitmapscan是可以走组合索引的。

　　7.2、使用 EXISTS 代替 DISTINCT

　　为了排除重复数据， DISTINCT 也会进行排序。如果需要对两张表的连接结果进行去重，可以考虑使用 EXISTS 代替 DISTINCT，以避免排序。

SELECT DISTINCT I.item_no FROM Items I INNER JOIN SalesHistory SH ON I. item_no = SH. item_no;

--可以改写为以下SQL避免排序。
SELECT item_no FROM Items I WHERE EXISTS (SELECT * FROM SalesHistory SH WHERE I.item_no = SH.item_no);

　　7.3、在极值函数中使用索引（MAX/MIN）

　　使用这两个函数时都会进行排序。但是如果参数字段上建有索引，则只需要扫描索引，不需要扫描整张表。

-- item字段无索引，这样写需要扫描全表
SELECT MAX(item) FROM Items;

--item_no字段有索引，这样写能用到索引
SELECT MAX(item_no) FROM Items;

　　如果item_no 是表 Items 的唯一索引，效果会更好。对于多列索引，只要查询条件是联合索引的第一个字段，索引就是有效的，所以也可以对表 SalesHistory 的 sale_date 字段使用极值函数。

　　这种方法并不是去掉了排序这一过程，而是优化了排序前的查找速度，从而减弱排序对整体性能的影响。

　　7.4、在 GROUP BY 子句和 ORDER BY 子句中使用索引

　　一般来说， GROUP BY 子句和 ORDER BY 子句都会进行排序，来对行进行排列和替换。不过，通过指定带索引的列作为 GROUP BY 和 ORDER BY 的列，可以实现高速查询。特别是，在一些数据库中，如果操作对象的列上建立的是唯一索引，那么排序过程本身都会被省略掉。

8、删除冗余和重复索引

　　重复的索引需要维护、占用存储空间以及在一般情况下会降低update的性能，并且优化器在优化查询的时候也需要逐个地进行考虑，扰乱执行计划的生成，进而影响性能。

9、关于大量DELETE/UPDATE操作

　　如果数据量较大，优化你的修改/删除语句，改成批量删除。避免同时修改或删除过多数据，因为会造成 I/O利用率过高，从而影响别人对数据库的访问。

10、where 子句中考虑使用默认值代替 null

　　并不是说使用了 is null 或者 is not null 就会不走索引了，这个跟查询成本都有关。

　　如果优化器发现，走索引比不走索引成本还要高，肯定会放弃索引，这些条件！=，>isnull，isnotnull 经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃索引的。

　　如果把 null 值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点。

　　null值也会带来查询出错，如：

select name from emp where id not in (select id from emp where age=’18’);如果子查询中返回（1,2,null）这样结果会返回空。

原因分析：
上述SQL可以改写为：

select name from emp where (id<>1) and (id<>2) and (id<>null);
对null使用<>后，结果为unknown，如果and运算符中包含unknown则结果不为true，因此会返回错误结果。正确的方法是改成对应的exists。
select name from emp e1 where not exists (select 1 from emp e2 where e1.age='18' and e1.id=e2.id);

消除NULL：

编号：使用异常编号

　　如性别编号：1：男性，2:女性；可以额外定义0：未知

姓名：使用“无名氏”

　　未知名字使用“UNKNOWN”

数值：用0代替

日期：用最大值或最小值代替

　　当需要表示开始日期和结束日期这样的“期限”的时候，我们可以使用 0000-01-01 或者 9999-12-31 这样可能存在的最大值或最小值来处理。

　　在设计表结构时尽量先考虑能不能设置默认值，实在无法设置为默认值情况下允许使用NULL。

11、合理使用exists&in

　　假设表 A 表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下 SQL：

select * from A where deptId in (select deptId from B);

#这样写等价于：
#先查询部门表B
select deptId from B
#再由部门deptId，查询A的员工
select * from A where A.deptId = B.deptId
可以抽象成这样的一个循环：
   List<> resultSet ;
    for(int i=0;i<B.length;i++) {
          for(int j=0;j<A.length;j++) {
          if(A[i].id==B[j].id) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

显然，除了使用 in，我们也可以用 exists 实现一样的查询功能，如下：


select * from A where exists (select 1 from B where A.deptId = B.deptId);
#这样写等价于：
select * from A,先从A表做循环
select * from B where A.deptId = B.deptId,再从B表做循环.
#可以抽象成这样一个循环：
   List<> resultSet ;
    for(int i=0;i<A.length;i++) {
          for(int j=0;j<B.length;j++) {
          if(A[i].deptId==B[j].deptId) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

　　数据库最费劲的就是跟程序链接释放。假设链接了两次，每次做上百万次的数据集查询，查完就走，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，这样系统就受不了了。

即 PG优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。

因此，我们要选择最外层循环小的，也就是，如果 B 的数据量小于 A，适合使用 in，如果 B 的数据量大于 A，即适合选择 exist；当然一般情况下，子查询的in用exists替代往往能得到更高的性能；

原因在于：

如果连接列（deptId）上建立了索引，那么查询 B 时不用查实际的表，只需查索引就可以了。

如果使用 EXISTS，那么只要查到一行数据满足条件就会终止查询，不用像使用 IN 时一样扫描全表。在这一点上 NOT EXISTS 也一样。

当 IN 的参数是子查询时，数据库首先会执行子查询，然后将结果存储在一张临时的工作表里（内联视图），然后扫描整个视图。很多情况下这种做法都非常耗费资源。使用 EXISTS 的话，数据库不会生成临时的工作表。

12、能写在 WHERE 子句里的条件不要写在 HAVING 子句里

　　例如，下面两条 SQL 语句返回的结果是一样的。

--反例 聚合后使用 HAVING 子句过滤
SELECT sale_date, SUM(quantity) FROM SalesHistory
GROUP BY sale_date
HAVING sale_date = '2007-10-01';

--正例 聚合前使用 WHERE 子句过滤
SELECT sale_date, SUM(quantity) FROM SalesHistory
WHERE sale_date = '2007-10-01'
GROUP BY sale_date;

但是从性能上来看，第二条语句写法效率更高。

原因如下：

第一个是在使用 GROUP BY 子句聚合时会进行排序，如果事先通过 WHERE 子句筛选出一部分行，就能够减轻排序的负担。

第二个是在 WHERE 子句的条件里可以使用索引。 HAVING 子句是针对聚合后生成的视图进行筛选的，但是很多时候聚合后的视图都没有继承原表的索引结构。

13、用varchar代替char，合理设置varchar可变字段长度

理由如下：

因为首先变长字段存储空间小，可以节省存储空间。

其次对于查询来说，在一个相对较小的字段内搜索，效率更高（PG中varchar和char在性能上面差不多）。

能满足长度需求情况下，varchar(20)较varchar(255)更优。

两者具体区别参考另外一篇博客《PG_字符串探究》

https://www.cnblogs.com/duanleiblog/p/14037377.html

14、where后字段值注意引号使用，易导致索引失效

　　在很多项目现场SQL评审中发现数值类型用单引号，字符类型不用引号，此类SQL要么导致索引失效，要么导致日志中存在大量报错。

15、当在 SQL 语句中连接多个表时,请使用表的别名，并把别名前缀于每一列上，这样语义更加清晰

　　避免相同的别名，这样会降低SQL可读性。

16、索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段

　　因为 SQL 优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

17、表关联不要太多

　　连表越多，编译的时间和开销也就越大（PG可能选择GEQO（默认十几个的样子））。把连接表拆开成较小的几个执行，可读性更高。如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。

18、Inner join 、left join、right join，优先使用 Inner join，如果是 left join，左边表结果尽量小

　　Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集。

　　left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

　　right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

　　都满足 SQL 需求的前提下，推荐优先使用 Inner join（内连接），如果要使用 left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理。

理由如下：

如果 inner join 是等值连接，或许返回的行数比较少，所以性能相对会好一点。

同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。

　　关联字段需要建索引，同时以left join为例，左表应该尽可能的小，如果左表太大会产生很大的笛卡尔乘积，尤其是在多表关联时，此乘积将非常大，此时就需要想办法是否能有其它SQL写法能够代替了。

解决方案：

　　18.1、分解关联查询；

　　很多高性能的应用都会对关联查询进行分解；简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。例如，下面的这个查询：

select * from tag t
join tag_post tp on t.tag_id=tp.tag_id
join post on tp.post_id=p.post_id
where t.tag='postgresql';

可以分解为下面这些查询来代替：

select * from tag t where t.tag='postgresql';
select * from tag_post tp where tp.tag_id=1234;
select * from post p where p.post_id in(123,456,789);

　　一条SQL变成多条，且结果并未变化，那为啥能成为优化手段呢？实际上，用分解关联查询的方式重构查询有如下优势：

让缓存的效率更高。很多应用程序可以方便的缓存单表查询对应的结果对象。例如，上面查询的tag已经被缓存了，那么应用就可以跳过第一个查询。再例如，应用中已经缓存了id 123,456的内容，那么第三个SQL的in（）中就可以少几个ID。另外对于查询缓存来说，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分之后，如果某个表很少变化，那么基于该表的查询就可以利用查询缓存的结果。

将查询分解之后，执行单个查询可以减少锁的竞争。

在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。

查询本身效率也可能会有所提升。

　　18.2、改写关联查询

--其中code是ci表的主键，ci表数据量很大接近几亿，其余表数据量不大。
select info.*,res.* from ci  info,
(select ci.code
,count(distinct cp.clue_code) as num1
,count(distinct cc.clue_code) as num2
,strpos(string_agg(cv.inv_code,','),'xiaohu')=0) >0 as follow
from ci left join cp on ci.code=cp.clue_code
left join cc on ci.code=cc.clue_code
left join cv on ci.code=cv.clue_code
where ci.is_delete=0
and (ci.owner_org like '321231%' or cv.inv_code='xiaohu')
group by ci.code
having (count(cv.inv_code)=0 or strpos(string_agg(cv.inv_code,','),'xiaohu')=0) ) res
where res.code=info.code
order by update_at desc limit 10;
--耗时8s

SQL改写：

--避免大量笛卡尔乘积
select info.*,res.* from ci  info,
(
select ci.code
,sum(case when exists (select 1 from cp where cp.clue_code=ci.code) then 1 else 0 end) as num1
,sum(case when exists (select 1 from cc where cc.clue_code=ci.code) then 1 else 0 end) as num2
,case when exists (select 1 from cv where cv.clue_code=ci.code and cv.inv_code='xiaohu') then 'T' else 'F' end as follow
from ci
where ci.is_delete=0
and ci.owner_org like '321231%' 
and not exists (
select 1 from cv where cv.clue_code=ci.code
and cv.inv_code='xiaohu')
 ) res
where res.code=info.code
order by update_at desc limit 10;
--耗时8ms

以上是优化的例子，具体如何改写使得效率更高，以实际情况为准。

19、减少中间表

在 SQL 中，子查询的结果会被看成一张新表，这张新表与原始表一样，可以通过代码进行操作。这种高度的相似性使得 SQL 编程具有非常强的灵活性，但是如果不加限制地大量使用中间表，会导致查询性能下降。

频繁使用中间表会带来两个问题：

一是展开数据需要耗费内存资源；
二是原始表中的索引不容易使用到（特别是聚合时）。

因此，尽量减少中间表的使用也是提升性能的一个重要方法。

特定场景解决方案：

　　19.1、灵活使用 HAVING 子句


HAVING 子句和聚合操作是同时执行的，所以比起生成中间表后再执行的 WHERE 子句，效率会更高一些，而且代码看起来也更简洁。--原始SQL
SELECT *
FROM (SELECT sale_date, MAX(quantity) AS max_qty
FROM SalesHistory
GROUP BY sale_date) TMP --没用的中间表
WHERE max_qty >= 10;

--使用having改写
SELECT sale_date, MAX(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING MAX(quantity) >= 10;

　　19.2、需要对多个字段使用 IN 谓词时，将它们汇总到一处

--原始SQL
SELECT id, state, city
FROM Addresses1 A1
WHERE state IN
(SELECT state FROM Addresses2 A2 WHERE A1.id = A2.id)
AND city IN
 (SELECT city FROM Addresses2 A2 WHERE A1.id = A2.id);

--改写
SELECT *
FROM Addresses1 A1 WHERE (id, state, city)
IN (SELECT id, state, city FROM Addresses2 A2);

20、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

理由：

使用!=和<>很可能会让索引失效

21、使用多列索引时，注意索引列的顺序，一般遵循最左匹配原则

理由如下：

当我们创建一个联合索引的时候，如（k1，k2，k3），相当于创建了（k1）、（k1，k2）和（k1，k2，k3）三个索引，这就是最左匹配原则。

联合索引不满足最左原则，走索引的代价反而大于走全表扫描，索引一般会失效

22、字段类型能用数值尽量用数值类型

　　相对于数值类型字段，字符型会降低查询和连接的性能，并会增加存储开销；因此尽可能的使用数值类型，例如字典数据用数值类型表示，如sex 用int类型“1：男，2：女，0：未知”。

23、长度很长的多字段联合主键用hash

　　在业务中，需要用很多很长的多字段做联合主键，保证数据的唯一，这种情况下可以增加一个列，用hash函数计算后插入，保证数据唯一；例如hash(a,b,c),a,b,c字段经过hash函数处理后插入新列。在PG中主键会自动创建B+树索引，如果字段很大，将会导致索引结构很大，不便于维护。

24、禁用UUID作为主键

　　UUID是长的字符串，虽然能作为唯一值，但是会导致索引结构很大，建议实际生产中使用自增列。

25、善用set、explain查看&调试执行计划

　　PG的执行计划查看方式为“缩进从右往左查看，同级从上往下查看”，如果执行计划不符合预期，可以用set进行session级别更改；如设置尽可能用索引：set enable_seqscan=0;

具体使用使用以及代价参数在另外一篇博客有介绍《PG 执行计划》

https://www.cnblogs.com/duanleiblog/p/13751805.html

参考文献：

《高性能MYSQL》

《SQL进阶教程》

《数据库系统实现》

《数据库系统概念》

《数据库索引设计与优化》

《PostgreSQL 12.2 手册》

posted @ 2020-11-30 00:13 DUAN的博客阅读(2654) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

段磊的博客

PG_SQL优化

目录

前言

优化根本原则

优化具体方法

1、SQL后面添加limit

2、禁用select *

3、优化like语句

4、避免在索引列上使用内置函数和表达式操作

5、对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引，尽量避免全表扫描

6、在适当的时候，使用only indexscan

7、避免排序

8、删除冗余和重复索引

9、关于大量DELETE/UPDATE操作

10、where 子句中考虑使用默认值代替 null

11、合理使用exists&in

12、能写在 WHERE 子句里的条件不要写在 HAVING 子句里

13、用varchar代替char，合理设置varchar可变字段长度

14、where后字段值注意引号使用，易导致索引失效

15、当在 SQL 语句中连接多个表时,请使用表的别名，并把别名前缀于每一列上，这样语义更加清晰

16、索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段

17、表关联不要太多

18、Inner join 、left join、right join，优先使用 Inner join，如果是 left join，左边表结果尽量小

19、减少中间表

20、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

21、使用多列索引时，注意索引列的顺序，一般遵循最左匹配原则

22、字段类型能用数值尽量用数值类型

23、长度很长的多字段联合主键用hash

24、禁用UUID作为主键

25、善用set、explain查看&调试执行计划

公告