1、场景

我这里正常情况下一天将近两千万的数据需要存储,现在存储在Oracle 11g数据库中,目前数据量达到1个多亿

2、表现问题

查询速度特别慢,查询语句如下

表结构(表经过转化)

CREATE TABLE  MI_TEST_NUM

   (

   ID VARCHAR2(64) NOT NULL ENABLE,

PASSINGTIME TIMESTAMP (6) DEFAULT NULL,

CREATETIME TIMESTAMP (6) DEFAULT NULL,

FLAG VARCHAR2(32) DEFAULT NULL,

VEHICLETYPE VARCHAR2(30) DEFAULT null

) PARTITION BY RANGE (CREATETIME) INTERVAL (NUMTODSINTERVAL(1, 'day'))

(partition part_t01 values less than(to_date('2020-06-14', 'YYYY-MM-DD')));

 

-- 创建索引

create index MITESTNUM_PASSINGTIME_localidx on MI_TEST_NUM(PASSINGTIME) LOCAL tablespace USERS;

-- 其他索引不再展示

 

-- 执行查询计划

explain plan FOR

SELECT t.*

FROM MI_TEST_NUM t

WHERE t.FLAG = 'H'

AND t.PASSINGTIME BETWEEN to_date('2020-06-10 00:00:00','yyyy--mm-dd hh24:mi:ss')

AND to_date('2020-06-21 23:59:59','yyyy--mm-dd hh24:mi:ss')

ORDER BY t.PASSINGTIME DESC

-- 查看执行计划

select * from table(dbms_xplan.display)

执行结果如下:

 

Plan hash value: 2713456464

 

------------------------------------------------------------------------------------------------------------

| Id  | Operation             | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |

------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |            |  2258K|   629M|       |   167K  (1)| 00:33:35 |       |       |

|   1 |  SORT ORDER BY        |            |  2258K|   629M|   705M|   167K  (1)| 00:33:35 |       |       |

|*  2 |   FILTER              |            |       |       |       |            |          |       |       |

|   3 |    PARTITION RANGE ALL|            |  2258K|   629M|       | 26024   (2)| 00:05:13 |     1 |1048575|

|*  4 |     TABLE ACCESS FULL |MI_TEST_NUM|  2258K|   629M|       | 26024   (2)| 00:05:13 |     1 |1048575|

------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter(TO_DATE('2020-06-10 00:00:00','yyyy--mm-dd hh24:mi:ss')<=TO_DATE('2020-06-21

              23:59:59','yyyy--mm-dd hh24:mi:ss'))

   4 - filter("T"."PASSINGTIME">=TO_DATE('2020-06-10 00:00:00','yyyy--mm-dd hh24:mi:ss') AND

              "T"."FLAG"='H' AND "T"."PASSINGTIME"<=TO_DATE('2020-06-21 23:59:59','yyyy--mm-dd hh24:mi:ss'))

以上结果可以看出 TABLE ACCESS FULL 没有用索引,而是全表扫描

3、尝试解决过程

经过查询,有人说,当查询的结果大于总数的30%,Oracle就会放弃使用索引,我就尝试查1、询2个小时的数据,确实查询很

SELECT * FROM MI_TEST_NUM T   

 WHERE PASSINGTIME >=to_date('2020-06-14 00:00:00','yyyy--mm-dd hh24:mi:ss')

and PASSINGTIME<=to_date('2020-06-14 02:59:59','yyyy--mm-dd hh24:mi:ss')

ORDER BY PASSINGTIME DESC

用时间:10ms

查询数据:181236

总数据量:119666551条

查询数据/总数据量:0.0015145084276725

索引使用:使用索引

使用执行计划explain plan FOR 查看是使用索引的

 

 

2、询24个小时的数据,确实查询很

SELECT * FROM MI_TEST_NUM T   

 WHERE PASSINGTIME >=to_date('2020-06-14 00:00:00','yyyy--mm-dd hh24:mi:ss')

and PASSINGTIME<=to_date('2020-06-14 23:59:59','yyyy--mm-dd hh24:mi:ss')

ORDER BY PASSINGTIME DESC

用时间:1m37s

查询数据:6725049

总数据量:119666551条

查询数据/总数据量:0.0561982353782387

索引使用:不使用索引

 

3、询24个小时的数据,强制使用索引,确实查询很

SELECT /*+index(T MITESTNUM_PASSINGTIME_localidx)*/ * FROM MI_TEST_NUM T   

 WHERE PASSINGTIME >=to_date('2020-06-14 00:00:00','yyyy--mm-dd hh24:mi:ss')

and PASSINGTIME<=to_date('2020-06-16 23:59:59','yyyy--mm-dd hh24:mi:ss')

ORDER BY PASSINGTIME DESC

用时间:1m33s

查询数据:6725049

总数据量:119666551条

查询数据/总数据量:0.0561982353782387

索引使用:使用索引

 

4、解决方案(分页查询)

Oracle不像MySQL查询时会自动给你分页,Oracle查询比如有2千万,他会去查询2千万的数据不会分页,需要自己分页查询

比如我查询前20条

SELECT ROW_.*, ROWNUM ROWNUM_

          FROM (

        SELECT * FROM MI_TEST_NUM T   

 WHERE PASSINGTIME >=to_date('2020-06-14 00:00:00','yyyy--mm-dd hh24:mi:ss')

and PASSINGTIME<=to_date('2020-06-14 02:59:59','yyyy--mm-dd hh24:mi:ss')         

                 

                 ) ROW_

         WHERE ROWNUM <= 20

 

最后修改于 2020-06-16 16:24:28
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付
上一篇