MySQL(4)

오늘 배운 것

4주차 학습내용 (8.0 인덱스 ~ 8.4R-tree 인덱스)

8.0 인덱스

인덱스는 DB쿼리 성능에서 빠질 수 없는 요소다. 이번 장에서는 MySQL 쿼리의 개발이나 튜닝을 설명하기 전에 MySQL에서 사용가능한 인덱스의 종류 및 특성을 알아보자.

각 인덱스의 특성과 차이는 상당히 중요하다. 물리수준의 모델링을 할 때도 중요한 요소가 될것이다. MySQL 8.0까지 업그레이드 되면서 다른 상용 DBMS에서 지원하는걸 지원하게됐고,

MyISAM에서만 제공하던 전문검색이나 위치기반 검색기능도 모두 InnoDB에서 사용할 수 있게 개선됐다.

그러나 아무리 MySQL 옵티마이저의 성능이 개선되어도 관리자의 역할은 매우 중요하고. 쿼리튜닝의 기본이 될것이다.

8.1 디스크 읽기방식

CPU < 메모리 < HDD, SSD 순으로 시간이 많이 걸린다. 따라서 DB 성능 튜닝의 관건은 디스크 IO를 얼마나 줄이냐에 달려있다.

8.1.1 HDD와 SSD

순차에선 SSD와 HDD가 비슷하거나 SSD가 조금 더 빠르다. 근데 SSD의 장점은 랜덤 IO에서 나온다. 훨씬 빠르다.

심지어 DB서버에서 순차 IO작업은 비중이 크지않고 대부분이 랜덤IO를 통해 작은 데이터를 읽고 쓰는 작업이므로 SSD의 장점이 DBMS에 최적이다.

8.1.2 랜덤 IO와 순차 IO

HDD를 생각했을때, 순차 IO작업을 하면 헤더를 한번 옮기고 쭉 디스크 돌리면서 읽고 일을 한다. 랜덤 IO를 하면 헤더 움직였다가 찔끔쓰고 움직이고 찔끔쓰고 한다.

이 과정 자체가 헤더 돌아가는게 물리적으로 돌아가는거라서 시간이 오래 걸린다.

SSD도 마찬가지다. 랜덤 IO가 여전히 순차 IO보다 ThroughPut이 떨어진다.

DB작업 대부분이 랜덤IO+ 소량 데이터 읽고쓰기이므로, MySQL서버에서도 그룹커밋이나 바이너리 로그 버퍼 또는 InnoDB로그버퍼등이 내장돼있다.

-> 그러면 쿼리튜닝이란게 랜덤IO를 순차IO 로 바꾸는건가?

그다지 많지 않다. 일반적으로 쿼리를 튜닝한다는것은 랜덤IO자체를 크게 줄이는것이 목적, 랜덤 IO를 줄인다는 것은 쿼리를 처리하는데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것.

  • 인덱스 레인지 스캔은 랜덤IO를 주로 사용한다.
  • 풀테이블 스캔은 순차IO를 사용한다.

그래서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 쓰는게 나을대도 있다. 순차 IO가 랜덤IO보다 훨씬 빨리 많은 레코드를 읽어올 수 있기 때문. 보통 OLTP 성격의 웹서비스 보다는 데이터 웨어하우스나 통계작업에서 주로 활용된다.

8.2 인덱스란?

흔히 인덱스를 “색인” 이라고 한다. 칼럼의 값을 주어진 순서대로 미리 정렬해서 보관한다.

DBMS에서 데이터의 저장(INSERT, UPDATE, DELETE)를 희생하고 그 대신 데이터 읽기 속도를 높인것이다. select 쿼리 문장의 WHERE에 사용되는 COL이라고 해서 전부 인덱스로 생성하면 데이터 저장성능 감소, 인덱스 크기가 비대해져 오히려 역효과

인덱스는 데이터를 관리하는 방식과 중복 값의 허용 여부 등에 따라 여러가지로 나눌 수 있다.

이 책에서는 Key라는 말과 Index는 같은 의미로 사용하겠다

인덱스를 역할별로 구분해본다면, 프라이머리 키(PK)와 보조 키(세컨더리 인덱스)로 구분할 수 있다.

PK는 레코드를 대표하는 값으로 만들어진 인덱스, 이 칼럼(혹은 칼럼의 조합)은 NULL을 허용 x, 중복 허용 x

나머지 모든 인덱스는 세컨더리 인덱스로 분류된다. Unique Index는 PK와 성격이 비슷하고 PK를 대체할 수도 있으므로 대체 키 라고도 하는데, 별도로 분류하기도 하고 세컨더리 인덱스로 분류하기도 한다.

데이터 저장방식(알고리즘) 으로 구분할 경우, 분류가 많은데 대표적으로 B-Tree 인덱스와 Hash 인덱스로 구분할 수 있다. 최근에는 Fractal-Tree 인덱스나 로그 기반의 Merge-tree 를 쓰는 DBMS도 있다. 하여튼 대중적으로는 이렇다.

B-Tree : 가장 일반적으로 사용하는 인덱스 알고리즘, 오래전에 도입되었고 그만큼 성숙되었다. B-Tree 인덱스는 칼럼의 값을 변형하지 않고 원래의 값으로 인덱싱하는 알고리즘이다. R-Tree는 B-Tree의 응용이다.

hash : 칼럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘, 매우 빠른 검색. 하지만 값을 변형해서 인덱싱하므로 Prefix일치와 같이 값의 일부만 검색하거나 범위검색을 할대는 해시 인덱스를 못쓰고 주로 메모리 기반의 DB에서 많이 사용한다.

데이터의 중복 허용 여부로 분류하면

unique 인덱스

non-unique 인덱스

인덱스가 unique 한지 아닌지는 단순히 같은 값이 1개냐 여러개가 존재할 수 있는지 를 의미하지만, 실제 DBMS의 쿼리를 실행하는 옵티마이저애게는 상당히 중요한 문제다.

  • unique 인덱스에 대해 =, 동등조건으로 검색한다는 것은 항상 1건의 결과만 나온다는걸 의미하므로 옵티마이저는 편하다. 뿐만아니라 unique 인덱스로 인한 MySQL의 처리방식이나 차이점이 많은데 나중에 인덱스와 쿼리의 실행계획 에서 다룬다.

인덱스의 기능별로 분류하면 전문 검색용 인덱스, 공간 검색용 인덱스.. 많은데 일단 다음에

8.3 B-Tree 인덱스

가장 일반적, 가장 먼저 도입된 인덱스. B+tree, B* tree로도 변형되기도 한다.

B는 Balanced를 의미한다.

값의 앞부분만 잘라서 관리하기는 하지만, 칼럼의 원래 값을 변형시키지 않고, 정렬된 상태를 유지한다.

8.3.1 구조 및 특성

B트리인덱스구조

인덱스의 키 값은 모두 정렬되어있지만, 데이터 파일의 레코드는 정렬되어있지 않다.(리프노드까진 정렬, 데이터 파일은 제각각)

데이터파일의 레코드는 INSERT 된 순서대로 저장되어 있지 않다. 만약 중간에 삭제, 변경없이 INSERT만 된다면 맞지만, 레코드가 삭제되어 빈 공간이 생기면 INSERT시 가능한 삭제된 공간 재활용 하도록 설계되므로 항상 INSERT순서대로 저장되지는 않는다.

대부분 RDBMS의 데이터 파일에서 레코드는 정렬 안되고 임의의 순서대로 저장된다. 그러나 InnoDB 테이블은 레코드가 클러스터되어 저장되므로, 기본적으로 PK순서대로 정렬되어 저장된다. INNODB에서는 사용자가 별도의 설정없이 디폴트로 클러스터링 테이블이 생성된다.

results matching ""

    No results matching ""