对韩峰著《SQL优化最佳实践》P7 案例的质疑

事先申明下，我的DB环境是Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production，如果与作者环境不同而导致结论差异则另当别论。

该案例做了一个id为varchar类型的两种查询对比，我模拟了一下。

我是这样建表的：

create table tb_varchar2id(
   id varchar2(20) primary key,
   name nvarchar2(20),
   sal number(5,0)
)

insert into tb_varchar2id 
select rownum,dbms_random.string('*',dbms_random.value(6,20)),dbms_random.value(1000,30000)  from dual
connect by level<=2000000
order by dbms_random.random

原作中是320万，我机器受限只能弄200万，这个差别不影响作者的思路和我的结论。

建表完提交后，开始第一个查询并观察其执行计划：

SQL> select * from tb_varchar2id where id>='1900000';
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------
Plan hash value: 3377844066

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |  1006K|    45M|  3602   (2)| 00:00:44 |
|*  1 |  TABLE ACCESS FULL| TB_VARCHAR2ID |  1006K|    45M|  3602   (2)| 00:00:44 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID">='1900000')

Note
-----
   - dynamic sampling used for this statement (level=2)

这里走的也是全表扫描，cost是3602，作者那边不同的是8927.

 

再看封闭范围的查询及执行计划：

SQL> select * from tb_varchar2id where id between '1900000' and '2000000';
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------
Plan hash value: 1409398992

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   104K|  4773K|   399   (0)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_VARCHAR2ID |   104K|  4773K|   399   (0)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN          | SYS_C0011453  |   104K|       |   357   (0)| 00:00:05 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">='1900000' AND "ID"<='2000000')

Note
-----
   - dynamic sampling used for this statement (level=2)

这把走的是索引范围扫描，cost是399，原著中是5（作者加了索引SYS_C0025295）。

 

就本例而言，换了查询方式后cost从3602降到399，似乎有了数量级的提升，而原著中8927到5，更是令人瞠目结舌！

很多看官看到这里都要作者所云以为开放(>=)区间查询要次于封闭区间(between)查询了，还以为作者给出了一条可行的优化之路。

但是，下面两条SQL执行结果是不一样的。

select * from tb_varchar2id where id>='1900000';
select * from tb_varchar2id where id between '1900000' and '2000000';

让我们看看它们的数量：

SQL> set autotrace off;
SQL> select count(*) from tb_varchar2id where id>='1900000';

  COUNT(*)
----------
    999995

已用时间:  00: 00: 00.21
SQL> select count(*) from tb_varchar2id where id between '1900000' and '2000000';

  COUNT(*)
----------
    111113

已用时间:  00: 00: 00.00

前者是将近一百万条，后者是十一万条，数据量有一个数量级的差距，cost自然也有一个数量级的差距。

 

为什么会这样？因为id是varchar2类型，不是number类型，上面SQL在搞字符串比较呢。

就比如运行select * from tb_varchar2id where id>='1900000' and rownum<20;

SQL> select * from tb_varchar2id where id>='1900000' and rownum<20;

ID                   NAME                                            SAL
-------------------- ---------------------------------------- ----------
1900000              YQJQLHKTYVLSZX                                12533
1900001              SPLMMLXO                                      18104
1900002              TYGGIMJCSIWOWUX                                6383
1900003              SYYYNRXSL                                     15890
1900004              GEGQAG                                         9448
1900005              SFGBZMMPOSEVMNEHQ                             20339
1900006              OMQGZZWVEPRWIMTYK                             13421
1900007              PWHATEOVY                                     11135
1900008              TLBRFDWDCEMXFYUXYH                            15930
1900009              ZUIQECXIRQXBTO                                15961
190001               WKEAMSE                                       25082

ID                   NAME                                            SAL
-------------------- ---------------------------------------- ----------
1900010              CMPQCVUBXSMBCMI                               17296
1900011              QDPNUNBDXBKV                                  17393
1900012              OYQBIBRADGE                                   12009
1900013              VIRWDAKEE                                     18760
1900014              NQJYHGKREUKGENWH                              28990
1900015              IKUUFL                                         7899
1900016              ACQDSR                                         1195
1900017              NXIECMAVNE                                     4208

已选择19行。

连190001都混迹其中，这不应该是符合两个SQL意图的记录。 

所以，这是不同SQL在比较性能，这有意义吗？

作者一开头就出这么一个让人费解的地方,或是有些细节没有明写在书里，引起读者疑惑，有点不应该。

--2020年1月31日--