8.1 디스크 읽기 방식

데이터베이스 성능 튜닝은 어떻게 디스크 IO를 줄이느냐가 관건일 때가 많으므로, 랜덤 IO, 순차 IO와 같은 디스크 읽기 방식을 먼저 알아보자.

8.1.1 하드 디스크 드라이브와 솔리드 스테이트 드라이브(HDD, SSD)

• SSD는 기존 하드 디스크 드라이브에서 데이터 저장용 플래터(원판)을 제거하고 그 대신 플래시 메모리를 장착하고 있다. 그래서 아주 빠르고, 전원이 공급되지 않아도 데이터가 삭제되지 않는다.
• SSD의 장점은 기존 하드 디스크 드라이브보다 랜덤 IO가 훨씬 빠르다는 것이다. 데이터베이스 서버에서 순차 IO 작업은 그닥 비중이 크지 않고, 랜덤 IO를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD의 장점은 DBMS용 스토리지에 최적이다.
  

8.1.2 랜덤 IO와 순차 IO

• 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다.
• 데이터베이스의 대부분 작업은 이런 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에서는 그룹 커밋이나 바이너리 로그 버퍼나 InnoDB 로그 버퍼 등의 기능이 내장돼 있다.
• 랜덤 IO를 줄인다는 것은 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.

8.2 인덱스란?

• DBMS가 데이터베이스 테이블의 모든 데이터를 검색해 원하는 결과를 가져오려면 시간이 오래 걸린다. 그래서 컬럼들의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스를 만들어두는 것이다. DBMS의 인덱스는 컬럼의 값을 주어진 순서로 미리 정렬해서 보관한다.
  
• DBMS의 인덱스는 SortedList와 마찬가지로 저장되는 칼럼의 값을 이용해서 항상 정렬된 상태를 유지한다. 데이터 파일은 ArrayList와 같이 저장된 순서대로 별도의 정렬 없이 그대로 저장한다.
• DBMS의 인덱스는 데이터 저장(CUD) 성능을 희생하고, 읽기 속도를 높이는 기능이다.
• 역할별로 인덱스를 구분한다면, 프라이머리 키와 보조키로 구분할 수 있다.
  • 프라이머리 키는 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스
  • 프라이머리 키를 제외한 모든 인덱스는 세컨더리 인덱스
• 데이터 저장 방식(알고리즘)별로 구분한다면, B-Tree 인덱스와 해시 인덱스로 구분할 수 있다.
  • B-Tree 알고리즘은 가장 일반적으로 사용되는 인덱스 알고리즘으로, B-Tree 인덱스는 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘이다.
  • 해시 인덱스 알고리즘은 칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다.
• 데이터 중복 허용 여부로 분류하면, 유니크 인덱스와 유니크하지 않은 인덱스로 구분할 수 있다.
• 인덱스의 기능별로 분류하면, 전문 검색용 인덱스나 공간 검색용 인덱스 등이 있다.

8.3 B-Tree 인덱스

B-Tree는 칼럼의 원래 값을 변형하지 않고 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다.

8.3.1 구조 및 특성

• 최상위에 하나의 루트 노드, 가장 하위에 있는 노드를 리프 노드, 루트도 리프도 아닌 노드를 브랜치 노드라고 한다.
• 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.
• MyISAM 테이블은 세컨더리 인덱스가 물리적인 주소를 갖고, InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다.

8.3.2 B-Tree 인덱스 키 추가 및 삭제

8.3.2.1 인덱스 키 추가

B-Tree에 저장될 때는 저장될 키 값을 이용해 B-Tree 상의 적절한 위치를 검색해야 한다. 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다. 리프 노드가 꽉 찼다면 분리해야 하는데, 상위 노드까지 가야하므로 쓰기 비용이 많이 든다.

8.3.2.2 인덱스 키 삭제

해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 그냥 삭제 마크만 하면 된다. 삭제 마킹된 인덱스 키 공간은 그대로 방치하거나 재활용할 수 있다.

8.3.2.3 인덱스 키 변경

B-Tree의 키 값 변경 작업은 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리된다.

8.3.2.4 인덱스 키 검색

• 인덱스 관리로 인한 추가 비용을 감당하면서 인덱스를 구축하는 이유는 빠른 검색을 위해서다.
• InnoDB 검색에서는 인덱스가 없으면 불필요하게 많은 레코드를 잠그므로 인덱스 설계가 특히 중요하다.

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

8.3.3.1 인덱스 키 값의 크기

• 인덱스도 페이지 단위로 관리된다.
• MySQL의 B-Tree가 자식 노드를 몇 개까지 가질 수 있는지는 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다.
• 인덱스 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다. 또한 전체적인 인덱스 크기가 커진다는 것을 의미하고, 메모리 효율이 떨어지게 된다.

8.3.3.2 B-Tree 깊이

• B-Tree 인덱스의 깊이는 중요하지만 직접 제어할 방법은 없다.
• 인덱스 키 값의 크기가 커지면 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어지고, 같은 레코드 건수라고 해도 B-Tree의 깊이가 깊어져서 디스크 읽기가 더 많이 필요하다.

8.3.3.3 선택도(기수성)

• 선택도(기수성)은 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 전체 인덱스 키 값 100개인데, 유니크한 값의 수가 10개라면 기수성이 10
  
• 인덱스 키 값 가운데 중복된 값이 많아지면 기수성은 낮아지고, 선택도도 떨어진다. 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.
• 인덱스에서 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을 미친다.

8.3.3.4 읽어야 하는 레코드의 건수

인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는 필터링 방식으로 처리하는 것이 효율적이다.

8.3.4 B-Tree 인덱스를 통한 데이터 읽기

8.3.4.1 인덱스 레인지 스캔

• 가장 대표적인 방식
• 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다.
• 해당 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다. 이는 별도의 정렬 과정이 있는 것이 아니라 인덱스 자체의 정렬 특성 때문이다.
• 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다. 레코드 한 건 단위로 랜덤 IO가 한 번씩 일어나므로, 인덱스를 통해 데이터 레코드를 읽는 것은 비용이 많이 드는 작업이다. 인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 더 효율적인 방식이다.
  
• 레코드가 저장된 페이지를 가져와 읽어올 필요가 없는 것이 커버링 인덱스이다. 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 돼서 랜덤 읽기가 줄어들어 성능이 빨라진다.

8.3.4.2 인덱스 풀 스캔

• 인덱스의 처음부터 끝까지 모두 읽는 방식이다. ex. 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 사용
  
• 인덱스의 전체 크기는 테이블 자체의 크기보다 훨씬 작으므로 인덱스 풀 스캔은 테이블 전체를 읽는 것보다는 적은 디스크 IO로 쿼리를 처리할 수 있다.

8.3.4.3 루스 인덱스 스캔

• 듬성듬성하게 인덱스를 읽는 방식이다.
• 루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하지만, 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다.
• 인덱스에서 WHERE 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다.

8.3.4.4 인덱스 스킵 스캔

• (A, B)와 같이 컬럼 조합으로 이루어진 인덱스의 경우, WHERE 조건에 A를 사용하는 조건이 들어가야만 해당 인덱스를 사용하여 검색이 가능했다. 하지만 8.0 버전부터는 B 조건만 사용해도 옵티마이저가 최적화해주고, 이를 인덱스 스킵 스캔이라고 한다.
• 단점
  • WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 함 만약 유니크한 값의 개수가 많다면 MySQL 옵티마이저는 인덱스에서 스캔해야 할 시작 지점을 검색하는 작업이 많이 필요해지고, 성능이 오히려 느려질 수 있다.
    
  • 쿼리가 인덱스에 존재하는 칼럼만으로 처리가 가능해야 함(커버링인덱스) 그렇지 않으면 풀 테이블 스캔을 하게 된다.
    

8.3.5 다중 칼럼 인덱스

• 두 개 이상의 칼럼으로 구성된 인덱스 (multi-column index, concatenated index)
• 두 개의 칼럼으로 구성된 인덱스에서 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬돼 있다. 즉, 두 번째 칼럼의 정렬은 첫 번째 칼럼이 똑같은 레코드에서만 의미가 있다. 그래서 다중 칼럼 인덱스에서는 인덱스 내에서 각 칼럼의 위치(순서)가 중요하다.
  

8.3.6 B-Tree 인덱스의 정렬 및 스캔 방향

8.3.6.1 인덱스의 정렬

인덱스를 생성하는 시점에 인덱스를 구성하는 각 칼럼의 정렬을 오름차순 또는 내림차순으로 설정할 수 있다.

• 인덱스 스캔 방향 인덱스 생성 시점에 오름차순 또는 내림차순으로 정렬이 결정되지만 쿼리가 그 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다. MySQL 옵티마이저는 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어 낸다.
  
• 내림차순 인덱스 InnoDB에서 인덱스 역순 스캔이 정순 스캔에 비해 느릴 수 밖에 없다.

  • 페이지 잠금이 인덱스 정순 스캔에 적합한 구조이므로

  • 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조이므로

    쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 줄이는 데 도움이 될 것이다.

8.3.7 B-Tree 인덱스의 가용성과 효율성

8.3.7.1 비교 조건의 종류와 효율성

• 다중 칼럼 인덱스에서 각 칼럼의 순서와 그 칼럼에 사용된 조건이 동등 비교인지 아니면 크다 또는 작다 같은 범위 조건인지에 따라 각 인덱스 칼럼의 활용 형태, 효율이 달라진다.
• 작업 범위를 결정하는 조건은 많으면 많을수록 쿼리 처리 성능을 높이지만 체크 조건은 많다고 해서 쿼리의 처리 성능을 높이진 못한다.

8.3.7.2 인덱스의 가용성

• B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬돼 있다는 것이다.
• 하나의 칼럼으로 검색해도 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능하다.

8.3.7.3 가용성과 효율성 판단

B-Tree 인덱스 특성상, 작업 범위 결정 조건으로 사용할 수 없는 조건들 (체크 조건으로는 가능)

• NOT-EQUAL로 비교된 경우
• LIKE ‘%??’ (앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우
• 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
• NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
• 데이터 타입이 서로 다른 비교
• 문자열 데이터 타입의 콜레이션이 다른 경우

여기 내용은 인덱스 사용 쿼리 짤 때 또 보면 좋을 듯

8.4 R-Tree 인덱스

공간 인덱스는 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스다. B-Tree는 인덱스를 구성하는 칼럼의 값이 1차원의 스칼라 값인 반면, R-Tree 인덱스는 2차원의 공간 개념 값이다.

8.5 전문(full text) 검색 인덱스

문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문 검색에는 InnoDB나 MyISAM 스토리지 엔진에서 제공하는 일반적인 용도의 B-Tree 인덱스를 사용할 수 없다.

8.5.1 인덱스 알고리즘

전문 검색에서는 문서 본문의 내용에서 사용자가 검색하게 될 키워드를 분석해 내고, 빠른 검색용으로 사용할 수 있게 이러한 키워드로 인덱스를 구축한다.

문서의 키워드를 인덱싱하는 기법에 따라 크게 단어의 어근 분석과 n-gram 분석 알고리즘으로 구분할 수 있다.

8.5.1.1 어근 분석 알고리즘

• 어근 분석은 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업이다.
• MySQL 서버에서는 오픈소스 형태소 분석 라이브러리인 MeCab을 플러그인 형태로 사용할 수 있게 지원한다.

8.5.1.2 n-gram 알고리즘

• 형태소 분석이 문장을 이해하는 알고리즘이라면, n-gram은 단순히 키워드를 검색해내기 위한 인덱싱 알고리즘이다.
• n-gram이란 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법이다. 형태소 분석보다 간단하지만, 인덱스 크기가 상당히 크다.
  
• n글자짜리 토큰으로 구분하고, 토큰들에서 불용어를 걸러낸 후 전문 검색 인덱스에 등록한다.

8.5.1.3 불용어 변경 및 삭제

MySQL의 기본 불용어 리스트를 사용하면 더 혼란스러워질 수 있다.

전문 검색 인덱스의 불용어 처리 무시하고, 사용자 정의 불용어를 사용할 수도 있다.

8.5.2 전문 검색 인덱스의 가용성

전문 검색 인덱스를 사용하려면 아래 두 가지 조건을 갖춰야 한다.

• 쿼리 문장이 전문 검색을 위한 문법(match … against …)을 사용
• 테이블이 전문 검색 대상 칼럼에 대해 전문 인덱스 보유

8.6 함수 기반 인덱스

• 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때 사용하는 것이 함수 기반 인덱스이다.
• 함수 기반 인덱스는 인덱싱할 값을 계산하는 과정의 차이만 있고, 실제 인덱스의 내부 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.
• 함수 기반 인덱스를 구현하는 방법
  • 가상 칼럼을 이용한 인덱스
  • 함수를 이용한 인덱스

8.6.1 가상 칼럼을 이용한 인덱스

예를 들어, 두 개의 컬럼을 합쳐서 검색해야 하는 경우, 두 개의 컬럼을 합친 가상 칼럼을 추가하고 해당 가상 칼럼에 인덱스를 생성할 수 있다.

가상 칼럼은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.

8.6.2 함수를 이용한 인덱스

함수를 직접 사용하는 인덱스는 테이블의 구조는 변경하지 않고, 계산된 결과값의 검색을 빠르게 만들어준다. 함수 기반 인덱스를 제대로 활용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용돼야 한다.

8.7 멀티 밸류 인덱스

전문 검색 인덱스를 제외한 모든 인덱스는 인덱스 키와 데이터 레코드가 1:1 관계를 가진다.

멀티 밸류 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스다.

8.8 클러스터링 인덱스

8.8.1 클러스터링 인덱스

• 클러스터링 인덱스는 테이블의 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것이다.
• 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다. 프라이머리 키 값으로 클러스터링된 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크므로 신중히 프라이머리 키를 설정해야 한다.
  
• 클러스터링 인덱스는 프라이머리 키 값에 의해 레코드 저장 위치가 결정되므로 사실 인덱스 알고리즘이라기보다 테이블 레코드의 저장 방식이라고 볼 수 있다. 그래서 클러스터링 인덱스와 클러스터링 테이블이 동의어로 쓰이기도 한다.
  
• 프라이머리 키가 없는 경우에는 InnoDB 스토리지 엔진이 프라이머리 키를 대체할 칼럼을 선택한다.
• 프라이머리 키나 유니크 인덱스가 없는 InnoDB 테이블에서는 아무 의미 없는 값으로 클러스터링되는데, 이는 아무 이점이 없다. 클러스터링 인덱스는 테이블당 하나만 가질 수 있는 엄청난 혜택이므로 꼭 프라이머리 키를 생성하자.

8.8.2 세컨더리 인덱스에 미치는 영향

InnoDB 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현돼 있다.

조금 복잡하지만 프라이머리 키가 더 큰 장점을 제공하기 때문에 성능 저하에 대해 걱정하지 않아도 된다.

8.8.3 클러스터링 인덱스의 장점과 단점

장점은 빠른 읽기(R), 단점은 느린 쓰기(CUD)이다.

일반적으로 온라인 트랜잭션 환경에서는 읽기 비율이 매우 높으므로 읽기를 빠르게 유지하는 것이 중요하다.

8.8.4 클러스터링 테이블 사용 시 주의사항

8.8.4.1 클러스터링 인덱스 키의 크기

클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키 값을 포함한다. 그래서 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다.

8.8.4.2 프라이머리 키는 AUTO-INCREMENT보다는 업무적인 칼럼으로 생성

칼럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 프라이머리 키로 설정하는 것이 좋다.

8.8.4.3 프라이머리 키는 반드시 명시할 것

InnoDB 테이블에서 프라이머리 키를 정의하지 않으면 스토리지 엔진이 내부적으로 일련번호 칼럼을 추가한다. 이렇게 추가된 칼럼은 사용자에게 보이지 않아 접근할 수 없다.

8.8.4.4 AUTO-INCREMENT 칼럼을 인조 식별자로 사용할 경우

세컨더리 인덱스도 필요하고 프라이머리 키의 크기도 길다면 AUTO-INCREMENT 칼럼을 추가하고 이를 프라이머리 키로 설정하면 된다. 이를 인조 식별자라고 한다.

8.9 유니크 인덱스

8.9.1 유니크 인덱스와 일반 세컨더리 인덱스의 비교

구조상 차이는 없지만, 읽기와 쓰기 성능 관점에서 보자.

8.9.1.1 인덱스 읽기

유니크하지 않은 세컨더리 인덱스에서 한 번 더 해야 하는 작업은 디스크 읽기가 아니라, CPU에서 칼럼값을 비교하는 작업이라 성능상 영향이 없다.

유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지 인덱스 자체의 특성 때문에 느린 것이 아니다.

8.9.1.2 인덱스 쓰기

유니크 인덱스의 키 값을 쓸 때는 중복된 값 유무를 체크하는 과정이 필요하다. 그래서 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느리다.

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

유일성이 꼭 보장돼야 하는 칼럼에 대해서는 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크 인덱스보다는 유니크하지 않은 세컨더리 인덱스 생성을 고려하자.

8.10 외래키

외래키 관리 특징

• 테이블의 변경이 발생하는 경우만 잠금 경합이 발생한다.
• 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.