南宁网站建设 醉懂网络,吉林最新消息今天新增,wordpress图片生成插件,网站百度屏蔽关键词在过去的一些项目中#xff0c;我们发现对于标量子查询带limit或者rownum的情况下#xff0c;Oracle的性能非常高效#xff0c;而MogDB的性能似乎差强人意#xff0c;那么如果在使用MogDB的过程中遇到了这样的场景#xff0c;该如何进行优化呢#xff1f; 这里我们来给大… 在过去的一些项目中我们发现对于标量子查询带limit或者rownum的情况下Oracle的性能非常高效而MogDB的性能似乎差强人意那么如果在使用MogDB的过程中遇到了这样的场景该如何进行优化呢 这里我们来给大家分享一些优化方法。 首先来看看Oracle的情况 这里我们先模拟构造一些数据用来测试验证. create table test02 as select * from dba_objects;create table test01 as select * from test02;insert into test01 select * from test01;insert into test01 select * from test01;insert into test01 select * from test01;commit;insert into test01 select * from test01;insert into test01 select * from test01;insert into test01 select * from test01;commit;insert into test01 select * from test01;insert into test01 select * from test01;insert into test01 select * from test01;commit;create index idx_test01_owner on test01(owner); 数据构造完毕之后我们来看看Oracle 11g中该测试SQL的执行计划如何。 SQL select count(1) from test01; COUNT(1)---------- 22858240Elapsed: 00:00:03.75SQL select count(1) from test02; COUNT(1)---------- 89290Elapsed: 00:00:00.07SQL SQL SELECT object_id, t1.owner, 2 (SELECT t2.object_name 3 FROM test01 t2 4 WHERE t1.owner t2.owner 5 AND rownum 1) 6 FROM test02 t1 7 WHERE rownum 10; OBJECT_ID OWNER (SELECTT2.OBJECT_NAMEFROMTEST01T2WHERET1.OWNERT2.OWNERANDRO---------- ------------------------------ ------------------------------------------------------------ 20 SYS ICOL$ 46 SYS ICOL$ 28 SYS ICOL$ 15 SYS ICOL$ 29 SYS ICOL$ 3 SYS ICOL$ 25 SYS ICOL$ 41 SYS ICOL$ 54 SYS ICOL$ 40 SYS ICOL$10 rows selected.Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------Plan hash value: 1781923309-------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 300 | 2 (0)| 00:00:01 ||* 1 | COUNT STOPKEY | | | | | || 2 | TABLE ACCESS BY INDEX ROWID| TEST01 | 250K| 19M| 4 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | IDX_TEST01_OWNER | 100K| | 3 (0)| 00:00:01 ||* 4 | COUNT STOPKEY | | | | | || 5 | TABLE ACCESS FULL | TEST02 | 81940 | 2400K| 2 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM1) 3 - access(T2.OWNER:B1) 4 - filter(ROWNUM10)Note----- - dynamic sampling used for this statement (level2)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 9 consistent gets 12 physical reads 0 redo size 862 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processedSQL 可以看到在Oracle 中该SQL执行效率非常高效毫秒级返回结果。然后在早期的MogDB版本中处理该SQL性能并不高高效会走bitmap index scan。 首先我们通过mtk迁移工具将该2个表测试数据迁移到mogdb中然后测试验证一把。 至于迁移步骤这里我就不在提了大家可以参考之前的文章。 MogDB 验证 这里我使用了MogDB 5.0.5版本。废话不多说直接上测试过程。 [ommmogdb1 ~]$ gsql -r -d test -UrogerPassword for user roger: gsql ((MogDB 5.0.5 build b77f1a82) compiled at 2023-12-08 03:11:47 commit 0 last mr 1804 )Non-SSL connection (SSL connection is recommended when requiring high-security)Type help for help.test \timing onTiming is on.test test SELECT object_id, t1.owner,test- (SELECT t2.object_nametest( FROM test01 t2test( WHERE t1.owner t2.ownertest( AND rownum 1)test- FROM test02 t1test- WHERE rownum 10; object_id | owner | object_name ------------------------------- 20 | SYS | ICOL$ 46 | SYS | ICOL$ 28 | SYS | ICOL$ 15 | SYS | ICOL$ 29 | SYS | ICOL$ 3 | SYS | ICOL$ 25 | SYS | ICOL$ 41 | SYS | ICOL$ 54 | SYS | ICOL$ 40 | SYS | ICOL$(10 rows)Time: 18472.299 mstest test explain test- SELECT object_id, t1.owner,test- (SELECT t2.object_nametest( FROM test01 t2test( WHERE t1.owner t2.ownertest( AND rownum 1)test- FROM test02 t1test- WHERE rownum 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Limit (cost0.00..179830.95 rows10 width11) - Seq Scan on test02 t1 (cost0.00..1605908407.74 rows89301 width11) SubPlan 1 - Limit (cost17982.47..17983.07 rows1 width24) - Bitmap Heap Scan on test01 t2 (cost17982.47..591299.40 rows952544 width24) Recheck Cond: ((t1.owner)::text (owner)::text) - Bitmap Index Scan on idx_test01_owner (cost0.00..17744.33 rows952544 width0) Index Cond: ((t1.owner)::text (owner)::text)(8 rows)Time: 1.544 mstest test select t2.object_name from test01 t2 where SYS t2.owner and rownum 1; object_name ------------- ICOL$(1 row)Time: 1.496 mstest explain select t2.object_name from test01 t2 where SYS t2.owner and rownum 1; QUERY PLAN --------------------------------------------------------------------------- Limit (cost0.00..0.07 rows1 width24) - Seq Scan on test01 t2 (cost0.00..687643.20 rows9726617 width24) Filter: (SYS::text (owner)::text)(3 rows)Time: 1.292 mstest 从第一次执行的情况来看该SQL需要10多秒简直是比Oracle慢太多了无法仍受。实际上我们分析上述的执行计划可以看到主要慢在了bitmap scan上了。 这里我们暂时用hint禁用该功能。 test SELECT /* set(enable_bitmapscan off) */ object_id, t1.owner,test- (SELECT t2.object_nametest( FROM test01 t2test( WHERE t1.owner t2.ownertest( AND rownum 1)test- FROM test02 t1test- WHERE rownum 10; object_id | owner | object_name ------------------------------- 20 | SYS | ICOL$ 46 | SYS | ICOL$ 28 | SYS | ICOL$ 15 | SYS | ICOL$ 29 | SYS | ICOL$ 3 | SYS | ICOL$ 25 | SYS | ICOL$ 41 | SYS | ICOL$ 54 | SYS | ICOL$ 40 | SYS | ICOL$(10 rows)Time: 3.367 mstest explain SELECT /* set(enable_bitmapscan off) */ object_id, t1.owner,test- (SELECT t2.object_nametest( FROM test01 t2test( WHERE t1.owner t2.ownertest( AND rownum 1)test- FROM test02 t1test- WHERE rownum 10; QUERY PLAN ---------------------------------------------------------------------------------------- Limit (cost0.00..7.49 rows10 width11) - Seq Scan on test02 t1 (cost0.00..66929.56 rows89301 width11) SubPlan 1 - Limit (cost0.00..0.72 rows1 width24) - Seq Scan on test01 t2 (cost0.00..687643.20 rows952544 width24) Filter: ((t1.owner)::text (owner)::text)(6 rows)Time: 1.467 mstest 我们可以看到此时执行计划正常了快如闪电了。 在6月30号即将发布的新版本中SQL引擎就具备该能力不再需要人工干预了。 本文由 mdnice 多平台发布
