～性能を最大限に引き出すための設計・開発・運用～

永安 悟史

本記事は、技術評論社 WEB+DB PRESS Vol.63 で掲載されたものを、著者と出版社の許可を得て転載したものです。なお、一部 記述に変更のある箇所もあります。

【開発】開発生産性と処理性能のトレードオフ

【べからず】 ループ処理内部でSQLを発行してはいけない

アプリケーションの内部で、特定の条件でレコード数をカウントし、FORループを回してレコード数分のSELECTクエリを実行するようなコードを見かけることがあります（リスト1）。

（プログラミング言語の）関数やメソッド呼び出しのような感覚で簡単にSQLを呼び出すことができるため、このようなコードを書いてしまいがちですが、原則としてこのようなコーディングを行うべきではありません。

Webページ1画面を表示するために、SQLを100回あるいは1,000回実行するようなアプリケーションを見かけることもありますが、データベースの処理時間は「単一のSQLの実行時間×実行回数」です。仮に1回のSQL文の実行が10ミリ秒で完了する場合でも、100回ループすれば1秒、1,000回ループすれば10秒となりますので、いくら1回のSQLが速く実行できても、パフォーマンスは目に見えて劣化していきます。

このような状況を避けるには、SQLの実行回数を減らすために、面倒でもSQL文のWHERE句においてOR句やIN句を使って複数の条件を指定し、一括して複数のレコードを取り出す必要があります（リスト2）。

// idsはユーザIDを格納した配列
for (i=0 ; i<ids.length ; i++) {
  PreparedStatement pstmt =
    conn.prepareStatement("SELECT * FROM t1 WHERE id=?");
  pstmt.setInt(1, ids[i]);
  // ループの内部でSQLを実行
  ResultSet rs = pstmt.executeQuery();
  // 結果処理...
}

// idsはユーザIDを格納した配列
String id_list = "";
for (i=0 ; i<ids.length ; i++) {
  id_list = id_list + ids[i] + ",";
}
PreparedStatement pstmt =
  conn.prepareStatement("SELECT * FROM t1 WHERE id IN (" + id_list + ")");
// ループの外でSQLを実行
ResultSet rs = pstmt.executeQuery();
// 結果処理...

【べし】 データベースからのデータ取得は必要最小限に絞るべし

ほぼすべてのレコードを取得して、アプリケーション上で処理するようなコードを見かけることがしばしばあります（リスト3）。

このようなコードも、データ量が増えてくるとパフォーマンストラブルの原因になります。レコード数が数千件の場合であれば（見かけ上）パフォーマンスに問題がない場合も、数万件、数十万件と増えてくると、途端にパフォーマンスが低下します。

確かに、アプリケーション側でロジックを書いたほうが開発生産性が高くなることもありますが、このようなコードを書くと、データ量の増大やクライアント多重度の増加によってパフォーマンスが劣化していき、最悪の場合はアプリケーションサーバが「Out of Memory」で異常終了してしまうこともあります。

通信量を減らし、メモリ使用量を減らすためにも、データベースサーバから取得するデータは必要最小限に絞らなければなりません。そのSQLで最大何件のレコードが返却されるのかを考えながら、WHERE句の条件やサブクエリ、LIMIT/OFFSET句などを使って、うまく取得データ量を減らすようなコーディングを行いましょう（リスト4）。

// 最大何件返却されるかはテーブルの状況次第
PreparedStatement pstmt =
  conn.prepareStatement("SELECT * FROM t1 ORDER BY id");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
  if ( offset<=rs.getInt("id") && rs.getInt("id")<(offset+100) ) {
    // 結果処理...
  }
}

// 返却されるレコード数は最大100件
PreparedStatement pstmt =
  conn.prepareStatement("SELECT * FROM t1 ORDER BY id OFFSET ? LIMIT ?");
pstmt.setInt(1, offset);
pstmt.setInt(2, 100);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
  // 結果処理...
}

【べからず】 無闇にビューを多用してはいけない

複雑なSQLを書くのが得意でないプログラマがチーム内にいる場合、複数のテーブルを結合して簡単にアクセスできるようなビューを定義して、そのビューに対してアクセスするようにコーディングすることがあります。しかし、「難しいSQL文を書かなくて済むから」という理由でビューを多用するのは避けるべきです。

多くのRDBMSでは、複雑な結合や条件を持つビューを定義できることはもちろん、ビューに対するビューなどを定義することもできますが、これを多用していると、「アプリケーションから発行されるSQL文」と、「データを保存する実態としてのテーブル」とが乖離するようになります（図4）。

アプリケーションのSQL文とテーブルとが著しく乖離すると、トラブルシューティングやパフォーマンスチューニングの際の困難を引き起こします。多くのテーブルやビューを通して実行されているSQL文を一体どのように書き換えればパフォーマンスが改善するのか、極めてわかりづらくなります。また、安易にビューを多用するということは、裏を返せば「それぞれのビューへのアクセスの実行計画を十分に確認・吟味していない」ということになります。その結果として、RDBMSの正規化やインデックスなどの意味を損なうことにもなりかねません。

よって、ビューを多用するのではなく、あくまでもテーブルに対するSQL文を、実行計画を確認しながら実装していくことが、将来の安定運用やメンテナンスのためには重要です（図5）。

図4 ビューの多用

図5 ビューの適正な使用

次回は、試験編です。