sql server程序优化

Question

SQl Server 2005：

Option: 1

    CREATE TABLE #test
      (customerid, orderdate, field1 INT, field2 INT, field3 INT)

    CREATE UNIQUE CLUSTERED INDEX Idx1 ON #test(customerid)
    CREATE INDEX Idx2 ON #test(field1 DESC)
    CREATE INDEX Idx3 ON #test(field2 DESC)
    CREATE INDEX Idx4 ON #test(field3 DESC)

    INSERT INTO #test
      (customerid, orderdate, field1 INT, field2 INT, field3 INT)
    SELECT
      customerid, orderdate, field1, field2, field3 FROM 
    ATABLERETURNING4000000ROWS

相比

Option: 2

    CREATE TABLE #test
      (customerid, orderdate, field1 INT, field2 INT, field3 INT)

    INSERT INTO #test
      (customerid, orderdate, field1 INT, field2 INT, field3 INT)
    SELECT
      customerid, orderdate, field1, field2, field3 FROM 
    ATABLERETURNING4000000ROWS

    CREATE UNIQUE CLUSTERED INDEX Idx1 ON #test(customerid)
    CREATE INDEX Idx2 ON #test(field1 DESC)
    CREATE INDEX Idx3 ON #test(field2 DESC)
    CREATE INDEX Idx4 ON #test(field3 DESC)

当我们使用第二个选项时，它的运行速度快了近 50%。这是为什么呢？

Answer 1

来自 SQL Server 查询处理团队：

为了构建索引的 B 树，我们必须首先对源中的数据进行排序。流程是扫描源，对其进行排序（如果可能 - 在内存中*），然后根据排序构建 b 树。
为什么在构建b树之前需要先排序？理论上我们不必排序，我们可以使用常规 DML 并直接将数据插入内置索引（无排序），但在这种情况下我们将进行随机插入，在 b- 中随机插入树要求首先在b树中搜索正确的叶节点，然后插入数据。虽然搜索 B 树相当快，但在每次插入之前这样做远非最佳。

您的索引是 B+ 树。

第一个查询需要在 B+ 树中查找每条记录，并且然后修改B+树。

第二个查询将根据特定索引依次对每个索引所需的数据进行排序，B+树为 构建非常高效。

Answer 2

因为您是在添加索引之前插入行。唯一索引要求系统对新添加的行执行唯一性检查，并且在插入时，系统必须更新各个索引条目。不必执行唯一性检查工作会更快，但如果存在重复的 customerId 值，则在第二个选项中索引创建将失败。