主页 / dba / 问题 / 239974
Accepted
Kirk Saunders
Asked: 2019-06-07 06:37:41 +0800 CST

SQL Server - 物理表设计 - 列顺序 - 空列和非空列

  • 772

我们正在为新应用程序构建数据库。我从架构师那里得到了一些反馈，他在某处读到，首先将所有不可为空的列放在一个表中，然后是所有可空的列，这对内存利用率有好处。如果在不可空列之间存在可空列，则某种内存或存储优势将被丧失，因为这些可空列位于不可空列之间。

这些列的排序有点模糊，但一般来说，它优先用于更有可能首先使用的列。

我的印象是 SSMS 按其自己的逻辑而不是它们在表上创建的特定顺序对页面文件上的列进行排序。我将列放在表中的顺序完全独立于数据在页面文件中的存储方式。

是否有任何文件可以支持这一发现/理解？

编辑：

围绕弄清楚这个想法的来源进行了更多的对话。该请求过度简化了具有高密度数据或表中最常用的列SELECT或JOIN操作的列，以防止它们移动到页面文件的溢出部分。

分配给表的列的顺序与将数据添加到页面文件的顺序相同。对于给定的行，数据按照列在表中设置的顺序存储在页面文件中。如果该行包含的数据多于页面文件允许的数据（假设我们没有使用任何会进入 LOBIMAGE或VARCHAR(MAX)类似的东西），那么剩余部分将被放入溢出文件。如果需要该溢出数据，则需要花费额外的精力和时间来查找该溢出文件。这可能会对高行环境中的性能产生显着影响（10 或 100 的数百万行 +）。因此，我们希望按照我们认为该列将被访问的频率来确定列顺序的优先级，不一定是是否访问NULL。

这种理解/分析听起来对吗？

sql-server database-design

2 个回答

  1. Best Answer

    我将其发布在这里作为答案，以便我有更多空间发布资源和链接，并进行一些解释。

    为了回答有关 NULLABLE 与 NON-NULLABLE 列及其相对基数的具体问题，Kimberly Tripp 在这里有一篇文章：https ://www.sqlskills.com/blogs/kimberly/column-order-doesnt-matter-generally-but-这取决于/

    不幸的是，我找不到支持我关于在列顺序列表末尾具有可变宽度列的声明的参考，但原因是在行末尾更新可变宽度列不太可能导致碎片（并且关闭-page 溢出）而不是列顺序中间的一个。

    但正如我之前所说，这些很难做到正确，除非你确切地知道你的数据访问模式，否则你可能会弄错。还有许多其他容易实现的目标来调整担心列顺序的问题。切换到适当大小的静态列长度将是获得更好的内存估计和避免碎片的更简单方法。

    具有适当填充因子的索引、统计更新、数据访问的存储过程（因此您可以更严格地控​​制执行计划）等等。如果您使用的是企业版，那么您有更多选择，表压缩将允许您将更多数据放入内存中，这更容易（并且适用范围更广），分区将允许您在管理方面做一些非常酷的事情（尽管使用加快阅读速度是一项挑战（分区消除是另一件很难做到的事情））。

    请注意，当我说很难做到正确时，我通常是指很难让 SQL 充分利用它们，因此花在优化上的时间通常会更好地花在其他地方来获得 ROI。

    在编辑部分回答问题的最后一部分。是的，使用主键、外键和其他连接列预加载列可能会有所帮助。但是，如果您对这些有索引，那么它应该无关紧要，因为索引将用于确定要检索哪些行，然后执行 keylookup。我只是不认为在它上面花费架构时间是值得的。在任何情况下，您都可能会对这些列建立索引。

    • 1

  2. 案例1：不为空首先

     CREATE TABLE testspace 
                 ( 
                              id       INT IDENTITY PRIMARY KEY , 
                              code     CHAR(20) NOT NULL, 
                              NAME     VARCHAR(100) NOT NULL, 
                              address1 VARCHAR(200), 
                              address2 VARCHAR(200) 
                 )
CREATE NONCLUSTERED INDEX ix_code_testspce ON testspace 
                              ( 
                                                        code 
                              ) 
                              include 
                              ( 
                                                        NAME 
                              )

DECLARE @i INT=0

WHILE(@i<=500000) 
    BEGIN 
      IF(@i%10=0) 
      INSERT INTO testspace VALUES 
                  ( 
                              Replicate('code',5), 
                              Replicate('name',25), 
                              Replicate('add1',50), 
                              Replicate('add2',50) 
                  ) 
      ELSE 
      INSERT INTO testspace VALUES 
                  ( 
                              Replicate('code',5), 
                              Replicate('name',25), 
                              NULL, 
                              NULL 
                  ) 
      SET @i=@i+1 
    ENDsp_spaceused N'TestSpace' 
    --select * from sys.dm_db_database_page_allocations(db_id('DbName'),OBJECT_ID('TestSpace'),1,null,'DETAILED')

SELECT *
    FROM   sys.Dm_db_index_physical_stats(Db_id('DBName'),Object_id('TestSpace'),1,NULL,'DETAILED')

    案例2：空列优先

            CREATE TABLE testspace1 
  ( 
     address1 VARCHAR(200), 
     address2 VARCHAR(200), 
     NAME     VARCHAR(100) NOT NULL, 
     id       INT IDENTITY PRIMARY KEY, 
     code     CHAR(20) NOT NULL 
  ) 

CREATE NONCLUSTERED INDEX ix_code_testspce1 
  ON testspace1(code) 
  include(NAME) 

DECLARE @i INT=0 

WHILE( @i <= 500000 ) 
  BEGIN 
      IF( @i%10 = 0 ) 
        INSERT INTO testspace1 
                    (address1, 
                     address2, 
                     NAME, 
                     code) 
        VALUES     (Replicate('add1', 50), 
                    Replicate('add2', 50), 
                    Replicate('name', 25), 
                    Replicate('code', 5)) 
      ELSE 
        INSERT INTO testspace1 
                    (address1, 
                     address2, 
                     NAME, 
                     code) 
        VALUES     (NULL, 
                    NULL, 
                    Replicate('name', 25), 
                    Replicate('code', 5)) 

      SET @i=@i + 1 
  END 

SELECT * 
FROM   sys.Dm_db_index_physical_stats(Db_id('Fixture20180417'), 
       Object_id('TestSpace1') 
       , -1, NULL, 'DETAILED')

    Clustered index并且Non Clustered Index有意创建以检查每种索引的每页上的记录数等Page Count。fragmentation

    我发现 , Fragment_Count,Page_Count在record_count案例 2 中更多。

    因此，案例 1有望表现更好。

    就像我告诉过你的那样，我没有执行权限DBCC Page等DBCC Show_Statistics。

    您可以执行类似的实验并检查自己并在此处发布您的经验。您以真实且重要的表格示例为例。

    • 0

相关问题