【技術書】実践ハイパフォーマンスMySQL 第3版 〜毎週アウトプットチャレンジ⑨〜

毎週 １冊技術書を読んでブログでアウトプットするチャレンジの第９弾です！

今回は、実践ハイパフォーマンスMySQL 第3版 を読んでまとめました。

MySQLの詳細を解説している技術書で800ページありました...！

少し長いですが、今後実務でMySQLを使った時に、「あ！あの本に書いてあったことだ」とこの本に戻れるように、出来るだけ多くのキーワードに触れるように書いています。

実践ハイパフォーマンスMySQL 第3版

1章 MySQLのアーキテクチャと歴史

• MySQLのアーキテクチャは3つのレイヤに分かれている
• • 1つ目のレイヤには、MySQL特有ではないものが含まれている。接続の処理、認証、セキュリティなど、ネットワークベースのほとんどのクライアント／サーバーツールで必要となるものである
  • 2つ目のレイヤはMySQLの中枢部とも言える部分であり、クエリ解析、分析、最適化、キャッシュ、および全ての組み込み関数（日付、時間、算術演算、暗号化など）のコードが含まれている。ストアドプロシージャ、トリガ、ビューなど、ストレージエンジン全体で提供される機能は全て、このレベルに属している
  • 3つ目のレイヤには、ストレージエンジンが含まれている。それらはMySQLの中に格納される全てのデータの保存と取得を受け持つ
• MySQLはクエリを解析して内部構造（解析ツリー）を作成した後、様々な最適化を適用している。これには、クエリの書き換え、テーブルを読み取る順序の決定、使用するインデックスの選択などが含まれる。最適化の様々な側面についてサーバーに説明を求めることもできる。これにより、サーバーがどのような決断を下すのかが明らかとなり、クエリ、スキーマ、設定を見直すことができる
• あるクライアントがデータを変更している間、別のクライアントがそのデータを読み取らないようにしなければならないので、ロックが絶え間なく発生する。MySQLのストレージエンジンは、独自のロックポリシーと粒度を実装できる
• • テーブルロック：最も基本的で最もオーバーヘッドが低い
  • 行ロック：並行性が最も高く、オーバーヘッドが最も高い
• MySQLのトランザクショナルストレージエンジンのほとんどは、単純な行ロックメカニズムを使用しない。代わりに、行レベルのロックとMVCC（MultiVersion Concurrency Control）と呼ばれる並行性を高める手法を組み合わせて使用する

 

2章 MySQLのベンチマーク

• ベンチマークとは、システムにストレスをかけるように設計された作業のこと
• ベンチマークは、負荷がかかっている時のシステムの振る舞いを観察し、システムのキャパシティを特定し、どの変更が重要であるかを学習し、様々なデータを使ってアプリケーションの性能を調べるのに役立つ
• ベンチマークを開始する前に、「新しいインデックスは現在のものよりも上手く動作するか」のような質問を目標として設定する
• ベンチマークシステムは既存システムを利用すべきで、独自に作成するべきではない

 

3章 サーバーのパフォーマンスのプロファイリング

• パフォーマンスは応答時間で定義するのが最も効果的
• 最適化する際は、応答時間に多くの時間が費やされている場所を計測することに90%以上の時間を割くべき
• 計測はデータベースではなく、アプリケーションから開始するべき
• 詳細な計測によって分析しきれないほど大量のデータが生成されるため、プロファイルが必要。プロファイルは、重要な情報を浮き彫りにし、どこから手を付ければよいかを判断するためのツールである
• プロファイリングは、タスクと所要時間を計測する作業と、結果を集約してタスクを重要なものから並べていく作業の2つの手順に分かれている
• New Relicは優れたプロファイリングツールである。Webブラウザからアプリケーションコード、データベース、その他の外部呼び出しまで、様々なユーザーエクスペリエンスを、実際の稼働環境でも診断することができる
• スロークエリログは、クエリの実行時間を計測するための、最もオーバーヘッドが少なく、最も信頼できる方法である
• スロークエリログからプロファイルを生成するのは、ログ分析ツールが必要である
• 未知の問題が発生した場合は、大きく分けて2種類の原因がある。サーバーが処理に追われていてCPUサイクルを大量に消費している、もしくはリソースが解放されるのを待機していることが考えられる

 

4章 スキーマとデータ型の最適化

• インデックスをつける列にはNULL値を格納できないようにするのがベター
• TIMESTAMPとDATETIMEは似ているが、TIMESTAMPはDATETIMEよりもストレージ効率がよいため、TIMESTAMPを使用すべき
• 正規化はよいことであるが、（ほとんどの場合はデータが重複する）非正規化が実際には必要で、役立つこともある

 

5章 インデックスによるパフォーマンスの向上

• インデックスの最適化は、クエリのパフォーマンスを改善するための最も効果的な方法
• ほとんどの場合、MySQLではB木インデックスを使用することになる。その他のインデックスは特殊な目的に適している
• インデックスの利点
• • サーバーが調べなければならないデータの量が少なくなる
  • サーバー上でのソートや一時テーブルが不要になる
  • ランダムI/OがシーケンシャルI/Oに変わる
• 値全体ではなく最初の数文字にインデックスを付けると、記憶域を節約し、パフォーマンスを改善できることがある

 

6章 クエリのパフォーマンスの最適化

• クエリのパフォーマンスが不十分であるとしたら、最も基本的な理由は、クエリが操作するデータが多すぎることである。効率の悪いクエリのほとんどはアクセスするデータが少なくするように変更できる。以下手順で分析する
• • アプリケーションが必要以上に多くのデータを取得していないかどうかを確認する
  • MySQLサーバーが必要以上に多くの行を解析していないかどうか調べる
• MySQLがクエリを実行するためのプロセス
• • クライアントがSQLステートメントをサーバーに送信する
  • サーバーがクエリキャッシュをチェックする。ヒットした場合は、キャッシュに格納されている結果を返す。ヒットしなかった場合は、SQLステートメントを次のステップに渡す
  • サーバーがSQLステートメントを解析し、前処理を行い、最適化して、クエリ実行プランを作成する
  • クエリ実行エンジンがストレージエンジンAPIを呼び出し、クエリ実行プランを実行する
  • サーバーが結果をクライアントに送信する

 

7章 MySQLの高度な機能

• パーティションテーブルとは、複数の物理サブテーブルを1つの論理テーブルとして組み合わせたもの
• ビューはデータを一切格納しない仮想テーブルであり、テーブル内のデータはビューへのアクセス時に実行されるSQLクエリによって生成される
• トリガ、ストアドプロシージャ、ストアドファンクションという3つの形式でコードをサーバーに格納できる
• クエリキャッシュは、完了したクエリからクライアントに返された正確なデータを保持する。クエリキャッシュでヒットした場合、サーバーはクエリキャッシュに格納されている結果をそのまま返せばよく、解析、最適化、実行の3つのステップを省略できる

 

8章 サーバー設定の最適化

• サーバー設定ファイルを出発点として、サーバーとワークロードに合わせて基本オプションを設定し、安全性オプションと健全性オプションを適切に追加し、必要に応じてInnoDBプラグインを設定する
• 何かがうまく行かなくなった場合、サーバーのプロファイリングに現れる

 

9章 オペレーティングシステムとハードウェアの最適化

• MySQLサーバーのパフォーマンスは、システムの最も弱い部分によって決まってしまう。MySQLサーバーを実行するオペレーティングシステムとハードウェアがその制限因子となることが多い
• MySQLには、CPU、メモリ、ディスク、ネットワークリソースの4つの基本リソースが必要である。ネットワークが深刻なボトルネックとなって表れることはまずないが、CPU、メモリ、ディスクがボトルネックになることはある

 

10章 レプリケーション

• レプリケーションは、あるサーバーのデータを別のサーバーのデータと同期させることである。複数のレプリカが1つのマスターに接続し、マスターと同期することもできる。そして、レプリカがマスターの役割を兼ねることもできる
• マスターのバイナリログに変更を記録し、そのログをレプリカで再生するという仕組みで動作するようになっており、非同期である
• 一般に、レプリケーションはマスターのオーバーヘッドをそれほど増加させない
• レプリケーションは、読み込みクエリを複数のサーバーに分散させるのに役立ち、読み取り主体のアプリケーションにうってつけであり、負荷分散できる

 

11章 MySQLのスケーリング

• スケーラビリティとは、負荷の増加に対処するためにリソースを追加した時に、システムがその投資に見合うだけの価値をもたらす能力のこと
• キャパシティとは、一定時間内に処理できる作業量を表す
• スケーラビリティは、リソースを追加することによりキャパシティを増やす能力と言い換えることもできる
• より高性能なサーバーを導入することは垂直スケーリング、またはスケールアップと呼ばれ、複数のコンピュータにわたって作業を分割することは水平スケーリング、またはスケールアウトと呼ばれる
• データシャーディングは現在、非常に大きなMySQLをスケーリングするための最も一般的で優れた手法であり、データを小さく分割して、それらを別々のノードに格納する
• 急速な成長が見込まれる一般的なアプリケーションのスケーラビリティ戦略は次のようになる。1台のサーバーから読み取りレプリカを使用するスケールアウトアーキテクチャにへ移行し、さらにシャーディングや機能分割を行う

 

12章 高可用性

• 高可用性とはダウンタイムが少ないこと
• 高可用性は2つの手法によって実現される
• • ダウンタイムの原因を回避すること。ダウンタイムの原因の多くは、適切な設定、監視、人為的エラーを防ぐためのポリシーや予防措置といった手段を通じて簡単に回避できる
  • ダウンタイムが発生したら、直ちにリカバリできるようにする。通常は、冗長性やフェイルオーバー機能をシステムに組み込むという方法がとられる
• 高可用性のこれら2つの側面は、平均故障期間（MTBF）と平均修復時間（MTTR）の2つの数値として計測できる

 

13章 クラウドでのMySQL

• クラウドでのMySQLの分類
• • IaaS（Infrastructure as a Service）：クラウドで仮想サーバーリソースを購入し、そこにMySQLインスタンスをインストールして実行できる。MySQLとオペレーションシステムは自由に設定できるが、実際のハードウェアにはアクセスできず、それについて知ることはできない
  • DBasS（Database as a Service）：MySQL自体がクラウドで管理されるリソースとなりわMySQLにアクセスするための資格情報がユーザーに提供される。MySQLの一部の設定は指定できるが、実際のオペレーティングシステムや仮想サーバーインスタンスにはアクセスできず、それについて知ることはできない
• クラウドのメリットは、柔軟性や事前コストの削減、製品化までの工期短縮などがある

 

14章 アプリケーションレベルの最適化

• 負荷の高いアプリケーションにとって、キャッシュはきわめて重要である。一般的なWebアプリケーションは、キャッシュするコストよりも生成するコストの方がはるかに高いコンテンツを大量に提供するため、通常はキャッシュを通じてパフォーマンスを桁違いに向上させることができる
• キャッシュは、パッシブキャッシュとアクティブキャッシュの2つに分けられる。
• • パッシブキャッシュは何かをリクエストすると結果が返されるか、それは存在しないというメッセージが返される。memcachedはパッシブキャッシュの一例
  • アクティブキャッシュはリクエストをアプリケーションの他の部分に渡して、リクエストされた結果を生成し、その結果を格納してリクエスト元に返す

 

15章 バックアップとリカバリ

• 論理バックアップには2種類がある
• • SQLダンプ：ダンプファイルには、テーブル構造とデータの両方が含まれており、全てが有効なSQLコマンドとして書き出される
  • 区切りファイル：区切りファイルは、SQLダンプファイルよりも小さく、バックアップと復元が速い
• リカバリの方法はデータをバックアップした方法によって決まり、以下の手順の一部または全てを実行する必要がある
• • MySQLサーバーを停止する
  • サーバーの設定とファイルのパーミッションを書き留める
  • データをバックアップからMySQLのデータディレクトリへ移動する
  • 設定を変更する
  • ファイルのパーミッションを変更する
  • アクセスが制限された状態でサーバーをリブートし、完全に起動するまで待機する
  • 論理バックアップファイルを戻す
  • バイナリログを調べて再生する
  • 復元したものを検証する
  • 完全にアクセスできる状態でサーバーを再起動する

おわりに

ここまで読んでいただきありがとうございます。

MySQL、奥が深すぎますね、、！ 

MySQLで困ったことがあった時は、この本を参照すれば間違いないと思います。

実践ハイパフォーマンスMySQL 第3版

次は、Webを支える技術を読む予定です。

来週も頑張ります！