쉽게 이해하는 MySQL 인덱스 한판 정리

Real MySQL 책을 읽으며 활용하기 위해 간략히 정리한 내용입니다.
더 자세한 내용을 알고 싶다면 책 또는 구글 검색을 통해 찾아주시면 감사하겠습니다.

용어 정리

페이지

디스크와 메모리(버퍼풀)에 데이터를 읽고 쓰는 기본 단위를 말한다.

데이터가 저장되는 물리적 단위를 말하며

InnoDB에서 데이터는 데이터파일에 페이지 단위로 저장된다.

인덱스를 포함해 PK(클러스터 인덱스), 테이블등은 모두 페이지 단위로 관리된다.

데이터파일

InnoDB에서 실제 데이터가 저장되는 파일을 말한다.

여러개의 페이지로 나눠 데이터를 저장하며 페이지 단위로 관리한다.

(데이터 파일은 페이지의 집합 → 페이지를 담고 있는 물리적 파일)

바이너리 로그 버퍼

DB 서버에 DDL과 DML을 통해 데이터, 테이블 변경점에 대한 이벤트를 기록하는

이진파일을 바이너리 로그라 부르고 해당 공간을 버퍼라 부른다.

InnoDB 로그 버퍼

InnoDB 스토리지 엔진의 데이터 파일이나

인덱스 정보를 저장하기 위한 캐시 메모리 공간

1. 인덱스란

추가적인 쓰기 작업과 저장 공간을 활용해 DB의 테이블 검색 속도를 향상시키는 자료구조

색인과 같은 개념
레코드가 저장된 주소의 key, value로 인덱스 생성 (이때 주어진 순서대로 정렬하여 저장)
정렬이 된 상태는 값의 추가, 수정, 삭제보다 읽기의 성능이 훨씬 좋은데 이를 통해 알 수 있는 것은 데이터 저장의 성능을 희생하고 조회 속도를 높이는 기능을 한다라는 점

1-1. DB 성능 튜닝

디스크 I/O를 줄이느냐가 관건일 경우가 많다.

DB 서버에서는 순차 I/O 작업보다 랜덤 I/O 작업을 통해 데이터를 읽고 쓰는 작업이 대부분이다.

쿼리 튜닝이란 말의 의미는 대게 랜덤 I/O 자체를 줄여주는게 목적

2-1. B-Tree 알고리즘

DB의 데이터 저장방식 알고리즘중 하나로 가장 일반적으로 사용
컬럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱

2-1-1. B-Tree 인덱스

값을 변형시키지 않고 인덱스 구조체내에서는 항상 정렬된 상태로 유지
트리 구조로 루트 노드 → 브랜치 노드 → 리프 노드 → 데이터파일로 구성
- 리프 노드는 실제 데이터가 저장된 데이터 레코드를 찾기 위한 주소값을 보유
리프 노드의 인덱스 키 값들은 정렬(각 노드들)되어 있지만 데이터 파일 레코드는 정렬되어 있지 않다.
InnoDB 스토리지 엔진 사용 테이블은 PK가 rowid 역할을 하기 때문에 논리적 주소의 성향을 가진다.

B-Tree 리프노드와 테이블 데이터 레코드 (InnoDB)

2-1-2. 인덱스와 일반 테이블 조회 차이

InnoDB 테이블에서 인덱스를 통해 데이터가 없을시 레코드를 읽을 때는 데이터 파일을 바로 찾아가지 못한다.

인덱스에 저장된 리프 노드의 PK(레코드주소)를 가지고 데이터 파일에 PK 인덱스 키를 검색 (순차 I/O 발생)
데이터 파일내 리프노드 데이터 페이지에서 레코드 검색 (랜덤 I/O 발생)

3. 인덱스키 추가, 삭제, 변경, 검색

3-1. 인덱스 키 추가

B-Tree에 저장시 저장될 키 값을 이용해 위치 검색 필요
위치 결정시 B-Tree의 리프 노드에 저장 (새로운 키값이 인덱스 즉시 저장 또는 아닐 경우)
1. 리프 노드에 공간이 남아 있는 경우 인덱스 즉시 저장
2. 리프 노드 공간이 꽉찬 경우 리프노드 분리하여 상위 브랜치노드까지 처리 (쓰기 작업 추가 발생)

인덱스 키 추가시 알아둬야 할 정보

테이블의 인덱스 키 추가 작업 비용 = 인덱스 수 * 1.5(인덱스 추가 비용) + 1(레코드 추가 비용)
- 메모리, CPU의 비용이 아니라 디스크로부터 인덱스 페이지 읽기,쓰기 비용
지연하여 처리도 가능하나 유니크, PK 키의 경우는 즉시 처리

3-2. 인덱스 키 삭제

B-Tree의 리프 노드를 검색
삭제 마킹 처리 (물리적 삭제가 아니기에 디스크 I/O 발생)

3-3. 인덱스 키 변경

B-Tree 키(인덱스 키) 값 삭제
B-Tree 키(인덱스 키) 추가

3-4. 인덱스 키 검색

조건절 100% 일치(=동등 연산) 또는 값의 앞부분 일치(like 'demo%') 사용 가능
< > 대소 비교 부등호 사용 가능
변형된 가해지는 연산, 함수(max, avg, count)등 비교되는 경우는 B-Tree 인덱스 효과 x
B-Tree 인덱스 컬럼 크기, 레코드 수, 유니크 인덱스 갯수가 검색이나 변경 작업 성능에 영향

4. B-Tree 인덱스 사용에 영향 요소 목록

4-1. 인덱스 키 값의 크기

B-Tree는 인덱스의 페이지와 키값의 크기에 따라 결정
- innodb_page_size 라는 시스템 변수를 이용해 설정 (default 16kb)
인덱스 구성 키 값이 커지면(길이 증가) 디스크로부터 읽어야 하는 횟수 증가 → 메모리 효율 감소 → 느려짐
- 인덱스 크기 증가 → 메모리 캐시 레코드 공간 부족 → 효율성 감소
- 효율성이 떨어진다 → 한번에 읽을 수 있는 디스크 수가 감소 (가급적 인덱스의 키값의 크기는 작게)

4-2. Cardinality (선택도)

인덱스의 유니크한 값의 수량(전체 인덱스 키값 100개중 유니크한게 10개면 선택도 10)
- Cardinality가 높을수록 검색 대상이 감소하고 빠른 처리 가능 (중복이 많이 없어야 효율성 증가)

1
2
3
4
5
6
7
8
9


select * from test where ab='1' and bc='2'; -- 전체 레코드 10,000건

-- CASE 1: ab컬럼의 유니크한 값 10개 (선택도 10 -> 평균 1000건 조회)
-- ab = '1'이 단 1개 조회된다면 1000 - 1 = 999개의 불필요한 조회
 
-- CASE 2: ab컬럼의 유니크한 값 1000개 (선택도 1000 -> 평균 10건 조회)
-- ab = '1'이 단 1개 조회된다면 10 - 1 = 9개의 불필요한 조회 (우세!)

-- 즉 유니크한 값이 많을 수록 불필요한 조회를 줄여서 성능적으로 유리

4-3. 읽어야하는 레코드 건수

DBMS 옵티마이저는 인덱스를 통해 레코드 1건을 조회시 일반 테이블 조회 보다 4~5배 정도 비용이 더 많이 드는 것으로 예측
- 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않는 것이 효율적
  - Ex) 100만건 레코드 기준 25만건 이하 조회 → 인덱스 효율적(25%), 50만 → 테이블 풀 스캔 비효율(50%)
  - 옵티마이저가 손익분기점을 넘어서면 인덱스 무시하고 직접 테이블 풀스캔

5. B-Tree 인덱스를 통한 데이터 읽기 방식

5-1. 인덱스 레인지 스캔

대표적 접근 방식으로 3가지 방식중 가장 빠른 방식
검색해야 할 인덱스의 범위 지정시 사용
2가지 경우
- 실제 인덱스만 읽는 경우 → 커버링 인덱스 (레코드 접근 x)
  - mysql> show status like ‘handler_%’;
    - handler_read_key: 인덱스 탐색 횟수
    - handler_read_next, handler_read_prev: 인덱스 스캔
- 인덱스 리프 노드를 스캔하며 실제 레코드에 접근해야하는 경우 (한 건당 랜덤 I/O)

5-2. 인덱스 풀 스캔

인덱스의 처음부터 끝까지 전체를 읽는 방식

쿼리 where 조건에 컬럼이 인덱스의 첫번째 컬럼이 아닐 경우 사용

1
2
3
4
5
6
7


-- 인덱스 설정 (a,b) 쿼리 조건절은(b,c)로 검색시
CREATE INDEX idx_phone_with_created_at ON profile(phone, created_at);

-- 인덱스 풀 스캔 (bad)
select * from profile where created_at >= now(); 
-- 인덱스 레인지 스캔 (good)
select * from profile where phone = 1023456789 and created_at >= now();

인덱스 리프 노드의 제일 앞 또는 제일 뒤 이동 → 링크드 리스트를 따라 풀 스캔 (비효율적 사용)
효율성 인덱스 레인지 스캔 > 인덱스 풀 스캔 > 테이블 풀 스캔

5-3. 루스 인덱스 스캔

중간에 필요치 않은 인덱스 키값을 skip하고 처리하면서 띄엄띄엄 인덱스를 읽는 것
인덱스 레인지 스캔과 비슷하게 동작
- group by, 집계 함수등에 최적화 용도 사용

5-4. 인덱스 스킵 스캔

옵티마이저가 인덱스 순서를 보장하지 않아도 인덱스 스캔 최적화 해주는 기능(mysql8.0 이상)
- (a, b) 인덱스 설정 → (b)만 검색시 인덱스 동작
optimizer_switch를 통해 활성화 (type: index → range)
조건 필요
1. where 조건절에 조건이 없는 인덱스의 선행 컬럼이 유니크 값이 적어야 한다.
  1. 카디널리티가 낮다 → 중복 많다 (Ex: 주민번호 → 카디널리티가 높다, 성별 → 카디널리티가 낮다)
2. 커버링 인덱스가 가능해야 한다. (실제 인덱스만 읽는 경우)

6. B-Tree 인덱스의 정렬 및 스캔 방향

인덱스 생성시 인덱스 키 값은 오름차순, 내림차순 정렬되어 저장
- 정렬된 순으로 읽지는 않음 → 옵티마이저의 실시간 실행계획에 따라 결정(정순, 역순)
  - 인덱스는 항상 오름차순 정렬이지만 옵티마이저는 반대의 최대값부터 조회시 desc으로 동작
  - order by, min, max등 최적화시 인덱스 조회 방향을 전환해 문제 해결
순서를 혼합한 인덱스 생성 가능
- create index ix_teamname_userscore on employees (team_name asc, user_score desc);
역순 정렬 쿼리가 정순 정렬 쿼리보다 29%정도 더 시간적 비효율
- 페이지 잠금이 정순 스캔에 더 적합
- 페이지내에 인덱스 레코드는 단방향으로 연결되어진 구조
쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 병목 현상 완화에 도움

6-1. 내림차순 인덱스

1
2
3
4


-- 가장 높은 dept_no
select * from departments order by dept_no asc limit 8, 1; -- 마지막에서 첫번째
-- 가장 낮은 dept_no
select * from departments order by dept_no desc limit 8, 1; -- 마지막에서 첫번째

InnoDB의 스토리지 엔진은 정순, 역순 스캔은 double-linked-list로 이루어져 이동
- 역순, 정순 차이 발생 (정순이 더 빠름)
  - 페이지 잠금이 인덱스 정순 스캔에 더 적합
  - 페이지내 인덱스 레코드가 단방향으로 연결된 구조

7. B-Tree 인덱스의 가용성과 효율성

7-1. 비교 조건의 종류와 효율성

where절 인덱스 2개 이상 비교시 동등 조건(=)을 가장 왼쪽에 선언해야 효율성 증가
- 범위를 좁힐 수 있는 조건: 작업 범위 결정 조건
- 단순 거름종이 역할만 하는 조건: 필터링 조건

7-2. 가용성

하나의 컬럼 검색시 왼쪽값 없으면 인덱스 레인지 스캔 방식 검색 불가

1
2
3
4


-- good (왼쪽 값이 정해져 있기에 일치 레코드 우선 조회 가능)
select *** from employees where first_name like 'mer%'
-- bad (왼쪽 값이 정해져 있지 않아 비효율적)
select *** from employees where first_name like '%mer'

(a, b) 인덱스를 생성 후 b만 검색시에도 인덱스 레인지 스캔 방식 검색 불가
이는 where, group by, order by에도 똑같이 적용

7-2-1. 작업 범위 결정 조건 (부합)

동등 비교 (= 또는 in)
크기 비교 (> 또는 <)
like로 좌측 일치 패턴 (like ‘축구%’)

7-2-2. 작업 범위 결정 조건 (미부합)

not equal 비교 (<>, not 연산)
like로 우측 일치 패턴 (like ‘%축구’)
함수나 연산자를 사용해 비교된 경우 (substing(column,1,1))
데이터 타입이 다른 비교

8. 그외 인덱스

8-1. 함수 기반 인덱스

MySQL 8버전 부터는 함수 기반 인덱스를 지원하였고 가상 컬럼, 함수 2가지로 구분

8-1-1. 가상 컬럼 기반

2개의 컬럼을 합쳐서 가상 컬럼으로 인덱스를 생성
- Ex: first_name, last_name = full_name
실제 테이블의 구조가 변경되는 단점

8-1-2. 함수 기반

컬럼의 값을 변형한 값으로 인덱스 구축시 사용
- 계산 과정 차이외 구조 및 유지 방법은 B-Tree 인덱스와 동일

8-2. 멀티 밸류 인덱스

모든 인덱스는 레코드와 1:1 관계
멀티 밸류 인덱스는 하나의 레코드가 여러개 인덱스 키값을 가지는 형태
RDBMS의 JSON 데이터 타입 지원 후 JSON 필드 내 프로퍼티 값 인덱스 구축 가능
- member of()
- json_contains()
- json_overlaps()

8-3. 클러스터링 인덱스

테이블의 PK 기준 비슷한 레코드들끼리 묶어 물리적으로 정렬하여 저장하는 형태
InnoDB 스토리지 엔진에서만 지원
PK 키값에 의해 레코드 저장 위치 결정 → PK 변경시 물리적 저장 위치 변경 → PK 의존도 많음
- 테이블 레코드의 저장 방식 개념

장점

PK로 검색시 처리 성능 빠름

단점

PK를 변경할때 레코드를 추가 삭제하는 작업 비용이 발생하여 성능 저하 (물리적 정렬 때문)
- 보통 온라인 서비스의 쓰기와 읽기 트랜잭션 비율이 2:8, 1:9이기에 읽기 성능을 유지 권장

8-4. 유니크 인덱스

꼭 유일성이 보장되어야 하는 컬럼에만 사용 권장
중복값이 많은 일반 세컨더리 인덱스는 읽어야할 레코드가 많기에 느린것뿐
- 유니크 인덱스 : unique index {인덱스명} (컬럼명)
- 일반 인덱스 : index {인덱스명} (컬럼명)

8-5. 외래키 인덱스

외래키 설정시 자동 연관 테이블의 컬럼에 인덱스 생성
- 테이블 변경이 발생하는 경우만 잠금(대기) 발생
  
  📒 Ex)
  부서 정보와 사원 정보를 저장하는 DB에 부서 테이블과
  사원 테이블 간 부서 번호가 외래키로 설정되어 있는 경우,
  부서 정보 변경시 해당 부서 번호와 매핑되어 있는 모든 사원 정보가 변경되어야 한다.
  이러한 변경 작업 수행시, 부서 테이블과 사원 테이블 모두에서 잠금이 발생 가능성이 높다.
- 외래키와 연관되지 않은 컬럼의 변경점은 잠금(대기) 미발생

9. 랜덤 I/O과 순차 I/O의 차이

디스크 기록시 순차 I/O는 3개의 페이지를 디스크에 기록하기 위해 1번 시스템 콜을 요청하지만
랜덤 I/O는 3개의 페이지를 기록하려면 3번의 시스템 콜을 요청해야한다. (헤더의 이동 순차 → 1, 랜덤 → 3)

데이터를 쓰고 읽는데 걸리는 시간은 디스크 헤더를 움직여 읽고 쓸 위치로 옮기는 단계에서 결정된다.

성능은 헤더의 위치 이동없이 얼마나 많은 데이터를 한번에 기록하느냐에 의해서 결정

그렇기 때문에 랜덤 I/O의 부하가 더 크다. 보통 db의 작업은 대부분이 쓰고 읽는 작업이기에

바이너리 로그 버퍼 or InnoDB 로그 버퍼등이 내장되어 있다.

인덱스 레인지 스캔: 순차 IO
풀 테이블 스캔: 랜덤 I/O

용어 정리#

1. 인덱스란#

1-1. DB 성능 튜닝#

2-1. B-Tree 알고리즘#

2-1-1. B-Tree 인덱스#

2-1-2. 인덱스와 일반 테이블 조회 차이#

3. 인덱스키 추가, 삭제, 변경, 검색#

3-1. 인덱스 키 추가#

3-2. 인덱스 키 삭제#

3-3. 인덱스 키 변경#

3-4. 인덱스 키 검색#

4. B-Tree 인덱스 사용에 영향 요소 목록#

4-1. 인덱스 키 값의 크기#

4-2. Cardinality (선택도)#

4-3. 읽어야하는 레코드 건수#

5. B-Tree 인덱스를 통한 데이터 읽기 방식#

5-1. 인덱스 레인지 스캔#

5-2. 인덱스 풀 스캔#

5-3. 루스 인덱스 스캔#

5-4. 인덱스 스킵 스캔#

6. B-Tree 인덱스의 정렬 및 스캔 방향#

6-1. 내림차순 인덱스#

7. B-Tree 인덱스의 가용성과 효율성#

7-1. 비교 조건의 종류와 효율성#

7-2. 가용성#

8. 그외 인덱스#

8-1. 함수 기반 인덱스#

8-2. 멀티 밸류 인덱스#

8-3. 클러스터링 인덱스#

8-4. 유니크 인덱스#

8-5. 외래키 인덱스#

9. 랜덤 I/O과 순차 I/O의 차이#

References#