[Real MySQL 8장] 인덱스

정훈희·2023년 9월 9일

B-tree Index RealMySQL 인덱스 클러스터링 인덱스

Real MySQL

목록 보기

3/5

디스크 I/O

DB의 성능 튜닝은 대부분 디스크 I/O를 줄이는 방향으로 이루어진다.

SSD vs HDD

SSD는 DRAM보다는 느리지만, HHD보다 1000배 가량 빠르다.

순차 I/O에선 SSD가 HDD보다 조금 빠르거나 비슷하지만, 랜덤 I/O에선 SSD가 HDD보다 압도적으로 빠르다.

→ DBMS에선 랜덤 I/O가 대부분이므로 SSD가 좋다.

랜덤 I/O vs 순차 I/O

랜덤 I/O, 순차 I/O 다 읽을 데이터의 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽어야 한다.

위 그림 기준으로 순차 I/O는 읽을 데이터가 붙어있기 때문에 헤더를 1번만 움직여도 되지만 랜덤 I/O는 읽을 데이터가 흩어져있기 때문에 헤더를 3번 움직여야한다.

→ 순차 I/O의 성능이 랜덤 I/O보다 좋다.

→ 쿼리 튜닝 시 랜덤 I/O를 순차 I/O로 바꾸는 것이 중요하다.

B-Tree 인덱스

인덱스란?

DBMS에서 인덱스는 데이터의 쓰기(INSERT, UPDATE, DELETE)성능을 희생하고 읽기 성능을 높이는 기능이다.

B-Tree 인덱스

B-Tree는 DB에서 가장 일반적으로 사용되는 인덱스 알고리즘이다.

B-Tree는 트리 구조의 최상위에 하나의 루트 노드, 가장 하위에 있는 리프 노드, 루트 노드도 리프 노드도 아닌 중간 노드인 브랜치 노드로 구성된다.

인덱스의 키 값은 모두 정렬되어 있지만, 데이터 파일의 레코드는 정렬되어 있지 않다.

위 그림은 InnoDB에서 인덱스를 통해 데이터를 읽는 과정을 나타낸다.

InnoDB에선 PK만이 물리적인 주소를 가지므로 세컨더리 인덱스를 이용하여 데이터를 찾기 위해선 두번의 B-Tree 탐색이 필요하다.

B-Tree 인덱스 키 추가, 삭제, 변경

인덱스 추가로 인해 쓰기 성능에 미치는 영향 테이블에 레코드를 추가하는 작업의 비용을 1이라고 하면 인덱스에 키를 추가하는 비용은 대략 1.5이다. 이 비용의 대부분은 디스크로 부터 인덱스 페이지를 읽고 쓰기를 해야해서 발생하는 비용이다.

다른 스토리지 엔진에서는 INSERT, UPDATE, DELETE 문장이 실행되면 즉시 쓰기 연산을 실행한다.

But, InnoDB에선 필요한 경우 키 추가 작업을 지연시켜 나중에 처리할 수 있다.(By 체인지 버퍼)

But, PK나 유니크 인덱스의 경우는 중복 체크가 필요하므로 지연 처리가 불가능하다.

변경의 경우는, 인덱스는 키 값에 따라 저장될 위치가 결정되므로 삭제한 뒤 새로운 값을 추가하는 형태로 처리한다.

B-Tree 인덱스 키 검색

여러 단점을 감당하며 인덱스를 쓰는 이유는 “빠른 검색” 때문이다.

B-Tree 인덱스 검색 시 주의점
- 100% 일치, 앞 부분만 일치, 부등호 비교 조건에서는 인덱스를 활용할 수 있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없다.
  
  ex1) 이름이 “John”인 사람 검색 → 인덱스 O
  
  ex2) 이름이 “Jo”로 시작하는 사람 검색 → 인덱스 O
  
  ex3) 이름이 “J” 이후 인 사람 검색 → 인덱스 O
  
  ex4) 이름이 “hn”로 끝나는 사람 검색 → 인덱스 X
  
  ex5) 이름이 “John”이 아닌 사람 검색 → 인덱스 X (사용하지만 비효율적)
  
  ex6) 이름이 “Jo”로 시작하지 않는 사람 검색 → 인덱스 X (경우에 따라 사용됨, 비효율적)
- 인덱스를 이용한 검색 시 인덱스의 키 값에 변형이 가해진 후 비교되는 경우는 사용할 수 없다.
  
  → 연산을 수행한 결과로 검색하는 작업은 B-Tree의 장점을 이용할 수 없다.
- InnoDB에선 데이터를 읽어오며 해당 레코드의 잠금을 획득하는데, 인덱스가 설정되어 있지 않아서 테이블 풀 스캔을 하게 되면 많은 레코드와 갭이 잠기게되어 성능 문제나 데드락이 발생할 수 있다.
  
  → 인덱스 설계가 매우 중요하다.

B-Tree 인덱스 사용에 영향을 미치는 요소

인덱스 키 값의 크기

디스크에 데이터를 저장하는 가장 기본 단위를 “페이지” 라 하고, ****인덱스도 페이지 단위로 관리된다.

B-Tree는 자식 노드의 개수가 가변적인데, MySQL에서는 인덱스의 페이지 크기와 키 값의 크기에 따라 자식 노드 수가 결정된다.
- 페이지의 크기는 innodb_page_size 시스템 변수를 이용해 4~64kb 선택할 수 있다. (기본 값은 16kb)
만약 페이지 크기 = 16kb, 키 값의 크기 = 16b, 자식 노드 주소 = 12b 라고 하면 16 * 1024 / (16 + 12) = 585 개의 자식 노드를 가질 수 있다.

그러므로 당연히 키 값이 커지면 가질 수 있는 자식 노드의 수가 줄어들고, 그렇게 되면 디스크 I/O 횟수가 늘어나고, 속도가 느려지게 된다.

또한, 인덱스 키의 크기가 커지면 전체적인 인덱스의 크기도 커진다.

→ 인덱스를 캐시해 두는 InnoDB의 버퍼 풀 영역의 크기는 제한적이므로 인덱스 키의 크기가 커지면 메모리에 캐시해 둘 수 있는 레코드 수는 줄어들어 성능이 저하될 수 있다.
B-Tree 깊이

인덱스 키 값의 크기가 커지면 한 페이지에 담을 수 있는 키의 수가 적어지고, 같은 레코드 건수라 하더라도 B-Tree의 깊이가 깊어져서 더 많은 디스크 읽기가 필요하다.

ex) B-Tree의 깊이가 3이고 키의 크기가 16b면 최대 2억개 정도의 키를 담을 수 있지만, 키의 크기가 32b면 5천만개 정도의 키를 담을 수 있다.
카디널리티

카디널리티는 데이터의 중복도를 말하며, 높을 수록 중복된 값이 적다.

인덱스는 카디널리티가 높을수록 검색 대상이 줄어들기 때문에 더 빠르게 처리된다.
```
SELECT *
FROM tb_test
WHERE country = 'KOREA' AND city = 'SEOUL';
```
tb_test 테이블의 레코드 건수는 1만 건, country 컬럼에만 인덱스를 생성했다 했을 때 아래 두가지 케이스를 살펴보자
1. country 컬럼의 유니크한 값의 개수가 10개인 경우
  
  country = ‘KOREA’ 이 조건에서 일치하는 값은 1000개 일 것이고, 그 중 city = ‘SEOUL’ 인 값이 1건이면 999건은 불필요하게 읽은 것이다.
2. country 컬럼의 유니크한 값의 개수가 1000개인 경우
  
  country = ‘KOREA’ 이 조건에서 일치하는 값은 10개 일 것이고, 그 중 city = ‘SEOUL’ 인 값이 1건이면 9건은 불필요하게 읽은 것이다.
→ 카디널리티가 높을 수록 읽어오는 데이터가 줄어들며 인덱스의 효율이 상승한다.
읽어야 하는 레코드의 건수

인덱스를 통해 레코드를 읽는 것은 바로 테이블의 레코드를 읽는 것 보다 비용이 높다. (약 4~5배)

즉, 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블 풀 스캔 후 필요한 레코드만 걸러내는 방식으로 처리한다.

→ 읽어야 하는 레코드의 건수가 많을 수록 인덱스 사용이 비효율적이다.

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

인덱스 레인지 스캔

인덱스의 접근 방법 중 가장 대표적이고, 뒤에 나오는 다른 방식보다 빠르다.

[ 과정 ]
1. 인덱스에서 조건이 만족하는 값이 저장된 위치를 찾는다. (인덱스 탐색)
2. 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 읽는다. (인덱스 스캔)
  
  → 해당 인덱스를 구성하는 컬럼의 정순 or 역순으로 정렬된 상태로 레코드를 가져온다.
3. 읽어온 인덱스 키와 레코드 주소를 이용해 레코드를 읽는다.
  
  → 이때 레코드 한 건 마다 랜덤 I/O가 일어난다.
  
  → 인덱스를 통해 레코드를 읽는 작업은 그냥 읽는 것 보다 비용이 많이든다.
  
  근데, 만약 필요한 데이터가 전부 인덱스에 있다면 이 과정을 생략한다. (커버링 인덱스)
인덱스 풀 스캔

인덱스를 사용하지만 인덱스의 처음부터 끝까지 모두 읽는 방식이다.

인덱스 크기가 테이블의 크기보다 작으므로 인덱스만으로 조건을 처리할 수 있을 때 테이블 전체를 읽는 것 보다 인덱스를 읽는 것이 더 효율적이고, 그럴 때 인덱스 풀 스캔 방식이 사용된다.
루스 인덱스 스캔

인덱스 레인지 스캔과 비슷하지만, 중간에 필요치 않은 인덱스 키 값은 무시하고 넘어가는 형태로 처리한다.

일반적으로 GROUP BY or 집합 함수 중 MIN(), MAX() 함수에 대해 최적화를 하는 경우에 사용한다.
인덱스 스킵 스캔

인덱스 스킵 스캔이 없다면 new_idx(A,B) 와 같은 인덱스가 있을 때 아래 쿼리는 인덱스를 효율적으로 사용할 수 없다.
```
SELECT * FROM employees WHERE B >= '2000-11-05';
```
하지만, 인덱스 스킵 스캔은 아래와 같은 방식으로 처리하여 인덱스를 활용할 수 있다.
```
// A 컬럼이 'type1', 'type2' 두 종류의 값만 가지고 있음
SELECT * FROM employees WHERE A = 'type1' AND B >= '2000-11-05';
SELECT * FROM employees WHERE A = 'type2' AND B >= '2000-11-05';
```
위와 같이 처리하므로 아래와 같은 단점들이 있다.
1. WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼(위에선 A 컬럼)의 유니크한 값의 개수가 적어야한다.
2. 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 한다. (커버링 인덱스)

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

여러 컬럼을 포함하는 인덱스를 말한다.

중요한 점은, 복합 인덱스 생성 시 순서에 따라 정렬 값이 결정된다.

예를 들어 index_1(A,B) 이러한 인덱스라면, A에 따라 정렬된 뒤 A 값이 같은 경우 B 컬럼으로 정렬된다.

→ 복합 인덱스 생성 시 컬럼의 순서가 매우 중요하다.

인덱스의 정렬 및 스캔 방향

인덱스 역순 스캔은 아래의 이유들 때문에 정순 스캔에 비해 28.9% 정도 시간이 더 걸린다.

페이지 잠금이 인덱스 정순 스캔에 적합한 구조이다.
페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조이다.

→ 자주 사용되는 정렬 순서로 인덱스를 생성하는 것이 효율적이다.

인덱스의 가용성과 효율성

복합 인덱스의 경우 카디널리티가 높은 컬럼을 앞으로 해서 생성하는 것이 좋다.
복합 인덱스의 첫 번째 컬럼이 없으면 인덱스 레인지 스캔 방식을 이용할 수 없어서 효율이 떨어진다.
≠, LIKE ‘%??’, 인덱스 컬럼이 변형된 후 비교, 데이터 타입이 다른 경우 등 몇 몇 경우에는 인덱스를 효율적으로 활용할 수 없다.

그 외 다양한 인덱스들

R-Tree 인덱스
- 공간 인덱스 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스이다.
B-Tree와 내부 메커니즘은 비슷하지만, R-Tree는 인덱스를 구성하는 컬럼의 값이 2차원 공간 값이다.

MySQL은 공간 데이터 타입, 공간 인덱스, 공간 연산 함수 를 제공한다.

각 도형을 감싸는 최소 크기의 사각형을 기준으로 인덱스가 생성된다.

위 그림 처럼 루트 노드가 여러 도형들을 감싸는 가장 큰 범위의 사각형, 리프 노드가 하나의 도형을 감싸는 최소 크기의 사각형 인 형태로 되어있다.

ST_Contains(), ST_Within() 함수로 공간 인덱스를 활용한 효율적인 공간 검색이 가능하다.
전문 검색 인덱스

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

불용어 처리, 어근 분석 의 과정을 거쳐 색인 작업이 수행된다.
함수 기반 인덱스

칼럼의 값을 변형해서 만들어진 값에 대한 인덱스이다.

구현 방법은 아래와 같이 2가지가 있다.
1. 가상 컬럼을 이용한 인덱스
2. 함수를 이용한 인덱스
예를 들어 first_name, last_name 이렇게 2개 컬럼이 있고 이를 이용하여 full_name이라는 가상 컬럼을 만들면 가상 컬럼을 이용한 인덱스를 만들 수 있다.
멀티 밸류 인덱스

하나의 레코드에 여러가지 값의 인덱스를 가질 수 있는 인덱스이다.

주로 JSON의 배열 타입의 필드에 저장된 원소들에 대한 인덱스를 구현하기 위해 사용된다.

클러스터링 인덱스란?

MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(PK 기준)들 끼리 묶어서 저장하는 것을 말한다.

그러므로 PK 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야하고, PK 기반 검색이 매우 빠르지만, 레코드 저장 및 PK의 변경이 느리다.

만약 PK가 없다면 아래와 같은 로직으로 대체 컬럼을 선택한다.

NOT NULL 옵션의 유니크 인덱스 중 첫 번째 인덱스를 클러스터링 키로 선택한다.
자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후 클러스터링 키로 선택한다.

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

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

유니크 인덱스

유니크 인덱스는 같은 값이 2개 이상 저장될 수 없는 인덱스를 말한다.

하지만 NULL은 특정 값이 아니므로 2개 이상 저장될 수 있다.

PK는 기본적으로 NOT NULL + 유니크 인덱스이다.

유니크 VS 일반 인덱스

인덱스 읽기
- 유니크 인덱스는 데이터가 고유하므로 일치하는 레코드를 하나 찾으면 바로 검색이 끝난다.
- 반면, 일반 인덱스는 같은 인덱스 키 값인 레코드가 여러개일 수 있어서 여러 값중 어떤 것이 만족하는 레코드인지 찾는 연산이 필요하다.
- 하지만 이는 디스크 접근이 아닌 메모리 상에서 이뤄지는 작업이므로 성능에 영향이 거의 없다.
- 그러므로 유니크 vs 일반 인덱스는 같은 수의 레코드를 읽을 때 성능 차이가 거의 없다.
  - 물론 일반 인덱스는 중복된 데이터 때문에 더 많은 데이터를 읽을 수 있다.
- 결론: 같은 수의 레코드를 읽을 때 두 인덱스의 읽기 성능은 차이가 거의 없다.
인덱스 쓰기
- 유니크 인덱스의 키 값을 쓸 때는 중복 체크 과정이 필요해서 일반 인덱스 보다 느리다.
- 근데, MySQL에선 유니크 인덱스에서 중복 체크 시 읽기 잠금을 사용하고, 쓰기 시 쓰기 잠금을 사용하므로 데드락이 자주 발생한다.
- 또한, 유니크 인덱스는 저장 및 변경 시 중복 체크를 해야하므로 작업을 버퍼링하지 못한다.

→ 유일성이 꼭 보장되어야 하는 컬럼에 대해선 유니크 인덱스를 생성하되, 그렇지 않다면 일반 인덱스 생성을 고려하자.

왜래키

왜래키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스가 생성된다.

// 커넥션 1 시작
// 커넥션 1 부모 테이블 컬럼 업데이트
UPDATE users SET name = 'change' WHERE id = 1;
// 커넥션 2 시작
// 커넥션 2 자식 테이블 컬럼 업데이트
UPDATE posts SET user_id = 1 WHERE id = 100;
// 커넥션 1 롤백
// 커넥션 2 완료

InnoDB의 왜래키 관리에는 두 가지 특징이 있음

테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 대기가 발생한다. → 위와 같이 자식 테이블의 왜래키 변경(UPDATE posts SET user_id = 1 WHERE id = 100;)은 부모 테이블의 확인이 필요한데, 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때 까지 기다린다. (테이블이 변경되며 잠금 대기가 발생했다.)
왜래키가 연관되지 않은 컬럼의 변경은 최대한 잠금 대기를 발생시키지 않는다. → 자식 테이블의 왜래키가 아닌 컬럼의 변경(UPDATE posts SET user_id = 1 WHERE id = 100;)은 왜래키로 인한 잠금 확장이 발생하지 않는다. (왜래키가 연관되지 않아서 잠금 대기가 발생하지 않았다.)

정훈희

DB를 사랑하는 백엔드 개발자입니다. 열심히 공부하고 열심히 기록합니다.

[Real MySQL 8장] 인덱스

Real MySQL

디스크 I/O

SSD vs HDD

랜덤 I/O vs 순차 I/O

B-Tree 인덱스

인덱스란?

B-Tree 인덱스

B-Tree 인덱스 키 추가, 삭제, 변경

B-Tree 인덱스 키 검색

B-Tree 인덱스 사용에 영향을 미치는 요소

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

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

인덱스의 정렬 및 스캔 방향

인덱스의 가용성과 효율성

그 외 다양한 인덱스들

클러스터링 인덱스란?

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

유니크 인덱스

유니크 VS 일반 인덱스

왜래키

[Real MySQL 5장] 트랜잭션과 잠금

[Real MySQL 9장] 옵티마이저와 힌트

0개의 댓글