5.7 전문 검색(Full Text search) 인덱스

문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문(Full Text) 검색에는 일반적인 스토리지 엔진에서 제공하는 B-Tree 인덱스를 사용할 수 없다. 문서 전체에 대한 분석과 검색을 위한 이러한 인덱싱 알고리즘을 전문 검색(Full Text search) 인덱스 라고 하는데, 전문 검색 인덱스는 일반화된 기능의 명칭이지 전문 검색 알고리즘의 이름을 지칭하는 것은 아니다.

전문 검색 인덱스에는 문서의 키워드를 인덱싱하는 기법에 따라 크게 구분자(Stopword)와 N-그램으로 나눠서 생각해 볼 수 있다.

5.7.1 인덱스 알고리즘

구분자 기법

전문의 내용을 공백이나 탭 또는 마침표와 같은 문장 기호, 그리고 사용자가 정의한 문자열을 구분자로 등록한다. 구분자 기법은 이처럼 등록된 구분자를 이용해 키워드를 분석해 내고, 결과 단어를 인덱스로 생성해 두고 검색에 이용하는 방법을 말한다.

구분자 기법은 문서의 본문으로 부터 키워드를 추출해 내는 작업이 추가로 필요할 뿐, 내부적으로는 B-Tree 인덱스를 그대로 사용한다. 전문 검색 인덱스의 많은 부분은 B-Tree의 특성을 따르지만 전문 검색 엔진을 통해 조회되는 레코드는 검색어나 본문 내용으로 정렬되어 조회되지는 않는다. 전문 검색에서 결과의 정렬은 일치율(Match percent)이 높은 순으로 출력되는 것이 일반적이다.

N-그램(n-Gram) 기법

구분자 기법은 B-Tree의 특성을 따르기 때문에 그 단점도 따르게 된다. 또한 각 국가의 언어 문법이 다양한데 하나의 규칙을 적용해 키워드를 추출하기는 쉽지 않다. 이러한 단점을 보완한것이 N-그램 방식이다.

N-그램이란 본문을 무조건적으로 몇 글자씩 잘라서 인덱싱하는 방법이다. 구분자에 의한 방법보다는 인덱싱 알고리즘이 복잡하고, 만들어진 인덱스의 크기도 상당히 큰 편이다. N-그램에서 n은 인덱싱할 키워드의 최소 글자(또는 바이트)수를 의미하는데, 일반적으로 2글자 단위로 키워드를 쪼개서 인덱싱 하는 2-Gram(또는 Bi-Gram 이라고도 한다)방식이 많이 사용된다. 여기서도 2글자 키워드 방식의 2-Gram 위주로 알아보겟다.

2-Gram 인덱싱 기법은 2글자 단위의 최소 키워드에 대한 키를 관리하는 프론트엔드(Front-end) 인덱스와 2글자 이상의 키워드 묶음(n-SubSequence Window)을 관리하는 백엔드(Back-end)인덱스 2개로 구성된다. 인덱스 생성 과정은 다음과 같다.

  • 첫 번째 단계로, 문서의 본문을 2글자보다 큰 크기로 블록을 구분해서 백엔드 인덱스를 생성
  • 두 번째 단계로, 백엔드 인덱스의 키워드들을 2글자씩 잘라서 프론트엔드 인덱스를 생성

인덱스의 검색 과정은 전문 인덱스의 생성과는 반대로, 입력된 검색어를 2바이트 단위로 동일하게 잘라서 프론트엔드 인덱스를 검색한다. 그 결과를 대상 후보군으로 선정하고, 백엔드 인덱스를 통해 최종 검증을 거쳐 일치하는 결과를 가져온다.

5.7.2 구분자와 N-그램의 차이

실제 사용자가 보는 구분자와 N그램의 검색 결과를 보자. 구분자 방식은 검색할 때 왼쪽 일치 기준으로 비교 검색을 실행한다. 그래서 검색단어의 왼쪽에 다른글자가 같이 있으면 검색이 안된다. 예를 들면 “아이폰”을 검색하면 “애플아이폰” 이 검색되지 않는다. 하지만 N그램은 모든 데이터에 대해 무작위로 2바이트씩 인덱스를 생성하므로 검색이 가능하다.

5.7.3 전문 검색 인덱스의 가용성

전문 검색 인덱스를 사용하려면 반드시 그에 맞는 구문을 사용해야 한다.

1
select * from tb_test where doc_body like '%애플%';

위와같은 구문은 원하는 검색을 찾을 수 있지만, 효율적으로 쿼리가 실행된 것이 아니라 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔으로 쿼리를 처리한다. 전문 검색 인덱스를 사용하려면 MATCH() AGAINST() 구문으로 검색쿼리를 작성해야 하며, MATCH 절의 괄호에 포함되는 내용은 반드시 사용할 전문 검색 인덱스에 정의된 칼럼이 모두 명시돼야 한다.

5.8 비트맵 인덱스와 함수 기반 인덱스

Mysql 스토리지 엔진 가운데 비트맵 인덱스와 함수 기반 (Function based) 인덱스를 지원하는 스토리지 엔진은 없다. 비트 맵 인덱스에 대한 대안은 없지만 함수 기반의 인덱스는 쉽게 우회해서 구현할 수 있다. 테이블에 함수의 결과 값을 저장하기 위한 칼럼을 추가하고, 그 칼럼에 인덱스를 생성해서 사용하면 된다.

5.9 클러스터링 인덱스

클러스터란 여러 개를 하나로 묶는다는 의미로 주로 사용되는데, 지금 설명하고자 하는 인덱스의 클러스터링도 그 의미를 크게 벗어나지 않는다. 인덱스에서 클러스터링은 값이 비슷한 것들을 묶어서 저장하는 형태로 구현되는데, 이는 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에 착안한것이다.

5.9.1 클러스터링 인덱스

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다. 즉 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다. 여기서 중요한 것은 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다는 것이다. 또한 프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미하기도 한다. 프라이머리 키 값으로 클러스터링된 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크기 때문에 신중히 PK를 결정해야 한다.

클러스터링 인덱스는 프라이머리 키 값에 의해 레코드의 저장 위치가 결정되므로 사실 인덱스 알고리즘이라기 보다 테이블 레코드의 저장 방식이라고 볼 수도 있다. 그래서 “클러스터링 인덱스”와 “클러스터링 테이블”은 동의어로 사용되기도 한다.

클러스터링 인덱스의 구조는 B-Tree와 많이 닮았지만 클러스터링 인덱스의 리프 노드에는 레코드의 모든 칼럼이 같이 저장돼 있다. 즉 클러스터링 테이블은 그 자체 하나의 거대한 인덱스 구조로 관리되는 것이다.

PK가 없는 경우는 다음의 우선순위대로 PK를 대체할 칼럼을 선택한다.

  • PK가 있으면 기본적으로 PK를 클러스터 키로 선택
  • NOT NULL 옵션의 유니크 인덱스(UNIQUE INDEX) 중에서 첫 번째 인덱스를 클러스터 키로 선택
  • 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터 키로 선택

5.9.2 보조 인덱스(Secondary index)에 미치는 영향

PK가 보조 인덱스에 미치는 영향을 알아보자. MyISAM이나 MEMORY테이블과 같은 클러스터링 되지 않은 테이블은 Insert 될 때 한번 저장된 공간에서 절대 이동하지 않는다. MyISAM 테이블이나 MEMORY 테이블에서는 PK와 보조 인덱스는 구조적으로 아무 차이가 없다. 하지만 InnoDB 테이블의 모든 보조 인덱스는 해당 레코드가 저장된 주소가 아니라 PK값을 저장하도록 구현돼 있다.

5.9.3 클러스터 인덱스의 장점과 단점

  • 장점
    • PK로 검색할 때 처리 성능이 매우 빠름
    • 테이블의 모든 보조 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음
  • 단점
    • 테이블의 모든 보조 인덱스가 클러스터 키를 갖기 때문에 클러스터 키값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
    • 보조 인덱스를 통해 검색할 때 PK로 다시 한번 검색해야 하므로 처리 성능이 조금 느림
    • Insert 할 때 PK에 의해 레코드의 저장 뒤치가 결정되기 때문에 성능이 느림
    • PK를 변경할 때 레코드를 Delete하고 Insert하는 작업이 필요하기 때문에 처리 성능이 느림

5.9.4 클러스터 테이블 사용 시 주의사항

MyISAM과 같이 클러스터링되지 않은 테이블에 비해 InnoDB에서는 조금 더 주의해야 할 사항이 있다.

  • 클러스터 인덱스 키의 크기
    클러스터 테이블의 경우, 모든 보조 인덱스가 PK값을 포함한다. 그래서 PK의 크기가 커ㅣ면 보조 인덱스도 크키가 커진다. 하지만 일반적으로 테이블에 보조 인덱스가 4~5개 정도 생성된다는 것을 고려하면 보조 인덱스 크기는 급격히 증가한다.
  • PK는 AUTO-Increment 보다는 업무적인 칼럼으로 생성할 것(가능한 경우)
  • PK는 반드시 명시할 것
  • Auto-Increment 칼럼을 인조 식별자로 사용할 경우

5.10 유니크 인덱스

유니크 인덱스는 사실 인덱스라기 보다는 제약조건에 가깝다. 말 그대로 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미하는데, Mysql에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.

5.10.1 유니크 인덱스와 일반 보조 인덱스의 비교

유니크 인덱스와 유니크하지 않은 일반 보조 인덱스는 사실 인덱스의 구조상 아무런 차이점이 없다. 유니크 인덱스와 일반 보조 인덱스의 읽기와 쓰기를 성능 관점에서 한번 살펴보자

  • 인덱스 읽기

많은 사람들이 유니크 인덱스가 빠르다고 생각하지만 사실은 그렇지 않다. 유니크하지 않은 보조 인덱스에서 한번 더 해야 하는 작업은 디스크 읽기가 아니라 CPU에서 칼럼값을 비교하는 작업이기 때문에 이는 성능상의 영향이 거의 없다고 볼 수 있다. 유니크하지 않은 보조 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지, 인덱스 자체의 특성 때문에 느린 것이 아니라는 것이다.

  • 인덱스 쓰기

새로운 레코드가 Insert되거나 인덱스 칼럼의 값이 변경되는 경우에는 인덱스 쓰기 작업이 필요하다. 그런데 유니크 인덱스의 키값을 쓸때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다. 그래서 일반 보조 인덱스의 쓰기보다 느리다. 그런데 Mysql에서는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번하게 발생한다.

InnoDB 스토리지 엔진에서는 인덱스 키의 저장을 버퍼링하기 위해 인서트 버퍼가 사용된다. 그리서 인덱스의 저장이나 변경 작업이 상당히 빨리 처리되지만 안타깝게도 유니크 인덱스는 반드시 중복 체크를 해야 하므로 작업 자체를 버퍼링하지 못한다. 이 때문에 유니크 인덱스는 일반 보조 인덱스보다 더 느려진다.

5.10.2 유니크 인덱스 사용 시 주의사항

유일성이 꼭 보장돼야 하는 칼럼에 대해서는 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크 인덱스보다는 유니크하지 않은 보조 인덱스를 생성하는 방법도 한 번씩 고려해 보자.

5.11 외래키

Mysql에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성된다. 외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없다.

InnoDB의 외래키 관리에는 중요한 두 가지 특징이 있다.

  • 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
  • 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.

5.11.1 자식 테이블의 변경이 대기하는 경우

자식 테이블의 외래키 칼럼의 변경은 부모 테이블의 확인이 필요한데, 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 잇으면 해당 쓰기 잠금이 해제될 때까지 기다리게 되는 것이다. 이것이 InnoDB의 외래키 관리의 첫 번째 특징에 해당한다.

만약 자식 테이블의 외래키가 아닌 칼럼의 변경은 외래키로 인한 잠금 확장이 발생하지 않는다. 이는 InnoDB의 외래키의 두 번째 특징에 해당한다.

5.11.2 부모 테이블의 변경 작업이 대기하는 경우