조회 성능을 위한 DB Index(feat.MySQL)

들어가며

대부분의 웹 어플리케이션을 구축하는 데 있어 DB 입출력 작업은 필수적이다. 그러므로 서버 성능의 관점에서, 클라이언트의 요청에 신속하게 대응하기 위해서는 DB 성능을 함께 고려해야 한다. DB 성능에 있어 인덱스는 늘 빠지지 않고 등장하는 주제이다. 관련하여 여러 개념들을 들어왔는데, 한 번 정리해보고자 한다. 널리 활용되는 RDBMS인 MySQL을 기준으로 작성했다.

 

Index의 정의와 필요성

Index란 DB의 Table에 대한 동작 속도를 높여주는 자료구조이다. 책을 읽을 때 활용하는 목차와 같다. 목차를 참고하여, 원하는 정보를 더욱 빠르게 얻을 수 있다. 웹 어플리케이션 동작 시에는 DB Table에 대한 조회가 빈번하게 일어나고, 대부분 필터링을 적용한다. 이 때 DB에서는 조건에 맞는 데이터를 조회하기 위해 모든 테이블을 조회(Full-Scan)하게 되는데, 연산의 관점에서 보면 낭비가 발생한다. Index를 활용하여, 이러한 낭비를 줄일 수 있다.

 

Index 동작 원리(B+Tree 자료구조)

Index는 어떻게 조회 성능을 비약적으로 높일 수 있을까? 내부적으로 B+Tree 자료구조를 사용하기 때문에 가능하다. B+Tree 자료구조는 바이너리 트리랑 비슷한 자료구조로, 차이점은 Children 노드가 여러 개이다. Key 값으로 정렬되어 있어 Binary Search를 사용하기에 조회 시 시간 복잡도를 비약적으로 향상시킬 수 있다. 데이터는 Leaf 노드에만 저장되어 있어 Key를 탐색하여 해당하는 데이터를 조회해온다.

단순한 형태의 B+Tree(출처 : 위키피디아)

 

데이터를 조회할 때, B+Tree 구조에서 어떻게 읽게 되는지 살펴보자. 각 노드는 디스크에 저장되어 있으므로 주소값을 가진다. 루트에서부터 Key 값을 찾기 시작하는데, 노드에 저장된 Key들을 기준으로 작은 경우, 사잇값(같은 경우를 포함), 큰 경우를 따져서 다음 노드로 내려가면서 탐색한다. 노드 내부에서 Binary Search를 적용한다.

 

시간 복잡도를 계산해보자. N개의 데이터가 B+Tree에 저장되어 있다고 가정한다. 포인터는 주소를 가리키기 때문에 O(1)이며, 각 노드별로 포인터의 갯수(차수)만큼 조회해야 하는 비용으로 O(logM)이 들지만, M은 디스크 블록 크기에 따라 고정되어 있으므로 상수 취급한다. 그러면 트리의 높이만큼 탐색해야 하는데, 이는 m을 밑으로 하는 log N이며 근사치로 O(logN)이다. 그러므로 O(logN)의 시간 복잡도를 가지게 되며, Full-Scan을 할 때의 시간 복잡도인 O(N)보다 비약적으로 향상된 성능을 가지게 된다.

 

참고로 각 노드는 sibling(형제, 같은 층위에 있는 노드)로 연결할 수 있는 포인터가 존재한다. 이러한 특징은 Range 쿼리를 수행할 때 유용하게 활용될 수 있다. B+Tree 자료구조에 삽입, 수정, 삭제 등의 연산을 할 때는 복잡한 연산이 필요하다. 참고한 영상에서 쉽게 풀어서 설명하고 있다.

 

Index의 종류

Index의 종류는 다음과 같다. 대표적인 것들만 작성했고, 이외에도 여러 Index가 있다.

 

Clustered Index

테이블 당 한 개만 생성되는 인덱스로, 행 데이터를 인덱스로 지정한 열에 맞추어 자동 정렬한다. Primary Key 설정 시 자동으로 생성된다. 모든 보조 인덱스(Non-Clustered Index)는 PK를 포함하며, PK가 커질수록 자연히 보조 인덱스의 크기도 커진다.

Non-Clustered Index

Clustered Index 이외의 인덱스를 의미한다. Clustered Index와 달리 여러 개를 가질 수 있으며, 인덱스 순서와 물리적 순서가 불일치한다.

Single Index

하나의 컬럼으로 구성된 Index를 의미한다.

Composite Index

여러 개의 컬럼 조합으로 생성된 Index를 의미한다.

 

Index 설계 전략

Index를 과도하게 사용한다면 되려 성능이 악화될 수 있다. Index도 디스크에 저장되기 때문에 공간을 낭비하게 될 수도 있고, 조회가 아닌 변경(수정,삭제,추가) 연산의 경우 Index에도 반영해주어야 하기 때문에 시간을 낭비(불필요한 연산)하게 될 수도 있다. 그러므로 적절한 설계 전략을 세워야 한다. <주니어 백엔드 개발자가 반드시 알아야 할 실무 지식> 책을 주로 참고하였으며, 마지막 PK 전략의 경우 프로젝트를 하며 늘 적용했던 이유가 궁금해서 한 번 정리했다.

 

조회 패턴에 맞게 설계하기

자주 날아오는 조회 쿼리가 어떤 패턴을 가지고 있는지 파악해야 한다. 예를 들어 게시판 서비스에서 ‘마이페이지’ 기능이 있고 ‘내가 작성한 글 조회하기’ 기능이 있는 경우, 어떤 유저가 작성했는지를 기준으로(user_id 라던지) 인덱스를 걸어야 할 것이다. 비즈니스 로직을 잘 이해하고 있어야 한다.

 

선택도를 고려하기

선택도가 높은, 칼럼 데이터의 경우의 수가 다양한 컬럼일수록 인덱스의 효율이 높다. 태어난 년도와 성별을 기준으로 조회하는 경우를 생각해보자. gender 칼럼이 M, F, N 3개의 값 중 하나를 갖고, 전체 회원 데이터 중 M이 50만 개, F가 50만 개, N이 천 개라면 gender를 인덱스로 걸었더라도 여전히 50만개의 데이터를 확인해야 한다. 그러나 birthyear는 정수형이고 더 많은 경우의 수를 가지게 되므로 좀 더 효율이 높을 것이다.

select * from member where gender = 'F' and birthyear = 1900;

 

커버링 인덱스 활용하기

커버링 인덱스란 특정 쿼리를 실행하는 데 필요한 칼럼을 모두 포함하는 인덱스를 의미한다. 커버링 인덱스가 지정되어 있으면 조회할 때 Clustered Index를 통해 실제 테이블에 접근하지 않고 바로 데이터를 조회하므로 성능 상으로 이점을 볼 수 있다.

create index test_idx on tbl (a,b);
select a,b from tbl where a='3' and b='4';

 

PK(Clustered Index)로 정수형, AUTO INCREMENT 사용하기

흔히 정수형으로 설정하고 AUTO INCREMENT를 주는데, 저장 공간의 이점도 있고 보조 인덱스를 사용하면 내부적으로 데이터 조회 시 해당하는 PK를 가져와서 PK 기준으로 한 번 더 조회를 하기 때문에 정수형으로 설정하는 것이 성능에 유리하다. 데이터 크기가 작을수록 트리 높이가 낮아지기 때문이다. 또한, AUTO INCREMENT를 주면 새로운 데이터가 생성되어도 인덱스 상에서 무조건 맨 뒤에 삽입되기 때문에 인덱스를 조정하는 연산을 보다 단순하게 할 수 있다.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255),
    name VARCHAR(100)
);

 

Index 사용 예시

Index를 간단하게 사용하고, 옵티마이저를 통해 성능 개선 여부를 확인하였다.

 

스키마

PRIMARY KEY는 InnoDB가 자동으로 인덱스 생성

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255),
    name VARCHAR(100)
);

 

인덱스 확인 - PK

SHOW INDEX from Users;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

 

인덱스 성능 비교를 위한 데이터 주입

• 저 Selection vs 고 Selection
• email → 모든 행이 서로 다른 값 → 고 Selection (인덱스 효과 큼)
• name → 값 종류가 적고 반복됨 (예: 'Alice', 'Bob', 'Charlie', ‘David’) → 저 Selection (인덱스 효과 낮음)
• 총 20개의 행 생성

INSERT INTO users (email, name) VALUES
-- name = 'Alice'
('alice001@example.com', 'Alice'),
('alice002@example.com', 'Alice'),
('alice003@example.com', 'Alice'),
('alice004@example.com', 'Alice'),
('alice005@example.com', 'Alice'),

-- name = 'Bob'
('bob001@example.com', 'Bob'),
('bob002@example.com', 'Bob'),
('bob003@example.com', 'Bob'),
('bob004@example.com', 'Bob'),
('bob005@example.com', 'Bob'),

-- name = 'Charlie'
('charlie001@example.com', 'Charlie'),
('charlie002@example.com', 'Charlie'),
('charlie003@example.com', 'Charlie'),
('charlie004@example.com', 'Charlie'),
('charlie005@example.com', 'Charlie'),

-- name = 'David'
('david001@example.com', 'David'),
('david002@example.com', 'David'),
('david003@example.com', 'David'),
('david004@example.com', 'David'),
('david005@example.com', 'David');

 

인덱스 설정 및 테스트

봐야할 rows의 갯수가 1개, 5개로 차이가 있다. rows는 EXPLAIN 했을 때 옵티마이저가 확인할 것으로 예상되는 row의 숫자이다.

-- 고Selection 인덱스 테스트
CREATE INDEX idx_users_email ON users(email);

EXPLAIN SELECT * FROM users WHERE email = 'bob003@example.com';

+------+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+
| id   | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra                 |
+------+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | users | ref  | idx_users_email | idx_users_email | 1023    | const | 1    | Using index condition |
+------+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+

-- 저Selection 인덱스 테스트
CREATE INDEX idx_users_name ON users(name);

EXPLAIN SELECT * FROM users WHERE name = 'Bob';

+------+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+
| id   | select_type | table | type | possible_keys  | key            | key_len | ref   | rows | Extra                 |
+------+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | users | ref  | idx_users_name | idx_users_name | 403     | const | 5    | Using index condition |
+------+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+

 

INDEX를 DROP하고 확인하면, rows가 20개가 나온다.

DROP INDEX idx_users_email ON USERS;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

EXPLAIN SELECT * FROM USERS WHERE email = 'bob003@example.com';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | USERS | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

 

참고 자료

데이터베이스 인덱싱 자료구조 | B+ Tree 완전정복 | DB 의 데이터 저장 방법

1. 커버링 인덱스 (기본 지식 / WHERE / GROUP BY)

주니어 백엔드 개발자가 반드시 알아야 할 실무 지식 CHATPER 3(2025, 최범균)

주니어 백엔드 개발자가 반드시 알아야 할 실무 지식 | 최범균 - 교보문고

 

B+ 트리 - 위키백과, 우리 모두의 백과사전