実践ハイパフォーマンスMySQLメモ
はじめに
- 実践ハイパフォーマンスMySQLを読む中で、知らなかった単語や気になったことのメモ
- この書籍では
5.5について書かれているが、最新は5.7なので異なる点がいくつかあるのだろうと懸念はある。
とはいえ、根本的な仕組みはそうそう変わるものではないだろう。
1章
MySQLの論理アーキテクチャ
- MySQLは3つのレイヤーに分けることができる
- 接続の処理、認証、セキュリティといったネットワークベースのほとんどのクラインアント/サーバーツールで必要となるレイヤー
- 1クライアント1スレッドが割り当てられ、スレッドは1つのコアまたはCPUに関連づけられる
- サーバーはスレッドをキャッシュするので、新たな接続の度にスレッドの作成・削除は不要
- クエリ解析、分析、最適化、キャッシュ、組み込み関数などの中枢ともいえるレイヤー
- クエリのパース・書き換え、テーブル読み取り順序の決定、使用するインデックスの選択など
- ストレージエンジンのレイヤー
- 接続の処理、認証、セキュリティといったネットワークベースのほとんどのクラインアント/サーバーツールで必要となるレイヤー
MVCC(MultiVersionConcurrencyControl)- InnoDBの行ロックで単純な行ロックと異なっている
クラッシュセーフとは
- プロセスやマシンが正常に動作しなくなって再起動した後でも以前の状態に戻って処理を再開できるということ
2章
ベンチマークについて
- 各ベンチマークツールについて、ベンチマークの手法などについての説明
- 知識として持っておくものではなく、実際にベンチマークする時にこの章を手がかりに進めていくのがいいと思う。
- 普段の業務で使わず忘れてしまうので
3章
プロファイルについて
- パフォーマンスを最適化するにあたって必要なことが示されている
- この章も2章同様、その存在や概要を把握しておくに留め、必要になった段階でその詳細に入るとよい
4章
最適なデータ型
- 可能な限り小さいデータ型を選択する
- 例えば選手の背番号とかであれば、int(4バイト)ではなくて、tinyint(1バイト)でいいよね。
- 単純なデータ型を選択する
- 文字列型よりも整数型の比較コストが低い。
- 例えば、IPは整数型に直すのがよい
- 可能であれば
NULLを使用しない- インデックスや値の比較を複雑にするのでできるだけ避けるべき
- とはいえ、意味不明なデフォルト定数を用いるくらいならNULLでいい
- 外部キーになりうる値には同じデータ型を使おう
- IDにはできるだけ整数を使おう。コストを下げる
正規化と非正規化
- テーブルを正規化した結果、インデックスが上手く利用できない場合には非正規化もあり。
- その場合には、参照頻度と更新が煩雑になる手間のトレードオフになるということを認識しよう
5章
プレフィックスインデックスの適切な設定
- 非常に長い文字列にインデックスをつける場合、最初の数文字にインデックスをつけることで記憶域を節約し、パフォーマンスを改善できるかも。
- 下記の方法で最適なプレフィックスの文字数を確認できる
mysql> SELECT COUNT(DISTINCT カラム) / COUNT(*) FROM テーブル;
=> 文字列全体でインデックスを貼ったときの選択性を出す
選択性 - カラム内の個別値の数 (カーディナリティー) をテーブル内のレコード数で割ったもの
=> 結果の値が高ければ高いほどSELECT時に除外できる件数が多くなり望ましい(カラムの値の重複が少ないので、SELECTされる行も少ない)
mysql> SELECT COUNT(DISTINCT LEFT(カラム, 1)) / COUNT(*) AS 1,
-> SELECT COUNT(DISTINCT LEFT(カラム, 2)) / COUNT(*) AS 2,
-> SELECT COUNT(DISTINCT LEFT(カラム, 3)) / COUNT(*) AS 3,
-> SELECT COUNT(DISTINCT LEFT(カラム, 4)) / COUNT(*) AS 4
-> FROM テーブル;
=> 各プレフィックスの文字数ごとの選択性を出して、文字列全体でインデックスを貼った時の値に近しい値が出たところの文字数をプレフィックスにする
複数列のインデックス
- 適切な列の順序でインデックスを設定しよう
- 何が適切かは実際に発行するクエリ、インデックスに設定される各列の値の重複が少ない(カーディナリティが高い)ものを選択しよう
カバリングインデックスとセカンダリインデックス
- 別記事にまとめる
範囲条件について
- WHERE句で範囲条件を指定できる方法は下記のように2つあり、EXPAINのtypeではどちらもrangeになるが、インデックスにおいて違いが存在する
IN()を使ってリストで指定する方法- 1クエリの中に複数用いても、インデックスを使用することができる
- 不等号やBETWEENで範囲を指定する方法
- 1クエリの中に複数使用した場合、インデックスが効かなくなってしまう
- どちらも範囲条件を指定するものではあるが、前者の場合、等値条件を複数指定しているからだと考えられる
選択性は低いが常に検索条件やSELECTで指定されるカラムのインデックス戦略
- インデックスのプレフィックスに
IN()で指定する
まとめ
- 単一行へのルックアップを回避しよう
- テーブルアクセスしての検索?
- ファイルソートを回避しよう
- インデックスのみを使用するアクセスを利用できるようなインデックスとクエリを選択するようにしよう
6章
スロークエリの原因
- アプリケーションが必要以上に多くのデータを取得していないか
- MySQLサーバーが必要以上に多くの行を解析していないか
- 下記3項目を確認
- 応答時間
- 処理時間と待ち時間(I/O処理の完了や行のロックなど)からなるので正確な計測は難しい
- 調査される行と返される行の数
- 調査される行の数とアクセスタイプ
- EXPLAINの記事参照
- 応答時間
- 下記3項目を確認
クエリ再構築
- 1度に処理するのは1000行など、クエリを分割する
- JOINされているクエリを複数クエリに分割する
- ロックの競合が少なくなる可能性がある
- 結合するテーブルのうち、変更頻度が高いものが1つだけの場合など、他のクエリでクエリキャッシュを使えるようになる
クエリがどのように実行されるか
大まかな流れ。強調した部分は下で簡単に補足・解説
- クライアントがSQLステートメントをサーバーに送信
- サーバーがクエリキャッシュをチェック。ヒットしたらキャッシュの結果を返す。なければ3へ
- サーバーがSQLステートメントを
解析し、前処理を行い、最適化してクエリ実行プランを作成 クエリ実行エンジンがストレージエンジンAPIを呼び出し、クエリ実行プランを実行する
クエリ最適化プロセス(括弧内は処理する主体)
- 解析(パーサー)
- 前処理(プリプロセッサ)
- 最適化(クエリオプティマイザ)
- 最もコストの低いクエリ実行プランに変換。ただし、メモリキャッシュ、他クエリの影響などは考慮されない
- 下記のような最適化を行う
- 結合の並び替え
- OUTER JOINからINNER JOINへの変換
- 代数的等値ルールの適用
- 例) (5=5 and a>5) は、a>5 のみに
- COUNT、MAX、MINの最適化
- インデックスを有効活用すればテーブル内の全データをみなくてもよくなる
- 定数式の評価と縮小
- カバリングインデックス
- サブクエリの最適化
- 早期終了
- auto_increment の id に -1 で検索をかけるような不可能な条件などは最適化段階で終了する
第7章
業務で使われている、パーティション、プリペアドステートメント、クエリキャッシュ
パーティション
- (主に)特定カラムの範囲で、物理的にデータを区切る。
- WHERE句でその範囲が除外された時には、オプティマイザはそのテーブルを考慮しない
プリペアドステートメント
流れは下記の通り
プリペアドステートメントが効率がよい可能性がある理由
クエリキャッシュ
- クエリキャッシュとは
- まずSELECT文の完全な結果セットをキャッシュする。
後で全く同じクエリが投げられ、キャッシュされたデータが有効な場合にはクエリの解析・最適化・実行をスキップして、結果をキャッシュ内容から返却できる
- まずSELECT文の完全な結果セットをキャッシュする。
- クエリキャッシュの注意点
- キャッシュヒットの確認
- キャッシュヒット確認時、
クエリの解析、正規化、パラメータ化を行わない。
なので、大文字、小文字、スペース等も全てが一致した場合にのみヒットする - 結果を生成したクエリが決定的でない限りキャッシュされない(CURRENT_DATE()などの関数が入っている場合などはキャッシュされない)
ただし、SELECT時にキャッシュの確認は行われる
- キャッシュヒット確認時、
- クエリキャッシュはいくつかの点でオーバーヘッドになる
- 読み込みクエリは開始する前にキャッシュをチェックしなければならない
- クエリがキャッシュ可能で、まだキャッシュに存在しなければ、結果を生成してから格納する
- 書き込みクエリが変更するテーブルを使用するクエリがあれば、そのキャッシュエントリを無効にしなければならない。
- キャッシュヒットの確認
