空间查看网站,自在源码网官网,wordpress官方空间,wordpress 主题添加小工具在进行数据库应用开发中#xff0c;分页查询是一项非常常见而又至关重要的任务。但你是否曾因为需要获取总记录数的性能而感到头疼#xff1f;现在#xff0c;让PawSQL的投影下推优化来帮你轻松解决这一问题#xff01;本文以TPCH的Q12为案例进行验证#xff0c;经过Paw…在进行数据库应用开发中分页查询是一项非常常见而又至关重要的任务。但你是否曾因为需要获取总记录数的性能而感到头疼现在让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-URGENTOR O_ORDERPRIORITY 2-HIGHTHEN 1ELSE 0END) AS HIGH_LINE_COUNT,SUM(CASEWHEN O_ORDERPRIORITY 1-URGENTAND O_ORDERPRIORITY 2-HIGHTHEN 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-01AND 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-01and 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-01and 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倍 其他应用场景
除了分页查询PawSQL的投影下推优化还能在以下场景中大放异彩 星号查询优化避免使用SELECT *带来的数据传输和计算开销。 EAV模型数据优化减少高度规范化数据模型的连接操作成本。 视图和嵌套视图优化简化复杂视图查询降低计算开销。 报表查询优化提高报表生成的性能尤其是在处理多维度数据时。 往期文章精选
SQL审核 | PawSQL的审核规则集体系
高级SQL优化 | 查询折叠
EverSQL向左PawSQL向右 关于PawSQL
PawSQL专注数据库性能优化的自动化和智能化提供的解决方案覆盖SQL开发、测试、运维的整个流程支持MySQLPostgreSQLopenGaussOracle等各种数据库。
