[DB 성능 최적화] 기본 SQL 튜닝

ReBugs

|2024. 8. 23. 00:27

이 글은 인프런의 MySQL 성능 최적화 입문/실전 (SQL 튜닝편) (박재성) 강의를 듣고 개인적으로 정리하는 글임을 알립니다.

많은 데이터를 조회하는 단건 쿼리

데이터를 조회할 때 한 번에 너무 많은 데이터를 조회하는 건 아닌지 체크
LIMIT, WHERE문 등을 활용해서 한 번에 조회하는 데이터의 수를 줄이는 방법을 고려

테이블 생성

DROP TABLE IF EXISTS users; # 기존 테이블 삭제

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

100만건의 랜덤 데이터 삽입

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000; 

-- 더미 데이터 삽입 쿼리
INSERT INTO users (name, age)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')),   -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    FLOOR(1 + RAND() * 1000) AS age    -- 1부터 1000 사이의 난수로 나이 생성
FROM cte;

데이터 조회

데이터를 만개정도 조회하면 대략 200ms 정도 걸린다.

SELECT * FROM users LIMIT 10000;

하지만 데이터를 10개 조회하면 대략 20ms 정도 걸린다.

SELECT * FROM users LIMIT 10;

실제 페이스북, 인스타그램의 서비스를 보더라도 한 번에 모든 게시글의 데이터를 불러오지 않는다.

스크롤을 내리면서 필요한 데이터를 그때그때 로딩하는 방식이다. 다른 커뮤니티 서비스의 게시판을 보면 페이지네이션을 적용시켜서 일부 데이터만 조회하려고 한다.

그 이유가 조회하는 데이터의 개수가 성능에 많은 영향을 끼치기 때문이다.

직관적으로 생각해보면 100만개의 데이터에서 1개의 데이터를 찾는 것보다 10,000개의 데이터를 찾는 게 오래 걸릴 수 밖에 없다.

WHERE 문 튜닝

WHERE문의 부등호(>, <, ≤, ≥, =), IN, BETWEEN, LIKE와 같은 곳에서 사용되는 컬럼은 인덱스를 사용했을 때 성능이 향상될 가능성이 높다.
데이터 액세스(rows)를 크게 줄일 수 있는 컬럼은 중복 정도가 낮은 컬럼이다. 따라서 중복 정도가 낮은 컬럼을 골라서 인덱스를 생성
"단일 컬럼에 설정하는 일반 인덱스"를 설정했을 때와 "멀티 컬럼 인덱스"를 설정했을 때의 성능 차이가 별로 나지 않는다면, 멀티 컬럼 인덱스를 사용하지 말고 일반 인덱스를 활용하자.

조건이 한개

최근 3일 이내에 가입한 유저 조회

테이블 생성

DROP TABLE IF EXISTS users; 

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

100만건의 랜덤 데이터 삽입

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000; 

-- 더미 데이터 삽입 쿼리
INSERT INTO users (name, department, created_at)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')) AS name,  -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    CASE 
        WHEN n % 10 = 1 THEN 'Engineering'
        WHEN n % 10 = 2 THEN 'Marketing'
        WHEN n % 10 = 3 THEN 'Sales'
        WHEN n % 10 = 4 THEN 'Finance'
        WHEN n % 10 = 5 THEN 'HR'
        WHEN n % 10 = 6 THEN 'Operations'
        WHEN n % 10 = 7 THEN 'IT'
        WHEN n % 10 = 8 THEN 'Customer Service'
        WHEN n % 10 = 9 THEN 'Research and Development'
        ELSE 'Product Management'
    END AS department,  -- 의미 있는 단어 조합으로 부서 이름 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

-- 잘 생성됐는 지 확인
SELECT COUNT(*) FROM users;
SELECT * FROM users LIMIT 10;

성능 측정

SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

대략 200ms 정도의 시간이 걸린다.

실행 계획 조회

EXPLAIN SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

type이 ALL이고 rows가 997,632인 걸 보니, 전체 데이터를 처음부터 끝까지 다 탐색해서 데이터를 찾아내는 풀 테이블 스캔을 했다.

풀 테이블 스캔은 성능상으로 비효율적이다.

성능 개선을 위한 인덱스 추가

created_at이 만약 정렬이 되어 있다면 최근 3일 이내에 가입한 유저를 훨씬 빠르게 조회할 수 있을 것이다.

그래서 created_at을 기준으로 정렬을 시키게끔 도와주는 인덱스를 추가한다.

CREATE INDEX idx_created_at ON users (created_at);

다시 실행 계획을 조회하면 인덱스 레인지 스캔을 한 것을 확인할 수 있다.

조건이 여러개

Sales 부서이면서 최근 3일 이내에 가입한 유저 조회

테이블 생성

DROP TABLE IF EXISTS users; 

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

100만건의 랜덤 데이터 삽입

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000; 

-- 더미 데이터 삽입 쿼리
INSERT INTO users (name, department, created_at)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')) AS name,  -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    CASE 
        WHEN n % 10 = 1 THEN 'Engineering'
        WHEN n % 10 = 2 THEN 'Marketing'
        WHEN n % 10 = 3 THEN 'Sales'
        WHEN n % 10 = 4 THEN 'Finance'
        WHEN n % 10 = 5 THEN 'HR'
        WHEN n % 10 = 6 THEN 'Operations'
        WHEN n % 10 = 7 THEN 'IT'
        WHEN n % 10 = 8 THEN 'Customer Service'
        WHEN n % 10 = 9 THEN 'Research and Development'
        ELSE 'Product Management'
    END AS department,  -- 의미 있는 단어 조합으로 부서 이름 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

-- 잘 생성됐는 지 확인
SELECT COUNT(*) FROM users;
SELECT * FROM users LIMIT 10;

성능 측정

SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)

약 200ms 정도의 시간이 걸린다.

실행 계획 조회

# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

type이 ALL인걸 보니 풀 테이블 스캔을 했다. 비효율적이다. rows가 996,810인 걸 보니 전체 데이터를 대부분 액세스 했다는 걸 추측할 수 있다.

-> Filter: ((users.department = 'Sales') and (users.created_at >= <cache>((now() - interval 3 day))))  
(cost=93877 rows=33224) (actual time=12.6..205 rows=114 loops=1)
    -> Table scan on users  (cost=93877 rows=996810) 
    (actual time=0.0994..152 rows=1e+6 loops=1)

EXPLAIN ANLYZE는 아래 화살표부터 위로 읽어나가야 한다.

풀 테이블 스캔을 했다.
→ 이 때, 액세스한 데이터의 개수는 1e+6(= 10의 6제곱 = 1,000,000)개 이다.
액세스한 1,000,000개의 데이터 중 department = ‘Sales’와 created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)을 만족하는 데이터를 필터링해온다.
→ 조건을 만족한 데이터의 개수는 rows는 114개이다.

성능 개선을 위한 인덱스 추가

인덱스를 추가하는 방법이 3가지가 있다.

created_at 컬럼을 기준으로 인덱스 생성
department 컬럼을 기준으로 인덱스 생성
department, created_at 둘 다 인덱스 생성

결론부터 말하면 created_at 컬럼을 기준으로 인덱스를 생성해야 한다.

이유는 아래와 같다.

department 컬럼을 기준으로 인덱스를 잡는 것보다 created_at 컬럼을 기준으로 인덱스를 잡는 것이 훨씬 적은 데이터를 가져온다.
-department 컬럼: 고정된 값(예: ‘Sales’, ‘Engineering’)이 반복적으로 등장한다. 즉, 특정 값을 검색할 때 많은 행들이 중복되기 때문에, 인덱스를 사용해도 선택되는 데이터의 양이 많다. 따라서 department에 인덱스를 설정해도, 쿼리에서 너무 많은 데이터를 가져오게 되어 인덱스의 이점이 줄어든다.
-created_at 컬럼: 시간 순서대로 연속적인 값이 기록된다. 범위 검색(예: 최근 3일, 1주일)을 하면 선택되는 데이터의 양이 훨씬 적다. 그러므로 created_at에 인덱스를 설정하면, 필요한 범위 내에서만 데이터를 가져오므로 성능이 크게 향상된다.
인덱스는 최소한으로 설정하는 것이 좋다.
둘 다 인덱스를 설정하면 created_at 컬럼을 인덱스로 하나만 설정한 것과 동일하게 작동한다. 따라서 쓰기 성능을 저하시키지 않기 위해서 필요없는 department 컬럼은 인덱스로 설정할 필요가 없다.

또한 멀티 컬럼 인덱스를 고려할 수 있다.

하지만 이 경우에 멀티 컬럼 인덱스와 created_at 인덱스만 걸었을 때와 크게 성능 차이가 없다.

이런 경우에는 굳이 멀티 컬럼 인덱스를 사용하지 않고 단일 컬럼 인덱스만 적용시키는 것이 낫다.

인덱스가 작동하지 않는 경우

넓은 범위의 데이터를 조회하는 경우, MySQL은 인덱스를 사용해서 조회하는 것보다 풀 테이블 스캔이 효과적이라고 판단한다.
인덱스 컬럼을 가공(함수 적용, 산술 연산, 문자역 조작 등)하면, MySQL은 해당 인덱스를 사용하지 못하는 경우가 많다. 따라서 인덱스를 적극 활용하기 위해서는 인덱스 컬럼 자체를 최대한 가공하지 않아야 한다.

조회 범위가 너무 넓은 경우

DROP TABLE IF EXISTS users; # 기존 테이블 삭제

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000; 

-- 더미 데이터 삽입 쿼리
INSERT INTO users (name, age)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')),   -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    FLOOR(1 + RAND() * 1000) AS age    -- 1부터 1000 사이의 난수로 나이 생성
FROM cte;

인덱스 설정

CREATE INDEX idx_name ON users (name);

실행 계획 조회

EXPLAIN SELECT * FROM users 
ORDER BY name DESC;

분명 인덱스를 걸었음에도 왜 풀 테이블 스캔으로 데이터를 조회된다.

그 이유는 옵티마이저가 넓은 범위의 데이터를 조회할 때는 인덱스를 활용하는 것이 비효율적이라고 판단한다. 인덱스를 활용하지 않고 풀 테이블 스캔으로 데이터를 찾을 때 훨씬 효율적이라고 판단한다.

즉, 굳이 인덱스를 거쳤다가 각 원래 테이블의 데이터를 일일이 하나씩 찾아내는 것보다, 바로 원래 테이블에 접근해서 모든 데이터를 통째로 가져와서 정렬하는 게 효율적이라고 판단한 것이다. 실제 성능상으로도 풀 테이블 스캔을 통해 데이터를 가져오는 게 효율적이다.

인덱스 컬럼을 가공한 경우

DROP TABLE IF EXISTS users; 

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    salary INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000; 

-- users 테이블에 더미 데이터 삽입
INSERT INTO users (name, salary, created_at)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')) AS name,  -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    FLOOR(1 + RAND() * 1000000) AS salary,    -- 1부터 1000000 사이의 난수로 급여 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

인덱스 설정

CREATE INDEX idx_name ON users (name);
CREATE INDEX idx_salary ON users (salary);

실행 계획 조회

# User000000으로 시작하는 이름을 가진 유저 조회
EXPLAIN SELECT * FROM users
WHERE SUBSTRING(name, 1, 10) = 'User000000';

# 2달치 급여(salary)가 1000 이하인 유저 조회
SELECT * FROM users
WHERE salary * 2 < 1000
ORDER BY salary;

위의 실행 계획들을 실행시켜보면 인덱스를 활용하지 않고 풀 테이블 스캔으로 탐색하는 걸 확인할 수 있다. 인덱스를 활용하지 않기 때문에 비효율적으로 데이터를 조회한다.

SQL문을 작성할 인덱스 컬럼을 가공(함수 적용, 산술 연산, 문자역 조작 등)하면, MySQL은 해당 인덱스를 활용하지 못하는 경우가 많다. 따라서 인덱스를 적극 활용하기 위해서는 인덱스 컬럼 자체를 최대한 가공하지 않아야 한다.

인덱스 컬럼을 가공하지 않은 SQL 문

# User000000으로 시작하는 이름을 가진 유저 조회
EXPLAIN SELECT * FROM users
WHERE name LIKE 'User000000%';

# 2달치 급여(salary)가 1000 이하인 유저 조회
EXPLAIN SELECT * FROM users
WHERE salary < 1000 / 2
ORDER BY salary;

인덱스 컬럼을 가공하지 않아야 인덱스를 제대로 활용할 수 있게 된다.

ORDER BY문 튜닝

ORDER BY는 시간이 오래걸리는 작업이므로 최대한 피해주는 것이 좋다. 인덱스를 사용하면 미리 정렬을 해둔 상태이기 때문에, ORDER BY를 사용해서 정렬해야 하는 번거로운 작업을 피할 수 있다.
LIMIT 없이 큰 범위의 데이터를 조회해오는 경우 옵티마이저가 인덱스를 활용하지 않고 풀 테이블 스캔을 해버릴 수도 있다. 따라서 성능 효율을 위해 LIMIT을 통해 작은 데이터의 범위를 조회해오도록 항상 신경쓰자.

DROP TABLE IF EXISTS users; 

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000; 

-- 더미 데이터 삽입 쿼리
INSERT INTO users (name, department, salary, created_at)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')) AS name,  -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    CASE 
        WHEN n % 10 = 1 THEN 'Engineering'
        WHEN n % 10 = 2 THEN 'Marketing'
        WHEN n % 10 = 3 THEN 'Sales'
        WHEN n % 10 = 4 THEN 'Finance'
        WHEN n % 10 = 5 THEN 'HR'
        WHEN n % 10 = 6 THEN 'Operations'
        WHEN n % 10 = 7 THEN 'IT'
        WHEN n % 10 = 8 THEN 'Customer Service'
        WHEN n % 10 = 9 THEN 'Research and Development'
        ELSE 'Product Management'
    END AS department,  -- 의미 있는 단어 조합으로 부서 이름 생성
    FLOOR(1 + RAND() * 1000000) AS salary,    -- 1부터 1000000 사이의 난수로 나이 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

성능 측정

SELECT * FROM users
ORDER BY salary
LIMIT 100;

약 200ms 정도의 시간이 걸린다.

실행 계획 조회

# 실행 계획
EXPLAIN SELECT * FROM users
ORDER BY salary
LIMIT 100;

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
ORDER BY salary
LIMIT 100;

type이 ALL이다. 풀 테이블 스캔을 했음을 뜻한다.
ORDER BY는 시간이 오래걸리는 작업이므로 최대한 피해주는 것이 좋다. 왜냐하면 정렬이라는 작업 자체가 다른 작업에 비해서 부담스러운 작업이며 성능에 안 좋은 영향을 끼치는 요소 중 하나이기 때문이다.

그런데 만약 인덱스를 사용하면 미리 정렬을 해둔 상태이기 때문에, ORDER BY를 사용하여 정렬해야 하는 번거로운 작업을 피할 수 있다.

성능 개선을 위한 인덱스 추가

CREATE INDEX idx_salary ON users (salary);

실행 계획 조회

EXPLAIN SELECT * FROM users
ORDER BY salary
LIMIT 100;

풀 테이블 스캔(type: ALL)이 아닌 인덱스 풀 스캔(type: index)을 활용해서 빠르게 데이터를 정렬해서 조회해왔다.
LIMIT 없이 큰 범위의 데이터를 조회해오는 경우 옵티마이저가 인덱스를 활용하지 않고 테이블 풀 스캔을 해버릴 수도 있다. 따라서 성능 효율을 위해 LIMIT을 통해 작은 데이터의 범위를 조회해오도록 항상 신경써야 한다.

실행 계획 세부 내용 조회

EXPLAIN ANALYZE SELECT * FROM users
ORDER BY salary
LIMIT 100;

-> Limit: 100 row(s)  
(cost=0.0918 rows=100) (actual time=0.732..0.759 rows=100 loops=1)
    -> Index scan on users using idx_salary
    (cost=0.0918 rows=100) (actual time=0.731..0.752 rows=100 loops=1)

인덱스 스캔으로 100개의 데이터에 대해서만 액세스 했다.
(정렬 작업을 따로 하지 않았다. 왜냐하면 인덱스라서 이미 정렬이 되어 있기 때문이다.)
Limit에 의해 100개의 데이터만 조회했다.

WHERE 문에 인덱스 VS ORDER BY 문에 인덱스

ORDER BY의 특징상 모든 데이터를 바탕으로 정렬을 해야 하기 때문에, 인덱스 풀 스캔 또는 테이블 풀 스캔을 활용할 수 밖에 없다.
이 때문에 ORDER BY문보다 WHERE문에 있는 컬럼에 인덱스를 걸었을 때 성능이 향상되는 경우가 많다.
(항상 그런 것은 아니니까 성능 측정과 실행 계획을 살펴야한다.)

DROP TABLE IF EXISTS users; 

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000; 

-- 더미 데이터 삽입 쿼리
INSERT INTO users (name, department, salary, created_at)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')) AS name,  -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    CASE 
        WHEN n % 10 = 1 THEN 'Engineering'
        WHEN n % 10 = 2 THEN 'Marketing'
        WHEN n % 10 = 3 THEN 'Sales'
        WHEN n % 10 = 4 THEN 'Finance'
        WHEN n % 10 = 5 THEN 'HR'
        WHEN n % 10 = 6 THEN 'Operations'
        WHEN n % 10 = 7 THEN 'IT'
        WHEN n % 10 = 8 THEN 'Customer Service'
        WHEN n % 10 = 9 THEN 'Research and Development'
        ELSE 'Product Management'
    END AS department,  -- 의미 있는 단어 조합으로 부서 이름 생성
    FLOOR(1 + RAND() * 1000000) AS salary,    -- 1부터 1000000 사이의 난수로 나이 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

데이터 조회 성능 확인

SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

약 200ms 정도의 시간이 소요

실행 계획 조회

EXPLAIN SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

type이 ALL인걸 보니 풀 테이블 스캔을 했다. 비효율적이다.

성능 개선을 위한 인덱스 추가

SQL문만 봤을 때는 created_at, department, salary 컬럼에 인덱스를 걸 수 있는 선택지가 있다는 걸 알 수 있다.

어떤 컬럼에 거는 게 효율적인지는 하나씩 걸어보고 SQL문의 성능을 측정해서 판단해도 된다.

우선 created_at와 department 컬럼 중에서 인덱스를 걸었을 때 효율적인 컬럼은 created_at이라는 걸 예상할 수 있다.

왜냐하면 위의 SQL 문 조건을 봤을 때 department = ‘Sales’의 조건은 데이터 액세스 수가 많을 수 밖에 없다.

하지만 created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)의 조건은 데이터 액세스 수가 적다.

성능을 향상시키는 데 중요한 요소 중 하나는 데이터 액세스 수를 줄이는 것이다.

따라서 department보다 created_at에 인덱스를 생성하는 게 더 좋을 것으로 예상할 수 있다.

그렇다면 WHERE 문에 created_at 인덱스를 거는 것과 ORDER BY 문에 있는 salary에 인덱스를 거는 것 둘 중에 어느것이 더 효율적인지 고려해봐야 한다.

결론부터 말하면 WHERE 절에 있는 created_at에 인덱스를 거는 것이 ORDER BY절에 있는 salary에 인덱스를 거는 것 보다 더 효율적이다.

salary에 인덱스

CREATE INDEX idx_salary ON users (salary);

-- 성능 측정
SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

-- 실행 계획
EXPLAIN SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

-- 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

인덱스를 걸기 전보다 약 5배정도 느려진 것을 확인할 수 있다.

위의 실행 계획에서 type이 index이면서 salary 인덱스를 사용한 걸로 봐서 인덱스 풀 스캔을 했음을 알 수 있다.

-> Limit: 100 row(s) 
 (cost=9.09 rows=3.33) (actual time=23.8..1009 rows=100 loops=1)
    -> Filter: ((users.created_at >= <cache>((now() - interval 3 day))) and (users.department = 'Sales'))  
    (cost=9.09 rows=3.33) (actual time=23.8..1009 rows=100 loops=1)
        -> Index scan on users using idx_salary  
        (cost=9.09 rows=100) (actual time=1.03..943 rows=974891 loops=1)

idx_salary 인덱스를 활용해 인덱스 풀 스캔을 했다. 이 인덱스를 활용했기 때문에 정렬 과정이 따로 필요없다.
그런 뒤에 WHERE 조건을 만족시키는 데이터 100개를 필터링한다. 이 때, 인덱스에는 created_at, department 정보가 없기 때문에 실제 테이블에 접근해서 조건을 만족하는 지 확인해야한다. 풀 테이블 스캔과 거의 다를 바가 없다.
조건을 만족시키는 데이터 100개를 찾는 순간 더 이상 탐색을 하지 않는다. (LIMIT 100)

created_at에 인덱스

ALTER TABLE users DROP INDEX idx_salary; -- 기존 인덱스 삭제
CREATE INDEX idx_created_at ON users (created_at);

-- 성능 측정
SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

-- 실행 계획
EXPLAIN SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

-- 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

인덱스를 걸지 않은 200ms보다 약 6~7배 정도 빨라졌음을 알 수 있다.

type이 range인걸 보니 인덱스 레인지 스캔을 활용했음을 알 수 있다.

-> Limit: 100 row(s)  
(cost=504 rows=100) (actual time=8.94..8.96 rows=100 loops=1)
    -> Sort: users.salary, limit input to 100 row(s) per chunk  
    (cost=504 rows=1120) (actual time=8.94..8.95 rows=100 loops=1)
        -> Filter: (users.department = 'Sales')  
        (cost=504 rows=1120) (actual time=0.269..8.84 rows=104 loops=1)
            -> Index range scan on users using idx_created_at over ('2024-06-30 01:21:20' <= created_at), 
            with index condition: (users.created_at >= <cache>((now() - interval 3 day)))  
            (cost=504 rows=1120) (actual time=0.0537..8.54 rows=1120 loops=1)

idx_created_at 인덱스를 활용해 인덱스 레인지 스캔을 했다. 이 때, users.created_at >= ((now() - interval 3 day))을 만족하는 데이터에만 액세스했다. (rows=1120)
1번 과정에서 액세스한 데이터 중에서 users.department = ‘Sales’을 만족하는 데이터를 필터링했다. (rows=104)
2번 과정에서 필터링한 104개의 데이터를 users.salary를 기준으로 정렬시켰다.
LIMIT으로 인해 100개의 데이터만 가져왔다.

HAVING문 튜닝

HAVING문 대신에 WHERE문을 쓸 수 있는지 체크

DROP TABLE IF EXISTS users; 

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(100),
    salary INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000; 

-- 더미 데이터 삽입 쿼리
INSERT INTO users (name, age, department, salary, created_at)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')) AS name,  -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    FLOOR(1 + RAND() * 100) AS age, -- 1부터 100 사이의 난수로 생성
    CASE 
        WHEN n % 10 = 1 THEN 'Engineering'
        WHEN n % 10 = 2 THEN 'Marketing'
        WHEN n % 10 = 3 THEN 'Sales'
        WHEN n % 10 = 4 THEN 'Finance'
        WHEN n % 10 = 5 THEN 'HR'
        WHEN n % 10 = 6 THEN 'Operations'
        WHEN n % 10 = 7 THEN 'IT'
        WHEN n % 10 = 8 THEN 'Customer Service'
        WHEN n % 10 = 9 THEN 'Research and Development'
        ELSE 'Product Management'
    END AS department,  -- 의미 있는 단어 조합으로 부서 이름 생성
    FLOOR(1 + RAND() * 1000000) AS salary,    -- 1부터 1000000 사이의 난수로 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

인덱스 생성

CREATE INDEX idx_age ON users (age);

조히 성능 확인

SELECT age, MAX(salary) FROM users
GROUP BY age
HAVING age >= 20 AND age < 30;

약 800ms 정도의 시간이 소요

실행 계획 조회

EXPLAIN SELECT age, MAX(salary) FROM users
GROUP BY age
HAVING age >= 20 AND age < 30;

type이 index인걸로 봐서 인덱스 풀 스캔을 하고 있다.

세부 실행 계획 조회

EXPLAIN ANALYZE SELECT age, MAX(salary) FROM users
GROUP BY age
HAVING age >= 20 AND age < 30;

-> Filter: ((users.age >= 20) and (users.age < 30))  (cost=200263 rows=101) (actual time=208..882 rows=10 loops=1)
    -> Group aggregate: max(users.salary)  (cost=200263 rows=101) (actual time=38.4..882 rows=100 loops=1)
        -> Index scan on users using idx_age  (cost=100624 rows=996389) (actual time=1.53..850 rows=1e+6 loops=1)

성능 개선

SELECT age, MAX(salary) FROM users
WHERE age >= 20 AND age < 30
GROUP BY age;

HAVING 문 대신에 WHERE 문에 조건을 넣으니 약 150ms 정도 시간이 걸린다.

실행 계획 조회

EXPLAIN SELECT age, MAX(salary) FROM users
WHERE age >= 20 AND age < 30
GROUP BY age;

EXPLAIN ANALYZE SELECT age, MAX(salary) FROM users
WHERE age >= 20 AND age < 30
GROUP BY age;

-> Group aggregate: max(users.salary)  (cost=111397 rows=101) (actual time=75.7..198 rows=10 loops=1)
    -> Index range scan on users using idx_age over (20 <= age < 30), 
    with index condition: ((users.age >= 20) and (users.age < 30))  (cost=91143 rows=202540) (actual time=0.582..193 rows=99990 loops=1)

HAVING 대신에 WHERE문을 사용함으로써 GROUP BY를 처리하기 전에 데이터를 필터링했다.

그런 뒤에 필터링 된 데이터를 기반으로 GROUP BY를 진행했다.

'데이터베이스 > MySQL' 카테고리의 다른 글

[DB 성능 최적화] 실전 SQL 튜닝 (0)	2024.08.24
[DB 성능 최적화] 실행 계획(Explain) (0)	2024.08.22
[DB 성능 최적화] 인덱스(Index) (0)	2024.08.21
[MySQL] 인덱스(Index) (1)	2023.12.06
[MySQL] 뷰(View) (1)	2023.12.05