특정 열의 빠른 조회 를 위해서 인덱스를 사용합니다. 하지만 많은 인덱스를 걸어주는 것 보다는 가장 적절하고 효율성 있게 인덱스를 사용하는것이 중요합니다. 인덱스에 대해서 알아보도록 하겠습니다. MySQL 인덱스는 PRIMARY KEY, UNIQUE KEY, INDEX, FULLTEXT 를 지원하고 있습니다. 저장 방식은 B-Tree 로 저장이 됩니다. 우선 B-Tree 에 대해서 간단하게 알아보겠습니다.

B-Tree (출처 : CCL : Wikipedia B Tree)
B-Tree 는 메모리의 저장 공간의 부족과, 저장장치에 효율적으대용량 처리 방식을 위해 고안된 알고리즘입니다. 디스크에서 탐색트리를 구현할때의 발생하는 문제들을 최적으로 해결해줍니다. 검색시 검색키와 타 키들과의 연산을 줄이고 메모리에 올려져 있지 않은 데이터(B-Tree)를 조회하기 위해 로(최소한으로) 접근할 수 있도록 합니다. 검색시는 상당한 효율성이 있고 방식은 이진트리 탐색 방식과 동일한 패턴으로 수행합니다. 하향식으로 검색 대상의 값을 구분 값과 비교하여 자식포인터를 찾아가는 방식으로 진행됩니다. 삽입의 경우에는 디스크에 인덱싱된 내용을 검색하고 그 노드를 비교 하면서 삽입을 함으로 오버해드가 발생하게 됩니다.

INDEX
인덱스는 말 그대로 데이터의 목차를 알려주는 것입니다. 어떤 데이터가 어느 위치에 있는지 알려주어 그 위치에 해당하는 정보를 빠르게 접근 할 수 있도록 하기 위해서 사용됩니다.

인덱스를 사용하는 목적은 아래와 같습니다.
1. 조건과 일치하는 열을 빨리 찾기 위해
2. JOIN 시 다른 테이블의 열을 추출 하기 위해
3. MAX, MIN값을 찾기 위해 사용합니다.

인덱스를 생성할때 가장 효율적인 자료 형은 정수형 자료 입니다. (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 등) 가변적인 크기와 정규화 할 수 없는 데이터 (텍스트 데이터) 등은 인덱스를 생성할때 비효율적으로 동작합니다. 이를 위해 FULLTEXT 인덱스를 사용할 수 있습니다. Fulltext 는 TEXT , BLOB, VARCHAR등 가변적이고 일반적인 인덱싱의 효율성이 떨어지는 부분에서 많은 효과를 가져올 수 있는 형태의 인덱싱입니다. LIKE ‘%검색어%’ 와 비슷하고 빠르게 검색을 할 수 있습니다.

PRIMARY KEY
PRIMARY KEY 는 첫번째 키 즉 중복이 될수 없으며 ROW와 1:1 매칭이 되는 키 입니다. 주로 정수형 필드와 AUTO_INCREMENT 를 함께 사용하여 순차적으로 증가한 값을 사용하게 됩니다. 중요한것은 회원 ID나 정수형이 아닌 필드를 PRIMARY KEY로 이용하는것은 매우 비 효율적인 방식입니다. 이 키는 ROW의 고유성을 유지 시키기 위해 사용이 됩니다.

INDEX
일반적인 인덱스 입니다. 중복이 가능하고 검색을 빠르게 진행하기 위해 사용되는 인덱스 입니다. LIKE검색시 LIKE앞 부분에 와일드 카드 %,? 가 입력될 시는 INDEX를 사용할 수 없습니다.

UNIQUE
PRIMARY KEY 와 동일하게 중복이 안되는 키 입니다. 회원 ID나 , 전화번호등 고유한 정보를 인덱싱 할때 사용됩니다.

FULLTEXT
FULLTEXT 텍스트 필드에 LIKE ‘%검색문자열%’ 과 비슷한 형태의 검색결과를 얻을 수 있고 텍스트에 최적화된 인덱싱 방식입니다. 현재 MySQL에서는 4.1이상 UTF-8형태의 필드에서 검색이 가능합니다.

FOREIGN KEY
데이터 의 참조 무결성을 유지 시키기 위해 사용됩니다. 자세한 내용은 MySQL FOREIGN KEY를 참조하세요.

단일 컬럼 인덱스와 , 다중 컬럼 인덱스
단일 컬럼 인덱스는 하나의 필드에 인덱스를 걸어주는 형태입니다. 예를 들어 회원의 생년월일을 인덱스를 걸어 줄때 하나의 필드에 인덱스를 걸어줍니다. 다중 컬럼 인덱스는 WHERE 조건에서 2개 이상(최대 15개)의 필드를 조합하여 검색을 할때 사용합니다. 첫번째 조건과 이를 만족하는 두번째 조건을 함께 인덱싱을 하여 최상의 결과를 뽑아 내기 위해 사용이 됩니다. 하지만 다중 컬럼 인덱스의 경우에는 단일 컬럼 인덱스 보다 더 비효율적으로 INSERT / UPDATE 를 수행하기 때문에 꼭 필요한 경우만 사용하는게 좋습니다. 그리고 단일 컬럼 인덱스 2개 이상을 사용할때 첫번째 조건과 두번째 조건이 포함관계(AND)일때는 다중 인덱스를 사용하는게 좋습니다. 가급적 인덱싱에 이용되는 필드는 업데이트가 안되는 값이 좋습니다.

INDEX 의 사용방법
인덱스를 실제 테이블에 적용하는 방법입니다. 테이블 설계시(생성) 또는 변경시에 적용이 가능합니다.

DB 테이블 엔진별 사용가능한 인덱스 타입

테이블 엔진별 사용 가능한 인덱스 타입

DB엔진	인덱스 타입
MyISAM	BTREE, RTREE
InnoDB	BTREE
MEMORY, HEAP	HASH, BTREE
NDB	HASH, BTREE

 

 

위와 같이 테이블이 생성이 되었습니다. DESC 화면에서 KEY 부분에 PRI , MUL, UNI 라고 되어 있는 부분이 인덱스가 걸린 형태를 보여주는 것입니다. 더욱더 자세한 인덱스의 화면을 보시려면 SHOW INDEX FROM 을 사용하면 됩니다.

mysql> SHOW INDEX FROM tMember;
+———+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+———+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+
| tMember |          0 | PRIMARY  |            1 | seq         | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| tMember |          0 | tel      |            1 | tel         | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| tMember |          1 | name     |            1 | name        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+———+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+
3 rows in set (0.00 sec)

SHOW INDEX FROM 은 테이블 인덱스 상태를 상세 하게 볼 수 있습니다. 테이블과 Unique여부 그리고 인덱스 타입 , Cardinality 를 볼 수 있습니다. 실제적으로 인덱싱된 내용의 갯수는 Cardinality 로 알아 볼수 있습니다.(블로그 디자인상 가로가 좁아서 2줄로 보이는 군요 ㅋㅋ)

테이블에 데이터는 있으나 Cardinality 가 정상적인 값이 아닌 NULL 이거나 0일때 는 REPAIR TABLE 로 테이블을 재 색인 할 수 있습니다.

인덱스 생성 방법 및 확인 방법을 알아보았습니다. ^^

출처 : http://www.lovelgw.com/Blog/212