您在SQL Server上优化大型表(+1M行)的方法是什么?

1. 对于100万条记录,我不会认为这是一个特别大的表,需要不寻常的优化技术,如拆分表、非规范化等。但是,当你尝试了所有不影响你使用标准查询技术的正常方法后,你就会做出这些决定。

现在,第二种优化方法是制作一个聚集索引。实际上,主索引是自动包含的,我将其设置为包含股票和日期字段的复合索引。这是独一无二的,我不能在同一天为同一只股票提供两个报价数据。

clusted指数确保同一只股票的报价保持一致,并可能按日期排序。这第二条信息是真的吗?

这在逻辑上是正确的——聚集索引定义了磁盘上记录的逻辑顺序,这是您应该关心的。SQL Server可能会放弃在物理块内进行排序的开销,但它仍然会表现得像这样,所以这并不重要。在任何情况下,查询一只股票可能需要读1或2页;并且优化器从页面读取中的无序数据中获益不大。

现在有50万条记录,从特定资产中选择700个报价大约需要200毫秒。我相信随着表格的增加,这个数字会越来越高。

不一定很重要。表大小和查询速度之间没有线性关系。通常有更多更重要的考虑因素。在你描述的范围内,我不会担心。这就是你担心的原因吗?在我看来,200毫秒是很好的,足以让你达到表格加载的地步,你可以开始进行现实的测试,并更好地了解现实生活中的性能。

现在,对于第三种方法,我想也许可以将表格分为三个表格,每个表格对应一个特定的市场(股票、期权和远期)。这可能会将表格大小减少1/3。现在,这种方法会有帮助吗,还是没什么大不了的?目前,该表的大小为50mb,因此可以完全放入RAM中而不会遇到太多麻烦。

不!这种优化还为时过早,可能已经胎死腹中。

另一种方法是使用SQL Server的分区功能。

同样的评论。您将能够长期坚持严格逻辑、完全规范化的模式设计。

还有什么其他好的方法可以使这一过程尽可能快?

最好的第一步是集中库存。除非你看到每秒插入多条记录,否则插入速度根本不重要——我在这里没有看到任何接近该活动的东西。这应该能让你接近最高效率,因为它能有效地读取与股票相关的每条记录,这似乎是你最常见的指数。任何进一步的优化都需要在测试的基础上完成。

一百万张唱片真的没那么大。听起来搜索时间太长了——你搜索的列有索引吗?

一如既往,第一个调用端口应该是SQL分析器和查询计划计算器。询问SQL Server将如何处理您感兴趣的查询。我相信您甚至可以要求它建议更改,例如额外的索引。

我还没有开始讨论分区等问题——正如你所说,现在应该都很舒服地放在内存中,所以我怀疑你的问题更可能是缺少索引。

请先检查该查询的执行计划。确保你的索引正在被使用。我找到了。一百万条记录并不多。为了提供一些视角,我们有一个包含3000万行的库存表,我们的整个查询连接了大量的表并进行了大量的计算,可以在200毫秒内运行。我们发现,在四进程64位服务器上,我们可以有更多的记录,所以我们从来没有费心进行分割。

您可以使用SQL Profier查看执行计划,也可以从SQL Management Studio或查询分析器运行查询。

重新评估指标。..这是最重要的部分,数据的大小并不重要,确实如此,但并不完全是为了速度。

我的建议是重新构建该表的索引,为最需要的列创建一个复合索引。现在你只有几条记录在玩不同的索引,否则一旦你在表中有了所有的历史数据,尝试新事物会变得非常烦人。

完成上述操作后,请检查您的查询,让查询计划评估器成为您的朋友,并检查引擎是否使用了正确的索引。

我刚刚读了你上一篇文章,有一件事我不明白,你在插入数据的同时在查询表格?同时?为了什么?插入是指一条记录还是数十万条记录?你怎么插入?逐一地?

但关键还是索引,先不要搞分区之类的。.特别是对于一个millon记录,这没什么,我有1.5亿个记录的表,返回40k个特定记录需要引擎大约1500ms。..

我在一个学区工作,我们必须跟踪每个学生的出勤情况。这就是我们赚钱的方式。我的表上显示了每个学生的每日出勤记录,目前有3890万条记录。我可以很快地从中提取出一个学生的出勤情况。我们在这个表上保留了4个索引(包括主键)。我们的聚集索引是student/date,它按顺序保存所有学生的记录。在插入学生的旧记录的情况下,我们在这张表中的插入内容受到了打击,但这对我们来说是一个值得冒的风险。

关于选择速度,我当然会在你的情况下利用缓存。

您提到您的主键是(Stock,Date)上的复合键,并且是集群键。这意味着该表按库存组织,然后按日期组织。每当插入新行时,它都必须将其插入表的中间,这可能会导致其他行被推到其他页面(页面拆分)。

我建议尝试将主键反转为(Date,Stock),并在Stock上添加一个非聚集索引,以方便快速查找特定的股票。这将允许插入始终发生在表的末尾(假设您按日期顺序插入),并且不会影响表的其余部分,并且页面拆分的可能性较小。

执行计划显示它很好地使用了聚集索引,但我忘记了一个非常重要的事实,我仍在插入数据!插入物可能过于频繁地锁定桌子。我们有办法看到这个瓶颈吗?

执行计划似乎没有显示任何关于锁的问题。

现在,这些数据只是历史数据,当导入过程完成时,插入将停止,并且频率会大大降低。但我很快就会有一个更大的实时数据表,这将受到影响 恒定插入问题 而且会比这张桌子大。因此,任何优化这种情况的方法都是非常受欢迎的。

另一种解决方案是为每年创建一个历史表,并将所有这些表放入历史数据库中,填写所有这些表,然后为它们创建适当的索引。一旦你完成了这件事,你就再也不用碰它们了。你为什么要一直插入数据?要查询所有这些表,只需将它们“联合”即可:p

今年的表格应该与历史表格大不相同。据我所知,你打算在旅途中插入记录?,我会计划一些不同的事情,比如每天时不时地做一次批量插入或类似的事情。当然,这一切都取决于你想做什么。

这里的问题似乎出在设计上。我会选择新的设计。据我所知,你现在拥有的那个并不合适。

实际上,主索引是自动包含的,我将其设置为包含股票和日期字段的复合索引。这是独一无二的,我不能在同一天为同一只股票提供两个报价数据。

clusted指数确保同一只股票的报价保持一致,并可能按日期排序。这第二条信息是真的吗?

SQL Server中的索引始终按索引中的列顺序排序。因此,[stock,date]上的指数将首先按股票排序,然后按日期在股票内排序。[日期,股票]上的指数将首先按日期排序,然后按股票在日期内排序。

执行查询时,应始终在WHERE部分包含索引的第一列,否则索引将无法有效使用。

针对您的特定问题:如果股票的日期范围查询是最常见的用法,那么在[date,stock]上执行主键,这样数据将按日期顺序存储在磁盘上,您应该可以获得最快的访问速度。根据需要建立其他索引。插入大量新数据后,进行索引重建/统计信息更新。