性能考虑:将行分散在多个表中 vs 将所有行集中在一个表中 [英] Performance consideration: Spread rows in multiple tables vs concentrate all rows in one table
问题描述
嗨.
我需要在 SQL DB 中记录有关应用程序中进行的每个步骤的信息.有某些表，我希望日志应该与:产品 - 应该在创建产品时记录更改等.顺序 - 同上运费 - 相同等等等等等等
I need to log information about about every step that goes on in the application in an SQL DB. There are certain tables, I want the log should be related to: Product - should log when a product has been created changed etc. Order - same as above Shipping - same etc. etc. etc.
需要经常检索数据.
我对如何去做的想法很少:
I have few ideas on how to do it:
- 有一个日志表，其中包含所有这些表的列，然后当我想在 UI 中表示某个产品的数据时，将选择 * from Log where LogId = Product.ProductId.我知道有很多列可能很有趣，但我有这种感觉，性能会更好.另一方面，此表中将有大量行.
- 每种日志类型(ProductLogs、OrderLogs 等)都有很多日志表.我真的不喜欢这个想法，因为它不一致，并且有许多具有相同结构的表没有意义，但是 (?)在行数较少的表中搜索时更快(我错了吗?).
- 根据声明编号.1，我可以做第二个多对一表，其中包含 LogId、TableNameId 和 RowId cols，并将日志行引用到数据库中的许多表行，而不是使用 UDF 来检索数据(例如日志 ID 234属于 CustomerId 345 处的 Customer 表和 productId = RowId 的 Product 表)；我认为这是最好的方法，但同样，可能会有大量的行，它会减慢搜索速度吗?或者这就是应该做的，怎么说?...
以上列表中第 3 号的示例:
Example of No. 3 in the above list:
CREATE TABLE [dbo].[Log](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NULL,
[Description] [varchar](1024) NOT NULL,
CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
[LogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Log] WITH CHECK ADD CONSTRAINT [FK_Log_Table] FOREIGN KEY([UserId])
REFERENCES [dbo].[Table] ([TableId])
GO
ALTER TABLE [dbo].[Log] CHECK CONSTRAINT [FK_Log_Table]
---------------------------------------------------------------------
CREATE TABLE [dbo].[LogReference](
[LogId] [int] NOT NULL,
[TableName] [varchar](32) NOT NULL,
[RowId] [int] NOT NULL,
CONSTRAINT [PK_LogReference] PRIMARY KEY CLUSTERED
(
[LogId] ASC,
[TableName] ASC,
[RowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[LogReference] WITH CHECK ADD CONSTRAINT [FK_LogReference_Log] FOREIGN KEY([LogId])
REFERENCES [dbo].[Log] ([LogId])
GO
ALTER TABLE [dbo].[LogReference] CHECK CONSTRAINT [FK_LogReference_Log]
---------------------------------------------------------------------
CREATE FUNCTION GetLog
(
@TableName varchar(32),
@RowId int
)
RETURNS
@Log TABLE
(
LogId int not null,
UserId int not null,
Description varchar(1024) not null
)
AS
BEGIN
INSERT INTO @Log
SELECT [Log].LogId, [Log].UserId, [Log].Description
FROM [Log] INNER JOIN
LogReference ON [Log].LogId = LogReference.LogId
WHERE (LogReference.TableName = @TableName) AND (LogReference.RowId = @RowId)
RETURN
END
GO
推荐答案
我肯定会选择选项 3，原因如下:
I would definitely go for option 3, for several reasons:
数据应该在表格的字段中，而不是作为表格名称(选项 2)或字段名称(选项 1).这样，数据库就更易于使用和维护.
Data should be in the fields of a table, not as a table name (option 2) or a field name (option 1). That way the database gets easier to work with and easier to maintain.
更窄的桌子通常表现更好.与字段数相比，行数对性能的影响较小.
Narrower tables genrally perform better. The number of rows has less impact on the performance than the number of fields.
如果每个表都有一个字段(选项 1)，当只有少数表受到操作影响时，您可能会得到很多空字段.
If you have a field for each table (option 1), you are likely to get a lot of empty fields when only a few of the tables are affected by an operation.
这篇关于性能考虑:将行分散在多个表中 vs 将所有行集中在一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
