はじめに
この記事はプログラミング初学者による備忘録用の記事であり、また、少しでも他の初学者のお役に立てればと思い書いています。
今回は、MySQL8.0のパフォーマンスチューニングについて調べた際に、クエリに関する最適化方法をいくつか見つけたので、いつでも自分で見返せるようにまとめておきたいと思います。(新しい情報を見つけ次第、随時更新します)
間違いなどがございましたら、ご指摘のほどよろしくお願い致します。
クエリに関する最適化
SELECT *を使わない
SELECT *すると不要な列まで取得したり、全列を取得したい場合でも*を項目名に置き換える処理が内部で行われるため、その分時間がかかってしまいます。
従って、取得する列はすべて明示的に選択してあげるべきです。
暗黙の型変換を避ける
MySQLでは、式評価での型変換が行われます。
演算子が別の型のオペランドとともに使用されると、オペランドの互換性を保つために型変換が発生します。 一部の変換は暗黙的に発生します。 たとえば、MySQL は必要に応じて文字列を数値に自動的に変換し、その逆も行います。
引用MySQL8.0 式評価での型変換
暗黙の型変換では、プログラマは型の変換内容を明記しないため、RDBごとに持つ型変換ルールに従って型が決定されます。
暗黙の型変換の危険性
・検索時にインデックスが使用できずにパフォーマンスが低下する
・暗黙の型変換により精度が損なわれる
・暗黙の型変換により、型の範囲外になるというエラーが発生する
・DBのバージョンアップにより暗黙の型変換の仕様が変わる
引用:SQLの暗黙の型変換はワナがいっぱい
上記の危険性を見て分かるとおり、暗黙の型変換には様々な予期しない動作が起きる可能性があります。
従って、暗黙の型変換を避け、型変換が必要な場合は、CASTによる明示的な型変換を指定すべきです。
LIKEの後方一致検索はなるべく使わない
インデックスのスキャンにおいてLIKEが有効なのは、ワイルドカードの前までということを理解しておく必要があります。
LIKE '%テスト'のように、%が前方につく「後方一致検索」の場合、インデックスは無効となり非常に重たいクエリになってしまいます。
~NG例~
SELECT *
FROM samples
WHERE name LIKE '%テスト';
WHERE句の条件指定順を見直す
WHERE句で複数条件を指定する場合、記述した順番に処理が実施されていきます。
その為、where句で絞り込んだ際のデータ結果が少なくなる順に条件を指定した方が、後述の条件に掛かる負担が少なくなります。
~例~
members table
+------+-----------------+------+------+
| id | name | age |gender|
+------+-----------------+------+------+
|1 | 鈴木一郎 | 30 | 男 |
|2 | 山田哲子 | 25 | 女 |
|3 | 大山悠輔 | 20 | 男 |
|4 | 田中正義 | 40 | 男 |
|5 | 西野奈緒 | 19 | 女 |
+------+-----------------+------+------+
--性別を先に指定
SELECT id, name
FROM members
WHERE gender = '男'
AND age = 30;
--上記のように、絞り込むことができるデータの数が2つしかないgenderカラムを先に指定しても、
--男か女でしかデータを分けることができない為、どちらかに属するデータが残ってしまします。
--年齢を先に指定
SELECT id, name
FROM テーブル名
WHERE age = 30
AND gender = '男';
--年齢を先に指定することで、性別指定でデータを取得するよりも取得データ数が少なくなることが分かると思います。
ASを使いテーブルに別名を付ける
カラム名の取得元テーブルをデータベースに判断させると処理が遅くなるので、複数テーブルを使用する場合、ASを使用してテーブル名に別名を付けた上で、全てのカラム名に接頭辞を付けます。
~例~
SELECT id, name, user_id, age
FROM members AS m
INNER JOIN infos AS i ON m.id = i.user_id
WHERE age >= 20;
範囲検索でBETWEENを使う
範囲検索において、必ずBETWEENを使うということではありませんが、下記のような例の場合、BETWEEN演算子を用いたほうが高速化できます。
~注意~
betweenは、指定範囲の境界値を含む「以上、以下」の指定のみ可能です。
※NOT INを組み合わせることで「未満」「より大きい」指定が可能となります。
~例~
SELECT name
FROM members
WHERE 20 <= age and age <= 40;
--上記のように、where句を使いandで繋げた範囲検索のSQLよりも、BETWEEN演算子を用いたほうが高速化できます。
SELECT name
FROM members
WHERE age BETWEEN 20 and 40;
--未満、超過の場合
SELECT name
FROM members
WHERE age BETWEEN 20 and 40
AND age NOT IN(20,40);
HAVINGとWHEREの使い分け
WHEREとHAVINGは同じように使えて、結果も同じです。
※実際のHAVING句の使い方とは異なります。(詳細は後述します)
しかし、実行計画をEXPLAINで確認してみると違いが分かります。
--HAVINGの場合
mysql> EXPLAIN SELECT * FROM tests HAVING id > 1;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tests | NULL | ALL | NULL | NULL | NULL | NULL | 19 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
--WHEREの場合
mysql> EXPLAIN SELECT * FROM articles WHERE id > 1;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tests | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 22 | 100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
上記の結果から
WHERE句を使用したSQLは、インデックスを使用して実行（key=PRIMARY）し、HAVING句を使用したSQLは、インデックス不使用（key=NULL）のテーブルフルスキャン（type=ALL）となっていることがわかります。
従って、HAVINGを使用する際は、パフォーマンス低下に繋がる可能性があります。
~正しいHAVING句の使用例~
HAVING句は、countなどの集約関数とGROUP BY句の組み合わせ等によるグループ化が行われたデータに対して条件を指定してデータを絞り込む場合に使用します。
要するに下記2点を押さえておくべきです。
・「HAVING」でグループ化したデータに対して検索条件を指定することができる
・「GROUP BY」と組み合わせて使う
~例~
SELECT カラム名 FROM テーブル名 GROUP BY カラム名 HAVING 検索条件;
IN句の記述方法
IN句の引数リストでは、最も存在する確率が高いキーを左寄せにして記述します。
場合によりますが、高速化に繋がる可能性があります。
--遅い例
SELECT id, prefecture, address FROM populations WHERE address IN ('島根', '岐阜', '東京', '大阪');
--高速化に繋がるかもしれない例
SELECT id, prefecture, address FROM populations WHERE address IN ('東京', '大阪', '岐阜', '島根');
IN演算子とEXISTS演算子の使い分け
IN演算子とEXISTS演算子に関する使い方や処理速度等で、様々な意見を見かけますが、私は下記のように使い分けています。(間違っている場合はご指摘くださると嬉しいです)
IN演算子が適しているケース
サブクエリ内の複数の絞り込み条件等と主問い合わせで射影された列を比較する場合
IN句を用いることで、サブクエリ内の条件指定によって返されるすべての値のうち、どれか1つと一致するレコードを全て抽出することができます。
~例~
--単数列、単数行との比較
sample IN ('データ1') -- = による比較と同義
--単数列、複数行との比較
sample IN ('データ1','データ2') --1列2行の選択が行われるサブクエリ
--複数列、複数行との比較
(sampleA ,sampleB) IN (('データ1','データ2'), ('データ3','データ4')) --2行2列
EXISTS演算子が適しているケース
サブクエリで主問い合わせの列を参照する場合
EXISTS演算子は、サブクエリから主問い合わせにアクセスできるというのが非常に大きな特徴であり、サブクエリ内で主問い合わせで射影された列を参照する場合に利用することが多いと思います。
このサブクエリで主問い合わせの列を参照することを相関副問い合わせと言います。
また、EXISTS演算子は単なる比較ではなく、副問い合わせの結果が1行以上あるかないか、要するに、存在するならTrue存在しないならFalseを返すといった判定をします。
~例~
users table
+------+-----------------+-------------+
| id | name | friend_id |
+------+-----------------+-------------+
|1 | 鈴木一郎 | 3 |
|2 | 山田哲子 | 3 |
|3 | 大山修斗 | 2 |
|4 | 田中正義 | 5 |
|5 | 西野奈緒 | 4 |
|6 | 柴内梨沙 | 1 |
+------+-----------------+------+------+
SELECT id
FROM users AS u1
WHERE EXISTS (SELECT * FROM users u2 WHERE u1.id = u2.friend_id);
--結果 主問い合わせの列(id)として取得されるのは下記となる
id = 1,2,3,4,5