부하테스트 ‐ (7) 게시글 목록 무한 스크롤 조회 쿼리 수정 - ttasjwi/board-system GitHub Wiki

기존 쿼리의 문제점

mysql> EXPLAIN
    -> SELECT
    ->     a.article_id AS articleId,
    ->     a.title AS title,
    ->     a.board_id AS boardId,
    ->     b.name AS boardName,
    ->     b.slug AS boardSlug,
    ->     a.article_category_id AS articleCategoryId,
    ->     ac.name AS articleCategoryName,
    ->     ac.slug AS articleCategorySlug,
    ->     a.writer_id AS writerId,
    ->     a.writer_nickname AS writerNickname,
    ->     COALESCE(acc.comment_count, 0) AS articleCommentCount,
    ->     COALESCE(alc.like_count, 0) AS articleLikeCount,
    ->     COALESCE(adc.dislike_count, 0) AS articleDislikeCount,
    ->     a.created_at AS createdAt
    -> 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 = 1 AND a.article_id < 12663454
    -> ORDER BY a.article_id DESC
    -> LIMIT 51;
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+-------------------------------------------+----------+----------+----------------------------------+
| id | select_type | table | partitions | type   | possible_keys                   | key     | key_len | ref                                       | rows     | filtered | Extra                            |
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+-------------------------------------------+----------+----------+----------------------------------+
|  1 | SIMPLE      | b     | NULL       | const  | PRIMARY                         | PRIMARY | 8       | const                                     |        1 |   100.00 | NULL                             |
|  1 | SIMPLE      | a     | NULL       | range  | PRIMARY,idx_board_id_article_id | PRIMARY | 8       | NULL                                      | 11919586 |     0.01 | 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)
  • 기존의 게시글 무한스크롤 조회 쿼리(lastArticleId 있는 버전)에 대한 실행계획을 확인해보면 게시글 필터링 과정에서 인덱스를 idx_board_id_article_id 를 활용하지 않고 Primary Key Index 에서 Where 절 조건에서 필터링을 해서 게시글을 가져오는 것을 확인할 수 있다.
  • filtered 에서는 0.01 정도의 효율을 보이고 있다.
mysql> EXPLAIN ANALYZE
    -> SELECT
    ->     a.article_id AS articleId,
    ->     a.title AS title,
    ->     a.board_id AS boardId,
    ->     b.name AS boardName,
    ->     b.slug AS boardSlug,
    ->     a.article_category_id AS articleCategoryId,
    ->     ac.name AS articleCategoryName,
    ->     ac.slug AS articleCategorySlug,
    ->     a.writer_id AS writerId,
    ->     a.writer_nickname AS writerNickname,
    ->     COALESCE(acc.comment_count, 0) AS articleCommentCount,
    ->     COALESCE(alc.like_count, 0) AS articleLikeCount,
    ->     COALESCE(adc.dislike_count, 0) AS articleDislikeCount,
    ->     a.created_at AS createdAt
    -> 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 = 1 AND a.article_id < 12663454
    -> ORDER BY a.article_id DESC
    -> LIMIT 51;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 51 row(s)  (cost=2.41e+6 rows=51) (actual time=0.0557..0.538 rows=51 loops=1)
    -> Nested loop left join  (cost=2.41e+6 rows=731) (actual time=0.0551..0.533 rows=51 loops=1)
        -> Nested loop left join  (cost=2.41e+6 rows=731) (actual time=0.0472..0.403 rows=51 loops=1)
            -> Nested loop left join  (cost=2.41e+6 rows=731) (actual time=0.0395..0.285 rows=51 loops=1)
                -> Nested loop inner join  (cost=2.41e+6 rows=731) (actual time=0.0309..0.161 rows=51 loops=1)
                    -> Filter: ((a.board_id = 1) and (a.article_id < 12663454))  (cost=2.41e+6 rows=731) (actual time=0.0242..0.107 rows=51 loops=1)
                        -> Index range scan on a using PRIMARY over (article_id < 12663454) (reverse)  (cost=2.41e+6 rows=11.9e+6) (actual time=0.0227..0.0941 rows=102 loops=1)
                    -> Single-row index lookup on ac using PRIMARY (article_category_id=a.article_category_id)  (cost=0.667 rows=1) (actual time=809e-6..843e-6 rows=1 loops=51)
                -> Single-row index lookup on acc using PRIMARY (article_id=a.article_id)  (cost=0.931 rows=1) (actual time=0.00224..0.00226 rows=0.373 loops=51)
            -> Single-row index lookup on alc using PRIMARY (article_id=a.article_id)  (cost=0.937 rows=1) (actual time=0.00214..0.00215 rows=0.294 loops=51)
        -> Single-row index lookup on adc using PRIMARY (article_id=a.article_id)  (cost=0.951 rows=1) (actual time=0.00235..0.00237 rows=0.529 loops=51)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
  • 상세 계획을 확인해봐도 PRIMARY KEY 인덱스를 활용하여 필터링하는 것처럼 보인다. (필터링이 끝나기까지 0.107 ms 소요)
  • 하지만, 사실 이것만으로도 성능은 잘 나오긴 하고 굳이 쿼리를 바꿀 필요는 없어보인다. 20ms 정도면 충분히 좋은 성능이다.

개선

mysql> EXPLAIN
    -> WITH cte(article_id) AS (
    ->     SELECT a.article_id
    ->     FROM articles a
    ->     WHERE a.board_id = 1 AND a.article_id < 12663454
    ->     ORDER BY a.article_id DESC
    ->     LIMIT 51
    -> )
    -> SELECT
    ->     a.article_id AS articleId,
    ->     a.title AS title,
    ->     a.board_id AS boardId,
    ->     b.name AS boardName,
    ->     b.slug AS boardSlug,
    ->     a.article_category_id AS articleCategoryId,
    ->     ac.name AS articleCategoryName,
    ->     ac.slug AS articleCategorySlug,
    ->     a.writer_id AS writerId,
    ->     a.writer_nickname AS writerNickname,
    ->     COALESCE(acc.comment_count, 0) AS articleCommentCount,
    ->     COALESCE(alc.like_count, 0) AS articleLikeCount,
    ->     COALESCE(adc.dislike_count, 0) AS articleDislikeCount,
    ->     a.created_at AS createdAt
    -> FROM articles a
    ->          JOIN cte ON cte.article_id = a.article_id
    ->          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;
+----+-------------+------------+------------+--------+---------------------------------+-------------------------+---------+-------------------------------------------+----------+----------+--------------------------+
| id | select_type | table      | partitions | type   | possible_keys                   | key                     | key_len | ref                                       | rows     | filtered | Extra                    |
+----+-------------+------------+------------+--------+---------------------------------+-------------------------+---------+-------------------------------------------+----------+----------+--------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                            | NULL                    | NULL    | NULL                                      |       51 |   100.00 | NULL                     |
|  1 | PRIMARY     | a          | NULL       | eq_ref | PRIMARY,idx_board_id_article_id | PRIMARY                 | 8       | cte.article_id                            |        1 |   100.00 | NULL                     |
|  1 | PRIMARY     | acc        | NULL       | eq_ref | PRIMARY                         | PRIMARY                 | 8       | cte.article_id                            |        1 |   100.00 | NULL                     |
|  1 | PRIMARY     | alc        | NULL       | eq_ref | PRIMARY                         | PRIMARY                 | 8       | cte.article_id                            |        1 |   100.00 | NULL                     |
|  1 | PRIMARY     | adc        | NULL       | eq_ref | PRIMARY                         | PRIMARY                 | 8       | cte.article_id                            |        1 |   100.00 | NULL                     |
|  1 | PRIMARY     | b          | NULL       | eq_ref | PRIMARY                         | PRIMARY                 | 8       | board_db_production.a.board_id            |        1 |   100.00 | NULL                     |
|  1 | PRIMARY     | ac         | NULL       | eq_ref | PRIMARY                         | PRIMARY                 | 8       | board_db_production.a.article_category_id |        1 |   100.00 | NULL                     |
|  2 | DERIVED     | a          | NULL       | range  | PRIMARY,idx_board_id_article_id | idx_board_id_article_id | 16      | NULL                                      | 11658266 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+--------+---------------------------------+-------------------------+---------+-------------------------------------------+----------+----------+--------------------------+
8 rows in set, 1 warning (0.01 sec)
  • 이왕이면 다홍치마. idx_board_id_article_id 를 활용하도록 쿼리를 개선해보자.
  • WITH 절을 통해, 커버링인덱스를 활용하여 대상 article_id 를 바로 50건 특정하도록 한다. 인덱스만을 통해 조회 대상 게시글을 바로 50건을 찾을 수 있는 장점을 가진다.
  • filtered 값이 100.00 인 것을 보아, 필터링 효율은 기본에 비해 향상된 것을 알 수 있다.
mysql> EXPLAIN ANALYZE
    -> WITH cte(article_id) AS (
    ->     SELECT a.article_id
    ->     FROM articles a
    ->     WHERE a.board_id = 1 AND a.article_id < 12663454
    ->     ORDER BY a.article_id DESC
    ->     LIMIT 51
    -> )
    -> SELECT
    ->     a.article_id AS articleId,
    ->     a.title AS title,
    ->     a.board_id AS boardId,
    ->     b.name AS boardName,
    ->     b.slug AS boardSlug,
    ->     a.article_category_id AS articleCategoryId,
    ->     ac.name AS articleCategoryName,
    ->     ac.slug AS articleCategorySlug,
    ->     a.writer_id AS writerId,
    ->     a.writer_nickname AS writerNickname,
    ->     COALESCE(acc.comment_count, 0) AS articleCommentCount,
    ->     COALESCE(alc.like_count, 0) AS articleLikeCount,
    ->     COALESCE(adc.dislike_count, 0) AS articleDislikeCount,
    ->     a.created_at AS createdAt
    -> FROM articles a
    ->          JOIN cte ON cte.article_id = a.article_id
    ->          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;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=2.37e+6 rows=51) (actual time=0.112..0.629 rows=51 loops=1)
    -> Nested loop inner join  (cost=2.37e+6 rows=51) (actual time=0.108..0.581 rows=51 loops=1)
        -> Nested loop left join  (cost=2.37e+6 rows=51) (actual time=0.104..0.559 rows=51 loops=1)
            -> Nested loop left join  (cost=2.37e+6 rows=51) (actual time=0.097..0.427 rows=51 loops=1)
                -> Nested loop left join  (cost=2.37e+6 rows=51) (actual time=0.0893..0.305 rows=51 loops=1)
                    -> Nested loop inner join  (cost=2.37e+6 rows=51) (actual time=0.0804..0.178 rows=51 loops=1)
                        -> Table scan on cte  (cost=2.37e+6..2.37e+6 rows=51) (actual time=0.0709..0.0775 rows=51 loops=1)
                            -> Materialize CTE cte  (cost=2.37e+6..2.37e+6 rows=51) (actual time=0.0695..0.0695 rows=51 loops=1)
                                -> Limit: 51 row(s)  (cost=2.37e+6 rows=51) (actual time=0.0282..0.0553 rows=51 loops=1)
                                    -> Filter: ((a.board_id = 1) and (a.article_id < 12663454))  (cost=2.37e+6 rows=11.7e+6) (actual time=0.0276..0.0512 rows=51 loops=1)
                                        -> Covering index range scan on a using idx_board_id_article_id over (board_id = 1 AND 12663454 < article_id)  (cost=2.37e+6 rows=11.7e+6) (actual time=0.0256..0.0429 rows=51 loops=1)
                        -> Single-row index lookup on a using PRIMARY (article_id=cte.article_id)  (cost=0.998 rows=1) (actual time=0.00172..0.00176 rows=1 loops=51)
                    -> Single-row index lookup on acc using PRIMARY (article_id=cte.article_id)  (cost=0.933 rows=1) (actual time=0.0023..0.00231 rows=0.373 loops=51)
                -> Single-row index lookup on alc using PRIMARY (article_id=cte.article_id)  (cost=0.939 rows=1) (actual time=0.00221..0.00222 rows=0.294 loops=51)
            -> Single-row index lookup on adc using PRIMARY (article_id=cte.article_id)  (cost=0.953 rows=1) (actual time=0.00238..0.0024 rows=0.529 loops=51)
        -> Single-row index lookup on b using PRIMARY (board_id=a.board_id)  (cost=0.439 rows=1) (actual time=217e-6..252e-6 rows=1 loops=51)
    -> Single-row index lookup on ac using PRIMARY (article_category_id=a.article_category_id)  (cost=0.669 rows=1) (actual time=733e-6..772e-6 rows=1 loops=51)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
  • 이번엔 대상 추출 과정(filtering)까지 0.0512 ms 정도 소요된 것을 알 수 있다. (커버링 인덱스를 활용하기 때문에 매우 빠름.)
  • 전반적인 성능은 기존 방식과 큰 차이 없어보이지만, 처리 효율을 놓고보면 새로운 쿼리 쪽이 좀 더 효율이 괜찮아 보인다.
⚠️ **GitHub.com Fallback** ⚠️