PawSQL优化 | 分页查询太慢?别忘了投影下推

2024-06-11 03:12

本文主要是介绍PawSQL优化 | 分页查询太慢?别忘了投影下推,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

​在进行数据库应用开发中,分页查询是一项非常常见而又至关重要的任务。但你是否曾因为需要获取总记录数的性能而感到头疼?现在,让PawSQL的投影下推优化来帮你轻松解决这一问题!本文以TPCH的Q12为案例进行验证,经过PawSQL的优化后性能提升6000多倍!

分页查询的痛点

在进行分页查询时,我们通常需要获取总记录数以计算总页数。绝大多少程序员会在原查询上添加count(1)count(*),性能可能会非常差,特别是在面对复杂查询时。其实对于这个场景,有很大的概率能够对SQL进行重写优化。

解决方案

PawSQL的投影下推优化功能,能够智能地识别并保留关键列,生成一个等价但更高效的count查询。以下是具体的优化步骤:

Step1. 获取原始分页查询,

首先识别原始查询结构,例如:

SELECT * FROM (SELECT col1, col2, ..., colNFROM tableWHERE ...
) dt
ORDER BY ...
LIMIT ?, ?

Step2. 将分页查询改为记录总数查询

        Step2.1 将外层的SELECT *更改为SELECT count(1) FROM (...)

        Step2.2 删除最外层的ORDER BY子句和LIMIT子句

得到的SQL如下:

SELECT count(1) FROM (SELECT col1, col2, ..., colNFROM t1, t2WHERE ...
) dt

Step3. PawSQL投影下推优化

PawSQL可以对对内层查询进行投影下推优化,仅保留对结果有影响的列;同时可能触发其他的重写优化,譬如表关联消除,推荐覆盖索引等。

Step4. 生成高效查询

经过PawSQL的优化重写,新查询可能如下(经过投影下推、表关联消除、查询折叠等重写优化):

SELECT count(1)
FROM t1
WHERE ...

TPCH案例解析

Q12:货运模式和订单优先级查询

SELECT
L_SHIPMODE,
SUM(CASE
WHEN O_ORDERPRIORITY = '1-URGENT'
OR O_ORDERPRIORITY = '2-HIGH'
THEN 1
ELSE 0
END) AS HIGH_LINE_COUNT,
SUM(CASE
WHEN O_ORDERPRIORITY <> '1-URGENT'
AND O_ORDERPRIORITY <> '2-HIGH'
THEN 1
ELSE 0
END) AS LOW_LINE_COUNT
FROM
ORDERS,
LINEITEM
WHERE
O_ORDERKEY = L_ORDERKEY
AND L_SHIPMODE IN ('RAIL', 'FOB')
AND L_COMMITDATE < L_RECEIPTDATE
AND L_SHIPDATE < L_COMMITDATE
AND L_RECEIPTDATE >= DATE '2021-01-01'
AND L_RECEIPTDATE < DATE '2021-01-01' + INTERVAL '1' YEAR
GROUP BY
L_SHIPMODE
ORDER BY
L_SHIPMODE;

查询总记录数

Q12查询总记录数的SQL如下

select count(*)
from (SELECTL_SHIPMODE,SUM(CASEWHEN O_ORDERPRIORITY = '1-URGENT'OR O_ORDERPRIORITY = '2-HIGH'THEN 1ELSE 0END) AS HIGH_LINE_COUNT,SUM(CASEWHEN O_ORDERPRIORITY <> '1-URGENT'AND O_ORDERPRIORITY <> '2-HIGH'THEN 1ELSE 0END) AS LOW_LINE_COUNTFROMORDERS,LINEITEMWHEREO_ORDERKEY = L_ORDERKEYAND L_SHIPMODE IN ('RAIL', 'FOB')AND L_COMMITDATE < L_RECEIPTDATEAND L_SHIPDATE < L_COMMITDATEAND L_RECEIPTDATE >= DATE '2021-01-01'AND L_RECEIPTDATE < DATE '2021-01-01' + INTERVAL '1' YEARGROUP BYL_SHIPMODE) as t

PawSQL优化过程

1. PawSQL首先进行投影下推优化,可以看到派生表的列被消除

select count(*)
from ( select 1from ORDERS, LINEITEMwhere ORDERS.O_ORDERKEY = LINEITEM.L_ORDERKEYand LINEITEM.L_SHIPMODE in ('RAIL', 'FOB')and LINEITEM.L_COMMITDATE < LINEITEM.L_RECEIPTDATEand LINEITEM.L_SHIPDATE < LINEITEM.L_COMMITDATEand LINEITEM.L_RECEIPTDATE >= date '2021-01-01'and LINEITEM.L_RECEIPTDATE < date '2021-01-01' + interval '1' YEARgroup by LINEITEM.L_SHIPMODE) as t

2. 选择列被消除,从而触发了表连接消除(ORDERS被消除)

select /*QB_1*/ count(*)
from (select /*QB_2*/ 1from LINEITEMwhere LINEITEM.L_SHIPMODE in ('RAIL', 'FOB')and LINEITEM.L_COMMITDATE < LINEITEM.L_RECEIPTDATEand LINEITEM.L_SHIPDATE < LINEITEM.L_COMMITDATEand LINEITEM.L_RECEIPTDATE >= date '2021-01-01'and LINEITEM.L_RECEIPTDATE < date '2021-01-01' + interval '1' YEARgroup by LINEITEM.L_SHIPMODE) as t

3. PawSQL接着推荐最优索引(索引查找+避免排序+避免回表)

CREATE INDEX PAWSQL_IDX0245689906 ON tpch_pkfk.lineitem(L_SHIPMODE,L_RECEIPTDATE,L_COMMITDATE,L_SHIPDATE);

4. 性能验证性能提升

执行时间从优化前的453.48ms,降低到0.065ms,性能提升6975倍!

 

cf1cdc13932e4c0c0c73dd1f79a056ff.png

其他应用场景

除了分页查询,PawSQL的投影下推优化还能在以下场景中大放异彩:

  • 星号查询优化:避免使用SELECT *带来的数据传输和计算开销。

  • EAV模型数据优化:减少高度规范化数据模型的连接操作成本。

  • 视图和嵌套视图优化:简化复杂视图查询,降低计算开销。

  • 报表查询优化:提高报表生成的性能,尤其是在处理多维度数据时。


往期文章精选

SQL审核 | PawSQL的审核规则集体系

高级SQL优化 | 查询折叠

EverSQL向左,PawSQL向右


关于PawSQL

PawSQL专注数据库性能优化的自动化和智能化,提供的解决方案覆盖SQL开发、测试、运维的整个流程,支持MySQL,PostgreSQL,openGauss,Oracle等各种数据库。

 

dea225fe7037133e201a764f14167b11.png

 

 

这篇关于PawSQL优化 | 分页查询太慢?别忘了投影下推的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1050047

相关文章

Vue3 的 shallowRef 和 shallowReactive:优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉,那么对 shallowRef 和 shallowReactive 是否了解呢? 在编程和数据结构中,“shallow”(浅层)通常指对数据结构的最外层进行操作,而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素,而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层(Shallow) 定义

HDFS—存储优化(纠删码)

纠删码原理 HDFS 默认情况下,一个文件有3个副本,这样提高了数据的可靠性,但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码,采用计算的方式,可以节省约50%左右的存储空间。 此种方式节约了空间,但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件,都会执行此策略。 默认只开启对 RS-6-3-1024k

使用opencv优化图片(画面变清晰)

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化,使其看起来更清晰,同时保持尺寸不变,通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多,主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器:相机传

活用c4d官方开发文档查询代码

当你问AI助手比如豆包,如何用python禁止掉xpresso标签时候,它会提示到 这时候要用到两个东西。https://developers.maxon.net/论坛搜索和开发文档 比如这里我就在官方找到正确的id描述 然后我就把参数标签换过来

MySQL高性能优化规范

前言:      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后,总结出了这篇! 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份

SWAP作物生长模型安装教程、数据制备、敏感性分析、气候变化影响、R模型敏感性分析与贝叶斯优化、Fortran源代码分析、气候数据降尺度与变化影响分析

查看原文>>>全流程SWAP农业模型数据制备、敏感性分析及气候变化影响实践技术应用 SWAP模型是由荷兰瓦赫宁根大学开发的先进农作物模型,它综合考虑了土壤-水分-大气以及植被间的相互作用;是一种描述作物生长过程的一种机理性作物生长模型。它不但运用Richard方程,使其能够精确的模拟土壤中水分的运动,而且耦合了WOFOST作物模型使作物的生长描述更为科学。 本文让更多的科研人员和农业工作者

从状态管理到性能优化:全面解析 Android Compose

文章目录 引言一、Android Compose基本概念1.1 什么是Android Compose?1.2 Compose的优势1.3 如何在项目中使用Compose 二、Compose中的状态管理2.1 状态管理的重要性2.2 Compose中的状态和数据流2.3 使用State和MutableState处理状态2.4 通过ViewModel进行状态管理 三、Compose中的列表和滚动

ural 1026. Questions and Answers 查询

1026. Questions and Answers Time limit: 2.0 second Memory limit: 64 MB Background The database of the Pentagon contains a top-secret information. We don’t know what the information is — you

Mybatis中的like查询

<if test="templateName != null and templateName != ''">AND template_name LIKE CONCAT('%',#{templateName,jdbcType=VARCHAR},'%')</if>

oracle分页和mysql分页

mysql 分页 --查前5 数据select * from table_name limit 0,5 select * from table_name limit 5 --limit关键字的用法:LIMIT [offset,] rows--offset指定要返回的第一行的偏移量,rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。   oracle 分页 --查前1-9