1년 반 전의 실수를 다시 실험해보다
약 1년 반 전에 대학 졸업 프로젝트였던 '메디레디'에서 의약품 검색 기능을 구현했었다. 약 5만개 정도의 의약품 데이터를 이름으로 검색할 때 2글자 이상 입력시 검색할 수 있도록 구현했었다. 나름 그 때는 검색 성능을 높여 보고자 MySQL의 역인덱싱 기능인 Fulltext Index를 적용해보고 LIKE와 성능 비교를 했었는데, 이상하게도 쿼리 수행 속도가 비슷했다. 뭐지 데이터가 적어서 그런가?하고 안일하게 넘어갔던 그때의 바보같은 나...
우연한 기회로 이게 잘못된 테스트였다는 걸 알아차렸다. 그래서 다시 테스트를 수행해봤다. 여러번 해볼 필요도 없었다.
테스트 해보기
우선 그때와 비슷한 환경을 만들어주기 위해서 Docker를 이용해 MySQL을 컨테이너로 실행하고, '의약품 안전나라' 사이트에서 데이터를 다운로드 받아서 일부만 넣어주었다.
넣어준 데이터는 정확히 44006행이다.
그러고 나서 LIKE 문으로 데이터를 검색해봤다.
예상대로 전체 스캔을 수행했고 43.3ms가 걸렸다.
이번에는 Fulltext를 적용해주었다. 적용하기 전에 ngram 값을 확인해줘야 한다. ngram은 몇글자 단위로 끊어서 인덱싱을 적용할 것인지에 대한 옵션인데, 기본적으로 2로 설정되어 있어 그대로 진행했다.
이번에는 확실히 줄어든 것을 볼 수 있다. 약 19ms 내에 완료되었다..!! 심지어 105개의 행만 탐색했다는 것도 알 수 있다.
테스트 결과
아래는 다른 검색어도 테스트해본 결과이다.
| 검색어 | 방식 | 실제 수행 시간 (actual time) | 스캔한 행 수 (rows) |
| 타이 | LIKE | 40.5 ms | 44,006행 전체 스캔 |
| FULLTEXT | 19 ms | ✅ 105행만 스캔 | |
| 이레 | LIKE | 37.9 ms | 44,006행 전체 스캔 |
| FULLTEXT | 4.58 ms | ✅ 39행만 스캔 | |
| 레놀 | LIKE | 35.6 ms | 44,006행 전체 스캔 |
| FULLTEXT | 1.46 ms | ✅ 16행만 스캔 | |
| 이지 | LIKE | 37.9 ms | 44,006행 전체 스캔 |
| FULLTEXT | 11.1 ms | ✅ 124행만 스캔 |
이렇게 비교해봐도 확실히 Fulltext를 적용한 것이 빠르게 수행된 것을 알 수 있다.
결국 중요한 건 ‘데이터 특성’과 ‘설계’
근데 왜 '타이'와 '이지'는 10ms가 넘어가는걸까?
이유는 단순하다. '타이'와 '이지'가 들어간 데이터가 많기 때문이다. 스캔한 행의 수를 보더라도 '이레'와 '레놀'에 비해 많다는 것을 알 수 있다.
Fulltext 인덱스에서는 검색어의 길이보다는 해당 단어의 '출현 빈도'가 더 중요하다. 의약품 데이터에는 비슷한 이름들이 많기 때문에 어떤 검색어냐에 따라 성능이 달라질 수 밖에 없다.
결국 Fulltext 인덱스는 만능 검색 최적화가 아니며, 데이터 특성과 검색 패턴을 이해한 설계가 핵심이라는 것을 느꼈다.
졸업 프로젝트 당시에 테스트했던 자료가 충분하지 않아 어느 부분에서 문제가 생겼는지는 모르겠지만, 그래도 이번에 확실히 그때 무언가 잘못되었음을 인지하였고 어떤 결과가 나왔어야 했는지를 확인해볼 수 있었다.