代码之家 › 专栏 › 技术社区 › David Oneill

SQL:如何加速这个查询

optimization oracle sql

David Oneill · 技术社区 · 16 年前

情况是这样的。我有一个表,其中包含基于许多不同表中的记录的记录(下面的t1)。t2是一个表,它将信息从表中拉入t1。

t1
  table_oid --which table id is a FK to
  id        --fk to other table
  store_num --field

t2
  t2_id

select max(id) from t1
join t2 on t2.t2_id = t1.id
where store_num is not null
and table_oid = 1234;

在精神上,我会按照描述的顺序得到t2的id,而不是一个接一个地,用t1来测试它们,直到找到第一个有store-num的。

select t2_id from t2 order by t2_id desc;

成本是25612

select t1.* from t1 where table_oid = 1234
and id in (select max(t2_id) from t2);

费用是8英镑。

那么,为什么上面的查询的开销最多不会是25612*8=204896呢?当我解释的时候,它回来了不止三倍。

实际上,我的问题是如何重新编写查询以更快地运行。

注意:我使用的是Oracle。

t2有11895731行

编辑2:

由于我尝试了不同的方法,查询中花费时间最长的部分是t1上的完整扫描,以查找store\u num。有没有办法阻止它进行完整扫描,因为我只需要最大的条目?

4 回复 | 直到 16 年前

Aaronaught 16 年前

你说:

所有的ID都有索引

...
where store_num is not null
and table_oid = 1234;

你所有的 _id store_num 和 table_oid

max(id) 立即没有任何过滤条件,但只要你把过滤器,它不能使用 id store_num is not null 条目-没有扫描就不行。

(store_num, table_oid, id) . 关于为单个即席查询创建索引的标准免责声明适用;索引太多会影响插入/更新性能。

实际上,如何“重写”查询并不重要——这不像应用程序代码,优化器无论如何都要重新排列查询的所有部分。除非在seek列上有足够的选择性索引,或者整个查询完全被一个索引覆盖,否则速度会很慢。

Patrick Kafka 16 年前

不确定这些是否适用于Oracle。在fk id列中是否有用于联接的索引。另外,如果您可以避免'notin'在SQL中不是一个不可搜索的类型,这会减慢查询的速度。

select max(id) from t1
left outer join t2 on t2.t2_id = t1.id
where t1... IS NULL
and table_oid = 1234;

Adam Musch 16 年前

按该顺序在(表\ OID、ID DESC、覆盖\实体\ ID)上创建索引。为什么?

表\u oid—这是您的主要访问条件 --你先得到更高的ID值 covered\u entity\u id—您正在基于此筛选数据,null vs not null

这样就完全不需要访问T1中的473m行了。

确保T2\u ID上有索引。

如果所有这些都准备好了,查询如下:

select max(id) 
  from t1     
       inner join t2 
          on t2.t2_id = t1.id     
 where covered_entity_id is not null     
   and table_oid = 1234;

应该是(优化器是一个挑剔的野兽)能够做一个半连接驱动的快速全面扫描索引对T1,从来没有扫描数据块。也要考虑把它写得像:

select max(id) 
  from t1     
 where covered_entity_id is not null     
   and table_oid = 1234
   and exists (select null 
                 from t2
                where t1.id = t2.t2_id);   

select max(id)
  from t1
 where covered_entity_id is not null
   and table_oid = 1234
   and id in (select t2_id from t2);

因为优化器可能会以稍微不同的方式编写这些计划。

APC 16 年前

下面我假设 covered_entity_id 与相同 store_num

有一个商店号码。

考虑到这种情况,下面的子句不应该对查询的性能有任何影响。。。

where covered_entity_id is not null

然而,你接着说

正在执行的查询部分最长的是t1的全扫描寻找商店号码

这表明查询正在查找 covered_entity_id is not null 首先,而不是更具选择性的 table_oid = 1234

where table_oid = 1234 
and  covered_entity_id is not null;

... 尽管我怀疑不是。您可以尝试暗示获取要在其上使用索引的查询 table_oid .

另一件事是,统计数据有多新鲜?当优化器选择一个非常糟糕的执行计划时,通常是因为统计数据已经过时了。

顺便问一下,你为什么要加入T2?您的要求可以通过选择 max(id) 从T1开始(除非您没有外键) T1.ID 参考文献 T2.T2_ID

编辑

select table_name
       , num_rows
       , last_analyzed
from user_tables
where table_name in ('T1', 'T2')
/

如果结果显示 num_rows 与您在第一次编辑中给出的值有很大差异,那么您应该重新收集统计数据。如果 last_anlayzed Find out more .