인덱스 설계 입문 - MySQL
이번 포스팅은 인덱스 설계에 필요한 밑바탕 지식을 습득하는 걸 목적으로 한다. 이 글에서 별도로 RDBMS를 통틀어 지칭하거나 특정 RDBMS 종류를 언급하지 않으면 모두 MySQL를 지칭하는 것이라 생각하면 된다.
1. 인덱스를 사용해야 하는 이유
RDBMS는 데이터를 저장하고 조회하기 위해 사용하는 소프트웨어이다. 이런 RDBMS는 쇼핑몰이나 블로그 그리고 그 외의 다양한 서비스에서 운영에 필요한 데이터를 효과적으로 관리하기 위해 사용된다.
그리고 그런 용도로 RDBMS가 사용될 때는 읽기 작업과 쓰기 작업의 비율이 9:1 정도로 발생 빈도가 읽기 작업에 훨씬 치우친다. 만약 읽기 작업이 훨씬 많은지 확신이 안든다면, 쇼핑몰에 새로운 상품들이 올라오는 빈도 수와 쇼핑하기 위해 옷을 구경하며 빈도 수가 어떻게 될지 생각해보면 좋다.
그렇게 읽기 작업이 훨씬 많이 일어나기 때문에 우리는 RDBMS가 읽기 작업을 더 효율적으로 수행할 수 있도록 설계해야 한다. 그리고 그런 설계 과정에서 중요한 역할을 하는 것이 바로 인덱스이다.
2. 인덱스가 뭘까?
우리가 무언가 공부하기 위해 구입하는 책을 펼쳐보면, 책의 도입부에 ‘목차’ 라는 게 있다. 우리는 이 목차를 읽고 우리가 찾고자 하는 책의 내용이 몇 페이지에 있는지 빠르게 찾을 수 있다.
만약 책에 목차가 없다면 우리가 원하는 내용을 찾아서 읽기 위해 모험을 떠나야 한다. 중간 쯤에 있을까 하여 중간부터 앞으로 읽는다던지, 맨 앞부터 순서대로 넘기면서 찾게될 것이다. 이런 책의 목차처럼 RDBMS의 인덱스도 우리가 찾고자 하는 레코드를 빠르게 찾을 수 있게 하는 열쇠 역할을 한다.
대학교 학생 테이블을 예시로 들어 인덱스가 어떤 역할을 하고, 조회 작업이 수행되는 방식을 어떻게 변화시키는지 알아보자.
만약 어떤 대학교의 학생들의 정보로 이루어진 테이블에서 ‘학번’ 컬럼을 가지고 인덱스를 생성했다면, 데이터베이스는 내부적으로 학번 컬럼의 값과 해당 레코드의 실제 위치를 찾아갈 수 있는 정보를 별도로 저장한다. 그렇게 관리되는 인덱스를 통해서 학번으로 데이터를 조회하면 학번 인덱스만을 확인하여 학번을 확인하고 레코드를 찾아낼 수 있어 효율적으로 조회 작업을 수행할 수 있게 된다.
인덱스를 통한 효율적인 조회
만약 조회 작업에 인덱스를 사용할 수 없다면, 모든 레코드(테이블 전체)를 다 불러와서 학번을 찾아야 한다. 그런데 모든 레코드를 읽어들이는 것은 데이터베이스에서 가장 비용이 많이 드는 작업인 디스크 I/O가 늘어나는 것이기 때문에 결과적으로 아주 비싼 작업을 수행하게 되버린다.
모든 레코드를 읽어들이는 것과 디스크 I/O가 늘어나는 것의 인과관계를 세부적으로 이해하기 위해서는 RDBMS가 데이터를 어떻게 디스크에 저장하고 읽어오는지에 대해서 이해해야 하는데, 이는 이 글의 주제에서 벗어난다. 쉽게 생각해서, 사람이 벽돌을 옮길 때 옮겨야 하는 벽돌의 양이 많으면 여러 번 옮기는 작업을 해야 하는 논리와 같다고 이해하자.
RDBMS 종류에 따라 다르지만 MySQL은 프라이머리 키를 제외한 인덱스가 프라이머리 키를 참조하도록 설계되어 있다. 그래서 프라이머리 키를 통해 실제 레코드를 찾는다.
2.1 인덱스의 중요한 특성, ‘정렬된 상태를 유지한다.’ 그리고 B-Tree 구조
인덱스의 ‘정렬된 상태를 유지한다.’는 특성은 읽기 작업이 효율적으로 처리되게 하는 데에 크게 기여한다. 사실 우리의 실생활에서도 데이터가 정렬된 상태이면 특정 데이터를 찾기가 더 쉬워진다. 다음의 예를 살펴보자.
우리가 학생부인데, 아직 학비를 안낸 학생들에게 연락을 해야 한다고 하자.
우리에게 주어진 것은 학생 이름과 번호가 적힌 문서이다. 만약 이 문서의 데이터가 이름을 기준으로 가나다~타파하 순으로 정렬이 되어있다면 “신제우” 라는 이름을 찾기가 훨씬 수월할 테지만, 정렬이 안돼어 있다면 손으로 짚고 내려가며 하나씩 확인해야 한다.
데이터베이스의 경우도 흡사하다. 사람이 정렬된 데이터에서 원하는 값을 찾는 방식과 유사하게 건너뛰면서 데이터를 검색하는데, 단순한 선형 건너뛰기를 하는 것은 아니다. 특정 자료구조를 활용해서 불필요한 탐색 작업을 건너뛴다.
그러한 목적을 위해 선택할 수 있는 자료구조는 다양한데, 가장 대중적이고 사용되어진 역사가 긴 것은 B-Tree이다. MySQL도 이것을 사용한다.
B-Tree는 루트 노드, 브랜치 노드, 리프 노드로 이루어진 자료구조이다. MySQL 인덱스는 이 자료구조를 이용해 실제 레코드는 리프 노드에 두고, 정렬된 상태를 유지하며 레코드들을 브랜치 노드를 기준으로 균일하게 묶어 관리한다.
B-Tree의 구조 예시
위와 같은 구조를 이용해 루트 노드 → 브랜치 노드 → 리프 노드 순서로 이동하며 필요 없는 부분을 건너뛰어 데이터를 탐색한다.
즉, 단순히 첫번째부터 하나씩 검사하는 것이 아니라, 트리 구조를 활용해 한 번에 많은 범위를 제외하고 빠르게 탐색할 수 있다.
예시의 B-Tree에서 자동차를 찾는 과정을 살펴보자. 리프 노드들이 정렬이 되어 있다는 것이 전제되기 때문에 ‘사’로 시작하지 않는 ‘가’와 ‘라’ 브랜치 노드 하위의 노드들은 살펴볼 필요가 없다.
따라서 ‘가’와 ‘라’ 브랜치 노드는 건너뛰고 ‘사’ 브랜치 노드 하위에서 자동차를 찾으면 된다.
예시의 그림에선 데이터의 수가 적어 효율이 크게 느껴지지 않지만, 데이터가 100억개 있는 경우에선 70억개를 읽지 않고 건너뛸 수 있는 것이다.
예시는 인덱스를 통한 데이터 조회가 효율적으로 이루어질 수 있는 논리에 대해 설명하기 위해 사용된 것으로, 실제 MySQL의 인덱스를 정확히 묘사한 것은 아님을 참고하자.
3. 인덱스 설계 이론
인덱스를 잘 설계하기 위해선 해당 테이블이 조회되는 양상을 알고 있어야 한다.
예를 들어, ‘User 테이블은 아이디로 조회되는 일이 잦아’, ‘주문 데이터는 주문일자를 기준으로 조회하는 일이 잦아’ 와 같이 해당 테이블을 조회할 때 WHERE 조건절에 사용하는 컬럼이 무엇인지, 어떤 정렬 순서로 조회되는 것이 잦은지를 알고 있어야 인덱스를 설계할 수 있다.
그런데 사용이 잦은 쿼리의 WHERE 조건절에 사용되는 컬럼은 하나가 아닐 때가 많다.
자주 사용되는 쿼리의 WHERE 조건절에 사용되는 컬럼이 여러 개이면 어떻게 해야할까?
정말 자주 사용되는 쿼리여서 그 쿼리가 효율적으로 처리되어야 할 필요가 있다면, 인덱스를 생성해야 겠다. 앞으로 여러 컬럼으로 구성된 인덱스를 다중 인덱스라 지칭하겠다.
인덱스에 포함되는 컬럼의 갯수와 인덱스의 성능은 반비례한다. 따라서 인덱스를 설계할 땐 각자의 상황에 맞게 성능과 가용성을 trade-off를 조정해야 한다.
3.1 다중 컬럼 인덱스
여러 개의 컬럼으로 인덱스를 생성할 땐 첫번째부터 두번째, 세번째 … 순서로 정렬이 이루어진다.
예를 들어, 다음과 같은 인덱스가 있다고 가정하자.
1
2
3
4
5
6
7
8
9
CREATE INDEX ix_name_team_age ON employees (name, team, age);
(name , team , age)
('김민호' ,'개발' , 36 )
('김민호' ,'마케팅', 27 )
('김민호' , '인사' , 40 )
('신제우' , '개발' , 38 )
('신제우' , '마케팅', 29 )
('옥택연' , '치어리딩', 48 )
왼쪽부터 정렬하여, 그 다음 컬럼이 정렬되는 논리이기 때문에 쿼리에 인덱스의 첫번째 컬럼을 사용하지 않으면 인덱스를 사용할 수 없다.
위의 예시에서 이름이 ‘김민호’이고 부서가 ‘마케팅 팀’인 레코드를 찾는 것은 이름이 김민호 인 것에서만 찾으면 되지만,
부서가 ‘마케팅 팀’ 인 레코드를 찾는 것은 결국 레코드 하나 씩을 살피면서 개발팀인지를 살펴봐야 한다. 2.1 절에서 살펴본 건너뛰기가 불가능한 것이다.
다음의 테이블과 쿼리를 보자.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE member ( -- 이런 테이블이 있다고 생각하자
member_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(5),
job VARCHAR(20),
gender BIT(1), -- 0은 남자
birth_date DATETIME,
INDEX ix_name_birth_gender (name, birth, gender)
);
SELECT * -- 쿼리 1
FROM member
WHERE name='김재우'
AND birth_date>'2002-02';
SELECT * -- 쿼리 2
FROM member
WHERE birth_date>'2002-02'
AND gender=1;
위의 예시에서 쿼리 1번은 인덱스를 사용할 수 있지만, 쿼리 2번은 사용할 수 없다.
인덱스가 B-Tree 구조를 가지며 첫번째 컬럼을 기준으로 정렬이 되어 있는데, 첫번째 컬럼을 사용하지 않으면 이 구조를 이용한 효율적인 탐색이 불가능하기 때문이다.
따라서 다중 인덱스를 설계할 땐 ‘자주 사용되는 쿼리’ 들이 조건절에 가장 자주 사용하는 컬럼을 첫번째 쿼리로 둬야 한다. 그래야 더 많은 쿼리가 인덱스를 활용할 수 있게 되기 때문이다.
만약 쿼리 2번이 인덱스를 사용할 수 있게 만드려면, INDEX ix_birth_gender (birth, gender) 인덱스를 추가로 생성해야 한다.
인덱스가 많아질수록 하나의 레코드가 WRITE 되는 작업에서 쓰기 작업이 늘어나기 때문에 비효율적으로 인덱스가 많이 생성되는 것은 지양해야 한다.
4. 인덱스 설계 실습
쇼핑몰에서 판매자가 자신의 의류 상품의 판매 상황을 자주 확인하는 시나리오에서 인덱스를 설계해보자.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE user (
user_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10),
role ENUM('ADMIN', 'SELLER', 'BUYER'),
created_date DATETIME,
modified_date DATETIME
);
CREATE TABLE seller (
seller_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT,
company_name VARCHAR(10),
created_date DATETIME,
modified_date DATETIME
);
CREATE TABLE orders (
order_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
seller_company_name VARCHAR(10),
buyer_name VARCHAR(10),
product_name VARCHAR(20),
price INT,
order_status ENUM('PENDING', 'ACCEPT', 'DELIVERING', 'REFUND_REQ', 'REFUND_ACCEPT', ...),
created_date DATETIME,
modified_date DATETIME
);
위의 DDL이 예시를 구성할 테이블들이다.
판매자의 서비스를 위해 날아가는 쿼리들은 다음과 같을 것이다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT buyer_name, product_name, price, order_status, create_date, modified_date -- 쿼리 1
FROM orders
WHERE seller_company_name='National Geographic'
AND order_status='PENDING';
SELECT buyer_name, product_name, price, order_status, create_date, modified_date -- 쿼리 2
FROM orders
WHERE seller_company_name='National Geographic'
AND created_date>'2025-01-01';
SELECT buyer_name, product_name, price, order_status, create_date, modified_date -- 쿼리 3
FROM orders
WHERE seller_company_name='National Geographic'
AND created_date>'2025-01-01'
AND order_status='REFUND_REQ';
이런 서비스에서 어떻게 인덱스를 설계하는 것이 좋을까? 이전에 말했듯 인덱스를 잘 설계하기 위해선 테이블이 어떻게 조회되는지 양상을 파악해야 한다.
위의 쿼리에서도 알 수 있듯 판매자가 자신의 정보를 확인할 때는 판매자의 고유한 식별값을 필터링할 때 사용해야 하므로 모든 쿼리에 seller_company_name을 쿼리 조건에 사용할 것이다. 그리고 많은 경우 추가적으로 조건절에 다른 조건들도 포함될 것이다.
그러면, seller_company_name을 첫번째 컬럼으로 해서 자주 사용되는 컬럼들을 다음 순서대로 넣어야 한다.
그리고 seller_company_name 와 함께 쓰이는 빈도 수가 높은 순서대로 인덱스의 다음 컬럼으로 추가하면 된다.
create_date가 가장 많이 쓰인다면 (seller_company_name, create_date, ...) 으로, order_status가 가장 많이 쓰인다면 (seller_company_name, order_status, ...) 으로 생성하면 된다.
즉, 다중 컬럼 인덱스의 가용성을 높이기 위해서는 WHERE 조건절에 사용되는 빈도 수가 높은 것부터 낮은 것을 순서대로 인덱스의 첫 번째, 두 번째, 세 번째 … 컬럼으로 지정하면 된다.
왜 이렇게 해야 다중 컬럼 인덱스의 가용성을 높일 수 있을까?
4.1 예제: 중간 컬럼을 사용하지 않는 경우
첫 번째 컬럼이 조건에 포함되어 있으면 MySQL은 해당 다중 컬럼 인덱스를 사용할 수 있다.
하지만 그 다음 순서의 컬럼을 건너 뛰고 다른 컬럼이 조건에 포함되면, 그 컬럼은 조건은 인덱스 탐색에는 사용되지 않고, 필터링에만 사용된다. 이런 인덱스 이용 방식을 Index Condition Pushdown (ICP)라고 한다.
1
2
3
CREATE INDEX idx_col1_col2_col3 ON my_table (col1, col2, col3);
SELECT * FROM my_table WHERE col1 = 'A' AND col3 = 'Z';
👉 이 경우 MySQL의 동작은?
- col1 = ‘A’ → 인덱스 탐색에 사용됨
- col3 = ‘Z’ → 인덱스 필터링에 사용
인덱스는 사용되지만 순서대로 정렬되는 방식이기 때문에 col2 를 건너뛰고 col3을 직접 탐색하는 것은 불가능하다.
마치며
이 글의 내용들 중에는 주제에 집중하기 위해 간략하게 설명하고 넘어간 것들이 많은데, 그것 때문에 이 글을 읽는 독자들이 답답함을 느꼈지는 않았을까 걱정이 된다. 기회와 시간이 된다면 그런 답답함을 모두 해소할 수 있도록 관련 글들을 추가로 게시하겠다. 혹시 특별히 관심이 있어 다른 글들 보다 먼저 적어줬으면 하는 주제가 있다면, 댓글로 달아주면 참고하도록 하겠다.
부족한 글을 읽어주어 감사드린다. 모든 분들에게 즐거운 개발 생활이 이어지길 바란다.