MySQLを使用したWebプロジェクト最適化戦略

はじめに

大規模なWebプロジェクト、特にPHPでは、これは一般的に、Web開発に適したサーバー側プログラミング言語に当てはまります。通常、「このように生きることはできません」という理解と最適化の時期が来ています。サイトの動作により、ブレーキが停止します（少なくとも生産時）。



原則として、実稼働環境での「遅い」言語の重いフレームワーク（SymfonyやRoRなど）でさえかなり合理的に高速に動作し、主な「ブレーキ」はSQLクエリと非識字キャッシング（たとえば、初期化で十分です） Symfonyでのプロジェクトの複雑かつ大規模な構成には約80ミリ秒かかり、この場合、ページ実行時間は数秒以上に達することがあります。



これがあなたの事例であり、MySQLプロジェクトであると判断できた場合、この記事は具体的な対策を講じ、結果を修正し、DBMSでの明示的な問題の発生を後で防ぐことで状況を修正するのに役立ちます。

ボトルネックの特定

そもそも、MySQLでの作業を最適化することの本当の利点が常に感じられるとは限らないので、その前にMySQLでの作業によって問題が実際に引き起こされることを確認することをお勧めします。 ここで一般的なアドバイスをするのはかなり難しいですが、いずれにしても、たとえば、DBMSドライバーの（実稼働環境での）ページ実行時間に対するクエリ実行時間の合計割合など、何らかの方法で測定する必要があります。 この割合が約50以上の場合、MySQLでの作業の最適化は正当化されています。

何をどのように測定するか

原則として、「最悪」の（パフォーマンスに関して）フレームワークおよび/またはCMSでも、DBMSパフォーマンスの問題を追跡するためのツールがいくつかあります。通常、少なくともページあたりのリクエスト数は常に計算されます。 念のため、ページあたりのリクエスト数は100を超えていると言います-これは悪いです:)、これから始める価値があります。



DBMSにそれほど多くのクエリが存在せず、依然として非常に遅くなると仮定し、何が最も大きな影響を与えるかは明確ではありません。

クエリ実行時間の測定

もちろん、「遅い」リクエストを追跡する最も簡単で適度に効果的な方法は、各リクエストの実行時間をカウントし、画面にリクエストの実行時間を表示することです（一部のリクエストは「スローダウン」すると結論付けます）。 問題は、DBMSが適切に構成されており、MySQLサーバーにI / Oが最小限になるように十分なメモリがある場合、つまり、クエリ実行時間の場合にのみ、このメソッドが多かれ少なかれ本番環境に適していることです実際には、実行の複雑さとそれにかかるCPU時間のみに依存し、パフォーマンスに影響を与えるサードパーティの要素が最小限になります。 クエリキャッシュが有効になっている場合、同じクエリを繰り返すだけで完全に異なる実行時間が得られます（ ストップリストの関数が使用されていない場合）。



開発環境でこのアプローチを使用する場合、多くの場合、要求は「スローダウン」します。それは、それらが複雑であるためではなく、単に偶発的な外部負荷または単純なI / Oのためです。 もちろん、ページを更新して同じリクエストを再度実行するように要求した直後にできますが、これでも副作用が発生しないことは保証されません（たとえば、MySQL開発サーバーのクエリキャッシュが含まれる場合-もちろん、必要な場合は、すぐに切断します）。



多くの場合、開発ベースでのリクエストの実行時間は非常に「ジャンピー」であるため、低速リク​​エストのしきい値は非常に大きな値に設定されます（Symfonyを使用するPropelでは、これはデフォルトで100ミリ秒です）か、ゆっくり実行されるリクエストに注意を払いませんI / Oへのすべての書き込み



もちろん、すべての欠点にもかかわらず、クエリの実行時間はパフォーマンスを評価するための基本的な基準であり、これらの時間を正確に解釈できる必要があります。

クエリの自動EXPLAIN

「EXPLAIN」プレフィックスを持つすべてのSELECTクエリに対して別のクエリを作成し、EXPLAINのすべての一意のクエリIDの「rows」フィールドの積を考慮して、クエリの複雑さを評価したい場合があります。 このアイデアは優れていますが、たとえば、同じ準備された要求の実行が困難なため、実装が難しい場合がよくあります。 しかし、これは最大の問題ではありません（準備済みステートメントの問題は、まだ何らかの形で解決できます）。 最大の問題は、MySQLのEXPLAINがしばしば露骨に嘘をついていることです。



簡単な例を示します（たとえば、テーブルにはsome_fieldのランダム値を持つ100,000レコードがあります）。



ネームプレート

CREATE TABLE some_table(id INT PRIMARY KEY AUTO_INCREMENT, some_field INT)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

100,000行をスキャンして何も見つからない単純なクエリを実行します

 SELECT * FROM some_table WHERE some_field = 100500 ORDER BY id LIMIT 100
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

MySQL 5.1.49でEXPLAINを探します

 EXPLAIN SELECT * FROM some_table WHERE some_field = 100500 ORDER BY id LIMIT 100
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

EXPLAINは次のように述べています。napalnikには、100行を調べる必要があります。napalnikにはプライマーインデックスを使用します。



100行（またはそれ以上）をスキャンしてすぐに返す単純なクエリを実行します

 SELECT * FROM some_table WHERE some_field <> 0 ORDER BY id LIMIT 100
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

MySQL 5.0.51でEXPLAINを探します

 EXPLAIN SELECT * FROM some_table WHERE some_field <> 0 ORDER BY id LIMIT 100
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

EXPLAINは次のように述べています。 ナパルニクには、100,000行を調べる必要があります。プライマリインデックスを使用し、長時間アスペンを起動し、ナパルニクに行きます。 （はい、それはまさに彼が言うことです、私は床を与えます:)）



これらは非常に単純な例であるため、EXPLAINの行数を注意して処理する必要があることを簡単に理解できます。結局、EXPLAINは要求を実行しません。つまり、実際に読む必要がある行数を知ることはできません。評価（そしてしばしば10回ごとに間違えます:)）

読み取った行の数をカウントします

MySQLでパフォーマンスを測定するための最も高度な方法がEXPLAINであると考えた場合、あなたは間違っています（それほど前ではありませんでした:)）。



実際、次のクエリを実行します。

 SET query_cache_type = OFF; --  query cache ,   FLUSH STATUS; --   :) SELECT * FROM some_table WHERE some_field = 100500 ORDER BY id LIMIT 100; --   SHOW SESSION STATUS LIKE 'handler_%' --  ;)!
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次の画像のようなものが生成されます。







MySQLのハンドラーをよりよく理解するために、たとえばMySQLのHANDLERコマンドの説明を読むことができます 。 要するに、ハンドラは、さまざまな種類のテーブルのMySQLで（だけでなく）行を読み書きするための共通のインターフェイスです。 MySQLの対応するインターフェースのHandlerSocketという名前は、それがどんな種類の獣であるかを暗示しているはずです。



したがって、ページの最初（クエリキャッシュをオフにして統計情報をリセットする）で最初の2つのクエリを実行し、ページの最後-最後のクエリで、MySQLから読み取り/書き込みされた行の総数を取得できます。 同様の最適化を行っていたプロジェクトの場合、ページの結果として10,000行を超える読み取り/書き込みがあった場合、devバージョンのページにalert（）を追加しました。



もちろん、これは万能薬ではありませんが、サイト上の遅いページを見つけて「中和」し、適切な対策を講じるのに非常に役立ちます。 このメソッドは、リクエストが何であるかについて気にしません（INSERT INTO ... SELECT ...でも動作します）。EXPLAINが何も良いことを言えなくても、クエリの結果として実行されたアクションに関する正確な統計を常に提供します。

サーバーから送信された情報の量

実際、SESSION STATUSには、クライアントとSQLサーバー間のトラフィック情報（Bytes_％）など、より多くの情報が含まれています。「ワイド」テーブルがある場合、これも関連します（特にORMを使用する場合、テーブルからすべての列を選択するのが好きな人、たとえそこに列が必要でないとしても）

実際、クエリの最適化

ボトルネックが見つかった後、これらのクエリの最適化を開始するか、サイトの特定のブロックが何をすべきかについての見方を再検討する必要があります。



このトピックの情報がお役に立てば幸いです:)。 この手法を使用して、ボトルネックを特定し、Symfonyサイトのほとんどのページの実行時間を〜1000ミリ秒から200-300ミリ秒の間まで短縮し、自動バージョンにツールを追加して、将来同様の問題を自動的に防ぐことができました。 アクティブな「独自の」〜20 Mbのサイト（合計コード約100 Mb）のPHPコードの場合、このすべてに約3日かかりました:)。 それはたくさんまたは少しです-あなた自身のために考えてください:)