월드컵 결승에서 손흥민 선수의 골로 인해 극적인 승리를 한다면 해축갤에 무슨 일이 일어날까? (제발 이런 일이 일어났으면)
생각만 해도 달달하다.
이런 경우 아마 해축갤에 흥민찬양에 대한 주제로 글이 미어터질 것입니다.
이렇게 생성된 글들이 쌓이면, 이는 결국 대규모 데이터라고 불릴 만큼 늘어날 것입니다.
그리고 저는 늘어난 글의 갯수를 대략 1억개로 잡아봤습니다.
그래서 최근 데이터를 200만 개에서 1억 개로 늘리고 난 뒤
기존 API의 성능이 얼마나 나오는지 궁금해 쿼리를 한번 테스트해 봤습니다.
4분 21초?
정신 나갔다,,, 쿼리 응답시간 4분 20초…?
해축갤은 제 자식과 같은 프로젝트이지만, 저라도 이 서비스는 사용하지 않을 거 같습니다 ㅠ
상황
현재 해축갤의 ERD입니다.
해축갤 ERD
그리고 위에서 시도한 검색 쿼리는 다음과 같습니다.
SELECT
p.title,
p.content,
m.name
FROM
post p
LEFT JOIN member m ON p.id = m.member_id
WHERE
p.title LIKE '%흥민%'
OR p.content LIKE '%흥민%'
ORDER BY
p.reg_date DESC
LIMIT 10
원인 분석
이전에 확인했던 실행 계획을 다시 분석해 봤습니다.
인덱스가 없을 때 퀴리 실행 분석 결과 (1)인덱스가 없을 때 퀴리 실행 분석 결과 (2)
이 실행계획을 토대로 쿼리의 실행 순서는 다음과 같을 겁니다.
1. 테이블 p (post) 스캔: - 첫 단계에서 MySQL은 post 테이블(p로 별칭 지정)을 전체 스캔(FULL TABLE SCAN)합니다. 이는 WHERE 절에서 사용된 조건(p.title LIKE '%흥민%' OR p.content LIKE '%흥민%') 때문에 발생합니다. 이 조건은 특정 키워드를 포함하는 레코드를 찾아내기 위해 테이블의 모든 행을 검사해야 합니다.
- possible_keys가 NULL로 표시되어 있음을 볼 수 있습니다. 이는 해당 조건을 처리하기 위한 적절한 인덱스가 없음을 의미합니다.
- Using where; Using filesort는 MySQL이 조건에 맞는 레코드를 찾은 후 reg_date 열에 대해 정렬을 수행하기 위해 추가적인 작업을 해야 함을 나타냅니다. Using filesort는 인덱스가 없기 때문에 정렬을 위해 파일 기반 정렬을 사용한다는 것을 의미합니다.
2. 테이블 m (member) 조인 - post 테이블에서 조건을 만족하는 레코드를 찾은 후, LEFT JOIN을 사용하여 member 테이블(m으로 별칭 지정)과 조인합니다.
여기서는 eq_ref 접근 방식을 사용하여 각 post 행에 대해 정확히 일치하는 member 행을 찾습니다. eq_ref는 가장 효율적인 조인 타입 중 하나로, 주로 기본 키나 유니크 키를 조인 조건으로 사용할 때 발생합니다.
- 이 단계에서 rows가 1로 표시되는 것은 member_id를 기반으로 member 테이블에서 단 한 개의 일치하는 행만 찾아낸다는 것을 의미합니다. 이는 member_id가 member 테이블의 기본 키로 설정되어 있기 때문입니다.
실행 순서를 토대로 다음과 같이 원인을 분석해 봤습니다.
💡 주요 원인 1. 풀 테이블 스캔 post 테이블에서 인덱스를 사용하지 않고 전체 행을 스캔합니다. 이는 특히 테이블의 크기가 큰 경우에 성능 저하의 주요 원인이 될 수 있습니다.
2. LIKE 연산자 사용: title과 content 필드에 대한 LIKE '% 흥민%' 검색은 인덱스를 효과적으로 활용할 수 없어서 성능 저하를 초래할 수 있습니다.
3. 정렬(filesort) 사용: reg_date 컬럼으로 정렬하는 과정에서 Using filesort가 발생합니다. 이는 외부 정렬 작업을 의미, 데이터셋이 클 경우 대량의 시간이 소요될 수 있습니다.
방안 검토
2 가지의 방안을 검토해 봤습니다.
title, content에 대한 풀텍스트 인덱스: 장점: 대량의 텍스트 데이터에서 특정 키워드(예: "손흥민", "골")에 대한 검색을 빠르게 수행할 수 있다. 사용자가 관련 게시물을 효과적으로 찾는 데 도움. -> post 테이블에서 FULL TABLE SCAN 을 안할 수 있다
단점: 새로운 게시글이 생성될 때, 풀 텍스트 인덱스 업데이트에 상당한 시간과 자원이 소요될 수 있다. 이는 시스템의 쓰기 성능에 악영향을 줌.
reg_date에 대한 B-Tree 인덱스(보통): 장점: 일반적인 검색과 정렬 작업에서 효율적 -> filesort 을 안할 수 있다.
단점: 본문 내용이나 제목에 있는 특정 키워드에 대한 검색에는 효율적 x
다음 2가지 방안 중 선택을 한 것은 풀텍스트 인덱스입니다.
선택한 이유는 다음과 같습니다.
1. 성능이 저하된 주된 원인은 1억 개로 늘어난 대량의 텍스트 데이터가 주된 원인이다. 실제로 200만 개에서 1억 개로 늘어났을 때 성능이 저하
2. 그래서 filesort 가 주된 원인이라고 생각을 하지 않았다. (사실 이게 주된 원인이었습니다. 밑에 가시면 바로 나와유,,, ㅠㅠㅠ)
풀텍스트 인덱스의 원리
풀텍스트 인덱스 원리
풀텍스트 인덱싱의 원리는 간단히 다음처럼 얘기할 수 있습니다.
1. Col의 데이터를 토큰 단위까지 분해 2. 이때 parser 가 토큰으로 분해를 하게 된다. 3. 분해된 토큰들을 인덱싱
다음 문장을 예시로 간단히 알아보겠습니다. (build-in parser의 경우입니다.)
나는 짬뽕을 좋아해.
풀텍스트 인덱스는 위 문장을 다음과 같이 분해합니다.
'나는', '짬뽕을', '좋아해'
이때분해된 각각의 단어를 '토큰'이라는 단위로 일컫습니다.
그리고 각각의 토큰(단어)에 대해 위치를 별도로 저장하죠.
이때 저장하는 것은 보통의 인덱스가 위치를 저장하는 것처럼 하게 됩니다.
간단히는 이게 답니다.
조금 더 사전적인 의미를 보자면 다음과 같습니다.
저장 방식
1. 인덱싱 풀텍스트 인덱스는 텍스트 데이터를 단어(또는 "토큰") 단위로 분해하여 인덱싱 합니다. 이 과정에서 텍스트를 분석하고, 각 단어의 위치와 중요도를 기록합니다.
2. 역색인(Inverted Index) 인덱싱 된 단어(토큰)는 Inverted Index라는 데이터 구조에 저장됩니다. 역색인은 각 단어가 출현하는 문서(또는 데이터 레코드)의 ID를 매핑합니다.
풀텍스트 인덱스 사용방법
FULLTEXT Index를 사용하게 될 경우 SQL에 Match, Against라는 문법을 사용해야 합니다.
Full-text searching is performed using MATCH() AGAINST() syntax.
1. 검색 쿼리 처리 사용자가 MATCH... AGAINST 구문을 사용하여 풀텍스트 검색 쿼리를 실행하면, MySQL은 해당 쿼리를 분석하여 검색어를 추출합니다.
2. 단어 매칭 검색어는역색인에서 매칭되는 단어와 비교됩니다. 이 과정에서 해당 검색어를 포함하는 모든 문서의 ID가 검색됩니다.
3. 결과 반환 매칭된 문서 ID를 바탕으로 실제 문서 또는 데이터 레코드가 검색 결과로 반환됩니다. 이때, 검색 결과는 종종 관련성 순으로 정렬되어 제공됩니다.
주의사항
- 불용어(Stopwords)
제외된 불용어들
MySQL은 기본적으로 일정한 불용어 목록을 사용하여
너무 흔하거나 검색에 도움이 되지 않는 단어들을 인덱싱에서 제외합니다.
- 최소 단어 길이
innodb_ft_min_token_size 설정에 따라,
인덱싱하는 토큰(단어)의 최소 길이를 정의할 수 있습니다.
이 값보다 짧은 단어는 인덱스에 포함되지 않습니다.
MySQL 8.0 기준 초기값이 4로 설정되어 있기 때문에 이를 유의하셔야 합니다.
만약 값이 4로 설정이 되어있다면, 4자 미만의 검색은 불가능합니다.
또한 블로그글들을 보면 누구는 ft_min_size, 또
누구는 innodb_ft_min_token_size를 수정합니다.
이는 개인 DB 스토리지 엔진에 따라 다릅니다.
만약 본인이 어떤 스토리지 엔진을 쓰는지 잘 모르겠다면 다음과 같은 명령어로 확인 가능합니다.
SHOW TABLE STATUS FROM 데이터베이스명 LIKE '테이블명';
저는 InnoDB 네요!
풀텍스트 인덱스 적용
풀 텍스트 인덱스를 post 테이블의 title과 content 칼럼에 적용했습니다.
제목이나 내용을 다 합쳐서 만든 이유는 크게 2가지입니다.
1. 본인이 원하는 검색어가 제목에는 없고, 내용에만 있는 경우도 존재한다. 제목에는 '%흥민%' 이 없지만내용에는 '%흥민%' 이 있다는거
2. 애초에 해축갤이 그렇게 만들기도 한다.
제목 + 내용
다시 본론으로 돌아와서 풀인덱스 인덱스를 생성해 보겠습니다.
ALTER TABLE post ADD FULLTEXT idx_title_content (title, content);
1억개라 24분이나 걸린다.
이후 기존의 쿼리를 수정합니다.
SELECT
p.title,
p.content,
m.name
FROM
post p
LEFT JOIN member m ON p.member_id = m.id
WHERE
MATCH(p.title, p.content) AGAINST ('흥민*' IN BOOLEAN MODE)
ORDER BY
p.reg_date DESC
LIMIT 10;
수정 후 다음과 같은 결과가 나왔습니다.
아무고토 없다
??? 왜지,,,?
그러다 FULLTEXT Index에 관해 정리한 위의 내용을 다시 한번 생각했습니다.
ft_min_word_len 설정에 따라, 인덱싱 되는 단어의 최소 길이를 정의할 수 있습니다. 이 값보다 짧은 단어는 인덱스에 포함되지 않습니다.
'Content_흥민_95506'이라는 단어는 하나의 단어로 MySQL 이 판단했기 때문이죠.
그래서 하나의 단어로 토큰화, 인덱싱 된 인덱스라 잘못됨을 느끼고
모든 언더스코어(_)를 공백으로 바꾸고 인덱스를 새롭게 만들어 재시도해봤습니다.
여전히 느리다...?
하지만 여전히 결과가 느림을 알 수 있습니다.
reg_date 기준 order by 가 문제다.
실행결과를 보고 나서 쿼리의 이 부분 저부분을 빼보며 원인을 분석해 보니
원인은 Filesort를 사용했기 때문이었습니다.
검색어의 결과는 항상 시간순대로 되어야 하기 때문에, order by 절을 빼먹을 수는 없습니다.
그래서 order by 절에 들어가는 칼럼에 인덱스를 부여해야 한다고 생각이 들어,
다음과 같은 방법들을 생각해 봤습니다.
1. reg_date에 인덱스 생성 후 다시 쿼리 날려보기 2. auto_increment 라 id가 클수록 최신순이다. 불필요한 인덱스 생성 대신 id를 기준으로 order by 하기!
1번을 택할 기술적 이유는 전혀 없기에 2번의 방법으로 바로 시도를 해봤습니다.
2번, FULLTEXT Search 이후 id로 order by 하기
SELECT
p.title,
p.content,
m.name
FROM
post p
LEFT JOIN member m ON p.member_id = m.id
WHERE
MATCH(p.title, p.content) AGAINST ('흥민*' IN BOOLEAN MODE)
ORDER BY
p.id DESC
LIMIT 10;
위와 같은 SQL 쿼리로 다시 한번 시도를 해봤습니다.
1분 26초 -> 28초
약 60% 의 성능 개선가지 이뤘습니다.
사실 수치만 생각했을 때는 드라마틱한 개선을 이뤘다고 생각은 합니다.
하지만 지금 이 기능은 유저가 빈번하게 사용하는 '검색' 기능입니다.
제아무리 구글급 정확도를 가지고 있다 해도, 이런 성능이면 아무도 사용하지 않을 겁니다.
다시 한번 실행계획을 봤습니다.
ORDER BY p.id 의 실행계획
order by 절에 인덱스가 있는 id를 사용해도 여전히 속도가 느리고,
그 원인은 결국 filesort 밖에 없다고 생각할 수 있습니다.
하지만 어째서 index를 사용했음에도 order by를 사용할까요?
정답은 풀텍스트의 좀 더 구체적인 풀텍스트 인덱스의 작동 원리에 있습니다.
FULLTEXT Search + Order By 조합은 filesort 다
1. 풀텍스트 인덱스 작동 순서 풀텍스트 인덱스는 역인덱스 구조를 사용하여 오로지 특정 키워드를 포함하는 행의 ID 만을 빠르게 찾습니다. 검색된 행 ID를 바탕으로 해당 행의 나머지 데이터를 원본 테이블에서 조회합니다.
2. ORDER BY와 filesort 풀텍스트 인덱스는 텍스트 내용 자체보다는 row의 ID를 중점으로 작동합니다. 즉 풀텍스트 인덱스는 검색에'만' 초점이 맞춰줘 있지, 정렬에는 효율적으로 설계되어 있지 않습니다. 결과적으로, MySQL은 filesort 알고리즘을 사용하여 검색 결과를 원하는 순서로 재정렬합니다
즉 FullText Search 방식은 제 경우에 어울리지 않는 방법이라는 것을 알 수 있습니다.
2번째 방법, order by reg_date 에 index 만들고 정렬하기
이제 fulltext 말고 2번째 방법, 정렬 기준이었던 reg_date 에 인덱스 만들고 원래 쿼리를 실행해보겠습니다.
인덱스 생성 명령어 및 결과
위처럼 인덱스를 추가해주고, 시작 부분에서 문제가 됐던 쿼리를 다시한번 실행 해보겠습니다.
SELECT
p.title,
p.content,
m.name
FROM
post p
LEFT JOIN member m ON p.member_id = m.id
WHERE
p.title LIKE '%흥민%'
OR p.content LIKE '%흥민%'
ORDER BY
p.reg_date DESC
LIMIT 10
이 쿼리를 실행하면 결과는 다음과 같습니다.
0.03 초!
하!!!!! 드디어 성공했습니다.
시마이!!!!
확실히 쿼리의 성능은 대부분 디스크 I/O 에서 온다는 것을 확실히 체감할 수 있던 결과입니다!
3번째 방법, 어차피 id 순서가 reg_date 순서 아닌가?
하지만 여기까지 한다면 안됩니다. 더 고민해봐야겠죠?
개인적인 건데 항상 인덱스를 쓸 때 마다 이런 죄책감? PTSD? 같은게 듭니다.
과연 인덱스가 필요한 상황인가요? insert 비용이 늘어날텐데 괜찮을까요? 인덱스 없이도 할 수 있지 않을까요?
아ㅏㅏㅏㅏㅏㅏㅏㅏㅏ
거의 이 상황과 비슷한 급의 PTSD 랄까...?
면접에서 데여서 그런걸까...
어찌됐든 만족할만한 성능 개선이 되고 나서도 잘 한거지 고민을 했습니다.
그러다 위에서 풀텍스트 인덱스를 만들다 이런 고민을 한게 기억이 났습니다.
auto_increment 라 id가 클수록 최신순이다. 불필요한 인덱스 생성 대신 id를 기준으로 order by 하기!
그렇다면 어차피 인덱스라는 자료구조를 이용하게 되면 결국 리프 노드에 도달한 후,
PK 의 인덱스를 타는 구조라서, 처음부터 id 를 기준으로 정렬을 하면 더 이득이 아닌가?
개이득
그래서 쿼리를 다음과 같이 수정했습니다.
SELECT
p.title,
p.content,
m.name
FROM
post p
LEFT JOIN member m ON p.id = m.member_id
WHERE
p.title LIKE '%흥민%'
OR p.content LIKE '%흥민%'
ORDER BY
p.id DESC
LIMIT 10
이러고 실행을 해보니 결과가 다음과 같습니다.
???
아 답은 멀지 않았습니다,,,
PTSD 가 가끔은 도움이 되는구나... 싶네요!
정리
PTSD 가 도움이 된다! 가 아니라 다음과 같이 정리해볼 수 있습니다.
1. 성능이 느리다면 실행계획을 분석해본다.
2. 이번 케이스에서는 (FULL TABLE + LIKE 절) / FILESORT 2 가지가 문제라고 여겨짐
3. FULL TABLE 안 쓰려고 FULL TEXT 썼지만 FILESORT 때문에 개선이 그닥 안됨
