[혼공S] CH6-(3). 인덱스의 실제 사용

지금까지는 인덱스의 개념을 파악했다면, 실제로 인덱스를 생성하는 SQL을 익히도록 하자. 

 

인덱스의 생성과 제거 문법

인덱스 생성 문법

앞에서 PRIMARY KEY를 지정하면 클러스터형 인덱스가, UNIQUE를 지정하면 보조 인덱스가 생성되는 것을 확인하였다. 이외에 직접 인덱스를 생성하려면 CREATE INDEX 문을 사용해야 한다. 

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

UNIQUE를 쓰면 중복이 안 되는 고유 인덱스를 만드는 것이고, 이를 생략하면 중복이 허용된다. ASC/DESC는 오름차순, 내림차순을 의미한다.

 

인덱스 제거 문법

-- 인덱스 제거
DROP INDEX 인덱스_이름 ON 테이블_이름 ;

여기서 기본 키와 고유 키로 자동 생성된 INDEX는 DROP INDEX로 제거하지 못하고 ALTER TABLE 문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스를 제거할 수 있다는 것을 주의하자. 

※ 하나의 테이블에 클러스터형 인덱스와 보조 인덱스가 모두 있는 경우에는 인덱스를 제거할 때는 보조 인덱스부터 제거하는 것이 더 좋다. 

 

인덱스 생성과 제거 실습

인덱스 생성 실습

지금까지 계속 사용해왔던 member 테이블을 이용해본다. mem_id가 PRIMARY KEY로 지정되어 있으므로 자동으로 클러스터형 인덱스가 생성된 것을 볼 수 있다.

-- member 테이블 확인
USE market_db ; 
SELECT * FROM member ;
DESC member ; 

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

인덱스의 크기도 SHOW TABLE STATUS 문으로 확인 가능하다. 

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

Data_length의 16384는 인덱스(또는 데이터)의 크기를 byte 단위로 표기한 것으로, 기본적으로 16KB인 1페이지가 할당되어 있는 것이다. 인덱스 크기의 최소 단위는 16KB이다. 또한 Index_length는 보조 인덱스의 크기인데 여기서는 보조인덱스가 없기 때문에 나타나지 않는다.  단순 보조 인덱스를 생성해보자.

-- 단순 보조 인덱스 생성
CREATE INDEX idx_member_addr
   ON member (addr) ;
   
SHOW INDEX FROM member ;

member라는 테이블의 addr에서 idx_member_addr이라는 이름으로 중복을 허용하는 보조 인덱스를 생성하였다. Non_unique 에서 1을 나타내는 부분은 중복이 허용된다는 것이다. 

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

인덱스의 크기를 다시 확인해보는데, 보조 인덱스의 크기를 나타내는 Index_length의 부분이 0인 것을 확인할 수 있다. 생성한 인덱스를 실제로 적용시키려면 ANALYZE TABLE문으로 테이블을 분석 및 처리를해줘야 한다.

-- 테이블의 분석 및 처리
ANALYZE TABLE member ; 
SHOW TABLE STATUS LIKE 'member' ;

그럼 Index_length의 부분이 16KB가 된 것을 볼 수 있다. 즉, 인덱스를 생성한 후에는 ANALYZE TABLE 문을 실행해줘야 한다는 것. 

이번에는 mem_number에 중복을 허용하지 않는 고유 보조 인덱스를 생성해보도록 하자. 기존의 mem_number에서는 4라는 중복된 값이 있기 때문에 만들어지지 않는다.

-- mem_number 고유 보조 인덱스 생성
CREATE UNIQUE INDEX idx_member_mem_number 
   ON member (mem_number) ; -- 에러 발생, 중복값 존재 때문.

중복값이 없는 mem_name으로 고유 보조 인덱스를 생성해보도록 하자. 중복값을 허용하지 않는 (Non_unique 값이 0인) 보조 인덱스가 만들어진 것을 확인할 수 있다. (하지만 일반적으로 이름에 고유 보조 인덱스를 설정하지 않는다.)

-- mem_name으로 고유 보조 인덱스 생성
CREATE UNIQUE INDEX idx_member_mem_name 
   ON member (mem_name) ;  
SHOW INDEX FROM member ;
INSERT INTO member VALUES('MOO', '마마무', 2, '태국', '001', '12341234', 155, '2020.10.10') ;

이름이 중복되는 데이터를 입력하려 하면 에러가 나고 입력되지 않는다.

 

인덱스의 활용 실습

생성한 인덱스를 활용해보도록 하자. 먼저 지금까지 만든 인덱스가 어느 열에 있는지 확인해보면, mem_id, mem_name, addr에 인덱스가 생성된 것을 알 수 있다.

-- 인덱스 확인
ANALYZE TABLE member ; 
SHOW INDEX FROM member ;

인데스를 사용하지 않고 전체 테이블을 조회해보도록 하자. 인덱스를 사용했는지의 여부는 execution plan을 통해 확인 가능하다. full table scan을 통해서 쿼리문의 결과를 출력한 것을 알 수 있다. 즉, 책으로 빗대어 말하자면 책의 첫 페이지부터 마지막 페이지까지 다 넘겨본 것이다. 

-- 인덱스의 활용
SELECT * FROM member ;

이번에는 인덱스가 있는 열을 조회해보도록 하자. 이번에도 full table scan을 통해서 출력하였고, 인덱스를 사용하지 않았다.

SELECT mem_id, mem_name, addr FROM member ;

인덱스가 생성된 mem_name에서 값이 '에이핑크'인 데이터를 조회해본다고 하자. single row에서 데이터를 얻었다는 것을 볼 수 있고, 이는 index인 idx_member_mem_name을 이용하여 데이터를 찾았다는 것이다.  

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

이번에는 숫자의 범위로 데이터를 조회해보도록 하자. 먼저 숫자로 구성된 mem_number로 단순 보조 인덱스를 만들어본다.

CREATE INDEX idx_member_mem_number 
   ON member (mem_number) ; 
ANALYZE TABLE member ;
SELECT mem_name, mem_number, addr FROM member 
	WHERE mem_number >= 7 ;

idx_member_mem_number 로 지정된 열만 스캔해서 데이터를 출력한 것을 확인할 수 있다. 

 

인덱스를 사용하지 않을 때

인덱스가 있고 WHERE 절에 열 이름이 나와도 인덱스를 사용하지 않는 경우도 있다. 예를 들어 인원수가 1 이상인 회원을 조회해보도록 하자. 인덱스가 있더라도 MySQL이 인덱스를 사용하지 않고 전체 테이블을 검색하는 게 효율적이라고 생각하여 full table scan을 사용한 것이다. 찾을 건수가 엄청 많을 때, 책의 맨 뒤의 찾아보기와 본문을 계속 번갈아가면서 찾아보는 것보다 처음부터 읽어가며 찾는 게 더 효율적인 경우라고 생각하면 된다.

-- 인덱스를 사용하지 않는 경우
SELECT mem_name, mem_number, addr FROM member
   WHERE mem_number >= 1 ;

또한 WHERE 절에 어떤 연산이 들어가면 인덱스를 사용하지 않는다. 그 예시는 아래와 같다.

SELECT mem_name, mem_number, addr FROM member
	WHERE mem_number * 2 >= 14 ;

연산을 풀면 앞서 찾아보았던 인원 수가 7보다 큰 경우랑 같게 되는데, 인덱스를 사용하지 않는다. 즉, WHERE 절에 나온 열에는 아무런 연산을 하지 않는 게 좋다. 

 

인덱스 제거 실습

지금까지 사용했던 인덱스를 제거해보도록 하자.

-- 현재 사용중인 인덱스 확인
SHOW INDEX FROM member ;

네 개의 열에 인덱스를 사용하는 것을 확인한 후에 보조인덱스 먼저 제거해보도록 하자. 

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

마지막으로 PRIMARY KEY에 설정된 INDEX는 DROP INDEX 문으로 제거되지 않고 ALTER TABLE을 통해서 PRIMARY KEY를 없애줌으로써 제거할 수 있다.

-- 기본 키 제거
ALTER TABLE member
   DROP PRIMARY KEY ; -- 에러 발생

여기서 기본키를 제거할 수 없다는 오류 메세지가 뜨는데, 이는 외래 키와 연결되어 있기 때문이다. 테이블에는 여러 개의 외래 키가 있을 수 있다. 그래서 먼저 외래 키의 이름을 알아내야 한다.

※ information_schema와 referential_constriants는 MySQL 안에 원래 포함되어있는 시스템 데이터베이스와 테이블이다. 여기에는 MySQL 전체의 외래 키 정보가 들어있다. 

-- 외래 키 이름 알아내기
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 ; 
SHOW INDEX FROM member ;

Reference 

혼자 공부하는 SQL, 우재남 지음