検索の最適化についての覚え書き

PHP MySQL

私本管理Plusの開発者さんが行っている書籍保管計画で検索速度に悩んでいらっしゃるようなので、ウチの東方CDデータベース構築時に行った検索の最適化を覚え書きとして残しておく。以下のバージョンについての記述はMySQLのバージョン。

  • 適切なストレージエンジンを選択する
    ウチのデータベースの場合は自分しかデータ更新を行わないのでトランザクションを考慮する必要が無く、MyISAMエンジンを使用。Ver5からはデフォルトがInnoDBになっているので変更を忘れないこと。但し、こちらの記事によればメモリに余裕があれば、Memory(昔のHeap)エンジンが最速らしい。リブートするとテーブルが消えてしまうので一工夫必要だが。
  • クエリの発行回数を減らす
    PHPとDBサーバとのやり取りは同一マシン上であっても結構実行コストが高い。なので、出来る限りクエリの発行回数を抑える。Ver4.1以上であればサブクエリを使用したり、Ver5であれば、mysqli_multi_queryやストアドプロシージャの使用を考慮。とりあえずDBの乗り換えは考えていないのでMySQLi系のネイティブ関数を使用。5.0.24になってからWindowsのバイナリパッケージではMySQLクライアントがVer5(5.0.22まではVer4.1だった)になっているので、ほぼ全ての構文は大丈夫だろう。PDOはVer4までの対応なのでいまいち(MySQL関数相当)。Pear::DBやADOdbなどによるクエリキャッシュの効果は未確認だが単体の実行速度はネイティブ関数より遅い。まぁキャッシュが効くには同じクエリが何度も発行されるような環境であることが前提だろう。
    ストアドファンクションでは実行時にテーブルロックが発生するとの情報あり。ストアドプロシージャでも同等の処理が可能なのでどちらを使用するかは設計次第。
  • プリペアドステートメントの使用を考慮する
    構文が変わらず、検索するキーだけが異なるクエリを繰り返し発行する場合はプリペアドステートメントの使用を考慮する。200回のクエリ発行でウチの環境では実行時間が約1/3〜1/4になった。
  • インデックスは適当か
    EXPLAIN文を使用してインデックスの妥当性をチェックすること。フルテーブルスキャンになっている場合はインデックスの追加、検索条件の見直しが必要。特にテーブルを結合している場合は注意。油断するとデカルト積で検索件数が増加してしまう。
  • 正規化はきちんと行う
    一度第三正規形にまでして、その後検索速度向上のため、冗長なフィールドを追加。データ更新する際はマスタテーブルを修正した後、冗長なフィールドを更新するバッチを実行するように運用。基本的にデータの削除はないのでこの方法で問題は無い。
  • 本当にPHPが遅いのか？
    行数の多いテーブルを表示しようとすると、ブラウザでの表示速度が問題となる。各処理でスクリプトの開始からの経過時間を確認しながらどこがボトルネックなのかを確認すること。ウチのデータベースの曲一覧は何の工夫もしていないので、1500件ぐらいまとめて出力されるため極めて遅い。ただ1/30ページとかってのはイヤなんでどうしようかと。

うーん、ストアドプロシージャの実行権限の変更の仕方が分かっていない。実行するユーザで作成している分には問題ないけど、rootユーザが作成したストアドプロシージャを他のユーザが利用するにはどうしたらいいんだろう。早くMySQL 5.0のリファレンスマニュアルが日本語化されないものだろうか。