테이블
앞으로 사용할 예제 테이블입니다.
CREATE TABLE users
(
id INT UNSIGNED PRIMARY KEY,
name VARCHAR(50) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT '유저'
;
CREATE TABLE products
(
id INT UNSIGNED PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description VARCHAR(255) NOT NULL,
price INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT '상품'
;
CREATE TABLE reviews
(
id INT UNSIGNED PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
product_id INT UNSIGNED NOT NULL,
contents VARCHAR(255) NOT NULL,
score TINYINT UNSIGNED COMMENT '점수 (1, 2, 3, 4, 5)',
deleted_at DATETIME NULL COMMENT '삭제된 경우에만 입력',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT '상품 리뷰'
;
CREATE TABLE review_images
(
id INT UNSIGNED PRIMARY KEY,
review_id INT UNSIGNED NOT NULL,
sequence TINYINT UNSIGNED NOT NULL COMMENT '표시 순서',
file_name VARCHAR(255) NOT NULL,
file_path VARCHAR(255) NOT NULL,
file_size INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT '리뷰 첨부 이미지'
;유저와 상품에 10만건을 넣었습니다.
리뷰와 리뷰이미지에 각 1억건의 더미데이터를 입력한 상태입니다.
상품 상세페이지에 보여질 리뷰 목록을 페이징처리하는걸 목표로 진행합니다.
우선 product_id로 조회하므로 인덱스를 추가합니다.
== product_id+id로 구성된 BTree를 생성합니다
ALTER TABLE reviews
ADD INDEX idx_product_id (product_id);첫 번째 리뷰 페이징 쿼리
[Query]
SELECT r.*
, u.name AS 'user.name'
FROM reviews r
LEFT OUTER JOIN users u
ON u.id = r.user_id
WHERE r.product_id = 100
ORDER BY r.id DESC
LIMIT 100
[Result]
100 rows / 18.9575ms
[Explain]
┌─────────┬────┬─────────────┬───────┬────────────┬──────────┬─────────────────┬─────────────────┬─────────┬──────────────────┬───────┬──────────┬───────────────────────┐
│ (index) │ id │ select_type │ table │ partitions │ type │ possible_keys │ key │ key_len │ ref │ rows │ filtered │ Extra │
├─────────┼────┼─────────────┼───────┼────────────┼──────────┼─────────────────┼─────────────────┼─────────┼──────────────────┼───────┼──────────┼───────────────────────┤
│ 0 │ 1 │ 'SIMPLE' │ 'r' │ null │ 'ref' │ 'idx_product_id'│ 'idx_product_id'│ '4' │ 'const' │ 42980 │ 100 │ 'Backward index scan' │
│ 1 │ 1 │ 'SIMPLE' │ 'u' │ null │ 'eq_ref' │ 'PRIMARY' │ 'PRIMARY' │ '4' │ 'blog.r.user_id' │ 1 │ 100 │ null │
└─────────┴────┴─────────────┴───────┴────────────┴──────────┴─────────────────┴─────────────────┴─────────┴──────────────────┴───────┴──────────┴───────────────────────┘
-> Limit: 100 row(s) (cost=61954 rows=100) (actual time=0.421..0.682 rows=100 loops=1)
-> Nested loop left join (cost=61954 rows=42980) (actual time=0.42..0.677 rows=100 loops=1)
-> Index lookup on r using idx_product_id (product_id=100) (reverse) (cost=46911 rows=42980) (actual time=0.411..0.428 rows=100 loops=1)
-> Single-row index lookup on u using PRIMARY (id=r.user_id) (cost=0.25 rows=1) (actual time=0.00231..0.00234 rows=1 loops=100)
[Status]
┌──────────────────────────────────┬────────┐
│ (index) │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 723 │
│ Innodb_buffer_pool_reads │ 0 │
│ Handler_read_first │ 0 │
│ Handler_read_key │ 101 │
│ Handler_read_last │ 0 │
│ Handler_read_next │ 0 │
│ Handler_read_prev │ 99 │
│ Handler_read_rnd │ 0 │
│ Handler_read_rnd_next │ 0 │
└──────────────────────────────────┴────────┘
Handler_read_key는 수직탐색 횟수,
Handler_read_next/Handler_read_prev는 인덱스 수평탐색(앞으로/뒤로) 횟수를 나타냅니다
시간도 얼마 안걸리고 상태값도 문제가 없어 보입니다.
20번째 페이지를 조회해봅니다.
20번째 페이지 조회
[Query]
SELECT r.*
, u.name AS 'user.name'
FROM reviews r
LEFT OUTER JOIN users u
ON u.id = r.user_id
WHERE r.product_id = 9000
ORDER BY r.id DESC
LIMIT 1900, 100
[Result]
100 rows / 184.15375ms
[Explain]
┌─────────┬────┬─────────────┬───────┬────────────┬──────────┬─────────────────┬─────────────────┬─────────┬──────────────────┬────────┬──────────┬───────────────────────┐
│ (index) │ id │ select_type │ table │ partitions │ type │ possible_keys │ key │ key_len │ ref │ rows │ filtered │ Extra │
├─────────┼────┼─────────────┼───────┼────────────┼──────────┼─────────────────┼─────────────────┼─────────┼──────────────────┼────────┼──────────┼───────────────────────┤
│ 0 │ 1 │ 'SIMPLE' │ 'r' │ null │ 'ref' │ 'idx_product_id'│ 'idx_product_id'│ '4' │ 'const' │ 378200 │ 100 │ 'Backward index scan' │
│ 1 │ 1 │ 'SIMPLE' │ 'u' │ null │ 'eq_ref' │ 'PRIMARY' │ 'PRIMARY' │ '4' │ 'blog.r.user_id' │ 1 │ 100 │ null │
└─────────┴────┴─────────────┴───────┴────────────┴──────────┴─────────────────┴─────────────────┴─────────┴──────────────────┴────────┴──────────┴───────────────────────┘
-> Limit/Offset: 100/1900 row(s) (cost=545764 rows=100) (actual time=4.36..4.44 rows=100 loops=1)
-> Nested loop left join (cost=545764 rows=378200) (actual time=0.191..4.4 rows=2000 loops=1)
-> Index lookup on r using idx_product_id (product_id=9000) (reverse) (cost=413394 rows=378200) (actual time=0.184..2.75 rows=2000 loops=1)
-> Single-row index lookup on u using PRIMARY (id=r.user_id) (cost=0.25 rows=1) (actual time=727e-6..743e-6 rows=1 loops=2000)
[Status]
┌──────────────────────────────────┬────────┐
│ (index) │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 13569 │
│ Innodb_buffer_pool_reads │ 1486 │
│ Handler_read_first │ 0 │
│ Handler_read_key │ 1995 │
│ Handler_read_last │ 0 │
│ Handler_read_next │ 0 │
│ Handler_read_prev │ 1999 │
│ Handler_read_rnd │ 0 │
│ Handler_read_rnd_next │ 0 │
└──────────────────────────────────┴────────┘실행계획도 괜찮고 시간도 얼마 안걸려서 괜찮아보이지만 status 값이 많이 증가했습니다.
약 2000번의 수직/수평탐색이 발생했습니다.
BTree에서 어떻게 진행됬을지 생각해보니
idx_product_id 인덱스 트리에서 2000건의 리뷰id를 찾아서
리뷰테이블에 2000번 수직탐색이 진행됬고,
조인된 users 테이블에도 2000번의 수직탐색이 일어났습니다.
필요한 100건 외에 1900건에 대해서도 리뷰테이블과 유저테이블에 각각 1900번의 불필요한 수직탐색이 추가됬습니다.
쿼리 최적화의 핵심은 논리적io를 줄이는 것입니다.
필요한 수직탐색만 발생하도록 쿼리를 수정해봅니다.
서브쿼리로 수정한 쿼리
[Query]
SELECT r.*
, u.name AS 'user.name'
FROM (
SELECT r.id
FROM reviews r
WHERE r.product_id = 400
ORDER BY r.id DESC
LIMIT 1900, 100
) a
INNER JOIN reviews r
ON r.id = a.id
INNER JOIN users u
ON u.id = r.user_id
[Result]
100 rows / 71.8225ms
[Explain]
┌─────────┬────┬─────────────┬──────────────┬────────────┬──────────┬──────────────────────┬─────────────────┬─────────┬──────────────────┬───────┬──────────┬────────────────────────────────────┐
│ (index) │ id │ select_type │ table │ partitions │ type │ possible_keys │ key │ key_len │ ref │ rows │ filtered │ Extra │
├─────────┼────┼─────────────┼──────────────┼────────────┼──────────┼──────────────────────┼─────────────────┼─────────┼──────────────────┼───────┼──────────┼────────────────────────────────────┤
│ 0 │ 1 │ 'PRIMARY' │ '<derived2>' │ null │ 'ALL' │ null │ null │ null │ null │ 2000 │ 100 │ null │
│ 1 │ 1 │ 'PRIMARY' │ 'r' │ null │ 'eq_ref' │ 'PRIMARY,idx_user_id'│ 'PRIMARY' │ '4' │ 'a.id' │ 1 │ 100 │ null │
│ 2 │ 1 │ 'PRIMARY' │ 'u' │ null │ 'eq_ref' │ 'PRIMARY' │ 'PRIMARY' │ '4' │ 'blog.r.user_id' │ 1 │ 100 │ null │
│ 3 │ 2 │ 'DERIVED' │ 'r' │ null │ 'ref' │ 'idx_product_id' │ 'idx_product_id'│ '4' │ 'const' │ 42112 │ 100 │ 'Backward index scan; Using index' │
└─────────┴────┴─────────────┴──────────────┴────────────┴──────────┴──────────────────────┴─────────────────┴─────────┴──────────────────┴───────┴──────────┴────────────────────────────────────┘
-> Nested loop inner join (cost=6770 rows=100) (actual time=2.1..3.1 rows=100 loops=1)
-> Nested loop inner join (cost=6260 rows=100) (actual time=2.09..2.64 rows=100 loops=1)
-> Table scan on a (cost=4263..4267 rows=100) (actual time=2.06..2.08 rows=100 loops=1)
-> Materialize (cost=4263..4263 rows=100) (actual time=2.06..2.06 rows=100 loops=1)
-> Limit/Offset: 100/1900 row(s) (cost=4253 rows=100) (actual time=2..2.03 rows=100 loops=1)
-> Covering index lookup on r using idx_product_id (product_id=400) (reverse) (cost=4253 rows=42112) (actual time=1.64..1.91 rows=2000 loops=1)
-> Single-row index lookup on r using PRIMARY (id=a.id) (cost=0.991 rows=1) (actual time=0.0053..0.00534 rows=1 loops=100)
-> Single-row index lookup on u using PRIMARY (id=r.user_id) (cost=0.25 rows=1) (actual time=0.00425..0.00429 rows=1 loops=100)
[Status]
┌──────────────────────────────────┬────────┐
│ (index) │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 528 │
│ Innodb_buffer_pool_reads │ 0 │
│ Handler_read_first │ 0 │
│ Handler_read_key │ 201 │
│ Handler_read_last │ 0 │
│ Handler_read_next │ 0 │
│ Handler_read_prev │ 1999 │
│ Handler_read_rnd │ 0 │
│ Handler_read_rnd_next │ 101 │
└──────────────────────────────────┴────────┘
Handler_read_key 상태값이 많이 줄었는데 확인해봅니다.
서브쿼리에서 idx_product_id 인덱스 트리를 탐색하여 2000개를 읽습니다.
인덱스에는 자동으로 pk가 들어가기 때문에 select에 필요한 review id를 알기 위해 테이블 트리를 탐색할 필요가 없습니다.
이렇게 인덱스에서만 모든 처리를 끝내는걸 커버링인덱스 라고 하며 실행계획엔 using index가 표시됩니다.
서브쿼리가 반환한 100개의 리뷰의 id로 각각 리뷰테이블에서 수직탐색을 하고,
조인된 유저테이블에서도 찾은 리뷰테이블의 user_id로 수직탐색을 하면 쿼리가 종료됩니다.
총 201회의 수직탐색(Handler_read_key)이 일어난 것을 볼 수 있는데
reviews와 users 테이블에 각 100번씩 200회와
서브쿼리에서 product_id 값을 인덱스에서 찾은 1회 입니다.
수평탐색(Handler_read_prev)은 1999회 일어났는데
서브쿼리 내에서 limit이 2000(1900+100)이라 최초 탐색한것부터 1999번 이동해서 2000개를 찾은 것 입니다.
Handler_read_rnd_next은 서브쿼리의 결과가 100건이라 없는거 확인할때까지 총 101번입니다.
수평탐색이 저번 쿼리와 동일하지만 크게 걱정할 건 없습니다.
mysql이 데이터를 읽을 때 하나의 페이지(16kb) 단위로 읽기 때문에,
수직탐색은 필요한 곳을 찾아가느라 1회에 몇개의 페이지를 읽지만,
수평탐색은 하나의 페이지에서 수십~수백개의 행을 읽습니다.
위 쿼리는 더이상 문제가 없는 것 같습니다.
정렬
위 쿼리는 ORDER BY reviews.id DESC 로 정렬하고 있습니다.
그런데 실행계획을 봐도 별도의 정렬을 하고있지 않고 필요한 2000개만 읽고 쿼리를 종료하고 있습니다.
왜 product_id가 일치하는 모든 행을 읽어서 id로 정렬한 후 반환하지 않는지 BTree에서 확인해봅니다.
현재 리뷰 테이블의 인덱스를 product_id로 생성한 상태입니다.
트리의 정렬이 product_id에 따라 정렬됩니다.
인덱스 트리의 리프노드는 가장 앞에 product_id 1이 있고 마지막엔 가장 끝번호인 100000이 있을 것 입니다.
그런데 mysql은 인덱스에 자동으로 pk를 추가한다고 했습니다
모든 리프노드의 각 행에 product_id에 id가 추가되어 있고
product_id가 같으면 id로 정렬되어 있습니다.
# idx_product_id 인덱스 리프노드 예시
┌────────────┬────────────┬────────────┬────────────┬────────────┬────────────┬────────────┐
│ product_id │ 9000 │ 9000 │ 9000 │ 9001 │ 9001 │ 9001 │
│ id │ 99812310 │ 99900520 │ 99901200 │ 155 │ 212235 │ 1021235 │
└────────────┴────────────┴────────────┴────────────┴────────────┴────────────┴────────────┘즉 product_id가 9000인 가장 앞쪽 값을 검색하고 뒤로 수평탐색하면 id로 오름차순 정렬,
product_id가 9000인 가장 뒤쪽 값을 수직탐색해서 앞쪽으로 읽으면 id로 내림차순으로 정렬된 결과를 얻을 수 있습니다.
위 서브쿼리 실행계획에서 'Backward index scan;'가 있는데 원하는 결과가 내림차순이기 때문에 mysql이 뒤에서부터 앞으로 인덱스를 읽은 것 입니다.
별점 필터 추가
리뷰 목록 중 별점(1~5점)을 통한 필터링 기능이 추가됬습니다
이제 원하는 점수의 리뷰만 선택하여 보여줄 수 있어야 합니다.
이전 쿼리에서 where문만 수정하면 어떻게 될지 확인해봅시다.
[Query]
SELECT r.*
, u.name AS 'user.name'
FROM (
SELECT r.id
FROM reviews r
WHERE r.product_id = 1000
and r.score = 3 # 추가된 where 조건
ORDER BY r.id DESC
LIMIT 1900, 100
) a
INNER JOIN reviews r
ON r.id = a.id
INNER JOIN users u
ON u.id = r.user_id
[Result]
100 rows / 817.189ms
[Explain]
┌─────────┬────┬─────────────┬──────────────┬────────────┬──────────┬──────────────────────┬─────────────────┬─────────┬──────────────────┬────────┬──────────┬────────────────────────────────────┐
│ (index) │ id │ select_type │ table │ partitions │ type │ possible_keys │ key │ key_len │ ref │ rows │ filtered │ Extra │
├─────────┼────┼─────────────┼──────────────┼────────────┼──────────┼──────────────────────┼─────────────────┼─────────┼──────────────────┼────────┼──────────┼────────────────────────────────────┤
│ 0 │ 1 │ 'PRIMARY' │ '<derived2>' │ null │ 'ALL' │ null │ null │ null │ null │ 2000 │ 100 │ null │
│ 1 │ 1 │ 'PRIMARY' │ 'r' │ null │ 'eq_ref' │ 'PRIMARY,idx_user_id'│ 'PRIMARY' │ '4' │ 'a.id' │ 1 │ 100 │ null │
│ 2 │ 1 │ 'PRIMARY' │ 'u' │ null │ 'eq_ref' │ 'PRIMARY' │ 'PRIMARY' │ '4' │ 'blog.r.user_id' │ 1 │ 100 │ null │
│ 3 │ 2 │ 'DERIVED' │ 'r' │ null │ 'ref' │ 'idx_product_id' │ 'idx_product_id'│ '4' │ 'const' │ 398160 │ 10 │ 'Using where; Backward index scan' │
└─────────┴────┴─────────────┴──────────────┴────────────┴──────────┴──────────────────────┴─────────────────┴─────────┴──────────────────┴────────┴──────────┴────────────────────────────────────┘
-> Nested loop inner join (cost=399916 rows=100) (actual time=43.8..44.3 rows=100 loops=1)
-> Nested loop inner join (cost=398914 rows=100) (actual time=43.8..43.9 rows=100 loops=1)
-> Table scan on a (cost=396927..396930 rows=100) (actual time=43.8..43.8 rows=100 loops=1)
-> Materialize (cost=396927..396927 rows=100) (actual time=43.8..43.8 rows=100 loops=1)
-> Limit/Offset: 100/1900 row(s) (cost=396917 rows=100) (actual time=41.4..43.8 rows=100 loops=1)
-> Filter: (r.score = 3) (cost=396917 rows=39816) (actual time=0.178..43.8 rows=2000 loops=1)
-> Index lookup on r using idx_product_id (product_id=1000) (reverse) (cost=396917 rows=398160) (actual time=0.178..43.5 rows=10220 loops=1)
-> Single-row index lookup on r using PRIMARY (id=a.id) (cost=0.987 rows=1) (actual time=969e-6..981e-6 rows=1 loops=100)
-> Single-row index lookup on u using PRIMARY (id=r.user_id) (cost=0.496 rows=1) (actual time=0.00306..0.00308 rows=1 loops=100)
[Status]
┌──────────────────────────────────┬────────┐
│ (index) │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 48500 │
│ Innodb_buffer_pool_reads │ 6832 │
│ Handler_read_first │ 0 │
│ Handler_read_key │ 201 │
│ Handler_read_last │ 0 │
│ Handler_read_next │ 0 │
│ Handler_read_prev │ 10219 │
│ Handler_read_rnd │ 0 │
│ Handler_read_rnd_next │ 101 │
└──────────────────────────────────┴────────┘수평탐색이 많이 증가했습니다.
중요한 부분은 서브쿼리의 using index;가 없어졌고 커버링 인덱스로 해결하지 못했습니다.
인덱스로 수평탐색한 pk로 테이블을 수직탐색해도 Handler_read_prev가 1만 증가합니다.
즉 실제로는 리뷰테이블에 대한 수직탐색도 많이 증가했다고 볼 수 있습니다.
기존에 커버링 인덱스로 해결되던 서브쿼리가 score 필터로 인해 테이블까지 탐색해야 처리할 수 있도록 바뀌었기 때문입니다.
잘못된 인덱스 변경 (product_id, score)
커버링 인덱스를 위해 idx_product_id 인덱스를 (product_id, score)로 변경한다면 어떻게 될까요
# 인덱스를 product_id, score 로 구성한 경우 리프노드 예시
┌────────────┬────────────┬────────────┬────────────┬────────────┬────────────┬────────────┐
│ product_id │ 9000 │ 9000 │ 9000 │ 9000 │ 9000 │ 9000 │
│ score │ 1 │ 1 │ 1 │ 2 │ 2 │ 2 │
│ id │ 22 │ 999 │ 1232132 │ 222 │ 55555 │ 1123555 │
└────────────┴────────────┴────────────┴────────────┴────────────┴────────────┴────────────┘첫 번째 문제는 인덱스 트리에서 같은 product_id에 대해 id보다 score가 우선으로 정렬되게 됩니다.
점수 필터링이 없는 기존 쿼리에서 id로 정렬하기 위해 찾는 product_id 의 모든 행을 인덱스에서 다 읽고 정렬해야 합니다.
두 번째 문제는 score를 여러개 선택한 경우에도 발생합니다.
score가 1과 2인것을 검색하려면 1과 2의 검색결과를 합친 후 id로 정렬해서 출력해야 합니다.
커버링 인덱스와 정렬을 고려한 인덱스 변경
이 상황에서 어떻게 해야 커버링 인덱스는 유지하면서 정렬 문제까지 해결할 수 있을지 보겠습니다.
mysql에서 pk는 모든 인덱스에 자동으로 추가됩니다.
하지만 이미 pk가 있는 인덱스는 뒤에 추가로 생성되지 않는 점을 알고 있습니다.
product_id가 동일한 행들에 대해 id로 정렬되어있고, score도 갖고있는 인덱스를 생성해야 합니다.
idx_product_id 인덱스를 변경합니다.
# 기존 인덱스 제거 후 다시 생성
ALTER TABLE reviews
DROP INDEX idx_product_id,
ADD INDEX idx_product_id (product_id, id, score)인덱스를 (product_id, id, score)로 id를 중간에 추가했습니다.
┌────────────┬────────────┬────────────┬────────────┬────────────┬────────────┬────────────┐
│ product_id │ 9000 │ 9000 │ 9000 │ 9000 │ 9000 │ 9000 │
│ id │ 22 │ 222 │ 999 │ 55555 │ 1232132 │ 1123555 │
│ score │ 1 │ 2 │ 1 │ 2 │ 1 │ 2 │
└────────────┴────────────┴────────────┴────────────┴────────────┴────────────┴────────────┘이제 product_id가 같은 행들에 대해 id로 정렬되어있고, score도 갖고있는 인덱스가 되었습니다.
다시 위 쿼리를 실행해봅니다.
[Query]
SELECT r.*
, u.name AS 'user.name'
FROM (
SELECT r.id
FROM reviews r
WHERE r.product_id = 2000
and r.score = 3
ORDER BY r.id DESC
LIMIT 1900, 100
) a
INNER JOIN reviews r
ON r.id = a.id
INNER JOIN users u
ON u.id = r.user_id
[Result]
100 rows / 6.8045ms
[Explain]
┌─────────┬────┬─────────────┬──────────────┬────────────┬──────────┬──────────────────────┬──────────────────┬─────────┬──────────────────┬────────┬──────────┬─────────────────────────────────────────────────┐
│ (index) │ id │ select_type │ table │ partitions │ type │ possible_keys │ key │ key_len │ ref │ rows │ filtered │ Extra │
├─────────┼────┼─────────────┼──────────────┼────────────┼──────────┼──────────────────────┼──────────────────┼─────────┼──────────────────┼────────┼──────────┼─────────────────────────────────────────────────┤
│ 0 │ 1 │ 'PRIMARY' │ '<derived2>' │ null │ 'ALL' │ null │ null │ null │ null │ 2000 │ 100 │ null │
│ 1 │ 1 │ 'PRIMARY' │ 'r' │ null │ 'eq_ref' │ 'PRIMARY,idx_user_id'│ 'PRIMARY' │ '4' │ 'a.id' │ 1 │ 100 │ null │
│ 2 │ 1 │ 'PRIMARY' │ 'u' │ null │ 'eq_ref' │ 'PRIMARY' │ 'PRIMARY' │ '4' │ 'blog.r.user_id' │ 1 │ 100 │ null │
│ 3 │ 2 │ 'DERIVED' │ 'r' │ null │ 'ref' │ 'idx_product_id' │ 'idx_product_id' │ '4' │ 'const' │ 426582 │ 10 │ 'Using where; Backward index scan; Using index' │
└─────────┴────┴─────────────┴──────────────┴────────────┴──────────┴──────────────────────┴──────────────────┴─────────┴──────────────────┴────────┴──────────┴─────────────────────────────────────────────────┘
-> Nested loop inner join (cost=8644 rows=100) (actual time=4.88..5.74 rows=100 loops=1)
-> Nested loop inner join (cost=7018 rows=100) (actual time=4.87..5.35 rows=100 loops=1)
-> Table scan on a (cost=5005..5009 rows=100) (actual time=4.77..4.78 rows=100 loops=1)
-> Materialize (cost=5005..5005 rows=100) (actual time=4.76..4.76 rows=100 loops=1)
-> Limit/Offset: 100/1900 row(s) (cost=4995 rows=100) (actual time=4.53..4.73 rows=100 loops=1)
-> Filter: (r.score = 3) (cost=4995 rows=42658) (actual time=0.111..4.66 rows=2000 loops=1)
-> Covering index lookup on r using idx_product_id (product_id=2000) (reverse) (cost=4995 rows=426582) (actual time=0.105..4.01 rows=9960 loops=1)
-> Single-row index lookup on r using PRIMARY (id=a.id) (cost=1 rows=1) (actual time=0.00501..0.00504 rows=1 loops=100)
-> Single-row index lookup on u using PRIMARY (id=r.user_id) (cost=0.808 rows=1) (actual time=0.00369..0.00372 rows=1 loops=100)
[Status]
┌──────────────────────────────────┬────────┐
│ (index) │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 633 │
│ Innodb_buffer_pool_reads │ 0 │
│ Handler_read_first │ 0 │
│ Handler_read_key │ 201 │
│ Handler_read_last │ 0 │
│ Handler_read_next │ 0 │
│ Handler_read_prev │ 9959 │
│ Handler_read_rnd │ 0 │
│ Handler_read_rnd_next │ 101 │
└──────────────────────────────────┴────────┘status 값은 큰 차이가 나지 않고 using index;가 다시 생겼습니다.
서브쿼리에서 1회 수직탐색 후, 원하는 id를 찾을때까지 테이블 트리에 검색하지 않고 인덱스에서만 해결할 수 있습니다.
2000개를 찾기위해 수평탐색으로 약 1만개를 찾고있으나, 위에서 말햇듯이 수직탐색을 줄이는게 중요합니다.
가상컬럼 활용
위 문제와 비슷하게, 삭제된 리뷰를 결과에서 제거하려면 어떻게 해야될까요?
인덱스를 (product_id, id, score, deleted_at)로 수정하고 where문에 deleted_at is not null 조건을 추가하면 됩니다.
하지만 deleted_at은 datetime 타입으로 5byte를 차지하며, 현재 상황에서 삭제한 시간이 인덱스에 필요하지 않습니다.
mysql8의 가상컬럼 기능을 사용해 인덱스 크기를 줄여봅니다.
ALTER TABLE reviews
ADD COLUMN is_deleted BOOLEAN AS (deleted_at IS NOT NULL) VIRTUAL AFTER deleted_at,
DROP INDEX idx_product_id,
ADD INDEX idx_product_id (product_id, id, score, is_deleted)
;is_deleted라는 가상컬럼을 추가했습니다.
insert나 update에 해당 컬럼을 작성하지 않고 컬럼을 사용할때마다 자동으로 값이 계산되어 나옵니다.
인덱스 트리에는 계산된 값이 저장됩니다. boolean(tinyint)이기 때문에 1byte를 차지하게 됩니다.
실제 테이블트리에는 사용할때마다 계산되기 때문에 용량을 차지하지 않습니다.(virtual 대신 stored로 설정하면 실제 저장까지 됩니다.)
이제 where 문에서 deleted_at is not null 대신 is_deleted = FALSE 로 수정하면 원하는 결과를 얻을 수 있습니다.
리뷰 이미지 추가
마지막으로 첨부된 리뷰의 이미지도 한번에 가져오도록 해보겠습니다.
우선 인덱스를 만듭니다.
ALTER TABLE review_images
ADD INDEX idx_review_id (review_id, sequence);이미지는 review_id로 검색할 것이고, 검색 결과가 sequence로 정렬되도록 만듭니다.
잘못된 1:N 관계 조인
SELECT r.*
, u.id AS 'user.id'
, u.name AS 'user.name'
, ri.id AS 'image.id'
, ri.file_name AS 'image.file_name'
, ri.file_path AS 'image.file_path'
, ri.file_size AS 'image.file_size'
FROM (
SELECT r.id
FROM reviews r
WHERE r.product_id = 2000
AND r.score = 3
AND r.is_deleted = FALSE
ORDER BY r.id DESC
LIMIT 1900, 100
) a
INNER JOIN reviews r
ON r.id = a.id
INNER JOIN users u
ON u.id = r.user_id
LEFT OUTER JOIN review_images ri
ON ri.review_id = r.id리뷰와 리뷰이미지는 1:n 관계입니다. 즉 리뷰 한건에 2개 이상의 이미지가 있을 수 있습니다.
이런 경우 일반 join으로 해결하면 하나의 리뷰가 여러번 출력될 수 있고,
이런 테이블이 여러개 있다면 n * m * ... 으로 출력 크기가 기하급수적으로 증가합니다.
하나의 리뷰 행에 여러개의 이미지를 한번에 보여줘야 합니다.
mysql의 json 기능을 사용해봅시다.
select 서브쿼리로 변경
[Query]
SELECT r.*
, u.id AS 'user.id'
, u.name AS 'user.name'
, IFNULL(
(
SELECT JSON_ARRAYAGG(JSON_OBJECT(
'id', ri.id,
'file_name', ri.file_name,
'file_size', ri.file_size,
'file_path', ri.file_path
)) AS images
FROM review_images ri
WHERE ri.review_id = r.id
),
JSON_ARRAY()
) AS 'images'
FROM (
SELECT r.id
FROM reviews r
WHERE r.product_id = 44000
AND r.score = 3
AND r.is_deleted = FALSE
ORDER BY r.id DESC
LIMIT 1900, 100
) a
INNER JOIN reviews r
ON r.id = a.id
INNER JOIN users u
ON u.id = r.user_id
[Result]
100 rows / 46.54575ms
[Explain]
┌─────────┬────┬──────────────────────┬──────────────┬────────────┬──────────┬──────────────────────┬──────────────────┬─────────┬──────────────────┬────────┬──────────┬─────────────────────────────────────────────────┐
│ (index) │ id │ select_type │ table │ partitions │ type │ possible_keys │ key │ key_len │ ref │ rows │ filtered │ Extra │
├─────────┼────┼──────────────────────┼──────────────┼────────────┼──────────┼──────────────────────┼──────────────────┼─────────┼──────────────────┼────────┼──────────┼─────────────────────────────────────────────────┤
│ 0 │ 1 │ 'PRIMARY' │ '<derived3>' │ null │ 'ALL' │ null │ null │ null │ null │ 2000 │ 100 │ null │
│ 1 │ 1 │ 'PRIMARY' │ 'r' │ null │ 'eq_ref' │ 'PRIMARY,ix_user_id' │ 'PRIMARY' │ '4' │ 'a.id' │ 1 │ 100 │ 'Using where' │
│ 2 │ 1 │ 'PRIMARY' │ 'u' │ null │ 'eq_ref' │ 'PRIMARY' │ 'PRIMARY' │ '4' │ 'blog.r.user_id' │ 1 │ 100 │ null │
│ 3 │ 3 │ 'DERIVED' │ 'r' │ null │ 'ref' │ 'idx_product_id' │ 'idx_product_id' │ '4' │ 'const' │ 418080 │ 10 │ 'Using where; Backward index scan; Using index' │
│ 4 │ 2 │ 'DEPENDENT SUBQUERY' │ 'ri' │ null │ 'ref' │ 'idx_review_id' │ 'idx_review_id' │ '4' │ 'blog.r.id' │ 4 │ 100 │ null │
└─────────┴────┴──────────────────────┴──────────────┴────────────┴──────────┴──────────────────────┴──────────────────┴─────────┴──────────────────┴────────┴──────────┴─────────────────────────────────────────────────┘
-> Nested loop inner join (cost=8646 rows=100) (actual time=2.61..2.97 rows=100 loops=1)
-> Nested loop inner join (cost=7020 rows=100) (actual time=2.61..2.82 rows=100 loops=1)
-> Table scan on a (cost=5007..5011 rows=100) (actual time=2.6..2.6 rows=100 loops=1)
-> Materialize (cost=5007..5007 rows=100) (actual time=2.6..2.6 rows=100 loops=1)
-> Limit/Offset: 100/1900 row(s) (cost=4997 rows=100) (actual time=2.45..2.58 rows=100 loops=1)
-> Filter: (r.score = 3) (cost=4997 rows=41808) (actual time=0.0474..2.53 rows=2000 loops=1)
-> Covering index lookup on r using idx_product_id (product_id=2000) (reverse) (cost=4997 rows=418080) (actual time=0.0448..2.15 rows=9960 loops=1)
-> Filter: (r.user_id is not null) (cost=1 rows=1) (actual time=0.00193..0.00199 rows=1 loops=100)
-> Single-row index lookup on r using PRIMARY (id=a.id) (cost=1 rows=1) (actual time=0.00183..0.00185 rows=1 loops=100)
-> Single-row index lookup on u using PRIMARY (id=r.user_id) (cost=0.808 rows=1) (actual time=0.00132..0.00134 rows=1 loops=100)
-> Select #2 (subquery in projection; dependent)
-> Aggregate: json_arrayagg(json_object('id',ri.id,'file_name',ri.file_name,'file_size',ri.file_size,'file_path',ri.file_path)) (cost=5.1 rows=1) (actual time=0.00159..0.00161 rows=1 loops=100)
-> Index lookup on ri using idx_review_id (review_id=r.id) (cost=4.68 rows=4.25) (actual time=0.00144..0.00144 rows=0 loops=100)
[Status]
┌──────────────────────────────────┬────────┐
│ (index) │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 1139 │
│ Innodb_buffer_pool_reads │ 214 │
│ Handler_read_first │ 0 │
│ Handler_read_key │ 301 │
│ Handler_read_last │ 0 │
│ Handler_read_next │ 0 │
│ Handler_read_prev │ 9959 │
│ Handler_read_rnd │ 0 │
│ Handler_read_rnd_next │ 101 │
└──────────────────────────────────┴────────┘
select 절의 서브쿼리에서 review_images 테이블을 조회합니다.
(lateral join는 데이터가 커질수록 성능이 안좋아져서 사용하지 않았습니다.)
json_object 함수로 여러개의 컬럼을 하나의 object로 담고,
json_arrayagg 집계함수로 여러개의 object를 하나의 array에 담았습니다.
이미지가 없는 경우 빈 배열을 담기위해 ifnull 함수에 json_array()를 추가합니다.
select는 from절 이후에 동작하기 때문에 원하는 100건의 리뷰에 대해서만 서브쿼리가 실행됩니다.
서브쿼리는 매번 수직탐색 1회와, 수평탐색이 찾은개수 만큼 실행됩니다. (2개인 경우 3번째까지 확인해야되서 2번 필요)
정리
추가/수정한 인덱스와 쿼리 입니다.
# 리뷰 테이블의 가상컬럼 is_deleted
ALTER TABLE reviews
ADD COLUMN is_deleted BOOLEAN AS (deleted_at IS NOT NULL) VIRTUAL AFTER deleted_at;
# 리뷰 테이블의 인덱스
CREATE INDEX idx_product_id
ON reviews (product_id, id, score, is_deleted);
# 리뷰 이미지 테이블의 인덱스
CREATE INDEX idx_review_id
ON review_images (review_id, sequence);
# 페이징 쿼리
SELECT r.*
, u.id AS 'user.id'
, u.name AS 'user.name'
, IFNULL(
(
SELECT JSON_ARRAYAGG(JSON_OBJECT(
'id', ri.id,
'file_name', ri.file_name,
'file_size', ri.file_size,
'file_path', ri.file_path
)) AS images
FROM review_images ri
WHERE ri.review_id = r.id
),
JSON_ARRAY()
) AS 'images'
FROM (
SELECT r.id
FROM reviews r
WHERE r.product_id = 44000
AND r.score = 3
AND r.is_deleted = FALSE
ORDER BY r.id DESC
LIMIT 1900, 100
) a
INNER JOIN reviews r
ON r.id = a.id
INNER JOIN users u
ON u.id = r.user_id같은 실행계획이라도 실제 동작이 다른 경우가 많습니다.
실제 BTree에서 어떤식으로 탐색이 되는지 이해하는게 중요합니다.
정렬은 대부분 정렬하는데 걸리는 시간보다 정렬하기위해 필요한 데이터를 모두 조회하는 게 성능상 문제가 됩니다.
대부분의 페이징 조회에는 정렬에 인덱스를 사용할 수 있도록 만드는게 중요합니다.
