金仓数据库KingabseES优化SQL_关联倾斜数据

最新推荐文章于 2024-07-13 18:10:19 发布
最新推荐文章于 2024-07-13 18:10:19 发布
阅读量94 收藏
点赞数
文章标签： 金仓数据库 kingbase 数据库 人大金仓 DBA
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/arthemis_14/article/details/130493506
版权

背景

对于两个大表关联的场景，如果过滤条件的列值，存在高度倾斜，可以考虑根据反向滤值，进行过滤操作，减少连接的CPU时间。

数据准备

-- 状态表 tp01_state 记录 大表tp01 记录的多种状态 

kingbase=# select count(*) from tp01;
 count
----------
10000000
(1 行记录)

--只有一个高度倾斜的列值
kingbase=# select issuc,count(*) from tp01_state group by issuc order by issuc;

issuc |  count
-------+---------
N     |     100
Y     | 9999900
(2 行记录)

--有多个高度倾斜的列值
kingbase=# select istype, count(*) from tp01_state group by istype order by istype;
istype |  count
--------+---------
A      |     100
C      | 8999700
G      |     100
M      | 1000000
W      |     100
(5 行记录)

查询issuc='Y'数据

标准语句

多数数据匹配issuc='Y'条件，执行计划就是两个大表，进行hashjoin。

select *
from tp01
where id in (select id from tp01_state where issuc = 'Y');
--或者
select *
from tp01
where exists(select 1 from tp01_state where id = tp01.id and issuc = 'Y');

-- QUERY PLAN
Hash Semi Join  (cost=338555.00..1033383.15 rows=10000000 width=241) (actual time=2398.867..5889.537 rows=9999900 loops=1)
  Hash Cond: (tp01.id = tp01_state.id)
  ->  Seq Scan on tp01  (cost=0.00..444828.12 rows=10000012 width=241) (actual time=0.005..611.596 rows=10000000 loops=1)
  ->  Hash  (cost=213555.00..213555.00 rows=10000000 width=4) (actual time=2384.857..2384.858 rows=9999900 loops=1)
        Buckets: 16777216  Batches: 1  Memory Usage: 482631kB
        ->  Seq Scan on tp01_state  (cost=0.00..213555.00 rows=10000000 width=4) (actual time=0.011..775.853 rows=9999900 loops=1)
              Filter: (issuc = 'Y'::text)
              Rows Removed by Filter: 100
Planning Time: 0.186 ms
Execution Time: 6137.233 ms

优化1：使用NOT IN 代替 IN

因为只有少量数据，匹配issuc='Y'反向条件，使用not in 减少大表的过滤操作。

select *
from tp01
where tp01.id not in (select id from tp01_state where issuc <> 'Y' or issuc is null);

-- QUERY PLAN
Seq Scan on tp01  (cost=213555.00..683383.15 rows=5000006 width=241) (actual time=517.554..1629.795 rows=9999900 loops=1)
  Filter: (NOT (hashed SubPlan 1))
  Rows Removed by Filter: 100
  SubPlan 1
    ->  Seq Scan on tp01_state  (cost=0.00..213555.00 rows=1 width=4) (actual time=271.143..517.503 rows=100 loops=1)
          Filter: (issuc <> 'Y'::text)
          Rows Removed by Filter: 9999900
Planning Time: 0.087 ms
Execution Time: 1870.376 ms

优化2：使用not between 代替 <>

not between 操作可以使用索引，就可以减少子查询的执行时间。

select *
from tp01
where tp01.id not in (select id from tp01_state where issuc not between 'Y' and 'Y' or issuc is null);

-- QUERY PLAN
Seq Scan on tp01  (cost=17.35..469845.50 rows=5000006 width=241) (actual time=0.098..1109.085 rows=9999900 loops=1)
  Filter: (NOT (hashed SubPlan 1))
  Rows Removed by Filter: 100
  SubPlan 1
    ->  Bitmap Heap Scan on tp01_state  (cost=13.33..17.34 rows=1 width=4) (actual time=0.035..0.045 rows=100 loops=1)
          Recheck Cond: ((issuc < 'Y'::text) OR (issuc > 'Y'::text) OR (issuc IS NULL))
          Heap Blocks: exact=2
          ->  BitmapOr  (cost=13.33..13.33 rows=1 width=0) (actual time=0.028..0.030 rows=0 loops=1)
                ->  Bitmap Index Scan on tp01_state_issuc  (cost=0.00..4.44 rows=1 width=0) (actual time=0.020..0.020 rows=100 loops=1)
                      Index Cond: (issuc < 'Y'::text)
                ->  Bitmap Index Scan on tp01_state_issuc  (cost=0.00..4.44 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1)
                      Index Cond: (issuc > 'Y'::text)
                ->  Bitmap Index Scan on tp01_state_issuc  (cost=0.00..4.44 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
                      Index Cond: (issuc IS NULL)
Planning Time: 0.109 ms
Execution Time: 1349.526 ms

查询istype in ('C','M')数据

标准语句

多数数据匹配istype in ('C','M')条件，执行计划就是两个大表，进行hashjoin。

explain analyze
select *
from tp01
where id in (select id from tp01_state where istype in ('C', 'M'));

-- QUERY PLAN
Hash Semi Join  (cost=307305.11..927445.94 rows=7500009 width=241) (actual time=2848.058..6398.654 rows=9999700 loops=1)
  Hash Cond: (tp01.id = tp01_state.id)
  ->  Seq Scan on tp01  (cost=0.00..444828.12 rows=10000012 width=241) (actual time=0.005..613.502 rows=10000000 loops=1)
  ->  Hash  (cost=213555.00..213555.00 rows=7500009 width=4) (actual time=2840.972..2840.972 rows=9999700 loops=1)
        Buckets: 16777216 (originally 8388608)  Batches: 1 (originally 1)  Memory Usage: 482624kB
        ->  Seq Scan on tp01_state  (cost=0.00..213555.00 rows=7500009 width=4) (actual time=0.034..1032.910 rows=9999700 loops=1)
              Filter: (istype = ANY ('{C,M}'::text[]))
              Rows Removed by Filter: 300
Planning Time: 0.193 ms
Execution Time: 6646.452 ms

优化1：使用NOT IN 代替 IN

因为只有少量数据，匹配istype in ('C','M')反向条件，使用not in 减少大表的过滤操作。

select *
from tp01
where id not in (select id from tp01_state where istype not in ('C', 'M') or istype is null );

-- QUERY PLAN
Seq Scan on tp01  (cost=175497.98..645326.13 rows=5000006 width=241) (actual time=778.116..2699.271 rows=9999700 loops=1)
  Filter: (NOT (hashed SubPlan 1))
  Rows Removed by Filter: 300
  SubPlan 1
    ->  Seq Scan on tp01_state  (cost=0.00..169248.00 rows=2499991 width=4) (actual time=0.006..767.589 rows=300 loops=1)
          Filter: ((istype <> ALL ('{C,M}'::text[])) OR (istype IS NULL))
          Rows Removed by Filter: 9999700
Planning Time: 0.101 ms
Execution Time: 2934.265 ms

优化2：使用not between 代替 <>

not between 操作根据选择率最佳的列值，使用索引，就可以减少子查询的执行时间。

select *
from tp01
where id not in (select id from tp01_state
where (istype not between 'C' and 'C' and istype not between 'M' and 'M') or istype is null);


-- QUERY PLAN
Seq Scan on tp01  (cost=106721.48..576549.63 rows=5000006 width=241) (actual time=223.295..1862.006 rows=9999700 loops=1)
  Filter: (NOT (hashed SubPlan 1))
  Rows Removed by Filter: 300
  SubPlan 1
    ->  Bitmap Heap Scan on tp01_state  (cost=22927.80..104507.84 rows=885454 width=4) (actual time=58.615..220.275 rows=300 loops=1)
          Recheck Cond: (((istype < 'C'::text) OR (istype > 'C'::text)) OR (istype IS NULL))
          Filter: ((((istype < 'C'::text) OR (istype > 'C'::text)) AND ((istype < 'M'::text) OR (istype > 'M'::text))) OR (istype IS NULL))
          Rows Removed by Filter: 1000000
          Heap Blocks: exact=4428
          ->  BitmapOr  (cost=22927.80..22927.80 rows=981652 width=0) (actual time=58.266..58.268 rows=0 loops=1)
                ->  BitmapOr  (cost=22701.99..22701.99 rows=981652 width=0) (actual time=58.262..58.263 rows=0 loops=1)
                      ->  Bitmap Index Scan on tp01_state_istype  (cost=0.00..5.69 rows=167 width=0) (actual time=0.026..0.027 rows=100 loops=1)
                            Index Cond: (istype < 'C'::text)
                      ->  Bitmap Index Scan on tp01_state_istype  (cost=0.00..22253.58 rows=981486 width=0) (actual time=58.235..58.235 rows=1000200 loops=1)
                            Index Cond: (istype > 'C'::text)
                ->  Bitmap Index Scan on tp01_state_istype  (cost=0.00..4.44 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (istype IS NULL)
Planning Time: 0.350 ms
Execution Time: 2099.544 ms

优化3：使用<和>的范围条件组合，代替not between

将多个not between条件，分解成范围条件组合，充分利用索引，减少filter操作。

select *
from tp01
where id not in (
    select id
    from tp01_state
    where (istype < 'C')
       or (istype > 'C' and istype < 'M')
       or (istype > 'M')
       or istype is null);

-- QUERY PLAN
Seq Scan on tp01  (cost=350.11..470178.26 rows=5000006 width=241) (actual time=0.142..1099.829 rows=9999700 loops=1)
  Filter: (NOT (hashed SubPlan 1))
  Rows Removed by Filter: 300
  SubPlan 1
    ->  Bitmap Heap Scan on tp01_state  (cost=8.60..349.28 rows=334 width=4) (actual time=0.067..0.091 rows=300 loops=1)
          Recheck Cond: ((istype < 'C'::text) OR ((istype > 'C'::text) AND (istype < 'M'::text)) OR (istype > 'M'::text) OR (istype IS NULL))
          Heap Blocks: exact=2
          ->  BitmapOr  (cost=8.60..8.60 rows=334 width=0) (actual time=0.058..0.060 rows=0 loops=1)
                ->  Bitmap Index Scan on tp01_state_istype  (cost=0.00..2.69 rows=167 width=0) (actual time=0.019..0.019 rows=100 loops=1)
                      Index Cond: (istype < 'C'::text)
                ->  Bitmap Index Scan on tp01_state_istype  (cost=0.00..1.45 rows=1 width=0) (actual time=0.024..0.024 rows=100 loops=1)
                      Index Cond: ((istype > 'C'::text) AND (istype < 'M'::text))
                ->  Bitmap Index Scan on tp01_state_istype  (cost=0.00..2.69 rows=167 width=0) (actual time=0.014..0.014 rows=100 loops=1)
                      Index Cond: (istype > 'M'::text)
                ->  Bitmap Index Scan on tp01_state_istype  (cost=0.00..1.44 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
                      Index Cond: (istype IS NULL)
Planning Time: 0.183 ms
Execution Time: 1340.184 ms

总结

查询优化的宗旨，是更少的数据量和更少计算量，不要摒弃 not in这样不易优化的操作符。 更多信息，参见https://help.kingbase.com.cn/v8/index.html

沉舟侧畔千帆过_
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
人大金仓数据库sql语句_人大金仓数据库问题记录
weixin_39668408的博客
12-21 1166
1.人大金仓进入web管理平台浏览器无法访问。在tomcat的server.xml中的Connector下添加两个参数sslEnabledProtocols=“TLSv1,TLSv1.1,TLSv1.2”ciphers=“TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256, TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA,TLS_ECDHE_RSA_W...
人大金仓数据库sql语句_人大金仓数据库总结（SQL和JDBC）
weixin_39675178的博客
12-21 4704
人大金仓作为一款国产数据库，使用的人数和相关资料都比较少。最近使用人大金仓数据库，做了个简单的SQL和java jdbc的测试，希望能够供大家参考。1、SQL语句创建表：CREATE TABLE "PUBLIC"."TB_SYS_CONFIGURE"("ATTR_KEY" VARCHAR (100) NOT NULL ,"ATTR_VALUE" VARCHAR (100) NOT NULL);添加...
人大金仓KingbaseES SQL优化篇-如何减少耗时查询的调用次数
arthemis_14的博客
01-11 1029
使用临时表的结果进行连接，避免循环。注意，这里CTE 有materilaized 选项，主要是把耗时的部分先执行出结果，避免与其他部分查询合并，引发多次执行。在查询语句中，如果 Select 子句中，调用较为耗时的函数或子查询，比较各种SQL语句的编写方案，得出其中最优选择。使用子查询和 LATERAL 连接，可以避免结果集的重复计算。CTE子句，先计算全部结果，然后关联主表，总时长是可控。三、查询局部数据（过滤主表的条件）的优化方案。四、查询局部数据（过滤从表的条件）的优化方案。
KingbaseES的查询优化器简介
Leo_Sui的博客
10-01 216
¶选择：与SQL查询的WHERE子句对应投影：与SQL查询的SELECT子句对应卡氏积：与SQL查询的FROM子句的关系列表对应连接：与SQL查询中的JOIN、NATURAL JOIN和OUTER JOIN对应，并、交和差：与SQL的操作符UNION、INTERSECT、EXCEPT相对应消除重复：与SQL SELECT子句中的关键字DISTINCT对应，SQL的操作符UNION、INTERSECT、EXCEPT默认消除重复分组：与SQL查询中的GROUP BY对应排序。
[运维|数据库] 数据库迁移到金仓数据库时，sys_user表报错原因
梦醒贰零壹柒
09-25 1339
数据库中存在表sys_user，人大金仓数据库存在系统表sys_user，表冲突导致代码中查询的时候查的是系统表sys_user，而不是我们建的表。解决方法：设置普通用户忽略系统表。
人大金仓数据库用户手册
04-16
人大金仓数据库用户手册知识点汇总 人大金仓数据库用户手册是 KingbaseES V8 数据库管理系统的官方指导手册，旨在帮助用户快速了解和掌握 KingbaseES V8 的使用和管理。下面是人大金仓数据库用户手册中的关键知识点...
Linux下shell连接金仓数据库（ksql）并完成查询、插入、修改表数据实例
07-15
Linux下连接金仓数据库（ksql）并完成查询、插入、修改表数据实例，修改后可用于oracle及mysql
人大金仓数据库SQL手册
07-27
人大金仓数据库SQL手册： 详细讲解SQL语法
Qt5.6.1 访问金仓数据库驱动 window64_linux64版本
02-21
QT数据库驱动插件必须放在特殊的子目录下（如plugins/xxx） QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL"); db.setHostName("acidalia"); db.setDatabaseName("customdb"); db.setUserName("mojito"); ...
set user & password for database 设置数据库 用户和密码
DavidsonGong的博客
07-12 138
USE sbms;
7.10飞书一面面经
东篱君的博客
07-10 1167
B+树的⾮叶⼦节点不存放实际的记录数据，仅存放索引，所以数据量相同的情况下，相⽐存储即存索引⼜存记录的 B 树，B+树的⾮叶⼦节点可以存放更多的索引，因此 B+ 树可以⽐ B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。主从复制：单节点Redis的并发能力是有上限的，要进一步提高Redis的并发能力，可以搭建主从集群，实现读写分离。B+ 树有⼤量的冗余节点（所有⾮叶⼦节点都是冗余索引），这些冗余索引让 B+ 树在插⼊、删除的效率都更⾼，⽐如删除根节点的时候，不会像 B 树那样会发⽣复杂的树的变化；
Redis集群和高可用
最新发布
xiaogengtongxu的博客
07-13 325
主从架构无法实现master和slave角色的自动切换，即当master出现redis服务异常、主机断电、磁盘损坏等问题导致master无法使用，而redis主从复制无法实现自动的故障转移(将slave 自动提升为新master)，需要手动修改环境配置,才能切换到slave redis服务器，另外当单台Redis服务器性能无法满足业务写入需求的时候,也无法横向扩展Redis服务的并行写入性能。master和slave角色的无缝切换，让业务无感知从而不影响业务使用。
mysql笔记1
m0_62975692的博客
07-11 343
首先查看systemctl status mysqld，如果是关闭的可以进入的配置文件打开mysql，我的mysql配置文件就是在/usr/local/mysql/support-filess/下，在此目录下执行./mysql.server start,打开mysql服务，也可以通过systmectl start mysqld，如果你嫌麻烦的话可以systemctl enable mysqld 一直打开mysql的服务;重新给字段添加约束的时候不可以添加not null约束的！
MySQL空间索引
你的指尖有改变世界的力量
07-10 264
空间类型是建立在空间类型字段上的。
docker-compose安装PolarDB-PG数据库
wnfff的博客
07-12 245
docker-compose安装PolarDB-PG数据库
Redis数据结构-String篇
时间如瑾 代码如诗
07-12 670
对⼀个内部表示成long型的string执行append, setbit, getrange这些命令，针对的仍然是string的值（即⼗进制表示的字符串），而不是针对内部表⽰的long型进⾏操作。因此，在这些命令的实现中，会把long型先转成字符串再进行相应的操作。String 的内部存储结构⼀般是 sds（Simple Dynamic String，可以动态扩展内存），但是如果⼀个String 类型的 value 的值是数字，那么 Redis 内部会把它转成 long 类型来存储，从⽽减少内存的使用。
系统架构设计师教程 第二章 计算机系统基础知识-2.3计算机软件
Remon的专栏
07-09 756
《系统架构设计师教程》清华第二版 2.3计算机软件 章节的详细解读
CH-benCHmark：一个HTAP数据库测试基准
算力测评
07-09 265
这一基准测试，旨在弥合TPC-C（专注于联机事务处理，即OLTP）与TPC-H（侧重于联机分析处理，即OLAP）这两项业界权威但单一的工作负载测试之间的鸿沟。它巧妙地结合了TPC-C的订单录入流程产生的交易负载，以及与之对应的、等同于TPC-H级别的OLAP查询套件，并在单一数据库系统内的同一数据集上并行执行。正因为继承自两个最广泛应用的TPC标准，CH-benCHmark的测试结果不仅对混合负载系统具有重大参考价值，同时也适用于传统的单一工作负载场景。
人大金仓数据库FIND_IN_SET函数怎么使用
08-31
```sql FIND_IN_SET(search_value, comma_separated_string_list) ``` 其中，search_value是要查找的值，comma_separated_string_list是逗号分隔的字符串列表。 FIND_IN_SET函数会返回search_value在comma_...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值