データベースを高速化するためのヒント

ほとんどの Web サイトのコンテンツはデータベースに保存されており、ユーザーはリクエストを通じてコン​​テンツにアクセスします。データベースは非常に高速であり、サーバー リソースを無駄にしないようにデータベースの速度を最適化できる技術が多数あります。この記事では、データベースの速度を最適化するための 10 のヒントを紹介しました。

1. データベースを慎重に設計する

最初のヒントは明白に思えるかもしれませんが、実際、データベースの問題のほとんどは、設計が不十分なデータベース構造に起因しています。

例えば、顧客情報と支払い情報が同じデータベース列に格納されている例に遭遇したことがあります。これはシステム開発者やデータベース開発者にとって好ましくありません。

新しいデータベースを作成するときは、標準の命名方法と主キーを使用して、情報を別のテーブルに保存する必要があります。

出典: http://www.simple-talk.com/sql/database-administration/ten-common-database-design-missing/

2. 最適化するために何が必要かを知る

何かを最適化したい場合クエリ ステートメント。このステートメントの結果を明確に知ることは非常に役立ちます。 EXPLAIN ステートメントを使用すると、多くの役立つ情報が得られます。例を次に示します。

 EXPLAIN SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

出典: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

3最速のクエリは、送信しないクエリです

データベースにステートメントを送信するたびに、大量のサーバー リソースが消費されます。したがって、トラフィックの多い Web サイトでは、クエリ ステートメントをキャッシュするのが最善の方法です。

ステートメントをキャッシュする方法はたくさんありますが、そのいくつかを以下に示します:

AdoDB: AdoDB は、PHP 用のデータベースを簡素化したライブラリです。これを使用すると、さまざまなデータベース システム (MySQL、PostGreSQL、Interbase など) から選択でき、速度を重視して設計されています。 AdoDB は、シンプルだが強力なキャッシュ システムを提供します。また、AdoDB には BSD ライセンスがあるため、プロジェクトで無料で使用できます。商用プロジェクトの場合は、LGPL ライセンスも取得しています。

Memcached: Memcached は、データベースの負荷を軽減し、動的データベースに基づいて Web サイトを高速化できる分散メモリ キャッシュ システムです。

CSQL Cache: CSQL Cache は、オープンソースのデータ キャッシュ アーキテクチャです。試したことはありませんが、とても良さそうです。

4. 必要のないものは選択しないでください

必要なデータを取得するには、すべての列をリストする * 文字を使用するのが一般的な方法です。

 SELECT * FROM wp_posts;

ただし、以下に示すように、必要な列のみをリストする必要があります。非常に小規模な Web サイトの場合、たとえば 1 分間に 1 人のユーザーがアクセスする場合は、何の違いも生じない可能性があります。ただし、Cats Who Code のようなトラフィックの多い Web サイトの場合、これによりデータベースの作業が大幅に節約されます。

 SELECT title, excerpt, author FROM wp_posts;

5. LIMITの使用

特定の行数のデータのみを取得することは非常に一般的です。たとえば、ブログには 1 ページに 10 件の記事しか表示されません。このとき、LIMIT を使用して、選択するデータの行数を制限する必要があります。

LIMIT がなく、テーブルに 100,000 行のデータがある場合、すべての行を走査することになり、サーバーに不必要な負担がかかります。

  SELECT title, excerpt, author FROM wp_posts LIMIT 10;

6. ループ内のクエリを避ける

PHP で SQL を使用する場合、ループ文の中に SQL を入れることができます。ただし、そうするとデータベースにさらなる負担がかかります。

次の例は、「ループ内でのクエリのネスト」の問題を示しています:

   foreach ($display_order as $id => $ordinal){     
          $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
          mysql_query($sql); 
      }

これを行うことができます:

   UPDATE categories
         SET display_order = CASE id 
             WHEN 1 THEN 3
             WHEN 2 THEN 4
             WHEN 3 THEN 5    
      END WHERE id IN (1,2,3)

出典: http://www.karlrixon.co.uk/articles/sql/update -multiple-rows -with- Different-values-and-a-single-sql-query/

7. join を使用してサブクエリを置き換えます

プログラマはサブクエリを使用したり、乱用したりする場合があります。次のサブクエリは非常に便利です:

 SELECT a.id,
            (SELECT MAX(created)
             FROM posts
             WHERE author_id = a.id) 
      AS latest_post FROM authors a

サブクエリは便利ですが、結合ステートメントで置き換えることができ、より高速に実行されます。

  SELECT a.id, MAX(p.created) AS latest_post
      FROM authors a
      INNER JOIN posts p
         ON (a.id = p.author_id)
      GROUP BY a.id

出典: http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/

8. ワイルドカードは慎重に使用してください

検索時にワイルドカードを使用するのは非常に簡単ですデータの場合、ワイルドカードを使用して 1 つ以上の文字を置き換えることができます。使用できないとは言いませんが、使用する場合は注意が必要で、前置ワイルドカードや後置ワイルドカードを使用しても同じ作業が行われる可能性がありますので使用しないでください。

実際、何百万ものデータに対して単語全体のワイルドカード検索を使用すると、データベースがクラッシュします。

#Full wildcard 
      SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%'; 
      #Postfix wildcard 
      SELECT * FROM TABLE WHERE COLUMN LIKE 'hello%'; 
      #Prefix wildcard 
      SELECT * FROM TABLE WHERE COLUMN LIKE '%hello';

出典: http://hungred.com/useful-information/ways-optimize-sql-queries/

9. UNION を使用して OR を置き換えます

次の例では、OR ステートメントを使用しています。 2 つ以上の select ステートメントの結果を結合できます。次の例は、上記と同じ結果を返しますが、より高速です:

  SELECT * FROM a, b WHERE a.p = b.q or a.x = b.y;

出典: http://www.bcarter.com/optimsql.htm

10. インデックスを使用します

データベース インデックスを使用すると、ライブラリにアクセスできます。図書館の索引を使用すると、読者が必要な本をより早く見つけることができるのと同じように、必要な情報をより迅速に入手できます。

　　可以在一个列上创建索引，也可以在多个列上创建。索引是一种数据结构，它将表中的一列或多列的值以特定的顺序组织起来。

　　下面的语句在Product表的Model列上创建索引。这个索引的名字叫作idxModel

   CREATE INDEX idxModel ON Product (Model);

　　来源: http://www.sql-tutorial.com/sql-indexes-sql-tutorial/