假设我有一个表,用于存储来自移动设备的跟踪信息(每条记录都有 deviceID、事务信息、一个 json 列,其中包含有关操作、操作时间、用户等的完整详细信息...),这是操作数据库。某些用户可能需要获取有关特定用户或设备的某个时间范围的跟踪信息。因此,使用过滤器进行简单的选择,不会影响数据插入。
其他一些用户将需要进行更大规模的分析操作,汇总数月收集的数据。我读到这个分析过程应该在分析数据库上完成,以免影响操作数据库。
但我有一个限制,只有一个 SQLServer 实例可用。
因此,我应该编写一个 ETL,在新数据库上定期批量复制数据,使用带有更多索引的同一个表,并且没有 json 列(以节省空间,因为对于我们需要的那种分析来说是无用的)
现在我的问题是,考虑到我们正在谈论操作表和分析表(不仅仅是操作数据库和分析数据库),并且在我的客户端,我只有一个 SQLServer 实例,这是否有意义将两个表保留在同一个 SQLServer 实例的同一个数据库中?与使用单个表进行操作和分析查询相比,我的性能是否有所提高?换句话说,我有两种选择,使用一个 SQLServer 实例:
- 在一张包含我需要的所有索引的表上插入数据,并查询该表以进行跟踪和分析
- 在一个索引很少的表上插入数据,然后将其复制到另一个索引较多且总体大小较小的表,并查询第一个表进行跟踪,查询第二个表进行分析
如果您告诉我,如果我将所有内容都放在同一个 SQLServer 上,则 1 和 2 在性能方面不会有太大差异,那么我会坚持使用 1,因为它更容易。
如果我在分析表上进行几分钟长的聚合,那么在操作表上写入的速度是否会比直接在操作表上进行分析的速度减慢或相同?
谢谢
正如评论中已经提到的,哪种实现更适合您的答案将取决于具体的用例和数据库中表的读写比率,以及一般的整体资源争用。
选项2
如果对表有大量并发读取和写入,那么它们可能会互相锁定和阻塞(使用 SQL Server 中的默认隔离级别)。在这种情况下,拥有表的第二个实例可以解耦(如果可能)读取和写入用例的重叠,可以提高锁定方面的性能。通常,最好将第二个实例隔离到单独的服务器,以消除资源争用,但正如您所指出的,这在您的情况下是不可能的。
但是两次写入相同的数据并不是免费的。再说一次,在同一台服务器上将使用双倍的资源,而以前您只将内容保存在一个表中。为了最大限度地发挥读写用例解耦的优势,您可能希望在与读取用例发生时间不同的时间批量写入数据。这将使您的表副本变得陈旧,而不是与原始表实时同步。当原始表上发生架构更改时,还需要进行额外的维护来保持表结构同步。这不是一个简单的任务。
选项1
相反,如果您没有大量并发读取和写入,或者有大量并发,但总体上没有大量查询和资源争用,则表的单个实例会为您的大部分数据进行适当的索引。主要用例,可能就很好了。锁定时间可能是毫秒到纳秒,就您的应用程序而言是无法估量的。
选项0
除此之外,第三个替代选项是将数据库隔离级别更改为更有利于乐观并发的级别。在 SQL Server 中,选项是读提交快照隔离 (RCSI) 和快照隔离。简而言之,乐观并发通常允许读和写查询同时对同一个表进行操作,而不会互相阻塞。读者不会阻止作者,作者也不会阻止读者。(写入者仍会阻止其他写入者。)这是有效的,因为版本存储维护表中主动更改记录的每个版本。当写入查询当前正在对行进行更改时,可以读取其先前的版本,直到提交写入更改并释放其锁定。
乐观并发隔离级别的两个选项都有不同的优缺点(如链接文章中所述),但我更喜欢 RCSI,它可以在服务器上全局启用乐观并发。这类似于 Azure SQL 数据库和其他一些现代数据库系统开箱即用的工作方式。打开它本质上是用一行代码打开数据库属性的开关。