고성능을 위한 인덱싱 (2)

앞선 고성능을 위한 인덱싱 (1) 에서는 다양한 유형의 인덱스를 소개하고, 유의할 점들을 살펴보았다. 이곳에서는 이런 인덱스들을 실제로 활용하고 최적화하는 방법에 대해 살펴본다.

 

 

고성능을 위한 인덱싱 전략

 

Prefix 인덱스 및 인덱스 선택성

 

인덱싱도 결국 비용이다. 데이터가 크기가 클수록 인덱스의 크기는 커진다. 이 때, 전체 값 대신 앞의 몇 개의 문자를 인덱싱 하면 공간을 절약하고 성능을 향상시킬 수 있다. "앞의 몇 개"의 기준은 총 데이터의 갯수 (#T)에 대한 고유한 데이터의 갯수(Cardinality)의 비율에 따라 달라진다.  Cardinality / #T 의 값은 1 / #T부터 1까지의 스펙트럼을 지니며, 1에 가까울수록 훌륭한 인덱스 선택이 된다. 

 

즉, 데이터의 고유성이 보장되는 범위 내에서, 어디까지를 인덱싱할 것인지를 정하는 것이 바람직하다. 예를 들어, 아래와 같은 데이터를 가정해보자.

 

name

apple

absolute

apache

auto

 

#T는 4로 고정되어 있지만, 어디까지를 잘라서 인덱싱 할 것인지에 따라 Cardinality가 달라진다. 앞에서부터 1개까지만을 Prefix 인덱스로 지정하면, Cardinality는 1이 되고 ( 모두 a 이다 ) 바람직하지 못한 인덱스 설정이 될 것이다. 앞에서부터 3개까지를 Prefix 인덱스로 지정할 때, Cardinality가 4가 되면서 가장 효과적인 인덱싱이 된다.

 

이는 아주 간단한 예시이지만, 보다 데이터가 복잡하고 많은 상황에서 쿼리문이 실행되다고 생각하면 적절한 Cardinality를 찾기 위해 Cardinality / #T를 계산하며 최적화가 진행되어야 한다. 

 

-- Cardinality / #T 를 구하는 예시 쿼리

SELECT COUNT(DISTINCT name)/COUNT(*) AS ratio FROM user;

-- 최적화를 위한 비교 분석

SELECT 
COUNT(DISTINCT LEFT(name, 3))/COUNT(*) AS ratio_by_3,
COUNT(DISTINCT LEFT(name, 4))/COUNT(*) AS ratio_by_4,
COUNT(DISTINCT LEFT(name, 5))/COUNT(*) AS ratio_by_5,
COUNT(DISTINCT LEFT(name, 6))/COUNT(*) AS ratio_by_6,
COUNT(DISTINCT LEFT(name, 7))/COUNT(*) AS ratio_by_7
FROM user;

 

Prefix 인덱스는 인덱스를 더 작고 빠르게 만드는 좋은 방법이 될 수 있지만, 단점도 존재한다. MySQL은 ORDER BY 또는 GROUP BY 쿼리에 Prefix 인덱스를 사용할 수 없으며, 커버링 인덱스에 적용할 수도 없다. 보통 이 인덱스가 이점을 얻는 경우는 긴 16진수 식별자를 사용하는 경우 등이 될 것이다.

 

다중 열 인덱스

 

다중 열 인덱스 ( 복합 인덱스 )는 제대로 활용되지 못하는 경우가 많이 있다. 일반적인 실수는 열을 여러 개 또는 모든 열을 "개별적"으로 인덱싱 하거나, 열을 "잘못된 순서"로 인덱싱하는 것이다. 

 

 열을 개별적으로 인덱싱 하는 경우는 대게 'WHERE 절에 나타난 모든 열에 인덱스를 생성하세요' 와 같은 모호하고도 권위적인 조언으로부터 시작된다. 이 조언은 매우 잘못되었다. 이러한 인덱스는 실제 최적의 인덱스보다 훨씬 느린 성능을 갖는다.

 

MySQL의 인덱스 병합 ( Index Merge ) 전략을 사용하면, 제대로 인덱싱 되지 않은 테이블에 약간의 대처가 가능하다. 이 전략을 사용하면 쿼리가 단일 테이블의 여러 인덱스를 제한적으로 사용하여 원하는 인덱스를 찾을 수 있다. 또는 두 인덱스를 모두 사용하여 동시에 스캔하고 결과를 병합할 수도 있다. 인덱스 병합을 통해 실행된 쿼리는 EXPLAIN 구문을 통해서 확인이 가능하다.

 

EXPLAIN SELECT * FROM user WHERE email = 'seungo.ha@gmail.com' OR name = 'seungo';

 

*************** 1. row *************** 
id: 1
select_type: SIMPLE
... (생략)
Extra: USING usion(idx_email, idx_name); Using where

 

 

인덱스 병합 전략은 때때로 매우 잘 작동하지만 대게 실제로는 제대로 인덱싱되지 않은 테이블을 나타낸다. MySQL의 옵티마이저는 인덱스 병합 과정에서 발생하는 CPU  및 메모리 자원 소모를 고려에 두지 않기에, 랜덤 페이지 읽기 수만 최적화하고, 이는 쿼리의 성능을 더 느리게 할 수 있다.

 

좋은 열 순서 선택

 

앞서, 잘못된 순서로 인덱싱하는 것을 일반적인 실수로 소개했다. 그렇다면, 좋은 열을 선택하는 기준은 무엇일까? 

열 순서를 선택하는 데 있어 오래된 경험에서 나온 법칙은 "가장 선별적인 열을 먼저 배치하는 것"이다. 즉, 특정 열에 대한 Cardinality 가 높은 순으로 인덱싱을 진행하는 것이다. 아래와 같은 예시를 살펴보자

 

SELECT * FROM user WHERE role_id = 1 AND name = 'seungo';

 

이 예시에서 role_id 열과 name 열 중 어떤 것을 우선으로 인덱싱 해야 되는지는 두 가지 열의 Cardinality를 아래와 같이 비교한 후 결정할 수 있다.

 

SELECT SUM(role_id = 1) AS result1, SUM(name='seungo') AS result2 FROM user;

******************* 1. row *******************
result1: 4253
result2: 17

 

경험에 따르면, 인덱스에서 name을 우선적으로 배치해야 한다. 물론 방안은 이것은 고려할 정렬이나 그룹화가 없을 때 단순히 WHERE 조회를 최적화하는 데에 유용하다. 따라서 평균적인 성능이 특별한 경우의 성능을 대표한다고 가정하지 않도록 주의해야 한다. 특별한 경우, 쿼리의 성능이 오히려 저하될 수 있다.

 

클러스터형 인덱스

 

클러스터형 인덱스는 새로운 인덱스 유형이라기보다는 데이터 스토리지에 대한 접근 방식에 가깝다. InnoDB의 클러스터형 인덱스는 실제로 B-Tree 인덱스와 행을 동일한 구조에 함께 저장한다.

 

테이블에 클러스터형 인덱스가 있는 경우, 해당 행은 실제로 인덱스의 리프 페이지에 저장된다. "클러스터형"이라는 용어 가 의미하는 바도 인접한 키 값이 있는 행이 서로 가깝게 저장된다는 것이다. 클러스터형 인덱스는 동시에 두 곳에 행을 저장할 수 없기 때문에 테이블당 하나의 클러스터형 인덱스만 가질 수 있다. 

 

아래 그림은 클러스터형 인덱스에서 레코드가 배치되는 방식을 나타낸다. 리프 페이지에는 전체 행이 포함되지만, 노드 페이지에는 인덱싱 된 열만 포함이 된다.

 

클러스터형 인덱스 데이터 레이아웃

 

만약 기본키를 정의하지 않으면, InnoDB는 null 허용이 아닌 고유한 인덱스를 대신 사용하려고 시도한다. 그러한 인덱스조차 없다면 InnoDB는 숨겨진 기본 키를 정의한 다음 클러스터링을 한다. 

 

숨겨진 기본 키를 사용할 경우 단점은, 기본 키의 증분 값이 동일한 키를 사용하는 모든 테이블에서 공유되어 공유 키에 대한 뮤텍스 경합이 더 높다는 것이다.

 

뮤텍스 (mutex) 란?

여러 스레드를 실행하는 환경에서 자원에 대한 접근에 제한을 강제하기 위한 동기화 메커니즘. 잠금이라고도 한다.

 

따라서, InnoDB를 사용 중이고, 기본 키가 필요하지 않은 경우, 대리 키( surrogate key )를 정의하는 것이 좋다. 이를 수행하는 가장 쉬운 방법은 AUTO_INCREMENT 열을 사용하는 것이다. 자동 증가하는 정수 키를 두는 경우, UUID와 같이 랜덤한 값을 설정하는 것보다 쓰기 관점에서 우수한 성능을 지닐 수 있다. 책에 따르면 3,000,000 개의 행을 삽입할 때, 자동 증가 정수형 키를 지닌 테이블과 랜덤 키를 지닌 테이블 사이에는 3,300 초 가량의 유의미한 성능 차이가 나타난다.

 

이것은 클러스터링 구조에 이유가 있는데, 아래 그림을 살펴보면 쉽게 이해할 수 있다.

 

클러스터형 인덱스에 순차 인덱스 값을 삽입하는 경우

 

클러스터형 인덱스에 랜덤 값을 삽입하는 경우

 

기본 키가 순차적일 경우 각 레코드를 이전 레코드 바로 뒤에 저장하고, 페이지의 최대 채우기 비율에 다다르면 새 페이지로 이동한다. 반면, 랜덤 키의 경우, 각 새 행의 키 값이 이전 행보다 크다는 보장이 없기에 InnoDB는 항상 새 행을 인덱스의 끝에 배치할 수 없다. 즉, 기존 데이터의 중간 부근에서 적절한 위치를 찾아야 하고, 이 과정에서 새 행을 위한 공간 확보를 위해 페이지를 자주 분할해야 한다. 분할로 인해 기존 데이터를 새 페이지로 이동시켜야 할뿐더러, 페이지는 듬성듬성 채워지고 결국 최종 데이터는 단편화된다. 

 

커버링 인덱스

 

쿼리의 WHERE 절에 대한 인덱스를 만드는 것이 일반적이지만, 이는 일부에 불과하다. 인덱스는 WHERE 절뿐만 아니라 전체 쿼리에 대해 설계되어야 한다. 또한, 인덱스는 행을 효율적으로 찾는 방법이지만, MySQL 은 인덱스를 사용하여 열의 데이터를 검색할 수 있으므로 행을 전혀 읽을 필요가 없다. 쿼리를 충족하는 데 필요한 모든 데이터를 커버하는 인덱스를 "커버링 인덱스 ( covering index )"라고 한다. 

 

인덱스 항목은 일반적으로 전체 행 크기보다 훨씬 작기 때문에 MySQL 은 인덱스만 읽는 경우 훨씬 적은 데이터에 액세스 할 수 있다. 또한, 인덱스가 데이터보다 작고 메모리에 더 적합하기 대문에 I/O 바운드 워크로드에 유용하다. 아래와 같은 쿼리 EXPLAIN을 살펴보자. 

 

EXPLAIN SELECT id, name FROM user;

 

******************* 1. row *******************
id: 1
select_type: SIMPLE
...
Extra: Using index

 

user 테이블에 (id, name) 다중열 인덱스가 있다면, MySQL은 두 열에만 액세스 하는 쿼리에 이 인덱스를 사용할 수 있다.

 

사용하지 않는 인덱스

중복 인덱스와 이중 인덱스 이외에도 서버에서 사용하고 있지 않은 인덱스가 있을 수 있다. performance_schema를 활용하여, 이들을 식별하고 제거해 주도록 하자.

 

SELECT * FROM sys.schema_unused_indexes;