mysql 인덱싱

많은 분들이 mysql 테이블을 생성할 때 index를 주는데, 실제로 보니까 index를 잘못주는 경우가 많더군요.

1. 인덱스를 검사하는 법.
explain select * from Table_Name where A='a' and B='b' order by C,D,E ;
해당 쿼리문이 인덱스를 타는지 안타는지 알기 위해서는 쿼리문 앞에 explain을 붙여주면 인덱스를 타는지 안타는지 알 수 있습니다.
type의 결과값이 ALL일 경우 인덱스를 타지 않고 있습니다. range,index등일 때 인덱스를 타고 있습니다.(system,const,eq_ref,range,index,ALL,fulltext)
key의 값이 해당 쿼리문이 타고 있는 인덱스입니다.

2. 경우의 수가 작은 것은 인덱스를 타지 않습니다.
가령 특정 테이블에서 성별을 구분하는 테이블이 있고, 이 테이블에서 성별로 구분해서 보여주는 경우가 많다고 해서 성별을 나타내는 필드에 인덱스를 걸어주어도 인덱스를 타지 않습니다.
인덱스는 결과값이 1/3미만일 경우에만 인덱스를 탑니다. 그러므로 성별같은 경우에는 인덱스를 걸어주는 것이 테이블의 용량만 키우는 결과이기 때문에 인덱스를 걸어주지 않는 것이 좋습니다.
이것은 PRIMARY KEY에도 적용이 됩니다.
Table_Name 테이블에서 No 필드가 Primary Key, A 필드에 Index가 걸려있고, 데이타수가 30000개일 경우.
select * from Table_Name where No>0 and A>0; // 인덱스 안탐.
select * from Table_Name where No>20000 and A>0; // Primary 인덱스를 탐.
select * from Table_Name where No>0 and A>20000; // A 인덱스를 탐.

3. 인덱스는 하나만 탑니다.
오라클등에서는 인덱스를 여러개 걸어주면 그것을 다 타지만 mysql에서는 인덱스를 하나밖에 타지 않습니다.
가령 Table_Name 에서 A필드와 B필드, C필드에 인덱스를 걸어주고, 아래와 같은 쿼리문을 날립니다.
select * from Table_Name where A='a' and B='b' and C='c';
이럴경우 세개의 인덱스중 하나의 인덱스만 탑니다.

4. 결합인덱스 사용법.
특정 테이블에서 동일한 쿼리문을 주로 사용하고 그 쿼리문이 아래와 같을 경우...
select * from Table_Name where A='a' and B='b' and C='c';
이경우 이 세개의 인덱스를 전부 타기 위해서는 결합인덱스를 걸어줘야 됩니다.
ALTER TABLE Table_Name ADD INDEX (A,B,C);
이렇게 인덱스를 줄 경우에 세개의 필드를 정렬해서 하나의 인덱스를 생성하기 때문에 해당인덱스를 탈 수 있습니다.

5. order by 에서 사용하는 인덱스.
select * from Table_Name order by A; -> 이경우에 A인덱스를 탑니다.
select * from Table_Name where B='b' order by A; -> 이경우 B인덱스만 탑니다.
이경우에 두개의 인덱스를 다 타게 하려면 결합인덱스로 (A,B)를 생성시키고, A의 조건을 where 문에 지정을 해줍니다.(이부분은 사실 저도 조금 헷갈리는 부분입니다. 어쨌든 며칠동안 인덱스를 살펴본 결과 이렇게 해줘야 될거 같아서 일단 적습니다. ㅠ.ㅠ)

6. 검색에서 사용하는 인덱스.
보통 본문검색을 할 때 [where A like '%a%' ]와 같은 방식으로 조건을 주는데, 이경우 인덱스를 타지 않습니다. 가끔보면 검색에 주로 사용되는 필드에 인덱스를 거는 경우를 볼 수 있는데, 쓸데없는 짓입니다.
단, [where A like 'a%']와 같이 검색을 하는 경우에는 인덱스를 탑니다.

7. join문에서의 인덱스.
개인적으로 join문을 잘 사용하지 않는데, 그 이유가 join문을 사용할 경우 속도문제 때문이었습니다. 지금은 join문에서 인덱스를 타는 경우에 대해서 테스트해보고, 이것은 나중에 결과를 보고 글을 올려보도록 하겠습니다.