8장

8.1 디스크 읽기 방식

MySQL에서 사용 가능한 인덱스의 종류 및 특성에 대한 장이다.

데이터베이스 성능 튜닝은 어떻게 디스크 IO를 줄이느냐가 관건일 때가 많다. 요즘은 DBMS용으로 사용할 서버에서 대부분 SSD를 채택하고 있다. 순차 IO에서는 속도가 비슷할 수 있지만 랜덤 IO에서는 SSD가 압도적으로 빠르다.

쿼리를 튜닝해서 랜덤IO를 순차IO로 바꿔 실행할 방법은 많지 않아서 랜덤IO 자체를 줄여주는 것이 쿼리 튜닝의 목적이다.

8.2 인덱스란?

인덱스 - 책의 마지막에 찾아보기 데이터 파일 - 책의 내용 데이터 파일에 저장된 레코드의 주소 - 페이지 번호

DBMS에서 인덱스는 데이터가 저장될 때마다 정렬해야 하므로 데이터의 저장 성능을 희생하고 데이터의 읽기 속도를 높이는 기능이다.

인덱스는 역할별로 프라이머리키와 보조키(세컨더리 인덱스)로 구분할 수 있다.

프라이머리키: NULL을 허용하지 않으며 중복을 허용하지 않는 레코드를 식별할 수 있는 식별자
세컨더리인덱스: 프라이머리키를 제외한 모든 인덱스

알고리즘별 분류

B-Tree: 가장 일반적으로 사용되는 인덱스 알고리즘, 칼럼의 값을 변형하지 않고 원래의 값을 인덱싱하는 알고리즘 R-Tree는 B-Tree의 응용 알고리즘이다.
Hash: 칼럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘으로 빠른 검색을 지원하지만 값을 변형해서 인덱싱하므로 값의 일부만 검색하거나 범위 검색에서는 이용할 수 없으며 메모리 기반의 데이터베이스에서 많이 사용한다.

8.3 B-Tree 인덱스(Balanced-Tree)

가장 일반적 알고리즘으로 일반적으로 DBMS에서는 B+-Tree 혹은 B-Tree가 사용된다.

구조 및 특성

B-Tree는 최상위 하나의 루트 노드, 그 하위의 브랜치 노드 가장 하위 노드를 리프 노드라고 한다. 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데 리프노드는 항상 실제 데이터 레코드의 주솟값을 가지고 있다.

InnoDB 테이블은 프라이머리 키가 물리적 주솟값 역할을 하고 세컨더리 인덱스가 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다고 볼 수 있다.

B-Tree 인덱스 키 추가 및 삭제

테이블의 레코드를 저장하거나 변경하는 경우 인덱스 키 추가나 삭제 작업이 발생한다.

인덱스 키 추가

새로운 키 값이 B-Tree에 저장되려면 B-Tree의 적절한 위치를 검색해 리프노드가 꽉 차면 리프노드가 분리되고 이는 상위 브랜치 노드까지 범위가 넓어진다.

테이블에 레코드를 추가하는 작업 비용을 1이라고 하면 인덱스에 키를 추가하는 작업 비용은 1.5정도로 예측된다. 인덱스가 3개인 테이블이라면 1 만큼의 저장에 (1.5 * 3 + 1)5.5의 비용이 예측된다.

InnoDB엔진은 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있지만 프라이머리키나 유니크 인덱스는 중복 체크를 위해 즉시 처리한다.

인덱스 키 삭제

B-Tree의 삭제는 해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 삭제 마크만 하면 작업이 완료된다. 이것도 5.5버전 이상에서는 버퍼링되어 지연 처리될 수 있다.

인덱스 키 변경

B-Tree의 키 값 변경은 키 값을 삭제한 후 다시 새로운 값을 추가하는 형태로 처리되며 체인지 버퍼를 이용해 지연처리될 수 있다.

인덱스 키 검색

인덱스 검색은 B-Tree의 루트 노드부터 브랜치를 거쳐 리프까지 이동하면서 비교 작업을 수행하는데 이 작업을 트리 탐색이라 하며 UPDATE나 DELETE를 위한 선행검색에도 사용된다.

B-Tree 인덱스 검색은 100% 일치 또는 값의 앞부분만 일치하는 경우, 부등호 등을 사용할 수 있지만 뒷부분만 검색하는 용도로는 사용할 수 없다. 인덱스 키 값 변경이 가해진 후 비교되는 경우에도 빠른 검색 기능을 사용할 수 없다.

B-Tree 인덱스 사용에 영향을 미치는 요소

인덱스는

인덱스를 구성하는 칼럼의 크기와 레코드 건수
유니크한 인데스 키 값의 개수

등에 의해 검색이나 변경 작업의 성능이 영향을 받는다.

인덱스 키 값의 크기

스토리지 엔진은 디스크에 데이터를 저장하는 기본 단위를 페이지 또는 블록이라하고 모든 읽기 및 쓰기 작업의 최소 작업 단위가 되고, 버퍼링의 기본 단위이기도 하다.

페이지 사이즈는 innodb_page_size 변수를 이용해 4 ~ 64KB 사이의 값 설정이 가능하다. 기본은 16KB다.

자식 노드 주소는 복합적인 정보가 담긴 영역으로 6byte ~ 12byte의 다양한 값을 갖는다.

그럼 하나의 인덱스 페이지에 16 * 1024 / (16 + 12) = 585의 키 값이 저장 가능하다. 키 값이 32바이트라고 가정하면 372개의 키 값 저장이 가능해진다.

인덱스의 키 값이 커지면 디스크로부터 읽어야 하는 횟수가 느려지고 메모리에 캐시해둘 수 있는 레코드 수가 줄어들어 메모리 효율이 떨어지게 된다.

B-Tree 깊이

B-Tree의 깊이가 3인 경우 585 * 585 * 585 = 2억 정도의 키 값이 담긴다. 키 값이 32byte라면 372 * 372 * 372 = 5천만개의 키 값 저장이 가능하므로 사이즈가 작을수록 깊이가 얕아진다. 물론 depth는 보통 5를 넘지 않는다.

선택도(기수성)

선택도와 기수성은 거의 같은 의미로 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 인덱스는 선택도가 높을수록 빠르게 처리된다.

레코드 건수가 1만건이고 인덱스 칼럼의 유니크 갯수가 각각 10개, 1000개인 A, B의 사례로 보면 A는 선택도가 1000, B는 10이 되기 때문에 A의 인덱스는 비효율적이다.

SELECT *
FROM tb_test
WHERE country='KOREA' AND city='SEOUL';

A: country 컬럼의 유니크 값이 10개일 때 B: country 컬럼의 유니크 값이 1000개일 때

A는 검색시 1000개의 결과가 나오고 AND 이후의 연산에서 1건만 검색해 999건의 불필요한 읽기가 발생했고 B는 검색시 10개의 결과가 나오고 1건만 검색해 9건의 불필요한 읽기가 나왔기 때문에 효율성에서 차이가 난다.

읽어야 하는 레코드의 건수

인덱스를 통해 레코드 1 건을 읽는 것이 직접 레코드 1 건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 것으로 옵티마이저는 예측하기 때문에 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20 ~ 25를 넘어서면 인덱스보다 직접 필터링 방식을 하는 것이 효율적이다.

B-Tree 인덱스를 통한 데이터 읽기

인덱스를 이용하는 세 가지 방법을 살펴보자.

인덱스 레인지 스캔

가장 대표적 방법으로 나머지 접근 방식보다는 빠른 방법이다. 인덱스를 통해 한 건만 읽는 경우와 한 건 이상을 읽는 경우의 이름은 구분되지만 이번 절에서는 동일하게 인덱스 레인지 스캔으로 표현한다.

SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';

인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식으로

스캔하다가 리프 노드의 끝까지 읽으면 리프 노드 간의 링크를 이용해 다음 리프노드를 찾아 스캔한다.

인덱스의 레코드를 스캔하면서 실제 데이터 파일의 레코드를 읽어와야 하는 경우도 발생하는데 이 경우 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다.

인덱스의 검색 조건에 일치하는 건들은 리프 노드의 레코드 주소로 데이터 파일을 읽어오는데 한건 단위마다 랜덤IO가 일어난다. 인덱스를 통한 레코드 스캔은 4~~5배 시간이 든다고 했기 때문에 읽어야 할 레코드가 20~~25%를 넘어가면 직접 읽는 것이 더 효율적인 방식이다.

인덱스 레인지 스캔의 순서는 다음과 같다.

인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다(인덱스 탐색)
1번에서 탐색된 위치부터 필요한만큼 인덱스를 차례대로 읽는다.
2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

만약 쿼리가 필요한 데이터가 인덱스에 있다면 이를 커버링 인덱스라고 하고 성능이 빨라진다.

MySQL 서버는 1번과 2번 단계의 작업 수행 객수를 확인할 수 있는 상태값을 제공한다.

SHOW STATUS LIKE 'Handler_%'

Handler_read_first: 1번의 실행 횟수
Handler_read_last: 마지막 레코드를 읽은 횟수
Handler_read_key: 첫번째 레코드를 읽은 횟수
Handler_read_next: 2번 단계로 읽은 레코드 건수
Handler_read_prev: 2번 단계로 읽은 레코드 건수

인덱스 풀 스캔

인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다. 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌경우 이 방식을 사용한다.

쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는경우 이 방식이 사용된다.

루스 인덱스 스캔

오라클의 인덱스 스킵 스캔과 작동 방식이 비슷한 스캔으로 8.0부터 최적화를 지원하기 시작했다.

앞의 두 스캔은 타이트 스캔, 해당 스캔은 상반된 의미에서 듬성듬성 인덱스를 읽는 것을 의미한다.

인덱스 레인지 스캔과 비슷하지만 중간에 필요하지 않는 인덱스 키 값은 무시하고 넘어가는 형태로 처리한다.

SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dept_no BETWEEN 'd002' AND 'd004'
GROUP BY dept_no;

위의 쿼리에서 검색 조건에 들어있는 dept_emp 테이블은 (dept_no, emp_no) 조합으로 정렬이 돼 있어서 dept 그룹별로 첫 번째 레코드의 emp_no 값만 읽으면 된다. 따라서 필요한 값만 읽을 수 있따.

인덱스 스킵 스캔

DBMS에서 인덱스의 핵심은 값이 정렬돼 있다는 것이며 인덱스를 구성하는 칼럼의 순서가 중요하다.

ALTER TABLE employees
  ADD INDEX ix_gender_birthdate(gender, birth_date);

다음과 같은 인덱스를 가진 테이블이 있을 때 인덱스를 사용하려면 WHERE 조건절에 gender 칼럼의 비교조건이 필수다.

SELECT * FROM employees WHERE gender='M' AND birth_date>='1965-02-01';

8.0 부터 옵티마이저가 gender 컬럼을 뛰어넘어 birth_date컬럼만으로 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능이 도입됐다.

루스 인덱스 스캔이 GROUP BY 작업을 수행하기 위해 인덱스를 사용하는 경우에만 적용 가능한 반면 인덱스 스킵 스캔은 WHERE 조건절의 검색을 위해 사용 가능하도록 용도가 넓어졌다.

MySQL 옵티마이저는 gender 컬럼에서 유니크한 값을 모두 조회해서 주어진 쿼리에 gender 칼럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리한다.

인덱스 스킵 스캔은

WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야 함
쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야 함

이라는 단점이 있다.

다중 칼럼 인덱스

실제 서비스용 DB에서는 2개 이상의 컬럼을 포함하는 인덱스가 더 많이 사용되는데 이를 다중 칼럼 인덱스라고 하며 Concatenated Index라고도 한다.

인덱스 내에서 각 칼럼의 위치가 상당히 중요하다. 다음 순서의 인덱스 컬럼은 이전의 컬럼에 의해 정렬된 후 정렬된다.

B-Tree 인덱스의 정렬 및 스캔 방향

인덱스를 생성할 때 정렬 규칙에 따라 인덱스의 키 값은 오름차순이나 내림차순으로 정렬되며 어느 방향으로 읽을지는 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정된다.

인덱스의 정렬

상용 DBMS에서는 인덱스를 생성하는 시점에 인덱스의 각 컬럼의 정렬을 설정할 수 있다. 5.7버전까지는 컬럼단위로 설정이 불가능해 숫자 컬럼에 -1을 곱하는 우회 방법을 사용했지만 8.0부터는 다음과 같이 설정 가능해졌다.

CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);

인덱스 스캔 방향

옵티마이저는 ORDER BY 값에 따라 인덱스를 위에서부터 읽을지 아래서부터 읽을지 실행 계획을 만들어내 추가적 ordering을 방지한다.

내림차순 인덱스

내림차순, 오름차순과 관계없이 인덱스를 읽는 순서만 변경하면 속도가 똑같을 것 같지만 InnoDB에서 인덱스 역순 스캔이 정순 스캔에 비해 느리다. 그 이유는

페이지 잠금이 인덱스 정순 스캔에 적합한 구조
페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조

결국 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상 완화에 도움이 될 것이다.

B-Tree 인덱스의 가용성과 효율성

WHERE, GROUP BY, ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지 식별할 수 있어야 한다. 그래야 쿼리의 조건을 최적화하거나 쿼리에 맞게 인덱스를 최적화할 수 있다.

비교 조건의 종류와 효율성

다중 칼럼 인덱스에서 각 칼럼의 순서와 칼럼에 사용된 조건이 동등비교인지, 범위조건인지에 따라 인덱스 칼럼의 활용 형태와 효율이 달라진다.

SELECT * FROM dept_emp
WHERE dept_no='d002' AND emp_no >= 10114;

A: INDEX(dept_no, emp_no) B: INDEX(emp_no, dept_no)

A는 dept_no=d002 AND emp_no>=10144인 레코드를 찾고 인덱스를 쭉 읽기만 하면 된다.

B는 emp_no>=10144 AND dept_no=d002인 레코드를 찾고 모든 레코드에 대해 dept_no가 d002인지 비교하는 과정을 거쳐야 한다. 이를 필터링이라고 한다.

A에서 2번째 칼럼 emp_no은 비교 작업의 범위를 좁히는데 도움을 주지만 B는 조건 확인만 하고 범위를 좁혀주진 못한다.

A는 작업 범위 결정 조건이라고 하고 B는 필터링 조건 또는 체크 조건이라고 한다.

체크 조건은 쿼리 실행을 더 느리게 만들 때가 많다.

인덱스의 가용성

B-Tree 인덱스의 특징은 왼쪽 값에 기준에서 오른쪽 값이 정렬돼 있다는 것으로 다중 칼럼 인덱스의 칼럼에 대해서도 함께 적용된다.

A: INDEX(first_name) B: INDEX(dept_no, emp_no)

하나의 칼럼으로 검색해도 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능하고 다중 칼럼 인덱스에서도 왼쪽 칼럼의 값을 모르면 인덱스 레인지 스캔을 사용할 수 없다.

그 예로 다음과 같은 케이스들이 있다.

A: SELECT * FROM employees WHERE first_name LIKE '%mer'; B: SELECT * FROM dept_emp WHERE emp_no>=10144;

가용성과 효율성 판단

B-Tree 인덱스의 특성상 다음 조건에서는 작업 범위 결정 조건으로 사용할 수 없다.

NOT-EQUAL로 비교된 경우(<>, NOT IN, NOT BETWEEN, IS NOT NULL)
LIKE '%??'(뒷부분 일치)
스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
- WHERE SUBSTRING(column, 1, 1) = 'X'
- WHERE DAYOFMONTH(column) = 1
NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
- WHERE column = deterministic_function()
데이터 타입이 서로 다른 비교
문자열 데이터 타입의 콜레이션이 다른경우
- WHERE utf8_bin_char_column = euckr_bin_char_column

MySQL에서는 NULL도 인덱스에 저장된다. 따라서 다음과 같은 WHERE 조건도 작업 범위 결정 조건으로 인덱스를 사용한다.

WHERE column IS NULL;

다중 칼럼으로 만들어진 인덱스의 조건들에 대해 알아보면 다음과 같은 인덱스가 있을 때

INDEX ix_test(column_1, column_2, column_3...)

작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
- column_1 칼럼에 대한 조건이 없는 경우
- column_1 칼럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우
작업 범위 결정 조건으로 인덱스를 사용하는 경우(2 < i < n)
- column_1 ~ column_(i - 1) 칼럼까지 동등 비교하는 형태
- column_i 칼럼에 대해 다음 연산자 중 하나로 비교
  - 동등 비교(=, IN)
  - 크다 작다 형태
  - LIKE 좌측 일치 패턴

8.4 R-Tree 인덱스

MySQL의 공간인덱스라 불리는 R-Tree 인덱스 알고리즘은 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스다.

MySQL의 공간 확장에는 다음과 같은 크게 세 가지 기능이 포함돼있다.

공간 데이터를 저장할 수 있는 데이터 타입
공간 데이터의 검색을 위한 공간 인덱스(R-Tree 알고리즘)
공간 데이터의 연산 함수(거리 또는 포함 관계의 처리)

해당 장에서는 R-Tree 알고리즘에 대해 서술하고 12.2절 공간 검색에서 더 자세히 다룬다.

Mysql은 공간 정보의 저장 및 검색을 위해 여러 가지 기하학적 도형 정보를 관리할 수 있는 데이터 타입을 제공한다.

마지막의 GEOMETRY 타입은 나머지 3개의 타입의 슈퍼 타입으로 나머지 객체를 모두 저장할 수 있다.

MBR(Minimum Bounding Rectagle): 해당 도형을 감싸는 최소 크기의 사각형을 의미한다. 이 사각형들의 포함 관계를 B-Tree 형태로 구현한 인덱스가 R-Tree 인덱스다.

다음과 같은 도형들이 있을 때

이 도형들의 MBR을 3개의 레벨로 나눠서 그리면 다음과 같다.

최상위 레벨: R1, R2
촤상위 레벨: R3, R4, R5, R6
최하위 레벨: R7 ~ R14

최하위 레벨은 각 도형 데이터의 MBR, 차상위는 중간 크기의 MBR이며 최상위 MBR은 R-Tree의 루트 노드, 차상위는 브랜치 노드, 최하위는 각 도형을 의미한다.

일반적으로는 WGS84(GPS) 기준의 위도, 경도 좌표 저장에 주로 사용되며 CAD/CAM 소프트웨어 또는 회로 디자인 등 좌표 시스템에 기반을 둔 정보에 대해 모두 적용할 수 있다.

R-Tree는 각 도형의 포함 관계를 이용해 만들어진 인덱스로 포함 관계를 비교하는 ㅎ마수로 검색을 수행하는 경우에만 인덱스를 이용할 수 있다.

현재 버전의 ST_Distance(), ST_Distance_Sphere() 함수는 공간 인덱스를 효율적으로 사용하지 못하기 때문에 공간 인덱스를 사용할 수 있는 ST_Contains() 또는 ST_Within()을 이용해 거리 기반의 검색을 해야한다.

가운데 위치한 P를 기준점으로 반경 거리 5km 이내의 점들을 검색하려면 사각 점선의 상자에 포함되는(ST_Contains 또는 ST_Within()함수 이용) 점들을 검색하면 된다. P6 결과를 포함해도 무방하다면 ST_Contains()나 ST_Within() 비교만 수행하는 것이 좋다.

SELECT * FROM tb_location
WHERE ST_Contains(사각 상자, px);

SELECT * FROM tb_location
WHERE ST_Within(px, 사각 상자);

두 함수는 거의 동일한 비교를 수행하지만 반대로 사용되어야 하는데 ST_Contains()는 첫 번째 파라미터로 포함 경계를 가진 도형을 명시하고 두 번째 파라미터로 포함되는 도형(또는 점 좌표)를 명시해야 한다. ST_Within() 함수는 첫 번째 파라미터로 포함되는 도형(또는 점 좌표)을 명시하고 두 번째 파라미터로 포함 경계를 가진 도형을 명시해야 한다.

P6를 반드시 제거하려면 ST_Distance_Sphere()를 사용해서 한번 더 필터링 해야한다.

SELECT * FROM tb_location
WHERE ST_Contains(사각상자, px) -- 공간 좌표 Px가 사각 상자에 포함되는지 비교
      AND ST_Distance_Sphere(p, px) <= 5 * 1000; /* 5km */

8.5 전문 검색 인덱스

지금까지의 알고리즘은 일반적으로 크지 않은 데이터 또는 이미 키워드화한 작은 값에 대한 인덱싱 알고리즘이었다.

MySQL의 B-Tree 인덱스는 실제 컬럼의 값이 1MB더라도 1MB 전체의 값을 인덱스로 사용하는 것이 아니라 1000바이트 또는 3072바이트까지만 잘라서 인덱스 키로 사용한다. 또한, 전체일치 또는 좌측 일치와 같은 경우만 검색이 가능하다.

문서 내용 전체의 인덱싱해 특정 키워드가 포함된 문서를 검색하는 전문 검색에는 B-Tree 인덱스를 사용할 수 없다.

8.5.1 인덱스 알고리즘

전문 검색에서는 문서 본문의 내용에서 사용자가 검색하게 될 키워드를 분석해 내고 빠른 검색용으로 사용할 수 있게 키워드로 인덱스를 구축한다.

전문 검색 인덱스는 문서의 키워드를 인덱싱하는 기법에 따라 단어의 어근 분석과 n-gram 분석 알고리즘으로 구분할 수 있다.

8.0버전부터는 구분자 방식은 어근 분석과 n-gram 알고리즘에 포함됐다.

어근 분석 알고리즘

MySQL 서버의 전문 검색 인덱스는 두 가지 과정을 거쳐 색인 작업이 수행된다.

불용어(Stop Word) 처리
어근 분석(Stemming)

불용어 처리는 검색에서 가치가 없는 단어를 모두 필터링해서 제거하는 작업을 의미한다. 불용어는 개수가 많지 않기 때문에 알고리즘을 구현한 코드에서 상수로 정의해 사용하거나, 불용어를 데이터베이스화해서 사용자가 추가하거나 삭제할 수 있게 구현하는 경우도 있고 이미 정의된 불용어에 사용자가 별도로 정의할 수 있는 기능도 제공한다.

영어는 MongoDB에서 사용되는 Snowball, 한글은 MeCab을 이용해 분석이 가능하다. 하지만 MeCab을 이용한 형태소 분석은 많은 시간과 노력이 필요하기 때문에 범용적으로 적용하기 위해 n-gram 알고리즘이 도입됐다. 형태소 분석이 문장을 이해하는 알고리즘이라면, n-gram은 단순히 키워드를 검색해내기 위한 인덱싱 알고리즘이다.

n-gram은 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법으로 언어별 이해와 준비 작업이 필요 없는 반면, 만들어진 인덱스가 상당히 크다. 일반적으로 2글자 단위로 키워드를 쪼개서 2-gram 방식으로 많이 사용한다.

To be or not to be. That is the question

각 단어는 띄어쓰기와 마침표를 기준으로 10개의 단어로 구분되고 각 글자가 중첩해서 토큰으로 구분된다.

기본적으로 MySQl 서버에 내장된 불용어는 information_schema.innodb_ft_default_stopword 테이블에서 확인할 수 있는데 불용어와 동일하거나 포함하는 경우 걸러서 버린다.

전문 검색 인덱스의 불용어 처리 무시

불용어 처리를 무시하거나 사용자가 직접 불용어를 등록하는 방법이 권장된다.

불용어 처리를 무시하는 방식은 두 가지가 있는데

스토리지엔진에 상관없이 MySQL 서버의 모든 전문 검색 인덱스에 대해 불용어를 완전히 제거하는 것
- my.cnf 파일의 ft_stopword_file 시스템 변수에 빈 문자열을 설정하면 된다.
- 해당 변수에 사용자가 직접 정의한 불용어 목록 파일 경로를 적어 사용 가능하다.
InnoDB 스토리지 엔진의 경우 innodb_ft_enable_stopword 시스템 변수를 OFF로 설정하면 된다.

사용자 정의 불용어 사용

사용자 정의 불용어를 사용하는 방법은 두 가지로

위의 my.cnf의 ft_stopword_file 설정에 등록하는 방법
InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 엔진에서만 사용할 수 있는 불용어의 목록을 테이블로 저장하는 방식
- innodb_ft_server_stopword_table 시스템 변수에 불용어 테이블을 설정하면 된다.
- 불용어 목록을 변경한 이후 전문 검색 인덱스가 생성돼야만 변경된 불용어가 적용된다.
- 여러 전문 검색 인덱스가 서로 다른 불용어를 사용해야 한다면 innodb_ft_user_stopword_table 변수를 이용하면 된다.

CREATE TABLE my_stopword(value VARCHAR(30)) ENGINE = INNODB;
INSERT INTO my_stopword(value) VALUES('MySQL');

SET GLOBALE innodb_ft_server_stopword_table='mydb/my_stopword';
ALTER TABLE tb_bi_gram
        ADD FULLTEXT INDEX fx_title_body(title, body) WITH PARSER ngram;

전문 검색 인덱스의 가용성

전문 검색 인덱스를 사용하려면 두 가지 조건을 갖춰야 한다.

쿼리 문장이 전문 검색을 위한 문법(MATCH ... AGAINST ..) 사용
테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스 보유

쿼리는 다음과 같다.

SELECT * FROM tb_text
WHERE MATCH(doc_body) AGAINST('애플' IN BOOLEAN MODE);

8.6 함수 기반 인덱스

일반적인 인덱스는 칼럼의 값 일부 또는 전체에 대해서만 인덱스 생성이 허용되지만 컬럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때가 있을 때 8.0부터 지원되는 함수 기반의 인덱스를 활용하면 된다. 함수 기반 인덱스 구현 방법은 두 가지가 있다.

가상 칼럼을 이용한 인덱스
함수를 이용한 인덱스

함수 기반 인덱스는 인덱싱할 값을 계산하는 과정만 빼면 구조는 B-Tree와 같다.

가상 칼럼을 이용한 인덱스

가상 칼럼은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다. 만약 first_name과 last_name을 합쳐서 full_name 인덱스를 만들어야 하는 경우 8.0부터 가상 칼럼을 추가하고 가상 칼럼에 인덱스를 생성할 수 있게 됐다.

ALTER TABLE user
  ADD full_name VARCHAR(30) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
  ADD INDEX ix_fullname(full_name);

이제 full_name에 대한 검색도 인덱스를 이용해 실행 계획이 만들어진다.

함수를 이용한 인덱스

8.0 버전부터는 테이블의 구조를 변경하지 않고 함수를 직접 사용하는 인덱스를 생성할 수 있게 됐다.

CREATE TABLE user (
  user_id BIGINT,
  first_name VARCHAR(10),
  last_name VARCHAR(10),
  PRIMARY KEY (user_id),
  INDEX ix_fullname((CONCAT(first_name, ' ', last_name)))
);

함수 기반 인덱스를 사용하려면 명시된 표현식과 쿼리의 WHERE 조건절에 사용된 표현식이 같아야만 한다.

8.7 멀티 밸류 인덱스

전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키를 가지는데 멀티 밸류 인덱스는 하나의 데이터에 여러 개의 키 값을 가질 수 있는 인덱스다.

다중 값 인덱스는 CREATE TABLE, ALTER TABLE, CREATE INDEX 문에서 생성할 수 있으며 정의에 CAST(...AS...ARRAY)를 사용해야 한다.

CREATE TABLE user (
  user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(10),
  last_name VARCHAR(10),
  credit_info JSON,
  INDEX mx_creditscores( (CAST(credit_info -> '$.credit_scores' AS UNSIGNED ARRAY)))
);

INSERT INTO user VALUES (1, 'Matt', 'Lee', '{"credit_scores": [360, 353, 351]}');

멀티 밸류 인덱스의 사용을 위해서는 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 이용한 실행 계획을 수립한다.

MEMBER OF()
JSON_CONTAINS()
JSON_OVERLAPS()

8.8 클러스터링 인덱스

MySQL에서 클러스터링 인덱스는 InnoDB 스토리지 엔진에서만 지원하며 나머지 엔진에서는 지원되지 않는다.

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용된다. 클러스터링 인덱스는 인덱스 알고리즘보다도 테이블 레코드의 저장 방식이라고 볼 수 있다.

클러스터링 인덱스는 키 값이 변경되면 데이터의 위치도 같이 변경된다.

만약 프라이머리 키가 없는 경우 InnoDB 스토리지 엔진이 다음 우선순위대로 PK를 대체할 칼럼을 선택한다.

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

나머지 부분은 쿼리 시점에 아무런 이득을 가져다줄 수 없으므로 가능하다면 PK를 명시적으로 생성하는것이 낫다.

8.8.2 세컨더리 인덱스에 미치는 영향

MyISAM이나 MEMORY 테이블은 클러스터링 되지 않기 때문에 INSERT 될 때 처음 저장된 공간에서 이동하지 않는다. 데이터 레코드가 저장된 주소는 내부적인 레코드 아이디(ROWID) 역할을 한다. 따라서, 프라이머리키나 세컨더리 인덱스가 구조적으로 차이가 없다.

InnoDB 인덱스에서 세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있다면 클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경되고 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야 한다. 이런 오버헤드 제거를 위해 InnoDB 테이블의 모든 세컨더리 인덱스는 레코드의 주소가 아닌 프라이머리 키 값을 저장하도록 구현돼있다.

CREATE TABLE employees (
  emp_no INT NOT NULL,
  first_name VARCHAR(20) NOT NULL,
  PRIMARY KEY(emp_no),
  INDEX ix_firstname(first_name)
);

SELECT * FROM employees WHERE first_name='Aamer';

MyISAM: ix_firstname인덱스를 검색해서 레코드의 주소를 확인한 후 레코드의 주소로 레코드를 가져옴
InnoDB: ix_firstname인덱스를 검색해서 레코드의 PK를 확인한 후 PK 인덱스를 검색해 최종 레코드를 가져옴

8.8.3 클러스터링 인덱스의 장점과 단점

장점
- 프라이머리 키로 검색할 때 처리 성능이 매우 빠름(범위 검색 유리)
- 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음
단점
- 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스링 키 값의 크기가 클 수록 인덱스의 크기가 커짐
- 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림
- INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림
- 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느림

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

클러스터링 인덱스 키의 크기

클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키값을 포함한다. 그래서 프라이머리 키가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다. 예를 들어 PK 크기가 10바이트일 때 세컨더리 인덱스가 5개라면 100만 건 레코드 저장시 인덱스 크기는 50 * 1,000,000 = 47MB지만 50바이트라면 250 * 1,000,000 = 238MB로 크기 차이가 상당히 나게 된다.

프라이머리 키는 AUTO_INCREMENT보다는 업무적인 칼럼으로 생성

칼럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 프라이머리 키로 설정하는 것이 좋다.

프라이머리 키는 반드시 명시할 것

PK를 명시적으로 정의하지 않으면 InnoDB 스토리지 엔진이 내부적으로 일련번호 칼럼을 추가하고 해당 칼럼은 사용자가 접근할 수 없다.

AUTO_INCREMENT 칼럼을 인조 식별자로 사용할 경우

여러 개의 칼럼이 복합으로 프라이머리 키가 만들어지는 경우 프라이머리 키의 크기가 길어질 때가 가끔 있다. 하지만 프라이머리 키의 크기가 길어도 세컨더리 인덱스가 필요하지 않다면 그대로 프라이머리 키를 사용하는 것이 좋다.

세컨더리 인덱스도 필요하고 프라이머리 키의 크기도 길다면 AUTO_INCREMENT칼럼을 프라이머리 키로 설정하면 된다. 이를 인조 식별자라고 하는데 로그테이블 같이 조회보다 INSERT 위주의 테이블을 AUTO_INCREMENT로 프라이머리 키를 설정하는 것이 성능 향상에 도움이 된다.

8.9 유니크 인덱스

유니크는 인덱스보다는 제약 조건에 가까운 것으로 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미한다. MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.

유니크 인덱스와 일반 세컨더리 인덱스의 비교

인덱스 읽기

유니크 인덱스와 유니크하지 않은 세컨더리 인덱스의 읽기 속도는 큰 차이가 없다.

인덱스 쓰기

MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금, 쓸 때는 쓰기 잠금을 이용하는데 이 과정에서 데드락이 빈번히 발생한다. 또한, InnoDB 스토리지 엔진에는 인덱스 키의 저장을 버퍼링하기 위해 체인지 버퍼가 사용된다. 그래서 인덱스의 저장이나 변경이 빨리 처리되지만 유니크 인덱스는 중복 체크를 위해 버퍼링하지 못한다. 따라서, 일반 세컨더리 인덱스가 조금 더 빠르다.

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

꼭 필요한 경우 유니크 인덱스를 사용하는 것이 당연하지만 성능을 생각하며 생성하는것은 좋지 않다.

유일성이 꼭 보장돼야 하는 칼럼에는 유니크 인덱스를 생성하지만 꼭 필요하지 않다면 유니크 인덱스보다는 일반 세컨더리 인덱스를 생성하는 것을 고려해보자.

외래키

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

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

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

CREATE TABLE tb_parent (
  id INT NOT NULL,
  fd VARCHAR(100) NOT NULL, PRIMARY KEY(id)
) ENGINE=InnoDB

CREATE TABLE tb_child (
  id INT NOT NULL,
  pid INT DEFAULT NULL, -- //parent.id 칼럼 참조
  fd VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY(id),
  KEY ix_parentid(pid),
  CONSTRAINT child_ibfk_1 FOREIGN KEY (pid) REFERENCES tb_parent (id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO tb_parent VALUES(1, 'parent-1'), (2, 'parent-2');
INSERT INTO tb_child VALUES(100, 1, 'child-100');

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

작업 번호	커넥션-1	커넥션-2
1	BEGIN;
2	UPDATE tb_parent SET fd='changed-2' WHERE id=2;
3		BEGIN;
4		UPDATE tb_child SET pid=2 WHERE id=100;
5	ROLLBACK;
6		Query OK, 1 row affected

자식 테이블의 외래 키 칼럼의 변경은 부모 테이블의 확인이 필요한데 부모 테이블의 쓰기 잠금이 걸려 있으면 쓰기 잠금의 해제를 기다린다.

작업 번호	커넥션-1	커넥션-2
1	BEGIN;
2	UPDATE tb_child SET fd='changed-100' WHERE id=100;
3		BEGIN;
4		DELETE FROM tb_parent WHERE id=1;
5	ROLLBACK;
6		Query OK, 1 row affected

자식 테이블이 생성될 때 정의된 외래키의 특성(ON DELETE CASCADE) 때문에 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되는 식으로 작동한다. 외래키의 물리적 생성은 체크 작업뿐만이 문제가 아니라 연관 테이블에 읽기 잠금을 겅어야 된다는 것이고 잠금이 다른 테이블로 확장되면 전체 쿼리의 동시 처리에 영향을 미친다.

Previous5장 Next9장

Last updated 1 year ago