웹프로그래밍이 널리 퍼지면서 그 파트너로 Database(이하 DB)가 널리 사용되어지고 있다. ASP로 대표되는 Windows진영에서는 M$의 MSSQL이 주로 사용되며 상업적인 사이트에서는 Oracle을 많이 이용하고 있다. 무료라는 장점으로 인해 근래 널리 퍼지고 있는 Linux의 경우에는 ProgreSQL, mSQL, MySQL등이 많이 사용되고 있다. 특히 MySQL의 경우에는 작년부터 주목받기 시작한 PHP와 짝을 이루면서 현재에는 Linux 기반에서 가장 많이 쓰이고 있는 DB중의 하나가 되었다.

이번 글에서는 점점 널리 쓰여지고 있는 MySQL에서 성능을 높이기 위해 INDEX를 사용하는 법과 사용할 때의 주의점들에 대해서 알아보도록 하겠다.


---------------------------------------------------------------

INDEX는 하나의 컬럼에서 특정한 값을 찾을때(즉, select작업을 할때) 주로 사용된다. MySQL에서는 어떠한 type의 컬럼이라도 인덱스될 수 있다. 하나의 table은 16개까지의 index를 가질 수 있다. index의 최대 길이는 256byte지만 이 값은 MySQL을 컴파일 할 때 변경할 수 있다. CHAR type이나 VARCHAR type의 경우에는 컬럼의 앞쪽의 일부만 인덱스화 할 수 있는데 이는 컬럼의 모든 부분을 인덱스 하는 것 보다 더 낫다. 그리고 BLOB나 TEXT type의 컬럼의 경우에는 모든 컬럼이 인덱스화 될 수 없으며 앞의 일부분만을 인덱스화 하여야 한다.

INDEX를 사용하기 위한 형식과 실제 table을 생성시킬때의 예제는 다음과 같다.

KEY index_name (column_name(length))

예제1)
Create table tablename(
id varchar(14) primary key,
name varchar(10) not null,
occupation varchar(10) not null,
address varchar(100),
key idx_name(name),
key idx_occupation(occupation)
)

MySQL에서는 multiple-column index도 가능하다. 하나의 index에는 최고 15개의 컬럼을 가질 수 있다.

예제2)
Create table tablename(
id varchar(14) primary key,
name varchar(10) not null,
occupation varchar(10) not null,
address varchar(100),
key idx_name(name, occupation)
)
여기서 multiple-column index를 사용할 때 주의하여야 한다. 실제로 multiple-column index가 사용될 때, 어떠한 방식으로 사용되는지 살펴볼 필요가 있다. 다음 예제를 살펴보자.

mysql> SELECT * FROM tablename WHERE name='dinosfx' AND occupation='scan';

여기서 name 컬럼과 occupation이 위의 예제1과 같이 두개의 컬럼 각각에 index가 설정되어 있다고 하자. 이 경우 MySQL에서는 name 컬럼이나 occupation 컬럼을 살펴보고 index로 살펴보아야 할 행의 수가 적은쪽을 선택하여 검색을 한다. 만약 occupation에서 검색해야 할 행의 수가 적다면 MySQL은 occupation 컬럼을 먼저 검색한다는 이야기이다.

그리고 예제2에서처럼 두개의 컬럼이 multiple-column으로 index가 설정되어 있다고 가정하자. 이 경우 MySQL은 원하는 값을 바로 찾아내게 된다.

이렇게 multiple-column index를 사용하면 검색을 손쉽게 할 수 있다. 그러나 multiple-column index를 사용할 때에는 주의할 점이 있다. multiple-column index에서는 가장 왼쪽의 컬럼이 select의 where절에 사용되어야 한다는 것이다. 다음의 예제를 보자.
이 예제에서 index는 KEY index_name (column1, column2, column3) 과 같이 걸려 있다.

1) mysql> SELECT * FROM tablename WHERE column1 = value1
2) mysql> SELECT * FROM tablename WHERE column2 = value2;
3) mysql> SELECT * FROM tablename WHERE column2 = value2
and column3 = value3;

1)의 경우 index에서 제일 왼쪽에 설정되어 있는 column이므로 index를 사용하여 검색할 수 있다. 그러나 2)와 3)의 경우에는 column1이 검색 조건에 포함되지 않았으므로 index를 사용하지 못하고 검색이 이루어진다. 따라서 1), 2)와 3)의 세가지 경우에 대해서 모두 index를 사용하고자 한다면

key idx1(column1)
key idx2(column2, column3)

와 같이 index를 설정하여야 할 것이다.

MySQL에서는 다음과 같은 경우에 index를 사용하여 검색을 한다. (예제1 참조)

mysql> select * from tablename where name LIKE "dino%";
mysql> select * from tablename where name LIKE "di%sf%";

다음과 같은 경우에는 index가 설정이 되어 있더라도 index를 사용하지 못하고 검색을 하게 된다. (예제1 참조)

mysql> select * from tablename where name LIKE "%nos%";
mysql> select * from tablename where name LIKE id; // 다른 컬럼과의 비교

첫번째의 경우에는 wild card문자가 앞쪽에 있기 때문에 index를 사용하지 못한다. 그러므로 위의 예제에서처럼 검색어의 가운데나 끝에 wild card문자가 들어가야 한다. 두번째의 경우에는 like에서의 비교 대상이 변하지 않는 값이 아니므로(the LIKE value is not a constant.) index를 사용할 수 없다.

MySQL에서는 이 외에도 =, >, >=, <, <=, BETWEEN, LIKE 연산자를 이용하여 검색을 할 때 index를 사용한다. (물론 검색어의 첫머리에 wild card 문자가 포함되면 index를 사용하지 못한다.)


----------------------------------------------------------------

간단하게나마 MySQL에서 index를 사용하여 검색 속도를 향상하는 방법을 살펴보았다. 그러나 실제로 index를 사용할 때 주의해야 할 점이 하나 있다. Index는 검색(select 작업)을 할때 큰 역할을 한다. (MySQL reference를 보면 1000개의 행에서 검색을 할때 index를 설정한 경우가 그렇지 않은 경우보다 적어도 100배 빠르다고 한다.) 하지만 빈번하게 insert나 update가 이루어지는 경우 index를 갱신하는 작업이 필요하므로 select할 때 줄인 시간만큼 insert나 update에서 더 시간이 걸릴 수도 있다. 그러므로 index의 사용은 table과 자료의 목적에 따라 적절히 사용되어야 할 것이다.