인덱스
- 인덱스(Index)는 데이터베이스의 테이블에 대한 검색 속도를 향상시켜주는 자료구조이다.
- 테이블의 특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터를 정렬한 후 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다.
- 인덱스는 책에서의 목차 혹인 색인이라고 생각하면 된다.
- 책에서 원하는 내용을 찾을 때 목차나 색인을 이용하면 훨씬 빠르게 찾을 수 있는데, 마찬가지로 테이블에서 원하는 데이터를 찾기 위해 인덱스를 이용하면 빠르게 찾을 수 있다.
- 그러므로 '데이터=책의 내용', '인덱스=책의 목차', '물리적 주소=책의 페이지 번호'라고 생각할 수 있다.
장점
- 인덱스의 장점으로는 앞에서 말했듯이 테이블을 검색하는 속도와 성능이 향상된다.
- 이를 통해, 시스템의 전반적인 부하를 줄일 수 있다.
- 핵심은 인덱스에 의해 데이터들이 정렬된 형태를 갖는다는 것이다.
- 기존엔 WHERE 문으로 특정 조건의 데이터를 찾기 위해서 테이블의 전체를 조건과 비교해야 하는 '풀 테이블 스캔(Full Table Scan)' 작업이 필요했는데, 인덱스를 이용하면 데이터들이 정렬되어 있기 때문에 조건에 맞는 데이터를 빠르게 찾을 수 있다.
- 또한 ORDER BY 문이나 MIN/MAX 같은 경우도 이미 정렬되어 있기 때문에 빠르게 수행할 수 있다.
단점
- 인덱스를 항상 정렬된 상태로 유지해야 하기 때문에 인덱스가 적용된 컬럼에 INSERT, DELETE, UPDATE 작업을 수행하면 다음과 같은 추가 작업이 필요하다.
- 이처럼 인덱스의 수정도 추가적으로 필요하기 때문에 데이터의 수정이 잦은 경우 성능이 낮아진다.
- 또한, 데이터의 인덱스를 제거하는 것이 아니라, '사용하지 않음'으로 처리하고 남겨두기 때문에 수정 작업이 많은 경우 실제 데이터에 비해 인덱스가 과도하게 커지는 문제점이 발생할 수 있다.
- 별도의 메모리 공간에 저장되기 때문에 추가 저장 공간이 많이 필요하게 된다.
- 인덱스는 전체 데이터의 10~20% 이상의 데이터를 처리하거나, 데이터의 형식에 따라 오히려 성능이 낮아질 수 있다.
- 예를 들어, 나이나 성별과 같이 값의 range가 적은 컬럼인 경우, 인덱스를 읽고 나서 다시 많은 데이터를 조회해야 하기 때문에 비효율적이다.
1. 인덱스의 종류
(1) 클러스터형 인덱스(Clustered Index)
- PK로 지정한 열에는 자동으로 클러스터형 인덱스가 생성된다.
DROP DATABASE IF EXISTS market_db;
CREATE DATABASE market_db;
USE market_db;
CREATE TABLE member -- 회원 테이블
( mem_id CHAR(8) NOT NULL PRIMARY KEY, -- 회원 아이디(PK)
mem_name VARCHAR(10) NOT NULL, -- 이름
mem_number TINYINT NOT NULL, -- 인원수
addr CHAR(2) NOT NULL, -- 주소(경기,서울,경남 식으로 2글자만입력)
phone1 CHAR(3), -- 연락처의 국번(02, 031, 055 등)
phone2 CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
height TINYINT UNSIGNED, -- 평균 키
debut_date DATE -- 데뷔 일자
);
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19') ;
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016.08.08') ;
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015.01.15') ;
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, '2015.04.21');
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, '2007.08.02') ;
INSERT INTO member VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, '2019.02.12') ;
INSERT INTO member VALUES('RED', '레드벨벳', 4, '경북', '054', '55555555', 161, '2014.08.01') ;
INSERT INTO member VALUES('APN', '에이핑크', 6, '경기', '031', '77777777', 164, '2011.02.10') ;
INSERT INTO member VALUES('SPC', '우주소녀', 13, '서울', '02', '88888888', 162, '2016.02.25') ;
INSERT INTO member VALUES('MMU', '마마무', 4, '전남', '061', '99999999', 165, '2014.06.19') ;
- 아래의 결과에서, member 테이블의 mem_id는 PK이기에 A-Z 순으로 정렬되는 것을 볼 수 있다.
- 인덱스를 생성할 때는 데이터 페이지 전체를 다시 정렬한다.
- 이미 대용량의 데이터가 입력된 상태라면, 업무시간에 클러스터형 인덱스를 생성하는 것은 심각한 시스템 부하를 줄 수 있으므로 신중해야 한다.
- 클러스터형 인덱스는 인덱스 자체의 리프 페이지가 곧 데이터 페이지다.
- 즉, 인덱스 자체에 데이터가 포함되어 있다고 말할 수 있다.
- 비 클러스터형 인덱스보다 검색속도는 더 빠르다.
- 하지만, 데이터의 입력, 수정, 삭제는 느리다.
- 성능은 좋지만, 테이블에 한 개만 생성할 수 있다. 그래서 어느 열에 클러스터형 인덱스를 생성하느냐에 따라서 시스템의 성능이 달라질 수 있다.
(2) 보조 인덱스/비 클러스터형 인덱스(Non Clusterd Index)
- 보조 인덱스를 생성할 때, 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성한다.
- 보조 인덱스에서 인덱스 자체의 리프 페이지는 데이터가 아니라, 데이터에 위치하는 포인터이다.
- 클러스터형보다 검색속도는 느리지만, 데이터의 입력, 수정, 삭제는 더 빠르다.
- 보조 인덱스는 여러 개 생성할 수 있지만, 함부로 남용할 경우에는 오히려 시스템 성능을 떨어뜨리는 결과를 가져온다.
2. 클러스터형/보조 인덱스 생성
클러스터형 인덱스
CREATE TABLE table1 (
col1 INT PRIMARY KEY,
col2 INT,
col3 INT
) ;
SHOW INDEX FROM table1 ;
- col1을 PK로 지정하여 클러스터형 인덱스를 생성할 수 있다.
보조형 인덱스
CREATE TABLE table2 (
col1 INT PRIMARY KEY,
col2 INT UNIQUE,
col3 INT UNIQUE
) ;
SHOW INDEX FROM table2 ;
- col2, col3는 UNIQUE로 지정하여 보조 인덱스를 생성할 수 있다.
3. 인덱스의 내부 작동
균형 트리의 개념
- 클러스터형 인덱스와 보조 인덱스 모두 내부적으로 균형 트리로 만들어진다.
- 균형 트리는 자료 구조에 나오는 범용적으로 사용되는 데이터 구조이다.
- 위 그림에 표시된 각각의 사각형 데이터를 노드라고 한다.
- 최상단의 노드를 루트 노드(Root Node), 중간 노드들을 브랜치 노드(Branch Node), 최하단의 노드들을 리프 노드(Leaf Node)라고 한다.
- 노드는 MySQL에서는 페이지(Page)라고 부른다.
- 위 그림에서 MMM 데이터를 검색한다고 하자.
- 모두 리프 페이지만 있으므로 MMM을 찾는 방법은 처음부터 검색하는 방법밖에 없다.
- AAA부터 MMM까지 8건의 데이터, 페이지 3개를 검색해야 결과를 알 수 있다.
- 균형 트리는 무조건 루트 페이지부터 검색한다.
- 모든 데이터는 정렬되어 있고, MMM은 AAA, FFF, LLL 3개를 읽은 다음에 나오므로 세 번째 리프 페이지로 직접 이동하게 된다.
- 세 번째 리프 페이지에서 LLL, MMM 2개를 읽어 MMM을 찾았다.
- 결과적으로 루트 페이지에서 AAA, FFF, LLL 3개와 리프 페이지에서 LLL, MMM 2개, 합쳐서 5건의 데이터를 검색해서 원하는 결과를 찾았으며, 페이지 2개를 읽었다.
- 균형 트리를 사용할 때, 더 적은 검색을 통해 MMM 데이터를 찾을 수 있다.
균형 트리의 페이지 분할
- 앞서 데이터를 검색하는데 균형 트리가 더 효율적이라고 살펴보았다.
- 인덱스는 균형 트리로 구성되어 있다.
- 즉, 인덱스를 만들면 SELECT의 속도를 향상시킬 수 있다.
- 두 페이지를 읽어서 데이터를 찾는 것은 3페이지를 읽어서 데이터를 찾는 것보다 빠른 방법이다.
- 그런데 인덱스를 구성하면 데이터 변경 작업(INSERT, UPDATE, DELETE) 시 성능이 나빠진다.
- 특히, INSERT 작업 시 페이지 분할이라는 작업이 발생하기에 느리게 입력될 수 도 있다.
- 페이지 분할은 새로운 페이지를 준비해서 데이터를 나누는 작업을 말한다.
- 페이지 분할이 일어나면, MySQL이 느려지고 너무 자주 일어나면 성능에 큰 영향을 준다.
- III만 추가하면 단순히 두 번째 리프 페이지에 HHH 다음에 입력되어 FFF, HHH, III, JJJ 순으로 정렬되고 페이지 분할은 일어나지 않는다.
- 이때, GGG가 새로 입력이 된다면 FFF 다음에 위치하게 되고, 두 번째 리프 페이지는 공간이 부족하여 페이지 분할이 일어나게 된다.
- 그리고 적절히 분배되어 두 번째 리프 페이지는 FFF, GGG, HHH가 되고 분할된 세 번째 리프 페이지는 III, JJJ가 된다.
4. 인덱스의 실제 사용
(1) 인덱스의 생성과 제거 문법
생성
CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름 (열_이름) [ASC | DESC]
- UNIQUE는 중복이 안되는 고유 인덱스를 만드는 것인데, 생략하면 중복이 허용된다.
- CREATE UNIQUE로 인덱스를 생성하려면 기존에 입력된 값들에 중복이 있으면 안된다.
- 그리고 인덱스를 생성한 후에는 입력되는 데이터와도 중복될 수 없으니 신중해야 한다.
- ASC 또는 DESC를 정해주지 않으면, ASC 정렬이 기본값이다.
제거
DROP INDEX 인덱스_이름 ON 테이블_이름
- 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다.
- ALTER TABLE 문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스도 제거할 수 있다.
(2) 인덱스의 생성 실습
SHOW TABLE STATUS LIKE 'member' ;
- member 테이블의 인덱스 정보를 확인해 보자.
- 위 결과에서 Data_length가 16384(16kbyte)로 나오는데, 이것이 한 페이지의 크기이다.
- Index_length는 0으로 나오는데, 이것은 보조 인덱스의 크기이다.
- 보조 인덱스는 만들지 않았기에 0으로 값이 나온 것이다.
CREATE INDEX idx_member_addr
ON member (addr) ;
SHOW INDEX FROM member ;
- addr의 단순 인덱스를 만들고 확인해 보자.
- 아래 member 테이블의 mem_number는 중복 값이 있으므로 UNIQUE INDEX를 만들 수 없다.
CREATE UNIQUE INDEX idx_member_mem_number
ON member (mem_number) ; #오류 발생
CREATE UNIQUE INDEX idx_member_mem_number
ON member (mem_name) ; #오류 발생하지 않음
- member 테이블의 mem_name은 중복 값이 없으므로 UNIQUE INDEX를 만들 수 있다.
- 그러나, 당장에는 문제가 없어 보이지만, 동명이인의 정보가 새로 입력된다면 문제가 발생한다.
(3) Execution Plan으로 인덱스 사용 여부 확인하기
- member 테이블 전체를 보는 것은 인덱스를 사용하는 것이 무의미하므로 사용되지 않는다.
SELECT * FROM member
- 그렇다면, SELECT 문을 사용할 때는 인덱스를 사용하나?
- 그렇지 않다.
- SELECT 문 안의 내용은 인덱스 사용 여부와 관계가 없다.
- 인덱스를 사용하기 위해서는 WHERE 절의 조건식이 나와야 한다.
SELECT mem_id, mem_name, addr
FROM member
WHERE mem_name = '에이핑크';
예외
SELECT mem_name, mem_number
FROM member
WHERE mem_number >= 1;
- 인덱스를 사용하지 않는 것이 더 효율적이라고 MySQL에서 판단하여 사용하지 않는다.
- 아래 결과도 인덱스를 사용하지 않는다.
- 인덱스를 사용하기 위해서는 WHERE 조건식에서 받는 열이 가공되면 안되기 때문이다.
SELECT mem_name, mem_number
FROM member
WHERE mem_number*2 >= 14 ;
- 다음과 같이 작성하면 인덱스를 사용하면서 위와 동일한 결과를 얻을 수 있다.
SELECT mem_name, mem_number
FROM member
WHERE mem_number >= 14/2 ;
