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

