mysql/퍼지 검색을 위한 Levenshtein 거리 구현?

mysql/퍼지 검색을 위한 Levenshtein 거리 구현?

1바리안스 내에서 모든 것을 얻을 수 있도록 스미스를 위해 아래와 같이 테이블을 검색할 수 있으면 좋겠습니다.

데이터:

오브라이언스미테돌란스무스웡스모스군터스미트

Levenshtein distance를 사용하는 방법을 알아보았습니다.

Levenshtein 거리를 사용하여 효율적으로 검색하려면 bk-tree와 같은 효율적이고 전문적인 인덱스가 필요합니다.안타깝게도 MySQL을 비롯한 제가 아는 데이터베이스 시스템은 bk-tree 인덱스를 구현하지 않습니다.행당 한 개의 용어만 검색하는 것이 아니라 전체 텍스트 검색을 찾는 경우 이 작업은 더욱 복잡해집니다.즉, 레벤슈테인 거리에 따라 검색할 수 있는 방법으로 전체 텍스트 인덱싱을 수행할 수 있는 방법이 생각나지 않습니다.

Levenshtein Distance 함수의 mysql UDF 구현이 있습니다.

https://github.com/jmcejuela/Levenshtein-MySQL-UDF

C에 구현되어 Schnaader가 언급한 "MySQL Levenshtein distance query"보다 성능이 우수합니다.

다메로-레벤슈테인 거리 구현은 여기서 찾을 수 있습니다.다메로-레벤슈테인 알고리즘:전위치가 있는 레벤슈테인 순수 레벤슈테인 거리에 대한 개선 사항은 문자 교환을 고려한다는 것입니다.슈나이더 링크 댓글에서 찾았어, 고마워!

위의 levenshtein <= 1에 대해 주어진 함수는 올바르지 않습니다. 예를 들어 "bed" 및 "bid"에 대해 잘못된 결과를 제공합니다.

위의 "MySQL Levenshtein distance query"를 수정하여 속도를 조금 높일 수 있는 "limit"를 받아들였습니다.기본적으로 Levenshtein < = 1에만 관심이 있는 경우 한계를 "2"로 설정하면 함수는 정확한 Levenshtein 거리가 0 또는 1이면 정확한 Levenshtein 거리를 반환하고 정확한 Levenshtein 거리가 2 이상이면 2를 반환합니다.

이 모드를 사용하면 검색어를 15~50% 고속화할 수 있습니다.검색어가 길어질수록 이점이 커집니다(알고리즘이 조기에 회피할 수 있기 때문입니다).예를 들어, "giggle"이라는 단어의 거리 1에 있는 모든 일치를 찾기 위해 200,000개의 단어를 검색할 때, 원본은 노트북에서 3분 47초가 걸리는데 반해, "limit" 버전은 1분 39초가 걸립니다.물론 이 두 가지 모두 실시간으로 사용하기에는 너무 느립니다.

코드:

DELIMITER $$
CREATE FUNCTION levenshtein_limit_n( s1 VARCHAR(255), s2 VARCHAR(255), n INT) 
  RETURNS INT 
  DETERMINISTIC 
  BEGIN 
    DECLARE s1_len, s2_len, i, j, c, c_temp, cost, c_min INT; 
    DECLARE s1_char CHAR; 
    -- max strlen=255 
    DECLARE cv0, cv1 VARBINARY(256); 
    SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0, c_min = 0; 
    IF s1 = s2 THEN 
      RETURN 0; 
    ELSEIF s1_len = 0 THEN 
      RETURN s2_len; 
    ELSEIF s2_len = 0 THEN 
      RETURN s1_len; 
    ELSE 
      WHILE j <= s2_len DO 
        SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1; 
      END WHILE; 
      WHILE i <= s1_len and c_min < n DO -- if actual levenshtein dist >= limit, don't bother computing it
        SET s1_char = SUBSTRING(s1, i, 1), c = i, c_min = i, cv0 = UNHEX(HEX(i)), j = 1; 
        WHILE j <= s2_len DO 
          SET c = c + 1; 
          IF s1_char = SUBSTRING(s2, j, 1) THEN  
            SET cost = 0; ELSE SET cost = 1; 
          END IF; 
          SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost; 
          IF c > c_temp THEN SET c = c_temp; END IF; 
            SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1; 
            IF c > c_temp THEN  
              SET c = c_temp;  
            END IF; 
            SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
            IF c < c_min THEN
              SET c_min = c;
            END IF; 
        END WHILE; 
        SET cv1 = cv0, i = i + 1; 
      END WHILE; 
    END IF;
    IF i <= s1_len THEN -- we didn't finish, limit exceeded    
      SET c = c_min; -- actual distance is >= c_min (i.e., the smallest value in the last computed row of the matrix) 
    END IF;
    RETURN c;
  END$$

Chella의 답변과 Ryan Ginstrom의 기사에 기초하여 다음과 같이 퍼지 검색을 구현할 수 있습니다.

DELIMITER $$
CREATE FUNCTION fuzzy_substring( s1 VARCHAR(255), s2 VARCHAR(255) )
    RETURNS INT
    DETERMINISTIC
BEGIN
    DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
    DECLARE s1_char CHAR;
    -- max strlen=255
    DECLARE cv0, cv1 VARBINARY(256);
    SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
    IF s1 = s2 THEN
        RETURN 0;
    ELSEIF s1_len = 0 THEN
        RETURN s2_len;
    ELSEIF s2_len = 0 THEN
        RETURN s1_len;
    ELSE
        WHILE j <= s2_len DO
            SET cv1 = CONCAT(cv1, UNHEX(HEX(0))), j = j + 1;
        END WHILE;
        WHILE i <= s1_len DO
            SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1;
            WHILE j <= s2_len DO
                SET c = c + 1;
                IF s1_char = SUBSTRING(s2, j, 1) THEN
                    SET cost = 0; ELSE SET cost = 1;
                END IF;
                SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost;
                IF c > c_temp THEN SET c = c_temp; END IF;
                    SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
                IF c > c_temp THEN
                    SET c = c_temp;
                END IF;
                SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
            END WHILE;
            SET cv1 = cv0, i = i + 1;
        END WHILE;
    END IF;
    SET j = 1;
    WHILE j <= s2_len DO
        SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10);
        IF c > c_temp THEN
            SET c = c_temp;
        END IF;
        SET j = j + 1;
    END WHILE;
    RETURN c;
END$$
DELIMITER ;

저는 Levenshtein 또는 Damerau-Levenshtein(아마 후자)에 기초한 검색을 설정하고 있습니다.색인된 텍스트에 대한 다중 검색은 Gonzalo Navarro와 Ricardo Baeza-yates의 논문을 기반으로 합니다.link text:

서픽스 배열(wikipedia 참조)을 작성한 후 검색 문자열과 일치하지 않는 문자열이 최대 k개일 경우 검색 문자열을 k+1개로 나눕니다. 적어도 그 중 하나는 손상되지 않은 상태여야 합니다.접미사 배열에 대한 이진 검색을 통해 하위 문자열을 찾은 다음 일치하는 각 조각 주위의 패치에 거리 함수를 적용합니다.

이 기능을 사용할 수 있습니다.

CREATE FUNTION 'levenshtein'(s1 텍스트, s2 텍스트)이 int(11)를 반환합니다.결정론적인시작한다.declar s1_len, s2_len, i, j, c, c_temp, 비용 INT;declar s1_char char;DECLARE cv0, cv1 텍스트;SET s1_len = CHAR_LENGH(s1), s2_len = CHAR_LENGH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;s1 = s2이면반품 0;ELSIF s1_len = 0 이후s2_len을 반환한다;ELSIF s2_len = 0 이후반품 s1_len;또 다른반면 j <= s2_len DOSET cv1 = CONCAT(cv1, UNHEX(HEX(j)), j = j + 1;종료 중;반면 나는 <= s1_len doSET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i), j = 1;반면 j <= s2_len DOSET c = c + 1;s1_char = 서브스트링(s2, j, 1)일 경우SET 비용 = 0, ELSE SET 비용 = 1;종료:SET c_set = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + 비용;c > c_set then SET c = c_set; END IF;SET c_set = CONV(HEx(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;if c > c_temp THENSET c = c_disc;종료:SET cv0 = CONCAT(cv0, UNHEX(HEX(c)), j = j + 1;종료 중;SET cv1 = cv0, i = i + 1;종료 중;종료:반품 c;끝.

XX%로 취득하려면 이 기능을 사용합니다.

CREATE FUNTION 'levenshtein_ratio'( s1 text, s2 text )는 int(11)를 반환합니다.결정론적인시작한다.DECLARE s1_len, s2_len, max_len INT;SET s1_len = 길이(s1), s2_len = 길이(s2);s1_len > s2_len의 경우SET max_len = s1_len;또 다른SET max_len = s2_len;종료:리턴 라운드((1 - LEVENSHTEIN(s1, s2) / max_len) * 100);끝.

Levenshtein-distance가 최대 1인지 알고 싶다면 다음 MySQL 함수를 사용할 수 있습니다.

CREATE FUNCTION `lv_leq_1` (
`s1` VARCHAR( 255 ) ,
`s2` VARCHAR( 255 )
) RETURNS TINYINT( 1 ) DETERMINISTIC
BEGIN
    DECLARE s1_len, s2_len, i INT;
    SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), i = 1;
    IF s1 = s2 THEN
        RETURN TRUE;
    ELSEIF ABS(s1_len - s2_len) > 1 THEN
        RETURN FALSE;
    ELSE
        WHILE SUBSTRING(s1,s1_len - i,1) = SUBSTRING(s2,s2_len - i,1) DO
            SET i = i + 1;
        END WHILE;
        RETURN SUBSTRING(s1,1,s1_len-i) = SUBSTRING(s2,1,s2_len-i) OR SUBSTRING(s1,1,s1_len-i) = SUBSTRING(s2,1,s2_len-i+1) OR SUBSTRING(s1,1,s1_len-i+1) = SUBSTRING(s2,1,s2_len-i);
    END IF;
END

이것은 기본적으로 레벤슈테인 거리를 재귀적으로 기술하기 위한 한 단계입니다.이 함수는 거리가 최대 1이면 1을 반환하고 그렇지 않으면 0을 반환합니다.

이 함수는 레벤슈테인 거리를 완전히 계산하지 않기 때문에 훨씬 빠르다.

이 가 반환되도록 .trueLevenshtein-distance 최 、 2 、 3 、 3 、 lev lev lev lev 。MySQL이 재귀 호출을 지원하지 않는 경우 이 함수의 약간 수정된 버전을 두 번 복사하고 대신 호출할 수 있습니다.그러나 정확한 레벤슈테인 거리를 계산하기 위해 재귀 함수를 사용하면 안 됩니다.

K-distance 검색의 특수한 케이스가 있었는데 MySQL에 Damerau-Levenshtein UDF를 설치한 결과 쿼리가 너무 오래 걸렸습니다.저는 다음과 같은 해결책을 생각해 냈습니다.

• 검색 공간이 매우 한정되어 있습니다(9문자열은 수치로 제한됩니다.

대상 필드의 각 문자 위치에 대한 열이 있는 새 테이블을 만들거나 대상 테이블에 열을 추가합니다.즉, 내 VARCHAR(9)은 내 메인 테이블과 일치하는 9개의 TINYINT 열 + 1개의 Id 열이 되었습니다(각 열에 인덱스를 추가합니다).기본 테이블이 업데이트될 때 이러한 새 열이 항상 업데이트되도록 트리거를 추가했습니다.

k 거리 쿼리를 수행하려면 다음 술어를 사용합니다.

(열1=s[0]) + (열2=s[1]) + (열3=s[2]) + (열4=s[3]) + ...>= m

여기서 s는 검색 문자열이고 m은 일치하는 문자 수(또는 m = 9 - d)입니다.여기서 d는 반환할 최대 거리입니다.

테스트 결과 평균 4.6초 걸린 100만 행 이상의 쿼리가 1초 이내에 일치하는 ID를 반환하는 것을 발견했습니다.메인 테이블의 일치하는 행에 대한 데이터를 반환하는 두 번째 쿼리는 마찬가지로 1초도 걸리지 않았습니다(이 두 쿼리를 하위 쿼리 또는 결합으로 결합하면 실행 시간이 상당히 길어지고 그 이유는 알 수 없습니다).

이것은 Damerau-Levenshtein은 아니지만 내 목적에 충분하다.

이 솔루션은 더 큰 (길이) 검색 공간에서는 잘 확장되지 않을 수 있지만, 이 제한적인 경우에는 매우 효과적이었습니다.

언급URL : https://stackoverflow.com/questions/634995/implementation-of-levenshtein-distance-for-mysql-fuzzy-search