테이블 정보 및 인덱스 정보, 건수 정보 등이 필요합니다.
PK정보, 인덱스 정보, 전체 건수, 조건에 맞는 건수 , 집계시 건수, 기타 특이사항 등등
실 쿼리는 아니더라도 간략 쿼리 올려주시면 됩니다.

안녕하세요 마농님

게시글을 올리고 다시 한번 프로파일링을 해보는데

지연되는 항목이 Executing, converting HEAP to ondisk 두가지로 보입니다.

Executing이 MySQL 8.0 이상에서는 Sending Data 항목도 포함이 되어 표기가 된다고 책에서 봤습니다.

테이블 정보는 다음과 같습니다. 엔진은 모두 InnoDB 사용중입니다.

** 기본테이블 (유저정보 테이블, PK는 Unsigned,BIGINT,Auto Increment 로 되어있습니다.)

** 서브쿼리로 조인되는 두개의 테이블은 각각 해당 유저의 현재까지의 총 수입금액의 합과 지출금액의 합입니다. 

** transactions 테이블은 거래 종류를 구분하는 용도로 PK는 (Unsigned, BIGINT, Auto Increment), 인덱스는 type(INT) 컬럼에 걸려있습니다.

** transaction_details 테이블은 거래 상세내역을 입력하는 용도로 PK는 (trans_id 컬럼과, user_id 컬럼입니다. Unsigned, BIGINT,)

각 테이블의 전체 행 갯수는 다음과 같습니다.

users = 434

transactions = 77755

transaction_detail = 3053

작성한 쿼리를 대략적으로 설명하자면 다음과 같습니다.

SELECT
    users.id as uid,
    users.name as name,
    income.amount as income_amount,
    outcome.amount as outcome_amount
FROM  users
LEFT JOIN (
    SELECT
         t.user_id as user_id,
         SUM(ut.amount) as amount
    FROM
        transactions as t
    LEFT JOIN
        transaction_detail as ut ON ut.trans_id = t.id
    WHERE
        t.type = 1 AND ut.is_confirm = 1
    GROUP BY
        t.user_id
) income ON income.user_id = users.id
LEFT JOIN (
    SELECT
         t.user_id as user_id,
         SUM(ut.amount) as amount
    FROM
        transactions as t
    LEFT JOIN
        transaction_detail as ut ON ut.trans_id = t.id
    WHERE
        t.type = 2 AND ut.is_confirm = 1
    GROUP BY
        t.user_id
) outcome ON outcome.user_id = users.id

정리를 하다보니까 느린 이유를 도저히 모르겠네요. 

현재 사용중인 데이터베이스 서버의 사양은 4CORE, RAM 8GB 사양 사용중이며

쿼리 실행시 대략 10초정도 걸립니다

문제되는 쿼리가 따로 있었습니다. 

각 회원의 최종 잔액과 포인트도 각 행에 표기가 되는데 그부분이 문제였습니다.

문제되는 쿼리 지금 바로 남겨보겠습니다.

SELECT u.id uid
     , u.name
     , IFNULL(SUM(CASE t.type WHEN 1 THEN ut.amount END), 0)  income_amount
     , IFNULL(SUM(CASE t.type WHEN 2 THEN ut.amount END), 0) outcome_amount
  FROM users u
  LEFT JOIN transactions t
    ON t.user_id = u.user_id
  LEFT JOIN transaction_detail ut
    ON ut.trans_id = t.id
   AND ut.is_confirm = 1
 GROUP BY u.id, u.name
;

문제되는 쿼리는 다음과 같습니다.

각 조인되는 테이블의 각 마지막 행을 가지고 오는데 시간이 오래 걸렸습니다

문제가 되는 테이블의 정보는 다음과 같습니다.

** user_balance (회원 잔액 정보 테이블) PK(Unsigned,BIGINT,Auto Increment 로 transactions 테이블에 외래키로 설정 되어있습니다.) 인덱스는 (user_id 컬럼에 걸려있습니다)

** user_point (회원 구매 포인트 테이블) PK(Unsigned,BIGINT,Auto Increment 로 transactions 테이블에 외래키로 설정 되어있습니다.), 인덱스는 (user_id 컬럼에 걸려있습니다)

각 테이블의 전체 행개수는 다음과 같습니다.

user_balance = 1,241,234

user_point = 1,494,156

작성한 쿼리는 다음과 같습니다.

SELECT
    users.id as uid,
    users.name as name,
    ub.balance as user_balance,
    up.point as user_point
FROM  users
LEFT JOIN (
    SELECT
         ub.user_id as user_id,
         ub.balance as balance,
         ROW_NUMBER() OVER(PARTITION BY ub.user_id ORDER BY ub.created_at DESC) rn
    FROM
        user_balance as ub
) ub ON ub.user_id = users.id AND ub.rn = 1
LEFT JOIN (
    SELECT
         up.user_id as user_id,
         up.point as point,
         ROW_NUMBER() OVER(PARTITION BY up.user_id ORDER BY up.created_at DESC) rn
    FROM
        user_point as up
) ub ON up.user_id = users.id AND up.rn = 1

책에서 보니 WHERE 절에 따라서 인덱스를 타지 못하는경우도 있다고 하던데 그부분 문제는 아닌것 같고

최종 잔액을 윈도우함수로 불러오는게 문제인것 같다는 생각이 방금 들었습니다.

각 멤버별 최종잔액을 구하기 위해 저렇게 쿼리를 작성했는데 문제가 발생한게 맞는것인지 아니면 쿼리자체가 헤비해서 

집계용 테이블을 만들어서 관리를 해야하는지 궁금합니다.

-- (user_id + created_at) 으로 이루어진 인덱스가 있다면?
-- 다음 스칼라서브쿼리 방법을 시도해 볼 수 있습니다.
SELECT u.id uid
     , u.name
     , (SELECT balance
          FROM user_balance
         WHERE user_id = u.id
         ORDER BY created_at DESC
         LIMIT 1
        ) user_balance
     , (SELECT point
          FROM user_point
         WHERE user_id = u.id
         ORDER BY created_at DESC
         LIMIT 1
        ) user_point
  FROM users u
;

스칼라서브쿼리는 유저 수만큼 반복 수행됩니다. 434회
해당 인덱스가 있다면? 인덱스 스캔 1행만 읽고 바로 끝납니다.
400번 반복하지만 1행만 읽고 끝나므로 큰 부담이 없습니다.
해당 인덱스가 없거나? 유저ID 인덱스만 있다면?
스칼라서브쿼리는 오히려 더 큰 부담이 될 수 있습니다.

rn = 1 조건을 인라인뷰 안으로 넣는 방법은 시도해 보셨나요?
인덱스가 없는 상황에서는 이 방법이 좋을 것 같습니다.

두가지 목적이 상충되어서 그렇습니다.
검색속도를 향상시킬 것인가?
입력속도를 향상시킬 것인가?
인덱스와 같은 추가적인 관리 요소는 입력속도를 저하시키게 됩니다.
너무 많으면 안되고, 그렇다고 아예 만들지 말아야 하는 것도 아니고
적당히 만들어야 합니다.
자주 사용되는 쿼리에서 속도가 안나온다면 만들어야 합니다.
가끔 사용하는 쿼리에서 속도가 안나온다면 인내해야 하겠죠.

정규화 만으로 속도를 만족하지 못할 경우 반정규화를 고려할 수 있습니다.
- 사용자 테이블에 최종 잔액과 포인트를 두는 방안
- 잔액 및 포인트 테이블에 최종 여부 항목을 관리하는 방안, 인덱스 필요(유저ID + 최종여부)

-- 1. rn = 1 조건을 안으로 넣는 예제
SELECT u.id uid
     , u.name
     , ub.user_balance
     , up.user_point
  FROM users u
  LEFT OUTER JOIN
       (SELECT *
          FROM (SELECT user_id
                     , balance user_balance
                     , ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) rn
                  FROM user_balance
                ) a
         WHERE rn = 1
        ) ub
    ON u.id = ub.user_id
  LEFT OUTER JOIN
       (SELECT *
          FROM (SELECT user_id
                     , point user_point
                     , ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) rn
                  FROM user_point
                ) a
         WHERE rn = 1
        ) up
    ON u.id = up.user_id
;

-- 2. MAX 쿼리를 이용하는 예제
SELECT u.id uid
     , u.name
     , ub.user_balance
     , up.user_point
  FROM users u
  LEFT OUTER JOIN
       (SELECT user_id
             , SUBSTR(
               MAX(CONCAT(DATE_FORMAT(created_at, '%Y%m%d'), balance))
               , 9, 20) user_balance
          FROM user_balance
         GROUP BY user_id
        ) ub
    ON u.id = ub.user_id
  LEFT OUTER JOIN
       (SELECT user_id
             , SUBSTR(
               MAX(CONCAT(DATE_FORMAT(created_at, '%Y%m%d'), point))
               , 9, 20) user_point
          FROM user_point
         GROUP BY user_id
        ) up
    ON u.id = up.user_id
;

rn = 1 쿼리와 MAX 쿼리는 각각 수행시간이 어떻게 될까요?
- 조인 하기 전 각 쿼리의 수행시간

조언대로 처리 해본 후기를 남깁니다.

마농님 말씀대로 결합인덱스로 획기적인 개선을 경험했습니다 (총 20초 이상 걸리는 쿼리 => 1초)

획기적인 개선에 대한 경험이후 욕심이 생겨 지연이 되던 다양한 쿼리에 대해 조건에 따른 결합인덱스를 걸어봤습니다.

이후 다시 쿼리가 지연되는 현상이 발생했습니다.

해당 증상에 대해 찾아보니 테이블에 다양한 인덱스가 여러개가 걸려있으면 지연되는 현상이 발생할 수 있다고 하더군요.

(정확한 표현인진 모르겠습니다만, 옵티마이저가 쿼리 실행시 의도하지 않은 인덱스를 선택한다는 의미로 봤습니다)

그래서 이건 제 지식부족에 따른 문제라 생각하고 공부중입니다.

다시한번 조언해주신 마농님께 감사드립니다.