내맘대로 정리하는 Real MySQL #5장) 인덱스

<내맘대로 정리하는> 시리즈는, 책을 읽으며 몰랐던 내용을 위주로 정리한 내용 그대로 포스팅하는 시리즈입니다 ^^

원문의 문맥이 궁금하면 (좋은 책이니) 이 참에 하나 장만하는 것은 어떤가요??

인덱스는 DB 쿼리의 성능을 언급하면서 빼놓을 수 없는 부분이다. 이번 장에서는 MySQL 쿼리의 개발이나 튜닝을 설명하기 전에 MySQL에서 사용 가능한 인덱스의 종류 및 특성을 간단히 살펴보겠다. 각 인덱스의 특성과 차이는 상당히 중요하며, 물리 수준의 모델링을 할 때도 중요한 요소가 될 것이다.

5.1 디스크 읽기 방식

DB의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건인 것들이 상당히 많다.

모든 저장 매체는 내부적으로 1개 이상의 디스크 드라이브를 장착하고 있다. 대부분의 저장 매체는 디스크 드라이브의 플래터(디스크 드라이브 내부의 데이터 저장용 원판)를 회전시켜 데이터를 읽고 쓰는 기계적인 방식을 사용한다.

디스크에 데이터를 쓰고 읽는데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정된다. 즉, 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다고 볼 수 있다. 그래서 여러번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 훨씬 작업의 부하가 커지는 것이다.

• 랜덤 I/O는 헤더를 움직이는 시스템 콜을 N번 보낼때 순차 I/O는 1번 보내는 식

일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O 자체를 줄여주는 것이 목적이라 할 수 있다. 여기서 랜덤 I/O를 줄인다는 것은 쿼리를 처리하는데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.

5.2 인덱스란?

DBMS에서 인덱스는 데이터의 저장 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다.
인덱스를 역할별로 구분해본다면 PK와 Secondary Key로 구분해볼 수 있다.

• PK : 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스
• Secondary Index : PK를 제외한 나머지 모든 인덱스

사용 DBMS의 인덱스에 널리 사용되는 알고리즘은 아래와 같다.

• B-Tree 알고리즘 : 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘이다.
• Hash 인덱스 : 칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘. 매우 빠른 검색을 지원한다.
• Fractal-Tree 알고리즘 : B-Tree의 단점을 보완하기 위한 알고리즘

데이터의 중복 허용 여부로 구분하면, 유니크 인덱스와 유니크하지 않은 인덱스로 구분할 수 있다. 유니크 여부는 실제 DBMS 쿼리를 실행해야 하는 옵티마이저에게는 상당히 중요한 문제이다. 유니크 인덱스에 대해 동등 조건으로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주기 때문이다.

5.3 B-Tree 인덱스

B-Tree 시뮬레이션 : https://www.cs.usfca.edu/~galles/visualization/BTree.html

루트 노드 - 브랜치 노드 - 리프 노드 → 데이터 파일 구성을 가지고 있으며, (데이터 레코드 건수가 작은 경우에는 브랜치 노드가 없는 경우도 있을 수 있다) 인덱스의 키 값은 모두 정렬되어 있지만 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서대로 저장돼 있다.
인덱스는 테이블의 키 칼럼만 가지고 있으므로 나머지 칼럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 한다. 이를 위해 인덱스의 '리프 노드'는 데이터 파일에 저장된 레코드의 주소를 가지게 된다.

3장 인서트 버퍼에서도 다룬 것 같긴하지만, InnoDB 스토리지 엔진은 레코드를 추가하며 인덱스에도 값을 추가하는 작업을 상황에 따라 지연시켜 나중에 처리할지, 아니면 바로 처리할지 결정한다.

1. 사용자의 쿼리 실행
2. InnoDB의 버퍼 풀에 새로운 키값을 추가해야 할 페이지(B-Tree의 리프 노드)가 존재한다면 즉시 키 추가 작업 처리
3. 버퍼 풀에 B-Tree의 리프 노드가 없다면 인서트 버퍼에 추가할 키값과 레코드의 주소를 임시로 기록해두고 작업 완료
4. 백그라운드 작업으로 인덱스 페이지를 읽을 때마다 인서트 버퍼에 머지해야 할 인덱스 키값이 있는지 확인한 후, 있다면 병합 (B-Tree에 인덱스 키와 주소를 저장)
5. DB 서버 자원의 여유가 생기면, MySQL 서버의 인서트 버퍼 머지 스레드가 조금씩 인서트 버퍼에 임시 저장된 인덱스 키와 주소를 병합

인덱스를 검색하는 작업은 B-Tree의 루트 노드부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행하는데, 이 과정을 '트리 탐색'이라고 한다. 인덱스 트리 탐색은 SELECT에서만 사용하는 것이 아니라 UPDATE나 DElETE를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우에도 인덱스가 있다면 빠른 검색이 가능하다.

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 또는 블록이라 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다. 인덱스도 결국은 페이지 단위로 관리되며, B-Tree에서 루트와 브랜치, 그리고 리프 노드를 구분한 기준이 바로 페이지 단위이다.

MySQL의 B-Tree는 자식 노드를 몇 개까지 가질 수 있을까? InnoDB의 모든 페이지 크기는 16KB로 고정되어 있다. 인덱스의 key가 16byte이고 자식노드주소가 12byte라면 B-Tree element 하나는 28byte가 된다. 16kb / 26byte 는 대략 585이므로 자식 노드는 585개까지 가질 수 있게 되는 것이다.

인덱스의 키 값의 길이가 길어지면, 전체 인덱스의 크기가 커지게 된다. 인덱스를 캐시해 두는 InnoDB의 버퍼 풀이나 MyISAM의 키 캐시 영역은 크기가 제한적이기 때문에 하나의 레코드를 위한 인덱스 크기가 커지면 커질수록 메모리(버퍼 풀이나 키 캐시)에 캐시해 둘 수 있는 레코드 수는 줄어드는 것을 의미한다. 자연히 메모리의 효율성이 떨어지게 되는 결과를 가져온다.

선택도(기수성) : 인덱스 키값 가운데 유니크한 값의 수를 의미. 중복된 값이 적을 수록 선택도가 높으며(유니크한 요소 개수가 증가) 쿼리가 효율적이게 된다.

일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4 ~ 5배 정도 더 비용이 많이 드는 작업인 것으로 예측한다. 즉 인덱스를 통해 읽어야 할 레코드 건수(옵티마이저가 판단한 예상 건수)가 전체 테이블 레코드의 20 ~ 25%를 넘어서면 인덱스를 이용하지 않고 직접 테이블을 모두 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리한느 것이 효율적이다.

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

인덱스 레인지 스캔

• 검색해야 할 인덱스 범위가 결정됐을 때 사용하는 방식
• B-Tree의 인덱스레코드를 타서 리프노드의 일부분만을 읽으며, 레코드에 접근할때는 한 건 한 건 단위로 랜덤 I/O가 발생한다.

인덱스 풀 스캔

• 인덱스의 처음부터 끝까지 모두 읽는 방식
• 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 많이 사용된다.
• 인덱스 레인지 스캔보다는 빠르지 않지만, 테이블 풀 스캔보다는 효율적이다.

인덱스 풀 스캔 방식 또한 인덱스를 이용하는 것이지만 효율적인 방식은 아니며,
일반적으로 인덱스를 생성하는 목적은 아니다.

루스 인덱스 스캔

• 느슨하게 또는 듬성듬성 인덱스를 읽는 것, 여러 조건을 만족해야 사용 가능하다.

MySQL 5.x 에서는 역순으로 정렬할 수 없지만, 스캔 방향을 반대로 할 수는 있다.

B-Tree 인덱스 특성상 사용할 수 없는 조건 목록

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

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

5.9 클러스터링 인덱스

클러스터링 인덱스는 PK에 의해 레코드의 저장 위치가 결정되므로 사실상 인덱스 알고리즘이라기 보다 테이블 레코드의 저장 방식이라 볼 수 있다. 테이블의 레코드가 PK로 정렬되어 저장된 경우만을 '클러스터링 인덱스; 또는 '클러스터링 테이블'이라 한다.

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

PK가 없는 InnoDB 테이블의 경우 PK를 대체할 column을 선택한다.

1. PK가 있으면 기본적으로 PK = 클러스터 키 이다.
2. NOT NULl 옵션의 유니크 인덱스 중 첫 번째 인덱스
3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후 클러스터 키로 선택 (사용 불가능)

InnoDB 테이블의 모든 보조 인덱스는 해당 레코드가 저장된 주소가 아니라 PK 값을 저장하도록 구현돼 있다. 때문에 InnoDB에서는 인덱스만으로 SELECT 할 수 있는 쿼리의 경우 아래 절차를 밟게 된다.

1. 인덱스를 검색해 레코드의 PK 값을 확인
2. PK 값을 이용해 다시 한 번 테이블을 검색한 후 최종 레코드를 가져온다.

장점과 단점

• PK로 검색할때 성능이 매우 빠르다.
• 테이블의 모든 보조 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많다. (커버링 인덱스)
• 모든 보조 인덱스가 PK를 가지고 있어, 클러스터 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커진다.
• 보조 인덱스를 통해 검색할때 PK로 다시 한 번 검색해야 하므로 처리 성능이 조금 느리다.
• INSERT할 때 PK에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느리다.
• PK를 변경할때 레코드를 DElETE하고 INSERT 하는 작업이 필요해 처리 성능이 느리다.

5.10 유니크 인덱스

유니크란 사실 인덱스라기보단 제약 조건에 가깝다고 볼 수 있다. MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다. 유니크 인덱스에서 NULL도 저장될 수 있는데, NULL은 특정의 값이 아니므로 2개 이상 저장될 수 있다. MySQL에서 PK는 NULL이 허용되지 않은 유니크 속성이 자동으로 부여된다.

유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다. 그래서 일반 보조 인덱스의 쓰기보다 느리다. 그런데 MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번히 발생한다.

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

5.11 외래키

• 테이블의 변경이 잠금하는 경우에만 잠금 경합이 발생한다.
  • 자식 테이블의 외래키 칼럼의 변경은 부모 테이블의 확인이 필요한데, 이 상태에서 부모 테이블에 해당 레코드가 잠겨 있다면, 그 잠금이 해제될 때까지 기다리게 된다.
• 외래키와 관련되지 않은 칼럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.

5.12 기타 주의사항

InnoDB 테이블의 경우, 인덱스에 대한 통계 정보를 관리하고 각 통계 정보를 기반으로 쿼리의 실행 계획을 수립한다. 인덱스에 대한 통계 정보는 아래와 같이 확인할 수 있다.

SHOW INDEX FROM table;

Cardinality 항목이 제일 중요하다.
인덱스의 통계 정보가 실제와는 너무 다르게 수집되어 MySQL이 실행 계획을 엉뚱하게 만들어버릴 수 있다. 이렇게 통계 정보가 크게 잘못되는 경우는 '테이블의 데이터가 별로 없거나', '단시간에 대량의 데이터가 늘어나거나 줄어든 경우' 발생한다. 이럴때, ANALYZE 명령어로 통계 정보를 다시 수집해 보는 것이 좋다.