[MySQL] 14. 인덱스의 생성과 제거 문법(CREATE INDEX, DROP INDEX)

인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용해야 한다.

 

UNIQUE는 중복이 안 되는 고유 인덱스를 만드는 것인데, 생략하면 중복이 허용된다. CREATE UNIQUE로 인덱스를 생성하려면 기존에 입력된 값들에 중복이 있으면 안 된다. 그리고 인덱스를 생성한 후에 입력되는 데이터와도 중복될 수 없으니 신중해야 한다.

 

ASC 또는 DESC는 인덱스를 오름차순 또는 내림차순으로 만들어준다. 기본은 ASC로 만들어지며, 일반적으로 DESC로 만드는 경우는 거의 없다.

 

CREATE [UNIQUE] INDEX 인덱스_이름
    ON 테이블_이름 (열_이름) [ASC | DESC]

 

인덱스 생성의 예

CREATE INDEX idx_member_addr
    ON member (addr);
    
SHOW INDEX FROM member;

 

Result

 

 

CREATE UNIQUE INDEX idx_member_name
    ON member (mem_name);

ANALYZE TABLE member; -- 지금까지 만든 인덱스 모두 적용
SHOW INDEX FROM member;

 

Result

 

 

인덱스를 실질적으로 활용하기 위해서는 WHERE 문을 사용해야 한다.

 

인덱스 활용의 예

SELECT mem_id, mem_name, addr
    FROM member
    WHERE mem_name = '에이핑크';

 

Result

 

Execution Plan

 

 

MySQL의 경우 WHERE 문에 작성된 조건에 따라 그 효율성을 먼저 따져, 만약 인덱스 검색의 효율이 더 좋다면 인덱스 검색을, 그렇지 않다면 테이블 전체 검색을 수행한다.

따라서 되도록 WHERE 절에 작성된 열 이름에는 가공을 하지 않는 것이 바람직하다.

---

인덱스를 제거하기 위해서는 DROP INDEX 문을 사용하고 형식은 다음과 같다.

 

DROP INDEX 인덱스_이름 ON 테이블_이름

 

주의할 점은 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다는 것이다. 이 경우 ALTER TABLE 문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스도 제거할 수 있다.

 

ALTER TABLE member
    DROP PRIMARY KEY;