기본 데이터 처리 - DISTINCT

DISTINCT 처리 방식 분석 - MySQL 옵티마이저는 무엇을 다르게 하는가 ?

 

SQL을 작성할 때, DISTINCT를 아무 생각 없이 붙이기 쉽습니다. 하지만 MySQL 내부에서는 같은 DISTINCT라도 처리하는 방식이 완전히 달라집니다.

DISTINCT의 처리 방식은 옵티마이저가 실행 계획을 수립하는 시점에 결정됩니다. 그리고 옵티마이저는 상황에 따라 전혀 다른 방식으로 중복 제거를 처리합니다. 

 

이 글에서는 MySQL이 DISTINCT를 처리하는 방식을 세 가지로 나눠 다뤄보겠습니다. 주요 내용은 아래와 같습니다.

• 집합 함수가 없는 경우의 DISTINCT 
• 집합 함수 + DISTINCT, 인덱스 X 
• 집합 함수 + DISTINCT, 인덱스 O 

---

예시 테이블 , 데이터 , 인덱스

 

[ERD]

 

 

[인덱스 구성]

customers
  └─ (인덱스 없음)                               ← Case 1 인덱스 미사용 재현용

loans
  └─ idx_loan_type          (loan_type)          ← Case 1, 2 기본 스캔용
  └─ idx_type_amount        (loan_type, amount)  ← Case 3 루스 인덱스 스캔용

 

 

[CREATE 쿼리]

-- 고객 테이블
CREATE TABLE customers (
  customer_id   INT          NOT NULL AUTO_INCREMENT,
  customer_name VARCHAR(100) NOT NULL,
  region        VARCHAR(50)  NOT NULL,
  credit_grade  CHAR(1)      NOT NULL,  -- A, B, C, D
  PRIMARY KEY (customer_id)
);

-- 대출 테이블
CREATE TABLE loans (
  loan_id       INT           NOT NULL AUTO_INCREMENT,
  customer_id   INT           NOT NULL,
  loan_type     VARCHAR(50)   NOT NULL,  -- '주택담보', '신용', '자동차'
  loan_amount   DECIMAL(15,2) NOT NULL,
  loan_status   VARCHAR(20)   NOT NULL,  -- '정상', '연체', '상환완료'
  created_at    DATE          NOT NULL,
  PRIMARY KEY (loan_id),
  KEY idx_customer (customer_id),
  KEY idx_loan_type_amount (loan_type, loan_amount)
);

 

 

 

[샘플 데이터 삽입]

INSERT INTO customers (customer_name, region, credit_grade) VALUES
  ('김민준', '서울', 'A'),
  ('이서연', '부산', 'B'),
  ('박지훈', '서울', 'A'),
  ('최수아', '대구', 'C'),
  ('정우성', '서울', 'B'),
  ('한지민', '부산', 'A'),
  ('오세훈', '대구', 'C'),
  ('윤아라', '서울', 'D');

INSERT INTO loans (customer_id, loan_type, loan_amount, loan_status, created_at) VALUES
  (1, '주택담보', 150000000, '정상',    '2023-01-15'),
  (1, '신용',    5000000,   '정상',    '2023-06-01'),
  (2, '자동차',  30000000,  '연체',    '2022-11-20'),
  (2, '주택담보', 200000000, '정상',    '2021-05-10'),
  (3, '신용',    8000000,   '상환완료', '2020-03-22'),
  (3, '신용',    12000000,  '정상',    '2023-09-01'),
  (4, '자동차',  25000000,  '정상',    '2023-02-14'),
  (5, '주택담보', 180000000, '연체',    '2022-07-30'),
  (5, '신용',    6000000,   '상환완료', '2021-12-05'),
  (6, '자동차',  35000000,  '정상',    '2023-04-18'),
  (7, '신용',    9000000,   '연체',    '2022-08-11'),
  (8, '주택담보', 120000000, '정상',    '2023-10-01'),
  (8, '신용',    7000000,   '상환완료', '2021-06-15'),
  (8, '자동차',  28000000,  '정상',    '2023-03-09');

 

---

CASE 1 : 집합 함수가 없는 경우의 DISTINCT

 

[DISTINCT = GROUP BY]

 

집합함수 없이 사용된 DISTINCT 는 MySQL 내부에서 GROUP BY 와 동일하게 처리 됩니다. 

따라서 , 아래 두 쿼리는 MySQL 내부에서 동일하게 처리 됩니다.

 

-- 아래 두 쿼리는 MySQL 내부에서 동일하게 처리된다
SELECT DISTINCT loan_type FROM loans;
SELECT loan_type FROM loans GROUP BY loan_type;

 

이는 실행 계획으로 봐도 일치함을 알 수 있습니다. 

 

 

[DISTINCT는 SELECT 전체에 적용된다]

 

집합함수 없이 사용된 DISTINCT 는 특정 컬럼 하나에만 적용되는 것이 아니라 , SELECT 절 전체 컬럼 조합에 적용 됩니다. 

따라서 , 아래 두 쿼리는 동일하게 동작 합니다. 

 

-- 아래 두 쿼리는 같은 쿼리
SELECT DISTINCT(loan_type), loan_status FROM loans;
SELECT DISTINCT loan_type, loan_status FROM loans;

 

 

DISTINCT 는 함수가 아니기에 괄호는 무시 됩니다. 

---

CASE 2 : 집합 함수 + DISTINCT , 인덱스 X  

 

[집합 함수란?]

 

집합 함수는 여러 행의 값을 하나의 값으로 집계하는 함수입니다. 

COUNT() , SUM() , AVG() , MIN() , MAX() 가 있습니다. 

이 집합 함수 안에 DISTINCT 를 함께 사용할 수 있습니다. 

 

-- 대출 유형이 몇 종류인지 (중복 제거 후 카운트)
SELECT COUNT(DISTINCT loan_type) FROM loans;

-- 대출 유형별 중복 제거된 최소/최대 대출금액
SELECT MIN(DISTINCT loan_amount), MAX(DISTINCT loan_amount) FROM loans;

 

 

 

[어떻게 처리되는가]

 

SELECT COUNT(DISTINCT loan_status) FROM loans;

 

 

 

loan_status 컬럼에는 인덱스가 없습니다. 

MySQL 은 이 상황에서 아래 순서로 처리합니다. 

 

1. loans 테이블을 풀 스캔 (ALL) 
2. loan_status 값을 임시 테이블에 저장하면서 중복 제거 
3. 임시 테이블의 행 수를 COUNT 

 

핵심은 임시 테이블이 생성 된다는 점 입니다.  

임시 테이블은 유니크 키를 가지고 있어서 , 값을 삽입할 때 중복이 자동으로 걸러집니다. 

데이터가 많아질수록 이 임시 테이블 생성 비용이 쿼리 성능에 직접적인 영향을 줍니다. 

 

[실행 계획 분석]

 

 

실행 계획은 위와 같습니다. 

type : ALL 은 풀 스캔을 의미 합니다. 

적절한 인덱스가 존재하지 않아 loans 테이블을 풀 스캔하고 있음을 알 수 있습니다.

 

보통 임시 테이블을 사용하면 Extra 컬럼에 "Using temporary" 가 표시 되지만 , 해당 경우에서는 표시가 되지 않습니다. 

---

CASE 3 : 집합 함수 + DISTINCT , 인덱스 O  

 

[어떻게 처리되는가]

 

SELECT loan_type, MIN(DISTINCT loan_amount), MAX(DISTINCT loan_amount)
FROM loans
GROUP BY loan_type;

 

 

이 쿼리는 idx_type_amount (loan_type , loan_amount) 인덱스를 활용할 수 있습니다. 

 

인덱스는 loan_type 순으로 정렬되어 있고 , 같은 loan_type 안에서는 loan_amount 순으로 정렬되어 있습니다. 

MySQL 은 이 인덱스를 순서대로 읽는 것만으로 아래 두 가지를 동시에 처리 합니다. 

• GROUP BY loan_type — 인덱스가 이미 loan_type 순으로 정렬되어 있으므로 별도 정렬 불필요
• MIN(), MAX() — 각 그룹의 첫 번째 값이 MIN, 마지막 값이 MAX이므로 임시 테이블 불필요

즉 인덱스를 처음부터 끝까지 순서대로 읽는 것만으로 집계가 완료됩니다.

 

[실행 계획 분석]

 

 

• type: index - 테이블이 아닌 인덱스를 풀 스캔 했음을 의미 합니다. 
• key: idx_loan_type_amount - loan_type_amount 인덱스가 실제로 사용됐음을 의미합니다. 
• Extra: 테이블을 전혀 건드리지 않고 인덱스만으로 쿼리가 완료됐음을 의미합니다. (커버링 인덱스)

---

인덱스 추가로 쿼리 개선하기(CASE 2 를 CASE 3 로)   

 

[개선 전 - 인덱스 없이 집계]

 

SELECT loan_status,
       COUNT(DISTINCT loan_type)
FROM loans
GROUP BY loan_status;

 

 

대출 상태 별로 대출 유형이 몇 종류 인지 집계 하는 쿼리 입니다. 

loan_status 컬럼에는 인덱스가 없습니다.

실행 계획을 확인해보겠습니다. 

 

 

• type : ALL 로 풀 스캔이 발생 
• Extra : Using temporary 가 나타나진 않지만 , 집계 함수와 DISTINCT 가 함께 사용되었기에 임시 테이블 사용

 

지금은 데이터가 14건 뿐이라 체감하기 어렵지만 , 실제 운영 환경처럼 데이터가 수백만 건으로 늘어나면 어떻게 될까요 ? 

직접 확인해보겠습니다. 아래 프로시저로 100만 건의 데이터를 삽입 합니다. (2분 가량 걸림)

DELIMITER $$

CREATE PROCEDURE insert_loan_data()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE v_customer_id INT;
  DECLARE v_loan_type VARCHAR(50);
  DECLARE v_loan_amount DECIMAL(15,2);
  DECLARE v_loan_status VARCHAR(20);
  DECLARE v_created_at DATE;

  WHILE i <= 1000000 DO
    SET v_customer_id = FLOOR(1 + RAND() * 8);
    SET v_loan_type = ELT(FLOOR(1 + RAND() * 3), '주택담보', '신용', '자동차');
    SET v_loan_amount = FLOOR(1000000 + RAND() * 500000000);
    SET v_loan_status = ELT(FLOOR(1 + RAND() * 3), '정상', '연체', '상환완료');
    SET v_created_at = DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND() * 1460) DAY);

    INSERT INTO loans (customer_id, loan_type, loan_amount, loan_status, created_at)
    VALUES (v_customer_id, v_loan_type, v_loan_amount, v_loan_status, v_created_at);

    SET i = i + 1;
  END WHILE;
END$$

DELIMITER ;

CALL insert_loan_data();