投票系统：我应该使用 SQL 触发器还是更多代码？

发布于 2024-10-13 05:17:11 字数 530 浏览 3 评论 0原文

我正在构建一个投票系统，其中每张投票都被捕获在投票表中，其中包含投票的 UserID 和 DateTime 以及 int 1 或 -1。

我还在表中保留了 TotalVotes 的运行总数，其中包含用户实际投票的项目。这样我就不会不断地运行对 Vote 表进行 SUM 的查询。

我的问题是更新 TotalVotes 字段时的优点/缺点问题。就代码可管理性而言，在应用程序中添加额外的更新方法可以轻松排除故障并发现任何潜在问题。但是，如果该应用程序的用户群显着增长，则可能会导致从应用程序到数据库的大量额外 SQL 调用。可以这么说，使用触发器将其“全部纳入 sql 系列”，并且应该会增加一点性能提升，并将日常活动排除在代码库之外。

我知道在这个特定问题中可以调用过早优化，但由于我还没有构建它，我不妨尝试立即找出更好的方法。

就我个人而言，我倾向于触发器。请告诉我你的想法/推理。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

反话 2024-10-20 05:17:11

另一种选择是在投票表上创建视图，将投票聚合为 TotalVotes。
然后索引视图。

SQL Server 优化器（我认为仅限企业版）的神奇之处在于，当它看到 sum(voteColumn) 的查询时，它会从相同数据视图的索引中选取该值，当您认为自己是不要在查询中直接引用视图！

如果您没有企业版，您可以查询视图而不是表上的总票数，然后利用索引。

索引本质上是优化器所知道的数据的非规范化。您根据需要创建或删除它们，然后让优化器弄清楚（不需要更改代码）一旦您开始走上自己手工制作的非规范化之路，您将在未来几年将其融入到您的代码中。

查看通过索引视图提高性能

有一些特定的标准必须满足满足使索引视图正常工作的要求。以下是基于对数据模型的猜测的示例：

create database indexdemo
go
create table votes(id int identity primary key, ItemToVoteOn int, vote int not null)
go

CREATE VIEW dbo.VoteCount WITH SCHEMABINDING AS
select ItemToVoteOn, SUM(vote) as TotalVotes, COUNT_BIG(*) as CountOfVotes from dbo.votes group by ItemToVoteOn
go
CREATE UNIQUE CLUSTERED INDEX VoteCount_IndexedView ON dbo.VoteCount(itemtovoteon)
go
insert into votes values(1,1)
insert into votes values(1,1)
insert into votes values(2,1)
insert into votes values(2,1)
insert into votes values(2,1)
go

select ItemToVoteOn, SUM(vote) as TotalVotes from dbo.votes group by ItemToVoteOn

此查询（不引用视图或扩展其索引）会产生此执行计划。请注意使用了索引。当然，删除索引（并获得插入性能）

alt text

最后一句话。在您启动并运行之前，您将知道任何类型的非规范化实际上是否有助于整体吞吐量。通过使用索引，您可以创建它们，衡量它是否有帮助或有害，然后根据需要保留或删除它们。这是唯一一种可以安全执行的非规范化性能。

Another option is to create view on the votes table aggregating the votes as TotalVotes.
Then index the view.

The magic of SQL Server optimizer (enterprise edition only i think) is that when it sees queries of sum(voteColumn) it will pick that value up from the index on the view of the same data, which is amazing when you consider you're not referencing the view directly in your query!

If you don't have the enterprise edition you could query for the total votes on the view rather than the table, and then take advantage of the index.

Indexes are essentially denormalization of your data that the optimizer is aware of. You create or drop them as required, and let the optimizer figure it out (no code changes required) Once you start down the path of your own hand crafted denormalization you'll have that baked into your code for years to come.

Check out Improving performance with indexed views

There are some specific criteria that must be met to get indexed views working. Here's a sample based on a guess of your data model:

create database indexdemo
go
create table votes(id int identity primary key, ItemToVoteOn int, vote int not null)
go

CREATE VIEW dbo.VoteCount WITH SCHEMABINDING AS
select ItemToVoteOn, SUM(vote) as TotalVotes, COUNT_BIG(*) as CountOfVotes from dbo.votes group by ItemToVoteOn
go
CREATE UNIQUE CLUSTERED INDEX VoteCount_IndexedView ON dbo.VoteCount(itemtovoteon)
go
insert into votes values(1,1)
insert into votes values(1,1)
insert into votes values(2,1)
insert into votes values(2,1)
insert into votes values(2,1)
go

select ItemToVoteOn, SUM(vote) as TotalVotes from dbo.votes group by ItemToVoteOn

And this query (which doesn't reference the view or by extension it's index) results in this execution plan. Notice the index is used. Of course drop the index, (and gain insert performance)

alt text

And one last word. Until you're up and running there is really know way that you'll know if any sort of denormalization will in fact help overall throughput. By using indexes you can create them, measure if it helps or hurts, and then keep or drop them as required. It's the only kind of denormalization for performance that is safe to do.

回复收藏 0 原文