赞
踩
在进行数据库应用开发中,分页查询是一项非常常见而又至关重要的任务。但你是否曾因为需要获取总记录数的性能而感到头疼?现在,让PawSQL的投影下推优化来帮你轻松解决这一问题!本文以TPCH的Q12为案例进行验证,经过PawSQL的优化后性能提升6000多倍!
在进行分页查询时,我们通常需要获取总记录数以计算总页数。绝大多少程序员会在原查询上添加count(1)
或count(*)
,性能可能会非常差,特别是在面对复杂查询时。其实对于这个场景,有很大的概率能够对SQL进行重写优化。
PawSQL的投影下推优化功能,能够智能地识别并保留关键列,生成一个等价但更高效的count
查询。以下是具体的优化步骤:
首先识别原始查询结构,例如:
- SELECT * FROM (
- SELECT col1, col2, ..., colN
- FROM table
- WHERE ...
- ) dt
- ORDER BY ...
- LIMIT ?, ?
Step2.1 将外层的SELECT *
更改为SELECT count(1) FROM (...)
,
Step2.2 删除最外层的ORDER BY子句和LIMIT子句
得到的SQL如下:
- SELECT count(1) FROM (
- SELECT col1, col2, ..., colN
- FROM t1, t2
- WHERE ...
- ) dt
PawSQL可以对对内层查询进行投影下推优化,仅保留对结果有影响的列;同时可能触发其他的重写优化,譬如表关联消除,推荐覆盖索引等。
经过PawSQL的优化重写,新查询可能如下(经过投影下推、表关联消除、查询折叠等重写优化):
- SELECT count(1)
- FROM t1
- WHERE ...
- 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 (
- 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

1. 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专注数据库性能优化的自动化和智能化,提供的解决方案覆盖SQL开发、测试、运维的整个流程,支持MySQL,PostgreSQL,openGauss,Oracle等各种数据库。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。