개발자는 기록이 답이다
1000만건의 데이터를 대상으로 쿼리최적화 with. 복합인덱스, 커버링인덱스 본문
2024.04.05 - [SQL/쿼리 최적화] - Index를 활용한 10만건의 레코드 Join쿼리를 최적화하자
Index를 활용한 10만건의 레코드 Join쿼리를 최적화하자
마이페이지에서 유저가 발급받은 쿠폰 이력을 조회하는 기능을 구현해야 했습니다 해당 기능 구현 시 쿼리 최적화를 고려했던 과정을 포스팅 해보고자 합니다. 📌 무조건 쿼리 속도가 빠르면
strong-park.tistory.com
지난번에는 간단한 쿼리를 이용해서 인덱스를 통해 성능을 개선한 내용을 포스팅했습니다.
쿼리 수행 시간이 약 19ms정도 개선되었는데, 이 정도의 차이는 상당히 적은 차이입니다.
왜냐하면 네트워크 IO작업 한번만 해도 10ms가 넘는 경우가 있기 때문입니다.
그래서 좀 더 복잡한 쿼리를 만들기 위해서는 (진행중인 프로젝트 내에서 의미있는 통계API를 만들기 위해) 테이블 증량이 필요하다고 판단했습니다. 예를 들어, 집계를 위해 특정 날에 특정 조건을 수행한 특정 집단을 뽑는 쿼리 라던가, 집계 함수와 여러 후 처리 작업을 진행해서 느린 쿼리라던가 하는 등 일단 Slow Query가 있어야 개선했다는걸 판단할 수 있기 때문입니다.
📌 사전 준비
그래서 쿠폰 사용 로직을 구현하려면 주문 로직도 필요하기 때문에 아래처럼 추가적으로 DDL을 구성했습니다.
CREATE TABLE product
(
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '상품 식별자',
category VARCHAR(255) NOT NULL COMMENT '상품 카테고리',
title VARCHAR(255) NOT NULL COMMENT '상품명',
description VARCHAR(255) NOT NULL COMMENT '상품 설명',
original_price DECIMAL(12, 2) UNSIGNED NOT NULL COMMENT '원래 상품 가격',
sale_price DECIMAL(12, 2) UNSIGNED DEFAULT 0 NOT NULL COMMENT '할인 상품 가격(쿠폰과 관계없이 전체적으로 할인할 경우)',
min_order_price DECIMAL(12, 2) UNSIGNED NULL COMMENT '최소 주문 가격',
created_at DATETIME NOT NULL COMMENT '데이터 생성일',
updated_at DATETIME NOT NULL COMMENT '데이터 변경일'
);
CREATE TABLE order_detail
(
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '주문 식별자',
product_id BIGINT UNSIGNED NOT NULL COMMENT '상품 ID',
member_id BIGINT UNSIGNED NOT NULL COMMENT '회원 ID',
quantity BIGINT UNSIGNED NOT NULL COMMENT '상품 주문 수량',
price_per_each DECIMAL(12, 2) UNSIGNED NOT NULL COMMENT '상품 개당 가격',
total_order_price DECIMAL(12, 2) UNSIGNED NOT NULL COMMENT '총 상품 주문 가격',
total_discount_price DECIMAL(12, 2) UNSIGNED NOT NULL COMMENT '총 할인 가격',
total_payment_price DECIMAL(12, 2) UNSIGNED NOT NULL COMMENT '총 결제 가격',
created_at DATETIME NOT NULL COMMENT '데이터 생성일',
updated_at DATETIME NOT NULL COMMENT '데이터 변경일'
);
## 주문 한 개에 여러 쿠폰을 사용할 수 있으므로 별도의 테이블로 분리
CREATE TABLE order_coupon
(
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '쿠폰을 사용한 주문 식별자',
order_id BIGINT UNSIGNED NOT NULL COMMENT '주문 ID',
coupon_id BIGINT UNSIGNED NOT NULL COMMENT '쿠폰 ID',
discount_amount DECIMAL(12, 2) UNSIGNED NOT NULL COMMENT '쿠폰 할인액',
created_at DATETIME NOT NULL COMMENT '데이터 생성일',
updated_at DATETIME NOT NULL COMMENT '데이터 변경일'
);
지난번과 마찬가지로 처음에 생성한 테이블에는 현재는 프라이머리 키에 대한 인덱스만 있는 상황입니다.
InnoDB스토리지엔진에서는 프라이머리 키가 지원되는데, 해당 키에 대해 자동으로 클러스터링 인덱스가 적용됩니다.
그리고 사전 작업으로 product, order_detail, order_coupon에 1000만건의 데이터를 csv파일을 이용해 import해주었습니다.
import하는데 한 테이블 당 40분~1시간 정도 기다렸기 때문에 테스트 동안 데이터 유실이 되지 않도록 조심해야 합니다!
📌인덱스 설정 전
어떤 쿼리가 복잡한 쿼리일까? 고민했봤는데, 막상 테이블은 증량해놓고 어떤 통계에 대한 요구사항을 만들어야 할지 어렵더라구요.
그래서 select절에 집계함수를 최대한 많이 사용하고, group by와 order by를 사용했습니다.
# 특정 기간 동안의 월별 주문 총 수량, 총 주문 금액. 주문 중에 사용된 쿠폰 수량, 총 쿠폰 할인 금액
select Month(od.created_at) as MONTH,
count(od.id) as totalOrderCnt,
SUM(od.total_payment_price) as totalPaymentPrice,
count(oc.id) as totalCouponUseCnt,
SUM(od.total_discount_price) as total_discount_price
from order_detail od
left join order_coupon oc on od.id = oc.order_id
where od.created_at BETWEEN '2024-01-01' and '2024-05-01'
group by MONTH
order by MONTH;
1000만건의 데이터를 넣어서 그런지 해당 쿼리를 실행하니까 금방 "느린 쿼리"를 만들 수 있게 되었는데,
테스트 결과 9~ 19초 정도 걸렸습니다.
analyze를 통해 확인한 정보에서는 평균 13초 정도 소요되었습니다.
위의 실행 계획을 보고 "어떻게 인덱스를 설계해야 할지" 고민했던 내용은 아래와 같습니다.
📌 인덱스 설계 고찰
첫번째는 실행계획의 type칼럼과 key칼럼입니다.
where절이 있음에도 COUNT()와 SUM() 함수는 그 조건에 일치하는 레코드를 읽어보지 않는 이상 알 수 없으므로 풀 테이블 스캔을 하고 있습니다. ① 또한 위에서 언급했던 것 처럼 두 테이블 모두 프라이머리 키를 클러스터드 인덱스로 갖고 있지만, 해당 인덱스를 전혀 활용하지 못하고 있습니다. SQL문장에서는 프라이머리키 이외에도 다른 칼럼들을 사용하고 있기 때문에 인덱스 탐색으로 찾은 칼럼 외에도 데이터 파일에서 레코드를 읽어오는 과정이 필요하기 때문에 랜덤 I/O가 발생하게 됩니다.② 이는 복합인덱스로 설정한 커버링 인덱스로 해결할 수 있습니다.
① (Real MySQL 2권 p. 82)
② (Real MySQL 1권 p.232, p. 483)
두번째는 드라이빙 테이블의 Extra칼럼입니다.
Using where표시는 실행 계획에서 흔히 표시되는 내용으로서 해당 표시가 성능상의 문제를 일으킬지 아닐지를 선별하기 위해서는 filtered칼럼을 같이 보아야 합니다. 옵티마이저는 약 640만건의 데이터 중에서 10%만 반환될 것으로 예측했습니다. where절에 사용되는 작업의 범위를 줄일 수 있도록 인덱스를 설정할 수 있습니다.① WHERE 절에 created_at을 기준으로 범위 조건이 적용되어있고, 해당 칼럼에 대한 인덱스가 없어 인덱스를 활용할 수 없습니다.
① (Real MySQL 1권 p. 489)
Using temporary표시는 임시 테이블을 사용했다는 것인데, 임시 테이블이 메모리에서 처리됐는지 디스크에서 처리됐는지 확인은 아래 명령어를 통해 알 수 있습니다.
## MYSQL 서버의 상태 변수
mysql> show status like 'Created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 | -> 디스크에 내부 임시 테이블이 만들어진 개수만 누적한 상태 값
| Created_tmp_files | 926 | -> 디스크에 생성된 내부 임시 파일의 개수
| Created_tmp_tables | 1 | -> 내부 임시 테이블의 개수를 누적하는 상태값 (메모리 + 디스크)
+-------------------------+-------+
3 rows in set (0.01 sec)
GROUP BY와 ORDER BY가 같이 사용되서 임시테이블을 생성했는데, 칼럼이 아닌 select절의 MONTH를 사용해서 인덱스를 적용해도 타지 않을 것 같습니다.
세번째는 드리븐 테이블의 Extra칼럼입니다.
드리븐 테이블은 검색 위주로 사용되기 때문에 인덱스가 없으면 성능에 미치는 영향이 매우 큽니다. 조인이 수행될 때 드리븐 테이블의 조인 칼럼에 적절한 인덱스가 없다면 MySQL서버는 해시조인을 사용합니다. 그래서 Using join buffer(hash join)이 표시되었다는 것은 쿼리가 조인을 수행하기 위해 조인 버퍼를 활용해서 해시 조인으로 처리됐음을 의미합니다.① 조인 버퍼는 메모리에 할당되기 때문에 과도하게 사용하게 되면 OOME가 발생할 수 있습니다. 따라서 인덱스를 설정하면 성능도 개선하고 메모리 관리도 할 수 있습니다.
① (Real MySQL 1권 p. 483)
네번째는 드라이빙과 드리븐 테이블의 rows 차이입니다.
LEFT JOIN으로 연결되어 있으며, od.id = oc.order_id 조건을 사용했지만 인덱스가 활용되지 않아서 해시조인으로 사용되었습니다.
일반적으로 드리븐 테이블을 읽을때 드라이빙 테이블에서 읽은 레코드 건수만큼 반복하기 때문에 부하가 많이 발생합니다. 실행계획을 보면 드라이빙 테이블은 약 640만건을 읽었는데, 드리븐테이블은 970만건을 읽으면서 비효율적으로 풀 테이블 스캔을 하게 되기 때문에 join 시 참조되는 칼럼에 인덱스를 사용해서 개선할 수 있습니다.
총 10회 쿼리 실행한 결과를 보면 평균 약 9초가 걸렸습니다.
처음에는 select절에 사용된 칼럼이나, where절에 사용된 칼럼에 단일 인덱스를 걸어봤지만, 모두 인덱스를 제대로 활용하지 못하고 풀 테이블 스캔을 했습니다.
그래서 위에서 인덱스 설계에 대해 고려한 대로 아래와 같이 인덱스를 설정했습니다.
## datetime 바이트 수 5바이트, deciaml 5바이트
alter table order_detail add index idx_all
(created_at, total_payment_price,total_discount_price);
alter table order_coupon add index idx_order_id (order_id);
왜냐하면 복합인덱스를 통해 where절에 있는 created_at를 선행 칼럼으로 설정한다면 작업 범위를 줄일 수 있고, select절에 있는 칼럼들을 커버링 인덱스로 설정한다면 Disk I/O를 줄일 수 있습니다. 또한 복합 인덱스의 순서는 쿼리 실행 순서에 영향을 미칩니다. 쿼리 실행 순서와 아래 그림과 같이 동작하기 때문에 where절에 있는 칼럼이 선행칼럼이어야 한다고 생각했습니다.
실제로 created_at을 1번째로 설정할때와 3번째로 설정할때 차이가 납니다. 첫번째 컬럼이 아닐 경우 인덱스를 통한 범위 검색이 효율적으로 이뤄지지 않습니다. 읽어야할 rows의 개수부터 차이가 납니다.
또한 프라이머리키를 복합인덱스에 포함하지 않은 이유는 mysql 공식문서에 따르면 InnoDB는 자동으로 세컨더리 인덱스에 프라이머리 키 칼럼을 붙여주는 Use of Index Extensions(인덱스 확장)라는 기능을 제공하기 때문입니다. 그리고 드리븐 테이블을 효과적으로 검색하기 위해 참조되는 조건절에 인덱스를 설정했습니다.
"인덱스 설정 후"로 넘어가기 전에 이번 포스팅에서 알아야할 키워드에 대해 정리해보겠습니다.
🤔 복합 인덱스란?
테이블의 여러 칼럼을 조합해서 구성되는 인덱스를 말합니다. MySQL에서는 최대 16개까지 칼럼을 조합해서 다중 칼럼 인덱스를 구축할 수 있습니다. 복합인덱스에서 중요한 건 명시된 칼럼의 순으로 정렬되어 있다는 것입니다.
복합 인덱스를 설계할 때 칼럼의 순서에 따라서 쿼리의 성능이 급격하게 차이가 나기 때문에 올바르게 설계하는것이 중요합니다. 예를 들어 (A, B)로 인덱스가 구축되어 있는 경우, 먼저 A가 순서대로 정렬되고 있고 이후에 B순서대로 정렬됩니다.그러므로 A가 먼저 정렬되어있기 때문에 B만으로 쿼리를 날릴 경우에 인덱스 풀스캔이 발생할 것입니다.
즉, 가장 왼쪽의 선행 칼럼을 조건절에서 사용하지 않으면 쿼리에서 인덱스를 사용할 수 없는 경우도 발생합니다.
(col1, col2, col3)으로 복합 인덱스를 만들었는데, col1을 조건절에 사용하지 않는다면 인덱스는 효과가 없습니다.
일반적으로 복합 인덱스를 설계하는 기준은 카디널리티입니다. 카디널리티가 높은(더 유니크하고, 중복이 적음) 칼럼을 복합 인덱스의 선행 칼럼으로 둬야 합니다. (주민번호가 성별보다 카디널리티가 높습니다) 하지만 복합 인덱스를 설계할때 카디널리티만 고려해서 인덱스를 설계하면 안됩니다.
1. 자주 사용하는 쿼리가 무엇인가?
만약 애플리케이션에서 특정 칼럼을 단독으로 사용하는 쿼리들이 많다면 해당 칼럼이 카디널리티가 좀 떨어지더라도 충분히 인덱스의 선행 칼럼으로 두는 것도 좋은 선택입니다.
2. 조인에도 인덱스가 사용하는가?
조인에 자주 사용되는 칼럼을 선행 칼럼으로 두면 조인 처리에 도움이 됩니다.
3. 인덱스의 선행 칼럼이 범위 기반의 쿼리로 많이 이용되는가?
선행 칼럼이 범위 쿼리로 사용된다면 이는 카디널리티가 높더라도 많은 범위의 인덱스 탐색이 이뤄질수 있기 때문에 선행칼럼으로 적합하지 않을 수 있습니다.
4. 이렇게 설계하면 슬로우 쿼리가 발생하지 않을까?
슬로우 쿼리가 하나씩 누적되면 데이터베이스 성능에 큰 영향을 줄 수 있습니다.
인덱스를 적용했을때에 슬로우 쿼리가 발생한다면 옵티마이저가 인덱스가 있어도 테이블 풀스캔이 적절하다고 판단할 경우 풀테이블 스캔을 할 수 있습니다.
🖍️ 잠깐! 칼럼의 카디널리티 확인 방법
✓ 이미 생성된 인덱스라면 인덱스 통계 테이블(= innodb_index_stats)를 통해 볼 수 있습니다.
# 인덱스 통계 테이블로 확인하는 방법 select table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name='tb_test';
✓ 아직 인덱스를 생성하지 않았더라면 MySQL 8.0에서는 히스토그램을 통해 확인할 수 있습니다.
# 히스토그램을 이용해서 조회하는 방법 ANALYZE TABLE your_table UPDATE HISTOGRAM ON your_column WITH 100 BUCKETS;
🤔 커버링 인덱스란?
커버링 인덱스는 읽기 쿼리 성능을 높이기 위해 사용되는 방법입니다. 인덱스를 사용해서 쿼리를 처리할 때 인덱스에 있는 칼럼만으로 쿼리를 처리하는게 충분하지 않은 경우에 실제 테이블 레코드에 접근해야 합니다. 하지만 커버링 인덱스를 사용하면 테이블 레코드에 접근하는 과정 없이 인덱스 수준에서만 쿼리를 처리할 수 있습니다.
1. 특정 칼럼 자주 조회
인덱스를 사용 중이지만 특정 칼럼때문에 테이블에 접근하는 게 비용이 크게 느껴질때 유용합니다.
2. 조인 연산 비용 줄이기
여러 테이블을 연결할 때 발생하는 비용을 줄일 수 있습니다.
3. 읽기 성능이 필요한 경우
인덱스 레벨에서만 필터링해서 읽기 성능이 필요한 경우 적용할 수 있습니다.
📌 인덱스 설정 후
위에서 언급했던 인덱스 설정 후 어떻게 변경되었는지 다시 Explain명령어를 통해 실행계획을 살펴보겠습니다.
이전 실행 계획에 비해 쿼리 최적화를 통해 많은 개선이 이루어진걸 확인할 수 있었는데요.
먼저, 드라이빙 테이블인 order_detail에서는 인덱스 레인지 스캔이 사용되어 풀 테이블 스캔보다 훨씬 효율적으로 데이터에 접근할 수 있었습니다. 이전에는 약 6462623개의 레코드를 읽었던 것에 비해 이번에는 인덱스를 활용하여 약 3231311개의 레코드만 읽음으로써 비용을 절반으로 줄였습니다.
드라이빙 테이블의 Extra칼럼을 보면 Using index가 표시되었는데, 의도했던 대로 커버링 인덱스를 사용하여 Disk I/O가 줄었음을 확인할 수 있습니다.
🖍️ Extra 칼럼에 표시되는 "Using index" 와 type칼럼에 표시되는 "index"는 서로 다릅니다.
✓ type칼럼의 "index"는 인덱스 풀 스캔으로 처리하는 방식을 의미하며, 인덱스 레인지 스캔보다 훨씬 느린 처리 방식입니다.
✓ Extra 칼럼 "Using index" 는 커버링인덱스를 사용하지 않은 쿼리 보다 훨씬 빠르게 처리하며, type에 관계 없이 사용될 수 있습니다.
또한, 드리븐 테이블인 order_coupon에서는 이전에는 해시 조인이 수행되었던 것에 비해 이번에는 인덱스를 활용한 조인이 이루어졌습니다. 이로 인해 약 9734087개의 레코드를 읽었던 것에 비해 이번에는 단지 773개의 레코드만 읽음으로써 데이터 접근 비용이 획기적으로 줄었습니다.
analyze를 통해 확인한 정보에서도 이전에는 약 13초가 걸렸던 작업이, 개선 이후에는 약 6.9초가 걸리는 것으로 보입니다. 따라서 절반인 6.1초 정도의 작업 시간이 절약되었습니다. 배수로 따지면 약 1.9배 빨라진 것입니다.
총 10번의 쿼리 실행 테스트해봤을때에는 평균적으로 4.8초정도 걸립니다. 이전 쿼리 실행 테스트에서 9초 걸리던게 4.8초 걸렸다면 약 1.9배 개선이 있었다고 할 수 있습니다.
📌 마무리
여러번 인덱스 설정과 삭제를 반복하고, Real MySQL 1,2권 책을 뒤적이면서 왜 인덱스 설계를 이렇게 했는가에 대해 초점을 맞추고자 했습니다. 개인적으론 유의미한 성과이고 쿼리상으로 개선은 있었지만, 4.8초 걸리는 쿼리가 과연 이게 운영 상황에서도 사용될 수 있을까에 대한 의문점은 존재합니다.
Group By와 Order By에 대한 임시테이블 생성때문에 느리다고 판단이 되는데, MySQL 내장 함수를 사용하기 때문에 인덱스가 적용되지 않습니다. 내장함수를 사용해서 그루핑 및 정렬하는 경우에는 더 이상 쿼리 최적화가 불가능한지, 실제 운영상황에서도 이런 쿼리 요구사항이 있다고 할때에는 쿼리를 분리해서 여러번 처리하고 애플리케이션 단에서 처리해야 할지 고민해봐야할 것 같습니다.
참고 : Real MySQL 1, 2권
MySQL튜닝2) SQL 튜닝 용어 이해하기2
MYSQL 인덱스 튜닝
인프런 강의 - MySQL 성능 최적화
'Database > 쿼리 최적화' 카테고리의 다른 글
| Index를 활용한 10만건의 레코드 Join쿼리를 최적화하자 (0) | 2024.04.05 |
|---|
