[Real MySQL] 5장. 인덱스

디스크 읽기 방식

• CPU나 메모리와 같은 주요 장치는 ⇒ 전기적 장치
• 디스크 드라이브 ⇒ 기계식 장치

따라서 데이터베이스 서버에서는 항상 디스크 장치가 병목 지점이 된다. 데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건인 것들이 많음.

 

이러한 기계식 디스크 드라이브를 대체하기 위해 전자식 저장 매체인 SSD가 출시되고 있음. (SSD는 기존의 디스크 드라이브에서 데이터 저장용 플러터를 제거하고 플래시 메모리를 장착하고 있음)

---

랜덤 I/O와 순차 I/O

랜덤 I/O, 순차 I/O 모두 디스크 드라이버의 플래터를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미.

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

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

---

2. 인덱스란?

색인으로, DBMS에서 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능.

 

인덱스의 종류

프라이머리 키 (Primary Key)

• 레코드를 대표하는 칼럼.
• NULL을 허용하지 않으며, 유니크해야 한다.

 

보조 인덱스 (Secondary Index)

• PK를 제외한 나머지 모든 인덱스
• 유니크 인덱스와 유니크 하지 않은 인덱스로 구분할 수 있다.

---

3. 대표적인 인덱스 알고리즘

B-Tree 알고리즘

• 가장 일반적으로 사용되는 인덱스 알고리즘
• 칼럼의 값을 변형하지 않고, 원래의 값을 이용해 인덱싱하는 알고리즘

 

Hash 인덱스 알고리즘

• 칼럼의 값으로 해시 값을 계산해서 인덱싱 하는 알고리즘.
• B-Tree와 다르게 인덱스 키 값을 변형해서 인덱싱 ⇒ Prefix (ex: '%승호') 일치와 같은 검색 시 인덱스를 사용할 수 없음)
• 매우 빠른 검색을 지원하며 주로 메모리 기반 데이터베이스에서 많이 사용된다.

 

Fractal-Tree

• B-Tree의 단점을 보완하기 위해 고안된 알고리즘.
• B-Tree와 마찬가지로 인덱스 키 값을 변형하지 않고 인덱싱하며, 데이터가 저장되거나 삭제될 때 처리 비용을 상당히 줄일 수 있게 설계되어 있다고 한다.

 

---

3-1. B-Tree 인덱스 알고리즘

데이터베이스의 인덱싱 알고리즘 중 대표적인 알고리즘으로, 대부분의 일반적인 용도로 적합한 알고리즘.

B가 Binary가 아니라 Balanced이다 (이진 트리가 아니라, 2개 이상의 여러 자식 노드가 있을 수 있음)

칼럼의 원래 값을 변형시키지 않고 인덱스 구조체 내애서는 항상 정렬된 상태로 유지한다.

 

B-Tree 구조

B-Tree 트리 구조의 최상위에 하나의 루트 노드가 존재하고, 가장 하위에 있는 노드를 리프 노드라고 한다. (중간에 있는 브랜치 노드라고 하며 없을 수도 있다)

인덱스는 테이블의 키 칼럼만 가지고 있고, 나머지 칼럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 한다.

데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주소 값을 가지고 있다.

참고로 레코드 주소는 InnoDB 테이블에서는 PK에 의해 클러스터링되기 때문에 PK 자체가 주소 역할은 한다.

 

B-Tree 구조

인덱스의 키 값은 모두 정렬돼 있지만, 데이터 파일은 레코드는 정렬돼 있지 않고 임의의 순서대로 저장돼 있다.

(인덱스는 SortedList, 데이터 파일은 ArrayList라고 생각하면 좋다)

 

인덱스 키 추가

인덱스 키 추가시, 저장될 키 값을 이용해 B-Tree상의 적절한 위치를 검색해야 한다. 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다.

 

인덱스 키 삭제

해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 그냥 삭제 마크만 하면 작업이 완료.

이렇게 삭제 마킹된 인덱스 키 공간은 계속 그대로 방치하거나 재활용할 수 있다.

 

인덱스 키 변경

먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리된다.

 

인덱스 키 검색

인덱스를 검색하는 과정은 B-Tree 루트 노드부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행하는데, 이 과정을 트리 탐색이라고 한다.

• 인덱스 트리 탐색은 SELECT 뿐만 아니라 UPDASTE, DELETE를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우에도 인덱스가 있으면 빠른 검색이 가능하다.
• B-Tree 인덱스를 이용한 검색은 100% 일치 또는 앞부분만 일치하는 경우에만 사용할 수 있다. ( <> 비교나 값의 뒷부분이 일치하는 경우에는 B-Tree 인덱스를 이용한 검색이 불가능하며, 인덱스 키 값에 변형이 가해진 후 비교되는 경우에도 절대 B-Tree의 빠른 검색 기능을 사용할 수 없다)

 

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

인덱스 레인지 스캔

• 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식 (한 건이든, 여러 건이든)
• 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요해서 레코드 한 건 한 건 단위로 랜덤 I/O가 한 번씩 실행된다.
• 인덱스를 통해 읽어야 할 데이터 레코드가 20% ~ 25%를 넘으면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 더 효율적인 처리 방식이 될 수 있다.

 

인덱스 풀 스캔

• 인덱스의 처음부터 끝까지 모두 읽는 방식
• 대표적으로 쿼리의 조건절에 사용된 칼럼 인덱스의 첫 번째 칼럼이 아닌 경우 사용됨.
• 일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 효율적이다.
• 따라서 쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우 이 방식이 사용된다. (커버링 인덱싱): 인덱스에 포함된 칼럼만으로 쿼리를 처리할 수 있는 경우 테이블의 레코드를 읽을 필요가 없기 때문이다.
• 하지만 인덱스뿐만이 아니라 데이터 레코드까지 모두 읽어야 한다면 절대 이 방식으로 처리되지 않는다.

 

루스 인덱스 스캔

• 듬성듬성하게 인덱스를 읽는 것을 의미한다.
• 일반적으로 GROUP BY 또는 집합 함수 가운데 MAX(), MIN() 함수에 대해 최적화를 하는 경우에 사용.

 

다중 컬럼 인덱스

• 인덱스의 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬돼 있다.
• 즉, 두 번째 칼럼의 정렬은 첫 번째 칼럼이 똑같은 레코드에서만 의미가 있다는 것이다.

# 인덱스가 (first_name, last_name)에 생성되어 있을 때 

SELECT * FROM `member` WHERE last_name >= 'seungho'; 

# 이렇게 인덱스에 지정된 첫 번째 칼럼이 없이 두 번째 칼럼만 사용될 경우 의미가 없다는 것이다.

• 이러한 이유로 다중 칼럼 인덱스에서는 인덱스 내에서 각 칼럼의 위치(순서)가 상당히 중요하며 아주 신중히 결정해야 한다.

 

인덱스 정렬 및 스캔 방향

MySQL에서 인덱스를 생성하는 시점에 인덱스를 구성하는 각 칼럼의 정렬을 오름차순 혹은 내림차순을 설정할 수 없다.

하지만, 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다.

 

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

• NOT-EQUAL로 비교된 경우 (<>, NOT IN, NOT BETWEEN, IS NOT NULL)
• Like '%??' (앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴 비교
• 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우 (WHERE SUBSTRING(id)='A')
• NOT-DETERMINISTIC 속성이 스토어드 함수가 비교 조건에 사용된 경우
• 데이터 타입이 서로 다른 비교
• 문자열 데이터 타입의 콜레이션이 다른 경우 (utf8 vs euckr)

 

MySQL에서는 다른 일반적인 DBMS와 다르게 NULL 값도 인덱스로 관리된다.

따라서 "WHERE column is NULL"과 같은 WHERE 조건도 작업 범위 결정 조건으로 인덱스를 사용한다.

---

3-2. 해시 인덱스

해시 인덱스는 동등 비교 검색에는 최적화돼있지만 범위를 검색한다거나 정렬된 결과를 가져오는 목적으로는 사용할 수 없다.

일반적인 DBMS에서 해시 인덱스는 메모리 기반 테이블에 주로 구현돼있으며 디스크 기반의 대용량 테이블용으로는 거의 사용되지 않는다.

• 인덱스의 크기가 작고 검색이 빠르다. (해시 인덱스는 원래의 키 값을 저장하는 것이 아니라 해시 함수의 결과만을 저장하므로 크기가 작다.)
• 참고로 해시 인덱스에서 가장 중요한 것은 해시 함수로, 해시 함수의 결과 범위가 넓으면 그만큼 버킷이 많이 필요해서 공간의 낭비가 커지고, 값의 범위가 너무 작으면 충돌되는 경우가 너무 많이 발생해 해시 인덱스의 장점이 사라진다.

---

4. 클러스터링 인덱스

• MySQL에서 InnoDB, TokuDB 스토리 엔진에서만 클러스터링 인덱스를 지원.
• PK 키값이 비슷한 레코드끼리 묶어서 저장하는 것.
• 일반적으로 InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 PK 기반의 검색이 매우 빠르며, 대신 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느릴 수밖에 없다.
• 그러므로 가능하다면 PK를 명시하자.

 

보조 인덱스에 미치는 영향

InnoDB 테이블의 모든 보조 인덱스는 해당 레코드가 저장된 주소가 아니라 PK 키값을 저장하도록 구현돼있다.

• 보조 인덱스를 검색해 레코드의 PK 키 값을 확인 ⇒ PK 키값을 이용해서 최종 레코드를 가져옴.

 

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

장점

• PK로 검색할 때 처리 성능이 매우 빠름
• 테이블의 모든 보조 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음 (커버링인덱스라고 한다)

 

단점

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

 

---

5. 유니크 인덱스

유니크란 인덱스라기보다는 제약 조건에 가까움

말 그대로 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미

유니크 인덱스에서 NULL도 저장될 수 있는데, NULL은 특정의 값이 아니므로 2개 이상 저장될 수 있다. (PK는 NULL을 허용하지 않는다)

 

유니크 인덱스 vs 보조 인덱스

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

InnoDB 스토리지 엔진에서는 인덱스의 키의 저장을 버퍼링 하기 위해 인서트 버퍼가 사용된다. 그래서 인덱스의 저장이나 변경 작업이 상당히 빨리 처리되지만, 유니크 인덱스는 반드시 중복 체크를 해야 하므로 작업 자체를 버퍼링 하지 못한다. (이 때문에 유니크 인덱스는 일반 보조 인덱스보다 더 느려진다)

 

유니크 인덱스 주의사항

MySQL의 유니크 인덱스는 일반 다른 인덱스와 같은 역할을 하므로 중복해서 인덱스를 생성할 필요 없다.

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

---

6. 외래키

외래 키 제약이 설정되면, 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성된다.

외래 키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없다.