인덱스(index)는 테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용합니다.
이러한 인덱스는 자주 사용되는 필드 값으로 만들어진 원본 테이블의 사본이라고 생각할 수 있습니다.
MySQL은 데이터를 검색할 때 첫 번째 필드부터 차례대로 테이블 전체를 검색합니다.
따라서 테이블이 크면 클수록 데이터를 탐색하는 시간도 많이 늘어나게 됩니다.
장점
MIN(), MAX() 등의 함수를 사용하는 쿼리에 대해 해당 필드에 인덱스가 있는 경우 빠르게 찾을 수 있습니다.
ORDER BY와 GROUP BY를 빠르게 수행할 수 있습니다.
단점
ISAM, MyISAM 테이블 인덱스를 무겁게 만들면 인덱스 파일이 데이터 파일보다 더 빠르게 최대 크기에 도달됩니다.
BDB(Berkeley DB) 테이블은 동일한 파일 내에 데이터와 인덱스 값을 함께 저장하며, 인덱스를 추가하게 되면 해당 테이블의 최대 파일 크기에 빠르게 도달하게 됩니다.
InnoDB 테이블은 테이블 스페이스 안에 모든 공간을 공유하는 구조이며, 인덱스를 추가하면 테이블 스페이스 내의 공산을 빠르게 소모합니다.
검색속도는 향상되지만 삽입, 삭제, 갱신 속도는 느려집니다.
고려사항
인덱스를 위한 칼럼을 선택하는 가장 좋은 기준은 WHERE 절 안에 나오는 칼럼, 조인 절에 명명된 칼럼, ORDER BY, GROUP BY 절에 나오는 칼럼 등이 된다는 것입니다.
SELECT 키워드 뒤에 나오는 출력 칼럼, 목록에만 나오는 칼럼은 좋은 선택이 아닙니다.
유일한 인덱스 사용: 유일한 값을 가지고 있는 칼럼들에 대해 효과가 좋고, 중복된 값들이 많은 칼럼들에 대해 효과가 나쁩니다.
짧은 값으로 인덱스를 만듭니다.
가장 왼쪽의 접두어를 활용합니다.
인덱스 생성
CREATE 문을 사용하여 인덱스를 생성할 수 있습니다.
1
CREATEINDEX 인덱스명 ON 테이블명 (필드명1, 필드명2, ...)
이때 쉼표(,)를 사용하여 여러 필드를 가지는 인덱스를 생성할 수도 있습니다.
1 2
-- 예 CREATEINDEX NameIdx OnTest (Name);
인덱스 추가
기본 인덱스에서 필드의 값은 같은 값이 여러 번 저장될 수 있으며, NULL 값을 가질 수도 있습니다.
1
ALTERTABLE 테이블명 ADDINDEX 인덱스명 (필드명)
1 2
-- 예 ALTERTABLETestADDINDEX NameIdx (Name);
인덱스 삭제
ALTER 문으로 삭제
ALTER 문을 사용하면 해당 테이블에서 명시된 인덱스를 삭제할 수 있습니다.
1
ALTERTABLE 테이블명 DROPINDEX 인덱스명
1 2
-- 예 ALTERTABLETestDROPINDEX NameIdx;
DROP 문으로 삭제
DROP 문을 사용하면 해당 테이블에서 명시된 인덱스를 삭제할 수 있습니다.
1
DROPINDEX 인덱스명 ON 테이블명
1 2
-- 예 DROPINDEX NameIdx ONTest;
인덱스 정보 보기
생성한 인덱스를 확인할 수 있습니다.
1
SHOW INDEX FROM 테이블명
이때 반환되는 인덱스 정보의 필드 값은 다음과 같습니다.
Table : 테이블의 이름을 표시함.
Non_unique : 인덱스가 중복된 값을 저장할 수 있으면 1, 저장할 수 없으면 0을 표시함.
Key_name : 인덱스의 이름을 표시하며, 인덱스가 해당 테이블의 기본 키라면 PRIMARY로 표시함.
Seq_in_index : 인덱스에서의 해당 필드의 순서를 표시함.
Column_name : 해당 필드의 이름을 표시함.
Collation : 인덱스에서 해당 필드가 정렬되는 방법을 표시함.
Cardinality : 인덱스에 저장된 유일한 값들의 수를 표시함.
Sub_part : 인덱스 접두어를 표시함.
Packed : 키가 압축되는(packed) 방법을 표시함.
Null : 해당 필드가 NULL을 저장할 수 있으면 YES를 표시하고, 저장할 수 없으면 ''를 표시함.