3月第六讲pg distinct 改写递归优化

于 2024-03-12 00:18:14 发布
阅读量778 收藏 19
点赞数 16
文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2401_83442335/article/details/136639088
版权

下面SQL1000W行数据，列的选择性很低，只有两个值（'1'和'11'）都是字符串类型，'1'只有一条数据，'11'有9999999行数据。

慢SQL：

select distinct col from tt;

                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=169247.11..169247.12 rows=1 width=3) (actual time=5082.733..5082.735 rows=2 loops=1)
   Group Key: col
   ->  Seq Scan on tt  (cost=0.00..144247.29 rows=9999929 width=3) (actual time=0.005..275.906 rows=10000000 loops=1)
 Planning Time: 0.365 ms
 Execution Time: 5082.772 ms
(5 行记录)

CTE递归优化：

WITH RECURSIVE t AS (
   (SELECT col FROM tt ORDER BY col LIMIT 1)  
   UNION ALL
   SELECT (SELECT col FROM tt WHERE col > t.col ORDER BY col LIMIT 1)
   FROM t
   WHERE t.col IS NOT NULL
   )
SELECT col FROM t WHERE col IS NOT NULL;

                                                                        QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on t  (cost=50.84..52.86 rows=100 width=38) (actual time=0.024..0.079 rows=2 loops=1)
   Filter: (col IS NOT NULL)
   Rows Removed by Filter: 1
   CTE t
     ->  Recursive Union  (cost=0.43..50.84 rows=101 width=38) (actual time=0.022..0.076 rows=3 loops=1)
           ->  Limit  (cost=0.43..0.46 rows=1 width=3) (actual time=0.021..0.021 rows=1 loops=1)
                 ->  Index Only Scan using idx_1_2_tt on tt tt_1  (cost=0.43..260443.37 rows=9999929 width=3) (actual time=0.020..0.020 rows=1 loops=1)
                       Heap Fetches: 0
           ->  WorkTable Scan on t t_1  (cost=0.00..4.84 rows=10 width=38) (actual time=0.017..0.017 rows=1 loops=3)
                 Filter: (col IS NOT NULL)
                 Rows Removed by Filter: 0
                 SubPlan 1
                   ->  Limit  (cost=0.43..0.46 rows=1 width=3) (actual time=0.024..0.024 rows=0 loops=2)
                         ->  Index Only Scan using idx_1_2_tt on tt  (cost=0.43..95149.36 rows=3333310 width=3) (actual time=0.024..0.024 rows=0 loops=2)
                               Index Cond: (col > (t_1.col)::text)
                               Heap Fetches: 0
 Planning Time: 0.096 ms
 Execution Time: 0.096 ms
(18 行记录)

里面的逻辑是：

(SELECT col FROM tt ORDER BY col LIMIT 1)

　　根节点通过order by 升序 找到最小的一条数据作为起点。

递归查询:

SELECT (SELECT col FROM tt WHERE col > t.col ORDER BY col LIMIT 1)
FROM t
WHERE t.col IS NOT NULL

　　在第一次迭代中，CTE t 包含值'1'。这个查询将在tt表中寻找col大于'1'的最小值。在数据集中，这将是'11'。

　　在第二次迭代，CTE t 将包含'11'。此时，查询将尝试找到大于'11'的最小值，但没有这样的值，所以返回NULL。

递归结束:
　　当递归查询返回NULL时，递归结束。这时，CTE t 将包含'1'和'11'，返回和distinct 一样逻辑的数据。

慢SQL（执行时间 8s ，限制返回 30 行） 

explain analyze 
SELECT GI.ID,
       GI.MODULE_ID,
       GI.BT,
       GI.WH,
       GI.JJCD_TEXT,
       GI.CREATE_DEPTNAME,
       GI.CREATE_TIME,
       GI.MODULE_NAME
FROM gifgifgif GI
         INNER JOIN gufgufguf GUF ON (GUF.ifid = GI.ID)
WHERE GI.ROWSTATE > - 1
  AND (GUF.usid = '0' OR GUF.usid = '210317100256if6gVcTb3Ado1o2ytLs')
  AND ((GI.BT LIKE '%签%') OR (GI.MODULE_NAME LIKE '%签%') OR (GI.WH LIKE '%签%') OR (GI.JJCD_TEXT LIKE '%签%') OR
       (GI.CREATE_DEPTNAME LIKE '%签%'))
ORDER BY GI.CREATE_TIME DESC LIMIT 30;

慢SQL执行计划

                                                                                                                        QUERY PLAN                                                                                                           
             
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
 Limit  (cost=1001.05..17578.06 rows=30 width=240) (actual time=6458.263..8763.733 rows=7 loops=1)
   ->  Gather Merge  (cost=1001.05..3879467.79 rows=7019 width=240) (actual time=6458.261..8763.728 rows=7 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Nested Loop  (cost=0.99..3877631.71 rows=1755 width=240) (actual time=2843.144..8274.217 rows=1 loops=5)
               ->  Parallel Index Scan Backward using gifgifgif_CREATE_TIME1 on gifgifgif GI  (cost=0.43..1158925.09 rows=433728 width=240) (actual time=0.043..2159.037 rows=350466 loops=5)
                     Filter: ((ROWSTATE > '-1'::numeric) AND (((BT)::text ~~ '%签%'::text) OR ((MODULE_NAME)::text ~~ '%签%'::text) OR ((WH)::text ~~ '%签%'::text) OR ((JJCD_TEXT)::text ~~ '%签%'::text) OR ((CREATE_DEPTNAME)::text ~~ '%
%'::text)))
                     Rows Removed by Filter: 423271
               ->  Index Only Scan using idx_gufgufguf_1_2_3 on gufgufguf GUF  (cost=0.56..6.26 rows=1 width=32) (actual time=0.017..0.017 rows=0 loops=1752329)  -- 慢：(1752329/5) * 0.017 / 1000  = 5.95s
                     Index Cond: (ifid = (GI.ID)::text)
                     Filter: (((usid)::text = '0'::text) OR ((usid)::text = '210317100256if6gVcTb3Ado1o2ytLs'::text))
                     Rows Removed by Filter: 3
                     Heap Fetches: 0
 Planning Time: 0.832 ms
 Execution Time: 8763.803 ms
(15 行记录)

我看到这计划简直无语，这种SQL不能 300 ms以内出来就绝对有问题，而且这么简单的语句都能用上并行，真的服。

　　Index Only Scan using idx_gufgufguf_1_2_3 on gufgufguf GUF 每个并行进程执行 5.95s 这也太拉跨了。

看执行计划基本都是用 Index Scan 或者是 Index Only Scan，但是本SQL 谓词过滤条件很多 or ，其实优化器如果执行位图扫描才是最优解计划，但是CBO偏偏没执行！！！

SQL去掉 LIMIT 30限制条件:

explain analyze 
SELECT GI.ID,
       GI.MODULE_ID,
       GI.BT,
       GI.WH,
       GI.JJCD_TEXT,
       GI.CREATE_DEPTNAME,
       GI.CREATE_TIME,
       GI.MODULE_NAME
FROM gifgifgif GI
         INNER JOIN gufgufguf GUF ON (GUF.ifid = GI.ID)
WHERE GI.ROWSTATE > - 1
  AND (GUF.usid = '0' OR GUF.usid = '210317100256if6gVcTb3Ado1o2ytLs')
  AND ((GI.BT LIKE '%签%') OR (GI.MODULE_NAME LIKE '%签%') OR (GI.WH LIKE '%签%') OR (GI.JJCD_TEXT LIKE '%签%') OR
       (GI.CREATE_DEPTNAME LIKE '%签%'))
ORDER BY GI.CREATE_TIME DESC ;

去掉 LIMIT 30限制条件SQL执行计划：

https://www.iqiyi.com/v_2clubpj56m4.html
https://m.iqiyi.com/v_2clubpj56m4.html
https://www.iqiyi.com/v_2bh6kmrmq9w.html
https://m.iqiyi.com/v_2bh6kmrmq9w.html
                                                                                                                   QUERY PLAN                                                                                                           
             
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
 Gather Merge  (cost=98222.89..99026.61 rows=6792 width=240) (actual time=33.640..35.974 rows=7 loops=1)
   Workers Planned: 3
   Workers Launched: 3
   ->  Sort  (cost=97222.85..97228.51 rows=2264 width=240) (actual time=26.724..26.725 rows=2 loops=4)
         Sort Key: GI.CREATE_TIME DESC
         Sort Method: quicksort  Memory: 25kB
         Worker 0:  Sort Method: quicksort  Memory: 25kB
         Worker 1:  Sort Method: quicksort  Memory: 25kB
         Worker 2:  Sort Method: quicksort  Memory: 26kB
         ->  Nested Loop  (cost=510.90..97096.70 rows=2264 width=240) (actual time=11.118..26.693 rows=2 loops=4)
               ->  Parallel Bitmap Heap Scan on gufgufguf GUF  (cost=510.35..59045.81 rows=5049 width=32) (actual time=0.480..3.498 rows=1178 loops=4)
                     Recheck Cond: (((usid)::text = '0'::text) OR ((usid)::text = '210317100256if6gVcTb3Ado1o2ytLs'::text))
                     Heap Blocks: exact=1464
                     ->  BitmapOr  (cost=510.35..510.35 rows=15652 width=0) (actual time=1.567..1.568 rows=0 loops=1)
                           ->  Bitmap Index Scan on gufgufguf_usid  (cost=0.00..251.26 rows=7826 width=0) (actual time=0.022..0.022 rows=0 loops=1)
                                 Index Cond: ((usid)::text = '0'::text)
                           ->  Bitmap Index Scan on gufgufguf_usid  (cost=0.00..251.26 rows=7826 width=0) (actual time=1.545..1.545 rows=4713 loops=1)
                                 Index Cond: ((usid)::text = '210317100256if6gVcTb3Ado1o2ytLs'::text)
               ->  Index Scan using gifgifgif_PKEY1 on gifgifgif GI  (cost=0.56..7.54 rows=1 width=240) (actual time=0.019..0.019 rows=0 loops=4713)
                     Index Cond: ((ID)::text = (GUF.ifid)::text)
                     Filter: ((ROWSTATE > '-1'::numeric) AND (((BT)::text ~~ '%签%'::text) OR ((MODULE_NAME)::text ~~ '%签%'::text) OR ((WH)::text ~~ '%签%'::text) OR ((JJCD_TEXT)::text ~~ '%签%'::text) OR ((CREATE_DEPTNAME)::text ~~ '%
%'::text)))
                     Rows Removed by Filter: 1
 Planning Time: 0.815 ms
 Execution Time: 36.060 ms
(24 行记录)

可以看到去掉LIMIT 30 以后，CBO能正常使用上 Bitmap Index Scan + BitmapOr 的查询策略，SQL只需要 36ms就能跑出结果。

PG比较牛逼的地方是B+树索引能基于SQL的查询条件，自动能转换成位图索引的查询策略。

像这种情况就简单了，只需要改变下限制SQL返回条数的逻辑即可，kingbase也兼容Oracle rownum 的语法，我们可以将上面SQL等价改成 rownum 来优化。

LIMIT 改写成 rownum ：

explain analyze 
SELECT * FROM (
SELECT GI.ID,
       GI.MODULE_ID,
       GI.BT,
       GI.WH,
       GI.JJCD_TEXT,
       GI.CREATE_DEPTNAME,
       GI.CREATE_TIME,
       GI.MODULE_NAME
FROM gifgifgif GI
         INNER JOIN gufgufguf GUF ON (GUF.ifid = GI.ID)
WHERE GI.ROWSTATE > - 1
  AND (GUF.usid = '0' OR GUF.usid = '210317100256if6gVcTb3Ado1o2ytLs')
  AND ((GI.BT LIKE '%签%') OR (GI.MODULE_NAME LIKE '%签%') OR (GI.WH LIKE '%签%') OR (GI.JJCD_TEXT LIKE '%签%') OR
       (GI.CREATE_DEPTNAME LIKE '%签%'))
ORDER BY GI.CREATE_TIME DESC) WHERE ROWNUM <= 30;

LIMIT 改写成 rownum 执行计划：

                                                                                                                           QUERY PLAN                                                                                                        
                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------
 Count  (cost=98222.89..99162.45 rows=0 width=240) (actual time=31.418..33.691 rows=7 loops=1)
   Stop Keys: (ROWNUM <= 30)
   ->  Gather Merge  (cost=98222.89..99026.61 rows=6792 width=240) (actual time=31.415..33.686 rows=7 loops=1)
         Workers Planned: 3
         Workers Launched: 3
         ->  Sort  (cost=97222.85..97228.51 rows=2264 width=240) (actual time=26.497..26.498 rows=2 loops=4)
               Sort Key: GI.CREATE_TIME DESC
               Sort Method: quicksort  Memory: 25kB
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               Worker 1:  Sort Method: quicksort  Memory: 27kB
               Worker 2:  Sort Method: quicksort  Memory: 25kB
               ->  Nested Loop  (cost=510.90..97096.70 rows=2264 width=240) (actual time=14.246..26.465 rows=2 loops=4)
                     ->  Parallel Bitmap Heap Scan on gufgufguf GUF  (cost=510.35..59045.81 rows=5049 width=32) (actual time=0.513..3.401 rows=1178 loops=4)
                           Recheck Cond: (((usid)::text = '0'::text) OR ((usid)::text = '210317100256if6gVcTb3Ado1o2ytLs'::text))
                           Heap Blocks: exact=1373
                           ->  BitmapOr  (cost=510.35..510.35 rows=15652 width=0) (actual time=1.664..1.664 rows=0 loops=1)
                                 ->  Bitmap Index Scan on gufgufguf_usid  (cost=0.00..251.26 rows=7826 width=0) (actual time=0.024..0.024 rows=0 loops=1)
                                       Index Cond: ((usid)::text = '0'::text)
                                 ->  Bitmap Index Scan on gufgufguf_usid  (cost=0.00..251.26 rows=7826 width=0) (actual time=1.639..1.639 rows=4713 loops=1)
                                       Index Cond: ((usid)::text = '210317100256if6gVcTb3Ado1o2ytLs'::text)
                     ->  Index Scan using gifgifgif_PKEY1 on gifgifgif GI  (cost=0.56..7.54 rows=1 width=240) (actual time=0.019..0.019 rows=0 loops=4713)
                           Index Cond: ((ID)::text = (GUF.ifid)::text)
                           Filter: ((ROWSTATE > '-1'::numeric) AND (((BT)::text ~~ '%签%'::text) OR ((MODULE_NAME)::text ~~ '%签%'::text) OR ((WH)::text ~~ '%签%'::text) OR ((JJCD_TEXT)::text ~~ '%签%'::text) OR ((CREATE_DEPTNAME)::text 
~~ '%签%'::text)))
                           Rows Removed by Filter: 1
 Planning Time: 0.897 ms
 Execution Time: 33.778 ms
(26 行记录)

可以看到SQL通过将LIMIT 改写成 rownum 以后，原来执行时间 8s 降低到 33ms 就能跑出结果了，本条SQL到此已经优化完毕。

2401_83442335
关注
  • 16
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL中索引优化distinct语句及distinct的多字段操作
09-10
主要介绍了MySQL中索引优化distinct语句及distinct的多字段操作方法,distinct语句去重功能的使用是MySQL入门学习中的基础知识,需要的朋友可以参考下
PG中的查询：2.统计--（1）
帅的数说
03-15 770
本节讨论成本优化器的基础：统计。通过示例进行讲解。这里会由很多执行计划，后续会更加详细讨论这些计划如何运行。现在只需要注意每个计划的第一行看到的数字以及行数。这些是行数估计值。基本统计pg...
distinct xx和count(distinct xx)的变态递归优化方法 - 收敛(skip scan)扫描
weixin_34004750的博客
12-21 196
标签 PostgreSQL , 递归去重 , 递归优化 , count(distinct ), 稀疏列 , 统计 背景 今天要说的这个优化是从前面一篇讲解《performance tuning case :use cursor or trigger replace group by and order by》 http://blog.163.com/...
PostgreSQL利用递归优化求稀疏列唯一值
Focus on PostgreSQL
01-19 1613
在数据库中经常会碰到一些表的列是稀疏列，只有很少的值，例如性别字段，一般就只有2种不同的值。 但是当我们求这些稀疏列的唯一值时，如果表的数据量很大，速度还是会很慢。 例如： 创建测试表 bill=# create table t_sex (sex char(1), otherinfo text); CREATE TABLE bill=# insert into t_sex select 'm', generate_series(1,10000000)||'this is test'; INSERT 0 10
postgresql etc功能改写Oracle start with connect by递归功能
杀不死我的终将使我强大
01-27 3346
目前已做修改的三种例子： 1、start with connect by改写 SELECT t.* FROM pub_organ t, ( SELECT organ_id FROM ( –原来的写法 – SELECT – p.organ_id, – p.organ_type – FROM – pub_organ p START WITH p.organ_id = ‘370000’ CONNECT BY PRIOR p.organ_id = p.parent_id 改写成PG的： with
PostgreSQL Oracle 兼容性之 - INDEX SKIP SCAN (递归查询变态优化) 非驱动列索引扫描优化...
weixin_34355715的博客
04-18 471
标签 PostgreSQL , Oracle , index skip scan , 非驱动列条件 , 递归查询 , 子树 背景 对于输入条件在复合索引中为非驱动列的，如何高效的利用索引扫描？ 在Oracle中可以使用index skip scan来实现这类CASE的高效扫描： INDEX跳跃扫描一般用在WHERE条件里面没有使用到引导列，但是用到了...
oracle有没有稀疏列,PostgreSQL利用递归优化求稀疏列唯一值的方法
weixin_29016833的博客
04-07 110
在数据库中经常会碰到一些表的列是稀疏列，只有很少的值，例如性别字段，一般就只有2种不同的值。但是当我们求这些稀疏列的唯一值时，如果表的数据量很大，速度还是会很慢。例如：创建测试表bill=# create table t_sex (sex char(1), otherinfo text);CREATE TABLEbill=# insert into t_sex select 'm', genera...
PG内核分析 Question and Answer
liuhhaiffeng的专栏
06-02 1357
PG内核分析 Question and Answer PG系统概述 为什么说PG是一种先进的对象—关系数据库系统 因为PG它不仅支持关系数据库的各种功能, 而且还具备类, 继承等对象数据库的特征. 面向对象数据库技术可望成为继关系数据库技术之后的新一代数据管理技术。 它是一种以关系数据库和SQL为基础, 扩展了抽象数据类型, 从而具备面向对象特征的数据库. PG不是完全的对象数据库, 而是综合了在关系数据库的基础上, 吸收了对象数据库的优点发展起来的. 对象数据库的缺点参见下面所述: 参考: 面向对象数据
＜PostgreSQL数据库内核分析＞之第五章：查询编译
喜欢打篮球的普通人
02-10 1070
文章目录一、概述二、查询分析 一、概述 查询编译的主要任务是根据用户的查询语句生成数据库中最优的执行计划 查询执行过程中所涉及的各个模块说明如下： 若是简单的命令(建表、创建用户、备份等)则将其分配到功能性命令处理模块 负责命令(SELECT\INSERT\DELETE\UPDATE)则要为其构建查询树(Query结构体)，然后交给查询重写模块 查询重写模块接收到查询树后，按照该查询所涉及的规则和视图对查询树进行重写，生成新的查询树 生成路径模块依据重写过的查询树，考虑关系的访问方式、连接方式和连接顺序等
如何从Oracle迁移到Greenplum 第三篇
Greenplum中文社区
05-14 344
在过去的两周里，我们的《如何从Oracle迁移到Greenplum》系列文章分别介绍了《Greenplum和Oracle的产品对比》，《迁移效果的...
分析MySQL中优化distinct的技巧
12-15
有这样的一个需求：select count(distinct nick) from user_access_xx_xx; 这条sql用于统计用户访问的uv，由于单表的数据量在10G以上，即使在user_access_xx_xx上加上nick的索引， 通过查看执行计划，也为全索引扫描...
oracle中使用group by优化distinct
12-16
今天mentor给了一个sql语句优化的任务。（环境是sql developer）有一个语句执行很慢，查询出来的结果有17544条记录，但需970秒，速度很慢。语句是这样的： SELECT DISTINCT 'AMEND_NEW', reporttitle, ...
Hive Sql – Multi Distinct（多个distinct在同一个query中） 优化
01-07
优化点 1、如果不要求精确值，可以使用 spark-sql approx_count_distinct函数 （基数计数 hyperloglog） 2、修改SQL 基础数据准备如下， 需要计算 不同渠道下的 不同周期 的访问uv presto:bi> desc tmp.multi_...
【DISTINCT】优化之MySQL官方文档翻译
10-01
【DISTINCT】优化之MySQL官方文档翻译
MySQL关键字Distinct的详细介绍
01-19
MySQL关键字Distinct用法介绍 DDL Prepare SQL： create table test(id bigint not null primary key auto_increment, name varchar(10) not null, phone varchar(10) not null, email varchar(30) not null)...
MySQL DISTINCT 的基本实现原理详解
12-16
DISTINCT 实际上和 GROUP BY 操作的实现非常相似，只不过是在 GROUP BY 之后的每组中只取出一条记录而已。所以，DISTINCT 的实现和 GROUP BY 的实现也基本差不多，没有太大的区别。同样可以通过松散索引扫描或者是...
MySQL中distinct和count(*)的使用方法比较
12-15
首先对于MySQL的DISTINCT的关键字的一些用法： 1．在count 不重复的记录的时候能...3．上面的情况2对于需要返回mysql表中2列以上的结果时会有歧义，比如SELECT DISTINCT id， type FROM tablename；实际上返回的是 i
distinct 优化
最新发布
09-21
对于优化 distinct 操作，有几个常见的方法可以考虑： 1. 使用索引：在数据库中为需要去重的列创建索引，这样可以加速去重操作的速度。如果数据量很大，可以考虑使用哈希索引或位图索引来提高性能。 2. 使用临时表：可以将需要去重的列数据插入到一个临时表中，然后使用 SELECT DISTINCT 查询临时表来获取去重后的结果。临时表可以利用内存或者磁盘空间来存储数据，具体使用哪种方式取决于数据量和系统资源。 3. 使用窗口函数：在支持窗口函数的数据库中，可以使用类似于 ROW_NUMBER() OVER (PARTITION BY column ORDER BY column) 的语法来实现去重操作。窗口函数可以更灵活地操作数据，并且性能通常比使用 DISTINCT 更好。 4. 使用聚合函数：在某些情况下，可以通过使用聚合函数（如 MAX、MIN 或 SUM）来实现去重操作。例如，如果只关心某列的最大或最小值，可以使用 MAX 或 MIN 函数进行聚合并得到去重后的结果。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值