부하테스트를 진행.(VUs 1-50 10분간 점진적 증가)
하지만, 초기부터 TPS 가 들쑥날쑥하고, latency가 1초, 10초, ... 30초까지 증가하는 현상이 발생
데이터베이스 부하가 많이 나오는 것(CPU 사용률 72% 초과)을 보아 데이터베이스쪽이 병목구간으로 의심됨
실제 API 를 통해 한번 조회 요청을 해보기
단순히 500번 게시판의 20번 페이지를 조회하는데, 1.55s 만에 응답이 왔음.
여기서 더 페이지 숫자를 늘려서 300번 페이지를 조회해보면?
----------------------------------------------------------------------------------------------------
2025-06-16T04:05:58.072Z INFO 1 --- [board-system] [io-8080-exec-36] p6spy :
Execute DML :
WITH cte AS ( SELECT
a.article_id
FROM
articles AS a
WHERE
a.board_id = 500
LIMIT
15001 ) SELECT
COUNT(*)
FROM
cte
Execution Time: 32372 ms
----------------------------------------------------------------------------------------------------
2025-06-16T04:06:40.046Z INFO 1 --- [board-system] [io-8080-exec-36] p6spy :
Execute DML :
select
ja1_0.article_id,
ja1_0.title,
ja1_0.board_id,
jb1_0.name,
jb1_0.slug,
ja1_0.article_category_id,
jac1_0.name,
jac1_0.slug,
ja1_0.writer_id,
ja1_0.writer_nickname,
coalesce(jacc1_0.comment_count, 0),
coalesce(jalc1_0.like_count, 0),
coalesce(jadc1_0.dislike_count, 0),
ja1_0.created_at
from
articles ja1_0
join
boards jb1_0
on jb1_0.board_id=ja1_0.board_id
join
article_categories jac1_0
on jac1_0.article_category_id=ja1_0.article_category_id
left join
article_comment_counts jacc1_0
on jacc1_0.article_id=ja1_0.article_id
left join
article_like_counts jalc1_0
on jalc1_0.article_id=ja1_0.article_id
left join
article_dislike_counts jadc1_0
on jadc1_0.article_id=ja1_0.article_id
where
ja1_0.board_id=500
order by
ja1_0.article_id desc
limit
14950, 50
Execution Time: 41972 ms
----------------------------------------------------------------------------------------------------
로그를 확인해보자.
게시글 수 조회 쿼리 : 32372 ms (약 32.37초)
게시글 조회 쿼리 : 41972 ms (약 41.97초)
이건 offset 값이 너무 커져서 생긴 문제이기도 한데, 그래도 전체적으로 쿼리 성능을 의심해봐야한다.
mysql> WITH cte AS (SELECT a .article_id
- > FROM articles AS a
- > WHERE a .board_id = 500
- > LIMIT 1001 )
- > SELECT COUNT (* )
- > FROM cte;
+ -- --------+COUNT (* ) |
+ -- --------+1001 |
+ -- --------+1 row in set (1 .12 sec)
1001건(20페이지 + 1개 게시글)까지 게시글 수를 조회해보면, 실행 쿼리 속도는 약 1120ms 정도 소요된다.
단순히 이것만 놓고 보면 작게 느껴질(?) 수도 있다.
그러나 초당 수 십, 수백 사용자가 들어왔을 때 이렇게 쿼리 시간이 길어지는 것은 한 트랜잭션이 커넥션을 오래 차지하게 됨으로서, 성능상 부담이 될 수 밖에 없다.
mysql> WITH cte AS (SELECT a .article_id
- > FROM articles AS a
- > WHERE a .board_id = 500
- > LIMIT 15001 )
- > SELECT COUNT (* )
- > FROM cte;
+ -- --------+COUNT (* ) |
+ -- --------+15001 |
+ -- --------+1 row in set (32 .02 sec)
15001 번쨰 게시글까지 게시글수를 조회해보면 조회 속도가 32020ms 정도까지 소요된다.(300페이지까지 게시글 수 조회)
페이지 번호가 높아질 수록 이 성능 저하는 커질 것이다.
offset 문제도 있을 수 있지만, 일단 전체적인 쿼리 동작이 어떤 식으로 되는지 볼 필요가 있다.
mysql> explain
- > WITH cte AS (SELECT a .article_id
- > FROM articles AS a
- > WHERE a .board_id = 500
- > LIMIT 15001 )
- > SELECT COUNT (* )
- > FROM cte;
+ -- --+-------------+------------+------------+------+---------------+------+---------+------+----------+----------+-------------++ -- --+-------------+------------+------------+------+---------------+------+---------+------+----------+----------+-------------+1 | PRIMARY | < derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 15001 | 100 .00 | NULL |
| 2 | DERIVED | a | NULL | ALL | NULL | NULL | NULL | NULL | 23839172 | 10 .00 | Using where |
+ -- --+-------------+------------+------------+------+---------------+------+---------+------+----------+----------+-------------+2 rows in set , 1 warning (0 .02 sec)
실행계획을 확인해보자.
drived table(with 절) 의 조회 방식을 보면
테이블 풀스캔(type=All) 을 통해 진행되고 있는 것을 볼 수 있으며, filtered=10.00 으로 조회 과정에서 필터링에 시간을 많이 잡아먹는 것 을 볼 수 있다.
rows : 필터 조건과 인덱스 유무를 고려하여 MySQL이 예측한 평균적인 처리량 -> 거의 테이블 전체를 확인하고 있음 -> 매우 비효율적
mysql> explain analyze
- > WITH cte AS (SELECT a .article_id
- > FROM articles AS a
- > WHERE a .board_id = 500
- > LIMIT 15001 )
- > SELECT COUNT (* )
- > FROM cte;
+ -- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------++ -- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+- > Aggregate: count (0 ) (cost= 2 .55e+ 6 ..2 .55e+ 6 rows= 1 ) (actual time = 32019 ..32019 rows= 1 loops= 1 )
- > Table scan on cte (cost= 2 .54e+ 6 ..2 .54e+ 6 rows= 15001 ) (actual time = 32016 ..32018 rows= 15001 loops= 1 )
- > Materialize CTE cte (cost= 2 .54e+ 6 ..2 .54e+ 6 rows= 15001 ) (actual time = 32016 ..32016 rows= 15001 loops= 1 )
- > Limit : 15001 row(s) (cost= 2 .54e+ 6 rows= 15001 ) (actual time = 0 .215 ..31996 rows= 15001 loops= 1 )
- > Filter: (a .board_id = 500 ) (cost= 2 .54e+ 6 rows= 2 .38e+ 6 ) (actual time = 0 .215 ..31991 rows= 15001 loops= 1 )
- > Table scan on a (cost= 2 .54e+ 6 rows= 23 .8e+ 6 ) (actual time = 0 .0367 ..31085 rows= 14 .8e+ 6 loops= 1 )
|
+ -- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (32 .05 sec)
explain analyze 를 통해 실행 흐름을 확인해보면, 게시글 테이블을 풀스캔, 필터링하는데만 거의 31996ms 정도 소요하고 있는 것을 볼 수 있다.
mysql> select a .article_id ,
- > a .title ,
- > a .board_id ,
- > b .name ,
- > b .slug ,
- > a .article_category_id ,
- > ac .name ,
- > ac .slug ,
- > a .writer_id ,
- > a .writer_nickname ,
- > coalesce(acc .comment_count , 0 ),
- > coalesce(alc .like_count , 0 ),
- > coalesce(adc .dislike_count , 0 ),
- > a .created_at
- > from articles a
- > join
- > boards b
- > on b .board_id = a .board_id
- > join
- > article_categories ac
- > on ac .article_category_id = a .article_category_id
- > left join
- > article_comment_counts acc
- > on acc .article_id = a .article_id
- > left join
- > article_like_counts alc
- > on alc .article_id = a .article_id
- > left join
- > article_dislike_counts adc
- > on adc .article_id = a .article_id
- > where a .board_id = 500
- > order by a .article_id desc
- > limit 50 offset 950 ;
+ -- ----------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+acc .comment_count , 0 ) | coalesce(alc .like_count , 0 ) | coalesce(adc .dislike_count , 0 ) | created_at |
+ -- ----------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+23080717 | title23080717 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 1751748 | user01751748 | 0 | 0 | 0 | 2024 - 09 - 24 03 :18 :37 |
| 23080707 | title23080707 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 4929538 | user04929538 | 0 | 0 | 1 | 2024 - 09 - 24 03 :18 :27 |
| 23077000 | title23077000 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 1849600 | user01849600 | 0 | 0 | 1 | 2024 - 09 - 24 02 :16 :40 |
| 23075774 | title23075774 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 4759095 | user04759095 | 0 | 2 | 1 | 2024 - 09 - 24 01 :56 :14 |
| 23075659 | title23075659 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4417094 | user04417094 | 1 | 2 | 1 | 2024 - 09 - 24 01 :54 :19 |
| 23075107 | title23075107 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 10415294 | user10415294 | 0 | 0 | 0 | 2024 - 09 - 24 01 :45 :07 |
| 23072801 | title23072801 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 1848164 | user01848164 | 0 | 2 | 2 | 2024 - 09 - 24 01 :06 :41 |
| 23072321 | title23072321 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 7152434 | user07152434 | 0 | 0 | 1 | 2024 - 09 - 24 00 :58 :41 |
| 23072251 | title23072251 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 2374906 | user02374906 | 1 | 0 | 0 | 2024 - 09 - 24 00 :57 :31 |
| 23071668 | title23071668 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 265932 | user00265932 | 1 | 1 | 0 | 2024 - 09 - 24 00 :47 :48 |
| 23071481 | title23071481 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 2964706 | user02964706 | 0 | 0 | 0 | 2024 - 09 - 24 00 :44 :41 |
| 23068708 | title23068708 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 3008032 | user03008032 | 2 | 0 | 0 | 2024 - 09 - 23 23 :58 :28 |
| 23068642 | title23068642 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 3052449 | user03052449 | 1 | 0 | 0 | 2024 - 09 - 23 23 :57 :22 |
| 23068163 | title23068163 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 10226348 | user10226348 | 0 | 1 | 2 | 2024 - 09 - 23 23 :49 :23 |
| 23067130 | title23067130 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 10594333 | user10594333 | 0 | 0 | 0 | 2024 - 09 - 23 23 :32 :10 |
| 23066142 | title23066142 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 250492 | user00250492 | 0 | 2 | 0 | 2024 - 09 - 23 23 :15 :42 |
| 23063360 | title23063360 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4356722 | user04356722 | 0 | 0 | 0 | 2024 - 09 - 23 22 :29 :20 |
| 23063263 | title23063263 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 1412300 | user01412300 | 0 | 0 | 0 | 2024 - 09 - 23 22 :27 :43 |
| 23060851 | title23060851 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 7266297 | user07266297 | 0 | 0 | 2 | 2024 - 09 - 23 21 :47 :31 |
| 23059786 | title23059786 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 8065671 | user08065671 | 0 | 0 | 1 | 2024 - 09 - 23 21 :29 :46 |
| 23059630 | title23059630 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4509268 | user04509268 | 0 | 0 | 0 | 2024 - 09 - 23 21 :27 :10 |
| 23056551 | title23056551 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 7987783 | user07987783 | 0 | 2 | 1 | 2024 - 09 - 23 20 :35 :51 |
| 23055245 | title23055245 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 3059304 | user03059304 | 0 | 0 | 1 | 2024 - 09 - 23 20 :14 :05 |
| 23054708 | title23054708 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 1393597 | user01393597 | 0 | 0 | 2 | 2024 - 09 - 23 20 :05 :08 |
| 23054495 | title23054495 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4203220 | user04203220 | 1 | 0 | 2 | 2024 - 09 - 23 20 :01 :35 |
| 23053875 | title23053875 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4122696 | user04122696 | 0 | 0 | 1 | 2024 - 09 - 23 19 :51 :15 |
| 23053846 | title23053846 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 9400051 | user09400051 | 1 | 1 | 0 | 2024 - 09 - 23 19 :50 :46 |
| 23053054 | title23053054 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 3507777 | user03507777 | 1 | 0 | 0 | 2024 - 09 - 23 19 :37 :34 |
| 23052720 | title23052720 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 8345481 | user08345481 | 0 | 0 | 1 | 2024 - 09 - 23 19 :32 :00 |
| 23052235 | title23052235 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 6012482 | user06012482 | 1 | 0 | 1 | 2024 - 09 - 23 19 :23 :55 |
| 23050297 | title23050297 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 8190055 | user08190055 | 0 | 0 | 1 | 2024 - 09 - 23 18 :51 :37 |
| 23048367 | title23048367 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 8023377 | user08023377 | 1 | 2 | 2 | 2024 - 09 - 23 18 :19 :27 |
| 23046975 | title23046975 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 6575470 | user06575470 | 0 | 0 | 0 | 2024 - 09 - 23 17 :56 :15 |
| 23046924 | title23046924 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 3696167 | user03696167 | 2 | 2 | 0 | 2024 - 09 - 23 17 :55 :24 |
| 23046374 | title23046374 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 4506931 | user04506931 | 0 | 2 | 1 | 2024 - 09 - 23 17 :46 :14 |
| 23045752 | title23045752 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 3827561 | user03827561 | 0 | 0 | 0 | 2024 - 09 - 23 17 :35 :52 |
| 23044940 | title23044940 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 82760 | user00082760 | 0 | 2 | 0 | 2024 - 09 - 23 17 :22 :20 |
| 23044494 | title23044494 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 6042253 | user06042253 | 0 | 0 | 1 | 2024 - 09 - 23 17 :14 :54 |
| 23042548 | title23042548 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 2000762 | user02000762 | 1 | 0 | 2 | 2024 - 09 - 23 16 :42 :28 |
| 23042389 | title23042389 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 5885393 | user05885393 | 1 | 0 | 0 | 2024 - 09 - 23 16 :39 :49 |
| 23041587 | title23041587 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 5549905 | user05549905 | 1 | 0 | 1 | 2024 - 09 - 23 16 :26 :27 |
| 23041411 | title23041411 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 2505354 | user02505354 | 1 | 2 | 0 | 2024 - 09 - 23 16 :23 :31 |
| 23039863 | title23039863 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 1214389 | user01214389 | 1 | 0 | 2 | 2024 - 09 - 23 15 :57 :43 |
| 23039469 | title23039469 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 2973998 | user02973998 | 0 | 1 | 0 | 2024 - 09 - 23 15 :51 :09 |
| 23037764 | title23037764 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 7301362 | user07301362 | 0 | 0 | 0 | 2024 - 09 - 23 15 :22 :44 |
| 23037410 | title23037410 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 8489062 | user08489062 | 1 | 0 | 0 | 2024 - 09 - 23 15 :16 :50 |
| 23037030 | title23037030 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 482543 | user00482543 | 0 | 0 | 0 | 2024 - 09 - 23 15 :10 :30 |
| 23034781 | title23034781 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 11789921 | user11789921 | 1 | 1 | 0 | 2024 - 09 - 23 14 :33 :01 |
| 23034388 | title23034388 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 2011291 | user02011291 | 1 | 0 | 0 | 2024 - 09 - 23 14 :26 :28 |
| 23033958 | title23033958 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 7751880 | user07751880 | 0 | 2 | 0 | 2024 - 09 - 23 14 :19 :18 |
+ -- ----------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+50 rows in set (1 .67 sec)
20번 페이지를 조회하는데 1670ms 정도 소요된다.
mysql> select a .article_id ,
- > a .title ,
- > a .board_id ,
- > b .name ,
- > b .slug ,
- > a .article_category_id ,
- > ac .name ,
- > ac .slug ,
- > a .writer_id ,
- > a .writer_nickname ,
- > coalesce(acc .comment_count , 0 ),
- > coalesce(alc .like_count , 0 ),
- > coalesce(adc .dislike_count , 0 ),
- > a .created_at
- > from articles a
- > join
- > boards b
- > on b .board_id = a .board_id
- > join
- > article_categories ac
- > on ac .article_category_id = a .article_category_id
- > left join
- > article_comment_counts acc
- > on acc .article_id = a .article_id
- > left join
- > article_like_counts alc
- > on alc .article_id = a .article_id
- > left join
- > article_dislike_counts adc
- > on adc .article_id = a .article_id
- > where a .board_id = 500
- > order by a .article_id desc
- > limit 50 offset 14950 ;
+ -- ----------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+acc .comment_count , 0 ) | coalesce(alc .like_count , 0 ) | coalesce(adc .dislike_count , 0 ) | created_at |
+ -- ----------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+9261611 | title09261611 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 2297565 | user02297565 | 1 | 0 | 2 | 2024 - 04 - 17 04 :40 :11 |
| 9261269 | title09261269 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 4352823 | user04352823 | 1 | 0 | 0 | 2024 - 04 - 17 04 :34 :29 |
| 9259361 | title09259361 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 4045943 | user04045943 | 0 | 0 | 0 | 2024 - 04 - 17 04 :02 :41 |
| 9258235 | title09258235 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 6248844 | user06248844 | 0 | 0 | 0 | 2024 - 04 - 17 03 :43 :55 |
| 9258155 | title09258155 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 1614575 | user01614575 | 1 | 1 | 0 | 2024 - 04 - 17 03 :42 :35 |
| 9257605 | title09257605 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 10738688 | user10738688 | 0 | 0 | 2 | 2024 - 04 - 17 03 :33 :25 |
| 9257470 | title09257470 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 8398993 | user08398993 | 0 | 0 | 2 | 2024 - 04 - 17 03 :31 :10 |
| 9257138 | title09257138 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 6804379 | user06804379 | 0 | 1 | 0 | 2024 - 04 - 17 03 :25 :38 |
| 9256610 | title09256610 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 6509521 | user06509521 | 0 | 0 | 0 | 2024 - 04 - 17 03 :16 :50 |
| 9254303 | title09254303 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 5508880 | user05508880 | 1 | 2 | 0 | 2024 - 04 - 17 02 :38 :23 |
| 9254052 | title09254052 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 11554158 | user11554158 | 0 | 2 | 0 | 2024 - 04 - 17 02 :34 :12 |
| 9252556 | title09252556 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 3742044 | user03742044 | 0 | 0 | 2 | 2024 - 04 - 17 02 :09 :16 |
| 9251178 | title09251178 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4282349 | user04282349 | 0 | 0 | 2 | 2024 - 04 - 17 01 :46 :18 |
| 9250272 | title09250272 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 9700401 | user09700401 | 0 | 0 | 0 | 2024 - 04 - 17 01 :31 :12 |
| 9248636 | title09248636 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 9126024 | user09126024 | 1 | 2 | 2 | 2024 - 04 - 17 01 :03 :56 |
| 9247641 | title09247641 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 6650763 | user06650763 | 1 | 0 | 1 | 2024 - 04 - 17 00 :47 :21 |
| 9247180 | title09247180 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 1085136 | user01085136 | 1 | 2 | 0 | 2024 - 04 - 17 00 :39 :40 |
| 9245185 | title09245185 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 8850340 | user08850340 | 0 | 1 | 0 | 2024 - 04 - 17 00 :06 :25 |
| 9244725 | title09244725 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 7962302 | user07962302 | 0 | 1 | 1 | 2024 - 04 - 16 23 :58 :45 |
| 9242701 | title09242701 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 9084609 | user09084609 | 0 | 0 | 0 | 2024 - 04 - 16 23 :25 :01 |
| 9242019 | title09242019 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 3640956 | user03640956 | 0 | 2 | 0 | 2024 - 04 - 16 23 :13 :39 |
| 9241855 | title09241855 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 1527734 | user01527734 | 0 | 0 | 0 | 2024 - 04 - 16 23 :10 :55 |
| 9240702 | title09240702 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 1025406 | user01025406 | 0 | 0 | 0 | 2024 - 04 - 16 22 :51 :42 |
| 9240542 | title09240542 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 7967611 | user07967611 | 1 | 0 | 0 | 2024 - 04 - 16 22 :49 :02 |
| 9240457 | title09240457 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 7293857 | user07293857 | 1 | 1 | 2 | 2024 - 04 - 16 22 :47 :37 |
| 9239788 | title09239788 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 1942271 | user01942271 | 0 | 1 | 0 | 2024 - 04 - 16 22 :36 :28 |
| 9238732 | title09238732 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 8630110 | user08630110 | 1 | 1 | 1 | 2024 - 04 - 16 22 :18 :52 |
| 9238723 | title09238723 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 2607420 | user02607420 | 0 | 2 | 1 | 2024 - 04 - 16 22 :18 :43 |
| 9237613 | title09237613 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 11005785 | user11005785 | 1 | 1 | 2 | 2024 - 04 - 16 22 :00 :13 |
| 9236785 | title09236785 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 3348169 | user03348169 | 0 | 0 | 2 | 2024 - 04 - 16 21 :46 :25 |
| 9236779 | title09236779 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 3681642 | user03681642 | 1 | 1 | 0 | 2024 - 04 - 16 21 :46 :19 |
| 9236438 | title09236438 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 4246787 | user04246787 | 0 | 1 | 0 | 2024 - 04 - 16 21 :40 :38 |
| 9235142 | title09235142 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 8789005 | user08789005 | 1 | 1 | 1 | 2024 - 04 - 16 21 :19 :02 |
| 9234524 | title09234524 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 8397890 | user08397890 | 0 | 2 | 0 | 2024 - 04 - 16 21 :08 :44 |
| 9232133 | title09232133 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 5156143 | user05156143 | 1 | 0 | 2 | 2024 - 04 - 16 20 :28 :53 |
| 9232087 | title09232087 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 10600332 | user10600332 | 0 | 2 | 0 | 2024 - 04 - 16 20 :28 :07 |
| 9230336 | title09230336 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 7678944 | user07678944 | 0 | 1 | 0 | 2024 - 04 - 16 19 :58 :56 |
| 9230233 | title09230233 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4374216 | user04374216 | 0 | 0 | 1 | 2024 - 04 - 16 19 :57 :13 |
| 9228378 | title09228378 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 11648063 | user11648063 | 0 | 0 | 0 | 2024 - 04 - 16 19 :26 :18 |
| 9227510 | title09227510 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 6241854 | user06241854 | 0 | 0 | 2 | 2024 - 04 - 16 19 :11 :50 |
| 9227506 | title09227506 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 6933624 | user06933624 | 1 | 0 | 1 | 2024 - 04 - 16 19 :11 :46 |
| 9225224 | title09225224 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 3493351 | user03493351 | 1 | 0 | 0 | 2024 - 04 - 16 18 :33 :44 |
| 9224546 | title09224546 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 5434068 | user05434068 | 1 | 0 | 0 | 2024 - 04 - 16 18 :22 :26 |
| 9221284 | title09221284 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 114708 | user00114708 | 0 | 0 | 0 | 2024 - 04 - 16 17 :28 :04 |
| 9221224 | title09221224 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 4411866 | user04411866 | 0 | 0 | 1 | 2024 - 04 - 16 17 :27 :04 |
| 9219611 | title09219611 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 1793842 | user01793842 | 0 | 1 | 0 | 2024 - 04 - 16 17 :00 :11 |
| 9219568 | title09219568 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 11335221 | user11335221 | 1 | 2 | 1 | 2024 - 04 - 16 16 :59 :28 |
| 9216857 | title09216857 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 2468886 | user02468886 | 1 | 0 | 2 | 2024 - 04 - 16 16 :14 :17 |
| 9216748 | title09216748 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 1470915 | user01470915 | 0 | 0 | 2 | 2024 - 04 - 16 16 :12 :28 |
| 9211557 | title09211557 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 6695339 | user06695339 | 0 | 0 | 0 | 2024 - 04 - 16 14 :45 :57 |
+ -- ----------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+50 rows in set (40 .82 sec)
300번 페이지를 조회하는데에는 40820 ms 나 걸렸다.
너무 오래 걸린다.
mysql> explain
- > select a .article_id ,
- > a .title ,
- > a .board_id ,
- > b .name ,
- > b .slug ,
- > a .article_category_id ,
- > ac .name ,
- > ac .slug ,
- > a .writer_id ,
- > a .writer_nickname ,
- > coalesce(acc .comment_count , 0 ),
- > coalesce(alc .like_count , 0 ),
- > coalesce(adc .dislike_count , 0 ),
- > a .created_at
- > from articles a
- > join
- > boards b
- > on b .board_id = a .board_id
- > join
- > article_categories ac
- > on ac .article_category_id = a .article_category_id
- > left join
- > article_comment_counts acc
- > on acc .article_id = a .article_id
- > left join
- > article_like_counts alc
- > on alc .article_id = a .article_id
- > left join
- > article_dislike_counts adc
- > on adc .article_id = a .article_id
- > where a .board_id = 500
- > order by a .article_id desc
- > limit 50 offset 14950 ;
+ -- --+-------------+-------+------------+--------+---------------+---------+---------+-------------------------------------------+-------+----------+----------------------------------++ -- --+-------------+-------+------------+--------+---------------+---------+---------+-------------------------------------------+-------+----------+----------------------------------+1 | SIMPLE | b | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100 .00 | NULL |
| 1 | SIMPLE | a | NULL | index | NULL | PRIMARY | 8 | NULL | 15000 | 10 .00 | Using where ; Backward index scan |
| 1 | SIMPLE | ac | NULL | eq_ref | PRIMARY | PRIMARY | 8 | board_db_production .a .article_category_id | 1 | 100 .00 | NULL |
| 1 | SIMPLE | acc | NULL | eq_ref | PRIMARY | PRIMARY | 8 | board_db_production .a .article_id | 1 | 100 .00 | NULL |
| 1 | SIMPLE | alc | NULL | eq_ref | PRIMARY | PRIMARY | 8 | board_db_production .a .article_id | 1 | 100 .00 | NULL |
| 1 | SIMPLE | adc | NULL | eq_ref | PRIMARY | PRIMARY | 8 | board_db_production .a .article_id | 1 | 100 .00 | NULL |
+ -- --+-------------+-------+------------+--------+---------------+---------+---------+-------------------------------------------+-------+----------+----------------------------------+6 rows in set , 1 warning (0 .01 sec)
실행계획을 보면, 다른 조인쿼리 부분은 문제가 없어보이는데(PK 인덱스 조회), 게시글 조회하는 부분에서 primay key 인덱스에서 index 풀 스캔을 하고 있다.
filtered = 10.00 인걸 보면 쿼리 효율이 좋아보이지 않는다.
mysql> explain analyze
- > select a .article_id ,
- > a .title ,
- > a .board_id ,
- > b .name ,
- > b .slug ,
- > a .article_category_id ,
- > ac .name ,
- > ac .slug ,
- > a .writer_id ,
- > a .writer_nickname ,
- > coalesce(acc .comment_count , 0 ),
- > coalesce(alc .like_count , 0 ),
- > coalesce(adc .dislike_count , 0 ),
- > a .created_at
- > from articles a
- > join
- > boards b
- > on b .board_id = a .board_id
- > join
- > article_categories ac
- > on ac .article_category_id = a .article_category_id
- > left join
- > article_comment_counts acc
- > on acc .article_id = a .article_id
- > left join
- > article_like_counts alc
- > on alc .article_id = a .article_id
- > left join
- > article_dislike_counts adc
- > on adc .article_id = a .article_id
- > where a .board_id = 500
- > order by a .article_id desc
- > limit 50 offset 14950 ;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| - > Limit / Offset: 50 / 14950 row(s) (cost= 9 .47e+ 6 rows= 0 ) (actual time = 40665 ..40815 rows= 50 loops= 1 )
- > Nested loop left join (cost= 9 .47e+ 6 rows= 1500 ) (actual time = 8 .84 ..40810 rows= 15000 loops= 1 )
- > Nested loop left join (cost= 7 .11e+ 6 rows= 1500 ) (actual time = 7 .49 ..35898 rows= 15000 loops= 1 )
- > Nested loop left join (cost= 4 .75e+ 6 rows= 1500 ) (actual time = 6 .53 ..30863 rows= 15000 loops= 1 )
- > Nested loop inner join (cost= 2 .39e+ 6 rows= 1500 ) (actual time = 4 .69 ..24682 rows= 15000 loops= 1 )
- > Filter: (a .board_id = 500 ) (cost= 1450 rows= 1500 ) (actual time = 3 .3 ..24546 rows= 15000 loops= 1 )
- > Index scan on a using PRIMARY (reverse) (cost= 1450 rows= 15000 ) (actual time = 1 .78 ..23647 rows= 14 .8e+ 6 loops= 1 )
- > Single- row index lookup on ac using PRIMARY (article_category_id= a .article_category_id ) (cost= 1 rows= 1 ) (actual time = 0 .00855 ..0 .0086 rows= 1 loops= 15000 )
- > Single- row index lookup on acc using PRIMARY (article_id= a .article_id ) (cost= 0 .992 rows= 1 ) (actual time = 0 .412 ..0 .412 rows= 0 .391 loops= 15000 )
- > Single- row index lookup on alc using PRIMARY (article_id= a .article_id ) (cost= 0 .992 rows= 1 ) (actual time = 0 .335 ..0 .335 rows= 0 .406 loops= 15000 )
- > Single- row index lookup on adc using PRIMARY (article_id= a .article_id ) (cost= 0 .99 rows= 1 ) (actual time = 0 .327 ..0 .327 rows= 0 .4 loops= 15000 )
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (40 .85 sec)
실행상세계획을 보면, article_id 의 Primary key 인덱스에서 board_id=500 기준로 순서대로 역순으로 탐색하면서 필터링을 하고 있다.
이 구간에서만 24546 ms 정도 소요하고 있다.
특정 게시판 (board_id=500) 이란 조건으로 필터링을 하고, 그 안에서 게시글 id 역순으로 최신 50건, 15950번부터 50건 조회를 한다.
그러나 테이블에서 기본적으로 이런 조건에 대한 정렬 기준이 없다.
정렬 기준을 하나 추가해주면 좋을 것 같다.
ALTER TABLE articles
ADD INDEX idx_board_id_article_id (board_id ASC , article_id DESC );mysql> show index from articles;
+ -- --------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+Null | Index_type | Comment | Index_comment | Visible | Expression |
+ -- --------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+0 | PRIMARY | 1 | article_id | A | 23839172 | NULL | NULL | | BTREE | | | YES | NULL |
| articles | 1 | idx_board_id_article_id | 1 | board_id | A | 16310 | NULL | NULL | | BTREE | | | YES | NULL |
| articles | 1 | idx_board_id_article_id | 2 | article_id | D | 23839172 | NULL | NULL | | BTREE | | | YES | NULL |
+ -- --------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+3 rows in set (0 .04 sec)
(board_id asc, article_id desc) 멀티칼럼 인덱스를 추가했다.
각 게시판별로, 게시글을 최신순으로 조회할 때 큰 도움이 될 것이다.
mysql> WITH cte AS (SELECT a .article_id
- > FROM articles AS a
- > WHERE a .board_id = 500
- > LIMIT 1001 )
- > SELECT COUNT (* )
- > FROM cte;
+ -- --------+COUNT (* ) |
+ -- --------+1001 |
+ -- --------+1 row in set (0 .01 sec)
501건까지 게시글 수를 조회하는데, 10ms 로 속도가 향상됐다. (기존 1120ms)
이 부분만 놓고보면 성능이 거의 112배 향상된 것이다.
mysql> WITH cte AS (SELECT a .article_id
- > FROM articles AS a
- > WHERE a .board_id = 500
- > LIMIT 15001 )
- > SELECT COUNT (* )
- > FROM cte;
+ -- --------+COUNT (* ) |
+ -- --------+15001 |
+ -- --------+1 row in set (0 .02 sec)
15001건까지 게시글 수를 조회하는 것 역시 20ms 정도까지 속도가 향상됐다. (기존: 32020ms)
mysql> explain
- > WITH cte AS (SELECT a .article_id
- > FROM articles AS a
- > WHERE a .board_id = 500
- > LIMIT 15001 )
- > SELECT COUNT (* )
- > FROM cte;
+ -- --+-------------+------------+------------+------+-------------------------+-------------------------+---------+-------+-------+----------+-------------++ -- --+-------------+------------+------------+------+-------------------------+-------------------------+---------+-------+-------+----------+-------------+1 | PRIMARY | < derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 15001 | 100 .00 | NULL |
| 2 | DERIVED | a | NULL | ref | idx_board_id_article_id | idx_board_id_article_id | 8 | const | 44248 | 100 .00 | Using index |
+ -- --+-------------+------------+------------+------+-------------------------+-------------------------+---------+-------+-------+----------+-------------+2 rows in set , 1 warning (0 .04 sec)
실행계획을 보면 새로 만들어준 idx_board_id_article_id 를 사용하며, filterd=100.00 인 것을 볼 수 있다.
실제 탐색하는 행수도 많이 줄어들었다.
mysql> EXPLAIN ANALYZE
- > WITH cte AS (SELECT a .article_id
- > FROM articles AS a
- > WHERE a .board_id = 1
- > LIMIT 15001 )
- > SELECT COUNT (* )
- > FROM cte;
+ -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------++ -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+- > Aggregate: count (0 ) (cost= 1 .23e+ 6 ..1 .23e+ 6 rows= 1 ) (actual time = 10 .2 ..10 .2 rows= 1 loops= 1 )
- > Table scan on cte (cost= 1 .23e+ 6 ..1 .23e+ 6 rows= 15001 ) (actual time = 7 .46 ..9 .3 rows= 15001 loops= 1 )
- > Materialize CTE cte (cost= 1 .23e+ 6 ..1 .23e+ 6 rows= 15001 ) (actual time = 7 .46 ..7 .46 rows= 15001 loops= 1 )
- > Limit : 15001 row(s) (cost= 1 .23e+ 6 rows= 15001 ) (actual time = 0 .0247 ..5 .69 rows= 15001 loops= 1 )
- > Covering index lookup on a using idx_board_id_article_id (board_id= 1 ) (cost= 1 .23e+ 6 rows= 11 .9e+ 6 ) (actual time = 0 .0242 ..4 .63 rows= 15001 loops= 1 )
|
+ -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0 .03 sec)
상세 실행 계획을 보면, idx_board_id_article_id 인덱스를 사용하는데 커버링 인덱스를 활용하여 식별자를 추출해낸다.
정렬이 board_id, article_id(역순) 순으로 되어 있기 때문에 인덱스를 따라 쭉 원하는 건수만큼 읽어내고 추출해내기 때문에 성능이 빨라진다.
정렬을 별도로 안 해도 되고, article_id 를 인덱스에서 바로 추출 하기 때문에 커버링 인덱스를 활용하는 것이다. (디스크 접근 x)
추출한 식별자 테이블(cte)를 카운팅한다.
mysql> select a .article_id ,
- > a .title ,
- > a .board_id ,
- > b .name ,
- > b .slug ,
- > a .article_category_id ,
- > ac .name ,
- > ac .slug ,
- > a .writer_id ,
- > a .writer_nickname ,
- > coalesce(acc .comment_count , 0 ),
- > coalesce(alc .like_count , 0 ),
- > coalesce(adc .dislike_count , 0 ),
- > a .created_at
- > from articles a
- > join
- > boards b
- > on b .board_id = a .board_id
- > join
- > article_categories ac
- > on ac .article_category_id = a .article_category_id
- > left join
- > article_comment_counts acc
- > on acc .article_id = a .article_id
- > left join
- > article_like_counts alc
- > on alc .article_id = a .article_id
- > left join
- > article_dislike_counts adc
- > on adc .article_id = a .article_id
- > where a .board_id = 500
- > order by a .article_id desc
- > limit 50 offset 950 ;
+ -- ----------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+acc .comment_count , 0 ) | coalesce(alc .like_count , 0 ) | coalesce(adc .dislike_count , 0 ) | created_at |
+ -- ----------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+23080717 | title23080717 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 1751748 | user01751748 | 0 | 0 | 0 | 2024 - 09 - 24 03 :18 :37 |
| 23080707 | title23080707 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 4929538 | user04929538 | 0 | 0 | 1 | 2024 - 09 - 24 03 :18 :27 |
| 23077000 | title23077000 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 1849600 | user01849600 | 0 | 0 | 1 | 2024 - 09 - 24 02 :16 :40 |
| 23075774 | title23075774 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 4759095 | user04759095 | 0 | 2 | 1 | 2024 - 09 - 24 01 :56 :14 |
| 23075659 | title23075659 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4417094 | user04417094 | 1 | 2 | 1 | 2024 - 09 - 24 01 :54 :19 |
| 23075107 | title23075107 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 10415294 | user10415294 | 0 | 0 | 0 | 2024 - 09 - 24 01 :45 :07 |
| 23072801 | title23072801 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 1848164 | user01848164 | 0 | 2 | 2 | 2024 - 09 - 24 01 :06 :41 |
| 23072321 | title23072321 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 7152434 | user07152434 | 0 | 0 | 1 | 2024 - 09 - 24 00 :58 :41 |
| 23072251 | title23072251 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 2374906 | user02374906 | 1 | 0 | 0 | 2024 - 09 - 24 00 :57 :31 |
| 23071668 | title23071668 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 265932 | user00265932 | 1 | 1 | 0 | 2024 - 09 - 24 00 :47 :48 |
| 23071481 | title23071481 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 2964706 | user02964706 | 0 | 0 | 0 | 2024 - 09 - 24 00 :44 :41 |
| 23068708 | title23068708 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 3008032 | user03008032 | 2 | 0 | 0 | 2024 - 09 - 23 23 :58 :28 |
| 23068642 | title23068642 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 3052449 | user03052449 | 1 | 0 | 0 | 2024 - 09 - 23 23 :57 :22 |
| 23068163 | title23068163 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 10226348 | user10226348 | 0 | 1 | 2 | 2024 - 09 - 23 23 :49 :23 |
| 23067130 | title23067130 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 10594333 | user10594333 | 0 | 0 | 0 | 2024 - 09 - 23 23 :32 :10 |
| 23066142 | title23066142 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 250492 | user00250492 | 0 | 2 | 0 | 2024 - 09 - 23 23 :15 :42 |
| 23063360 | title23063360 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4356722 | user04356722 | 0 | 0 | 0 | 2024 - 09 - 23 22 :29 :20 |
| 23063263 | title23063263 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 1412300 | user01412300 | 0 | 0 | 0 | 2024 - 09 - 23 22 :27 :43 |
| 23060851 | title23060851 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 7266297 | user07266297 | 0 | 0 | 2 | 2024 - 09 - 23 21 :47 :31 |
| 23059786 | title23059786 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 8065671 | user08065671 | 0 | 0 | 1 | 2024 - 09 - 23 21 :29 :46 |
| 23059630 | title23059630 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4509268 | user04509268 | 0 | 0 | 0 | 2024 - 09 - 23 21 :27 :10 |
| 23056551 | title23056551 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 7987783 | user07987783 | 0 | 2 | 1 | 2024 - 09 - 23 20 :35 :51 |
| 23055245 | title23055245 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 3059304 | user03059304 | 0 | 0 | 1 | 2024 - 09 - 23 20 :14 :05 |
| 23054708 | title23054708 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 1393597 | user01393597 | 0 | 0 | 2 | 2024 - 09 - 23 20 :05 :08 |
| 23054495 | title23054495 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4203220 | user04203220 | 1 | 0 | 2 | 2024 - 09 - 23 20 :01 :35 |
| 23053875 | title23053875 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4122696 | user04122696 | 0 | 0 | 1 | 2024 - 09 - 23 19 :51 :15 |
| 23053846 | title23053846 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 9400051 | user09400051 | 1 | 1 | 0 | 2024 - 09 - 23 19 :50 :46 |
| 23053054 | title23053054 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 3507777 | user03507777 | 1 | 0 | 0 | 2024 - 09 - 23 19 :37 :34 |
| 23052720 | title23052720 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 8345481 | user08345481 | 0 | 0 | 1 | 2024 - 09 - 23 19 :32 :00 |
| 23052235 | title23052235 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 6012482 | user06012482 | 1 | 0 | 1 | 2024 - 09 - 23 19 :23 :55 |
| 23050297 | title23050297 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 8190055 | user08190055 | 0 | 0 | 1 | 2024 - 09 - 23 18 :51 :37 |
| 23048367 | title23048367 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 8023377 | user08023377 | 1 | 2 | 2 | 2024 - 09 - 23 18 :19 :27 |
| 23046975 | title23046975 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 6575470 | user06575470 | 0 | 0 | 0 | 2024 - 09 - 23 17 :56 :15 |
| 23046924 | title23046924 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 3696167 | user03696167 | 2 | 2 | 0 | 2024 - 09 - 23 17 :55 :24 |
| 23046374 | title23046374 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 4506931 | user04506931 | 0 | 2 | 1 | 2024 - 09 - 23 17 :46 :14 |
| 23045752 | title23045752 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 3827561 | user03827561 | 0 | 0 | 0 | 2024 - 09 - 23 17 :35 :52 |
| 23044940 | title23044940 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 82760 | user00082760 | 0 | 2 | 0 | 2024 - 09 - 23 17 :22 :20 |
| 23044494 | title23044494 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 6042253 | user06042253 | 0 | 0 | 1 | 2024 - 09 - 23 17 :14 :54 |
| 23042548 | title23042548 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 2000762 | user02000762 | 1 | 0 | 2 | 2024 - 09 - 23 16 :42 :28 |
| 23042389 | title23042389 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 5885393 | user05885393 | 1 | 0 | 0 | 2024 - 09 - 23 16 :39 :49 |
| 23041587 | title23041587 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 5549905 | user05549905 | 1 | 0 | 1 | 2024 - 09 - 23 16 :26 :27 |
| 23041411 | title23041411 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 2505354 | user02505354 | 1 | 2 | 0 | 2024 - 09 - 23 16 :23 :31 |
| 23039863 | title23039863 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 1214389 | user01214389 | 1 | 0 | 2 | 2024 - 09 - 23 15 :57 :43 |
| 23039469 | title23039469 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 2973998 | user02973998 | 0 | 1 | 0 | 2024 - 09 - 23 15 :51 :09 |
| 23037764 | title23037764 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 7301362 | user07301362 | 0 | 0 | 0 | 2024 - 09 - 23 15 :22 :44 |
| 23037410 | title23037410 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 8489062 | user08489062 | 1 | 0 | 0 | 2024 - 09 - 23 15 :16 :50 |
| 23037030 | title23037030 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 482543 | user00482543 | 0 | 0 | 0 | 2024 - 09 - 23 15 :10 :30 |
| 23034781 | title23034781 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 11789921 | user11789921 | 1 | 1 | 0 | 2024 - 09 - 23 14 :33 :01 |
| 23034388 | title23034388 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 2011291 | user02011291 | 1 | 0 | 0 | 2024 - 09 - 23 14 :26 :28 |
| 23033958 | title23033958 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 7751880 | user07751880 | 0 | 2 | 0 | 2024 - 09 - 23 14 :19 :18 |
+ -- ----------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+50 rows in set (0 .05 sec)
인덱스 추가 후, 20 페이지 조회(limit 50, offset 0) 성능은 50ms 로 향상됐다. (기존: 1670ms)
mysql> select a .article_id ,
- > a .title ,
- > a .board_id ,
- > b .name ,
- > b .slug ,
- > a .article_category_id ,
- > ac .name ,
- > ac .slug ,
- > a .writer_id ,
- > a .writer_nickname ,
- > coalesce(acc .comment_count , 0 ),
- > coalesce(alc .like_count , 0 ),
- > coalesce(adc .dislike_count , 0 ),
- > a .created_at
- > from articles a
- > join
- > boards b
- > on b .board_id = a .board_id
- > join
- > article_categories ac
- > on ac .article_category_id = a .article_category_id
- > left join
- > article_comment_counts acc
- > on acc .article_id = a .article_id
- > left join
- > article_like_counts alc
- > on alc .article_id = a .article_id
- > left join
- > article_dislike_counts adc
- > on adc .article_id = a .article_id
- > where a .board_id = 500
- > order by a .article_id desc
- > limit 50 offset 14950 ;
+ -- ----------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+acc .comment_count , 0 ) | coalesce(alc .like_count , 0 ) | coalesce(adc .dislike_count , 0 ) | created_at |
+ -- ----------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+9261611 | title09261611 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 2297565 | user02297565 | 1 | 0 | 2 | 2024 - 04 - 17 04 :40 :11 |
| 9261269 | title09261269 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 4352823 | user04352823 | 1 | 0 | 0 | 2024 - 04 - 17 04 :34 :29 |
| 9259361 | title09259361 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 4045943 | user04045943 | 0 | 0 | 0 | 2024 - 04 - 17 04 :02 :41 |
| 9258235 | title09258235 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 6248844 | user06248844 | 0 | 0 | 0 | 2024 - 04 - 17 03 :43 :55 |
| 9258155 | title09258155 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 1614575 | user01614575 | 1 | 1 | 0 | 2024 - 04 - 17 03 :42 :35 |
| 9257605 | title09257605 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 10738688 | user10738688 | 0 | 0 | 2 | 2024 - 04 - 17 03 :33 :25 |
| 9257470 | title09257470 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 8398993 | user08398993 | 0 | 0 | 2 | 2024 - 04 - 17 03 :31 :10 |
| 9257138 | title09257138 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 6804379 | user06804379 | 0 | 1 | 0 | 2024 - 04 - 17 03 :25 :38 |
| 9256610 | title09256610 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 6509521 | user06509521 | 0 | 0 | 0 | 2024 - 04 - 17 03 :16 :50 |
| 9254303 | title09254303 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 5508880 | user05508880 | 1 | 2 | 0 | 2024 - 04 - 17 02 :38 :23 |
| 9254052 | title09254052 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 11554158 | user11554158 | 0 | 2 | 0 | 2024 - 04 - 17 02 :34 :12 |
| 9252556 | title09252556 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 3742044 | user03742044 | 0 | 0 | 2 | 2024 - 04 - 17 02 :09 :16 |
| 9251178 | title09251178 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4282349 | user04282349 | 0 | 0 | 2 | 2024 - 04 - 17 01 :46 :18 |
| 9250272 | title09250272 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 9700401 | user09700401 | 0 | 0 | 0 | 2024 - 04 - 17 01 :31 :12 |
| 9248636 | title09248636 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 9126024 | user09126024 | 1 | 2 | 2 | 2024 - 04 - 17 01 :03 :56 |
| 9247641 | title09247641 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 6650763 | user06650763 | 1 | 0 | 1 | 2024 - 04 - 17 00 :47 :21 |
| 9247180 | title09247180 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 1085136 | user01085136 | 1 | 2 | 0 | 2024 - 04 - 17 00 :39 :40 |
| 9245185 | title09245185 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 8850340 | user08850340 | 0 | 1 | 0 | 2024 - 04 - 17 00 :06 :25 |
| 9244725 | title09244725 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 7962302 | user07962302 | 0 | 1 | 1 | 2024 - 04 - 16 23 :58 :45 |
| 9242701 | title09242701 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 9084609 | user09084609 | 0 | 0 | 0 | 2024 - 04 - 16 23 :25 :01 |
| 9242019 | title09242019 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 3640956 | user03640956 | 0 | 2 | 0 | 2024 - 04 - 16 23 :13 :39 |
| 9241855 | title09241855 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 1527734 | user01527734 | 0 | 0 | 0 | 2024 - 04 - 16 23 :10 :55 |
| 9240702 | title09240702 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 1025406 | user01025406 | 0 | 0 | 0 | 2024 - 04 - 16 22 :51 :42 |
| 9240542 | title09240542 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 7967611 | user07967611 | 1 | 0 | 0 | 2024 - 04 - 16 22 :49 :02 |
| 9240457 | title09240457 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 7293857 | user07293857 | 1 | 1 | 2 | 2024 - 04 - 16 22 :47 :37 |
| 9239788 | title09239788 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 1942271 | user01942271 | 0 | 1 | 0 | 2024 - 04 - 16 22 :36 :28 |
| 9238732 | title09238732 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 8630110 | user08630110 | 1 | 1 | 1 | 2024 - 04 - 16 22 :18 :52 |
| 9238723 | title09238723 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 2607420 | user02607420 | 0 | 2 | 1 | 2024 - 04 - 16 22 :18 :43 |
| 9237613 | title09237613 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 11005785 | user11005785 | 1 | 1 | 2 | 2024 - 04 - 16 22 :00 :13 |
| 9236785 | title09236785 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 3348169 | user03348169 | 0 | 0 | 2 | 2024 - 04 - 16 21 :46 :25 |
| 9236779 | title09236779 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 3681642 | user03681642 | 1 | 1 | 0 | 2024 - 04 - 16 21 :46 :19 |
| 9236438 | title09236438 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 4246787 | user04246787 | 0 | 1 | 0 | 2024 - 04 - 16 21 :40 :38 |
| 9235142 | title09235142 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 8789005 | user08789005 | 1 | 1 | 1 | 2024 - 04 - 16 21 :19 :02 |
| 9234524 | title09234524 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 8397890 | user08397890 | 0 | 2 | 0 | 2024 - 04 - 16 21 :08 :44 |
| 9232133 | title09232133 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 5156143 | user05156143 | 1 | 0 | 2 | 2024 - 04 - 16 20 :28 :53 |
| 9232087 | title09232087 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 10600332 | user10600332 | 0 | 2 | 0 | 2024 - 04 - 16 20 :28 :07 |
| 9230336 | title09230336 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 7678944 | user07678944 | 0 | 1 | 0 | 2024 - 04 - 16 19 :58 :56 |
| 9230233 | title09230233 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 4374216 | user04374216 | 0 | 0 | 1 | 2024 - 04 - 16 19 :57 :13 |
| 9228378 | title09228378 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 11648063 | user11648063 | 0 | 0 | 0 | 2024 - 04 - 16 19 :26 :18 |
| 9227510 | title09227510 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 6241854 | user06241854 | 0 | 0 | 2 | 2024 - 04 - 16 19 :11 :50 |
| 9227506 | title09227506 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 6933624 | user06933624 | 1 | 0 | 1 | 2024 - 04 - 16 19 :11 :46 |
| 9225224 | title09225224 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 3493351 | user03493351 | 1 | 0 | 0 | 2024 - 04 - 16 18 :33 :44 |
| 9224546 | title09224546 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 5434068 | user05434068 | 1 | 0 | 0 | 2024 - 04 - 16 18 :22 :26 |
| 9221284 | title09221284 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 114708 | user00114708 | 0 | 0 | 0 | 2024 - 04 - 16 17 :28 :04 |
| 9221224 | title09221224 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 4411866 | user04411866 | 0 | 0 | 1 | 2024 - 04 - 16 17 :27 :04 |
| 9219611 | title09219611 | 500 | board00000500 | board00000500 | 1999 | 정보 | tip | 1793842 | user01793842 | 0 | 1 | 0 | 2024 - 04 - 16 17 :00 :11 |
| 9219568 | title09219568 | 500 | board00000500 | board00000500 | 1997 | 일반 | general | 11335221 | user11335221 | 1 | 2 | 1 | 2024 - 04 - 16 16 :59 :28 |
| 9216857 | title09216857 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 2468886 | user02468886 | 1 | 0 | 2 | 2024 - 04 - 16 16 :14 :17 |
| 9216748 | title09216748 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 1470915 | user01470915 | 0 | 0 | 2 | 2024 - 04 - 16 16 :12 :28 |
| 9211557 | title09211557 | 500 | board00000500 | board00000500 | 1998 | 질문 | question | 6695339 | user06695339 | 0 | 0 | 0 | 2024 - 04 - 16 14 :45 :57 |
+ -- ----------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+50 rows in set (25 .48 sec)
인덱스 추가 후, 300 페이지 조회(limit 50, offset 14950) 성능은 25480ms 로 향상됐다. (기존: 40820ms)
mysql> explain
- > select a .article_id ,
- > a .title ,
- > a .board_id ,
- > b .name ,
- > b .slug ,
- > a .article_category_id ,
- > ac .name ,
- > ac .slug ,
- > a .writer_id ,
- > a .writer_nickname ,
- > coalesce(acc .comment_count , 0 ),
- > coalesce(alc .like_count , 0 ),
- > coalesce(adc .dislike_count , 0 ),
- > a .created_at
- > from articles a
- > join
- > boards b
- > on b .board_id = a .board_id
- > join
- > article_categories ac
- > on ac .article_category_id = a .article_category_id
- > left join
- > article_comment_counts acc
- > on acc .article_id = a .article_id
- > left join
- > article_like_counts alc
- > on alc .article_id = a .article_id
- > left join
- > article_dislike_counts adc
- > on adc .article_id = a .article_id
- > where a .board_id = 500
- > order by a .article_id desc
- > limit 50 offset 14950 ;
+ -- --+-------------+-------+------------+--------+-------------------------+-------------------------+---------+-------------------------------------------+-------+----------+-------++ -- --+-------------+-------+------------+--------+-------------------------+-------------------------+---------+-------------------------------------------+-------+----------+-------+1 | SIMPLE | b | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100 .00 | NULL |
| 1 | SIMPLE | a | NULL | ref | idx_board_id_article_id | idx_board_id_article_id | 8 | const | 44248 | 100 .00 | NULL |
| 1 | SIMPLE | ac | NULL | eq_ref | PRIMARY | PRIMARY | 8 | board_db_production .a .article_category_id | 1 | 100 .00 | NULL |
| 1 | SIMPLE | acc | NULL | eq_ref | PRIMARY | PRIMARY | 8 | board_db_production .a .article_id | 1 | 100 .00 | NULL |
| 1 | SIMPLE | alc | NULL | eq_ref | PRIMARY | PRIMARY | 8 | board_db_production .a .article_id | 1 | 100 .00 | NULL |
| 1 | SIMPLE | adc | NULL | eq_ref | PRIMARY | PRIMARY | 8 | board_db_production .a .article_id | 1 | 100 .00 | NULL |
+ -- --+-------------+-------+------------+--------+-------------------------+-------------------------+---------+-------------------------------------------+-------+----------+-------+6 rows in set , 1 warning (0 .02 sec)
실행계획을 확인해보면 idx_board_id_article_id 를 사용하며, filtered=100.00 인 것을 볼 수 있다.
mysql> explain analyze
- > select a .article_id ,
- > a .title ,
- > a .board_id ,
- > b .name ,
- > b .slug ,
- > a .article_category_id ,
- > ac .name ,
- > ac .slug ,
- > a .writer_id ,
- > a .writer_nickname ,
- > coalesce(acc .comment_count , 0 ),
- > coalesce(alc .like_count , 0 ),
- > coalesce(adc .dislike_count , 0 ),
- > a .created_at
- > from articles a
- > join
- > boards b
- > on b .board_id = a .board_id
- > join
- > article_categories ac
- > on ac .article_category_id = a .article_category_id
- > left join
- > article_comment_counts acc
- > on acc .article_id = a .article_id
- > left join
- > article_like_counts alc
- > on alc .article_id = a .article_id
- > left join
- > article_dislike_counts adc
- > on adc .article_id = a .article_id
- > where a .board_id = 500
- > order by a .article_id desc
- > limit 50 offset 14950 ;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| - > Limit / Offset: 50 / 14950 row(s) (cost= 236564 rows= 50 ) (actual time = 24877 ..25005 rows= 50 loops= 1 )
- > Nested loop left join (cost= 236564 rows= 44248 ) (actual time = 2 .25 ..25000 rows= 15000 loops= 1 )
- > Nested loop left join (cost= 190134 rows= 44248 ) (actual time = 2 .24 ..19597 rows= 15000 loops= 1 )
- > Nested loop left join (cost= 143504 rows= 44248 ) (actual time = 2 .23 ..14166 rows= 15000 loops= 1 )
- > Nested loop inner join (cost= 96917 rows= 44248 ) (actual time = 2 .21 ..8630 rows= 15000 loops= 1 )
- > Index lookup on a using idx_board_id_article_id (board_id= 500 ) (cost= 48244 rows= 44248 ) (actual time = 0 .0701 ..8540 rows= 15000 loops= 1 )
- > Single- row index lookup on ac using PRIMARY (article_category_id= a .article_category_id ) (cost= 1 rows= 1 ) (actual time = 0 .00551 ..0 .00556 rows= 1 loops= 15000 )
- > Single- row index lookup on acc using PRIMARY (article_id= a .article_id ) (cost= 0 .953 rows= 1 ) (actual time = 0 .369 ..0 .369 rows= 0 .391 loops= 15000 )
- > Single- row index lookup on alc using PRIMARY (article_id= a .article_id ) (cost= 0 .954 rows= 1 ) (actual time = 0 .362 ..0 .362 rows= 0 .406 loops= 15000 )
- > Single- row index lookup on adc using PRIMARY (article_id= a .article_id ) (cost= 0 .949 rows= 1 ) (actual time = 0 .36 ..0 .36 rows= 0 .4 loops= 15000 )
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (25 .09 sec)
실행 상세 계획을 보면, 대상게시글을 조회하는 과정에서 idx_board_id_article_id 를 사용하는 것을 볼 수 있다.
로컬에서 EC2 에 직접 요청을 보내보니 20페이지 조회 소요시간이 43ms 정도로 줄어들었다. (기존 : 1550 ms)
300 페이지 조회는 기존 기능이 타임아웃 발생이 됐는데, 이번엔 24320 ms 만에 성공적인 응답을 받을 수 있었다.
Vus 23 쯤에서 최대 tps에 도달하고(20정도) 그 이후, 정체되거나 들쑥날쑥해짐.
latency는 최대 tps 기준점에서 422ms 정도이고, 그 이후에는 1s, 2s, 4s 정도까지 증가.
EC2 CPU 사용률은 14.8 %, RDS CPS 사용률은 거의 24.8% 정도까지 증가.
인덱스 추가 전에는 API 성능이 초기부타 불량하여 들쑥날쑥해졌고 최대 TPS 가 몇 인지 알 수 없었다.
인덱스 추가 후에는 API 성능에 눈에 띄게 향상되었고, 최대 TPS 를 20 정도로 잡을 수 있게 됐다. (20페이지 정도 수준까지 조회 기준)
현재 기능도 고쳐야할 부분이 더 보이긴한다.
게시글 조회 성능의 쿼리를 개선할 수 있을 것 같다. (커버링 인덱스 활용 쿼리)
게시글 수 조회 쿼리에서 정말 Count 쿼리를 사용하는데, 그냥 게시판 별 게시글 수를 비정규화하면 성능을 개선시킬 수 있을 듯 하다.
