MySQL의 최적화 기법 (Optimizing MySQL Database)

1. DB설계 원칙

1.1 가능한한 데이터를 작게 만들어라.
* 디스크 공간과 메모리를 작게 차지하는 DataType을 사용하여라.
* 가능한한 Column을 Not NULL로 선언해라. 그러면 컬럼당 한 bit를 절약할 수 있다.
* MyISAM파일 시스템에서, 가변길이의 컬럼 사용은 자제하여라. (ex: VARCHAR, TEXT, BLOB)
* Primary Index는 가능한한 작게 만들어라.
* 꼭 필요한 것만 Index로 만들어라. 특히 자료를 빠르게 저장/검색 하고자 할때.
* String을 인덱스로 사용할 경우. MySQL의 "Create Index Syntax"를 사용하면 빠르고 디스크 공간도 적게 차지한다.

1.2 Column Indexes
* Index를 사용하는 것이 Select를 빠르게 하는 가장 좋은 방법.
* Index를 만드는 예> create table test(blob_col, index(blob_col(10)));
   --> blob_col의 앞의 10 character를 index로 만든다.

1.3 Multiple Column Indexes
  * 예> 아래와 같이 Index를 만들어 주면
    CREATE TABLE test (
        id         INT NOT NULL,
        last_name  CHAR(30) NOT NULL,
        first_name CHAR(30) NOT NULL,
        PRIMARY KEY (id),
        INDEX name (last_name,first_name)
    );
 
--> SELECT * FROM test WHERE last_name='Widenius';
      SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';
위의 상황에서는 Index가 사용된다. 왜냐면 last_name을 Index에서 먼저 검색할 수 있으므로,
--> SELECT * FROM test WHERE first_name='Michael';
위의 상황에서는 Index가 사용되지 않는다 .First_name을 먼저 검색하므로

1.4 MySQL의 Index 사용하는 법
* MySQL의 Index(Primary Key, UNIQUE, INDEX, FULLTEXT)는 B-Tree에서 사용된다. 단, ISAM의 Search Engine의 경우 R-Tree를 지원하고 Memory Type의 Search Engine은 Hash Index도 지원한다.
* 만약 index가 (col1, col2, col3)로 되어 있다면 아래의 경우에는 Index를 사용한다.
   ... WHERE index1=1 AND index2=2 AND other_column=3
    /* index = 1 OR index = 2 */
   ... WHERE index=1 OR A=10 AND index=2
    /* optimized like "index_part1='hello'" */
   ... WHERE index_part1='hello' AND index_part3=5
    /* Can use index on index1 but not on index2 or index3 */
   ... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
* 그러나 아래의 경우는 Index가 사용되지 않는다. Index만들고 Query만들때 항상 염두해서 만들어야겠다!!
    /* index_part1 is not used */
    ... WHERE index_part2=1 AND index_part3=2
    /*  Index is not used in both parts of the WHERE clause  */
    ... WHERE index=1 OR A=10
    /* No index spans all rows  */
    ... WHERE index_part1=1 OR index_part2=10

  * Hash Index는 다른 특징을 가지고 있다.
   --> '=', '<>' Operation만 지원하며 >,< 등의 Operation은 지원하지 않는다. (생각해보면 당연하다)
   --> ORDER BY도 지원하지 않는다.
   --> BETWEEN문도 사용하지 않는것이 좋다.
   --> 전체 Key가 모두 Match되는데에 사용할만 하다.

  * CHAR, VARCHAR, BINARY, VARBINARY 모두 index로 사용될 수 있다.
  * BLOB, TECT가 Index로 사용되려면 prefix length가 세팅되어야한다.
  * 예> CREATE INDEX part_of_name ON customer (name(10));
  * 다음은 Storage Engine별 사용 가능한 Index Type들 이다.
Storage Engine Allowable Index Types
MyISAM BTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE

  * Engine과 Index사용법
     CREATE TABLE lookup (id INT) ENGINE = MEMORY;
     CREATE INDEX id_index USING BTREE ON lookup (id);