はじめに
達人に学ぶSQL徹底指南書を読んでいてクエリチューニングの章があったので、
この機会に本書および各種記事から学んだクエリのチューニング方法の中で
すぐに実践可能なものをまとめてみようと思います。
記載する内容は DBMS の種類やテーブルに格納されている
データによっても結果が変わってきますので、
必ずしもパフォーマンスが改善される訳ではないという点にご留意ください。
クエリチューニング方法
SELECT ではカラム名を指定する
SELECT * を使用すると不要なカラムまで取得してメモリを圧迫したり、
実際のカラム名への変換が発生するため、必要なカラム名のみを指定する。
テーブルにエイリアスを付ける
テーブルに別名を付けておくと、クエリ解析時にカラムがどのテーブルのものかを
判別する処理を省略可能。特に複数のテーブルを参照する場合に有効。
-- エイリアスを指定する
SELECT b.title, b.publication_date FROM Books AS b;
-- エイリアスを指定しない
SELECT title, publication_date FROM Books;
ORDER BY 句ではカラム名を指定する
ORDER BY 句に列番号を指定した場合、実際のカラム名への
読み替えが発生するため、カラム名を指定する。
-- カラム名を指定
SELECT * FROM Players ORDER BY uniform_number;
-- 列番号を指定
SELECT * FROM Players ORDER BY 2;
インデックスを使用する
パフォーマンス改善といえば真っ先に思いつくのがインデックスを張ること。
しかし、SQL の書き方によってはインデックスが使用されない場合がある。
具体的には以下のようなパターンではインデックスを使用できないので注意が必要。
- インデックスカラムで演算子を使用している
-- インデックスが使用される
SELECT * FROM Products WHERE price > 1500 / 1.1;
-- インデックスが使用されない
SELECT * FROM Products WHERE price * 1.1 > 1500;
- インデックスカラムに関数を使用している
-- インデックスが使用されない
SELECT * FROM Products WHERE CONCAT('A', category_id) = 'A01';
- 暗黙の型変換が行われる検索条件を記述している
-- インデックスが使用される
SELECT * FROM Users WHERE user_id = CAST(100, AS CHAR(3));
-- インデックスが使用されない
SELECT * FROM Users WHERE user_id = 100;
CAST で明示的に型を変換してやればインデックスを使用可能。
- 否定形の論理比較(
<>,!=,NOT INなど)を行っている
-- インデックスが使用されない
SELECT * FROM Employee WHERE employee_id NOT IN (12, 13, 14);
-
IS NULLで検索している
NULLは値ではないため、インデックスに含まれない。
したがって、IS NULLで検索した場合はインデックスが使用されない。
ただし、DBMS によってはインデックスを使用するものもある。
例えば MySQL5.6 の場合はインデックスを使用する。
MySQL は、col_name = constant_value に対して使用できる同じ最適化を
col_name IS NULL に対しても実行できます。たとえば、MySQL は、
インデックスと範囲を使用して、IS NULL を含む NULL を検索できます。
- 複合インデックスと異なる順序で取得条件を記述している
-- インデックスが使用される
SELECT * FROM Students WHERE student_id = 'A01' AND department_id = 'B01';
-- インデックスが使用される
SELECT * FROM Students WHERE student_id = 'A01';
-- インデックスが使用されない
SELECT * FROM Students WHERE department_id = 'B01' AND student_id = 'A01';
-- インデックスが使用されない
SELECT * FROM Students WHERE department_id = 'B01';
- 後方一致、中間一致の
LIKE検索を行なっている
前方一致の場合はインデックスを使用できるが、
後方一致、中間一致の場合は使用できない。
-- インデックスが使用される
SELECT * FROM Students WHERE student_id LIKE 'S0%';
-- インデックスが使用されない
SELECT * FROM Students WHERE student_id LIKE '%01';
-- インデックスが使用されない
SELECT * FROM Students WHERE student_id LIKE '%1%';
インデックスを張れば参照時の性能は向上するが、追加・更新時はオーバーヘッドになる。
したがって、カーディナリティの低い(取り得る値のバリエーションが少ない)カラムや
検索に使用しないカラムにまでインデックスを張らない。
ソートを避ける
ソートが発生する演算を行うとパフォーマンスが低下するため、なるべく避ける。
例えば、UNION は重複排除のためにソートを行うが UNION ALL は
重複を許容するためソートを行わない。よって、重複が発生しないことがわかっているか、
重複が発生しても問題ない場合は UNION ALL を使用する。
Department_A
| id | emp_id | name |
|---|---|---|
| 1 | 1 | 西園寺 |
| 2 | 3 | 財前 |
| 3 | 7 | 綾小路 |
Department_B
| id | emp_id | name |
|---|---|---|
| 1 | 4 | 伊集院 |
| 2 | 8 | 九条 |
| 3 | 9 | 京極 |
-- ソートが発生しない
SELECT emp_id, name FROM Department_A
UNION ALL
SELECT emp_id, name FROM Department_B;
-- ソートが発生する
SELECT emp_id, name FROM Department_A
UNION
SELECT emp_id, name FROM Department_B;
結果
| emp_id | name |
|---|---|
| 1 | 西園寺 |
| 3 | 財前 |
| 7 | 綾小路 |
| 4 | 伊集院 |
| 8 | 九条 |
| 9 | 京極 |
ソートが発生する代表的な演算
- GROUP BY 句
- ORDER BY 句
- 集約関数
- DISTINCT
- 集合演算子(UNION, INTERSECT, EXCEPT)
- ウィンドウ関数
IN より EXISTS を使用する
サブクエリを引数に取る場合、IN よりも EXISTS
の方がパフォーマンスが高くなることがある。
Library_A
| id | title |
|---|---|
| 1 | こころ |
| 2 | 人間失格 |
| 3 | 雪国 |
Library_B
| id | title |
|---|---|
| 1 | こころ |
| 2 | 人間失格 |
| 4 | 金閣寺 |
-- IN を使用する場合
SELECT *
FROM Library_A
WHERE id IN (SELECT id FROM Library_B);
-- EXISTS を使用する場合
SELECT *
FROM Library_A a
WHERE EXISTS
(SELECT *
FROM Library_B b
WHERE a.id = b.id);
結果
| id | title |
|---|---|
| 1 | こころ |
| 2 | 人間失格 |
上記のような場合には、以下の理由から EXISTS の方が
高パフォーマンスとなることが期待できる模様。
- 結合キーにインデックスが張られている場合、全件走査せずインデックスの参照のみで済む
INを使用する場合はサブクエリの結果のワークテーブルが作成される。
ワークテーブルにはインデックスが存在しないため、結果として全件走査することになる。
一方で、EXISTSの場合はワークテーブルが作成されないため
インデックスの使用が可能。(NOT IN,NOT EXISTSの場合も同様) -
EXISTSはヒットするレコードを見つけた時点で検索を止めるのでINのように全件走査しない
暗黙の型変換を避ける
「インデックスを使用する」にも記載している通り、暗黙の型変換が発生すると
インデックスを使用できなくなることに加え、型変換を行う分のオーバーヘッドを
発生させるため、カラム定義と一致した型で検索する。
IN の引数はマッチしやすい条件から順に記述する
IN による検索は左に記述した引数から順に判定し、ヒットした時点で
検索を止めるため、ヒットしやすいキーから順に記述する。
レコード数を絞り込んでから結合する
レコード数の多いテーブルを結合する場合、あらかじめ WHERE や
HAVING でレコード数を絞り込んだ上で結合する。
HAVING句 より WHERE句 を使用する
WHERE 句は GROUP BY 句より前に実行されるのに対して、HAVING 句は後に行われる。
グループ化の処理はソートが発生するため、事前にレコード数を絞り込んでおいた方が
パフォーマンスが上がる。また、WHERE の場合はインデックスを使用できる
可能性もあるため、WHERE を優先して使用する。
AND より BETWEEN を使用する
範囲の下限と上限を指定した条件を AND でつなぐ場合は範囲評価が2回発生するのに対して、
BETWEEN を使用する場合は1回で済むため、できる限り BETWEEN を使用する。
中間テーブルを削減する
サブクエリによって中間テーブルが作成されると、その分メモリを使用してしまう。
また、インデックスが使用されなくなることがあるためパフォーマンス低下の原因になる。
そのため、以下のようになるべく無駄な中間テーブルは省く。
- 集約よりも結合を先に行う
集約よりも結合を先に行うことで、中間テーブルを省略できることがある。
Departments
| id | name |
|---|---|
| 1 | 経理部 |
| 2 | 営業部 |
| 3 | 財務部 |
| 4 | 企画部 |
| 5 | 製造部 |
Employee
| id | dept_id | name | age |
|---|---|---|---|
| 1 | 3 | 山本 | 64 |
| 2 | 2 | 山田 | 22 |
| 3 | 3 | 山口 | 35 |
| 4 | 4 | 山崎 | 42 |
| 5 | 2 | 山下 | 31 |
| 6 | 1 | 山内 | 29 |
| 7 | 4 | 山中 | 55 |
| 8 | 3 | 山根 | 57 |
| 9 | 2 | 山川 | 35 |
| 10 | 1 | 山岸 | 26 |
| 11 | 1 | 山村 | 47 |
| 12 | 3 | 山岡 | 41 |
-- 中間テーブルを作成しない
SEELCT d.id, AVG(e.age) AS avg_age
FROM Departments AS d
LEFT OUTER JOIN Employees AS e
ON d.id = e.dept_id
GROUP BY d.id;
-- 中間テーブルを作成する
SELECT d.id, e.avg_age
FROM Departments AS d
LEFT OUTER JOIN
(SELECT dept_id, AVG(age) AS avg_age
FROM Employees
GROUP BY dept_id
) AS e
ON d.id = e.dept_id;
結果
| id | avg_age |
|---|---|
| 1 | 34.0000 |
| 2 | 29.3333 |
| 3 | 49.2500 |
| 4 | 48.5000 |
| 5 |
- 安易に VIEW を作成しない
複雑な VIEW はパフォーマンス低下を招く恐れがある。
特に集約関数や集合演算子を使用している場合、非効率となる可能性が高い。
まとめ
以上、簡単にできそうなクエリチューニング方法を列挙してみました。
中でもインデックスの使用についてはパフォーマンスに与える影響が大きいため、
上述のインデックスが使用されない書き方を避けるのに加え、
複雑なクエリを作成・修正する際には、フルテーブルスキャンをしている
箇所がないか、意図しない中間テーブルが作成されていないかなどを
EXPLAIN で確認しておくことが重要だと改めて感じました。