☠️

確実に遅くなるSQL

2022/08/11に公開約7,400字

目的

確実に遅くなるSQLを理解することで、それを避け、良いSQLを書けるようになること。

検証DB

今回検証に用いたDBは、MySQL5.6.10。

確実に遅くなるSQL

<インデックスが使われていない>

1. インデックス列に加工をしているSQL

インデックスを貼っている列に対して加工をして絞り込むと、せっかく用意したインデックスが使われなくなってしまう。(加工 : 計算、文字列の追加など)
逆に言うと、インデックスを使用するにはその列を裸で使うこと。

下記SQLのように、WHERE句を3パターン用意して検証。

  • <1> : 通常はインデックスが使われていることを確認するために用意。(msg_typeにはインデックスが貼られている。)
  • <2> : インデックスを貼っている列を加工することでインデックスが使われなくなることを確認するために用意。
  • <3> : インデックスを貼っている列と比較する側の値に加工することでインデックスが使われるようになり、<2>の状態から改善されることを確認するために用意。
SELECT *
FROM messages
where msg_type = 50 -- <1>
-- WHERE msg_type / 2 > 50 -- <2>
-- where msg_type > 50 / 2 -- <3>
;

EXPLAIN実行結果

<1> : 通常はインデックスが使われていることが確認できた。

<2> : インデックスを貼っている列を加工することでインデックスが使われなくなった。

<3> : インデックスが使われるように改善された。

2. インデックス列に否定形を使っているSQL

<>, !=, NOT INを使う場合はインデックスが使用できない。

SELECT *
FROM messages
WHERE msg_type = 50 -- <1>
-- WHERE msg_type <> 50 -- <2>
;

EXPLAIN実行結果

<1> : 通常はインデックスが使われていることが確認できた。

<2> : インデックスを貼っている列に、否定(<>)することでインデックスが使われなくなった。

3. インデックス列にORを使っているSQL

  • (column_1, column_2)に複合インデックスが貼られている場合
  • column_1, column_2にそれぞれ別々のインデックスが貼られている場合

上記の場合、条件にORを使うとインデックスが使用されなくなるか、使えたとしてもANDより非効率な検索になる。

msg_typeとuser_toで複合インデックスを成しているテーブルにて検証。

SELECT *
FROM messages
WHERE msg_type = 50
AND user_to = 1000 -- <1>
-- OR  user_to = 1000 -- <2>
;

EXPLAIN実行結果

<1> : 複合インデックスを成す列同士をAND条件で絞る際はインデックスが使われていることが確認できた。

<2> : 複合インデックスを成す列同士をOR条件で指定すると、インデックスが使われなくなることが確認できた。

4. 複合インデックスが使えていないSQL

複合インデックスはcolumn_1から順に整列されてインデックスが形成されている。
そのため、「column_1, column_2, column_3」の順で複合インデックスが貼られている場合、
[column_1, column_3だけ使う], [column_2, column_3だけ使う]ような指定では複合インデックスが使えない。

<1> 全てのカラムを指定
SELECT *
FROM messages
WHERE 1 = 1
AND msg_type = 50 -- column_1
AND group_type = 2 -- column_2
AND group_number = 100 -- column_3
;
<2> column_1, column_2だけを指定
SELECT *
FROM messages
WHERE 1 = 1
AND msg_type = 50
AND group_type = 2
-- AND group_number = 100
;
<3> column_1, column_3だけを指定
SELECT *
FROM messages
WHERE 1 = 1
AND msg_type = 50
-- AND group_type = 2
AND group_number = 100
;
<4> column_2, column_3だけを指定
SELECT *
FROM messages
WHERE 1 = 1
-- AND msg_type = 50
AND group_type = 2
AND group_number = 100
;

EXPLAIN実行結果

<1> : 複合インデックスが使われていることが確認できた。

<2> : 複合インデックスが使われていることが確認できた。

<3> : 本来使いたい複合インデックスは使われなかった。(それとは別の、column_1にだけ有効なインデックスを使っている。)

<4> : 複合インデックスは使われなかった。

5. インデックス列に後方一致or中間一致のLIKE述語を使っているSQL

LIKE述語は、前方一致のみインデックスが使用される

SELECT *
FROM users
WHERE email LIKE 'hoge%' -- <1>
-- WHERE email LIKE '%hoge%' -- <2>
;

EXPLAIN実行結果

<1> : 前方一致のLIKE検索では、インデックスが使われていることが確認できた。

<2> : 中間一致のLIKE検索では、インデックスが使われなくなった。

6. インデックス列に暗黙の型変換を行っているSQL

暗黙の型変換を行うと、インデックスが使用不可になる。

文字列型で定義された列に対して、数値と比較して検証。

SELECT *
FROM userdata
WHERE school_name2 = '222' -- <1: 文字列型 = 文字列 >
-- WHERE school_name2 = 222 -- <2: 文字列型 = 数値 >
;

EXPLAIN実行結果

<1> : 前方一致のLIKE検索では、インデックスが使われていることが確認できた。

<2> : 中間一致のLIKE検索では、インデックスが使われなくなった。

参考記事 : 記事

7. インデックス列にNULLが存在する

NULLが多い列にIS NULLIS NOT NULLを使う場合に、インデックスが使われなかったりすることがある。(実装により異なる。)

インデックス列(msg_type)がNULLであるレコードが1件しかなかったため、検証用に全2,778,844レコード中、812,078レコードをNULLにsetしたテーブルを用意。

SELECT *
FROM messages
WHERE msg_type = 50 -- <1>
-- WHERE msg_type = 51 -- <2>
-- WHERE msg_type is null -- <3>
-- WHERE msg_type is not null -- <4>
-- WHERE msg_type = 3 -- <5>
;

EXPLAIN実行結果

<1> : 通常はインデックスが使われていることが確認できた。

<2> : 通常はインデックスが使われていることが確認できた。

<3> : NULLが多い列に対してIS NULLを使うと、インデックスが使われなかった。

<4> : NULLが多い列に対してIS NOT NULLを使うと、インデックスが使われなかった。

<5> : インデックスが使われなかった。

msg_type レコード数 検証No. インデックスの使用 考察
50 32,125 <1> 使った -
51 1 <2> 使った -
NULL 812,078 <3>, <4> 使わなかった
(IS NULLIS NOT NULLともに)
ちなみにNULLのレコードが1件ときの実行結果は、IS NULLはインデックスを使い、IS NOT NULLは使わなかった(1件以外はnot nullなのでフルスキャン)。
3 1,882,560 <5> 使わなかった テーブルの大多数のためフルスキャンした方が良いと判断したと思われる。
(その他) 52,080 - - -
(合計) 2,778,844 - - -

<無駄にソートする>

8. 重複を残しても良い場面で、UNIONにALLオプションを付けていないSQL

ALLオプションを付けないと、暗黙的にソートが行われてパフォーマンスが悪くなる。

ALLオプション 実行時間 (n=5平均)
無し 150.6 ms
有り 73.1 ms
ALL無し
SELECT v.id
FROM voices v -- テーブルの選定に特に意味は無い
UNION
SELECT u.id
FROM users u
;
ALL有り
SELECT v.id
FROM voices v
UNION ALL
SELECT u.id
FROM users u
;

参考

https://www.shoeisha.co.jp/book/detail/9784798157825

以上

Discussion

ログインするとコメントできます