Real MySQL

[DB] Real MySQL - 5장 인덱스

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

5.1 디스크 읽기 방식

5.1.1 저장 매체

• 내장 디스크: 개인 pc에 저장되는 빠르고 안정적인 것들
• DAS(Direct Attached Storage): 본체에 다는 추가 디스크
• NAS(Network Attached Storage): 여러 컴퓨터에 동시에 연결
• SAN(Storage Area Network): 대용량의 스토리지 공간 제공

5.1.3 랜덤 i/o vs 순차 i/o

디스크 성능은 헤더 이동 없이 얼마나 많은 데이터를 한번에 기록하느냐에 있어서 순차가 랜덤보다 3배정도 빠르다.

쿼리튜닝은 랜덤 i/o를 줄이는 데에 목적이 있다.

인덱스 레인지 스캔은 랜덤 i/o, 풀 테이블 스캔은 순차 i/o 사용한다.

그래서 큰 테이블의 레코드 대부분을 읽을 때에는 인덱스를 안거는 것이 낫다.

5.2 인덱스란?

책의 색인처럼 칼럼의 값과 해당 레코드가 저장된 주소를 key-value 쌍으로 두어 주어진 순서에 따라 정렬된 값을 빠르게 찾기 위함이다.

읽기 속도 향상을 위해 CUD 성능을 희생한다.

• primary key

  레코드를 대표하는 칼럼의 값으로 만들어진 인덱스

• secondary key

  pk를 제외한 나머지 모든 인덱스, 유니크 인덱스의 경우 대체 키로도 불리운다.

5.3 B-Tree 인덱스

B-Tree 알고리즘을 사용하는데 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱한다.

인덱스 키를 추가할 때 적절한 위치를 검색하거나, 빈 자리가 없는 경우에는 리프 노드가 분리되어야 해서 비용이 많이 들게 된다.

InnoDB 에서는 insert, update, delete가 들어왔을 때 리프노드 있으면 바로, 없으면 임시로 기록하고 추후에 처리한다. (change buffering)

select는 트리 탐색을 거쳐 루트부터 브랜치, 리프까지 가게 된다. 인덱스 키값에 변형이 가해진 후에는 빠른 검색을 할 수 없으니 주의해야 한다.

인덱스 키 값의 길이가 커지면 디스크로부터 읽어야 하는 횟수가 많아져 느리지고 깊이도 깊어져 디스크 읽기가 더 많이 필요하므로 줄이는 것이 좋다.

인덱스를 이용해 걸러내는 것이 많아질 수록 효율적이기 때문에 유니크 값이 많은, 카디널리티가 높은, 중복된 수치가 적은 것을 인덱스로 거는 것이 좋다.

인덱스로 읽는게 전체의 20~25%가 넘으면 필터링이 효율적이다.

5.3.4 B-Tree 인덱스를 이용한 데이터 읽기

• 인덱스 레인지 스캔

  가장 빠른 방법, 인덱스를 통해 레코드 한 건 이상을 읽어오는 것

  검색해야할 인덱스의 범위가 결정됐을 때 레코드 수와 관계없이 사용하는 방식

  루트 노드 -> 브랜치 노드 -> 리프 노드의 레코드 순서대로 스캔 -> 검색 조건에 일치하면 데이터 파일에서 레코드 읽어옴

  이때 데이터 주소로 읽어오는데 한건 마다 랜덤 i/o가 발생해 20-25% 넘으면 직접 읽는 것이 효율적

• 인덱스 풀 스캔

  인덱스의 처음부터 끝까지 모두 읽는 방식, 일반적으로 조건절에 들어간게 인덱스의 첫 칼럼이 아니면 발생

  인덱스 칼럼만으로 조건을 처리하는 것이 효율적이라서 쿼리가 그렇게 해결할 수 있는 경우 사용되지만 인덱스를 잘 쓰는 방법은 아님

• 루스 인덱스 스캔

  듬성듬성 인덱스를 읽는 것, 중간마다 필요없는 인덱스는 무시하고 다음으로 넘어가는 형태

  Group by나 min, max 최적화에 사용

5.3.5 다중 컬럼 인덱스

컬럼의 순서에 따라 정렬되므로 신중히 순서를 정해야 한다.

5.3.6 인덱스의 정렬과 스캔 방향

MySQL에서는 인덱스를 구성하는 칼럼 단위로 정렬 방식을 혼합해서 생성하는 기능을 지원하지 않는다.

기본은 오름차순이다. 스캔의 경우 역순이나 정순 중 더 나은 경우를 이용한다.

작업의 범위를 결정하는 작업 범위 결정 조건은 많을수록 쿼리 처리 성능이 좋아진다.

단순히 거름종이 역할만 하는 필터링 조건은 많으면 오히려 느리게 되는 경우가 많다.

인덱스는 왼쪽 값 기준으로 오른쪽 값이 정렬되어 있다.

5.9 클러스터링 인덱스

테이블의 프라이머리 키에 대해서만 적용되는 내용으로 비슷한 것들을 묶어서 저장하는 형태로 구현된다.

pk 값에 의해 레코드의 저장 위치가 결정된다. pk 기반 검색이 매우 빠르지만, 레코드 저장이다 pk 변경이 상대적으로 느리다.

pk가 있으면 pk 키가 클러스터 키

없으면 not null 옵션의 unique index 중에서 첫번째 인덱스가 클러스터 키

그것도 없으면 자동으로 unique 값 가지도록 증가되는 칼럼을 내부적으로 추가하고, 클러스터 키 지정

장점: pk 검색 매우 빠름, 인덱스로만 처리할 수 있는게 많음(보조인덱스는 레코드 저장 주소가 아닌 pk 값 저장)

단점: 인덱스 크기 증가, 보조인덱스 성능 저하, insert 성능저하, pk 변경 시 성능 저하

5.10 유니크 인덱스

MySQL에서는 인덱스 없이 유니크 제약만 설정할 수 없다. 유니크와 보조 인덱스는 거의 유사하다. 유니크는 한개일 뿐

pk와 unique를 동일하게 생성하지 않도록 주의하자. 꼭 필요하지 않다면 보조 인덱스도 고려해보자.

5.11 외래키

외래키 제약이 설정되면 자동으로 연관되는 테이블 칼럼에 인덱스가 생성된다.

테이블 변경이 발생하는 경우 잠금 발생, 외래키와 무관한 변경은 최대한 잠금 발생하지 않도록 함