08. 인덱스-2

8.4 R-Tree 인덱스

공간 인덱스(Spatial Index)

공간 인덱스는 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스

기본적인 내부 메커니즘은 B-Tree와 흠사하다.

B-Tree는 인덱스를 구성하는 컬럼의 값이 1차원의 스칼라 값인 반면, R-Tree 인덱스는 2차원의 공간 개념 값이다.

 

MySQL의 공간 확장(Spatial Extension)을 이용하면 위치 기반 서비스를 간단하게 구현할 수 있다.

 

MySQL의 공간 확장의 기능

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

-> 12.2절 '공간 검색'

구조 및 특성

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

GEOMETRY 데이터 타입

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

 

MBR(Minimum Bounding Renctangle)

도형을 감싸는 최소 크기의 사각형

이 사각형(MBR)들의 포함 관계를 B-Tree 형태로 구현한 인덱스가 R-Tree 인덱스이다.

 

최소 경계 상자(MBR, Minimum Bounding Renctangle)

 

R-Tree의 구조

공간(Spatial) 데이터

 

여기에는 표시되지 않았지만 단순히 X좌표와 Y좌표만 있는 포인트 데이터 또한 하나의 도형 객체가 될 수 있다. 

이 도형들의 MBR을 3개 레벨로 나눠서 그린다.

 

공간 데이터의 MBR

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

최하위 레벨의 MBR(각 도형을 제일 안쪽에서 둘러싼 점선 상자)은 각 도형 데이터의 MBR을 의미한다.

차상위 레벨 MBR은 중간 크기의 MBR(도형 객체의 그룹)이다.

 

최상위 MBR은 R-Tree의 루트 노드에 저장되는 정보이며, 차상위 그룹 MBR은 R-Tree의 브랜치 노드, 각 도형의 객체는 리프 노드에 저장된다.

 

공간(R-Tree, Spatial) 인덱스 구조

R-Tree 인덱스의 용도

R(Rectangle)-Tree

R-Tree는 MBR 정보를 이용해 B-Tree 형태로 인덱스를 구축한다. 공간 인덱스라고도 한다.

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

 

R-Tree는 각 도형의 MBR의 포함 관계를 이용해 만들어진 인덱스이다. 따라서 ST_Contains() 또는 ST_Whthin() 등과 같은 포함 관계를 비교하는 함수로 검색을 수행하는 경우에만 인덱스를 이용할 수 있다. 

대표적으로 '현재 사용자의 위치로부터 반경 5km 이내의 음식점 검색' 등과 같은 검색에 사용할 수 있다.

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

 

특정 지점을 기준으로 사각 박스 내의 위치를 검색

 

위 그림에서 가운데 위치한 'P'가 기준점이라고 할 때, 기준점으로부터 반경 거리 5km 이내의 점(위치)들을 검색하기 위해 우선 사각 점선의 상자에 포함되는 점들을 검색한다.

ST_Contains() , ST_Whthin() 연산은 사각형 박스와 같은 다각형(Polygon)으로만 연산할 수 있으므로 반경 5km를 그리는 원을 포함하는 최소 사각형(MBR)으로 포함 관계 비교를 수행한다.

점 'P6'은 기준점 P로부터 반경 5km 이상 떨어져 있지만 최소 사각형 내에는 포함된다. P6을 빼고 결과를 조회하려면 좀 더 복잡한 비교가 필요하다. P6를 결과에 포함해도 무방하다면 쿼리에서 ST_Contains() , ST_Whthin() 비교만 수행해도 된다.

 

 

ST_Contains() 함수와 ST_Whthin() 함수는 거의 동일한 비교를 수행하지만 파라미터의 순서가 반대이다.

• ST_Contains( 포함 경계 도형, 포함되는 도형(또는 점 좌표) )
• ST_Whthin( 포함되는 도형(또는 점 좌표), 포함 경계 도형 )

 

P6를 반드시 제거해야 한다면 다음과 같이 ST_Contains() 비교 결과에 대해 ST_Distance_Sphere() 함수로 한번 더 필터링 해야 한다.

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

지금까지의 인덱스 알고리즘은 일반적으로 크지 않은 데이터 또는 이미 키워드한 작은 값에 대한 인덱싱 알고리즘이었다. 대표적으로 MySQL의 B-Tree 인덱스는 실제 컬럼의 값이 1MB이더라도 1MB 전체의 값을 인덱스 키로 사용하는 것이 아니라 1,000Byte(MyISAM) 또는 3072Byte(InnoDB)까지만 잘라서 인덱스 키로 사용한다. 또한 전체 일치 또는 좌측 일부 일치와 같은 검색만 가능하다.

 

문서의 내용 전체를 인덱스화하여 특정 키워드가 포함된 문서를 검색하는 전문(Full Text) 검색에는 InnoDB나 MyISAM 스토리지 엔진에서 제공하는 일반적인 용도의 B-Tree 인덱스를 사용할 수 없다. 

 

전문 검색 인덱스: 문서 전체에 대한 분석과 검색을 위한 인덱싱 알고리즘

인덱스 알고리즘

전문 검색에서는 문서 본문의 내용에서 사용자가 검색하게 될 키워드를 분석하고, 빠른 검색을 위해 이러한 키워드로 인덱스를 구축한다. 전문 검색 인덱스는 문서의 키워드를 인덱싱하는 기법에 따라 크게 단어의 어근 분석 알고리즘과 n-gram 분석 알고리즘으로 구분할 수 있다. 

 

예전에는 구분자(공백이나 일부 문장 기호를 기준으로 토큰을 분리)도 하나의 인덱싱 알고리즘처럼 생각됐지만 8.0 버전부터는 구분자 방식은 이미 어미 분석과 n-gram 알고리즘에 함께 포함되어 별도로 언급하지 않겠다.

어근 분석 알고리즘

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

1. 불용어(Stop Word) 처리: 검색에서 큰 의미가 없는 단어 제거(필터링) 작업
   • 불용어의 개수는 많지 않기 때문에 알고리즘을 구현한 코드에 상수로 정의해서 사용하거나, 불용어 자체를 사용자가 추가하거나 삭제할 수 있게 데이터베이스화 하는 경우도 있다. 
   • 현재 MySQL 서버는 불용어를 소스코드에 정의해뒀지만, 이를 무시하고 사용자가 별도로 불용어를 정의할 수 있는 기능을 제공한다.
2. 어근 분석(Stemming): 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업
   • MySQL 서버에서는 오픈소스 형태소 분석 라이브러리인 MeCab을 플러그인 형태로 사용할 수 있게 지원한다. 한글이나 일본어의 경우, 영어와 같이 단어의 변형 자체는 거의 없기 때문에 어근 분석보다는 문장의 형태소를 분석해서 명사와 조사를 구분하는 기능이 더 중요한 편이다.
   • 각 국가의 언어가 서로 문법이 다르고 다른 방식으로 발전해왔기 때문에 형태소 분석, 어근 분석 모두 언어별로 방식이 모두 다르다.  
   • MeCab이 제대로 작동하려면 단어 사전과 문장을 해체해서 각 단어의 품사를 식별할 수 있는 문장 구조 인식이 필요하다. 문장 구조 인식을 위해 실제 언어의 샘플을 이용해 언어를 학습하는 과정이 필요한데, 이 과정은 상당한 시간이 필요한 작업이다.
   • ex. MeCab(MySQL-일본어, 한글), Snowball(MongoDB-서구권 언어)

 

MeCab을 위한 형태소 분석은 매우 전문적인 전문 검색 알고리즘이어서 만족할 만한 결과를 내기 위해서는 많은 노력과 시간을 필요로 한다. 전문적인 검색 엔진을 고려하는 것이 아니라면 범용적으로 적용하기는 쉽지 않다.

-> n-gram 알고리즘

n-gram 알고리즘

형태소 분석이 문장을 이해하는 알고리즘이라면, n-gram은 단순히 키워드를 검색하기 위한 인덱싱 알고리즘이라고 할 수 있다. n-gram 이란 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법이다. 형태소 분석보다는 알고리즘이 단순하고 국가별 언어에 대한 이해와 준비 작업이 필요 없는 반면, 만들어진 인덱스 크기가 상당히 큰 편이다. n-gram에서 n은 인덱싱할 키워드의 최소 글자 수를 의미하는데, 일반적으로는 2글자 단위로 키워드를 쪼개서 인뎅싱하는 2-gram(Bi-gram) 방식이 많이 사용된다.

 

2-gram 알고리즘으로 문장의 토큰을 분리하는 예제

To be or not to be. That is the question

 

각 단어는 띄어쓰기(공백)과 마침표(.)를 기준으로 10개의 단어로 구분되고, 2글자씩 중첩해서 토큰으로 분리된다. 

주의해야 할 것은 각 글자가 중첩해서 2글자씩 토큰으로 구분된다는 것이다. 그래서 10글자 단어라면 2-gram 알고리즘에서는 (10-1)개의 토큰으로 구분된다. 이렇게 구분된 각 토큰을 인덱스에 저장한다. 이때 중복된 토큰은 하나의 인덱스 엔트리로 병합되어 저장된다.

 

 

MySQL 서버는 이렇게 생성된 토큰들에 대해 불용어를 걸러내는 작업을 수행하는데, 이때 불용어와 동일하거나 불용어를 포함하는 경우 걸러서 버린다.

기본적으로 MySQL 서버에 내장된 불용어는 information_schema.innodb_ft_default_stopword 테이블을 통해 확인할 수 있다.

 

innodb_ft_default_stopword 테이블에 등록된 불용어 목록

 

MySQL 서버에서 실제 저장되는 인덱스 엔트리는 다음과 같다. 결과적으로 다음 표의 "출력(최종 인덱스 등록)" 컬럼에 표시된 것들만 전문 검색 인덱스에 등록하는 것이다.

 

 

물론 전문 검색을 더 빠르게 하기 위해 2단계 인덱싱(프런트엔드와 백엔드 인덱스)과 같은 방법도 있지만 MySQL 서버는 이렇게 구분된 토큰을 단순한 B-Tree 인덱스에 저장한다. 물론 성능 향상을 위한 Merge-Tree 같은 기능을 가지고 있긴 하지만 MySQL에서 구현하는 n-gram 알고리즘의 핵심 내용은 이정도로 이해하면 된다.

불용어 변경 및 삭제

앞서 살펴본 n-gram의 토큰 파싱 및 불용어 처리 예시에서 결과를 보면 "ti"와 "at", "ha" 같은 토큰들은 "a"와 "i" 철자가 불용어로 등록돼 있기 때문에 모두 걸러져서 버려졌다. 이런 불용어 처리는 사용자에게 도움이 되기 보다 혼란을 야기하는 기능일 수 있다. 그래서 불용어 처리 자체를 완전히 무시하거나 MySQL 서버에 내장된 불용어 대신 사용자가 직접 불용어를 등록하는 방법을 권장한다.

 

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

방법은 아래 두가지

 

1. 스토리지 엔진에 관계없이 MySQL 서버의 모든 전문 검색 인덱스에 대해 불용어를 완전히 제거하기

• MySQL 서버의 설정 파일(my.cnf)의 ft_stopword_file 시스템 변수에 빈 문자열을 설정한다.
• ft_stopword_file 시스템 변수는 MySQL 서버가 시작될 때만 인지하기 때문에 서버를 재시작해야 변경사항이 반영된다.
• ft_stopword_file: 사용자가 직접 정의한 불용어 목록 파일의 경로를 설정하면 해당 파일을 가져와 적용한다.

ft_stopword_file=''

 

2. InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 인덱스에 대해서만 불용어 처리 무시하기

• innodb_ft_enable_stopword 시스템 변수를 OFF로 설정한다.
• 설정을 변경해도 InnoDB 가 아닌 다른 스토리지 엔진을 사용하는 테이블은 여전히 내장 불용어 처리를 사용한다.
• innodb_ft_enable_stopword 시스템 변수는 동적 변수이므로 서버가 실행 중에도 반영된다.

mysql> SET GLOBAL innodb_ft_enable_stopword=OFF;

 

사용자 정의 불용어 사용

MySQL 서버 내장 불용어를 사용하지 않고, 응용 프로그램 특성에 맞게 사용자가 직접 정의한 불용어를 사용할 수 있다.

방법은 아래 두가지

 

1. 불용어 목록을 파일로 저장하고, MySQL 서버 설정파일에서 파일 경로를 ft_stopword_file 설정에 등록하기

ft_stopword_file='/data/my_custom_stopword.txt'

 

2. InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 엔진의 경우, 불용어 목록을 테이블로 저장하기

• 불용어 테이블을 생성하고, innodb_ft_server_stopword_table 시스템 변수에 설정한다.
• 불용어 목록을 변경한 이후에 전문 검색 인덱스가 생성되어야만 변경된 불용어가 적용된다.
• 여러 전문 인덱스가 서로 다른 불용어를 사용해야 하는 경우라면 innodb_ft_user_stopword_table 시스템 변수를 사용하자. 사용 방법은 동일하다.

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

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

전문 검색 인덱스의 가용성

전문 검색 인덱스 사용을 위한 조건

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

예)

테이블 생성 및 doc_body 컬럼에 전문 검색 인덱스(fx_docbody) 생성

 

다음과 같은 검색 쿼리로도 원하는 검색 결과를 얻을 수 있다. 하지만 전문 검색 인덱스를 이용하여 효율적으로 쿼리가 실행된 것이 아니라 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔으로 쿼리를 처리한다.

mysql> SELECT * FROM tb_test WHERE doc_body LIKE '%애플%';

 

전문 검색 인덱스를 사용하려면 반드시 MATCH (...) AGAINST (...) 구문으로 검색 쿼리를 작성해야 하며, 전문 검색 인덱스를 구성하는 컬럼들은 MATCH 절의 괄호 안에 모두 명시되어야 한다.

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

8.6 함수 기반 인덱스

일반적인 인덱스는 컬럼의 값 일부(컬럼의 값 앞부분) 또는 전체에 대해서만 인덱스 생성이 허용된다. 하지만 때로는 컬럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때도 있는데, 이 경우 함수 기반의 인덱스를 활용하면 된다.

MySQL 서버는 8.0 버전부터 함수 기반 인덱스를 지원하기 시작했는데, MySQL 서버에서 함수 기반 인덱스를 구현하는 방법은 다음 두 가지로 구분할 수 있다.

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

인덱싱할 값을 계싼하는 과정의 차이만 있을 뿐, 실제 인덱스의 내부적인 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.

가상 컬럼을 사용한 인덱스

 

요구사항 추가 - first_name과 last_name을 합쳐서 검색 가능

• MySQL 8.0 이전: full_name이라는 컬럼을 추가하고 모든 레코드에 대해 full_name을 업데이트, 그리고 full_name 컬럼에 대한 인덱스 생성
• MySQL 8.0: 가상 컬럼을 추가하고 그 가상 컬럼에 대한 인덱스 생성 가능 

기존 테이블에 가상 컬럼 추가

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

• 옵션은 VIRTUAL 와 STORED 중에서 선택
• CONCAT() 함수를 사용하여 "(first_name) (last_name)" 이라는 컬럼 값 생성
• 가상 컬럼 full_name 에 대한 인덱스 생성

 

가상 컬럼 full_name에 대한 검색도 새로 만들어진 ix_fullname 인덱스를 이용해 실행 계획이 만들어진다.

 

가상 컬럼이 VIRTUAL, STORED 중 어떤 옵션으로 생성됐든 해당 가상 컬럼에 인덱스를 생성할 수 있다. 가상 컬럼은 테이블에 새로운 컬럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.

VIRTUAL 과 STORED 옵션의 차이 -> 15.8절 '가상 컬럼(파생 컬럼)'

함수를 이용한 인덱스

가상 컬럼은 MySQL 5.7 버전에서 사용할 수 있었지만 함수를 직접 인덱스 생성 구문에 사용할 수는 없었다. 8.0 버전부터는 다음과 같이 테이블의 구조를 변경하지 않고 함수를 직접 사용하는 인덱스를 생성할 수 있게 됐다.

 

함수를 직접 사용하는 인덱스 생성

• 테이블 구조는 변경하지 않고 계산된 결괏값의 검색을 빠르게 만들어 준다.
• 함수 기반 인덱스를 제대로 활용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용돼야 한다.
• 함수 생성 시 명시된 표현식과 쿼리의 WHERE 조건절에 사용된 표현식이 다르다면 (결과가 같다고 해도) 옵티마이저는 다른 표현식으로 간주해서 함수 기반 인덱스를 사용하지 못한다.

 

 

만약 이 예제를 실행했을 때 옵티마이저가 표시하는 실행 계획이 "ix_fullname" 인덱스를 사용하지 않는다고 표시된다면 CONCAT 함수에 사용된 공백 문자 리터럴 때문일 가능성이 높다. 이 경우 다음 3개 시스템 변수의 값을 동일 콜레이션(ex.utf8mb4_0900_ai_ci) 로 일치시킨 후, 다시 테스트를 수행해보자.

• collation_connection
• collation_database
• collation_server

참고: 가상 컬럼과 함수를 직접 이용하는 인덱스를 구분했는데, 실제로 이 두 방법은 사용법과 SQL 문장의 문법에서 조금 차이가 있다. 하지만 내부적으로는 동일한 구현 방법을 사용한다. 이는 어떤 방법을 사용하더라도 둘의 성능 차이는 발생하지 않는다는 것을 의미한다.

8.7 멀티 밸류 인덱스 (Multi-Value Index)

전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키 값을 가진다. 즉, 인덱스 키와 데이터 레코드는 1:1 관계를 가진다.

 

멀티 밸류 인덱스

• 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스
• 일반적인 RDBMS를 기준으로 생각하면 정규화에 위배되는 형태지만, 최근 RDBMS들이 JSON 데이터 타입을 지원하기 시작하면서 JSON의 배열 타입 필드에 저장된 원소(Element)들에 대한 인덱스 요건이 발생했다.

JSON 포맷으로 데이터를 저장하는 MongoDB는 처음부터 이런 형태의 인덱스를 지원하고 있었지만 MySQL 서버는 멀티 밸류 인덱스에 대한 지원 없이 JSON 타입의 컬럼만 지원했다. 배열 형태에 대한 인덱스 생성이 되지 않아서 MongoDB의 기능과 많이 비교되곤 했다. 하지만 MySQL 8.0 부터는 JSON 관리 기능이 많이 개선되었다.

 

예) 신용 정보 점수를 배열로 JSON 타입 컬럼(credit_info)에 저장하는 테이블

 

멀티 밸류 인덱스를 활용하기 위해서는 일반적인 조건 방식을 사용하면 안 되고, 반드시 다음 함수를 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행 계획을 수립한다.

• MEMBER OF()
• JSON_CONTAINS()
• JSON_OVERLAPS()

 

신용 점수 검색

MEMBER OF()

 

참고: MySQL 서버의 Workinglog에는 DECIMAL, INTEGER, VARCHAR/CHAR 타입에 대해 멀티 밸류 인덱스를 지원한다고 명시했지만 MySQL 8.0.21 버전에서는 VARCHAR/CHAR 타입에 대해서는 지원하지 않는다. 하지만 곧 VARCHAR/CHAR 타입의 배열 형태 CAST와 멀티 밸류 인덱스가 지원될 것으로 예상한다.

8.8 클러스터링 인덱스

클러스터링이란 여러 개를 하나로 묶는다는 의미로 주로 사용되는데, MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(프라이머리 키를 기준으로)들끼리 묶어서 저장하는 형태로 구현된다. 이는 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에 착안한 것이다.

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

클러스터링 인덱스

• 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 형태
• 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다.
• 프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다.
• InnoDB처럼 항상 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르고 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다.

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

또한 클러스터링의 기준이 되는 프라이머리 키는 클러스터링 키라고도 표현한다.

 

참고: 일반적으로 B-Tree 인덱스도 인덱스 키 값으로 이미 정렬되어 저장된다. 이 또한 인덱스의 키 값으로 클러스터링 된 것으로 생각할 수 있다. 하지만 일반적인 B-Tree 인덱스를 클러스터링 인덱스라고 부르지 않는다. 테이블의 레코드가 프라이머리 키 값으로 정렬되어 저장된 경우만 "클러스터링 인덱스" 또는 "클러스터링 테이블"이라고 한다.

 

클러스터링 테이블(인덱스) 구조

 

클러스터링 테이블의 구조 자체는 일반 B-Tree와 비슷하다. 하지만 세컨더리 인덱스를 위한 B-Tree의 리프 노드와 달리 클러스터링 인덱스의 리프 노드에는 레코드의 모든 컬럼이 저장되어 있다. 즉, 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리되는 것이다.

 

프라이머리 키 변경 작업 후 클러스터링 테이블의 변화

mysql> UPDATE tb_test SET emp_no=100002 WHERE emp_no=100007;

 

업데이트 문장이 실행된 이후의 데이터 구조

 

emp_no=100007 인 레코드는 3번 페이지에 저장돼 있었으나 emp_no=100002 로 변경되면서 2번 페이지로 이동했다.

실제로 프라이머리 키 값이 변경되는 경우는 거의 없을 것이다. 

 

참고: MyISAM 테이블이나 기타 InnoDB를 제외한 테이블의 데이터 레코드는 프라이머리 키나 인덱스 키 값이 변경된다고 해서 실제 데이터 레코드의 위치가 변경되지는 않는다. 데이터 레코드가 INSERT 될 때 데이터 파일의 끝(또는 임의의 빈 공간)에 저장된다. 이렇게 한번 결정된 위치는 절대 바뀌지 않고, 레코드가 저장된 주소는 MySQL 내부적으로 레코드를 식별하는 아이디로 인식된다. 레코드가 저장된 주소를 ROW-ID 라고 표현하며 일부 DBMS에서는 이 값을 사용자가 직접 조회하거나 쿼리의 조건으로 사용할 수 있다. 하지만 MySQL에서는 사용자에게 노출되지 않는다.

 

클러스터링 적용 컬럼 우선순위

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

1. 프라이머리 키
2. NOT NULL 옵션의 유니크 인덱스(UNIQUE INDEX) 중에서 첫 번째 인덱스
3. 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가

3번에서 추가된 프라이머리 키(일련번호 컬럼)는 사용자에게 노출되지 않으며 쿼리 문장에 명시적으로 사용할 수 없다.

즉, 프라이머리 키나 유니크 인덱스가 전혀 없는 InnoDB 테이블에서는 아무 의미 없는 숫자 값으로 클러스터링되는 것이며, 이는 특별한 혜택이 없을 것이다. InnoDB 테이블에서 클러스터링 인덱스는 테이블당 단 하나만 가질 수 있는 엄청난 혜택이므로 가능하면 프라이머리 키를 명시적으로 생성하자.

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

MyISAM이나 MEMORY 테이블 같은 클러스터링되지 않은 테이블은 INSERT 될 때 처음 저장된 공간에서 절대 이동하지 않는다. 데이터 레코드가 저장된 주소는 내부적인 레코드 아이디(ROWID) 역할을 하며, 프라이머리 키나 세컨더리 인덱스의 각 키는 그 주소(ROWID)를 이용해서 실제 데이터 레코드를 찾아온다. 그래서 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다.

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

 

 

• MyISAM: ix_firstname 인덱스를 검색해서 레코드의 주소를 확인한 후, 레코드의 주소를 이용해 최종 레코드를 가져옴
• InnoDB: ix_firstname 인덱스를 검색해 레코드의 프라이머리 키 값을 확인한 후, 프라이머리 키 인덱스를 검색해서 최종 레코드를 가져옴

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

MyISAM과 같은 클러스터링되지 않은 일반 프라이머리 키와 클러스터링 인덱스를 비교

 

장점

• 프라이머리 키(클러스터링 키)로 검색할 때 처리 성능이 매우 빠름 (특히 프라이머리 키를 범위 검색하는 경우)
• 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음 (-> 커버링 인덱스)

단점

• 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
• 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림
• INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림
• 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느림

요약하면 클러스터링 인덱스의 장점은 빠른 읽기(SELECT), 단점은 느린 쓰기(INSERT, UPDATE, DELETE).

일반적으로 웹 서비스와 같은 온라인 트랜잭션 환경(OLTP, On-Line Transaction Processing)에서는 쓰기와 읽기의 비율이 2:8 또는 1:9 정도이기 때문에 조금 느린 쓰기를 감수하고 읽기를 빠르게 유지하는 것은 매우 중요하다.

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

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

클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키(클러스터링 키) 값을 포함한다. 그래서 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다. 일반적으로 테이블에 세컨더리 인덱스가 4~5개 정도 생성된다는 것을 고려하면 세컨더리 인덱스의 크기는 급격히 증가한다.

 

5개의 세컨더리 인덱스를 가지는 테이블의 프라이머리 키가 10바이트인 경우와 50바이트인 경우

 

인덱스가 커질수록 같은 성능을 내기 위해 그만큼의 메모리가 더 필요해지므로 InnoDB 테이블의 프라이머리 키는 신중하게 선택해야 한다.

프라이머리 키는 AUTO-INCREMENT보다 업무적인 컬럼으로 생성 (가능한 경우)

프라이머리 키는 아무 의미 없는 값보다 업무적으로 중요한 컬럼으로 설정하는 것이 좋다.

클러스터링되지 않는 테이블에서는 사실 프라이머리 키로 뭘 선택해도 성능의 차이는 별로 없을 수 있지만, InnoDB에서는 프라이머리 키로 검색했을 때 얻는 성능상 이점이 매우 크다. 프라이머리 키는 대부분 검색에서 빈번하게 사용되는 것이 일반적이다. 그러므로 설령 그 컬럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있는 컬럼을 프라이머리 키로 설정하는 것이 좋다.

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

가끔 프라이머리 키가 없는 테이블을 자주 보게 되는데, 가능하면 AUTO_INCREMENT 컬럼을 이용해서라도 프라이머리 키는 생성하는 것을 권장한다. InnoDB 테이블에서 프라이머리 키를 정의하지 않으면 InnoDB 스토리지 엔진이 내부적으로 일련번호 컬럼을 추가한다. 하지만 자동으로 추가된 컬럼은 사용자에게 보이지 않기 때문에 사용자가 접근(사용)할 수 없다. 즉, InnoDB 테이블에 프라이머리 키를 지정하지 않는 경우와 AUTO_INCREMENT 컬럼을 생성하고 프라이머리 키로 설정하는 것이 결국 똑같다. 그렇다면 사용자가 사용할 수 있는 값(AUTO_INCREMENT 값)을 프라이머리 키로 설정하는 것이 좋을 것이다. 또한 ROW 기반의 복제나 InnoDB Cluster에서는 모든 테이블이 프라이머리 키를 가져야만 정상적인 복제 성능을 보장하기도 하므로 프라이머리 키는 꼭 생성하자.

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

여러 개 컬럼의 복합으로 프라이머리 키가 만들어지는 경우 프라이머리 키의 크기가 길어질 때가 가끔 있다. 하지만 프라이머리 키의 크기가 길어도 세컨더리 인덱스가 필요치 않다면 그대로 프라이머리 키를 사용하는 것이 좋다. 세컨더리 인덱스도 필요하고 프라이머리 키의 크기도 길다면 AUTO_INCREMENT 컬럼을 추가하고, 이를 프라이머리 키로 설정하면 된다. 잃게 프라이머리 키를 대체하기 위해 인위적으로 추가된 프라이머리 키를 인조 식별자(Surrogate key)라고 한다. 그리고 로그 테이블과 같이 조회보다는 INSERT 위주의 테이블들은 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 된다.

 

정리하면 프라이머리 키는 크기가 작은 업무적 컬럼이 가장 좋고, 크기가 크더라도 세컨더리 인덱스가 필요하지 않다면 괜찮다. 최소한 AUTO_INCREMENT 컬럼으로라도 프라이머리 키를 생성하는 것을 권장한다.

8.9 유니크 인덱스

유니크는 인덱스라기보다 제약 조건에 가깝다. 테이블이나 인덱스에 같은 값이 2개 이상 저장할 수 없음을 의미하며, MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다. 유니크 인덱스에는 NULL도 저장할 수 있는데, NULL은 특정 값이 아니므로 2개 이상 저장할 수 있다.

• MySQL의 프라이머리 키는 기본적으로 NULL을 허용하지 않는 유니크 속성이 자동으로 부여된다. (NOT NULL, UNIQUE)
• MyISAM이나 MEMORY 테이블에서 프라이머리 키는 사실상 NULL이 허용되지 않는 유니크 인덱스와 같다.
• InnoDB 테이블의 프라이머리 키는 클러스터링 키의 역할도 하므로 유니크 인덱스와는 근본적으로 다르다.

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

유니크 인덱스와 유니크하지 않은 일반 세컨더리 인덱스는 구조상 차이점이 없다.

유니크 인덱스와 일반 세컨더리 인덱스의 읽기와 쓰기를 성능 관점에서 살펴보자.

인덱스 읽기

유니크 인덱스가 빠르다고 생각하기 쉽지만 사실이 아니다. 어떤 책에서는 유니크 인덱스는 1건만 읽으면 되지만 유니크하지 않은 세컨더리 인덱스에서는 레코드를 한 건 더 읽어햐 하므로 느리다고 한다. 하지만 유니크하지 않은 세컨더리 인덱스에서 한 번 더 해야 하는 작업은 디스크 읽기가 아니라 CPU에서 컬럼값을 비교하는 작업이기 때문에 성능상 영향이 거의 없다고 볼 수 있다. 유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지, 인덱스 자체의 특성 때문에 느린 것이 아니라는 것이다. 하나의 값을 검색하는 경우, 유니크 인덱스와 일반 세컨더리 인덱스는 사용되는 실행 계획이 다르다. 이는 인덱스의 성격이 유니크한지 아닌지에 따른 차이일 뿐 큰 차이는 없다.

정리하면 1개의 레코드를 읽느냐 2개 이상의 레코드를 읽느냐의 차이만 있을 뿐, 읽어야 할 레코드 건수가 같다면 성능상의 차이는 미미하다.

인덱스 쓰기

유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 필요하다. 그래서 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느리다. 그런데 MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 빈번하게 발생한다. 또한 InnoDB 스토리지 엔진에는 인덱스 키의 저장을 버퍼링하기 위해 체인지 버퍼(Change Buffer)가 사용된다. 그래서 인덱스의 저장이나 변경 작업이 빠르게 처리되지만, 유니크 인덱스는 그 시점에 중복 체크를 해야 하므로 작업 자체를 버퍼링하지 못한다. 이 때문에 유니크 인덱스는 일반 세컨더리 인덱스보다 변경 작업이 더 느리게 작동한다. 

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

1. 성능 향상을 기대하고 불필요하게 유니크 인덱스를 생성하지 않는다.
2. 하나의 테이블에서 같은 컬럼에 유니크 인덱스와 일반 인덱스를 중복해서 생성하지 않는다. MySQL의 유니크 인덱스는 일반 다른 인덱스와 같은 역할을 하므로 중복해서 생성할 필요가 없다. 
3. 유니크 인덱스는 쿼리의 실행 계획이나 테이블의 파티션에 미치는 영향이 있다. -> 10장 '실행 계획', 13장 '파티션'

8.10 외래키 

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

 

외래키 관리의 중요한 특징

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

 

• tb_parent ( id, fd ), PK=id
• tb_child ( id, pid, fd ), PK=id, FK=pid REFERENCES tb_parent.id

 

위와 같은 예제 테이블에서 언제 자식 테이블의 변경이 잠금 대기를 하고, 언제 부모 테이블의 변경이 잠금 대기를 하는지 예제로 살펴보자.

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

 

1. 커넥션-1 트랜잭션 시작
2. 부모 테이블(tb_parent)에서 id가 2인 레코드에 UPDATE 실행 -> tb_parent.id=2 인 레코드에 대해 쓰기 잠금 획득
3. 커넥션-2 트랜잭션 시작
4. 자식 테이블(tb_child)의 외래키 컬럼(부모의 키를 참조하는 컬럼)인 pid를 2로 변경하는 쿼리 실행 -> 잠금 대기
5. 커넥션-1 ROLLBACK/COMMIT으로 트랜잭션 종료
6. 커넥션-2 에서 대기 중이던 작업이 즉시 처리됨

자식 테이블의 외래 키 컬럼의 변경(INSERT, UPDATE)은 부모 테이블의 확인이 필요한데, 이때 변경하고자 하는 부모 테이블의 레코드에 쓰기 잠금이 걸려있으면 해제될 때까지 기다리게 된다.

자식 테이블의 외래키(pid)가 아닌 컬럼(tb_child.td 같은)의 변경은 "외래 키로 인한 잠금 확장"이 발생하지 않는다.

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

 

1. 커넥션-1 트랜잭션 시작
2. tb_parent.id=1 을 참조하는 자식 테이블의 레코드에 UPDATE 실행 -> tb_child.pid=1 인 레코드에 대해 쓰기 잠금 획득
3. 커넥션-2 트랜잭션 시작
4. 부모 테이블(tb_parent)의 id(자식이 참조하는 컬럼)가 1인 레코드를 삭제하는 쿼리 실행 -> 잠금 대기 
5. 커넥션-1 ROLLBACK / COMMIT으로 트랜잭션 종료
6. 커넥션-2 에서 대기 중이던 작업이 즉시 처리됨

자식 테이블(tb_child)은 생성될 때 정의된 외래키의 특성 ON DELETE CASCADE 때문에 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되는 식으로 작동한다. 4에서 커넥션-2 가 대기하는 이유는 함께 삭제해야 하는 자식 레코드에 쓰기 잠금이 걸려 있기 때문이다.

 

데이터베이스에서 외래키를 물리적으로 생성하려면 이러한 잠금 경합까지 고려하여 모델링을 진행하는 것이 좋다. 물리적으로 외래키를 생성하면 자식 테이블에 레코드가 추가되는 경우 해당 참조키가 부모 테이블에 있는지 확인해야 한다. 중요한 것은 체크 작업 자체가 아니라 체크 작업을 위해 연관 테이블에 읽기 잠금을 걸어야 한다는 것이다. 이렇게 잠금이 다른 테이블로 확장되면 그만큼 쿼리의 동시 처리에 영향을 미친다.