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

 

 

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/346176.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Redisson分布式锁原理解析

前言 首先Redis执行命令是单线程的&#xff0c;所以可以利用Redis实现分布式锁&#xff0c;而对于Redis单线程的问题&#xff0c;是其线程模型的问题&#xff0c;本篇重点是对目前流行的工具Redisson怎么去实现的分布式锁进行深入理解&#xff1b;开始之前&#xff0c;我们可以…

Vmess协议是什么意思? VLESS与VMess有什么区别?

VMess 是一个基于 TCP 的加密传输协议&#xff0c;所有数据使用 TCP 传输&#xff0c;是由 V2Ray 原创并使用于 V2Ray 的加密传输协议&#xff0c;它分为入站和出站两部分&#xff0c;其作用是帮助客户端跟服务器之间建立通信。在 V2Ray 上客户端与服务器的通信主要是通过 VMes…

ThinkPHP发邮件配置教程?群发功能安全吗?

ThinkPHP发邮件的注意事项&#xff1f;如何优化邮件发送的性能&#xff1f; 无论是用户注册、密码重置还是消息提醒&#xff0c;发送邮件都是一个常见的需求。AokSend将详细介绍如何在ThinkPHP框架中配置和发送邮件&#xff0c;帮助开发者轻松实现邮件功能。 ThinkPHP发邮件&…

43【PS 作图】颜色速途

1 通过PS让画面细节模糊&#xff0c;避免被过多的颜色干扰 2 分析画面的颜色 3 作图 参考网站&#xff1a; 色感不好要怎么提升呢&#xff1f;分享一下我是怎么练习色感的&#xff01;_哔哩哔哩_bilibili https://www.bilibili.com/video/BV1h1421Z76p/?spm_id_from333.1007.…

【Python教程】3-控制流、循环结构与简单字符串操作

在整理自己的笔记的时候发现了当年学习python时候整理的笔记&#xff0c;稍微整理一下&#xff0c;分享出来&#xff0c;方便记录和查看吧。个人觉得如果想简单了解一名语言或者技术&#xff0c;最简单的方式就是通过菜鸟教程去学习一下。今后会从python开始重新更新&#xff0…

MySQL之查询性能优化(七)

查询性能优化 排序优化 无论如何排序都是一个成本很高的操作&#xff0c;所以从性能角度考虑&#xff0c;应尽可能避免排序或者尽可能避免对大量数据进行排序。前面已经提到了&#xff0c;当不能使用索引生成排序结果的时候&#xff0c;MySQL需要自己进行排序&#xff0c;如果…

人脸考勤项目实训

第一章 Python-----Anaconda安装 文章目录 第一章 Python-----Anaconda安装前言一、Anaconda是什么&#xff1f;二、Anaconda的前世今生二、Windows安装步骤1.官网下载2.安装步骤安装虚拟环境 总结 前言 工欲善其事必先利其器&#xff0c;项目第一步&#xff0c;安装我们的环境…

【Unity UGUI】Screen.safeArea获取异形屏数据失败

Screen.safeArea获取不到异形屏的尺寸位置等数据 检查AndroidManifest.xml文件是否有设置&#xff1a;android:theme"style/UnityThemeSelector"&#xff0c;没有加上即可 android:theme"style/UnityThemeSelector"

第1章Hello world 4/5:对比Rust/Java/C++创建和运行Hello world全过程:运行第一个程序

讲动人的故事,写懂人的代码 1.7 对比Rust/Java/C++创建和运行Hello world全过程 有了会听懂人类的讲话,还能做记录的编程助理艾极思,他们三人的讨论内容,都可以变成一份详细的会议纪要啦。 接下来,我们一起看看艾极思是如何记录下赵可菲创建和运行Java程序Hello world,…

简记:为Docker配置服务代理

简记 为Docker配置服务代理 - 文章信息 - Author: 李俊才 (jcLee95) Visit me at CSDN: https://jclee95.blog.csdn.netMy WebSite&#xff1a;http://thispage.tech/Email: 291148484163.com. Shenzhen ChinaAddress of this article:https://blog.csdn.net/qq_28550263/art…

Leetcode3040. 相同分数的最大操作数目 II

Every day a Leetcode 题目来源&#xff1a;3040. 相同分数的最大操作数目 II 解法1&#xff1a;记忆化搜索 第一步可以做什么&#xff1f;做完后&#xff0c;剩下要解决的问题是什么&#xff1f; 删除前两个数&#xff0c;剩下 nums[2] 到 nums[n−1]&#xff0c;这是一个…

分享一个 .NET Core Console 项目中应用 NLog 写日志的详细例子

前言 日志在软件开发中扮演着非常重要的角色&#xff0c;通常我们用它来记录应用程序运行时发生的事件、错误信息、警告以及其他相关信息&#xff0c;帮助在调试和排查问题时更快速地定位和解决 Bug。 通过日志&#xff0c;我们可以做到&#xff1a; 故障排除和调试&#xff…

4.大模型微调技术LoRA

大模型低秩适配(LoRA)技术 现有PEFT 方法的局限与挑战 Adapter方法,通过增加模型深度而额外增加了模型推理延时。Prompt Tuning、Prefix Tuning、P-Tuning等方法中的提示较难训练,同时缩短了模型可用的序列长度。往往难以同时实现高效率和高质量,效果通常不及完全微调(f…

已解决Error || RuntimeError: size mismatch, m1: [32 x 100], m2: [500 x 10]

已解决Error || RuntimeError: size mismatch, m1: [32 x 100], m2: [500 x 10] 原创作者&#xff1a; 猫头虎 作者微信号&#xff1a; Libin9iOak 作者公众号&#xff1a; 猫头虎技术团队 更新日期&#xff1a; 2024年6月6日 博主猫头虎的技术世界 &#x1f31f; 欢迎来…

基于Java-SpringBoot-VUE-MySQL的高校数字化迎新管理系统

基于Java-SpringBoot-VUE-MySQL的高校数字化迎新管理系统 登陆界面 联系作者 如需本项目源代码&#xff0c;可扫码或者VX:bob1638联系作者。 首页图表 系统功能持续更新中。。。 介绍 这是一款主要用于高校迎新的系统&#xff0c;主要是采用了SpringBoot2.X VUE2.6 ElementUI2.…

mysql 数据库datetime 类型,转换为DO里面的long类型后,只剩下年了,没有了月和日

解决方法也简单&#xff1a; 自定义个一个 Date2LongTypeHandler <resultMap id"BeanResult" type"XXXX.XXXXDO"><result column"gmt_create" property"gmtCreate" jdbcType"DATE" javaType"java.lang.Long&…

软件游戏steam_api.dll丢失的解决方法,总结5种有效的方法

在玩电脑游戏时&#xff0c;我们经常会遇到一些错误提示&#xff0c;其中之一就是“游戏缺少steam_api.dll”。这个问题可能让很多玩家感到困惑和烦恼。那么&#xff0c;究竟是什么原因导致游戏缺少steam_api.dll呢&#xff1f;又该如何解决这个问题呢&#xff1f;本文将为大家…

Jmeter压测 —— 1秒发送1次请求

场景&#xff1a;有时候测试场景需要设置请求频率为一秒一次&#xff08;或几秒一次&#xff09;实现方法一&#xff1a;1、首先需要在线程组下设置循环次数&#xff08;可以理解为请求的次数&#xff09; 次数设置为请求300次&#xff0c;其中线程数跟时间自行设置 2、在设置…

JavaScript前端技术入门教程

引言 在前端开发的广阔天地中&#xff0c;JavaScript无疑是最耀眼的一颗明星。它赋予了网页动态交互的能力&#xff0c;让网页从静态的文本和图片展示&#xff0c;进化为可以与用户进行实时交互的丰富应用。本文将带您走进JavaScript的世界&#xff0c;为您提供一个入门级的教…

按键精灵在Win11中弹窗出现乱码并且自带的部分系统插件不能使用的解决方法

按键精灵中出现以下问题&#xff1a; 提示信息的弹窗出现乱码&#xff1a; 系统自带的部分像 plugin. 开头的插件不能使用&#xff0c;如下&#xff1a;s Plugin.Sys.GetDateTime() screenX Plugin.GetSysInfo.GetScreenResolutionX screenY Plugin.GetSysInfo.GetScreenRe…