이번에도 페이징 쿼리를 더 알아보겠습니다.
저번글에서 사용한 테이블에 추가하여 사용합니다.
CREATE TABLE products
(
id INT UNSIGNED NOT NULL
PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description VARCHAR(255) NOT NULL,
price INT UNSIGNED NOT NULL,
review_count INT UNSIGNED NOT NULL COMMENT '리뷰 개수',
review_score INT UNSIGNED NOT NULL COMMENT '리뷰 점수 총합',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
) COMMENT '상품';
CREATE TABLE categories
(
id SMALLINT UNSIGNED NOT NULL
PRIMARY KEY,
parent_id SMALLINT UNSIGNED NOT NULL,
name VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT '카테고리';
CREATE TABLE product_category_mapping
(
product_id INT UNSIGNED NOT NULL,
category_id SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (category_id, product_id)
) COMMENT '상품-카테고리 매핑'
이번글에서는 상품 목록 검색 쿼리 작성을 목표로 합니다.
카테고리는 총 1000개이며 N단계로 나눠지고 부모의 id를 참조하고 있습니다.
상품 목록의 기능은 다음과 같습니다.
카테고리가 3단계로 [옷] - [상의] - [티셔츠]로 구성되있다면
[상의]를 선택하면 [상의]와 [티셔츠] 에 매핑된 상품이 나와야 합니다.
[옷]을 선택한다면 [옷], [상의], [티셔츠]에 매핑된 상품들이 모두 나와야 합니다.
우선 성능을 고려하지 않은 쿼리입니다.
[Query]
WITH RECURSIVE cte AS (
SELECT c.id
FROM categories c
WHERE c.id IN (11, 101, 201)
UNION ALL
SELECT c.id
FROM categories c
INNER JOIN cte
ON cte.id = c.parent_id
)
SELECT *
FROM (
SELECT DISTINCT pcm.product_id
FROM product_category_mapping pcm
INNER JOIN cte
ON cte.id = pcm.category_id
) a
INNER JOIN products p
ON p.id = a.product_id
ORDER BY p.price DESC
LIMIT 500, 100
[Result]
100 rows / 59.79275ms
[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 │ 5252 │ 100 │ 'Using temporary; Using filesort' │
│ 1 │ 1 │ 'PRIMARY' │ 'p' │ null │ 'eq_ref' │ 'PRIMARY' │ 'PRIMARY' │ '4' │ 'a.product_id' │ 1 │ 100 │ null │
│ 2 │ 2 │ 'DERIVED' │ '<derived3>' │ null │ 'ALL' │ null │ null │ null │ null │ 10 │ 100 │ 'Using where; Using temporary' │
│ 3 │ 2 │ 'DERIVED' │ 'pcm' │ null │ 'ref' │ 'PRIMARY' │ 'PRIMARY' │ '4' │ 'cte.id' │ 525 │ 100 │ 'Using index' │
│ 4 │ 3 │ 'DERIVED' │ 'c' │ null │ 'range' │ 'PRIMARY' │ 'PRIMARY' │ '4' │ null │ 3 │ 100 │ 'Using where' │
│ 5 │ 4 │ 'UNION' │ 'cte' │ null │ 'ALL' │ null │ null │ null │ null │ 3 │ 100 │ 'Recursive; Using where' │
│ 6 │ 4 │ 'UNION' │ 'c' │ null │ 'ref' │ 'idx_parent_id' │ 'idx_parent_id' │ '4' │ 'cte.id' │ 2 │ 100 │ 'Using index' │
└─────────┴────┴─────────────┴──────────────┴────────────┴──────────┴─────────────────┴─────────────────┴─────────┴────────────────┴──────┴──────────┴───────────────────────────────────┘
-> Limit/Offset: 100/500 row(s) (actual time=38.3..38.3 rows=100 loops=1)
-> Sort: p.price DESC, limit input to 600 row(s) per chunk (actual time=38.3..38.3 rows=600 loops=1)
-> Stream results (cost=3559 rows=5253) (actual time=7.9..32.1 rows=19647 loops=1)
-> Nested loop inner join (cost=3559 rows=5253) (actual time=7.9..24.7 rows=19647 loops=1)
-> Table scan on a (cost=1653..1721 rows=5253) (actual time=7.89..8.99 rows=19647 loops=1)
-> Materialize (cost=1653..1653 rows=5253) (actual time=7.89..7.89 rows=19647 loops=1)
-> Table scan on <temporary> (cost=1059..1127 rows=5253) (actual time=5.88..7.04 rows=19647 loops=1)
-> Temporary table with deduplication (cost=1059..1059 rows=5253) (actual time=5.87..5.87 rows=19647 loops=1)
-> Nested loop inner join (cost=534 rows=5253) (actual time=0.0896..3.39 rows=21710 loops=1)
-> Filter: (cte.id is not null) (cost=7.26..3.62 rows=10) (actual time=0.0552..0.0612 rows=29 loops=1)
-> Table scan on cte (cost=7.91..10.3 rows=10.6) (actual time=0.0549..0.0586 rows=29 loops=1)
-> Materialize recursive CTE cte (cost=7.66..7.66 rows=10.6) (actual time=0.0546..0.0546 rows=29 loops=1)
-> Filter: (c.id in (11,101,201)) (cost=2.26 rows=3) (actual time=0.0179..0.0209 rows=3 loops=1)
-> Index range scan on c using PRIMARY over (id = 11) OR (id = 101) OR (id = 201) (cost=2.26 rows=3) (actual time=0.0168..0.0195 rows=3 loops=1)
-> Repeat until convergence
-> Nested loop inner join (cost=4.34 rows=7.56) (actual time=0.0026..0.0146 rows=13 loops=2)
-> Filter: (cte.id is not null) (cost=2.84 rows=3) (actual time=500e-6..0.00152 rows=14.5 loops=2)
-> Scan new records on cte (cost=2.84 rows=3) (actual time=291e-6..646e-6 rows=14.5 loops=2)
-> Covering index lookup on c using idx_parent_id (parent_id=cte.id) (cost=0.334 rows=2.52) (actual time=624e-6..766e-6 rows=0.897 loops=29)
-> Covering index lookup on pcm using PRIMARY (category_id=cte.id) (cost=5.76 rows=525) (actual time=0.0327..0.0856 rows=749 loops=29)
-> Single-row index lookup on p using PRIMARY (id=a.product_id) (cost=0.25 rows=1) (actual time=691e-6..708e-6 rows=1 loops=19647)
[Status]
┌──────────────────────────────────┬────────┐
│ (index) │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 39544 │
│ Innodb_buffer_pool_reads │ 0 │
│ Handler_read_first │ 0 │
│ Handler_read_key │ 19708 │
│ Handler_read_last │ 0 │
│ Handler_read_next │ 21736 │
│ Handler_read_prev │ 0 │
│ Handler_read_rnd │ 0 │
│ Handler_read_rnd_next │ 39355 │
└──────────────────────────────────┴────────┘
with 문이 가장 먼저 실행됩니다.
선택한 카테고리id로 카테고리를 찾고 자식 카테고리를 재귀적으로 찾도록 되어있습니다.
다음으로 서브쿼리에서는 with문으로 찾은 카테고리id로 모든 상품번호를 매핑테이블에서 찾습니다.
이때 각 카테고리id에 대해 수직탐색1회와 상품개수만큼의 수평탐색이 일어납니다.
상품 번호가 중복될 수 있으므로 distinct 처리를 해줬습니다.
서브쿼리에서 찾은 상품의 id로 각각 수직탐색하여 상품을 찾고, price로 정렬하여 결과를 출력합니다.
가장 문제가 되는 부분은 페이징 목록임에도 정렬을 위해 일치하는 모든 상품을 id로 각각 조회하는 것 입니다.
정렬을 위해서는 where 조건과 정렬 조건이 하나의 인덱스에 있어야 하는데
정렬을 위한 price는 상품테이블에, 조회를 위한 카테고리id는 매핑 테이블에 있습니다.
상품 테이블에 매핑된 카테고리의 id들을 json으로 넣는 방법을 사용해보겠습니다.
# json 컬럼 추가
ALTER TABLE products
ADD COLUMN category_ids JSON NOT NULL AFTER id;
# 추가한 컬럼 업데이트
UPDATE products p
SET p.category_ids = IFNULL(
(
SELECT JSON_ARRAYAGG(category_id)
FROM product_category_mapping pcm
WHERE pcm.product_id = p.id
), JSON_ARRAY())
WHERE TRUE
상품 테이블에 매핑된 카테고리id를 모두 넣었습니다.
json 타입은 일반 인덱스를 사용할 수 없고, 멀티밸류 인덱스를 사용해야 합니다.
ALTER TABLE products
ADD INDEX idx_category_ids ((CAST(category_ids AS UNSIGNED ARRAY)))
쿼리를 수정해봅니다.
[Query]
WITH RECURSIVE cte AS (
SELECT c.id
FROM categories c
WHERE c.id IN (49, 220, 582)
UNION ALL
SELECT c.id
FROM categories c
INNER JOIN cte
ON cte.id = c.parent_id
)
SELECT p.*
FROM products p
WHERE JSON_OVERLAPS(
p.category_ids,
(
SELECT JSON_ARRAYAGG(id) AS ids
FROM cte
)
)
ORDER BY p.price
LIMIT 500, 100
[Result]
100 rows / 26.741ms
[Explain]
┌─────────┬────┬─────────────┬──────────────┬────────────┬─────────┬────────────────────┬────────────────────┬─────────┬──────────┬──────┬──────────┬───────────────────────────────┐
│ (index) │ id │ select_type │ table │ partitions │ type │ possible_keys │ key │ key_len │ ref │ rows │ filtered │ Extra │
├─────────┼────┼─────────────┼──────────────┼────────────┼─────────┼────────────────────┼────────────────────┼─────────┼──────────┼──────┼──────────┼───────────────────────────────┤
│ 0 │ 1 │ 'PRIMARY' │ 'p' │ null │ 'range' │ 'idx_category_ids' │ 'idx_category_ids' │ '9' │ null │ 3861 │ 100 │ 'Using where; Using filesort' │
│ 1 │ 2 │ 'SUBQUERY' │ '<derived3>' │ null │ 'ALL' │ null │ null │ null │ null │ 10 │ 100 │ null │
│ 2 │ 3 │ 'DERIVED' │ 'c' │ null │ 'range' │ 'PRIMARY' │ 'PRIMARY' │ '4' │ null │ 3 │ 100 │ 'Using where' │
│ 3 │ 4 │ 'UNION' │ 'cte' │ null │ 'ALL' │ null │ null │ null │ null │ 3 │ 100 │ 'Recursive; Using where' │
│ 4 │ 4 │ 'UNION' │ 'c' │ null │ 'ref' │ 'idx_parent_id' │ 'idx_parent_id' │ '4' │ 'cte.id' │ 2 │ 100 │ 'Using index' │
└─────────┴────┴─────────────┴──────────────┴────────────┴─────────┴────────────────────┴────────────────────┴─────────┴──────────┴──────┴──────────┴───────────────────────────────┘
-> Limit/Offset: 100/500 row(s) (cost=1739 rows=100) (actual time=10.6..10.6 rows=100 loops=1)
-> Sort: p.price, limit input to 600 row(s) per chunk (cost=1739 rows=3861) (actual time=10.5..10.6 rows=600 loops=1)
-> Filter: json_overlaps(cast(category_ids as unsigned array),json'[49, 220, 582, 424, 529, 977]') (cost=1739 rows=3861) (actual time=0.0237..9.03 rows=3830 loops=1)
-> Index range scan on p using idx_category_ids over (977 MEMBER OF (category_ids)) OR (582 MEMBER OF (category_ids)) OR (4 more) (cost=1739 rows=3861) (actual time=0.0222..6.58 rows=3830 loops=1)
[Status]
┌──────────────────────────────────┬────────┐
│ (index) │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 11801 │
│ Innodb_buffer_pool_reads │ 0 │
│ Handler_read_first │ 0 │
│ Handler_read_key │ 15 │
│ Handler_read_last │ 0 │
│ Handler_read_next │ 3860 │
│ Handler_read_prev │ 0 │
│ Handler_read_rnd │ 0 │
│ Handler_read_rnd_next │ 13 │
└──────────────────────────────────┴────────┘
json_overlaps 함수는 두 json 배열을 비교해서 하나라도 동일하면 TRUE를 반환하는 함수입니다.
status 값이 많이 줄었지만 첫 번째 쿼리와 동일하게 모든 상품을 가져와서 정렬 후 반환하고 있습니다.
mysql의 멀티밸류 인덱스 특성상 여러 컬럼으로 구성할 수 없어서 정렬을 위한 price를 인덱스에 추가할 수 없습니다.
카테고리가 많고 각 카테고리에 매핑된 상품의 개수가 적다면 이 방법도 괜찮을 것 같습니다.
위 방법이 카테고리에 맞는 상품을 먼저 모두 찾은 다음 정렬하였다면,
이제 해볼것은 정렬되어있는 목록을 찾으면서 카테고리가 조건에 맞는지 확인하는 방법입니다.
이미 정렬이 되있기 때문에 원하는 개수를 찾으면 검색을 종료할 수 있습니다.
필요한 인덱스는 (price, category_ids) 입니다.
하지만 json 타입은 일반 인덱스에 포함할 수 없으니 테이블을 새로 만듭니다.
CREATE TABLE product_search
(
product_id INT UNSIGNED NOT NULL,
category_id INT UNSIGNED NOT NULL,
price INT UNSIGNED NOT NULL,
review_count INT UNSIGNED NOT NULL,
review_score INT UNSIGNED NOT NULL,
review_avg DOUBLE UNSIGNED AS (IF(review_count > 0, review_score / review_count, 0)) VIRTUAL,
PRIMARY KEY (product_id, category_id),
INDEX idx_price (price, product_id),
INDEX idx_review_count (review_count, product_id),
INDEX idx_review_avg (review_avg, product_id)
)
;
상품-카테고리 매핑과 비슷하지만 정렬용 컬럼들이 추가되었습니다.
하나의 상품이 카테고리별로 여러번 입력되는 구조입니다.
리뷰 평균 점수를 위해 개수와 총합으로 가상 컬럼을 생성했습니다.
쿼리를 해봅니다.
[Query]
WITH RECURSIVE cte AS (
SELECT c.id
FROM categories c
WHERE c.id IN (1, 2, 3, 4, 5, 6, 7, 9, 10, 11, 12, 13, 14, 21, 23, 24, 25, 49, 220, 582)
UNION ALL
SELECT c.id
FROM categories c
INNER JOIN cte
ON cte.id = c.parent_id
)
SELECT p.*
FROM (
SELECT ps.product_id
FROM product_search ps
WHERE ps.category_id IN (
SELECT id
FROM cte
)
GROUP BY ps.price
, ps.product_id
ORDER BY ps.price DESC
LIMIT 500, 100
) a
INNER JOIN products p
ON p.id = a.product_id
[Result]
100 rows / 11.3725ms
[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 │ 600 │ 100 │ null │
│ 1 │ 1 │ 'PRIMARY' │ 'p' │ null │ 'eq_ref' │ 'PRIMARY' │ 'PRIMARY' │ '4' │ 'a.product_id' │ 1 │ 100 │ null │
│ 2 │ 2 │ 'DERIVED' │ 'ps' │ null │ 'index' │ 'idx_price' │ 'idx_price' │ '8' │ null │ 600 │ 100 │ 'Backward index scan; Using index' │
│ 3 │ 2 │ 'DERIVED' │ '<subquery3>' │ null │ 'eq_ref' │ '<auto_distinct_key>' │ '<auto_distinct_key>' │ '3' │ 'blog.ps.category_id' │ 1 │ 100 │ 'Using where' │
│ 4 │ 3 │ 'MATERIALIZED' │ '<derived4>' │ null │ 'ALL' │ null │ null │ null │ null │ 70 │ 100 │ null │
│ 5 │ 4 │ 'DERIVED' │ 'c' │ null │ 'range' │ 'PRIMARY' │ 'PRIMARY' │ '2' │ null │ 20 │ 100 │ 'Using where' │
│ 6 │ 5 │ 'UNION' │ 'cte' │ null │ 'ALL' │ null │ null │ null │ null │ 20 │ 100 │ 'Recursive; Using where' │
│ 7 │ 5 │ 'UNION' │ 'c' │ null │ 'ref' │ 'idx_parent_id' │ 'idx_parent_id' │ '2' │ 'cte.id' │ 2 │ 100 │ 'Using index' │
└─────────┴────┴────────────────┴───────────────┴────────────┴──────────┴───────────────────────┴───────────────────────┴─────────┴───────────────────────┴──────┴──────────┴────────────────────────────────────┘
-> Nested loop inner join (cost=100186 rows=100) (actual time=9.15..9.69 rows=100 loops=1)
-> Table scan on a (cost=99616..99620 rows=100) (actual time=9.14..9.15 rows=100 loops=1)
-> Materialize (cost=99616..99616 rows=100) (actual time=9.14..9.14 rows=100 loops=1)
-> Limit/Offset: 100/500 row(s) (cost=99606 rows=100) (actual time=7.26..9.13 rows=100 loops=1)
-> Group (no aggregates) (cost=99606 rows=600) (actual time=0.182..9.1 rows=600 loops=1)
-> Nested loop inner join (cost=99546 rows=600) (actual time=0.174..8.95 rows=983 loops=1)
-> Covering index scan on ps using idx_price (reverse) (cost=1.09 rows=600) (actual time=0.0386..3 rows=9637 loops=1)
-> Filter: (ps.category_id = `<subquery3>`.id) (cost=1421..1421 rows=1) (actual time=483e-6..500e-6 rows=0.102 loops=9637)
-> Single-row index lookup on <subquery3> using <auto_distinct_key> (id=ps.category_id) (cost=1465..1465 rows=1) (actual time=347e-6..358e-6 rows=0.102 loops=9637)
-> Materialize with deduplication (cost=44.3..44.3 rows=70.4) (actual time=0.133..0.133 rows=135 loops=1)
-> Filter: (cte.id is not null) (cost=33.9..37.2 rows=70.4) (actual time=0.111..0.123 rows=135 loops=1)
-> Table scan on cte (cost=33.9..37.2 rows=70.4) (actual time=0.111..0.118 rows=135 loops=1)
-> Materialize recursive CTE cte (cost=33.8..33.8 rows=70.4) (actual time=0.11..0.11 rows=135 loops=1)
-> Filter: (c.id in (1,2,3,4,5,6,7,9,10,11,12,13,14,21,23,24,25,49,220,582)) (cost=12 rows=20) (actual time=0.00542..0.0148 rows=20 loops=1)
-> Index range scan on c using PRIMARY over (id = 1) OR (id = 2) OR (18 more) (cost=12 rows=20) (actual time=0.00458..0.0128 rows=20 loops=1)
-> Repeat until convergence
-> Nested loop inner join (cost=14.8 rows=50.4) (actual time=0.00237..0.0435 rows=57.5 loops=2)
-> Filter: (cte.id is not null) (cost=4.75 rows=20) (actual time=500e-6..0.00408 rows=67.5 loops=2)
-> Scan new records on cte (cost=4.75 rows=20) (actual time=312e-6..0.00185 rows=67.5 loops=2)
-> Covering index lookup on c using idx_parent_id (parent_id=cte.id) (cost=0.263 rows=2.52) (actual time=418e-6..489e-6 rows=0.852 loops=135)
-> Single-row index lookup on p using PRIMARY (id=a.product_id) (cost=0.927 rows=1) (actual time=0.00519..0.00522 rows=1 loops=100)
[Status]
┌──────────────────────────────────┬────────┐
│ (index) │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 599 │
│ Innodb_buffer_pool_reads │ 55 │
│ Handler_read_first │ 0 │
│ Handler_read_key │ 9893 │
│ Handler_read_last │ 1 │
│ Handler_read_next │ 115 │
│ Handler_read_prev │ 9636 │
│ Handler_read_rnd │ 0 │
│ Handler_read_rnd_next │ 372 │
└──────────────────────────────────┴────────┘
선택한 카테고리의 개수가 많고, 대부분의 상품이 해당할수록 성능이 좋습니다.
마지막으로 cte를 제거합니다.
1000개행 정도는 recursive cte로 재귀적으로 검색하는거보다,
전체를 조회해서 서버 코드에서 해결하는게 좋습니다.
SELECT id
, parent_id
FROM categories
전체 조회해서 id에 맞는 카테고리와 자식 카테고리를 서버에서 가져옵니다.
가져온 카테고리 목록을 통해 조회합니다.
[Query]
SELECT p.*
FROM (
SELECT ps.product_id
FROM product_search ps
WHERE ps.category_id IN (1, 2, 3, 4, 5, 6, 7, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 23, 24, 25, 49, 220, 582)
GROUP BY ps.price
, ps.product_id
ORDER BY ps.price DESC
LIMIT 500, 100
) a
INNER JOIN products p
ON p.id = a.product_id
[Result]
100 rows / 15.113ms
[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 │ 600 │ 100 │ null │
│ 1 │ 1 │ 'PRIMARY' │ 'p' │ null │ 'eq_ref' │ 'PRIMARY' │ 'PRIMARY' │ '4' │ 'a.product_id' │ 1 │ 100 │ null │
│ 2 │ 2 │ 'DERIVED' │ 'ps' │ null │ 'index' │ 'idx_price' │ 'idx_price' │ '8' │ null │ 600 │ 50 │ 'Using where; Backward index scan; Using index' │
└─────────┴────┴─────────────┴──────────────┴────────────┴──────────┴───────────────┴─────────────┴─────────┴────────────────┴──────┴──────────┴─────────────────────────────────────────────────┘
-> Nested loop inner join (cost=583 rows=0) (actual time=5.93..6.35 rows=100 loops=1)
-> Table scan on a (cost=63.6..63.6 rows=0) (actual time=5.91..5.92 rows=100 loops=1)
-> Materialize (cost=61.1..61.1 rows=0) (actual time=5.91..5.91 rows=100 loops=1)
-> Limit/Offset: 100/500 row(s) (cost=61.1 rows=0) (actual time=4.86..5.9 rows=100 loops=1)
-> Group (no aggregates) (cost=61.1 rows=300) (actual time=0.0545..5.89 rows=600 loops=1)
-> Filter: (ps.category_id in (1,2,3,4,5,6,7,9,10,11,12,13,14,15,16,17,18,19,20,21,23,24,25,49,220,582)) (cost=31.1 rows=300) (actual time=0.0495..5.85 rows=663 loops=1)
-> Covering index scan on ps using idx_price (reverse) (cost=31.1 rows=600) (actual time=0.0463..4.82 rows=32613 loops=1)
-> Single-row index lookup on p using PRIMARY (id=a.product_id) (cost=0.866 rows=1) (actual time=0.00419..0.00421 rows=1 loops=100)
[Status]
┌──────────────────────────────────┬────────┐
│ (index) │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 558 │
│ Innodb_buffer_pool_reads │ 82 │
│ Handler_read_first │ 0 │
│ Handler_read_key │ 101 │
│ Handler_read_last │ 1 │
│ Handler_read_next │ 0 │
│ Handler_read_prev │ 32612 │
│ Handler_read_rnd │ 0 │
│ Handler_read_rnd_next │ 101 │
└──────────────────────────────────┴────────┘
상품 검색이 완료되었습니다.
검색용 테이블이 추가되어 상품 테이블이 변경될때마다 수정해줘야 합니다.
화면에 보여지는 금액과 같은 데이터는 원본 상품 테이블을 참조하기 때문에 큐나 이벤트 등을 사용해 비동기적으로 수정해주어도 됩니다.
상품의 개수나 카테고리의 개수, 매핑된 개수에 따라 쿼리의 방법이 달라집니다.
일반적으로 현재 데이터에서 가장 최악의 성능을 피하는 방법을 택해야 합니다.
인덱스를 특정 부분만 읽지만 테이블에 조금 더 많은 수직탐색을 할 것인지,
인덱스를 정렬된 채로 읽어서 원하는 항목을 찾아 테이블에 수직탐색을 줄일지,
아니면 데이터의 성격과 기능에 따라 다른 방법이 필요할 수 있습니다.