[MySQL] SQL문 최적화

작성일: 2023-05-11 (목)

오늘의 주제는 “SQL 최적화” 입니다.

최근 지인으로부터 쿼리 튜닝 요청을 받아 해당 게시물을 작성하게 되었습니다.

요청 내용 및 SQL

1. 회원가입 7일, 14일된 A지역 회원 조회 쿼리

   # 특이사항: 일자는 서버에서 변수로 넘겨줄 예정이며, 7일차 14일차 따로따로 쿼리 수행 예정
   SELECT user_id, name
     FROM test.user
    WHERE area = 'A'
      AND DATE(created_at) = @created_at
   ; -- 454건 / 556ms
   
   # 내부 보안상 쿼리 내부의 테이블명 / 컬럼명은 존재하지 않는 가상의 명칭을 사용하였음을 알려드립니다.!

2. 회원가입 30일, 90일된 A지역 회원 중, 서비스를 한번도 이용하지 않은 회원 조회 쿼리

   # 특이사항: 일자는 서버에서 변수로 넘겨줄 예정이며, 30일차 90일차 따로따로 쿼리 수행 예정
   SELECT a.user_id, a.user_name
     FROM test.user a
     LEFT JOIN test.service b ON a.user_id = b.user_id
    WHERE a.area = 'A'
      AND DATE(a.created_at) = @created_at
      AND b.user_id IS NULL
   ; -- 9건 / 378ms
   
   # 내부 보안상 쿼리 내부의 테이블명 / 컬럼명은 존재하지 않는 가상의 명칭을 사용하였음을 알려드립니다.!

3. 미로그인 45일차 A지역 회원 중, 무료 서비스 플랜을 이용하거나 서비스를 한번도 이용하지 않은 회원 조회 쿼리

   SELECT DISTINCT a.email, a.user_name
     FROM db01.users a
     LEFT JOIN db01.services b ON a.user_id = b.user_id
    WHERE a.area = 'A'
      AND DATE(a.last_login_time) = @last_login_time
      AND b.user_id IS NULL
      AND (b.user_id IS NULL OR b.plan = 'free')
   ; -- 23건 / 14.786s
   
   # 내부 보안상 쿼리 내부의 테이블명 / 컬럼명은 존재하지 않는 가상의 명칭을 사용하였음을 알려드립니다.!

(2), (3)번 SQL문에서 Anti-Join 도 잘 작성했으며 이대로 배포한다면 서비스에는 전혀 문제가 없습니다.

그러나, (3)번 SQL 문의 경우 23건을 조회하는 과정에서 약 15초 가까이 시간이 발생했는데
과연 고객은 15초라는 시간을 어떻게 생각할까요 ??

이처럼 고객이 겪을 불편함을 최소화 시키면서, 데이터베이스의 부담을 최소화 하기 위해서는 쿼리 튜닝 작업이 필수적으로 선행되어야 합니다.

쿼리 튜닝을 진행하기 전, 조회에 필요한 테이블의 구조를 확인해보겠습니다.

SHOW CREATE TABLE test.user;
CREATE TABLE user(
    user_id int(11) not null auto_increment,
    user_name varchar(100) not null,
    email varchar(100) not null,
    last_login_time datetime default null,
    created_at datetime not null default current_timestamp,
    primary key(user_id),
    key (user_name),
    key (last_login_time)
);

SHOW CREATE TABLE test.server;
CREATE TABLE server(
    service_id int(11) not null auto_increment,
    user_id int(11) not null,
    plan varchar(10) not null,
    created_at datetime not null default current_timestamp,
    primary key(service_id),
    unique key(user_id)
    key (created_at)
);

# 내부 보안상 쿼리 내부의 테이블명 / 컬럼명은 존재하지 않는 가상의 명칭을 사용하였음을 알려드립니다.!

위의 테이블 구조를 바탕으로 작성된 SQL문의 문제점을 정리하여 적어보았습니다.

1. 조건절 컬럼 데이터 가공
   • DATE() 와 같은 함수를 통해 데이터를 가공하면 인덱스 스캔이 아닌 테이블 스캔을 진행하게 됩니다.

     • LIKE : ‘LIKE%’(앞라이크) 는 인덱스 스캔 진행, ‘%LIKE’(뒷라이크) 는 테이블 스캔 진행
     • BETWEEN : 묵시적 형변환을 일으키기 때문에 테이블 스캔 진행

   • users 테이블의 last_login_time 컬럼의 경우 인덱스가 생성되어 있음에도 불구하고, DATE() 함수를 통해 데이터를 가공했기 때문에 인덱스 스캔을 하지 못하고 테이블 스캔을 하는 문제점이 존재합니다.
2. 중복 제거를 위한 DISTINCT 사용
   • DISTINCT 는 많은 엔지니어가 중복을 제거하기 위해 일반적으로 사용하는 예약어 입니다.
     그러나, DISTINCT 예약어를 사용하는 것보다 GROUP BY 를 사용하는 것이 비용적인 측면에서 매우 유리합니다.

튜닝 쿼리

1. 회원가입 7일, 14일된 A지역 회원 조회 쿼리

   -- AS-IS
   SELECT user_id, user_name
     FROM test.user
    WHERE area = 'A'
      AND DATE(created_at) = @created_at
   ; -- 454 / 556ms
   
   -- TO-BE
   ALTER TABLE test.user ADD KEY (`created_at`)
   ; -- 154ms
   
   SELECT user_id, user_name
     FROM test.user
    WHERE area = 'A'
      AND created_at <= @created_at
      AND created_at > @created_at + 1 day
   ; -- 454 / 132ms
   
   # 내부 보안상 쿼리 내부의 테이블명 / 컬럼명은 존재하지 않는 가상의 명칭을 사용하였음을 알려드립니다.!

• 튜닝 내용
  1. 회원 가입 시간을 의미하는 created_at 컬럼에 대해서 인덱스 생성
     • area + created_at & created_at 두개의 선택지가 존재
     • 서비스에 사용되는 기존 SQL 문을 참고했을 때, created_at 인덱스 생성이 효율적이라 판단
  2. DATE() 함수를 통해 데이터를 가공하지 않고 ≤, > 부등호를 통해 인덱스 스캔 유도

2. 회원가입 30일, 90일된 A지역 회원 중, 서비스를 한번도 이용하지 않은 회원 조회 쿼리

   -- AS-IS
   SELECT a.user_id, a.user_name
     FROM test.user a
     LEFT JOIN test.service b b ON a.user_id = b.user_id
    WHERE a.area = 'A'
      AND DATE(a.created_at) = @created_at
      AND b.user_id IS NULL
   ; -- 9 / 378ms
   
   -- TO-BE
   SELECT a.user_id, a.user_name
     FROM test.user a
     LEFT JOIN test.service b ON a.user_id = b.user_id
    WHERE a.area = 'A'
      AND a.created_at <= @created_at
      AND a.created_at > @created_at + 1 day
      AND b.user_id IS NULL
   ; -- 9 / 62ms
   
   # 내부 보안상 쿼리 내부의 테이블명 / 컬럼명은 존재하지 않는 가상의 명칭을 사용하였음을 알려드립니다.!

• 튜닝 내용
  1. DATE() 함수를 통해 데이터를 가공하지 않고 ≤, > 부등호를 통해 인덱스 스캔 유도

3. 미로그인 45일차 A지역 회원 중, 무료 서비스 플랜을 이용하거나 서비스를 한번도 이용하지 않은 회원 조회 쿼리

   -- AS-IS
   SELECT DISTINCT a.email, a.user_name
     FROM test.user a
     LEFT JOIN test.service b ON a.user_id = b.user_id
    WHERE a.area = 'A'
      AND DATE(a.last_login_time) = @last_login_time
      AND b.user_id IS NULL
      AND (b.user_id IS NULL OR b.plan = 'free')
   ; -- 23 / 14.786s
   
   -- AS-IS
   SELECT a.email, a.user_name
     FROM test.user a
     LEFT JOIN test.service b ON a.user_id = b.user_id
    WHERE a.area = 'A'
      AND a.last_login_time <= @last_login_time
      AND a.last_login_time > @last_login_time + 1 day
      AND b.user_id IS NULL
      AND (b.user_id IS NULL OR b.plan = 'free')
    GROUP BY a.email, a.user_name
   ; -- 23 / 664ms
   
   # 내부 보안상 쿼리 내부의 테이블명 / 컬럼명은 존재하지 않는 가상의 명칭을 사용하였음을 알려드립니다.!

• 튜닝 내용
  1. DATE() 함수를 통해 데이터를 가공하지 않고 ≤, > 부등호를 통해 인덱스 스캔 유도
  2. GROUP BY 를 통해 중복 제거 및 비용 감소

결과

💡 user 테이블의 created_at 컬럼에 대한 인덱스를 생성함으로써 엄청난 기대효과를 확인할 수 있었습니다.
그러나, 이렇게 인덱스를 추가할 경우 기존에 사용하던 SQL 문의 실행계획이 변경될 수 있기 때문에 신중할 필요가 있으며 사전에 서비스에 사용되는 쿼리를 전수 조사하여 실행계획을 확인할 필요가 있습니다.