InnoDB에서 MySQL LIKE '%string%' 쿼리 최적화

많은 애플리케이션에서는 텍스트 본문 내에서 문자열을 검색해야 합니다. MySQL은 이러한 검색을 수행하기 위해 LIKE 연산자를 제공하지만 최적의 성능을 위해 이러한 쿼리를 어떻게 최적화할 수 있습니까?

문제

다음 표를 고려하십시오.

CREATE TABLE `example` (
`id` int(11) unsigned NOT NULL auto_increment,
`keywords` varchar(200) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

다음과 같은 쿼리를 실행할 때:

SELECT id FROM example WHERE keywords LIKE '%whatever%'

우리는 데이터베이스가 검색 속도를 높이려면 색인을 사용하십시오. 그러나 다음을 사용하여 키워드 열에 간단한 인덱스를 추가하면:

ALTER TABLE `example` ADD INDEX `idxSearch` (`keywords`);

EXPLAIN 쿼리는 다음을 나타냅니다. MySQL은 여전히 ​​전체 테이블을 스캔해야 합니다.

해결책

MySQL은 검색 문자열의 시작 부분을 일치시켜 인덱스를 사용합니다. LIKE 'whatever%'와 같은 쿼리의 경우 'whatever'가 문자열 시작 부분에 고정되어 있으므로 인덱스를 사용할 수 있습니다.

그러나 LIKE '%whatever%'와 같은 쿼리에는 이러한 앵커가 없습니다. 검색어는 문자열 내에서 "떠다니는" 것처럼 나타나므로 MySQL은 전체 필드를 스캔해야 합니다.

이러한 쿼리를 최적화하려면 전체 텍스트 인덱스를 사용할 수 있습니다. 이러한 색인은 "부동" 검색을 위해 특별히 설계되었습니다. InnoDB는 버전 5.6.4부터 전체 텍스트 인덱스를 지원하여 LIKE '%string%' 쿼리를 최적화하기 위한 실행 가능한 옵션이 되었습니다.