Real My SQL 8.0 읽고 공부하기 - ⑧-①,② 인덱스

MySQL 8.0 버전까지 업그레이드가 되면서 MyISAM 스토리지엔진에서만 제공하던 전문검색, 위치기반 검색 기능 모두

InnoDB스토리지 엔진에서 사용할 수 있게 개선되었다.

 

MySQL서버의 옵티마이저가 발전하고 성능이 개선되었어도 여전히 관리자의 역할은 매우 중요하기 때문에

인덱스에 관한 지식은 개발자나 관리자에게 모두 중요하며, 쿼리 튜닝의 기본이 된다.

 

 

1) 디스크 읽기 방식

데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 큰 관건이다.

 

컴퓨터에서 CPU나 메모리 같은 주요 장치는 전자식 장치이지만 HDD의 경우 기계식 장치이다.

이런 HDD를 대체하기 위해 전자식 저장 매체인 SSD가 많이 출시 되고 있다.

SSD는 기존 하드 디스크 드라이브에서 데이터 저장용 플래터(원판)을 제거하고 그 대신 플래시 메모리를 장착하고 있다.

이는, 디스크 원판을 기계적으로 회전시킬 필요가 없어 아주 빠르게 데이터를 읽고 쓸 수 있다.

플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않는다.

 

부품 성능에 따라 다르지만 메모리와 HDD의 처리 속도는 10만배, SSD와는 1000배 처리 속도가 차이난다.

이에 따라 대부분 비용이 비싸지만 SSD를 채택하고 있다.

 

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

보이기도 하지만 랜덤 I/O에서는 확연히 다르다.

책에서 SDD와 HDD의 랜덤I/O 벤치마크 비교 결과는 약 7배 정도 차이난다.

 

■ 랜덤 I/O

랜덤 I/O는 3개의 페이지를 디스크에 기록하기 위해 3번 시스템 콜을 요청해야한다.

디스크 헤드를 3번 움직여야 하는 것이다.

 

■ 순차 I/O

순차 I/O는 3개의 페이지를 기록하기 위해 1번의 시스템 콜을 요청하면 된다.

디스크 헤드를 1번만 움직이면 되는 것이다.

 

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

달라진다.

이는 디스크 헤드를 가지지 않는 SSD에서도 랜덤I/O가 순차I/O보다는 스루풋이 떨어진다.

 

쿼리를 튜닝하는 것이 랜덤 I/O를 순차 I/O로 바꿔서 실행하는 방법은 많지 않고

랜덤 I/O자체를 줄이기 위해 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.

 

인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하며, 풀 테이블 스캔은 순차I/O를 사용한다.

그래서 큰 테이블의 레코드를 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도 할 수 있다.

이러한 형태는 OLTP(On-Line Transaction Processing)성격의 웹 서비스보다 데이터 웨어하우스, 통계작업에서 주로 사용된다.

 

 

 

---

 

 

2) 인덱스란?

많은 사람들이 인덱스를 언급할 때 책 맨 끝에 있는 찾아보기 (색인)으로 설명한다.

책의 내용은 데이터 파일에 해당하는 것이다.

 

DBMS도 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래걸리기 때문에

컬럼 값과 레코드의 저장된 주소를 키와 값의 쌍으로 삼아 인덱스로 만들어 두는 것이다.

인덱스는 컬럼의 값을 주어진 순서로 미리 정렬 해서 보관해서 최대한 빠르게 찾아갈 수 있게 한다. (ㄱ,ㄴ,ㄷ,ㄹ...)

 

DBMS에서 인덱스는 데이터 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 데이터 읽기 속도를 높이는 기능이다.

인덱스를 하나 더 추가할지 말지는 데이터의 저장 속도를 어디까지 희생할 수 있는지, 읽기 속도를 얼마나 더 빠르게 만들어야

하느냐에 따라 달려있다.

 

인덱스를 역할별로 구분해본다면 프라이머리 키와 보조키로 구분할 수 있다.

 

■ 프라이머리 키

레코드를 대표하는 컬럼의 값으로 만들어진 인덱스를 의미한다.

테이블에서 해당 레코드를 식별할 수 있는 기준 값이 되기 때문에 식별자라고도 불린다.

Null과 중복을 허용하지 않는다.

 

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

프라이머리 키를 제외한 모든 인덱스는 세컨더리 인덱스로 분류한다. 유니크 인덱스는 프라이머리 키와 성격이 비슷하고

대체할 수 있다고 해서 대체 키라고도 하는데, 별도로 분류하기도 하고 세컨더리 인덱스로 분류하기도 한다.

 

데이터 저장 방식(알고리즘) 별로 구분할 경우 상당한 많은 분류가 있겠지만 대표적으로 B-Tree 인덱스와

Hash인덱스로 구분할 수 있다.

 

■ B-Tree 알고리즘은 가장 일반적으로 사용되는 인덱스 알고리즘으로 컬럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는

알고리즘이다.

 

■ Hash 인덱스 알고리즘은 컬럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원한다.

하지만 값을 변형해서 인덱싱 하므로 전방(Prefix)일치와 같이 값의 일부만 검색하거나 범위 검색시에는

해시 인덱스를 사용불가능하다.

 

데이터 중복 허용 여부로서 유니크 인덱스나 유니크하지 않은 인덱스로 구분할 수 있는데

인덱스가 유니크한지 아닌지는 같은 값 1개만 존재하는지 1개이상 존재하는지로 나눌 수 있지만

옵티마이저는 유니크인덱스에 대해 동등(=) 조건의 레코드를 1개만 찾으면 더이상 찾지 않아도 된다는 것을 옵티마이저에게 알려준다.

그외에도 유니크 인덱스로 인한 MySQL의 처리방식 변화나 차이점이 많다.

 

인덱스를 기능 별로 분류해보면 전문 검색용 인덱스나 공간 검색용 인덱스 등을 예로 들 수 있다.