SQLを速くするぞ―お手軽パフォーマンス・チューニング

SQLを速くするぞ

　このサイトでは、SQL を高速化するためのちょっとしたパフォーマンス・チューニングの技術を紹介します。と言っても、『プログラマのためのSQL　第2版』の受け売りがほとんどなので、この本を読んでいただければ、本稿を読む必要はありません。

　最初に、パフォーマンス・チューニングに関する全体の方針を述べておくと、それはボトルネック（一番遅いところ）を改善することです。当たり前ですが、既に十分速い処理をもっと速くしたところで、システム全体のパフォーマンスには影響しません。従って「処理が遅い」と感じたら、最初にすることは、SQL やアプリの改修ではなく、「どこが遅いのか」を調査することです。いきなりあてずっぽうで改善をはじめても効果は出ません。医者が患者を診るとき最初にすることが検査であるのと同じです。病因が何であるかを突き止めてからでないと、正しい処方はできないのです。

　その基本を承知していただいた上で、データベースに関して最もボトルネックになりやすい箇所はどこか、と聞かれたならば、それはディスク I/O であると答えましょう。現在のコンピュータの構成上、CPU による演算やメモリアクセスなどにかかるコストは、ディスク I/O のそれに比べれば微々たるものです。従って、データベースが用意している各種のチューニング手段（インデックス、パーティション、処理分散）も、煎じ詰めれば、いかにディスク I/O を減らし、その処理を高速化するか、という一点にかかっています。

　以下に紹介するいろいろなチューニング手段のうち、重要なものはやはり、ディスク I/O に関わるものです。インデックスにまつわるものがそうですし、ソートをなるべく減らすための手段もそうです。それに比べると、IN 述語の評価順序とか列番号からの読み替えなど、CPU の演算に関わるものは、効果としては小さいものです（全く効果がない、というわけではないのですが、優先度は低いと思ってください）。

　なお、以下に紹介する手段は、なるべく実装非依存で、SQL を見直すだけで手軽に実践できるものに限りました。DBMS 固有のチューニング方法については、それぞれの製品のマニュアルなどを参照してください。

サブクエリを引数に取る場合、IN述語よりもEXISTS述語を使う
BETWEENはおしゃれなアクセサリ
EXISTS述語のサブクエリ内では、SELECT * を使う
極値関数でインデックスを利用する
行数を数えるときはCOUNT(*)よりもCOUNT(列名)を使う
GROUP BY 句でインデックスを利用する
ORDER BY 句でインデックスを利用する
UNION、INTERSECT、EXCEPT には ALL を付ける
実はインデックスが使用されていないという罠。
行ポインタによるアクセスが最速
ワイルドカードは使わない
列番号は使わない
表に別名をつける
暗黙の型変換を回避する
IN述語の引数リストには、最もありそうなキーを左寄せする
ビューを濫用してはいけません

サブクエリを引数に取る場合、IN述語よりもEXISTS述語を使う

　[NOT] IN と [NOT] EXISTS は、たいていの場合、全く等しい結果を返します。しかし、この両者でサブクエリを作る場合は、EXISTS の方が圧倒的に速く動作します。例えば、有名人の誰かと同じ誕生日に生まれた社員を全て探すための SQL を考えます。以下の二つの SQL は、同じ結果を返しますが、EXISTS の方が速く動作する可能性が高くなります。

  使用テーブル：
      Personnel(社員テーブル)
      Celebrities(有名人テーブル)

    --１．遅い
    SELECT  name
    FROM    Personnel
    WHERE   birthday IN (SELECT  birthday
                           FROM     Celebrities);

    --２．速い
    SELECT  P.name
    FROM    Personnel AS P
    WHERE   EXISTS   (SELECT
                      FROM Clelebrities AS C
                      WHERE P.birthday = C.birthday);

　EXISTS の方が速いと期待できる理由は以下の二つです。

もし結合キー(この場合は birthday )にインデックスが張られていれば、Celebrities テーブルの実表は見に行かず、インデックスのみを参照する。
birthday 列がインデックスを持っていない場合でも、オプティマイザがある程度優秀なら、birthday 列をソートした一時テーブルを作り、2分探索することで、全表走査よりも効率的に検索を行なう。EXISTS は一行でも条件に合致する行を見つけたらそこで検索を打ち切るので、IN のように全表検索の必要がない。

　IN の引数にサブクエリを与える場合、DB はまずサブクエリから実行し、その結果を一時的なワーク・テーブル（インライン・ビュー）に格納し、その後、ビューを全件走査します。これは、多くの場合、非常にコストがかかります（特に大規模なビューをメモリ上に展開する場合は）。EXISTS ならば、上述のようにワーク・テーブルは作成されません。

　ただし、ソースコードの可読性という点において、IN は EXISTS に勝ります。要するに、IN で書いた方がぱっと見て意味が分かりやすいコードになります。そのため、IN を使っても十分短い応答時間が確保されているなら、その SQL を敢えて EXISTS で書き直す必要はありません。

　PostgreSQL ユーザへの付記：PostgreSQL はバージョン7.4から IN 述語によるサブクエリが速度改善され、EXISTS 述語と同じぐらい速くなったとのことです。良いニュースです。

BETWEEN はおしゃれなアクセサリ

　BETWEEN は余計な述語です。これを使わなくても =, <, > を組み合わせることで同じ条件を記述できるからです。しかし使える場面では、BETWEEN を使うと良いでしょう。なぜならその方がコードがすっきりして可読性が上がるうえに、速度改善も望めるからです。BETWEEN が使われている場合、オプティマイザは指定された範囲のインデックスのノードを1回の操作で比較することができます。一方、もし比較述語による二つの式で表現されていた場合、オプティマイザはインデックスに複数回の操作を行なわなければなりません。

　いわば、BETWEEN 述語というのは、生活必需品ではないけれど、持っているとちょっと役立つ洒落たアクセサリのようなものです。

EXISTS述語のサブクエリ内では、SELECT * を使う

　サブクエリの SELECT 句を書くには、以下の3つの選択肢があります。

    １．EXISTS (SELECT * FROM …)
    ２．EXISTS (SELECT 列名 FROM …)
    ３．EXISTS (SELECT 定数 FROM …)

　このうち、最も良いのは１．です。この書き方は、オプティマイザにどの列を使うべきかの選択を委ねることになります。そして、列にインデックスが張られていれば、実表を走査する必要はありません。

　ただし、例外的に２．や３．の方が１．よりも高速な場合もあります。古い DBMS では２．のように列名を指定した方が速いこともあります。また、Oracleその他の製品では、３．のように「SELECT 1 FROM …」など、定数を指定すると高速になります。この書き方は、行へのポインタさえ得られれば、実際の行を読む必要がないことを、DBMS に明示してやる効果があります。

　しかし、２．はもはや使う機会はないでしょうし、３．の書き方は意味的な混乱を招くので、基本的には１．を使うのが良いでしょう。

極値関数でインデックスを利用する

　最大値／最小値を求める MAX と MIN をあわせて極値関数と呼びます。この二つは集約関数の仲間なので、使うと必ずソートが必要になります。そのためソート領域を使用してパフォーマンス悪化の原因になりやすいのですが、SUM や AVG と違って、ソート処理の負荷を軽減できるケースがあります。それが、B-tree インデックスの作成されている列に対してこの二つの関数を適用したときです。

　例えば、col 列にインデックスが作成されている場合、MAX(col)、MIN(col) は、インデックスのない列に比べて処理が高速化されます。複合索引の場合でも同じで、(col_1, col_2, col_3) にインデックスがあるなら、やはり MAX(col_1) は高速化されます（ MAX(col_2) や MAX(col_3) は効果がありません）。

　その理由はもちろん、B-tree インデックスは、作成されるときにキー値によってソートされるからです。従って、テーブルではなくインデックスを走査するだけの低コストで最大／最小を求めることが可能になるのです。

行数を数えるときは COUNT(*) よりも COUNT(列名) を使う

　このトリックも B-tree インデックスを使います。したがって、これがうまく働くためには、COUNT 関数の引数となる列にインデックスが張られている必要があります。例えば、

SELECT COUNT(*)
  FROM Sales;

　よりは、次のSQLの方が速いかもしれません。

SELECT COUNT(sale_id)
  FROM Sales;

　ここで、sale_id が Sales テーブルの主キーだとすれば、当然、sale_id にはユニークなインデックスが存在します。それを利用しています。

GROUP BY でインデックスを利用する

　B-tree インデックスがソートされているという性質は、他にも応用がききます。例えば、GROUP BY 句のキーでもインデックスを利用できるのです。(col_1, col_2, col_3) にインデックスがある場合、GROUP BY col_1 や、GROUP BY col_1, col_2 といった集約キーの指定をすることで、高速なソートが行われます。DB によっては、これを利用できるのはユニーク・インデックスの場合に限られることもあるため、ご利用の DB について確認されることをすすめます。

ORDER BY でインデックスを利用する

　ORDER BY 句もソートを行う機能ですから、全く同様にインデックスを利用できます。(col_1, col_2, col_3) にインデックスがある場合、ORDER BY col_1 や、ORDER BY col_1, col_2 といった指定をすることで、高速なソートが行われます。DB によっては、これを利用できるのはユニーク・インデックスの場合に限られることもあるため、ご利用の DB について確認されることをすすめます。

UNION、INTERSECT、EXCEPT には ALL を付ける

　UNION は、二つの集合をマージします。しかし、重複行を排除するためのソートが発生するので、その分のコストがかかります。特に、物理メモリによるソートが発生すると速度が極端に遅くなります。（正確な数字ではありませんが、巷では論理メモリの100万倍遅くなると言われています。）

　重複を気にしなくてよい場合、または重複が発生しないことが事前に明らかな場合は、UNION の代わりに UNION ALL を使ってください。そうすればソートは発生しません。同様のことは、INTERSECT と EXCEPT についても当てはまります。INTERSECT ALL、EXCEPT ALL のほうが、ALL をつけない集合演算よりも高速に動作します。

実はインデックスが使用されていないという罠

　普通、ある程度大きなテーブルにはインデックスが張られています。インデックスの原理は、C言語のポインタ配列と同じです。サイズの大きなオブジェクト配列を検索するよりも、サイズの小さなポインタ配列を検索した方が効率がいい、ということです。しかも、2分探索による高速検索が可能なよう工夫されています。

　さて、col_1 という列にインデックスが張られているとします。以下の SQL はそのインデックスを使うつもりで、実のところテーブルを全件検索しています。

/* １．索引列に演算を行なっている */
SELECT * 
  FROM SomeTable
 WHERE col_1 * 1.1 > 100;

　検索条件の右側で式を用いれば、インデックスが使用されます。従って、代わりに

　　　WHERE col_1 > 100/1.1

　という条件を使えば OK です。
　あるいは、関数索引を使うという方法もありますが、不用意に使うことは薦めません。

/* ２．IS NULL 述語を使っている */
SELECT * 
  FROM   SomeTable
 WHERE  col_1 IS NULL;

　なぜなら、通常、索引データの中に NULL は存在しないからです。NULL は値ではないのです。（詳しくは3値論理を参照）

/* ３．索引列に対してSQL関数を適用している */
SELECT * 
  FROM   SomeTable
 WHERE  SUBSTR(col_1, 1, 1) = 'a';

　理由は「１．索引列に演算を行っている」と同じです。インデックスの中に存在する値は、あくまで「col_1」の値であって、「SUBSTR(col_1, 1, 1)」の値ではないのです。

/* ４．否定形を用いている */
SELECT *
  FROM   SomeTable
 WHERE  col_1 <> 100;

　否定形(<>, NOT EQUAL, NOT IN)はインデックスを使用できません。

/* ５．ORを用いている */
SELECT * 
  FROM   SomeTable
 WHERE  col_1 > 100  OR col_2 = 'abc';

　OR は極力 IN で置き換えましょう。IN ならば索引が使用できます。どうしても OR を使いたいならビットマップ索引を張りましょう。

/* ６．後方一致、または中間一致のLIKE述語を用いている */
× SELECT  *   FROM  SomeTable  WHERE  col_1  LIKE '%a';
× SELECT  *   FROM  SomeTable  WHERE  col_1  LIKE '%a%';
○ SELECT  *   FROM  SomeTable  WHERE  col_1  LIKE 'a%';

　LIKE 述語を使うときは、前方一致検索のみ索引が使用されます。もしどうしても後方一致検索で索引を利用したい場合、REVERSE 関数と関数索引を組み合わせるという方法もあります。どんな風にすればいいか、考えてみてください。

/* ７．暗黙の型変換を行なっている */
char型で定義されたcol_1に対する条件を書く場合の例：

× SELECT * FROM SomeTable WHERE col_1 = 10;
○ SELECT * FROM SomeTable WHERE col_1 = '10';
○ SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

　暗黙の型変換は、オーバーヘッドを発生させるだけでなく、インデックスまで使用不可になります。百害あって一利なしです。

/* ８．複合インデックスの場合に、列の順番を間違えている */
　col_1, col_2, col_3 に対して複合インデックスが張られているとします。
　その場合、条件指定の順番が重要です。

　○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;
　○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 ;
　× SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 500 ;
　× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ;
　× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_1 = 10 ;

　必ず最初の列（col_1）を先頭に書かねばなりません。順番も崩してはいけません。
　このルールを守れない場合は、別々のインデックスに分割しましょう。複合インデックスよりパフォーマンスが落ち、消費する表領域も大きくなりますが、仕方ありません。最近の DB には、この順番を無視してもインデックスを利用してくれる柔軟なものもありますが、それでも順番を守った場合ほどのパフォーマンスは出ません。

行ポインタによるアクセスが最速

　もし rowid（Oracle）、oid（PostgreSQL）といった行ポインタの存在を知らなければ、今すぐに

  SELECT  rowid
  FROM    SomeTable;

　という SQL を実行してください。

ROWID
------------------
AAAF+OAAIAAABmMABI
AAAF+OAAIAAABmMABK
AAAF+OAAIAAABmMABL

などの列が選択されるはずです。　行ポインタは、どのテーブルでも必ず持っている擬似列であり、そこに格納されている値はレコードの論理アドレスです。インデックスもこれを使用しています。行ポインタは、セッションが終了すると変化するかもしれませんが、同一セッション中は不変であり、特に Oracle では常に最速のアクセスが保証されます。主に自己結合や自己相関サブクエリの時に力を発揮します。応用例として、「重複行を削除する」を参照してください。

　なお、DB2、SQLServer、MySQL には、こういう行ポインタは実装されていません。実装依存の技術ですが、便利なので特別に紹介しておきます。

ワイルドカードは使わない

　ワイルドカード("*")で全列を指定すると、実行時に実際の列名への読替えが発生し、オーバヘッドが増加します。しかも、実際には必要のない列まで結果セットに含める必要があるため、無用のメモリまで使用することになり、二重に無駄です。なるべく不要な列を選択しないよう心がけましょう。その方がソースの可読性も上がり、仕様変更にも強くなってなおよしです。

× SELECT * FROM SomeTable;
○ SELECT col_1, col_2, col_3 FROM SomeTable;

列番号は使わない

　ORDER BY 句ではソートのキー列として、実際の列名の代わりに列番号を指定できます。動的に SQL を生成する場合などに重宝する機能ですが、パフォーマンス面では列名への読替えが発生するためマイナスです。おまけにこの機能は SQL-92 で「将来削除されるべき機能」のリストに挙げられました。そのため、ソースの保守性の観点からも使用してはいけません。

× SELECT col_1, col2 FROM SomeTable ORDER BY 1, 2;
○ SELECT col_1, col2 FROM SomeTable ORDER BY col_1, col2;

表に別名を付ける

　テーブルに別名を指定すると、解析時にどの列がどのテーブルに属するかの判定を省略できます。特に複数のテーブルを使う SQL で効果的ですが、単純な SQL でも大量に発行する場合は有効です。

× SELECT col_1, col2 FROM SomeTable ;
○ SELECT S.col_1, S.col2 FROM SomeTable S ;

暗黙の型変換を回避する

　多くのデータベースでは、データ型と代入値が一致しない場合、型を変換して代入が可能であれば暗黙の型変換が行なわれます。「気の利いた」機能ですが、暗黙の型変換は一度代入に失敗した後に行なわれるため、オーバーヘッドが発生します。しかもインデックスが使用されなくなります。面倒がらずに型を意識してコーディングしましょう。

--文字型で定義された col_1 に対するSQL：
× SELECT col_1 FROM SomeTable WHERE col_1 = 10;
○ SELECT col_1 FROM SomeTable WHERE col_1 = '10';
○ SELECT col_1 FROM SomeTable WHERE col_1 = CAST(10 AS CHAR(10));

IN 述語の引数リストには、最もありそうなキーを左寄せする

　なぜなら、IN は、左から右へ引数を評価し、見つかった時点で true を返しそれより後の引数は見ないからです。以下の二つの SQL を比較してください。

    --１．遅い(かもしれない)
    SELECT *
      FROM  Address  
     WHERE prefecture IN ('鳥取', '徳島', '東京', '大阪');

    --２．速い(かもしれない)
    SELECT *
      FROM  Address  
     WHERE prefecture IN ('東京', '大阪', '鳥取', '徳島');

このような、一度条件判定が真に評価されたらそれ以後の式評価を行なわない方式を「短絡論理和」と呼びます。CASE 式の評価この方式を使っているので、やはり同様のチューニングが可能です。プログラムの高速化の手法としてよく使われるので、覚えておくとよいでしょう。

ビューを濫用してはいけません

　ビューはとても便利な道具ですから、日常的に多くの人が活用していると思います。しかし、安易に複雑なビューを定義することは、パフォーマンス面では大きなマイナスになります。オプティマイザは、「ビュー定義のクエリ + ビューに対して発行されたクエリ」のセットを、可能な限り効率的な一つのクエリに変換して実行しようとします。しかし、ビュー定義のクエリに以下のような集約操作が含まれている場合、非効率的な SQL になり、思わぬ速度低下を招くことがあります。

OLAP 関数（RANK, DENSE_RANK, ROW_NUMBER 等）
集約関数（AVG, COUNT, SUM, MIN, MAX）
集合演算子（ UNION, INTERSECT, EXCEPT 等）

　基本的に、ビューで集約をしていたら要注意、ということです。最近では、ビューのこのような欠点を補うために、インデックスの作成可能なビューや、マテリアライズド・ビューなどの技術を実装する DBMS も増えてきました。これらを使用することも一案です。（「ビューの功罪」も参照。）

戻る