[데이터베이스] MySQL 인덱스

데이터베이스의 성능 튜닝은 어떻게 디스크의 I/O를 줄이는게 관건일떄가 상당히 많습니다.

인덱스를 알기 전에 디스크에 대해 알아보도록 하겠습니다.

 

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

컴퓨터에서 CPU나 메모라 같은 주요 장치는 대부분 전자식 장치입니다. 하지만 하드 디스크 드라이브는 기계적 장치입니다.

그렇기에 데이터베이스서버에서는 디스크 장치가 병목이 됩니다. 이러한 기계식 하드 디스크를 대체하기 위해 SSD가 많이 출시 되고있습니다.

전자식 장치 : 전기 신호를 이용해 동작하는 장치
기계식 장치 : 기계 부품의 물리적인 움직임으노 동작하는 장치

 

디스크 헤더를 움직이지 않고 한번에 많은 데이터를 읽는 순차 I/O에서는 SSD가 HDD보다 조금 빠르거나 비슷한 성능을 보입니다.

하지만 SSD는 랜덤 I/O가 훨씬 빠르다는 것입니다. 데이터베이스 서버에서는 주로 랜덤 I/O를 통해 작은 데이터를 일고 쓰는 작업이 대부분이므로 SSD가 DMBS에 최적이라고 볼 수 있습니다.

랜덤 I/O : 데이터가 여기저기 흩어져 있어, 특정 위치로 점프해서 읽거나 써야 하는 작업
순차 I/O : 데이터가 디스크상에서 연속적으로 저장되어있어, 순서대로 읽거나 쓰는 작업

 

디스크 원판을 가지지 않는 SSD는 두 개의 성능차이가 별로 없을꺼라 예상되지만 실제로는 그렇지 않습니다.

SSD또한 랜덤 I/O가 순차 I/O보다 성능이 떨어집니다.

 

실제로는 쿼리를 튜닝해서 랜덤 I/O를 순차 I/O로 바꿔서 실행하는 방법은 많지 않습니다. 일반적으로 쿼리를 튜닝한다는 말은 랜덤 I/O자체를 줄여주는것이 목적이라고 생각하면 될꺼같습니다. 랜덤I/O를 줄인다는말은 쿼리를 처리하는데 꼭 필요한 데이터만 읽도록 쿼리를 개선한다는 말입니다.

인덱스

DBMS도 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래걸립니다.

그래서 컬럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로  삼아 인덱스를 만드는 것 입니다.

또한 빠르게 찾아갈 수 있게 주어진 순서로 미리 정렬해서 보관합니다.

인덱스는 SortedList 같은 자료구조를 사용합니다.
데이터 파일은 ArrayList 같은 자료구조를 사용합니다.

 

SortedList의 특성은 저장할때는 느리지만 읽기 작업은 빠릅니다. 인덱스도 똑같습니다. INSERT나 UPDATE 및 DELETE 문장처리가 늦습니다. SELECT를 빠르게 할 수 있습니다. 그렇기에 개발자들은 저장속도를 희생해서 읽기 속도를 얼마나 더 빠르게 만들어야할지 결정해야합니다.

 

인덱스는 역할별로 구분한다면 프라이머리 키와 보조키 (세컨더리 인덱스)로 구분해 볼 수 있습니다.

프라이머리 키 : 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스, NULL을 허용하지 않으며 중복을 허용하지 않는다.
세컨더리 인덱스 : 프라이머리 키를 제외한 나머지 모든 인덱스를 의미합니다.

 

B-Tree

B-Tree는 데이터베이스의 인덱싱 알고리즘 가운데 가장 많이 사용되는 알고리즘입니다.

인덱스는 정렬되어있지만 데이터 파일의 레코드는 INSERT된 순서대로 저장되어있지 않습니다.

하지만 InnoDB는 테이블에서 레코드는 클러스터되어 디스크에 저장되므로 기본적으로 프라이머리 키 순서대로 정렬되어 저장됩니다.

 

MYSQL에서 InnoDB는 다음과 같은 관계를 가집니다.

 

다른 데이터베이스에서는 종종 ROWID라는 내부 식별자가 있어서, 특정 행의 저장 위치를 직접 나타냅니다.

하지만 InnoDB에서는 이런 ROWID가 따로 존재하지 않습니다. 대신, 프라이머리 키가 행의 고유 주소처럼 사용됩니다.

프라이머리 키 값 자체가 "이 데이터가 어디에 저장되어 있는지"를 나타내는 논리적 주소가 된다는 뜻입니다.

 

따라서 위의 그림같이 세컨더리 인덱스를 통해 데이터의 값을 가져오기 위해서는 프라이머리 키를 저장하고 있는 B-Tree를 거쳐 데이터를 가져옵니다.

ROWID : 행이 디스크에 저장된 물리적 위치를 참조하는 값

인덱스 검색

B-Tree를 이용한 검색은 100%일치 또는 값의 앞부분(Left-most-part)만 일치하는 경우만 사용할 수 있습니다.

부등로("<,>") 비교조건에서도 인덱스를 활용할 수 있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없습니다.

 

“100% 일치 또는 값의 앞부분만 일치하는 경우에만 사용할 수 있다”는 뜻은 다음과 같습니다.

아래와 같은 데이터베이스가 존재한다고 생각해봅시다.

CREATE INDEX idx_name_email ON users(name, email);

이 경우 다음과 같은 쿼리를 사용할 수 있습니다.

-- name만으로 정확히 일치 → 인덱스 사용
SELECT * FROM users WHERE name = 'Alice';

-- name으로 범위 조회 → 인덱스 사용
SELECT * FROM users WHERE name LIKE 'A%';

-- name + email 모두 조건 → 인덱스 완전 활용
SELECT * FROM users WHERE name = 'Alice' AND email = 'a@example.com';

하지만 다음과 같은 쿼리를 인덱스를 적용해서 검색할 수 없습니다.

-- email만 사용 → 인덱스 사용 못함
SELECT * FROM users WHERE email = 'a@example.com';

 

그 이유는B-Tree 인덱스는 "앞에서부터 순서대로 정렬"되어 있기 때문입니다. 즉, 복합 인덱스의 경우, 앞의 컬럼(name)을 모르면 email을 정렬 구조로 탐색할 수 없습니다.

 

또한, 인덱스 키에 변형된 값이 사용되면, B-Tree 인덱스는 그 값을 기준으로 정렬되어 있지 않기 때문에, 인덱스를 통한 빠른 검색이 어려워집니다.

 

인덱스 키값의 크기

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 또는 블록(Block)이라고 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다.

InnoDB 페이지 크기의 기본값은 16KB다.

인덱스의 키가 16바이트라고 가정한다면 다음 그림과 같은 인덱스 페이지가 구성됩니다.

여기서 자식 노드는 복합적인 정보가 담긴 영역이며 6바이트 부터 12바이트까지 여러가지 값을 가질 수 있습니다.

 

 

하나의 인덱스 페이지에 몇개의 키를 저장할 수 있을까요?

계산을 해보면 16 * 1024 ( 인덱스 페이지 크기)  / 16(인덱스 키값 크기)+ 12 (자식노드 크기) => 585개

인덱스의 크기가 2배로 늘어난다면 16 * 1024 ( 인덱스 페이지 크기)  / 32(인덱스 키값 크기)+ 12 (자식노드 크기) => 372개

 

만약 SELECT 쿼리가 레코드 500개를 읽어야 한다면 전자는 한번의 인덱스 페이지로 가능한다면 후자는 2번 이상 디스크로부터 읽어야합니다.

인덱스 키값의 길이가 길어진다면 전체적인 인덱스의 크기가 커지는 것을 의미합니다.

B-Tree 깊이

B-Tree 인덱스의 깊이는 상당히 중요하지만 직접 제어할 방법은 없습니다.

인덱스 키값의 평균 크기가 늘어단다면 어떤 현상이 일어나는지 보겠습니다. 깊이는 3으로 가정하겠습니다.

인덱스의 키값이 16바이트인 경우 최대 2억 (585 * 585 *585) 개 정도의 키값을 담을 수 있습니다. 

값이 2배라면 (372*372*372) 개로 줄어듭니다. B-Tree 깊이는 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지 직결되는 문제입니다.

 

출처 :

https://product.kyobobook.co.kr/detail/S000001766482

Real MySQL 8.0 (1권) | 백은빈 - 교보문고