혼자 공부하는 SQL 5주차

5주차 미션 

- 인덱스 생성하고 key_name이 PRIMARY로 출력된 결과화면 캡쳐하기

 

 

인덱스 생성

 

테이블을 생성할 때 Primary key 문법을 사용하면 클러스터형 인덱스가, Unique 문법을 사용하면 보조 인덱스가 자동으로 생성됩니다.

 

하지만 직접 인덱스를 생성하고 싶다면 다음과 같은 CREATE INDEX 문을 사용하면 됩니다.

 

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

 

위의 쿼리는 보조 인덱스 중 고유 인덱스를 만드는 쿼리인데 ASC 또는 DESC로 오름차순 또는 내림차순으로 만들어줍니다. 기본은 ASC로 만들어지며 DESC로 만드는 경우는 거의 없습니다.

 

---

 

이제 실습을 통해 더욱 자세히 알아보겠습니다.

 

SELECT * FROM market_db.member;

 

출력 결과

위의 테이블을 가지고 진행해보겠습니다. 위 테이블은 이미 인덱스가 생성되어 있습니다.

 

위 테이블은 어떤 인덱스가 설정되어 있는지 다음과 같은 쿼리로 확인해봅니다.

 

SHOW INDEX FROM member; -- 인덱스정보 확인

 

출력 결과

member 테이블에는 mem_id 열에 클러스터형 인덱스 1개만 설정되어있습니다.

 

이번에는 인덱스 크기를 다음과 같은 쿼리로 확인해봅니다.

 

SHOW TABLE STATUS LIKE 'member'; -- 인덱스크기 확인

 

출력 결과

Data_length는 클러스터형 인덱스(또는 데이터)의 크기를 Byte 단위로 표기한 것입니다. 16KB가 할당되어 있는 것을 확인할 수 있는데 MySQL의 1페이지 크기는 16KB이기 때문입니다. 

 

그치만 Index_length는 보조 인덱스의 크기인데 member 테이블은 보조 인덱스가 없기 때문에 0으로 표기되었습니다.

 

보조 인덱스는 단순 보조 인덱스와 고유 보조 인덱스로 나뉩니다. 

이 둘의 차이점은 중복을 허용하냐의 차이인데 중복을 허용하는 것은 단순 보조 인덱스이고, 그렇지 않은 것은 고유 보조 인덱스 입니다.

 

 

그렇다면 다음과 같은 쿼리로 주소(addr)에 중복을 허용하는 단순 보조 인덱스와 인원수(mem_number)에 중복을 허용하지 않는 고유 보조 인덱스를 생성해보겠습니다.

 

CREATE INDEX idx_member_addr ON member(addr); -- 단순 보조 인덱스 생성(중복 허용)
CREATE UNIQUE INDEX idx_member_mem_name ON member(mem_name); -- 고유 인덱스 생성(중복 비허용)

 

보조 인덱스 출력 결과

주황색은 고유 인덱스를 생성한 결과이고, 빨간색은 단순 보조 인덱스를 생성한 결과입니다. 고유 인덱스를 생성하면 이후로는 중복된 값이 입력되지 않습니다. 예를 들어 마마무와 이름이 같은 다른 그룹의 데이터를 넣을려고 해도 mem_name이 고유 인덱스로 지정되어 있기 때문에 생성되지 않을 것입니다.

 

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

 

ANALYZE TABLE member; -- 보조인덱스 실제 적용

 

ANALYZE TABLE 문 출력 결과

Index_length(보조 인덱스 크기)는 16KB입니다. 실제로는 이것보다 훨씬 작지만, 보조 인덱스가 1건이면 최소 1페이지가 필요하기 때문에 16KB로 표시되어 있는 것을 확인할 수 있습니다.

 

 

 

인덱스 제거

 

이제 생성을 했다면 제거까지 알아야겠죠.

제거는 간단합니다. DROP INDEX 문을 쓰면 되기 때문입니다.

 

보조 인덱스를 제거하려면 다음과 같은 쿼리를 사용하면 됩니다.

 

DROP INDEX idex_member_mem_name ON member;
DROP INDEX idex_member_addr ON member;

 

이때, 클러스터형 인덱스와 보조 인덱스가 섞여 있을 때는 보조 인덱스를 먼저 제거하는 것이 좋습니다. 왜냐하면 클러스터형 인덱스를 먼저 제거할 경우 데이터를 쓸데없이 재구성해서 시간이 더 오래 걸리기 때문입니다.

 

만약 PK로 설정된 인덱스를 제거하고 싶다면 DROP INDEX 문으로 제거되지 않습니다. 이때는 ALTER TABLE 문으로만 제거할 수 있습니다. 

 

다음과 같은 쿼리를 사용하면 됩니다.

 

ALTER TABLE member DROP PRIMARY KEY;

 

하지만 기본 키가 외래 키를 참조하고 있는 경우는 다음과 같은 쿼리로 먼저 관련된 외래 키를 제거한 다음 기본 키를 제거해야 합니다.

 

-- 외래 키 이름 확인
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;