sql - 加入 100 个表

Question

假设我有一个主表，它有 100 列引用(作为外键)到大约 100 个表(包含主键)。

整个信息包需要加入这 100 个表。并且加入如此多的表绝对是一个性能问题。希望我们可以期望任何用户都希望从不超过 5-7 个表(在这 100 个表中)在将条件(在查询的 WHERE 部分)放在以下字段的查询中请求一堆数据大约 3-4 张 table (在这 100 张 table 中)。不同的查询具有不同的表组合，用于生成查询的“SELECT”部分并将条件放在“WHERE”中。但是，同样，每个 SELECT 将需要一些 5-7 个表，每个 WHERE 将需要一些 3-4 个表(当然，用于生成 SELECT 的表列表可能与用于在 WHERE 中放置条件的表列表重叠)。

我可以编写一个带有连接所有这 100 个表的底层代码的 VIEW。然后我可以将上面提到的 SQL 查询写入这个 VIEW。但在这种情况下，如何指示 SQL Server(尽管代码中明确说明要连接所有这 100 个表)只应连接一些 11 个表(11 个表足以连接以生成 SELECT)对我来说是一个大问题结果并考虑 WHERE 条件)。

另一种方法可能是创建一个“功能”来转换以下“假”代码

SELECT field1, field2, field3 FROM TheFakeTable WHERE field1=12 and field4=5

进入以下“真实”代码:

SELECT T1.field1, T2.field2, T3.field3 FROM TheRealMainTable 
join T1 on ....
join T2 on ....
join T3 on ....
join T4 on ....
WHERE T1.field1=12 and T4.field4=5

从语法的角度来看，即使允许这种“TheFakeTable-mechanism”与真实表格和结构的任何混合组合也不是问题。这里真正的问题是如何在技术上实现这个“特性”。我可以创建一个函数，它将“假”代码作为输入并生成“真实”代码。但这并不方便，因为它需要在出现“TheFakeTable 机制”的任何地方使用动态 SQL 工具。一个梦幻般的解决方案是在我的 Management Studio 中扩展 SQL 语言的语法，以允许编写这样的假代码，然后在发送到服务器之前自动将此代码转换为真实代码。

我的问题是:

是否可以指示 SQl Server shomehow(或成为天才)在上述 VIEW 中仅加入 11 个表而不是 100 个表？

如果我决定创建这个“TheFakeTable-mechanism”特性，那么这个特性的技术实现的最佳形式是什么？

感谢大家的每一条评论!

聚苯乙烯
具有 100 个表的结构源于我在这里提出的以下问题:
Normalizing an extremely big table

Answer 1

SQL Server 优化器确实包含删除冗余连接的逻辑，但有限制，连接必须是 provably redundant .总而言之，联接可以产生四种效果:

它可以添加额外的列(来自连接表)

它可以添加额外的行(连接表可能多次匹配源行)

它可以删除行(连接的表可能没有匹配项)

可介绍NULL s(对于 RIGHT 或 FULL JOIN)

要成功删除冗余连接，查询(或 View )必须考虑所有四种可能性。正确地完成此操作后，效果会令人惊讶。例如:

USE AdventureWorks2012;
GO
CREATE VIEW dbo.ComplexView
AS
    SELECT
        pc.ProductCategoryID, pc.Name AS CatName,
        ps.ProductSubcategoryID, ps.Name AS SubCatName,
        p.ProductID, p.Name AS ProductName,
        p.Color, p.ListPrice, p.ReorderPoint,
        pm.Name AS ModelName, pm.ModifiedDate
    FROM Production.ProductCategory AS pc
    FULL JOIN Production.ProductSubcategory AS ps ON
        ps.ProductCategoryID = pc.ProductCategoryID
    FULL JOIN Production.Product AS p ON
        p.ProductSubcategoryID = ps.ProductSubcategoryID
    FULL JOIN Production.ProductModel AS pm ON
        pm.ProductModelID = p.ProductModelID

优化器可以成功地简化以下查询:

SELECT
    c.ProductID,
    c.ProductName
FROM dbo.ComplexView AS c
WHERE
    c.ProductName LIKE N'G%';

到:

Rob Farley 在 original MVP Deep Dives book 中深入阐述了这些想法。 ，还有一个 recording of him presenting on the topic在 SQLBits。
主要限制是外键关系 must be based on a single key有助于简化过程，并且针对这种 View 的查询的编译时间可能会变得很长，尤其是随着连接数量的增加。编写一个包含 100 个表的 View 来使所有语义完全正确可能是一个相当大的挑战。我倾向于寻找替代解决方案，也许使用 dynamic SQL .
也就是说，非规范化表的特殊特性可能意味着 View 组装起来非常简单，只需要强制执行 FOREIGN KEYs非 NULL能够引用的列，以及适当的 UNIQUE使此解决方案如您所愿运行的约束，而无需计划中 100 个物理连接运算符的开销。
例子
使用十个表而不是一百个:

-- Referenced tables
CREATE TABLE dbo.Ref01 (col01 tinyint PRIMARY KEY, item varchar(50) NOT NULL UNIQUE);
CREATE TABLE dbo.Ref02 (col02 tinyint PRIMARY KEY, item varchar(50) NOT NULL UNIQUE);
CREATE TABLE dbo.Ref03 (col03 tinyint PRIMARY KEY, item varchar(50) NOT NULL UNIQUE);
CREATE TABLE dbo.Ref04 (col04 tinyint PRIMARY KEY, item varchar(50) NOT NULL UNIQUE);
CREATE TABLE dbo.Ref05 (col05 tinyint PRIMARY KEY, item varchar(50) NOT NULL UNIQUE);
CREATE TABLE dbo.Ref06 (col06 tinyint PRIMARY KEY, item varchar(50) NOT NULL UNIQUE);
CREATE TABLE dbo.Ref07 (col07 tinyint PRIMARY KEY, item varchar(50) NOT NULL UNIQUE);
CREATE TABLE dbo.Ref08 (col08 tinyint PRIMARY KEY, item varchar(50) NOT NULL UNIQUE);
CREATE TABLE dbo.Ref09 (col09 tinyint PRIMARY KEY, item varchar(50) NOT NULL UNIQUE);
CREATE TABLE dbo.Ref10 (col10 tinyint PRIMARY KEY, item varchar(50) NOT NULL UNIQUE);

父表定义(带页面压缩):

CREATE TABLE dbo.Normalized
(
    pk      integer IDENTITY NOT NULL,
    col01   tinyint NOT NULL REFERENCES dbo.Ref01,
    col02   tinyint NOT NULL REFERENCES dbo.Ref02,
    col03   tinyint NOT NULL REFERENCES dbo.Ref03,
    col04   tinyint NOT NULL REFERENCES dbo.Ref04,
    col05   tinyint NOT NULL REFERENCES dbo.Ref05,
    col06   tinyint NOT NULL REFERENCES dbo.Ref06,
    col07   tinyint NOT NULL REFERENCES dbo.Ref07,
    col08   tinyint NOT NULL REFERENCES dbo.Ref08,
    col09   tinyint NOT NULL REFERENCES dbo.Ref09,
    col10   tinyint NOT NULL REFERENCES dbo.Ref10,

    CONSTRAINT PK_Normalized
        PRIMARY KEY CLUSTERED (pk)
        WITH (DATA_COMPRESSION = PAGE)
);

风景:

CREATE VIEW dbo.Denormalized
WITH SCHEMABINDING AS
SELECT
    item01 = r01.item,
    item02 = r02.item,
    item03 = r03.item,
    item04 = r04.item,
    item05 = r05.item,
    item06 = r06.item,
    item07 = r07.item,
    item08 = r08.item,
    item09 = r09.item,
    item10 = r10.item
FROM dbo.Normalized AS n
JOIN dbo.Ref01 AS r01 ON r01.col01 = n.col01
JOIN dbo.Ref02 AS r02 ON r02.col02 = n.col02
JOIN dbo.Ref03 AS r03 ON r03.col03 = n.col03
JOIN dbo.Ref04 AS r04 ON r04.col04 = n.col04
JOIN dbo.Ref05 AS r05 ON r05.col05 = n.col05
JOIN dbo.Ref06 AS r06 ON r06.col06 = n.col06
JOIN dbo.Ref07 AS r07 ON r07.col07 = n.col07
JOIN dbo.Ref08 AS r08 ON r08.col08 = n.col08
JOIN dbo.Ref09 AS r09 ON r09.col09 = n.col09
JOIN dbo.Ref10 AS r10 ON r10.col10 = n.col10;

修改统计信息，让优化器认为表非常大:

UPDATE STATISTICS dbo.Normalized WITH ROWCOUNT = 100000000, PAGECOUNT = 5000000;

示例用户查询:

SELECT
    d.item06,
    d.item07
FROM dbo.Denormalized AS d
WHERE
    d.item08 = 'Banana'
    AND d.item01 = 'Green';

给了我们这个执行计划:

规范化表的扫描看起来很糟糕，但是存储引擎在扫描期间应用了两个布隆过滤器位图(因此无法匹配的行甚至不会出现在查询处理器中)。这可能足以在您的情况下提供可接受的性能，并且肯定比扫描具有溢出列的原始表更好。
如果您能够在某个阶段升级到 SQL Server 2012 Enterprise，您还有另一个选择:在规范化表上创建列存储索引:

CREATE NONCLUSTERED COLUMNSTORE INDEX cs 
ON dbo.Normalized (col01,col02,col03,col04,col05,col06,col07,col08,col09,col10);

执行计划是:

这对您来说可能看起来更糟，但列存储提供了出色的压缩，并且整个执行计划在批处理模式下运行，并为所有贡献列提供过滤器。如果服务器有足够的线程和可用内存，这个替代方案真的很可行。
最终，考虑到表的数量以及执行计划不佳或需要过多编译时间的可能性，我不确定这种规范化是否是正确的方法。我可能会首先更正非规范化表的架构(正确的数据类型等)，可能应用数据压缩......通常的事情。
如果数据真的属于星型模式，它可能需要更多的设计工作，而不仅仅是将重复的数据元素拆分到单独的表中。