이 글은 인프런의 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;- 먼저 likes 테이블에서 post_id를 기준으로 GROUP BY를 수행하여 각 게시물에 대한 좋아요 수를 집계한다. 이때 GROUP BY는 post_id만을 사용하므로, 인덱스를 활용하여 효율적으로 조회할 수 있다. 즉, 테이블의 모든 데이터를 읽지 않고, 인덱스만으로도 필요한 정보를 얻을 수 있기 때문에 성능이 더 빠르다. 이것을 커버링 인덱스라고 한다.
- 그런 다음, 좋아요 수가 많은 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 |
