実践ハイパフォーマンスMySQL 5章まで読了
1/4に立てたはじめの目標はなんとか達成。もちろん全部の内容を完全に把握したわけではないけど、これまで仕事を進めながら上司やネットから断片的に得ていた知識を結びつけることができたと思う。
逆に言えば、おそらく仕事を始めた去年の5月とか6月の段階でこれを読んでもあまり納得いかなかったか、読むのにもっと時間がかかったはず。パフォーマンス向上のためのクエリ最適化やインデックスの検討、全文検索インデックスの使用、キーキャッシュ等のサーバ設定の変更を実際にやってきたおかげで、復習+αの内容として読むことができた。
というわけで自分にとっての+αをメモっとこう。
1章 MySQLのアーキテクチャ
MySQLの大きな特徴にクライアントからの接続を受けるサーバと、データを実際に格納するストレージエンジンが独立していることがあげられる。サーバが実行する処理とストレージエンジンが行う処理を把握しておかないと、何が共通機能で何がストレージエンジン固有のものなのかを判断できない。例えばインデックスの作成はストレージエンジンに任せられるのでサーバ共通ではない。
この特徴と共に把握しておきたいのが以下の処理の流れ(詳細は4章)。
- クライアントがクエリを投げる
- MySQLサーバはクエリキャッシュに載っているかどうかチェックする
- キャッシュが使えなければパーサーに渡す
- クエリオプティマイザが実行プランを立てる
- クエリ実行エンジンがストレージエンジンに対してAPI呼び出しする
- クエリキャッシュにセットしてクライアントに結果を返す
MySQLの主なストレージエンジンと言えばMyISAMとInnoDBの二つが有名だが、どちらを選択すべきかという問題はまず機能面(トランザクションや全文検索)で検討し、その後速度面で考えるのが良い。(私見:速度については自明ではないことも多いので、次の章で述べているベンチマークを取るのが良いということだと思う)
これ以外にもさまざまなエンジンを標準装備しており、場合によっては別のエンジンを選択した方が良いこともある。例えばデータを取得する際に一時テーブルを作成する場合に使われるのがMemoryエンジンだが、メモリに収まらない場合はディスク上にMyISAMテーブルを作成する。
2章 ボトルネックの検出
ベンチマークは「パフォーマンスはどれくらいか」という質問に答え、プロファイリングは「パフォーマンスがそうなるのはなぜか」という質問に答える
ベンチマークは基本的に既存のツールを使うのが良い
単一コンポーネントツール
- mysqlslap
- sysbench
- Database Test Suite
- MYSQL Benchmark Suite
- Super Smack
プロファイリングはアプリケーション全体のプロファイリングとMySQLのプロファイリングに分けられる。アプリケーション全体のプロファイリングではログクラスを作るなどして処理の各所で実行時間を計測し、ユーザー時間・システム時間・SQLクエリ時間・ネットワークI/Oなどに分けて検討する。SQLクエリ時間が大きな割合を占めている場合は次のMySQLプロファイリングを行う。
MySQLのクエリプロファイリングでは以下の2種類。もちろんEXPLAINも使う。
- SHOW STATUS LIKE〜クエリでCom_*, Select_*, Sort_* などの変数を参照することでクエリの実行に際して作成した一時テーブルの数などが確認できる
- SET profile=1; SHOW PROFILES;で1つのクエリがそれぞれの処理で要した時間を参照する
3章 スキーマの最適化とインデックス
インデックスについて
一般的にインデックスと言えばBツリーインデックス*1で、インデックスツリーのリーフノードから行へのポインタが存在する。ただし、InnoDBはクラスタ化インデックスを構成し、インデックスと行データを合わせて同じ構造に格納する。どういうことかというと、MyISAMではディスク上におけるインデックスの場所と行データの場所が離れているが、InnoDBでは(プライマリ)インデックスと合わせて主キーの順番で行データが隣接している。
また、InnoDBでは主キー以外のインデックス(セカンダリインデックス)はリーフノードで行へのポインタを持たずに該当する主キーの値を保持している。そのためセカンダリインデックスを使う場合はデータを参照するまでに2度インデックスツリーをたどることになる。(私見:主キーはNOT NULL制約とUNIQUEインデックスを組み合わせたものと等価であるとどこかで読んだ覚えがあるが、InnoDBにおいてはそうではない)
インデックスはリーフノードに該当する列のデータも含むため、参照クエリによってインデックスが付いた列のみを参照する場合は行データを参照しなくて良い。この場合のインデックスをカバリングインデックスと呼ぶ。
InnoDBでは上記の特性もあり、セカンダリインデックスを使った場合に主キーの値もインデックスから取得することができる。
MyISAMでは文字列値をプレフィックス圧縮してインデックスを作成するため、インデックスのサイズを減らし、メモリに収まるインデックスの数を増やすことができる。
インデックスの使い方
EXPLAIN SELECT actor_id FROM actor WHERE actor_id > 45
EXPLAIN SELECT actor_id FROM actor WHERE actor_id IN (1,2,3)
はどちらも type:range を返すが、1つめは範囲条件であり、2つめは複数の等価条件であり、インデックスアクセスの振る舞いが異なる。範囲条件はインデックスの他の列を無視させるが複数の等価条件にはその制限がない。そのため、範囲条件を等価条件に変換したクエリを生成するという手法がある。
(私見:なんとなくわかったようなわからないような感じ)
続きはまた明日。
4章 クエリパフォーマンスの最適化
1章でも簡単なクエリ実行パスは載っていたけど、もう少し詳細な図が4章に載っているのでこれは頭に入れておきたい。この章ではクエリパフォーマンスを向上させるさまざまな方法が載っているが、特に驚いたのはクエリキャッシュが1バイトでも違うとキャッシュを使えないということ。空白のたたみ込み、スキーマ名の省略等をしても意味的には変わらないのでキャッシュを使ってくれるだろうと思い込んでいたのだが、実はそうではなかった。
MySQLのクエリオプティマイザはjoinの順番等についてベターな方策を選んでくれるが、開発者がすでに把握している情報(ヒューリスティクス)を使ってより良い解を選択できる可能性もある。そういう場合はオプティマイザに与えるヒントをSQLに付加して実行する。
ユーザ定義変数を使うとSQLだけで実現可能な幅が少し広がりそうではあるが、この奇妙な動作をする機能を使いたいとは思わない……。