索引扫描令人费解的性能。为什么扫描索引变慢，即使结果集很小并编入索引

问题描述：

我有大表ir_data（150GB），其中包含不同日期的数据（列val_date）。我需要知道在我的应用程序中的各个点上ir_data是否有给定的日期。索引扫描令人费解的性能。为什么扫描索引变慢，即使结果集很小并编入索引

select distinct(val_date) from ir_data

我下面的实验ir_data包含val_date的29个不同的值。

SETUP 1

我预期ir_data（val_date，KEY_ID，other_colum），以帮助快速找到29个值的指数。事实上这需要超过5分钟：

查询1的1，行写着：29，经过时间（秒） - 合计：343.96， SQL查询：343.958，阅读效果：0.002

我总是期望索引是一个树，其中的节点存储在树形结构中，例如像这样

val_date -> key_id -> other_column -> data-nodes 

1.1.2017 -> 0-50  -> A   -> (1.1.2017, 0, Automobile), (1.1.2017, 2, Amsterdam) 
        -> B-E   -> (1.1.2017, 12, Batman) 
     -> 51-100 -> A   -> ... 
         X 
     -> 666-1000 -> A 
        -> B-C 
        -> E 
2.1.2017 -> ...

根据这个结构得到29个不同的val_dates应该很快。

问题：为什么需要这么长时间？

子问题：有没有办法解决这个问题而不创建另一个表？

SETUP 2

我创建仅包含val_date另一个索引。这需要大致相同的时间。

查询的计划：

The type of query is SELECT. 

2 operator(s) under root 

    |ROOT:EMIT Operator (VA = 2) 
    | 
    | |GROUP SORTED Operator (VA = 1) 
    | |Distinct 
    | | 
    | | |SCAN Operator (VA = 0) 
    | | | FROM TABLE 
    | | | ir_data 
    | | | Index : ir_data_idx1 <-- this is the index containing only val_date. 
    | | | Forward Scan. 
    | | | Positioning at index start. 
    | | | Index contains all needed columns. Base table will not be read. 
    | | | Using I/O Size 16 Kbytes for index leaf pages. 
    | | | With MRU Buffer Replacement Strategy for index leaf pages.

您的查询是什么？ –

@OfirWinegarten是的，我应该提到这一点。添加。 – Beginner

答

你的表是非常大的，以及你的索引。如您在计划中看到的，引擎执行索引扫描。此操作将会很漫长，因为它会扫描您的整个索引以获得不同的值。

作为第一步，您可以在索引上尝试update index statistics，不过，我并不认为这会有所帮助。

如果是一次性手动操作，我想你会满意5分钟的操作。

如果这是你的应用程序执行的查询，那么你有两个选择我能想到的：

正如你在问题中说的 - 通过创建日期附加表正常化的表并使用FK。
创建一个precomputed result set。这是一个物化视图 - 结果像常规表一样存储（与只存储定义的视图相反）。
它会自动刷新视图中的结果并快速检索值。
重要：比如索引，这将对插入，更新的性能产生影响...
它可以看起来像：
```
create precomputed result set prs_ir_data 
immediate refresh 
as 
select distinct val_date 
from ir_data 
```

你可以阅读有关预先计算的结果here和here设置为

谢谢。你知道他们使用什么样的数据结构吗？我发现很难理解为什么需要扫描整个索引来查找搜索结构的顶级属性。 – Beginner

外键思路非常好，我没有想到。 – Beginner

很高兴我能帮到你。我不确定sybases索引的内部结构，但肯定是某种b-tree允许快速搜索和插入 –

答

A recursive CTE相当快地加速了这个查询，即在一个大表中很少有不同的值。问题在于，在搜索不同值时搜索索引目前尚未实现。这是一个link to the approach。

索引扫描令人费解的性能。为什么扫描索引变慢，即使结果集很小并编入索引

相关推荐