sql-server - 当操作涉及对任何列进行排序、聚合和过滤时，针对读取(低/无写入)进行了优化的内存数据库

Question

我希望将 ~10GB 的数据加载到内存中并以以下形式对其执行 SQL:

按单列(任何列)排序

在单个列(任何列)上聚合

过滤单列(任何列)

什么是性能的好选择？我遇到的一些可能有效的解决方案是 TimesTen 、 ExtremeDB 和 SQL In-memory ，甚至是 Vaex 或 Cudf 等数据帧。
我希望优化查询时间——这才是我真正关心的。对于概念性示例，请考虑类似 Excel 表的内容，用户可以在其中对任何列进行排序或过滤(并且应用程序不知道要“索引”哪些列，因为可能会使用所有列)。

更新 :我在下面发布了来自 Pandas 的基准测试。我知道 Pandas 不适合这个，但是制作原型(prototype)并获得基准数据很棒:
文件 (20M 行，1.2GB): https://storage.googleapis.com/gcp-files/Sales20M.csv 。

加载时间 ( pd.read_csv ): 10.7s

聚合 : ( df.groupby('currency_code_id').count ): 3.3s

排序 : ( df.sort_values('price') ): 6.8s

枢轴 : ( df.pivot_table(index='code',columns='territory_id', values='id', aggfunc=len, fill_value=0) ): 3.4s。

如果使用数据库，请不要创建索引，因为用例是我们事先不知道使用的列。 (或者，我想您可以在每个字段上创建一个索引——但如果是这样，请在加载时包括所有这些索引的创建)。
哪种工具最适合这种情况？

Answer 1

我猜您想对随机数据文件进行 materialized 并对其执行亚秒级查询，并且您已准备好为此付出代价(因为内存功能通常是企业级的)。
以 SQL Server 为例，有很多选项:

使用 column store indexes which achieve gains up to 10 times the query performance

在 in memory tables 中使用并从 5 times to 20 times faster 性能

中获取

使用 Azure Hyperscale

或者只是使用分区，或者 PostgreSQL 或 MongoDB 分片。有很多此类技术的示例和演示显示亚秒级性能……但这取决于具体情况，因为存在局限性。
例如:

与传统索引相比，

列存储索引在过滤和仅获取几行时可能会出现问题

in-memory OLTP

在您的情况下，拥有 10 GB 的数据并希望获得良好的性能，您不需要做一些特别的事情。只需在插入之前分析和规范化数据并创建相应的索引。 Well begun is half done 并花一些时间以正确的方式写入数据将为您提供所需的性能。
例如:

在表中插入数据文件

对于表中的每一列

执行 count distinct

如果值更小 count distinct / count 小于 X，则创建一个单独的表，其中包含 id 和 value

在其中插入不同的值

向表中添加新列并在那里添加新 id

在该列上创建索引

减小表的大小将提高 IO 操作计数。按数字搜索和分组比按文本搜索和分组要快。
当然，您需要更改应用程序 - 不是按 some city name 搜索，而是按其 ID 进行过滤。在每个 count 的城市 countries ids 之后，您将执行第二个查询以将这些 ids 转换为 names 。
我觉得在你的案例中应用一些基本原则会比使用一些高价格和限制的高级技术更好，这些技术在 future 对应用程序提出新要求时可能至关重要。

在具有 8 GB RAM 和 4 个虚拟处理器的虚拟机上。不幸的是，它位于 HDD 上，并且来自此处的 I/O 非常糟糕。运行 SQL Server 2019 标准版。因此，由于硬件的原因，数据加载速度很慢。

表 2 分钟(我使用 SSMS 接口(interface)导入数据，如果使用 bcp 会更好，但...毕竟是 HDD)

1.5 分钟在每个字段上添加索引

因此，该表如下所示:

你可以看到我有多懒。每个字段都没有标准化和索引，导致为数据和索引分配了 3.2 GB:

exec sp_spaceused 'dbo.Sales20M'

但有些结果:
从 dbo.Sales20M 组中选择 count(*) ,currency_code_id bycurrency_code_id
当然，我们使用索引时为 0 秒:

select TOP 10 * from dbo.Sales20M   order by PRICE_IN_USD desc;

0 秒，但请注意我使用的是 TOP - 基本上，当您进行排序时，您需要显示部分行，对吗？如果我出于某种原因对整行进行排序:

select  * 
INTO #X
from dbo.Sales20M   order by PRICE_IN_USD desc;

它运行了 2 秒(我将数据插入表中，因为渲染也需要时间)。
至于 PIVOT ，它在 SQL Server 中不是很快，但如果你需要大量的东西，你可以使用 R 。我不明白你的，但通过 PIVOT 和 Code 制作了一个 territory_id 计算美元的平均价格:

SELECT *
FROM 
( 
    SELECT Code
              ,territory_id
              ,AVG(price_in_usd) price_in_usd
    FROM dbo.Sales20M
    GROUP BY Code
            ,territory_id
)DS
PIVOT
(
    MAX(price_in_usd) FOR territory_id IN ([AE], [AG], [AI], [AL], [AM], [AO], [AR], [AT], [AU], [AW], [AZ], [BA], [BE], [BF], [BG], [BH], [BJ], [BM], [BN], [BO], [BR], [BS], [BW], [BY], [BZ], [CA], [CH], [CI], [CL], [CO], [CR], [CV], [CY], [CZ], [DE], [DK], [DM], [DO], [EC], [EE], [EG], [ES], [FI], [FJ], [FM], [FR], [GA], [GB], [GD], [GH], [GM], [GR], [GT], [GW], [HK], [HN], [HR], [HT], [HU], [ID], [IE], [IL], [IN], [IS], [IT], [JM], [JO], [JP], [KG], [KH], [KN], [KR], [KW], [KY], [KZ], [LA], [LB], [LK], [LT], [LU], [LV], [MD], [MK], [ML], [MN], [MO], [MT], [MU], [MX], [MY], [MZ], [NA], [NE], [NI], [NL], [NO], [NP], [NZ], [OM], [PA], [PE], [PG], [PH], [PL], [PT], [PY], [QA], [RU], [RW], [SA], [SE], [SG], [SI], [SK], [SN], [SV], [SZ], [TG], [TH], [TJ], [TM], [TR], [TT], [TW], [TZ], [UA], [UG], [US], [UY], [UZ], [VE], [VG], [VN], [ZA], [ZM], [ZW])
) PVT;

我又偷懒了，没有使用动态 PIVOT。需要0-1秒。


结论:
我的观点是，即使我的设置很糟糕，而且我非常懒惰，因为没有花时间对数据进行规范化并创建适当的索引，我仍然得到接近 0 秒的结果。你可以简单地从像 PostgreSQL 这样的免费软件开始，我相信你会得到很好的结果。当然，如果您需要它们以优化特定用例，“花哨”的东西总是存在的。