MySQL Index 유형별 적용

MySQL에서 인덱싱 컬럼은 일반적으로 속도를 향상시키나 적절한 유형의 인덱스는 성능에 더 큰 효율을 가져온다.

 

SQL 구문에 최적화하기 위해 인덱스를 적용할 경우에는 일반적으로 4가지 인덱스 유형이 있다.

 

1.     Column Index

2.     Concatenated Index

3.     Covering Index

4.     Partial Index

 

1.     Column Index

단순하게 말해서, 단일 컬럼의 성능을 높이기 위한 인덱스이다.

 

SELECT user_name, adress
FROM   TB_Test
WHERE id = 1;

id 컬럼에 인덱스를 추가한다.

ALTER TABLE  TB_Test
  ADD INDEX (id);

2.     Concatenated Index

종종 테이블들에 많은 단일 컬럼 인덱스가 있는 것을 볼 수 있다. 일반적으로 자주 필요하지 않은 경우가 많다.

MySQL 인덱스 이론에 대해 이해해야 한다. 일반적으로 MySQL 쿼리에서 각 테이블을 위해 몇가지 특별한 경우를 제외하면,

단 하나의 인덱스가 사용된다는 것이다.

 

SELECT user_name, adress
FROM   TB_Test
WHERE id = 1 and devision_cd = 5;

 

기존의 단일 컬럼 인덱스도 이용하긴 하겠으나, 아래와 같은 경우에 항상 2개 컬럼이 더 자주 조건절에 사용된다면, Query plan을 더 향상 시킬 수 있다.

 

ALTER TABLE  TB_Test
  ADD INDEX (id, devision_cd);

 

3.     Covering Index

Convering Index는 Query의 모든 컬럼을 cover한다는 말이다. covering index의 장점은 다양한 B-Tree 인덱스 페이지의 Lookup 이다. 추가적으로 Data page에 대한 Lookup이 필요없다.

 

SELECT user_name, adress
FROM   TB_Test
WHERE id = 1;

 

ALTER TABLE  TB_Test
ADD INDEX (id, user_name, adress);

 

 

4.     Partial Index

마지막 유형은 Partial Index이다. 이것은 MySQL 특징이고, 인덱스를 위한 컬럼에 대한 특별한 하위 집합이다.

 

SELECT id, adress
FROM   TB_Test
WHERE user_name like 'A%';

 

이 경우 user_name 컬럼에 인덱스를 생성하면 성능을 개선할 수 있다.

user_name 컬럼의 데이터 길이 평균값에 영향을 받는다면 partial index를 생성해 인덱스 크기를 줄이고, 추가적인 data lookup 비용을 줄일 수 있다.

 

ALTER TABLE  TB_Test
ADD INDEX (user_name(8));

- 윤성용