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

金小仓

1470

10页

18次

2022-03-01

免费下载

基于金仓数据库

KingbaseES

的

SQL

优化

一、前言

分组查询是常见的

SQL

查询语句。

SQL

分组功能主要通过

GROUP BY

关键字来实现

并且

GROUP BY

通常需要配合聚合函数来使用。比如说，分组之后可以计数

（

COUNT

），求和（

SUM

），求平均数（

AVG

）等。

本文探讨的不是

GROUP BY

关键字的学习和使用，而是一种另类的“分组”查询。

有这样一个功能需求：系统中存在资讯信息模块，用于发布一些和业务相关的活动动

态，其中每条资讯信息都有一个所属类型（如科技类的资讯、娱乐类、军事类

···

）和浏览

量字段。官网上需要滚动展示一些热门资讯信息列表（浏览量越大代表越热门），而且每

个类别的相关资讯记录至多显示

条，换句话：“按照资讯分类分组，取每组的前

条资讯

信息列表”。

尝试使用

GROUP BY

的各种方式都不能实现，最后通过使用窗口函数获得了解决方法，

即子查询。

下面，将模拟一些实际的测试数据重现问题的解决过程。

二、数据准备

．表设计

新闻分类表

主键

name

分类名称

新闻信息记录表

主键

title

资讯名称

views

浏览量

info_type_id

资讯类别

code

说明

初始化

SQL

语句

set work_mem to '1GB';

Create table info(

id numeric not null primary key ,

titlevarchar(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;

．核心思想

通常我们在取前

条记录时候，都是根据某个业务字段进行降序排序，然后取前

条。

例如“

select * from info order by views asc limit 3”

，这条

SQL

就是取

info

表中

的前

条记录。但是“它是在每个类型下都要取浏览量的前

条记录”。

另一种比较简单粗暴的方式是在

Java

代码中循环所有的资讯类型，取出每个类型的前

条记录，最后进行汇总。但是，这种方式虽然也能实现我们的要求，但存在严重的弊端，

即有可能发送多次（成百上千次也有可能）

sql

语句。这种程序显然是有重大缺陷的。

如果能够在查询每条资讯记录时能查出其所在类型的排名，然后根据排名字段进行过

滤，是否也能解决该问题？这时候我们就想到了子查询，并且

MySQL

是可以实现这样的

功能子查询的。

要计算出某条资讯信息在同资讯分类下所有记录中排第几名，换算出有多少条浏览量

比当前记录的浏览量高，然后根据具体的多少（

）条

就是

N+1

就是当前记录所在其

分类下的的排名。

假如以本文上面的示例数据说明：就是在计算每个资讯信息记录时，多计算出一列作

为其“排名”字段，然后取“排名”字段的小于等于

的记录即可。

如果这里还不是很理解的话，就先看下面的

SQL

，然后根据

SQL

再回过头来理解这

段话。

三、

SQL

实现

．方法一：分组排名，取前

名记录

explain (analyse ,bu?ers )

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)

Bu?ers: shared hit=13325

-> Subquery Scan on i (cost=122981.84..155481.84 rows=333333

width=81) (actual time=1634.822..1642.165 rows=201 loops=1)

Filter: (i.sn <= 2)

Rows Removed by Filter: 9863

Bu?ers: shared hit=13324

-> WindowAgg (cost=122981.84..142981.84 rows=1000000

width=81) (actual time=1634.821..1641.693 rows=10064 loops=1)

Bu?ers: shared hit=13324

-> Sort (cost=122981.84..125481.84 rows=1000000 width=73)

(actual time=1634.807..1636.324 rows=10065 loops=1)

of 10

免费下载

金仓数据库

文档被以下合辑收录

墨天轮最受欢迎的技术文档-SQL优化篇（共20篇）

我们整理了墨天轮社区上一些受欢迎且优质的SQL语句优化原理思路、技术要点和方法实操案例文档，包含Oracle、SQL Server、PostgreSQL、MySQL等数据库，欢迎大家下载、补充💪。

文档被以下合辑收录

相关文档

评论