パフォーマンスを向上させるために SQL クエリをプロファイリングする方法

公開: 2023-03-16

Servebolt では、パフォーマンスに息を吹き込んでいます。

データベースのパフォーマンスも例外ではありません。

Web サイトの訪問者がリンクをクリックした後に非効率的なクエリを実行すると、ユーザー エクスペリエンスが大幅に低下します。 ページのレンダリングなどの他のアクションが実行される前に、スロー クエリが完全に実行されるまで待機する必要があります。これには数秒かかる場合があります。 この待機時間には、クエリの実行に必要な時間だけでなく、前処理と後処理に必要な追加の時間も含まれます。 その結果、不適切に設計されたクエリは、Web サイトの全体的なパフォーマンスを大幅に低下させ、ユーザー エクスペリエンスを苛立たせます。

Time to First Byte (TTFB) は、ユーザーが Web サイトにリクエストを行った後、データの最初のバイトが受信されるまでにかかる時間を測定する方法です。また、検索エンジンがサイトを評価する際に使用する重要な指標でもあります。 遅いクエリがトリガーされると、TTFB に悪影響を及ぼします。 遅いクエリの実行に時間がかかるほど、TTFB が高くなり、Web サイト全体のパフォーマンスが低下し、ユーザー エクスペリエンスの満足度が低下します。

このガイドでは、SQL クエリのプロファイルを作成する方法について説明します。これは、データベースの応答に依存する Web アプリケーションのパフォーマンスを維持するための重要な部分です。 これは、これらのクエリを最適化してパフォーマンスを向上させるための作業を開始できるようにするための基礎を設定するプロセスです。

SQL クエリ プロファイリングについて

Web アプリケーションを開発し、大規模な運用を開始すると、一度はスムーズに実行された SQL クエリがパフォーマンスの問題を引き起こす可能性があります。 一般的に言えば、1 秒あたりのリクエスト数の増加に伴い、増加するデータに対して実行されるクエリの数が増加する傾向があります。 また、パフォーマンスが低下すると、ユーザーがサイト、ソフトウェア、またはサービスを操作するときのエクスペリエンスも低下します。

クエリ プロファイリングは、データベース クエリを分析し、そのパフォーマンスを評価し、潜在的な問題を特定する方法です。

これらの問題のあるクエリを分析して特定することで、データベースのパフォーマンスに測定可能な違いをもたらす特定の改善を行うことができます。 これにより、アプリやサイトの応答性が向上するため、将来的にスケーラビリティが向上し、全体的な顧客満足度が向上します。

MariaDB (および MySQL) は、クエリ プロファイリング用のいくつかのツールと手法を提供します。これについては、この記事で説明します。 遅いクエリが特定されたら、次のステップはそれらを最適化することです。このプロセスには、問題の根本原因を特定し、クエリの構造を変更して効率を改善することが含まれます。

SQL クエリをプロファイリングする方法 (7 つの方法)

まず、低速で非効率的なクエリを特定するために使用できるさまざまなツールと手法を分析して、どこに改善の取り組みを集中させるべきかを理解することから始めましょう。

1 – EXPLAIN EXTENDEDコマンド

SQL クエリの分析に使用できるツールの 1 つは、 EXPLAINコマンドです。

クエリで EXPLAIN コマンドを実行すると、どのインデックスが使用されているか、検査された行数など、クエリがどのように実行されているかを確認できます。

EXPLAIN SELECT * FROM orders

JOIN customers ON orders.customer_id = customers.customer_id

WHERE customers.name = 'John Smith';

クエリでEXPLAINコマンドを実行すると、次のような複数の列を含む結果セットが返されます。

  • id: 実行計画におけるクエリの一意の識別子
  • select_type : SIMPLE や SUBQUERY などのクエリのタイプ
  • table : クエリ対象のテーブル
  • type : JOIN や INDEX など、使用される結合タイプ
  • possible_keys : MariaDB または MySQL がクエリを処理するために使用できた可能性のあるインデックス
  • key : MariaDB または MySQL が実際にクエリを処理するために使用したインデックス
  • key_len : 使用されたキーの長さ
  • rows : MariaDB または MySQL がクエリで調べると推定される行数

Extra : これには、全テーブル スキャンが実行されたかどうか、または一時テーブルが使用されたかどうかなど、クエリに関する追加情報が含まれます。

EXPLAINコマンドの出力を分析することにより、通常、パフォーマンスの潜在的なボトルネック (不十分なインデックス作成、次善の結合タイプ、多数の検査行など) を特定できます。

たとえば、タイプ列に「 index」ではなく「ALL」が表示されている場合、クエリは完全なテーブル スキャンを実行しているため、ほぼ確実にパフォーマンスが低下します。 キー列が NULL の場合、MySQL はインデックスを使用していないため、これも遅くなります。 行列の値が高い場合は、多くの行が検査されていることを意味し、パフォーマンスがさらに低下します。

追加情報を提供するために、 EXPLAIN EXTENDEDバリエーションを使用することをお勧めします。

注:これは MySQL では非推奨ですが、MariaDB では引き続き使用できます。

EXTENDED オプションを使用すると、検査された行数、返された行数、使用された JOIN のタイプ、スキャンされたテーブルの順序、使用されたインデックス、期間などの有用な情報を確認できます。クエリの実行が必要でした。

EXPLAIN EXTENDED コマンドを使用すると、次のようになります。

EXPLAIN EXTENDED SELECT * FROM your_table WHERE column_name = 'value';

この例では、EXPLAIN コマンドは、クエリを実行するためにデータベースが実行する手順のリストと、データベースが使用するリソースのリストを表示します。

このコマンドを使用すると、クエリのボトルネックをより簡単に特定できるようになり、必要な変更を加えてボトルネックを軽減し、クエリのパフォーマンスを向上させることができます。

たとえば、EXPLAIN EXTENDED コマンドを使用すると、インデックスを追加する必要性を特定し、JOIN 条件を最適化し、クエリによって返される行の総数を制限するのに役立ちます。

また、正確な結果が得られるように、このテストと最適化を実行するときにクエリ キャッシュを無効にしたことを確認する必要があります。 これを行うには、クライアントに接続するときに最初にこのコマンドを実行します。

SET SESSION query_cache_type=0;

クエリにこれらの変更を加えたら、そのパフォーマンスを再度テストして、どの程度の改善が達成されたかを特定します (ある場合)。 クエリのプロファイリングと最適化の場合と同様に、このプロセスは反復的であることを忘れないでください。EXPLAIN EXTENDED コマンドを使用し、その後にパフォーマンス テストを数回行うことを想定してください。

2 – EXPLAIN ANALYZEコマンド

このコマンドは、クエリの実行計画を分析し、クエリの実行にかかった実際の時間や実際に調べた行数などのパフォーマンス メトリックを返すために使用されます。 EXPLAIN ANALYZE コマンドの結果を分析することで、インデックスの不足や調査が必要な行数の増加など、クエリの実行における潜在的なボトルネックを特定できます。

3 – スロー クエリ ログ

これは MariaDB (および MySQL) の組み込み機能で、実行に一定時間以上かかるすべてのクエリをログに記録します。 スロー クエリ ログは、特定のしきい値 (1 秒など) よりも時間がかかるクエリをログに記録するように構成できます。

Servebolt では、実行に 1 秒以上かかるすべてのクエリがスロー クエリ ログに記録されます。 これは、ほとんどのクエリが一瞬で実行されるためです。 WordPress を実行しているサイトなどの Web アプリケーションのコンテキストでは、1 つのページを読み込むには 10 ～ 100 のデータベース クエリが必要です。ページを HTML にコンパイルしてユーザーに返す前に、これらすべてを順番に実行する必要があります。

現在の Servebolt Cloud 構成では、スロー クエリ ログがグローバル ログ サーバーに保持されます。 必要が生じた場合は、当社のサポート チームにご連絡ください。関連するログのファイルをフィルター処理し、出力を提供します。

独自の環境では、MariaDB または MySQL 構成ファイル (my.cnf または my.ini) に次の行を追加することで、スロー クエリ ログを有効にすることができます。

log_slow_queries = /path/to/slow.log

long_query_time = 1

4 – 視覚的な説明計画

ビジュアル EXPLAIN プランは、 EXPLAIN コマンド出力のグラフィカルな表現を提供し、クエリの実行を理解しやすくし、パフォーマンスの問題を検出しやすくします。

注: Visual Explain Plan は、Web アプリケーションの開発中に役立ちます。

プレーン テキスト出力の代わりに、クエリの実行をツリー構造で表示します。各ノードはテーブル、インデックス、または操作を表し、それらの間の接続は操作の順序を表します。

MySQL WorkbenchやEXPLAIN Analyzerなどのさまざまなツールは、視覚的な説明計画を生成し、実行計画をナビゲートして各操作を詳細に調べるための対話型インターフェイスを提供します。

たとえば、MySQL Workbench では、クエリを実行して結果タブの[ Explain Plan ] ボタンをクリックするだけで、視覚的な説明計画を簡単に生成できます。これは、各操作の詳細情報とともに、クエリ実行プランをグラフィカルに表示します。 これにより、パフォーマンスの問題を特定し、必要に応じてクエリを最適化できます。

5 – MySQL チューナー

MySQL Tuner は、データベース サーバーのパフォーマンスと構成をチェックし、改善のための推奨事項を提供するスクリプトです。 クエリの総数、スロー クエリの数、現在のバッファ プールの使用状況などの情報を含む、現在のサーバー ステータスの概要を提供します。

また、データベースのバージョン、使用中のストレージ エンジン、クエリ キャッシュの構成など、他のさまざまな設定を確認するためにも使用でき、現在のワークロードに基づいてこれらの設定を最適化するための推奨事項を提供します。

他のツールとの主な違いの 1 つは、サーバー自体またはリモートで実行できるコマンド ライン ツールであるため、データベース パフォーマンスの監視と最適化のプロセスを簡単に自動化できることです。

注: Web アプリケーション (およびデータベース) がすでに Servebolt クラウドでホストされている場合 – これは私たちのチームが専門としており、ツールが提供できる推奨事項よりも優れた機能を提供できます。

6 – クエリ プロファイラ

MariaDB Enterprise Query Analyzer 、 Dataedo 、 Percona Toolkitなど、SQL クエリのプロファイリングに使用できるサードパーティのクエリ プロファイラーがあります。 サードパーティのクエリ プロファイラーは、MariaDB (または MySQL) で利用可能な組み込みツールと比較して、追加の機能を提供できます。

注:クエリ プロファイラーは、Web アプリケーションを開発しているときに役立ちます。

たとえば、実行時間やロック待機時間など、クエリのパフォーマンスに関するより詳細な情報を提供したり、組み込みツールでは不可能な方法でデータを視覚化したりすることができます。

組み込みのツールでニーズが十分に満たされる場合は、サードパーティのクエリ プロファイラーを使用する必要はありません。 ただし、より詳細な情報や高度な機能が必要な場合は、サードパーティのプロファイラーを検討する価値があります。

7 – 監視ツールによるプロファイリング

Prometheus、Grafana、Nagios など、クエリのプロファイリングやデータベースのパフォーマンスの監視に使用できる監視ツールも多数あります。

Prometheus は、メトリック データを収集、保存、クエリできる効率的な監視システムであり、貴重な洞察をリアルタイムで得ることができます。MariaDB (および MySQL) と統合して収集したメトリックを保存し、効果的な視覚化のために Grafana が付属しています。

Grafanaは、Prometheus から収集されたデータを監視および視覚化するために使用できる、強力なオープンソースの分析ツールです。カスタム ダッシュボードとアラートを設定すると、データベースのパフォーマンスをリアルタイムで監視できます。

Nagios は、データベースの健全性を常に監視するのに役立ちます。CPU、RAM、ディスク容量などの主要なリソースを監視するように設定でき、他のサービスやネットワーク デバイスも追跡できます。 高度な設定が可能なため、積極的なデータベース クエリの監視に最適なツールです。

これらのサーバー監視ツールを使用すると、パフォーマンスの問題を追跡して迅速に対処できるため、データベース サーバーをスムーズに実行できます。

一般的なクエリ最適化手法

SQL クエリのパフォーマンスを向上させるために使用できる一般的なクエリ最適化手法がいくつかあります。

1 – 索引付け

インデックスは、特にフィルター (WHERE) を使用するクエリを高速化する方法です。 インデックスを使用すると、データベース エンジン (MariaDB または MySQL) のデータ構造が特定のテーブルの外側になり、クエリしようとしているデータがポイントされます。 インデックスを使用してデータベース クエリを改善するには、独自の記事が必要になるため、この投稿ではあまり詳しく説明しません。これについては、今後取り上げる予定です。

たとえば、注文 ID、顧客 ID、注文日などの情報を含む数百万行のデータを含む「注文」という大きなテーブルを考えてみましょう。 顧客 ID 列にインデックスを付けずに、特定の顧客が行ったすべての注文を取得するためにクエリを実行すると、MariaDB はテーブル全体をスキャンして関連データを見つける必要があります。 これには、特に大きなテーブルの場合、かなりの時間とリソースがかかる可能性があります。

大まかに言えば、特定のクエリを繰り返し実行し、読み取りパフォーマンスが重要であると確信している場合はいつでも、インデックス (または複数) を作成することが、そのクエリを高速化するための適切なアプローチになる可能性があります。

WordPress のコンテキストでは、これは非常に一般的です。 多くのプラグインは、(便宜上) インデックスを使用せずに一般的な共有テーブルを使用する開発者によって構築されています。 その結果、パフォーマンスが大幅に向上することが多い領域でもあります。

特定のテーブルに存在するインデックスを表示するには、

以下のwp_postmetaテーブルの例のように、 SHOW INDEX FROMを使用して特定のテーブルに存在するインデックスを表示できます。

MariaDB [db_name] > SHOW INDEX FROM wp_postmeta;

あるシナリオでは、最近 wp_postmeta テーブルにsb_postid_metakeyとsb_postid_metakey_metavalの 2 つのインデックスを作成しました。

これらのインデックスは、上位の低速クエリを調べて、多くの (AND/OR) 比較条件に加えて WHERE を使用してフィルター処理する SELECT ステートメントであるという特性によって、すべてが比較的類似していることを発見したことに基づいて追加されました。 これを見て、使用されているテーブルの現在のインデックスを確認し、クエリでEXPLAIN EXTENDEDを実行して、アプローチをさらに検証しました。

クエリはほとんど機能しており、 JOINを使用してwp_postmetaテーブルを使用していました。これが発生した順序に基づいて、これらのインデックスを追加すると、MariaDB (または MySQL) は、すべての行でテーブル全体をスキャンする代わりに、インデックスから応答を取得できます。

CREATE INDEX sb_postid_metakey ON wp_postmeta (post_id, meta_key);

CREATE INDEX sb_postid_metakey_metaval ON wp_postmeta (post_id, meta_key, meta_value);

これは、自由に使えるツール (上で概説したように) を使用して「物事を理解する」ことと、データベースのデータ型と内容に関する知識を組み合わせたものです。 これは決して常に機能するとは限りません。 実行したとしても、パフォーマンスが常に 500% 向上するとは限りません。 巨大なインデックスを持つと、すべての行をスキャンするよりも遅くなる可能性があるため、確実にインデックスを適用する前後にクエリをテストする必要があります。

注:インデックスの速度をテストする場合は、次を使用して、セッションのクエリ キャッシュを無効にする必要があります。

SET SESSION query_cache_type=0;

この場合、インデックスを使用する前は、クエリの実行に 10.437 秒かかりました。 2 つのインデックスを作成した後、同じクエリに [秒数] かかりました。

2 – データアクセスの削減

データ アクセスの削減。つまり、クエリを実行するためにアクセスする行と列の数を最小限に抑えます。これは、クエリによって取得されたデータをフィルター処理し、インデックスを使用し、大きなテーブルを分割することで実現できます。 ほとんどの人が行う必要がある (またはできる) ことではありませんが、データベース クエリをゼロから設計する場合に留意すべき重要な点です。

たとえば、データベース クエリがログイン目的でユーザーに関するデータを検索する場合、クエリは LIMIT 1 にする必要があります。

注:これは、最適化よりもデータベースの設計に関連しています。パフォーマンスを維持することは重要ですが、この取り組みは、大多数のエンド ユーザーよりもプラグイン開発者 (WordPress のコンテキストで) に関連しています。

データ アクセスに変更を加えた後に速度をテストする前に、次のコマンドを実行してクエリ キャッシュを無効にしていることを確認してください。

SET SESSION query_cache_type=0;

3 – データパーティショニングの使用

データを小さなチャンクに分割することで、データベースはより効率的になり、管理にかかる時間が短縮されます。 この戦略は、バックアップや更新などのメンテナンス プロセスにかかる時間を削減し、管理する必要があるデータの量を制限するのに役立ちます。 全体として、パフォーマンスを向上させ、リソースの使用を最適化するのに役立ちます。

データベース内のデータを分割するには、次の手順に従います。

  1. パーティション化するテーブルを選択するときは、大量のデータを保持し、分割することでメリットが得られるテーブルを選択してください。 これにより、システムが最適化され、クエリのパフォーマンスが向上します。
  2. データベースに適したパーティション分割方法を選択することは非常に重要です。 データの構造と実行する予定のクエリに応じて、範囲、リスト、ハッシュ、またはキー パーティション分割から選択できます。 パフォーマンスと結果を最適化するために、ニーズに最も適したものを選択してください。

    1. 範囲分割は、特定の範囲に分割できるデータがある場合に理想的な選択肢です。たとえば、複数年のデータを含むテーブルがある場合は、レンジ パーティションを作成して整理できます。 問題の列の日付または数値に基づいている可能性があります。
    2. リスト パーティショニングは、特定のパラメーターに従ってさまざまなグループに簡単に分離できるデータを処理するための効率的な手法です。たとえば、部門別に分類された従業員の情報を含むテーブルがあるとします。 これには、リスト パーティショニングの使用が必要です。
    3. ハッシュ パーティショニングは、特定の列のハッシュ値に基づいてデータを同じサイズのクラスターに配置するための効果的な戦略です。これにより、複数のパーティション間でデータを均等に分散できるため、データを効率的に分散するための優れた選択肢になります。
    4. キー パーティション分割はハッシュ パーティション分割に似ていますが、主な違いは、データをさまざまなグループに分割するための基準として特定の列の値を使用することです。これにより、一意の識別子または自然キーに基づいて個別のグループに分割できるデータ セットに最適です。
  3. 分割テーブルを作成すると、元のテーブルをより小さなテーブルに効果的に分割できます。 これは、CREATE TABLE ステートメントにパーティショニング句を追加することによって実現されます。ここでは、セグメンテーションに必要な方法と条件を指定します。 これにより、クエリのパフォーマンスが向上し、データ管理がより効率的になります。
  4. INSERT INTO… SELECT ステートメントを使用して、元のテーブルから新しく分割されたテーブルにデータをすばやくコピーできます。 これにより、パーティション分割されたテーブルにすべての関連情報が簡単に入力されます。
  5. 分割されたテーブルを利用するには、アプリケーションを再構成する必要があります。 これにより、元のテーブルが置き換えられ、アプリケーションがより効率的になります。
  6. テストを実行して潜在的なパフォーマンスの改善を評価する前に、まず次のコマンドを実行してクエリ キャッシュを無効にすることが不可欠です: SET SESSION query_cache_type=0;
  7. パーティション分割されたテーブルがスムーズに実行されるようにするには、そのパフォーマンスを注意深く監視することが重要です。 問題が発生した場合は、パーティショニング条件を調整するか、別の方法に切り替えると解決する可能性があります。 パーティションを定期的に監視することで、パーティションの可能性を最大限に引き出すことができます。

アップグレードのスクリプト作成と分割テーブルに関する重要な注意事項

データベースをパーティショニングすると効率が向上する可能性がありますが、アップグレード スクリプトを実行してデータベース スキーマを変更することによって発生する潜在的な問題に留意することが重要です。 これらのアップグレードのスクリプトを作成するときは、分割されたテーブルを考慮することが不可欠です。 分割されたテーブルがアップグレード スクリプトで説明されていない場合、ほぼ確実にサイトの誤動作につながる潜在的な問題が発生する可能性があります。

たとえば、パーティション分割されたテーブルに新しい列を追加するスクリプトを作成すると、1 つのパーティションしか変更されず、データ内に不整合や問題が生じる可能性があります。 同様に、パーティション分割されたテーブルにインデックスを追加するためにアップグレード スクリプトが作成された場合、1 つのパーティションにしかインデックスを生成できないため、パフォーマンスが低下し、一貫性のない結果が得られます。

このような問題を回避するには、分割されたテーブルを考慮するようにアップグレード スクリプトを設計する必要があります。 これには、各パーティションで個別にスクリプトを実行するか、パーティション化されたテーブルで動作するようにスクリプトを修正することが含まれます。 また、徹底的なテストを実施して、アップグレード プロセスで予期しない問題やデータの損失が発生しないことを確認することも重要です。

4 – レディス

Servebolt のお客様にとって、 Redis はクエリの最適化に役立つ (有料の) アドオンです。

Redis (リモート ディクショナリ サーバーとも呼ばれます) は、データをメモリ内に格納するオープン ソース ソリューションであり、キャッシュ、データベース、またはメッセージ ブローカーとしても使用できます。 データベースと統合してパフォーマンスを向上させることができ、アプリケーションとデータベースの間の効率的な仲介者として機能します。

データベースの負荷を軽減することで、アプリケーションのパフォーマンスと応答時間を改善します。 これは、頻繁に使用されるデータをリクエストごとにデータベースではなく Redis に保存することで行われるため、かなりの時間を節約できます。

プラグインを適切に設定することで、Redis をデータベースで使用して、クエリの実行を最適化できます。 必要なデータが Redis に存在しない場合、アプリケーションはデータベースからデータを取得し、後で使用できるように Redis に保存します。 これにより、データ取得がはるかに高速かつ効率的になります。

このアプローチを使用することで、アプリケーションは Redis の迅速なメモリ内アクセスの恩恵を受け、必要に応じてデータベースのデータを保存およびアクセスすることもできます。

初めて Redis を実装する場合は、パフォーマンス テストを実行する前にクエリ キャッシュを無効にする必要があることに注意してください。 これを行うには、次のコマンドを使用します。

SET SESSION query_cache_type=0;

結論

MariaDB と MySQL のエコシステムには、データベース クエリ実行のボトルネックを簡単に発見できるようにするさまざまなツールと方法があり、Web アプリケーションのパフォーマンスを向上させることができます。

スローダウンは、アプリケーションを実行している間ずっと発生する可能性があります。 それらを回避しようとすることは素晴らしいことですが、最終的には、パフォーマンスの問題の診断を開始するときにどこを見ればよいかを知る必要があります。 実行するデータベースのサイズと性質に応じて、これは継続的な監視、トラブルシューティング、継続的な改善を必要とする反復プロセスであり、データベースのパフォーマンスを高い水準に保つ必要があります。