[MySQL] 인덱스(INDEX) 정리

인덱스(index)란?

인덱스(index)는 테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용합니다.
이러한 인덱스는 자주 사용되는 필드 값으로 만들어진 원본 테이블의 사본이라고 생각할 수 있습니다.
MySQL은 데이터를 검색할 때 첫 번째 필드부터 차례대로 테이블 전체를 검색합니다.
따라서 테이블이 크면 클수록 데이터를 탐색하는 시간도 많이 늘어나게 됩니다.

장점

• MIN(), MAX() 등의 함수를 사용하는 쿼리에 대해 해당 필드에 인덱스가 있는 경우 빠르게 찾을 수 있습니다.
• ORDER BY와 GROUP BY를 빠르게 수행할 수 있습니다.

단점

• ISAM, MyISAM 테이블 인덱스를 무겁게 만들면 인덱스 파일이 데이터 파일보다 더 빠르게 최대 크기에 도달됩니다.
• BDB(Berkeley DB) 테이블은 동일한 파일 내에 데이터와 인덱스 값을 함께 저장하며, 인덱스를 추가하게 되면 해당 테이블의 최대 파일 크기에 빠르게 도달하게 됩니다.
• InnoDB 테이블은 테이블 스페이스 안에 모든 공간을 공유하는 구조이며, 인덱스를 추가하면 테이블 스페이스 내의 공산을 빠르게 소모합니다.
• 검색속도는 향상되지만 삽입, 삭제, 갱신 속도는 느려집니다.

고려사항

• 인덱스를 위한 컬럼을 선택하는 가장 좋은 기준은 WHERE 절 안에 나오는 칼럼, 조인 절에 명명된 칼럼, ORDER BY, GROUP BY 절에 나오는 칼럼 등이 된다는 것입니다.
• SELECT 키워드 뒤에 나오는 출력 칼럼, 목록에만 나오는 칼럼은 좋은 선택이 아닙니다.
• 유일한 인덱스 사용: 유일한 값을 가지고 있는 칼럼들에 대해 효과가 좋고, 중복된 값들이 많은 컬럼들에 대해 효과가 나쁩니다.
• 짧은 값으로 인덱스를 만듭니다.
• 가장 왼쪽의 접두어를 활용합니다.

인덱스 생성

CREATE 문을 사용하여 인덱스를 생성할 수 있습니다.

CREATE INDEX 인덱스명 ON 테이블명 (필드명1, 필드명2, ...)

이때 쉼표(,)를 사용하여 여러 필드를 가지는 인덱스를 생성할 수도 있습니다.

-- 예
CREATE INDEX NameIdx On Test (Name);

인덱스 추가

기본 인덱스에서 필드의 값은 같은 값이 여러 번 저장될 수 있으며, NULL 값을 가질 수도 있습니다.

ALTER TABLE 테이블명 ADD INDEX 인덱스명 (필드명)

-- 예
ALTER TABLE Test ADD INDEX NameIdx (Name);

인덱스 삭제

ALTER 문으로 삭제

ALTER 문을 사용하면 해당 테이블에서 명시된 인덱스를 삭제할 수 있습니다.

ALTER TABLE 테이블명 DROP INDEX 인덱스명

-- 예
ALTER TABLE Test DROP INDEX NameIdx;

DROP 문으로 삭제

DROP 문을 사용하면 해당 테이블에서 명시된 인덱스를 삭제할 수 있습니다.

DROP INDEX 인덱스명 ON 테이블명

-- 예
DROP INDEX NameIdx ON Test;

인덱스 정보 보기

생성한 인덱스를 확인할 수 있습니다.

SHOW INDEX FROM 테이블명

이때 반환되는 인덱스 정보의 필드 값은 다음과 같습니다.

1. Table : 테이블의 이름을 표시함.
2. Non_unique : 인덱스가 중복된 값을 저장할 수 있으면 1, 저장할 수 없으면 0을 표시함.
3. Key_name : 인덱스의 이름을 표시하며, 인덱스가 해당 테이블의 기본 키라면 PRIMARY로 표시함.
4. Seq_in_index : 인덱스에서의 해당 필드의 순서를 표시함.
5. Column_name : 해당 필드의 이름을 표시함.
6. Collation : 인덱스에서 해당 필드가 정렬되는 방법을 표시함.
7. Cardinality : 인덱스에 저장된 유일한 값들의 수를 표시함.
8. Sub_part : 인덱스 접두어를 표시함.
9. Packed : 키가 압축되는(packed) 방법을 표시함.
10. Null : 해당 필드가 NULL을 저장할 수 있으면 YES를 표시하고, 저장할 수 없으면 ''를 표시함.
11. Index_type : 인덱스에 사용되는 메서드(method)를 표시함.
12. Comment : 해당 필드를 설명하는 것이 아닌 인덱스에 관한 기타 정보를 표시함.
13. Index_comment : 인덱스에 관한 모든 기타 정보를 표시함.

참고

• http://www.tcpschool.com/mysql/mysql_index_create