[혼공S] CH6-(1). 인덱스 개념 파악

인덱스는 SELECT를 사용해서 테이블을 조회할 때 결과를 빠르게 추출하도록 도와주는 것으로, 실무에서는 현실적으로 인덱스 없이 데이터베이스 운영이 불가능하다. 클러스터형 인덱스(cluetered index)와 보조 인덱스(secondary index)가 있다. 

 

인덱스의 개념

인덱스는 데이터를 빠르게 찾을 수 있도록 해주는 도구이다. 

 

인덱스의 문제점

필요 없는 인덱스를 만든 것은 데이터베이스가 차지하는 공간만 늘어나고, 인덱스를 이용하여 데이터를 찾는 것이 전체 테이블을 찾아보는 것보다 느려질 수도 있다. 

 

인덱스의 장점과 단점

장점)

• 적절한 인덱스를 생성하고 인덱스를 사용하는 쿼리문을 만들면 기존보다 아주 빠른 응답 속도를 얻을 수 있다.
• 그 결과 컴퓨터의 부담이 줄어서 전체 시스템의 성능이 향상된다.

단점)

• 인덱스도 결국 공간을 차지하는 것이기 때문에 데이터베이스 안에 추가적인 공간이 필요하게 된다. 
• 처음에 인덱스를 만드는 것이 시간이 오래 걸릴 수 있다. 
•  

인덱스의 종류

자동으로 생성되는 인덱스 

인덱스는 테이블의 열 단위에 생성되며, 하나의 열에는 하나의 인덱스를 생성할 수 있다. 계속 다뤄왔던 member 테이블을 예로 들었을 때, member 테이블의 열은 8개이므로 8개의 서로 다른 인덱스를 생성할 수 있다. 또한 mem_id를 PRIMARY KEY로 정의했었는데, 이렇게 기본 키로 지정하게 되면 mem_id 열에 클러스터형 인덱스가 생성된다. 즉, 클러스터형 인덱스는 테이블 당 하나만 가능하다. 

USE market_db ; 
CREATE TABLE table1 (
   col1 INT PRIMARY KEY, 
   col2 INT, 
   col3 INT
)  ; 
SELECT * FROM table1 ; 
-- 인덱스 정보 확인
SHOW INDEX FROM table1 ;

위에 결과에서, table1의 PRIMARY KEY로 지정되어 있는 col1의 인덱스 정보를 볼 수 있다. 이것은 기본 키로 설정해서 자동으로 설정된 인덱스라는 뜻으로, 클러스터형 인덱스이다. 또한 Non-unique에서 0은 false를, 1을 true를 의미한다. 

PRIMARY KEY와 마찬가지로, UNIQUE 지정 열 또한 인덱스가 자동으로 생성된다.

-- UNIQUE 열 인덱스 확인
CREATE TABLE table2 (
   col1 INT PRIMARY KEY, 
   col2 INT UNIQUE, 
   col3 INT UNIQUE
) ; 
SHOW INDEX FROM table2 ;

 

자동으로 정렬되는 클러스터형 인덱스

클러스터형 인덱스는 기본 키로 지정하면 자동으로 생성되는 것과 테이블 당 하나만 생성되는 것을 확인했다. 클러스터형 인덱스가 생성되면 그 열을 기준으로 자동으로 정렬된다. 먼저 기본 키 지정 하지 않은 경우의 테이블을 출력해보자.

USE market_db ; 
DROP TABLE IF EXISTS buy, member ; 
-- 기본 키 지정 안 함 
CREATE TABLE member (
   mem_id CHAR(8),
   mem_name VARCHAR(10),
   mem_number INT, 
   addr CHAR(2)
) ; 
-- 데이터 입력
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울') ; 
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남') ; 
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기') ; 
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울') ; 
SELECT * FROM member ;

이번에는 mem_id 열을 PRIMARY KEY로 지정한 후에 테이블을 출력해보자.

-- 기본 키 지정
ALTER TABLE member
   ADD CONSTRAINT
   PRIMARY KEY (mem_id) ; 
SELECT * FROM member ;

이번에는 mem_id의 PRIMARY KEY를 제거하고, mem_name을 PRIMARY KEY로 지정한 후 테이블을 출력해보자.

-- 기본 키 변경
ALTER TABLE member DROP PRIMARY KEY ; 
ALTER TABLE member
   ADD CONSTRAINT 
   PRIMARY KEY (mem_name) ;
SELECT * FROM member ;

-- 새로운 데이터를 넣어도 순서대로 정렬됨
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울') ;  
SELECT * FROM member ;

 

정렬되지 않은 보조 인덱스

고유 키(UNIQUE)로 지정하면 보조 인덱스가 만들어진다. 이는 여러 개를 만들 수가 있다. 고유 키로 설정하면 데이터의 순서에는 변화가 없다. 즉, 보조 인덱스를 설정하면 데이터의 순서는 변경되지 않고 별도로 인덱스를 만드는 것이다. 

-- 고유 키 지정
DROP TABLE IF EXISTS member ; 
CREATE TABLE member (
    mem_id  CHAR(8), 
    mem_name VARCHAR(10), 
    mem_number INT, 
    addr CHAR(2)
) ; 
-- 데이터 입력
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울') ; 
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남') ; 
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기') ; 
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울') ; 
SELECT * FROM member ;
-- mem_id 고유 키 설정
ALTER TABLE member 
   ADD CONSTRAINT 
   UNIQUE (mem_id) ; 
SELECT * FROM member ; 
SHOW INDEX FROM member ;

추가로 mem_name에도 UNIQUE 키를 지정해보자. 

-- mem_name에 고유 키 설정
ALTER TABLE member 
   ADD CONSTRAINT 
   UNIQUE (mem_name) ; 
SHOW INDEX FROM member ;

 

Reference

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