MySQLを最適化する方法：インデックス、スロークエリ、構成

MySQLは、世界で最も人気のあるリレーショナルデータベースのままですが、非効率的に使用するのが最も簡単なデータベースでもあります。多くの人は、さらなる調査なしでデフォルト設定を使用しています。この記事では、以前に導入されたMySQL最適化手法の一部を確認し、最新の改善と組み合わせます。

コアポイント

• innodb_buffer_pool_size、innodb_log_file_sizeなどの主要なパラメーターを調整してMySQL構成を最適化し、サーバーリソースをよりよく利用し、データベースのパフォーマンスを改善します。 innodb_flush_method
インデックスを効率的に使用して、クエリインデックス、プライマリキーインデックス、およびクエリ要件とデータの一意性に基づいてフルテキストインデックスの使用を検討します。
MySQL用のPercona Toolkitなどのツールを使用して、デュプリケートまたは未使用のインデックスなどの問題を自動的に識別および解決して、データベースの効率を最適化します。
mysqlのスロークエリログや
• などのツールを使用して、スロークエリを監視および分析してボトルネックを検出し、クエリのパフォーマンスを最適化します。 pt-query-digest
MySQLチューナーとその他のパフォーマンス監視ツールを定期的に実行して、データベース操作に関する洞察を収集し、実際の使用パターンに基づいて構成を微調整します。

設定最適化 MySQLの最初で最も見落とされがちなパフォーマンス改善は、構成を調整することです。バージョン5.7（現在のバージョン）には、以前のバージョンよりも優れたデフォルト値がありますが、改善は引き続き行うことができます。

Linuxベースのホストまたは改良されたホームステッドのようなVagrant Virtual Machineを使用していると仮定しているため、構成ファイルは

にあります。インストーラーは補助設定ファイルをロードする可能性があるため、

ファイルがそれほど多くない場合は、

ファイルである場合があります。 /etc/mysql/my.cnf my.cnf構成を編集/etc/mysql/mysql.conf.d/mysqld.cnf

コマンドラインの使用に精通する必要があります。以前にさらされたことがなくても、楽しい時間です。

Vagrant Virtual Machineでローカルに編集した場合、コマンドを使用して、メインファイルシステムの共有フォルダーにファイルをコピーし、通常のテキストエディターを使用して編集してから、そのコピーに戻します。完成後の元の場所。それ以外の場合は、

などの単純なテキストエディターを使用し、コマンドを実行します。

cp /etc/mysql/my.cnf /home/vagrant/Codevim注：上記のパスを変更して、構成ファイルの実際の場所に一致するように - 実際にはsudo vim /etc/mysql/my.cnf

に配置される場合があります。

手動調整 /etc/mysql/mysql.conf.d/mysqld.cnf次の手動調整をすぐに行う必要があります。これらのヒントに基づいて、セクションの構成ファイルに次のものを追加します：

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

• innodb_buffer_pool_size - バッファープールは、メモリ内のデータとインデックスのキャッシュに使用されるストレージエリアです。頻繁にアクセスされるデータをメモリに保持するために使用され、専用サーバーまたは仮想サーバーを実行すると、アプリケーションのこの部分に最も多くのRAMを割り当てることが理にかなっており、データベースはしばしばボトルネックです。したがって、すべてのRAMの50〜70％をそれに割り当てます。バッファープールのサイズ変更ガイドは、MySQLドキュメントに記載されています。
• ログファイルのサイズはここでよく説明されていますが、要するに、それを消去する前にログに保存するデータの量です。この場合、ログはエラーログまたは慣れているログではなく、チェックポイント時間を示していることに注意してください。MySQLの場合、書き込みはバックグラウンドで発生しますが、フォアグラウンドのパフォーマンスに影響を与えるためです。ログファイルを大きくすると、新しい小さいチェックポイントが少なくなるため、パフォーマンスが向上しますが、クラッシュの場合は回復時間が長くなります（データベースにさらに書き直す必要があります）。
• innodb_flush_log_at_trx_commitここには説明があります。これは、ログファイルに何が起こるかを示します。 1では、各トランザクションの後にログがディスクにフラッシュされるため、最も安全なセットアップがあります。 0または2を使用すると、酸性のパフォーマンスが低くなりますが、パフォーマンスが高くなります。この場合、違いはセット1の安定性の利点を超えるのに十分ではありません。
• innodb_flush_method - リフレッシュ作業を完了するには、ダブルバッファリングを避けるためにO_DIRECTに設定します。これは、I/Oシステムのパフォーマンスが非常に低い場合を除き、常に実行する必要があります。 DigitalOcean Dropletsなどのほとんどのマネージャーサーバーでは、SSDがあるため、I/Oシステムのパフォーマンスが高くなります。

Perconaの別のツールがあり、残りの問題を自動的に見つけるのに役立ちます。上記の手動調整なしで実行すると、他の3つがアプリケーションの環境に依存するため、4つの修正のうち1つだけが手動で識別できることに注意してください。

変数検査官

Ubuntuに変数検査官をインストールするには：

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit

他のシステムの場合は、指示に従ってください。

次に、次のコマンドでツールキットを実行します。

次のような出力が表示されるはずです

pt-variable-advisor h=localhost,u=homestead,p=secret

これらはどれも重要な問題ではなく、修正する必要はありません。追加できる唯一のことは、複製とスナップショットのバイナリロギングです。

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

注：新しいバージョンでは、ビンログのサイズはデフォルトで1Gになり、PTはそれに気付きません。

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

バイナリログのサイズを決定するように設定
• max_binlog_size。これらのログは、トランザクションとクエリを記録し、チェックポイントを作成します。トランザクションが最大値よりも大きい場合、ディスクに保存するとログが最大値より大きくなる可能性があります。なかに、MySQLはこの制限内に保持されます。
• log_binオプションは、バイナリロギングを完全に有効にします。それがなければ、スナップショットやコピーはありません。これにより、ディスクスペースに大きな圧力がかかる可能性があることに注意してください。サーバーIDは、バイナリロギングのためにアクティブ化されるときに必要なオプションであるため、ログは（複製用）からどのサーバーから来るかを把握し、形式はログに書き込む方法にすぎません。

ご覧のとおり、新しいMySQLには合理的なデフォルト値があり、物事がほぼすぐに生産されるようになります。もちろん、各アプリは異なり、適用される追加のカスタム調整があります。

mysqlチューナー

チューナーは、長い間隔でデータベースを監視し（ライブアプリケーションで週に1回実行）、ログに表示されるものに基づいて変更を提案します。

ダウンロードしてインストールするだけです：

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit

で実行すると、データベースの管理者のユーザー名とパスワードに依頼し、クイックスキャンを出力します。たとえば、ここに私のinnodbセクションがあります：./mysqltuner.pl

pt-variable-advisor h=localhost,u=homestead,p=secret

繰り返しますが、このツールはサーバーが実行されてから1週間に1回実行する必要があることに注意することが重要です。構成値を変更し、サーバーを再起動した後、それから1週間実行する必要があります。 Cronの仕事を設定して、これを行い、定期的に結果を送信するのが最善です。

---

構成を変更するたびに、mysqlサーバーを再起動してください：

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

index

次に、多くのアマチュアデータベース管理者の主な問題点であるインデックスに焦点を当てましょう！特に、すぐにORMに飛び込み、したがって、元のSQLに実際に触れたことはありませんでした。

注：キーとインデックスという用語は同じ意味で使用できます。

MySQLインデックスを本のインデックスと比較できます。これにより、探しているトピックを含む正しいページを簡単に見つけることができます。インデックスがなければ、トピックを含むページを検索するには、本全体を読んでください。

想像できるように、インデックスで検索すると、各ページを通過するよりもはるかに高速です。したがって、通常、データベースにインデックスを追加すると、選択したクエリをスピードアップできます。ただし、インデックスも作成および保存する必要があります。したがって、クエリの更新と挿入は遅くなり、より多くのディスクスペースを占有します。一般に、テーブルに正しくインデックスを付けた場合、更新と挿入の違いに気付かないため、適切な場所にインデックスを追加することをお勧めします。

数行しか含まれていないテーブルは、実際にはインデックス作成の恩恵を受けません。ご想像のとおり、5ページを検索すると、最初にインデックスを付け、ページ番号を取得してから特定のページを開くことほど遅くはありません。

では、どのインデックスを追加するインデックスとどのような種類のインデックスが存在するかをどのように見つけますか？

一意/プライマリキーインデックス

プライマリキーインデックスはデータのインデックスであり、データに対処するデフォルトの方法です。ユーザーアカウントの場合、これはユーザーIDまたはユーザー名、またはプライマリメールである場合があります。プライマリキーインデックスは一意です。唯一のインデックスは、一連のデータで繰り返すことができないインデックスです。

たとえば、

ユーザーが特定のユーザー名を選択した場合、他の誰もそれを使用できないはずです。ユーザー名列に「一意の」インデックスを追加すると、この問題が解決します。他の誰かが既存のユーザー名で行を挿入しようとする場合、MySQLはエラーを報告します。

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

プライマリキー/インデックスは通常、テーブル作成時に定義され、テーブルを変更することで唯一のインデックスが定義されます。

プライマリキーと一意のキーの両方を1つ以上の列に作成できます。たとえば、定義する国ごとに1つのユーザー名があることを確認する場合は、次のような両方の列に一意のインデックスを作成できます。

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit

頻繁にアクセスできる列に一意のインデックスが追加されます。したがって、ユーザーアカウントを頻繁にリクエストし、データベースに多くのユーザーアカウントがある場合、これは良いユースケースです。

一般的なインデックス

一般的なインデックスは検索を簡素化します。特定の列または列の組み合わせのデータをすばやく見つける必要がある場合に役立ちますが、そのデータは一意である必要はありません。

pt-variable-advisor h=localhost,u=homestead,p=secret

上記の操作により、国ごとのユーザー名の検索がスピードアップされます。

インデックスは、並べ替えとグループ化の速度を改善するのにも役立ちます。

フルテキストインデックス

フルテキストインデックスは、フルテキスト検索に使用されます。 InnoDBおよびMyisamストレージエンジンのみがフルテキストインデックスをサポートし、CHAR、VARCHAR、およびテキスト列のみがサポートされています。

これらのインデックスは、実行する必要があるすべてのテキスト検索に非常に役立ちます。フルテキストインデックスは、テキスト本体の単語を見つけるのに適しています。アプリで投稿、コメント、説明、コメントなどを検索することがよくある場合は、これらのコンテンツでこれらのインデックスを使用します。

下向きのインデックス

は特別なタイプではなく、変更です。バージョン8.0から始めて、MySQLは下降インデックス作成をサポートしています。つまり、インデックスを降順で保存できます。これは、最初に最後に追加されたデータを取得する必要があることが多い大きなテーブルまたは優先度エントリがある場合に役立ちます。いつでも降順でソートすることができますが、これによりパフォーマンスが少しもなります。これにより、さらにスピードが上がります。

データベースに書き込まれたログを処理するときにDESCをインデックスに適用すること、前面に戻る順序でロードされた投稿、および同様のコンテンツを処理することを検討してください。

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

アシストツール：説明

最適化クエリを表示すると、説明ツールは貴重です。簡単なクエリの前に説明を追加すると、非常に深い方法でそれを処理し、使用されているインデックスを分析し、ヒットとミスの比率を示します。探している結果を得るために処理する行の数に気付くでしょう。

拡張：

<code>max_binlog_size = 1G
log_bin = /var/log/mysql/mysql-bin.log
server-id=master-01
binlog-format = 'ROW'</code>

を使用してさらに拡張できます

この優れた詳細な記事を読んで、それを使用して発見を適用する方法をご覧ください。

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl

支援ツール：Perconaは、重複インデックスを検出するために使用されます

以前にインストールされていたPercona Toolkitは、サードパーティCMSを使用するときに役立つ重複インデックスを検出するか、必要以上に予期せずに追加されているインデックスが追加されているかどうかを確認するためのツールを提供します。たとえば、

テーブルにインストールされているデフォルトのWordPressには、重複したインデックスがあります。 wp_posts

最後の行に示されているように、重複するインデックスを削除する方法についての提案も提供します。

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

支援ツール：Perconaは、未使用のインデックスを検出するために使用されます

Perconaは、未使用のインデックスを検出することもできます。スロークエリをログに記録する場合（下のボトルネックセクションを参照）、ツールを実行でき、それらのレコードのクエリがクエリに関連するテーブルのインデックスを使用しているかどうかを確認します。

このツールの詳細な使用については、こちらをご覧ください。

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit

ボトルネック

このセクションでは、データベース内のボトルネックを検出および監視する方法について説明します。

上記を構成に追加する必要があります。実行時間が1秒以上、インデックスを使用していないクエリを監視します。

このログにデータがあると、前述の

pt-variable-advisor h=localhost,u=homestead,p=secret

ツールまたは

ツールを使用してインデックス使用量を分析できます。これにより、次の結果が生成されます。

これらのログを手動で分析したい場合は、これを行うこともできますが、まずログをより「分析された」形式にエクスポートする必要があります。これは次のとおりです pt-index-usage pt-query-digestその他のパラメーターは、データをさらにフィルタリングし、重要なコンテンツのみがエクスポートされることを確認できます。たとえば、平均実行時間でソートされた上位10のクエリ。

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

他のパラメーターのドキュメントを参照してください。

<code>max_binlog_size = 1G
log_bin = /var/log/mysql/mysql-bin.log
server-id=master-01
binlog-format = 'ROW'</code>

結論

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl

この包括的なMySQL Optimization Articleでは、MySQLをより速く実行するさまざまな方法を検討します。

構成最適化を処理し、インデックスを完了し、いくつかのボトルネックを取り除きました。ただし、これはほとんど理論的です。実際のユースケースがこれらのテクノロジーを実際のアプリケーションに適用するためには、今後のパフォーマンスブーストプロジェクトに注意してください。 テクニックやトリックを見逃しましたか？教えてください！

mysqlインデックス作成とスロークエリ最適化FAQ（FAQ）

クエリ最適化におけるMySQLインデックスの重要性は何ですか？

MySQLインデックスは、データの取得を大幅に高速化できるため、クエリの最適化に重要です。本のインデックスと同様に機能し、テーブル内のすべての行をスキャンせずにデータベースを見つけて取得できるようにします。これにより、特に大規模なデータベースでは、クエリの実行が速くなります。ただし、インデックスは読み取り速度を上げるが、データを挿入または更新するときにインデックスを更新する必要があるため、書き込み速度を遅くする可能性があることに注意することが重要です。

mysqlでスロークエリを識別する方法は？

MySQLは、スロークエリログと呼ばれる便利なツールを提供します。このツールは、指定された時間を超えて実行されたすべてのSQLクエリに関する情報を記録します。 MySQL構成ファイルで有効にして、クエリがスロークエリと見なされる前に、を秒数に設定できます。 long_query_time

さまざまな種類のMySQLインデックスは何ですか？

MySQLは、Bツリー、ハッシュ、Rツリー、フルテキストインデックスなど、複数のタイプのインデックスをサポートしています。 Bツリーはデフォルトのインデックスタイプであり、さまざまなクエリに適しています。ハッシュインデックスは等しい比較に使用され、そのようなクエリのBツリーよりも高速です。 Rツリーインデックスは空間データ型に使用され、フルテキストインデックスはフルテキスト検索に使用されます。

mysql構成を最適化する方法は？

MySQL構成最適化には、パフォーマンスのためにさまざまなサーバー変数を調整することが含まれます。これには、バッファープールのサイズ、ログファイルサイズ、クエリキャッシュサイズなどの調整が含まれます。サーバーのパフォーマンスを定期的に監視し、必要に応じてこれらの変数を調整することが重要です。

MySQLクエリとインデックスを最適化するのに役立つツールは何ですか？

MySQLクエリとインデックスの最適化に利用できるツールがいくつかあります。これらのツールには、MySQLがクエリの実行方法に関する情報を提供するMySQLの組み込み説明ステートメント、およびPercona ToolkitやMySQL Workbenchなどのサードパーティツールが含まれます。

説明声明はクエリの最適化にどのように役立ちますか？

MySQLの説明声明は、MySQLがクエリをどのように実行するかについての情報を提供します。これには、アクセスされたテーブル、テーブルにアクセスされる順序、使用される特定のインデックス、および読み取り行数の推定に関する情報が含まれます。この情報は、潜在的なパフォーマンスの問題を特定し、インデックスの最適化をガイドするのに役立ちます。

インデックスはMySQLでの書き込み操作にどのような影響を与えますか？

インデックスは、データの取得をスピードアップすることで読み取り操作を大幅に改善しますが、書き込み操作が遅くなる可能性があります。これは、データが挿入または更新されるたびに、対応するインデックスを更新する必要があるためです。したがって、インデックスを作成するときは、読み取り操作と書き込み操作のバランスをとることが重要です。

mysqlでの参加操作にインデックス最適化を使用する方法は？

インデックスは、MySQLでの参加オペレーションのパフォーマンスを大幅に改善できます。結合条件で使用される列にインデックスを作成することにより、MySQLは接続されたテーブルで一致する行をすばやく見つけることができます。これにより、フルテキストスキャンの必要性が減り、クエリの実行が速くなります。

MySQLパフォーマンスの最適化におけるクエリキャッシュの役割は何ですか？

MySQLのクエリキャッシュは、選択クエリの結果とクエリ自体を保存します。同じクエリが受信されると、MySQLはクエリを再度実行する代わりに、キャッシュから結果を取得できます。これにより、特に複雑なクエリや頻繁に実行されるクエリの場合、パフォーマンスが大幅に向上します。

mysqlサーバーのパフォーマンスを監視する方法は？

MySQLは、サーバーのパフォーマンスを監視するためのいくつかのツールを提供します。これらのツールには、パフォーマンスパターン（詳細なパフォーマンスメトリックの提供）と情報パターン（データベースメタデータに関する情報の提供）が含まれます。さらに、Show Statusコマンドを使用して、サーバーの実行ステータスに関する情報を取得できます。

以上がMySQLを最適化する方法：インデックス、スロークエリ、構成の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。