[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) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci 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 COLLATE=utf8mb4_0900_ai_ci;

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 COLLATE=utf8mb4_0900_ai_ci COMMENT='상품 고유 ID';

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 COLLATE=utf8mb4_0900_ai_ci;

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 COLLATE=utf8mb4_0900_ai_ci COMMENT='사용자가 상품을 구매한 정보를 저장.';

성능 향상을 위한 쿼리 수정

1. Index 설정

테스트를 하기위해 모든 WHERE, JOIN, GROUP BY, ORDER, SELECT 에다가 설정을 하였지만 쿼리 성능에 큰 영향은 없었다.

대다수가 PK(Index)로 JOIN이 되었으며 출력하는 name과 같은 데이터는 대다수 중복이 적다 보니 별도의 Index를 사용해도 큰 차이는 없었다.

2. WITH 절 사용

MySQL의 WITH절은 **Common Table Expression (CTE)**를 정의하는 데 사용됩니다. CTE는 임시 결과 집합으로, 쿼리 내에서 반복적으로 사용하거나 더 가독성이 좋은 쿼리를 작성하는 데 유용합니다.

 

WITH 절을 사용하여 SUM, COUNT과 같은 데이터를 임시 테이블로 저장 하여 JOIN하여 사용하였습니다.

• 장점
  • 쿼리 성능 향상
    • 미리 오래 걸리는 데이터를 계산해 놓는다
• 단점
  • 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 사용

SUB QUERY를 사용하여 미리 계산된 데이터를 JOIN 한다 이러면 Index가 없어지는 단점이 있긴 하나 계산이 미리 되어 성능은 향상된다.
아마 기간이라는 WHERE 이 붙으면 더 빨라질 수도 있을 것 같다

• 장점
  • 쿼리 성능 향상
    • 미리 오래 걸리는 데이터를 계산해 놓는다
• 단점
  • 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;