데이터베이스 테이블의 인덱스를 활용한다해도 데이터가 계속 쌓이다보면 쿼리 속도가 느려질 수 있다.
이와 관련된 인덱스 조각화(Fragmentation) 문제와 해결 방법 및 이를 지연시킬 수 있는 방법에 대해 정리해보자.
1. 인덱스 조각화(Fragmentation)란?
MySQL(MariaDB)에서 인덱스 페이지가 비효율적으로 배치되는 현상을 말한다.
인덱스 또한 물리적인 페이지로 관리되는데 인덱스로 관리되는 데이터의 양이 많아지다보면 여러 페이지로 나뉘어 관리되게 되고 이로 인해 옵티마이저가 여러 페이지를 찾다보니 속도가 저하되게 된다.
조각화가 발생하는 주요 원인은 다음과 같다.
- INSERT(삽입): 새로운 데이터를 삽입하며 기존 페이지 공간이 부족하면 새로운 페이지가 생성됨
- UPDATE(수정): 데이터 크기가 커지면 원래 있던 페이지에 공간이 부족해지고, 일부 데이터가 다른 페이지로 이동함
- DELETE(삭제): 일부 데이터가 삭제되며 페이지에 빈 공간이 생기게 됨
조각화가 심해지면 검색 속도가 느려지고, 쿼리 성능이 저하된다.
1-1. 인덱스 조각화(Fragmentation) 유형
내부 조각화(Internal Fragmentation)
인덱스 페이지 내부에 사용되지 않는 빈 공간이 생기는 현상이다. 페이지 밀도가 낮아져 더 많은 페이지를 읽게 된다.
주로 다음과 같은 케이스들로 발생하게 된다.
1)레코드 삭제로 인한 빈 공간
1
2
3
4
5
6
7
8
9
10
11
12
13
삭제 전 페이지 상태:
┌─────────────────────────────────────────────┐
│[Header]│ Rec1 │ Rec2 │ Rec3 │ Rec4 │[Slots]│
│ │ 100 │ 200 │ 300 │ 400 │ │
└─────────────────────────────────────────────┘
페이지 밀도: 100%
삭제 후 (Rec2, Rec3 삭제):
┌─────────────────────────────────────────────┐
│[Header]│ Rec1 │ ---- │ ---- │ Rec4 │[Slots]│
│ │ 100 │ │ │ 400 │ │
└─────────────────────────────────────────────┘
페이지 밀도: 50%
2) 가변 길이 레코드 업데이트
1
2
3
4
5
6
7
업데이트 전:
Record1: [ID:100, Name:"Kim"] (20 bytes)
Record2: [ID:200, Name:"Lee"] (20 bytes)
업데이트 후:
Record1: [ID:100, Name:"Kim"] (20 bytes)
Record2: [ID:200, Name:"A"] (15 bytes) ← 5 bytes 빈 공간 발생
3. 페이지 분할 후 밀도 저하
1
2
3
4
5
6
7
8
9
10
11
12
분할 전 (100% 가득):
┌─────────────────────────────────────────────┐
│ 1│ 5│ 8│12│15│18│22│25│28│30│35│38│42│45│ │
└─────────────────────────────────────────────┘
값 20 삽입 시 분할:
Page A: ┌──────────────────────────┐
│ 1│ 5│ 8│12│15│18│ │ ← 33% 빈 공간
└──────────────────────────┘
Page B: ┌──────────────────────────┐
│20│22│25│28│30│35│38│42│45│ ← 11% 빈 공간
외부 조각화(External Fragmentation)
논리적으로 연속된 인덱스 페이지들이 물리적으로는 흩어져 저장되는 현상이다. 순차 읽기 성능이 크게 저하된다.
예를 들어, 처음에는 다음과 같이 연속된 물리적 위치에 저장된 인덱스가 있다고 가정해보자.
1
2
논리적 순서: [1-10] [11-20] [21-30] [31-40]
물리적 위치: Page1 Page2 Page3 Page4
시간이 지나면서 다음과 같은 변화가 일어나게 된다고 가정해보자.
- 페이지 분할 발생: 값 15가 삽입되어 Page2가 분할되면, 새로운 페이지가 물리적으로 멀리 떨어진 위치에 할당
- 삭제 후 재사용: 기존 페이지가 삭제되고 그 공간이 다른 용도로 사용된 후, 나중에 인덱스 페이지로 재할당
결과적으로 다음과 같은 상황이 된다.
1
2
논리적 순서: [1-10] [11-14] [15-20] [21-30] [31-40]
물리적 위치: Page1 Page2 Page47 Page3 Page4
이는 여러 디스크 I/O 작업을 필요로 하게 되어 성능 저하를 초래할 수 있다.
1-2. 인덱스 조각화(Fragmentation) 확인 방법
MySQL에서는 information_schema.tables 또는 SHOW TABLE STATUS 명령어를 사용하여 인덱스 조각화를 확인할 수 있다.
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 1. SHOW TABLE STATUS 명령어를 통해 확인
SHOW TABLE STATUS LIKE 'orders';
-- 2. information_schema.tables 를 통해 확인
SELECT
table_name,
ROUND((data_free / (data_length + index_length + data_free)) * 100, 2) AS fragmentation_percentage,
engine,
data_length,
index_length,
data_free
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name = 'orders';
information_schema.tables에서 인덱스 Fragmentation 퍼센티지를 확인할때 사용하는 각 컬럼들은 다음과 같다.
- data_length: 테이블의 실제 데이터가 차지하는 바이트 수, 즉 테이블에 저장된 모든 행(row)의 데이터 크기
- index_length: 테이블의 인덱스가 차지하는 바이트 수
- data_free: 할당되었지만 사용되지 않는 바이트 수, 삭제 또는 업데이트로 인해 생긴 빈 공간
즉, 분자(data_free)는 낭비되고 있는 공간의 크기를 나타내고 분모(data_length + index_length + data_free)는 테이블이 실제로 사용하고 있는 전체 물리적인 공간을 나타내게 된다.
간단하게, data_free 값이 크면 조각화가 많이 발생한 상태라고도 볼 수 있다.
1-3. 인덱스 조각화 확인 예제
아래 쿼리를 실행하여 orders 테이블을 생성후 20,000개의 데이터를 프로시져를 통해 삽입해보자.
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
CREATE TABLE orders
(
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
user_name VARCHAR(200) NOT NULL,
product_id INT NOT NULL,
order_date DATE NOT NULL,
order_status ENUM ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
amount DECIMAL(10, 2) NOT NULL,
payment_method VARCHAR(20) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
DROP PROCEDURE IF EXISTS InsertOrders;
DELIMITER $$
CREATE PROCEDURE InsertOrders()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE random_user_id INT;
DECLARE random_user_name VARCHAR(200);
DECLARE random_product_id INT;
DECLARE random_order_date DATE;
DECLARE random_order_status ENUM('pending', 'confirmed', 'shipped', 'delivered', 'cancelled');
DECLARE random_amount DECIMAL(10, 2);
DECLARE random_payment_method VARCHAR(20);
DECLARE status_rand INT;
DECLARE payment_rand INT;
WHILE i <= 20000 DO
-- 랜덤 값 생성
SET random_user_id = FLOOR(1 + RAND() * 1000); -- 1~1000 사이 user_id
SET random_user_name = concat('Name', random_user_id);
SET random_product_id = FLOOR(1 + RAND() * 500); -- 1~500 사이 product_id
SET random_order_date = DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 365) DAY); -- 최근 1년 이내
SET random_amount = ROUND(10 + RAND() * 990, 2); -- 10.00 ~ 1000.00 사이 금액
-- 주문 상태 랜덤 선택
SET status_rand = FLOOR(1 + RAND() * 5);
CASE status_rand
WHEN 1 THEN SET random_order_status = 'pending';
WHEN 2 THEN SET random_order_status = 'confirmed';
WHEN 3 THEN SET random_order_status = 'shipped';
WHEN 4 THEN SET random_order_status = 'delivered';
WHEN 5 THEN SET random_order_status = 'cancelled';
END CASE;
-- 결제 방법 랜덤 선택
SET payment_rand = FLOOR(1 + RAND() * 4);
CASE payment_rand
WHEN 1 THEN SET random_payment_method = 'credit_card';
WHEN 2 THEN SET random_payment_method = 'debit_card';
WHEN 3 THEN SET random_payment_method = 'paypal';
WHEN 4 THEN SET random_payment_method = 'bank_transfer';
END CASE;
-- 데이터 삽입
INSERT INTO orders (user_id, user_name, product_id, order_date, order_status, amount, payment_method)
VALUES (random_user_id, random_user_name, random_product_id, random_order_date, random_order_status, random_amount, random_payment_method);
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL InsertOrders();
InnoDB 엔진의 기본 페이지 사이즈는 16KB 크기로 고정되어 있다. 페이지가 가득 찬 상태에서 새로운 레코드를 삽입해야 할 때 페이지 분할이 발생하게 된다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
기본 50:50 분할 방식
분할 전 상황:
┌─────────────────────────────────────────────┐
│ Page 100 (16KB 가득 참) │
│ [Rec1][Rec2][Rec3]...[Rec200] ← 여기에 새 레코드 삽입 필요
└─────────────────────────────────────────────┘
50:50 분할 후:
┌─────────────────────────┐ ┌─────────────────────────┐
│ Page 100 (8KB 사용) │ │ Page 새번호 (8KB 사용) │
│ [Rec1]...[Rec100] │ │ [Rec101]...[Rec200] │
│ ← 50% 데이터 │ │ ← 50% 데이터 │
│ → 50% 빈 공간 │ │ → 50% 빈 공간 │
└─────────────────────────┘ └─────────────────────────┘
즉, 위 20,000개의 데이터를 삽입하는 프로시저를 수행하게 되면 InnoDB의 페이지 분할 패턴에 의해 페이지 내부 빈 공간이 생기게 된다.
따라서 fragmentation_percentage 값을 통해 72% 조각화 현상이 발생한 것을 확인할 수 있으며 data_free 값을 통해 4,194,304 바이트만큼의 빈 공간이 존재하는 것을 확인할 수 있다.
1-4. 인덱스 조각화(Fragmentation) 해결 방법
조각화가 심한 경우 OPTIMIZE TABLE(테이블 최적화) 또는 ANALYZE TABLE(인덱스 분석)을 실행하여 해결할 수 있다.
두 SQL 명령어와 관련된 더 상세한 내용은 아래 포스팅에 잘 정리되어 있다😀
- OPTIMIZE TABLE: https://monkeybusiness.tistory.com/806
- ANALYZE TABLE: https://monkeybusiness.tistory.com/804
- OPTIMIZE/ANALYZE TABLE: https://myinfrabox.tistory.com/145
테이블 최적화(OPTIMIZE TABLE)
MySQL에서는 OPTIMIZE TABLE 명령어를 실행하면 조각화를 제거하고 공간을 재사용할 수 있다.
1
OPTIMIZE TABLE orders;
인덱스 분석(ANALYZE TABLE)
MySQL에서는 ANALYZE TABLE 명령어를 실행하면 테이블의 인덱스 키 분포를 분석하고, 그 결과를 데이터베이스 통계로 저장하는 데 사용된다. 이 통계 정보는 MySQL의 쿼리 옵티마이저가 최적의 실행 계획을 결정하는 데 활용된다.
1
ANALYZE TABLE orders;
키 분포는 테이블의 각 컬럼(특히 인덱스가 걸린 컬럼)에서 값들의 다양성과 빈도를 나타낸다.
예를 들어, user 테이블이 있다고 가정해보자.
1
2
3
4
5
6
CREATE TABLE users (
id INT PRIMARY KEY,
gender VARCHAR(10),
age INT,
city VARCHAR(50)
);
키 분포 분석의 핵심 요소는 다음과 같다.
카디널리티(Cardinality): 고유한 값의 개수
- gender 컬럼: ‘M’, ‘F’ 두 값만 있다면 카디널리티 = 2 (낮음)
- id 컬럼: 100만 개 행에 100만 개 고유값이면 카디널리티 = 1,000,000 (높음)
- city 컬럼: 50개 도시가 있다면 카디널리티 = 50 (중간)
선택도(Selectivity): 전체 행 중에서 특정 값이 차지하는 비율
- gender = ‘M’인 행의 선택도 = 60%
- city = ‘서울’인 행의 선택도 = 20%
MySQL 옵티마이저는 위 분포 정보를 바탕으로 쿼리 실행 계획을 세운다.
1
SELECT * FROM users WHERE gender = 'M' AND city = '서울';
위 쿼리에서 옵티마이저는
- gender = ‘M’이 전체중 60% 행을 반환할 것으로 예상
- city = ‘서울’이 전체중 20% 행을 반환할 것으로 예상 두 조건을 결합하면 약 12%(0.6 x 0.2) 행이 결과로 나올 것으로 추정하는식으로 최적화한다.
1-5. 인덱스 최적화후 조각화 재확인
페이지 최적화후 fragmentation_percentage 값이 44% 로 줄어든 것을 확인할 수 있다. 또한, data_free 값을 통해 2,097,152 바이트로 빈 공간이 줄어든 것을 확인할 수 있다. 이를 통해 조각화가 해결된 것으로 볼 수 있다.
1-6. 정리
- MySQL에서 INSERT, UPDATE, DELETE 작업이 반복되면 인덱스 조각화가 발생함
- 조각화가 심해지면 쿼리 성능이 저하됨
- 조각화 확인은 SHOW TABLE STATUS 또는 information_schema.tables을 사용하여 수행함
- 조각화가 심하면 OPTIMIZE TABLE을 실행하여 테이블을 최적화해야 함
- 인덱스 통계를 최신 상태로 유지하려면 ANALYZE TABLE을 실행해야 함
MySQL에서 인덱스 유지보수를 정기적으로 수행하면 데이터베이스 성능을 최적화할 수 있다.
참고: https://dev.mysql.com/doc/refman/8.4/en/sorted-index-builds.html
2. Fill Factor란?
Fill Factor는 MySQL에서 새로운 인덱스를 생성할때 인덱스 페이지를 몇 퍼센트까지 채울지 지정하는 설정이다. 이를 통해 인덱스 페이지가 완전히 채워지는 것을 방지하고, 추가적인 삽입(INSERT)과 업데이트(UPDATE)시 공간을 확보해놓는 개념이다. Fill Factor를 사용하면 인덱스 Fragmentation을 지연시킬 수 있다.
2-1. Fill Factor 동작 원리
1. 인덱스 페이지 여유 공간 확보
- 예를 들어 Fill Factor를 90%로 설정하면, 인덱스 페이지가 최대 90%까지만 데이터가 채워진다.
- 나머지 10%는 삽입 또는 업데이트 시 사용할 수 있도록 비워둔다.
2. 페이지 단위 예시
- 루트 페이지, 중간 페이지, 리프 페이지로 구성된 인덱스 구조에서 각 페이지는 90% 까지만 채워진다.
- 새로운 레코드가 삽입되거나, 기존 레코드가 업데이트되어 크기가 증가해도 남은 10% 공간에 데이터가 저장될 수 있다.
- 이를 통해 새로운 페이지 생성 빈도를 줄여 조각화가 지연된다.
MySQL 8.0.36 및 MariaDB 10.6.0 이상부터 innodb_fill_factor 시스템 변수를 사용하여 Fill Factor를 조정할 수 있다.
- Fill Factor 기본값: 100 (페이지를 가득 채움)
- Fill Factor 조정 가능 범위: 10 ~ 100
2-2. Fill Factor 필요성 예시
1. 인덱스 재구축(Rebuild) 작업의 비용
- 인덱스 재구축(Index Rebuild) 작업은 CPU, 메모리, 디스크 I/O를 많이 사용하여 MySQL(MariaDB) 서버에 부담을 줌.
- 피크 타임(저녁 시간)에는 Rebuild 작업을 수행하기 어려우므로, Fill Factor를 설정하여 조각화를 지연시킬 필요가 있음.
2. 인덱스 조각화 문제
- DBA가 매일 밤 인덱스를 재구축(Rebuild) 하면 다음날 아침부터 저녁까지 쿼리 성능이 원활하다.
- 그러나 저녁 피크 시간대에 삽입(INSERT)과 업데이트(UPDATE)가 증가하면서 인덱스 조각화가 발생하고, 쿼리 성능이 저하된다.
- 저녁 피크 시간대에 인덱스를 재구축하려고 하면, 이미 쿼리 성능이 느려 부하가 발생하는 상태에서 MySQL(MariaDB) 서버에 CPU/메모리/디스크 I/O로 인해 추가적인 부하를 주는 Rebuild 작업이 어려워짐.
2-3. MySQL(MariaDB)에서 Fill Factor 설정 및 활용 방법
1. Fill Factor 값 확인
1
SHOW VARIABLES LIKE 'innodb_fill_factor';
2. Fill Factor 값 설정
1
SET GLOBAL innodb_fill_factor = 80;
Fill Factor 값을 변경해도 기존 인덱스에 대해선 즉시 반영되지 않는다. 기존 인덱스에도 적용하려면 인덱스를 재생성하거나 테이블 최적화(OPTIMIZE TABLE)를 수행해야 한다.
1
2
3
4
5
6
-- 인덱스 재생성
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name ADD INDEX index_name (column_name);
-- 테이블 최적화
OPTIMIZE TABLE table_name;