커버링 인덱스를 적용하여 기존 27초 내외의 조회 속도를 2초 내외로 개선하였으나, 목표로 한 1초 이내는 간헐적으로만 측정되고 있는 문제가 발생.
검색 결과, 인덱스 파편화 또는 캐싱에 의해 느려지거나 빨라진다고 하는데 원인을 파악하지 못함.
사용자가 검색창 키워드를 넣어 검색할 경우 WHERE 조건에 user_nick_name, profile_intro 조건이 포함되어야 함(주석처리)
기획에 의해 페이지네이션 방식을 고수해야 하기 때문에 LIMIT OFFSET 구문을 사용해야 함. 단, 개수기반방식을 활용해 개선할 수 있을것으로 예상됨. (참고 이미지 하단)
쿼리, DDL, EXPLAIN 키워드 결과는 다음과 같음.
SELECT
p.profile_id, p.user_id, p.profile_modify_date,
p.profile_intro, p.profile_image_url,
u.user_grade, u.user_nick_name,
k.keyword_names
FROM (
SELECT m.user_id, GROUP_CONCAT(k.keyword_subject) AS keyword_names
FROM t_m_user_keyword m
USE INDEX (idx_m_user_keyword_keyword_id)
JOIN t_keyword k USE INDEX (idx_keyword_subject) ON m.keyword_id = k.keyword_id
WHERE k.keyword_id IN (
598340246278506499, 598340246278506500, 598340246278506501, 598340246278506502,
598340246282702823, 598340246282702824, 598340246282702825, 598340246282702826,
598340246282702827, 598340246286893150, 598340246286893151, 598340246286893152,
598340246286893153, 598340246291091010, 598340246299478613
)
GROUP BY m.user_id
) k
INNER JOIN t_user_account u USE INDEX (idx_user_covering2)
ON u.user_id = k.user_id
AND u.user_grade IN ('FIRST_GRADE', 'SECOND_GRADE', 'THIRD_GRADE', 'FOURTH_GRADE', 'GRADUATE')
INNER JOIN t_profile p USE INDEX (idx_profile_covering)
ON p.user_id = u.user_id
AND p.profile_active_flag = 1
# WHERE u.user_nick_name LIKE '%검색어%' OR p.profile_intro LIKE '%검색어%'
ORDER BY
p.profile_modify_date DESC, p.user_id DESC
LIMIT 100;
-> Limit: 100 row(s) (cost=183046 rows=0) (actual time=2402..2424 rows=100 loops=1)
-> Nested loop inner join (cost=183046 rows=0) (actual time=2402..2424 rows=100 loops=1)
-> Nested loop inner join (cost=62396 rows=5) (actual time=0.0707..19.2 rows=1109 loops=1)
-> Filter: (p.profile_active_flag = 1) (cost=9.69 rows=10) (actual time=0.0485..1.56 rows=1656 loops=1)
-> Covering index scan on p using idx_profile_covering (cost=9.69 rows=100) (actual time=0.0468..1.26 rows=1837 loops=1)
-> Filter: (u.user_grade in ('FIRST_GRADE','SECOND_GRADE','THIRD_GRADE','FOURTH_GRADE','GRADUATE')) (cost=0.646 rows=0.5) (actual time=0.0098..0.0104 rows=0.67 loops=1656)
-> Covering index lookup on u using idx_user_covering2 (user_id=p.user_id) (cost=0.646 rows=1) (actual time=0.00903..0.00978 rows=1 loops=1656)
-> Index lookup on k using <auto_key0> (user_id=p.user_id) (cost=0.25..2.5 rows=10) (actual time=2.17..2.17 rows=0.0902 loops=1109)
-> Materialize (cost=0..0 rows=0) (actual time=2401..2401 rows=80000 loops=1)
-> Group aggregate: group_concat(t_keyword.keyword_subject separator ',') (actual time=1681..2075 rows=80000 loops=1)
-> Sort: m.user_id (actual time=1681..1788 rows=1.2e+6 loops=1)
-> Stream results (cost=117073 rows=1.15e+6) (actual time=0.0959..989 rows=1.2e+6 loops=1)
-> Nested loop inner join (cost=117073 rows=1.15e+6) (actual time=0.0929..649 rows=1.2e+6 loops=1)
-> Filter: (k.keyword_id in (598340246278506499,598340246278506500,598340246278506501,598340246278506502,598340246282702823,598340246282702824,598340246282702825,598340246282702826,598340246282702827,598340246286893150,598340246286893151,598340246286893152,598340246286893153,598340246291091010,598340246299478613)) (cost=3.4 rows=12) (actual time=0.0593..0.159 rows=15 loops=1)
-> Covering index scan on k using idx_keyword_subject (cost=3.4 rows=24) (actual time=0.0583..0.0916 rows=24 loops=1)
-> Covering index lookup on m using idx_m_user_keyword_keyword_id (keyword_id=k.keyword_id) (cost=979 rows=95747) (actual time=0.0461..34 rows=80000 loops=15)
DDL 코드블럭 열기 닫기
create table babpool_origin_paging.t_keyword
(
keyword_id bigint not null
primary key,
keyword_subject varchar(10) default 'common' not null,
keyword_name varchar(36) default 'none' not null
);
create index idx_keyword_subject
on babpool_origin_paging.t_keyword (keyword_subject);
create table babpool_origin_paging.t_user_account
(
user_id bigint not null
primary key,
user_email varchar(50) default 'none' not null,
user_status varchar(10) default 'active' not null,
user_role varchar(10) default 'ROLE_USER' not null,
user_grade varchar(20) default 'none' not null,
user_nick_name varchar(20) default 'none' null,
user_create_date datetime default CURRENT_TIMESTAMP not null,
user_modify_date datetime default CURRENT_TIMESTAMP not null
);
create table babpool_origin_paging.t_m_user_keyword
(
mapping_id bigint not null
primary key,
user_id bigint not null,
keyword_id bigint not null,
constraint t_m_user_keyword_t_keyword_keyword_id_fk
foreign key (keyword_id) references babpool_origin_paging.t_keyword (keyword_id),
constraint t_m_user_keyword_t_user_account_user_id_fk
foreign key (user_id) references babpool_origin_paging.t_user_account (user_id)
);
create index idx_m_user_keyword_keyword_id
on babpool_origin_paging.t_m_user_keyword (keyword_id, user_id);
create table babpool_origin_paging.t_profile
(
profile_id bigint not null
primary key,
user_id bigint not null,
profile_image_url varchar(150) default 'none' not null,
profile_intro varchar(50) null,
profile_contents varchar(1000) null,
profile_contact_phone varchar(11) null,
profile_contact_chat varchar(200) null,
profile_active_flag tinyint(1) default 1 not null,
profile_create_date datetime default CURRENT_TIMESTAMP not null,
profile_modify_date datetime default CURRENT_TIMESTAMP not null,
constraint t_profile_t_user_account_user_id_fk
foreign key (user_id) references babpool_origin_paging.t_user_account (user_id)
);
create index idx_profile_covering
on babpool_origin_paging.t_profile (profile_modify_date desc, user_id desc, profile_active_flag asc, profile_intro
asc, profile_image_url asc);
create index idx_user_covering2
on babpool_origin_paging.t_user_account (user_id, user_nick_name, user_grade);
📌 목적 / 이슈 내용
📝 상세 내용
DDL 코드블럭 열기 닫기
✔ 체크 리스트
💡 비고