大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。
Microsoft SQL Server 過濾索引（篩選索引）是指基于滿足特定條件的數(shù)據(jù)行進(jìn)行索引。與全表索引（默認(rèn)創(chuàng)建）相比，設(shè)計(jì)良好的篩選索引可以提高查詢性能、減少索引維護(hù)開銷并可降低索引存儲開銷。本文就給大家介紹一下 Microsoft SQL Server 中的過濾索引功能。
在創(chuàng)建過濾索引之前，我們需要了解它的適用場景。
- 在某個字段中只有少量相關(guān)值需要查詢時，可以針對值的子集創(chuàng)建過濾索引。 例如，當(dāng)字段中的值大部分為 NULL 并且查詢只從非 NULL 值中進(jìn)行選擇時，可以為非 NULL 數(shù)據(jù)行創(chuàng)建篩選索引。 由此得到的索引與對相同字段定義的全表非聚集索引相比，前者更小且維護(hù)開銷更低。
- 表中含有分類數(shù)據(jù)行時，可以為一種或多種類別的數(shù)據(jù)創(chuàng)建篩選索引。 通過將查詢范圍縮小為表的特定區(qū)域，這可以提高針對這些數(shù)據(jù)行的查詢性能。此外，由此得到的索引與全表非聚集索引相比，前者更小且維護(hù)開銷更低。
我們在創(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)。一般情況下，我們只需要針對某個用戶未完成的訂單進(jìn)行查詢跟蹤，因此可以創(chuàng)建一個基于用戶編號和狀態(tài)的部分索引：
CREATE INDEX full_idx ON orders (customer_id, status);
然后我們查看以下查詢語句的執(zhí)行計(jì)劃：
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 占用的空間大小：
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 占用的空間大小：
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，因?yàn)榻^大多數(shù)訂單都處于完成狀態(tài)。
以下查詢顯式了適用過濾索引時的執(zhí)行計(jì)劃：
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';
另外，過濾索引還可以用于實(shí)現(xiàn)其他的功能。例如，我們可以將索引 partial_idx 定義為唯一索引，從而實(shí)現(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)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- Sql Server 索引使用情況及優(yōu)化的相關(guān)Sql語句分享
- SQL Server 索引維護(hù)sql語句
- Sql Server 數(shù)據(jù)庫索引整理語句,自動整理數(shù)據(jù)庫索引
- SQL Server 索引結(jié)構(gòu)及其使用（二） 改善SQL語句
