서론
RDBMS에서 조회 쿼리의 성능을 높이고자 할 때 가장 먼저 떠올리는 방법은 아마도 인덱스를 생성하는 것일 것입니다. 필자 역시 인덱스 적용을 위해 여러 관련 학습 자료를 찾아보았지만, 대부분 인덱스의 동작 방식이나 성능 향상 효과에 대해서만 다루고 있었습니다. 반면, 인덱스를 언제, 어떤 기준으로 적용해야 하는지에 대해서 설명하는 자료를 찾기 어려웠습니다.
일부 자료에서는 "읽기/쓰기 비율이 9:1 또는 8:2처럼 읽기 비중이 클 경우 인덱스를 사용하는 것이 효과적"이라고 설명하지만, 이는 너무도 단순한 상황입니다. 만약 그런 단순한 상황만 존재했다면, 인덱스 적용에 대해 깊이 고민할 필요조차 없었을 것입니다.
이러한 고민을 하게 된 데에는 실제 경험이 바탕이 되었습니다.
콘서트 예매 관련 프로젝트를 진행하던 중, 예매 테이블과 관련된 API를 개발하게 되었습니다. 티켓팅 시스템은 특정 시간대에 트래픽이 집중되는 경우가 많기 때문에, 예매 테이블에 대한 조회 성능을 높이는 것이 중요한 과제였습니다.
하지만 예매 테이블의 특성상 단순히 조회만 많은 것이 아니라, 예매 생성, 삭제, 변경 등 쓰기 작업도 빈번하게 발생합니다.
이처럼 읽기와 쓰기 작업이 모두 빈번한 상황에서는, 인덱스를 적용하는 것이 정말 적절한 선택인지 쉽게 판단하기 어려웠습니다.
이번 포스팅에서는 단순히 인덱스를 사용하면 조회 성능은 향상되고 쓰기 성능은 저하된다는 설명을 넘어서, 실제로 성능이 어느 정도 변화하는지를 수치적으로 살펴보려 합니다. 조회 성능이 얼마나 개선되고, 반대로 쓰기 성능은 어느 정도 영향을 받는지를 구체적인 수치를 통해 확인해 보겠습니다.
이를 통해 인덱스를 도입할 때 수치적으로 판단하고 접근할 수 있는 기준을 마련하고자 합니다.
읽기 성능
우선 인덱스 유무에 따라 읽기 성능이 얼마나 개선되는지부터 살펴보겠습니다. 실험은 MySQL의 버퍼 풀의 영향을 배제하기 위해 도커를 이용해서 각 실험마다 MySQL 재시작하여 콜드 스타트로 진행하겠습니다.
테이블 & 데이터 준비
CREATE TABLE reservation (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
concert_id BIGINT NOT NULL,
note VARCHAR(255) DEFAULT NULL
);
테이블은 서론에서 말한 예약 테이블을 간략화해서 구성한 것으로, 실험에 필요한 최소한의 칼럼만 포함하고 있습니다. 테이블 엔진은 InnoDB를 사용합니다.
데이터는 아래처럼 재귀적 CTE를 활용해 더미 데이터를 활용하겠습니다.
-- 더미 데이터 10,000건 삽입
INSERT INTO reservation (user_id, concert_id, note)
WITH RECURSIVE seq (n) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT
FLOOR(1 + RAND(n) * 10000), -- user_id: 1~10,000 랜덤
FLOOR(1 + RAND(n+10000) * 10000), -- concert_id: 1~10,000 랜덤
CONCAT('note ', n)
FROM seq;
단건 조회 성능 비교
조회 성능은 쿼리 구성에 따라 인덱스 활용 방식과 효과가 달라지지만, 이번 절에서는 인덱스 존재 유무에 따라 테이블 크기별 단건 조회 성능 차이를 비교해 보겠습니다.
1,000 건 / 인덱스 미사용
mysql> EXPLAIN ANALYZE select * from reservation where user_id=552 and concert_id=736;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: ((reservation.concert_id = 736) and (reservation.user_id = 552)) (cost=3.5 rows=1) (actual time=5.55..5.72 rows=1 loops=1)
-> Table scan on reservation (cost=3.5 rows=25) (actual time=4.01..5.54 rows=1000 loops=1)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
위 결과는 다음과 같이 해석할 수 있습니다.
- 테이블 스캔 4.01 ms (스캔 시작 시간) ~ 5.54 ms (스캔 종료 시간) : 총 1.53 ms 소요
- 필터 처리 5.55 ms ~ 5.72 ms : 총 0.17 ms 소요
- 쿼리 전체 소요 시간 : 약 5.72 ms
- MySQL은 파이프라인 방식으로 처리합니다. 즉, 테이블 스캔에서 한 줄 읽고 바로 필터로 넘겨줍니다. 하지만 위 결과를 보면 시간 순서상 테이블 스캔이 모두 끝난 후 필터가 진행되는 것으로 해석할 수 있습니다. 이는 파이프라인 방식으로 동작하긴 하지만 첫 몇 줄은 버퍼링 되거나 다른 작업이 먼저 이뤄질 수 있기 때문에 테이블 스캔 종료 시간 후 필터가 진행되는 것처럼 보입니다. 테이블 크기가 작아 나오는 결과로 해석됩니다. 이 부분은 다른 테이블 크기에서도 확인해 보겠습니다.
1,000 건 / 인덱스 사용
CREATE INDEX idx_user_concert ON reservation(user_id, concert_id);
mysql> EXPLAIN ANALYZE select * from reservation where user_id=552 and concert_id=736;
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on reservation using idx_user_concert (user_id=552, concert_id=736) (cost=1.1 rows=1) (actual time=0.266..0.268 rows=1 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
위 결과는 다음과 같이 해석할 수 있습니다.
- 인덱스 룩업 0.266 ms ~ 0.268 ms : 총 0.002 ms 소요
- 위 실험은 콜드 스타트로 시작했으므로 디스크에 인덱스 페이지 접근 및 실제 데이터 레코드 접근까지 2 μs 정도 소요되는 것을 확인할 수 있습니다.
- 쿼리 전체 소요 시간 : 약 0.268 ms
1,000 건 / 비교
| 항목 | 인덱스 미사용 | 인덱스 사용 |
| 총 실행 시간 | 약 5.72ms | 약 0.268ms |
| 읽은 행 수 | 1000 | 1 |
| 접근 방식 | Full Table Scan | Index Lookup |
| 실행 시간 감소율 | 약 95.3% 감소 | |
10,000 건 / 인덱스 미사용
mysql> EXPLAIN ANALYZE select * from reservation where user_id=8703 and concert_id=7014;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: ((reservation.concert_id = 7014) and (reservation.user_id = 8703)) (cost=1.4 rows=1) (actual time=23.7..26.7 rows=1 loops=1)
-> Table scan on reservation (cost=1.4 rows=4) (actual time=11.5..25.7 rows=10000 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
만 건의 레코드를 가진 테이블에서는 테이블 스캔 중간에 필터링이 시작됨을 확인할 수 있습니다. 그리고 테이블 스캔과 필터링이 동시에 시작하지 않는 것을 보아 버퍼링이나 다른 작업이 먼저 수행되는 것으로 추측할 수 있습니다.
10,000 건 / 인덱스 사용
CREATE INDEX idx_user_concert ON reservation(user_id, concert_id);
mysql> EXPLAIN ANALYZE select * from reservation where user_id=8703 and concert_id=7014;
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on reservation using idx_user_concert (user_id=8703, concert_id=7014) (cost=1.1 rows=1) (actual time=1.28..1.28 rows=1 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
10,000 건 / 비교
| 항목 | 인덱스 미사용 | 인덱스 사용 |
| 총 실행 시간 | 약 26.7ms | 약 1.28ms |
| 읽은 행 수 | 10,000 | 1 |
| 접근 방식 | Full Table Scan | Index Lookup |
| 실행 시간 감소율 | 95.21% 감소 | |
100,000 건 / 인덱스 미사용
mysql> EXPLAIN ANALYZE select * from reservation where user_id=5414 and concert_id=36506;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: ((reservation.concert_id = 36506) and (reservation.user_id = 5414)) (cost=1.1 rows=1) (actual time=67.9..91.1 rows=1 loops=1)
-> Table scan on reservation (cost=1.1 rows=1) (actual time=2.22..84.4 rows=100000 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)
100,000 건 / 인덱스 사용
CREATE INDEX idx_user_concert ON reservation(user_id, concert_id);
mysql> EXPLAIN ANALYZE select * from reservation where user_id=5414 and concert_id=36506;
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on reservation using idx_user_concert (user_id=5414, concert_id=36506) (cost=1.1 rows=1) (actual time=1.9..1.9 rows=1 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
100,000 건 / 비교
| 항목 | 인덱스 미사용 | 인덱스 사용 |
| 총 실행 시간 | 약 91.1ms | 약 1.9ms |
| 읽은 행 수 | 100,000 | 1 |
| 접근 방식 | Full Table Scan | Index Lookup |
| 실행 시간 감소율 | 97.91% 감소 | |
위의 과정과 동일하게 천만 건의 레코드까지 비교하였고 다음과 같은 결과 그래프를 얻을 수 있었습니다
위 인덱스 사용과 미사용에 따른 (레코드 수 - 실행시간) 그래프를 보면 인덱스 미사용 시 테이블 크기가 늘어날수록 실행 시간이 기하급수적으로 늘어나는 것에 비해 인덱스 사용 시 실행 시간이 안정적인 것을 확인할 수 있습니다.
인덱스 사용 시 테이블 크기가 늘어날수록 실행 시간 개선률이 꾸준하게 올라가며 최소 95% 이상을 꾸준히 유지합니다. 당연히 조회 쿼리의 구성과 인덱스 적용 칼럼의 선택도(기수성)에 따라 차이가 있겠지만 위 실험을 통해 단건 조회에서 인덱스를 적용하면 테이블 크기에 상관없이 90% 이상의 성능 개선 효과를 기대하고 적용해 볼 수 있습니다.
다건 조회 성능 비교
'Real MySQL'이란 도서에서는 일반적인 DBMS의 옵티마이저에서는 인덱스를 레코드 1건을 읽는 것이 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측합니다. 그래서 인덱스를 통해 읽어야 할 레코드의 건수(물론 옵티마이저가 판단한 예상 건수)가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블 풀 스캔 방식으로 처리하는 것이 효율적이라고 설명하고 있습니다.
하지만 위 글은 인덱스에서 읽는 레코드 수가 일정 수준을 넘어가면 테이블 풀 스캔 보다 성능이 낮아질 수 있음을 말하지만 만일 인덱스를 통해 전체 테이블 레코드의 10% 정도 읽게 된다면 어느 정도 성능이 개선될지 예측할 수는 없습니다. 이번 절에서는 인덱스를 통해 읽는 전체 테이블에 대한 레코드의 비율을 조절하며 성능 개선 수치에 대해 실험해 보겠습니다.
이번 실험에서 진행할 더미 데이터는 다음과 같습니다.
-- 더미 데이터 1,000,000건 삽입
set cte_max_recursion_depth = 1000001;
INSERT INTO reservation (id, user_id, concert_id, note)
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 1000000
),
shuffled AS (
SELECT
n,
ROW_NUMBER() OVER (ORDER BY RAND()) AS user_id,
ROW_NUMBER() OVER (ORDER BY RAND()) AS concert_id
FROM seq
)
SELECT n, user_id, concert_id, CONCAT('note ', n) from shuffled;
예시 데이터는 다음과 같습니다.
mysql> select * from reservation limit 10;
+----+---------+------------+---------+
| id | user_id | concert_id | note |
+----+---------+------------+---------+
| 1 | 610 | 610 | note 1 |
| 2 | 161 | 161 | note 2 |
| 3 | 57 | 57 | note 3 |
| 4 | 824 | 824 | note 4 |
| 5 | 956 | 956 | note 5 |
| 6 | 267 | 267 | note 6 |
| 7 | 604 | 604 | note 7 |
| 8 | 6 | 6 | note 8 |
| 9 | 300 | 300 | note 9 |
| 10 | 600 | 600 | note 10 |
+----+---------+------------+---------+
10 rows in set (0.00 sec)
위 더미 데이터는 값 범위 백만에서 user_id와 concert_id가 랜덤 한 순서로 유일한 값으로 존재하도록 만든 더미 데이터입니다. 이런 이유는 테이블 크기가 백만 일 때 전체 테이블 크기의 5%를 지정하는 간단한 방법은 ' WHERE user_id BETWEEN 1 AND 50000(백만의 5%)' 같은 방법입니다. 이런 방법을 사용하기 위해 위의 더미 데이터를 생성했습니다.
mysql> SELECT COUNT(*) FROM (SELECT user_id FROM reservation GROUP BY user_id HAVING COUNT(*) = 1) AS tmp;
+----------+
| SUM(cnt) |
+----------+
| 1000000 |
+----------+
1 row in set (1.68 sec)
위 결과처럼 중복되는 user_id가 없기 때문에 user_id와 concert_id 칼럼의 선택도가 1이어서 정확한 비율만큼 데이터를 설정할 수 있습니다.
실험은 테이블 레코드 백 만개에 대해 5% 단위로 레코드 비율을 올리며 성능을 비교해 보겠습니다. 첫 5%에 대한 쿼리 예시는 다음과 같습니다.
mysql> EXPLAIN ANALYZE select * from reservation where user_id between 1 and 50000;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (reservation.user_id between 1 and 50000) (cost=103084 rows=100346) (actual time=0.112..758 rows=50000 loops=1)
-> Table scan on reservation (cost=103084 rows=996647) (actual time=0.105..694 rows=1e+6 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.77 sec)
mysql> EXPLAIN ANALYZE select * from reservation where user_id between 1 and 50000;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index range scan on reservation using idx_user over (1 <= user_id <= 50000), with index condition: (reservation.user_id between 1 and 50000) (cost=45156 rows=100346) (actual time=0.682..97.7 rows=50000 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.11 sec)
그런데 5% 범위에 대한 첫 번째 조회 시 인덱스를 사용하지 않고 풀 테이블 스캔을 진행합니다. 그리고 동일 쿼리를 바로 다시 실행하면 인덱스를 사용합니다. 필자가 유추하는 원인으로는 모든 실험은 도커를 이용해 MySQL 재시작 후 실행하므로 버퍼 풀에 데이터 페이지가 없어 옵티마이저가 전체 레코드 범위의 5%라도 풀 테이블 스캔을 진행하고 두 번째는 워밍업 돼 있어 인덱스를 사용하는 것으로 생각됩니다.
참고
MySQL 재시작 직후, 버퍼 풀에 디스크의 데이터가 적재돼 있지 않은 상태를 콜드 스타트라고 부릅니다. 반대의 상태를 워밍업이라고 합니다. MySQL 5.6 버전부터 버퍼 풀 덤프 및 적재 기능이 도입됐습니다. 실제 서비스 환경에 있는 MySQL 재시작 후 버퍼 풀 복구 기능을 사용해 워밍업을 진행하지만 실험에서는 콜드 스타트로 진행하겠습니다.
실험의 목적이 인덱스 사용에 대한 성능 측정이므로 콜드 스타트에서 인덱스를 강제해서 실험을 진행하도록 하겠습니다.
EXPLAIN ANALYZE SELECT user_id, concert_id, note
FROM reservation FORCE INDEX (idx_user)
WHERE user_id BETWEEN 1 AND 50000;
과정은 단건 조회와 동일하므로 생략하겠습니다. 결과 그래프는 아래와 같습니다.
그래프를 분석하면 다음과 같습니다.
- 콜드 스타트 상태에서 인덱스 사용은 인덱스 페이지와 데이터 페이지 모두 필요하기 때문에 Data Volume 5%를 제외하고 풀테이블 스캔이 월등히 빠릅니다. 5%에서도 인덱스 사용과 풀 테이블 스캔이 크게 차이 나지 않습니다.
- 단건 조회 결과를 기반으로 콜드 스타트 상태에서 조회 데이터 5% 이내까지 성능 개선율이 최대 100%까지 줄어듬을 확인할 수 있습니다.
- 테이블 풀 스캔은 조회 데이터 건수에 상관없이 일정한 실행시간을 보입니다.
위 실험을 통해 콜드 스타트 상태에서는 5% 이내까지만 인덱스 사용이 풀 테이블 스캔보다 성능 개선이 됨을 확인했습니다. 옵티마이저가 인덱스 사용보다 풀 테이블 스캔을 선택하는 25% 선 까지는 많이 못 미칩니다. 추가 실험으로 테이블과 인덱스를 풀 스캔 하여 워밍업 상태를 만든 뒤 인덱스 성능을 측정해 보겠습니다.
그래프를 분석하면 다음과 같습니다.
- 워밍업 후 5% 개선률 65.8% 으로 콜드 스타트의 5% 보다 많은 성능 개선이 이루어졌습니다.
- 워밍업 후 10% 까지 인덱스 사용이 성능 개선을 보이고 이후는 인덱스 사용 성능이 테이블 풀 스캔보다 떨어집니다.
아래 결과처럼 옵티마이저 또한 대략 10%를 기점으로 인덱스보다 테이블 풀 스캔을 사용합니다.
mysql> EXPLAIN ANALYZE SELECT user_id, concert_id, note FROM reservation WHERE user_id BETWEEN 1 AND 100000;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index range scan on reservation using idx_user over (1 <= user_id <= 100000), with index condition: (reservation.user_id between 1 and 100000) (cost=93011 rows=206690) (actual time=2.11..404 rows=100000 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.41 sec)
mysql> EXPLAIN ANALYZE SELECT user_id, concert_id, note FROM reservation WHERE user_id BETWEEN 1 AND 150000;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (reservation.user_id between 1 and 150000) (cost=104408 rows=317524) (actual time=0.0461..247 rows=150000 loops=1)
-> Table scan on reservation (cost=104408 rows=1.03e+6) (actual time=0.0421..200 rows=1e+6 loops=1)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.26 sec)
결론
인덱스는 일반적으로 조회 대상이 전체 데이터의 25% 이하일 때 효과적이라 알려져 있지만, 실험 결과 10% 이하에서만 성능 개선이 확인됩니다. 따라서, 인덱스 사용 시 데이터 분포와 조회 범위 반드시 고려해야 합니다.
실험 결과를 활용해서 인덱스 적용 시 성능 개선 기준을 잡으면 다음과 같습니다.
- 단건 조회 시 조회 성능 최소 95% 이상 개선
- 다건 조회 시 Data Volume 1% 당 성능 개선률 100%에서 10% 씩 감소
쓰기 성능
사실 이 포스팅을 쓰게 된 계기는 쓰기 성능입니다. 인덱스가 마냥 장점만 있다면 걱정 없이 인덱스를 남발할 것입니다. 하지만 인덱스는 양날의 검으로 조회 성능을 높이는 대신 쓰기(삽입, 변경, 삭제) 성능을 떨어뜨리고 저장 공간 또한 추가로 요구합니다.
이번 절에서는 테이블 레코드 수와 인덱스 개수에 따른 쓰기 작업의 성능 차이를 실험하여 인덱스 생성에 따른 성능 저하에 대해 분석해 봅니다.
삽입 성능 비교
테스트 목적이 인덱스 추가 작업에 대한 성능 테스트이므로 삽입, 변경, 삭제 성능 테스트 모두 하는 것은 불필요한 중복으로 판단하여 삽입 성능 테스트만 진행하겠습니다. INSERT 문에 대해서는 EXPLAIN ANALYZE를 사용할 수 없습니다. 삽입 성능 테스트는 다음 스토어드 프로시저를 활용해서 시간을 측정하겠습니다.
DELIMITER $$
CREATE PROCEDURE insert_and_measure_avg(IN cnt BIGINT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE start_time DATETIME(6);
DECLARE end_time DATETIME(6);
DECLARE elapsed BIGINT DEFAULT 0;
WHILE i <= 30 DO
SET start_time = NOW(6);
INSERT INTO reservation (user_id, concert_id, note)
VALUES (
FLOOR(1 + RAND() * cnt),
FLOOR(1 + RAND() * cnt),
CONCAT('note ', i)
);
SET end_time = NOW(6);
SET elapsed = elapsed + TIMESTAMPDIFF(MICROSECOND, start_time, end_time);
SET i = i + 1;
END WHILE;
DELETE FROM reservation WHERE id > cnt;
SELECT elapsed / 30 AS avg_microseconds;
END$$
DELIMITER ;
위 프로시저에서 30회 삽입 쿼리를 트랜잭션 롤백으로 취소하지 않고 삭제 쿼리로 제거하는 이유는 InnoDB에서 커밋 시 체인지 버퍼를 사용하여 인덱스의 쓰기를 지연시키지만 롤백하면 언두 로그만 사용하고 체인지 버퍼는 사용하지 않습니다. 따라서, 해당 실험은 쓰기 성능에 대한 테스트이므로 체인지 버퍼까지 사용하도록 트랜잭션 롤백을 사용하지 않았습니다.
테스트 시간 측정은 30번의 삽입 쿼리 수행 시간을 측정해 평균을 낸 값을 사용하겠습니다. 테스트 테이블과 더미 데이터는 다건 조회 성능 측정에 사용한 것과 동일합니다. 삽입 또한 콜드 스타트 상태로 측정합니다.
성능 테스트 실험의 결과 그래프는 다음과 같습니다.
그래프를 분석하면 다음과 같습니다.
- 인덱스가 많아질수록 삽입 시간이 증가하는 경향이 있습니다.
- 하지만 인덱스 개수에 비례해서 명확하게 삽입 시간이 늘어난다고 보기 힘듭니다.
인덱스가 늘어나면 그에 따라 삽입 시간도 늘어날 것으로 예상했지만 실험 결과 3개 또는 4개의 인덱스 정도가 인덱스 미사용과 비교할 때 항상 성능 저하가 있을 뿐 1개 또는 2개는 차이 없거나 낮은 경우도 있습니다. 그리고 전체적인 실행 시간 상승률이 모두 100% 이내입니다.
이러한 결과가 나오는 이유는 InnoDB 스토리지 엔진은 인덱스 업데이트 작업이 발생할 때 인덱스 페이지가 버퍼 풀에 있지 않다면 즉, 디스크에서 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 체인지 버퍼에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상합니다. 따라서 인덱스 개수가 늘어나도 InnoDB 스토리지 엔진이 체인지 버퍼에 있는 내용을 디스크에 써야 할 필요가 없다고 판단하면 메모리 작업뿐이므로 성능 차이가 많이 나지 않습니다.
체인지 버퍼는 기본적으로 InnoDB 버퍼 풀로 설정된 메모리 공간의 25%까지 사용할 수 있게 설정돼 있으며, 필요하다면 버퍼 풀의 50%까지 사용하게 설정할 수 있습니다. 만일 빈번하게 쓰기 작업이 요구되는 테이블에 인덱스가 필요하다면 체인지 버퍼 크기를 늘리 것도 성능에 도움이 될 수 있습니다. 단, 유니크 칼럼에 대한 인덱스는 중복 확인 작업으로 체인지 버퍼를 사용할 수 없습니다.
다음은 콜드 스타트에서 테이블 크기가 백만이고 인덱스가 4개인 테이블에 삽입 테스트 이후 체인지 버퍼 관련 상태 값입니다.
mysql> show engine innodb status \g
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 4 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
위 출력을 해석하면 다음과 같습니다.
- Ibuf: size 1 : Insert Buffer에는 현재 1개의 항목이 들어 있음 (버퍼에 쌓인 변경 작업).
- free list len 0: 버퍼에서 사용 가능한 슬롯은 없음.
- seg size 2: 해당 버퍼의 세그먼트 크기가 2 페이지임.
- 0 merges: 아직 디스크로 병합(적용)된 작업이 없음.
merged operations 통해 디스크 작업이 한 번도 없었음을 확인할 수 있습니다.
스토리지 엔진 변경
MySQL 8.0 이후로 대부분 기능이 InnoDB 스토리지 엔진에서 동작합니다. 이번 포스팅은 성능 변화를 보는 것이 목적이기도 하기에 체인지 버퍼 기능을 제공하지 않는 MyISAM 스토리지 엔진을 사용해서 삽입과 동시에 디스크 작업이 일어날 때 변화도 확인해 보겠습니다. 스토리지 엔진만 MyISAM으로 변경할 뿐 모든 과정은 위와 동일합니다.
실험 결과 그래프는 다음과 같습니다.
그래프를 분석하면 다음과 같습니다.
- 테이블 크기가 백만 건 외에는 인덱스 개수가 늘어난다고 해서 실행 시간이 늘어난다고 보기 힘듭니다.
테이블 크기가 백만 건이상이면 인덱스 개수에 따라 성능이 차이 난다고 볼 수 있겠지만 그 이하는 인덱스 개수가 뚜렷하게 성능 저하에 영향을 준다고 하기 합니다. 그리고 InnoDB와 비교해서 MyISAM의 실행시간이 확인히 작습니다.
비록 MyISAM은 체인지 버퍼를 사용하지 않지만 InnoDB보다 실행 시간이 낮은 원인은 다음과 같이 유추할 수 있습니다.
- MyISAM은 트랜잭션 처리 하지 않아 undo/redo 로그를 사용하지 않습니다.
- InnoDB의 체인지 버퍼도 작업 시 비용이 발생합니다. 오히려 MyISAM의 복잡한 지연 구조 없이 디스크에 바로 쓰는 것이 단순하고 빠를 수 있습니다.
InnoDB는 성능보다 안전성을 택한 면이 있고 MyISAM은 속도를 위해 안전성을 포기한 면이 있습니다.
결론
MySQL의 대표적인 두 스토리지 엔진으로 진행한 인덱스 개수와 테이블 크기에 따른 쓰기 성능 실험 결과를 보면 각각의 스토리지 엔진의 특징으로 인덱스로 얻는 읽기의 성능 개선에 비해 쓰기 성능은 저하는 크지 않음을 확인했습니다.
대부분 MySQL 사용자들은 InnoDB를 사용할 테니 체인지 버퍼를 잘 활용한다면 인덱스 적용 시 쓰기 성능 저하를 크게 걱정하지 않고 생성할 수 있을 것입니다.
'데이터베이스' 카테고리의 다른 글
| [도서] Real MySQL : 인덱스 - 질문지 (0) | 2025.05.06 |
|---|---|
| MySQL 스레드 풀 적용 및 성능 비교 (0) | 2025.05.04 |
| 존재하지 않는 레코드에 비관적 락으로 동시성 제어가 가능할까 (0) | 2025.05.02 |
| MySQL에서 GROUP BY 처리는 어떻게 할까? (0) | 2025.04.30 |
| [도서] Real MySQL : InnoDB 스토리지 엔진 아키텍처 - 질문지 (0) | 2025.04.26 |
