【SQL】SQL入門（パフォーマンスチューニング編）
ここではSQLのパフォーマンスチューニングについて解説していきます。
SQLはSELECT文の書き方をある程度覚えてしまえば、やりたいことは大抵実現できます。
しかし、インデックスなどのパフォーマンスに関する知識がないと、データ件数が増えた時に速度が遅くなるSQLを書いてしまう可能性があります。
また、実際に速度が遅くなっているときに、どのように調査してどう改善してよいかも分からない状態になってしまいます。
そうならないためにパフォーマンスに関する知識を解説していきます。
ここではSQLの基本は出来ている前提とします。
前提知識については下記の記事を参照ください。
この記事での目標
- インデックスの仕組みを理解する
- インデックスが適切に使われるSQLの書き方を理解する
- 実行計画の仕組みと読み方を理解する
目次
- インデックスとは
- インデックスの種類
- インデックスのメリットとデメリット
- インデックスの作成方法
- インデックスが使用されるSQL・使用されないSQL
- データ移行テクニック
- 実行計画とは
- 実行計画の確認方法
- 実行計画の確認
- 統計情報
インデックスとは
インデックスとは
インデックスとは日本語では索引の意味です。
辞書や専門書には本の最後の方に用語がアルファベット順、五十音順に並べられている索引があります。
インデックスはその索引がコンピュータ上で実現されているものだと認識してください。
インデックスがある場合とない場合でどういう違いがあるかを理解しておきましょう。
まずは紙の辞書を例に考えます。
本の情報が載っている辞書があるとします。
載っている情報は以下の通りです。
- 本のタイトル
- 著者
- 出版社
- 出版日付
- 本の概要
上記の情報が出版日付順に載っていて、約500ページ分の情報が載っているとします。
このとき、「SQL入門」というタイトルの本を探したいとします。
そしてその本の出版日付は分からないとします。
ここで仮にタイトルについての索引がないとしたら、探したい本はいつ出版されているのか分からないので、
1ページ目から探すしかありません。
運が良ければ最初の10ページくらい探せば見つかるかもしれません。
しかし、その本よりも後に同じタイトルの本が出版された可能性もあります。
そのため、最初の10ページで見つかったとしても、残りのページも探す必要があります。
結局、索引がない場合、出版日付が分からなければ全てのページを探す必要があります。
ではタイトルに関する索引があった場合はどうなるでしょうか。
まず索引の中から「SQL入門」というタイトルを探しますが、索引ではタイトルがアルファベット順に載っているので、せいぜい5～6ページ探せば見つかるでしょう。
索引で対象のタイトルを見つけたら、その情報が載っているページを探せばいいので、多くても10ページ以内には見つけることができます。
つまり、ここの例では索引がない場合は500ページ検索する必要があるが、索引があれば10ページ以内で該当のデータが探せます。
DBのインデックスも基本的に考え方は同じです。
辞書をbooksという名前のテーブルに置き換えると、「SQL入門」という本を探すという行為は、
SELECT * FROM books WHERE title = 'SQL入門';
というSQLを実行するのに等しいです。
この時、titleに関するインデックスがない場合、レコード件数を全て取得してtitleが「SQL入門」と一致するのかを調べなければいけません。
titleに関するインデックスが存在するのであれば、インデックスの領域からtitleが「SQL入門」のレコードがある場所を特定し、実レコードを検索します。
結果、少ないアクセスで欲しいレコードを取得することができます。
レコード件数が少ない場合はあまり差は出ませんが、レコード件数が多くなればなるほど速度に差が出るようになります。
物理的にはどうなっているか
上記の説明は辞書を例に話しましたが、実際にコンピュータ上ではどうやってこのような仕組みを実現しているのでしょうか。
DBのデータもOSから見るとただのファイルです。
そのためストレージ(HDD, SSDなど)上にデータが保存されています。
ストレージ上には、ブロック(ページと呼ぶ場合もある)と呼ばれる単位でデータが保存されています。
ブロックには、同じテーブルのレコードが複数行格納されています。
ブロックには、そのブロックを特定するためのアドレスが割り当て割れています。
辞書や本での索引は、用語と、その用語が載っているページの組み合わせですが、DBでのインデックスは、カラムの値と、その実データが格納されているブロックのアドレスの組み合わせです。
インデックスの種類
インデックスは大きく3つの種類があります。
- B-Treeインデックス
- ビットマップインデックス
- ハッシュインデックス
の3つです。
B-Treeインデックス
一般にRDBにおけるインデックスはB-Treeインデックスのことを指します。
DBに関する説明でインデックスという言葉が出てきたら9割がたこのB-Treeインデックスのことだと思って良いでしょう。
先にインデックスを紙の辞書に例えて説明しましたが、あのイメージに合致するインデックスがB-Treeインデックスです。
インデックス作成時に指定したカラムが値が小さい順(数値なら数値の順、文字なら文字コード順)に並び、その値に対して、実レコードが格納されているブロックのアドレスが対応付けられています。
ビットマップインデックス
ビットマップインデックスは、カーディナリティ(選択度)が低いカラムにおいて有効なインデックスです。
例えば、性別というカラムがあった場合、そのカラムが取りうる値は基本的に男、女の2種類になります。
また、血液型というカラムの場合、レコード数がどれだけ多くなっても、取りうる値はA, B, O, ABの4種類です。
このように、レコード数が増えても取りうる値の数が少ない場合、カーディナリティが低いといいます。
ビットマップインデックスはこのようなカラムに対して有効となるインデックスです。
ただ、更新時の負荷が大きいなど、デメリットが大きいので、B-Treeインデックスと比べると使用頻度は低くなります。
サポートの有無もDBMSによって異なります。
詳しい仕組みはこちらにも書いています。
https://qiita.com/gohandesuyo/items/b3a684157b2eefc69a79
ハッシュインデックス
ハッシュインデックスは、ハッシュ値を使ったインデックスになります。
ハッシュインデックスは、「=」での検索(つまり完全一致での検索)でしか有効になりません。
B-Treeインデックスの場合、範囲指定や部分一致の場合でもSQLの書き方によってはインデックスが使用されます。
しかしハッシュインデックスは、範囲指定での検索や部分一致の検索で利用できないため、限定的なインデックスになります。
そのため使用頻度は低いです。
インデックスのメリットとデメリット
インデックスはたくさん作ればよいというものではありません。
インデックスを作成することにはメリットとデメリットがあるので、その両方を理解したうえで適切に設定することが重要です。
インデックスのメリット
インデックスのメリットは、検索(SELECT文)が速くなることです。
ただし、SQLの書き方によってインデックスが適切に使用される場合とそうでない場合があるので、インデックスを有効活用するにはSQLをどう書くかの知識も必要です。
インデックスで処理が速くなるのは基本的に検索(SELECT文)です。
ただし、UPDATEやDELETEの高速化も期待できます。
それは、UPDATEやDELETEを実行する際にも、初めに該当のレコードを検索するためにSELECTが内部的に実行されるからです。
後述しますが、インデックスが増えると基本的に更新の負荷は増えます。
ただ、その分検索は速くなるので、トータルでどちらの方がメリットが大きいかを適切に判断する必要があります。
インデックスのデメリット
インデックスのデメリットは2つあります。
1つ目は、ディスク容量の圧迫です。
辞書で考えた場合、索引のページが増えれば、その分辞書全体のページ数も増えてしまいます。
それと同じで、DBでもインデックスを作成するとその分ディスク容量は減ります。
2つ目は、更新(INSERT, UPDATE, DELETE)の負荷がかかることです。
これも紙の辞書で考えてみます。
例えば、既存の辞書に新しいデータを増やすとします。
データそのものは、最後のページ以降にどんどん追加していけば問題ないでしょう。
ただ、索引は、アルファベット順、あるいは五十音順で並んでいます。
新しいデータを追加したとき、既存の並び順の間にデータを入れ込む必要がある場合、それ以降の索引を全部後ろにずらしていく必要があります。
索引にデータを追加するのは、本編にデータを追加するよりも大変であることが想像できると思います。
DBでもインデックスに関して、内部的にはこれと同じことがおきます。
B-treeインデックスは、その名の通り、データが木構造になっています。
テーブルに対してレコードが更新(INSERT, UPDATE, DELETE)されると、それに伴って木構造のデータを組み替えるアルゴリズムが走ります。
そのためテーブルに対してインデックスの数が多ければ多いほど、多くの負荷がかかることになります。
インデックスは一つのテーブルに対して複数作成できますが、メリットとデメリットを理解したうえで適切に設計することが必要になります。
インデックスの作成方法
インデックスはテーブル作成の際にPRIMARY KEYを設定したカラムがあれば、そのカラムについてのインデックスが作成されます。
また、UNIQUE制約を付けた場合にも、インデックスが作成されます。
PRIMARY KEYやUNIQUEの制約を指定していないカラムに対してインデックスを作成したい場合は、CREATE INDEXで自分で定義します。
CREATE INDEX <index_name> ON <table_name> (column1, …)
インデックス名は、どのテーブルのどのカラムを使用したインデックスかが分かる名前にすると良いでしょう。
カラムは複数指定することもできます。
複数のカラムを指定して作成したインデックスは、マルチカラムインデックスと呼ばれます。
マルチカラムインデックスを作成する場合は、カラムの順番も非常に重要になります。
インデックスが使用されるSQL・使用されないSQL
インデックスを作成したとしても、SQLを正しく書いていなければインデックスは適切に使用されません。
ここでは、どのようSQLを書けばインデックスが使用されて、どのような書き方をすると使用されないかを見ていきます。
シンプルなインデックス
以下のようなテーブルを参考に考えます。
books
| id | title |
|---|---|
| 1 | SQL入門 |
| 2 | Java入門 |
| 3 | PHP入門 |
本のタイトルを格納するbooksテーブルを考えます。
主キーはidで、titleにもインデックスが使用されているとします。
ここでは3件分のみ表示していますが、実際には数万件のレコードがあり、idは連番、titleは適当な文字列が格納されていると考えてください。
インデックスは値が昇順（あるいは降順）で並んでいることを意識することが重要です。
辞書で指定された検索条件から索引を使って調べることができるかどうかをイメージすることで、理解しやすくなるかと思います。
以下、SQLのサンプルと共にインデックスが使用されるかどうかを見ていきます。
-- 1 -- 使用される -- 完全一致の場合はインデックスは使用される SELECT * FROM books WHERE id = 100; -- 2 -- 使用されない -- 条件に否定(<>, !=)が入ると使用されない SELECT * FROM books WHERE id <> 100; -- 3 -- 使用される -- 全体の件数と絞り込みの件数に依存する SELECT * FROM books WHERE id BETWEEN 1 AND 1000; -- 4 -- 使用される -- 1と同じ。完全一致はOK SELECT * FROM books WHERE title = 'SQL入門'; -- 5 -- 使用されない -- 後方一致は、絞り込みができないので使用されない SELECT * FROM books WHERE title LIKE '%QL入門'; -- 6 -- 使用される可能性が高い -- 前半部分が分かれば、並び順である程度絞り込みができるので、使用される -- ただし、DBMSによる SELECT * FROM books WHERE title LIKE 'SQ%'; -- 7 -- 使用されない -- ORが入ると絞り込みが意味なくなるので、使用されない SELECT * FROM books WHERE id = 100 OR title = 'SQL入門'; -- 8 -- 使用される -- AND条件なら絞り込みが有効なので使用される SELECT * FROM books WHERE id = 100 AND title = 'SQL入門'; -- 9 -- 基本は使用されない。DBMSによって変わる -- NULLは値ではないので、使用されないと考えたほうがよい -- ただ、実際にはDBMSに依存する SELECT * FROM books WHERE title IS NULL; -- 10 -- 使用されない -- 関数を使って加工した時点で使用されなくなる SELECT * FROM books WHERE trim(title) = 'SQL入門'; -- 11 -- 使用されない -- 上と同じ SELECT * FROM books WHERE substr(title, 1, 2) = 'SQ'; -- 12 -- 使用される -- INは値が少なければインデックスを使用したほうが効率が良いので使用される SELECT * FROM books WHERE title IN ('AAA', 'BBB', 'SQL');
これはあくまでもサンプルです。
実際にはDBMSによっても変わるし、環境によっても変わります。
厳密にインデックスが使用されているかどうかを知りたければ、後述の実行計画を確認する必要があります。
マルチカラムインデックス
続いてはマルチカラムインデックスについても見てみます。
table1
| id | name1 | name2 | name3 |
|---|---|---|---|
| 1 | a1 | b1 | c1 |
| 2 | a2 | b2 | c2 |
| 3 | a3 | b3 | c3 |
このテーブルではidを主キーとし、name1, name2, name3という順番でマルチカラムインデックスが定義されているとします。
マルチカラムインデックスの場合、左のカラムから順番に条件指定で絞り込むことができれば、インデックスが使用されます。
-- 1 -- 使用される -- 1番目のキーで絞り込むのはOK SELECT * FROM table1 WHERE name1 = 'a1'; -- 2 -- 使用されない -- name1での絞り込みがなければname2だけ絞り込んでも使用されない SELECT * FROM table1 WHERE name2 = 'b1'; -- 3 -- 使用されない -- ORがあると使用されない SELECT * FROM table1 WHERE name1 = 'a1' OR name2 = 'b1'; -- 4 -- 使用される -- ANDなら使用される SELECT * FROM table1 WHERE name1 = 'a1' AND name2 = 'b1'; -- 5 -- 使用される -- 順番が変わってもOK -- ただし、DBMSやバージョンによっては確認が必要 SELECT * FROM table1 WHERE name2 = 'b1' AND name1 = 'a1'; -- 6 -- 使用される -- name1でインデックスでの絞り込みができるのでOK SELECT * FROM table1 WHERE name1 = 'a1' AND trim(name2) = 'b1'; -- 7 -- 使用されない -- name1で関数が使用されるとその時点で使用されない SELECT * FROM table1 WHERE name2 = 'b1' AND trim(name1) = 'a1';
ここまでサンプルを元にインデックスが使用されるかどうかを見てきました。
インデックスが使用される書き方をイメージできましたでしょうか。
実際にはDBMSや、データの件数など、環境によって変わってくるので、最終的には実行計画の確認で使用の有無を確認するのが望ましいです。
まとめ
最後に、どんな条件の書き方だとインデックスが使用され、どんな書き方だと使用されないのかを整理しておきましょう。
インデックスが使用される可能性の高い条件指定
-- ＝を使用する WHERE column1 = '100' -- 狭い範囲での条件指定 WHERE column1 BETWEEN '1' AND '100' -- 前方一致のあいまい検索 WHERE column1 LIKE 'abc%'
インデックスが使用されない条件指定
-- 否定（<>）を使用している WHERE column1 <> '100' -- 広い範囲での条件指定（ヒットする件数が多い場合） WHERE column1 BETWEEN '1' AND '99999999' -- OR を使用している WHERE column1 = '100' OR column2 = 'abc' -- NULLでの検索をしている -- ※ただしNULLの扱いはDBMSに依存する。 WHERE column1 IS NULL -- あいまい検索（後方一致）を使用している WHERE column1 LIKE '%abc%' -- 絞り込み対象のカラムに対して演算を適用している WHERE column1 * 1.1 = 1100 -- 絞り込み対象のカラムに対して関数を適用している WHERE TRIM(column1) = 'abc' -- CASE式を利用している WHERE column1 = CASE WHEN column2 = 100 then 'A' WHEN column2 = 200 then 'B' END
インデックスの構造を理解し、どんなSQLを書けばインデックスが適切に使用されるかを理解しておきましょう。
データ移行テクニック
ここではインデックスに関連した、データ移行・あるいはデータ作成のテクニックについてお伝えします。
大量のデータをインサートしたい時、処理を速くするには、以下の手順で実施するのが良いです。
- テーブル定義の作成
- データのインサート
- インデックスの作成
ポイントはインデックスの作成を後に行うことです。
インデックスのデメリットの部分でお伝えしましたが、インデックスがあると更新の処理は負荷が増えます。
例えば、インデックスが存在している状態で100万件のデータをインサートしたとすると、100万回インデックスの再構築のアルゴリズムが動くことになり、非常に負荷が大きくなります。
先にデータをインサートしておけば、インデックス構築のアルゴリズム自体は1回で済みます。
当然、100万件のデータに対してインデックスを作成するのはそれなりに負荷はかかりますが、100万回再構築のアルゴリズムが走ることの負荷に比べればかなり小さな負荷になります。
実行計画とは
インデックスの構造について理解しても、実際にインデックスが適切かどうかは、実行計画を確認してみなければ分かりません。
ここでは実行計画について解説します。
実行計画とは
実行計画とは、一言でいえばデータへのアクセス方法のことです。
これは例えるならGoogle Mapのようなものです。
Google Mapでは、目的地を入力すると、現在地から目的地までの最短のルートを示してくれます。
あとはこの指示に従って移動すれば示された時間で目的地に辿り着くことができます。
DBに話を戻すと、ここでは欲しいデータが目的地に該当します。
Google Mapでは、目的地までのルートが複数表示されます。
これはDBで言えば、インデックスを使わない場合のデータのアクセス、インデックスを使い場合のアクセス、など、
どのようなアルゴリズムで該当のデータまでアクセスするかを示したものと同じです。
Google Mapでは、複数の選択肢の中から、最短のルートが優先的に表示されます。
DBでも、複数のデータのアクセス方法の中から、自動的に最適なものを選択してくれます。
このDBが自動的に選んだ最適なデータアクセス手法が実行計画です。
実行計画を学ぶ意味
DBが自動的に最適なデータアクセスの手法を選んでいるのであれば、わざわざエンジニアが実行計画の仕組みや読み方を学習する意味はないと感じるかもしれません。
しかし、プログラムのパフォーマンスが悪くなった時に、どのSQLに原因があるのかを特定したり、遅いSQLがどういう原因で遅くなっているのかを特定するたに、実行計画の知識は不可欠です。
SQL実行までの流れ
実行計画について知る前に、SQLが実行されるまでの流れを見ていきましょう。
SQLの実行の流れは以下のようになっています。
- DBMSにクエリ(SQL)が投げられる
- パーサによってSQLがパース(解析)される
- カタログマネージャから統計情報やインデックス情報を取得する
- プランを作成する
- コストを評価する
- プランを評価する
- SQL実行
順番に解説していきます。
まず、パーサはSQL文の文法に間違いがないかを解析するプログラムです。
SQL文の文法自体が間違っていれば、SQLはそもそも実行されませんので、この時点ではじかれます。
パースされたSQLは、オプティマイザと呼ばれるプログラムに渡されます。
オプティマイザは最適化という意味があります。
このオプティマイザが最終的に実行計画を決定します。
オプティマイザはまず、カタログマネージャと呼ばれる部分から統計情報やインデックスに関する情報などを取得します。
統計情報とは、例えば、テーブルに対して何件のレコードがあるかや、カラムのデータの偏り(カーディナリティ)などの情報などです。
そのあと、オプティマイザは、データアクセスのプランを作成します。
これは例えば、インデックスを使用しないアクセス方法、インデックスを使用するアクセス方法、結合でどのアルゴリズムを使用するか、などです。
オプティマイザは取得した統計情報を元にそれぞれのプランでどのくらいのコストがかかるかを計算します。
その後に最終的に最適な(コストが低い)プランを選び、SQLを実行します。
これもGoogle Mapに例えて考えると理解しやすいかと思います。
Google Mapでは目的地を入力すると、現在地から目的地までの経路がいくつか表示されます。
そこから、渋滞状況や距離などを元に、それぞれの経路でのかかる時間が計算されます。
そこから、最短ルートが選択されます。
カタログマネージャが、Google Mapでいうところの、渋滞情報や距離で、コストが目的地までの時間だと考えると分かりやすいのではないでしょうか。
実行計画の確認方法
実行計画の確認方法は、DBMSによって異なります。
| DBMS | コマンド |
|---|---|
| Oracle | SET AUTOTRACE TRACEONLY |
| SQL Server | SET SHOWPLAN_TEXT ON |
| DB2 | EXPLAIN ALL WITH SNAPSHOT FOR SQL文 |
| PostgreSQL | EXPLAIN [ANALYZE] SQL文 |
| MySQL | EXPLAIN EXTENDED SQL文 |
OracleとSQL Serverの場合は、コマンドを実行するとモードが切り替わり、それ以降SQLを実行すると実行計画が表示されるような仕組みです。
MySQL, PostgreSQLは、SQLの前にEXPLAINのコマンドを付けることで、そのSQLの実行結果が見れるようになります。
など、GUIツールを使用している場合、そのツールで実行計画の参照方法が定義されていると思います。
自身でよく使用するツールがある場合は、そちらも調べてみると良いでしょう。
実行計画の確認
ここでは、PostgreSQLの環境を例に、実際の実行計画を確認します。
まず、実行計画確認用で以下の2つのテーブルとインデックスがあるとします。
tableA, tableBにはそれぞれ10万件のデータが、tableCには1万件のデータが格納されているとします。
tableA
| 物理名 | 型 | primary key |
|---|---|---|
| a_id | integer | 〇 |
| name | varchar(50) | |
| value | real |
インデックス
- a_id
tableB
| 物理名 | 型 | primary key |
|---|---|---|
| b_id | integer | 〇 |
| a_id | integer | |
| value | real |
インデックス
- b_id
- a_id
tableC
| 物理名 | 型 | primary key | 備考 |
|---|---|---|---|
| c_id | integer | 〇 | |
| blood_type | varchar(2) | A,B,O,ABのいずれか |
インデックス
- c_id
- blood_type
実際に環境を作って試したい場合は、以下のSQLを実行してテーブルとインデックス、テストデータを作成してください。
-- テーブルA作成 create table tableA( a_id int primary key , name varchar(50) , value real -- ); -- テーブルAのテストデータ作成 insert into tableA with recursive DUMMY(i) as ( select 1 i union all select i+1 from DUMMY where i < 100000 ) select i , 'テスト', 500 from DUMMY; -- テーブルB create table tableB( b_id int primary key , a_id int -- tableAのID , value real -- ); -- テーブルBのテストデータ作成 insert into tableB with recursive DUMMY(i) as ( select 1 i union all select i+1 from DUMMY where i < 100000 ) select i , i, i % 100 + 1 from DUMMY; -- インデックス作成 create index a_index on tableB (a_id); -- テーブルC（ビットマップ用） create table tableC( c_id int primary key , blood_type varchar(2) ); insert into tableC with recursive DUMMY(i) as ( select 1 i union all select i+1 from DUMMY where i < 10000 ) select i, case when i % 4 = 0 then 'A' when i % 4 = 1 then 'B' when i % 4 = 2 then 'O' when i % 4 = 3 then 'AB' end from DUMMY; create index c_index on tableC (blood_type);
では色んなSQLの実行計画を見ていきます。
テーブルフルスキャン
-- 全件取得のSQL EXPLAIN SELECT * FROM tableA;
QUERY PLAN --------------------------------------------------------------- Seq Scan on tablea (cost=0.00..1637.00 rows=100000 width=18)
結果が「Seq Scan」となっています。
これは、テーブルの全てのレコードにアクセスする方法で、全件スキャン、テーブルフルスキャンなどと呼ばれます。
今回のSQLは条件により絞り込みを行っていないので、「Seq Scan」になるのはある意味当然です。
レコード件数が多いテーブルに対してテーブルフルスキャンになると、速度が低下する可能性が高いです。
実行計画の中で「1637.00」となっているのは、SQLの実行にかかるコストです。
コストは基本的には大きいほど負荷がかかり、小さいほど負荷が少なくなります。
ただし、実行の速度に関してはハードの性能などでも変わってくるので、コストが大きいから速度が遅くなるとは一概には言えません。
ただ、少ない方が良いことには変わりないので、コストの数値が目安になることはおさえておきましょう。
続いて、同じSQL文ですが、ANALYZEというキーワードを付けてみます。
-- ANALYZE追加 EXPLAIN ANALYZE SELECT * FROM tableA;
QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Seq Scan on tablea (cost=0.00..1637.00 rows=100000 width=18) (actual time=12.664..90.661 rows=100000 loops=1) Planning Time: 0.040 ms Execution Time: 108.198 ms
SQLが同じなため、実行計画そのものは変わりません。
ただ、実際に実行にかかった時間も表示されます。
通常、実行計画を確認するときは、SQLの実行は行われませんが、ANALYZEを付けると、実際に実行して、通常よりも詳しい情報まで表示してくれます。
ANALYZEを使用するときの注意点としては、更新系のSQLを実行するとそのまま実行されてしまう事です。
更新系のSQLの実行計画の確認でANALYZEを使用する場合は、事前にトランザクションを開始して、実行計画確認後にロールバック処理が必要になります。
インデックススキャン
絞り込み条件を指定して1件のみ取得します。
-- 1件のみ取得 EXPLAIN SELECT * FROM tableA WHERE a_id = 1;
QUERY PLAN --------------------------------------------------------------------------- Index Scan using tablea_pkey on tablea (cost=0.29..8.31 rows=1 width=18) Index Cond: (a_id = 1)
結果が「Index Scan」になっています。
これはインデックスを使用したデータアクセスになっています。
どのテーブルのどのインデックスが使用されたかも表示されています。
インデックスが使用され、かつデータが1件のため、コストはテーブルフルスキャンに比べて低くなっています。
インデックスオンリースキャン
カラムを指定してa_idのみ取得してみます。
EXPLAIN SELECT a_id FROM tableA WHERE a_id = 1;
QUERY PLAN ------------------------------------------------------------------------------- Index Only Scan using tablea_pkey on tablea (cost=0.29..8.31 rows=1 width=4) Index Cond: (a_id = 1)
結果が「Index Only Scan」になっています。
インデックスオンリースキャンは、インデックスのみを検索し、実データのブロックにはアクセスせずにデータを取得したことを表します。
インデックスは、カラムの値と、実データが格納されたブロックのアドレスがセットになっています。
今回指定したSQLでは、必要なデータがa_idのみのため、インデックスに欲しいデータが全て格納された状態だと言えます。
そのため、インデックスオンリースキャンが実行されます。
ビットマップスキャン
-- A型のデータを取得 EXPLAIN SELECT * FROM tableC WHERE blood_type='A';
QUERY PLAN
--------------------------------------------------------------------------
Bitmap Heap Scan on tablec (cost=51.66..127.91 rows=2500 width=6)
Recheck Cond: ((blood_type)::text = 'A'::text)
-> Bitmap Index Scan on c_index (cost=0.00..51.03 rows=2500 width=0)
Index Cond: ((blood_type)::text = 'A'::text)
結果が「Bitmap Heap Scan」になっています。
これはビットマップスキャンでのデータアクセスになります。
blood_typeは、データがA, B, AB, Oの4種類しかありません。
データが1万件に対して、4種類しかないので、通常のインデックスでは効率よくデータを絞り込むことができません。
こういう場合にはビットマップインデックスが有効になります。
Oracleの場合、CREATE INDEXコマンドを使ってインデックスを作成する時点で、ビットマップインデックスかどうかを指定します。
しかし、PostgreSQLの場合、CREATE INDEXでそのカラムに対してインデックス作成していれば、実行時にメモリ上にビットマップを作成し、それをもとにデータにアクセスする実装になっているようです。
ただし、これはLinuxにインストールされたPostgreSQLの場合です。
Windwos環境で実施した場合には、Bitmap Heap Scan は実行されませんでした。
結合のアルゴリズム
続いては結合のSQLを実行した場合を見ていきます。
DBMSにもよりますが、一般に結合には3つのアルゴリズムがあります。
それぞれ確認していきます。
Nested Loop
EXPLAIN SELECT * FROM tableA a INNER JOIN tableB b ON a.a_id = b.a_id WHERE b.value = 1 AND b.a_id BETWEEN 10 AND 200;
QUERY PLAN
-----------------------------------------------------------------------------------
Nested Loop (cost=0.58..30.53 rows=2 width=30)
-> Index Scan using a_index on tableb b (cost=0.29..13.91 rows=2 width=12)
Index Cond: ((a_id >= 10) AND (a_id <= 200))
Filter: (value = '1'::double precision)
-> Index Scan using tablea_pkey on tablea a (cost=0.29..8.31 rows=1 width=18)
Index Cond: (a_id = b.a_id)
結合のアルゴリズムでNested Loopが利用されています。
結合が使用されるSQL文を実行するとき、取得するレコードが少ない方のテーブルを「駆動表」といいます。 「駆動表」でない方のテーブルを「内部表」といいます。
Nested Loopのアルゴリズムは以下になります。
- 駆動表を1行ずつループしながらスキャンする。
- 駆動表の1行に対して、内部表を1行ずるスキャンして、結合条件に合致する場合はそれを取得する。
- その処理を駆動表のすべてのループに対して実施。
結合で最も基本的なアルゴリズムがNested Loopです。
MySQLではそもそも結合のアルゴリズムがNested Loopしかサポートされていなかったりします。
Nested Loopで処理が速くなるかどうかは2つのポイントがあります。
- 駆動表を小さくすること
- 内部表の結合キーにインデックスが存在する
の2つです。
まず駆動表が小さいと、そもそもループ件数が少なくなるので処理のコストは小さくなります。
また、内部表の結合キーにインデックスが存在するかどうかが重要です。
例えば駆動表で該当するレコードが10件、内部表が全体で100件あったとします。
内部表の結合キーにインデックスが存在しない場合、処理件数は「10×100=1000」となります。
一方でインデックスがある場合は、ほぼ一回のアクセスで該当のレコードにアクセスできると考えると処理件数は「10×2=20」となります。
レコード件数が多いほどインデックスの有無でコストに大きな差が出ます。
Hash
続いて少し条件を変えてみます。
EXPLAIN SELECT * FROM tableA a INNER JOIN tableB b ON a.a_id = b.a_id WHERE b.value = 1;
QUERY PLAN
---------------------------------------------------------------------------
Hash Join (cost=1803.91..3826.24 rows=1033 width=30)
Hash Cond: (a.a_id = b.a_id)
-> Seq Scan on tablea a (cost=0.00..1637.00 rows=100000 width=18)
-> Hash (cost=1791.00..1791.00 rows=1033 width=12)
-> Seq Scan on tableb b (cost=0.00..1791.00 rows=1033 width=12)
Filter: (value = '1'::double precision)
ここでは「Hash Join」といういアルゴリズムが使用されました。
Hash Joinのアルゴリズムは以下になります。
- 小さいテーブルに対してスキャンし、結合キーに対してハッシュ関数を適用することでハッシュ値に変換。それをハッシュテーブルと呼ぶ。
- もう一方のテーブルをスキャンし、そのハッシュ値がハッシュテーブルに存在するかどうかを調べる。
- 合致したら結合を行う。
Hash Joinの特徴は以下になります。
- ハッシュテーブルを作成する必要があるため、Nested Loopsよりも多くのメモリを消費する。
- 等値結合（=での結合）でのみ使用可能。
- Nested Loopsで適切な駆動表が存在しない場合や、結合キーにインデックスが存在しない場合などでは、Hashが有効なケースとなる。
Sort Merge
さらにSQLを変更してみます。
EXPLAIN SELECT * FROM tableA a INNER JOIN tableB b ON a.a_id = b.a_id order by b.a_id;
QUERY PLAN ------------------------------------------------------------------------------------------- Merge Join (cost=0.65..7892.48 rows=100000 width=34) Merge Cond: (a.a_id = b.a_id) -> Index Scan using tablea_pkey on tablea a (cost=0.29..3244.29 rows=100000 width=18) -> Index Scan using a_index on tableb b (cost=0.29..3148.29 rows=100000 width=12)
ここでは「Merge Join」となっており、Sort Mergeというアルゴリズムが使用されています。
Sort Mergeのアルゴリズムは以下になります。
- テーブルをそれぞれ結合キーでソートする。
- 片方のテーブルをスキャンしながら一致する結合キーが存在したら、結合する。
特徴は以下になります。
- 対象のテーブルを両方ソートするため、多くのメモリを使用する。
- Hashとは違って不等号を使った結合も可能。
まとめ
結合について3つのアルゴリズムを見てきました。
基本はNested Loopと考えてよいでしょう。
ですので、結合の処理を速くしようと思ったら、
- 駆動表を小さくすること
- 結合キーにインデックスを作成する
この2つをおさえておくとよいでしょう。
様々な実行計画
ここからは様々なSQLの実行計画を見ていきます。
-- ORDER BY EXPLAIN SELECT * FROM tableB WHERE a_id BETWEEN 1 AND 100 ORDER BY b_id;
QUERY PLAN
--------------------------------------------------------------------------------
Sort (cost=14.04..14.31 rows=107 width=12)
Sort Key: b_id
-> Index Scan using a_index on tableb (cost=0.29..10.43 rows=107 width=12)
Index Cond: ((a_id >= 1) AND (a_id <= 100))
ORDER BYが入ると、レコードを並び替える処理が必要なため、Sortの処理が行われます。
実行計画は入れ子になっている場合、中の処理から実行されます。
ここでは、まずインデックスによる絞り込みが行われ、その後のソートの処理が行われます。
-- ORDER BY... EXPLAIN SELECT * FROM tableB WHERE a_id BETWEEN 1 AND 100 ORDER BY a_id;
QUERY PLAN -------------------------------------------------------------------------- Index Scan using a_index on tableb (cost=0.29..10.43 rows=107 width=12) Index Cond: ((a_id >= 1) AND (a_id <= 100))
ここではORDER BYが使用されているにもかかわらず、Sortの処理がありません。
これは、インデックスがそもそも昇順に並んでいることが影響しています。
a_idで条件を絞っているので、a_idのインデックスを使ってデータを取得します。
このとき、データはa_idの昇順で並んでいるので、ORDER BYでa_idを指定しても、Sortの処理はいらないということです。
-- ORDER BY DESC EXPLAIN SELECT * FROM tableB WHERE a_id BETWEEN 1 AND 100 ORDER BY a_id DESC;
QUERY PLAN ----------------------------------------------------------------------------------- Index Scan Backward using a_index on tableb (cost=0.29..10.43 rows=107 width=12) Index Cond: ((a_id >= 1) AND (a_id <= 100))
Index Scan Backward
これはインデックスを逆から探しています。
探すのが逆になっただけなので、コスト自体は変更はありません。
-- LIMIT EXPLAIN SELECT * FROM tableB WHERE a_id BETWEEN 1 AND 100 LIMIT 50;
QUERY PLAN
--------------------------------------------------------------------------------
Limit (cost=0.29..5.03 rows=50 width=12)
-> Index Scan using a_index on tableb (cost=0.29..10.43 rows=107 width=12)
Index Cond: ((a_id >= 1) AND (a_id <= 100))
LIMITを使用すると、それ以降のデータは切り捨てられるので、全体のコストとしては下がっています。
-- ORDER BY & LIMIT EXPLAIN SELECT * FROM tableB WHERE a_id BETWEEN 1 AND 100 ORDER BY b_id LIMIT 50;
QUERY PLAN
--------------------------------------------------------------------------------------
Limit (cost=13.99..14.11 rows=50 width=12)
-> Sort (cost=13.99..14.25 rows=107 width=12)
Sort Key: b_id
-> Index Scan using a_index on tableb (cost=0.29..10.43 rows=107 width=12)
Index Cond: ((a_id >= 1) AND (a_id <= 100))
Sortで並び替えをしてからのLIMITとなるため、先ほどよりはコストは増していますが、それでもLIMITなしに比べると若干コストは減っています。
-- COUNT EXPLAIN SELECT count(*) FROM tableB;
QUERY PLAN -------------------------------------------------------------------- Aggregate (cost=1791.00..1791.01 rows=1 width=8) -> Seq Scan on tableb (cost=0.00..1541.00 rows=100000 width=0)
countなどの集約関数を使用すると、Aggregateという処理で集約が行われます。
データ取得後に集約する分だけコストが上乗せされています。
-- GROUP BY EXPLAIN SELECT value, count(*) FROM tableB GROUP BY value;
QUERY PLAN -------------------------------------------------------------------- HashAggregate (cost=2041.00..2042.00 rows=100 width=12) Group Key: value -> Seq Scan on tableb (cost=0.00..1541.00 rows=100000 width=4)
GROUP BYを使用すると、 Group Keyによる集約の処理が行われます。
GROUP BYでの集約にはHashAggregateか、GroupAggregateがあるようです。
私の環境ではHashAggregateになりました。
-- GROUP BY & HAVING EXPLAIN SELECT value, count(*) FROM tableB GROUP BY value HAVING count(*) > 100;
QUERY PLAN -------------------------------------------------------------------- HashAggregate (cost=2291.00..2292.25 rows=33 width=12) Group Key: value Filter: (count(*) > 100) -> Seq Scan on tableb (cost=0.00..1541.00 rows=100000 width=4)
HAVINGではGROUP BYで絞り込みを行った後に絞り込みを行うため、その分コストが加算されています。
-- WHERE & GROUP BY EXPLAIN SELECT value, count(*) FROM tableB WHERE b_id < 1000 GROUP BY value;
QUERY PLAN
------------------------------------------------------------------------------------
HashAggregate (cost=45.41..46.41 rows=100 width=12)
Group Key: value
-> Index Scan using tableb_pkey on tableb (cost=0.29..40.05 rows=1072 width=4)
Index Cond: (b_id < 1000)
WHEREとGROUP BYを組み合わせた場合は、先に絞り込みを行ってから集約の処理が行われるため、全体のコストがかなり下がっています。
-- GROUP BY & ORDER BY EXPLAIN SELECT value FROM tableB GROUP BY value ORDER BY value;
QUERY PLAN
--------------------------------------------------------------------------
Sort (cost=1795.32..1795.57 rows=100 width=4)
Sort Key: value
-> HashAggregate (cost=1791.00..1792.00 rows=100 width=4)
Group Key: value
-> Seq Scan on tableb (cost=0.00..1541.00 rows=100000 width=4)
集約して、ソートの処理が行われます。
集約するときにはそもそもそのカラムの値でソートしなければ集約できません。
そのためORDER BYで並び替えてもそれほどコストが上乗せされていないのの出はないかと推測できます。
-- UNION EXPLAIN SELECT a_id FROM tableA UNION SELECT a_id FROM tableB;
QUERY PLAN
--------------------------------------------------------------------------------
Unique (cost=26524.64..27524.64 rows=200000 width=4)
-> Sort (cost=26524.64..27024.64 rows=200000 width=4)
Sort Key: tablea.a_id
-> Append (cost=0.00..6178.00 rows=200000 width=4)
-> Seq Scan on tablea (cost=0.00..1637.00 rows=100000 width=4)
-> Seq Scan on tableb (cost=0.00..1541.00 rows=100000 width=4)
UNIONではAppendという処理が行われます。
重複したデータは排除されるため、ソートして、Uniqueという処理で重複を削除します。
-- UNION ALL EXPLAIN SELECT a_id FROM tableA UNION ALL SELECT a_id FROM tableB;
QUERY PLAN -------------------------------------------------------------------- Append (cost=0.00..4178.00 rows=200000 width=4) -> Seq Scan on tablea (cost=0.00..1637.00 rows=100000 width=4) -> Seq Scan on tableb (cost=0.00..1541.00 rows=100000 width=4)
UNION ALLでは、重複の削除が行われないため、Appendの処理だけで終了しており、UNIONよりもコストは低くなっています。
-- EXISTS EXPLAIN SELECT * FROM tableA a WHERE EXISTS (SELECT * FROM tableB WHERE a_id = a.a_id AND value = 1 AND a_id BETWEEN 1 AND 200);
QUERY PLAN
-----------------------------------------------------------------------------------------
Nested Loop (cost=14.43..30.77 rows=2 width=18)
-> Unique (cost=14.14..14.15 rows=2 width=4)
-> Sort (cost=14.14..14.15 rows=2 width=4)
Sort Key: tableb.a_id
-> Index Scan using a_index on tableb (cost=0.29..14.13 rows=2 width=4)
Index Cond: ((a_id >= 1) AND (a_id <= 200))
Filter: (value = '1'::double precision)
-> Index Scan using tablea_pkey on tablea a (cost=0.29..8.31 rows=1 width=18)
Index Cond: (a_id = tableb.a_id)
Nested Loopになっています。
内部的にはtableAをループしながら、tableBを結合キーで検索するという結合とほとんど同じアルゴリズムのようです。
-- INを使ったサブクエリ EXPLAIN SELECT * FROM tableA WHERE a_id in (SELECT a_id FROM tableB WHERE value = 1 AND a_id BETWEEN 1 AND 200);
QUERY PLAN
-----------------------------------------------------------------------------------------
Nested Loop (cost=14.43..30.77 rows=2 width=18)
-> Unique (cost=14.14..14.15 rows=2 width=4)
-> Sort (cost=14.14..14.15 rows=2 width=4)
Sort Key: tableb.a_id
-> Index Scan using a_index on tableb (cost=0.29..14.13 rows=2 width=4)
Index Cond: ((a_id >= 1) AND (a_id <= 200))
Filter: (value = '1'::double precision)
-> Index Scan using tablea_pkey on tablea (cost=0.29..8.31 rows=1 width=18)
Index Cond: (a_id = tableb.a_id)
EXISTSを使った場合と同じになった。
INとEXISTSではどちらが速いのかという話は色んな所で色んな意見を見かけますが、少なくとも私のPostgreSQLの環境では同じでした。
他にも色々なSQLの実行計画を確認して、SQLの中で何が起きているのかを想像できるようになっておくと良いでしょう。
実行計画の読み方はDBMSによっても異なりますが、今回のサンプルである程度慣れることができるかと思います。
統計情報
オプティマイザが実行計画を作るとき、統計情報を使っています。
ここでは統計情報について解説します。
細かくはDBMSによっても変わってくると思いますが、統計情報として管理しているのは概ね次のような情報です。
- 各テーブルのレコード数
- 各テーブルの列数と列のサイズ
- 列値の値の個数
- 列値の分布
- 列内のNULLの数
- インデックス情報
オプティマイザはこれらの情報を元に実行計画を作成します。
統計情報は、DBMSが定期的に自動的に取得する場合が多いので、ほとんどの場合手動で更新する必要はないのですが、大量にデータを更新した直後などは注意が必要です。
例えば、あるテーブルにレコードが10件存在するとします。
レコードが10件の場合、1つのブロックに納まる場合がほとんどなので、DBMSはインデックスを使用せずにテーブルフルスキャンでデータを取得したほうが速いと判断するでしょう。
ここで例えば100万件のデータをインサートしたとします。
そのとき、統計情報が最新に更新されていなかったら、レコード件数は10件のままなので、本当はインデックスを使用したほうが速くデータが取れる場合でも、テーブルフルスキャンを選択してしまう可能性があります。
大量のデータを更新した直後は、統計情報を最新に更新するようにした方が良いでしょう。
統計情報を最新にするコマンド
| DBMS | コマンド |
|---|---|
| Oracle | EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => スキーマ名, TABNAME => テーブル名) |
| SQL Server | UPDATE STATISTICS テーブル名 |
| DB2 | RUNSTATS ON TABLE テーブル名 |
| PostgreSQL | ANALYZE テーブル名 |
| MySQL | ANALYZE TABLE テーブル名 |
Oracleでは、統計情報の更新が夜間に設定されているバージョンもあります。
この場合、毎晩サーバーをシャットダウンしていたりすると、統計情報がいつまでの最新に更新されないという状況になることもあります。
SQLのパフォーマンスには統計情報が大きくかかわっており、定期的に最新に更新する必要があることを認識しておきましょう。
