利用 SQL Server 過濾索引提高查詢語句的性能分析
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。
Microsoft SQL Server 過濾索引（篩選索引）是指基于滿足特定條件的數(shù)據(jù)行進行索引。與全表索引（默認(rèn)創(chuàng)建）相比，設(shè)計良好的篩選索引可以提高查詢性能、減少索引維護開銷并可降低索引存儲開銷。本文就給大家介紹一下 Microsoft SQL Server 中的過濾索引功能。
在創(chuàng)建過濾索引之前，我們需要了解它的適用場景。
- 在某個字段中只有少量相關(guān)值需要查詢時，可以針對值的子集創(chuàng)建過濾索引。 例如，當(dāng)字段中的值大部分為 NULL 并且查詢只從非 NULL 值中進行選擇時，可以為非 NULL 數(shù)據(jù)行創(chuàng)建篩選索引。 由此得到的索引與對相同字段定義的全表非聚集索引相比，前者更小且維護開銷更低。
- 表中含有分類數(shù)據(jù)行時，可以為一種或多種類別的數(shù)據(jù)創(chuàng)建篩選索引。 通過將查詢范圍縮小為表的特定區(qū)域，這可以提高針對這些數(shù)據(jù)行的查詢性能。此外，由此得到的索引與全表非聚集索引相比，前者更小且維護開銷更低。
我們在創(chuàng)建索引時可以通過一個 WHERE 子句指定需要索引的數(shù)據(jù)行，從而創(chuàng)建一個過濾索引。例如，對于以下訂單表 orders：
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
status VARCHAR(10)
);
BEGIN
DECLARE @counter INT = 1
WHILE @counter <= 1000000
BEGIN
INSERT INTO orders
SELECT @counter, (rand() * 100000),
CASE
WHEN (rand() * 100)<1 THEN 'pending'
WHEN (rand() * 100)>99 THEN 'shipped'
ELSE 'completed'
END
SET @counter = @counter + 1
END
END;
訂單表中總共有 100 萬個訂單，通常絕大部分的訂單都處于完成狀態(tài)。一般情況下，我們只需要針對某個用戶未完成的訂單進行查詢跟蹤，因此可以創(chuàng)建一個基于用戶編號和狀態(tài)的部分索引：
CREATE INDEX full_idx ON orders (customer_id, status);
然后我們查看以下查詢語句的執(zhí)行計劃：
SET STATISTICS PROFILE ON SELECT * FROM orders WHERE customer_id = 5043 AND status != 'completed'; id |customer_id|status | ------+-----------+-------+ 743436| 5043|pending| 947848| 5043|shipped| Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions 2 1 SELECT * FROM [orders] WHERE [customer_id]=@1 AND [status]<>@2 1 1 0 NULL NULL NULL NULL 1.405213 NULL NULL NULL 0.003283546 NULL NULL SELECT 0 NULL 2 1 |--Index Seek(OBJECT:([hrdb].[dbo].[orders].[full_idx]), SEEK:([hrdb].[dbo].[orders].[customer_id]=(5043) AND [hrdb].[dbo].[orders].[status] < 'completed' OR [hrdb].[dbo].[orders].[customer_id]=(5043) AND [hrdb].[dbo].[orders].[status] > 'completed') ORDERED FORWARD) 1 2 1 Index Seek Index Seek OBJECT:([hrdb].[dbo].[orders].[full_idx]), SEEK:([hrdb].[dbo].[orders].[customer_id]=(5043) AND [hrdb].[dbo].[orders].[status] < 'completed' OR [hrdb].[dbo].[orders].[customer_id]=(5043) AND [hrdb].[dbo].[orders].[status] > 'completed') ORDERED FORWARD [hrdb].[dbo].[orders].[id], [hrdb].[dbo].[orders].[customer_id], [hrdb].[dbo].[orders].[status] 1.405213 0.003125 0.0001585457 27 0.003283546 [hrdb].[dbo].[orders].[id], [hrdb].[dbo].[orders].[customer_id], [hrdb].[dbo].[orders].[status] NULL PLAN_ROW 0 1
輸出結(jié)果顯示查詢利用索引 full_idx 掃描查找所需的數(shù)據(jù)。
我們可以查看一下索引 full_idx 占用的空間大?。?/p>
SELECT ix.name AS "Index name", SUM(sz.used_page_count) * 8/1024.0 AS "Index size (MB)" FROM sys.dm_db_partition_stats AS sz INNER JOIN sys.indexes AS ix ON sz.object_id = ix.object_id AND sz.index_id = ix.index_id INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id WHERE tn.name = 'orders' GROUP BY ix.name; Index name |Index size (MB)| ----------------------------+---------------+ full_idx | 26.171875| PK__orders__3213E83F1E3B8A3B| 29.062500|
接下來我們再創(chuàng)建一個部分索引，只包含未完成的訂單數(shù)據(jù)，從而減少索引的數(shù)據(jù)量：
CREATE INDEX partial_idx ON orders (customer_id) WHERE status != 'completed';
索引 partial_idx 中只有 customer_id 字段，不需要 status 字段。同樣可以查看一下索引 partial_idx 占用的空間大?。?/p>
SELECT ix.name AS "Index name", SUM(sz.used_page_count) * 8/1024.0 AS "Index size (MB)" FROM sys.dm_db_partition_stats AS sz INNER JOIN sys.indexes AS ix ON sz.object_id = ix.object_id AND sz.index_id = ix.index_id INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id WHERE tn.name = 'orders' GROUP BY ix.name; Index name |Index size (MB)| ----------------------------+---------------+ full_idx | 26.171875| partial_idx | 0.289062| PK__orders__3213E83F1E3B8A3B| 29.062500|
索引只有 0.29 MB，而不是 26 MB，因為絕大多數(shù)訂單都處于完成狀態(tài)。
以下查詢顯式了適用過濾索引時的執(zhí)行計劃：
SELECT * FROM orders WITH ( INDEX ( partial_idx ) ) WHERE customer_id = 5043 AND status != 'completed'; Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions 2 1 SELECT * FROM orders WITH ( INDEX ( partial_idx ) ) WHERE customer_id = 5043 AND status != 'completed' 1 1 0 NULL NULL NULL NULL 1.124088 NULL NULL NULL 0.03279812 NULL NULL SELECT 0 NULL 2 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([hrdb].[dbo].[orders].[id])) 1 2 1 Nested Loops Inner Join OUTER REFERENCES:([hrdb].[dbo].[orders].[id]) NULL 1.124088 0 4.15295E-05 24 0.03279812 [hrdb].[dbo].[orders].[id], [hrdb].[dbo].[orders].[customer_id], [hrdb].[dbo].[orders].[status] NULL PLAN_ROW 0 1 2 1 |--Index Seek(OBJECT:([hrdb].[dbo].[orders].[partial_idx]), SEEK:([hrdb].[dbo].[orders].[customer_id]=(5043)) ORDERED FORWARD) 1 3 2 Index Seek Index Seek OBJECT:([hrdb].[dbo].[orders].[partial_idx]), SEEK:([hrdb].[dbo].[orders].[customer_id]=(5043)) ORDERED FORWARD, FORCEDINDEX [hrdb].[dbo].[orders].[id], [hrdb].[dbo].[orders].[customer_id] 9.935287 0.003125 0.0001679288 15 0.003292929 [hrdb].[dbo].[orders].[id], [hrdb].[dbo].[orders].[customer_id] NULL PLAN_ROW 0 1 2 2 |--Clustered Index Seek(OBJECT:([hrdb].[dbo].[orders].[PK__orders__3213E83F1E3B8A3B]), SEEK:([hrdb].[dbo].[orders].[id]=[hrdb].[dbo].[orders].[id]) LOOKUP ORDERED FORWARD) 1 5 2 Clustered Index Seek Clustered Index Seek OBJECT:([hrdb].[dbo].[orders].[PK__orders__3213E83F1E3B8A3B]), SEEK:([hrdb].[dbo].[orders].[id]=[hrdb].[dbo].[orders].[id]) LOOKUP ORDERED FORWARD, FORCEDINDEX [hrdb].[dbo].[orders].[status] 1 0.003125 0.0001581 16 0.02946366 [hrdb].[dbo].[orders].[status] NULL PLAN_ROW 0 9.935287
我們比較通過 full_idx 和 partial_idx 執(zhí)行以下查詢的時間：
-- 300 ms SELECT count(*) FROM orders WITH ( INDEX ( full_idx ) ) WHERE status != 'completed'; -- 10 ms SELECT count(*) FROM orders WITH ( INDEX ( partial_idx ) ) WHERE status != 'completed';
另外，過濾索引還可以用于實現(xiàn)其他的功能。例如，我們可以將索引 partial_idx 定義為唯一索引，從而實現(xiàn)每個用戶只能存在一個未完成訂單的約束。
DROP INDEX partial_idx ON orders; TRUNCATE TABLE orders; CREATE UNIQUE INDEX partial_idx ON orders (customer_id) WHERE status != 'completed'; INSERT INTO orders(id, customer_id, status) VALUES (1, 1, 'pending'); INSERT INTO orders(id, customer_id, status) VALUES (2, 1, 'pending'); SQL 錯誤 [2601] [23000]: 不能在具有唯一索引“partial_idx”的對象“dbo.orders”中插入重復(fù)鍵的行。重復(fù)鍵值為 (1)。
用戶必須完成一個訂單之后才能繼續(xù)生成新的訂單。
通過以上介紹可以看出，過濾索引是一種經(jīng)過優(yōu)化的非聚集索引，尤其適用于從特定數(shù)據(jù)子集中選擇數(shù)據(jù)的查詢。
到此這篇關(guān)于利用 SQL Server 過濾索引提高查詢語句的性能分析的文章就介紹到這了,更多相關(guān)SQL Server索引提高語句性能內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信