30亿关系数据库的SQL数据库架构设计

Question

让你的极客。你能解决这个问题吗？

我正在为 SQL Server 2008 R2 Ed 设计一个产品数据库。 (不是 Enterprise Ed.)，它将用于存储超过 30,000 种不同产品的自定义产品配置。该数据库一次最多可容纳 500 个用户。

这是设计问题...

每个产品都有一组零件(每个产品最多 50 个零件)。
因此，如果我有 30,000 个产品，并且每个产品最多可以有 50 个部件，那就是 150 万个不同的产品对部件关系

                                  …or as an equation…

30,000(产品)X 50(零件)= 150 万条产品到零件记录。

…而如果…

每个零件最多可以有 2000 个饰面选项(饰面是一种油漆颜色)。

注意:用户在运行时只会选择一个完成。我需要存储的 2000 完成选项是特定产品上特定部件的允许选项。

因此，如果我有 150 万个不同的产品到零件关系/记录，并且每个零件最多可以有 2,000 次完成，即 30 亿个允许的产品到零件到完成关系/记录

                                  …or as an equation…

150 万(零件)x 2,000(成品)= 30 亿条产品到零件到成品记录。

我如何设计这个数据库，以便我可以执行 快速高效的查询对于特定产品，并返回其零件列表和每个零件的所有允许表面处理，而没有 30 亿条产品到零件到完工记录？读时间比写时间更重要。

如果您有使用大型数据库的经验，请发表您的想法/建议。

谢谢!

Answer 1

为什么这甚至具有挑战性？如果关系数据库擅长一件事，那就是 正好您描述的问题:3 个表和 2 个多对多关系。数字“30 亿”仅在某些失控的完全笛卡尔连接运行时才会出现。只需进行非常基本的标准化设计:

:setvar dbname test
:setvar PRODUCTSCOUNT 30000
:setvar PARTSCOUNT 5000
:setvar FINISHESCOUNT 2000
:setvar PRODUCTSPARTS 50
:setvar PARTFINISHES 1

use master;
set nocount on;
go

rollback
go

:on error exit

if db_id('$(dbname)') is not null
begin
    alter database [$(dbname)] set single_user with rollback immediate;
    drop database [$(dbname)];
end 
go

create database [$(dbname)] 
    on (name = test_data, filename='c:\temp\test.mdf', size = 10GB)
    log on (name = test_log, filename='c:\temp\test.ldf', size = 100MB);
go

use [$(dbname)];
go

create table Products (
    Product_Id int not null identity(0,1) primary key,
    Description varchar(256));
go      

create table Parts (
    Part_Id int not null identity(0,1) primary key,
    Description varchar(256));

create table Finishes (
    Finish_Id smallint not null identity(0,1) primary key,
    Description varchar(256));

create table ProductParts (
    Product_Id int not null,
    Part_Id int not null,
    constraint fk_products_parts_product
        foreign key (Product_Id)
        references Products (Product_Id),
    constraint fk_product_parts_part 
        foreign key (Part_Id)
        references Parts (Part_Id),
    constraint pk_product_parts
        primary key (Product_Id, Part_Id));

create table PartFinishes (
    Part_Id int not null,
    Finish_Id smallint not null,
    constraint fk_part_finishes_part
        foreign key (Part_Id)
        references Parts (Part_Id),
    constraint fk_part_finishes_finish
        foreign key (Finish_Id)
        references Finishes (Finish_Id),
    constraint pk_part_finishes
        primary key (Part_Id, Finish_Id));
go      

-- populate Products
declare @cnt int = 0, @description varchar(256);
begin transaction;
while @cnt < $(PRODUCTSCOUNT)
begin
    set @description = 'Product ' + cast(@cnt as varchar(10));
    insert into Products (Description) values (@description);
    set @cnt += 1;
    if @cnt % 1000 = 0
    begin
        commit;
        raiserror (N'Inserted %d products', 0,1, @cnt);
        begin transaction;
    end
end
commit;
raiserror (N'Done. %d products', 0,1, @cnt);
go

-- populate Parts
declare @cnt int = 0, @description varchar(256);
begin transaction;
while @cnt < $(PARTSCOUNT)
begin
    set @description = 'Part ' + cast(@cnt as varchar(10));
    insert into Parts (Description) values (@description);
    set @cnt += 1;
    if @cnt % 1000 = 0
    begin
        commit;
        raiserror (N'Inserted %d parts', 0,1, @cnt);
        begin transaction;
    end
end
commit;
raiserror (N'Done. %d parts', 0,1, @cnt);
go

-- populate Finishes
declare @cnt int = 0, @description varchar(256);
begin transaction;
while @cnt < $(FINISHESCOUNT)
begin
    set @description = 'Finish ' + cast(@cnt as varchar(10));
    insert into Finishes (Description) values (@description);
    set @cnt += 1;
    if @cnt % 1000 = 0
    begin
        commit;
        raiserror (N'Inserted %d finishes', 0,1, @cnt);
        begin transaction;
    end
end
raiserror (N'Done. %d finishes', 0,1, @cnt);
commit;
go

-- populate product parts
declare @cnt int = 0, @parts int = 0, @part int, @product int = 0;
begin transaction;
while @product < $(PRODUCTSCOUNT)
begin
    set @parts = rand() * ($(PRODUCTSPARTS)-1) + 1;
    set @part = rand() * $(PARTSCOUNT);     
    while 0 < @parts 
    begin
        insert into ProductParts (Product_Id, Part_Id)
            values (@product, @part);
        set @parts -= 1;
        set @part += rand()*10+1;
        if @part >= $(PARTSCOUNT)
            set @part = rand()*10;
        set @cnt += 1;
        if @cnt % 1000 = 0
        begin
            commit;
            raiserror (N'Inserted %d product-parts', 0,1, @cnt);
            begin transaction;
        end
    end
    set @product += 1;
end
commit;
raiserror (N'Done. %d product-parts', 0,1, @cnt);
go      

-- populate part finishes
declare @cnt int = 0, @part int = 0, @finish int, @finishes int;
begin transaction;
while @part < $(PARTSCOUNT)
begin
    set @finishes = rand() * ($(PARTFINISHES)-1) + 1;
    set @finish = rand() * $(FINISHESCOUNT);
    while 0 < @finishes 
    begin
        insert into PartFinishes (Part_Id, Finish_Id)
            values (@part, @finish);
        set @finish += rand()*10+1;
        if @finish >= $(FINISHESCOUNT)
            set @finish = rand()*10+1;
        set @finishes -= 1;
        set @cnt += 1;
        if @cnt % 1000 = 0
        begin
            commit;
            raiserror (N'Inserted %d part-finishes', 0,1, @cnt);
            begin transaction;
        end
    end
    set @part += 1;
end
commit;
raiserror (N'done. %d part-finishes', 0,1, @cnt);
go

现在，如果我们通过一个基本的测试来运行它，结果非常好:

set statistics time on;
set statistics io on;

declare @product int = rand()*30000;
select *
from Products po
join ProductParts pp on po.Product_Id = pp.Product_Id
join Parts pa on pa.Part_Id = pp.Part_Id
join PartFinishes pf on pf.Part_Id = pa.Part_Id
join Finishes f on pf.Finish_id = f.Finish_Id
where po.Product_Id = @product;

执行次数:

(33 row(s) affected)
Table 'Finishes'. Scan count 0, logical reads 66, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Parts'. Scan count 0, logical reads 66, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PartFinishes'. Scan count 33, logical reads 66, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ProductParts'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Products'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 5 ms.

这是一个随机产品的 5ms 执行时间。这远不是“服务器”，我在我的笔记本电脑上运行它。毫不奇怪，所有访问都由这些表上的聚集索引覆盖。我会让你设置一个有 500 个用户的压力测试，并自己衡量它在并发下的表现。我希望它保持得很好。