原作者：师庆栋
- 版本
- OS
- 版本
- OS版本
- DB版本
- 背景
- 案例模拟
- 所用数据
- 原始语句
- 解析
- 尝试1
- 解析
- 尝试2
版本
OS
- 版本
- OS
- 版本
- OS版本
- DB版本
- 背景
- 案例模拟
- 所用数据
- 原始语句
- 解析
- 尝试1
- 解析
- 尝试2
版本
OS版本
[omm@mogdb-01 ~]$ lsb_release -a
LSB Version: :core-4.1-amd64:core-4.1-noarch:cxx-4.1-amd64:cxx-4.1-noarch:desktop-4.1-amd64:desktop-4.1-noarch:languages-4.1-amd64:languages-4.1-noarch:printing-4.1-amd64:printing-4.1-noarch
Distributor ID: CentOS
Description: CentOS Linux release 7.6.1810 (Core)
Release: 7.6.1810
Codename: Core
DB版本
stone=> select version();
version
--------------------------------------------------------------------------------------------------------------------------------------------------
(MogDB 3.0.1 build eb4f387f) compiled at 2022-08-19 18:14:41 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)
背景
为客户优化Oracle查询语句时，遇到一个分页语句。想测试在MogDB中的场景，因此在MogDB中做了模拟优化
案例模拟
所用数据
模拟数据来自Oracle中的字典表 dba_objects dba_users
原始语句
explain analyze
select * From
(
select rownum as rn,t.*
from
(
select u.username,u.user_id,u.account_status,o.object_name,o.object_type
from t_users u
left join
(
select o.owner,
o.object_name,
o.object_type,
row_number() over (partition by o.owner order by o.object_id) as rn
from t_objects o
)o on (o.rn = 1 and o.owner = u.username)
order by (case when u.account_status= 'LOCKED' then 1 else 0 end),u.username
)t
where rownum <= 10
)
where rn >= 6;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on __unnamed_subquery__ (cost=10615.72..10615.97 rows=3 width=439) (actual time=823.558..823.563 rows=5 loops=1)
Filter: (__unnamed_subquery__.rn >= 6)
Rows Removed by Filter: 5
-> Limit (cost=10615.72..10615.84 rows=10 width=431) (actual time=823.539..823.548 rows=10 loops=1)
-> Subquery Scan on t (cost=10615.72..10620.27 rows=364 width=431) (actual time=823.537..823.544 rows=10 loops=1)
-> Sort (cost=10615.72..10616.63 rows=364 width=431) (actual time=823.536..823.536 rows=10 loops=1)
Sort Key: (CASE WHEN ((u.account_status)::text = 'LOCKED'::text) THEN 1 ELSE 0 END), u.username
Sort Method: quicksort Memory: 29kB
-> Hash Right Join (cost=8226.75..10600.23 rows=364 width=431) (actual time=700.380..823.409 rows=42 loops=1)
Hash Cond: ((o.owner)::text = (u.username)::text)
-> Subquery Scan on o (cost=8216.08..10584.03 rows=364 width=47) (actual time=700.154..821.876 rows=30 loops=1)
Filter: (o.rn = 1)
Rows Removed by Filter: 72830
-> WindowAgg (cost=8216.08..9673.28 rows=72860 width=53) (actual time=700.140..800.309 rows=72860 loops=1)
-> Sort (cost=8216.08..8398.23 rows=72860 width=53) (actual time=700.123..711.265 rows=72860 loops=1)
Sort Key: o.owner, o.object_id
Sort Method: quicksort Memory: 12523kB
-> Seq Scan on t_objects o (cost=0.00..2331.60 rows=72860 width=53) (actual time=0.006..33.842 rows=72860 loops=1)
-> Hash (cost=10.30..10.30 rows=30 width=388) (actual time=0.057..0.057 rows=42 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 3kB
-> Seq Scan on t_users u (cost=0.00..10.30 rows=30 width=388) (actual time=0.010..0.019 rows=42 loops=1)
Total runtime: 823.756 ms
(22 rows)
Time: 825.316 ms
解析
可以看到该查询中t_users为主数据，返回行数与t_objects无关，而t_objects占用了大量的成本，如果能减少对t_objects的访问，肯定可以大大提高性能。
尝试1
尝试把t_objects放到标量子查询中
explain analyze
select t.* From
(
select rownum as rn,username,user_id,account_status,
trim((row_o->'object_name')::varchar,'"') as object_name,
trim((row_o->'object_type')::varchar,'"') as object_type
from
(
select u.username,u.user_id,u.account_status,
(
select row_to_json(o)
from (select owner,object_name,object_type,object_id from t_objects) o
where o.owner = u.username
order by o.object_id
limit 1
)as row_o
from t_users u
order by (case when u.account_status= 'LOCKED' then 1 else 0 end),u.username
)t
where rownum <= 10
)t
where rn >= 6;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------
Subquery Scan on t (cost=53140.77..53141.22 rows=3 width=460) (actual time=649.656..649.709 rows=5 loops=1)
Filter: (t.rn >= 6)
Rows Removed by Filter: 5
-> Limit (cost=53140.77..53141.10 rows=10 width=420) (actual time=649.591..649.702 rows=10 loops=1)
-> Subquery Scan on t (cost=53140.77..53141.75 rows=30 width=420) (actual time=649.590..649.698 rows=10 loops=1)
-> Sort (cost=53140.77..53140.85 rows=30 width=388) (actual time=649.576..649.579 rows=10 loops=1)
Sort Key: (CASE WHEN ((u.account_status)::text = 'LOCKED'::text) THEN 1 ELSE 0 END), u.username
Sort Method: quicksort Memory: 32kB
-> Seq Scan on t_users u (cost=0.00..53140.04 rows=30 width=388) (actual time=554.276..649.433 rows=42 loops=1)
SubPlan 1
-> Limit (cost=1770.99..1770.99 rows=1 width=53) (actual time=649.265..649.275 rows=29 loops=42)
-> Sort (cost=1770.99..1777.27 rows=2512 width=53) (actual time=649.192..649.192 rows=29 loops=42)
Sort Key: t_objects.object_id
Sort Method: top-N heapsort Memory: 25kB
-> Bitmap Heap Scan on t_objects (cost=47.72..1758.43 rows=2512 width=53) (actual time=19.525..591.155 rows=61340 loops=42)
Recheck Cond: ((owner)::text = (u.username)::text)
Heap Blocks: exact=1643
-> Bitmap Index Scan on t_ojects_owner (cost=0.00..47.09 rows=2512 width=0) (actual time=18.345..18.345 rows=61340 loop
s=42)
Index Cond: ((owner)::text = (u.username)::text)
Total runtime: 650.267 ms
(20 rows)
Time: 652.380 ms
解析
可以看到使用标量子查询对性能提升很有限，MogDB对于标量子查询中的Limit解析不友好。
我们可以单独看下Limit语句
stone-> select * into v_objects from t_objects o where owner = 'SYS' order by object_id limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Insert on v_objects (cost=0.00..0.16 rows=1 width=566) (actual time=0.147..0.149 rows=1 loops=1)
-> Limit (cost=0.00..0.15 rows=1 width=566) (actual time=0.040..0.040 rows=1 loops=1)
-> Index Scan using t_objects_n1 on t_objects o (cost=0.00..8051.15 rows=52054 width=566) (actual time=0.038..0.038 rows=1 loops=1)
Index Cond: ((owner)::text = 'SYS'::text)
Total runtime: 0.286 ms
(5 rows)
Time: 18.062 ms
可以看到，单独的Limit语句中t_objects只访问了一行，前面的 rows=72860 显然无法发会limit的优势
尝试2
既然标量子查询中的Limit解析不好，我们尝试把标量子查询改用函数来代替
CREATE OR REPLACE FUNCTION f_get_object(p_owner scott.t_objects.owner%type)
RETURN scott.t_objects%rowtype is
v_objects scott.t_objects%rowtype;
begin
select * into v_objects
from t_objects o
where owner = p_owner
order by object_id
limit 1;
RETURN v_objects;
exception when others then
RETURN null;
END;
explain analyze
select t.*
From
(
select rownum as rn,username,user_id,account_status,(o::t_objects).object_name,(o::t_objects).object_type
from
(
select u.username,u.user_id,u.account_status,f_get_object(u.username) as o
from t_users u
order by (case when u.account_status= 'LOCKED' then 1 else 0 end),u.username
)t
where rownum <= 10
)t
where rn >= 6;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on t (cost=18.61..18.86 rows=3 width=734) (actual time=9.564..9.568 rows=5 loops=1)
Filter: (t.rn >= 6)
Rows Removed by Filter: 5
-> Limit (cost=18.61..18.74 rows=10 width=420) (actual time=9.548..9.559 rows=10 loops=1)
-> Subquery Scan on t (cost=18.61..18.99 rows=30 width=420) (actual time=9.547..9.558 rows=10 loops=1)
-> Sort (cost=18.61..18.69 rows=30 width=388) (actual time=9.544..9.549 rows=10 loops=1)
Sort Key: (CASE WHEN ((u.account_status)::text = 'LOCKED'::text) THEN 1 ELSE 0 END), u.username
Sort Method: quicksort Memory: 34kB
-> Seq Scan on t_users u (cost=0.00..17.88 rows=30 width=388) (actual time=1.745..9.372 rows=42 loops=1)
Total runtime: 9.648 ms
(10 rows)
Time: 10.959 ms
优化成功，希望MogDB新版本对标量子查询的解析能有提升，不再需要借助自定义函数
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
