

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

PawSQL

2024-06-05

导读：PawSQL的投影下推优化技术，是一种在任何需要处理多余列的查询场景中都能提升查询性能的有效手段。通过减少数据传输和降低计算复杂度，PawSQL让数据库查询变得更加高效。

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

分页查询的痛点

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

解决方案

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

获取原始分页查询：首先识别原始查询结构，例如：

SELECT * FROM ( SELECT col1, col2, ..., colN FROM table WHERE ...) dtORDER BY ...LIMIT ?, ?

人工改写成记录数查询：

将外层的SELECT *更改为SELECT count(1)...，
删除最外层的 ORDER BY子句和LIMIT子句

SELECT count(1) FROM ( SELECT col1, col2, ..., colN FROM t1, t2 WHERE ...) dt

PawSQL投影下推优化：PawSQL可以对对内层查询进行投影下推优化，仅保留对结果有影响的列，同时可能触发其他的重写优化，譬如表关联消除，推荐覆盖索引等。
生成高效查询：经过PawSQL的优化，新查询可能如下：
```
SELECT count(1)FROM ( SELECT 1 FROM t1 WHERE ...)
```

TPCH案例解析

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

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_SHIPMODEORDER BYL_SHIPMODE;

查询总记录数的SQL

select count(*)from (    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  ) as t

PawSQL优化过程

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

select count(*)from (    select 1   from ORDERS, LINEITEM   where ORDERS.O_ORDERKEY = LINEITEM.L_ORDERKEY   and LINEITEM.L_SHIPMODE in ('RAIL', 'FOB')   and LINEITEM.L_COMMITDATE < LINEITEM.L_RECEIPTDATE   and LINEITEM.L_SHIPDATE < LINEITEM.L_COMMITDATE   and LINEITEM.L_RECEIPTDATE >= date '2021-01-01'   and LINEITEM.L_RECEIPTDATE < date '2021-01-01' + interval '1' YEAR   group by LINEITEM.L_SHIPMODE   ) as t

2. 选择列被消除，从而触发了表连接消除（ORDERS被消除）

select /*QB_1*/ count(*)from ( select /*QB_2*/ 1 from LINEITEM where LINEITEM.L_SHIPMODE in ('RAIL', 'FOB') and LINEITEM.L_COMMITDATE < LINEITEM.L_RECEIPTDATE and LINEITEM.L_SHIPDATE < LINEITEM.L_COMMITDATE and LINEITEM.L_RECEIPTDATE >= date '2021-01-01' and LINEITEM.L_RECEIPTDATE < date '2021-01-01' + interval '1' YEAR group 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倍！

其他应用场景

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

星号查询优化：避免使用SELECT *带来的数据传输和计算开销。
EAV模型数据优化：减少高度规范化数据模型的连接操作成本。
视图和嵌套视图优化：简化复杂视图查询，降低计算开销。
报表查询优化：提高报表生成的性能，尤其是在处理多维度数据时。

总结

PawSQL的投影下推优化技术，是一种在任何需要处理多余列的查询场景中都能提升查询性能的有效手段。通过减少数据传输和降低计算复杂度，PawSQL让数据库查询变得更加高效。

PawSQL往期文章精选

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

高级SQL优化 | 查询折叠

EverSQL向左，PawSQL向右

关于PawSQL

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

欢迎点击关注PawSQL公众号

【声明】内容源于网络

PawSQL

PawSQL专注于数据库性能优化的自动化和智能化，支持MySQL、PostgreSQL、openGauss，金仓、达梦、Oracle等主流商用和开源数据库，为开发者和企业提供一站式的创新SQL优化解决方案。

内容 35

粉丝 0

PawSQL PawSQL专注于数据库性能优化的自动化和智能化，支持MySQL、PostgreSQL、openGauss，金仓、达梦、Oracle等主流商用和开源数据库，为开发者和企业提供一站式的创新SQL优化解决方案。

总阅读4

粉丝0

内容35