by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

17장. 가난한 자의 검색 엔진




목차

h1. XVII. 가난한 자의 검색 엔진

XVII. 가난한 자의 검색 엔진

  • 시스템에서 공통적인 질문에 대한 해법을 설명하는 짧은 문서의 모음들을, 지식 관리 시스템의 웹 단에서 보여주려고 함.
  • 초반에는 속도 이슈가 없으나, 문서 집합이 커지면서, 검색어 입력 시 속도가 저하되는 것이 파악됨.
  • 또한, crash란 단어로 검색 시 creahed, crashes, crashing 같은 단어와 매칭되는 파생어도 검색이 되도록 하고 싶었으나, 이 또한 구현이 어려움.
  • 이 경우, 어떻게 효율적인 검색을 제공할 수 있나?

1. 목표 - 전체 텍스트 검색

  • SQL에서 특정 단어를 검색할 때는, 값이 원자적이어야 하며, 비교 시 항상 전체 값과 비교해야 함(Ex : LIKE 검색)
  • 그러나, 긴 문자열과 짧은 문자열을 비교하고, 짧은 문자열이 긴 문자열에 나오는 경우를 찾는 방법이 필요한데, 이를 SQL에서 어떻게 검색할 수 있나?

2. 안티패턴 - 패턴 매칭 사용

1) LIKE 검색

  • LIKE 연산자는 0개 이상의 문자와 매치되는 와일드카드(%)를 지원함.
  • 키워드의 앞뒤에 와일드카드를 사용하면, 해당 키워드를 포함하는 문자열과 매치됨.
  • 첫 번째 와일드카드는 단어 앞의 어느 텍스트든 매치되고, 두 번재 와일드카드는 단어 뒤에 나오는 텍스트와 매치됨.
SELECT *
FROM   BUGS
WHERE  DESCRIPTION LIKE '%crash%';

2) REGEXP

  • 표준방법은 아니지만, 정규 표현식 또한 많은 데이터베이스 제품에서 지원됨.
  • 정규 표현식은 패턴을 부분문자열에 매치시키기 때문에 와일드카드는 필요하지 않음.
  • 아래는 MySQL 구문이며, Oracle은 'REGEXP_SUBSTR' 명령어임
SELECT *
FROM   BUGS
WHERE  DESCRIPTION REGEXP '%crash%';

3) 패턴 매칭 단점

3-1) 성능 저하

  • 패턴 매칭 연산자의 가장 중요한 단점은 성능이 나쁜데, 일반 인덱스를 활용할 수 없기 때문에 테이블의 모든 행을 스캔해야 함.
  • 문자열 컬럼에 대한 패턴 매칭은 상당한 비용이 드는 연산이기 때문에, 이 검색을 위해 테이블을 스캔할 때 전체비용은 매우 높아짐

3-2) 패턴 매칭 불일치

  • LIKE나 정규 표현식을 사용한 단순한 패턴 매칭이 원치 않는 결과도 찾을 수 있다는 것
SELECT *
FROM   BUGS
WHERE  DESCRIPTION LIKE '%one%';
  • 이 쿼리는 단어 one을 포함한 텍스트를 찾아낼 뿐 아니라, money, prone, lonely 등과 같은 문자열도 찾아냄.
  • 이를 개선하기 위해 데이터베이스에 따라 단어 경계(word boundary)를 위한 특별한 정규 표현식 패턴을 지원함.
SELECT *
FROM   BUGS
WHERE  DESCRIPTION REGEXP '[[:<:]]one[[:>:]]';
  • 성능과 확장적응성 문제, 정확한 매치를 위해서는 연습이 필요함을 고려할 때, 단순한 패턴 매칭은 키워드 검색을 위한 좋은 방법이 아님.

3. 안티패턴 인식 방법

다음과 같은 질문은 보통 가난한 자의 검색 엔진 안티패턴이 사용되고 있음을 나타냄

  • "LIKE를 사용할 때 어떻게 하면 두 와일드카드 사이에 변수를 넣을 수 있지?"
    • 프로그래머가 사용자로부터 입력 받은 텍스트로 패턴 매칭 검색을 하려고 할 때 보통 이런 질문이 나온다.
  • "문자열이 여러 개의 주어진 단어를 포함하고 있는지, 문자열이 특정 단어를 포함하지 않고 있는지, 또는 문자열이 주어진 단어의 변형을 포함하고 있는지를 확인하는 정규 표현식을 작성하려면 어떻게 해야 하지?"
    • 문제가 복잡해 정규 표현식으로 풀기에 어려워 보이면, 아마 정규 표현식으로는 못 풀 것이다.
  • "우리 웹 서이트의 검색 기능은 데이터베이스에 많은 문서를 추가했을 때 사용하기 어려울 정도로 느려, 뭐가 잘못된 걸까?"
    • 데이터 크기가 늘어날수록, 안티패턴을 이용한 방법의 문제는 심각해질 것이다.

4. 안티패턴 사용이 합당한 경우

  • 안티패턴 절에서 보인 표현은 모두 적합한 SQL 쿼리고, 직관적일 뿐 아니라 사용하기도 쉽다.
  • 복잡한 쿼리에서 패턴 매칭 연산자를 사용하기는 어렵지만, 간단한 경우에 대한 패턴이라면 최소의 노력으로 옳바른 결과를 얻는 데 도움이 될 수 있다.

5. 해법 - 작업에 맞는 올바른 도구 사용하기

  • SQL 대신 특화된 검색 엔진을 사용하는 것이 가장 좋으며, 다른 대안은 검색 결과를 저장해 반복되는 비용을 줄이는 것.
  • 아래에는 각 데이터베이스 제품에서 확장 기능으로 제공하는 기술과 독립된 프로젝트가 제공하는 기술을 살펴봄.

1) 밴더 확장기능

  • 대부분 데이터베이스 제품은 전체 텍스트 검색(full-text search) 요구에 대응하기 위해 각자의 대응방안이 있지만, 이는 표준이 아니고 데이터베이스 제품 간 호환성도 없음.
  • 하지만, 하나의 데이터베이스만 사용한다면 각각에서 제공하는 기능을 사용하는 것이 최선이며, 아래에 제품별 사용방법을 기술함.
1-1) MySQL에서의 전체 텍스트 검색
  • MySQL은 MyISAM 스토리지 엔진에서만 간단한 전체 텍스트 인덱스 타입을 제공함.
  • CHAR, VARCHAR, TEXT 타입의 컬럼에 전체 텍스트 인덱스를 정의할 수 있음.
  • 아래는 BUGS 테이블의 SUMMARY와 DESCRIPTION 컬럼에 전체 텍스트 인덱스를 정의한 내용
ALTER TABLE BUGS ADD FULLTEXT INDEX BUGFTS (SUMMARY, DESCRIPTION);
  • 인덱스가 걸린 텍스트에서 키워드를 검색할 때 MATCH() 함수를 사용하며, 이 때 전체 텍스트 인덱스의 컬럼을 지정해 주어야 함(그래야 같은 테이블의 다른 컬럼에 대한 인덱스도 사용할 수 있음)
SELECT * 
FROM   BUGS 
WHERE  MATCH(SUMMARY, DESCRIPTION) AGAINST ('crash');
  • MySQL 4.1부터는 결과를 좀 더 세밀하기 걸러내기 위해, 패턴에 간단한 불리언 수식 표시법 사용 가능함.
SELECT * 
FROM   BUGS 
WHERE  MATCH(SUMMARY, DESCRIPTION) AGAINST ('+crash -save' IN BOOLEAN MODE);
1-2) Oracle에서의 텍스트 인덱싱

☞ CONTEXT

  • 하나의 텍스트 컬럼에 대해서는 이 타입의 인덱스를 생성하며, 이 인덱스를 사용하는 검색에서는 CONTAINS() 연산자를 사용함.
  • 이 인덱스는 데이터가 변경되어도 일관적인 상태를 유지하지 않으므로, 인덱스를 직접 또는 스케쥴을 걸어 주기적으로 REBULID를 해주어야 함.

-- 1. 인덱스 생성(오타. CTSSYS 가 아니라 CTXSYS)
CREATE INDEX BUGSTEXT ON BUGS(SUMMARY) INDEXTYPE IS CTXSYS.CONTEXT;

-- 2. 검색
SELECT * 
FROM   BUGS 
WHERE  CONTAINS(SUMMARY, 'crash') > 0;

-- 1. 테이블 생성
DROP TABLE TX_TEST;

CREATE TABLE TX_TEST(TXTFIELD VARCHAR2(2000));

-- 2. 데이터 생성
INSERT INTO TX_TEST VALUES('asian institute of technology');

-- 3. CONTEXT 인덱스 생성
CREATE INDEX TX_TEST_INDEX ON TX_TEST(TXTFIELD) INDEXTYPE IS CTXSYS.CONTEXT;

-- 4. CONTEXT 인덱스에 포함된 단어 검색이 검색 가능(대소문자 구별 안함)
col TXTFIELD format a40

SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'institute') > 0
;

TXTFIELD                                
----------------------------------------
asian institute of technology           

SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'INsTItUTe') > 0
;

TXTFIELD                                
----------------------------------------
asian institute of technology           

-- 5. 텍스트 필드에 포함되지 않은 단어를 지정하면 검색되지 않음
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'techy') > 0
;

no rows selected

-- 6. 단어의 일부분을 검색어로 사용할 경우 검색되지 않음
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'tech') > 0
;

no rows selected

-- 7. 텍스트 필드에 전체 단어로 검색해야 검색 가능
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'technology') > 0
;

TXTFIELD                                
----------------------------------------
asian institute of technology           

-- 8. 두 글자 단어는 검색되지 않음
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'of') > 0
;

no rows selected

-- 9. 텍스트 필드의 시작 부분 단어를 FULL로 검색할 경우 검색 가능
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'asian') > 0
;

TXTFIELD                                
----------------------------------------
asian institute of technology           

-- 10. 새로운 데이터 생성
INSERT INTO TX_TEST VALUES('no problem of money');

-- 11. 새로운 텍스트가 자동으로 생성되지 않음
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'of') > 0
;

no rows selected

-- 12. 특히 두 글자 단어는 검색 안됨
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'no') > 0
;

no rows selected

-- 13. 그리고 다섯 글자 단어도 안됨 -_-
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'money') > 0
;

no rows selected

-- 14. 심지어 일곱 글자 단어도 안됨 ㅎㅎ
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'problem') > 0
;

no rows selected

-- 15. 검색 시 빠른 조회는 이전에 텍스트 항목에 대한 작업이 있는 단어만 검색 가능함
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'technology') > 0
;

TXTFIELD                                
----------------------------------------
asian institute of technology           

-- 16. 테이블에 생성된 데이터 확인
SELECT TXTFIELD
FROM   TX_TEST
;

TXTFIELD                                
----------------------------------------
asian institute of technology           
no problem of money                     

-- 17. COMMIT
COMMIT;

-- 18. 을 한 후에도 CONTEXT 작업 하기 전 단어가 검색되는지 확인 
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'problem') > 0
;

no rows selected

-- 19. 테이블과 인덱스가 동기화 되어 있지 않아, 이를 동기화 하는 작업 수행
EXEC CTX_DDL.SYNC_INDEX('TX_TEST_INDEX');

-- 19_2. 내부에서 수행되는 SQL(동기화 엄청 느림 -_- 결국 20분 넘게 걸려 포기함)
--       이 정도 수준이라면 이 기능 사용 못할것으로 보임
 [  Bind Variable  ]                                                                                                           
-------------------------------------------------------------------------------------------------------------------------------
Bind_Name           Data Type                     Value                                                       Position          
-------------------------------------------------------------------------------------------------------------------------------
:B1                 NUMBER                        0                                                           2                 
:B2                 NUMBER                        55388                                                       1                 


 [  Current SQL  ]                                                                                                             
-------------------------------------------------------------------------------------------------------------------------------
DELETE FROM DR$WAITING
WHERE  WTG_CID = :B2
AND    WTG_PID = :B1
AND    EXISTS (SELECT 1
               FROM   DR$PENDING
               WHERE  DR$PENDING.PND_CID = DR$WAITING.WTG_CID
               AND    DR$PENDING.PND_PID = DR$WAITING.WTG_PID
               AND    DR$PENDING.PND_ROWID = DR$WAITING.WTG_ROWID)


 [ EXECUTION  PLAN ]                                                                                                           
-------------------------------------------------------------------------------------------------------------------------------
{( CHILD NUMBER = 0 }}
*DELETE STATEMENT - ALL_ROWS- Cost Estimate:                                                                                    
* DELETE :CTXSYS.DR$WAITING(Ali:)                                                                                               
*  HASH JOIN SEMI (SEMI)                                                                                                        
*   TABLE ACCESS FULL {{FULL:14286}}:CTXSYS.DR$WAITING(T)(POS:1)(Ali:DR$WAITING@DEL$1)                                          
*   INDEX RANGE SCAN (RANGE SCAN):CTXSYS.SYS_IOT_TOP_27194(I)(Ali:DR$PENDING@SEL$1)(IOT:PND_CID,PND_PID,PND_ROWID)(SC:2)        

-- 20. 동기화 이후 검색 시 새로운 텍스트에 대해 검색 가능
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'problem') > 0
;

TXTFIELD                                
----------------------------------------
no problem of money                     

-- 21. 5글자 단어도 검색 가능
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'money') > 0
;

TXTFIELD                                
----------------------------------------
no problem of money                     

-- 22. 하지만 두 글자 단어는 동기화 이후에도 여전히 검색 불가함
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'on') > 0
;

no rows selected

-- 23. 새로운 데이터 생성
INSERT INTO TX_TEST VALUES('was there a war inside the store here');

COMMIT;

-- 24. 동기화 되기 전이므로 검색 불가
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'war') > 0
;

no rows selected

-- 25. 동기화 진행
EXEC CTX_DDL.SYNC_INDEX('TX_TEST_INDEX')
;

-- 26. 이번 검색은 3 글자로 된 단어 결과를 보여줌
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'war') > 0
;

TXTFIELD                                
----------------------------------------
was there a war inside the store here   

-- 27. 일반적인 영어 단어 "here"는 검색되지 않는다는 테스트인데 검색은 됨 -_-
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'here') > 0
;

TXTFIELD                                
----------------------------------------
was there a war inside the store here   

-- 28. 같은 원리로 "the"는 검색되지 않음
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'the') > 0
;

no rows selected

-- 29. "was"도 마찬가지
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'was') > 0
;

no rows selected

-- 30. 하지만 "war"는 검색 가능 
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'war') > 0
;

TXTFIELD                                 
---------------------------------------- 
was there a war inside the store here    

-- 31. 마찬가지로 "there"는 검색 불가
SELECT TXTFIELD
FROM   TX_TEST
WHERE  CONTAINS(TXTFIELD, 'there') > 0
;

no rows selected

☞ CTXCAT

  • 이 인덱스 타입은 짧은 텍스트 샘플(온라인 카탈로그와 같은 곳에서 사용하는)과 동일 테이블의 다른 컬럼을 함께 사용하는데 특화됨.
  • 이 인덱스는 테이블 데이터가 변경되어도 일관적인 산태를 유지함.
CTX_DDL.CREATE_INDEX_SET('BugsCatalogSet');
CTX_DDL.ADD_INDEX('BugsCatalogSet', 'status');
CTX_DDL.ADD_INDEX('BugsCatalogSet', 'priority');

CREATE INDEX BUGSCATALOG ON BUGS(SUMMARY) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS('BugsCatalogSet');

CATSEARCH() 연산자는 텍스트 컬럼과 일반 컬럼 집합을 위한 두 개의 인수를 취함

SELECT *
FROM   BUGS
WHERE  CATSEARCH(SUMMARY, '(crash save)', 'status = "NEW"') > 0;
  • 아래의 사이트에서 참고 가능

☞ CTXXPATH

  • 이 인덱스 타입은 existsNode() 연산자로 XML 문서를 검색하는 데 특화되었다.
CREATE INDEX BUGTEXTXML ON BUGS(TESTOUTPUT) INDEXTYPE IS CTXSYS.CTXXPATH;

SELECT *
FROM   BUGS
WHERE  TESTOUTPUT.EXISTSNODE('/testsuite/test(@status="fail")') > 0;

☞ CTXRULE

  • CTXRULE 인덱스를 사용하면, 문서를 분석해 분류하는 규칙을 설계할 수 있음.
  • 또는 사람의 생각대로 분류한 문서 집합의 샘플을 제공하면 Oracle이 규칙을 설계해 나머지 문서 집합에 적용토록 할 수 있음.
1-3) MSSQL에서의 전체 텍스트 검색
  • SQL Server에서는 일련의 저장 프로시저를 사용해 전체 텍스트 인덱스를 생성할 수 있으며, 전체 텍스트 인덱스를 사용하기 위해 CONTAINS() 연산자 사용함.

-- 1. crash란 단어를 포함하는 버그를 검색하려면, 먼저 전체 텍스트 기능을 활성화하고, 
--   데이터베이스에 카탈로그 정의해야 함.
EXEC sp_fulltext_database 'enable'
EXEC sp_fulltext_catalog  'BugsCatalog', 'create'


-- 2. 이후 BUGS 테이블에 전체 텍스트 인덱스를 정의하고, 인덱스에 컬럼을 추가하고, 
--    인덱스를 활성화함.
EXEC sp_fulltext_table  'Bugs', 'create', 'BugsCatalog', 'bug_id'
EXEC sp_fulltext_column 'Bugs', 'summary', 'add', '2057'
EXEC sp_fulltext_column 'Bugs', 'description', 'add', '2057'
EXEC sp_fulltext_table  'Bugs', 'activate'

-- 3. 전체 텍스트 인덱스에 대해 자동 동기화 기능을 활성화 해, 
--    인덱스가 걸린 컬럼의 데이터가 바뀌면 변경 내용이 전파되도록 하고, 
--    이후 인덱스를 띄울 프로세스를 시작함.
--    이 프로세스는 백그라운드로 실행되며, 쿼리가 인덱스를 활용할 수 있게 되기까지 약간 시간 소요됨
EXEC sp_fulltext_table 'Bugs', 'start_change_tracking'
EXEC sp_fulltext_table 'Bugs', 'start_background_updateindex'
EXEC sp_fulltext_table 'Bugs', 'start_full'

-- 4. 마지막으로 CONTAINS() 연산자를 사용하는 쿼리 실행
SELECT * FROM Bugs WHERE CONTAINS(summary, '"crash"');
1-4) PostgreSQL에서의 텍스트 검색
  • PostgreSQL 8.3은 텍스트를 검색 가능한 어휘 요소의 집합으로 변환하고, 이를 패턴에 매칭하도록 하는, 정교하며 고도로 설정 가능한 방법 제공함.
  • 성능 이점을 최대로 얻으려면, 내용을 원래의 텍스트 뿐 아니라 TSVECTOR란 특별한 데이터 타입을 사용하여 검색 가능한 형태로 저장해야 함.
CREATE TABLE BUGS (BUG_ID SERIAL PRIMARY KEY,
                   SUMMARY      VARCHAR(80),
                   DESCRIPTION  TEXT,
                   TS_BUGTEXT   TSVECTOR
                   -- OTHER COLUMNS
                  );
  • 검색 가능하게 하고 싶은 텍스트 컬럼의 내용과 TSVECTOR 컬럼이 동기화 되도록 했는지 확인해야 하는데, PostgreSQL은 이를 쉽게 해주는 내부 트리거 제공함.
CREATE TRIGGER ts_bugtext BEFORE INSERT OR UPDATE ON Bugs
FOR EACH ROW EXECUTE PROCEDURE
  tsvector_update_trigger(ts_bugtext, 'pg_catalog.english', summary, description);
  • 또한 TSVECTOR 컬럼에 GIN(generalized inverted index) 인덱스를 생성해야 함.
CREATE INDEX bugs_ts ON Bugs USING GIN(ts_bugtext);
  • 이후, 전체 텍스트 인덱스의 도움을 받아 검색을 효율적으로 할 수 있으며, 검색연산자는 @@를 사용하면 됨
SELECT *
FROM   BUGS
WHERE  TS_BUGTEXT @@ TO_TSQUERY('crash');
1-5) SQLite에서의 전체 텍스트 검색
  • SQLite에서 일반 테이블은 효과적인 전체 텍스트 검색을 지원하지 않으나, SQLite의 확장기능을 시용할 경우 텍스트 검색에 특화된 가상 테이블에 검색하려는 텍스트 저장 가능.
    • FTS1, FTS2, FTS3 세 가지 버전의 텍스트 검색 확장기능 존재함.
  • FTS 확장은 SQLite의 디폴트 빌드에는 활성화되어 있지 않음.
    따라서 FTS 확장 중 하나를 활성화하고 소스 코드를 직접 빌드해야 함.

-- 1. MakeFile.in에서 다음 옵션을 추가하여 SQLite 빌드
TCC += -DSQLITE_CORE=1
TCC += -DSQLITE_ENABLE_FTS3=1

-- 2. 텍스트 검색을 위한 가상테이블 생성(데이터 타입, 제약조건, 다른 컬럼 옵션은 무시)
CREATE VIRTUAL TABLE BugsText USING fts3(summary, description);

-- 3. 다른 테이블의 텍스트를 인덱싱하려면, 데이터를 가상 테이블에 복사해주어야 함
INSERT INTO BUGSTEXT (DOCID,
                      SUMMARY,
                      DESCRIPTION)
SELECT BUG_ID,
       SUMMARY,
       DESCRIPTION
FROM   BUGS
;

-- 4. 전체 텍스트 검색 연산자 MATCH를 이용해 FTS 가상 테이블 BUGSTEXT를 조회하고, 
--    매칭되는 행을 원본 테이블인 BUGS와 조인할 수 있음.
SELECT B.*
FROM   BUGSTEXT T
JOIN   BUGS B ON (T.DOCID = B.BUG_ID)
WHERE  BUGSTEXT MATCH 'crash'
;

-- 5. 또한 패턴 매칭에서 제한된 BOOLEAN 수식 사용 가능
SELECT *
FROM   BUGSTEXT
WHERE  BUGSTEXT MATCH 'crash -save'

2) 서드파티 검색 엔진

2-1) Sphinx Search
  • Sphinx Search(http://www.sphinxsearch.com/)는 오픈 소스 검색 엔진 기술로 MySQL, PostgreSQl과 잘 통합됨.
  • Sphinx Search는 인덱싱과 검색이 빠르고, 분산 쿼리 또한 지원됨. UPDATE 빈도가 적고, 검색 빈도가 많은 어플리케이션에 적합함.
  • MySQL 데이터베이스에 저장된 데이터를 인덱싱하는 데 Sphinx Search 사용 가능

-- 1. sphinx.conf에 설정
source bugsrc
{
  type                 = mysql
  sql_user             = bugsuser
  sql_pass             = xyzzy
  sql_db               = bugsdatabase
  sql_query            = \
    SELECT bug_id, status, date_reported, summary, description \
    FROM Bugs
  sql_attr_timestamp   = date_reported
  sql_attr_str2ordinal = status
  sql_query_info       = SELECT * FROM Bugs WHERE bug_id = $id
}

index bugs
{
  source               = bugsrc
  path                 = /opt/local/var/db/sphinx/bugs
}

-- 2. 1번에서 설정된 내용으로 Shell에서 indexer 명령어로 인덱스 생성 가능
indexer -c sphinx.conf bugs

-- 3. search 명령을 사용해 인덱스 검색 가능
search -b "crash -save"
  • Sphinx Search는 데몬 프로세스와 API도 가지고 있음. 이 API를 이용해 PHP나 Perl, Ruby같은 인기 있는 스크립팅 언어에서 검색 호출도 가능함.
  • 가장 큰 단점은, 색인 알고리즘이 점증적 업데이트를 효율적으로 지원하지 못함. 번번하게 UPDATE되는 데이터 소스에 Sphinx Search를 사용하려면 타협이 필요함.
    예를들어, 검색 대상 테이블을 둘로 나누어, 한쪽 테이블에는 변하지 않은 과거 데이터를 저장하고, 다른 쪽 테이블에는 점증적으로 늘어나고 주기적으로 재색인 작업을 해야 하는 현재 데이터를 저장한 후, 어플리케이션에서 두 개의 Sphinx Search 인덱스 검색 가능
2-2) Apache Lucene
  • Lucene(http://lucene.apache.org/)은 JAVA 어플리케이션을 위한 성숙한 검색 엔진.
  • Lucene은 텍스트 문서 집합에 대해 자신의 고유형식으로 인덱스를 생성하나, 인덱스는 색인하는 소스 데이터와 동기화되지 않음.
  • 데이터베이스에 레코드를 INSERT, DELETE, UPDATE 하는 경우 Lucene 인덱스에도 대응되는 변경을 직접 적용해 주어야 함.
    • 다행이 Apache는 Solr(http://lucene.apache.org/solr)라 불리는 보완 프로젝트 제공하는데, Slor은 Lucene 인덱스로의 게이트웨이 제공함.
    • 또한DataImportHandeler 도구를 사용해 Solr가 직접 데이터베이스에 접속해 쿼리를 실행하고, 결과를 색인하도록 할 수 있음.

3) 직접 만들기

  • 특정 밴더나 데이터베이스에 종속된 검색방법을 사용하고 싶지 않을 경우, 전치 인덱스(inverted index) 고려 가능
  • 전치 인덱스는 검색할 모든 단어의 목록인데, M:M 관계에서, 인덱스는 이 단어들과 각 단어를 포함한 텍스트 항목을 연관시킴.
  • 즉, crash란 단어는 많은 버그에 나타날 수 있고, 각 버그는 다른 많은 키워드를 가질 수 있음.
3-1) 테이블 생성
  • 사용자가 검색할 키워드 목록을 나타내는 Keywords 테이블을 정의하고, BugsKeywords 교차 테이블을 정의해 M:M 관계 생성
CREATE TABLE KEYWORDS (KEYWORD_ID  SERIAL PRIMARY KEY,
                       KEYWORD     VARCHAR(40) NOT NULL,
                       UNIQUE KEY (KEYWORD));

CREATE TABLE BUGSKEYWORDS (KEYWORD_ID  BIGINT UNSIGNED NOT NULL,
                           BUG_ID      BIGINT UNSIGNED NOT NULL,
                           PRIMARY KEY (KEYWORD_ID, BUG_ID),
                           FOREIGN KEY (KEYWORD_ID) REFERENCES KEYWORDS(KEYWORD_ID),
                           FOREIGN KEY (BUG_ID) REFERENCES BUGS(BUG_ID));
  • 이후, 주어진 버그에 댛 ㅐ설명 텍스트와 매치되는 모든 키워드를 BugsKeywords에 추가함.
  • LIKE나 정규 표현식을 이용한 부분문자열 매치쿼리를 사용해 매치되는지 여부 결정 가능.
3-2) 프로시저 생성
  • 단어가 이미 검색된 적이 있다면, 그 키워드를 포함하는 문서 목록이 BugsKeywords에 존재하므로 쿼리 속도는 빠름.
  • 주어진 키워드를 아직 아무도 검색한 적이 없으면, 어려운 방법으로 텍스트 항목의 집합을 검색해야 함.
CREATE PROCEDURE BugsSearch(keyword VARCHAR(40))
BEGIN
  SET @keyword = keyword;

  -- 1. 사용자가 지정한 키워드를 검색함.
  --    정수형 PK인 keywords.keyword_id 값을 리턴함.
  --    이전에 검색된 적이 없는 단어라면 NULL을 리턴함.
  PREPARE s1 FROM 'SELECT MAX(keyword_id) INTO @k FROM Keywords -- <callout id="code.search.getk"/> WHERE keyword = ?';
  EXECUTE s1 USING @keyword;
  DEALLOCATE PREPARE s1;

  IF (@k IS NULL) THEN
      -- 2. 단어가 없으면, 새로운 키워드로 등록함.
      PREPARE s2 FROM 'INSERT INTO Keywords (keyword) VALUES (?)'; -- <callout id="code.search.insert1"/>
      EXECUTE s2 USING @keyword;
      DEALLOCATE PREPARE s2;
    
      -- 3. Keywords에서 생성된 PK 값을 조회함.
      SELECT LAST_INSERT_ID() INTO @k; -- <callout id="code.search.lastid"/>

      -- 4. Bugs에서 새로운 키워드를 포함하는 행을 검색해 교차 테이블에 넣는다.
      PREPARE s3 FROM 'INSERT INTO BugsKeywords (bug_id, keyword_id) -- <callout id="code.search.insert2"/>
          SELECT bug_id, ? FROM Bugs
          WHERE  summary REGEXP CONCAT(''[[:<:]]'', ?, ''[[:>:]]'')
	             OR description REGEXP CONCAT(''[[:<:]]'', ?, ''[[:>]]'')';
      EXECUTE s3 USING @k, @keyword, @keyword;
      DEALLOCATE PREPARE s3;
  END IF;
  
  -- 5. 마지막으로, 키워드를 찾았든 새로운 항목을 추가했든 keyword_id를 이용해 Bugs의 전체 행을 조회함.
  PREPARE s4 FROM 'SELECT b.* FROM Bugs b -- <callout id="code.search.select"/>
                   JOIN BugsKeywords k USING (bug_id)
                   WHERE k.keyword_id = ?';
  EXECUTE s4 USING @k;
  DEALLOCATE PREPARE s4;
END
3-3) 프로시저 호출
  • 이제 원하는 키워드로 이 저장 프로시저 호출 가능. 프로시저는 매칭되는 버그 집합을 리턴하는데, 새로운 키워드에 대해 매칭되는 버그를 찾고 교차 테이블에 데이터를 넣고 나서 리턴한 것일수 있고, 간단히 이전 검색의 결과를 이용한 것일 수 있다.
CALL BugsSearch('crash');
  • 이 방법의 또 다른 조각이 있다. 트리서를 정의해 새로운 버그가 등록될 때 마다 교차 테이블에 데이터를 넣어줘야 한다. 버그 설명을 편집할 수 있는 경우에는, 텍스트를 다시 분석해 BugsKeywords 테이블에 행을 추가하거나 삭제하는 트리거도 작성해야 함.
DELIMITER //
CREATE TRIGGER Bugs_Insert AFTER INSERT ON Bugs
FOR EACH ROW
BEGIN
  INSERT INTO BugsKeywords (bug_id, keyword_id)
    SELECT NEW.bug_id, k.keyword_id FROM Keywords k
    WHERE NEW.description REGEXP CONCAT('[[:<:]]', k.keyword, '[[:>:]]')
	   OR NEW.summary REGEXP CONCAT('[[:<:]]', k.keyword, '[[:>:]]');
END;
  • 키워드 목록은 사용자가 검색을 수행하면 자연히 생성되기 때문에, 문서에 나오는 모든 단어로 키워드 목록을 채울 필요는 없음.
  • 그러나, 검색될 것 같은 키워드를 예상하여 이를 미리 검색해 두면, 각 키워드의 첫 검색에 드는 초기 비용을 사용자가 느끼지 못하게 할 수 있음.

모든 문제를 SQL로 풀어야 하는 것은 아니다.

Reference Site

1. techxplore

2. oracle-base

문서에 대하여

  • 최초작성자 : 강정식
  • 최초작성일 : 2011년 12월 17일
  • 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 인사이트(insight) 에서 출간한 'SQL AntiPatterns : 개발자가 알아야 할 25가지 SQL 함정과 해법'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.