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

はじめに

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

以前のガイドでは、MySQLで低パフォーマンスの低速クエリを特定する方法を検討しました。このガイドでは、低速で実行されるクエリを評価し、最適化する方法について議論することで、次のステップに進みます。これにより、以前の記事での議論を基に、パフォーマンスの低いクエリに関連する問題を特定し、修正するための完全な戦略が提供されます。

クエリプロファイリングを有効にして低速クエリの段階を理解する

低速クエリを最適化しようとするときに最初に行いたいことの1つは、クエリが正確にどこで最も時間を費やしているかを発見するために、クエリのプロファイリングを行うことです。

まず、クエリプロファイリングが有効になっているかを確認するには、次のように入力します。

SELECT @@PROFILING;

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

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

SET PROFILING = 1;

プロファイリングが有効になったら、次のように入力して利用可能なプロファイルをリスト表示できます。

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)

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

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

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のデフォルト表示を取得するには、次のように入力します。

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ステータスに長い時間が費やされていることを明確に示しており、これは実行された関数を直接管理しています。

異なる種類の情報を表示するように出力を変更することもできます。これは、デフォルトの出力を確認した後、より具体的な情報をチェックするのに役立つことが多いです。

たとえば、最初のクエリのブロック入出力に関する情報は、次のように入力することで確認できます。

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：クエリが必要としたスワップの数を表示します

クエリに関連する可能なすべての情報を表示するには、次のように入力します。

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つのJOINステートメントがあり、結果を構築するために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のクエリプランナーとオプティマイザのメカニズムには、クエリに応答するのにかかる時間を最小限に抑えるために、数えきれないほどの最適化が組み込まれています。

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