ORACLE的优化器,在众多dba眼中都是世界上最好的优化器,一直是其他数据库,包括国产数据库追赶的目标,特别是在查询转换上,oracle确实所向无敌。但是本文要介绍的一个小案例对于oracle来说应该算是一个美中不足的地方。
该sql是一条非常简单的分页sql,由于生产sql不便展示,我在自己的测试环境中复现了一下,发现及时最新的23ai优化器存在着这一点点的小不足。
sql文本非常的简单,T1的object_id列是存在索引的。
1 2 3 4 5 6 7 8 9 10 11 |
SELECT * FROM (SELECT t1.object_name ,t2.DATA_OBJECT_ID ,t2.OBJECT_TYPE ,t2.LAST_DDL_TIME ,t1.STATUS FROM t1,t2 WHERE t1.object_id = t2.object_id AND t2.OWNER = 'SYS' ORDER BY t2.LAST_DDL_TIME DESC) WHERE rownum <= 20; |
执行计划和执行消耗:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
SQL> set autot trace SQL> select * from (select t1.object_name,t2.DATA_OBJECT_ID,t2.OBJECT_TYPE,t2.LAST_DDL_TIME,t1.STATUS from t1,t2 where t1.object_id=t2.object_id and t2.owner='SYS' order by t2.LAST_DDL_TIME desc) where rownum<=20; 20 rows selected. Elapsed: 00:00:03.54 Execution Plan ---------------------------------------------------------- Plan hash value: 2229090023 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 2120 | | 2446 (1)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | | 2 | VIEW | | 19910 | 2060K| | 2446 (1)| 00:00:01 | |* 3 | SORT ORDER BY STOPKEY| | 19910 | 1555K| 1824K| 2446 (1)| 00:00:01 | |* 4 | HASH JOIN | | 19910 | 1555K| | 2075 (1)| 00:00:01 | |* 5 | TABLE ACCESS FULL | T2 | 10115 | 296K| | 1244 (1)| 00:00:01 | | 6 | TABLE ACCESS FULL | T1 | 141K| 6914K| | 831 (1)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=20) 3 - filter(ROWNUM<=20) 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 5 - filter("T2"."OWNER"='SYS') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 98234 consistent gets 50989 physical reads 3951508 redo size 1381 bytes sent via SQL*Net to client 133 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 20 rows processed |
要优化该sql非常简单,按照之前写过的一篇分页sql优化文章的方法,很快就能把sql优化掉。由于T1的object_id列是存在索引的,所以只需要创建T2表的(OWNER,LAST_DDL_TIME)基本可以实现秒出,具体原因参考之前的文章。http://www.minniebaby.tech/2023/05/04/sql%e4%bc%98%e5%8c%96%e6%a1%88%e4%be%8b%ef%bc%9a%e5%88%86%e9%a1%b5%e6%9f%a5%e8%af%a2%e4%b8%80/
sql优化:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
SQL> create index idx2 on t2(owner,last_ddl_time); Index created. Elapsed: 00:00:00.31 SQL> set autot trace SQL> SELECT * 2 FROM (SELECT t1.object_name 3 ,t2.DATA_OBJECT_ID 4 ,t2.OBJECT_TYPE 5 ,t2.LAST_DDL_TIME 6 ,t1.STATUS 7 FROM t1,t2 8 WHERE t1.object_id = t2.object_id 9 AND t2.OWNER = 'SYS' 10 ORDER BY t2.LAST_DDL_TIME DESC) 11 WHERE rownum <= 20; 20 rows selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 3110385888 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 2120 | 37 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 21 | 2226 | 37 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 21 | 1680 | 37 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 22 | 1680 | 37 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID | T2 | 10115 | 296K| 4 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN DESCENDING| IDX2 | 11 | | 3 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | IDX1 | 2 | | 1 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID | T1 | 2 | 100 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=20) 6 - access("T2"."OWNER"='SYS') 7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 31 consistent gets 23 physical reads 0 redo size 1381 bytes sent via SQL*Net to client 133 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed |
但是虽然优化完了该sql,也发现了oracle优化器的一个不足,即使没有创建索引,该sql也应该利用rownum的stopkey特性,先去扫描T2表并且排序之后再去连接T1表,而不是先连接再排序再stopkey。
我们把索引删除,手动去改写sql:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
SQL> SELECT * 2 FROM (SELECT t1.object_name 3 ,t2.DATA_OBJECT_ID 4 ,t2.OBJECT_TYPE 5 ,t2.LAST_DDL_TIME 6 ,t1.STATUS 7 FROM t1 8 ,(SELECT * 9 FROM t2 10 WHERE t2.OWNER = 'SYS' 11 ORDER BY t2.LAST_DDL_TIME DESC) t2 12 WHERE t1.object_id = t2.object_id) 13 WHERE rownum <= 20; 20 rows selected. Elapsed: 00:00:00.09 Execution Plan ---------------------------------------------------------- Plan hash value: 1310430299 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 1800 | | 35 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | | 2 | NESTED LOOPS | | 21 | 1890 | | 35 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 22 | 1890 | | 35 (0)| 00:00:01 | | 4 | VIEW | | 10115 | 474K| | 2 (0)| 00:00:01 | | 5 | SORT ORDER BY | | 10115 | 1501K| 2144K| 1588 (1)| 00:00:01 | |* 6 | TABLE ACCESS FULL | T2 | 10115 | 1501K| | 1244 (1)| 00:00:01 | |* 7 | INDEX RANGE SCAN | IDX1 | 2 | | | 1 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 100 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=20) 6 - filter("T2"."OWNER"='SYS') 7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") Statistics ---------------------------------------------------------- 57 recursive calls 0 db block gets 4644 consistent gets 4599 physical reads 0 redo size 1381 bytes sent via SQL*Net to client 133 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 20 rows processed |
虽然肯定比不上之前创建索引的方式,但是也比之前好很多,oracle的查询转换居然没考虑到这种情况。
相比之下mysql居然有这方面的考虑,在没有被优化过的情况下,该sql场景mysql选择了最正确的执行计划。
1 2 3 4 5 6 7 8 |
mysql> explain select t1.name,t1.email,t2.created_at,t2.is_active,t2.balance from sample_table t1,sample_table1 t2 where t1.id=t2.id order by t2.created_at desc,t2.id limit 20; +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+--------+----------+----------------+ | 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 198266 | 100.00 | Using filesort | | 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test_db.t2.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+--------+----------+----------------+ 2 rows in set, 1 warning (0.00 sec) |
先扫描T2并且排序之后再与T1关联,很好的用上了limit的特点,而不是像oracle一样先关联再去对关联出的大量结果集排序再去做stopkey。
这也给了我们一个启发,就是如果是从mysql迁移到其他数据库的情况,需要考虑到这种sql场景在mysql上可能会跑的不错,但是在其他数据库上会明显变慢,需要做对应的优化。