MySQL 인덱스

인덱스(index)란?

인덱스(index)는 테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용합니다.

 

이러한 인덱스는 자주 사용되는 필드 값으로 만들어진 원본 테이블의 사본이라고 생각할 수 있습니다.

 

 

 

MySQL은 데이터를 검색할 때 첫 번째 필드부터 차례대로 테이블 전체를 검색합니다.

 

따라서 테이블이 크면 클수록 데이터를 탐색하는 시간도 많이 늘어나게 됩니다.

 

 

 

하지만 인덱스를 사용하면 테이블 전체를 읽지 않아도 되므로, 검색과 질의에 대한 처리가 빠르게 이루어집니다.

 

이러한 인덱스는 사용자가 직접 접근할 수는 없으며, 검색과 질의에 대한 처리에서만 사용됩니다.

 

 

 

이렇게 인덱스가 설정된 필드 값을 포함한 데이터의 삽입, 삭제, 수정 작업이 원본 테이블에서 이루어질 경우, 인덱스도 함께 수정되어야 합니다.

 

따라서 인덱스가 설정된 테이블의 처리 속도가 느려질 수 있습니다.

 

그러므로 인덱스는 수정보다는 검색이 자주 사용되는 테이블에서 사용하는 것이 더 좋습니다.

 

인덱스 생성

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

 

 

 

MySQL에서 인덱스를 생성하는 문법은 다음과 같습니다.

 

문법

CREATE INDEX 인덱스이름

 

ON 테이블이름 (필드이름1, 필드이름2, ...)

 

 

 

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

 

 

 

다음 예제는 Reservation 테이블의 Name 필드에 NameIdx라는 인덱스를 설정하는 예제입니다.

 

예제

 

 

CREATE INDEX NameIdx

 

On Reservation (Name);

 

 

인덱스 정보 보기

위와 같이 생성한 인덱스는 다음과 같은 문법을 통해 확인할 수 있습니다.

 

문법

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 : 인덱스에 관한 모든 기타 정보를 표시함.

 

UNIQUE INDEX 생성

UNIQUE INDEX는 중복 값을 허용하지 않는 인덱스입니다.

 

 

 

MySQL에서 UNIQUE INDEX를 생성하는 문법은 다음과 같습니다.

 

문법

CREATE UNIQUE INDEX 인덱스이름

 

ON 테이블이름 (필드이름1, 필드이름2, ...)

 

 

 

또한, 쉼표(,)를 사용하여 여러 필드에 UNIQUE INDEX를 설정해 줄 수도 있습니다.

 

 

 

다음 예제는 Reservation 테이블의 ID 필드에 IdIdx라는 UNIQUE INDEX를 설정하는 예제입니다.

 

예제

 

 

CREATE UNIQUE INDEX IdIdx

 

On Reservation (ID);

 

인덱스 정렬

인덱스를 생성할 때 인덱스에 포함되는 필드의 정렬 방식을 설정할 수 있습니다.

 

DESC 키워드를 사용하면 내림차순으로 정렬되며, ASC 키워드를 사용하면 오름차순으로 정렬됩니다.

 

문법

1. CREATE INDEX 인덱스이름

 

ON 테이블이름 (필드이름 DESC)

 

2. CREATE INDEX 인덱스이름

 

ON 테이블이름 (필드이름 ASC)

 

 

 

다음 예제는 Reservation 테이블의 Name 필드에 NameDescIdx라는 인덱스를 설정하는 예제입니다.

 

이때 Name 필드의 값들은 내림차순으로 정렬됩니다.

 

예제

 

 

CREATE INDEX NameDescIdx

 

On Reservation (Name DESC);

 

 

인덱스 추가

ALTER 문을 사용하여 테이블에 인덱스를 추가할 수 있습니다.

 

MySQL에서 추가할 수 있는 인덱스의 타입은 다음과 같습니다.

 

 

 

1. 기본 인덱스

 

2. UNIQUE INDEX

 

3. FULLTEXT INDEX

 

기본 인덱스 추가

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

 

 

 

MySQL에서 기본 인덱스를 추가하는 문법은 다음과 같습니다.

 

문법

ALTER TABLE 테이블이름

 

ADD INDEX 인덱스이름 (필드이름)

 

 

 

다음 예제는 Reservation 테이블의 Name 필드에 기본 인덱스를 추가하는 예제입니다.

 

예제

 

 

ALTER TABLE Reservation

 

ADD INDEX NameIdx (Name);

 

 

UNIQUE INDEX 추가

UNIQUE INDEX에서 필드의 값은 중복될 수 없으나, NULL 값을 가질 수는 있습니다.

 

 

 

MySQL에서 UNIQUE INDEX를 추가하는 문법은 다음과 같습니다.

 

문법

ALTER TABLE 테이블이름

 

ADD UNIQUE 인덱스이름 (필드이름)

 

 

 

다음 예제는 Reservation 테이블의 ID 필드에 UNIQUE INDEX를 추가하는 예제입니다.

 

예제

 

 

ALTER TABLE Reservation

 

ADD UNIQUE IdIdx (ID);

 

 

FULLTEXT INDEX 추가하기

FULLTEXT INDEX는 일반적인 인덱스와는 달리 매우 빠르게 테이블의 모든 텍스트 필드를 검색합니다.

 

이 인덱스는 검색 엔진과 유사한 방법으로 자연어를 이용하여 데이터를 검색할 수 있도록 모든 데이터의 문자열 단어를 저장합니다.

 

 

 

MySQL에서 FULLTEXT INDEX를 추가하는 문법은 다음과 같습니다.

 

문법

ALTER TABLE 테이블이름

 

ADD FULLTEXT INDEX이름 (필드이름)

 

 

 

다음 예제는 Reservation 테이블의 Name 필드에 FULLTEXT INDEX를 추가하는 예제입니다.

 

예제

 

 

ALTER TABLE Reservation

 

ADD FULLTEXT NameFtIdx (Name);

 

 

위의 예제처럼 FULLTEXT 인덱스의 인덱스 타입은 언제나 FULLTEXT입니다.

 

인덱스 삭제

ALTER 문을 사용하여 테이블에 추가된 인덱스를 삭제할 수 있습니다.

 

MySQL에서 인덱스를 삭제하는 방법은 다음과 같습니다.

 

 

 

1. ALTER 문

 

2. DROP 문

 

ALTER 문으로 인덱스 삭제

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

 

문법

ALTER TABLE 테이블이름

 

DROP INDEX 인덱스이름

 

 

 

다음 예제는 Reservation 테이블에서 NameIdx라는 이름의 인덱스를 삭제하는 예제입니다.

 

예제

 

 

ALTER TABLE Reservation

 

DROP INDEX NameIdx;

 

 

DROP 문으로 인덱스 삭제

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

 

문법

DROP INDEX 인덱스이름

 

ON 테이블이름

 

 

 

DROP 문은 내부적으로 ALTER 문으로 자동 변환되어 명시된 이름의 인덱스를 삭제해 줍니다.

 

 

 

다음 예제는 Reservation 테이블에서 IdIdx라는 이름의 인덱스를 삭제하는 예제입니다.

 

예제

 

 

DROP INDEX IdIdx

 

ON Reservation;