主页 / dba / 问题 / 6836
Accepted
Gabe
Asked: 2011-10-15 13:53:28 +0800 CST

设计这张里程表的最佳方法是什么？

  • 772

我将填充每加仑英里数 (MPG) 表。它来自里程表源。

目前是这样设置的：

  id               (primary_key)
, truck_num
, start_date
, end_date
, start_miles
, end_miles
, start_fuel
, end_fuel
, miles
, gals
, mpg

似乎有一些冗余。是miles， (end_miles - start_miles)同上gals。

我们是否应该预先计算这些列miles并gals存储在数据库中？它肯定会使查询更容易，但会以空间为代价。计算的问题相同mpg。计算列会减慢速度，不是吗？

什么索引最有效？每周大约有 3000 辆卡车（记录）分批插入。

我正在使用 SQL Server 2008 R2。

编辑：我将使用的示例查询

-- find average mpg for since ytd
select m.truck_num, avg(mpg)
from mpg m
join truck t on t.truck_num = m.truck_num
where start_date >= @begin_of_year and end_date <= @today
group by truck_num
sql-server-2008 database-design

2 个回答

  1. Best Answer
    • 计算列是您的朋友。 使用它们来捕获您无论如何都要进行的简单计算，并保证计算值是正确的。
    • 如果要对结果进行索引或过滤，请保留结果；如果您只需要偶尔提取值，请不要坚持它们。
    • 使用约束捕获所有数据CHECK约束。

    这是一个伪模式定义：

      id               PRIMARY KEY
, truck_num

, start_date
, end_date         CHECK (end_date > start_date)

, start_miles      CHECK (start_miles >= 0)
, end_miles        CHECK (end_miles > start_miles)

                   -- what if they refill the tank?
, start_gals       CHECK (start_gals >= 0)
, end_gals         CHECK (end_gals < start_gals AND end_gals >= 0)

-- all these should be computed
, miles = end_miles - start_miles
, gals = start_gals - end_gals
, mpg = miles/gals

    至于索引表，这就是我的想法：

    • 群集上start_date ASC。这将满足您的WHERE条款。您总是通过增加来插入数据start_date，这意味着在此集群方案下您的写入将始终是顺序的。您也总是在查询，start_date因此您也可以满足您最大的查询模式。（每周 3,000 次插入不算什么。因为您的插入量如此之低，您甚至可以聚集在start_date ASC, end_date ASC.）
    • truck_num在和上创建非聚集索引INCLUDE mpg。这应该满足您的SELECT,JOIN和GROUP BY子句。如果你愿意ORDER BY mpg，那么在之后创建mpg索引键的一部分，truck_num而不是仅仅INCLUDE-ing 它。

    完成后，按如下方式测试您的设置：

    1. 创建测试表
    2. 将其充满测试数据
    3. 创建索引
    4. 更新统计
    5. 运行最常见的查询
    6. 检查他们的计划和运行时间
    • 7

  2. 我喜欢尼克的回答，所以不会重复他关于计算列和约束的信息。

    我会以不同的方式处理索引：

    • 如果所有 NC 索引都可以覆盖，那么聚簇索引的选择就无关紧要了，所以一个代理就可以了。
    • 这仅在更大容量/大型数据库以及查询模式需要更多 NC 索引时才重要。
    • 无论如何覆盖的聚集索引..因此，从那开始，索引(startdate, enddate, trucknum)必须是唯一的（同一辆卡车不能进行相同的旅程）。因此，使其具有集群性和独特性：使用自然键的经典 PK
    • 3

相关问题