文字列結合を使うと、インデックスがうまく使われない場合がある

仕事のコードレビュー中、レビュイーから質問されたことをメモにしておきます(´・ω・｀)

前提

以下のような複合主キーを持つテーブルがあるとします。

CREATE TABLE t (
  col1 CHAR(1),
  col2 CHAR(1),
  col3 CHAR(1),
  col4 CHAR(1),
  PRIMARY KEY (col1, col2, col3, col4)
)

このテーブルから以下のような条件を満たす行を取得したいとします (よくあるやつですね)

col1	col2	col3	col4
A	B	C	D
E	F	G	H
I	J	K	L
M	N	O	P
Q	R	S	T
U	V	W	X
Y	Z	A	B

SQLコーディング規約として、INの利用が推奨されていたこともあり、レビュイーの提出したSQLは以下のようなものでした (これをSQL1とします)

SELECT *
FROM t
WHERE CONCAT(col1, '@', col2, '@', col3, '@', col4) IN (
  'A@B@C@D',
  'E@F@G@H',
  'I@J@K@L',
  'M@N@O@P',
  'Q@R@S@T',
  'U@V@W@X',
  'Y@Z@A@B'
);

自分はレビュアーとして「SQL1はインデックスが利かない可能性があるから、以下のように修正してほしい」とお願いしました (これをSQL2とします)

SELECT *
FROM t
WHERE (col1 = 'A' AND col2 = 'B' AND col3 = 'C' AND col4 = 'D')
   OR (col1 = 'E' AND col2 = 'F' AND col3 = 'G' AND col4 = 'H')
   OR (col1 = 'I' AND col2 = 'J' AND col3 = 'K' AND col4 = 'L')
   OR (col1 = 'M' AND col2 = 'N' AND col3 = 'O' AND col4 = 'P')
   OR (col1 = 'Q' AND col2 = 'R' AND col3 = 'S' AND col4 = 'T')
   OR (col1 = 'U' AND col2 = 'V' AND col3 = 'W' AND col4 = 'X')
   OR (col1 = 'Y' AND col2 = 'Z' AND col3 = 'A' AND col4 = 'B')

ちなみにSQLServerはいわゆる行値式をサポートしていないため、以下のようなSQLは不正になります。

SELECT *
FROM t
WHERE (col1, col2, col3, col4) IN (
  ('A', 'B', 'C', 'D'),
  ('E', 'F', 'G', 'H'),
  ('I', 'J', 'K', 'L'),
  ('M', 'N', 'O', 'P'),
  ('Q', 'R', 'S', 'T'),
  ('U', 'V', 'W', 'X'),
  ('Y', 'Z', 'A', 'B')
);

実行計画

レビューコメントでいろいろ説明したのですが、いまいち納得してもらえなかったので、実際に実行計画を見てもらうことにしました。

まず以下のようなSQLで、テストデータ456976件(=26の4乗)を投入します。

WITH s (alphabet) AS (
            SELECT 'A'
  UNION ALL SELECT 'B'
  UNION ALL SELECT 'C'
  UNION ALL SELECT 'D'
  UNION ALL SELECT 'E'
  UNION ALL SELECT 'F'
  UNION ALL SELECT 'G'
  UNION ALL SELECT 'H'
  UNION ALL SELECT 'I'
  UNION ALL SELECT 'J'
  UNION ALL SELECT 'K'
  UNION ALL SELECT 'L'
  UNION ALL SELECT 'M'
  UNION ALL SELECT 'N'
  UNION ALL SELECT 'O'
  UNION ALL SELECT 'P'
  UNION ALL SELECT 'Q'
  UNION ALL SELECT 'R'
  UNION ALL SELECT 'S'
  UNION ALL SELECT 'T'
  UNION ALL SELECT 'U'
  UNION ALL SELECT 'V'
  UNION ALL SELECT 'W'
  UNION ALL SELECT 'X'
  UNION ALL SELECT 'Y'
  UNION ALL SELECT 'Z'
)
INSERT INTO t (col1, col2, col3, col4)
SELECT 
  a.alphabet,
  b.alphabet,
  c.alphabet,
  d.alphabet
FROM s AS a
CROSS JOIN s AS b
CROSS JOIN s AS c
CROSS JOIN s AS d

あとは実行計画を取得するだけです。まずはSQL1から。

SHOWPLAN_ALL  (2.771)
 +-Filter(WHERE:([Expr1002]='Y@Z@A@B' OR [Expr1002]='U@V@W@X' OR [Expr1002]='Q@R@S@T' OR [Expr1002]='M@N@O@P' OR [Expr1002]='I@J@K@L' OR [Expr1002]='E@F@G@H' OR [Expr1002]='A@B@C@D'))  (2.771)
    +-Compute Scalar(DEFINE:([Expr1002]=concat([master].[dbo].[t].[col1],'@',[master].[dbo].[t].[col2],'@',[master].[dbo].[t].[col3],'@',[master].[dbo].[t].[col4])))  (1.684)
       +-Clustered Index Scan(OBJECT:([master].[dbo].[t].[PK__t__193CF730906FC80E]))  (1.638)

次にSQL2の実行計画は以下のようになりました。

SHOWPLAN_ALL  (0.003)
 +-Clustered Index Seek(OBJECT:([master].[dbo].[t].[PK__t__193CF730906FC80E]), SEEK:([master].[dbo].[t].[col1]='A' AND [master].[dbo].[t].[col2]='B' AND [master].[dbo].[t].[col3]='C' AND [master].[dbo].[t].[col4]='D' OR [master].[dbo].[t].[col1]='E' AND [master].[dbo].[t].[col2]='F' AND [master].[dbo].[t].[col3]='G' AND [master].[dbo].[t].[col4]='H' OR [master].[dbo].[t].[col1]='I' AND [master].[dbo].[t].[col2]='J' AND [master].[dbo].[t].[col3]='K' AND [master].[dbo].[t].[col4]='L' OR [master].[dbo].[t].[col1]='M' AND [master].[dbo].[t].[col2]='N' AND [master].[dbo].[t].[col3]='O' AND [master].[dbo].[t].[col4]='P' OR [master].[dbo].[t].[col1]='Q' AND [master].[dbo].[t].[col2]='R' AND [master].[dbo].[t].[col3]='S' AND [master].[dbo].[t].[col4]='T' OR [master].[dbo].[t].[col1]='U' AND [master].[dbo].[t].[col2]='V' AND [master].[dbo].[t].[col3]='W' AND [master].[dbo].[t].[col4]='X' OR [master].[dbo].[t].[col1]='Y' AND [master].[dbo].[t].[col2]='Z' AND [master].[dbo].[t].[col3]='A' AND [master].[dbo].[t].[col4]='B') ORDERED FORWARD)  (0.003)

結論

SQL1のコストは2.771、一方、SQL2のコストは0.003で、パフォーマンスに大きく違いがあることが一目でわかりますね。この差の原因は何かというと、インデックスが使えているかどうかで、SQL1はClustered Index Scanで全量走査になっているのに対して、SQL2はClustered Index Seekなので、インデックスが適切に利用されています。

データ件数が少ないので、この程度のパフォーマンス差におさまっていますが、データ量が増えてくると、SQL1のようなSQLは実用に耐えなくなる可能性があります。

環境情報

Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) 
	Jan 25 2021 20:16:12 
	Copyright (C) 2019 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>