DB 두 테이블 쿼리 EXPLAIN

ttobe·2025년 3월 1일

db

목록 보기
3/3
post-thumbnail

환경

채팅 100만개, 유저 10만개

페이지네이션을 위해 채팅을 20개씩 가져오기

채팅 정보와 함께 보낸 이 정보를 같이 반환

시도

1. Join

EXPLAIN ANALYZE
SELECT user.chatName, chat.stringContent
FROM user
JOIN chat ON user.id = chat.senderId
WHERE chat.masterId = 2
  AND chat.updated_at < '2025-02-28 15:01:30'
ORDER BY chat.updated_at DESC
LIMIT 20;
mysql> EXPLAIN ANALYZE
    -> SELECT user.chatName, chat.stringContent
    -> FROM user
    -> JOIN chat ON user.id = chat.senderId
    -> WHERE chat.masterId = 2
    ->   AND chat.updated_at < '2025-02-28 15:01:30'
    -> ORDER BY chat.updated_at DESC
    -> LIMIT 20;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 20 row(s)  (cost=616088 rows=20) (actual time=223..223 rows=20 loops=1)
    -> Nested loop inner join  (cost=616088 rows=493698) (actual time=223..223 rows=20 loops=1)
        -> Index range scan on chat using idx_masterId_updated_at over (masterId = 2 AND updated_at < '2025-02-28 15:01:30.000000') (reverse), with index condition: ((chat.masterId = 2) and (chat.updated_at < TIMESTAMP'2025-02-28 15:01:30'))  (cost=107521 rows=493698) (actual time=222..222 rows=20 loops=1)
        -> Single-row index lookup on user using PRIMARY (id = chat.senderId)  (cost=0.93 rows=1) (actual time=0.0503..0.0507 rows=1 loops=20)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.35 sec)
  • 실제 실행 시간: 223ms, 실제 행 수: 20
  • 분석

    1. 전체 쿼리 실행:

      • Limit: 20 rows
      • 예상 비용: 616088, 예상 행 수: 20
      • 실제 실행 시간: 223ms, 실제 행 수: 20

    2. Nested loop inner join:

      • 예상 비용: 616088, 예상 행 수: 493698
      • 실제 실행 시간: 223ms, 실제 행 수: 20

    3. chat 테이블 접근:

      • Index range scan on chat using idx_masterId_updated_at
      • 조건: masterId = 2 AND updated_at < '2025-02-28 15:01:30'
      • 역순 스캔 (reverse)
      • 예상 비용: 107521, 예상 행 수: 493698
      • 실제 실행 시간: 222ms, 실제 행 수: 20

    4. user 테이블 조인:
      - Single-row index lookup on user using PRIMARY
      - 실행 횟수: 20 loops
      - 실제 실행 시간: 0.0503ms에서 0.0507ms per loop

      분석:

    5. 인덱스 효율성: idx_masterId_updated_at 인덱스가 효과적으로 사용되고 있습니다.

    6. 조인 효율성: user 테이블과의 조인이 매우 효율적으로 수행되고 있습니다.

    7. LIMIT의 효과: 20개의 행만 필요하므로, 전체 493698개의 예상 행 중 실제로는 20개만 처리됩니다.

    8. 실행 시간: 전체 쿼리 실행 시간이 223ms로, 상대적으로 빠른 편입니다.

2. 인덱스 추가

|        5 | 0.04615650 | SELECT user.chatName, chat.stringContent
FROM user
JOIN chat ON user.id = chat.senderId
WHERE chat.masterId = 2
  AND chat.updated_at < '2025-02-28 15:01:30'
ORDER BY chat.updated_at DESC
LIMIT 20         |

0.04 초 소요!!

3. 서브쿼리

chat을 조건에 맞는거 20개 가져오고나서, 그다음에 user과 조인하기

SELECT user.chatName, c.stringContent
FROM (
    SELECT senderId, stringContent
    FROM chat
    WHERE masterId = 2
      AND updated_at < '2025-02-28 15:01:30'
    ORDER BY updated_at DESC
    LIMIT 20
) AS c
JOIN user ON user.id = c.senderId;

mysql> EXPLAIN ANALYZE
    -> SELECT user.chatName, c.stringContent
    -> FROM (
    ->     SELECT senderId, stringContent
    ->     FROM chat
    ->     WHERE masterId = 2
    ->       AND updated_at < '2025-02-28 15:01:30'
    ->     ORDER BY updated_at DESC
    ->     LIMIT 20
    -> ) AS c
    -> JOIN user ON user.id = c.senderId;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=107575 rows=20) (actual time=2.46..2.59 rows=20 loops=1)
    -> Table scan on c  (cost=107552..107554 rows=20) (actual time=2.45..2.48 rows=20 loops=1)
        -> Materialize  (cost=107552..107552 rows=20) (actual time=2.44..2.44 rows=20 loops=1)
            -> Limit: 20 row(s)  (cost=107547 rows=20) (actual time=2.38..2.41 rows=20 loops=1)
                -> Index range scan on chat using idx_masterId_updated_at over (masterId = 2 AND updated_at < '2025-02-28 15:01:30.000000') (reverse), with index condition: ((chat.masterId = 2) and (chat.updated_at < TIMESTAMP'2025-02-28 15:01:30'))  (cost=107547 rows=493698) (actual time=2.38..2.39 rows=20 loops=1)
    -> Single-row index lookup on user using PRIMARY (id = c.senderId)  (cost=0.935 rows=1) (actual time=0.00247..0.00293 rows=1 loops=20)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
  • 실제 실행 시간: 2.46ms에서 2.59ms, 실제 행 수: 20
  • 분석 이 EXPLAIN ANALYZE 결과를 상세히 분석해보겠습니다:

    1. 전체 쿼리 실행:

      • Nested loop inner join을 사용합니다.
      • 예상 비용: 107575, 예상 행 수: 20
      • 실제 실행 시간: 2.46ms에서 2.59ms, 실제 행 수: 20

    2. 서브쿼리 (c) 실행:

      • Table scan on c: 물리화된(materialized) 서브쿼리 결과를 스캔합니다.
      • 예상 비용: 107552에서 107554, 예상 행 수: 20
      • 실제 실행 시간: 2.45ms에서 2.48ms, 실제 행 수: 20

    3. 서브쿼리 내부 실행:

      • Limit: 20 rows: 결과를 20개로 제한합니다.
      • Index range scan on chat: idx_masterId_updated_at 인덱스를 사용합니다.
      • 조건: masterId = 2 AND updated_at < '2025-02-28 15:01:30'
      • 역순 스캔 (reverse): ORDER BY updated_at DESC를 위해 수행됩니다.
      • 예상 행 수: 493698, 실제 스캔 행 수: 20
      • 실제 실행 시간: 2.38ms에서 2.39ms

    4. user 테이블 조인:
      - Single-row index lookup: PRIMARY 키를 사용하여 각 행마다 정확히 한 번의 조회를 수행합니다.
      - 예상 비용: 0.935, 예상 행 수: 1
      - 실제 실행 시간: 0.00247ms에서 0.00293ms, 실제 행 수: 1
      - 20번 반복 (loops=20): 서브쿼리의 각 결과에 대해 수행됩니다.

      분석:

    5. 인덱스 효율성: idx_masterId_updated_at 인덱스가 매우 효과적으로 사용되고 있습니다. 493698개의 행 중 필요한 20개만 빠르게 찾아냅니다.

    6. LIMIT의 효과: 서브쿼리에서 20개의 행만 선택하여 전체 쿼리 성능을 크게 향상시킵니다.

    7. 조인 효율성: user 테이블과의 조인이 매우 효율적으로 수행되고 있습니다. 각 조인마다 단 한 번의 인덱스 조회만 필요합니다.

    8. 전체 실행 시간: 약 2.59ms로 매우 빠른 실행 시간을 보여줍니다.

4. chat 테이블에서 먼저 20개의 레코드를 가져온 후, 필요한 user 레코드만 IN 절을 사용하여 조회

WITH chat_subset AS (
    SELECT senderId, stringContent
    FROM chat
    WHERE masterId = 2
      AND updated_at < '2025-02-28 15:01:30'
    ORDER BY updated_at DESC
    LIMIT 20
)
SELECT u.chatName, c.stringContent
FROM chat_subset c
JOIN user u ON u.id IN (SELECT DISTINCT senderId FROM chat_subset)
WHERE u.id = c.senderId;

mysql> EXPLAIN ANALYZE
    -> WITH chat_subset AS (
    ->     SELECT senderId, stringContent
    ->     FROM chat
    ->     WHERE masterId = 2
    ->       AND updated_at < '2025-02-28 15:01:30'
    ->     ORDER BY updated_at DESC
    ->     LIMIT 20
    -> )
    -> SELECT u.chatName, c.stringContent
    -> FROM chat_subset c
    -> JOIN user u ON u.id IN (SELECT DISTINCT senderId FROM chat_subset)
    -> WHERE u.id = c.senderId;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=107605 rows=400) (actual time=0.395..0.503 rows=20 loops=1)
    -> Nested loop inner join  (cost=107565 rows=20) (actual time=0.385..0.422 rows=6 loops=1)
        -> Table scan on <subquery3>  (cost=107559..107562 rows=20) (actual time=0.372..0.381 rows=6 loops=1)
            -> Materialize with deduplication  (cost=107559..107559 rows=20) (actual time=0.369..0.369 rows=6 loops=1)
                -> Table scan on chat_subset  (cost=107552..107554 rows=20) (actual time=0.311..0.34 rows=20 loops=1)
                    -> Materialize CTE chat_subset if needed  (cost=107552..107552 rows=20) (actual time=0.306..0.306 rows=20 loops=1)
                        -> Limit: 20 row(s)  (cost=107547 rows=20) (actual time=0.247..0.276 rows=20 loops=1)
                            -> Index range scan on chat using idx_masterId_updated_at over (masterId = 2 AND updated_at < '2025-02-28 15:01:30.000000') (reverse), with index condition: ((chat.masterId = 2) and (chat.updated_at < TIMESTAMP'2025-02-28 15:01:30'))  (cost=107547 rows=493698) (actual time=0.244..0.255 rows=20 loops=1)
        -> Single-row index lookup on u using PRIMARY (id = `<subquery3>`.senderId)  (cost=1.03 rows=1) (actual time=0.0034..0.00386 rows=1 loops=6)
    -> Index lookup on c using <auto_key0> (senderId = `<subquery3>`.senderId)  (cost=107552..107552 rows=2) (actual time=0.0035..0.00852 rows=3.33 loops=6)
        -> Materialize CTE chat_subset if needed (query plan printed elsewhere)  (cost=107552..107552 rows=20) (never executed)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  • 예상 비용: 107605, 예상 행 수: 400
  • 분석 이 EXPLAIN ANALYZE 결과를 상세히 분석해보겠습니다:

    1. 전체 쿼리 실행:

      • Nested loop inner join
      • 예상 비용: 107605, 예상 행 수: 400
      • 실제 실행 시간: 0.395ms에서 0.503ms, 실제 행 수: 20

    2. CTE (chat_subset) 실행:

      • Limit: 20 rows
      • Index range scan on chat using idx_masterId_updated_at
      • 조건: masterId = 2 AND updated_at < '2025-02-28 15:01:30'
      • 역순 스캔 (reverse)
      • 실제 실행 시간: 0.247ms에서 0.276ms, 실제 행 수: 20

    3. DISTINCT senderId 처리:

      • Materialize with deduplication
      • 실제 행 수: 6 (20개의 채팅에서 고유한 senderId 수)

    4. user 테이블 조인:

      • Single-row index lookup on u using PRIMARY
      • 실행 횟수: 6 loops (고유한 senderId 수만큼)
      • 실제 실행 시간: 0.0034ms에서 0.00386ms per loop

    5. 최종 결과 조합:
      - Index lookup on c using <auto_key0>
      - 실행 횟수: 6 loops
      - 실제 실행 시간: 0.0035ms에서 0.00852ms per loop
      - 평균 행 수: 3.33 rows per loop

      분석:

    6. 인덱스 효율성: idx_masterId_updated_at 인덱스가 효과적으로 사용되어 초기 20개 행을 빠르게 선택합니다.

    7. CTE 사용: chat_subset CTE를 사용하여 초기 데이터를 효율적으로 제한합니다.

    8. DISTINCT 처리: 20개의 채팅에서 6개의 고유한 senderId를 효율적으로 추출합니다.

    9. 조인 효율성: user 테이블과의 조인이 매우 효율적으로 수행되고 있습니다.

    10. 전체 실행 시간: 약 0.503ms로 매우 빠른 실행 시간을 보여줍니다.

결론

방식실행 시간실제 행 수예상 행 수인덱스 사용특징
1. Join223ms20493,698idx_masterId_updated_atNested loop inner join 사용
2. 인덱스 추가46ms---인덱스 추가로 성능 크게 향상
3. 서브쿼리2.59ms2020idx_masterId_updated_atNested loop inner join, Materialize 사용
4. CTE와 IN0.503ms20400idx_masterId_updated_atNested loop inner join, Materialize with deduplication 사용

Materialize

Materialize는 쿼리 실행 과정에서 중간 결과를 임시로 저장하는 기법입니다. 이는 다음과 같은 이점을 제공합니다:

  1. 반복적인 계산 방지: 동일한 서브쿼리나 표현식이 여러 번 사용될 때 한 번만 계산하고 재사용합니다.
  2. 성능 향상: 복잡한 서브쿼리 결과를 미리 계산하여 저장함으로써 전체 쿼리 실행 속도를 높일 수 있습니다.
  3. 메모리 사용: 결과를 메모리에 저장하여 빠른 접근을 가능하게 합니다.

CTE (Common Table Expression)

CTE는 복잡한 쿼리를 더 읽기 쉽고 관리하기 쉽게 만드는 SQL 기능입니다. 주요 특징은 다음과 같습니다:

  1. 임시 명명 결과 집합: 메인 쿼리 내에서 참조할 수 있는 임시 결과 집합을 정의합니다.
  2. WITH 절 사용: WITH 키워드로 시작하여 정의됩니다.
  3. 가독성 향상: 복잡한 쿼리를 논리적 부분으로 분할하여 이해하기 쉽게 만듭니다.
  4. 재사용성: 동일한 CTE를 쿼리 내에서 여러 번 참조할 수 있습니다.

결론

특성DB에서 IN으로 검색Backend에서 map으로 검색
네트워크 통신단일 쿼리로 통신 횟수 감소여러 번의 쿼리로 통신 횟수 증가 가능
인덱스 활용DB 인덱스 효율적 활용 가능개별 쿼리마다 인덱스 활용, 비효율적일 수 있음
쿼리 복잡성복잡한 쿼리 작성 필요 가능간단한 쿼리 유지 가능
DB 부하단일 쿼리로 부하 집중 가능여러 쿼리로 부하 분산 가능
코드 복잡성DB 쿼리에 로직 집중애플리케이션 코드에 로직 분산
N+1 문제회피 가능발생 가능성 높음

💡 그 당시에는 Backend에서 처리하는게 훨씬 빠르다고 느꼈지만, sql에 관해 공부하고, 직접 explain analzye 쳐보고 비교하면서 DB에서 IN으로 처리하는게 좋다고 생각한다.
그리고 N+1쿼리 문제 발생하지 않고 DB 접근을 한번에 해서 더 수월하다!

profile
ttobe
이전 포스트

Lock, 2단계 잠금 규약(2-Phase Locking protocol : 2PL)

0개의 댓글