SQL：我怎么加快这个查询

Question

这是情况。我有一个表包含基于许多不同表中的记录的记录（下面的t1）。 t2是将信息从t1中抽出的表格之一。

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

t2 
    t2_id 

这是我需要找到的：我需要最大的t2_id，其中store_num在t1的相应记录中不为空。这是我写的查询：

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

但是，这需要相当长的时间。我认为这应该是一个快速查询。所有_ids都有索引。 （t1.id/t1.table_oid，t2.t2_id）。 t1中的绝大多数条目都有一个store_num。

精神上，我会按照desc顺序获取t2_ids，然后逐个尝试它们，直到找到第一个有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 ？当我解释它时，它回来的次数超过了3次。

真的，我的问题是如何重新编写该查询以加快运行速度。

注意：我正在使用Oracle。

编辑：

t2超过11895731行
T1已经473235192行

编辑2：

正如我已经尝试不同的东西，那就是时间最长的查询的一部分在t1上查找store_num的完整扫描。有没有办法阻止这样做全面扫描，因为我只需要最大的入口？

Answer 1

你说：

所有_ids对他们

指标，但您的查询是：

... 
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)上创建索引。有关为单个即席查询创建索引的标准免责声明适用;索引太多会影响插入/更新性能。

如何“重写”查询并不重要 - 这与应用程序代码不同，优化程序将重新排列查询的所有部分。除非在查找列上有足够选择性的索引，否则整个查询完全由单个索引覆盖，否则将会很慢。

Answer 2

不确定这些是否适用于Oracle。你有没有在联盟的fk id列的索引。另外，如果您可以避免使用'NOT IN'，那么SQL中的非可搜索类型会降低查询速度。

另一个可能较慢的选项是执行外连接，然后在该列上检查null。 （不知道这只是也适用于SQL）

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

Answer 3

我能想到的最好的办法有这个跑得快是：

1. 创建上（TABLE_OID，ID DESC，COVERED_ENTITY_ID指数） 以该顺序。为什么？

table_oid - 这是您的主要接入条件 ID - 所以你不必访问数据块读它， - 你得到更高的ID值第一 covered_entity_id - 您正在过滤基于此的数据，null与非null

这应该防止需要访问T1中的473m行。

1. 确保T2_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); 

由于优化器可能写入这些计划的方式稍有不同。

Answer 4

在下面，我假设covered_entity_id与store_num相同 - 如果您的命名一致，它会让我们更容易。

t1 中的绝大多数条目都有一个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？您可以通过从T1中选择max(id)来满足您的要求（除非您没有外键执行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。