MySQLを最適化するための10の秘訣

MySQLは、多くの重要なビジネス・アプリケーションのデータ・プラットフォームとして人気を集め続けています。最も広く利用されているオープンソースデータベースであるMySQLは、多くのオンプレミスおよびクラウドベースのアプリケーションにデータベースサービスを提供しています。MySQLのパフォーマンスは非常に重要です。MySQLのパフォーマンス・チューニングは多くの要因に左右されますが、ユーザは常にデータベースを微調整してパフォーマンスを向上させる方法を探し求めています。ここでは、MySQLデータベースのパフォーマンスを最適化するための基本的な方法と、より技術的な方法を紹介します。

1 十分なサーバリソースの確保
2 SSDストレージの活用
3 LinuxのSwappiness設定を調整する
4 インデックスの最適化
5 バッファプールの適切なサイズ設定
6 REDOログファイルのサイズを最適化する
7 SELECT * の使用を避ける
8 先頭のワイルドカードを含む LIKE エクスプレッションを避ける
9 EXPLAINを活用する
10 ワークロードの監視

十分なサーバリソースの確保

MySQLのようなデータベースサーバは、4つの基本的なサーバリソースを必要とします。CPU、メモリ、ストレージ、ネットワーク帯域幅です。これらのうち、メモリはデータベースのパフォーマンスを向上させるために最も重要な要素です。MySQL はメモリを使用してクエリ結果をキャッシュし、低速なストレージ I/O を使用する必要性を低減します。Linux の top コマンドを使用すると、サーバの CPU とメモリの使用率を簡単に確認できます。

SSDストレージの活用

可能な限り高速なストレージシステムを利用することが、パフォーマンスレベルを向上させる早道です。現在、MySQLのストレージにHDDを使用している場合、SSDに移行することで、データベースのパフォーマンスを大幅に向上させることができます。SSD は HDD よりもはるかに高速です。また、信頼性も高く、消費電力も少なくなっています。7,200 RPMのHDDは約150MB/sの転送スループットを提供しますが、SATA III SSD接続は約550MB/sの読み取り速度と520MB/sの書き込み速度を提供します。ただしSSDの価格を計算することが必要です。

LinuxのSwappiness設定を調整する

LinuxでMySQLを実行している場合、LinuxのSwappiness設定を最適化することでパフォーマンスを向上させることができます。Swappinessの値は、カーネルがメモリページをスワップアウトする能力を制御します。十分なRAMを持つデータベースサーバでは、この値は可能な限り低くする必要があります。デフォルトの値は60です。より良い選択は、最小限のスワッピングを可能にする値である 1 を使用することです。Linux Swappinessの値を設定するには、/etc/sysctl. confファイルを管理者ユーザで開き、vm.swappiness = 1という行を追加します。

インデックスの最適化

インデックスの作成ミスは、MySQLのパフォーマンスを低下させる原因としてよく知られています。インデックスがない場合、MySQLは必要な行を探すためにテーブル全体をスキャンしなければならず、これは低速で、I/O集中的な操作となります。インデックスが多ければ多いほど、関連するインデックスを更新する必要があるため、データベースへの書き込みが遅くなることを覚えておくことが重要です。EXPLAIN文、log_queries_not_using_indexes 構成設定、またはデータベース性能監視ツールを使用すると、隠れたインデックスを発見するのに役立ちます。

バッファプールの適切なサイズ設定

バッファプールは、頻繁に使用されるデータを常にディスクに移動するのではなく、メモリ内のバッファプールキャッシュから直接アクセスすることを可能にします。これにより、MySQLは繰り返しクエリされるデータに対してはるかに速い応答時間を提供することができます。一般的な経験則として、システムメモリの約80%をバッファプールに割り当てます。例えば、8GB RAMの場合は6GB、32GB RAMの場合は24GB、128GB RAMの場合は100GBとなります。MySQLのバッファプールサイズは、通常 /etc/mysql/my.cnf にあるMySQL設定ファイルの値 innodb_buffer_pool_size を編集することによって調整できます。

REDOログファイルのサイズを最適化する

REDOログファイルのサイズをデフォルト値の48MBから大きくすると、書き込み操作のパフォーマンスが向上します。ログファイルのサイズをより正確に計算する方法がありますが、簡単な経験則では、ログファイルのサイズをバッファプールと同じサイズにすることです。ログファイルのサイズは、/etc/mysql/my.cnf にある MySQL 設定ファイルで innodb_log_file_size 設定を構成することで最適化できます。

**SELECT * の使用を避ける**

SELECT * を使用すると、対象テーブルからすべてのカラムを取得するため、不必要な I/O やメモリ、ネットワーク帯域幅を使用することになります。これらの不要なカラムは、データベースにさらなる負荷を与え、特定のクエリやシステム全体の速度を低下させる原因となります。SELECT * を使用する代わりに、SELECT 句で実際に必要なカラムを指定してください。これにより、必要なデータのみを取得することで、MySQL クエリのパフォーマンスとシステム全体の使用率を向上させることができます。

先頭のワイルドカードを含む LIKE エクスプレッションを避ける

しかし、MySQLはクエリに先頭のワイルドカードがある場合、インデックスを使用することができません。例えば、次のクエリはstudentsテーブルのlast_nameフィールドにインデックスを付けても、MySQLはフルテーブルスキャンを実行します。「students」テーブルの「last_name」フィールドにインデックスした場合： SELECT * FROM students WHERE last_name LIKE ‘%son’

EXPLAINを活用する

EXPLAIN は、MySQL クエリを理解し最適化するのに役立つツールです。EXPLAIN を利用するには、クエリの先頭に EXPLAIN 式を追加するだけです。EXPLAIN はクエリを読み込んで検証し、クエリ内の各テーブルに関する情報を 1 行ずつ出力します。EXPLAINは、処理された行数、使用可能なインデックス、実際に選択されたインデックスなどのクエリー情報を提供します。EXPLAINは次のように使用します。EXPLAIN SELECT last_name, first_name FROM students.

ワークロードの監視

MySQL のパフォーマンスを向上・維持する最善の方法のひとつは、定期的なデータベース監視を行うことです。データベース・パフォーマンスの監視により、CPU、メモリ、I/Oの使用率、IOホットスポット、待機統計だけでなく、チューニングが必要な最も遅いクエリや最もコストのかかるクエリを明らかにすることができます。定期的なモニタリングにより、パフォーマンスのベースラインを作成し、長期的なトレンドとパフォーマンスを確認し、異常を迅速に見つけることができます。また、データベースのチューニングや設定の変更が効果的であったかどうかを調べるのにも役立ちます。

それにはDatabase Performance Analyzer(DPA)とDatabase Performance Monitor(DPM) を検討ください。