ホームページ >データベース >mysql チュートリアル >MySQL のパフォーマンスを向上させる 7 つのヒントを紹介します

MySQL のパフォーマンスを向上させる 7 つのヒントを紹介します

jackloveオリジナル: 2018-06-11 17:25:001674ブラウズ

翻訳者注: サイズと負荷が増加すると、MySQL のパフォーマンスは低下する傾向があります。 MySQL をスムーズに実行し続けるために、次のヒントを覚えておいてください。

アプリケーションを測定する方法の 1 つは、パフォーマンスを確認することです。パフォーマンスの指標の 1 つはユーザーエクスペリエンスです。平たく言えば、「ユーザーが欲しいものを手に入れるまでに長く待つ必要があるかどうか」です。

このインジケーターはアプリケーションごとに変化します。モバイルショッピングアプリの場合、応答時間は数秒を超えることはできません。従業員の HR ページの場合は、さらに数秒かかる場合があります。

パフォーマンスがユーザーの行動にどのような影響を与えるかについては、多くの研究が行われています:

79% の顧客は、遅い Web サイトに戻る可能性が低くなります
47% の消費者は、Web ページが 2 秒以内に完了することを期待しています読み込み中が少なくなります
サイトの読み込みに 3 秒以上かかると、ユーザーの 40% が諦めます
ページの読み込み時間が 1 秒遅れると、ページビューが 7% 失われ、11% 減少する可能性があります

採用に関係なく標準に関係なく、アプリケーションの良好なパフォーマンスを維持する必要があります。そうしないと、ユーザーが苦情を言うことになります（さらに悪いことに、別のアプリに移動することになります）。アプリケーションのパフォーマンスに影響を与える要因の 1 つはデータベースのパフォーマンスです。アプリケーション、Web サイト、データベース間の相互作用は、アプリケーションのパフォーマンスを確立するために重要です。

この対話の中核となるコンポーネントは、アプリケーションがデータベースにクエリを実行する方法と、データベースがリクエストにどのように応答するかです。いずれにしても、MySQL は最も人気のあるデータベース管理システムの 1 つです。運用環境では、データベースソリューションとして MySQL (およびその他のオープンソースデータベース) に注目する企業が増えています。

データベースがクエリに迅速に応答し、アプリケーションのパフォーマンスの低下を最小限に抑えるために役立つ MySQL の構成方法は数多くあります。

ここでは、MySQL データベースのパフォーマンスを最適化するのに役立つ基本的なヒントをいくつか紹介します。

最適化のヒント #1: EXPLAIN の使用方法を学ぶ

データベースに関して行う 2 つの最も重要な決定は、アプリケーションエンティティ間の関係をテーブル (データベーススキーマ) にマッピングする方法を設計することと、アプリケーションがテーブル (データベーススキーマ) にどのように動作するかを設計することです。必要なデータ (クエリ) を形式で取得します。

複雑なアプリケーションには、複雑なパターンやクエリが含まれる場合があります。アプリケーションに必要なパフォーマンスとスケーラビリティを実現したい場合、クエリの実行方法を直感に頼るだけでは不十分です。

ランダムな推測や想像ではなく、EXPLAIN コマンドの使用方法を学ぶ必要があります。このコマンドは、クエリの実行方法を示し、期待できるパフォーマンスと、データのサイズの変化に応じてクエリがどのようにスケールされるかを示します。

EXPLAIN 出力を視覚化できるツール (MySQL Workbench など) は数多くありますが、それを理解するには基本を理解する必要があります。

EXPLAIN コマンドは 2 つの異なる形式で出力を提供します。1 つは昔ながらの表形式、もう 1 つはより現代的な構造化された JSON ドキュメントであり、より詳細な情報が提供されます (以下を参照):

mysql> explain format=json select avg(k) from sbtest1 where MySQL のパフォーマンスを向上させる 7 つのヒントを紹介します between 1000 and 2000 \G
*************************** 1. row ***************************
EXPLAIN: {
  “query_block”: {
    “select_MySQL のパフォーマンスを向上させる 7 つのヒントを紹介します”: 1,
    “cost_info”: {
      “query_cost”: “762.40”
    },
    “table”: {
      “table_name”: “sbtest1”,
      “access_type”: “range”,
      “possible_keys”: [
        “PRIMARY”
      ],
      “key”: “PRIMARY”,
      “used_key_parts”: [
        “MySQL のパフォーマンスを向上させる 7 つのヒントを紹介します”
      ],
      “key_length”: “4”,
      “rows_examined_per_scan”: 1874,
      “rows_produced_per_join”: 1874,
      “filtered”: “100.00”,
      “cost_info”: {
        “read_cost”: “387.60”,
        “eval_cost”: “374.80”,
        “prefix_cost”: “762.40”,
        “data_read_per_join”: “351K”
      },
      “used_columns”: [
        “MySQL のパフォーマンスを向上させる 7 つのヒントを紹介します”,
        “k”
      ],
      “attached_condition”: “(`sbtest`.`sbtest1`.`MySQL のパフォーマンスを向上させる 7 つのヒントを紹介します` between 1000 and 2000)”
    }
  }
}

注目すべきコンポーネントの 1 つは「クエリコスト」です。。クエリコストとは、MySQL がこの特定のクエリのコストをクエリ実行の総コストの観点から考慮することを意味し、多くのさまざまな要因に基づいています。

単純なクエリのクエリオーバーヘッドは通常 1,000 未満です。コストが 1,000 ～ 100,000 のクエリは中コストのクエリとみなされ、通常、これらのクエリを 1 秒あたり (数万件ではなく) 数百件のみ実行する方が高速になります。

100,000 を超えるクエリは高価であると考えられます。通常、システム上でユーザーが 1 人の場合でも、これらのクエリは高速に実行されますが、対話型アプリケーションでこのようなクエリを使用する頻度を慎重に検討する必要があります (特にユーザー数が増えると)。

もちろん、これらの数値はパフォーマンスの大まかな表現にすぎませんが、一般原則を示しています。システムのアーキテクチャと構成に応じて、クエリワークロードの処理が良くなったり悪くなったりする場合があります。

クエリのオーバーヘッドを決定する主な要素は、クエリがインデックスを正しく使用するかどうかです。 EXPLAIN コマンドを使用すると、クエリでインデックスが使用されているかどうかを知ることができます (通常、データベース内でのインデックスの作成方法、またはクエリ自体の設計方法によります)。だからこそ、 EXPLAIN の使い方を学ぶことが非常に重要です。

最適化のヒント #2: 適切なインデックスを作成する

インデックスを使用すると、クエリでスキャンする必要があるデータベース内のデータ量が削減され、クエリの効率が向上します。 MySQL のインデックスは、データベースへのアクセスを高速化し、データベース制約 (UNIQUE や FOREIGN KEY など) を強制するために使用されます。

データベースの索引は書籍の索引によく似ています。これらは独自の場所に保存され、メインデータベースにすでに存在する情報が含まれています。これらは、データが存在する場所を示す参照メソッドまたはマップです。インデックスによってデータベース内のデータは変更されません。それらはデータの場所を指すだけです。

どのようなワークロードにも完全に適したインデックスはありません。代わりに、常にシステムが実行されているクエリのコンテキストでインデックスを表示する必要があります。

適切にインデックスが作成されたデータベースは高速に実行されるだけでなく、インデックスが欠落している場合でもデータベースのクロールが遅くなる可能性があります。 EXPLAIN (前述したように) を使用して、欠落しているインデックスを見つけて追加します。ただし、必要のないインデックスは追加しないように注意してください。不必要なインデックスはデータベースの速度を低下させます
(MySQL インデックス作成のベストプラクティスの紹介をご覧ください)。

最適化のヒント #3: デフォルト設定の使用を拒否する

他のソフトウェアと同様、MySQL には動作 (そして最終的にはパフォーマンス) を変更するために使用できる構成可能な設定が多数あります。他のソフトウェアと同様に、管理者はこれらの構成可能な設定の多くを無視し、デフォルトモードで使用することになります。

MySQL から最高のパフォーマンスを得るには、構成可能な MySQL 設定を理解することが重要であり、さらに重要なのは、データベース環境に最適になるように設定することです。

デフォルトでは、MySQL は運用規模ではなく、小規模な開発インストールを対象としています。通常、利用可能なすべてのメモリリソースを使用し、アプリケーションが必要とするだけの接続を許可するように MySQL を構成する必要があります。

常に注意深く確認する必要がある 3 つの MySQL パフォーマンス最適化設定を次に示します:

innodb_buffer_pool_size: バッファプールは、キャッシュされたデータとインデックスを保存するために使用されます。これが、大量の RAM を搭載したシステムをデータベースサーバーとして使用する主な理由です。 InnoDB ストレージエンジンのみを実行する場合、通常、メモリの 80% がバッファプールに割り当てられます。非常に複雑なクエリを実行している場合、または多数の同時データベース接続がある場合、または多数のテーブルがある場合は、この値を少し下げて、他の操作により多くのメモリを割り当てることができます。

InnoDB バッファープールサイズを設定するときは、大きすぎないように注意する必要があります。大きすぎるとスワップが発生します。これは間違いなくデータベースのパフォーマンスに影響します。簡単に確認する方法は、Percona Monitoring and Management のシステム概要図でスワップアクティビティを確認することです。ただし、1 秒あたり 1MB 以上のスワップアクティビティが継続的に発生する場合は、バッファプールサイズ (またはその他のメモリ使用量) を減らす必要があります。

最初のアクセスで innodb_Buffer_pool_size の値が正しく取得されなくても、心配する必要はありません。 MySQL 5.7 以降では、データベースサーバーを再起動せずに InnoDB バッファプールのサイズを動的に変更できるようになりました。

innodb_ log_ file_ size: これは、単一の InnoDB ログファイルのサイズです。デフォルトでは、InnoDB は 2 つの値を使用するため、この数値を 2 倍にして、InnoDB がトランザクションの耐久性を確保するために使用する循環 REDO ログスペースのサイズを取得できます。これにより、データベースへの変更の適用も最適化されます。 innodb_log_file_size の設定はトレードオフの問題です。割り当てられる REDO 領域が多いほど、書き込み集中型のワークロードのパフォーマンスは向上しますが、システムの電源喪失やその他の問題が発生した場合、クラッシュからの回復に時間がかかります。
MySQL のパフォーマンスが現在の InnoDB ログファイルサイズによって制限されているかどうかを確認するにはどうすればよいですか?利用可能な REDO ログ領域が実際にどのくらい使用されているかを見るとわかります。最も簡単な方法は、Percona モニターと管理の InnoDB メトリックダッシュボードを表示することです。以下の図では、使用されているスペースが利用可能な REDO ログスペース (赤い線で示されている) に非常に近いため、InnoDB ログファイルのサイズは十分に大きくありません。ログファイルのサイズは、システムを最適に実行し続けるために必要な領域より少なくとも 20% 大きくする必要があります。 MySQL のパフォーマンスを向上させる 7 つのヒントを紹介します

MAX_ 接続数: 大規模なアプリケーションの接続数は、通常、デフォルト値よりも大きくする必要があります。他の変数とは異なり、正しく設定されていなくても (それ自体) パフォーマンス上の問題はありません。逆に、接続数がアプリケーションのニーズに対して十分でない場合、アプリケーションはデータベースに接続できません (ユーザーにとってはダウンタイムのように見えます)。したがって、この変数を正しく処理することが重要です。

複数のサーバー上で複数のコンポーネントを含む複雑なアプリケーションを実行する場合、必要な接続の数を知ることが困難になることがあります。幸いなことに、MySQL を使用すると、ピーク時の動作中に使用されている接続の数を簡単に確認できます。一般に、アプリケーションで使用される接続の最大数と使用可能な接続の最大数の間に少なくとも 30% のギャップがあることを確認する必要があります。これらの数値を簡単に確認するには、Percona Monitoring and Management の MySQL 概要ダッシュボードの MySQL 接続グラフを使用します。以下の図は、多数の追加接続が利用可能な堅牢なシステムを示しています。

覚えておくべきことの 1 つは、データベースが遅い場合、アプリケーションが作成する接続の数が多すぎるということです。この場合、単に接続を増やすだけでなく、データベースのパフォーマンスの問題に対処する必要があります。接続が増えると、根本的なパフォーマンスの問題が悪化する可能性があります。

(注: max_Connections 変数をデフォルト値より大幅に大きく設定する場合、多くの場合、テーブルキャッシュのサイズや開いている MySQL ファイルの数など、他のパラメーターを増やすことを検討する必要があります。ただし、これは、この記事)

最適化のヒント #4: データベースをメモリ内に保持する

近年、ソリッドステートディスク (SSD) への移行が見られます。 SSD はハードドライブよりもはるかに高速ですが、それでも RAM 内のデータには及びません。この違いは、ストレージのパフォーマンスそのものだけでなく、ディスクまたは SSD ストレージからデータを取得するときにデータベースが実行する必要がある追加作業からも発生します。

最新のハードウェアの改善により、クラウドで実行する場合でも、独自のハードウェアを管理する場合でも、データベースをメモリに保存することがますます可能になります。

さらに良いニュースは、インメモリのパフォーマンス上の利点を最大限に活用するために、すべてのデータベースをメモリに配置する必要がないことです。一連の作業データ (最も頻繁にアクセスされるデータ) をメモリに保存するだけです。

データベースのどの部分をメモリに保持する必要があるかについて、10% から 33% までの具体的な数値を示している記事をいくつか見たことがあるかもしれません。実のところ、「すべてに適合する」という数字は存在しません。パフォーマンスを最大限に高めるためにメモリに収まるデータの量は、ワークロードによって異なります。特定の「魔法の」数値を探すのではなく、データベースが定常状態 (通常は起動後数時間) で実行されている I/O を確認してください。データベースがメモリ内にある場合は、READ を完全に削除できるため、READ に注目してください。利用可能なメモリの量に関係なく、書き込みは常に実行する必要があります。

以下では、Percona によって監視および管理されている InnoDBMetrics ダッシュボードの InnoDB I/O グラフの I/O を確認できます。上のグラフでは、1 秒あたり最大 2,000 の I/O 操作のピークが見られます。これは、(少なくともワークロードの一部では) データベースが正常に動作していることを示しています。セットがメモリに収まりません。

最適化のヒント #5: SSD ストレージを使用する

データベースがメモリに収まらない場合でも (そうでない場合でも)、データベースがウォームアップしたとき (データベースがウォームアップした後) に書き込みを処理し、パフォーマンスの問題を回避するために高速ストレージが必要です。再起動）。今日、SSD は高速ストレージの代名詞です。
一部の「専門家」は、コストや信頼性を理由に、依然として回転ディスク (機械式ディスク) の使用を主張しています。率直に言って、データベースの運用に関しては、これらの議論は時代遅れであるか、まったく間違っていることがよくあります。現在、SSD は優れたパフォーマンスと信頼性をプレミアム価格で提供しています。

ただし、すべての SSD が適しているわけではありません。データベースサーバーの場合は、(停電時などの) データを保護するサーバーワークロード向けに設計された SSD を使用する必要があります。デスクトップコンピューターやラップトップ用に設計された市販の SSD は避けてください。

NVMe または Intel OpTan テクノロジー経由で接続された SSD は最高のパフォーマンスを提供します。 SAN、NAS、またはクラウドブロックデバイスとしてリモートに接続されている場合でも、SSD は回転ディスクと比較して優れたパフォーマンスを提供します。

最適化のヒント #6: スケールアウト

高性能サーバーにも限界があります。拡張方法には、アップとアウトの 2 つがあります。スケールアップとは、より多くのハードウェアを購入することを意味します。これには費用がかかる可能性があり、ハードウェアはすぐに陳腐化してしまいます。より多くの負荷を処理するためにスケールアウトすると、いくつかの利点があります:

1. より小型で低コストのシステムを利用できる。

2. 水平方向の拡張により、線形拡張がより速く簡単になります。

3. データベースは複数の物理マシンに分散されているため、データベースは単一のハードウェア障害点の影響を受けません。

水平スケーリングには利点もありますが、一定の制限もあります。スケーリングには、データ同期のための基本的な MySQL レプリケーションや Percona XtraDB クラスターなどのレプリケーションが必要です。しかし、その代わりに、さらなるパフォーマンスと高可用性が得られます。より大きなスケーリングが必要な場合は、MySQL シャーディングを使用してください。

また、クラスターアーキテクチャに接続されているアプリケーションが、通常はプロキシサーバーやロードバランサー (ProxySQL や HAProxy など) を通じて、必要なデータを見つけられるようにする必要もあります。

スケールアウトを計画する場合は、早すぎるスケールを避けてください。分散データベースの操作は、より複雑になる傾向があります。最新のハードウェアと MySQL サーバーを使用すると、1 台のサーバーだけで優れたエクスペリエンスを実現できます。最近リリースされた MySQL 8 リリース候補では、単一システム上で 200 万を超える単純なクエリを処理できることが示されています。

最適化のヒント #7: 可観測性

最高のシステムは可観測性を念頭に置いて設計されています - MySQL も例外ではありません。

MySQL 環境を立ち上げ、実行し、適切に調整したら、設定するだけで管理しないというわけにはいきません。データベース環境は、システムまたはワークロードの変更の影響を受ける可能性があります。トラフィックの急増、アプリケーションエラー、MySQL の障害などの予期せぬ事態に備えてください。これらのことは起こる可能性があり、起こるでしょう。

問題が発生した場合は、迅速かつ効率的に解決する必要があります。これを行う唯一の方法は、何らかの監視ソリューションをセットアップし、それを適切に初期化することです。これにより、本番環境でのデータベース実行中にデータベース環境で何が起こっているかを確認し、問題が発生したときにサーバーデータを分析することができます。理想的には、システムを使用すると、問題が発生する前、またはユーザーがその影響を確認できるほど問題が発展する前に、問題を防ぐことができます。

監視ツールには、MySQL Enterprise Monitor、Monyog、Percona Monitoring and Management (PMM) が含まれます。後者には、無料でオープンソースであるという追加の利点があります。これらのツールは、監視とトラブルシューティングに優れた操作性を提供します。

大規模な運用環境でビジネスデータを管理および提供するためにオープンソースデータベース (特に MySQL) を利用する企業が増えているため、これらのデータベースを最適化して最高の状態で実行し続けることに重点を置く必要があります。ビジネス目標にとって重要なすべてのことと同様、データベースのパフォーマンスは、ビジネス目標や成果を左右する可能性があります。 MySQL は、アプリケーションや Web サイトに高品質のデータベースソリューションを提供できるデータベースソリューションですが、ニーズに合わせて調整し、ボトルネックやパフォーマンスの問題を見つけて防止するために監視する必要があります。

Peter Zaitsev は、エンタープライズグレードの MySQL および MongoDB ソリューションとサービスのプロバイダーである Percona の共同創設者兼 CEO です。オライリー発行の『High Performance MySQL』は、最も人気のある MySQL パフォーマンス書籍の 1 つです。 Zaitsev は PerconaDatabasePerformanceBlog.com で頻繁にブログを書いており、世界中のカンファレンスで講演しています。

この記事では、MySQL のパフォーマンスを向上させるための 7 つのヒントを詳しく紹介します。その他の関連コンテンツについては、php 中国語 Web サイトを参照してください。

MySQL のパフォーマンスを向上させる 7 つのヒントを紹介します

最適化のヒント #1: EXPLAIN の使用方法を学ぶ

最適化のヒント #2: 適切なインデックスを作成する

最適化のヒント #3: デフォルト設定の使用を拒否する

最適化のヒント #4: データベースをメモリ内に保持する

1. より小型で低コストのシステムを利用できる。

2. 水平方向の拡張により、線形拡張がより速く簡単になります。

3. データベースは複数の物理マシンに分散されているため、データベースは単一のハードウェア障害点の影響を受けません。

最適化のヒント #7: 可観測性

関連記事