티스토리 뷰

카테고리 없음

MySQL의 B-Tree 인덱스

sanook 2023. 6. 18. 22:15

인덱스

8.1 디스크 읽기 방식

데이터 저장 매체는 컴퓨터에서 가장 느리고 데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건

8.1.1 하드 디스크 드라이브(HDD)와 솔리드 스테이트 드라이브(SSD)

전자식 장치

전기나 전자의 성질을 이용하여 정보를 처리하거나 저장하는 장치
CPU, 메모리

기계식 장치

물리적인 움직임을 이용하여 정보를 처리하거나 저장하는 장치
하드 디스크 드라이브
- 원판(데이터 저장용 플래터)
  - 플래터는 여러 개의 '트랙'으로 나눠져 있으며, 이 트랙은 더 작은 '섹터'로 나눠져 있습니다. 데이터는 이 섹터들에 저장되며, 이는 HDD가 데이터를 읽고 쓸 수 있게 하는 기본 단위입니다.
  - 원판은 지속적으로 고속으로 회전하며, 데이터를 읽고 쓰기 위해 'read/write'라는 장치가 이 원판 위를 따라 움직입니다. 이 헤드는 플래터가 회전하는 동안 초미세 간격을 유지하여 원판 위를 '날아다니며' 각 트랙과 섹터에서 정보를 읽거나 씁니다.
  - 플래터가 회전하는 속도는 HDD의 성능에 큰 영향을 미칩니다. 일반적으로, 플래터가 빠르게 회전할수록 데이터를 더 빠르게 읽고 쓸 수 있습니다

데이터베이스 서버에서는 항상 디스크 장치가 병목이 됨

HDD 대체를 위해 SSD를 사용하고 기존 인터페이스를 지원함으로 내장 디스크등 스토리지에 사용 가능
원판 대신 플래시 메모리를 사용
- 회전 필요 없어 빠름
- 전원이 공급되지 않아도 데이터가 삭제 되지 않음

8.1.2 랜덤 I/O와 순차 I/O

랜덤 I/O

하드 디스크 드라이브의 플래터(원판)을 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동 시킨 다음 데이터를 읽는 것
- 데이터가 디스크의 여러 위치에서 비연속적으로 액세스되고 디스크 헤드는 각 I/O 작업 사이에 서로 다른 위치로 '시킹(seeking)’
순차 I/O(디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽음)에서는 SSD가 HDD 보다 조금 빠르거나 비슷하지만 랜덤 I/O에서는 SSD가 압도적으로 빠르다
데이터베이스 서버에서 순차 I/O보다 랜덤 I/O를 통해 작은 데이터를 read/write하고 더 많은 트랜잭션을 처리 함으로 SSD가 DBMS용 스토리지에 최적
사용처 : 개별 레코드에 대한 CRUD, 인덱스를 통한 조회
예 : 3개의 페이지를 디스크에 기록하기 위해 3번 시스템 콜 (디스크 헤드 3번 움직임)

순차 I/O

랜덤 I/O 와 작업 과정은 동일
데이터가 디스크에서 연속적으로, 또는 '순차적'으로 액세스되는 방식
디스크 헤드는 특정 시작점에서 데이터를 읽기 시작하여 연속적으로 다음 섹터로 이동하며 데이터를 읽음
디스크 헤드의 '시킹(seeking)'이 거의 필요하지 않기 때문에, 순차 I/O는 일반적으로 랜덤 I/O보다 빠르고 효율적
시킹
- 운영체제는 디스크 컨트롤러에게 필요한 데이터를 어디에 쓸 것인지를 알려주고 디스크 컨트롤러는 이 정보를 바탕으로 디스크 헤드를 움직여 적절한 위치로 이동
사용처 : 큰 범위의 데이터를 스캔, 데이터베이스의 백업과 복구 작업
예 : 3개의 페이지를 디스크에 기록하기 위해 1번 시스템 콜 (디스크 헤드 1번 움직임)

페이지

가상 메모리 시스템에서 데이터를 관리하는 데 사용되는 고정된 크기의 블록
주기억장치와 보조기억장치(하드 디스크 같은) 간의 데이터 전송 단위
페이지 크기는 시스템에 따라 다르지만 일반적으로 수 KB에서 수 MB의 범위

시스템 콜

운영체제에게 특정 기능을 수행하도록 요청하는 프로그램의 방법
- 예를 들어, 파일을 생성하거나 네트워크 소켓을 열거나 데이터를 읽거나 쓰는 등의 작업을 수행하려면, 프로그램은 운영체제에게 해당 작업을 수행하도록 요청

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

여러번 read / write 하는 랜덤 I/O가 부하가 큼
원판을 가지지 않는 SSD 드라이브에서도 랜덤 I/O가 Throughput이 떨어짐

⇒ MySQL 서버에는 작은 데이터 read / write 빈번함을 대비하기 위해 그룹 커밋 / 바이너리 로그 버퍼 / InnoDB 로그 버퍼 등의 기능이 내장

쿼리 튜닝 방법

랜덤 I/O를 순차 I/O로 바꾸는 방법이 많지 않고 쿼리를 처리시 꼭 필요한 데이터만 읽도록 쿼리를 개선 한다 == 랜덤 I/O 자체를 줄인다.

8.2 인덱스

DBMS에서 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 많이 걸림으로 N개의 칼럼 값과 해당 레코드가 저장된 주소를 Key - Value pair 삼아 인덱스를 만들어 둠
칼럼의 값을 주어진 순서대로 미리 정렬해서 보관
프로그래밍 언어와 비교
- SortedList는 DBMS의 인덱스와 같은 구조로 저장되는 값을 항상 정렬된 상태로 유지하기 위해 저장하는 과정이 복잡하고 느리지만 이미 정렬되어 있어 빨리 원하는 값을 찾아 올 수 있음
- ↔ ArrayList는 값을 저장되는 순서 그대로 유지
인덱스 는 이와 같이 데이터의 저장(Insert, Update, Delete) 성능을 희생하는 대신 데이터의 읽기 속도를 높이는 기능
인덱스 추가시 데이터 저장 속도 희생과 읽기 속도 빠름의 트레이드 오프 고려 필요
SELECT ~ WHERE에 사용되는 컬럼 모두 인덱스 생성시 데이터 저장 성능 떨어지고 인덱스의 크기가 비대해져 오히려 역효과

역할별 구분

프라이머리 키

레코드를 대표하는 칼럼의 값으로 만들어진 인덱스
해당 레코드를 식별 가능해 짐으로 식별자
NULL과 중복 허용 하지 않음

보조 키 (세컨더리 인덱스)

PK 제외한 인덱스
대체키(유닉크 인덱스) : PK와 성격이 비슷하고 대체 가능
- 후보키가 두개 이상일 경우 그 중에서 어느 하나를 기본키로 지정하고 남은 후보키

데이터 저장 방식(알고리즘)별 구분

B-Tree

칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱
R-Tree
- 위치 기반 검색을 지원하기 위함, B-Tree의 응용

Hash

칼럼의 값을 해시값으로 계산해 인덱싱
매우 빠름
값을 변형해서 인덱싱함으로 prefix 일치와 같이 값의 일부 만 검색하거나 범위 검색 사용 불가
메모리 기반의 데이터베이스에서 많이 사용

중복 허용 여부 구분

→ 옵티마이저에게 중요한 문제

유니크 인덱스

동등 조건(equal, =) 검색시 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다고 옵티마이저에게 알려줌

유니크 하지 않은 인덱스

기능별 분류

전문 검색용 / 공간 검색용

8.3 B-Tree 인덱스

칼럼의 원래 값을 변형시키지 않고 (별도로 값의 앞부분만 잘라서 관리) 인덱스 구조체 내에서는

항상 정렬된 상태 유지

8.3.1 구조 및 특성

트리 구조의 최상위의 하나의 Root node / Branch node / Lead node 순으로 존재한다.
인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주소 값을 가지고 있다.
인덱스의 키 값은 모두 정렬 되어 있지만 데이터 파일 레코드는 정렬 되어 있지 않고 임의의 순서
레코드가 전혀 삭제 / 변경 되지 않고 INSERT만 수행되면 INSERT 된 순서대로 저장이지만 중간에 레코드가 삭제되어 빈 공간이 생기면 그 다음 INSERT는 가능한 삭제된 공간을 재활용하도록 DBMS가 설계됨으로 항상 INSERT된 순서대로 저장되는 것은 아니다.
- InnoDB 테이블에서 레코드는 클러스터되어 디스크에 저장됨으로 PK 순서대로 정렬되어 저장

인덱스는 테이블의 키 칼럼 만 가지고 있음으로 나머지 칼럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 함
인덱스의 리프 노드 는 데이터 파일에 저장된 레코드 주소를 가진다.
- 다만, MyISAM 의 경우 루트/브랜치/리프로 나누어 지지 않고 하나의 테이블 형태이며 테이블 생성 옵션에 따라 레코드가 테이블에 INSERT된 순번이거나 데이터 파일 내의 위치(Offset)
InnoDB 스토리지 엔진을 사용하는 테이블에서는 PK가 ROWID 역할을 한다.
- PK를 주소로 사용함으로 논리적인 주소를 가진다고 본다.
- cf ) MyISAM은 세컨더리 인덱스가 물리적인 주소를 가짐
InnoDB 스토리지 엔진에서는 인덱스에 저장되어 있는 PK 값을 이용해 → PK 인덱스를 한 번 더 검색하고→ PK 인덱스의 리프 페이지에 저장되어 있는 레코드를 읽는다.
- 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 PK를 저장하고 있는 B-Tree를 다시 검색해야 한다.

8.3.2 B-Tree 인덱스 키 추가 및 삭제

8.3.2.1 인덱스 키 추가

B-Tree에 저장될 때 저장될 키 값을 이용해 B-Tree상의 적절한 위치를 검색해야 하고 이때, 테이블의 스토리지 엔진에 따라 새로운 키 값이 즉시 인덱스에 저장될 수도 있고 그렇지 않을 수도 있다.

저장될 위치 결정시 레코드의 키 값과 레코드의 주소 정보를 B-Tree의 리프 노드에 저장!

B-Tree의 새로운 키 추가 작업이 비용이 많이 드는 이유? 리프 노드가 꽉 차서 저장 불가시 분리(split)되는데 이 과정이 상위 브랜치 노드 까지 처리 범위가 넓어짐

INSERT, UPDATE시 어떤 영향을 받는가? 중요한 점은 테이블의 칼럼 수, 칼럼의 크기, 인덱스의 칼럼의 특성 확인

테이블에 레코드 추가 작업 비용 1 : 테이블에 인덱스에 키를 추가하는 작업 비용 1.5

예: 일반적으로 테이블에 모든 인덱스가 B-tree고 3개, 작업 테이블에 인덱스가 하나도 없음

→ 작업비용 1, 3개인 경우 5.5 (1.5*3 + 1)

→ ⭐ 중요한 것은 대부분의 비용이 메모리 / CPU가 아닌 디스크로부터 인덱스 페이지를 읽고/쓰기 비용

MyIAMS/MEMORY vs InnoDB

MyIAMS/MEMORY

INSERT 문이 실행시 즉시 새로운 키 값을 B-Tree 인덱스에 변경

InnoDB

필요시 인덱스 키 추가 작업을 지연 시켜 나중에 처리 가능(체인지 버퍼)
PK나 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree에 추가/삭제

8.3.2.2 인덱스 키 삭제

해당 키 값이 저장된 B-Tree의 리프 노트를 찾아 삭제 마크

이때, 디스크 쓰기로 디스크 I/O
삭제 마킹된 인덱스 키 공간은 그대로 방치 / 재활용
Inno DB에서는 이 작업 또한 버퍼링 되어 지연 처리 가능 (but MySQL 서버가 내부 처리함_

8.3.2.3 인덱스 키 변경

인덱스의 키 값에 따라 저장될 리프 노드의 위치가 결정됨으로 B-Tree 키 값 변경시 단순히 인덱스상 키 값 변경 불가

B-Tree의 변경 작업은 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가(위의 설명 절차대로 처리)

8.3.2.4 인덱스 키 검색

트리 탐색

인덱스를 검색하는 작업은 B-Tree의 루트노드 → 브랜치 노드 → 최종 리프 노드 이동하며 비교 작업 수행

SELECT, UPDATE나 DELETE를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 하는 경우 사용

이때, 인덱스 관리에 따른 추가 비용까지 감당하며 인덱스를 구축하는 이유는 빠른 검색

100% 일치 / 값의 일부분(Left-most part) 만 일치하는 경우 사용 가능

부동호(”<,>”) 비교 조건에서 사용 가능
인덱스 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스 사용 불가

⭐ 인덱스의 키 값에 변경(함수/연산)이 가해진 후 비교 되면 B-Tree의 빠른 검색 기능을 사용할 수 없음

변형된 값은 B-Tree 인덱스에 존재하는 값이 아님

InnoDB 스토리지 엔진에서 인덱스

InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현 되어 있다. → UPDATE / DELETE 수행시 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다 (심지어 테이블의 모든 레코드도….)
1. 레코드 잠금(Record Locking) : 레코드 잠금은 특정 레코드에 대한 동시 접근을 제한하는 기능입니다. 레코드 잠금을 통해 여러 트랜잭션에서 동시에 같은 레코드를 수정하려는 경우 데이터 무결성을 보장할 수 있습니다. InnoDB에서는 각 레코드에 대한 공유(S) 잠금과 배타적(X) 잠금을 지원합니다. 공유 잠금은 다른 트랜잭션에서 레코드를 읽을 수 있지만 변경할 수는 없게 하는 반면, 배타적 잠금은 다른 트랜잭션에서 해당 레코드를 읽거나 쓰는 것을 모두 방지합니다.
2. 넥스트 키 락(Next-Key Locking) : 넥스트 키 락은 레코드 잠금과 갭 잠금의 결합입니다. 이는 현재 레코드와 그 다음 레코드 사이의 갭까지 잠그는 것입니다. 이러한 방식은 Phantom Reads라 불리는 문제를 해결하기 위해 사용됩니다. Phantom Reads는 한 트랜잭션 내에서 같은 쿼리를 두 번 실행했을 때 나타나는 현상으로, 첫 번째 쿼리에서는 없었지만 두 번째 쿼리에서 새로운 레코드가 나타나는 경우를 말합니다. 넥스트 키 락을 통해 해당 범위에 새로운 레코드가 삽입되는 것을 방지함으로써 Phantom Reads를 방지할 수 있습니다.
3. 갭 락(Gap Locking) : 갭 락은 레코드가 아닌 레코드 사이의 '갭'에 대한 잠금을 의미합니다. 갭 락을 사용하면 트랜잭션은 겹치지 않는 레코드 간의 갭에 대해 잠금을 얻을 수 있습니다. 이렇게 하면 해당 범위 안에는 새로운 레코드가 삽입되지 않도록 할 수 있습니다. 이는 넥스트 키 락과 함께 Phantom Reads 문제를 해결하는 데 사용됩니다.
이런 방식으로 InnoDB는 여러 사용자가 동시에 데이터베이스를 사용하더라도 데이터의 일관성과 무결성을 보장합니다.

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

B-Tree 인덱스의 검색 / 변경 성능 영향 요소

인덱스 구성 칼럼의 크기, 레코드의 건수, 유니크 인덱스 키 값

8.3.3.1 인덱스 키 값의 크기

페이지(Page), 블록(Block)

Inno 스토리지 엔진에서 디스크에 데이터를 CRUD 하는 기본 단위
버퍼 풀에서 데이터를 버퍼링 하는 기본 단위
인덱스도 페이지 단위로 관리
루트, 브랜치, 리프 노드를 구분하는 기준

B-Tree의 자식 노드

⭐ 이진 아님, 자식 노드의 개수 가변적인 구조
인덱스의 페이지 크기와 키 값의 크기에 따라 자식 노드 결정
default : 16KB (innodb_page_size에 따라 변경 가능)
- 자식 노드 주소(6바이트~12바이트)의 복잡적인 정보가 담긴 영역
하나의 인덱스 페이지에 몇 개의 키를 저장 가능?
- - 16*1024 / (16+12) = 585개 저장 가능
    - 최종적으로 자식 노드를 585개 가지는 B-Tree가 됨
- 인덱스 값이 커지면?
  - 키 값이 2배인 32 바이트 16 * 1024 / (32+12) = 372 개 저장 가능
→ SELECT 쿼리로 레코드 500 개를 읽어야 한다면 전자는 인덱스 페이지를 한 번으로 해결 / 후자는 2번
⇒ 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려짐
인덱스 키 값이 길어진다 == 전체적인 인덱스의 크기가 커짐
- 인덱스를 캐시해 두는 InnoDB의 버퍼 풀이나 MyISAM의 캐시 영역은 제한적이기 때문에 하나의 레코드를 위한 인덱스 크기가 커지면 커질수록 메모리에 캐시해 둘 수 있는 레코드의 수는 줄어듬
⇒ 메모리의 효율 떨어짐

8.3.3.2 B-Tree 깊이

인덱스의 B-Tree의 깊이가 3인 경우 최대 몇개의 키 값을 가질 수 있나?

키 값이 16바이트인 경우 최대 2억(585 * 585 * 585)개의 키 값을 담을 수 있지만, 23바이트의 경우 5천만개 → B-Tree의 깊이는 MySQL에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결된 문제로 인덱스 키 값의 크기가 커지면 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어지고,

같은 레코드 건수라 하더라도 B-Tree의 깊이(Depth)가 깊어져서 디스크 읽기가 더 많이 필요 하다는 것을 의미

+) B-Tree 인덱스의 깊이(Depth)는 상당히 중요하지만 직접 제어할 방법은 없고 아무리 대용량이라도 5단계 이상은 흔하지 많음

8.3.3.3 선택도(기수성)

모든 인덱스 키 값 가운데 유니크 한 키 값의 수

(전체 인덱스 키 값이 100인데, 그중에서 유니크한 키 값의 수가 10개라면 기수성은 10)

인덱스 키 값 가운데 중복된 값이 많아지면 많아질수록 기수성은 낮아지고 동시에 선택도 또한 떨어짐
인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리

⚠️ 선택도가 안 좋아도 정렬이나 그룹핑을 위해 인덱스를 만드는 경우도 만드는 것이 훨씬 나은 경우도 있음

MySQL에서는 인덱스의 통계 정보(유니크한 값의 개수)가 관리됨으로 칼럼의 기수성은 작업 범위에 아무런 영향을 끼지치 못함
- MySQL은 인덱스를 만들 때 해당 인덱스의 칼럼 값들에 대한 통계 정보를 수집하고 이 정보를 기반으로 쿼리 최적화를 수행합니다. 통계 정보에는 칼럼의 최솟값, 최댓값, NULL 값의 수, 칼럼의 값들이 얼마나 고유한지(기수성) 등이 포함됩니다.
- 작업범위 == 쿼리가 작업해야 하는 데이터의 양이나 범위
인덱스에서 유니크한 값의 개수는 인덱스와 쿼리의 효율성에 큰 영향

SELECT *
FROM tb_test
WHERE country = 'KOREA' AND city = 'SEOUL';

tb_test 테이블의 전체 레코드 건수는 1만건, country 칼럼으로만 인덱스가 생성된 상태

실제 모든 조건을 만족하는 레코드가 단 1건 있는 경우?

case A : country 칼럼의 유니크한 값의 개수가 10개
- 1건의 레코드를 위해 쓸모없는 999건을 조회
- country 칼럼의 인덱스를 비효율
- 현실적으로 모든 조건을 만족하는 인덱스 생성은 불가능 함으로 이정도의 낭비는 무시 가능
case B : country 칼럼의 유니크한 값의 개수가 1000개

8.3.3.4 읽어야 하는 레코드의 건수

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블 레코드를 읽는 것보다 높은 비용

테이블 레코드 100만건 중 50만건을 읽어야 하면 모두 읽어 필요 없는 50만건을 버릴지 / 인덱스를 통해 필요한 50만 건을 읽어야 할지 판단해야 함
일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도의 비용이 든다고 판단
인덱스를 통해 읽어야 할 레코드의 건수(옵티마이저가 판단한 예상 건수)가 전체 테이블 레코드의 20~25% 를 넘어서면 인덱스를 이용하지 않고 테이블을 직접 모두 읽어 필요한 레코드만 필터링하는 것이 효율적
- 전체 레코드의 20~25%이상 읽을 때에는 강제 인덱스를 사용하도록 힌트를 추가해도 성능상 이점 없음

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

어떤 경우에 인덱스를 사용하게 유도 / 사용 못하게 할지 판단하려면 MySQL이 어떻게 인덱스를 이용해서 레코드를 읽는지 알아야 함

8.3.4.1 인덱스 레인지 스캔

SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';

검색해야 할 인덱스의 범위가 결정되었을 때 사용하는 방식
검색하려는 값의 수나 검색 결과 레코드 건수와 관계없이 레인지 스캔이라고 표현
인덱스 탐색
- 루트에서 비교 시작 → 브랜치 → 리프 노드까지 찾아 들어가야만 필요한 레코드의 시작 지점을 찾기 가능
시작해야 할 위치를 찾으면 그때부터 리프 노드의 레코드만 순서대로 읽으면(스캔) 된다.
스캔하다 리프 노드의 끝까지 읽으면 리프 노드 간의 링크를 이용해 다음 리프 노드를 다시 스캔
최종적으로 스캔을 멈춰야 할 위치에 다다르면 지금까지 읽은 레코드를 사용자에게 반환하고 쿼리 끝냄

⭐ 어떤 방식(오름차순 / 내림차순)으로 스캔하든 관계없이, 해당 인덱스를 구성하는 칼럼의 정순 / 역순으로 정렬된 상태로 레코드를 가져 온다 (인덱스 자체의 정렬 특성 때문)

⭐ 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요

리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽을때 레코드 한건 한건 단위로 랜덤 I/O 발생
→ 비용 많이듬

커버링 인덱스

쿼리가 필요로 하는 데이터에 따라 인덱스의 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어오는 과정이 필요하지 않은 인덱스
디스크의 레코드를 읽지 않아도 됨으로 랜덤 읽기가 상당히 줄어듬예를 들어, **SELECT id, name FROM Users WHERE id > 1000**이라는 쿼리가 있을 때, **id**와 name 모두를 포함하는 인덱스가 있다면, 이 인덱스는 이 쿼리의 커버링 인덱스입니다.그러나 커버링 인덱스를 너무 많이 만들면 인덱스 유지 비용이 증가하고, 데이터 삽입, 삭제, 업데이트 작업의 성능이 저하될 수 있습니다. 따라서 실제 쿼리 패턴을 고려하여 커버링 인덱스를 적절히 설계하는 것이 중요합니다.
커버링 인덱스의 장점은 인덱스 만으로 쿼리의 결과를 생성할 수 있으므로, 실제 데이터를 참조하기 위한 디스크 I/O 작업을 줄일 수 있다는 것입니다. 이로 인해 쿼리 성능이 크게 향상될 수 있습니다. 따라서 커버링 인덱스를 사용하는 것은 데이터베이스 최적화 전략 중 하나입니다.
커버링 인덱스(Covering Index)는 쿼리의 선택과 투영에 필요한 모든 데이터를 포함하는 인덱스를 의미합니다.

인덱스 탐색과 인덱스 스캔을 확인할 수 있는 상태 값

```jsx
mysql> SHOW STATUS LIKE 'Handler_%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 0     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.01 sec)
```

Handler_read_key
- 인덱스 탐색이 실행된 횟수
Handler_read_next(인덱스 정순), Handler_read_prev(인덱스 역순)
- 인덱스 스캔으로 읽은 레코드 건수
Handler_read_first, Handler_read_last
- 인덱스의 첫 번째 레코드와 마지막 레코드를 읽은 횟수
- MIN() / MAX()와 같이 제일 큰 값 또는 제일 작은 값만 읽은 경우 증가
실제 인덱스만 읽었는지 인덱스를 통해 테이블 레코드를 읽었는지는 구분 안함

8.3.4.2 인덱스 풀 스캔

인덱스의 처음부터 끝까지 모두 읽는 방식

예시 : 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우
- 인덱스는 (A, B, C) 칼럼의 순서로 만들어져 있지만 쿼리의 조건절은 B 칼럼이나 C 칼럼

일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 테이블 풀스캔보다 인덱스만 읽는 것이 효율적

쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우

인덱스 풀 스캔

인덱스 리프 노드의 제일 앞 / 제일 뒤로 이동한 후, 인덱스의 리프 노드를 연결하는 링크드 리스트를 따라서 처음부터 끝까지 스캔하는 방식
인덱스 레인지 스캔도바는 빠르지 않지만 테이블 풀 스캔보다 효율적
인덱스의 전체 크기는 테이블 자체의 크기보다 훨씬 작으므로 인덱스 풀 스캔은 테이블 전체를 읽는 것보다 적은 디스크 I/O로 쿼리를 처리 가능

8.3.4.3 루스 인덱스 스캔

말그대로 느슨하게 듬성듬성하게 인덱스를 읽는 것
인덱스 레인지 스캔과 비슷하지만 필요하지 않은 인덱스 키 값은 무시(SKIP)하고 다음으로 넘어감
일반적으로 GROUP BY / 집합 함수 중 MAX(), MIN() 함수에 대해 최적화시 사용

SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dep_no BETWEEN 'd001' AND 'd004'
GROUP BY dept_no;

dept_emp 테이블은 dept_no와 emp_no라는 두 개의 칼럼으로 인덱스 생성
- 인덱스는 (dept_no, emp_no) 조합으로 정렬까지 되어 있어 dept_no 그룹별 첫 번째 레코드의 emp_no 만 읽으면 됨
- 인덱스에서 WHERE 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저 는 알고 있음으로 조건에 만족하지 않은 레코드는 무시하고 다음 레코드로 이동

8.3.4.4 인덱스 스킵 스캔

⭐ 인덱스의 핵심은 값이 정렬되어 있다는 것 → 인덱스를 구성하는 칼럼의 순서가 매우 중요

ALTER TABLE employees
ADD INDEX ix_gender_birthdate(gender, birth_date);

//case 1 : 인덱스를 사용하지 못하는 쿼리
SELECT * FROM employees WHERE birth_date >='1965-02-01';

//case 2 : 인덱스를 사용할 수 있는 쿼리
SELECT * FROM employees WHERE gender='M' AND birth_date >='1965-02-01';

→ case 1의 경우 gender 칼럼의 비교 조건이 없어 birth_date 칼럼부터 시작하는 인덱스를 새로 생성해야 했으나 MySQL 8.0 부터 gender 칼럼을 뛰어서 birth_date 칼럼만으로 인덱스 검색이 가능 하게 하는 인덱스 스킵 스캔 을 새로 생성

(8.0 이전에서는 루스 인덱스 스캔으로 GROUP BY 에서만 사용 가능했지만 WHERE 조건 검색까지로 넒어짐)

인덱스 스킵 스캔 기능 비활성화시

mysql> SET optimizer_switch = 'skip_scan=off';
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT gender, birth_date 
FROM employees WHERE birth_date >='1965-02-01';
+----+-------------+-----------+------------+-------+---------------+---------------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key                 | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+---------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | ix_gender_birthdate | 8       | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+---------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

WHERE 조건절에 gender 칼럼에 대한 조건 없이 birth_date 칼럼만 비교 조건으로 가져 ix_gender_birthdate 인덱스를 효율적으로 이용할 수 없음

type : index
- gender, birth_date 만으로 처리 완료가 가능함 :인덱스를 처음부터 끝까지 모두 읽음(풀 인덱스 스캔) → 인덱스 비 효율적 사용
- 모든 칼럼 조회시 테이블 풀 스캔 했어야 함

인덱스 스킵 스캔 기능 활성화시

mysql> SET optimizer_switch = 'skip_scan=on';
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT gender, birth_date 
FROM employees WHERE birth_date >='1965-02-01';
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+--------------------------+
		|  1 | SIMPLE      | employees | NULL   | range | ix_gender_birthdate | ix_gender_birthdate | 8       | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

type : range
- 인덱스에 꼭 필요한 부분만 읽음
Using index for skip scan
- ix_gender_bitrhdate 인덱스에 대해 인덱스 스킵 스캔을 활용해 데이터를 조회함
MySQL 옵티마이저는 우선 gender 칼럼에서 유니크한 값을 모두 조회해 주어진 쿼리에 gender 칼럼 조건 을 추가해서 쿼리를 다시 실행하는 형태
- gender 칼럼이 ENUM(’M’,’F’) 타입이기 때문이 아닌 어떤 타입이더라도 MySQL 서버는 인덱스를 루스 인덱스 스캔과 동일한 방식으로 읽으면서 인덱스에 존재하는 모든 값을 먼저 추출하고 이 결과를 이용해 인덱스 스캡 스캔을 실행
단점
- WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 함
  - MySQL 옵티마이저가 인덱스에서 스캔해야 할 시작 지점을 검색하는 작업이 많이 필요해 쿼리 성능 이 오히려 떨어짐
  - (emp_no, dept_no) 조합으로 이루어진 인덱스에서 스킵 스캔을 실행한다고 가정시 사원수 만큼 레인지 스캔 시작 지점을 검색하는 작업이 필요해 쿼리 성능 떨어짐
- 쿼리가 인덱스에 존재하는 칼럼으로 처리가 가능해야 함(커버링 인덱스)
  - 전체 조회시 풀스캔
    - type = ALL

8.3.5 다중 칼럼(Multi-column) 인덱스

(위에 루트 노드는 생략되었지만, 루트와 리프는 항상 존재, 브랜치는 없을 수 있음)

다중 칼럼 인덱스(복합 칼럼 인덱스, Concatenated Index)

2개 이상의 칼럼으로 구성된 인덱스

다중 칼럼 인덱스를 구성하는 칼럼 값이 어떻게 정렬되는가?

⭐ 두 번째 칼럼은 첫 번째 칼럼에 의존하여 정렬됨
== 두 번째 칼럼의 정렬은 첫 번째 칼럼이 같은 레코드에서만 의미 있음
N번째 칼럼의 정렬은 N-1 번째 칼럼에 의존해 정렬됨

⇒ 인덱스 내에서 칼럼의 위치 즉, 순서가 상당히 중요하며 신중히 결정해야 한다.

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

인덱스 생성시 설정한 정렬 규칙에 따라 인덱스 키 값은 오름차순 / 내림차순으로 정렬 되어 저장되고 인덱스를 어떤 방향으로 읽을지는 쿼리에 따라 옵티마이저 가 실시간으로 만들어 내는 실행 계획에 따름

8.3.6.1 인덱스의 정렬

인덱스를 생성하는 시점에 이를 구성하는 칼럼의 정렬을 오름차순/내림차순 으로 설정가능

8.0~ 부터는 아래와 같은 형태의 정렬 순수를 혼합한 인덱스 생성 가능

CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);

8.3.6.1.1 인덱스 스캔 방향

인덱스가 오름차순으로 정렬 되어 있지만 아래의 쿼리에서 옵티마이저는 최댓값부터 꺼꾸로 읽으면 내림차순으로 값을 가져올 수 있다는 것을 앎

SELECT *
FROM employees
ORDER BY first_name DESC
LIMIT 1;

⇒ 인덱스 생성 시점의 오름/내림차순 정렬이 되어 있으면 쿼리가 그 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름/내림차순 정렬 효과를 얻을 수 있음

쿼리의 ORDER BY 처리나 MIN()/MAX() 등의 최적화가 필요한 경우 MySQL 옵티마이저는 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어냄

8.3.6.1.2 내림차순 인덱스

2개 이상의 칼럼으로 구성된 복합 인덱스에서 각각의 칼럼이 내림/오름차순으로 혼합된 경우 8.0 내림차순 인덱스 로만 처리가 가능

오름차순 인덱스(Ascending index) : 작은 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스
내림차순 인덱스(Descending index) : 큰 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스
인덱스 정순 스캔(Forward index scan) : 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드의 왼쪽 페이지부터 오른쪽으로 스캔
인덱스 역순 스캔(Backward index scan) : 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드의 오른쪽 페이지부터 왼쪽으로 스캔

CREATE TABLE t1 (
  tid INT NOT NULL AUTO_INCREMENT,
  TABLE_NAME VARCHAR(64),
  COLUMN_NAME VARCHAR(64),
  ORDINAL_POSITION INT,
  PRIMARY KEY(tid)
) ENGINE=InnoDB;

//information_schema.COLUMNS 테이블은 MySQL 데이터베이스의 메타데이터를 저장하는 테이블로, 
//모든 테이블의 각 칼럼에 대한 정보를 제공합니다
INSERT INTO t1 SELECT NULL, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION 
FROM information_schema.COLUMNS;

INSERT INTO t1 SELECT NULL, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION FROM t1;

-- // 12번 실행
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 12619776 |
+----------+

천만건 정도의 테스트 데이터를 만들고 테이블을 풀 스캔하며 정렬만 수행하는 쿼리를 실행해보자.

다음 두 쿼리는 PK를 정순/역순 으로 스캔하며 마지막 레코드 1건만 반환한다.

limit ... offset…(limit …,….) 쿼리로 인해 실제 MySQL 서버는 테이블의 모든 레코드를 스캔한다.

//tid를 기준으로 오름차순으로 정렬한 결과 집합에서 12619776번째 행(인덱스는 0부터 시작하기 때문에)만을 가져오는 쿼리
mysql> SELECT * FROM t1 ORDER BY tid ASC  LIMIT 12619775,1;
1 row in set (4.15 sec)

mysql> SELECT * FROM t1 ORDER BY tid DESC LIMIT 12619775,1;
1 row in set (5.35 sec)

역순 정렬 쿼리가 정순 정렬 쿼리보다 28.9% 시간이 더 걸림

InnoDB에서 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느린 이유

페이지 잠금이 인덱스 정순 스캔(Forward index scan)에 적합한 구조
페이지 내에서 인덱스 레코드가 단방향으로 연결된 구조
- cf) 페이지(블록)간은 양방향 연결 고리(Double linked list)

아래의 쿼리에서 어떤 차순 인덱스를 고려해야 할까?

SELECT * FROM tab
WHERE userid = ?
ORDER BY score DESC
LIMIT 10;

오름차순 인덱스 : INDEX (userid ASC, score ASC);
내림차순 인덱스 : INDEX (userid DESC, score DESC);

case 1 : ORDER BY … DESC 하는 쿼리가 소량의 레코드에서 드물게 실행

두가지 인덱스 모두 적절한 선택

case 2 : 많은 레코드를 조회하며 빈번히 실행

내림차순 인덱스가 효율적

case 3 : 많은 쿼리가 인덱스의 앞쪽 / 뒤쪽만 집중적으로 읽어 인덱스의 특정 페이지 잠금이 병목

쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 병목 현상 완화에 도움

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

WHERE 조건 / GROUP BY / ORDER BY 이 언제 인덱스를 사용 가능 / 어떤 방식으로 사용하는지 식별 필요

8.3.7.1 비교 조건의 종류와 효율성

다중 칼럼 인덱스에서 각 칼럼의 순서 와 그 칼럼에 사용된 조건 이 동등 비교(=) / 크다(>) / 작다 (<) 같은 범위 조건인지에 따라 인덱스 칼럼의 활용 형태와 효율이 달라짐

mysql> SELECT * FROM dept_emp
			 WHERE dept_no = 'd002' AND emp_no >= 10114;

CASE A : INDEX(dept_no, emp_no)

dept_no = 'd002' AND emp_no >= 10114 인 레코드를 찾고, dept_no가 ‘d002’가 아닐 때 까지 인덱스를 쭉 읽으면 됨
- 작업 범위 결정 조건
  - 2번째 칼럼인 emp_no는 비교 작업의 범위를 좁히는 데 도움
  - 이 조건이 많으면 성능 높힘
조건을 만족하는 레코드를 찾는데 해당 수 만큼 비교 작업만 수행함으로 효율적인 인덱스 이용

CASE B : INDEX(emp_no, dept_no)

emp_no >= 10114 AND dept_no = 'd002'인 레코드를 찾고 그 이후 모든 레코드에 대해 dept_no가 ‘d002’인지 비교하는 과정 거쳐야 함
- 필터링 조건 / 체크 조건
  - 2번째 칼럼인 dept_no는 비교 작업의 범위를 좁히는 데 쓰이지 않고 쿼리의 조건에 맞는지 검사
  - 이 조건 많으면 최종 레코드는 작아도 성능 높히진 못함
- 필터링
  - 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하면서 취사선택하는 작업
조건을 만족하는 레코드를 찾는데 해당 수 이상으로 비교 작업만 수행함
- 이유
  - 다중 칼럼 인덱스의 인덱스의 N번째 키 값은 N-1번째 키 값에 대해 다시 정렬됨

8.3.7.2 인덱스의 가용성

B-Tree 인덱스의 특징은 왼쪽 값을 기준으로(Left-most) 오른쪽 값이 정렬되어 있다는 것

왼쪽 값 정렬은 빠른 검색의 전제 조건인데 하나의 칼럼으로 검색해도 값의 왼쪽 부분이 없거나 다중 컬럼에서 왼쪽 칼럼의 값을 모르면 인덱스 레인지 스캔을 사용할 수 없음
WHERE 조건 / GROUP BY / ORDER BY 모두 동일 적용

mysql> SELECT * FROM employees WHERE first_name LIKE '%mer';

인덱스 레인지 스캔 사용 불가
- first_name에 저장된 왼쪽 값부터 한 글자씩 비교해야 하는데, 상수값(%mer)에는 왼쪽 부분 고정되지 않음 (정렬 우선순위가 낮은 뒷부분의 값만으로는 왼쪽 기준 정렬 기반 인덱스인 B-Tree) 효과 없음

mysql> SELECT * FROM dept_emp WHERE emp_no>=10144;

다중 컬럼으로 구성된 인덱스임으로 먼저 dept_no 칼럼에 대해 먼저 정렬후, emp_no 칼럼값으로 정렬되어 있어 인덱스 효율적 사용 불가

8.3.7.3 가용성과 효율성 판단

아래의 조건에서는 작업 범위 조건이 아닌 체크 조건으로만 인덱스 사용 가능

1.NOT-EQUAL로 비교된 경우("<>", "NOT IN", "NOT BETWEEN", "IS NOT NULL")

WHERE column <> 'N'
WHERE column NOT IN (10,11,12)
WHERE column IS NOT NULL

2.LIKE '%??'(앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우

WHERE column LIKE '%승환'
WHERE column LIKE '_승환'
WHERE column LIKE '%승%'

3.스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우

WHERE SUBSTRING(column, 1, 1)='X'
WHERE DAYOFMONTH(column)=1

4.NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우

WHERE column=deterministic_function()

5.데이터 타입이 서로 다른 비교(인덱스 칼럼의 타입을 변환해야 비교가 가능한 경우)

WHERE char_column = 10

6.문자열 데이터 타입의 콜레이션이 다른 경우

WHERE utf8_bin_char_column=euckr_bin_char_column

+)MySQL에서는 NULL 값도 인덱스에 저장됨으로 WHERE 조건 사용시 작업 범위 결정 조건으로 사용

다중 컬럼으로 만들어진 인덱스의 작업 범위 조건 사용 가능 여부

작업 범위 결정 조건으로 인덱스를 사용 못하는 경우

column_1 칼럼에 대한 조건이 없는 경우
column_1 칼럼 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우

작업 범위 결정 조건으로 인덱스를 사용하는 경우

column_1 ~ column_(i-1) 칼럼까지 Equal 형태("=" 또는 "IN")로 비교
column_i 칼럼에 대해 다음 연산자 중 하나로 비교
- Equal("=" 또는 "IN")
- 크다 작다 형태(">" 또는 "<")
- LIKE로 좌측 일치 패턴(LIKE '승환%')

-- // 다음 쿼리는 인덱스를 사용할 수 없음
.. WHERE column_1 <> 2

-- // 다음 쿼리는 column_1과 column_2까지 범위 결정 조건으로 사용됨
.. WHERE column_1=1 AND column_2 > 10

-- // 다음 쿼리는 column_1, column_2, column_3까지 범위 결정 조건으로 사용됨
.. WHERE column_1 IN (1,2) AND column_2=2 AND column_3 <= 10

-- // 다음 쿼리는 column_1, column_2, column_3까지 범위 결정 조건으로,
-- // column_4는 체크 조건으로 사용됨
.. WHERE column_1 AND column_2 AND column_3 IN (10,20,30) AND column_4 <> 100

-- // 다음 쿼리는 column_1, column_2, column_3, column_4까지 범위 결정 조건으로 사용됨
-- // 좌측 패턴 일치 LIKE 비교는 크다 또는 작다 비교와 동급으로 생각하면 될 듯하다.
.. WHERE column_1 AND column_2 IN (2,4) AND column_3=30 AND column_4 LIKE '김승%'

-- // 다음 쿼리는 column_1, column_2, column_3, column_4, column_5 칼럼까지
-- // 모두 범위 결정 조건으로 사용됨
.. WHERE column_1=1 AND column_2=2 AND column_3=30 AND column_4='김승환' AND column_5='서울'

출저 : Real MySQL 8.0 (1권) 개발자와 DBA를 위한 MySQL 실전 가이드 [ 전면개정판 ] 백은빈, 이성욱저 , 위키북스

공지사항

최근에 올라온 글

최근에 달린 댓글

Total

Today

Yesterday

링크

TAG more

« 2025/05 »
일	월	화	수	목	금	토
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

글 보관함

개발블로그, 숲