[DB] MySQL 검색 쿼리 성능 개선기

Loopy·2023년 8월 23일
Springdb삽질로그
3

삽질기록

목록 보기
23/29
post-thumbnail

60만건의 유실물 데이터를 내용으로 검색할 때, 프론트에서 가끔 응답 시간이 너무 오래 걸린다는 피드백을 받고 성능을 올려보기로 했다.

1. 성능 하락 원인 파악

기존 쿼리 성능

select * from tb_lost_post
        where title like '%1호선%' or content like '%1호선%'
        order by created_at desc
        limit 10 offset 10000;  // 4.7

MySQL 책을 읽기전에는, limit 과 offset 를 걸었지만 실행계획을 봤을 때 테이블 풀 스캔이 일어난 이유가 무엇인지 이해가 안됐었다.

우선, 60만건 기준으로 아래 방법으로 각 조건에 대해 측정해보자.

측정하는 방법

SET PROFILING=1;
SET PROFILING_HISTORY_SIZE=30;
SHOW profiles;

EXPLAIN ANALYZE ...; # 2025.06 추가

1. 아무런 조건이 없을 때

select * from tb_lost_post;

특정 범위가 주어진 것도 아니기 때문에 클러스터링 인덱스로 조회되지 못하고, 풀 테이블 스캔이 일어난다. 풀 테이블 스캔은 랜덤 I/O 가 아닌 순차 I/O 방식이므로, 디스크 헤더를 한번만 움직이고 쭉 읽으면 되므로 생각보다 많은 데이터에도 빠른 성능을 보여준다.

실제로 테스트해보면 0.02s 정도로 측정되었다.

🫧 풀 테이블 스캔이 일어나는 조건

  1. 테이블 레코드 건수가 너무 작아 페이지 1 개로 구성되는 경우, 인덱스를 통해 읽는 것보다 풀 테이블 스캔이 빠르다.
  2. WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
  3. 인덱스 레인지 스캔을 사용할 수 있어도, 조건이 일치하는 레코드 건수가 전체 테이블의 20% 이상이라면 풀 테이블 스캔이 빠르다.

따라서 위에서는 우선 2 번 조건에 의해 풀 테이블 스캔이 일어났을 것이다.

2. 아무런 조건이 없을 때 + 페이징 적용

다음으로 페이징을 적용해보자.

select * from tb_lost_post
    limit 10 offset 0;

성능이 갑자기 엄청 빨라졌다. 왜 이런 현상이 발생한 것일까?

MYSQL은 두 가지 방식으로 쿼리가 처리되는데, 스트리밍과 버퍼링 방식이다.

  1. 스트리밍 처리 방식

서버 쪽에서 처리할 데이터가 어느정도 되는지에 관계없이, 조건에 일치하는 레코드가 검색될 때 마다 바로 클라이언트로 전송하므로 매우 빠른 응답 시간을 보장한다.

주로 인덱스를 사용해 정렬을 할 때 스트리밍 방식으로 처리된다. limit 가 없어도 스캔의 결과가 아무런 버퍼링 처리나 필터링 과정 없이 바로 클라이언트로 전송되기에 빠르다.

하지만 여기에 limit 까지 붙이면 클라이언트에 바로바로 반환을 하기 때문에, 개수를 충족하는 순간 바로 동작을 멈추므로 매우 빨라진다.

  1. 버퍼링 처링 방식

ORDER BY 나 GROUP BY 조건이 있는데 해당 컬럼에 인덱스가 없는 경우 스트리밍 방식을 사용할 수 없다.

우선 WHERE 조건에 일치하는 레코드를 모두 가져온 이후 정렬하거나 그루핑하는 과정이 일어나야 하기 때문인데, 결국 클라이언트로 응답 속도가 매우 늦어진다.

따라서 해당 방식은 limit 로 결과 건수를 제한해도, 네트워크로 전송되는 레코드의 건수만 줄일 뿐 MySQL 작업의 성능 향상에는 효과가 크지 않다.

결론적으로 앞에서는 정렬이나 그룹핑 조건이 없었고, 이로 인해 스트리밍 처리 방식이 적용되어서 속도가 엄청 빨라진것으로 볼 수 있다.

3. WHERE 조건 + 페이징 적용

where 조건이 인덱스를 타기만 한다면, 위에서와 큰 차이는 없다.

select * from tb_lost_post
    where lost_post_id < 10000
    limit 10 offset 0;

4. 정렬 조건이 붙을 경우

그렇다면 실제로 이제 정렬 조건을 붙여보자. 현재 created_at 필드에는 인덱스를 생성하지 않았다.

select * from tb_lost_post
    order by created_at desc;

정렬 조건이 붙었더니, 시간이 5 초대로 매우 느려진 것을 볼 수 있다.

select * from tb_lost_post
    order by created_at desc
    limit 10 offset 0;

앞에서 얘기했듯이, 혹시나 해서 페이징을 적용해도 4 초대로 큰 차이로 성능 개선 효과가 존재하지 않는다.

🫧 정렬 조건과 페이징 적용
페이징을 적용해서 레코드 건수를 줄여도, 조건에 해당하는 모든 데이터를 대상으로 최신순으로 정렬이 일어나야 하기 때문에 버퍼링 방식이 선택된다.

따라서 실행계획을 분석해보았더니, 처음 보는 Filesort 라는 방식이 사용되었다.

Q. FileSort 가 뭐지?

MySQL에서 정렬을 처리하는 방법은 크게 두가지이다.

1. 인덱스를 통해 정렬하는 방법

INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬이 되어있으므로 별도의 작업이 필요 없이 순서대로 읽기만 하면 되서 성능이 매우 빠르다. 하지만 당연히 조회 말고 다른 변경 쿼리에 대해서는 느리고, 인덱스가 저장될 때 디스크 공간과 InnoDB 버퍼 풀을 위한 메모리가 많이 필요하다는 단점도 있다.

select * from tb_lost_post
    order by lost_post_id desc
    limit 10 offset 0;

위와 같은 쿼리에 대해서는, 클러스터링 인덱스에 대한 역방향 스캔이 정상적으로 적용된다. 해당 포스팅에서 말했듯이 인덱스는 정방향 스캔이 역방향 스캔보다 빠르다. 이런 경우, 아예 내림차순 인덱스 를 만드는게 나을 수도 있다.

2. FileSort를 이용하는 방법

인덱스를 이용하지 않고 MySQL 서버가 별도의 정렬 처리를 수행했다는 것을 나타낸다. 문제는 정렬을 수행하기 위해, 소트 버퍼(Sort buffer) 라고 하는 별도의 메모리 공간을 할당받는다.

하지만 할당된 범위를 벗어날 만큼 많은 데이터가 들어오는 경우 MySQL 는 정렬 레코드를 여러 조각으로 나누고, 임시 저장을 위해 디스크 공간을 사용해버린다. 결국 디스크를 왔다갔다 하면서 정렬을 하기 때문에 수많은 디스크 I/O 가 발생하고, 마지막에 각 정렬된 레코드들을 다시 병합하면서 정렬이 완료되므로 성능이 매우 느려진다.

위 테스트 쿼리와 같은 경우 전체 데이터를 정렬해야 하므로 external sort 가 발생해 5 초라는 느린 성능이 나오게 되었다.

5. %% 형식의 LIKE 검색 조건이 붙은 경우

select * from tb_lost_post
        where title like '%1호선%' or content like '%1호선%'
        order by created_at desc
        limit 10 offset 0;

%char 가 아닌 %char% 조건의 like가 포함되면 인덱스를 통해 범위를 줄이는게 불가능하고, 전체 데이터를 탐색해야 한다. 즉 (1)정렬 컬럼에 인덱스가 없어서 where 조건 필터링이 끝난 이후 데이터를 정렬해야 하는 버퍼링 방식으로 처리되며, (2) 심지어 where 조건이 %% 이기 때문에 전체 데이터를 탐색해야 한다.

(추가) 따라서 explain anaylze 를 통해 분석해보았을 때 다음과 같은 방식으로 동작하게 된다. 참고로 아래 테스트에서 인덱스를 생성해버려서 인덱스가 없는 다른 컬럼으로 테스트했다.

  1. Full Table Scan으로 전체 데이터를 읽는다. (가장 많은 시간 소요)
  2. 그중 LIKE 조건을 만족하는 row를 찾고, 대상 row들을 직접 정렬하며 FileSort가 발생한다. (현재 상황에서는 sort buffer로 처리되었지만, 데이터가 많아 external sort가 발생하면 두번째로 많은 시간 소요)
  3. 이후 LIMIT로 10개를 잘라낸다.

그렇다면 어떻게 해결할 수 있을까?

핵심은 현재 가장 병목이 되는 Full Table Scan 이 발생하지 않도록 해야한다.

  1. 텍스트 검색 조건을 개선하여, LIKE 대신 전문 검색 인덱스(full-text index) 를 활용하면 텍스트 기반 필터링에서의 탐색 범위를 줄일 수 있다.

  2. LIKE 조건을 유지하되, 정렬 조건이 인덱스를 활용할 수 있도록 설계하면 스트리밍 처리로 성능을 개선할 수 있다.

그럼 이제 두가지 방법을 모두 테스트해보고 최적의 결과를 도출해보자.

2. 해결을 위한 다양한 시도들

1. Full-Text Search 도입(전문 검색 인덱스)

전문 검색 인덱스란 역색인 방식의 인덱스로, 그 중에서 n-gram 알고리즘은 본문을 n개의 글자로 잘라서 인덱싱하는 방법이다. 따라서 특정 단어를 찾는다면 빠르게 찾을 수 있다.

//  인덱스 생성
CREATE FULLTEXT INDEX ft_index ON tb_lost_post(title, content) WITH PARSER ngram;
explain analyze select * from tb_lost_post
where MATCH(title, content) AGAINST ('1호선' IN NATURAL LANGUAGE MODE)
order by created_at desc
limit 10 offset 30000;

아래 테스트 결과를 보면 대략 0.8 초 정도의 속도로 개선이 된 것을 볼 수 있다.

단 주의할 점은 MySQL에서 보통 하나의 쿼리에 대해 하나의 인덱스만 사용하며, Index Merge로 여러 인덱스를 사용하는 경우도 있지만 전문 검색 인덱스는 이 경우에 해당되지 않기 때문에 정렬 컬럼의 인덱스를 사용해서 정렬할 수 없다. 따라서 정렬 대상이 너무 많다면 마찬가지로 external sort 로 인해 성능이 나빠질 수 있다.

# explain analyze 결과
-> Limit/Offset: 10/30000 row(s)  (cost=1.1 rows=0) (actual time=821..821 rows=10 loops=1)
    -> Sort row IDs: tb_lost_post.created_at DESC, limit input to 30010 row(s) per chunk  (cost=1.1 rows=1) (actual time=744..819 rows=30010 loops=1)
        -> Filter: (match tb_lost_post.title,tb_lost_post.content against ('1호선'))  (cost=1.1 rows=1) (actual time=63.4..670 rows=146553 loops=1)
            -> Full-text index search on tb_lost_post using ft_index (title='1호선')  (cost=1.1 rows=1) (actual time=62.6..657 rows=146553 loops=1)

결론적으로 성능은 괜찮지만 전문 검색 인덱스는 아래와 같은 단점이 존재한다.

전문 검색 인덱스 단점

  1. 의미 없는 단어 또는 불완전한 형태가 인덱싱되어 검색 품질이 저하될 수 있다.
    예를 들어 '테스트입니다'가 '트입'처럼 쪼개지면, 의미 없는 단어까지 검색에 포함돼 정확도가 떨어질 수 있다.(근데 이건 %like%도 마찬가지이다)
  2. QueryDSL에서 full-text-search를 사용하려면 기본적으로는 지원하지 않기 때문에 Custom Dialect를 별도로 생성해줘야 한다.
  3. 현재 상황에서 전문 검색 인덱스는 Index Merge가 불가능해, 다른 인덱스를 동시에 사용할 수가 없다. 따라서 검색 결과 데이터가 극단적으로 많다면 DB를 통해서 정렬하는 외부정렬이 발생해 성능 저하가 심해진다.

따라서 밑에서 나올 2번째 방안으로도 개선이 어느정도 되었다는 부분을 고려해서 full-text search는 적용하지 않기로 결정했으며, 추후 더 개선이 필요하다면 아예 형태소 기반의 인덱싱 방법인 elasticsearch를 학습해서 도입해 볼 예정이다.

2-1. 정렬 필드에 인덱스를 도입

explain select * from tb_lost_post
    where title like '%1호선%' or content like '%1호선%' # 4.7
    order by created_at desc
    limit 10 offset 10;  // offset 10 주의 
create INDEX idx ON tb_lost_post (created_at desc);

createdAt 필드에 내림차순 인덱스를 생성해보았다.

type=index 인것을 보아 위 쿼리는 like 절로 인해서 인덱스 데이터를 풀로 스캔하며, 이후 mysql 서버에서 검색 조건에 대한 필터링이 일어난다. 중요한 것은 이제 더이상 filesort 는 발생하지 않는다는 것!

아래처럼 실제로 어떻게 동작했는지 확인해보면, Index scan 을 통해 탐색한 rows가 533 개 인것을 보아 인덱스 전체를 읽어나가면서 조건을 확인하며 10개가 되면 종료하는 스트리밍 방식으로 처리되고 있는 것을 볼 수 있다.

# (추가) explain analyze 결과
-> Limit/Offset: 10/10 row(s)  (cost=2.66 rows=0) (actual time=19.5..34.6 rows=10 loops=1)
    -> Filter: ((tb_lost_post.title like '%1호선%') or (tb_lost_post.content like '%1호선%')) 
       (cost=2.66 rows=4.2) (actual time=7.16..34.6 rows=20 loops=1)
        -> Index scan on tb_lost_post using created_at_desc_idx 
        (cost=2.66 rows=20) (actual time=1.47..33 rows=533 loops=1)
  • actual time : 첫번째 행을 읽어오는데 들었던 시간의 평균(ms) ~ 모든 행을 읽어오는데 들었던 시간의 평균(ms)

🔗 offset 기반 페이지네이션 단점

하지만 이러한 방식은 offset 이 뒤로 갈 수록 읽는 행의 개수가 증가되어 성능이 나빠진다. 쿼리를 다음과 같이 바꿔보면 총 읽은 행이 30010 개나 되며 2 초 정도로 느려지는 것을 볼 수 있다.

explain select * from tb_lost_post
    where title like '%1호선%' or content like '%1호선%'
    order by created_at desc
    limit 10 offset 30000;  // offset 30000 주의

오프셋은 PK 가 아니고 말그대로 몇번째 레코드인지를 나타낸다. MySQL 입장에서는 해당 몇 번째 레코드인지 줘도 실제 위치를 모르니 다시 처음부터 읽은 행을 중복으로 읽게 된다. 따라서 100000 번째 데이터부터 시작해서 실제 가져올 값이 10 개라면, 1000010 개의 모든 데이터를 읽고 앞에 100000 개는 버리는 비효율적인 방식으로 동작한다.

문제는 현재 무한 스크롤 방식이기는 하지만 악의적인 사용자가 url의 offset을 맨 뒤에 있는 데이터로 변경해 요청을 한다면?

옵티마이저는 조건의 대상이 너무 많아지게 되면 더 이상 인덱스를 사용하는 것이 효율적이라고 생각하지 않고, 그냥 풀 테이블 스캔을 하고 정렬 방법으로 FileSort 을 선택해버리는 상황이 발생할 수 있다.

따라서 오프셋 기반이 아닌, 커서 기반(no-offset) 페이지네이션을 고려해보았다.

2-2. no-offset 페이지네이션 도입

No-Offset 방식은 이미 읽은 행을 매번 중복으로 읽는 상황이 발생하지 않도록, 조회하려는 시작 부분을 인덱스로 빠르게 찾아서 매번 첫 페이지만 읽도록 하는 방식이다.

전체 레코드를 다시 읽는게 아니고, B+ Tree 를 읽기 때문에(클러스터, 넌클러스터 인덱스) 시작 지점을 바로 찾을 수 있는 것이다. 따라서 조건에 인덱스를 걸지 않는다면 커서 기반 페이지네이션을 사용하지 않는거나 마찬가지가 된다.

select * from tb_lost_post
where created_at < timestamp('2024-04-30 21:00:00') and
      (title like '%1호선%' or content like '%1호선%')  # 조건문 집중
order by created_at desc
limit 10;

실제 위 쿼리의 실행 결과는 아래와 같이 40ms 정도로 측정이 되며, 실행 계획을 보면 Index full scan 이 아니라 range scan 이 발생했다.

(2025.06 추가) 다시 테스트했을 때는 200ms 정도 소요된다.

하지만 이는 조건이 유니크한 칼럼일 때 이야기이고, 나와 같이 조건 칼럼이 중복될 가능성이 높다면? 우리는 조건에 해당하는 온전한 데이터를 받지 못할 가능성이 높다.

2-3. 조건 칼럼이 Unique 하지 않을 때

아래와 같이 우선 최신순으로 정렬된 레코드에서 1호선이 포함된 레코드만 3개씩 가져오고 싶다고 하자. 마지막으로 받은 데이터의 날짜(id = 1인 레코드 )가 2023-03-01 이므로, 다음 쿼리는 where created_at < timestamp('2023-03-01') 형태로 나가게 된다.

id = 5 created_at = 2023.03.04   1호선  
id = 4 created_at = 2023.03.03   2호선   (X)
id = 2 created_at = 2023.03.02   1호선
id = 1 created_at = 2023.03.01   1호선   
id = 3 created_at = 2023.03.01   1호선
id = 6 created_at = 2023.02.29   1호선
id = 7 created_at = 2023.02.29   1호선
id = 7 created_at = 2023.02.29   1호선

하지만 이러면 데이터베이스 입장에서는 id=3 인 데이터를 건너뛰고 2023-02-29 날짜를 가진 id=6 부터 3개를 가져오게 된다.

물론 예시에서는 중복을 보여주기 위해 저렇게 잡았고, created_at 를 밀리초까지 기록되게 하면 중복을 피할 수 있긴 하다. 하지만 지금 내 상황과 같이 created_at 가 이미 외부에서 시간이 정해진 데이터였고, 그렇기에 중복을 피할 수가 없었다.

따라서 이런 경우, 해당 레코드를 함께 판별할 수 있는 다른 유니크한 기준이 있는지 확인해야 한다. 

id = 5 unique_id = 1 created_at = 2023.03.04   1호선  
id = 4 unique_id = 2 created_at = 2023.03.03   2호선   (X)
id = 2 unique_id = 3 created_at = 2023.03.02   1호선
id = 1 unique_id = 4 created_at = 2023.03.01   1호선   
id = 3 unique_id = 5 created_at = 2023.03.01   1호선
id = 6 unique_id = 6 created_at = 2023.02.29   1호선
id = 7 unique_id = 7 created_at = 2023.02.29   1호선
id = 8 unique_id = 8 created_at = 2023.02.29   1호선

pk인 id 나 unique_id 와 같이 또 다른 유니크 칼럼이 있다면, 조인 을 통해 위와 같은 형태를 만들어 준 이후 아래 쿼리와 같이 조회해주면 된다. 여기서는 pk를 사용해보자.

explain select * from tb_lost_post
where
    (title like '%1호선%' or content like '%1호선%') and
    (
    	received_date < timestamp('2023-12-07 12:00:16') or
    	(
        	received_date = timestamp('2023-12-07 12:00:16') and
         	lost_post_id > 30000
        )
    )
order by received_date desc, lost_post_id
limit 10;

첫 번째 조건으로 인해 이전에는 누락된 id=3 레코드를 조회할 수 있고, 두 번째 조건으로 인해 2023-03-01 이전의 데이터 들까지 성공적으로 가져올 수가 있어진다.

실제 쿼리를 수행해보면 아래처럼 index range scan을 통해 데이터를 가져오고 있는 것을 볼 수 있으며, 최종적으로 300ms 정도로 개선이 된 것을 볼 수 있다. (추가 : 2-2 섹션의 결과는 offset이 30000 기준으로 테스트된 결과가 아니라 비교 대상이 정확하지 않다고 판단했다)

# explain analyze 결과
-> Limit: 10 row(s)  (cost=40457 rows=10) (actual time=14.8..293 rows=10 loops=1)
    -> Filter: ((tb_lost_post.title like '%1호선%') or (tb_lost_post.content like '%1호선%'))  (cost=40457 rows=21084) (actual time=14.8..293 rows=10 loops=1)
        -> Index range scan on tb_lost_post using received_date_desc_idx over (received_date = '2023-12-07 12:00:16' AND 30000 < lost_post_id) OR ('2023-12-07 12:00:16' < received_date < NULL), with index condition: ((tb_lost_post.received_date < <cache>(cast('2023-12-07 12:00:16' as datetime))) or ((tb_lost_post.received_date = <cache>(cast('2023-12-07 12:00:16' as datetime))) and (tb_lost_post.lost_post_id > 30000)))  (cost=40457 rows=100467) (actual time=3.73..290 rows=213 loops=1)

반대로 오프셋 기반 페이지네이션을 다시 테스트 해본다면? 오프셋이 앞번호면 인덱스를 잘 타지만 뒤로 가면 filesort 가 일어나게 된다.

explain select * from tb_lost_post
order by received_date desc, lost_post_id
limit 10 offset 30000;    // 똑같이 30000부터 탐색하도록 설정

QueryDSL 구현

@Repository
class CustomLostPostRepository(
    private val queryFactory: JPAQueryFactory,
    private val jdbcTemplate: JdbcTemplate
) {
    fun searchLostPosts(command: GetSliceLostPostsCommand): Slice<LostPostEntity> {
        val response = queryFactory.selectFrom(lostPostEntity)
            .where(
                subwayLineEq(command.subwayLine),
                lostTypeEq(command.lostType),
                categoryEq(command.category),
                titleAndContentLike(command.keyword),
                createdAtBeforeOrEqual(command.receivedDate, command.lostPostId)
            )
            .orderBy(lostPostEntity.receivedDate.desc(), lostPostEntity.id.asc())
            .limit((command.pageSize + 1).toLong())
            .fetch()

        return SliceImpl(response, Pageable.unpaged(), hasNext(response, command.pageSize))
    }
     
    ...

    private fun createdAtBeforeOrEqual(receivedDate: LocalDateTime?, id: Long?) =
        lostPostEntity.receivedDate.lt(receivedDate).or(
            lostPostEntity.receivedDate.eq(receivedDate).and(lostPostEntity.id.gt(id))
        )

☁️ 참고 자료
RealMySQL 1편
https://jojoldu.tistory.com/529?category=637935

profile
Loopy
개인용으로 공부하는 공간입니다. 피드백 환영합니다 🙂
이전 포스트

[Spring Boot] @Cacheable 동작 과정과 Redis 직렬화 방식에 대해 알아보자

다음 포스트

[Java] HashSet과 HashMap의 차이와 해시 충돌을 어떻게 해결하는지 알아보자!

2개의 댓글

comment-user-thumbnail
금정욱
2023년 10월 25일

처음 테이블 풀스캔 실행시간이 0.02ms가 아니라 0.02s인 것 같네여

1개의 답글