大数据量下，查询速度优化，多索引情况

[liugabvtc]

-- Create table
create table TDDEMO
(
UID NUMBER(13) not null,
LID NUMBER(7) not null,
START_DATE DATE not null,
END_DATE DATE not null,
EXPIRE_DATE DATE not null,
NAME VARCHAR2(16) default 0,
UPDATE_DATE DATE default SYSDATE
);
两个索引：
create index TDDEMO_INDEX1 on TDDEMO (UID, LID)
create index TDDEMO_INDEX2 on TDDEMO (EXPIRE_DATE)
大约600万数据，
我有如下两种语句需要很频繁的执行，
Select UID, LID, NAME from TDDEMO where UID >= XX and UID <= YY and LID = ZZ order by UID ;执行需要 17ms, 说明，每次结果大约1000条，
Delete from TDDEMO where expire_date < sysdate; 基本不花时间，
大家看看我可以怎么优化一些，期望查询语句能更快。

[sych888]

看一下查询计划 另外 LID 字段的区分度高吗？ 考虑一下在LID 字段上加索引？

[liugabvtc]

是说给LID单独加一个索引是么。 我建过索引，基本上没有效果。LID的区分不是很高， 他的值就在1-1000， 大概是这样，一个UID，有1000个LID，由 UID和LID组成的主键。 另外问下，因为我没给这个表建立主键，是不是加上 UID 和LID作为主键后，会好很多。因为数据在运行，暂时没法建，如果有效果的话，我就停了系统来建立一个。

[liugabvtc]

SQL> explain plan for select uid,lid,name,suspend from tddemo where uid>=171 798988800 and uid<=171798988823 and lid=9 order by uid; 已解释。 SQL> select * from table(DBMS_XPLAN.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 251470869 -------------------------------------------------------------------------------- ---------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ---------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 17 | 544 | 10 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| tddemo | 17 | 544 | 10 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | tddemo_INDEX | 17 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- ---------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("uid">=171798988800 AND "lid"=9 AND "uid"<=171798988823) filter("lid"=9) 已选择15行。 SQL> explain plan for select uid,lid,name,suspend from tddemo where uid>=171 798988800 and uid<=171798989823 and lid=9 order by uid; 已解释。 SQL> select * from table(DBMS_XPLAN.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- Plan hash value: 251470869 -------------------------------------------------------------------------------- ---------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ---------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 753 | 24096 | 317 (0)| 00:00:04 | | 1 | TABLE ACCESS BY INDEX ROWID| tddemo | 753 | 24096 | 317 (0)| 00:00:04 | |* 2 | INDEX RANGE SCAN | tddemo_INDEX | 753 | | 11 (0)| 00:00:01 | -------------------------------------------------------------------------------- ---------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("uid">=171798988800 AND "lid"=9 AND "uid"<=171798989823) filter("lid"=9) 已选择15行。 SQL>

[sych888]

你已经建立了复合索引，查询结果1000条，理论上查询计划会走索引的，你把查询计划贴出来看看

[u010412956]

17ms? 又不是17s

[linwaterbin]

1）从你执行计划可以看出、目前你的索引确实没问题了 2）麻烦贴上统计信息？结合执行计划一起分析

[liugabvtc]

是的，17毫秒

[linwaterbin]

引用 2 楼 liugabvtc 的回复:

是说给LID单独加一个索引是么。 我建过索引，基本上没有效果。LID的区分不是很高， 他的值就在1-1000， 大概是这样，一个UID，有1000个LID，由 UID和LID组成的主键。 另外问下，因为我没给这个表建立主键，是不是加上 UID 和LID作为主键后，会好很多。因为数据在运行，暂时没法建，如果有效果的话，我就停了系统来建立一个。

1）不必麻烦在LID上单独建立索引了、LID会用上复合索引 TDDEMO_INDEX1 2）明白主键的作用就不会这么想了、主键是负责"外交"的、你2个频繁sql都不做关联、没必要建 3）感觉你的sql可优化的空间不会很大了哈、order by UID 已经在索引里面帮你搞好了、这部分没有开销 4）贴上执行计划来看看吧

[shy315]

引用 楼主 liugabvtc 的回复:

create index TDDEMO_INDEX1 on TDDEMO (UID, LID) Select UID, LID, NAME from TDDEMO where UID >= XX and UID <= YY and LID = ZZ order by UID ;

一般是这样吧

create index TDDEMO_INDEX1 on TDDEMO (LID, UID);

试试