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

相关文章

SpringBoot3实现Gzip压缩优化的技术指南

《SpringBoot3实现Gzip压缩优化的技术指南》随着Web应用的用户量和数据量增加,网络带宽和页面加载速度逐渐成为瓶颈,为了减少数据传输量,提高用户体验,我们可以使用Gzip压缩HTTP响应,... 目录1、简述2、配置2.1 添加依赖2.2 配置 Gzip 压缩3、服务端应用4、前端应用4.1 N

Spring Boot + MyBatis Plus 高效开发实战从入门到进阶优化(推荐)

《SpringBoot+MyBatisPlus高效开发实战从入门到进阶优化(推荐)》本文将详细介绍SpringBoot+MyBatisPlus的完整开发流程,并深入剖析分页查询、批量操作、动... 目录Spring Boot + MyBATis Plus 高效开发实战:从入门到进阶优化1. MyBatis

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Python如何使用__slots__实现节省内存和性能优化

《Python如何使用__slots__实现节省内存和性能优化》你有想过,一个小小的__slots__能让你的Python类内存消耗直接减半吗,没错,今天咱们要聊的就是这个让人眼前一亮的技巧,感兴趣的... 目录背景:内存吃得满满的类__slots__:你的内存管理小助手举个大概的例子:看看效果如何?1.

一文详解SpringBoot响应压缩功能的配置与优化

《一文详解SpringBoot响应压缩功能的配置与优化》SpringBoot的响应压缩功能基于智能协商机制,需同时满足很多条件,本文主要为大家详细介绍了SpringBoot响应压缩功能的配置与优化,需... 目录一、核心工作机制1.1 自动协商触发条件1.2 压缩处理流程二、配置方案详解2.1 基础YAML

浅谈mysql的sql_mode可能会限制你的查询

《浅谈mysql的sql_mode可能会限制你的查询》本文主要介绍了浅谈mysql的sql_mode可能会限制你的查询,这个问题主要说明的是,我们写的sql查询语句违背了聚合函数groupby的规则... 目录场景:问题描述原因分析:解决方案:第一种:修改后,只有当前生效,若是mysql服务重启,就会失效;

MySQL多列IN查询的实现

《MySQL多列IN查询的实现》多列IN查询是一种强大的筛选工具,它允许通过多字段组合快速过滤数据,本文主要介绍了MySQL多列IN查询的实现,具有一定的参考价值,感兴趣的可以了解一下... 目录一、基础语法:多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析与优化1.

MySQL中慢SQL优化的不同方式介绍

《MySQL中慢SQL优化的不同方式介绍》慢SQL的优化,主要从两个方面考虑,SQL语句本身的优化,以及数据库设计的优化,下面小编就来给大家介绍一下有哪些方式可以优化慢SQL吧... 目录避免不必要的列分页优化索引优化JOIN 的优化排序优化UNION 优化慢 SQL 的优化,主要从两个方面考虑,SQL 语

Mysql中深分页的五种常用方法整理

《Mysql中深分页的五种常用方法整理》在数据量非常大的情况下,深分页查询则变得很常见,这篇文章为大家整理了5个常用的方法,文中的示例代码讲解详细,大家可以根据自己的需求进行选择... 目录方案一:延迟关联 (Deferred Join)方案二:有序唯一键分页 (Cursor-based Paginatio

MySQL中慢SQL优化方法的完整指南

《MySQL中慢SQL优化方法的完整指南》当数据库响应时间超过500ms时,系统将面临三大灾难链式反应,所以本文将为大家介绍一下MySQL中慢SQL优化的常用方法,有需要的小伙伴可以了解下... 目录一、慢SQL的致命影响二、精准定位问题SQL1. 启用慢查询日志2. 诊断黄金三件套三、六大核心优化方案方案