[MySQL] Query 성능 향상 _ 1(JOIN 및 SUM, COUNT)

쿼리 향상 테스트

성능 향상 쿼리

-- 1s 소요
select
    c.id,
    c.name,
    p.company_id,
    p.name,
    sum(o.total_price),
    sum(o.quantity),
    count(o.user_id)
from study.company c
join study.product p 
    on c.id = p.company_id
join study.category c2 
    on p.category_id = c2.id 
join study.`order` o 
    on p.id = o.product_id 
group by c.id, p.id;

DDL

CREATE TABLE `company` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL COMMENT '회사 주소',
  `industry` varchar(255) NOT NULL COMMENT '업종',
  `create_at` datetime(6) NOT NULL,
  `update_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id_IDX` (`id`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8203 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `product` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '상품 고유 ID',
  `company_id` bigint DEFAULT NULL COMMENT '상품 소유 회사 ID',
  `category_id` bigint DEFAULT NULL COMMENT '상품 카테고리',
  `name` varchar(255) DEFAULT NULL COMMENT '상품명',
  `price` double DEFAULT NULL COMMENT '가격',
  `create_at` datetime(6) NOT NULL COMMENT '생성일',
  `update_at` datetime(6) NOT NULL COMMENT '수정일',
  PRIMARY KEY (`id`),
  KEY `product_company_id_IDX` (`company_id`) USING BTREE,
  KEY `product_category_id_IDX` (`category_id`) USING BTREE,
  CONSTRAINT `fk_category_id` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`),
  CONSTRAINT `fk_company_id` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1073122 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `category` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `create_at` datetime(6) NOT NULL COMMENT '생성일',
  `update_at` datetime(6) NOT NULL COMMENT '수정일',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1107 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `product_id` bigint DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `total_price` double NOT NULL,
  `quantity` int NOT NULL,
  `order_date` datetime(6) DEFAULT NULL,
  `create_at` datetime(6) NOT NULL COMMENT '생성일',
  `update_at` datetime(6) NOT NULL COMMENT '수정일',
  PRIMARY KEY (`id`),
  KEY `order_user_id_IDX` (`user_id`) USING BTREE,
  KEY `order_product_id_IDX` (`product_id`) USING BTREE,
  CONSTRAINT `fk_product_id` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=110001 DEFAULT CHARSET=utf8mb4 COMMENT='사용자 구매 정보';

성능 향상을 위한 쿼리 수정

1. Index 설정

테스트를 위해 WHERE, JOIN, GROUP BY, ORDER, SELECT에 모두 인덱스를 추가했으나,
대부분 PK로 연결되었고 조회 컬럼이 중복이 적어 별도 인덱스 추가가 큰 효과를 주지 못했습니다.

2. WITH 절 사용

MySQL의 WITH절(CTE)을 이용해 order 집계를 미리 계산한 후 조인하여 사용했습니다.

• 장점
  • 복잡한 집계 연산을 사전에 수행하여 메인 쿼리 처리 속도 향상
• 단점
  • JPA에서는 아직 미지원

-- 평균 0.6s
WITH OrderSummary AS (
    SELECT 
        product_id,
        SUM(total_price)      AS total_price,
        SUM(quantity)         AS total_quantity,
        COUNT(user_id)        AS user_count
    FROM study.`order`
    GROUP BY product_id
)
SELECT
    c.id,
    c.name,
    p.id,
    p.company_id,
    p.name,
    os.total_price,
    os.total_quantity,
    os.user_count
FROM study.company c
JOIN study.product p 
    ON c.id = p.company_id
JOIN study.category c2 
    ON p.category_id = c2.id
JOIN OrderSummary os 
    ON p.id = os.product_id
ORDER BY c.id, p.id;

3. SUB QUERY 사용

서브쿼리로 집계를 미리 수행한 결과를 조인해 성능을 개선했습니다.
(인덱스 사용이 제한되지만, 여전히 WITH절보다는 느리지만 효과적인 방법)

• 장점
  • 집계 로직을 별도 블록으로 처리하여 메인 쿼리 단순화
• 단점
  • WITH절 대비 다소 느림

-- 평균 0.8s
SELECT
    c.id,
    c.name,
    p.id,
    p.name,
    c2.name,
    oi.total_price,
    oi.total_quantity,
    oi.user_count
FROM study.company c
JOIN study.product p 
    ON c.id = p.company_id
JOIN study.category c2 
    ON p.category_id = c2.id 
JOIN (
    SELECT
        product_id,
        SUM(total_price)  AS total_price,
        SUM(quantity)     AS total_quantity,
        COUNT(user_id)    AS user_count
    FROM study.`order`
    GROUP BY product_id
) AS oi
    ON p.id = oi.product_id
GROUP BY c.id, p.id
ORDER BY c.id, p.id;