索引政策 #聚集索引 #非聚集索引 #資料

在很多時候一個表上有一個聚集索引和幾個非聚集索引就可以讓性能表現的更優秀

一：堆

1.臨時對象

最常見的堆應用就是臨時對象，由于對象具有臨時性特點，是以沒必要對其進行聚集索引化，表變量隻能在定義時建立聚集索引，定義後就不能單獨建立了

--建立臨時表
         CREATE TABLE #TempWithHeap(SalesOrderID INT);
         --插入資料，此時是一個堆表
         INSERT INTO #TempWithHeap
         SELECT SalesOrderID FROM Sales.SalesOrderHeader 
         WHERE SalesPersonID=283

         --開啟執行計劃，并執行下面語句
         SELECT sod. * FROM Sales.SalesOrderDetail sod
         INNER JOIN #TempWithHeap t
         ON t.SalesOrderID=sod.SalesOrderID;

--做少許改動，除了表名和聚集索引外，其他語句和上面一樣

--建立臨時表
         CREATE TABLE #TempWithClusteredIX( SalesOrderID INT PRIMARY KEY CLUSTERED);
         --插入資料，此時是一個聚集索引表
         INSERT INTO #TempWithClusteredIX 
         SELECT SalesOrderID FROM sales.SalesOrderHeader
         WHERE SalesPersonID=283;
         --開啟執行計劃
         SELECT sod.* FROM sales.SalesOrderDetail sod
         INNER JOIN #TempWithClusteredIX t
         ON t.SalesOrderID=sod.SalesOrderID

使用堆表還是使用具有聚集索引的臨時表，基本上沒有什麼差別。

對于堆表由于EXISTS徐亞預先排序，相對聚集索引來說沒有這種特性，是以在堆表中需要額外引入一個排序操作，這和聚集索引表相比，多了一部分的開銷

SELECT sod.* FROM sales.SalesOrderDetail sod
        WHERE EXISTS (SELECT * FROM  #TempWithHeap t WHERE t.SalesOrderID=sod.SalesOrderID);

      
        SELECT sod.* FROM sales.SalesOrderDetail sod
        WHERE EXISTS (SELECT * FROM  #TempWithClusteredIX   t WHERE t.SalesOrderID=sod.SalesOrderID);
帶有Exists的臨時表執行計劃（傳回結果集true和false）

聚集索引表的執行計劃

2.聚集索引

對聚集索引鍵值的選擇，通常優先考慮下面的類型：

（1）靜态，使用靜态值，能盡可能使得索引中行的位置不會因為更改而變動。使用了非靜态列，非導緻資料行在更改時插入到其他頁中

（2）窄列，隻需要一列作為聚集索引鍵即可，并且最好使用最小的資料類型

（3）唯一

（自增）

3.非聚集索引

非聚集索引會引用聚集索引的鍵值或者堆上的行辨別符（RID）

建立非聚集索引的時候需要考慮的因素：

(1).非聚集索引鍵上的更改是否頻繁？鍵值修改越頻繁，索引的行位置就變的越頻繁。

(2).對于經常執行的查詢來說，非聚集索引能使性能提高多少

(3).索引是否支援業務

(4).維護的成本與其給查詢帶來的好處相比，哪個更好

一.查找列

IF OBJECT_ID(N'Contacts','U') IS NOT NULL
              DROP TABLE Contacts
        CREATE TABLE dbo.Contacts
        (
        ContactID INT IDENTITY(1,1),
        FirstName NVARCHAR(50),
        LastName NVARCHAR(50),
        IsActive BIT,
        EmailAddress NVARCHAR(50),
        CertificationDate CHAR(1000),
        CONSTRAINT PK_Contacts PRIMARY KEY CLUSTERED(ContactID)
        );
        INSERT INTO dbo.Contacts
                ( 
                  FirstName,
                  LastName,
                  IsActive,
                  EmailAddress,
                  CertificationDate
                )
       SELECT pp.FirstName,
       pp.LastName,
       CASE WHEN pp.BusinessEntityID/10=1 THEN 1 ELSE 0 END,
       pea.EmailAddress,
       CASE WHEN pp.BusinessEntityID/10=1 THEN pp.ModifiedDate ELSE NULL end
       FROM person.Person pp
       INNER JOIN Person.EmailAddress pea ON pp.BusinessEntityID=pea.BusinessEntityID

       --沒有非聚集索引的查詢情況
       SET STATISTICS IO ON;
       SELECT ContactID,FirstName ContactID,FirstName FROM dbo.Contacts WHERE FirstName='Catherine';
       SET STATISTICS IO OFF

表 'Contacts'。掃描計數 1，邏輯讀取 2866 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。

建立一個非聚集索引：

CREATE INDEX IX_Contacts_FirstName ON dbo.Contacts(FirstName);
       SET STATISTICS IO ON 
       SELECT ContactID,FirstName FROM dbo.Contacts WHERE FirstName='Catherine'
       SET STATISTICS IO OFF

表 'Contacts'。掃描計數 1，邏輯讀取 2 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。

對查找列上加上非聚集索引，通常是提升性能的第一步，它提供了一條高效通路資料的路徑。

二：索引交叉

對于SELECT中的一些額外列，如果他們不在聚集索引和建立非聚集索引中，查詢就不那麼高效了。

SET STATISTICS IO ON 
       SELECT ContactID,FirstName,LastName FROM dbo.Contacts WHERE FirstName='Catherine' AND LastName='Cox';
       SET STATISTICS IO OFF;

表 'Contacts'。掃描計數 1，邏輯讀取 68 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。

額外在LastName列建立一個非聚集索引。

CREATE INDEX IX_Contacts_LastName ON  dbo.Contacts(LastName)
       SET STATISTICS IO ON
             SELECT ContactID,FirstName,LastName FROM dbo.Contacts WHERE  FirstName='Catherine' AND LastName='Cox'
       SET STATISTICS IO OFF

表 'Contacts'。掃描計數 2，邏輯讀取 5 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。

索引交叉方式不需要在索引中定義過多的列，是以統計資訊更準确.

三：複合索引

SQL Server的非聚集索引可以包含16列。

CREATE INDEX IX_Contacts_FirstNameLastName ON dbo.Contacts(FirstName,LastName);
       SET STATISTICS IO ON
             SELECT ContactID,FirstName,LastName FROM dbo.Contacts WHERE  FirstName='Catherine' AND LastName='Cox'
       SET STATISTICS IO OFF

表 'Contacts'。掃描計數 1，邏輯讀取 2 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。

4.覆寫索引

覆寫索引也可以算是複合索引的一種，如果一個查詢通過索引完全不需要通路底層表，則可以認為是覆寫索引

CREATE INDEX IX_Contacts_FirstNameLastName ON dbo.Contacts(FirstName,LastName);
       SET STATISTICS IO ON
             SELECT ContactID,FirstName,LastName FROM dbo.Contacts WHERE  FirstName='Catherine' AND LastName='Cox'
       SET STATISTICS IO OFF

表 'Contacts'。掃描計數 1，邏輯讀取 5 次，實體讀取 1 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。

将IsActive列添加到索引中，進而減少Select借助聚集索引查找資料

CREATE INDEX IX_Contacts_FirstNameLastNameIsActive ON dbo.Contacts(FirstName,LastName,IsActive)
       SET STATISTICS IO ON 
       SELECT ContactID,FirstName,LastName,IsActive FROM dbo.Contacts WHERE FirstName='Catherine' AND LastName='Cox'
       SET STATISTICS IO OFF

表 'Contacts'。掃描計數 1，邏輯讀取 2 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。

5.包含索引：

允許非鍵列加到非聚集索引中，類似于非鍵值存放在聚集索引上

和覆寫索引的主要差別：

非聚集索引隻能定義16列，900bytes，如果列很多，要加入到覆寫索引中，就會導緻無法建立，包含索引沒有這方面的限制

覆寫索引中的列按定義時的順序排列，但是包含索引中的列不會排序

沒有包含索引的查詢語句：

SET STATISTICS IO ON 
       SELECT ContactID,FirstName,LastName,EmailAddress FROM dbo.Contacts WHERE FirstName='Catherine'
       SET STATISTICS IO OFF

表 'Contacts'。掃描計數 1，邏輯讀取 68 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。

包含索引的查詢語句：

CREATE INDEX IX_Contacts_FirstNameINC ON dbo.Contacts(FirstName)
       INCLUDE (LastName,IsActive,EmailAddress);
       SET STATISTICS IO ON;
      SELECT ContactID,FirstName,LastName,EmailAddress FROM dbo.Contacts WHERE FirstName='Catherine'
      SET STATISTICS IO OFF;

表 'Contacts'。掃描計數 1，邏輯讀取 3 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。

删除多餘的索引：

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('dbo.Contacts') AND name='IX_Contacts_FirstNameLastName')
      DROP INDEX IX_Contacts_FirstNameLastName ON dbo.Contacts
      GO
            IF EXISTS(SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('dbo.Contacts') AND name='IX_Contacts_FirstNameLastNameIsActive')
      DROP INDEX IX_Contacts_FirstNameLastNameIsActive ON dbo.Contacts
            GO
              IF EXISTS(SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('dbo.Contacts') AND name='IX_Contacts_FirstName')
      DROP INDEX IX_Contacts_FirstName ON dbo.Contacts

6.篩選索引

可以通過在索引上“移除”這些列值來降低索引的大小，甚至在表關聯時也能減少數量級，而這個“移除”動作就是通過篩選索引來實作

SET STATISTICS IO ON
            SELECT ContactID,FirstName,LastName,CertificationDate FROM dbo.Contacts WHERE CertificationDate IS NOT NULL
            ORDER BY CertificationDate 
      SELECT ContactID,FirstName,LastName,CertificationDate FROM dbo.Contacts WHERE CertificationDate BETWEEN '20050101' AND '20050201' ORDER BY CertificationDate
      SET STATISTICS IO OFF

表 'Contacts'。掃描計數 1，邏輯讀取 2866 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。

--檢視null值
       SELECT COUNT(1) FROM dbo.Contacts WHERE CertificationDate IS NULL
       SELECT COUNT(1) FROM dbo.Contacts

CREATE INDEX IX_Contacts_CertificationDate ON dbo.Contacts(CertificationDate) INCLUDE(FirstName,LastName) WHERE CertificationDate IS NOT NULL
       --查詢
       SET STATISTICS IO ON
             SELECT ContactID,FirstName,LastName,CertificationDate FROM dbo.Contacts WHERE CertificationDate IS NOT NULL
             ORDER BY CertificationDate

       SELECT ContactID,FirstName,LastName,CertificationDate FROM dbo.Contacts WHERE CertificationDate BETWEEN '20050101' AND '20050201'
       ORDER BY CertificationDate SET STATISTICS IO OFF

通過減少索引上不必要的行，能從很大程度上降低查詢和維護的開銷

7.外鍵

外鍵用于為兩表間的資料提供一緻性，它屬于資料庫設計範疇。

為了提高效率，可以在外鍵上建立索引。

CREATE TABLE dbo.Customer(
       CustomerId INT,
       FillterData CHAR(1000),
             CONSTRAINT PK_Customer_CustomerID PRIMARY KEY CLUSTERED (CustomerId)
       );
       CREATE TABLE dbo.SalesOrderHeader(
       SalesOrderID INT,
       OrderDate DATETIME,
       DueDate DATETIME,
       CustomerID INT,
       FillterData CHAR(1000),
       CONSTRAINT PK_SalesOrderHeader_SalesOrderID PRIMARY KEY CLUSTERED (SalesOrderID),
       CONSTRAINT GK_SalesOrderHeader_CustomerID_FROM_Customer FOREIGN KEY (CustomerID) REFERENCES dbo.Customer(CustomerId));
       --架構不同，可以放心執行，現在插入資料
       INSERT INTO dbo.Customer
               ( CustomerId )
       SELECT CustomerID FROM Sales.Customer;

             INSERT INTO dbo.SalesOrderHeader(SalesOrderID,OrderDate,DueDate,CustomerID)
       SELECT SalesOrderID,OrderDate,DueDate,CustomerID  FROM dbo.SalesOrderHeader;

       --沒有索引的情況
       SET STATISTICS  IO ON
       SELECT MAX(c.CustomerId) FROM dbo.Customer c LEFT OUTER JOIN dbo.SalesOrderHeader soh
       ON  c.CustomerId=soh.CustomerID WHERE soh.CustomerID IS NULL;
        SET STATISTICS IO OFF

       SET STATISTICS  IO ON
             delete FROM dbo.Customer
       WHERE CustomerId=701
       SET STATISTICS IO OFF

表 'SalesOrderHeader'。掃描計數 1，邏輯讀取 0 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。
表 'Customer'。掃描計數 1，邏輯讀取 2844 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。

表 'SalesOrderHeader'。掃描計數 1，邏輯讀取 0 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。
表 'Customer'。掃描計數 0，邏輯讀取 3 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。

這個删除操作隻影響一行，但是由于有外鍵關聯，導緻在dbo.SalesOrderHeader表上産生了很多次邏輯讀，這個操作需要通過掃描Customer=701的資料來查找需要删除的資料

--建立一個與外鍵相關的索引，然後再次删除

CREATE INDEX IS_SalesOrderHeader_CustomerID ON dbo.SalesOrderHeader(CustomerID)
       GO
       SELECT MAX(c.CustomerId) FROM dbo.Customer c
        LEFT OUTER JOIN dbo.SalesOrderHeader soh
       ON c.CustomerId=soh.CustomerID 
       WHERE soh.CustomerID IS NULL;

       SET STATISTICS IO ON
             DELETE FROM dbo.Customer WHERE CustomerId=608
       SET STATISTICS IO OFF

表 'SalesOrderHeader'。掃描計數 1，邏輯讀取 0 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。
表 'Customer'。掃描計數 0，邏輯讀取 3 次，實體讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 實體讀取 0 次，lob 預讀 0 次。

建立索引後，SalesOrderHeader的邏輯讀從4000降到2，而且SalesOrderHeader上的聚集索引掃描也變成了索引查找

8.列存儲索引

微軟從SQL Server 2012開始引入了列存儲索引技術，列存儲索引有非常特殊的應用情景。

列存儲索引主要是資料倉庫而設計的，借助列存儲和内置壓縮，可以不用加載無用的列，進而提高查詢的速度。

列存儲索引的順序不重要，每個列都單獨存儲，在執行查詢并組合它們（列）之前，它們之間沒有關系

沒有列存儲時的性能情況

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT  dd.CalendarQuarter ,
        dpc.ProductCategoryName ,
        COUNT(*) AS TotalRows ,
        SUM(SalesQuantity) AS TotalSales
FROM    dbo.FactSales fs
        INNER JOIN dbo.DimDate dd ON fs.DateKey = dd.Datekey
        INNER JOIN dbo.DimProduct dp ON fs.ProductKey = dp.ProductKey
        INNER JOIN dbo.DimProductSubcategory dps ON dp.ProductSubcategoryKey = dps.ProductSubcategoryKey
        INNER JOIN dbo.DimProductCategory dpc ON dps.ProductCategoryKey = dpc.ProductCategoryKey
GROUP BY dd.CalendarQuarter ,
        dpc.ProductCategoryName
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

在FactSales表上建立列存儲索引

CREATE COLUMNSTORE INDEX IX_FactSales_CStore ON FactSales(SalesKey,DateKey,channelKey,StoreKey,ProductKey,PromotionKey,CurrencyKey,
UnitCost,UnitPrice,SalesQuantity,ReturnQuantity,ReturnAmount,DiscountQuantity,DiscountAmount,TotalCost,SalesAmount,ETLLoadID,LoadDate,UpdateDate)

再次執行上面的語句，性能減少了很高

索引政策

繼續閱讀

Kafka：Topic概念與API介紹

5G小型蜂應用指南

PAT (Advanced Level) Practise 1012 The Best Rank (25)

mysql5.7的sql優化

線程通信和程序通信差別（線程程序差別）

Matlab随機波動率SV、GARCH用MCMC馬爾可夫鍊蒙特卡羅方法分析匯率時間序列

微信小程式前端解密擷取使用者資訊

Spring MVC 自學雜記（五） -- SpringMVC與前台的json資料互動

《MySQL技術内幕：InnoDB存儲引擎》筆記

擴容TIKV節點遇到的坑

PHP輔導代做程式設計：CS353 Database System

自學Zabbix3.10.2-事件通知Notifications upon events-Actions報警配置點選傳回：自學zabbix集錦

HDU 5678 ztr loves trees

拓端tecdat|R語言彈性網絡Elastic Net正則化懲罰回歸模型交叉驗證可視化

二叉樹及其應用--二叉樹建立

詳解STM32單片機的堆棧