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

相关文章

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》:本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J

Go语言开发实现查询IP信息的MCP服务器

《Go语言开发实现查询IP信息的MCP服务器》随着MCP的快速普及和广泛应用,MCP服务器也层出不穷,本文将详细介绍如何在Go语言中使用go-mcp库来开发一个查询IP信息的MCP... 目录前言mcp-ip-geo 服务器目录结构说明查询 IP 信息功能实现工具实现工具管理查询单个 IP 信息工具的实现服

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括:左连接(leftjoin)、右连接(rightjoin)、全连接(fulljoin)、内连接(innerjoin)、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表

MySQL高级查询之JOIN、子查询、窗口函数实际案例

《MySQL高级查询之JOIN、子查询、窗口函数实际案例》:本文主要介绍MySQL高级查询之JOIN、子查询、窗口函数实际案例的相关资料,JOIN用于多表关联查询,子查询用于数据筛选和过滤,窗口函... 目录前言1. JOIN(连接查询)1.1 内连接(INNER JOIN)1.2 左连接(LEFT JOI

MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录

《MySQL中查询VARCHAR类型JSON数据的问题记录》在数据库设计中,有时我们会将JSON数据存储在VARCHAR或TEXT类型字段中,本文将详细介绍如何在MySQL中有效查询存储为V... 目录一、问题背景二、mysql jsON 函数2.1 常用 JSON 函数三、查询示例3.1 基本查询3.2

MySQL中的交叉连接、自然连接和内连接查询详解

《MySQL中的交叉连接、自然连接和内连接查询详解》:本文主要介绍MySQL中的交叉连接、自然连接和内连接查询,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、引入二、交php叉连接(cross join)三、自然连接(naturalandroid join)四

mysql的基础语句和外键查询及其语句详解(推荐)

《mysql的基础语句和外键查询及其语句详解(推荐)》:本文主要介绍mysql的基础语句和外键查询及其语句详解(推荐),本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋... 目录一、mysql 基础语句1. 数据库操作 创建数据库2. 表操作 创建表3. CRUD 操作二、外键

Python通过模块化开发优化代码的技巧分享

《Python通过模块化开发优化代码的技巧分享》模块化开发就是把代码拆成一个个“零件”,该封装封装,该拆分拆分,下面小编就来和大家简单聊聊python如何用模块化开发进行代码优化吧... 目录什么是模块化开发如何拆分代码改进版:拆分成模块让模块更强大:使用 __init__.py你一定会遇到的问题模www.

Mybatis 传参与排序模糊查询功能实现

《Mybatis传参与排序模糊查询功能实现》:本文主要介绍Mybatis传参与排序模糊查询功能实现,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友跟随小编一起看看吧... 目录一、#{ }和${ }传参的区别二、排序三、like查询四、数据库连接池五、mysql 开发企业规范一、#{ }和${ }传参的