인덱스 생성과 제거

인덱스 생성

SELECT * FROM member;

SHOW INDEX는 테이블에 생성된 인덱스 정보를 보여줍니다.

SHOW INDEX FROM member;

SHOW TABLE STARUS는 테이블에 생성된 인덱스의 크기를 확인 할 수 있습니다.

SHOW TABLE STATUS LIKE 'member';

member 라는 글자가 들어간 테이블의 정보를 보자는 의미 입니다.

보조 인덱스

보조 인덱스는 단순 보조 인덱스와 고유 보조 인덱스로 나뉩니다. 단순 보조 인덱스는 중복을 허용한다는 의미로 중복이 안되는 고유 보조 인덱스와 반대 개념입니다.

CREATE INDEX idx_member_addr
	ON member (addr);

인덱스를 생성한 후에 ANALYZE TABLE문을 실행해줘야 실제로 적용됩니다.

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

고유 인덱스를 생성시에는 UNIQUE를 사용합니다.

CREATE UNIQUE INDEX idx_member_mem_name
	ON member (mem_name);

인덱스 활용 실습

ANALYZE TABLE member;
SHOW INDEX FROM member;

SELECT * FROM member;

인덱스를 사용했는지 여부는 결과 중 [Execution Plan] 창을 확인하면 됩니다. 전체 테이블 검색(Full Table Scan)을 한 것이 확인 됩니다.

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

Single Row(constant)라고 되어 있습니다. 인덱스를 사용해서 결과를 얻었다는 의미입니다. WHERE절에서 열 이름이 들어가야 인덱스를 사용합니다.

숫자의 범위를 조회해보겠습니다. 단순 보조 인덱스를 만듭니다.

CREATE INDEX idx_member_mem_number
	ON member (mem_number);
ANALYZE TABLE member; -- 인덱스 적용

인원수가 7명 이상인 그룹의 이름과 인원수를 조회해보겠습니다.

SELECT mem_name, mem_number
	FROM member
    WHERE mem_number >= 7;

인덱스를 사용한 것을 확인 할 수 있습니다.

인덱스 제거

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';

외래 키를 먼저 제거하고 기본 키를 제거합니다.

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