时间类型数据存储的效率问题

来源：互联网发布：金融建模需要会编程吗编辑：程序博客网时间：2024/06/05 03:35

最近有客户问了一个与时间字段相关的SQL效率问题，数据库为Oracle,SQL语句如下：

select * from sometable where to_date(somedate,'YYYY-MM-DD:HH24:MI:SS')>to_date('2016-04-16','YYYY-MM_DD')

该SQL语句的效率如何。

从SQL语句中能看出来其somedate的类型为文本型的，由于谓词使用了to_date对该列进行了转换，因此该语句的执行计划一定是全表扫描，因此随着表中的数量的增大，其查询效率会越来越低。正确的做法应该将该字段的类型设置为date类型，并在其上建B-TREE索引。具体效率提高多少也要看查询语句，下面做几个测试看看两种情况下效率会相差多少。

创建两张表，一张使用varchar2存储时间值，一张使用date存储，并分别用随机插入100w条从20160401到20160417这17天的数据，并对两个存储时间的字段建索引

SQL*Plus: Release 12.1.0.2.0 Production on 星期二 4月 19 14:04:58 2016Copyright (c) 1982, 2014, Oracle.  All rights reserved.上次成功登录时间: 星期二 4月  19 2016 09:41:46 +08:00连接到:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> create table testdate(id number primary key, test date);表已创建。SQL> begin  2  for i in 1..1000000 loop  3  insert into testdate values(i,to_date(TRUNC(DBMS_RANDOM.VALUE(to_number(to_char(to_date('20160401','yyyymmdd'),'J')),to_number(to_char(to_date('20160417','yyyymmdd')+1,'J')))),'J')+DBMS_RANDOM.VALUE(1,3600)/3600);  4  end loop;  5  end;  6  /PL/SQL 过程已成功完成。SQL> commit;提交完成。SQL> create index test_idx on testdate(test);索引已创建。SQL> create table testchar as select id,to_char(test,'YYYY-MM-DD:HH24:MI:SS') as test from testdate;表已创建。SQL> create index testchar_idx on testchar(test);索引已创建。

测试内容：
分别测试查询两张表中时间大于20160417,20160412,20160403的id的记录数量

SQL> set autot traceonlySQL>SQL> select count(id) from testdate where test>to_date('20160417','YYYY-MM-DD');已用时间:  00: 00: 00.03执行计划----------------------------------------------------------Plan hash value: 190015244------------------------------------------------------------------------------| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |          |     1 |     9 |   160   (0)| 00:00:01 ||   1 |  SORT AGGREGATE   |          |     1 |     9 |            |          ||*  2 |   INDEX RANGE SCAN| TEST_IDX | 51476 |   452K|   160   (0)| 00:00:01 |------------------------------------------------------------------------------走索引区域扫描，时间为0.03sSQL> select count(id) from testdate where test>to_date('20160412','YYYY-MM-DD');已用时间:  00: 00: 00.09执行计划----------------------------------------------------------Plan hash value: 3044144586-------------------------------------------------------------------------------| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |          |     1 |     9 |   698   (3)| 00:00:01 ||   1 |  SORT AGGREGATE    |          |     1 |     9 |            |          ||*  2 |   TABLE ACCESS FULL| TESTDATE |   308K|  2712K|   698   (3)| 00:00:01 |-------------------------------------------------------------------------------全表扫描，时间为0.09sSQL> select count(id) from testdate where test>to_date('20160403','YYYY-MM-DD');已用时间:  00: 00: 00.09执行计划----------------------------------------------------------Plan hash value: 3044144586-------------------------------------------------------------------------------| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |          |     1 |     9 |   698   (3)| 00:00:01 ||   1 |  SORT AGGREGATE    |          |     1 |     9 |            |          ||*  2 |   TABLE ACCESS FULL| TESTDATE |   779K|  6849K|   698   (3)| 00:00:01 |-------------------------------------------------------------------------------全表扫描0.09s*************************************************************************************SQL> select count(id) from testchar where to_date(test,'YYYY-MM-DD:HH24:MI:SS')>to_date('20160417','YYYY-MM-DD');已用时间:  00: 00: 01.07执行计划----------------------------------------------------------Plan hash value: 2794086230-------------------------------------------------------------------------------| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |          |     1 |    25 |  1175   (3)| 00:00:01 ||   1 |  SORT AGGREGATE    |          |     1 |    25 |            |          ||*  2 |   TABLE ACCESS FULL| TESTCHAR | 50000 |  1220K|  1175   (3)| 00:00:01 |-------------------------------------------------------------------------------全表扫描：1.07sSQL> select count(id) from testchar where to_date(test,'YYYY-MM-DD:HH24:MI:SS')>to_date('20160412','YYYY-MM-DD');已用时间:  00: 00: 01.10执行计划----------------------------------------------------------Plan hash value: 2794086230-------------------------------------------------------------------------------| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |          |     1 |    25 |  1175   (3)| 00:00:01 ||   1 |  SORT AGGREGATE    |          |     1 |    25 |            |          ||*  2 |   TABLE ACCESS FULL| TESTCHAR | 50000 |  1220K|  1175   (3)| 00:00:01 |-------------------------------------------------------------------------------全表扫描1.10sSQL> select count(id) from testchar where to_date(test,'YYYY-MM-DD:HH24:MI:SS')>to_date('20160403','YYYY-MM-DD');已用时间:  00: 00: 01.13执行计划----------------------------------------------------------Plan hash value: 2794086230-------------------------------------------------------------------------------| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |          |     1 |    25 |  1175   (3)| 00:00:01 ||   1 |  SORT AGGREGATE    |          |     1 |    25 |            |          ||*  2 |   TABLE ACCESS FULL| TESTCHAR | 50000 |  1220K|  1175   (3)| 00:00:01 |-------------------------------------------------------------------------------全表扫描1.13s

结论：

如果查询的数据相对于表的总记录数较小的情况下，testdate会选择索引，时间为0.03s,如果较大的情况，会选择全表扫描，时间为0.09s, 但是testchar不论什么情况都走全表扫描，而且每条记录都要使用to_date进行转换，因此其时间也比较固定为1.13s,比testdate走索引慢了38倍，比testdate走全表扫描慢了12倍。

这还只是单用户并且只有两个字段的情况下，如果多用户多字段时间相差的会更多。所以建议存储时间信息还是用原生的date类型，不要为了开发省事使用文本存储。

1 0