데이터베이스를 더 빠르게 만들기 위한 팁

대부분의 웹사이트 콘텐츠는 데이터베이스에 저장되며 사용자는 요청을 통해 콘텐츠에 액세스합니다. 데이터베이스는 매우 빠르며, 서버 리소스를 낭비하지 않도록 데이터베이스 속도를 최적화할 수 있는 기술이 많이 있습니다. 이 기사에는 데이터베이스 속도 최적화를 위한 10가지 팁이 포함되어 있습니다.

1. 데이터베이스를 신중하게 설계하세요

첫 번째 팁은 당연해 보일 수 있지만 실제로 대부분의 데이터베이스 문제는 잘못 설계된 데이터베이스 구조에서 발생합니다.

예를 들어 고객 정보와 결제 정보가 동일한 데이터베이스 열에 저장되는 예를 본 적이 있습니다. 이는 시스템 및 데이터베이스 개발자에게 좋지 않습니다.

새 데이터베이스를 생성할 때 표준 명명 방법과 기본 키를 사용하여 정보를 다른 테이블에 저장해야 합니다.

출처: http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

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. 가장 빠른 쿼리문은...보내지 않은 쿼리문입니다

　 데이터베이스에 명령문을 보낼 때마다 많은 서버 리소스를 사용하게 됩니다. 따라서 트래픽이 많은 웹사이트에서 가장 좋은 방법은 쿼리문을 캐시하는 것입니다.

명령문을 캐시하는 방법에는 여러 가지가 있으며 그 중 몇 가지가 아래에 나열되어 있습니다.

AdoDB: AdoDB는 PHP 데이터베이스 단순화된 라이브러리입니다. 이를 사용하면 다양한 데이터베이스 시스템(MySQL, PostGreSQL, Interbase 등) 중에서 선택할 수 있으며 속도를 위해 설계되었습니다. AdoDB는 간단하지만 강력한 캐싱 시스템을 제공합니다. 또한 AdoDB에는 BSD 라이선스가 있으므로 프로젝트에 무료로 사용할 수 있습니다. 상업용 프로젝트의 경우 LGPL 라이센스도 있습니다.

Memcached: Memcached는 동적 데이터베이스를 기반으로 데이터베이스의 부하를 줄이고 웹 사이트를 가속화할 수 있는 분산 메모리 캐싱 시스템입니다.

CSQL 캐시: CSQL 캐시는 오픈소스 데이터 캐싱 아키텍처입니다. 나는 그것을 시도하지 않았지만 멋져 보입니다.

4. 필요하지 않은 것을 선택하지 마세요

원하는 데이터를 얻으려면 가장 일반적인 방법은 모든 열을 나열하는 * 문자를 사용하는 것입니다.

 SELECT * FROM wp_posts;

단, 아래와 같이 필요한 열만 나열해야 합니다. 예를 들어 아주 작은 웹사이트에서는 한 명의 사용자가 1분에 방문한다면 별 차이가 없을 수도 있습니다. 그러나 Cats Who Code와 같이 트래픽이 많은 웹사이트인 경우 데이터베이스 작업을 많이 절약할 수 있습니다.

 SELECT title, excerpt, author FROM wp_posts;

5. LIMIT 사용

특정 개수의 데이터 행만 가져오는 것이 매우 일반적입니다. 예를 들어, 블로그는 페이지당 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-other-values-and-a-single-sql-query/

　7. 조인을 사용하여 하위 쿼리 교체

프로그래머는 하위 쿼리를 사용하거나 심지어 남용하는 것을 좋아할 수도 있습니다. 다음 하위 쿼리는 매우 유용합니다.

 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. 와일드카드를 신중하게 사용하세요.

와일드카드는 데이터를 검색할 때 하나 이상의 문자를 대체하는 데 매우 유용합니다. 사용할 수 없다는 말은 아니지만 주의해서 사용해야 하며 접두사 와일드카드나 후위 와일드카드를 사용하면 동일한 작업을 수행할 수 있습니다.

실제로 수백만 개의 데이터에 대해 전체 단어 와일드카드 검색을 사용하면 데이터베이스가 손상됩니다.

#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. OR 대신 UNION을 사용하세요

다음 예에서는 OR 문을 사용합니다.

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

UNION 문, 2개 이상의 select 문의 결과를 함께 넣을 수 있습니다. 다음 예는 위와 동일한 결과를 반환하지만 더 빠릅니다.

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

출처: http://www.bcarter.com/optimsql.htm

10. Index <를 사용하세요. 🎜>

데이터베이스 색인은 도서관에서 보는 색인과 비슷합니다. 도서관의 색인을 사용하면 독자가 원하는 책을 더 빨리 찾을 수 있듯이, 원하는 정보를 더 빨리 얻을 수 있습니다.

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

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

   CREATE INDEX idxModel ON Product (Model);

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