查询百万级数据太慢的问题请教。

Question

我有一个数据目前接近四百万的表，（数据库oracle）
1：这个表有几个重要字段如下：IMEI ， APP_ID ,STATE ,(其他字段省略)；
2：这个表每新增一条数据，都必须判断IMEI和APP_ID是否已经同时存在过，根据是否已经存在过，来更新STATE 字段；
3：每天新增数据有好几万，所以要判断好几万次；我做过的优化：IMEI和APP_ID都已经建过索引，表也重构过。
备注：IMEI这个字段数据变化较大，APP_ID数据合计就是几千个，变化不大；现在问题是，逻辑是很简单，但是由于数据量太大，每次判断都要10来秒，效率很低，请问是否有更好的设计方法，或者是优化的方法。

Answer 1

1、试一试用merge into 能不能提高性能
2、如果业务允许可以试试，先将每天新增的数据直接放入一张中间表，在一个统一的时间 或者 利用job 不定期的 从A表merge into 到你的400W表中。

Answer 2

你索引怎么建的？是建个联合索引，(app_id,imei), app_id放前面吗？

Answer 3

这是可以提高效率，但是现在问题不在插入，最大问题是查询，查询很慢，我认为oracle处理几百万数据不应该这么慢的，肯定有什么办法可以提高查询的效率的。

Answer 4

不是联合索引，两个都是独立建的，查询的时候是app_id放前面。

Answer 5

select * from fbs_imei where app_id=200 and imei='862951015823710'  ;已启用自动跟踪
仅显示执行计划。
ID                     WORK_ORDER_ID          ORDER_ITEM_ID          CHANNEL_ID                                                                                                                                                                                               IMEI                                                                                                                                                                                                     APP_ID                 MOBILE_MAC_ADDR                                                                                                                                                                                          MOBILE_INST_ID                                                                                                                                                                                                                                                                                                  ADD_DATE                  STATE                REMARK                                                                                                                                                                                                   OPER_USER_ID           SUB_OPER_USER_ID                                                                                                                                                                                         CLIENT_TYPE                                                                                                                                                                                              APP_TEMPLATE_TYPE                                                                                                                                                                                        IS_REWARD            1653700                499607                 2007737                GDSZCK                                                                                                                                                                                                   862951015823710                                                                                                                                                                                          200                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              03-6月 -12                                                                                                                                                                                                                                              9840                                                                                                                                                                                                                                                                                                                                                                                                                                     APP                                                                                                                                                                                                      reward               Plan hash value: 666258702
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    97 |  4050   (1)| 00:00:49 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| FBS_IMEI        |     1 |    97 |  4050   (1)| 00:00:49 |
|*  2 |   INDEX RANGE SCAN          | FBS_IMEI_INDEX1 | 12384 |       |   621   (2)| 00:00:08 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("IMEI"='862951015823710')
   2 - access(SYS_OP_DESCEND("APP_ID")=HEXTORAW('3DFCFF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("APP_ID"))=200)

Answer 6

把索引 rebuild一下
alter index index_name rebuild;

Answer 7

做一个联合索引
 imei在前面

Answer 8

用联合索引create index t_idx on t(app_id,imei);你看你的查询计划，只用到了FBS_IMEI_INDEX1，imei上的索引。

Answer 9

还有建好后，执行下统计
begin
  dbms_stats.gather_table_stats(user,'T');
end;