SQL Server Index 測試

Database > SQLServer SQLServer

發布時間 : 2022-05-12 00:07

字數:668 觀看數 :

Test Table Schema
索引(Index) 測試
參考文章、測試資料來源

Test Table Schema

--Table
CREATE TABLE [dbo].[Products](
    [ProductID]            [int] IDENTITY(1,1) NOT NULL,
    [ProductName]        [nvarchar](40) NOT NULL,
    [SupplierID]        [int] NOT NULL,
    [CategoryID]        [int] NULL,
    [QuantityPerUnit]    [nvarchar](20) NULL,
    [UnitPrice]            [money] NULL,
    [UnitsInStock]        [smallint] NULL,
    [UnitsOnOrder]        [smallint] NULL,
    [ReorderLevel]        [smallint] NULL,
    [Discontinued]        [bit] NOT NULL,
)
--PK
ALTER TABLE [dbo].[Products] ADD CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
    ProductID ASC,
    SupplierID ASC
)
--NonClustered Index
CREATE NONCLUSTERED INDEX [IX_Products_CategoryID] ON [dbo].[Products] 
(
    CategoryID ASC
)

索引(Index) 測試

-- 叢集索引 (ProductID, SupplierID)
-- 非叢集索引 (CategoryID)

-- 測試 1
select SupplierID from products where ProductID=999                -- clustered index seek (叢集索引搜尋)
select * from products where ProductID=999　                    -- clustered index seek (叢集索引搜尋)
select * from products where ProductID=999 AND SupplierID=12    -- clustered index seek (叢集索引搜尋)
select * from products where SupplierID=12 AND ProductID=999    -- clustered index seek (叢集索引搜尋)

-- 測試 2
select SupplierID from products where SupplierID=12                            -- index scan (索引掃描) (因where 需要按照索引順序)
select ProductID,SupplierID from products where SupplierID=12                -- index scan (索引掃描)
select ProductID,SupplierID,ProductName from products where SupplierID=12    -- clustered index scan (叢集索引掃描) (因select 有非 index欄位)

-- 測試 3
select SupplierID,CategoryID from products where CategoryID=8                                        -- index seek (索引搜尋)
select SupplierID,CategoryID from products where CategoryID=8 AND SupplierID=12                        -- index seek (索引搜尋) (where 搜尋欄位 都有 index)
select SupplierID,CategoryID from products where CategoryID=8 AND SupplierID=12 AND UnitPrice=10    -- clustered index scan (叢集索引掃描) (where 搜尋欄位 有非 index欄位)
select SupplierID,CategoryID,ProductName from products where CategoryID=8                            -- clustered index scan (叢集索引掃描) (select 欄位 有非 index欄位)
select SupplierID,CategoryID,CategoryID from products where CategoryID=8                            -- index seek (索引搜尋) (select 欄位 都有 index)


-- 測試 4
SELECT ProductID,SupplierID,ProductName from products where ProductName='Schoggi Schokolade' -- 新增 非叢集索引 ProductName前 (clustered index scan (叢集索引掃描))

-- 新增 非叢集索引 ProductName
CREATE NONCLUSTERED INDEX [IX_Products_ProductName] ON [dbo].[Products] (
    [ProductName] ASC
)

SELECT ProductID,SupplierID,ProductName from products where ProductName='Schoggi Schokolade' -- 新增 非叢集索引 ProductName後 (index seek (索引搜尋))


-- 測試5
select ProductID,SupplierID,ProductName,UnitPrice from products where ProductName='Schoggi Schokolade' -- clustered index scan (叢集索引掃描) (select 欄位 有非 index欄位)

-- 因為SELECT欄位包含非索引欄位 所以結果會是 叢集索引掃描
-- 但我們常常會select 很多其他欄位 可以在索引加上 Include 讓其他欄位 變為 Nonkey

CREATE NONCLUSTERED INDEX [IX_Products_ProductName] ON [dbo].[Products] (
    [ProductName] ASC
)
INCLUDE (UnitPrice,UnitsInStock)

select ProductID,SupplierID,ProductName,UnitPrice from products where ProductName='Schoggi Schokolade' -- index seek (索引搜尋) (select 欄位 有非 index欄位)
select ProductID,SupplierID,ProductName from products where ProductName='Schoggi Schokolade' AND UnitPrice = 1 -- index seek (索引搜尋) (select 欄位 有非 index欄位)
select ProductID,SupplierID,ProductName from products where ProductName='Schoggi Schokolade' AND UnitsInStock = 1 -- index seek (索引搜尋) (select 欄位 有非 index欄位)
select ProductID,SupplierID,ProductName,UnitsInStock from products where ProductName='Schoggi Schokolade' -- index seek (索引搜尋) (select 欄位 有非 index欄位)

參考文章、測試資料來源

轉載請註明來源，若有任何錯誤或表達不清楚的地方，歡迎在下方評論區留言，也可以來信至 leozheng0621@gmail.com
如果文章對您有幫助，歡迎斗內(donate)，請我喝杯咖啡