페이지네이션 성능을 개선하기 위해 인덱스를 적용하고 싶었습니다ㅠ

Question

안녕하세요, 페이징 조회 쿼리를 개선하고 있습니다.연관 테이블마다 최소 100만~200만건의 데이터를 삽입하고, 기존 쿼리에서 조인 순서 변경, 조인/정렬/그룹/조건절 에서 사용될 인덱스(또는 복합인덱스) 추가 등을 통해 기존 1분 이상 걸리던 조회 쿼리를 2.2초 이내로 개선하는데는 성공했습니다.하지만 목표로 한 1초 이내는 제가 관련 개념에 대해 많이 부족해 어려움이 있습니다. 키워드 식별 값에 올 수 있는 최대 개수가 약 20여개가 되는데, 최대개수에 도달할 수록 실행속도가 저하되었고, 키워드 개수가 10여개 미만인 적은 경우에는 간신히 1초 이내가 가능했습니다.어떤 부분을 수정해야하는지 조언해주시면 정말 감사드리겠습니다 :)순서대로 쿼리, ERD, 실행계획 결과는 다음과 같습니다.SELECT p.profile_id, p.user_id, p.profile_modify_date, u.user_grade, p.profile_image_url, p.profile_intro, p.profile_contents, 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 ON m.keyword_id = k.keyword_id WHERE k.keyword_id IN (키워드 식별 값 최대 20여개) GROUP BY m.user_id ) k INNER JOIN t_user_account u ON u.user_id = k.user_id AND u.user_grade IN (사용자 학력 최대 10여개) INNER JOIN t_profile p USE INDEX (idx_profile_active_modify_user) ON p.user_id = u.user_id AND p.profile_active_flag = 1 ORDER BY p.profile_modify_date DESC, p.user_id DESC LIMIT 100;*************************** 1. row *************************** id: 1 select_type: PRIMARY table: p partitions: NULL type: ref possible_keys: idx_profile_active_modify_user key: idx_profile_active_modify_user key_len: 1 ref: const rows: 496515 filtered: 100.00 Extra: Backward index scan *************************** 2. row *************************** id: 1 select_type: PRIMARY table: u partitions: NULL type: eq_ref possible_keys: PRIMARY,idx_user_grade key: PRIMARY key_len: 8 ref: babpool_paging.p.user_id rows: 1 filtered: 100.00 Extra: Using where *************************** 3. row *************************** id: 1 select_type: PRIMARY table: partitions: NULL type: ref possible_keys: key: key_len: 8 ref: babpool_paging.p.user_id rows: 10 filtered: 100.00 Extra: NULL *************************** 4. row *************************** id: 2 select_type: DERIVED table: k partitions: NULL type: index possible_keys: PRIMARY key: idx_keyword_subject key_len: 42 ref: NULL rows: 24 filtered: 62.50 Extra: Using where; Using index; Using temporary; Using filesort *************************** 5. row *************************** id: 2 select_type: DERIVED table: m partitions: NULL type: ref possible_keys: idx_m_user_keyword_keyword_id key: idx_m_user_keyword_keyword_id key_len: 8 ref: babpool_paging.k.keyword_id rows: 91188 filtered: 100.00 Extra: Using index-> Limit: 100 row(s) (cost=1.81e+6 rows=0) (actual time=2517..2519 rows=100 loops=1) -> Nested loop inner join (cost=1.81e+6 rows=0) (actual time=2517..2519 rows=100 loops=1) -> Nested loop inner join (cost=570686 rows=496515) (actual time=0.342..2.13 rows=130 loops=1) -> Index lookup on p using idx_profile_active_modify_user (profile_active_flag=1) (reverse) (cost=68649 rows=496515) (actual time=0.323..1.37 rows=179 loops=1) -> Filter: (u.user_grade in ('FIRST_GRADE','SECOND_GRADE','THIRD_GRADE','FOURTH_GRADE','GRADUATE')) (cost=0.911 rows=1) (actual time=0.00387..0.00398 rows=0.726 loops=179) -> Single-row index lookup on u using PRIMARY (user_id=p.user_id) (cost=0.911 rows=1) (actual time=0.00322..0.00327 rows=1 loops=179) -> Index lookup on k using (user_id=p.user_id) (cost=0.25..2.5 rows=10) (actual time=19.4..19.4 rows=0.769 loops=130) -> Materialize (cost=0..0 rows=0) (actual time=2516..2516 rows=80000 loops=1) -> Group aggregate: group_concat(t_keyword.keyword_subject separator ',') (actual time=1780..2185 rows=80000 loops=1) -> Sort: m.user_id (actual time=1780..1893 rows=1.2e+6 loops=1) -> Stream results (cost=138918 rows=1.37e+6) (actual time=0.0776..1027 rows=1.2e+6 loops=1) -> Nested loop inner join (cost=138918 rows=1.37e+6) (actual time=0.075..698 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=15) (actual time=0.0372..0.149 rows=15 loops=1) -> Covering index scan on k using idx_keyword_subject (cost=3.4 rows=24) (actual time=0.0347..0.0705 rows=24 loops=1) -> Covering index lookup on m using idx_m_user_keyword_keyword_id (keyword_id=k.keyword_id) (cost=750 rows=91188) (actual time=0.0492..37.7 rows=80000 loops=15) 기획에서 페이지네이션 방식을 택해 OFFSET 방식을 고수해야 하는 상황에서 범위 조건을 사용한 개수 기반 방식 으로 최적화 하기 위해 1회차 페이징 쿼리의 ORDER BY 절에 날짜와 PK 식별 값을 명시했습니다.

개발잡부 · Answer

커버링 인덱스 했는데도 더 성능개선이 필요하면 쿼리단에서 해결하기보다는 다른 방법을 생각하시는게...

Henry7 · Answer

몇 가지 생각 해 보면방안1. 위 데이터를 기반으로 배치를 돌려, 고성능 단일 테이블을 만듭니다. (실시간 조회는 포기해야 함)방안2. 검색 엔진을 도입합니다. Elasticsearch 등방안3. 쿼리에 사용 되는 일부 데이터를 제거 후, App 메모리에 캐시화 하여 올려서 매핑시키는 방법방안4. 3과 비슷한 방법인데, 단일 테이블 등 조회 후, 해당 페이지에 해당하는 데이터만 화면에서 비동기로 호출해서 가져와서 추가 렌더링 시키기.

Clickin · Answer

Limit 방식 페이지네이션은 이전 데이터를 읽되 버리고 다음을 fetch하는 방식이니 최대 페이지에 가까워질수록 느립니다.Zero offset paging 키워드로 검색 후 해당 기법을 적용해보시면 좋겠습니다.Zero offset paging은 where 절에 id나 커버링 인덱스가 추가되기 때문에 실행계획에서부터 이전 데이터는 고려되지 않아 성능이 일정합니다.

windmii · Answer

offset 써야하나요? cursor는 사용하면안되나요? 몇 백건이면 cursor가 속도 개선에 직빵이긴한데 그리고 쿼리를 개선해도 윗분들이 말씀하신대로 한계가 있을거에요 엘라스틱서치 도입하는건 뭔가 배꼽이 커지는 느낌이고, 기획을 변경해서 cursor 페이지네이션으로 변경한다 or 인덱스를 걸어버린다 근데 인덱스걸때 기존에 데이터들이 존재하면 새로운 인덱스테이블 생성되면 DB lock 걸릴 수 있습니다

풍월상신(가니메디안) · Answer

테이블을 분할하고, 연관을 temp table 로 생성하는 게 최선일 것 같습니다.SE 같은 대규모도 사실 비슷하게 처리하고 있는 것 같습니다.

페이지네이션 성능을 개선하기 위해 인덱스를 적용하고 싶었습니다ㅠ

5개의 답변

Q&A 목록