基于金仓数据库KingbaseES的SQL优化

金仓数据库

已于 2022-03-01 10:06:35 修改

阅读量1.1k

点赞数 1

文章标签：数据库 sql database

于 2022-03-01 10:01:08 首次发布

本文链接：https://blog.csdn.net/Kingbase_/article/details/123198857

版权

本文介绍了如何在KingbaseES数据库中使用子查询和窗口函数解决“按照资讯分类分组，取每组的前3条资讯信息列表”的问题。通过数据准备、SQL实现及查询计划分析，展示了两种不同方法，包括利用窗口函数的分组排名和按分类取第N名记录的SQL语句，以及对应的查询效率比较。

摘要由CSDN通过智能技术生成

一、前言
分组查询是常见的SQL查询语句。SQL分组功能主要通过GROUP BY关键字来实现，并且GROUP BY通常需要配合聚合函数来使用。比如说，分组之后可以计数（COUNT），求和（SUM），求平均数（AVG）等。
本文探讨的不是GROUP BY关键字的学习和使用，而是一种另类的“分组”查询。
有这样一个功能需求：系统中存在资讯信息模块，用于发布一些和业务相关的活动动态，其中每条资讯信息都有一个所属类型（如科技类的资讯、娱乐类、军事类•••）和浏览量字段。官网上需要滚动展示一些热门资讯信息列表（浏览量越大代表越热门），而且每个类别的相关资讯记录至多显示3条，换句话：“按照资讯分类分组，取每组的前3条资讯信息列表”。
尝试使用GROUP BY的各种方式都不能实现，最后通过使用窗口函数获得了解决方法，即子查询。
下面，将模拟一些实际的测试数据重现问题的解决过程。

二、数据准备
1．表设计

新闻分类表:
id 主键
name 分类名称
新闻信息记录表:
id 主键
title 资讯名称
views 浏览量
info_type_id 资讯类别
code 说明

初始化SQL语句:
set work_mem to ‘1GB’;
Create table info(
id numeric not null primary key ,
title varchar(100) ,
Viewnum numeric ,
info_type_id numeric ,
Code text
);
create index info_infotypeid on info (info_type_id);
Create table info_type(
Id numeric not null primary key,
Name varchar(100)
);
–插入100个新闻分类
Insert into info_type
select id, ‘TYPE’ || lpad(id::text, 5, ‘0’ ) from generate_series(1, 100) id;

–插入1000000个新闻
Insert into info_type
select id, ‘TTL’ || lpad(id::text, 20, ‘0’ ) title, ceil(random()*10000) views, ceil(random()*10000) info_type_id , md5(id) code
from generate_series(1, 1000000) id;

vacuum analyse info_type,info;

2．核心思想
通常我们在取前N条记录时候，都是根据某个业务字段进行降序排序，然后取前N条。
例如“select * from info order by views asc limit 3”，这条SQL就是取info表中的前3条记录。但是“它是在每个类型下都要取浏览量的前3条记录”。
另一种比较简单粗暴的方式是在Java代码中循环所有的资讯类型，取出每个类型的前3条记录，最后进行汇总。但是，这种方式虽然也能实现我们的要求，但存在严重的弊端，即有可能发送多次（成百上千次也有可能）sql语句。这种程序显然是有重大缺陷的。
如果能够在查询每条资讯记录时能查出其所在类型的排名，然后根据排名字段进行过滤，是否也能解决该问题？这时候我们就想到了子查询，并且MySQL是可以实现这样的功能子查询的。
要计算出某条资讯信息在同资讯分类下所有记录中排第几名，换算出有多少条浏览量比当前记录的浏览量高，然后根据具体的多少（N）条+1就是N+1就是当前记录所在其分类下的的排名。
假如以本文上面的示例数据说明：就是在计算每个资讯信息记录时，多计算出一列作为其“排名”字段，然后取“排名”字段的小于等于3的记录即可。
如果这里还不是很理解的话，就先看下面的SQL，然后根据SQL再回过头来理解这段话。

三、SQL实现
1．方法一：分组排名，取前3名记录

explain (analyse ,buffers )
with i as (
select i.*,
row_number() over (partition by i.info_type_id order by i.viewnum desc) sn
from info i)
select *
from info_type t
left join i on i.sn <= 3 and i.info_type_id = t.id;

                                                             QUERY PLAN

Merge Right Join (cost=122990.46…123343.08 rows=3341 width=96) (actual time=1634.866…1642.284 rows=200 loops=1)
Merge Cond: (i.info_type_id = t.id)
Buffers: shared hit=13325
-> Subquery S