0. はじめに
SQL Serverなんか嫌いだ！
いきなり、本音をつぶやいてしまいましたが、SQL Serverについて受けた問い合わせをいろいろ調べているうちに得られた情報をブログに書こうと思います。（本当はもっと面白いネタがあったのですが…）
SQL Serverに限らず、よくあるSQL文の書き方のミスの１つに、データ型ミスマッチ というものがあります。例えば、以下のように、文字列型のカラムidと数値リテラルを=で比較してしまうケースが当たります
SELECT * FROM test WHERE id = 1
SQLにおいては、文字列型と数値型のデータを=で比較する場合、内部で暗黙的に文字列型のデータを数値型に変換した上で比較することになっています。
（以下のSQL文は暗黙変換のイメージです）
SELECT * FROM test WHERE to_number(id) = 1 -- Oracleの場合
こうなると、カラムidにインデックスを作成しても、このSQL文ではインデックスが利用されずに処理時間が長くなってしまいます。
SQL Severにおいて、VARCHAR型とNVARCHAR型のデータを=で比較する場合も、VARCHAR型のデータをNVARCHAR型に暗黙変換されます。そのため、遅くなるのが普通なのですが、実際には問題ないケースが存在します。
そこで、今回はVARCHAR型とNVARCHAR型のデータミスマッチをしてもインデックスが利用できるケースについて、どう動いているのかを追ってみたいと思います。
1. インデックスが利用できるケースの再現
本記事の手順はすべてREAD COMMITED SNAPSHOTを有効にしたデータベース上で確認をとっています。
まず、以下のスクリプトでテスト用テーブルとデータ(5,000万件)を作成します。
CREATE TABLE test (
id1 VARCHAR(10), -- クラスタ化索引あり列
id2 VARCHAR(10), -- 索引なし列
dummy VARCHAR(100), -- データ量かさ増し用のダミー列
CONSTRAINT pk_test PRIMARY KEY CLUSTERED (id1)
);
-- 5,000万件のテストデータ生成
DECLARE @i INT = 0;
WHILE @i < (50000000 / 5000)
BEGIN
-- 5,000件ずつデータ生成
INSERT INTO test
SELECT
right('00000000' + cast(a.num + 5000 * @i AS VARCHAR), 8),
right('00000000' + cast(a.num + 5000 * @i AS VARCHAR), 8),
replicate('A', 100)
FROM
-- sys.all_columnsを使って5,000件データ生成
(SELECT TOP 5000 row_number() over (order by object_id) num FROM sys.all_columns) a;
SET @i = @i + 1;
END;
このテーブルについて、まずはVARCHAR型のデータ同士を比較する以下のSQL文を実行してみます。
SELECT
*
FROM
test
WHERE
-- VARCHAR = VARCHAR
id1 = '00000123';
すると、実行プランは以下のようにインデックスを利用できており、実際の実行も一瞬で終わります。これは想定通りの動作です。
次に、VARCHAR型とNVARCHAR型のデータを比較する以下のSQL文を実行してみます。
SELECT
*
FROM
test
WHERE
-- VARCHAR = NVARCHAR
id1 = N'00000123';
# ちなみに、このSQL文ではNVARCHAR型のリテラルを指定していますが、
# JavaプログラムからJDBCでアクセスする場合に、条件を
# PreparedStatement#setStringで渡すと、NVARCHAR型になるため、
# 似たような挙動になります。
本来は、VARCHAR型がNVARCHAR型に変換されて比較されるためインデックスが有効活用できず、フルスキャンが起こるはずですが、実際にはそうならず、インデックスを利用して一瞬で実行は完了します。以下がこのSQL文の実行プランです。
ちなみに、インデックスが作成されていないカラムid2に対して検索すると、フルスキャンが発生し、手元の環境では実行完了に90秒程度を要します。
SELECT
*
FROM
test
WHERE
-- 索引がない列の検索
id2 = '00000123';
2. 内部的な動作の確認
VARCHAR型とNVARCHAR型を比較するケースにおいて、なぜインデックスが利用でき高速なのかを探るために、詳細を見ていきたいと思います。
まずは、プラン詳細の述語の部分に注目してみます。
【述語】の部分を確認すると、CONVERT_IMPLICITが利用されており、内部では想定通りVARCHAR型のカラムid1に対してNVARCHAR型への暗黙型変換が行われていることが分かります。
ただし、インデックスを利用したデータ絞り込みの条件【シーク述語】を確認すると、SQL文の上では等価条件にも関わらず範囲選択が行われています。分かりやすく書き直すと以下のような条件ですね。
[Expr1004] < id1 AND id1 < [Expr1005]
この [Expr1004]と[Expr1005]がどこから出てきたかは、GUIではなく実行プランのXMLデータを直接見てみると何となく分かります（飽くまで何となくですが）。以下がその抜粋です。
<DefinedValue>
<ValueVector>
<ColumnReference Column="Expr1004" />
<ColumnReference Column="Expr1005" />
<ColumnReference Column="Expr1003" />
</ValueVector>
<ScalarOperator ScalarString="GetRangeThroughConvert([@1],[@1],(62))">
@1はSQL文で指定されたN'00000123'を示しています。
また、 GetRangeThroughConvertの第3引数(62)はVARCHAR型を表しています。
つまりは、 GetRangeThroughConvertはN'00000123'～N'00000123'というNVARCHAR型の範囲をVARCHAR型に変換した場合、どの範囲に該当するかを求めているのではないかと想像できます。
この関数はSQL Serverの内部関数なので仕様は公開されておらず、ドキュメントも見つけられませんでした。そのため、[Expr1004]と[Expr1005]が具体的にどういった値になるかは特定できませんが、それを推測するために、以下のWITH (UPDLOCK)付きのSQL文がどのキーに対してロックを取得(Lock:Acquired)するか確認します。
SELECT
*
FROM
test WITH (UPDLOCK)
WHERE
-- VARCHAR = NVARCHAR
id1 = N'00000123';
この時のSQL Profilerの取得内容は以下になります。
(ad143ec0fd9a)と(d4505477816e)という2つのキーに対してロックを取得していることが分かります。これが具体的にどのレコードを指すのかは、以下のSQL文で調べることができます。
SELECT TOP 2
*
FROM
test
WHERE
%%lockres%% IN (
'(ad143ec0fd9a)',
'(d4505477816e)'
);
結果は以下のようになり、id1 = '00000123', '00000124'の2レコードであることが分かります。
また、元のSQL文の実行時ライブクエリ統計において、「読み取った行数」が2件になっていることでも、この2件の範囲をインデックスシークしていることが分かります。
つまり、id1 = N'00000123'というVARCHAR/NVARCHARの等価条件は、少なくとも、id1 <= '00000123' AND '00000124' <= id1というVARCHAR同士の範囲条件に読み替えられて、インデックスシークが利用されていると想像されます。
（<=と<の違いを考えると、実際にはもう少し広い範囲をGetRangeThroughConvertは返しているのではないかと思いますが）
以上のような動作で、VARCHAR型とNVARCHAR型のミスマッチにも関わらず、インデックスが利用可能で高速になります。
3. ならVARCHAR/NVARCHAR型のミスマッチを気にしなくて良いかというと…
インデックスシークで読み取る件数が若干増えるとはいえ高速に動作するのであれば、VARCHAR型とNVARCHAR型のミスマッチは気にしなくて良いかというと、残念ながらNGなケースがあります。
特に問題になるのが複合キーによる検索の場合です。id1とid2が複合主キーになっている以下のテーブルで見てみます。
-- id1とid2の複合主キー
CREATE TABLE test02 (
id1 VARCHAR(10),
id2 VARCHAR(10),
dummy VARCHAR(100),
CONSTRAINT pk_test02 PRIMARY KEY CLUSTERED (id1, id2)
);
このテーブルについて、以下のSQLを投げた場合、実行プランとしてはインデックスシークが選択されるのですが、実際には遅いです。
SELECT
*
FROM
test02
WHERE
id1 = N'1'
AND id2 = N'00000123';
プラン詳細を見ると【シーク述語】において、複合キーの1番目のカラムid1でしか絞り込みが行われていません。これは、「? < id1 < ? & ? < id2 < ?」という条件が指定された場合、複合インデックスが適切に利用できないケースに似ています。
また、別の問題として、先に確認したようにデータ型ミスマッチがある場合、適切にデータ型を合わせた場合に比べ、インデックスシーク範囲が広く、余分なロックが取得されるという問題もあります。
なので、やはりできる限りデータ型は一致させるべきです。
4. さいごに
今回は、SQL文でよくあるミスの1つであるデータ型ミスマッチが、SQL Serverにおいてあまり問題とならないケースとその推測される原因についてご紹介しました。加えて、問題が発生するケースについても示しました。
ここからは個人的な感想なのですが…
GetRangeThroughConvertの仕組みを知ったときは、SQL Server頑張っているなぁという第一印象を持ちました。しかし、本来はSQL文の記述の仕方を修正することで対応すべき問題であることを考えると、あまりうれしくない機能のようにも思えます。
特に、AP開発者の人が、 この仕組みのおかげでデータ型ミスマッチはいけないことだと気づくのが遅くなる、もしくは気づかないというのは、そのAP開発者にとって不幸なのではないでしょうか。
ということで、やっぱりSQL Serverは嫌いです。
（私のSQL Server嫌いは今回の件だけの話ではないのですが）
