MySQL 문자열 검색에서 검색 적합도 순으로 정렬하기

게시판이나 상품 목록에 간단한 검색창을 붙이면 처음엔 대개 이렇게 만듭니다. 사용자가 입력한 문자열을 공백으로 쪼개고, 단어마다 LIKE '%단어%'를 만들어 OR로 이어 붙이는 방식이죠. 그런데 결과가 몇십 건만 넘어가도 "왜 내가 찾는 게 맨 아래에 있냐"는 얘기가 나옵니다. 검색 조건에 걸린 행을 걸린 정도에 따라 줄 세우는 장치가 없기 때문입니다.

질문에 나온 PHP 코드는 아래와 같았습니다.

$search = $_GET['search'];
$key_terms = explode(" ", $search);
$query = "SELECT * FROM sentence WHERE";

foreach ($key_terms as $each)
{
    if($each != '')
    {
        $i++;

        if($i == 1)
        {
            $query .= " keywords LIKE '%$each%' ";
        }
        else
        {
            $query .= "OR keywords LIKE '%$each%' ";
        }
    }
}

$result_query = mysql_query($query);

예를 들어 "이효리 한예슬 전지현"을 입력하면 이런 SQL이 만들어집니다.

SELECT *
FROM sentence
WHERE keywords LIKE '%이효리%'
  OR keywords LIKE '%한예슬%'
  OR keywords LIKE '%전지현%';

참고로 이 PHP 코드는 지금 그대로 쓰면 안 됩니다. mysql_query는 PHP 7부터 아예 없어졌고, $_GET 값을 이스케이프 없이 쿼리 문자열에 끼워 넣기 때문에 SQL 인젝션에 그대로 노출됩니다. 실제로 쓸 때는 PDO나 mysqli의 prepared statement로 바꾸고, 단어마다 ? 자리표시자를 만들어 '%단어%'를 바인딩하세요.

적합도를 무엇으로 정할 것인가

검색 엔진에서 말하는 적합도(Relevance Score)는 이 문서가 사용자가 원하는 것에 얼마나 가까운지를 수치로 나타낸 값입니다. 진짜 검색 엔진은 이 계산이 핵심 기술이지만, LIKE만으로 할 수 있는 건 많지 않습니다. 결국 "무엇을 높게 칠지"를 내가 정해서 SQL 식으로 표현해야 합니다. 검색어가 문장 앞쪽에 있을수록 우선한다는 것도 하나의 기준이 될 수 있고요. 여기서는 바로 써먹기 좋은 두 가지 점수를 만들어 봅니다.

1. 몇 개의 단어를 포함했나 (중복 무시)

MySQL에서 keywords LIKE '%이효리%' 같은 비교식은 참이면 1, 거짓이면 0이라는 숫자로 평가됩니다. 그래서 비교식을 그냥 더하기만 해도 "검색어 중 몇 개를 포함했는지"가 나옵니다. 세 단어를 다 가진 행이 3점으로 맨 위에 오고, 한 단어가 여러 번 나와도 그 단어는 1점만 받습니다.

SELECT keywords,
  (keywords LIKE '%이효리%') + (keywords LIKE '%한예슬%') + (keywords LIKE '%전지현%') AS score
FROM sentence
WHERE keywords LIKE '%이효리%'
  OR keywords LIKE '%한예슬%'
  OR keywords LIKE '%전지현%'
ORDER BY score DESC;

여러 키워드를 고루 갖춘 결과를 앞으로 올리고 싶을 때 맞는 방식입니다. 태그 검색처럼 키워드 하나가 반복될 일이 별로 없는 데이터에 잘 어울립니다.

2. 몇 번 등장했나 (중복 포함)

이번엔 검색어가 문자열에 등장한 횟수를 셉니다. 원리는 간단합니다. 원래 길이에서 검색어를 지운(REPLACE로 빈 문자열로 바꾼) 길이를 빼면 사라진 글자 수가 나오고, 이걸 검색어 길이로 나누면 등장 횟수가 됩니다.

SELECT keywords,
  (
    ((LENGTH(keywords) - LENGTH((REPLACE(keywords, '이효리', '')))) / LENGTH('이효리'))
    + ((LENGTH(keywords) - LENGTH((REPLACE(keywords, '한예슬', '')))) / LENGTH('한예슬'))
    + ((LENGTH(keywords) - LENGTH((REPLACE(keywords, '전지현', '')))) / LENGTH('전지현'))
  ) AS score
FROM sentence
WHERE keywords LIKE '%이효리%'
  OR keywords LIKE '%한예슬%'
  OR keywords LIKE '%전지현%'
ORDER BY score DESC

이 방식에서는 '이효리'만 세 번 나온 행과 세 단어가 한 번씩 나온 행이 똑같이 3점입니다. 본문 길이가 긴 글에서 특정 단어가 많이 언급된 걸 우선하고 싶을 때 쓸 만합니다. LENGTH는 바이트 길이를 돌려주지만 분자와 분모를 모두 같은 함수로 계산하니 한글이어도 나눗셈 결과는 정확합니다. 한쪽만 CHAR_LENGTH로 바꾸면 값이 틀어지니 섞어 쓰지 마세요. 그리고 REPLACE는 기본적으로 대소문자를 구분하므로, 영문 검색어라면 LOWER()로 양쪽을 맞춰 두는 게 안전합니다.

PHP에서 조립할 때

실제로는 단어 개수가 매번 달라지니 위 점수식도 WHERE 절처럼 반복문에서 같이 만들어 붙이게 됩니다. 두 방식을 섞어 "포함 단어 수"를 1차 정렬, "등장 횟수"를 2차 정렬로 두는 것도 괜찮은 조합입니다. 다만 LIKE '%...%'는 인덱스를 못 타기 때문에 테이블이 커지면 느려집니다. 데이터가 많아지면 MySQL의 FULLTEXT 인덱스와 MATCH ... AGAINST가 돌려주는 점수를 쓰는 쪽을 검토해 보세요.

참고: MySQL 문자열 검색 후 적합도 순 정렬 (jason-heo)

MySQL 문자열 검색에서 검색 적합도 순으로 정렬하기

적합도를 무엇으로 정할 것인가

1. 몇 개의 단어를 포함했나 (중복 무시)

2. 몇 번 등장했나 (중복 포함)

PHP에서 조립할 때

댓글 0개