ReBugs
블로그 이미지

Home

About Me
11-23 21:26
light
데이터베이스/MySQL

[DB 성능 최적화] 실전 SQL 튜닝

ReBugs
|2024. 8. 24. 00:19

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

유저 이름으로 특정 기간에 작성된 글 검색

기본 테이블 생성

DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

 

더미 데이터 추가

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

-- users 테이블에 더미 데이터 삽입
INSERT INTO users (name, 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자리 숫자로 구성된 이름 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

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

 

기존 SQL문 성능 측정

SELECT p.id, p.title, p.created_at
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.name = 'User0000046'
AND p.created_at BETWEEN '2022-01-01' AND '2024-03-07';

약 150ms 정도의 시간이 소요

 

실행 계획 조회

EXPLAIN SELECT p.id, p.title, p.created_at
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.name = 'User0000046'
AND p.created_at BETWEEN '2022-01-01' AND '2024-03-07';

풀 테이블 스캔을 하기 때문에 인덱스를 추가해야 한다.

인덱스를 추가할 수 있는 컬럼이 users.name과 posts.created_at이 있다.

일단 둘 다 인덱스로 추가해보자. 

 

성능 개선을 위한 인덱스 생성

CREATE INDEX idx_name ON users (name);
CREATE INDEX idx_created_at ON posts (created_at);

옵티마이저는 posts.created_at 인덱스가 존재하는 걸 알지만 굳이 사용하지 않는 게 효율적이라고 판단했다.

그렇기 때문에 사용하지 않는 인덱스는 삭제해주자. 

ALTER TABLE posts DROP INDEX idx_created_at;

 

다시 성능 측정

150ms에서 20ms로 성능이 많이 개선되었다. 

 

특정 부서에서 최대 연봉을 가진 사용자들 조회

테이블 생성

DROP TABLE IF EXISTS posts;
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() * 100000) AS salary,    -- 1부터 100000 사이의 난수로 나이 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

 

기존 SQL문 성능 측정

SELECT *
FROM users
WHERE salary = (SELECT MAX(salary) FROM users)
AND department IN ('Sales', 'Marketing', 'IT');

약 280ms 정도 소요

 

실행 계획 조회

type이 ALL -> 풀 테이블 스캔

인덱스를 활용해서 풀 테이블 스캔을 하지 않도록 바꿔보자.

 

인덱스 생성

데이터 액세스 수를 크게 줄일 수 있는 컬럼은 중복 정도가 낮은 컬럼이다. 따라서 salary로 인덱스를 생성

CREATE INDEX idx_salary ON users (salary);

 

성능 측정

SELECT *
FROM users
WHERE salary = (SELECT MAX(salary) FROM users)
AND department IN ('Sales', 'Marketing', 'IT');

280ms에서 25ms 정도로 성능이 향상

 

실행 계획 조회

EXPLAIN SELECT *
FROM users
WHERE salary = (SELECT MAX(salary) FROM users)
AND department IN ('Sales', 'Marketing', 'IT');

인덱스를 활용해서 데이터를 액세스 했고, 액세스 수도 6개로 확 줄었다. 

 

부서별 최대 연봉을 가진 사용자들 조회

테이블 생성

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() * 100000) AS salary,    -- 1부터 100000 사이의 난수로 나이 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

 

SQL문 성능 체크

SELECT u.id, u.name, u.department, u.salary, u.created_at
FROM users u
JOIN (
    SELECT department, MAX(salary) AS max_salary
    FROM users
    GROUP BY department
) d ON u.department = d.department AND u.salary = d.max_salary;

약 600ms 정도 걸린다. 

 

실행계획 조회

EXPLAIN SELECT u.*
FROM users u
JOIN (
    SELECT department, MAX(salary) AS max_salary
    FROM users
    GROUP BY department
) d ON u.department = d.department AND u.salary = d.max_salary;

JOIN 문 내부에 있는 서브쿼리를 실행시킬 때 풀 테이블 스캔이 이뤄어졌음을 알 수 있다. 

 

성능 개선

GROUP BY department는 department를 기준으로 정렬을 시킨 뒤에 MAX(salary) 값을 구하게 된다. 이 때, MAX(salary)를 구하기 위해 이리저리 찾아다닐 수 밖에 없다. 

이를 해결하기 위해 (department, salary)의 멀티 컬럼 인덱스가 있으면 department를 기준으로 정렬을 시키는 작업을 하지 않아도 되고, 심지어 MAX(salary)도 빠르게 찾을 수 있다. 멀티 컬럼 인덱스를 생성해보자.

CREATE INDEX idx_department_salary ON users (department, salary);

 

다시 성능 측정

SELECT u.*
FROM users u
JOIN (
    SELECT department, MAX(salary) AS max_salary
    FROM users
    GROUP BY department
) d ON u.department = d.department AND u.salary = d.max_salary;

600ms에서 20ms로 30배 정도 성능이 향상됐다. 

 

실행 계획을 조회

실행 계획을 조회해봐도 인덱스를 잘 활용해서 데이터를 찾고 있고, 접근한 rows 자체도 훨씬 적어졌다. 

 

특정 유저의 2023년 주문 데이터 조회

테이블 생성

DROP TABLE IF EXISTS users; 
DROP TABLE IF EXISTS orders; 

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

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

 

더미 데이터 생성

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

-- users 테이블에 더미 데이터 삽입
INSERT INTO users (name, 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자리 숫자로 구성된 이름 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

-- orders 테이블에 더미 데이터 삽입
INSERT INTO orders (ordered_at, user_id)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS ordered_at, -- 최근 10년 내의 임의의 날짜와 시간 생성
    FLOOR(1 + RAND() * 1000000) AS user_id    -- 1부터 1000000 사이의 난수로 급여 생성
FROM cte;

 

기존 SQL문 성능 조회

SELECT *
FROM orders
WHERE YEAR(ordered_at) = 2023
ORDER BY ordered_at
LIMIT 30;

약 180ms 정도가 걸린다. 

 

실행계획 조회

EXPLAIN SELECT *
FROM orders
WHERE YEAR(ordered_at) = 2023
ORDER BY ordered_at
LIMIT 30;

 

 

성능 개선

ordered_at에 인덱스를 추가하면 풀 테이블 스캔을 막을 수 있을 것 같다. 그래서 인덱스를 추가해보자. 

CREATE INDEX idx_ordered_at ON orders (ordered_at);

 

700ms로 더 느려졌다.

 

실행계획을 살펴보면

인덱스 풀 스캔을 했다. 풀 테이블 스캔 대신에 인덱스 풀 스캔을 하면 더 빨라져야 한다.  또한 WHERE문으로 특정 범위의 데이터만 접근하면 인덱스 풀 스캔이 아니라 인덱스 레인지 스캔이 나와야한다.

 

문제는 인덱스의 컬럼을 가공해서 사용했기 때문이다. 

그래서 인덱스를 제대로 활용 하지 못한 것이다. 인덱스의 컬럼을 가공하지 않게 SQL문을 다시 수정해보자. 

 

성능 개선2

SELECT *
FROM orders
WHERE ordered_at >= '2023-01-01 00:00:00' 
  AND ordered_at < '2024-01-01 00:00:00'
ORDER BY ordered_at
LIMIT 30;

180ms에서 20ms로 9배 가량 성능을 향상시켰다. 

 

실행 계획도 인덱스 레인지 스캔으로 바뀌었다.

 

 

2024년 1학기 평균 성적이 100점인 학생 조회

테이블 생성

DROP TABLE IF EXISTS scores;
DROP TABLE IF EXISTS subjects;
DROP TABLE IF EXISTS students;

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

CREATE TABLE subjects (
    subject_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE scores (
    score_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    subject_id INT,
    year INT,
    semester INT,
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
);

 

더미 데이터 생성

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

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

-- subjects 테이블에 과목 데이터 삽입
INSERT INTO subjects (name)
VALUES
    ('Mathematics'),
    ('English'),
    ('History'),
    ('Biology'),
    ('Chemistry'),
    ('Physics'),
    ('Computer Science'),
    ('Art'),
    ('Music'),
    ('Physical Education'),
    ('Geography'),
    ('Economics'),
    ('Psychology'),
    ('Philosophy'),
    ('Languages'),
    ('Engineering');

-- scores 테이블에 더미 데이터 삽입
INSERT INTO scores (student_id, subject_id, year, semester, score)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    FLOOR(1 + RAND() * 1000000) AS student_id,  -- 1부터 1000000 사이의 난수로 학생 ID 생성
    FLOOR(1 + RAND() * 16) AS subject_id,             -- 1부터 16 사이의 난수로 과목 ID 생성
    YEAR(NOW()) - FLOOR(RAND() * 5) AS year,   -- 최근 5년 내의 임의의 연도 생성
    FLOOR(1 + RAND() * 2) AS semester,                -- 1 또는 2 중에서 랜덤하게 학기 생성
    FLOOR(1 + RAND() * 100) AS score -- 1부터 100 사이의 랜덤한 점수 생성
FROM cte;

 

SQL문 성능 측정

SELECT 
    st.student_id,
    st.name,
    AVG(sc.score) AS average_score
FROM 
    students st
JOIN 
    scores sc ON st.student_id = sc.student_id
GROUP BY 
    st.student_id,
    st.name,
    sc.year,
    sc.semester
HAVING 
    AVG(sc.score) = 100
    AND sc.year = 2024
    AND sc.semester = 1;

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

 

성능 개선

HAVING절에 굳이 있지 않아도 될 조건이 HAVING 절에 포함되어 있다.

WHERE 문으로 옮길 수 있는 조건을 옮긴 뒤 성능을 다시 테스트

SELECT 
    st.student_id,
    st.name,
    AVG(sc.score) AS average_score
FROM 
    students st
JOIN 
    scores sc ON st.student_id = sc.student_id
WHERE 
    sc.year = 2024
    AND sc.semester = 1
GROUP BY 
    st.student_id,
    st.name
HAVING 
    AVG(sc.score) = 100;

WHERE문으로 옮길 수 있는 조건을 옮기면서, 불필요한 GROUP BY 컬럼을 삭제했다. 

450ms 정도로 성능이 향상됐다. 

 

좋아요 많은 순으로 게시글 조회

기본 테이블 생성

DROP TABLE IF EXISTS likes;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS posts;


CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE likes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT,
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

 

더미 데이터 추가

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

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

-- users 테이블에 더미 데이터 삽입
INSERT INTO users (name, 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자리 숫자로 구성된 이름 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

-- likes 테이블에 더미 데이터 삽입
INSERT INTO likes (post_id, user_id, created_at)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    FLOOR(1 + RAND() * 1000000) AS post_id,    -- 1부터 1000000 사이의 난수로 급여 생성
    FLOOR(1 + RAND() * 1000000) AS user_id,    -- 1부터 1000000 사이의 난수로 급여 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

 

SQL 성능 측정

SELECT
    p.id,
    p.title,
    p.created_at,
    COUNT(l.id) AS like_count
FROM
    posts p
INNER JOIN
    likes l ON p.id = l.post_id
GROUP BY
    p.id, p.title, p.created_at
ORDER BY
    like_count DESC
LIMIT 30;

대략 2600ms 정도의 시간이 소요되고 있다.

 

실행 계획 조회

EXPLAIN SELECT
    p.id,
    p.title,
    p.created_at,
    COUNT(l.id) AS like_count
FROM
    posts p
INNER JOIN
    likes l ON p.id = l.post_id
GROUP BY
    p.id, p.title, p.created_at
ORDER BY
    like_count DESC
LIMIT 30;

 

실행 계획 세부 내용 조회

EXPLAIN ANALYZE SELECT
    p.id,
    p.title,
    p.created_at,
    COUNT(l.id) AS like_count
FROM
    posts p
INNER JOIN
    likes l ON p.id = l.post_id
GROUP BY
    p.id, p.title, p.created_at
ORDER BY
    like_count DESC
LIMIT 30;
-> Limit: 30 row(s)  (actual time=2775..2775 rows=30 loops=1)
    -> Sort: like_count DESC, limit input to 30 row(s) per chunk  (actual time=2775..2775 rows=30 loops=1)
        -> Table scan on <temporary>  (actual time=2675..2745 rows=575582 loops=1)
            -> Aggregate using temporary table  (actual time=2675..2675 rows=575582 loops=1)
                -> Nested loop inner join  (cost=449599 rows=997632) (actual time=0.126..920 rows=1e+6 loops=1)
                    -> Table scan on p  (cost=100428 rows=997632) (actual time=0.0937..115 rows=1e+6 loops=1)
                    -> Covering index lookup on l using post_id (post_id=p.id)  (cost=0.25 rows=1) (actual time=602e-6..703e-6 rows=1 loops=1e+6)

세부 실행 계획을 보니 INNER JOIN과 GROUP BY(Aggreagte using temporary table)에 시간을 많이 사용했다.

이 이유를 추측하면 INNER JOIN, GROUP BY를 수행할 때 풀 테이블 스캔으로 조회한 데이터 100만개를 가지고 처리를 해서 오래 걸렸다고 추측할 수 있다.

 

성능 개선

SELECT p.*, l.like_count
FROM posts p
INNER JOIN
	(SELECT post_id, count(post_id) AS like_count FROM likes l
	GROUP BY l.post_id
	ORDER BY like_count DESC
	LIMIT 30) l
ON p.id = l.post_id;
  1. 먼저 likes 테이블에서 post_id를 기준으로 GROUP BY를 수행하여 각 게시물에 대한 좋아요 수를 집계한다. 이때 GROUP BY는 post_id만을 사용하므로, 인덱스를 활용하여 효율적으로 조회할 수 있다. 즉, 테이블의 모든 데이터를 읽지 않고, 인덱스만으로도 필요한 정보를 얻을 수 있기 때문에 성능이 더 빠르다. 이것을 커버링 인덱스라고 한다.
  2. 그런 다음, 좋아요 수가 많은 30개의 post_id를 찾은 후, 이를 posts 테이블과 INNER JOIN을 통해 결합한다. 이 과정에서 미리 필터링된 30개의 행만을 사용하여 INNER JOIN을 수행하므로 데이터 액세스가 훨씬 줄어들어 성능이 최적화된다.

 

성능도 2500ms에서 170ms로 아주 많이 개선되었다. 

 

성능 개선 후 실행 계획

EXPLAIN SELECT p.*, l.like_count
FROM posts p
INNER JOIN
	(SELECT post_id, count(post_id) AS like_count FROM likes l
	GROUP BY l.post_id
	ORDER BY like_count DESC
	LIMIT 30) l
ON p.id = l.post_id;

풀 테이블 스캔으로 액세스한 데이터의 수가 30으로 줄었다. 그리고 l이라는 테이블에서 인덱스 풀 스캔을 했음을 알 수 있다.

즉, 대부분의 데이터를 원래 풀 테이블 스캔을 하던 걸 풀 인덱스 스캔으로 고친 것이다. 

 

실행 계획 세부 내용 조회

EXPLAIN ANALYZE SELECT p.*, l.like_count
FROM posts p
INNER JOIN
	(SELECT post_id, count(post_id) AS like_count FROM likes l
	GROUP BY l.post_id
	ORDER BY like_count DESC
	LIMIT 30) l
ON p.id = l.post_id;
-> Nested loop inner join  (cost=20.5 rows=30) (actual time=227..227 rows=30 loops=1)
    -> Filter: (l.post_id is not null)  (cost=0.196..5.88 rows=30) (actual time=227..227 rows=30 loops=1)
        -> Table scan on l  (cost=2.5..2.5 rows=0) (actual time=227..227 rows=30 loops=1)
            -> Materialize  (cost=0..0 rows=0) (actual time=227..227 rows=30 loops=1)
                -> Limit: 30 row(s)  (actual time=227..227 rows=30 loops=1)
                    -> Sort: like_count DESC, limit input to 30 row(s) per chunk  (actual time=227..227 rows=30 loops=1)
                        -> Stream results  (cost=200702 rows=573484) (actual time=0.0883..199 rows=575582 loops=1)
                            -> Group aggregate: count(l.post_id)  (cost=200702 rows=573484) (actual time=0.0837..163 rows=575582 loops=1)
                                -> Covering index scan on l using idx_post_id  (cost=100912 rows=997899) (actual time=0.074..101 rows=1e+6 loops=1)
    -> Single-row index lookup on p using PRIMARY (id=l.post_id)  (cost=0.392 rows=1) (actual time=0.0019..0.00192 rows=1 loops=30)

실제 커버링 인덱스를 활용했음을 알 수 있다. 그리고 풀 테이블 스캔의 데이터보다 훨씬 크기가 작은 커버링 인덱스만을 활용해서 GROUP BY를 실행하니 훨씬 속도가 빠른 걸 알 수 있다. 

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

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

티스토리툴바