読んだ理由

• MySQL の index といったパフォーマンスに関する情報を学びたい

• DB に得意な人がいなくなるため、知識をつけておきたい

学んだことを3行で

• パフォーマンススキーマをよく見る

• デフォルトの設定は意味があるため、サーバの設定は最低限とし複雑は変更は行わない

• サーバの設定よりも、クエリチューニングを行う

メモ

Chapter 3. Performance Schema

• パフォーマンススキーマを有効にしたら調査に有効

• 設定を有効にするにはサーバの起動が必要

• バージョン5.7以降
• パフォーマンス・スキーマはデフォルトで有効になっており、ほとんどのインスツルメンテーションは無効になっている
• グローバル、スレッド、ステートメント、トランザクションのインスツルメンテーションのみが有効になっている

• バージョン8.0以降、メタデータ・ロックとメモリ・インスツルメンテーションもデフォルトで有効になっている

Chapter 5. Optimizing Server Settings

• ハードウェアだけでなく、ワークロード、データ、およびアプリケーションの要件に合わせてサーバを構成する必要がある

• 変更可能な設定が多数あるが、変更すべきではない
• 重要な設定はごくわずかなので基本的な設定を正しく行い、スキーマの最適化、インデックス、クエリの設計に時間をかける方がよい
• デフォルト設定には十分な理由がある
• 悪い点を改善するために設定オプションにアプローチすることはお勧めしない
• 何かを改善する必要がある場合、それはクエリの応答時間に現れるはずであり、設定オプションではなくクエリとその応答時間から検索を始めるのがベスト
• ベンチマーク・スイートをセットアップし、最適な設定を求めて設定を繰り返し変更しながらサーバーを「チューニング」することを期待されていかもしれないが、ほとんどの人にお勧めできることではない
• 多くの労力と調査を必要とし、ほとんどの場合、潜在的な見返りは非常に小さいため、時間の大きな無駄になりかねない
• その時間をバックアップのチェックやクエリプランの変更の監視など、他のことに費やすべき
• 比例チューニングはすべきではない

• 最小構成の設定は以下

• MySQL8.0 では innodb_dedicated_server というオプションが導入された
• サーバーで利用可能なメモリを調べ、以下4つの追加変数を専用データベースサーバー用に適切に設定(ここは比例チューニングでも良いということ？)
• innodb_buffer_pool_size
• innodb_log_file_size
• innodb_log_files_in_group
• innodb_flush_method

• innodb_buffer_pool
• パフォーマンスにとって最も重要な変数
• index のキャッシュ、行デーア、ハッシュインデックス、ロックなどの内部構造も保持
• サイズが大きすぎると、シャットダウンやウォームアップ時間に影響する

• I/O において、通常の使用で変更すべき最も重要な点は以下
• InnoDBログファイルのサイズ
• トランザクションのコミット・コストを削減するためにログを使用
• 各トランザクションのコミット時にバッファプールをディスクにフラッシュする代わりに、トランザクションをログに記録
• トランザクションがデータやインデックスに加えてディスクにフラッシュする変更はランダムI/Oになり遅い
• ランダムI/OよりもシーケンシャルI/Oの方が高速であるため、シーケンシャルI/Oに変換するためにログファイルを使用して書き込む
• InnoDBがログバッファをフラッシュする方法
• InnoDBが何らかのデータを変更すると、その変更の記録をログバッファに書き込み、それをメモリに保持する
• バッファが満杯になった時、トランザクションがコミットした時、または1秒に1回のどちらか早い方に、バッファをディスク上のログファイルにフラッシュする
• デフォルトでは1MBであるバッファサイズを大きくすると、大きなトランザクションがある場合にI/Oを減らすのに役立つ
• バッファサイズを制御する変数は innodb_log_buffer_size
• ハイパフォーマンスなトランザクションを必要とする場合の最良の構成は、innodb_flush_log_at_trx_commitを 1に設定したままにしておき、ログファイルをバッテリバックアップされた書き込みキャッシュとSSDを持つRAIDボリュームに置くことで、安全かつ非常に高速となる
• InnoDBがI/Oを実行する方法

• 同時実行設定
• max_connections
• 通常発生すると思われる負荷と、サーバーへのログインや管理を可能にする安全マージンを考慮して、十分に高く設定
• max_used_connections を確認し、経過を見る

• innodb_file_per_tableを使い、共有テーブルスペースのサイズに上限を設定することを推奨

Chapter 6. Schema Design and Management

• オンラインスキーマ管理ツール
• Flyway
• Percona
• Skeema

• MySQL5.6 でノンブロッキングスキーマ変更が導入
• バージョン8が GA になるまで、DDL サポートは拡張されたが、まだ万能ではない
• オンラインDDL がサポートされていたとしても、テーブルサイズが大きい場合テーブル変更のログ・ファイルが大きくなりすぎるとロールバックが発生してしまう

• スキーマ変更外部ツール
• pt-online-schema-change
• 新しいテーブルを作成し、トリガーを使って同期
• rename して新旧テーブルを切り替える
• 注意点
• トリガーに関する制限がある
• トリガーはパフォーマンスに影響する
• トリガーの制限により、同じテーブルに対し複数のスキーマ変更を実行できない
• gh-ost
• binlog を利用する
• 外部キーがない場合に有効

Chapter 7. Indexing for High Performance

• インデックスは、データ構造でストレージエンジンが行を素早く見つけるために使用

• インデックスの最適化は、クエリのパフォーマンスを向上させる最も強力な方法である

• 複数のカラムにインデックスを作成する場合、MySQL はインデックスの左端の接頭辞に対してのみ効率的な検索を行うことができるため、カラムの順序は非常に重要である
• 2 つのカラムにインデックスを作成することは、2 つの別々の単一カラムインデックスを作成することとは異なる

• ORM は論理的かつ構文的に正しいクエリを作成するが、主キー検索などの最も基本的なタイプのクエリだけに使用しない限り、インデックスフレンドリーなクエリを作成することはほとんどない

• インデックスの種類
• B-tree インデックス
• B+tree は innodb のデフォルト
• 以下のクエリでテーブルを作成した場合、key オプションで指定している last_name, first_name, dob の複合インデックスをあわせて作成している
• key オプションは index オプションのシノニムであり同義である
• アダプティブハッシュインデックス
• あるインデックス値が非常に頻繁にアクセスされていることに気付くと、B-treeインデックスの上にそのインデックス用のハッシュインデックスをメモリ上に構築する
• これにより、B-Treeインデックスには、非常に高速なハッシュ検索などのハッシュインデックスの特性が与えられる
• このプロセスは完全に自動的なものであり、制御したり設定したりすることはできないが、無効にすることはできる
• B-tree インデックス は、完全なキー値、キー範囲、キー接頭辞による検索に有効
• index はルックアップ（値の検索）と ORDER BYクエリ（ソートされた順序で値を検索）の両方に使用できる
• フルテキストインデックス
• 全文検索(文章中の単語を見つけるような場合)で効果を発揮する

• index の利点
• サーバがテーブルの目的の位置に素早く移動できる
• order by や group by でも利用できる
• ランダム I/O をシーケンシャル I/O に変える

• プレフィックス index
• 先頭N文字を対象にする
• サンプルクエリ

• 複合インデックス
• 多くのカラムに個別にインデックスを付けても、パフォーマンス向上には繋がらない
• 個別での複数 index があると、index merge が発生する
• index_merge は、うまくいくこともあるが index が不十分なテーブルを示すことが多い
• EXPLAINでインデックスマージが表示された場合、クエリとテーブル構造を調べ、これが本当に最善かどうかを確認する必要がある
• 複合インデックスでは列の順番が重要
• 以下のクエリ実行結果の場合、customer_id を最初のインデックスに配置すべき(幅が狭くなる方を優先)
• 最初に定設定するインデックスはカーディナリテイが高いカラム（絞り込めるレコード数が多い）の場合に、足きりが多くできるので一気に対象レコードを絞り込めるメリットがある

• カバリングインデックス
• インデックスでカバーされるクエリ
• Extra列に "Using index "と表示される
• unique key を定義した場合、自動的に index が作成される

• インデックスを増やしたテーブルへの新しい行の挿入は遅くなる
• 新しいインデックスを追加すると、INSERT、UPDATE、DELETE操作のパフォーマンスに影響が出る可能性がある

• sys スキーマは table_io_waits_summary_by_index_usage テーブルのビューを作成し、どのインデックスが未使用であるかを知ることができる

• ファイルソートは常にファイルシステム上のファイルを使用するわけではなく、メモリ内でデータをソートできない場合のみディスクに移動する