| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 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 |
- 성능개선
- 페이징
- Spring
- AWS Lambda
- 이메일 비동기
- SMTP
- 빅맥
- ALB
- ECR
- 비동기
- 동시성 문제
- explain
- Docker
- redis
- 포트원
- Lock
- M오더
- buildx
- scheduling
- varchar
- mysql
- health check
- 결제누락
- 레디스
- url 단축기
- JMeter
- cache miss
- 실행계획
- spring actuator
- 선착순
- Today
- Total
jjunhub
게시글 목록 미리보기 성능 개선하기 - 3편 본문
부제 : EXPLAIN으로 쿼리 실행 계획 점검하기
상황
사실 이건 실제로 겪은 것이라기 보다는 기술 면접에서 면접관님이 다음과 같이 상황을 제시해주시고 병목 지점에 대해서 예상해보라고 하셔서 그에 대한 검증을 위한 분석 리포트라고 볼 수 있다. 제시해주신 상황은 아래와 같았다.
게시글은 테마라는 분류가 존재합니다. ( 테마 : 게시글 = 1 : N )
그리고 각 게시글마다 카테고리라는 분류가 또 존재합니다. ( 카테고리 : 게시글 = 1 : N )
이 때, 게시글이 1000만개가 존재하는 상태라면 어떤 지점에서 병목이 발생할까요?
정해진 답은 없고, 자유롭게 상상해서 답변해주시면 됩니다.
질문에 대한 답변
곰곰히 생각해보니.. 최근에 친절한 SQL 성능 튜닝 이야기에서 읽었던 내용이 떠올랐다. 'index를 사용한다고 해서 항상 Full Scan이 안되는 것은 아니다.' 따라서 index가 걸려있음에도 병목이 발생하는 부분이 있을 것이라 생각하고 아래와 같이 답변을 했다.
이 상황에서 특정 테마의 특정 카테고리에 대한 최근 글들을 불러올 때, 병목이 발생할 것 같습니다. 현재 구조에서는 게시글-테마 관계에 대한 제약 조건과, 게시글-카테고리 관계에 대한 제약 조건으로 인해 두 개의 column에 대해 인덱스가 생겼을 것입니다. 하지만 (테마, 카테고리) 라는 조건에 대해서는 인덱스가 존재하지 않습니다. 따라서 이 조건에 해당하는 쿼리의 경우에는 멀티 인덱스 중에 선행 열인 테마에 대해서만 Index 스캔을 하고, 그 뒤부터는 쭉 스캔하면서 해당되는 카테고리만 선택해오게 됩니다. 이 과정에서 병목이 발생할 수도 있을 것 같습니다. 이를 해결하기 위해서는 (테마, 카테고리) 라는 멀티 인덱스를 추가로 생성하여 준다면 테마, 카테고리 순으로 게시글이 정렬되어 병목 현상을 해결할 수 있을 것이라고 생각합니다.
답변에 대한 검증 방식
이제 답변이 진짜 병목 현상이었는지와 이를 개선할 수 있는가를 확인해야한다. 필요한 것은 다음과 같다.
- MySQL 8.0
그리고 검증 과정은 다음과 같이 수행할 것이다.
- Post 10만개, Category 10개, Theme 10개 생성
- 현재 상황에서 MySQL의 EXPLAIN 기능을 통한 성능 검증
- (테마, 카테고리) 형태로 복합 인덱스를 추가하고 성능 검증
- (테마, 카테고리, 생성일) 형태로 복합 인덱스를 추가하고 성능 검증
데이터베이스 기본 구조 및 데이터 설정
category, theme을 10개씩 생성하고, post를 1000만개 생성하는 sql 문은 다음과 같다.
# category 10개 생성
INSERT INTO category (title, content)
SELECT
CONCAT('Category 제목', numbers.n),
CONCAT('Category 내용', numbers.n)
FROM (
SELECT ROW_NUMBER() OVER () AS n
FROM (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10
) a
) numbers;
# Theme 10개 생성
INSERT INTO theme (title, content)
SELECT
CONCAT('Theme 제목', numbers.n),
CONCAT('Theme 내용', numbers.n)
FROM (
SELECT ROW_NUMBER() OVER () AS n
FROM (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10
) a
) numbers;
# Post 1,000만개 생성
INSERT INTO post (title, content, preview_content, category_id, theme_id, created_at)
SELECT
CONCAT('Post 제목', numbers.n),
CONCAT('Post 내용', numbers.n),
CONCAT('Post 미리보기 내용', numbers.n),
FLOOR(1 + (RAND() * 10)), -- 1~10 범위의 무작위 category_id
FLOOR(1 + (RAND() * 10)), -- 1~10 범위의 무작위 theme_id
NOW() - INTERVAL FLOOR(RAND() * 365) DAY -- 현재 날짜 기준, 과거 1년 내 랜덤 날짜
FROM (
SELECT ROW_NUMBER() OVER () AS n
FROM (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9
) a
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9
) b
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9
) c
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9
) d
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9
) e
) numbers
LIMIT 10000000;이 상태에서는 PK 제약조건으로 인해 생긴 인덱스와 FK 제약조건으로 인해 생겨난 인덱스가 존재한다. MySQL에서는 테이블에 존재하는 인덱스를 다음과 같은 명령어로 확인할 수 있다.
SHOW INDEX FROM post;
이제 이 상태에서 각종 시나리오에서 특정 카테고리, 특정 테마의 최근 게시물 30개를 가져오는 것을 비교할 것이다. 이를 위해 MySql의 EXPLAIN FORMAT=JSON과 EXPLAIN ANALYZE를 통해 실행 결과를 예측하고, 실제 측정까지 할 것이다. 검증할 쿼리는 각각 다음과 같다.
EXPLAIN FORMAT=JSON
SELECT p.id, p.title, p.preview_content, c.title AS category_title, t.title AS theme_title
FROM post AS p
JOIN theme AS t ON p.theme_id = t.id
JOIN category AS c ON p.category_id = c.id
WHERE p.theme_id = 3 AND p.category_id = 3
ORDER BY p.created_at DESC
LIMIT 30;
EXPLAIN ANALYZE
SELECT p.id, p.title, p.preview_content, c.title AS category_title, t.title AS theme_title
FROM post AS p
JOIN theme AS t ON p.theme_id = t.id
JOIN category AS c ON p.category_id = c.id
WHERE p.theme_id = 3 AND p.category_id = 3
ORDER BY p.created_at DESC
LIMIT 30;
시나리오 1. 단일 인덱스만 있을 때
가설
테마까지는 인덱스를 타고 가지만, 카테고리부터는 인덱스를 탈 수 없어서 불필요한 인덱스 범위 스캔을 진행하여 데이터를 가져올 것이다.
EXPLAIN FORMAT=JSON를 통한 예측
아래는 아무런 인덱스를 추가하지 않은 현재 상태에서 EXPLAN FORMAT=JSON을 통한 검증 결과다.
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1804.75"
},
"ordering_operation": {
"using_filesort": true,
"nested_loop": [
{
"table": {
"table_name": "t",
"access_type": "const",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "8",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.10",
"prefix_cost": "0.00",
"data_read_per_join": "336"
},
"used_columns": [
"id",
"title"
]
}
},
{
"table": {
"table_name": "c",
"access_type": "const",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "8",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.10",
"prefix_cost": "0.00",
"data_read_per_join": "336"
},
"used_columns": [
"id",
"title"
]
}
},
{
"table": {
"table_name": "p",
"access_type": "ref",
"possible_keys": [
"category_fk",
"theme_fk"
],
"key": "theme_fk",
"used_key_parts": [
"theme_id"
],
"key_length": "9",
"ref": [
"const"
],
"rows_examined_per_scan": 18040,
"rows_produced_per_join": 3283,
"filtered": "18.20",
"cost_info": {
"read_cost": "0.75",
"eval_cost": "328.33",
"prefix_cost": "1804.75",
"data_read_per_join": "1M"
},
"used_columns": [
"id",
"created_at",
"preview_content",
"title",
"category_id",
"theme_id"
],
"attached_condition": "(`text-test`.`p`.`category_id` = 3)"
}
}
]
}
}
}
위 결과를 요약하면 다음과 같다.
- query cost = 1804.75
- 테마와 카테고리에 대해서 한 번에 PK로 접근하여 가져왔다. ( 성능 굿 )
- post를 가져올 때, 테마를 기준으로 인덱스를 사용하여 theme_id = 3 까지는 분리하였으나, category_id에 대해서 정리된 것이 없어서 Index Range Scan이 동작한다.
- post를 18040개 읽어들여서, 3283개만 사용하여 실제 읽은 것 대비 사용한 것이 약 18프로이다.
EXPLAIN ANALYZE를 통한 측정
아래는 아무런 인덱스를 추가하지 않은 현재 상태에서 EXPLAN ANALYZE을 통한 검증 결과다.
-> Limit: 30 row(s) (cost=865 rows=30) (actual time=36..36 rows=30 loops=1)
-> Sort: p.created_at DESC, limit input to 30 row(s) per chunk (cost=865 rows=3304) (actual time=36..36 rows=30 loops=1)
-> Filter: ((p.category_id = 3) and (p.theme_id = 3)) (cost=865 rows=3304) (actual time=0.065..35.5 rows=979 loops=1)
-> Intersect rows sorted by row ID (cost=865 rows=3304) (actual time=0.0636..35.2 rows=979 loops=1)
-> Index range scan on p using theme_fk over (theme_id = 3) (cost=533e-6..9.61 rows=18040) (actual time=0.0333..7.44 rows=10049 loops=1)
-> Index range scan on p using category_fk over (category_id = 3) (cost=532e-6..9.69 rows=18200) (actual time=0.0169..7.61 rows=10106 loops=1)
EXPLAN ANALYZE를 해석할 때는 가장 안쪽 indent부터 시작하여 바깥 indent로 읽기 시작한다. 같은 indent에서는 위에서부터 아래로 읽으면 된다. 즉 아래의 순서로 읽으면 된다.
[6]-> Limit: 30 row(s) ...
[5] -> Sort: p.created_at ...
[4] -> Filter: ((p.category_id = 3) ...
[3] -> Intersect rows sorted by ...
[1] -> Index range scan on p using theme_fk ...
[2] -> Index range scan on p using category_fk ...
이제 번호 순서대로 한 줄씩 해석해보자.
Index range scan on p using theme_fk over (theme_id = 3) (cost=533e-6..9.61 rows=18040) (actual time=0.0333..7.44 rows=10049 loops=1)[1] 가장 먼저 theme_fk 인덱스를 이용해서 theme_id가 3인 post들을 전부 다 확인했다. 18040개 처리할 것으로 예상됐지만, 실제로는 10049개를 처리하였다. 실행 시간은 쿼리 실행 이후 0.0333ms ~ 7.44ms 시간에 실행되었으며 모든 작업은 1번만 수행됐다.
참고) 위 실행을 통해 아래와 같은 구조로 row ID 목록들을 가져온다고 생각하면 된다.
| row ID | theme_id |
| 1 | 3 |
| 3 | 3 |
| 4 | 3 |
| ... | ... |
-> Index range scan on p using category_fk over (category_id = 3) (cost=532e-6..9.69 rows=18200) (actual time=0.0169..7.61 rows=10106 loops=1)[2] 다음으로 categroy_fk 인덱스를 이용해서 category_id가 3인 post들을 전부 다 확인했다. 18200개 처리할 것으로 예상됐지만, 실제로는 10106개를 처리하였다. 실행 시간은 쿼리 실행 이후 0.0169ms ~ 7.61ms 시간에 실행되었으며 모든 작업은 1번만 수행됐다.
참고) 위 실행을 통해 아래와 같은 구조로 row ID 목록들을 가져온다고 생각하면 된다.
| row ID | category_id |
| 1 | 3 |
| 2 | 3 |
| 4 | 3 |
| ... | ... |
-> Intersect rows sorted by row ID (cost=865 rows=3304) (actual time=0.0636..35.2 rows=979 loops=1)[3] row ID를 기준으로 정렬하여 교집합 연산을 수행한다. 3329개를 처리할 것으로 예상됐지만, 실제로는 990개만 처리하였다. 실행 시간은 쿼리 실행 이후 0.0636ms ~ 35.2ms 시간에 실행되었으며 모든 작업은 1번만 수행됐다. 이를 통해 2번의 Index range scan으로 가져온 값들 중 rowID가 교집합의 형태로 작용하여 필터링을 수행하였다.
참고) 필터링이 적용된다면 아래와 같이 데이터가 존재하게 될 것이다.
| row ID | category_id | theme_id |
| 1 | 3 | 3 |
| 4 | 3 | 3 |
| ... | ... | ... |
-> Filter: ((p.category_id = 3) and (p.theme_id = 3)) (cost=865 rows=3304) (actual time=0.065..35.5 rows=979 loops=1)[4] 3번의 결과에 대해서, 최종적으로 사용할 행을 실제 테이블에서 읽어와서 점검한다. 3304개를 처리할 것으로 예상됐지만, 실제로는 979개만 처리하였다. 실행 시간은 쿼리 실행 이후 0.065ms ~ 35.5ms 시간에 실행되었으며 모든 작업은 1번만 수행됐다.
-> Sort: p.created_at DESC, limit input to 30 row(s) per chunk (cost=865 rows=3304) (actual time=36..36 rows=30 loops=1)[5] 4번의 결과에 대해서 created_at을 기준으로 정렬하여 값을 제공해야하므로, 정렬 연산을 수행한다. 이 과정에서 30 row(s) per chunk를 통해 필요한만큼만 정렬을 수행하였다.
-> Limit: 30 row(s) (cost=870 rows=30) (actual time=15.1..15.1 rows=30 loops=1)[6] 최종적으로 30개의 row에 대해서 처리를 수행한다.
시나리오 1의 결론
- 총 실행 시간은 15.1ms 정도이다.
- 두 개 인덱스를 따로 조회 후 병합하는 index_merge_intersect를 사용하고 있어서 추가 연산이 발생한다.
- 정렬 과정이 포함됐다. 성능에 영향을 줄 수 있다.
-> 두 개의 인덱스를 기준으로 복합 인덱스가 필요하겠다!
시나리오 2. 멀티 인덱스 ( 테마, 카테고리 )가 있을 때
검증해야할 것
테마, 카테고리까지 인덱스를 타고 가서, 불필요한 인덱스 범위 스캔 없이 데이터를 가져온다.
이를 위해서 다음과 같이 멀티 인덱스를 추가해주고, post 테이블의 인덱스를 확인하는 명령어를 입력해보았다.
ALTER TABLE post ADD INDEX idx_category_theme (theme_id, category_id);
SHOW INDEX FROM post;
여기서 신기했던 점은, theme_fk 인덱스가 사라져버렸다는 것이다. 아마 ( theme_id, category_id ) 인덱스가 생겼으니까, theme_id 순으로 자동으로 정렬이 되니 이를 중복으로 여겨 제거한 것으로 추측된다.
EXPLAIN FORMAT=JSON를 통한 예측
복합 인덱스를 추가한 뒤에, 위와 똑같은 쿼리를 통해 실행 계획을 분석하면 다음과 같다.
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "342.65"
},
"ordering_operation": {
"using_filesort": true,
"nested_loop": [
{
"table": {
"table_name": "t",
"access_type": "const",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "8",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.10",
"prefix_cost": "0.00",
"data_read_per_join": "336"
},
"used_columns": [
"id",
"title"
]
}
},
{
"table": {
"table_name": "c",
"access_type": "const",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "8",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.10",
"prefix_cost": "0.00",
"data_read_per_join": "336"
},
"used_columns": [
"id",
"title"
]
}
},
{
"table": {
"table_name": "p",
"access_type": "ref",
"possible_keys": [
"category_fk",
"idx_category_theme"
],
"key": "idx_category_theme",
"used_key_parts": [
"theme_id",
"category_id"
],
"key_length": "18",
"ref": [
"const",
"const"
],
"rows_examined_per_scan": 979,
"rows_produced_per_join": 979,
"filtered": "100.00",
"cost_info": {
"read_cost": "244.75",
"eval_cost": "97.90",
"prefix_cost": "342.65",
"data_read_per_join": "504K"
},
"used_columns": [
"id",
"created_at",
"preview_content",
"title",
"category_id",
"theme_id"
]
}
}
]
}
}
}위 결과를 요약하면 다음과 같다.
- query cost = 342.65 (시나리오 1의 경우 1804.75이었다!)
- 테마와 카테고리에 대해서 한 번에 PK로 접근하여 가져왔다. ( 성능 굿 )
- post를 가져올 때, category_fk 인덱스와 idx_category_theme 복합 인덱스 중 idx_category_theme 복합 인덱스를 선택했다.
- post를 979개 읽어서, 979개를 사용하였다!
EXPLAIN ANALYZE를 통한 측정
-> Limit: 30 row(s) (cost=343 rows=30) (actual time=2.98..2.99 rows=30 loops=1)
-> Sort: p.created_at DESC, limit input to 30 row(s) per chunk (cost=343 rows=979) (actual time=2.98..2.99 rows=30 loops=1)
-> Index lookup on p using idx_category_theme (theme_id = 3, category_id = 3) (cost=343 rows=979) (actual time=0.268..2.79 rows=979 loops=1)위 결과도 마찬가지로, indent가 가장 안쪽에 있는 것부터 실행된다.
-> Index lookup on p using idx_category_theme (theme_id = 3, category_id = 3) (cost=343 rows=979) (actual time=0.268..2.79 rows=979 loops=1)[1] idx_category_theme 인덱스를 이용하여 979개의 row만 읽었다.
참고) Index lookup의 경우에는 정확한 키 값을 기준으로 탐색하는 것이며, Index Range Scan은 연속된 여러 개의 값을 검색하는 방식이다. Index lookUp에서는 리프 노드를 한 번만 접근하여 결과를 산출하지만, Index Range Scan은 리포 노드를 여러 개 접근하여 결과를 산출한다고 생각하면 된다.
-> Sort: p.created_at DESC, limit input to 30 row(s) per chunk (cost=343 rows=979) (actual time=2.98..2.99 rows=30 loops=1)[2] 1번 단계에서 979개에 대해서 정렬을 진행한다.
-> Limit: 30 row(s) (cost=343 rows=30) (actual time=2.98..2.99 rows=30 loops=1)[3] 정렬한 값 중 30개만 가져와서 제공한다.
시나리오 2의 결론
- 총 실행 시간은 2.99ms 정도로 약 5배 빨라졌다.
- 복합 인덱스를 추가하여 index_merge_intersect를 사용하지 않게 변경되었다.
- 정렬 과정이 포함됐다. 성능에 영향을 줄 수 있다.
-> 사실 여기까지만 해도 충분한 성능 개선이라고 생각된다. 하지만 정렬 과정이 sql 실행 중에 항상 포함된다면, 애초부터 정렬된 복합 인덱스를 만들어보는 것은 어떨까?
시나리오 3. 멀티 인덱스 ( 테마, 카테고리, 생성 날짜 )가 있을 때
가설
이제 복합 인덱스를 통해서, 생성 날짜까지 정렬되었으므로 실행 계획 중에 sort가 포함되지 않을 것이다.
이번엔 ( 테마, 카테고리, 생성 날짜)로 된 멀티 인덱스를 추가해주고, post 테이블의 인덱스를 확인하는 명령어를 입력해보았다.
ALTER TABLE post ADD INDEX idx_category_theme_created (theme_id, category_id, created_at DESC);
SHOW INDEX FROM post;
EXPLAIN FORMAT=JSON를 통한 측정
3개 짜리 복합 인덱스를 추가한 뒤에, 위와 똑같은 쿼리를 통해 실행 계획을 분석하면 다음과 같다.
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "342.65"
},
"ordering_operation": {
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "t",
"access_type": "const",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "8",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.10",
"prefix_cost": "0.00",
"data_read_per_join": "336"
},
"used_columns": [
"id",
"title"
]
}
},
{
"table": {
"table_name": "c",
"access_type": "const",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "8",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.10",
"prefix_cost": "0.00",
"data_read_per_join": "336"
},
"used_columns": [
"id",
"title"
]
}
},
{
"table": {
"table_name": "p",
"access_type": "ref",
"possible_keys": [
"category_fk",
"idx_category_theme",
"idx_category_theme_created"
],
"key": "idx_category_theme_created",
"used_key_parts": [
"theme_id",
"category_id"
],
"key_length": "18",
"ref": [
"const",
"const"
],
"rows_examined_per_scan": 979,
"rows_produced_per_join": 979,
"filtered": "100.00",
"cost_info": {
"read_cost": "244.75",
"eval_cost": "97.90",
"prefix_cost": "342.65",
"data_read_per_join": "504K"
},
"used_columns": [
"id",
"created_at",
"preview_content",
"title",
"category_id",
"theme_id"
]
}
}
]
}
}
}위 결과를 요약하면 다음과 같다.
- query cost = 342.65 (시나리오 1의 경우 1804.75이었다!)
- 테마와 카테고리에 대해서 한 번에 PK로 접근하여 가져왔다. ( 성능 굿 )
- post를 가져올 때, category_fk 인덱스, idx_category_theme 복합 인덱스, idx_category_theme_created 중 idx_category_theme_created 복합 인덱스를 선택했다.
- post를 979개 읽어서, 979개를 사용하였다!
EXPLAIN ANALYZE를 통한 측정
-> Limit: 30 row(s) (cost=343 rows=30) (actual time=0.312..0.321 rows=30 loops=1)
-> Index lookup on p using idx_category_theme_created (theme_id = 3, category_id = 3) (cost=343 rows=979) (actual time=0.312..0.318 rows=30 loops=1)앞서 말했던 것처럼, 실행 계획은 안쪽 indent 먼저 따지면 된다.
-> Index lookup on p using idx_category_theme_created (theme_id = 3, category_id = 3) (cost=343 rows=979) (actual time=0.312..0.318 rows=30 loops=1)[1] idx_category_theme_created 인덱스를 통해서, 효율적인 인덱스 탐색을 수행했다.
(시나리오 2에서는 원래는 [1]과 [2] 사이에 sort 관련된 내용이 있었다.)
-> Limit: 30 row(s) (cost=343 rows=30) (actual time=0.312..0.321 rows=30 loops=1)[2] 탐색 결과를 바탕으로 사용자에게 30개 정보를 제공합니다.
시나리오 3의 결론
- 총 실행 시간은 0.321ms 정도로 시나리오 2에 비해 약 9배 빨라졌다.
- 적절한 복합 인덱스를 추가하여 Index lookup이 발생했다.
- 정렬 과정이 사라졌다.
결론
복합 인덱스를 도입하여 15.1ms에서 0.321ms로 약 47배 빠른 결과를 얻을 수 있었다.
느낀점
검증한 결과, 내 답변은 대부분 맞았던 것 같다. 근데 해당 면접은 탈락 됐다! 면까몰. 면접 보면서 새롭게 배우는 것이 많다. 모르는 게 무엇인지 알게 되고, 이렇게 병목에 대해서 고려해볼 지점도 알게 되고,, 면접 보면서, 블로그 주제가 하나 둘 쌓이는 게 참 신기하다.
'Performance Improvement' 카테고리의 다른 글
| 게시글 목록 미리보기 성능 개선하기 - 2편 (0) | 2025.01.18 |
|---|---|
| 게시글 목록 미리보기 성능 개선하기 - 1편 (0) | 2025.01.10 |
| SMTP 환경에서 다수의 이메일 동시 전송하기 (0) | 2025.01.09 |
