久久久蜜臀国产一区二区,亚洲一区二区三区影院,一区二区三区四区av

主頁 > 知識庫 > 利用 SQL Server 過濾索引提高查詢語句的性能分析

利用 SQL Server 過濾索引提高查詢語句的性能分析

大家好，我是只談技術不剪發的 Tony 老師。

Microsoft SQL Server 過濾索引（篩選索引）是指基于滿足特定條件的數據行進行索引。與全表索引（默認創建）相比，設計良好的篩選索引可以提高查詢性能、減少索引維護開銷并可降低索引存儲開銷。本文就給大家介紹一下 Microsoft SQL Server 中的過濾索引功能。

在創建過濾索引之前，我們需要了解它的適用場景。

在某個字段中只有少量相關值需要查詢時，可以針對值的子集創建過濾索引。例如，當字段中的值大部分為 NULL 并且查詢只從非 NULL 值中進行選擇時，可以為非 NULL 數據行創建篩選索引。由此得到的索引與對相同字段定義的全表非聚集索引相比，前者更小且維護開銷更低。
表中含有分類數據行時，可以為一種或多種類別的數據創建篩選索引。通過將查詢范圍縮小為表的特定區域，這可以提高針對這些數據行的查詢性能。此外，由此得到的索引與全表非聚集索引相比，前者更小且維護開銷更低。

我們在創建索引時可以通過一個 WHERE 子句指定需要索引的數據行，從而創建一個過濾索引。例如，對于以下訂單表 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 萬個訂單，通常絕大部分的訂單都處于完成狀態。一般情況下，我們只需要針對某個用戶未完成的訂單進行查詢跟蹤，因此可以創建一個基于用戶編號和狀態的部分索引：

CREATE INDEX full_idx ON orders (customer_id, status);

然后我們查看以下查詢語句的執行計劃：

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

輸出結果顯示查詢利用索引 full_idx 掃描查找所需的數據。

我們可以查看一下索引 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|

接下來我們再創建一個部分索引，只包含未完成的訂單數據，從而減少索引的數據量：

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，因為絕大多數訂單都處于完成狀態。

以下查詢顯式了適用過濾索引時的執行計劃：

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 執行以下查詢的時間：

-- 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';

另外，過濾索引還可以用于實現其他的功能。例如，我們可以將索引 partial_idx 定義為唯一索引，從而實現每個用戶只能存在一個未完成訂單的約束。

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”中插入重復鍵的行。重復鍵值為 (1)。

用戶必須完成一個訂單之后才能繼續生成新的訂單。

通過以上介紹可以看出，過濾索引是一種經過優化的非聚集索引，尤其適用于從特定數據子集中選擇數據的查詢。

到此這篇關于利用 SQL Server 過濾索引提高查詢語句的性能分析的文章就介紹到這了,更多相關SQL Server索引提高語句性能內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: