クエリのパフォーマンスを決める実行計画を見てみよう
さて、こうして実行計画が作られると、DBMSはそれをもとにしてデータアクセスを行います。しかし、データ量の多いテーブルへアクセスしたり、複雑なクエリを実行する場合、クエリのレスポンスが遅いことがよくあります。この理由には、先述のように統計情報が不正確であるという原因もありますが、それ以外にも、そもそも現状で最適なアクセスパスが選択されているのに遅い、ということもあります。また、統計情報は最新でも、クエリが複雑過ぎてオプティマイザが最適なアクセスパスを作成できないこともあります。
こうした理由からクエリの遅延が発生したとき、最初に調べるべき対象が実行計画です。DBMSはいずれも、実行計画を調べる手段を提供しています。これから、次の3つの実行計画を見ていきましょう。
シーケンシャルアクセスの実行計画
ランダムアクセスの実行計画
結合の実行計画
シーケンシャルアクセスの実行計画
実行計画を確認する手段は実装によって違いますが、多くのDBMSがコマンドラインのインタフェースから確認するためのコマンドを用意しています(表2 ) 。
表2 実行計画を確認するコマンド
DBMS 実行計画取得コマンド
Oracle(※ ) set autotrace traceonly
SQL Server(※ ) SET SHOWPLAN_TEXT ON
DB2 EXPLAIN ALL WITH SNAPSHOT FOR <SQL文>
PostgreSQL EXPLAIN <SQL文>
MySQL EXPLAIN EXTENDED <SQL文>
※ OracleおよびSQL Serverでは、上記コマンドのあとに対象のクエリを実行する
具体的に、図2 のようなサンプルテーブルを使うことにしましょう。ある業種の店舗についての評価や所在地域のデータを保存するテーブルだと考えてください。主キーは店舗IDで、テーブルには60行のデータを入れ、そのあとに統計情報を取得済みだとします。
図2 店舗テーブル
Shops(店舗)
shop_id(店舗ID) shop_name(店舗名) rating(評価) area(地域)
00001 ○○商店 3 北海道
00002 △△商店 5 青森県
00003 ××商店 4 岩手県
00004 □□商店 5 宮城県
: :
00060 ☆☆商店 1 東京都
まずは、レコードを全件取得するクエリの実行計画を見てみましょう。
SELECT *
FROM Shops;
Oracle、PostgreSQL、MySQLの3つのDBMSで取得した実行計画を掲載します(リスト1~3 ) 。
リスト1 シーケンシャルアクセスの実行計画(Oracle)
実行計画
----------------------------------------------------------
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 |
------------------------------------------------------------------------
リスト2 シーケンシャルアクセスの実行計画(PostgreSQL)
QUERY PLAN
-------------------------------------------------------
Seq Scan on shops (cost=0.00..1.60 rows=60 width=22)
リスト3 シーケンシャルアクセスの実行計画(MySQL)
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
| 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 実行計画で重要な項目は、オブジェクト、操作、レコード数の3つ
レコード数はテーブルの実件数と一致するとは限らない
なお、この件数について1つ誤解してほしくないことがあります。それは、この数値が取得されている情報源についてです。先ほど「クエリが処理される流れ」でオプティマイザが実行計画を作る過程を解説した際にも説明したように、オプティマイザはテーブル情報をカタログマネージャから受け取ります。つまり、ここに出ている件数は、統計情報として取得された値がもとになっています。けっして、いま現時点のテーブルの実件数を示すものではない のです。
ためしに、このShopsテーブルから全レコードを削除して(もちろんコミットもして) 、それから再度実行計画を取得したらどうなるでしょう。実際にやっていただければわかりますが、3つのDBMSすべてについて、やはりRowsの項目には「60」という値が出力されます。オプティマイザは、あくまで統計というメタ情報を頼りにしていて、実テーブルなんか見ていないのです(注6 ) 。
[5] これに比べて、OracleやPostgreSQLの出力に含まれる実行コスト(cost)という指標は、あくまで筆者の経験上ですが、あまり役に立ちません。一見するとこの数値を減らすことが良いことのように思えますし、それも間違いではないのですが、どういうアルゴリズムで計算されているか不明なうえ、値の大小についても絶対評価ができないからです(たとえば「このクエリはcostが5,000しかないので1秒以内で終わるでしょう」という予測はできない) 。
ランダムアクセスの実行計画
先ほど実行した簡単なクエリにWHERE条件を付けます。
SELECT *
FROM Shops
WHERE shop_id = ‘00050’;
再度実行計画を見てみましょう(リスト4~6 ) 。
リスト4 ランダムアクセスの実行計画(Oracle)
実行計画
----------------------------------------------------------
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')
リスト5 ランダムアクセスの実行計画(PostgreSQL)
QUERY PLAN
------------------------------------------------------
Seq Scan on shops (cost=0.00..1.75 rows=1 width=22)
Filter: (shop_id = '00050'::bpchar)
リスト6 ランダムアクセスの実行計画(MySQL)
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
| 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 ) 。
図3 インデックススキャンとシーケンシャルスキャン
今は60行しかデータが存在しないので、テーブルを順次読み込むのもインデックスでランダムアクセスするのも、レスポンスは大差ありませんが[8] 、この差はレコード数が増えたときに効いてきます。