クエリのパフォーマンスを決める実行計画を見てみよう

さて、こうして実行計画が作られると、DBMSはそれをもとにしてデータアクセスを行います。しかし、データ量の多いテーブルへアクセスしたり、複雑なクエリを実行する場合、クエリのレスポンスが遅いことがよくあります。この理由には、先述のように統計情報が不正確であるという原因もありますが、それ以外にも、そもそも現状で最適なアクセスパスが選択されているのに遅い、ということもあります。また、統計情報は最新でも、クエリが複雑過ぎてオプティマイザが最適なアクセスパスを作成できないこともあります。

こうした理由からクエリの遅延が発生したとき、最初に調べるべき対象が実行計画です。DBMSはいずれも、実行計画を調べる手段を提供しています。これから、次の3つの実行計画を見ていきましょう。

• シーケンシャルアクセスの実行計画
• ランダムアクセスの実行計画
• 結合の実行計画

シーケンシャルアクセスの実行計画

実行計画を確認する手段は実装によって違いますが、多くのDBMSがコマンドラインのインタフェースから確認するためのコマンドを用意しています（表2⁠）⁠。

具体的に、図2のようなサンプルテーブルを使うことにしましょう。ある業種の店舗についての評価や所在地域のデータを保存するテーブルだと考えてください。主キーは店舗IDで、テーブルには60行のデータを入れ、そのあとに統計情報を取得済みだとします。

まずは、レコードを全件取得するクエリの実行計画を見てみましょう。

SELECT *
  FROM Shops;

Oracle、PostgreSQL、MySQLの3つのDBMSで取得した実行計画を掲載します（リスト1～3⁠）⁠。

実行計画
----------------------------------------------------------
Plan hash value: 2761254732
               ②          ①      ③
------------------------------------------------------------------------
| Id | Operation        | Name  | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------
| 0  | SELECT STATEMENT |       | 60   | 1260  |      3  (0)| 00:00:01 |
| 1  | TABLE ACCESS FULL| SHOPS | 60   | 1260  |      3  (0)| 00:00:01 |
------------------------------------------------------------------------

              QUERY PLAN
-------------------------------------------------------
Seq Scan on shops (cost=0.00..1.60 rows=60 width=22)
   ②        ①                      ③

           ②         ①      ②                                            ③
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | re f | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
| 1  | SIMPLE      | Shops | ALL  | NULL          | NULL | NULL    | NULL | 60   | 100.00   |       |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+

出力される情報のフォーマットは完全に同じではありませんが、すべてのDBMSに共通する出力項目があります。それは、「⁠① 対象のオブジェクト」「⁠② オブジェクトの操作」「⁠③ 操作の対象となるレコード数」です。この3つは、それだけ重要な項目だということです。

① 対象オブジェクト

今、対象オブジェクトについて見ると、Oracleは「Name」列、PostgreSQL はon のあと、MySQLは「table」列に、Shopsテーブルが出力されています。今はこの1つしかテーブルを使用していないため迷うことはありませんが、複数のテーブルを結合する場合、どのオブジェクトに対する操作なのかを混同しないように注意してください。

また、この項目にはテーブル以外にも、インデックスやパーティション、シーケンスなど、SQL文でアクセス対象となるオブジェクトなら何でも現れます。

② オブジェクトの操作

オブジェクトに対する操作は、実行計画で最も重要な項目です。Oracleの「Operation⁠」⁠、PostgreSQLの文頭、MySQLの「select_type」および「type」列からわかります。今、3つのDBMSは同じアクセス操作、すなわちシーケンシャルスキャンを実施しています。PostgreSQLのSeq Scanは「シーケンシャルスキャン」の略です。OracleとMySQLの出力情報は、「⁠テーブルのデータを全部読み込みます」ということしか言っていないため、正確にはこれをシーケンシャルスキャンと同義と取るのは、レイヤを混同しています[4]⁠。PostgreSQLとは、出力レベルが違うということです。しかし実際のところ、テーブルへのフルアクセスにはほとんどの場合、シーケンシャルスキャンが実施されます。

DBMSはランダムスキャンも選択肢として持ちますが、このサンプルではテーブルのレコード数が少なく、またすべてのレコードを選択するクエリを実行していることから、シーケンシャルスキャンが選択されています。

③ 操作の対象となるレコード数

3つ目の重要な項目は、操作の対象となるレコード数です。これらはいずれも「Rows」の項目に出力されるため、わかりやすいでしょう。結合や集約が入ってくると、1つのクエリを実行するためだけでも、複数の操作が行われます。そうすると、各操作でどれだけのレコードが処理されるかが、クエリ全体の実行コストを把握するために重要になります[5]⁠。

レコード数はテーブルの実件数と一致するとは限らない

なお、この件数について1つ誤解してほしくないことがあります。それは、この数値が取得されている情報源についてです。先ほど「クエリが処理される流れ」でオプティマイザが実行計画を作る過程を解説した際にも説明したように、オプティマイザはテーブル情報をカタログマネージャから受け取ります。つまり、ここに出ている件数は、統計情報として取得された値がもとになっています。けっして、いま現時点のテーブルの実件数を示すものではないのです。

ためしに、このShopsテーブルから全レコードを削除して（もちろんコミットもして⁠）⁠、それから再度実行計画を取得したらどうなるでしょう。実際にやっていただければわかりますが、3つのDBMSすべてについて、やはりRowsの項目には「60」という値が出力されます。オプティマイザは、あくまで統計というメタ情報を頼りにしていて、実テーブルなんか見ていないのです（注6⁠）⁠。

ランダムアクセスの実行計画

先ほど実行した簡単なクエリにWHERE条件を付けます。

SELECT *
  FROM Shops
  WHERE shop_id = ‘00050’;

再度実行計画を見てみましょう（リスト4～6⁠）⁠。

実行計画
----------------------------------------------------------
Plan hash value: 1968021522
                  ②                     ①        ③
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    1 |    21 |       1 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SHOPS       |    1 |    21 |       1 (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C004155 |    1 |       |       0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("SHOP_ID"='00050')

                    QUERY PLAN
------------------------------------------------------
   ②      ①                        ③
Seq Scan on shops (cost=0.00..1.75 rows=1 width=22)
  Filter: (shop_id = '00050'::bpchar)

           ②         ①      ②                                                   ③
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1  | SIMPLE      | Shops | const | PRIMARY       | PRIMARY | 5       | const |    1 |   100.00 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+

3つの結果には、おもしろい変化が見られます。先ほどと同様、3つの項目に注目していきましょう。

操作の対象となるレコード数

まず、いずれのDBMSも、Rows（リスト4～6③）が1になっています。主キーで「00050」の店舗を指定しているわけですから、結果が必ず1行になるためです。これは当然の変化です。

対象オブジェクトと操作

オブジェクト（リスト4～6①）と操作（リスト4～6②）についてはどうでしょう。PostgreSQLには、特段の変化はありません。Shopsに対するシーケンシャルスキャンです。他方、OracleとMySQLには変化が見られます。一言でいうと、両者ともインデックススキャンが行われるようになったのです。

Oracleでは、TABLE ACCESS FULLの代わりにTABLE ACCESS BY INDEX ROWIDと表示され（リスト4②⁠）⁠、さらにその内訳としてId=2の行にINDEX UNIQUE SCAN、対象オブジェクトにSYS_C004155という見たことのないオブジェクトが出ています（リスト4①⁠）⁠。このSYS_C004155は主キーのインデックスの名前です。テーブル作成時に特に指定しなかったため、適当な名称が自動的に付けられています。

MySQLのほうは、key列にPRIMARYが現れています。これも、主キーのインデックスが使用されたことを意味しています。

インデックススキャン

インデックスについては本稿では解説する紙幅がないため詳細は略しますが[7]⁠、一般的に、スキャンする母集合のレコード数に対して、選択されるレコード数が非常に少ない場合に高速なアクセスを実現します。

シーケンシャルスキャンがデータ量に正比例的に処理コストが増大するのに対し、インデックスの中で一般的なB-treeインデックスの場合、データ量に対数関数的に増大します。これは、インデックスのほうが処理コストの増大のしかたが緩やかだということを意味するので、あるデータ量（N）を損益分岐点として、インデックススキャンのほうが効率的にアクセスできるのです（図3⁠）⁠。

今は60行しかデータが存在しないので、テーブルを順次読み込むのもインデックスでランダムアクセスするのも、レスポンスは大差ありませんが[8]⁠、この差はレコード数が増えたときに効いてきます。