DB와 index

DB 와 I/O

데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건일 때가 많다. 쿼리 튜닝은 꼭 필요한 데이터만 읽도록, I/O를 줄이는 방향으로 쿼리를 개선하는 것을 의미한다.

SSD는 디스크 원판을 기계적으로 회전할 필요가 없어 HDD 보다 빠르게 읽고 쓸 수있다. SSD는 순차 I/O 도 HDD보다 빠르지만 랜덤 I/O에서 훨씬 빠르다.

성능 옵션에서 SSD도 고려해 보자. (SSD 초당 436, HDD 초당 60개 트랜잭션 처리)

디스크의 성능은 얼마나 많은 데이터를 한 번에 기록하느냐에 결정 될 수 있다. 따라서 빈번하게 읽고 쓰는 랜덤 I/O 보다, 순차 I/O가 빠르다.

 

인덱스

DBMS에서 인덱스는 데이터의 저장성능을 희생하고(일관성 정렬을 유지해야하기 때문에) 그 대신 데이터의 읽기 속도를 높이는 기능이다.

역할별 구분

• 프라이머리 키 : 레코드를 대표하는 컬럼의 값으로 만들어진 인덱스
• 세컨더리 인덱스 : 프라이머리 키를 제외한 나머지 모든 인덱스 InnoDB 세컨더리 인덱스 검색은 다음과 같이 된다. 세컨더리 인덱스 리프 노드 검색(리프노드에는 프라이머리 키가 있다) → 프라이머리 인덱스 검색 → 프라이머리 리프노드에서 레코드 읽기

알고리즘 구분

• B-Tree 알고리즘 인덱스 추가를 대략적으로 계산하는 방법: 테이블에 레코드를 추가하는 작업 비용을 1로 가정하면, 인덱스에 키를 추가하는 작업 비용을 1.5로 예측한다. 예를 들어 인덱스가 3개, 작업 비용이 1인 경우, 1 + 1.5*3 = 5.5로 예측한다. 키 값이 삭제되는 경우 : B-Tree의 리프 노드를 찾아 삭제 마크만 작업 (soft delete) → 해당 공간을 방치하거나 재활용한다. 키 값이 변경되는 경우 : 키 값을 삭제후에 새로운 키값을 추가하는 형태로 처리된다. 인덱스를 이용한 검색에서 인덱스의 키 값에 변형이 가해지고 비교하는 경우 B-Tree의 검색 기능을 사용할 수 없다. 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬된다. 하나의 컬럼 뿐만아니라 다중칼럼 인덱스도 포함된다.
• Hash 인덱스 알고리즘
• Fractal-Tree 인덱스
• Merge-Tree 인덱스

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 또는 블록이라고 하며, 디스크의 읽기 및 쓰기 작업의 최소 작업 단위이다. 인덱스 역시 페이지 단위(기본 16KB)로 관리된다.

B-Tree의 자식 노드를 몇개 까지 가질 수 있는 가는 페이지 크기와 키값의 크기에 따라 결정 된다. 인덱스 페이지의 구성은 키값과 자식 노드 주소(6~12, 평균 12바이트)를 가지고 있다. 만약 키값이 16바이트라면, 페이지가 16KB 다음과 같이 계산된다. 16* 1024(페이지 크기) / (16 (키값) + 12(자식 노드 주소)) = 583(자식노드 개수)

알맞는 인덱스 키값이 중요한데, 인덱스 키값이 증가가 된다면 자식노드의 개수가 줄어, 인덱스 페이지를 디스크로 읽어야 하는 횟수가 늘어난다. 또한 인덱스 키가 길어지는 것은 전체적인 인덱스의 크기가 커지는 것이다. 이는 메모리에 인덱스와 레코드를 캐싱하는 버퍼풀에 더 적은 인덱스를 담아야 하기에 효율이 떨어진다.

인덱스를 이용한 읽기 에는 손익을 따져야한다. 일반적으로 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이, 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 비용이 더 많이 드는 것으로 예측한다. 따라서 20~25%이상 읽는다면, 인덱스를 이용하지 않고 테이블을 모두 읽는 방법이 효율적이다

인덱스 스캔 방법

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

인덱스 레인지 스캔

1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다 (인덱스 탐색)
2. 탐색된 위치부터 필요한 만큼 인덱스를 읽는다 (인덱스 스캔)
3. 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 레코드를 읽는다

쿼리가 필요로 하는 데이터에 따라 3번 과정 필요 없을 수 있음, 이를 커버링 인덱스라고 한다.

• 커버링 인덱스는 쿼리의 모든 항목이 인덱스 컬럼으로 이루어져 있기 때문에 인덱스만으로 데이터를 추출할 수 있는 것입니다.

루스 인덱스 스캔

말 그대로 느슨하게 또는 듬성듬성 인덱스를 읽는 것, 인덱스 레인지 스캔과 비슷하게 작동하지만, 중간에 필요 하지 않는 인덱스 키 값은 무시하고 읽지 않는다.

인덱스 스킵 스캔

index의 중간 칼럼이 없는 경우 풀 스캔이 이루어 졌다. 하지만 Mysql8.0 부터는 인덱스 스킵 스캔을 활용해서 최적화 할 수 있다. 인덱스 스킵 스캔은 내부적으로 중간에 컬럼이 가능한 값을 구한다음, 내부적으로 해당 중간값을 추가해서 인덱스를 타게 만들어준다. Mysql8.0 버전 부터 새로 도입된 기능이어서 다음과 같은 단점이 있다.

• where 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야 한다
• 쿼리가 인덱스에 존재하는 컬럼만으로 처리가 가능해야 한다(커버링 인덱스)

다중 컬럼 인덱스

두개 이상의 컬럼으로 구성된 인덱스이다. 다중 컬럼 인덱스의 정렬은 n+1 컬럼은 n의 컬럼에 의존해서 정렬된다. 따라서 n+1 컬럼의 정렬은 n의 컬럼이 같을 때만 의미가 있다. 이 때문에 다중 컬럼 인덱스는 각 컬럼의 순서가 상당히 중요하다

인덱스의 정렬은 5.7 버전까지 칼럼 단위로 정렬 순서를 혼합해서 사용 못했다. ( 각 컬럼이 모두 ASC or DESC) 하지만 8.0 부터 칼럼 단위로 정렬 순서를 혼합한 인덱스를 사용할 수 있다.

B-Tree 인덱스는 특성상 다음 조건에서 사용할 수 없다.

• NOT-EQUAL 비교
• LIKE %?? 뒷부분 일치 형태로 문자열 패턴 비교된 경우
• 다른 연산자로 인덱스 컬럼이 변형되고 비교된 경우
• 데이터 타입이 서로 다른 비교(인덱스 컬럼의 타입을 변환)해야 하는 경우
• 문자열 데이터 타입의 콜레이션이 다른 경우

Mysql의 공간인덱스는 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스이다. 내부적으로 B-Tree와 흡사하나, R-Tree는 2차원 공간 개념의 값이다.

Mysql 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(프라이머리 키 기준)들끼리 묶어 저장하는 형태로 구현되는데, 이는 비슷한 값을 동시에 조회하는 경우가 많다는 것을 착안한 것이다. InnoDB 스토리지 엔진에서만 지원한다. 클러스터링 인덱스에서 중요한 것은 프라이머리 값에 의해 레코드의 저장 위치가 결정된다는 것이다. 따라서 프라이머리 키 값이 변경된다면, 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미한다.

프라이머리 키가 없다면, 다음 우선순위대로 프라이머리 키를 대체할 컬럼을 선택한다.

1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
2. NOT NULL 옵션의 유니크 인덱스 중에서 첫번째 인덱스를 클러스터링 키로 선택
3. 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후, 클러스터링 키로 선택

자동으로 추가된 프라이머리 키는 사용자에게 노출되지 않으며, 쿼리 문장에서 사용을 못한다. 따라서 명시적으로 프라이머리 키를 사용하는 것이 좋다.