1. 디스크 읽기 방식

1-1. HDD(기계식) && SSD(전자식)

HDD: 데이터 저장용 플래터(원판)
SSD: 플래시 메모리
데이터를 가져오는 방법:
- 기계식에서는 원판을 돌려서 데이터 위치를 찾고 가져옵니다. 그러나 SSD는 전자식이라 이런 과정이 없습니다. 디스크의 성능은 디스크 헤더의 위치 이동횟수인데, SSD는 원판이 없으므로 HDD보다 빠른 것입니다.

1-2. 순차적 I/O && 랜덤 I/O

순차적 I/O:
- 연속된 데이터를 접근하기 때문에 헤더가 일정한 방향으로 이동
랜덤 I/O:
- 랜덤 I/O는 데이터가 디스크 여러 곳에 흩어져 있어 헤더가 불규칙하게 더 많이 이동

1-3. HDD의 I/O 방식

순차 I/O
- 연속된 섹터를 읽거나 씀
- 헤더가 한 방향으로 이동하며 데이터 접근
- 디스크 회전만으로 다음 데이터 접근 가능
랜덤 I/O
- 흩어진 섹터들을 읽거나 씀
- 헤더가 여러 방향으로 불규칙하게 이동
- 디스크 회전 + 헤더의 반복적인 이동 필요

1-4. SSD의 I/O 방식

순차 I/O
- 연속된 페이지들을 읽거나 씀
- 블록 내 연속된 페이지들에 접근
랜덤 I/O
- 흩어진 페이지들을 읽거나 씀
- 여러 블록의 페이지들에 접근

SSD의 장점은 기존 HDD 보다 랜덤 I/O 일 떄 더 빛을 발휘합니다. 그래서 DBMS 와 같이 순차 I/O 보다 랜덤 I/O 가 훨씬 더 자주 일어나는 환경에서는 SSD 가 적합합니다.

짚고 가는 이유

인덱스 종류마다(레인지 스캔: 랜덤 I/O / 풀 테이블 스캔: 순차적 I/O 등) 방식이 다름
앞서 봤던 것 처럼 순차 I/O가가 랜덤 I/O보다 빠릅니다. 인덱스 방법에서는 레인지 스캔이 빠르지만, 랜덤 I/O를 사용하기 때문에 큰 테이블의 레코드 대부분을 읽는 작업에서는 풀 테이블 스캔을 사용할 때도 있습니다.
옵티마이저가 인덱스를 활용하는 것보다 순차I/O로 테이블 전체를 탐색(Full Table Scan)하는 것이 더 효율적이라고 판단하면, B 트리 인덱스를 활용하지 않습니다.

+) InnoDB 클러스터형 ‘데이터’ 저장방식

클러스터형 인덱스와 클러스터 데이터는 InnoDB에서 같은 의미로 사용됩니다. 그러나 여기서는 인덱스 B-Tree와 데이터 데이터 파일과는 구별하기 위해 데이터를 강조했습니다. InnoDB 가 기본적으로 클러스터드 인덱스(Clustered Index)를 적용합니다. InnoDB의 모든 테이블은 기본적으로 PK를 기준으로 클러스터링되어 저장됩니다. 즉, PK 값 순서대로 디스크에 저장된다는 뜻이며, 모든 세컨더리 인덱스는 레코드의 주소 대신 PK의 값을 논리적인 주소로 사용합니다.

데이터 정렬

PK 값을 기준으로 데이터가 정렬되어 저장
예: PK가 1,2,3,4 순서면 데이터도 그 순서대로 저장

페이지 단위 저장

정렬된 데이터를 페이지 단위로 나눠서 저장
B-Tree 구조로 페이지들을 관리
장점
- 자주 사용되는 페이지만 메모리에 올려둘 수 있음
- 페이지 단위로 잠금이 가능해 여러 사용자가 동시에 접근할 때 더 효율적
- InnoDB의 이런 페이지 구조는 트랜잭션 처리와 동시성 제어에 더 유리
- 이 구조는 데이터 정합성과 동시성이 중요한 OLTP(온라인 트랜잭션 처리) 시스템에서 장점

2. 인덱스

데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조
책의 색인과 같은 개념

색인과 같이 한 눈에 파악하려면 정렬은 필수입니다. 매번 정렬되는 자료구조에는 항상 트레이드 오프가 존재합니다. INSERT, UPDATE, DELETE의 처리는 늦어지고 조회 속도가 빨라지는 것입니다. 즉, 저장속도를 얼마만큼 희생하여 읽기 속도를 늘릴 것이냐입니다.

데이터 저장 방식 : B-Tree 인덱스, Hash 인덱스, Fractal-Tree 인덱스, Merge-Tree 인덱스 등

인덱스 기능별: 전문 검색용, 공간 검색용 인덱스

MySQL은 B-Tree인덱스 사용

3. B-Tree 인덱스(InnoDB)

B-Tree(Balanced Tree) 는 DBMS 인덱싱 알고리즘 중 가장 많이 사용되는 알고리즘
- DMBS에서는 B-Tree의 변형 알고리즘인 B+-Tree 또는 B*-Tree가 사용
- B-Tree 인덱스는 Balanced-Tree를 의미한다. Binary가 아닌 것에 주의

3-1. 구조 및 특성

루트 - 브랜치 - 리프 노드로 B-Tree 구성

인덱스의 키를 보시면 모두 정렬된 형태인 것을 볼 수 있습니다. 그러나 데이터 파일 부분을 보면, 레코드는 정렬되지 않고 있습니다. 화살표가 여기저기 섞여있죠? 데이터 파일의 레코드는 insert 순서대로 저장되는 것이 아닌, 기존 레코드가 삭제된 빈 공간을 재활용하도록 설계됐기 때문입니다.

1) PK로 검색시:
클러스터형 인덱스 검색 -> 바로 데이터 접근

2) 세컨더리 인덱스로 검색시:
세컨더리 인덱스 검색 -> PK값 확인 -> 클러스터형 인덱스 검색 -> 데이터 접근

리프 노드에서 바로 레코드 주소값을 참조하는 MyISAM 과는 다르게, InnoDB 는 PK 를 참조

InnoDB는 왜 한 번에 레코드 접근 안하고 B-Tree 한 번 더 타지?

데이터가 정렬되어 있기 때문에, 데이터 변경(INSERT/UPDATE/DELETE)이 발생하면 물리적인 데이터 이동이 필요
만약 세컨더리 인덱스가 직접 데이터 주소를 가리킨다면:
- 데이터 이동시마다 모든 세컨더리 인덱스의 주소값을 전부 수정해야 함
- 이는 엄청난 오버헤드 발생
하지만 Primary Key를 통해 간접 참조하면:
- 데이터가 이동해도 Primary Key 값은 변하지 않음
- 세컨더리 인덱스의 수정 없이 데이터 재배치 가능

데이터의 물리적 정렬로 인한 단점(데이터 변경시 재정렬)을 보완하기 위한 설계

3-2. B-Tree 인덱스 키 추가 및 삭제

테이블의 레코드에 변화가 생기면, 인덱스에도 변화가 발생합니다.

이 변화가 어떻게 일어나는지 이해하면, 쿼리 성능을 쉽게 예측할 수 있습니다.

3-2-1. 인덱스 키 추가

새로운 키 값이 B-Tree 에 저장될 때, 적절한 노드를 찾아 리프 노드에 저장
리프 노드가 꽐 찰 경우, 분리
- 꽉 찬 노드를 절반으로 나눠서 2개의 리프 노드로 나눔
B-Tree 는 상대적으로 쓰기 작업(새로운 키 추가) 에 비용이 많이 듦
- 테이블에 레코드를 추가하는 작업 비용을 1이라고 가정하면 해당 테이블의 인덱스에 키를 추가하는 작업 비용은 1.5로 예측합니다.
체인지 버퍼를 활용해 지연 처리될 수도 있음
- 프라이머리 키, 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree에 추가
- 체인지 버퍼가 없는 MEMORY 나 MyISAM 은 바로 추가 Or 삭제처리됨

3-2-2. 인덱스 키 삭제

해당 리프 노드를 삭제 마크만
이렇게 마스킹된 공간은 그대로 방치하거나 재활용할 수 있음
이 역시 디스크 I/O 가 발생하기 때문에 MySQL 5.5 이상의 InnoDB 에서는 지연처리가 될 수 있음

3-2-3. 인덱스 키 변경

B-Tree 는 인덱스에에 따라 정렬된 형태이기 때문에, 단순히 해당 인덱스의 값만 변경할 수는 없습니다.

그래서 기존 키 값을 삭제한 후, 새로 추가하는 형태로 처리

이 또한 체인지 버퍼를 활용해 지연 처리될 수도 있음

3-2-4. 인덱스 키 검색

앞서 알아본 것 처럼, 인덱스는 INSERT, UPDATE, DELETE 작업 모두에 인덱스 관리에 따른 추가 비용 발생합니다. 그럼에도 인덱스를 사용하는 이유는 빠른 검색(SELECT) 때문입니다.

빠른 검색이 가능한 이유는 무작정 찾는것 아닌, 효율적인 트리 탐색(Tree search) 과정 때문인데요.

이는 100% 일치하는 데이터를 검색하는 경우에 효율적으로 활용된다고 합니다.

사실 이 부분은 UPDATE, DELETE 의 WHERE 조건에서 특정 레코드를 찾을때도 사용됩니다.

UPDATE, DELETE 입장에서도 마냥 손해보는것 만은 아닙니다.

다음은 B-Tree 인덱스를 이용한 키 검색이 가능한 경우와 불가능한 경우를 나타낸다.

100% 일치(동등 연산, = ‘test') 또는 값의 앞부분만 일치하는 경우(LIKE 연산, “test%”) 사용 가능
부등호 (< , >) 비교 조건에서 사용 가능
키 값의 뒷부분만 검색하는 경우(LIKE 연산, “%test”) 사용 불가능
키 값에 변형이 가해지는 경우 사용 불가능

InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락(갭 락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현돼 있다. 따라서 UPDATE나 DELETE를 수행할 때 적절한 인덱스가 없으면 불필요하게 많은 레코드를 잠근다. 따라서 InnoDB 스토리지 엔진에서는 인덱스 설계가 굉장히 중요하고 많은 부분에 영향을 미친다.

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

B-Tree 인덱스는 칼럼의 크기, 레코드 수, 유니크 키 개수 에 의해 영향을 받습니다.

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

InnoDB 가 디스크에 데이터를 저장하는 기본 단위는 페이지(Page) 또는 블록(Block) 이라고 합니다.

또한 InnoDB 내부의 버퍼 풀에서 데이터를 버퍼링 하는 기본 단위이기도 합니다.

이 페이지의 기본 크기는 16KB 이지만 innodb_page_size 시스템 변수로 4KB~64KB 사이의 값을 선택할 수 있습니다.

인덱스를 생성하면 이 페이지에 인덱스 키 가 저장되는데요

그러면 당연히 인덱스 키 의 크기에 따라 한 페이지에 저장할 수 있는 개수가 정해지게 됩니다.

인덱스 키(16바이트) + 자식 노드 주소(12바이트) = 28바이트

이 예시에 따르면, 인덱스 컬럼 하나의 크기는 28byte 가 됩니다.

그래서 한 페이지에 저장할 수 있는 키의 개수는 16 * 1024 / (16 + 12) = 약 585 라고 계산됩니다.

하지만, 키 값의 크기가 2배로 늘어나면 어떨까요?

그러면 16 * 1024 / (32 + 12) = 약 372 입니다.

500개의 키에 대한 조회 요청이 들어오면 컬럼 크기가 16byte 인 경우에는 한 페이지에서 모두 조회할 수 있지만,

컬럼 크기가 32byte 인 경우에는 2개의 페이지에서 조회해야합니다.

즉, 인덱스 키의 크기가 클수록 인덱스 탐색에도 더 많은 오버헤드가 발생한다는 의미입니다.

3-3-2. B-Tree 깊이

위에서 인덱스 키의 크기에 따라, 한 페이지에 얼마만큼의 인덱스 키가 저장될 수 있는지 알아봤습니다.

컬럼의 크기가 16byte 인 경우에는 한 페이지당 585개, 32byte 인 경우에는 372개를 저장할 수 있었습니다.

이전에 알아본 것 처럼 B-Tree 는 루트 노드, 브랜치 노드, 리프 노드 등으로 구성되어있습니다.

그러면 이번에는 깊이(Depth)가 3인 B-Tree 에서 몇개의 키 값을 가질 수 있는지 비교해보겠습니다.

먼저 인덱스 키 크기가 16byte 인 경우 585를 가지고 각 키는 하위 노드를 가지니 제곱을 합니다. 깊이가 3이니 3제곱을 하면 585^3 = 200,201,625 로 약 2억개의 키를 저장할 수 있습니다.

그리고 인덱스 키의 크기가 32byte 인 경우는 372^3 = 51,478,848 로 약 5천만개의 키를 저장할 수 있습니다.

인덱스 키의 크기는 2배로 늘었지만, 저장 가능한 키의 개수는 1/4로 줄어들었습니다.

이를 보면, B-Tree 에서 저장할 수 있는 키의 개수도 컬럼의 크기에 영향을 받는다는 것을 알 수 있습니다.

만약 깊이가 저장할 수 있는 키의 개수를 넘어선다면, 내부적으로 B-Tree 의 Depth 를 한 단계 늘리는 작업을 합니다. 깊이가 3인 B-Tree 였다면 4로, 4였다면 5로 늘어나게 됩니다.

3-3-3. 선택도(기수성)

인덱스에서 선택도(Selectivity) 또는 기수성(Cardinality)은 모든 인덱스키 값 가운데 유니크한 값의 수를 의미합니다.

카디널리티(Cardinality)가 가장 높은 것
- 성별: 카디널리티가 낮은 것
- 주민번호: 카디널리티가 높은 것
여러 컬럼으로 인덱스를 잡는다면, 카디널리티가 높은순에서 낮은순으로 구성하는게 더 성능이 뛰어납니다.
- 물론, 옵티마이저가 조회 조건의 컬럼을 인덱스 컬럼 순서에 맞춰 재배열하는 과정을 추가해서 차이는 미비함

100개의 값 중에서 유니크한 값의 개수가 30개라면, 기수성은 30입니다.

기수성이 낮으면 중복된 값이 많다는 것이니 검색할 대상이 많아져서 인덱스 키의 선택도가 낮아집니다.

반면에 기수성이 높으면(중복된 값이 적으면) 검색할 대상이 줄어들어서 키의 선택도가 높아져서 빠르게 처리됩니다.

물론 인덱스는 단순히 탐색뿐만 아니라, 정렬, 그루핑과 같은 작업의 속도도 올릴 수 있기 때문에,

상황에 맞게 적절하게 사용하는것이 중요합니다.

-- 100만건 데이터에서 1건 찾기
-- 1) 인덱스 없을 때
SELECT * FROM users WHERE email = 'user1@test.com';
-- → 100만건 모두 읽어서 비교

-- 2) 인덱스 있을 때
SELECT * FROM users WHERE email = 'user1@test.com';
-- → B-Tree 탐색 + 레코드 1건 읽기
-- → 비용은 4~5배지만, 100만건을 읽는 것보다 훨씬 빠름

인덱스는 선택도가 높을 수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리됩니다.

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

인덱스를 통해 레코드를 읽는 것은 인덱스를 거치지 않는 것 보다 높은 비용이 듭니다. 따라서 100만 건 중 50만 건을 읽을 때, 전체 테이블을 모두 읽어 50만개를 버릴지, 인덱스를 통해 필요한 50만개를 읽어올 지,

무엇이 더 효율적일지를 판단해야 합니다.

일반적인 dbms의 옵티마이저는 인덱스를 통해 레코드 1건을 읽는 것이, 테이블에서 직접 레코드 1건을 읽는 것 보다 4~5배의 비용이 드는 것으로 추산합니다.

따라서 인덱스를 통해 읽을 레코드가 전체 테이블의 20~25%를 넘어서는 인덱스를 이용하지 않고 테이블을 모두 읽어 필터링하는 것이 효율적입니다. 물론 이것도 옵티마이저가 알아서 테이블을 풀 스캔할지 인덱스 테이블을 읽을지 판단합니다.

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

그렇다면 Mysql이 어떻게 인덱스를 이용해 실제 레코드를 읽을까요?

3-4-1. 인덱스 레인지 스캔

검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식입니다.

루트노드부터 비교를 시작해 리프 노드에 도달하고, 필요한 레코드의 시작점을 찾습니다.

시작할 위치를 찾으면 리프 노트의 레코드만 순서대로 읽으면 됩니다.

스캔을 멈춰야 할 위치에 다다르면 레코드를 사용자에게 반환하고 쿼리를 끝냅니다.

같은 방식으로 스캔 시작 위치를 정하고, 그 지점부터 필요한 방향(오름차순 혹은 내림차순)으로 레코드를 읽어옵니다.

어떤 방식으로 스캔하든 상관없이, 인덱스 자체의 정렬 특성 덕에 정순, 혹은 역순 정렬된 상태로 레코드를 가져옵니다.

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

이때 리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데, 이 한 건 단위로 랜덤 I/O가 한 번씩일어납니다.

그래서 인덱스를 통해 데이터 레코드를 읽는 작업은 비용이 많이 드는 것입니다.

인덱스 레인지 스캔은 다음과 같이 크게 3단계를 거치는 것으로 정리 가능합니다.

인덱스에서 조건에 해당하는값이 저장된 위치를 찾는다
위치부터 필요한 만큼 인덱스를 차례로 읽는다
3. 읽어들인 키(Primary Key 또는 보조 인덱스의 값)를 이용해, 클러스터드 인덱스를 탐색하여 해당 레코드가 저장된 페이지를 가져오고, 최종적으로 레코드 데이터를 읽어온다.

쿼리가 필요하는 데이터에 따라 3번은 필요하지 않을 수 있습니다. 이를 커버링 인덱스라고 합니다.

커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되기 때문에, 랜덤 읽기가 상당히 줄어들고 성능은 그만큼 빨라집니다.

예를 들어,

SELECT country FROM tbl WHERE country='KOREA';

이 같은 쿼리에서 country라는 세컨더리 인덱스로 탐색하지만 select을 country밖에 안 하기 때문에 클러스터드 인덱스 탐색을 하지 않아 커버링 인덱스 라고 한다.

Mysql에서는 show status like 'Handler%' 명령어를 통해 1,2 번 단계의 작업이 얼마나 수행됐는지를 확인할 수 있습니다. Handler_read_key 상태 값은 1번 단계가 실행된 횟수, Handler_read_next Handler_read_prev는 2번 단계로 읽은 레코드 건수(앞은 정순 뒤는 역순)를 의미합니다. Handler_read_first와 Handler_read_last는 인덱스의 첫 번째 레코드와 마지막 레코드를 읽은 횟수입니다. 이 둘은 min() max() 와 같이 제일 큰 값 또는 제일 작은 값만 읽는경우 증가하는 상태 값입니다.

3-4-2. 인덱스 풀 스캔

인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 합니다.

사용 시기: 쿼리의 조건절에 사용된 칼럼이, 인덱스의 첫 번째 칼럼이 아닌 경우 사용

(인덱스가 a,b,c 칼럼의 순서로 걸려있지만, 쿼리 조건절은 b나 c로 검색하는 경우)

장점: 인덱스 레인지 스캔보다는 빠르지 않지만, 테이블 풀 스캔보다는 효율적입니다. 인덱스의 전체 크기는 테이 블보다는 작으므로 테이블 전체를 읽는 것 보다는 적은 디스크 I/O가 일어나기 때문입니다.

3-4-3. 루스 인덱스 스캔

인덱스 레인지 스캔과 비슷하지만 중간에 필요없는 인덱스 키 값은 무시하고 다음으로 넘어가는 형태입니다.

일반적으로 Group by 또는 집합 함수 가운데 Max() 또는 Min() 함수에 대해 최적화를 하는 경우 사용됩니다.

사용 시기:

-- 인덱스: (dept_no, emp_no)
SELECT dept_no, COUNT(*) 
FROM employees 
GROUP BY dept_no;

3-4-4. 인덱스 스킵 스캔

만약 한 테이블에 컬럼 두개에 인덱스를 (a,b) 순서대로 건다면,

조건절에서 a,b를 모두 사용한다면 인덱스를 쓰지만, b만 쓰면 인덱스를 사용할 수 없습니다.

이 경우 b부터 사용하는 인덱스를 새로 걸어야만 했죠.

그러나 Mysql 8.0 버전부터는 b만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능이 도입됐습니다.

AlTER TABLE employees ADD INDEX ix_gender_birthdate (gender, birth_date)

// 인덱스 스킵 스캔은 위와 같이 인덱스가 걸리는 상황에서, gender 없이 birth_date 칼럼만으로도 
인덱스 검색이 가능합니다.

select gender, birth_date from employees where birth_date>='1965-02-01'`

// 위와 같은 쿼리를 실행할 때 아래 쿼리로 실행
`select gender, birth_date from employees where gender='M' and birth_date>='1965-02-01'`

`select gender, birth_date from employees where gender='F' and birth_date>='1965-02-01'`

옵티마이저가 내부적으로 위 두개의 쿼리를 실행하는 것과 비슷한 형태의 최적화를 합니다.

따라서 위 쿼리는 인덱스가 걸리는 쿼리이므로 검색 속도가 빠릅니다.

그러나, 위처럼 인덱스의 선행 칼럼의 유니크한 값의 개수가 적고,

select 대상이 인덱스에 존재하는 칼럼만으로 처리 가능한 경우여야 합니다.

select * from employees where birth_date>='1965-02-01'

위와 같이 모든 칼럼을 필요로 하는 경우 인덱스 스킵 스캔을 사용하지 못합니다.

3-4-5. 다중 칼럼 인덱스 스캔

두 개 이상의 칼럼으로 구성된 인덱스입니다.

그림의 리프 노드를 확인하면, 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬돼 있는 것을 확인할 수 있습니다.

따라서 칼럼이 세개인 인덱스가 있다면 3번째 칼럼은 두 번째를 기준으로 정렬될 것입니다.

그러므로, 다중 칼럼 인덱스는 각 칼럼의 순서가 굉장히 중요합니다.

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

인덱스의 키 값은 오름차순 혹은 내림차순으로 정렬되는데, 이를 어느 방향으로 읽을지는 옵티마이저의 실행계획에 따라 결정됩니다.

3-5-1.인덱스의 정렬

MySQL 5.7 까지는 정렬 순서를 혼합해서 쓸 수 없었지만, 8.0 부터는 아래와 같은 형태로 정렬 순서를 혼합해 쓸 수 있습니다.(ASC DESC 혼합)

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

3-5-2. 내림차순 인덱스

만약 2개 이상의 칼럼으로 구성된 복합 인덱스에서, 각각의 칼럼이 내림차순과 오름차순이 혼합된 경우 내림차순 인덱스로만 해결할 수 있습니다.

내림차순 인덱스는 큰 값의 인덱스 키가, B-Tree의 왼쪽으로 정렬된 인덱스입니다.

인덱스 리프 노드에 왼쪽부터 오른쪽으로 스캔하면 정순 스캔, 오른쪽에서 왼쪽으로 스캔하면 역순 스캔입니다.

그러나 인덱스 역순 스캔은 정순 스캔보다 느립니다.

이유는 페이지 잠금이 인덱스 정순 스캔에 적합하고, 페이지 내에서 인덱스 레코드의 연결이 단방향으로 이루어져있기 때문입니다.

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

어떤 조건에서 인덱스를 사용할 수 있거나, 사용할 수 없을까요?

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

다중 칼럼 인덱스에서 칼럼의 순서와, 칼럼에 사용된 조건이 동등비교(=) 인지, 크다(>), 작다(<) 비교인지에 따라 인덱스 칼럼의 활용 형태가 다릅니다.

select * from dept_emp where dept_no **=** 'd002' and emp_no >= 10114;

라는 쿼리를 실행할 때,

(dept_no, emp_no)순으로 인덱스가 걸린 경우 A 와 (emp_no, dept_no)경우 B가 있다고 가정하면,

!https://velog.velcdn.com/images/rg970604/post/f7bda96e-3bd4-4fe4-a333-29ba191ceeb0/image.png

A(왼쪽 그림)
- 다음과 같이 A는 먼저 d002이면서 10144 이상인 레코드를 찾은 뒤, 이미 세컨더리 인덱스가 정렬된 상태이므로 밑으로 쭉 읽다가 doo2가 아니면 멈추면 됩니다.
- 5건을 조회하기 위해 딱 필요한 5건만 읽었습니다.
B(오른쪽 그림)
- 저 10144 이상이면서 d002인 레코드를 찾고, 쭉 내려가면서 모든 레코드에 대해 dept_no가 d002인지 확인하는 과정이 필요합니다. 총 7번을 읽었습니다.

A의 두 조건처럼 작업 범위를 줄여주는 조건을 작업 범위 결정 조건이라 하고

B의 dept_no 처럼 조건 검사 역할만 하는 조건을 필터링 조건 혹은 체크 조건이라 합니다.

작업 범위 조건이 많으면 쿼리 성능을 높이지만, 체크 조건은 성능을 높이지 못하고 실행을 느리게 할 수도 있습니다.

3-6-2. 인덱스의 가용성

B-Tree 인덱스는 왼쪽 값에 기준해서 오른쪽 값이 정렬돼 있습니다.

따라서 왼쪽 값을 기준으로 칼럼을 읽어야 합니다.

따라서 LIKE %something 과 같은 쿼리의 경우 왼쪽부터 비교할수 없으므로, 인덱스 효과를 얻을 수 없습니다.

또 위의 케이스 A처럼 (dept_no, emp_no)순으로 인덱스가 걸린 경우,

select * from dept_emp where emp_no >= 10114;

와 같은 쿼리를 실행했을 때 emp_no 는 dept_no를 기준으로 값이 정렬돼 있어, 인덱스를 효율적으로 사용할 수 없습니다. 조건에 선행 칼럼이 없기 때문입니다.

3-6-3.가용성과 효율성 판단

아래의 경우 인덱스를 사용할 수 없습니다.

NOT-EQUAL로 비교
LIKE %something 비교(앞 부분이 아닌 뒷부분 일치 비교)
인덱스 칼럼 변형 후 비교
WHERE SUBSTRING(column,1,1) = ‘x’
데이터 타입이 다른 비교
- WHERE char_column = 10
NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용
데이터 타입이 서로 다른 비교
문자열 데이터 타입이 콜레이션이 다른 경우

index ix_test (column_1, column_2, ..., column_N)

위 다중 칼럼 인덱스에서 아래의 경우는 다중 칼럼 인덱스 사용이 불가능합니다.

column_1 에 대한 조건 없음
column_1의 비교 조건의 위의 인덱스 사용 불가 조건 중 하나임
AND연산자는 각 조건들이 읽어와야할 ROW수를 줄이는 역할을 하지만, or 연산자는 비교해야할 ROW가 더 늘어나기 때문에 풀 테이블 스캔이 발생할 확률이 높습니다.
- WHERE 에서 OR을 사용할때는 주의가 필요합니다.

-- 같은 컬럼의 OR 조건이고 각각 인덱스가 있는 경우
SELECT * FROM employees 
WHERE name = 'Kim' OR name = 'Lee';  -- name 인덱스 사용 가능

-- IN으로 변환 가능한 경우
SELECT * FROM employees 
WHERE name IN ('Kim', 'Lee');  -- 더 효율적

-------------------

-- 다른 컬럼들의 OR 조건
SELECT * FROM employees 
WHERE name = 'Kim' OR department = 'IT';  
-- name, department 각각 인덱스가 있어도 
-- 옵티마이저가 풀 테이블 스캔 선택할 수 있음

-- OR 대신 UNION ALL 사용
SELECT * FROM employees WHERE name = 'Kim'
UNION ALL
SELECT * FROM employees WHERE department = 'IT';
-- 각각의 쿼리가 자신의 인덱스를 활용

Quiz

문제

-- 테이블 및 인덱스 생성
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    age INT,
    salary DECIMAL(10,2),
    department VARCHAR(50),
    hire_date DATE
);

CREATE INDEX idx_name ON employees(name);
CREATE INDEX idx_department_salary ON employees(department, salary);
CREATE INDEX idx_hire_date ON employees(hire_date);

SELECT * FROM employees WHERE name = 'Kim';  

SELECT * FROM employees 
WHERE department = 'IT' AND salary > 50000;  

SELECT * FROM employees 
WHERE name LIKE 'Kim%';  

SELECT * FROM employees 
WHERE department IN ('IT', 'HR');

SELECT * FROM employees 
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31'; 

SELECT * FROM employees 
WHERE UPPER(name) = 'KIM'; 

SELECT * FROM employees 
WHERE name LIKE '%Kim'; 

SELECT * FROM employees 
WHERE salary > 50000 AND department = 'IT'; 

SELECT * FROM employees 
WHERE department = 'IT' OR salary > 50000;  

SELECT * FROM employees 
WHERE name != 'Kim'; 

SELECT * FROM employees 
WHERE department = 'IT'; 

SELECT * FROM employees 
WHERE department = 'IT' 
  AND salary BETWEEN 50000 AND 70000;

정답


CREATE INDEX idx_name ON employees(name);
CREATE INDEX idx_department_salary ON employees(department, salary);
CREATE INDEX idx_hire_date ON employees(hire_date);

-- 1. 인덱스가 활용되는 케이스
-- 1-1. 단순 동등 비교
SELECT * FROM employees WHERE name = 'Kim';  -- idx_name 사용

-- 1-2. 복합 인덱스 순서 준수
SELECT * FROM employees 
WHERE department = 'IT' AND salary > 50000;  -- idx_department_salary 사용

-- 1-3. LIKE 좌측 일치
SELECT * FROM employees 
WHERE name LIKE 'Kim%';  -- idx_name 사용

-- 1-4. IN 절 사용
SELECT * FROM employees 
WHERE department IN ('IT', 'HR');  -- idx_department_salary 사용

-- 1-5. 범위 검색
SELECT * FROM employees 
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';  -- idx_hire_date 사용

-- 1-6. 작업 범위 조건 결정
SELECT * FROM employees 
WHERE department = 'IT' 
  AND salary BETWEEN 50000 AND 70000;  -- salary는 체크 조건으로만 사용

-- 2. 인덱스가 활용되지 않는 케이스
-- 2-1. 컬럼 변형
SELECT * FROM employees 
WHERE UPPER(name) = 'KIM';  -- name 인덱스 사용 불가

-- 2-2. NOT 조건
SELECT * FROM employees 
WHERE name != 'Kim'; 

-- 2-2. LIKE 와일드카드가 앞에 있는 경우
SELECT * FROM employees 
WHERE name LIKE '%Kim';  -- idx_name 사용 불가

-- 3. 비활용적 사용 케이스
-- 3-1. 체크 조건으로 사용
SELECT * FROM employees 
WHERE salary > 50000 AND department = 'IT';  

-- 3-2. OR 조건 사용
SELECT * FROM employees 
WHERE department = 'IT' OR salary > 50000;

4. 인덱스와 외래키

MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성됩니다. 외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없습니다.

자식 테이블 변경 시 인덱시 잠금 동작

-- 커넥션1: 부모 테이블 레코드 변경
UPDATE tb_parent SET fd='changed-2' WHERE id=2;

-- 커넥션2: 자식 테이블 외래키 변경 시도 (대기)
UPDATE tb_child SET pid=2 WHERE id=100;

자식 테이블의 외래키 변경은 부모 테이블의 잠금이 해제될 때까지 대기
인덱스 설계 시 외래키로 인한 잠금 고려 필요

부모 테이블 변경 시 잠금 동작

-- 커넥션1: 자식 테이블 레코드 변경
UPDATE tb_child SET fd='changed-100' WHERE id=100;

-- 커넥션2: 부모 테이블 레코드 삭제 시도 (대기)
DELETE FROM tb_parent WHERE id=1;

자식 테이블의 잠금이 해제될 때까지 대기
자식 테이블 COMMIT/ROLLBACK 후 처리 가능

R-Tree 인덱스

전문 검색 인덱스

멀티 벨류 인덱스

유니크 인덱스

마무리하며

인덱스의 갯수는 3~4개 정도가 적당합니다.
- 너무 많은 인덱스는 새로운 Row를 등록할때마다 인덱스를 추가해야하고, 수정/삭제시마다 인덱스 수정이 필요하여 성능상 이슈가 있습니다.
- 인덱스 역시 공간을 차지합니다. 많은 인덱스들은 그만큼 많은 공간을 차지합니다.
- 특히 많은 인덱스들로 인해 옵티마이저가 잘못된 인덱스를 선택할 확률이 높습니다.

참고

https://jojoldu.tistory.com/243

인덱스 실사용

https://jojoldu.tistory.com/528

https://jojoldu.tistory.com/529?category=637935

https://jojoldu.tistory.com/530?category=637935

https://jojoldu.tistory.com/531?category=637935

저작자표시

'CS > 데이터베이스' 카테고리의 다른 글

[Real MySQL 9장] 옵티마이저와 힌트 (0)	2025.01.09
[Real MYSQL 10장] 실행 계획 (0)	2025.01.03
공유 락과 배타 락 (0)	2024.12.10
MySQL 아키텍쳐 (1)	2024.11.26

아무튼, 쓰기

MySQL 인덱스

1. 디스크 읽기 방식

1-1. HDD(기계식) && SSD(전자식)

1-2. 순차적 I/O && 랜덤 I/O

1-3. HDD의 I/O 방식

1-4. SSD의 I/O 방식

+) InnoDB 클러스터형 ‘데이터’ 저장방식

2. 인덱스

3. B-Tree 인덱스(InnoDB)

3-1. 구조 및 특성

InnoDB는 왜 한 번에 레코드 접근 안하고 B-Tree 한 번 더 타지?

3-2. B-Tree 인덱스 키 추가 및 삭제

3-2-1. 인덱스 키 추가

3-2-2. 인덱스 키 삭제

3-2-3. 인덱스 키 변경

3-2-4. 인덱스 키 검색

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

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

3-3-2. B-Tree 깊이

3-3-3. 선택도(기수성)

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

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

3-4-1. 인덱스 레인지 스캔

3-4-2. 인덱스 풀 스캔

3-4-3. 루스 인덱스 스캔

3-4-4. 인덱스 스킵 스캔

3-4-5. 다중 칼럼 인덱스 스캔

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

3-5-1.인덱스의 정렬

3-5-2. 내림차순 인덱스

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

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

3-6-2. 인덱스의 가용성

3-6-3.가용성과 효율성 판단

Quiz

4. 인덱스와 외래키

마무리하며

'CS > 데이터베이스' 카테고리의 다른 글

티스토리툴바

MySQL 인덱스

1. 디스크 읽기 방식

1-1. HDD(기계식) && SSD(전자식)

1-2. 순차적 I/O && 랜덤 I/O

1-3. HDD의 I/O 방식

1-4. SSD의 I/O 방식

+) InnoDB 클러스터형 ‘데이터’ 저장방식

2. 인덱스

3. B-Tree 인덱스(InnoDB)

3-1. 구조 및 특성

InnoDB는 왜 한 번에 레코드 접근 안하고 B-Tree 한 번 더 타지?

3-2. B-Tree 인덱스 키 추가 및 삭제

3-2-1. 인덱스 키 추가

3-2-2. 인덱스 키 삭제

3-2-3. 인덱스 키 변경

3-2-4. 인덱스 키 검색

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

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

3-3-2. B-Tree 깊이

3-3-3. 선택도(기수성)

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

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

3-4-1. 인덱스 레인지 스캔

3-4-2. 인덱스 풀 스캔

3-4-3. 루스 인덱스 스캔

3-4-4. 인덱스 스킵 스캔

3-4-5. 다중 칼럼 인덱스 스캔

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

3-5-1.인덱스의 정렬

3-5-2. 내림차순 인덱스

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

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

3-6-2. 인덱스의 가용성

3-6-3.가용성과 효율성 판단

Quiz

4. 인덱스와 외래키

마무리하며

'CS > 데이터베이스' 카테고리의 다른 글

관련글

티스토리툴바