[개발 위키] 메인페이지 조회 api 쿼리 개선
- DB에 데이터가 많을 때 메인페이지의 피드 조회 API의 성능이 매우 낮음을 확인
DB
feed 테이블 테이터 수 30만개
rss_accept 테이블 데이터 3000개
artillery 라이브러리 사용
- 초당 10회의 요청, 20초간 지속
- 초당 5회의 요청, 20초간 지속
- 초당 10회의 요청, 20초간 지속
- 초당 500회의 요청, 20초간 지속
개선 전 결과 1
http.response_time:
min: ......................................................................... 933
max: ......................................................................... 5122
mean: ........................................................................ 3025.5
median: ...................................................................... 3011.6
p95: ......................................................................... 4770.6
p99: ......................................................................... 5065.6
개선 전 결과 2
http.response_time:
min: ......................................................................... 850
max: ......................................................................... 986
mean: ........................................................................ 898.5
median: ...................................................................... 889.1
p95: ......................................................................... 944
p99: ......................................................................... 982.6
개선 후 결과 1
http.response_time:
min: ......................................................................... 1
max: ......................................................................... 5
mean: ........................................................................ 2.6
median: ...................................................................... 2
p95: ......................................................................... 4
p99: ......................................................................... 4
개선 후 결과 2
http.response_time:
min: ......................................................................... 1
max: ......................................................................... 82
mean: ........................................................................ 28.7
median: ...................................................................... 19.9
p95: ......................................................................... 68.7
p99: ......................................................................... 74.4
SELECT
`FeedView`.`order_id` AS `FeedView_order_id`,
`FeedView`.`feed_id` AS `FeedView_feed_id`,
`FeedView`.`feed_title` AS `FeedView_feed_title`,
`FeedView`.`feed_path` AS `FeedView_feed_path`,
`FeedView`.`feed_created_at` AS `FeedView_feed_created_at`,
`FeedView`.`feed_thumbnail` AS `FeedView_feed_thumbnail`,
`FeedView`.`feed_view_count` AS `FeedView_feed_view_count`,
`FeedView`.`blog_name` AS `FeedView_blog_name`,
`FeedView`.`blog_platform` AS `FeedView_blog_platform`
FROM
`feed_view` `FeedView`
ORDER BY order_id
DESC LIMIT 13;| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | NULL | ALL | NULL | NULL | NULL | NULL | 297355 | 100.00 | Using filesort | |
| 2 | DERIVED | feed | NULL | ALL | FK_7474d489d05b8051874b227f868 | NULL | NULL | NULL | 297355 | 100.00 | Using temporary; Using filesort |
| 2 | DERIVED | rss_accept | NULL | eq_ref | PRIMARY | PRIMARY | 4 | denannu.feed.blog_id | 1 | 100.00 | NULL |
rss_accept 테이블과 조인 하는 것 빼고 모두 type이 ALL로 풀스캔하고 있음을 발견
- order by -> order_id에 인덱스가 있는지
- feedView는 view table을 사용하고 있었음
- feedView는 feed 테이블의 created_at으로 정렬한 뒤 행 번호로 order_id를 부여
- mysql의 view table에는 인덱스를 생성하지 못한다.
@ViewEntity({
expression: (dataSource: DataSource) =>
dataSource
.createQueryBuilder()
.select()
.addSelect('ROW_NUMBER() OVER (ORDER BY feed.created_at) AS order_id')
.addSelect('feed.id', 'feed_id')
.addSelect('title', 'feed_title')
.addSelect('feed.path', 'feed_path')
.addSelect('feed.created_at', 'feed_created_at')
.addSelect('feed.thumbnail', 'feed_thumbnail')
.addSelect('feed.view_count', 'feed_view_count')
.addSelect('rss_accept.name', 'blog_name')
.addSelect('rss_accept.blog_platform', 'blog_platform')
.from(Feed, 'feed')
.innerJoin(RssAccept, 'rss_accept', 'rss_accept.id = feed.blog_id')
.orderBy('feed_created_at'),
name: 'feed_view',
})
export class FeedView {
@ViewColumn({
name: 'order_id',
})
orderId: number;
...
}@Entity({ name: 'feed' })
export class Feed extends BaseEntity {
@PrimaryGeneratedColumn()
id: number;
@Column({
name: 'created_at',
type: 'datetime',
nullable: false,
})
@Index()
createdAt: Date;
...
}feed 테이블의 created_at에는 인덱스가 설정되어있어 정렬에는 문제가 없지만 feedView를 생성할 때 ROW_NUMBER() 함수 사용으로 풀스캔을 하고, 마지막 orderBy() 함수로 정렬할 때는 인덱스가 설정 되지 않은 상태로 정렬하고 있다.
물리적 뷰 테이블(Materialized View)을 생성해서 정렬된 데이터를 실제 테이블에 저장
-
동적으로 ROW_NUMBER를 계산하지 않고 미리 저장
-
order_id에 인덱스를 설정해두면 조회에 인덱스를 사용해서 속도 개선 예상
- Materialized View를 갱신을 해줘야 한다.
물리 테이블을 만들경우 order_id 값을 생성을 해야하는데
기존 등록된 RSS에서 새로운 피드들만 추가된다면 상관없지만
새로운 RSS를 등록하고, 기존 피드들 사이에 새로운 피드들이 추가가 된다면 order_id값이 변경이 되야한다.
pk는 기본적으로 clustered index 설정이 되어있는데 값을 변경하게 된다면 인덱스도 새로 설정을 해줘야 함
데이터가 많은 경우 새로 인덱스를 부여하는 작업 또한 무거운 작업이 될 수 있음
feedView 테이블을 사용하지 않고 인덱스가 생성되어 있는 feed 테이블을 직접 사용해서 피드를 조회
created_at에 이미 인덱스가 생성되어 있고, rss_accept 테이블과의 join도 primary key를 통해 조인하고 있기 때문에 괜찮을 것 같다.
SELECT
`f`.`id` AS feedId,
`f`.`title` AS title,
`f`.`path` AS path,
`f`.`created_at` AS createdAt,
`f`.`thumbnail` AS thumbnail,
`f`.`view_count` AS viewCount,
`r`.`name` AS blogName,
`r`.`blog_platform` AS blogPlatform
FROM `feed` `f`
INNER JOIN `rss_accept` `r` ON `f`.`blog_id` = `r`.`id`
ORDER BY `f`.`created_at`
DESC LIMIT 13| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | f | NULL | index | FK_7474d489d05b8051874b227f868 | IDX_fda780ffdcc013b739cdc6f31d | 5 | NULL | 13 | 100.00 | Backward index scan |
| 1 | SIMPLE | r | NULL | eq_ref | PRIMARY | PRIMARY | 4 | denannu.f.blog_id | 1 | 100.00 | NULL |
feedView를 사용하지 않고 feed 테이블을 직접 사용하면 두개의 실행단계 모두 인덱스를 사용
피드 조회 쿼리를 수정하며 기존 무한 스크롤 기능에서의 조회 쿼리도 같이 수정
SELECT
`FeedView`.`order_id` AS `FeedView_order_id`,
`FeedView`.`feed_id` AS `FeedView_feed_id`,
`FeedView`.`feed_title` AS `FeedView_feed_title`,
`FeedView`.`feed_path` AS `FeedView_feed_path`,
`FeedView`.`feed_created_at` AS `FeedView_feed_created_at`,
`FeedView`.`feed_thumbnail` AS `FeedView_feed_thumbnail`,
`FeedView`.`feed_view_count` AS `FeedView_feed_view_count`,
`FeedView`.`blog_name` AS `FeedView_blog_name`,
`FeedView`.`blog_platform` AS `FeedView_blog_platform`
FROM `feed_view` `FeedView`
WHERE order_id < (
(SELECT order_id FROM `feed_view` `fv` WHERE `fv`.`feed_id` = 111070)
) LIMIT 13| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | NULL | ALL | NULL | NULL | NULL | NULL | 297625 | 33.33 | Using where | |
| 3 | DERIVED | feed | NULL | ALL | FK_7474d489d05b8051874b227f868 | NULL | NULL | NULL | 297625 | 100.00 | Using temporary; Using filesort |
| 3 | 정DERIVED | rss_accept | NULL | eq_ref | PRIMARY | PRIMARY | 4 | denannu.feed.blog_id | 1 | 100.00 | NULL |
| 2 | SUBQUERY | NULL | ref | <auto_key0> | <auto_key0> | 4 | const | 10 | 100.00 | NULL | |
| 4 | DERIVED | feed | NULL | ALL | FK_7474d489d05b8051874b227f868 | NULL | NULL | NULL | 297625 | 100.00 | Using temporary; Using filesort |
| 4 | DERIVED | rss_accept | NULL | eq_ref | PRIMARY | PRIMARY | 4 | denannu.feed.blog_id | 1 | 100.00 | NULL |
SELECT
`f`.`id` AS feedId,
`f`.`title` AS title,
`f`.`path` AS path,
`f`.`created_at` AS createdAt,
`f`.`thumbnail` AS thumbnail,
`f`.`view_count` AS viewCount,
`r`.`name` AS blogName,
`r`.`blog_platform` AS blogPlatform
FROM `feed` `f`
INNER JOIN `rss_accept` `r` ON `f`.`blog_id` = `r`.`id`
WHERE
created_at <=
(
(SELECT created_at FROM `feed` `f` WHERE `f`.`id` = ?)
)
AND `f`.`id` != ?
ORDER BY `f`.`created_at`
DESC LIMIT 13| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | f | NULL | range | PRIMARY,IDX_fda780ffdcc013b739cdc6f31d,FK_7474d489d05b8051874b227f868 | IDX_fda780ffdcc013b739cdc6f31d | 5 | NULL | 148812 | 86.04 | Using index condition; Using where; Backward index scan |
| 1 | PRIMARY | r | NULL | eq_ref | PRIMARY | PRIMARY | 4 | denannu.f.blog_id | 1 | 100.00 | NULL |
| 2 | SUBQUERY | f | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
- 무한 스크롤 기능의 조회 속도도 평균 6886.9ms -> 2.7ms로 감소
http.response_time:
min: ......................................................................... 4642
max: ......................................................................... 8983
mean: ........................................................................ 6886.9
median: ...................................................................... 6838
p95: ......................................................................... 8868.4
p99: ......................................................................... 9047.6
http.response_time:
min: ......................................................................... 2
max: ......................................................................... 4
mean: ........................................................................ 2.7
median: ...................................................................... 3
p95: ......................................................................... 4
p99: ......................................................................... 4
- 개선 전 무한 스크롤 조회 테스트
- 초당 10회 요청, 20초간 지속 테스트
- 200번의 요청 중 170건의 요청이 실패가 나왔었다.
