確実に遅くなるSQL

目的

確実に遅くなる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> : インデックスが使われるように改善された。

EXPLAINについて

使い方

• 実行したいSQLの先頭にEXPLAINを付けて実行。
• テーブルのレコード数が比較的少なかったり、実行結果がそのテーブルの大多数を占めるSQLに対してEXPLAINすると、インデックス(key)を使用しない場合がある。インデックスを使用せずテーブルをフルスキャンした方が効率的だとオプティマイザが判断して最適化するため。(実際に今回の検証で起きた。)
  • 参考記事 : MySQLチューニング基礎の71ページ目, Qiita, teratail回答
    

結果の見方

項目	説明
select_type	クエリの種類。JOIN、UNION、サブクエリの3つのどれを使うかによって異なる。 SIMPLE : 単純なSELECT文。(UNION、サブクエリを使わない。) PRIMARY : 最も外側のSELECT文。(サブクエリを使う側 or UNIONの1つ目) SUBQUERY : FROM句ではないサブクエリのSELECT文。 DERIVED : FROM句で使われているサブクエリのSELECT文。 UNION : UNIONの2つ目以降のSELECT文。 UNION_RESULT : UNIONの実行結果を取得するためのSELECT文。
type	どのようにレコードにアクセスするか。 const : 一致するレコード数が0 or 1。PRIMARY KEY or UNIQUEインデックス が貼られている列を定数値と比較する場合。最速。 eq_ref : JOINするときにPRIMARY KEYやUNIQUE KEYが使われることを表す。constに似ているが、JOINの内部表へのアクセスで用いられる点が異なる。const以外の最適。 ref : PRIMARY KEY or UNIQUEでないインデックスを使って等価検索(WHERE key = value)を行うことを表す。 range : インデックスを用いた範囲検索。 ALL : フルテーブルスキャン。重い処理。 index : フルインデックススキャン。該当のインデックスをスキャンする重い処理。
possible_keys	利用可能なインデックス。ただし、使うとは限らない。 NULLの場合、インデックスが使われない。
key	MySQLが実際に使用すると決定したインデックス。 NULLの場合、インデックスが使われない。
rows	調査が必要と考える行の見積もり。(正確な数値ではない。) ExtraにUsing whereが表示されている場合は、rowsに対してさらにWHERE句で絞り込んだものが結果として返される。
Extra	クエリーを解決する方法に関する追加情報。オプティマイザがどのような戦略を立てたかを知ることが出来る。

参考記事 : MySQLリファレンス, 記事(Extraの説明が特に良い), 記事

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 NULLやIS 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 NULL、IS 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オプションを付けないと、暗黙的にソートが行われてパフォーマンスが悪くなる。

UNIONは重複を排除する。
UNION ALL(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

以上