SQL効率を分析する方法を説明する

Explain コマンドは、データベースのパフォーマンス問題を解決するために最初に推奨されるコマンドです。ほとんどのパフォーマンスの問題は、このコマンドで簡単に解決できます。Explain を使用すると、SQL ステートメントの実行効果を表示できます。これにより、より適切なインデックスを選択してクエリステートメントを最適化し、より最適化されたステートメントを記述できるようになります。

構文の説明:

EXPLAIN tbl_name または: EXPLAIN [EXTENDED] SELECT select_options

前者はテーブルのフィールド構造などを導出することができ、後者は主に関連するインデックス情報を提供します。今日は後者に焦点を当てます。

例：

説明する 
  SELECT sum(金額) 
顧客Aからの支払いb 
  ここで1 = 1 
かつ、a.customer_id = b.customer_id 
かつ、a.email = '[email protected]';

実行結果:

それぞれの属性を見てみましょう:

1. id:これはSELECTのクエリシーケンス番号です

2. select_type: select_type は選択のタイプであり、次のいずれかになります。

SIMPLE: 単純な SELECT (UNION やサブクエリなどは使用しません)

PRIMARY: 最も外側のSELECT

UNION: UNION内の2番目以降のSELECT文

依存UNION: UNION内の2番目以降のSELECT文は外側のクエリに依存します。

UNION RESULT: UNION の結果。

SUBQUERY: サブクエリの最初のSELECT

依存サブクエリ: 外部クエリに依存するサブクエリの最初のSELECT

DERIVED: エクスポートされたテーブルのSELECT (FROM句のサブクエリ)

3. テーブル:この行のデータが関連する実際のテーブル名 (select * from customer; など) またはテーブル別名 (select * from customer a など) を表示します。

4. type:この列は最も重要です。使用されている接続の種類とインデックスが使用されているかどうかを示します。Explain コマンドを使用してパフォーマンスのボトルネックを分析するための重要な項目の 1 つです。

最良から最悪までの結果は次の通りです:

システム > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般的に、クエリが少なくとも範囲レベル、できれば参照レベルに到達するようにする必要があります。そうしないと、パフォーマンスの問題が発生する可能性があります。

all : テーブルの最初の行から始めて、テーブル全体を行ごとにスキャンすることを意味します。運が悪ければ、最後の行をスキャンする可能性があります。

インデックス: すべてよりもわずかに優れたパフォーマンス。
簡単に言うと、all はすべてのデータ行をスキャンします。これは data_all に相当します。index はすべてのインデックスノードをスキャンします。これは index_all に相当します。

注: すべてはディスクに沿ってスキャンされ、インデックスはインデックスに沿ってスキャンされます

範囲: クエリを実行するときに、インデックスに基づいて範囲をスキャンできることを意味します

customer_id > 4 の場合、select * from customer を説明します。

index_subqueryサブクエリでは、一意のインデックス以外のインデックスに基づいてスキャンします。

unique_subquery は、EQ_REF と同様に、サブクエリ内の一意のインデックスに基づいてスキャンします。

index_merge複数範囲スキャン。 2 つのテーブルの接続では、各テーブルの接続フィールドにインデックスがあり、インデックスが順序付けられ、結果が結合されます。集合の和集合および積集合の演算に適用できます。

ref_or_nullはREFに似ていますが、検索条件に、col = 2またはcolがnullなど、接続フィールドの値がNULLになる可能性があるケースが含まれる点が異なります。

全文全文インデックス

refこれも、単一の値に一致するすべての行を返すインデックスアクセスです。ただし、複数の条件に該当する行が見つかる可能性があるため、検索とスキャンを組み合わせる必要があります (範囲間隔も指定しますが、範囲よりも正確です)。

select * from payment where customer_id =4; を説明します。

eq_ref は、インデックス列を通じてデータ行 (1 行のデータに正確) を直接参照することを意味します。これは結合クエリでよく使用されます。

const、system、null MySQL がクエリの一部を最適化し、それを定数に変換できる場合、このアクセスタイプが使用されます。たとえば、行の主キーを where 条件として指定すると、MySQL はそれを定数に変換してからクエリを実行できます。

5. possible_keys:この列は、MySQLがテーブル内の行を見つけるために使用できるインデックスを示します。

6. key: MySQL が実際に使用することを決定したキー (インデックス) を表示します。インデックスが選択されていない場合、キーはNULLになります

7. key_len: MySQL が使用するキーの長さを表示します。キーが NULL の場合、長さは NULL になります。使用するインデックスの長さ。長さが短いほど、精度を失わずに良くなります。

8. ref:テーブルから行を選択するためにキーと一緒に使用される列または定数を示します。

9. 行数:クエリを実行するために MySQL が調べる必要があると判断する行数を表示します。

10. 追加: MySQL がクエリを解決する方法に関する詳細な情報が含まれており、重要な参照項目の 1 つでもあります。

using index: これは、MySQL がカバーリング インデックスを使用してテーブルのデータ行にアクセスしないようにすることを示します。これは非常に効率的です。 
where の使用: これは、サーバーがストレージ エンジンから行を受け取った後に行をフィルター処理することを示します。一部の where 条件には、インデックスに属する列が含まれる場合があります。インデックスが読み込まれると、フィルタリングされます。そのため、一部の where ステートメントでは、追加列に「where を使用する」という記述がありません。 
一時テーブルの使用: これは、MySQL がクエリ結果をソートするときに一時テーブルを使用することを意味します。 
filesort の使用: これは、MySQL がテーブル内のインデックスの順序でデータを読み取るのではなく、外部インデックスを使用してデータをソートすることを意味します。

さらに、explain の拡張拡張機能は、元の explain に基づいて追加のクエリ最適化情報を提供できます。この情報は、mysql show warnings コマンドを通じて取得できます。ここに簡単な例を示します。

拡張説明
SELECT sum(金額)
顧客Aからの支払いb
1 = 1の場合
かつ、a.customer_id = b.customer_id
かつ、a.email = '[email protected]';

次に、警告の表示を実行します

mysql> 警告を表示します。
+---------+------+----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| レベル | コード | メッセージ

|
+---------+------+----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| 警告 | 1681 | 「EXTENDED」は非推奨であり、将来のリリースで削除される予定です
ase。

|
| 注記 | 1003 | /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount
nt)` は `sakila`.`customer` `a` から `sakila`.`payment` `b` に参加し、((`sakila`.`
b`.`customer_id` = `sakila`.`a`.`customer_id`) かつ (`sakila`.`a`.`email` = 'JANE
[email protected]')) |
+---------+------+----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
セット内の 2 行 (0.00 秒)

オプティマイザーが 1=1 が常に真であるという条件を自動的に削除していることがわかります。

MySQL 5.1 ではパーティショニング機能のサポートが開始され、explain コマンドにもパーティショニングのサポートが追加されました。 explain partitions コマンドを使用して、SQL によってアクセスされるパーティションを表示できます。