BinaryDevelop
  1. ホーム
  2. データベース
  3. エムエスエル

SQL Server のフィルタードインデックスによるクエリ文の改善に関するパフォーマンス分析

2022-01-05 11:28:37

皆さんこんにちは、ヘアカットの話ではなく、テクニックの話ばかりしている講師のトニーです。

Microsoft SQL Server のフィルター付きインデックス（filtered index）は、特定の条件を満たしたデータ行を基にしたインデックスです。うまく設計されたフィルタードインデックスは、フルテーブルインデックス（デフォルトで作成される）と比較して、クエリパフォーマンスの向上、インデックス保守のオーバーヘッドの削減、インデックスストレージのオーバーヘッドの削減が可能です。この記事では、Microsoft SQL Server のフィルタリングインデックス機能について紹介します。

フィルタリング・インデックスを作成する前に、それが適用されるシナリオを理解する必要があります。

  • クエリするフィールドに関連する値がわずかしかない場合、値のサブセットに対してフィルタインデックスを作成することができます。たとえば、フィールドの値の大半がNULLで、クエリがNULLでない値からしか選択しない場合、NULLでないデータ行のフィルタインデックスを作成することができます。結果として得られるインデックスは、同じフィールドに定義されたテーブル全体の非集約インデックスよりも小さく、維持コストも低くなります。
  • テーブルがカテゴリデータ行を含む場合、データの1つまたは複数のカテゴリに対するフィルタインデックスを作成することができます。これは、クエリをテーブルの特定の領域に絞り込むことで、これらのデータ行に対するクエリのパフォーマンスを向上させることができます。さらに、結果として得られるインデックスは、フルテーブルの非集約インデックスよりも小さく、維持コストも低くなります。

インデックスを作成する際に、WHERE句でインデックスの対象となるデータ行を指定することで、フィルタリングインデックスを作成することができます。たとえば、次のような注文テーブルの注文の場合。 

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

インデックスが26MBではなく、0.29MBしかないのは、注文の大半が完了状態であるためです。

次のクエリは、フィルタリングされたインデックスが適用された場合の実行プランを明示的に示しています。 

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 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 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というインデックスをユニークインデックスとして定義することで、「未完成の注文は1ユーザーにつき1つしか存在できない」という制約を実装することができます。 

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 error [2601] [23000]: Cannot insert a row with duplicate key in the object "dbo.orders" with unique index "partial_idx". The duplicate key value is (1).

ユーザーは、新しい注文を生成し続ける前に、注文を完了させる必要があります。

上記の紹介からわかるように、フィルタリングインデックスは最適化された非集計インデックスで、特定の部分集合からデータを選択するクエリに特に有効なインデックスです。

今回の記事は、SQL Serverのフィルタリングインデックスを利用して、クエリ文のパフォーマンスを向上させるというものです。SQL Server のインデックスを使ったステートメントのパフォーマンス向上については、スクリプトハウスの過去記事を検索するか、引き続き以下の関連記事を閲覧してください。

関連

最新

おすすめ

について
/
プライバシー・ポリシー
/
お問い合わせ
© 2022 BinaryDevelop. すべての権利は、それぞれの所有者に帰属します