MySQL 5: Index

인덱스는 튜닝에 즉각적인 효과를 내는 가장 빠른 방법 중 하나로, 인덱스를 생성하고 인덱스를 사용하는 SQL 을 만들어서 사용한다면 기존보다 아주 빠른 응답 속도를 얻을 수 있다. 인덱스를 만들어서 발생되는 장점을 아래와 같다.

 

1. 검색 속도가 빨라질 수 있다 (항상 그런 것은 아니다)
2. 결과적으로 쿼리의 부하가 줄어들어 시스템 전체의 성능이 향상된다.

 

하지만 단점도 존재한다.

 

1. 인덱스가 데이터베이스의 공간을 차지하여 추가적인 공간이 필요해지는데, 대략 데이터베이스 크기의 10% 정도의 추가공간이 필요하다.
2. 처음 인덱스를 생성하는데 시간이 많이 소요될 수 있다.
3. 데이터의 변경작업이 자주 일어나는 경우에는 오히려 성능이 나빠질 수 있다.

 

 

인덱스의 종류는 크게 두 가지로 나뉜다. 클러스터형 인덱스와 보조인덱스이다. (MySQL 이 아닌 다른 DBMS 에서는 클러스터형과 비클러스터형으로 나누기도 한다) 보조인덱스는 실제 책의 인덱스와 같이, 찾고 싶은 단어를 인덱스에서 찾은 후 그 옆에 표시된 페이지로 가야 실제 찾는 내용이 있는 것을 말한다. 반면 클러스터형 인덱스는 영어 사전처럼 책의 내용 자체가 순서대로 정렬되어 있어서 인덱스 자체가 책의 내용과 같은 것을 말한다. 클러스터형 인덱스는 테이블 당 한 개만 생성할 수 있고, 보조 인덱스는 여러 개를 생성할 수 있다. (그 외에도 인덱스는 Unique / Non-Unique 인덱스로 나뉘어질 수 있긴 하다)

 

 

인덱스는 테이블의 컬럼 단위에 생성된다. 하나의 열에 대해 인덱스를 생성할 수도, 여러 개의 열에 대해 생성할 수도 있다. 우선 테이블을 생성할 때 특정 컬럼을 PRIMARY KEY (또는 Unique NOT NULL) 로 지정하게 되면 그 컬럼에 클러스터형 인덱스가 생성된다. PK 는 테이블 당 하나만 생성할 수 있기에 기본 키가 지정된 열에 클러스터형 인덱스가 생성되는 것이다. 아래와 같은 구문으로 인덱스를 확인해볼 수 있다.

 

 

SHOW INDEX FROM userTbl;

 

 

조금 더 자세히 알아보자면, 테이블을 생성할 때 PK 혹은 UNIQUE NOT NULL 로 지정한 열은 클러스터형 인덱스가 생성되고 UNIQUE 또는 UNIQUE NULL 로 지정한 열에는 보조인덱스가 생성된다. PK 와 UNIQUE NOT NULL 이 있으면 PK 로 지정한 열에 우선적으로 클러스터형 인덱스가 생성된다. 그리고, PK 로 지정한 열로 데이터가 오름차순 정렬된다.

 

 

 

인덱스의 동작 방식을 알아보기 위해서는 B-Tree 자료구조에 대해 이해할 필요가 있다.

 

 

B-Tree

 

 

Node 란 트리 구조에서 데이터가 존재하는 공간을 뜻한다. 그 중에서도 Root 노드는 최상위노드를 뜻하며, 모든 출발은 이 루트노드에서 시작된다. 그리고 Leaf Node 는 제일 마지막에 존재하는 노드를 말한다. MySQL 에서 B-Tree 를 이용할 때 이 노드에 해당되는 것은 페이지이다. 페이지란 16KByte 크기의 최소한의 저장단위이다. 아무리 작은 데이터를 저장하게 되더라도 한 개의 페이지를 차지한다는 뜻이다. 이 B-Tree 는 데이터 검색 (SELECT) 에서 뛰어난 성능을 보인다. B-Tree 를 이용하여 검색을 진행한다면 우선 루트 페이지 (루트노드) 를 검색하게 된다. 모든 데이터는 정렬되어 있으므로 루트 페이지에서 해당되는 자식 노드로 쉽게 이동하여 검색을 진행하는 것이다.

 

 

하지만 이러한 B-Tree 는 데이터의 변경 작업 시에는 성능이 나빠지는 단점이 생긴다. 특히 INSERT 작업이 있을 때 급격히 느려질 수 있는데, 그 이유는 페이지분할이라는 작업이 발생되기 때문이다. B-Tree 에 데이터를 저장할 때, 리프페이지에 저장할 수 있는 빈 공간이 없다면 페이지 분할 작업이 일어나는데, 이 때 MySQL 은 비어있는 페이지를 한 개 확보한 후에, 기존 데이터를 삽입하려던 리프 페이지의 데이터를 공평하게 나눈다. 이러한 방식으로 계속 페이지를 분할/생성하며 데이터를 저장하는 것이다.

 

 

B-Tree

 

 

 

그렇다면 클러스터형 인덱스와 보조인덱스는 어떻게 다른 것일까? 이를 확인해보기 위해 우선 인덱스가 없이 테이블을 생성하고 데이터를 집어넣어본다.

 

 

DROP TABLE IF EXISTS clusterTbl;
CREATE TABLE clusterTbl 
(userId CHAR(8), name VARCHAR(10));
INSERT INTO clusterTbl VALUES('LSG', '이승기');
INSERT INTO clusterTbl VALUES('KBS', '김범수');
INSERT INTO clusterTbl VALUES('KKH', '김경호');
INSERT INTO clusterTbl VALUES('JYP', '조용필');
INSERT INTO clusterTbl VALUES('SSK', '성시경');
INSERT INTO clusterTbl VALUES('LJb', '임재범');
INSERT INTO clusterTbl VALUES('YJS', '윤종신');
INSERT INTO clusterTbl VALUES('EJW', '은지원');
INSERT INTO clusterTbl VALUES('JKW', '조관우');
INSERT INTO clusterTbl VALUES('BBK', '바비킴');

SELECT * FROM clusterTbl ;

 

 

result

 

데이터를 확인해보면 그냥 넣은 순서 그대로 데이터가 들어있는 것을 확인할 수 있다. 

 

 

 

위 테이블 userId 컬럼에 클러스터형 인덱스를 구성하고 순서를 다시 한 번 확인해본다.

 

 

ALTER TABLE clusterTbl ADD CONSTRAINT PK_clusterTbl_userId PRIMARY KEY(userId);
SELECT * FROM clusterTbl ;

 

 

result

 

 

userId 로 오름차순 정렬된 것을 확인할 수 있다. PK 로 지정했으니 클러스터형 인덱스가 생성된 것이다. 

 

 

 

그 다음으로는 UNIQUE 로 제약조건을 넣어주어 보조인덱스를 생성하고 순서를 확인해본다.

 

 

DROP TABLE IF EXISTS clusterTbl2;
CREATE TABLE clusterTbl2 
(userId CHAR(8), name VARCHAR(10));
INSERT INTO clusterTbl2 VALUES('LSG', '이승기');
INSERT INTO clusterTbl2 VALUES('KBS', '김범수');
INSERT INTO clusterTbl2 VALUES('KKH', '김경호');
INSERT INTO clusterTbl2 VALUES('JYP', '조용필');
INSERT INTO clusterTbl2 VALUES('SSK', '성시경');
INSERT INTO clusterTbl2 VALUES('LJb', '임재범');
INSERT INTO clusterTbl2 VALUES('YJS', '윤종신');
INSERT INTO clusterTbl2 VALUES('EJW', '은지원');
INSERT INTO clusterTbl2 VALUES('JKW', '조관우');
INSERT INTO clusterTbl2 VALUES('BBK', '바비킴');

ALTER TABLE clusterTbl2 ADD CONSTRAINT UK_clusterTbl2_userId UNIQUE(userId);
SELECT * FROM clusterTbl2 ;

 

 

result

 

 

입력한 것과 순서의 변화가 없다. 보조인덱스는 데이터 페이지를 건드리지 않고, 별도의 저장소에 인덱스 페이지를 생성하기 때문이다. 이는 인덱스 페이지의 리프 페이지에 인덱스로 구성한 열을 정렬하고, 데이터 위치 포인터를 생성한다. 데이터 위치 포인트는 클러스터형 인덱스와 달리 주소값 (페이지번호+#오프셋) 이 기록되어 바로 데이터의 위치를 가리키게 된다.

 

 

 

정리해보자면, 클러스터형 인덱스의 생성 시에는 데이터 페이지 전체가 재정렬된다. 클러스터형 인덱스는 인덱스 자체의 리프 페이지가 곧 데이터이다. 인덱스 자체에 데이터가 포함되어 있는 것이다. 이러한 클러스터형 인덱스는 보조인덱스보다 검색속도는 더 빠르지만, 데이터의 입력/수정/삭제는 더 느리다. 또 클러스터 인덱스는 성능이 좋지만 테이블에 한 개만 생성할 수 있고, 어느 컬럼을 기준으로 생성하느냐에 따라 시스템의 성능이 달라질 수 있다.

 

 

반면 보조인덱스는 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성하는 것이다. 보조인덱스는 데이터가 위치하는 주소값을 저장한 것으로, 클러스터형보다 검색속도는 느리지만 데이터의 입력/수정/삭제는 덜 느리다.

 

 

 

실무적으로 인덱스를 사용할 때는 위 클러스터형 인덱스, 보조인덱스를 혼합하여 사용하는 경우가 가장 많다고 한다. 클러스터형 인덱스와 보조인덱스를 같이 사용할 때는 보조인덱스는 데이터 페이지의 주소값을 저장하는 것이 아니라, 클러스터형 인덱스의 키 값을 저장하게 된다. 또, 보조인덱스를 검색한 후에는 모두 다시 클러스터형 인덱스의 루트 페이지부터 검색을 시작한다. 만약 클러스터형 인덱스의 키 값이 아니라 위치정보를 저장한다면 검색 속도는 더 빠를 것이다. 하지만, 새로운 데이터가 저장될 때 단 한 건의 행 삽입만으로 데이터 페이지의 페이지 번호 및 오프셋이 대폭 변경될 수 있는 문제가 발생하기 때문에, 시스템의 부하를 줄이기 위해 이와 같은 방식을 채택하는 것이다. (한 가지 짚고 넘어갈 점으로, 인덱스를 검색하기 위한 일차 조건은 WHERE 절에 해당 인덱스를 생성한 열의 이름이 나와야한다는 것이다. WHERE 절에 해당 인덱스를 생성한 열 이름이 나와도 물론 사용하지 않는 경우도 많다)

 

 

 

그 다음으로는, MySQL 내부에서 자동으로 생성해주는 인덱스가 아니라 직접 만들어 인덱스를 사용해보기로 하였다. 기존에 사용하던 userTbl 테이블의 height 컬럼에 index 를 아래와 같은 구문으로 생성할 수 있다.

 

 

CREATE INDEX idx_usertbl_height ON userTbl(height);

 

 

생성한 인덱스를 실제로 적용시키기 위해서는 ANALYZE TABLE 문으로 테이블을 분석/처리해줘야 한다. 그리고 인덱스를 삭제하고자 할 때에는 DROP INDEX indexName ON tableName; 의 형식으로 실행시켜주면 된다. (삭제 시에는 보조 인덱스를 먼저 삭제하는 편이 좋다고 한다)

 

 

 

그렇다면 이러한 인덱스는 언제 사용하고, 언제 사용하지 않아야 하는 것일까? 인덱스를 만드는 절대 기준은 따로 없으며, 테이블의 데이터 구성이 어떻게 되었는지, 어떠한 조회를 많이 사용하는지에 따라 인덱스를 생성할지 말지를 결정해야 한다. 아래 사항들을 기억해두자!

 

 

1. 인덱스는 열 단위에 생성된다
2. WHERE 절에 사용되는 열에 인덱스를 만들어야 한다
3. WHERE 절에 사용되더라도 자주 사용해야 가치가 있다
4. 데이터 중복도가 높은 열은 인덱스를 만들어도 별 효과가 없다 (이러한 경우에는 오히려 인덱스 관리 비용 때문에 인덱스가 없는 편이 나은 경우도 있다)
5. 외래 키를 지정한 열에는 자동으로 외래 키 인덱스가 생성된다 (쿼리문에서 외래 키 인덱스가 필요할 경우 MySQL 내부적으로 알아서 외래 키 인덱스를 사용한다)
6. JOIN 에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다
7. INSERT/UPDATE/DELETE 가 얼마나 자주 일어나는지 고려해야한다
8. 클러스터형 인덱스는 테이블 당 하나만 생성할 수 있다
9. 클러스터형 인덱스가 테이블에 아예 없는 것이 좋은 경우도 있다 (다음과 같은 사례를 서적에서는 설명한다. 어느 쇼핑몰 사이트에서 회원 테이블의 회원 ID 열을 PK 로 지정해서 자동으로 클러스터형 인덱스가 생성되었다. 쇼핑몰을 오픈한 초기에는 회원가입한 사람이 많지 않아서 문제가 되지 않았지만, 이벤트 할인 행사로 인해 갑자기 동시에 많은 사용자가 회원가입을 하는 상황이 발생한다. 문제는 클러스터형 인덱스의 키 때문에 MySQL 시스템의 성능에 심각한 문제가 발생된다는 점이다)
10. 사용하지 않는 인덱스는 제거한다

 

 

인덱스는 MySQL 의 성능에 아주 큰 영향을 미치게 되므로, 잘 작성하고 활용해야한다. 잘 활용되지 않는 인덱스는 제거하고 주기적인 OPTIMIZE TABLE, ANALYZE TABLE 구문으로 인덱스 재구성을 통해 조각화를 최소화해야 한다.

 

 

 

참고) OPTIMIZE TABLE, ANALYZE TABLE 은 MySQL 데이터베이스에서 테이블을 최적화하고, 테이블의 통계정보를 갱신하는데 사용된다.

 

OPTIMIZE TABLE: 테이블의 물리적인 구조를 최적화 / 삭제된 데이터를 정리하고 빈 공간을 회수하여 테이블의 크기를 줄임 / 테이블에 대한 인덱스를 재구성하여 검색 성능을 향상시킴

 

ANALYZE TABLE: 테이블 통계 정보를 갱신 / 테이블의 통계 정보를 분석하고 갱신하여 최적의 실행계획을 수립하는데 도움을 줌 / 테이블의 통계 정보가 최신 상태가 아니거나, 데이터의 분포가 변경되었을 때 이를 실행하여 성능을 향상시킬 수 있다.

 

 

 

 

 

 

 

Reference:

이것이 MySQL 이다