【MySQL】パフォーマンスチューニング: 実例を交えた解説

MySQLは広く使用されているオープンソースのリレーショナルデータベース管理システムであり、パフォーマンスの最適化はデータベースエンジニアにとって重要なスキルです。このブログでは、実例を交えながらMySQLのパフォーマンスチューニングの手法とベストプラクティスについて解説します。これらのテクニックを活用することで、MySQLデータベースのパフォーマンスを向上させることができます。

クエリの最適化と実例

MySQLのパフォーマンスチューニングにおいて、クエリの最適化は重要な要素です。この記事では、具体的なSQL文や設定例を交えながら、クエリの最適化について詳しく解説します。これにより、MySQLデータベースのパフォーマンスを向上させるための実践的な知識を得ることができます。

インデックスの適切な使用

インデックスは特定のカラムの、あるレコードをすばやく見つけるために使用されるものです。検索が爆速になります。

本を読む時、章ごとに付箋みたいなのを貼っておくと目的のページにたどり着くのが早いのと同じです。

インデックスがないとMySQLは関連するレコードを見つけるために、先頭行から始めてテーブル全体を読みとらないといけなくなります。(フルテーブルスキャン)

インデックスの選択

適切な列にインデックスを作成することで、クエリのパフォーマンスを向上させることができます。例えば、以下のようなクエリがあるとします。

SELECT * FROM users WHERE age > 25 AND country = 'Japan';

この場合、age 列と country 列にインデックスを作成すると効果的です。インデックスは次のように作成します。

CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_country ON users(country);

これにより、クエリの実行速度が向上します。

クエリの再構築

複雑なクエリはパフォーマンスのボトルネックになることがあります。以下はクエリの再構築の例です。

元のクエリ

SELECT * FROM users WHERE age > 25 AND country = 'Japan';

再構築後のクエリ

SELECT * FROM users WHERE country = 'Japan' AND age > 25;

この変更により、インデックスを効果的に使用することができます。

データベースの設定の最適化

最適化には、いくつかのレベルでの構成、チューニング、およびパフォーマンスの測定が含まれます。 業務の役割 (開発者、データベース管理者、または両方の組み合わせ) に応じて、個々の SQL ステートメント、アプリケーション全体、単一のデータベースサーバー、または複数のネットワーク接続されたデータベースサーバーのレベルで最適化できます。 プロアクティブにパフォーマンスを事前に計画する場合や、または問題の発生後に、構成やコードの問題のトラブルシューティングを行う場合があります。 CPU やメモリーの使用を最適化することで、スケーラビリティーを向上し、データベースを低下させず、より多くの負荷を処理させることもできます。

バッファプールの調整

バッファプールはデータベースから読み込まれたデータをキャッシュする領域です。適切なサイズに設定することで、データの読み取りアクセスのパフォーマンスを向上させることができます。

設定例

/etc/my.cnfを開いて設定を記述します。

innodb_buffer_pool_size = 4G

これにより、バッファプールのサイズが4GBに設定されます。

スレッドキャッシュの最適化

MySQLは同時接続を処理するためにスレッドを使用します。スレッドキャッシュのサイズを適切に設定することで、スレッドの生成と破棄にかかるオーバーヘッドを減らし、パフォーマンスを向上させることができます。

例えば、以下の設定を/etc/my.cnfへ行います。

thread_cache_size = 100

これにより、スレッドキャッシュのサイズが100に設定されます。

クエリキャッシュの使用

MySQLのクエリーキャッシュとは、クエリーの結果のキャッシュです。 SEL から始まる受け取ったクエリーをハッシュテーブルと比較し、一致するものがあれば、クエリーの過去の実行時の結果を返します。これにはいくつかの制限事項があります。

1. クエリーはバイト単位で一致している必要があります(クエリーキャッシュはパースをしません)。
2. 非決定的な機能を使うと、クエリーはキャッシュされません(一時テーブルやユーザー変数、 RAND() 、 NOW() 、UDFを含みます)。
3. クエリーキャッシュは有効期限切れの結果を返さないように設計されています。テーブルに対するどんな変更も、そのテーブルに関するキャッシュを無効化します。

クエリーキャッシュの理想的な使用例は、何百万行もスキャンするのに数行しか返さないような非常に重いクエリがたくさんあり、かつほとんど読み出し専用という場合です。例えば、Webページのフォームに必ず表示されるドロップダウンリスト内の値を生成する複雑なクエリのようなものがあり得るでしょう。このような状況では、インデックスが足りないことから来るパフォーマンス問題を、クエリーキャッシュが隠してくれて、ユーザーを助けてくれます。

クエリキャッシュの有効化

MySQLのクエリキャッシュを有効にすることで、頻繁に実行されるクエリの結果をメモリ内にキャッシュし、パフォーマンスを向上させることができます。

設定ファイル(my.cnf)に以下の設定を追加します。

query_cache_type = 1
query_cache_size = 128M

これにより、クエリキャッシュが有効化され、キャッシュのサイズが128MBに設定されます。

インデックスの最適化

SELECT 操作のパフォーマンスを向上する最善の方法は、クエリーでテストされる 1 つ以上のカラムにインデックスを作成することです。 インデックスエントリは、テーブル行へのポインタのように動作し、クエリーが WHERE 句の条件に一致する行を迅速に特定し、それらの行のほかのカラム値を取得できます。 すべての MySQL データ型にインデックスを設定できます。

クエリーで使用されている可能なすべてのカラムにインデックスを作成しようとしがちですが、不要なインデックスは領域を無駄にし、MySQL が使用するインデックスを判断するための時間を無駄にします。 各インデックスを更新する必要があるため、インデックスは挿入、更新、削除のコストも追加します。 最適なインデックスのセットを使用して、高速のクエリーを実現するために、適切なバランスを見つける必要があります。

インデックスの定期的な再構築

インデックスはデータベースのパフォーマンスに重要な役割を果たしますが、データの変更があるとインデックスの効果が低下することがあります。定期的にインデックスを再構築することで、最適なパフォーマンスを維持することができます。

ALTER TABLE users ENGINE=InnoDB;

このコマンドを使用することで、テーブルのインデックスが再構築されます。

複合インデックスの使用

複合インデックスは複数の列を組み合わせてインデックスを作成する方法です。適切な複合インデックスの使用は、クエリのパフォーマンス向上に大きく貢献します。以下に複合インデックスの使用の実例を示します。

実例: 複合インデックスの作成

注文情報を管理するテーブルがあるとします。

Order Table:
order_id | customer_id | product_id | order_date | quantity | price

このテーブルに対して、以下のクエリが頻繁に実行されるとします。

SELECT * FROM orders WHERE customer_id = 123 AND order_date >= '2023-01-01' AND order_date <= '2023-12-31';

このクエリのパフォーマンスを向上させるために、複合インデックスを作成します。

CREATE INDEX idx_customer_orderdate ON orders (customer_id, order_date);

これにより、customer_id 列と order_date 列を組み合わせた複合インデックスが作成されます。これにより、クエリの実行速度が向上します。

複合インデックスを適切に選択することで、クエリのパフォーマンスを最適化することができます。ただし、過度な複合インデックスの作成はデータベースのパフォーマンスに悪影響を与える場合があるため、適切な列を選択することが重要です。

複合インデックス詳細解説

1. 複合インデックスとは:
   • 複合インデックスは、複数の列を組み合わせて作成されるインデックスのことです。
   • 複合インデックスは、複数の列に対して高速な検索を可能にします。
2. 複合インデックスの作成:
   • 複合インデックスは、複数の列を指定して作成します。
   • 例: CREATE INDEX index_name ON table_name (column1, column2, column3);
3. 複合インデックスの利点:
   • クエリのパフォーマンスを向上させることができます。複数の列に対して高速な検索が可能になります。
   • インデックスの数を減らすことができます。複合インデックスを使用することで、複数の単一列インデックスを作成する必要がありません。
4. 複合インデックスの注意点:
   • 列の順序が重要です。複合インデックスの列の順序は、クエリの条件に合わせて考慮する必要があります。順序によってクエリの最適化が異なるため、より重要な条件を先頭に配置することが推奨されます。
   • 列の選択に慎重になる必要があります。複合インデックスは複数の列を組み合わせたものですが、必要のない列を含めるとインデックスのサイズが増え、パフォーマンスに悪影響を与える可能性があります。必要な列のみを選択しましょう。
   • インデックスのサイズに注意が必要です。複合インデックスは複数の列を含むため、単一列インデックスと比べてサイズが大きくなる傾向があります。ディスク使用量やメモリ使用量に留意しましょう。
   • 更新オーバヘッドに注意が必要です。複合インデックスの更新は、インデックス内の複数の列を同時に更新する必要があります。大量の更新操作がある場合は、パフォーマンスへの影響を検討する必要があります。
5. クエリ最適化と複合インデックス:
   • 複合インデックスは、クエリの最適化に大きな影響を与えます。列の順序が重要であり、クエリの条件と一致する列がインデックスの先頭に配置されるほど効果的です。
   • クエリで使用される列が複合インデックスの先頭に含まれていない場合、インデックスは効果を発揮しません。このため、クエリのパフォーマンスを向上させるためには、適切な列の順序を選択する必要があります。
6. 複合インデックスと単一列インデックスの比較:
   • 複合インデックスは、単一列インデックスと比較して効果的ですが、注意が必要です。
   • 複合インデックスは、複数の列を組み合わせているため、インデックスのサイズが大きくなります。これにより、ディスク使用量やメモリ使用量が増加する可能性があります。
   • 単一列インデックスは特定の列に対して効果的であり、特定のクエリに適しています。一方、複合インデックスは複数の列を組み合わせるため、より広範な範囲のクエリに適しています。
7. 複合インデックスの作成とメンテナンス:
   • 複合インデックスを作成する際は、データベースの設計やクエリのパフォーマンス要件に応じて適切な列を選択し、順序を検討する必要があります。
   • インデックスのメンテナンスも重要です。データの変更や新しいクエリの追加に伴い、複合インデックスも更新や再構築が必要になる場合があります。定期的なメンテナンスを実施し、インデックスの最適な状態を維持しましょう。

以上が複合インデックスに関する詳細な解説と注意点です。複合インデックスはデータベースのパフォーマンスを向上させる強力なツールですが、正しい設計と適切なメンテナンスが重要です。列の順序や選択に注意し、クエリの最適化とデータベースのパフォーマンスの向上に役立ててください。

クエリのプロファイリング

MySQL クエリ プロファイリングは、データベース駆動型アプリケーションの全体的なパフォーマンスを分析しようとするときに役立つ手法です。中規模から大規模のアプリケーションを開発する場合、大規模なコード ベース全体に数百のクエリが分散される傾向があり、データベースに対して毎秒多数のクエリが実行される可能性があります。ある種のクエリ プロファイリング手法がないと、ボトルネックやアプリケーションの速度低下の場所と原因を特定することが非常に難しくなります。

クエリプロファイルの有効化

クエリプロファイリングを有効にすることで、クエリの実行時間や使用リソースなどを詳細にモニタリングすることができます。

設定ファイル(my.cnf)に以下の設定を追加します。

profiling = 1

クエリ実行後に以下のクエリを実行することで、プロファイリング結果を取得できます。

SHOW PROFILES;

クエリーが処理される中で通過した”Status”と、かかった時間の”Duration”が出力されます。時間のかかっている部分を見直す事で最適化を計ります。

データベースの正規化

データベースの正規化はデータの整合性とパフォーマンスの両方を向上させるための重要な手法です。冗長なデータを排除し、データの一貫性を確保しましょう。以下にデータベースの正規化の実例を示します。

テーブルの正規化（ノーマライズ）

仮想的な顧客注文システムを考えてみましょう。最初の設計では、1つのテーブルにすべての情報を保存することが考えられます。

Order Table:
order_id | customer_name | product_name | quantity | price

しかし、この設計ではデータの冗長性が生じ、データの整合性が損なわれる可能性があります。そのため、正規化を行ってテーブルを分割します。

Order Table:
order_id | customer_id | product_id | quantity

Customer Table:
customer_id | customer_name

Product Table:
product_id | product_name | price

このようにテーブルを分割することで、データの整合性を維持し、データ冗長性を排除します。また、テーブルの結合を使用して必要なデータを取得することができます。

テーブルの非正規化（デノーマライズ）

一部の場合では、正規化されたデータベースよりもデノーマライズされたデータベースの方が効率的な場合があります。デノーマライズはテーブルの結合を減らし、パフォーマンスを向上させることができますが、データの整合性に注意が必要です

例えば、以下のような正規化されたデータベースがあるとします。

Customers Table:
customer_id | customer_name | customer_address

Orders Table:
order_id | customer_id | order_date | product_id | quantity | price

Products Table:
product_id | product_name | product_category

このデータベースでは、顧客情報（Customers）と注文情報（Orders）が別々のテーブルに分かれています。これは正規化の原則に従っていますが、クエリのパフォーマンスに影響を与える可能性があります。

考えられる効率化のためのデノーマライズの例として、以下のようなデノーマライズされたデータベースを検討してみましょう。

Orders Table:
order_id | customer_name | customer_address | order_date | product_name | product_category | quantity | price

このデノーマライズされたデータベースでは、注文情報に関連する顧客情報と商品情報が注文テーブルに統合されています。これにより、以下のような利点があります。

1. クエリの簡素化: 正規化されたデータベースでは、データの取得に複数のテーブルの結合が必要となりますが、デノーマライズされたデータベースでは、関連するデータが1つのテーブルにまとまっているため、クエリが簡素化されます。
2. クエリのパフォーマンスの向上: デノーマライズされたデータベースでは、データの取得に結合操作が不要になるため、クエリの実行速度が向上します。結合操作はリソースを消費するため、デノーマライズによってクエリのパフォーマンスが改善されます。
3. データの整合性の維持: デノーマライズされたデータベースでは、関連するデータが1つのテーブルに格納されるため、データの整合性を維持しやすくなります。正規化されたデータベースでは複数のテーブルにデータが分散しているため、整合性の確保がより複雑になる場合があります。

デノーマライズされたデータベースでは、デノーマライズによって得られる効率化は一時的なものであり、以下の点に留意する必要があります。

1. データの冗長性: デノーマライズによってデータが冗長化されるため、同じデータが複数の場所に格納される可能性があります。これにより、データの更新時に一貫性の維持が困難になります。冗長性が増すほど、データの整合性を保つための管理が複雑化し、更新時のエラーや不整合のリスクが高まります。
2. データの更新の影響範囲: デノーマライズされたデータベースでは、データの更新時に複数のテーブルや行を変更する必要がある場合があります。このため、データの更新が複雑になり、誤った更新や不整合の発生のリスクが高まります。正規化されたデータベースでは、データの更新は1つのテーブルに対して行われるため、更新の影響範囲が限定されます。
3. データの一貫性の維持: デノーマライズされたデータベースでは、データの一貫性を保つための制約やルールが不足している場合があります。正規化されたデータベースでは、データの整合性を維持するための制約や関連性が明確に定義されているため、データの一貫性が容易に維持できます。

以上の点を考慮すると、デノーマライズはパフォーマンスの向上に一時的な解決策として有効な場合もありますが、データの冗長性や更新の複雑さ、一貫性の維持の問題が生じる可能性があります。デノーマライズを検討する際は、データベースの要件やビジネスの要求に合わせて慎重に検討し、データの整合性とパフォーマンスのバランスを取る必要があります。

設定ファイルについて

MySQLに対する設定は、my.cnfというファイルに対して行います。

my.cnfの読み込み順序

MySQLに対する設定は、my.cnfというファイルに対して行います。
また、my.cnfの読み込まれる順序は、Linux環境では以下の順序になります。

また、MariaDBでは、設定ファイルは/etc/my.cnfになり、/etc/my.cnf.d以下の設定ファイル集が読み込まれる形になります。

環境によっては、/etc/mysql/mariadb.conf.d などの場合もありますので、自分の環境でどこに設置されているかは、locate などのコマンドで検索してください。

まとめ

MySQLのパフォーマンスチューニングは、クエリの最適化、データベースの設定の最適化、インデックスの適切な使用、キャッシングの最適化、データベースの正規化とデノーマライズなどの手法を組み合わせて行われます。これらの手法を適用することで、MySQLデータベースのパフォーマンスを劇的に向上させることができます。

パフォーマンスチューニングには多くの要素が関与するため、具体的なシナリオや環境によって最適な手法が異なる場合があります。重要なのは、パフォーマンスのボトルネックを正確に特定し、適切な手法を適用することです。また、パフォーマンスチューニングは継続的なプロセスであり、定期的にモニタリングと改善を行うことが重要です。

また、ここでは記載しませんでしたが、「スロークエリログ」を確認し、時間のかかっているクエリを「EXPLAIN（実行プラン）」で実行計画を確認し、SQL文を見直すという手法も有効です。MySQLでスロークエリログを発行するには設定ファイル（my.cnf）へ下記の設定を追記します。

slow_query_log=ON
long_query_time=0.5
slow_query_log_file=slow_query.log
log-queries-not-using-indexes

上の例では、SQLの実行に0.5秒以上かかったクエリを出力するように設定しています。 long_query_time というオプションで時間(秒)を指定しています。

「EXPLAIN（実行プラン）」は次の様にクエリの先頭に EXPLAIN をつけて実行します。

EXPLAIN SELECT * FROM users WHERE id = 1

EXPLAINを利用したSQLチューニングは解説すべき項目や注意点が多いので別の記事で詳細を解説したいと思っています。常にEXPLAINでSQLの実行計画を確認する習慣をつけると無駄のないSQLの組み方やインデックスの有効的な貼り方などが理解できるのでおすすめです。

データベースエンジニアとして、MySQLのパフォーマンスチューニングについて理解し、実際のシナリオに応じて適切な手法を選択することが重要です。データベースのパフォーマンスを最大限に引き出すために、常に学習と実践を続けましょう。

以上でMySQLのパフォーマンスチューニングについての解説を終わります。ご参考になれば幸いです。

スポンサーリンク

スポンサーリンク