実行計画はSQL理解の大きな手がかり

　SQLはほかの手続き型言語と大幅に違います。そのため、ほかの手続き型言語と同じ感覚で使うと違和感が大きくなり、アレルギーのように嫌う人も出てきます。最近では、手続き型言語でORMを使うとSQLを意識しないでRDBMSをストレージのように扱うことが可能なため、SQLを理解する前にORMを使ってしまった人の中には、SQLをまったく理解していない人もいるようです。

　しかし、正しい理解をしないまま使うということは非常に危険ですので、SQLとはどういうものかということを実行計画を通して理解しましょう。

　実行計画はSQL文の前に“⁠EXPLAIN⁠”を付けて実行するか、Workbenchなどのツールを利用すると視覚的に確認することも可能です。本稿ではWorkbenchでの結果（画像）を用いて解説します。

実行計画が変わるSQL

　実行計画とは何かというと、実際に実行されるプログラムの概要を提示したものです。SQLを直接実行できるCPUは存在していませんから、DBエンジンの中のオプティマイザーという機能が、手続き型のソースに翻訳してからコンパイルして実行するという処理を行っています。

　さっそく、worldデータベースでリスト1、リスト2の実行計画を取って確認してみましょう。リスト1の実行計画は図1、リスト2の実行計画は図2になります。基本的に同じSQLなのに、実行計画は少し違っていることがわかるでしょうか？

SELECT * FROM city WHERE CountryCode > 'B'  -- 結果は図1

SELECT * FROM city WHERE CountryCode > 'Z'  -- 結果は図2

　図では大きな違いに見えないので、それぞれJavaのようなソースコードに直してみます。図1をソースコードに直すとリスト3になります。インデックスを使わずにテーブル全体（第1回の図4の（5）プライマリーキーのリーフ部分）を読みながらデータをフィルタリングする処理になっています。

for(Row row:city){
    if(row.CountryCode > "B"){
        retRows.add(row);
    }
}
return retRows;

　一方、図2をソースコードに直したリスト4では、インデックス（第1回の図4の（1⁠）⁠～（⁠4⁠）⁠）を利用してフィルタリングしているというように、アルゴリズムが大幅に変更されていることがわかります。

rowKeys = city.Indices.CountryCode.getRangeKeys("Z");
for(RowID rowID:rowKeys){
    retRows.add(city.getRow(rowID));
}
return retRows;

　ここで第1回の図4のデータ構造を思い出し、リスト3とリスト4でヒットする件数が逆になったとき、読み込まなければならないページ数をイメージしてみましょう。ヒット率が高い（全レコード数に対してヒットする件数が多い）とき、第1回の図4の（1⁠）⁠～（⁠5）の順でインデックスを読み取ってから実データを探す、ということを繰り返すことは効率的ではなく、最初からすべてのデータ（第1回の図4の（5）だけ）を読み取って、不要なデータを読み飛ばすほうが効率的です。逆にヒット率が低いときはインデックスを使ったほうが効率的になります。

　「⁠CountryCode > 'B'」の条件では、4,079件のうち3,972件ヒットし、「⁠CountryCode > 'Z'」の条件では、4,079件のうち57件ヒットします。いずれも、効率が良いと考えられるアルゴリズムが選択され構築されていることがわかります。

　cityテーブルの件数は、現在は4,079件ですが、1,000万件中5,000件がヒットするというような条件のときはインデックスを使います。ヒットするレコード数の絶対値が多い／少ないではなく、全レコード数に対する比率でアルゴリズムが変わるわけです。

　つまり、パラメータを含むSQLがまったく同じであっても、時間経過とともにデータ数とその分散具合が変われば、最終的に実行されるアルゴリズムが変わる可能性があるということです。逆に考えれば、インデックスがあるカラムに抽出条件を1つ加えたSELECT文を実行する場合、DBエンジンはSQL実行前に「どのぐらいデータがヒットするか」を予想しているということがわかります。DBエンジンはヒット率を予想するために事前に統計情報を作っています。SQLの実行時にその統計情報を用いて次のような複雑で大量の内部処理を行います。

DBエンジンがSQL実行前に行う内部処理

• SQL文のシンタックス（文法）チェック
• DBオブジェクト（テーブルなど）の存在チェック
• DBオブジェクトに対する権限チェック
• カラム、ファンクション、引数などの存在、型チェック
• パース処理（文法上の整理）
• テーブル・インデックスの統計情報から実行計画を作成
• マシン語に翻訳（コンパイル）

統計情報の更新

　MySQLでは統計情報の更新は、次のような基準で自動で行われます。

• 作りなおすタイミングはインデックスが全体の数％書き換わったとき
• ランダムにインデックスの数ページを選んで統計情報を作成する

　MySQLの統計情報の更新方法は、Oracleなどに比べるとかなりアバウトです。たとえばOracleでは、統計情報を作ることがたいへん重い処理になるため夜間バッチなどで行っても問題になることがあるぐらいですが、その分実行計画はかなり精緻なものになります。

　逆に、MySQLでは統計情報の更新が高速である分、非常にアバウトになるため、同じデータでもインデックスを使うか使わないかの閾値はかなり変化します。気になる方は、次の方法で実験すればどのぐらいアバウトか体感できます。

1. リスト2の条件'Z'を、Mから順にM、N、O、P、Q……と、変化させてインデックスを使うようになるところを探す
2. 第1回で紹介した「https://dev.mysql.com/doc/index-other.html」からダウンロードした「world.sql」のSQL文を流して、worldデータベースを作りなおす
3. 再度、M～順に変化させてインデックスを使うようになるところを探す

　本稿を書くために筆者が行った複数回の実験では、O～Vの範囲で変化しました。

実行計画が変わらないSQL

　worldデータベースで、リスト5、リスト6の実行計画を取るとそれぞれ図3、図4になります。

SELECT *
FROM city
    INNER JOIN country
        ON city.CountryCode = country.Code;

SELECT *
FROM country
    INNER JOIN city
        ON country.Code = city.CountryCode;

　JOINする順番というのは、SQLとしては大幅にロジックが違うように感じるかもしれませんが、cityからJOINしても、countryからJOINしても、実行計画は同じになっています。もちろん結果も基本的に同じです（⁠「⁠select *」とするとカラムの順番は変わります⁠）⁠。

　これまで見てきたとおり、オプティマイザーは単純にSQL文を変換しているのではなく、SQL文を解析して、テーブルの件数、使えるインデックス、カーディナリティ^注1などから検討し、どのテーブルからJOINしていくか順番まで決めている。つまり、アルゴリズムを最適化（オプティマイズ）しているため、使われているテーブルと結合条件が同じであれば同じ実行計画になるわけです。

オプティマイザーはプログラマ、SQLは詳細設計書

　オプティマイザーはプログラマにあたり、SQLは詳細設計書にあたります。オプティマイザーの性格はRDBMSごとに違い、それぞれのバージョンでもまったく違ってきます。RDBMSごとの性格の違いから、向いているシステム、向いていないシステムがあります。RDBMSを選択するときも、本来はそれぞれのオプティマイザーの性格まで含めて検討するべきでしょう。

　また、オプティマイザー（プログラマ）は、当然、人間のプログラマにはおよびませんから、それぞれのオプティマイザーに合わせたSQLを書き、必ずレビュー、つまり、実行計画を確認する必要があります。注意する点は、「⁠データ量が変わると実行計画が変わってしまうため、できる限り本番環境に近い環境で行う」ということです。