mysql like함수 조회성능개선
mysql의 like함수를 사용하여 해당하는 키워드가 있으면 모두 필터링하여 가져오는 로직을 구현하였습니다. 하지만 like함수를 사용하게 되면 mysql은 full scan방식으로 데이터를 탐색하기때문에 성능이 안좋다는 것을 사전에 알고 있었기 때문에 직접 눈으로 확인해보고 싶었습니다. 또한 추후에 러닝커브가 높은 ealsticsearch를 통해 형태소 분석 및 사용자/동의어 사전을 통해 유사한 keyword가 들어왔을 때도 해당하는 단어를 출력해보고자 합니다.
ElasticSearch는 왜 성능이 좋을지 먼저 알아보자
색인이 문서들에서 키워드를 뽑아낸다면, 역색인은 뽑아낸 키워드들을 바탕으로 그 키워드가 포함된 문서를 찾아나간다
쉽게 말하면 인덱스는 1페이지에 무슨단어 무슨단어 이런식으로 인덱스를 설정하면 해당 페이지에서 단어를 찾는데 사용되고
역색인은 단어를 키로 사용해서 단어로 해당 문서를 값으로 가져와
GPT의 답변 역색인이 더 빠른 이유:
역색인은 단어를 키로 하고 해당 단어가 포함된 모든 페이지를 값으로 가지는 데이터 구조를 사용합니다. 이는 단어를 검색할 때 즉시 해당 단어가 있는 모든 페이지를 찾을 수 있도록 합니다. 즉, 단어가 있는 모든 페이지를 탐색할 필요가 없습니다.
반면에 인덱스에서는 단어를 찾는 경우에는 단어가 있는지 여부를 확인하기 위해 각 페이지를 순차적으로 스캔해야 합니다. 이는 역색인보다 더 많은 시간이 소요될 수 있습니다.
따라서 역색인은 단어가 포함된 페이지를 탐색하는 것에 특화되어 있어서, 해당 단어가 있는 모든 페이지를 효율적으로 찾을 수 있습니다. 이것이 역색인이 단어를 찾는 데에 비해 더 빠른 이유입니다.
Mysql like 함수
mysql에서는 like를 이용하여 문자열을 검색할 수 있습니다. like 키워드는 와일드카드 % 와 함께 사용된다.
하지만 like 키워드는 인덱스가 추가되어 있더라도 속도가 느리다. because -> full scan을 통해 해당 키워드를 가진
내용을 검색하기때문이다.
실행 계획(EXPLAIN)으로 확인해보자
실행 계획(EXPLAIN)
조회(SELECT)를 할 때, 그 성능이 어느 정도인 지 확인할 수 있는 키워드이다.
type 컬럼의 값을 보고 성능을 판단할 수 있다.
성능이 나쁜 순으로 나열
| Type | Description |
| all | 테이블 Full Scan 테이블 전체를 조회한다. 성능 개선 필수 |
| index | 테이블 Full Scan 테이블 전체를 조회한다. 성능 개선 필수 |
| range | 인덱스를 사용한 범위 검색 |
| fulltext | MATCH AGAINST 구문을 사용했을 때 실행 |
| ref | 테이블 간의 JOIN에서 PK 또는 Unique Key가 이용되었으며, 데이터가 2건 이상일 때 |
| eq_ref | ref와 같으나 데이터가 1건일 때 |
| const | PK또는 Unique Key로 조회되었으며 데이터가 단 1건일 때 |
| system | 데이터가 없거나 한 개만 있는 경우 |
Mysql FullText Search
- FullText index는 생성 시 공백을 기준으로 단어를 저장하기 때문에 검색하느 단어가 정확히 일치해야만 결과를 받을 수 있다.
예를 들어 '서울시 강남구'라는 키워드로 검색을 한다고해보자. 그럼 결과는 서울시 강남구의 검색결과만 나오는 것이 아니라. 서울시,강남구의 검색결과가 나온다. 즉 , 검색의 정확도(score)에 따라 내림차순으로 정렬되어 결과가 표시되는 것이다.
SELECT food_id, restaurant, address FROM food
WHERE MATCH (address) AGAINST('서울 아파트' IN NATURAL LANGUAGE MODE);
해당하는 쿼리를 날리게되면 서울 , 아파트으로 끊기고 scoring해서 내림차순으로 정렬을 해주기때문에 서울 아파트에 정확하게 일치하는 값으로 scoring해주는 문제가 발생하기도 한다. 정확히 일치하는 단어여야지만 검색이 가능하다라는 단점이 분명 존재하는것 같다.
NGRAM PARSE
FullText index를 생성할 때 ngram parser를 함께 추가해주면 이 문제를 해결할 수 있다.
ngram parser는 기본값인 2글자 단위로 단어를 쪼개어 저장한다.
// ngram parser 추가
ALTER TABLE food ADD FULLTEXT INDEX idx_ft_name(restaurant) WITH PARSER ngram;
ALTER TABLE food ADD FULLTEXT INDEX idx_ft_address(address) WITH PARSER ngram;
SELECT food_id, restaurant, address FROM food
WHERE MATCH (address) AGAINST('서울 아파트' IN NATURAL LANGUAGE MODE);
이 쿼리를 추가 한 후 다시 서울 아파트으로 검색을 해보자. 2글자씩 단어를 쪼개어 검색을 해주기때문에 데이터가 드디어 제대로 나온다.
ex) 서울 ,울O, O아, 아파, 파트 이런식으로 2글자 씩 쪼개진다. 쪼개진 단어를 인덱스로 사용하여 더욱 검색에 활용할 수 있도록한다.
기존에 사용한 자연어 모드검색인 NATURAL LANGUATE MODE는 "서울 아파트"을 검색어에 검색하게되면 검색의 정확도(score)에 따라 데이터를 내림차순 정렬하여 결과를 표시하는 반면 불린 모든 검색인 boolean mode 를 사용하면 검색의 정확도를 높일 수 있다.
자연어 모드 검색과 구분되는 불린 모드 검색의 차이점은 다음과 같다.
- 검색의 정확도에 따라 결과가 정렬되지 않는다.
- 구문 검색이 가능하다
- 필수(+), 예외(-), 부분(*) 등의 연산자를 사용할 수 있다.
불린 모드 연산자 목록
| Operator | Description |
+ |
AND, 반드시 포함하는 단어 |
- |
NOT, 반드시 제외하는 단어 |
> |
포함하며, 검색 순위를 높일 단어 ’+mysql >tutorial’ → mysql과 turorial가 포함하는 행을 찾을 때, tutorial이 포함되면 검색 순위가 높아짐 |
< |
포함하며, 검색 순위를 낮출 단어 ’+mysql <tutorial’ → mysql과 turorial가 포함하는 행을 찾을 때, tutorial이 포함되면 검색 순위가 낮아짐 |
() |
하위 표현식으로 그룹화 (포함, 제외, 순위 지정 등) ’+mysql +(>tutorial <training)’ |
~ |
- 연산자와 비슷하지만 제외 시키지는 않고 검색 조건을 낮춤 |
* |
와일드카드 |
"" |
구문 정의 |
테스트시작
더미데이터 약 50만개를 삽입하고 testcode를 작성하여 진행해보겠습니다.
데이터는 test.sql 파일을 만들어서 초기실행 시 해당 스크립트를 삽입하여 데이터를 채워넣었습니다.
테스트코드
@DisplayName("searchWord에 해당하는 키워드에 해당하는 음식점을 보여준다.")
@Test
void matchedRestaurants() throws Exception {
//given
String searchWord = "돈까스";
String url = "/api/v1/food/37.5070308/127.0586576?searchWord=" + searchWord;
int expectedCount = 0; // 예상되는 음식점 수
//when
MvcResult mvcResult = mockMvc.perform(get(url)
.contentType(MediaType.APPLICATION_JSON)
.characterEncoding(StandardCharsets.UTF_8)
)
.andDo(print())
.andExpect(status().isOk())
.andReturn();
//then
mvcResult.getResponse().setContentType("application/json;charset=UTF-8");
String result = mvcResult.getResponse().getContentAsString();
ObjectMapper objectMapper = new ObjectMapper();
JsonNode jsonNode = objectMapper.readTree(result);
JsonNode resultData = jsonNode.get("data");
JsonNode resultContent = resultData.get("content");
for (JsonNode resultContentItem : resultContent) {
JsonNode restaurantName = resultContentItem.get("restaurantName");
JsonNode category = resultContentItem.get("category");
JsonNode address = resultContentItem.get("address");
if (restaurantName != null && restaurantName.asText().contains(searchWord)) {
expectedCount++;
}
else if (category != null && category.asText().contains(searchWord)) {
expectedCount++;
}
else if (address != null && address.asText().contains(searchWord)) {
expectedCount++;
}
}
// 응답으로 받은 음식점 수와 예상되는 음식점 수를 비교
int actualCount = resultContent.size();
Assertions.assertThat(actualCount).isEqualTo(expectedCount);
}
돈까스라는 키워드로 검색했을 때 과연 몇초만에 "초밥"이라는 데이터를 가져올까요 ?
like함수를 통해 가져올 때는 약 2초정도 소요가 되었습니다.
FullText index를 생성할 때 ngram parser를 함께 추가하고 다시 테스트 진행
50%이상의 성능향상을 보였다.
하지만 테스트코드에서 오류발생
"돈까스 갈비 국수"라는 키워드로 검색을 했을때 2단어로 파싱이 이루어지고
- "돈까"
- "까스"
- "스 "
- " 갈"
- "갈비"
- "비 "
- " 국"
- "국수"
해당 단어들에 indexing이 걸려 더욱 찾기 쉽게 해준다.
하지만 해당하는 단어가 포함되지않았을 수도 있기떄문에 test code를 다시짜줘야한다.
TMI : contain함수를 쓰면 안될것같은데 ? 추후에 testcode 재작성해서 테스트다시 돌려보자
테스트코드 Ngram Parser 맞게 Refactoring
@DisplayName("searchWord에 해당하는 키워드에 해당하는 음식점을 보여준다.")
@Test
void matchedRestaurants() throws Exception {
//given
String searchWord = "돈까스";
String url = "/api/v1/food/37.5070308/127.0586576?searchWord=" + searchWord + "&page=0&size=100000";
int expectedCount = 0; // 예상되는 음식점 수
//when
MvcResult mvcResult = mockMvc.perform(get(url)
.contentType(MediaType.APPLICATION_JSON)
.characterEncoding(StandardCharsets.UTF_8)
)
.andDo(print())
.andExpect(status().isOk())
.andReturn();
//then
mvcResult.getResponse().setContentType("application/json;charset=UTF-8");
String result = mvcResult.getResponse().getContentAsString();
ObjectMapper objectMapper = new ObjectMapper();
JsonNode jsonNode = objectMapper.readTree(result);
JsonNode resultData = jsonNode.get("data");
JsonNode resultContent = resultData.get("content");
for (JsonNode resultContentItem : resultContent) {
JsonNode restaurantName = resultContentItem.get("restaurantName");
JsonNode category = resultContentItem.get("category");
JsonNode address = resultContentItem.get("address");
if (restaurantName != null && containsSearchWord(restaurantName.asText(),searchWord)) {
expectedCount++;
}
else if (category != null && containsSearchWord(category.asText(),searchWord)) {
expectedCount++;
}
else if (address != null && containsSearchWord(address.asText(),searchWord)) {
expectedCount++;
}
}
// 응답으로 받은 음식점 수와 예상되는 음식점 수를 비교
int actualCount = resultContent.size();
Assertions.assertThat(actualCount).isEqualTo(expectedCount);
}
// ngram 함수를 통해 문자열을 2글자씩 파싱하여 배열에 저장하는 함수
private String[] ngram(String str) {
List<String> ngrams = new ArrayList<>();
for (int i = 0; i < str.length() - 1; i++) {
ngrams.add(str.substring(i, i + 2));
}
return ngrams.toArray(new String[0]);
}
// 주어진 문자열이 검색어를 포함하는지 확인하고, 2글자씩 파싱하여 검색어와 일치하는지 검사하는 함수
private boolean containsSearchWord(String restaurantName, String searchWord) {
if (restaurantName != null && searchWord != null) {
String[] restaurantNgrams = ngram(restaurantName);
String[] searchWordNgrams = ngram(searchWord);
for (String ngram : restaurantNgrams) {
if (Arrays.asList(searchWordNgrams).contains(ngram)) {
return true;
}
}
}
return false;
}
DataGrip 에서 돈까스라는 단어로 검색을 했을 경우 약 0.5ms
SELECT * FROM food WHERE category LIKE '%돈까스%' and restaurant LIKE '%돈까스%' ORDER BY rand();
DataGrip에서 돈까스라는 단어를 fulltext search 했을 경우 약 0.1ms
SELECT * FROM food WHERE MATCH(category,restaurant) AGAINST("돈까스");
실행계획 분석
- id : 쿼리 안에 있는 각 select 문에 대한 순차 식별자이다. 이순서대로 select문이 실행된다고 생각하면 된다.
- select_type : select 문의 유형을 말한다. 각 유형은 아래와 같다
- SIMPLE : 서브쿼리나 'union'이 없는 가장 단순한 select문을 말한다
- PRIMARY : 가장 바깥에 있는 select 문을 말한다
- DERIVED : from 문 안에있는 서브쿼리의 select 문이다.
- SUBQUERY : 가장 바깥의 select 문에 있는 서브쿼리이다.
- DEPENDENT SUBQUERY : 기본적으로 SUBQUERY와 같은 유형이며, 가장 바깥의 select문에 '의존성'을 가진 서브쿼리의 select문이다.
- UNCACHEABLE SUBQUERY
- UNION : union 문의 두번째 select 문을 말한다
- DEPENDENT UNION : 바깥 쿼리에 의존성을 가진 union문의 두번째 select문을 말한다
- table : 참조되는 테이블을 말한다
- type : MySQL이 어떤식으로 테이블들을 조인하는지를 나타내는 항목이다. 이는 매우 중요한데, 이유는 이 타입을 분석함으로써 어떤 인덱스가 사용되고 사용되지 않았는지를 알 수 있고, 이를통해 어떤식으로 쿼리가 튜닝되어야하는지에 대한 insight를 제공하기 때문이다. 각 유형은 아래와 같다
- system : 0개 또는 하나의 row를 가진 테이블이다.
- const : 테이블에 조건을 만족하는 레코드가 하나일 때, 상수 취급
- eq_ref : primary key나 unique not null column으로 생성된 인덱스를 사용해 조인을 하는 경우이다. const 방식 다음으로 빠른 방법이다.
- ref : 인덱스로 지정된 컬럼끼리의 '=' , '<=>' 와 같은 연산자를 통한 비교로 수행되는 조인이다
- index_merge
- unique_subquery : 오직 하나의 결과만을 반환하는 'IN'이 포함된 서브쿼리의 경우이다.
- index_subquery : unique_subquery와 비슷하지만 여러개의 결과를 반환한다
- range : 특정한 범위의 rows들을 매칭시키는데 인덱스가 사용된 경우이다. BETWEEN이나 IN, '>', '>=' 등이 사용될 때이다.
- all : 조인시에 모든 테이블의 모든 row를 스캔하는경우이다. 물론 성능이 가장 좋지 않다.
- possible_keys : 테이블에서 row를 매핑시키기 위해 사용 가능한 (사용하지 않더라도) 키를 보여준다.
- key : 실제적으로 쿼리 실행에 사용된 key의 목록이다. 이 항목에는 possible_keys 목록에 나타지 않은 인덱스도 포함 될 수 있다.
- ref : key column에 지정된 인덱스와 비교되는 column 또는 constants를 보여준다.
- rows : 결과 산출에 있어서 접근되는 record의 숫자이다. 조인문이나 서브쿼리 최적화에 있어서 중요한 항목이다.
- Extra : 실행계획에 있어서 부가적인 정보를 보여준다.
not exist : left join 조건을 만족하는 하나의 레코드를 찾았을 때 다른 레코드의 조합은 더 이상 검사하지 않는다.
range checked for each record : 최적의 인덱스가 없는 차선의 인덱스를 사용한다는 의미.
using filesort : mysql이 정렬을 빠르게 하기 위해 부가적인 일을 한다.
using index : select 할때 인덱스 파일만 사용
using temporary : 임시 테이블을 사용한다. order by 나 group by 할때 주로 사용
using where : 조건을 사용한다는 의미.