혼공스 18일차(인덱스 실제 사용)

인덱스 생성

• 인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용합니다.
• UNIQE는 중복이 안되는 고유 인덱스를 만드는 것이며 생략하면 중복이 허용됩니다.
• CREATE UNIQUE로 인덱스를 생성하려면 기존 값들에 중복이 있으면 안됩니다.
• ASC 또는 DESC는 인덱스를 오름차순 또는 내림차순으로 만들어줍니다.
• 기본으로 ASC로 만들어지며 DESC로 만드는 경우는 거의 없다고 합니다.

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

 

 

예시) 단순 보조 인덱스 생성하기

• 인덱스 이름을 idx_member_addr로 지정하여 만들겠습니다.

CREATE INDEX idx_member_addr 
   ON member (addr);

SHOW INDEX FROM member;

 

아래 사진을 보시면 idx_member_addr 옆 Non_unique가 1로 설정되어 있는 것을 확인하실수 있습니다.

이는 고유 보조 인덱스가 아니라는 것입니다. (중복된 데이터 허용)

 

위가 다 되었으면 생성한 인덱스를 실제로 적용시키기 위해 ANALYZE TABLE문 을 사용해줘야 합니다.

사용시 Index_length 부분에 16KB(16384)가 표시된것을 확인할수 있습니다.

ANALYZE TABLE member;
SHOW TABLE STATUS LIKE 'member';

 

 

 

 

 

 

인덱스 활용 실습

• 인덱스를 사용했는지의 여부는 Execution Plan 창을 확인하면 됩니다.

 

사용하지 않았을때는 아래와 같습니다.

책을 예시로 들면 첫페이지부터 끝 페이지까지 넘겨본것입니다.

 

사용했을때는 아래와 같습니다.

인덱스가 생성된 mem_name으로 조회를 해본것입니다.

SELECT mem_id, mem_name, addr 
    FROM member 
    WHERE mem_name = '마마무';

 

 

 

 

인덱스 활용 실습_2

WHERE문에서 열에 연산이 가해지는 경우 인덱스를 사용하지 않습니다.

 

위와 같은 결과가 나오므로 아래와 같이 수정해주시면 됩니다.

 

 

인덱스 제거

• 인덱스를 제거하기 위해서 DROP INDEX 문을 사용합니다.
• 주의할 점으로 기본키, 고유키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못합니다.
• 클러스터형 인덱스와 보조 인덱스가 섞여 있을때는 보조 인덱스를 먼저 제거하는 것이 좋습니다.
• 클러스터형을 먼저 제거해도 되지만 시간이 더 오래걸립니다.

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

DROP INDEX idx_member_mem_name ON member;
DROP INDEX idx_member_addr ON member;
DROP INDEX idx_member_mem_number ON member;

 

외래키 제거 방법

먼저 외래키의 이름을 알아냅니다.

information_schema 데이터베이스의  referential_constraints 테이블을 조회하면
외래키의 이름을 알수 있습니다.

 

그후 외래 키를 먼저 제거 한후 기본키를 제거하면 됩니다.

ALTER TABLE buy 
    DROP FOREIGN KEY buy_ibfk_1;
ALTER TABLE member 
    DROP PRIMARY KEY;