代码之家 › 专栏 › 技术社区 › Jhong

把三个表合并成一个表,还是列太多?

query-optimization optimization sql-server sql

Jhong · 技术社区 · 14 年前

一个小时表,包含列链接\id、两个其他属性、小时\1到小时\24,以及一个计算列给出总和
一个工作日表,包含列click\u id、两个其他属性和day\u 1到day\u 7,以及一个计算列,给出总和
一个monthday表,如上所述,带有day_1到day_31列

因此,如果一个用户是类型a,并且坐在X中,那么在上面的表中会创建或添加三行——第一行记录一段时间内对该链接的所有单击,第二行记录“类型a人员”的所有单击,第三行记录“X人员的所有单击”。

我是这样设计的,因为我不想在每个小时/天/周/月移动数据。我只维护“current hour”(1-24)、“current day”(1-31)和“current weekday”(1-7)的指针,并写入表中相应的单元格。当我们进入一个新的时间段(例如“3pm-4pm”)时,我可以将当前列(例如hour15)清空,然后在链接进入时开始递增。每隔一段时间,我就可以删除掉到“全零”的旧行。

我将只选择当前日期/工作日/小时行(在插入/更新之前)或基于属性的计算列中的前20个值(可能会将这些结果缓存一个小时左右)。

三个问题:

把这三张大桌子合并成一张月/日/小时的大桌子可以吗?这将得到一个包含64列的表,我不确定这是否是一种过度的做法。另一方面,将它们分开,就像它们现在是所需的INSERT/UPDATE语句的三倍。我对SQL server了解不够,不知道哪一个是最好的。
这种方法明智吗?当然,我使用过的大多数数据集每个项目都有单独的一行,然后你会按日期排序——但是当跟踪成千上万用户的点击时,这会给我带来数十万行,我必须经常剔除这些行,对它们进行排序和求和会非常糟糕。一旦追踪器被证实,我计划将click listener推出数百个页面,因此它需要扩展。
在设计方面,显然有一些冗余的工作日和月日。然而,这是我能想到的唯一方法来维护一个指向列的指针并快速更新它,以及使用一个计算列。如果我去掉了weekdays表,我需要在“monthdays”上得到一个额外的计算列,该列对前7天进行求和(例如,如果今天是21日,那么求和day_14、day_15、day_16。。。第20天)。计算结果必须每天更新,我想这会很昂贵。因此,额外的“工作日”表用于简单的静态计算。我更看重简单和快速的计算,而不是小数据存储。

3 回复 | 直到 14 年前

Unreason 14 年前

repeating groups across columns )

正如Geert所说,从概念上来说,两个表就足够了。如果性能是一个问题,你可以去规范化每周/每月统计数据,但我仍然不会像上面那样建模,但我会保留

CREATE TABLE base_stats ( link_id INT, click_time DATETIME )
CREATE TABLE daily_stats ( link_id INT, period DATETIME, clicks INT )

SELECT link_id, count(*) as clicks, DATE(click_time) as day
FROM base_stats
GROUP_BY link_id, day

它可以定期运行以填充每日的\u统计数据。如果您想让它保持最新,您可以在触发器中实现它(或者如果您真的必须这样做,请在应用程序端实现它)。如果需要,还可以在不同级别上对数据进行反规范化(通过创建更多聚合表,或在聚合数据表中引入另一列),但这可能是过早的优化。

上面的设计对于将来的特殊分析(将在stats中发生)来说要干净得多。有关其他好处,请参阅维基百科上的重复组。

编辑: 即使解决方案有两个表 base_stats 和 aggregated_stats

在中插入每次单击 基本数据
定期聚合来自 基本数据 daily_stats

这可能不是最佳解决方案。 基本数据 没有必要。还应研究以下方法:

CREATE TABLE period_stats ( link_id INT, period DATETIME, ...)

更新很容易

UPDATE period_stats 
SET clicks = clicks + 1 
WHERE period = @dateTime AND link_id = @url AND ...

更新此表(正确索引)的成本与在表中插入行一样高效 base_table

SELECT link_id, SUM(clicks)
FROM period_stats
WHERE period between @dateTime1 AND @dateTime2
GROUP BY ...

Geert Immerzeel 14 年前

在数据库中所做的反规范化可以很好地解决某些问题。然而,在您的情况下,我不会选择上述解决方案,主要是因为您丢失了将来可能需要的信息,也许您希望在未来每隔半小时报告一次。

iDevlop 14 年前

那设计真的很糟糕。我儿子的建议更好。
如果您想让它变得更简单,您也可以使用一个带有2个字段的表:

   timeSlice  
   clickCount  
   location
   userType

时间片将日期和时间四舍五入到小时。
24*365*位置#*类型#
每年记录。