[SQL] - 인덱스의 실제 사용

1. 인덱스 생성

• 문법

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

인덱스 생성 이전




Index_length가 0이 나옴

• 단순 보조 인덱스 생성(주소 중복을 허용)

CREATE INDEX idx_member_addr
ON member(addr);

생성 확인

전체 인덱스 크기 확인

실제로 적용시키려면 ANALYZE TABLE 사용

ANALYZE TABLE member;

• 고유 보조 인덱스 생성(회원 이름 중복 허용X)

CREATE UNIQUE INDEX idx_member_mem_name
ON member(mem_name);

고유 인덱스 생성하면 이후로는 중복된 값이 입력되지 않는다.

중복된 데이터가 많은 열에 인덱스를 생성하는 것은 의미도 없고 오히려 성능에 나쁜 영향을 미친다.

2. 인덱스 활용 실습

ANALYZE TABLE member;
SHOW INDEX FROM member;

인덱스를 사용하려면 인덱스가 생성된 열 이름이 WHERE문에 있어야 함

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

WHERE에서 열에 연산이 가해지면 인덱스를 사용하지 않는다.

3. 인덱스 제거

• 문법

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;

보조 인덱스를 먼저 제거하는 것이 좋다.
클러스터형 인덱스를 먼저 제거하면 데이터를 쓸데없이 재구성해서 시간이 더 오래 걸림

• 외래 키 이름 조회하기

SELECT TABLE_NAME, CONSTRAINT_NAME
FROM information_schema.referential_constraints
WHERE CONSTRAINT_SCHEMA = 'market_db';

4. 효과적으로 인덱스 사용하기

• 인덱스는 열 단위에 생성된다.

• WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.

• WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.

• 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없다.

• 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.

• 사용하지 않는 인덱스는 제거하자.