MySQL でのスロークエリのプロファイリングと最適化

はじめに

データベースシステムのパフォーマンスを維持することは、データベースを利用したアプリケーションの応答性を最適化する上で不可欠な部分です。これには、サーバーのハードウェアリソースとソフトウェア構成の管理が含まれますが、実行を要求するクエリにも関係します。

前のガイドでは、MySQL でパフォーマンスの低いスロークエリを特定する方法を調べました。このガイドでは、次のステップとして、実行速度の遅いクエリを評価および最適化する方法について説明します。これは、パフォーマンスの低いクエリに関連する問題を特定して修正するための完全な戦略を提供するために、前の記事の議論に基づいて構築されます。

スロークエリの段階を理解するためにクエリプロファイリングを有効にする

スロークエリを最適化しようとする際に最初に行うことの 1 つは、クエリをプロファイリングして、クエリが最も時間を費やしている場所を正確に発見することです。

まず、クエリプロファイリングが有効になっているかどうかを typing で確認します

SELECT @@PROFILING;

 +-------------+
 | @@profiling |
 +-------------+
 |           0 |
 +-------------+
 1 row in set, 1 warning (0.00 sec)

0 は、プロファイリングが現在無効になっていることを示します。プロファイリングを有効にするには、typing で入力します

SET PROFILING = 1;

プロファイリングが有効になったら、typing で利用可能なプロファイルのリストを開始できます

SHOW PROFILES;

 +----------+------------+--------------------------+
 | Query_ID | Duration   | Query                    |
 +----------+------------+--------------------------+
 |        1 | 3.00368075 | select sleep(3)          |
 |        2 | 0.00740700 | select * from mysql.user |
 |        3 | 0.00075875 | select @@profiling       |
 +----------+------------+--------------------------+
 3 rows in set, 1 warning (0.00 sec)

出力には、プロファイリングが有効になってから現在のセッションで実行された最新のステートメントが表示されます。各クエリには、合計実行時間と、より深く掘り下げるために使用できるクエリ番号が表示されます。

最新のクエリのプロファイリング情報を表示するには、typing で入力します

SHOW PROFILE;

 +----------------------+----------+
 | Status               | Duration |
 +----------------------+----------+
 | starting             | 0.000144 |
 | checking permissions | 0.000020 |
 | Opening tables       | 0.000024 |
 | init                 | 0.000013 |
 | optimizing           | 0.000047 |
 | executing            | 0.000032 |
 | end                  | 0.000010 |
 | query end            | 0.000016 |
 | closing tables       | 0.000012 |
 | freeing items        | 0.000367 |
 | cleaning up          | 0.000074 |
 +----------------------+----------+
 11 rows in set, 1 warning (0.00 sec)

デフォルトでは、出力には、ステートメントがクエリ処理の各段階に費やした時間が正確に表示されます。これは、クエリのどの部分のパフォーマンスが低いかを正確に特定するのに役立ちます。

別のクエリに関連付けられた結果を表示するには、SHOW PROFILES 出力によって提供されるクエリ ID とともに FOR QUERY <N> 句を追加できます。

たとえば、クエリ 1 のデフォルト表示を取得するには、typing で入力できます

SHOW PROFILE FOR QUERY 1;

 +----------------------+----------+
 | Status               | Duration |
 +----------------------+----------+
 | starting             | 0.001419 |
 | checking permissions | 0.000028 |
 | Opening tables       | 0.000030 |
 | init                 | 0.000020 |
 | optimizing           | 0.000020 |
 | executing            | 0.000025 |
 | User sleep           | 3.000165 |
 | end                  | 0.000043 |
 | query end            | 0.000019 |
 | closing tables       | 0.000012 |
 | freeing items        | 0.000540 |
 | logging slow query   | 0.001320 |
 | cleaning up          | 0.000041 |
 +----------------------+----------+
 13 rows in set, 1 warning (0.00 sec)

ここで、出力は User sleep ステータスに長い時間が費やされていることを明確に示しており、これは実行された関数を直接管理します。

出力の表示形式を変更して、さまざまな種類の情報を表示することもできます。これは、より具体的な情報を確認するためにデフォルト出力を表示した後で役立つことがよくあります。

たとえば、最初のクエリのブロック入力と出力に関する情報を typing で確認できます

SHOW PROFILE BLOCK IO FOR QUERY 1;

 +----------------------+----------+--------------+---------------+
 | Status               | Duration | Block_ops_in | Block_ops_out |
 +----------------------+----------+--------------+---------------+
 | starting             | 0.001419 |          184 |             0 |
 | checking permissions | 0.000028 |            0 |             0 |
 | Opening tables       | 0.000030 |            0 |             0 |
 | init                 | 0.000020 |            0 |             0 |
 | optimizing           | 0.000020 |            0 |             0 |
 | executing            | 0.000025 |            0 |             0 |
 | User sleep           | 3.000165 |            0 |             0 |
 | end                  | 0.000043 |            0 |             0 |
 | query end            | 0.000019 |            0 |             0 |
 | closing tables       | 0.000012 |            0 |             0 |
 | freeing items        | 0.000540 |            0 |             0 |
 | logging slow query   | 0.001320 |           64 |             8 |
 | cleaning up          | 0.000041 |            0 |             0 |
 +----------------------+----------+--------------+---------------+
 13 rows in set, 1 warning (0.00 sec)

以前の情報に加えて、これにより、MySQL がファイルシステムのブロックデータと対話する必要があった時間が表示されます。

指定できる使用可能な出力バリエーションには、次のようなものがあります

• ALL: 利用可能なすべての情報を表示します
• BLOCK IO: クエリが生成した入力および出力のブロック数を表示します
• CONTEXT SWITCHES: クエリの実行中に発生した自発的および非自発的なコンテキストスイッチの数を表示します
• CPU: ユーザースペースおよびシステムカテゴリの CPU 時間を表示します
• IPC: 送受信されたプロセス間メッセージの数を表示します
• PAGE FAULTS: ページフォールトの数を表示します。メジャーフォールトとマイナーフォールトに分類されます
• SOURCE: 実行マッピングを可能にするために、MySQL 内で実行されている実際の関数をソースファイルと行番号とともに表示します
• SWAPS: クエリに必要なスワップの数を表示します

クエリに関連付けられた可能なすべての情報を表示するには、typing で入力します

SHOW PROFILE ALL FOR QUERY 1\G

追加の出力が多数表示されるため、結果を読みやすくするために、\G ステートメント終了シーケンスで代替出力フォーマットをトリガーします。

プロファイリング情報を使用して、クエリのどの部分が遅いかについて良いアイデアを得たら、リソースを節約するためにプロファイリングをオフに戻すことができます

SET PROFILING = 0;

クエリパフォーマンスを理解するために EXPLAIN を使用する

クエリプロファイルは、実行に最も時間がかかっているクエリ実行の段階と、クエリに関連付けられているリソースを理解するのに役立つはずです。次に、MySQL の EXPLAIN ステートメントを使用して、クエリオプティマイザーによるクエリの評価を理解できます。

EXPLAIN ステートメントは、SELECT、DELETE、UPDATE、INSERT、または REPLACE ステートメントを受け取り、クエリオプティマイザーが指定されたクエリをどのように評価および実行するかを表示します。出力には、MySQL がテーブルを結合する方法、フィールドを選択する方法、結果をソートおよびフィルタリングする方法などが表示されます。また、評価される行数や、プロセスを高速化するために参照されるインデックスなど、さまざまな情報も表示されます。

このステートメントの動作を理解するために、MySQL にクエリ SELECT * FROM INFORMATION_SCHEMA.VIEWS; をどのように実行するかを説明してもらいます

EXPLAIN SELECT * FROM information_schema.views\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cat
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: name
      key_len: 194
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: vw
   partitions: NULL
         type: ref
possible_keys: schema_id,type,view_client_collation_id,view_connection_collation_id,type_2
          key: type
      key_len: 1
          ref: const
         rows: 100
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: sch
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,catalog_id
          key: PRIMARY
      key_len: 8
          ref: mysql.vw.schema_id
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: conn_coll
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mysql.vw.view_connection_collation_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: client_coll
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,character_set_id
          key: PRIMARY
      key_len: 8
          ref: mysql.vw.view_client_collation_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: cs
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mysql.client_coll.character_set_id
         rows: 1
     filtered: 100.00
        Extra: NULL
6 rows in set, 1 warning (0.01 sec)

上記の出力は、そのクエリの情報をまとめるために 6 つの個別のクエリが必要であることを示しています。テーブル定義を見ると、理由がわかります

SHOW CREATE TABLE information_schema.views\G

フォーマットを変更すると、結果のテーブル作成コマンドは次のようになります

CREATE algorithm=undefined definer=`mysql.infoschema`@`localhost` SQL security definer view `views`
AS
  SELECT `cat`.`name`                                                                                                             AS `table_catalog`,
         `sch`.`name`                                                                                                             AS `table_schema`,
         `vw`.`name`                                                                                                              AS `table_name`,
         IF((can_access_view(`sch`.`name`,`vw`.`name`,`vw`.`view_definer`,`vw`.`options`) = TRUE),`vw`.`view_definition_utf8`,'') AS `view_definition`,
         `vw`.`view_check_option`                                                                                                 AS `check_option`,
         `vw`.`view_is_updatable`                                                                                                 AS `is_updatable`,
         `vw`.`view_definer`                                                                                                      AS `definer`,
         IF((`vw`.`view_security_type` = 'DEFAULT'),'DEFINER',`vw`.`view_security_type`)                                          AS `security_type`,
         `cs`.`name`                                                                                                              AS `character_set_client`,
         `conn_coll`.`name`                                                                                                       AS `collation_connection`
  FROM   (((((`mysql`.`tables` `vw`
  JOIN   `mysql`.`schemata` `sch`
  ON    ((
                       `vw`.`schema_id` = `sch`.`id`)))
  JOIN   `mysql`.`catalogs` `cat`
  ON    ((
                       `cat`.`id` = `sch`.`catalog_id`)))
  JOIN   `mysql`.`collations` `conn_coll`
  ON    ((
                       `conn_coll`.`id` = `vw`.`view_connection_collation_id`)))
  JOIN   `mysql`.`collations` `client_coll`
  ON    ((
                       `client_coll`.`id` = `vw`.`view_client_collation_id`)))
  JOIN   `mysql`.`character_sets` `cs`
  ON    ((
                       `cs`.`id` = `client_coll`.`character_set_id`)))
  WHERE  ((
                       0 <> can_access_table(`sch`.`name`,`vw`.`name`))
         AND    (
                       `vw`.`type` = 'VIEW'))

5 つの結合ステートメントがあります。つまり、結果を構築するには 6 つの個別のテーブルをクエリする必要があります。これは、実行した EXPLAIN ステートメントで返されたエントリの数（テーブルごとに 1 つ）と一致します。

EXPLAIN ステートメントの結果を解釈するには、個々の列がそれぞれ何を表しているかを理解する必要があります。特に興味深いのは、type、key、rows、および filtered 列です。

type: const

type: eq_ref

type: index

type: ref

. . .
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mysql.client_coll.character_set_id
. . .

         rows: 1
     filtered: 100.00

高速に実行するためにスロークエリを書き換える

MySQL で個々のステートメントのパフォーマンスを評価し、速度低下の原因に関する詳細情報を取得する方法について説明しましたので、そのパフォーマンスを向上させる方法について説明します。

検討する正確な手順は、クエリプロファイリングと EXPLAIN 調査で何が見つかったかによって大きく異なります。以下に、クエリパフォーマンスを最適化するための最も一般的な方法をいくつか示します。

CREATE INDEX <index_name> ON <table> (<columns_to_index>)

CREATE INDEX last_and_first_names ON person (last_name, first_name);

SELECT * FROM <table> LIMIT 5

結論

このガイドでは、個々のクエリを評価し、速度低下の原因に関する詳細情報を取得するための基本的な方法のいくつかについて説明しました。クエリのプロファイリングと、クエリプランナーがクエリをどのように評価するかに関する詳細情報を取得するための MySQL の EXPLAIN ステートメントの使用について説明しました。次に、データベースがデータを取得する方法により適切に一致するようにクエリを変更することで、これらの速度低下を回避できる方法をいくつか見てきました。

データベースは複雑なソフトウェアであり、さまざまな形式で大量のデータを扱う責任を負っています。必要なデータを単に取得するだけでなく、データベースは高速に結果を返すことが期待されています。MySQLのクエリプランナーとオプティマイザーのメカニズムには、クエリの応答にかかる時間を最小限に抑えるために、数え切れないほどの最適化が組み込まれています。

しかし、データベースが自動的に最適化できる範囲には限りがあります。データベースのユーザーとして、アクセスするデータ、クエリの要件、最適化する最も重要なクエリを理解する必要があります。そのためには、基準を評価し、クエリのパフォーマンスが期待どおりでない場合に調整する方法を理解することが重要です。