EXPLAINを使用します。 クエリの改善

クエリを実行すると、MySQLクエリオプティマイザーはこのクエリに最適なプランを見つけようとします。 キーワードEXPLAINを含むクエリを使用して、このまさに計画を見ることができます。 EXPLAINは、MySQLクエリを理解して最適化するために自由に使用できる最も強力なツールの1つですが、悲しい事実は、多くの開発者がめったに使用しないことです。 この記事では、出力でEXPLAINが提供するデータについて学び、それを使用してクエリを最適化する方法の例を参照します。

EXPLAINは何を提供しますか？

EXPLAINステートメントの使用は簡単です。 SELECTステートメントの前にクエリに追加する必要があります。 出力を分析して、チームから返された情報を把握しましょう。

EXPLAIN SELECT * FROM categories
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 ********************** 1. row ********************** id: 1 select_type: SIMPLE table: categories type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: 1 row in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

出力はまったく同じように見えない場合がありますが、同じ10列が含まれます。 これらの返される列は何ですか？

• id-クエリ内の各SELECTのシリアル番号（複数のサブクエリがある場合）
• select_typeは、SELECT要求のタイプです。
  
  
  
  
  • SIMPLE-サブクエリまたはUNIONのない単純なSELECTクエリ
  • PRIMARY-このSELECTはJOINの最も外部のクエリです
  • 派生 -このSELECTはFROM内のサブクエリの一部です
  • SUBQUERY-サブクエリの最初のSELECT
  • 従属サブクエリ-外部クエリに依存するサブクエリ
  • キャッシュできないサブクエリ-キャッシュされていないサブクエリ（クエリをキャッシュするには特定の条件があります）
  • UNION - UNIONの 2番目以降のSELECT
  • DEPENDENT UNION-外部クエリに依存する、UNIONの2番目以降のSELECT
  • UNION RESULT - UNIONの結果
  
  
  
• テーブル -表示された行が属するテーブル。
• タイプ -MySQLリンクがどのようにテーブルを使用したかを示します。 これは、欠落したインデックス、または書き込まれたリクエストを確認して書き換える必要がある理由を報告できるため、出力で最も有用なフィールドの1つです。
  
  可能な値：
  
  
  
  
  • システム -テーブルには1行しかありません
  • Const-テーブルには対応する行が1つだけあり、それにはインデックスが付けられます。 これは、テーブルが一度だけ読み取られ、文字列の値が将来の結合で定数として認識されるため、最速の結合タイプです。
  • Eq_ref-インデックスのすべての部分がバインディングに使用されます。 使用されるインデックス：PRIMARY KEYまたはUNIQUE NOT NULL。 これは、バインディングのもう1つの最良のタイプです。
  • 参照 -インデックス列の対応するすべての行は、前の表の行の組み合わせごとに読み取られます。 インデックス付き列のこの結合タイプは、=または<=>演算子を使用するように見えます
  • フルテキスト -接続はテーブルのフルテキストインデックスを使用します
  • Ref_or_nullはrefと同じですが、列にnull値を持つ行も含まれます
  • Index_merge-接続は、インデックスのリストを使用して結果セットを取得します。 EXPLAINコマンド出力のキー列には、使用されるインデックスのリストが含まれます。
  • Unique_subquery -INサブクエリはテーブルから1つの結果のみを返し、主キーを使用します。
  • Index_subqueryは前のものと同じですが、複数の結果を返します。
  • 範囲 -通常、キー列がBETWEEN、IN、>、> =などの演算子を使用して定数と比較される場合、特定の範囲内の対応する行を見つけるために使用されるインデックス
  • インデックス -インデックスツリー全体をスキャンして、対応する行を見つけます。
  • すべて -テーブル全体をスキャンして、対応する行を見つけます。 これは最悪のタイプの複合であり、通常、テーブルに適切なインデックスがないことを示します。
  
  
  
• possible_keys-テーブル内の行を見つけるために使用できるインデックスを示します。 実際には、使用される場合と使用されない場合があります。 実際、NULL値は適切なインデックスが見つからなかったことを示すため、この列はクエリの最適化に役立ちます。
• キー -使用されているインデックスを示します。 この列には、possible_keys列で指定されていないインデックスが含まれる場合があります。 テーブルを結合するプロセスで、オプティマイザーは最適なオプションを探し、possible_keysには表示されないが、使用に最適なキーを見つけることができます。
• Key_lenは、MySQLオプティマイザーが使用するために選択したインデックスの長さです。 たとえば、key_lenの値が4の場合、4文字を保存するにはメモリが必要です。 このトピックへのリンクはこちら
• Ref-キーフィールドで指定されたインデックスと比較される列または定数を示します。 MySQLは、クエリ実行プランに基づいて、比較用の定数値またはフィールド自体を選択します。
• 行 -出力を取得するために処理されたレコードの数を表示します。 これは、クエリ、特にJOINとサブクエリを使用するクエリを最適化する機会を提供するもう1つの非常に重要なフィールドです。
• Extra-クエリ実行プランに関連する追加情報が含まれます。 「temporaryを使用する」、「filesortを使用する」などの値は、問題のリクエストを示すインジケータです。 ここで可能な値の完全なリストを見つけることができます。

EXPLAINの後、クエリでEXTENDEDキーワードを使用でき、MySQLはクエリの実行方法に関する追加情報を表示します。 この情報を表示するには、EXTENDEDリクエストの直後にSHOW WARNINGSクエリをすぐに実行する必要があります。 クエリのオプティマイザによって変更が行われた後に実行されたクエリに関するこの情報を調べるのが最も便利です。

 EXPLAIN EXTENDED SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 ********************** 1. row ********************** id: 1 select_type: SIMPLE table: Country type: const possible_keys: PRIMARY key: PRIMARY key_len: 3 ref: const rows: 1 filtered: 100.00 Extra: ********************** 2. row ********************** id: 1 select_type: SIMPLE table: City type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4079 filtered: 100.00 Extra: Using where 2 rows in set, 1 warning (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SHOW WARNINGS
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 ********************** 1. row ********************** Level: Note Code: 1003 Message: select `World`.`City`.`Name` AS `Name` from `World`.`City` join `World`.`Country` where ((`World`.`City`.`CountryCode` = 'IND')) 1 row in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

EXPLAINによるパフォーマンスの問題のトラブルシューティング。

ここで、EXPLAINコマンドの出力を分析して、それほど高速ではないクエリを最適化する方法を見てみましょう。 現在動作しているアプリケーションには多くのテーブルがあり、それらの間に多くの関係があることは間違いありませんが、クエリを記述する最適な方法を予測することは難しい場合があります。



インデックスやプライマリキーを持たない電子商取引アプリケーション用のテストデータベースを作成し、恐ろしいクエリを使用してテーブルを作成するこのようなあまり良くない方法の効果を示します。 ここからダウンロードできるこのテーブルをダンプします-github.com/phpmasterdotcom/UsingExplainToWriteBetterMySQLQueries

 EXPLAIN SELECT * FROM orderdetails d INNER JOIN orders o ON d.orderNumber = o.orderNumber INNER JOIN products p ON p.productCode = d.productCode INNER JOIN productlines l ON p.productLine = l.productLine INNER JOIN customers c on c.customerNumber = o.customerNumber WHERE o.orderNumber = 10101
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果を見ると（下の例でのみ見る必要があります。上のリンクには既にキーが追加されたダンプが含まれています）、悪いリクエストのすべての症状が見られます。



更新 インデックスなしの固定ダンプがあります。 何らかの理由で、元の著者のダンプでは、インデックスが最初に追加されました。



しかし、より良いクエリを作成しても、インデックスを追加するまで結果は同じになります。 指定された接続タイプはALL（最悪）です。つまり、MySQLは接続で使用できる単一のキーを決定できませんでした。 つまり、possible_keysとkeyはNULLです。 最も重要なことは、行フィールドは、MySQLがクエリの各テーブルのすべてのレコードをスキャンすることを示します。 これは、7×110×122×326×2996 = 91,750,822,240レコードをスキャンして適切な4つを見つけることを意味します（クエリからEXPLAINを削除し、自分で確認します）。 これは非常に悪く、これらのレコードの数はデータベースが大きくなるにつれて指数関数的に増加します。



次に、各テーブルの主キーなどの明らかなインデックスを追加して、クエリを再度実行します。 これを一般的なルールとして、JOINで使用される列をキーを追加する候補として使用できます。 MySQLは常にそれらをスキャンして、一致するレコードを見つけます。

 ALTER TABLE customers ADD PRIMARY KEY (customerNumber); ALTER TABLE employees ADD PRIMARY KEY (employeeNumber); ALTER TABLE offices ADD PRIMARY KEY (officeCode); ALTER TABLE orderdetails ADD PRIMARY KEY (orderNumber, productCode); ALTER TABLE orders ADD PRIMARY KEY (orderNumber), ADD KEY (customerNumber); ALTER TABLE payments ADD PRIMARY KEY (customerNumber, checkNumber); ALTER TABLE productlines ADD PRIMARY KEY (productLine); ALTER TABLE products ADD PRIMARY KEY (productCode), ADD KEY (buyPrice), ADD KEY (productLine); ALTER TABLE productvariants ADD PRIMARY KEY (variantId), ADD KEY (buyPrice), ADD KEY (productCode);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

インデックスを追加した後、前のクエリを実行しましょう。 これが表示されます：

 ********************** 1. row ********************** id: 1 select_type: SIMPLE table: o type: const possible_keys: PRIMARY,customerNumber key: PRIMARY key_len: 4 ref: const rows: 1 Extra: ********************** 2. row ********************** id: 1 select_type: SIMPLE table: c type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: ********************** 3. row ********************** id: 1 select_type: SIMPLE table: d type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 4 Extra: ********************** 4. row ********************** id: 1 select_type: SIMPLE table: p type: eq_ref possible_keys: PRIMARY,productLine key: PRIMARY key_len: 17 ref: classicmodels.d.productCode rows: 1 Extra: ********************** 5. row ********************** id: 1 select_type: SIMPLE table: l type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 52 ref: classicmodels.p.productLine rows: 1 Extra: 5 rows in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

インデックスを追加した後、読み取りレコードの数は1×1×4×1×1 = 4に低下しました。orderdetailsテーブルの各レコードorder_number = 10101について、これはMySQLがインデックスを使用して他のすべてのテーブルで対応するレコードを見つけることができ、再ソートしなかったことを意味します全表スキャンに。



最初の結論では、結合タイプ-「const」を使用できます。これは、複数のレコードを持つテーブルの最速の結合タイプです。 MySQLは、PRIMARY KEYをインデックスとして使用できました。 「ref」フィールドには、「const」が表示されます。これは、WHEREキーワードの後のクエリで指定された値10101に他なりません。



もう1つの要求を確認します。 その中で、製品とproductvariantsの2つのテーブルの和集合を選択し、それぞれを製品ラインと組み合わせます。 productvariants。productCodeフィールドを持つさまざまな製品オプションで構成され、価格へのリンクです。

 EXPLAIN SELECT * FROM ( SELECT p.productName, p.productCode, p.buyPrice, l.productLine, p.status, l.status AS lineStatus FROM products p INNER JOIN productlines l ON p.productLine = l.productLine UNION SELECT v.variantName AS productName, v.productCode, p.buyPrice, l.productLine, p.status, l.status AS lineStatus FROM productvariants v INNER JOIN products p ON p.productCode = v.productCode INNER JOIN productlines l ON p.productLine = l.productLine ) products WHERE status = 'Active' AND lineStatus = 'Active' AND buyPrice BETWEEN 30 AND 50
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 ********************** 1. row ********************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 219 Extra: Using where ********************** 2. row ********************** id: 2 select_type: DERIVED table: p type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 110 Extra: ********************** 3. row ********************** id: 2 select_type: DERIVED table: l type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 52 ref: classicmodels.p.productLine rows: 1 Extra: ********************** 4. row ********************** id: 3 select_type: UNION table: v type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 109 Extra: ********************** 5. row ********************** id: 3 select_type: UNION table: p type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 17 ref: classicmodels.v.productCode rows: 1 Extra: ********************** 6. row ********************** id: 3 select_type: UNION table: l type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 52 ref: classicmodels.p.productLine rows: 1 Extra: ********************** 7. row ********************** id: NULL select_type: UNION RESULT table: <union2,3> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: 7 rows in set (0.01 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このクエリには多くの問題があります。 製品および製品変数のすべてのエントリをスキャンします。 なぜなら これらのテーブルには、productLineおよびbuyPrice列のインデックスがありません。possible_keysおよびキーフィールドにはNULL値が表示されます。 製品および製品ラインテーブルのステータスはUNIONの後にチェックされるため、それらをUNION内に移動するとレコードの数が減ります。 インデックスを追加します。

 CREATE INDEX idx_buyPrice ON products(buyPrice); CREATE INDEX idx_buyPrice ON productvariants(buyPrice); CREATE INDEX idx_productCode ON productvariants(productCode); CREATE INDEX idx_productLine ON products(productLine);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 EXPLAIN SELECT * FROM ( SELECT p.productName, p.productCode, p.buyPrice, l.productLine, p.status, l.status as lineStatus FROM products p INNER JOIN productlines AS l ON (p.productLine = l.productLine AND p.status = 'Active' AND l.status = 'Active') WHERE buyPrice BETWEEN 30 AND 50 UNION SELECT v.variantName AS productName, v.productCode, p.buyPrice, l.productLine, p.status, l.status FROM productvariants v INNER JOIN products p ON (p.productCode = v.productCode AND p.status = 'Active') INNER JOIN productlines l ON (p.productLine = l.productLine AND l.status = 'Active') WHERE v.buyPrice BETWEEN 30 AND 50 ) product
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 ********************** 1. row ********************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 12 Extra: ********************** 2. row ********************** id: 2 select_type: DERIVED table: p type: range possible_keys: idx_buyPrice,idx_productLine key: idx_buyPrice key_len: 8 ref: NULL rows: 23 Extra: Using where ********************** 3. row ********************** id: 2 select_type: DERIVED table: l type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 52 ref: classicmodels.p.productLine rows: 1 Extra: Using where ********************** 4. row ********************** id: 3 select_type: UNION table: v type: range possible_keys: idx_buyPrice,idx_productCode key: idx_buyPrice key_len: 9 ref: NULL rows: 1 Extra: Using where ********************** 5. row ********************** id: 3 select_type: UNION table: p type: eq_ref possible_keys: PRIMARY,idx_productLine key: PRIMARY key_len: 17 ref: classicmodels.v.productCode rows: 1 Extra: Using where ********************** 6. row ********************** id: 3 select_type: UNION table: l type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 52 ref: classicmodels.p.productLine rows: 1 Extra: Using where ********************** 7. row ********************** id: NULL select_type: UNION RESULT table: <union2,3> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: 7 rows in set (0.01 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ご覧のように、結果として、スキャンされたラインの数は2,625,810（219×110×109）から276（12×23）に減少しました。これはパフォーマンスの優れたゲインです。 インデックスを追加した直後にクエリ内で以前の順列なしで同じクエリを実行した場合、スキャンされた行にそのような減少は見られません。 派生結果でWHEREが使用されている場合、MySQLはインデックスを使用できません。 これらの条件をUNION内に配置すると、インデックスを使用できるようになります。 これは、インデックスを追加するだけでは必ずしも十分ではないことを意味します。 適切なクエリを記述するまで、MySQLはそれらを使用できません。 （http://www.php.su/mysql/manual/?page=MySQL_indexes-追加情報）。

まとめ

この記事では、EXPLAINキーワード、出力に関する情報、およびコマンドの出力を使用してクエリを改善する方法の例について説明します。 現実の世界では、このコマンドは、考慮されているシナリオよりも役立つ場合があります。 ほとんどの場合、WHEREを使用した複雑な構造を使用して一連のテーブルを結合します。 同時に、単にテーブルにインデックスを追加するだけでは、必ずしも目的の結果が得られるとは限りません。 この場合、リクエストを確認する必要があります。