代码之家 › 专栏 › 技术社区 › Dickon Reed

哪些索引实现可以处理任意列组合?

indexing database sql

Dickon Reed · 技术社区 · 16 年前

我正在开发一个带有web界面的小型数据仓库系统,人们可以在其中进行过滤搜索。目前,人们可能希望过滤大约50列,大约250万行。表格扫描速度非常慢。问题是,我收到的查询范围没有共同的前缀。

现在我使用的是sqlite3,只有当所需的列是索引中最左侧的列时,它才会使用索引。这似乎意味着我需要很多索引。快速浏览一下MySQL,就会发现这类查询也需要许多索引。

我的问题是,对于可以处理任意列组合的这种查询的不同数据库系统,有哪些索引实现可用?

我已经制作了自己的索引方案原型;我在我的大表中存储了额外的表,其中列出了整数主键,每列的每个值都出现在这个表中,我保留了足够的统计数据,以便能够首先检查匹配次数最少的值。它工作正常;比表扫描好得多,但仍然有点慢,这对于Python的第一个版本执行许多SQL查询来说并不奇怪。

4 回复 | 直到 8 年前

Remus Rusanu 16 年前

有 column-oriented databases 它以每列为基础存储数据,其中每列都是自己的索引。它们非常适合数据仓库,因为它们读取速度极快,但更新速度相当慢。

Kickfire 就是这样一个例子,它是一个定制的MySQL引擎 TPC-H benchmark 以令人印象深刻的系统成本,连续数周获得顶级冠军。请注意,Kickfire是一种设备,作为硬件盒出售。

Infobright 这将是另一个类似的例子,并且有一个免费的 community edition

Nicolas Buduroi 16 年前

当一个表有太多的索引需要创建时,我通常会求助于全文搜索。但我不能说它是否适合你的情况。

HLGEM 16 年前

SInce数据仓库通常针对读取数据而不是写入数据进行优化,我会考虑简单地对所有列进行索引。是的,这会减缓将数据放入仓库的速度,但通常发生在非高峰时段,每天只发生一次或更少。

mjv 16 年前

人们应该只考虑引入基于SQL表的“自制”索引结构,作为最后的手段,即如果仍然存在无法用传统索引设置正确处理的[业务上合理的]查询情况。例如,如果这些索引的列表变得太大等等。

您不一定需要包含以下内容的索引全部一个特定查询中可能涉及的列;可能只需要[集体]选择性的。

不如果c或d不是非常合理的搜索条件(很少使用),并且如果它们的宽度会给a+b索引带来过重的负担(或者如果有其他列更适合“附加”到a+b指数上),则引入带有a、b和c(或和d或两者)的索引。

除了对磁盘存储的明显额外需求外,额外的索引虽然可能有助于SELECT(读取)操作,但也可能成为CUD(创建/更新/删除)操作的障碍。这里的上下文似乎类似于数据仓库,很少发生[未计划的]CUD操作,但最好记住这一点。

看 SQLite Optimizer 以深入了解SQLite如何确定特定查询的执行方式。

制作索引列表
暂定的基础因为此应用程序的索引方案可能看起来像这样:

实际列表 以下各项所需的指标:

在上面的[B]索引末尾(以深思熟虑的顺序……)添加一个(或几个)额外的列。通常,选择这些列是因为它们的宽度相对较小(它们确实会过度增加索引),而且它们有相对的机会与索引中引用的列结合使用。
删除通常与一个或多个[B]索引等效的[A]索引。也就是说:以同一列开头的列,额外的列不会对索引造成太大负担。
审查所有可能(或所有可接受)情况的TREE,并标记出符合上述指标的分支。然后为不易涵盖的奇怪用例添加更多索引(如果只使用部分索引扫描+主表查找可接受的行数)。

在这种情况下,我发现 手写的树形结构 一个有用的工具,可以帮助管理原本无法管理的可能组合列表。假设从问题中指出的50列中最多选择4个搜索条件,我们有超过230000个组合需要考虑。..这棵树有助于很快修剪。