複雑なMySQLクエリの最適化

はじめに

MySQLは非常に物議を醸す製品です。 一方では、最も単純な操作/クエリで、他のデータベースと比較して速度が比較にならないほど優れています。 一方、未開発の（未開発でない場合）オプティマイザーがあり、複雑なクエリでは完全に失われます。



まず、最適化の問題の範囲を「幅の広い」大規模なテーブルに制限します。 最大10mのレコードと最大20Gbのサイズで、それらへの多数の変更可能なリクエストがあるとします。 テーブルに数百万のレコードがあり、それぞれのサイズが100バイトで、5つの単純なクエリが可能な場合、この記事は役に立ちません。 注意：MySQL innodb / perconaエンジンが考慮されます-以降は単にMySQLです。



ほとんどのクエリはそれほど複雑ではありません。 したがって、目的のクエリで使用するインデックスを作成する方法を知ること、および/または既存のインデックスを使用するようにクエリを変更することが非常に重要です。 結合、サブクエリ、結合なしで、通常のクエリ（ select_type = simple ）のインデックスを選択するためのオプティマイザの作業を検討します。



インデックスが存在するかどうかにかかわらず、オプティマイザーがtype = all （フルスキャン）を頻繁に使用する非常に小さなテーブルの最も単純なケース（たとえば、40エントリの分類子）は破棄します。 MySQLには複数のインデックスを使用するアルゴリズム（ インデックスマージ ）がありますが、このアルゴリズムはあまり頻繁に機能せず、 による順序付けなしでのみ機能します。 インデックスマージを使用する唯一の賢明な方法は、 ORを使用して異なる列をフェッチすることです。



別の余談：読者は既にExplainに精通していることが理解されます 。 多くの場合、クエリ自体はオプティマイザによって少し変更されるため、このインデックスまたはそのインデックスが使用されたかどうかを理解するには、以下を呼び出す必要があります。

explain extended select xxx;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして

 show warnings;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

オプティマイザーによって変更されたリクエストが表示されます。

カバリングインデックス-厚いテーブルからインデックスへ

そのため、タスク：かなり頻繁に実行されるかなり単純なリクエストを用意しますが、そのような頻繁な呼び出しは比較的遅いです。 インデックスを最速の選択肢として使用するクエリをキャストする戦略を検討してください。



インデックスを使用する理由 はい、MySQLはBツリーインデックスのみを使用しますが、それでも、MySQLは可能な限りインデックスを完全にメモリに保持しようとします（同時に、それらの上に適応ハッシュインデックスを追加することもできます）-実際、これにより、MySQLのパフォーマンスが大幅に向上します他のデータベース。 さらに、オプティマイザーは、多くの場合、より良いものではありませんが、メモリ上に既にロードされているインデックスよりも、ディスク上で使用することを好みます（ type = index / rangeの場合 ） したがって、いくつかの結論：

• 重すぎるインデックスは悪です。 まだメモリ内にないため使用されません。または、他のインデックスが置き換えられるため、メモリにロードされません。
• インデックスのサイズがテーブルのサイズに匹敵する場合、または異なる頻繁なクエリに使用されるインデックスのセットがサーバーのメモリサイズよりも大幅に大きい場合、大幅な最適化は行われません。
• ニュアンスは、TEXTでインデックス付け/並べ替えを行うことです。filesortを使用して、永久に永続化してください 。

時々忘れてしまう微妙な点の1つは、MySQLがクラスター化インデックスのみを作成することです。 クラスター-実際、テーブル内のレコードの絶対位置ではなく、（条件付きで）主キーのレコードを示します。これにより、目的のレコードを取得できます。 しかし、MySQLは、苦労せずに、2番目のルックアップなしで行うために、まさにそれを行います-主キーの幅に任意のキーを拡張します。 したがって、テーブルに主キー（ID）、キー（A、B、C）がある場合、実際には2番目のキーは（A、B、C）ではなく（A、B、C、ID）です。 したがって、道徳-太った主キーは悪です。



異なるデータベースでのクエリキャッシュの違いに注意する必要があります。 PostgreSQL / Oracleがクエリプランをキャッシュする場合（タイムアウトの準備のように）、MySQLは単にクエリSTRING（パラメータ値を含む）をキャッシュし、クエリ結果を保存します。 つまり、一貫して選択した場合

 select AAA from BBB where CCC=DDD
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

数回-DDDに変更機能が含まれておらず、 AAAテーブルが変更されていない場合（使用されている分離の意味で）、結果はキャッシュから直接取得されます。 かなり物議をかもす改善。



したがって、同じリクエストを数回呼び出すだけではないと考えています。 クエリパラメータの変更、テーブルデータの変更。 最適なオプションは、カバーリングインデックスを使用することです。 どのインデックスが対象になりますか？

1. まず、 による句の順序を見てください。 使用されるインデックスは、と同じ順序で記載されている列と同じか、同じ並べ替えまたは完全に逆の並べ替えで始まる必要があります。 ソートが直接または逆でない場合、インデックスは使用できません。 1つありますが、... MySQLは、まだソートが混在するインデックスをサポートしていません。 インデックスは常にascです。 したがって、 A asc、B descによる順序がある場合、 indexの使用に別れを告げます 。
2. 取得される列は、カバレッジインデックスに存在する必要があります。 非常に多くの場合、これはインデックスの無限の成長のために不可能な状態であり、ご存じのように悪です。 したがって、この点を回避する方法があります- 自己結合を使用します。 つまり、クエリを行選択とデータ抽出に分割します。 まず、指定された条件に従って、主キー（常にインデックスクラスターに存在する）の列のみを選択し、次に、この同じ主キーを使用して、取得した結果をすべての必要な列の選択に結合します。 したがって、最初の選択ではclean usingインデックスを使用し、2番目の選択ではeq_ref （複数のconstの本質）を使用します。 そのため、次のような結果が得られます。
   
   

    select AAA,BBB,CCC,DDD from tableName as a join tableName as b using (PK) «where over table b»
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

3. 次はどこですか 。 ここでは、最悪の場合、インデックス全体（ type = index ）を反復処理できますが、可能であれば、 type = range （ >、> =、<、<=、like“ xxx％”など）を超えない関数を使用する必要があります。 indexを使用して保存するには、 使用するインデックスに 、 そこからのすべてのフィールドを含める必要があります 。 上記のように-index_mergeを使用することができます-しかし、これは複雑な条件では単に不可能です。
   
   

実際には、リクエストのタイプが1つしかない場合に行うことができるのはこれだけです。 残念ながら、カバリングインデックスがある場合、MySQLオプティマイザーはクエリに対して常にそれを選択できるとは限りません。 さて、この場合、標準のuse / forceインデックスヒントを使用してオプティマイザを支援する必要があります。

カバリングインデックスからの厚いフィールドの分離-厚いインデックスから薄いインデックスへ

しかし、複数のタイプのクエリがある場合、または異なるソートを必要とし、太いフィールド（ varchar ）を使用する場合はどうでしょうか？ 100万レコードのvarcharフィールドインデックスサイズ（100）を数えるだけです。 そして、このフィールドがさまざまな種類のクエリで使用されている場合-異なるカバーリングインデックスがありますか？ 異なるクエリで同じ（またはほぼ同じ）パフォーマンスを維持しながら、このシックフィールドに対して1つのインデックスのみをメモリに保持することは可能ですか？ だから-最後のポイント。

1. 厚くて薄いフィールド。 明らかに、太いマージンを使用していくつかの異なるキーオプションを用意することは容認できない贅沢です。 したがって、可能な場合はいつでも、1つのキーのみが厚いフィールドで開始されるようにする必要があります。 そして、ここでは、条件を置き換えるために人工的なアルゴリズムを使用することが適切です。 つまり、この条件の結果に従って、太いフィールドの条件を結合に置き換えます。 例：
   
   

    select A from tableName where A=1 or fatB='test'
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   キーキー（fatB、A）を作成する代わりに、 シンキー （key）とシックキー（fatB）を作成します。 そして、条件を次のように書き換えます。
   
   

    create temporary table tmp as select PK from tableName where fatB='test'; select A from tableName left join tmp using (PK) where A=1 or tmp.PK is not null;
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

したがって、さまざまなリクエストに対して1つのシックフィールドfatBだけの、多くのシンキーを持つことができます。 ほぼ完全なパフォーマンスを維持しながら、実際のメモリを節約します。

自己解析割り当て

最小数のキー（メモリの観点）を作成し、次の形式のクエリを最適化する必要があります。

 select A,B,C,D from tableName where A=1 and B=2 or C=3 and D like 'test%'; select A,C,D from tableName where B=3 or C=3 and D ='test' order by B;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

クエリがtype = rangeに還元できないと仮定します 。

使用された文献のリスト

1. ハイパフォーマンスMySQL、第2版
   
   最適化、バックアップ、レプリケーションなど
   
   シュワルツ男爵、ピーター・ザイツェフ、ヴァディム・トカチェンコ、ジェレミー・D・ザウォドニー、アルジェン・レンツ、デレク・J・バリング
   
   パブリッシャー：O'Reilly Media
   
   リリース：2008年6月
   
   ページ：712
2. www.mysqlperformanceblog.com