MySQLでの実行計画と最適化による品質の向上
この記事では、MySQLでの実行計画についての基本と考慮すべきポイントを説明し、
業務を進めるにあたりなぜ実行計画が重要なのか、
どのような形で品質の向上につなげるのかを、理解してもらうことを目的としています。
SQLクエリの実行計画と最適化
実行計画の話をする前に、SQLが実行される仕組みについて説明します。
以下はDBがSQLを受 け取ってから、データを返すまでの流れです。
上の図を見てもらうとわかる通り、SQLが実行される前に、
クエリの最適化と実行計画の作成が行われています。
SQLを実行するにあたり、この実行計画が重要な役割を担っている
イメージがついたところで、実行計画とは何かを説明していきます。
実行計画とは
実行計画とは名前の通り、SQLを実行するための計画です。
もう少し深掘って説明すると、クライアントが発行したSQLを実行するにあたり、
どのようにアクセスし、どのようにデータを探して取得するかをオプティマイザが決定し、
まとめた計画書のようなものです。
実行計画には、どのようなテーブルを使うか、
どのような順番でテーブルを結合するかなどが記載されています。
より良い実行計画の作成を意識することで、クエリのパフォーマンス向上につながります。
オプティマイザとは
MySQLのオプティマイザは、与えられたSQLクエリに対して最も効率的な実行計画を生成する
データベースエンジンのコンポーネントです。
もっと簡単に言うと、速 くて楽にデータを取得するための最適な方法を考えてくれる人です。
オプティマイザは、テーブルの統計情報、インデックスの有効性、クエリの構造、
利用可能なシステムリソースなどを考慮して、複数の可能な実行パスの中から最適なものを選択します。
この過程により、クエリの実行時間が最小限に抑えられ、データベースの全体的なパフォーマンスが向上します。
ですが、オプティマイザはいつでも最適な実行計画を生成してくれるわけではありません。
そのため、まずはオプティマイザが生成した実行計画を確認し、思ってた実行計画と違う場合は、
こちらで手を加えてあげる必要があります。
クエリの最適化
オプティマイザが良い実行計画を作ってくれるために、クエリの最適化は必要不可欠です。
クエリの最適化とは、SQLクエリを効率的に実行するためのプロセスです。
これには、データ取得方法の改善、不要な処理の削減、
効率的なインデックスの使用、クエリの再構成などが含まれます。
最適化の目的は、クエリの実行時間を短縮し、データベースのリソース使用を最小限に抑えることです。
クエリの最適化は、適切なインデックスの作成、クエリの書き直し、不要な結合の削除、
集約操作の効率化などを通じて実現されます。
また、データベースの設定を調整すること でもパフォーマンスが向上することがあります。
ここまで、実行計画についての基本を説明してきました。
それでは実際にEXPLAINを使って実行計画を確認してみましょう。
実行計画の確認
今回使うテーブルは以下の通りです。
実行計画を確認するため、データは大量に入れておきます。
employees （従業員：100万件）
| id | name | department_id | hire_date | fired_date |
|---|---|---|---|---|
| 1 | 田中 太郎 | 1 | 2000-01-01 | null |
| 2 | 桜木 花道 | 1 | 2001-01-01 | 2001-01-17 |
departments （部署：20件）
| id | name | start_date |
|---|---|---|
| 1 | 人事部 | 2000-01-01 |
projects （プロジェクト：100件）
| id | name | start_date | end_date |
|---|---|---|---|
| 1 | 株式会社 マリンフォード | 2000-01-01 | 2000-01-31 |
| 2 | 有限会社 ウォールシーナ | 2000-01-01 | null |
employee_projects （従業員とプロジェクトの中間テーブル：100万件）
| id | employee_id | project_id |
|---|---|---|
| 1 | 14533 | 155 |
employee_idとproject_idは、employeesテーブルとprojectsテーブルのidの外部キーです。
EXPLAIN
MySQLでクエリの実行計画を確認するには、SQLクエリの先頭にEXPLAINを付けて実行します。
試しにemployeesテーブルから苗字が「田中」の従業員を取得するクエリを実行してみましょう。
EXPLAIN SELECT * FROM employees WHERE name LIKE '田中%';
この結果、以下のような実行計画が表示されます。
| id | select_type | table | partitions | type | possible_keys | key | fkey_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | employees | null | ALL | null | null | null | null | 1000000 | 100.00 | Using where |
| カラム名 | 意味 |
|---|---|
| id | クエリ内のテーブルの実行順序 |
| select_type | クエリの種類 |
| table | クエリ内のテーブル名 |
| partitions | クエリ内のテーブルのパーティション名 |
| type | クエリ内のテーブルのアクセス方法 |
| possible_keys | クエリ内のテーブルのインデックス |
| key | クエリ内のテーブルの実際に使用されたインデックス |
| key_len | クエリ内のテーブルのインデックスの長さ |
| ref | クエリ内のテーブルの参照先 |
| rows | クエリ内のテーブルのスキャンする行数 |
| filtered | クエリ内のテーブルのフィルタリングされた行数 |
| Extra | クエリ内のテーブルの追加情報 |
クエリの実行計画を確認する際のポイント
ここで注目すべきは、typeとkey、そしてrowsです。
typeはクエリ内のテーブルのアクセス方法を示しています。
このアクセス方法によって、クエリの実行速度が大きく変わってきます。
| type | 意味 | 速度 |
|---|---|---|
| ALL | 全ての行がスキャンされる | 超遅い |
| index | インデックスを使って特定の行を取得する | 遅い |
| range | 特定の範囲の行を取得する | 普通 |
| ref | ユニークインデックス値を使って行を取得する | 速い |
| eq_ref | 主キーもしくはユニークキーを使って、最大1行を取得する | 超速い |
| const/system | 0もしくは1行しか返さない | 超速い |
クエリのパフォーマンスを上げるためには、typeがALLにならないようにすることが重要です。
一番早いのは、typeがconstもしくはsystemになることですが、これはほとんどの場合、
主キーもしくはユニークキーを使って、最大1行を取得する場合になります。
constとeq_refの違いは、constは定数値を使って行を取得するのに対し、
eq_refはインデックスを使って行を取得するという点です。
次にkeyです。keyはクエリ内のテーブルの実際に使用されたインデックスを示しています。
ここがnullになっている場合は、インデックスが使用されていないので、
クエリのパフォーマンスが低下している可能性があります。
最後にrowsです。rowsはクエリ内のテーブルのスキャンする行数を示しています。
この数値が大きいほどクエリの実行に時間がかかっていると言えます。
まとめると、クエリの実行計画を確認する際は、typeがALLになっていないか、
keyがnullになっていないか、rowsが大きくなっていないかを確認すると良いでしょう。
今回の例では、typeがALLになっていいて、keyがnull、rowsが100万なので
クエリのパフォーマンスが低く、改善の余地があることがわかります。
ということで、まずはtypeがALLになるのを防ぐために、インデックスを利用してみましょう。
インデックス
インデックスとは、データベースのテーブルに対して作成されるデータ構造です。
簡単に言うと、本のもくじのようなものです。
もくじがあると、本の中身を探すのが簡単になるように、
インデックスがあると、データの検索が簡単になります。
インデックスの作り方
インデックスを作成するには、CREATE INDEX文を使います。
先ほどの例で、employeesテーブルのnameカラムにインデックスを作成してみます。
CREATE INDEX idx_employee_name ON employees (name);
そして、先ほどと同じクエリの実行計画を確認してみましょう。
EXPLAIN SELECT * FROM employees WHERE name LIKE '田中%';
| id | select_type | table | partitions | type | possible_keys | key | fkey_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | employees | null | range | idx_employee_name | idx_employee_name | 768 | null | 37826 | 100.00 | Using index condition |
keyがnullからidx_employee_nameに変わったことで、作ったインデックスが使用されていることがわかります。
これによりtypeがテーブル内フルスキャンのALLから範囲検索のrangeに変わり、
rowsが100万から37826に減りました。
スキャンした行数が減ったことで、クエリのパフォーマンスが向上したことがわかりますね。
インデックスの選択とパフォーマンスへの影響
インデックスを作れば、クエリのパフォーマンスが向上するということがわかりましたが、
どのカラムにインデックスを作成するかは重要です。
例えば、100件のデータがあるprojectsテーブルに対して、
プロジェクトの終了日が2010年1月1日以降のデータを取得するクエリの実行計画を確認してみます。
EXPLAIN SELECT * FROM projects where end_date > '2010-01-01';
| id | select_type | table | partitions | type | possible_keys | key | fkey_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | projects | null | ALL | null | null | null | null | 100 | 33.33 | Using where |
インデックスがないため、typeがALLになっていますね。
この結果に対し、end_dateにインデックスを作成し、再度クエリの実行計画を確認してみましょう。
CREATE INDEX idx_project_end_date ON projects (end_date);
EXPLAIN SELECT * FROM projects where end_date > '2010-01-01';
| id | select_type | table | partitions | type | possible_keys | key | fkey_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | projects | null | ALL | idx_project_end_date | null | null | null | 100 | 49.00 | Using where |
インデックスを作成したにも関わらず、typeがALLのままです。
これはデータの数が少なく、重複が多いためインデックスを使ってもクエリの実行が遅くなると
オプティマイザが判断したためです。
さらに、インデックスを作成するとデータの追加や更新、削除が遅くなるというデメリットもあります。
なぜなら、インデックスはデータの追加や更新、削除の際にも更新されるからです。
これらのことを踏まえて、インデックスはたくさん作れば良いというわけではなく、
どのカラムにインデックスを作成するかは慎重に選択する必要があります。
インデックスを作成するカラムを選択する際のポイントは、以下の通りです。
-
クエリで頻繁に使われるカラム
WHEREやJOIN、ORDER BYなどで頻繁に使われるカラムはインデックスによる効果が大きい。 -
keyがついてないカラム
idなどの主キーはインデックスが自動で作成されるので、インデックスを作成する必要はない。 -
データのパターンが多く、ばらつきがあるカラム
インデックスはデータの重複が多いほど効果が低い。
今回のケースでは、件数が少ないprojectsテーブルよりも、
件数が多いemployeesテーブルの方がインデックスを作成するメリットが大きく、
さらに名前での検索が多く行われる点を考慮すると、
ばらつきがあるemployees.nameにインデックスを作成するのが良いと言えるでしょう。
実行計画の最適化
クエリの書き直し
クエリの書き直しは、データベースのパフォーマンスを向上させるための重要なテクニックです。
書き直しによって、SQLクエリがより効率的に実行されるようになります。
以下に、クエリの書き直しのためのいくつかのアプローチを示します。
1. 不要な カラムの除去
- クエリの選択範囲を狭める
SELECT *の代わりに、必要なカラムだけを指定します。
これにより、データの取得量が減り、処理速度が向上します。
2. 効率的な結合の使用
- 適切な結合タイプの選択
INNER JOIN、LEFT JOIN、RIGHT JOINなど、クエリの目的に適した結合タイプを選択します。 - ON句の条件最適化
結合条件を最適化して、必要最小限のデータセットに絞り込みます。
3. WHERE句の条件を改善
- 条件の順序
最もデータを絞り込める条件を先に書くことで、検索効率を高めます。 - インデックスを活用する
インデックスが有効に機能するような条件を設定します。
4. サブクエリの代わりにJOINを使 用
- サブクエリの最適化
複雑なサブクエリは処理に時間がかかることがあるため、
可能な限りJOINに置き換えることでパフォーマンスを改善します。
5. 集約関数の効率的な使用
- GROUP BYとHAVINGの最適化
必要な集約だけを行い、不要なデータの集約を避けます。
6. インデックスを考慮したクエリ設計
- インデックスに適合するクエリ
インデックスが存在するカラムを活用し、クエリがインデックスを効率的に使えるようにします。
7. 不要なデータの削減
- LIMIT句の使用
特に大量のデータが存在する場合、必要な数のデータだけを取得するようにLIMIT句を使用します。
これらのアプローチを使って、クエリのパフォーマンスを向上させることができます。
実際にクエリの違いでパフォーマンスがどのように変わるかを見てみましょう。
まずは以下のクエリの実行計画を確認します。
emploeesテーブルから、プロジェクト名が「株式会社」から始まる
プロジェクトに携わっている従業員を取得します。
EXPLAIN
SELECT *
FROM employees
WHERE id IN (
SELECT employee_id
FROM employee_projects
WHERE project_id IN (
SELECT id
FROM projects
WHERE name LIKE '株式会社%'
)
);
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | ＜subquery2＞ | null | ALL | null | null | null | null | null | 100.00 | null |
| 1 | SIMPLE | employees | null | eq_ref | PRIMARY | PRIMARY | 4 | ＜subquery2＞.employee_id | 1 | 100.00 | null |
| 2 | MATERIALIZED | projects | null | ALL | PRIMARY | null | null | null | 100 | 11.11 | Using where |
| 2 | MATERIALIZED | employee_projects | null | ref | employee_id,project_id | project_id | 4 | test.projects.id | 10510 | 100.00 | null |
このクエリはサブクエリを使っているため、実行計画を見ると、
employee_projectsテーブルとprojectsテーブルがそれぞれ2回ずつスキャンされていることがわかります。
次に、サブクエリをJOINに置き換えたクエリの実行計画を確認します。
EEXPLAIN
SELECT *
FROM employees
INNER JOIN employee_projects ON employees.id = employee_projects.employee_id
INNER JOIN projects ON employee_projects.project_id = projects.id
WHERE projects.name LIKE '株式会社%';
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | projects | null | ALL | PRIMARY | null | null | null | 100 | 11.11 | Using where |
| 1 | SIMPLE | employee_projects | null | ref | employee_id,project_id | project_id | 4 | projects.id | 10510 | 100.00 | null |
| 1 | SIMPLE | employees | null | eq_ref | PRIMARY | PRIMARY | 4 | employee_projects.employee_id | 1 | 100.00 | null |
このクエリはサブクエリをJOINに置き換えたため、実行計画を見ると、
employee_projectsテーブルとprojectsテーブルがそれぞれ1回ずつスキャンされていることがわかります。
このように、サブクエリをJOINに置き換えることで、クエリの実行時間が大幅に短縮されました。
しかし、実行計画を見ると、projectsテーブルがそれぞれ全件スキャンされていることがわかります。
これは、projectsテーブルにインデックスがないためですが、今回はデータ数が100件しかないため、
インデックスを作成してもクエリの実行時間が短縮されることに期待はできません。
もし別で指定したいprojects.idを控えていれば、
WHERE句でidを指定することで、projectsテーブルのスキャンを回避でき、
さらにクエリの実行時間を短縮することができます。
EXPLAIN
SELECT *
FROM employees
INNER JOIN employee_projects ON employees.id = employee_projects.employee_id
INNER JOIN projects ON employee_projects.project_id = projects.id
WHERE projects.id IN (3, 4, 5, 6);
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | projects | null | range | PRIMARY | PRIMARY | 4 | null | 4 | 100.00 | Using where |
| 1 | SIMPLE | employee_projects | null | ref | employee_id,project_id | project_id | 4 | projects.id | 10510 | 100.00 | null |
| 1 | SIMPLE | employees | null | eq_ref | PRIMARY | PRIMARY | 4 | employee_projects.employee_id | 1 | 100.00 | null |
このように、クエリの書き方を工夫したり、インデックスを作成したりすることで、
クエリのパフォーマンスを向上させることができます。
まとめ
1. 自分が書いたSQLがどのような実行計画で実行されているかをEXPLAINを使って確認する
2. 実行計画を見て、typeがALLになっていないか、rowsが大きくなっていないかを確認する
3. イマイチな実行計画の場合は、まずはクエリの書き方を見直してみる
4. 必要に応じてインデックスの作成、インデックスの選択を行う
このようにして、日々の業務でも実行計画を確認し、
より良いSQLを書くことで、パフォーマンス改善に取り組み、品質の向上を目指していきましょう。