ウェブサイトデータベースを高速化

ウェブサイトの読み込み速度は常に重要なトピックでしたが、検索結果でウェブサイトをランク付けする際にGoogleが考慮に入れた2010年4月以降、さらに重要になりました。 ただし、主なバイアスは、原則として、ファイルサイズを縮小し、サーバー設定、CSS、およびJavaScriptを最適化することでした。

また、別の重要な要素があります。 これは、サーバー上でWebページが形成される速度です。 最新の大規模なサイトのほとんどは、すべての情報をデータベースに格納し、さまざまな言語（PHPやASPなど）を使用してそれを抽出し、HTMLページを生成してブラウザーに送信します。



したがって、サイトのページが1.5秒（サイトが高速であると見なされるGoogleのしきい値）以上で返された場合、同様に検索結果を含むページの読み込みに非常に長い時間がかかる場合、または商品の説明ページはすぐに読み込まれますが、ユーザーレビューは数秒間読み込まれます。

「高速」ウェブサイトを識別するためにGoogleが設定するしきい値は、約1.5秒です。 ウェブマスターツールの同様のスケジュールを取得できます（[ドメイン]→診断→パフォーマンスに移動）。

この記事では、同様の問題について説明し、データベースを最適化してサイトを高速化するいくつかの方法について説明します。 より複雑なメソッドの説明など、よく知られた事実から始まり、さらに参照するためのリソースのリストが含まれています。 この記事は、大胆不敵な初心者と、突発行為を行う開発者を対象としています。

データベースとは何ですか？ SQLとは何ですか？

一般に、データベースは、たとえば顧客とその注文のリストなどの情報を含むテーブルのセットです。 これには、ファイルキャビネット、多数のスプレッドシート、Microsoft Accessファイル、または40テラバイトのAmazonブックとクライアントデータがあります 。

一般的なブログデータベースには、ユーザー、カテゴリ、投稿、コメントに関する情報を含むテーブルが含まれています。 Wordpressには、最初にこれらと他のいくつかのテーブルがあります。 eコマースシステムのデータベースには、「バスケット」に含まれる顧客、製品、カテゴリ、注文、製品に関する情報を含むテーブルが含まれています。 オープンMagentoエンジンには、 これらのテーブルと他の多くのテーブルが含まれています。 データベースには、コンテンツの管理、顧客関係、アカウントと請求書、イベントの保存など、多くのアプリケーションの可能性があります。これらのタイプ（ブログとeコマースサイトのエントリ）の両方がこの記事に掲載されます。

データベース内の一部のテーブルは、他のテーブルに関連付けられています。 たとえば、ブログの投稿には複数のコメントが含まれ、クライアントは複数の注文を行うことがあります（これは1対多の関係です）。 データベースで最も複雑な関係の形式は、多対多の関係です。 この種の関係は、eコマースシステムのデータベースの中核です。1つの注文に異なる製品を含めることができます。製品が異なる注文に含まれるのと同じです。 これは、注文と商品の間にある「注文内容」テーブルが表示される場所であり、ユーザーが商品を注文に追加するたびに記録されます。 これがどれほど重要であるかは、後でいくつかのクエリが長時間実行される理由を考えるときに理解されます。

データベースは、このすべてのデータを含むソフトウェアも指します。 ソフトウェアとは、「今日は朝食を食べている間に、データベースが「落ちました」」または「データベースを更新する必要がある」という意味です。 （ 「これはデータベースではなく、DBMSを指している可能性が高い」と翻訳者は言いました（:) 。一般的なシステムは、Microsoft Access 2010、Microsoft SQL Server、MySQL、PostgreSQL、Oracle Database 11gです。

SQLの略語は、データベースに関してはしばしば言及されます。 「Structured Query Language」（構造化照会言語）というフレーズに由来し、「Es-Kyu-El」と発音されます（ 「YaZZ、ソ連で開発された場合、」と翻訳者は言いました（:） 。驚くべき方法でデータベースを使って：

SELECT lastname FROM customers WHERE city='Brighton';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このコードはリクエストと呼ばれます。 INSERT （データの追加用）、 UPDATE （更新用）、 DELETE （削除用）、 CREATE TABLE （テーブル作成用）、 ALTER TABLEなど、データベースを操作する他の式もあります。

データベースはどのようにサイトを遅くすることができますか？

新しい空のサイトは非常に迅速に機能しますが、プロジェクトの開発に伴い、特定のページ、特に機能の複雑な要素を持つページで「ブレーキ」が発生することがあります。 製品リストページの下部に「この製品で他に何を購入しますか...」と表示するとします。 データベースからこの情報を取得するには、次の手順を実行する必要があります。

1. 「作業」する製品を特定します。
2. この製品が顧客によって「バスケット」に追加された前回の回数（上記の「注文内容」の表）を確認します。
3. 同じ注文で「バスケット」に追加された製品のリストを取得します（確認済みの注文のみ）。
4. これらの注文を行った購入者を選択してください。
5. ユーザーが上記のアイテムから行った注文を選択します。
6. 注文の内容を確認します（同じ表「注文内容」）。
7. これらの製品に関する情報を入手してください。
8. これらの注文でより一般的な製品を特定し、このリストを表示します。

1つの複雑なクエリにすべてを収めることも、いくつかの単純なクエリに分割することもできます。 いずれの場合でも、データベースに20個の製品、12個の顧客、18個の注文、67個の購入品（「バスケット」内の製品の合計数）が含まれている場合、非常に迅速に完了できます。 しかし、すべてが非効率的に行われると、そのような操作の実行は大量のデータに対して非常に遅くなります。 たとえば、500の製品、10,000の顧客、14,000の注文、100,000の購入した商品を処理すると、ページの読み込みが遅くなります。

これは非常に複雑な例ですが、「舞台裏」で何が起こっているのか、そして無害に見える機能の要素がサイトに「置かれる」理由を想像することができます。

サイトのスローダウンは、他の理由によって引き起こされる可能性があります。サーバーが十分なメモリまたはディスクスペースで実行されていない。 このサーバー上の別のサイトは多くのリソースを消費します。 サーバーが電子メールを送信しているか、他の「重い」タスクでビジーです。 ソフトウェアまたはハードウェアのエラー。 間違った構成。 おそらく人気があり、その結果、サイトへのトラフィックが突然増加したのでしょうか？ 次の2つのセクションでは、作業の速度をさらに詳しく検討します。

データベースの場合？

現在、 FirefoxのFirebugプラグイン 、Google Chromeの開発者向けツール（Shift + Ctrl + I、以下リソース→リソーストラッキングを有効にする）、 Yahoo YSlowなど、サイトの速度を分析する方法がいくつかあります。 WebPagetestのような特別なサイトもあり、そこでURLを入力すると、指定された場所から速度を測定します。

これらのツールは、ページで使用可能なすべてのリソース（HTML、画像、CSS、およびJavaScriptファイル）の図を表示し、それぞれの読み込み時間を示します。 これらのツールは、以下に費やす時間も決定します。

• DNSクエリ（ドメイン名からIPアドレスへの変換）、
• サーバーへの接続
• サーバーからの応答を待っています（「最初のバイトまでの時間」、別名TTFB-最初のバイトまでの時間（コメント変換））、
• サーバーからデータを受信（ダウンロード）します。

データベースにアクセスし、すぐにすべてをブラウザーに送信するPHPを含む多くのWebページはサーバー上で完全に組み立てられます。そのため、データベースの遅延は長い待機時間につながり、データの受信/ダウンロード時間はボリュームに比例します。 。 したがって、高速接続では、5秒で形成される20 KBのWebページ（0.05秒でロードしますが）により、サーバーで大きな遅延が発生します。

それでも、すべてのページがそうであるとは限りません。 PHP関数flush（）は、既に生成されたHTMLデータをブラウザーに送信します。 それ以上の遅延は、このデータのロードにすでに関連付けられており、予想には関連付けられていません。

いずれの場合でも、同じサーバー上にある、おそらく低速で複雑なWebページの待機時間/ロード時間と、同じサイズのHTMLページ（または画像、その他の静的要素）の待機時間を同時に比較できます。 これにより、低速なインターネット接続やサーバー負荷（これらのオプションの両方が遅延を引き起こす可能性があります）の影響が排除され、ページの形成に費やされたこれらの期間を比較できます。 もちろん、これは正確な科学ではありませんが、何がどこで減速するかについてのいくつかのアイデアを提供します。

以下のスクリーンショットは、Google Chrome Webページでの開発者ツールによる分析の結果と、同じサイズ（20 Kb）の画像を示しています。 Webページの待機時間は130ミリ秒、ダウンロード時間は22ミリ秒でした。 画像の時間は、それぞれ51ミリ秒と11ミリ秒です。 ダウンロード時間はほぼ同じですが、サーバーはWebページの処理と生成にさらに80ミリ秒かかりました。これは、PHPコードの実行とデータベースとの相互作用の結果です。

これらのテストを実行するときは、静的コンテンツを分析しながら、キャッシュバージョンを取得しないようにページを更新します。 さらに、テストを数回実行して、統計上の逸脱に直面していないことを確認します。 以下の3番目のスクリーンショットは、WebPagetestが同じページで同時にGoogleのツールのほぼ2倍の時間を表示することを示しています。 これは、テストを実行するときにいずれかのツールを使用する必要があることを示唆しています。

Google Chromeツールキットを使用すると、Webページの読み込み時に130ミリ秒が得られます

同じツール。 同様のサイズの画像を読み込む場合、51 msがあります

WebPagetestで同じページを分析すると、待機時間が296ミリ秒、合計ダウンロード時間が417ミリ秒になります

MySQL / PHPでクエリを測定しますか？

一般的なアイデアを受け取ったので、より詳細に理解します。 データベースがサイトの速度を低下させている疑いがある場合は、遅延の原因を正確に特定する必要があります。 各データベースクエリの実行時間を計算する関数をいくつか定義します。 このコードはPHP / MySQL用ですが、このメソッドはデータベースを使用するすべてのWebサイトで使用できます。

 function StartTimer ($what='') { global $MYTIMER; $MYTIMER=0; //global variable to store time //if ($_SERVER['REMOTE_ADDR'] != '127.0.0.1') return; //only show for my IP address echo '<p style="border:1px solid black; color: black; background: yellow;">'; echo "About to run <i>$what</i>. "; flush(); //output this to the browser //$MYTIMER = microtime (true); //in PHP5 you need only this line to get the time list ($usec, $sec) = explode (' ', microtime()); $MYTIMER = ((float) $usec + (float) $sec); //set the timer } function StopTimer() { global $MYTIMER; if (!$MYTIMER) return; //no timer has been started list ($usec, $sec) = explode (' ', microtime()); //get the current time $MYTIMER = ((float) $usec + (float) $sec) - $MYTIMER; //the time taken in milliseconds echo 'Took ' . number_format ($MYTIMER, 4) . ' seconds.</p>'; flush(); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

StartTimerはタイマーを開始し、測定したすべてを表示します。 2行目はIPアドレスを確認しています。 これは、作業中のサイトで（一時的に）測定を行い、そのような統計を全員に見せたくない場合に役立ちます。 最初の//を削除して行のコメントを解除し、 127.0.0.1をIPアドレスに置き換えます 。 StopTimerはタイマーを停止し、経過時間を表示します。

最新のサイト（特によくできたオープンソースプロジェクト）の多くは、多くのPHPファイルを持っていますが、データベースクエリはそれらの一部でのみ実行されます。 これらのファイルでmysql_db_queryまたはmysql_query行を探します。 BBEditなどの多くのソフトウェア開発には、この種の検索を実行できる機能があります。 Linuxコンソールに精通している場合は、次のコマンドを試してください。

 grep mysql_query `find . -name \*php`
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果は次のようになります。

 mysql_query ($sql);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

WordPress 3.0.4の場合、これはwp-includes / wp-db.phpファイルの 1112行目になります 。 上記の関数をファイルの先頭（または各ページに接続するファイル）にコピーし、 mysql_query行の前後にStartTimerおよびStopTimer関数を追加して、これを取得できます。

 StartTimer ($query); $this->result = @mysql_query( $query, $dbh ); StopTimer();
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

以下のスクリーンショットは、WordPressをインストールした直後にコードを追加した結果を示しています。 合計で、15件のリクエストが処理され、それぞれ約0.0003秒かかります。 （0.3 ms）、空のデータベースから予想されます。

ここにすべてのWordPressリクエストが表示され、測定されます。

他の広く使用されているシステムでこの行を見つけた場合は、この記事にコメントを追加してこの情報を共有してください。

他の面白いことをすることができます：あなたのコンピューターが私のコンピューターと比較してどれくらい速いかを見ることができます。 私のコンピューターでは1.000.000への「カウントダウン」には2.9420秒かかりますが、サーバーは2.0726秒と少し高速です。

 StartTimer ('counting to 10000000'); for ($i=0; $i<10000000; $i++); //count to a high number StopTimer();
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果についての何か。

この方法では、比較結果のみが得られます。 サーバーがその時点でビジーだった場合、すべてのリクエストは通常​​よりも遅くなりました。 ただし、少なくとも「高速」リクエストの実行時間（1〜5ミリ秒）、低速（200ミリ秒以上）、および最も「重い」（1秒以上）を判断できるはずです。 このテストを1時間または1日中に数回実行できます（ただし、前のテストの直後ではありません-データベースキャッシュのセクションを参照）。

また、Webページのデザインを深刻に損なう可能性があります。 「ヘッダー情報を変更できません。 ヘッダーはすでに...で送信されています。 これは、測定メッセージがCookieおよびセッションヘッダーよりも先にあるためです。 ページのメインコンテンツが表示されている場合、これらのメッセージは無視できます。 ページが空白の場合、 mysql_queryの周りではなく、特定のコードブロックの周りでStartTimerおよびStopTimer関数を宣言する必要があります。

この方法は、大まかな結果を得るための非常に簡単な方法であり、実際のWebサイトに残さないでください。

他に何が原因でしょうか？

データベースのクエリがそれほど遅くない場合でも、ページが長時間にわたって形成される場合、その原因はコードの記述が不十分である可能性が高いです。 コードの大部分にタイマー関数を追加して、遅延があるかどうかを確認できますか？ たぶんその理由は、20項目しか表示していなくても、10,000行の情報を調べているからでしょうか？

プロファイリング

それでも混乱している場合、および/またはコードで何が行われているかについての詳細情報を取得したい場合は、サイトのローカルコピーを分析するXdebugなどのさまざまなデバッグおよびプロファイリングツールを試してください。 すべてのボトルネックを視覚的に表示することもできます。

インデックステーブル

上記の実験では、ページデータベースがサイト上で持っているクエリの数を示すことで驚くかもしれません。遅いクエリを特定するのに役立つことを願っています。

プロセスを高速化するために、いくつかの簡単な機能強化を見てみましょう。 これを行うには、何らかの形でリクエストをデータベースに直接送信する必要があります。 多くのサーバー管理パッケージ（CPanelやPleskなど）にPhpMyAdminが付属しており、同様のタスクを実行できます。 さらに、 phpMiniAdminのようなものをサイトにアップロードできます。データベースを表示してクエリを実行できるPHPファイルは1つだけです。 データベース名、ユーザー名、パスワードを入力する必要があります。 それらがわからない場合は、サイトの構成ファイルで簡単に見つけることができます（たとえば、WordPressの場合、これはWP-config.phpです）。

サイトページを含むデータベースクエリの中で、おそらくWHEREで条件を見ました。 これは、SQLを使用して結果をフィルタリングする方法です。 たとえば、サイトで「購入履歴」ページを表示している場合、おそらく誰が注文したかを特定するクエリがあります。 このようなもの：

 SELECT * FROM orders WHERE customerid = 2;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この要求は、クライアントがID 2で行ったすべての注文を取得します。100,000件の注文レコードがあるコンピューターでは、0.2158秒かかります。

WHEREの条件で使用される可能性のある多くの値を含むCustomerIDなどの列は、 =または< 、または>と組み合わせて、インデックスを作成する必要があります。 これは、本の最後の内容のようなものです。データベースがインデックス付きデータをすばやく取得するのに役立ちます。 これは、データベースクエリを高速化する最も速い方法の1つです。

インデックスを作成するもの

インデックスを作成する列を見つけるには、データベースの一般的な使用方法を把握する必要があります。 たとえば、名前でカテゴリを検索したり、日付でイベントを検索するためにサイトがよく使用される場合は、これらの列にインデックスを付ける必要があります。

 SELECT * FROM categories WHERE name = 'Books'; SELECT * FROM events WHERE startdate >= '2011-02-07';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

各データベーステーブルには、以下のwp_postsテーブルのスクリーンショットのように、 主キーとして示される識別子列（通常はidですが、 IDまたはArticleIDなど）が必要です。 これらの主キーは自動的にインデックス付けされます。 ただし、上記の例のCustomerIDなど、他のテーブルの識別子を参照する列にもインデックスを付ける必要があります。 この場合、それらは外部キーになります 。

 SELECT * FROM orders WHERE customerid = 2; SELECT * FROM orderitems WHERE orderid = 231;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

商品の説明や記事の内容など、大量のテキストデータを検索する必要がある場合は、別の種類のインデックス-FULL TEXTを追加できます。 タイプFULL TEXTのインデックスを使用するクエリは複数の列にまたがることができ、最初は4文字より長い単語に対応するように構成されています。 ストップワードおよびインデックス化されたエントリの50％以上で見つかったワードも除外されます。 ただし、このタイプのインデックスを使用するには、SQLクエリを変更する必要があります。 以下は、 FULL TEXTインデックスを使用する場合と使用しない場合のクエリです。

 SELECT * FROM products WHERE name LIKE '%shoe%' OR description LIKE '%shoe%'; SELECT * FROM products WHERE MATCH(name,description) AGAINST ('shoe');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この方法ですべてのインデックスを作成する必要があるように思われるかもしれません。 ただし、インデックス付けによって選択は高速化されますが、 挿入 、 更新 、 削除の操作は遅くなります。 したがって、頻繁に変更される可能性の低い商品の説明を含むテーブルがある場合は、インデックスを作成できます。 ただし、順序付きのテーブルは常に変更される可能性があります。この場合、インデックス作成に注意する必要があります。

また、 索引付けが役に立たない場合にも注意する必要があります 。 たとえば、列のほとんどの値が同じ値を持つ場合。 「在庫」を意味する製品ステータス列で値「1」が使用され、すべての商品の95％が「在庫」である場合、インデックスは在庫の商品を見つけるのに役立ちません。 本の最後で言い訳へのポインタを作成しなければならない場合、リンクはそのすべてのページに移動します。

 SELECT * FROM products WHERE stock_status = 1;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

インデックスを作成する方法は？

PhpMyAdminまたはphpMiniAdminを使用して、各テーブルの構造を調べ、列にインデックスが付けられているかどうかを確認できます。 PhpMyAdminでテーブル名を選択します。構造の最後にインデックスの列挙があります。 phpMiniAdminで、上部にある[テーブルの表示]をクリックしてから、必要なテーブルの反対側の[sct] （テーブルの作成を表示）をクリックします。 これらのアクションの結果として、テーブルの作成に必要なクエリが表示され、その最後に次のようなインデックスのリストも表示されます。

 orderidindex" KEY ("orderid");
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

PhpMiniAdminを使用して、WordPressのwp_postsテーブルのインデックスのリストを表示します

インデックスがない場合は、自分で作成できます。 PhpMyAdminの[インデックス]セクションで、インデックスを作成する列の数を指定し、[実行]をクリックします。 以下のスクリーンショットに示すように、インデックスの名前を入力し、必要な列を選択して「保存」をクリックします。

PhpMyAdminを使用してインデックスを作成する

PhpMiniAdminでは、ページの上部に対応するフィールドを挿入して、次のリクエストを実行する必要があります。

 ALTER TABLE orders ADD INDEX customeridindex (customerid);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

インデックスの作成後に検索クエリを実行するには、コンピューターで0.0019秒かかりました。これは113倍高速です。

全文索引の追加も同様に行われます。 インデックスは、実際に探している列に従ってコンパイルする必要があります。

 ALTER TABLE articles ADD FULLTEXT(title,author,articletext); SELECT * FROM articles WHERE MATCH(title,author,articletext) AGAINST ('mysql');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

バックアップとセキュリティ

テーブルに変更を加える前に、データベース全体のバックアップコピーを作成します。 これを行うには、[エクスポート]ボタンをクリックしてPhpMyAdminおよびPhpMiniAdminを使用します。 データベースに顧客などの重要な情報が含まれている場合は、バックアップを安全な場所に保管してください。 mysqldumpコマンドを使用して、SSH経由でデータベースをバックアップすることもできます。

 mysqldump --user=myuser --password=mypassword --single-transaction --add-drop-table mydatabase > backup`date +%Y%e%d`.sql
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

同様のシナリオは、次のようなセキュリティリスクももたらします。 攻撃者にデータへの簡単なアクセス方法を提供します。 サーバー管理ツールによってある程度保護されているPhpMyAdminとは異なり、phpMiniAdminは短時間ダウンロードして忘れてしまうことがある単一のファイルです。 パスワードでアクセスを保護するか、使用後すぐに削除することをお勧めします。

テーブルを最適化する

MySQLおよびその他の種類のデータベースソフトウェアには、最適化ツールが組み込まれています。 テーブル内のデータが頻繁に変更される場合は、同様のツールを定期的に使用して、データベーステーブルのスペースを節約し、効率を高めることができます。 ただし、このような手順には一定の時間（テーブルのサイズに応じて数秒から数分以上）がかかり、他のクエリをブロックする可能性があるため、負荷が最小の期間に最適化を行うことをお勧めします。 必要な最適化の頻度に関する論争はおさまりません 。

（ ordersテーブルの）最適化手順を開始するには、次のコマンドを実行します。

 OPTIMIZE TABLE orders;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

100,000レコードの最適化されていない注文テーブルは31.2 MBを占有し、 SELECT * FROM注文の形式のクエリは0.2676秒で完了しました。 最初の最適化の後、サイズは30.8 MBに縮小され、要求は0.0595秒で完了しました。

次のPHP関数は、データベース内のすべてのテーブルの最適化を開始します。

 function OptimizeAllTables() { $tables = mysql_query ('SHOW TABLES'); //get all the tables while ($table = mysql_fetch_array ($tables)) mysql_query ('OPTIMIZE TABLE ' . $table[0]); //optimize them }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この機能を開始する前に、データベースに接続する必要があります。最近のほとんどのサイトはこれを自動的に行いますが、完全を期すために、対応するコードを示します。

 mysql_connect (DB_HOST, DB_USER, DB_PASSWORD); mysql_select_db (DB_NAME); OptimizeAllTables();
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

キャッシュを使用します

ブラウザが訪問したWebページをキャッシュするように、頻繁に実行されるデータベースクエリにインデックスを付けることができます。上記のリクエストを完了するには0.0019秒かかりました。インデックスを使用：

 SELECT * FROM orders WHERE customerid=2;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

同じリクエストを繰り返し実行するには、0.0004秒しかかかりません。MySQLは実行結果を記憶しており、クエリを完全に再実行することなく出力できます。

ただし、多くのニュースサイトやブログでは、同様のクエリを使用して、「公開」日以降にのみ記事が表示されるようにします。

 SELECT * FROM posts WHERE publisheddate <= CURDATE(); SELECT * FROM articles WHERE publisheddate <= NOW();
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そのようなリクエストはキャッシュできないため、それらは現在の日時に依存します。100,000レコードのテーブルで、上記のいずれかのタイプのクエリが約0.38秒間コンピュータで実行されました。インデックスなしの列。

そのようなリクエストがサイトの各ページで毎分数百回実行される場合、キャッシュするだけで生産性が大幅に向上します。NOWとCURDATEを実際の時間に置き換えることで、たとえば次のようにリクエストを「強制」してキャッシュを使用できます。

 SELECT * FROM articles WHERE publisheddate <= '2011-01-17 17:00';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

PHPを使用して、時間間隔が5分程度であることを確認できます。

 $time = time(); $currenttime = date ('Ymd H:i', $time - ($time % 300)); mysql_query (“SELECT * FROM articles WHERE publisheddate <= '$currenttime'”);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

式％300は、時間を最も近い300秒（5分）に丸めます。

MySQLには、RANDなどの他のキャッシュ不可関数もあります。

そして、キャッシュは成長しています...

キャッシュされたデータの量が増えると、Webサイトの速度も低下します。サイトに投稿、ページ、カテゴリ、製品、記事、その他の要素が増えるほど、クエリの関連性が高まります。例を見てみましょう：

 SELECT * FROM articles WHERE publisheddate <= '2011-01-17 17:00' AND categoryid=12;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

サイトに500のカテゴリがある場合、そのようなリクエストはキャッシュされ、結果はミリ秒を返します。しかし、1000個の常時表示されるカテゴリがある場合はどうでしょうか？それらは互いにキャッシュから押し出され、はるかに遅く実行されます。この場合、キャッシュのサイズを増やすと役立つ場合があります。ただし、キャッシュにより多くのメモリを割り当てると、他のタスクに悪影響を与える可能性があるため、注意してください。サーバー変数を変更することにより、キャッシュ効率を有効にして改善するための多くのヒントを見つけることができます。

キャッシングが無力な場合

テーブルが変更された場合、キャッシュは無力になります。テーブルから行を挿入、更新、削除すると、このテーブルに関連するすべてのクエリがキャッシュから削除されます。したがって、記事を見るたびにテーブル「記事」が更新される場合（たとえば、このテーブルにビューカウンターを持つフィールドがある場合）、上記の改善は役に立たない可能性があります。

このような場合、アプリケーションレベルでキャッシュを実装するツール（Memcachedなど）を学習する必要があります。また、独自のキャッシングシステムの作成に関する詳細については、次のセクションをお読みください。これらのオプションはどちらも、説明したよりもはるかに大きなソフトウェア変更を必要とします。

独自のキャッシュ

特に重いデータベースクエリの完了に時間がかかり、データが頻繁に変更されない場合は、結果を自分でキャッシュできます。

検索クエリ、ビュー、お気に入りへの追加、友人への送信を考慮に入れた式を使用して、先週サイトで20の人気のある投稿を表示するとします。そして、メインページにこのリストを箇条書きリストとして表示したいとします。

たとえば、PHPを使用する最も簡単な方法は、1時間または1日1回データベースにクエリを実行し、結果を別のファイルに保存することです。このファイルはサイトページに接続されます。

リストファイルを生成するPHPコードを記述した後、いくつかの方法を使用してスケジュールに従って実行できます。サーバースケジューラ（Plesk 8：サーバー→スケジュールされたタスク）を使用して、このスクリプトを次のように1時間ごとに実行できます。

 wget -O /dev/null -q http://www.mywebsite.co.uk/runhourly.php
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

または、同じPHPを使用してファイル作成時間を確認できます。ファイルが少なくとも1時間前に作成された場合、リクエストを実行します。この場合の3600は、1時間あたりの秒数です。

 $filestat = stat ('includes/complicatedfile.html'); //look up information about the file if ($filestat['mtime'] < time()-3600) RecreateComplicatedIncludeFile(); //over 1 hour readfile ('includes/complicatedfile.html'); //include the file into the page
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

上記の例「顧客はこの製品で他に何を購入しますか...」に戻ると、一般に新しい列またはテーブルにデータをキャッシュすることもできます。週に一度、各製品に対して複雑なクエリの大規模なセットを実行して、購入する製品を決定できます。

その後、結果の製品識別子を、コンマで区切られた要素のセットとして新しい列に保存できます。将来、id = 12の製品と一緒に購入される商品のリストを取得するには、次の過成長を実行する必要があります。

 SELECT * FROM products WHERE FIND_IN_SET(12,otherproductids);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

JOINを使用してクエリの数を減らす

サイトのいずれかのセクション（管理セクションなど）に、注文のあるユーザーのリストが表示されます。

この場合、以下のようなクエリが使用されます（注文が完了したことを意味する値による選択のため）：

 SELECT * FROM orders WHERE status>1;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、注文ごとに、それを発行したクライアントを見つける必要があります。

 SELECT * FROM customers WHERE id=1; SELECT * FROM customers WHERE id=2; SELECT * FROM customers WHERE id=3; etc
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

100件の注文に関する情報がすぐにページに表示される場合、101件のリクエストを行う必要があります。さらに、別のテーブルからの配送先住所に関する情報、すべての注文の総コスト、ページ作成速度が低下する、低下するなどの情報が必要な場合は、JOINを介してリクエストを組み合わせることにより、すべてをはるかに高速に実行できます。以下は、上記のクエリを組み合わせた例です。

 SELECT * FROM orders INNER JOIN customers ON orders.customerid = customers.id WHERE orders.status>=1;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これらのリクエストを記述する別の方法もありますが、JOINを使用しません。

 SELECT * FROM orders, customers WHERE orders.customerid = customers.id AND orders.status>=1;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

JOINを使用するためのリクエストを翻訳するのは難しい場合があります。 PHPコードを変更する必要があります。しかし、「遅い」ページで何千ものリクエストが実行される場合、説明されている方法に注意を払う必要があるかもしれません。詳細については、ウィキペディアを参照してください。これにはJOINの詳細が記載されています。 JOINで使用される列（この例ではcustomerid）にインデックスを付ける必要があります。

また、MySQLにクエリの実行方法を「説明」するように依頼することもできます。その結果、クエリの実行時にどのテーブルがどのように使用されるかがわかり、何かを最適化できます。以下のスクリーンショットは、EXPLAINを使用してWordPressで複雑なクエリの1つを実行した結果を示しています。

EXPLAINを使用して、MySQLが複雑なクエリを「認識する」方法を学習します

スクリーンショットには、使用されているテーブルとインデックス、JOINのタイプ、分析された行の数、その他の情報が表示されます。MySQLの公式Webサイトでは、EXPLAINの説明について説明し、この情報を使用してクエリを最適化する方法について少し説明しています（たとえば、インデックスを追加するなど）。

チート

最後に、「顧客はこの製品で他に何を購入しますか...」の例に戻り、それを単純化できます。たとえば、「Featured Products」という名前に変更し、同じカテゴリの他の製品をいくつか表示するか、手動で指定します。

おわりに

この記事では、データベースのパフォーマンスを単純なものからかなり複雑なものに改善するいくつかの方法を示します。よく設計されたほとんどのサイトには、すでにそのようなメソッドが含まれているはずです（JOINとインデックスを使用）。

説明したいくつかの方法（速度測定、インデックス付け、最適化、キャッシュなど）を使用することの効率と信頼性については多くの議論があるため、最終的な決定はあなた次第です。いずれにせよ、今考えるべき選択肢があります。

数か月または数年の通常の操作後にサイトの動作が遅くなった場合、理由を見つけるために何かを始める必要があります。

PS , , .

PPS Q&A , Google Page Speed Online .