はじめに
現職でパフォーマンスチューニングの必要な箇所があり、それに備え、改めてSQL処理の流れについて学び直してみたいと思い、記事を作成しました。
本題
以下のようにユーザがSQLを発行して、結果を取得するまでの流れとなります。
- ユーザやWEBアプリケーションなどがSQLを発行する
- パーサがSQLの構文解析
- オプティマイザが実行計画を作成
- ストレージエンジンを通じたSQL実行の流れ
1. ユーザがSQLを発行する
ユーザが直接SQLをDBMSに発行すること、WEBアプリケーションでユーザが画面操作によって間接的にSQLがDBMSに発行されます。
2. パーサがSQLの構文解析
パーサはSQLの構文解析を行います。SQLの要素をバラバラにして、抽象構文木(AST)を生成します。
SQL文に文法的な誤りがないかをチェックして、適正なSQLをDBMSに渡すためです。例えばSQLの中にFROM句の記載漏れがないかをチェックして、もし誤りがある場合はユーザ側に教えてくれる。
また、SQLを抽象構文木にすることで、DBMS内部の後続処理で効率的に実行しやすいようにするためです。
3. オプティマイザが実行計画を作成
オプティマイザはDBMSの頭脳であり、司令塔的な役割を果たします。データを取得するための最適なアクセスパスである実行計画を作成します。
実行計画を作成する際に統計情報を元に作成します。統計情報とはテーブルのレコード数、カーディナリティ、インデックス情報などがあります。
注意点として、SQLのパフォーマンスが遅い原因として統計情報が古いと適切な実行計画をオプティマイザは作成できないため、統計情報の更新が必要になります。MySQLだとANALYZE TABLE テーブル名コマンドを実行することで最新の統計情報を取得できます。
実行計画の確認方法はDBMSの種類によって、異なりますが、MySQLだと以下のようにして確認できます。
mysql> EXPLAIN SELECT * FROM salaries WHERE salary = 74333;
+----+-------------+----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | salaries | NULL | ref | idx_salaries_salary | idx_salaries_salary | 4 | const | 41 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
typeはデータのアクセスパスを表しています。テーブルをフルスキャンで実行しているのか、プライマリーインデックスやセカンダリーインデックスを使用してデータを取得しているのかを把握できます。
keyはどのインデックスが使用されたのかを知ることができます。インデックスを作成する際に名前を指定するため、その名前が表示されます。例えば、以下のようにインデックスを作成しました。
CREATE INDEX idx_salaries_salary on salaries(salary);
rowsは統計情報をもとに算出された行数です。この行数が多いほどストレージエンジン（後ほど詳細を記載します）への負荷が大きくなります。
4. ストレージエンジンを通じたSQL実行の流れ
SQL文の実行にあたり、ストレージエンジン（MySQLの場合、デフォルトInnoDB）を通じてデータの読み書きが「メモリ」と「ストレージ」に対して行われます。
メモリは主に以下に対してデータの読み書きが行われます。
-
データキャッシュ
ストレージから読み込んだデータを一時的に保存する領域。都度ストレージにアクセスするよりも高速にデータを取得できます。 -
ログバッファ
INSERT・UPDATE・DELETE などの更新操作は、まずこの領域に記録され、非同期的にストレージに反映されます。
| 項目 | メモリ | ストレージ |
|---|---|---|
| 読み書き速度 | 高速 | 低速 |
| 永続性 | 再起動で消える | 永続 |
| コスト | 高い | 安い |
このような特性を活かし、頻繁に使用されるデータはメモリ上にキャッシュし、更新処理は一度ログバッファに保持してからストレージへ反映することで、パフォーマンスを確保しています。