MySQL의 인덱스 종류에 대해 알아보자.

클러스터링 인덱스 (Clustered Index)

클러스터링에 대해 설명하기 앞서 Index Page에 대해 짚고 넘어가자.

index page란?

Index Page란 인덱스 트리의 각 노드에 해당되며, Key - Value로 키 값에 대한 데이터의 위치 정보를 갖고 있어

DBMS가 테이블의 특정 행을 찾도록 도와준다.

 

클러스터링 인덱스는 기본적으로 테이블 당 1개만 생성가능하며, 실제 데이터 페이지가 정렬된 형태이다.

PK가 클러스터링 인덱스로 구성되어 있고 UNIQUE 제약 조건과 NOT NULL 제약 조건을 가진 컬럼도 클러스터링 인덱스로 구분되지만 PK가 있다면 PK가 우선순위를 가진다.

클러스터링 인덱스는 위와 같이 인덱스 페이지 안에 루트 노드와 리프 노드(데이터 페이지)가 들어가 있다.

루트 노드에서 부터 하위 노드를 거쳐 리프 노드까지 이동하면서 원하는 조건의 데이터를 찾다가

Key 값으로 데이터 위치를 갖는 값을 얻어 실제 데이터가 위치한 곳을 찾아가 데이터를 가져온다.

즉, 실제 데이터가 인덱스 페이지에 저장되어 있기 때문에 데이터를 가져오기 위한 추가적인 I/O 과정이 필요없다.

---

논 클러스터링 인덱스 (Non-Clustered Index) 또는 세컨더리 인덱스

테이블당 여러개 존재 가능하며, 실제 데이터 페이지는 정렬되지 않은 그대로이다.

CREATE INDEX로 인덱스를 만들면 기본적으로 "논 클러스터링 인덱스"이다.

또한 UNIQUE 제약조건 적용 시 자동으로 "논 클러스터링 인덱스"가 생성된다고 한다.

논 클러스터링 인덱스는 위와 같이 인덱스 페이지와 데이터 페이지가 별도로 분리되어 있다.

실제 데이터 페이지가 정렬되지 않은 그대로 라는 말은 인덱스 페이지안에서는 정렬되어 있지만

실제 데이터가 있는 디스크에는 해당 데이터들이 정렬되지 않은 상태라는 말이다.

클러스터링 인덱스와 다르게 인덱스 페이지에서 리프 노드까지 탐색을 마친후 해당 데이터를 가져오기 위해

실제 데이터 페이지에 추가적인 I/O 작업이 필요하다.

 

정리하자면, 클러스터링 인덱스는 루트 노드에서 바로 리프 노드를 찾아 그 값을 통해 실제 데이터를 찾아가고

논 클러스터링 검색하려는 데이터 위치를 찾기 위해 리프 노드까지 찾은 후 실제 데이터 페이지에서 조회해야한다.

그렇기 때문에 논 클러스터링 인덱스는 클러스터링 인덱스에 비해 I/O 작업이 상대적으로 오래걸리지만

그렇다고 Index를 걸지 않고 테이블 풀 스캔하는 것 보단 훨씬 성능이 좋다.

하지만 가능하다면 클러스터링 인덱스를 활용해 조회하는 것이 성능에 도움이 될 것이다.

---

다중 컬럼 인덱스 (복합 인덱스)

2개 이상의 컬럼을 묶어 생성하는 인덱스를 말한다.

단일 인덱스가 한 개의 컬럼에 대해 생성되는 것과는 달리, 복합 인덱스는 여러 개의 컬럼을 함께 사용하여 인덱스를 생성한다.

 

단일 인덱스를 여러개 사용할 수 없나 ?

단일 인덱스를 여러개 사용하면 되지 않나? 라고 할 수 있다.

아래 쿼리에 a와 b 컬럼에 각각 인덱스가 적용되어 있다고 가정해보자.

EXPLAIN SELECT * FROM test_table WHERE a = 4 AND b = 9;

이 쿼리를 실행해보면 두 인덱스를 모두 활용하여 “Using intersect” 교집합을 구하는 index merge 엑세스 방법을 사용한다.

이는 기본적으로 아래 옵티마이저 옵션이 기본값이기 때문이라고 한다.

SET optimizer_switch="index_merge_intersection=on";

index merge에 대한 참고자료가 많이 없어 시원하게 알순없었지만

데이터 분포가 균일하지 않을 때 병합과정이 생각보다 오래 걸려 훨씬 느릴 수 있다고 한다.

 

만약 index merge를 사용하지 않는다면 옵티마이저는 레코드 결과 수가 더 적은 인덱스를 활용해 효율적으로 조회한다.

하지만 인덱스를 사용하지 않는 컬럼에 대해서는 테이블 풀 스캔을 하기 때문에 성능이 그닥 좋진 않을 것이다. 

 

a와 b를 묶은 복합 인덱스를 생성하면 어떨까 ?

CREATE INDEX idx_a_b ON test_table(a, b);

복합 인덱스를 적용한 후 조회했을 때는 약 10배 정도 쿼리 속도가 빨라졌다.

즉, 여러 컬럼의 조합으로 조회할 때 복합 인덱스의 적용 유무가 성능을 크게 좌지우지한다는 것을 확인할 수 있었다.

 

복합 인덱스를 구성하는 컬럼의 순서

다시 돌아가서 복합 인덱스는 첫 번째 컬럼으로 스캔 범위를 좁히느냐 못 좁히느냐가 쿼리 성능을 크게 좌지우지한다.

즉, 복합 인덱스를 구성하는 컬럼의 순서가 중요하다는 것이다.

 

복합 인덱스에서 N번째 인덱스 키 값의 N - 1 키 값에 대해 다시 정렬이 일어난다.

이 예시를 보면 department가 먼저 정렬되고 그 다음 salary, name 순으로 정렬이 일어난다.

이러한 계층적 정렬 구조 때문에, 복합 인덱스를 사용할 때는 컬럼의 순서가 매우 중요하다.

 

만약 위 테이블에 대해 아래 쿼리를 실행하면 어떤식으로 탐색하게 될까?

-- CREATE INDEX idx_company_composite ON company (department, salary, name);

SELECT * FROM employees 
WHERE department = 'IT' AND salary >= 5000 AND name = 'ACE';

1. department = IT 조건으로 해당하는 레코드들을 찾는다.
2. salary ≥ 5000'조건을 확인하는데, 이는 범위 조건이므로 여러 값을 확인해야 한다.
3. salary가 5000보다 크거나 같은 각각의 레코드에 대해 name = 'ACE' 조건을 확인한다.

여기서 중요한 점은 salary가 범위 조건이므로, 해당 값들이 연속적이지 않을 수 있다는 것이다.

이로 인해 name 컬럼의 정렬 순서가 흐트러지게 되어 name 조건을 처리할 때 효율적인 검색이 어려워진다.

 

그렇다면 위 쿼리를 개선하려면 어떻게 할 수 있을까

-- CREATE INDEX idx_company_composite ON company (department, name, salary);

SELECT * FROM company 
WHERE department = 'IT' AND name = 'ACE' AND salary >= 5000;

이런식으로 수정한다면 범위 조건을 확인하기 전에

앞 컬럼 department, name에서 필터링이 되어 범위 탐색 작업의 범위를 좁힐 수 있고 salary에서 범위 조건을 체크할 수 있다.

 

복합 인덱스 사용 시 주의할점

B-Tree 인덱스의 특징은 왼쪽 값에 기준해 오른쪽 값이 정렬되어 있다.

그렇기 때문에 아래의 쿼리 경우에는 왼쪽 컬럼의 값(department)을 몰라 인덱스 레인지 스캔을 하지 못하고 인덱스 풀 스캔을 하게 된다.

-- CREATE INDEX idx_company_composite ON company (department, name, salary);

SELECT * FROM company 
WHERE name = 'ACE' AND salary >= 5000;

즉, 조건 절의 순서와 복합 인덱스를 생성했을 때의 컬럼 순서가 맞지 않으면 복합 인덱스를 검색할 수 없게 된다.

아래 처럼 앞쪽의 컬럼은 조건 절에 나와있어야하지만 뒤쪽 컬럼은 조건 절에서 빠져도 상관없이 인덱스를 사용할 수 있다.

SELECT * FROM company WHERE department = 'IT' AND name = 'ACE';
SELECT * FROM company WHERE department = 'IT';

이러한 이유로 복합 인덱스의 생성 순서는WHERE 절에서 먼저 사용되는 컬럼을 앞쪽에 위치시키는 것이 좋다.

 

참고로 컬럼의 순서가 맞지 않을 때 무조건 복합 인덱스를 사용하지 못하는 것은 아니다.

커버링 인덱스가 적용되어 있고 조건에 없는 선행 컬럼의 카디널리티가 높다면 Index Skip Scan이 적용된다.

이와 관련해서는 아래 포스팅을 참고하자.

2024.12.31 - [◼ DB] - MySQL의 인덱스 스캔에 대해 알아보자 (feat. 커버링 인덱스)

---

R-Tree 인덱스

공간 인덱스라고 한다.

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

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

 

B-Tree는 1차원인 반면 R-Tree는 2차원 공간 개념이 차이점이라 볼 수 있다.

GPS나 지도 기능를 제공하는 서비스에서는 MySQL의 공간 확장 기능을 사용하면 구현할 수 있다.

MySQL의 공간 확장에는 다음과 같이 크게 3가지 기능이 포함되어 있다.

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

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

GEOMETRY 타입은 POINT, LINE, POLYGON 객체를 모두 저장할 수 있다.

 

MBR (Mininum Bounding Rectangle)

R-Tree의 핵심은 MBR이다.

MBR이란 도형을 감싸는 최소 크기의 사각형을 의미하며

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

(R이 바로 Rectangle의 약자이며 공간 인덱스라고 함)

R-Tree 인덱스는 하나의 도형뿐만 아니라 근처의 도형도 함께 감싸는 저장방식을 통해 도형의 포함 관계를 이용해

B-Tree 와 처럼 MBR로 특정 범위의 도형을 묶어서 저장할 경우, 검색 시간을 크게 단축할 수 있다.

 

R-Tree에 대해서는 직접 활용할 일이 아직없어서 여기까지만 간단하게 다루도록 한다.

---

전문 검색 인덱스

B-Tree 인덱스처럼 키워드화 한 값에 대한 인덱스를 적용하는 것이 아닌

문서나 텍스트 필드의 내용을 단어 단위로 분석하여 검색할 수 있게 해주는 인덱스이다.

 

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

키워드 분석 및 인덱싱 방법에는 크게 어근 분석과 n-gram 분석 알고리즘으로 구분할 수 있다.

 

어근 분석 알고리즘

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

• 불용어 처리 (크게 의미가 없는 단어를 필터링해 제거하는 작업)
• 어근 분석 (검색어로 선정된 단어의 원형을 찾는 작업)

MySQL 서버에는 불용어가 이미 소스코드에 정의되어 있는데, 기본적으로 정의된 불용어는 다음 쿼리로 확인할 수 있다.

SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

등록된 불용어와 일치하거나 불용어를 포함하는 단어는 인덱스에 등록하지 않는다.

사용자가 별도로 불용어를 정의할 수 있는 기능도 총 2가지 방법으로 제공한다.

• 불용어 목록을 파일로 저장해 경로 등록
• 불용어 목록을 테이블로 저장해 해당 테이블을 시스템 변수에 등록

 

다음과 같이 불용어를 무시할 수도 있다.

-- 설정 후 MySQL 재시작 필요한 명령어 (전체 스토리지 엔진에 적용됨)
ft_stopword_file=''

-- 실행 중 변경 가능하지만 innodb 스토리지 엔진에만 적용되는 명령어
SET GLOBAL innodb_ft_enable_stopword=OFF;

 

어근 분석의 경우에는 한글의 경우 영어와 같이 단어의 변형 자체가 거의 없어

어근 분석보다는 문장의 형태소 분석으로 명사와 조사를 구분하는 기능이 더 중요해

형태소 분석 라이브러리인 MeCab 플러그인을 제공한다.

 

MeCab을 그냥 사용할 수 있는 것은 아니고 단어 사전이 필요하며

문장을 해체해 각 단어의 품사를 식별할 수 있도록 학습시키는 과정이 필요해

한글에 맞게 완성도를 갖추는 작업은 많은 시간과 노력이 필요하다고 한다.

 

n-gram 알고리즘

MeCab은 한국어를 학습시키기 위해 많은 시간을 필요로 해

전문적인 검색 엔진을 고려하는 것이 아니라면 범용적으로 적용하긴 어렵다.

이 단점을 보완하기 위한 방법이 n-gram이다.

 

n-gram은 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법이다.

국가별 언어에 대한 준비 작업이 필요 없지만 인덱스의 크기는 상당히 큰 편이다.

n-gram에서 n은 인덱싱할 키워드 최소 글자 수를 의미하는데

일반적으로 2글자 단위로 키워드를 쪼개 인덱싱하는 2-gram 방식이 많이 사용된다.

만약 아래 문장을 2-gram 방식으로 인덱싱하면 어떻게 되는지 확인해보자.

안녕하세요 -> 안녕, 녕하, 하세, 세요

이렇게 분리된 각각의 2글자 조합이 인덱스로 저장되어, 검색 시 활용된다.

"녕하"가 2-gram 인덱스 중 하나로 존재하기 때문에, 사용자가 "녕하" 라고 검색했을 때, “안녕하세요”가 검색 결과에 포함된다.

 

전문 검색 인덱스 사용 조건

전문 검색 인덱스를 사용하려면 반드시 아래 2가지 조건을 만족해야한다.

• 쿼리 문장이 전문 검색을 위한 문법
• 테이블이 전문 검색 대상 컬럼에 대해서 전문 인덱스 보유

 

다음과 같이 본문에 전문 검색 인덱스를 적용한 게시글 테이블을 만들었다고 가정해보자.

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT KEY ft_content (content) WITH PARSER ngram
) ENGINE=InnoDB;

MySQL에서 전문 검색 인덱스를 사용하기 위해서는 반드시 MATCH와 AGAINST 구문으로 검색 쿼리를 작성해야 한다.

-- 전문 검색 인덱스를 사용하는 올바른 쿼리
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('검색어' IN BOOLEAN MODE);

-- 전문 검색 인덱스를 사용할 수 없는 쿼리 (풀 테이블 스캔)
SELECT * FROM articles 
WHERE content LIKE '%검색어%';

 

---

함수 기반 인덱스

기본적으로 컬럼의 값을 변형해 조회할 경우 해당 컬럼의 인덱스를 사용할 수 없다.

때로는 변형된 칼럼의 값에 대해서도 인덱스를 생성할 필요도 있을 수 있는데

이 때, MySQL 8.0버전 부터 지원되는 함수 기반 인덱스를 사용할 수 있다.

 

함수 기반 인덱는 다음과 같이 2가지 방법으로 구현할 수 있다.

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

 

가상 컬럼 인덱스

다음과 같은 테이블이 있다고 가정해보자.

CREATE TABLE user (
	user_id INT PRIMARY KEY,
	first_name VARCHAR(10),
	last_name VARCHAR(10)
) ENGINE=InnoDB;

만약 first_name과 last_name을 합쳐서 검색해야하는 경우가 생긴다면

full_name이라는 컬럼을 추가해 인덱스를 적용해야했지만

 

MySQL 8.0부터는 다음과 같이 가상 컬럼을 추가하고 가상 컬럼에 인덱스를 생성할 수 있다.

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

 

가상 컬럼의 옵션으로는 VIRTUAL과 STORED가 있다.

VIRTUAL

VIRTUAL은 가상 컬럼의 기본 옵션이다.

이 옵션을 사용하면 컬럼의 값이 실제로 디스크에 저장되지 않고, 데이터를 조회할 때마다 정의된 표현식을 기반으로 실시간으로 계산된다.

이는 디스크 공간을 절약할 수 있다는 장점이 있지만, 조회 시마다 계산이 필요하므로 약간의 성능 오버헤드가 발생할 수 있다.

 

STORED

STORED 옵션을 사용하면 계산된 값이 실제로 디스크에 물리적으로 저장된다.

이는 더 많은 저장 공간을 사용하지만, 조회 시에는 이미 저장된 값을 바로 읽어오므로 VIRTUAL 옵션에 비해 더 빠른 조회 성능을 제공한다.

다만, 기반이 되는 컬럼의 값이 변경될 때마다 STORED 컬럼의 값도 자동으로 업데이트되어 저장되므로

쓰기 작업 시에는 추가적인 오버헤드가 발생한다.

 

각 옵션의 선택은 상황에 따라 달라질 수 있다.

저장 공간이 제한적이고 실시간 계산의 성능 영향이 크지 않다면 VIRTUAL을

조회 성능이 중요하고 저장 공간이 충분하다면 STORED를 선택할 수 있을 것 같다.

 

함수를 이용한 인덱스

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

CREATE TABLE user (
	user_id INT PRIMARY KEY,
	first_name VARCHAR(10),
	last_name VARCHAR(10),
	INDEX ix_fullname ((CONCAT(first_name,' ',last_name)))
) ENGINE=InnoDB;

이 방식은 테이블의 구조를 변경하지 않고 계산된 결과값의 검색을 빠르게 만들어준다.

 

다만 함수 기반 인덱스를 제데로 활용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용되어야만 한다.

SELECT * FROM user WHERE CONCAT(first_name,' ',last_name) = 'log hyun';

WHERE 조건절에 사용된 표현식이 다르다면 결과가 같다하더라도

MySQL 옵티마이저가 다른 표현식으로 간주해 함수 기반 인덱스를 사용하지 못한다.

만약 컬럼과 조건절의 문자열의 character set이 다르더라도 인덱스를 사용하지 못하니 이점 주의하자.

---

멀티 밸류 인덱스

전문 검색 인덱스를 제외한 모든 인덱스들은 인덱스 키와 데이터 레코드가 1:1관계를 가진다.

멀티 밸류 인덱스는 이름 그대로 하나의 데이터 레코드가 여러 개의 인덱스 키 값을 가질 수 있는 인덱스이다.

 

MySQL에서는 다음과 같이 JSON 포맷 데이터도 저장할 수 있는데

MYSQL 8.0부터는 JSON 필드에 저장된 배열 형태에 대한 인덱스도 생성할 수 있다.

-- credit_info JSON 필드 내의 'credit_scores' 배열에 멀티 밸류 인덱스를 생성
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    credit_info JSON
    INDEX mx_credit_scores ((CAST(credit_info -> '$.credit_scores' AS UNSIGNED ARRAY)))
);

INSERT INTO users VALUES (1, 'kaki', '{"credit_scores" : [350, 370, 362]}');

멀티 밸류 인덱스를 활용하기 위해서는 일반적인 조건 방식을 사용해선 안되고 반드시 다음 함수들을 이용해 검색해야만 사용할 수 있다.

• 값 MEMBER OF(컬럼명 → ‘$.json배열필드명’) : JSON 배열 안에 특정 값이 존재하는지 확인
• JSON_CONTAINS(target, 찾는값, target_path ) : JSON 문서나 배열이 특정 값이나 경로를 포함하는지 확인
• JSON_OVERLAPS(array1, array2) : 두 JSON 문서나 배열 간에 공통 요소가 있는지 확인 (교집합 확인)

아래는 위 함수를 이용해 조회하는 쿼리로

"JSON 형식으로 저장된 credit_info 컬럼 내의 credit_scores 배열에 350이라는 값이 포함되어 있는지" 확인하는 쿼리이다.

SELECT * FROM user WHERE 350 MEMBER OF(credit_info -> '$.credit_scores');

---

유니크 인덱스

인덱스라기 보다는 제약조건에 가까운데

말 그대로 유니크하다는 것으로 한 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없다는 것이다.

 

MySQL에서는 UNIQUE 제약조건을 적용한 컬럼에는 자동으로 유니크 인덱스가 지정된다.

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

다만 새로운 레코드가 추가되거나 변경될 경우에는 유니크 인덱스 경우 중복된 값이 있는지 없는지 체크하는 과정이 한단계 더 필요하다.

 

이 과정에서 중복된 값을 체크할 때는 읽기 잠금, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 빈번히 발생한다고 한다.

따라서 유니크하지 않은 세컨더리 인덱스에 비해 쓰기가 느리다.

꼭 필요하다면 유니크 인덱스를 생성하는 것은 당연하지만 유일성이 보장될 필요가 없다면 적용하지 않는 것이 좋을 것이다.

---

참고자료

• Real MySQL 8.0