CS/Real MySQL 8.0 요약

8장 - 인덱스

Junuuu 2023. 11. 13. 00:01

디스크 읽기 방식

  • 순차 I/O - 3개의 페이지를 기록하기 위해 1번의 시스템 콜(OS)
  • 랜덤 I/O - 3개의 페이지를 기록하기 위해 3번의 시스템 콜(OS)

 

데이터베이스의 성능 튜닝은 랜덤 I/O를 줄이는 것입니다.

랜덤 I/O를 줄인다는 것은 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것입니다.

 

인덱스란?

책의 색인에 비유되며 책의 내용은 데이터 파일에 해당됩니다.

책에서 "ㄱ", "ㄴ", "ㄷ" 형태로 정렬되어 있는 것처럼 인덱스로 키가 정렬되어 있습니다.

정렬되어 있기 때문에 저장을 수행할 때 성능을 희생되고 읽기를 수행할 때 성능이 좋습니다.

따라서 모든 칼럼에 인덱스를 거는 건 역효과를 낼 수 있습니다.

 

인덱스는 PK인지 아닌지에 따라 프라이머리 키와 세컨더리 키로 구분할 수 있습니다.

저장방식은 B-Tree 인덱스 Hash 인덱스가 대표적이며 Hash 인덱스는 범위 검색이 불가능합니다.

 

데이터의 중복여부로 유니크 인덱스, 논유니크 인덱스로 구분할 수 있습니다.

데이터가 유니크하다면 옵티마이저 입장에서 하나의 레코드만 찾으면 되기 때문에 좋습니다.

 

B-Tree 인덱스

Balanced Tree의 약자로 항상 정렬된 상태로 유지됩니다.

루트노드, 브랜치 노드, 리프 노드로 구성되어 있으며 리프노드에는 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있습니다.

INSERT 순으로 저장된다고 오해하고 있었지만 데이터 파일의 레코드는 정렬되어 있지 않습니다.

 

InnoDB의 인덱스 파일

InnoDB를 사용하면 프라이머리 키가 ROWID의 역할을 수행합니다.

ROWID란 row identifier의 약자로 레코드에 대한 고유 식별자 또는 주소입니다.

 

MyISAM 테이블을 인덱스가 물리적인 주소를 가지는 반면 InnoDB 테이블은 프라이머리 키를 주소처럼 활용하기 때문에 논리적인 주소를 가집니다.

인덱스로 바로 데이터 파일을 찾아가지 않고 프라이머리 키 인덱스를 한번 더 탐색한 후, 리프 페이지에 저장된 레코드를 읽습니다.

세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해 PK를 저장하는 B-Tree를 다시 읽어야 합니다.

 

인덱스의 추가 삭제 변경

MySQL 서버가 내부적으로 대부분 체인지 버퍼를 활용하여 지연 처리되기 때문에 사용자에게는 특별한 악영향이 없습니다.

 

 

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

인덱스 키 값의 크기

인덱스 페이지 크기를 기본값인 16KB라고 가정하였을 때 인덱스 키가 16바이트, 자식 노드의 주소 영역이 12바이트라고 가정하겠습니다.

이런 경우 하나의 인덱스 페이지에 585개를 저장할 수 있습니다.

하지만 키값의 크기가 32바이트인 2배로 늘어나면 372개를 저장할 수 있습니다.

이때 SELECT 쿼리가 500개를 읽어야 한다면 전자는 인덱스 페이지 한 번으로 해결될 수 있지만 후자는 2번 이상 읽어야 합니다.

이는 그만큼 느려진다는 것을 의미합니다.

 

Cardinality

성별을 남/여로 구분한다면 정렬로 반만 구별할 수 있으나, 모든 값이 유니크하다면 인덱스로 하나의 레코드만 딱 가져올 수 있습니다.

 

인덱스 Range Scan과 FullScan

보통 인덱스를 통해 읽는 것은 직접 읽는 것보다 4~5배 정보 비용이 더 많이 드는 작업입니다.

옵티마이저는 전체 테이블 레코드의 20~25% 넘으면 인덱스를 이용하지 않고 테이블을 모두 읽어오는 방식을 선택합니다.

20~25% 보다 많은 레코드를 읽을 때는 힌트를 사용해도 성능상 얻을 수 있는 이점이 없습니다.

 

Range Scan의 절차

  • 인덱스의 조건을 만족하는 값이 지정된 위치 탐색 (Index Seek)
  • 1번에서 탐색된 위치부터 필요한 만큼 읽기 (Index Scan)
  • 인덱스 키와 레코드 주소를 통해 실제 데이터를 가져온다.

이때 커버링인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되어 랜덤 읽기가 상당히 빨라집니다.

 

Index FullScan

RangeScan과는 다르게 처음부터 끝까지 읽지만 보통 인덱스의 크기는 테이블의 크기보다 작아 테이블 FullScan보다는 효율적입니다.

보통 인덱스는 A,B,C 칼럼의 순서로 만들어졌지만 B칼럼이나 C칼럼으로 검색하는 경우에 활용됩니다.

 

루스 인덱스 스캔

Oracle의 인덱스 스킵 스캔과 비슷합니다.

group by 또는 MAX MIN 함수를 최적화하는 경우에 중간에 필요하지 않은 인덱스 키 값은 무시하고 다음으로 넘어갑니다.

이를 사용하기 위해서는 여러 가지 조건을 만족해야 합니다.

 

인덱스 스킵 스캔

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

MySQL8.0부터 도입된 기능으로 기존에 라면 Index FullScan을 사용할만한 쿼리를 skip scan을 활용합니다.

 

 

인덱스의 정렬 및 스캔 방향

인덱스를 생성할때는 오름차순, 내림차순으로 생성됩니다.

하지만 오름차순으로 생성되었지만 그 인덱스를 거꾸로 읽으면 내림차순으로 정렬된 인덱스로도 사용될 수 있습니다.

이는 쿼리에 따라 옵티마이저가 결정합니다.

 

MySQL 8.0 부터는 정렬 순서를 혼합 인덱스로 생성할 수 있게 되었습니다.

A는 오름차순, B는 내림차순

 

아래와 같은 이유로 내부적으로 스토리지 엔진에서 인덱스 역순 스캔이 정순 스캔보다 느립니다.

  • 페이지 잠금이 정순 스캔에 적합한 구조
  • 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조

 

인덱스의 가용성과 효율성

동등비교, 크다, 작다와 같은 범위 조건인지에 따라 인덱스 칼럼의 효율이 달라지게 됩니다.

 

두 가지 케이스로 인덱스를 생성했다고 가정합니다.

  • 케이스 A: dept_no, emp_no
  • 케이스 B: emp_no, dept_no

 

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

 

케이스 A인 경우에는 해당 조건에 맞는 레코드를 찾고 dept_no가 d002가 아닐 때까지 읽어오면 됩니다.

케이스 B인 경우에는 emp_no >= 10144 인 레코드를 찾고 모든 레코드에 대해 d002인지 비교하는 과정을 거쳐야 합니다.

이런 작업을 필터링이라고 하며 필터링은 오히려 성능에 좋지 않습니다.

 

인덱스를 사용할 수 없는 다양한 경우들이 존재하며 만능이 아님을 주의해야 합니다.

 

 

가볍게 R-Tree 인덱스

공간 인덱스로 2차원 데이터를 인덱싱 하고 검색하는 목적으로 사용됩니다.

GPS 등의 공간정보가 필요할 때 사용합니다.

 

Full Text search 인덱스

특정 키워드가 포함된 문서를 검색하는 전문(Full Text) 검색에서는 B-Tree를 사용할 수 없어 별도의 인덱스를 활용해야 합니다.

 

 

함수 기반 인덱스

보통 칼럼의 값 일부 또는 전체에 대해 인덱스 생성을 활용합니다.

하지만 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때도 있는데, 이때 MySQL 8.0부터는 함수 기반 인덱스를 활용할 수 있습니다.

 

  • 가상 칼럼을 이용한 인덱스
  • 함수를 이용한 인덱스

 

Json 인덱스 (멀티 밸류 인덱스)

요새 json 데이터 타입을 지원하면서 해당 원소들에 대해 인덱스 니즈가 발생하였습니다.

MySQL 8.0 부터 MongoDB에 준하는 json을 활용할 수 있게 되었습니다.

 

클러스터링 인덱스

클러스터링 = 여러 개를 하나로 묶는다는 의미

테이블의 레코드를 PK를 기준으로 묶어서 저장하며 이유는 주로 비슷한 값들을 동시에 조회하는 경우가 많기 때문입니다.

* PK에 대해서만 적용되는 내용이며 InnoDB 스토리지 엔진에서만 지원합니다.

 

프라이머리 키 값이 변경되면 레코드의 물리적인 저장위치도 바뀌게 되며 이로 인해 신중하게 PK를 결정해야 합니다.

리프노드에 모든 칼럼이 저장되어 있으며 사실 PK가 변경될 일은 거의 없습니다.

 

클러스터링 키 지정순서

  • 1. PK가 있는 경우 PK를 클러스터링 키로 선택
  • 2. NOT NULL 옵션의 유니크 인덱스 중에 첫 번째 인덱스를 클러스터링 키로 선택
  • 3. 자동으로 Unique하게 증가되는 칼럼을 추가하고 클러스터링 키로 선택

 

이로 인해 PK로 범위검색 등이 매우 빠름!

보통 웹서비스에서는 읽기와 쓰기 비율이 2:8, 1:9이므로 느린 쓰기를 감수하고 빠른 읽기를 유지하는 게 중요하다!

 

유니크 인덱스 사용시 주의사항

성능이 더 좋아질 것으로 생각하여 불필요하게 유니크 인덱스를 생성하지 않는 것이 좋습니다.

유일성이 꼭 보장되는 칼럼에 대해서는 유니크 인덱스를 생성하되 그렇지 않다면 세컨더리 인덱스 생성에 대해 고려해보아야 합니다.

 

InnoDB 스토리지 엔진에서는 인덱스 키의 저장을 체인지 버퍼를 활용하는데 유니크 인덱스 쓰기 시에는 중복된 값을 체크해야 해서 버퍼링이 불가능해서 더 느리게 작동합니다.