MySQLにおけるクエリ（query）の速度改善
MySQLのレスポンスタイムを向上させる方法は、
サーバースペックを上げたり、コネクションプールやトランザクションロックの調整等、
さまざまテクニックはあるが、
今回は、クエリチューニングとテーブルのカラム見直しによる速度改善の鬼になりたいと思いまする。
（つまり、SQLの書き方による速度改善）
TL;DR
常識だとは思いますが、
レコードのスキャン数が少なければ少ないほど、クエリは高速に処理を終えることができます。
本稿は
- 効率的なクエリで解析処理を速くする
- インデックスをうまく活用して処理を早くする
この2大テーマで、
全集中で箇条書きして参る。
▼効率的なクエリで解析処理を速くする
SQL文をシンプルに記述する
もちろん複雑すぎるクエリの方が処理に時間がかかるので、
なるべくシンプルに書いて、SQLの処理時間を短縮する
SQL文の表現を統一して「キャッシュ」を上手く使う
SQLの予約語のケース(大小)を統一
文字ケースの大小を統一して、記述の仕方を揃えることで、
キャッシュを使う確率が上がり、SQLの高速化につながる。
ちなみに、キーワードを大文字で統一することで、デバッグ時など、検索キーワードとして指定しやすくなり、役立つよ〜
バインド変数を使用して、できる限りSQL文を統一
リレーショナルデータベースは、クエリをキャッシュに保存して再利用してくれるが、
SQL文に定数を直接記述してしまうと、定数値しか違わないのに、そのSQL文を別のものと解釈してしまうため、再利用できなくなる。
SELECT文でワイルドカード「＊」は使用せずに、ちゃんとカラム指定する
基本、ワイルドカードは「＊」を使わずに、必要なカラムだけをSELECTするようにする。
- クエリ実行時に実際のカラム名への変換処理がなくなり、その分オーバヘッドが減る。
- 取得カラムを絞ることで、結果セットに含めるデータ量が減り、メモリ消費の節約になる
- ソースの可読性が上がり、仕様変更にも強くなる
- 全カラム取得する場合でも、「＊」を項目名に置き換えておくと変換処理を省ける
ORDER BY句で列番号は使用せずに、ちゃんとカラム指定する
ORDER BY句では、ソートキーとして、実際のカラム名の代わりに列番号を指定できるが、
カラム番号を指定すると、実際のカラム名への変換処理が走るから遅くなる。
列番号を使う場面としては、
動的にSQLを生成するときなんかに重宝できる機能だが、パフォーマンス面とのトレードオフが発生。
テーブルにはエイリアス(別名)をつける
エイリアスってのは、テーブルの別名（あだな）のことで
それを指定すると、クエリの解析時に、どのカラムがどのテーブルに属するかという判定を省ける。
特に、複数のテーブルを扱うSQLで効果を発揮するが、単純なSQLを大量に発行するケースでも有効。
-- △Worse
SELECT id, price FROM table WHERE price > 100;
-- ◎Better
SELECT t.id, t.price FROM table t WHERE t.price > 100;
WHERE句を適切に使い、取得するデータを絞り込む
当たり前ですがw
全表スキャンさせないために、
なるべく早い段階でデータを絞り込んで、取得するデータ量を少なくするといい。
IN述語の引数リストには、最もヒットしそうなキーを左詰めにする
INは、左から右へ引数を評価していって、ヒットした時点でtrueを返し、それ以後の引数を見ないような処理になるので、
-- △Worse
SELECT * FROM profiles WHERE countries IN ('アメリカ', 'インド', '日本', '中国');
-- ◎Better
SELECT * FROM profiles WHERE countries IN ('中国', 'インド', 'アメリカ', '日本');
※人口順に並べてみましたw
ORDER BYやGROUP BY、HAVING句を必要以上に多用しない
ソートは、リソースをたくさん食う重い処理で、
データを全表スキャンして、メモリ上に一次的に展開し、大小比較をしたのちに並べ替えを行うので、メモリもCPUもたくさん必要。
余分にディスク領域を使ったり、ディスクI/Oが発生してしまうのでパフォーマンスに悪影響。
複数ソート条件を持ったクエリなんかの処理は苦手なので
使わずに済むならなるべく使わないように。
1つのカラムに複数の情報を入れない。
アンチパターンですが、
DBの操作がかなり煩雑になるかつパフォーマンスも出ないし、
データの整合性を保つのも大変になるので、やらない。
| key | value |
|---|---|
| 1 | aaa,bbb |
| 2 | ccc |
| 3 | bbb |
交差テーブルなんかを作って対処する。
| key | value |
|---|---|
| 1 | aaa |
| 1 | bbb |
| 2 | ccc |
| 3 | bbb |
カラムのデータ型の見直して、暗黙の型変換を回避する
カラムに設定されたデータ型と入力データの型が違う場合に
暗黙の了解的に型変換が行われるから、その分の時間がかかってしまう上に、インデックスまで使用不可になる。
カラムのデータ型に合ったデータを保存することが大事。
例えば、CHAR型で定義されたcolumn1に対しては、
-- △Worse
SELECT * FROM table WHERE column1 = 1;
-- ○Better
SELECT * FROM table WHERE column1 = CAST(1, AS CHAR(1));
-- ◎Much Better
SELECT * FROM table WHERE column1 = '1';
テーブル結合時には、なるべく行数を絞り込む
テーブル結合時には、結合する側のテーブルのレコード数をなるべく削ぎ落としてから、結合すると速度アップ。
適切にWHWRE句で絞り込みして、結合するテーブルを小さくしましょう。
ちなみに、正規化しすぎて、JOINしまくると遅くなる傾向があるみたいなので、注意。
UNION、INTERSECT、EXCEPT述語にはなるべくALLをつける
UNION（INTERSECT、EXCEPT）単体だと、二つの集合をマージする時に、重複行削除の処理が走るため時間がかかる。
重複を気にしなくていい場合や、または重複が発生しないことが事前に明らかな場合には、
ALLをつけて、UNION ALLで重複排除のソートを発生させずに、速度改善する。
※物理メモリによるソートが発生すると速度が極端に遅くなるらしい。（噂では論理メモリの100万倍遅くなるとのこと汗）
サブクエリ(副問合せ)はなるべく使わない
サブクエリを使用すると、仮テーブルが作成されてしまいその分データ量が増えてしまい、速度が遅くなる（重くなる）。
結合(JOIN)で解決するならなるべく使うべきではない。
IN述語で複数のキーを場合はまとめる
-- △Worse 検索が2度発生し冗長。
SELECT id, name, address
FROM t1
WHERE
name IN (SELECT name FROM t2 WHERE t1.id = t2.id) AND
address IN (SELECT address FROM t2 WHERE t1.id = t2.id);
-- ○Better 検索が１度でスッキリ
SELECT * FROM t1
WHERE id || name || address IN (SELECT id || name || address FROM t2);
-- ◎Much Better インデックスを利用できる
SELECT * FROM t1 WHERE (id,name,email) IN (SELECT id,name,email FROM t2);
サブクエリを引数に取る場合はIN述語よりEXISTS述語を使う
EXISTSの挙動としては、条件に合致するものを１行でも見つけたらそこで検索が終了するというもの。
一方で、INは基本的には、全表検索してしまうので、検索したい状況によって使い分けが必要。
ちなみに、NOT INからNOT EXISTSに代えることでパフォーマンス向上することもあるのでお試しを。
EXISTS述語のサブクエリ内では、「SELECT * 」を使う
EXISTS (SELECT * FROM …)
こう書くことで、
オプティマイザが、どの列を使うべきかの判定してくれて、
列にインデックスが張られていれば、実際のテーブルを走査する必要がなくなるので、速くなる。
DISTINCTの代りにEXISTSを使う
DISTINCTを使うと、重複排除のソートが走るので、重い処理になる。
EXISTSを使えば、ソートが発生しせずに、速くなる。
-- △Worse
SELECT DISTINCT t1.id, t1.name FROM t1, t2 WHERE t1.id = t2.id;
-- ◎Better
SELECT t1.id, t1.name FROM t1 WHERE EXISTS(SELECT * FROM t2 WHERE t1.id = t2.id);
▼インデックスをうまく活用して処理を速くする
インデックス（索引）は、文字通り、目次の役割がある。
検索のための索引集がindexなので、インデックスの有無で処理速度が大きく変わる。
前提知識として
インデックスデータには、rawデータ(加工されていないそのままの生データ)が入っているので、
インデックス参照する前に、インデックスをかけたい値を関数とか計算とかで変えてしまうと、
え、そもそも値がちがうやん知らんしってなって、
インデックスがそもそも効かなくなる。
インデックスカラムに対してSQL関数を使わない
例えば、CAST()とかFORMAT()、SUBSTR()などを指すが
理由としては、
上記の前提知識の通り、
インデックスカラムに演算を行ってしまうので、インデックスの中に存在する値と照合できずに、インデックスが効かない。
あくまでインデックスデータには、例えば、「column1」の値が入っていて、「SUBSTR(column1, 1, 1)」の結果の出力される値は入っていない。
WHERE句の左辺で、算術演算子や関数を使わない
検索条件の右側で式を使えばインデックスが活用できる。
-- Index効かない
SELECT name FROM employees WHERE salary - tax > 1000;
-- Index効く
SELECT name FROM employees WHERE salary > tax + 1000;
※salaryがindexとして。
等号と不等号の組み合わせは、等号のみインデックスが使われる
SELECT name FROM employees
WHERE position = ‘manager’ AND salary > 1000;
※position, salaryの順番の複合インデックスとして。
上記の場合、
「position = ‘manager’」にはインデックスが使われますが、
「salary > 1000」は全走査。
不等号同士の組み合わせでは，先に指定した条件だけにインデックスが使われます。つまり
SELECT name FROM employees
WHERE salary > 1000 AND tax > 100
※salary, taxの順番の複合インデックスとして。
上記の場合、
「salary > 1000」だけインデックスが使われ、
「tax > 100」では全走査（と同じになる※B-Tree的に）。
否定型は使わない
<> != NOT INなどは全表走査してしまうので、インデックスが使われない。
「前方一致」を使う。「後方一致」「中間一致」などのあいまい検索は避ける。
インデックスのツリー走査において、LIKEが有効なのは、最初のワイルドカードの前まで。
つまり、%indexは完全無効。ind%exはindまで有効。
フルにインデックスを有効活用したい場合は、
前方一致を使って、LIKE句の先頭に%を付けない。
どうしても後方一致検索でインデックスを活用したい場合は、
REVERSE関数と関数インデックスを組合せて力技でできるっちゃできる
あとは、AND条件などを使って、なるべく少量に絞り込んでから、後方一致やあいまい検索をすべし。
複数のフィールドに分割したり工夫して、少しでも前方・完全一致できる範囲を広げるといい。
ORはなるべくINに置き換る
一応ORでもインデックスは効くみたいだが、
各条件が抽出する件数が少ない（数％程度）場合じゃないと、あんまり効果的じゃないらしい。
INだとちゃんとインデックスが活用できるので◎。
どうしてもORを使いたいなら
ある程度レコードを絞り込んでから使うとか、ビットマップインデックスを張ったりするとか、工夫が必要。
GROUP BYとORDER BYではインデックスを適切に使う
インデックスを設定したカラムキーを指定すれば、ソート時に高速化が可能。
特に、ユニークインデックスの場合はソートをスキップできる。
例えば、
「column1, column2, column3」という順番で設定した複合インデックスがある場合、
GROUP BY column1や、GROUP BY column1, column2といった集約キーを指定することで、
高速なソートを実現。
※DBによっては、この利用はユニークインデックスの場合に限られることもある。
VIEW（ビュー）を濫用しない
ビューとは
テーブルの特定部分や複数テーブルを結合し、ユーザに必要な部分だけをあたかも一つのテーブルであるかのように見せるための、仮想的なテーブルの概念です。 ビューの実体はデータを持たないSQL文であり、CREATE TABLE文で作成する物理的なテーブルとは違う
引用：SQLで「ビュー作成」を行う
つまり、実際のテーブルを作成するのではなく、あくまで仮想的にテーブルっぽいものを作れるってことだが、
ビュー定義のクエリで集約を使っている場合
例えば、
- OLAP関数（RANK, DENSE_RANK, ROW_NUMBER 等）
- 集約関数（AVG, COUNT, SUM, MIN, MAX）
- 集合演算子（ UNION, INTERSECT, EXCEPT 等 ）
オプティマイザが頑張っても、非効率的なSQLが実行されてしまい、想定外の速度低下を招くことがある。
安易に複雑なビューを定義する前に、ちゃんと設計するとか、
DBMSの便利な機能、例えば、インデックスの作成可能なビューや、マテリアライズド・ビューなどの技術も使ったりして、
パフォーマンスチューニングするといい。
NULLを一般値に置き換えて使用しない
つまり、カラムにNOT NULL制約をつけて、本当はnullなのに、-1やunknownみたいな一般値を入れないということ。
理由は、インデックスデータの中に、NULLは存在しないので、そもそもNULLをデータとして持ってないが、
一般値を入れてしまうと、インデックスのデータ量が大きくなって重くなる。
NULL検索時にはIS NULLを使って検索をかけると一般値じゃなくても判定できる。
IS NULL、IS NOT NULL述語を単独では使わない
IS NULLとIS NOT NULLは全表走査が行われるから、
これらの条件を指定するときは、単独で指定はせずに、何らかの絞り込める条件を合わせて指定すると、
走査する量が減って、速くなる。
-- △Worse
WHERE column1 IS NULL
-- ◎Better
WHERE column1 IS NULL AND column2 = 10
極値関数「MAX」「MIN」でインデックスを利用する
SUMやAVGなどソート領域を使用する関数は、パフォーマンス悪化の原因になりやすいが、
その中で、この極値関数は、ソート処理の負荷を軽減できる
例えば、
column1にインデックスが張られているとしたら、
MAX(column1)、MIN(column1)は、インデックスが効くから処理が高速化される。
複合インデックスを「column1, column2, column3」という順番で張っていた場合に、
MAX(column1)は高速化される。
B-TREEインデックスは、
作成されるときにキー値によってソートされるからです。
つまり、実際のテーブルじゃなくて、インデックスを走査するだけで最大値、最小値を求めることができる。
行をカウントするときは、COUNT(*)よりもCOUNT(インデックスカラム)を使う
COUNT関数の引数にインデックスカラムにを指定すると高速化できる。
※オプティマイザーによってはCOUNT(*)でインデックスを判別して優先して使ってくれることも。
ジョインするときは、結合キーにインデックスが張られていればインデックス参照だけで済む
SELECT * FROM t1 lEFT JOIN t2 ON t1.index_column = t2.index_column;
比較演算子よりもBETWEENを使えう
BETWEENを使うとクエリがスッキリして可読性が上がるが、速度改善にも有効。
BETWEENを使うことで、
オプティマイザーは指定範囲のインデックスノードを1回の操作で比較できるが、
一方で、
比較演算子による二つの式で条件指定した場合、オプティマイザーはインデックスに複数回の操作をする必要がある。
-- △Worse
WHERE date >= '2021-02-01' AND date <= '2021-02-19'
-- ◎Better
WHERE date BETWEEN '2021-02-01' AND '2021-02-19'
複合インデックスの場合には、WHERE句でその順番通りに書くべし
順番を守ったほうがパフォーマンスを最大化できる。
例えば、
column1, column2, column3に対して複合インデックスが張られているとして、
その場合に、条件指定の順番を意識することが大切。
-- ×Worse: 順番通りじゃない
SELECT * FROM table WHERE column1 = 10 AND column3 = 1000 ;
SELECT * FROM table WHERE column2 = 100 AND column3 = 1000 ;
SELECT * FROM table WHERE column2 = 100 AND column1 = 10 ;
-- ◎Better: 順番通り
SELECT * FROM table WHERE column1 = 10 AND column2 = 100 AND column3 = 1000;
SELECT * FROM table WHERE column1 = 10 AND column2 = 100;
※DBによっては、オプティマイザーのおかげで、順番を変えてもインデックスが効く場合もあるが、オススメじゃない。
行ポインタによるアクセスが最速
※Oracle PostgreSQLが対象
行ポインタは、全テーブルでも必ず持っている擬似カラムであり、レコードの論理アドレスが格納される。
インデックスも、行ポインタを使用している。
行ポインタは、セッション終了時に変わる可能性があるが、
同一セッション中は不変で、特にOracleでは常に最速のアクセスが保証されているそう。
主に、自己結合や自己相関サブクエリの時に力を発揮するから使わない手はない。
※DB2、SQLServer、MySQLには、ポインタは実装されていないそう。
インデックスの張り方については、こちらご覧ください〜
EXPLAINを使って、スロークエリの原因調査ができる
EXPLAN SELECT * FROM table;
EXPLAINを使うと、クエリ内容の詳細を教えてくれるそう。
見るべき項目は６つで、その中でもtype、key、rows、Extraの4項目に注目。
| プロパティ | 説明 |
|---|---|
| type | ALL：テーブルフルスキャンという重い処理が実行されている。レコード数が膨大である場合は見直しが必要。 index：インデックスを使って高速アクセスされている。※レコード数が膨大である場合は見なおした方が良い。 |
| possible_keys | indexで使えるキーが無いとNULLに |
| key, key_len, ref | いずれもindexで使えるキーが無いとNULLに。 PRIMARY：主キーインデックスを使用 |
| rows | 少ないほうがベター。大きいほど負荷がかかる。 例：rows=500000, filterd=100.00=>対象データ件数は、500000件×100.00%で、50万件 |
| Extra | Using Temporary：一時テーブルが使用されている, Using Filesort：ファイルソートが使用されている。 index追加で見なおした方が良い。 Extra = NULL:一時テーブルもファイルソートも行っていない |
まとめ
速度改善の鬼と化しました。
SQLの呼吸。
以上です。
ありがとうございました。