MySQL 검색 기능 개선하기 : Full-text search
이 글은 당근마켓을 모티브로 한 프로젝트 Secondhand 구현시 이슈 사항을 정리한 글입니다.
발단 : 왜 이것이 필요했나?
Secondhand는 동네를 검색할 수 있는 기능이 있습니다. 예를 들어 이런거요.
[이미지 출처 : https://kikimong.com/7179]
먼저 데이터는 이런식으로 들어가 있습니다. 법정동 코드를 pk 로 하였습니다. 행정동 코드보다 법정동 코드를 선택한 이유는 변동이 더 적기 때문입니다. [참고링크 : 법정동과 행정동의 차이는?]
그래서 이것을 어떻게 구현하면 좋을까요?
- 이 글은 mysql 8.0 InnoDB 환경에서 테스트 되었습니다.
전개
초기 구현 방법 Where ~ like
사실 이 프로젝트의 기간 제한이 있었습니다. 총 4주간의 기간동안 처음 구현하는 채팅 시스템까지 모두 구현하는 것이 목표였기 때문에 이 로직에 투자할 시간이 매우 적었습니다. 그래서 가장 처음에는 가장 쉬운 방법으로 구현을 시도했습니다.
select (columns) from region where concat(city,county,district) like '%string%';concat()으로 city, county, district를 합쳐 검색할 수 있게 했습니다.like %검색어%로 검색어 앞 뒤로 다른 문자열이 있더라도 해당 문자를 포함하는 결과를 조회할 수 있도록 했습니다.
그런데 이 구현방법에는 심각한 단점들이 있었습니다.
먼저 기술적인 단점으로 like %검색어% 를 사용하게 되면 인덱스를 사용하여 성능개선을 하는 것이 불가능합니다. 인덱싱은 저장된 레코드의 제일 첫번째 글자부터 하나씩 비교해가며 일치하는 접두사가 있을 때 이를 사용하여 원하는 레코드를 빠르게 찾기 때문입니다. [8.3.1 How MySQL Uses Indexes] Full table scan을 하면서 성능 면에서 매우 떨어질 것이라는 예상을 할 수 있었습니다.
- explain 시 결과
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------+
| 1 | SIMPLE | r | NULL | ALL | NULL | NULL | NULL | NULL | 1117 | 100 | Using where; |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------+
- 실제 실행시 결과
430 rows retrieved starting from 1 in 150 ms (execution: 18 ms, fetching: 132 ms)그래도 일단 복합 index를 추가해봅니다.
인덱스를 탈 수 없는 쿼리라는 것을 알았지만 복합 index를 생성하여 성능개선이 실제로 되었는지 테스트 해보았습니다. 검색 범위가 3개의 컬럼이었기 때문에 이에 대해 미리 정렬되어있는 index가 있다면 검색 성능에 더 유리해지지 않을까 싶었기 때문입니다.
물론 이렇게 복합 index를 추가할 수 있었던 이유는 이 데이터는 여간해선 안변하는 고정된 데이터이기 때문입니다. 삽입 삭제가 잦은 데이터에서는 이 방법을 시도하지 않았을 것입니다.
- 인덱스는 이렇게 생성해주었습니다.
CREATE INDEX idx_address ON region (city, county, district);- explain 시 결과
- 인덱스를 타지 못할 것이라는 예상과는 달리 인덱스를 탈 수 있는 것을 확인할 수 있었습니다.
select (columns) from region where concat(city,county,district) like '%서울특별시강남구%';+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | r | NULL | index | NULL | idx_address | 546 | NULL | 1117 | 100 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+- 실행시 성능
- 성능이 눈에 뜨이게 좋아진 것도 확인할 수 있었습니다.
430 rows retrieved starting from 1 in 68 ms (execution: 13 ms, fetching: 55 ms)위기 : 근데 누가 이렇게 검색해요 👀
위에서 언급했던 단점 중에 가장 치명적이라고 생각하는 단점이 바로 이것이었습니다. 과연 이용자가 정확하게 DB에 저장한 문자열 그대로 검색을 할까요? 대부분 그냥 서울강남 혹은 역삼동 이렇게 검색하지 강남구역삼1동 이라고 검색하지는 않겠지요?
그래서 비즈니스 요구사항을 다시 확인하며 어떤 것을 구현해야 할지 정리해보았습니다.
- 시, 구, 동을 한꺼번에 검색할 수 있어야 한다.
- 정확한 문자열을 검색하지 않더라도 일부 문자열로 검색하더라도 원하는 동이 결과로 나와야 한다.
- 비슷한 결과나 인접한 동네 등 여러가지 결과를 추천해주어야 한다.
- 검색 성능을 향상시킨다.
그래서 이것을 어떻게 해결할 수 있을까요?
먼저 고려했던 것은 ElasticSearch 입니다. 검색 및 분석을 위한 엔진으로 빠르고 데이터 업데이트가 없는 주소 검색에 효과적일 것이라고 생각했기 때문입니다. [Elastic 가이드북] 하지만 빠른 구현을 요하는 시점에서 새로 배워서 적용하는 것보단 일단 빠르게 해당 구현을 마무리하고, 추후에 Log 시스템을 구축할 때 도입해보고 데이터가 3800여건밖에 안되는 동네 검색은 좀 더 가벼운 방식으로 해결해보고자 했습니다.
Full-text Search
그러다가 알게된 것이 전문검색이라고도하는 Fulltext search입니다. 게시물의 제목, 문장이나 문서 내용에서 키워드를 검색하는 기능입니다.
Full text index
이를 사용하기 위해서는 먼저 Full text Index를 먼저 정의해주어야 하는데요. 이는 MySQL에서 index와 같이 미리 데이터를 정렬해둔 자료구조를 생성하여 쿼리 및 DML 성능을 높일 수 있습니다.
InnoDB 혹은 MyISAM의 테이블에서 사용할 수 있습니다. char 혹은 varchar, text 등 텍스트를 다루는 컬럼에서 대해서만 사용할 수 있습니다. 테이블을 생성할 때 함께 생성하거나 나중에 ALTER TABLE 을 하여 추가할 수 있습니다.
- InnoDB에서 Full text index에 대한 자세한 설명은 이 링크를 참고했습니다.
다음의 쿼리를 실행하여 테이블 컬럼에 Fulltext index를 추가해 주었습니다. 여기서 ngram parser를 사용했는데요.
MySQL은 중국어, 일본어, 한국어(CJK)를 지원하는 내장형 ngram parser와 일본어용 설치 가능한 MeCab parser 플러그인을 제공합니다. (다른 DBMS는 추가적인 Parser를 제공하기도 합니다.)
ALTER TABLE region ADD FULLTEXT INDEX fulltext_address (city, county, district) with parser ngram;ngram parser는 ngram_token_size를 기준으로 문자열을 분해하여 구문을 분해합니다. 예를 들어 서울특별시 라는 문자열을 ngram_token_size 가 2일 때(이게 기본값입니다.), ngram 파서로 토큰 분해한다면 서울, 울특, 특별, 별시 이렇게 4개의 토큰으로 분석하는 것입니다. 만약 공백이 있다면 구문 분석시 공백을 제거합니다.
-- 이 쿼리로 토큰화된 데이터를 볼 수 있습니다.
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;- 더 자세한 내용은 이 문서를 참고하였습니다.
Full-text Search
위와 같이 Full-text index를 생성한 후, 이제 검색을 테스트해보았습니다. 검색 모드는 2가지가 있는데요. Natural Language 검색과 Boolean 검색입니다.
Boolean Full-Text Searches
문자열을 단어 단위로 분리한 후 검색 규칙을 붙여서 검색을 할 수 있습니다. 행을 정렬하지 않습니다. 조건을 만족하면 반환하는 식입니다.
예를 들어 이 쿼리에서는 MySQL 이 들어가고 YourSQL 이 없는 항목을 검색합니다.
mysql> SELECT * FROM articles WHERE MATCH (title,body)
-> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+-----------------------+-------------------------------------+
| id | title | body |
+----+-----------------------+-------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 2 | How To Use MySQL Well | After you went through a ... |
| 3 | Optimizing MySQL | In this tutorial, we show ... |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
| 6 | MySQL Security | When configured properly, MySQL ... |
+----+-----------------------+-------------------------------------+- explain
+----+-------------+-------+------------+----------+------------------+------------------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+----------+------------------+------------------+---------+-------+------+----------+-----------------------------------+
| 1 | SIMPLE | r | NULL | fulltext | fulltext_address | fulltext_address | 0 | const | 1 | 100 | Using where; Ft_hints: no_ranking |
+----+-------------+-------+------------+----------+------------------+------------------+---------+-------+------+----------+-----------------------------------+
- 쿼리 실행시 성능
> SELECT * FROM region r WHERE MATCH(city, county, district) AGAINST('서울특별시 ㅈ*' IN BOOLEAN MODE);
430 rows retrieved starting from 1 in 51 ms (execution: 16 ms, fetching: 35 ms)- 결과
Natural Language Searches
검색 문자열을 token_size로 분리한 후 해당 단어 중 하나라도 포함되는 행을 찾습니다. 연관성이 높을 수록, 즉 포함된 토큰이 많은 순서대로 정렬하여 반환합니다. 단, Order By가 없어야하고, 테이블 조인하는 경우 Fulltext index가 있는 테이블이 왼쪽 테이블이어야 합니다.
그 외의 주의할 점은 다음과 같습니다.
- 전체 테이블의 50% 이상의 레코드가 해당 토큰을 가지고 있다면 무시됩니다.
- 대소문자를 구분하지 않습니다.
- 길이가 기준보다 짧으면 무시됩니다.
-
a,the와 같은 특정 단어는 무시됩니다. 이를stopwords라고 합니다.select * from INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;로 직접 조회하여 목록을 확인할 수 있습니다.
- 분할된 테이블에서는 전체 텍스트 검색이 불가능합니다.
%를 와일드카드로 처리하지 않습니다.
- explain
+----+-------------+-------+------------+----------+--------------------+--------------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+----------+--------------------+--------------------+---------+-------+------+----------+-------------------------------+
| 1 | SIMPLE | r | null | fulltext | idx_fulltext_ngram | idx_fulltext_ngram | 0 | const | 1 | 100 | Using where; Ft_hints: sorted |
+----+-------------+-------+------------+----------+--------------------+--------------------+---------+-------+------+----------+-------------------------------+
- 실행시 성능
> SELECT * FROM region r WHERE MATCH(city, county, district) AGAINST('서울강남역삼' IN natural language MODE);
430 rows retrieved starting from 1 in 51 ms (execution: 16 ms, fetching: 35 ms)- 결과
구현 결과
natural language 검색 모드를 통해 서울강남역삼 으로 검색해도 원하는 결과가 나올 수 있게 되었습니다. 특히 초성이 들어간 검색어를 넣더라도 정확도가 높은 결과를 보여주어 요구사항에 적합하다 판단하였습니다.
단, 검색어에 따라 조회된 결과가 너무 많을 경우를 고려하여 연관도 상위 50개 데이터만 반환할 수 있도록 구현하였습니다.
- Repository
@Query(value = "SELECT * FROM region r WHERE MATCH(r.city, r.county, r.district) AGAINST(:address IN NATURAL LANGUAGE MODE) limit 100", nativeQuery = true)
List<Region> findAllByAddress(@Param("address") String address);- Get 요청시 반환
http://localhost:8080/regions?keyword=강남역삼{
"message": "Regions searched Successfully",
"data": [
{
"id": 1168064000,
"city": "서울특별시",
"county": "강남구",
"district": "역삼1동"
},
{
"id": 1168065000,
"city": "서울특별시",
"county": "강남구",
"district": "역삼2동"
},
{
"id": 1168051000,
"city": "서울특별시",
"county": "강남구",
"district": "신사동"
},
{
"id": 1168052100,
"city": "서울특별시",
"county": "강남구",
"district": "논현1동"
},
{
"id": 1168053100,
"city": "서울특별시",
"county": "강남구",
"district": "논현2동"
},
{
"id": 1168054500,
"city": "서울특별시",
"county": "강남구",
"district": "압구정동"
},
// 이하 생략
]
}마무리
위와 같이 요구사항을 충족하면서 가장 효율적인 방법으로 해당 기능을 구현하였습니다. 다만 의문점이 몇가지 남았는데요. 그 부분에 대해서 추가적으로 기재하고 이 글을 마무리하도록 하겠습니다.
Elastic Search를 쓰면 더 좋을까요?
본문에서 언급했다시피 Elastic Search를 사용해서 검색 엔진을 별도로 사용할 수 있는데요. 그 부분에 대한 성능 차이와 log와는 달리 한정된 수의 데이터에 쓰는 것이 좋을지 궁금해서 추가적으로 알아보았습니다.
- 검색 기능 고도화 하기 3 - 성능 테스트
- Elastic search full text vs mysql full text?
- Full-text search with MySQL and Elasticsearch
공통적으로 데이터의 양이 많을 수록 Elastic search와의 성능차이가 두드러지게 나타나는 것을 알 수 있었습니다.
근처 동네 검색은 어떻게 구현할 수 있을까?
실제 당근마켓은 인접한 동네를 알 수 있는 기능이 있습니다. 이것은 어떻게 구현할 수 있을까요? 지금의 구현 방법으로는 문자열에 기반하기 때문에 제가 만약 강남구 끝자락에 있어도 인접한 구가 우선순위로 반환되지 않습니다. 이때문에 다양한 생각을 했었던 것을 나열하자면 다음과 같습니다.
- 검색엔진 등을 통해 인접한 동네 정보를 별도로 저장합니다.
- 행정표준코드는 지명 정보와 일치하게 구성되어 있습니다. 이에 대해 더 분석해보아도 될 것 같습니다.
- 위도와 경도 등 이미 부여된 좌표를 이용하여 인접 동네에 대한 탐색 기준을 새로 정합니다.
Refs.
- RealMySQL
- MySQL FullText Search 전문검색 기능 (1) - 인덱싱 방식 - 빌트인 전문 검색 - Stopword
- MySQL FullText Search, 제대로 이해하기
- 12.9 Full-text search Function
- 가게 배달지역 관리방식 개편 프로젝트