当前位置:   article > 正文

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

pawsql

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

分页查询的痛点

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

解决方案

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

Step1. 获取原始分页查询,

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

  1. SELECT * FROM (
  2. SELECT col1, col2, ..., colN
  3. FROM table
  4. WHERE ...
  5. ) dt
  6. ORDER BY ...
  7. LIMIT ?, ?

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

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

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

得到的SQL如下:

  1. SELECT count(1) FROM (
  2. SELECT col1, col2, ..., colN
  3.  FROM t1, t2
  4. WHERE ...
  5. ) dt

Step3. PawSQL投影下推优化

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

Step4. 生成高效查询

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

  1. SELECT count(1)
  2. FROM t1
  3. WHERE ...

TPCH案例解析

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

  1. SELECT
  2. L_SHIPMODE,
  3. SUM(CASE
  4. WHEN O_ORDERPRIORITY = '1-URGENT'
  5. OR O_ORDERPRIORITY = '2-HIGH'
  6. THEN 1
  7. ELSE 0
  8. END) AS HIGH_LINE_COUNT,
  9. SUM(CASE
  10. WHEN O_ORDERPRIORITY <> '1-URGENT'
  11. AND O_ORDERPRIORITY <> '2-HIGH'
  12. THEN 1
  13. ELSE 0
  14. END) AS LOW_LINE_COUNT
  15. FROM
  16. ORDERS,
  17. LINEITEM
  18. WHERE
  19. O_ORDERKEY = L_ORDERKEY
  20. AND L_SHIPMODE IN ('RAIL', 'FOB')
  21. AND L_COMMITDATE < L_RECEIPTDATE
  22. AND L_SHIPDATE < L_COMMITDATE
  23. AND L_RECEIPTDATE >= DATE '2021-01-01'
  24. AND L_RECEIPTDATE < DATE '2021-01-01' + INTERVAL '1' YEAR
  25. GROUP BY
  26. L_SHIPMODE
  27. ORDER BY
  28. L_SHIPMODE;

查询总记录数

Q12查询总记录数的SQL如下

  1. select count(*)
  2. from (
  3. SELECT
  4. L_SHIPMODE,
  5. SUM(CASE
  6. WHEN O_ORDERPRIORITY = '1-URGENT'
  7. OR O_ORDERPRIORITY = '2-HIGH'
  8. THEN 1
  9. ELSE 0
  10. END) AS HIGH_LINE_COUNT,
  11. SUM(CASE
  12. WHEN O_ORDERPRIORITY <> '1-URGENT'
  13. AND O_ORDERPRIORITY <> '2-HIGH'
  14. THEN 1
  15. ELSE 0
  16. END) AS LOW_LINE_COUNT
  17. FROM
  18. ORDERS,
  19. LINEITEM
  20. WHERE
  21. O_ORDERKEY = L_ORDERKEY
  22. AND L_SHIPMODE IN ('RAIL', 'FOB')
  23. AND L_COMMITDATE < L_RECEIPTDATE
  24. AND L_SHIPDATE < L_COMMITDATE
  25. AND L_RECEIPTDATE >= DATE '2021-01-01'
  26. AND L_RECEIPTDATE < DATE '2021-01-01' + INTERVAL '1' YEAR
  27. GROUP BY
  28. L_SHIPMODE
  29. ) as t

PawSQL优化过程

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

  1. select count(*)
  2. from (
  3.    select 1
  4. from ORDERS, LINEITEM
  5. where ORDERS.O_ORDERKEY = LINEITEM.L_ORDERKEY
  6. and LINEITEM.L_SHIPMODE in ('RAIL', 'FOB')
  7. and LINEITEM.L_COMMITDATE < LINEITEM.L_RECEIPTDATE
  8. and LINEITEM.L_SHIPDATE < LINEITEM.L_COMMITDATE
  9. and LINEITEM.L_RECEIPTDATE >= date '2021-01-01'
  10. and LINEITEM.L_RECEIPTDATE < date '2021-01-01' + interval '1' YEAR
  11. group by LINEITEM.L_SHIPMODE
  12.    ) as t

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

  1. select /*QB_1*/ count(*)
  2. from (
  3. select /*QB_2*/ 1
  4. from LINEITEM
  5. where LINEITEM.L_SHIPMODE in ('RAIL', 'FOB')
  6. and LINEITEM.L_COMMITDATE < LINEITEM.L_RECEIPTDATE
  7. and LINEITEM.L_SHIPDATE < LINEITEM.L_COMMITDATE
  8. and LINEITEM.L_RECEIPTDATE >= date '2021-01-01'
  9. and LINEITEM.L_RECEIPTDATE < date '2021-01-01' + interval '1' YEAR
  10. group by LINEITEM.L_SHIPMODE
  11. ) 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

 

 

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/Li_阴宅/article/detail/779168
推荐阅读
相关标签
  

闽ICP备14008679号