[Real MySQL 8.0] 8장 - 인덱스

이번에 InnoDB 스토리지 엔진에서 인덱스 레코드를 잠금 한다는 사실을 알게 되어서, 인덱스 설계가 매우 중요하다는 사실을 알게 되었다. 8장을 통해 MySQL의 인덱스에 대해 자세히 공부해 보려고 한다.

 

8.1 디스크 읽기 방식

• 디스크 읽기에는 랜덤 I/O와 순차 I/O가 있는데, 디스크 헤더를 많이 움직여야 하는 랜덤 I/O가 부하가 더 크다.
• 쿼리를 튜닝하는 작업은 랜덤 I/O자체를 줄여주는 것이 목적이다.

8.2 인덱스란?

• 데이터 파일은 ArrayList와 같이 저장된 순서대로 별도의 정렬 없이 그대로 저장을 해둔다.
• 인덱스 파일은 Sorted List와 마찬가지로 저장되는 칼럼의 값을 이용해 항상 정렬된 상태를 유지한다.
• 프라이머리 키 : 레코드를 대표하는 컬럼의 값으로 만들어진 인덱스. 이 칼럼은 테이블에서 해당 레코드를 식별할 수 있는 기준값이 되기 때문에 우리는 이를 식별자라고도 부른다. NULL허용이 되지 않으며 유니크해야 한다.
• 프라이머리 키를 제외한 나머지 모든 인덱스는 세컨더리 인덱스로 분류한다.
• 가장 보편적으로 사용되는 인덱스 알고리즘 : B-Tree, Hash
  - B-Tree 알고리즘은 컬럼의 값을 변경하지 않고 원래 컬럼의 값을 이용해서 인덱싱한다.
  - 컬럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로 prefix일치와 같이 일부만 검색하거나 범위로 검색할 때는 해시 인덱스를 사용할 수 없다.

8.3 B-Tree 인덱스

• B는 Balanced를 의미한다.
• 루트 노드(Root node) : 트리 구조의 최상위에 존재하는 하나의 노드. 그 아래에 자식 노드가 붙어 있다.
• 리프 노드(Leaf node) : 트리 구조의 가장 하위에 있는 노드.
• 브랜치 노드(Branch node) : 루트도, 리프도 아닌 중간에 있는 노드.
• 데이터에서 인덱스와 실제 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 할상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.
• MyISAM 테이블은 세컨더리 인덱스가 물리적인 주소를 가지고, InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다고 본다.
• 인덱스 키 추가 : 새로운 키 값이 B-Tree에 저장될 위치가 결정되면 레코드의 키 값과 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다. 리프 노드가 꽉 차서 저장을 할 수 없으면 리프 노드를 분리한다.
• 인덱스 키 삭제 : 삭제되는 키 값이 저장된 B-Tree의 리프 노드를 찾아서 삭제 마크만 하면 작업이 완료되고, 삭제 마킹된 인덱스 키 공간은 그대로 남아 있거나 재활용 할 수 있다.
• 인덱스 키 변경 : B-Tree의 키 값 변경 작업은 키 값을 삭제한 후 다시 새로운 키 값을 추가하는 형태로 처리된다.
• 인덱스 키 검색 : B-Tree의 루트 노드부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행한다. 
• B-Tree 인덱스 사용에 영향을 미치는 요소
  - 인덱스 키 값의 크기
  - B-Tree 깊이
  - 선택도(Cardinality)
  - 읽어야 하는 레코드의 건수
• B-Tree 인덱스를 이용하는 대표적인 세 가지 방법
  • 인덱스 레인지 스캔 : 검색해야 할 인덱스의 범위가 결정되었을 때 사용. 
    - 어떤 방식으로 스캔하든 관계없이, 해당 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다.
  • 인덱스 풀 스캔 : 인덱스의 처음부터 끝까지 모두 읽는 방식.
  • 루스 인덱스 스캔 : 집합 함수를 사용하는 경우 등 필요치 않은 인덱스는 스캔하지 않고 다음으로 넘어가는 형태.
  • 인덱스 스킵 스캔 : 루스 인덱스 스캔과 비슷하나 MySQL 8.0부터는 WHERE 조건절의 검색을 위해 사용 가능하도록 용도가 넓어졌다. 인덱스가 (a,b,c) 칼럼으로 정의되어 있을 경우 b나 c칼럼만 가지고도 인덱스 스캔이 가능하다.
    - 가능 조건 : WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 한다. 쿼리가 인덱스에 존재하는 칼럼만으로 처리가 가능해야 한다(커버링 인덱스).
• 다중 칼럼 인덱스 : 2개 이상의 칼럼이 포함된 인덱스. 여기서 두번째 칼럼의 정렬은 첫 번째 칼럼이 똑같은 레코드에서만 의미가 있다.
• B-Tree 인덱스의 정렬 및 스캔 방향
  
  • 인덱스를 어떤 방향으로 읽을 것인지는 쿼리에 따라 옵티마이저가 결정한다.
  • MySQL 8.0부터는 정렬 순서를 혼합한 인덱스도 생성이 가능하다.
  • 인덱스를 정순으로 읽는 것과 역순으로 읽는 것 중 역순으로 읽는 것이 속도가 조금 느리다.
    (페이지 잠금이 인덱스 정순 스캔에 적합한 구조이고, 인덱스 레코드가 단방향으로만 연결된 구조이기 때문)
• B-Tree 인덱스에서는 작업 범위를 결정하는 조건이 많을수록 쿼리의 처리 성능을 높이지만, 체크 조건(단순히 조건에 맞는지 비교하면서 취사선택하는 필터링 작업)은 많다고 해서 쿼리의 성능을 높이지는 못한다.
• B-Tree 인덱스를 사용할 수 없는 경우
  - NOT-EQUAL로 비교된 경우 (<>, NOT IN, NOT BETWEEN, IS NOT NULL)
  - LIKE '%~~' 형태로 앞부분이 아닌 뒷부분 일치인 경우
  - 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우 (eg. WHERE SUBSTRING(column, 1, 1) = 'X')
  - NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
  - 데이터 타입이 서로 다른 비교 (eg. WHERE char_column = 10)
  - 문자열 데이터 타입의 콜레이션이 다른 경우

8.4 R-Tree 인덱스

• R은 Rectangle을 의미한다.
• 공간 인덱스로, R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적을 갖는다.
• 사용자 위치로부터 반경 2km안의 데이터 검색 등에 활용할 수 있다.

8.5 전문 검색 인덱스

• 문서의 내용 전체를 인덱스화 해서 특정 키워드가 포함된 문서를 검색하는 방법.
• 일반적인 용도의 B-Tree 인덱스를 사용할 수 없다.
• 불용어(Stop Word)처리, 어근 분석을 통해 색인 작업이 수행된다.
• n-gram 알고리즘 : 키워드를 검색해내기 위한 인덱싱 알고리즘. n개의 글자씩 토큰으로 구분된다.
• 전문 검색 인덱스를 사용하기 위한 조건
  - 쿼리 문장이 전문 검색을 위한 문법(MATCH ... AGAINST ...)를 사용해야 한다.
  - 테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스를 보유해야 한다.

8.6 함수 기반 인덱스

• 칼럼의 값을 변형해서 만들어진 값에 대해서 인덱스를 구축하는 방법
• MySQL 8.0부터 함수 기반 인덱스를 지원한다.
• 가상 칼럼을 이용한 인덱스, 함수를 이용한 인덱스 두 가지 방법이 있다.
  - 가상 칼럼을 이용한 인덱스 : VIRTUAL, STORED 옵션을 사용해 가상 칼럼을 추가 후 인덱스를 생성한다. 가상 칼럼은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 낸다.
  - 함수를 이용한 인덱스 : MySQL 8.0부터는 테이블의 구조를 변경하지 않고 함수를 직접 사용하여 인덱스를 생성할 수 있다. 함수 기반 인덱스를 사용하려면 인덱스에 명시된 표현식이 정확하게 그대로 사용되어야 한다.

8.7 멀티 밸류 인덱스

• 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스
• JSON 타입의 칼럼을 지원한다.
• MEMBER OF, JSON_CONTAINS, JSON_OVERLAPS 함수들을 이용해서 검색해야만 옵티마이저가 인덱스를 활용한 실행 계획을 수립한다.

8.8 클러스터링 인덱스

• MySQL에서 클러스터링 인덱스는 InnoDB 스토리지 엔진에서만 지원한다.
• 테이블의 프라이머리 키에 대해서만 적용되는 내용이다.
• 프라이머리 키의 값에 의해 레코드의 저장 위치가 결정된다.
• 프라이머리 키 기반의 검색이 매우 빠르나, 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다.
• 프라이머리 키가 없는 경우에는 InnoDB 스토리지 엔진이 아래와 같은 순서로 대체할 칼럼을 선택한다.
  1) 프라이머리 키가 있으면 프라이머리 키를 클러스터링 키로 선택
  2) NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 키로 선택
  3) 자동으로 유니크한 값을 가지는 칼럼을 내부적으로 생성하여 클러스터링 키로 선택
• 클러스터링 테이블 사용 시 주의사항
  - 클러스터링 인덱스 키의 크기에 주의 : 너무 커지면 서버의 자원을 많이 잡아먹는다.
  - (가능한 경우)프라이머리 키는 AUTO-INCREMENT보다는 업무적인 칼럼으로 생성
  - 프라이머리 키는 반드시 명시하는 것이 좋음 : 내부적인 칼럼을 생성해서 키로 사용하는 방식은 사용자가 해당 칼럼에 접근할 수 없다.

8.9 유니크 인덱스

• MySQL에서는 인덱스 없이 유니크 제약만 설정할 수 없다.
• 유니크 인덱스에서 NULL도 저장될 수 있는데, NULL은 특정 값이 아니므로 2개 이상 저장될 수 있다.
• 유니크 인덱스는 InnoDB 스토리지 엔진에서 사용하는 체인지 버퍼를 사용하지 못한다.
  (반드시 중복 체크를 해야 하기 때문에 작업을 버퍼링할 수 없음)

8.10 외래키

• MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있다.
• 외래키를 사용할 경우 연관된 칼럼의 잠금으로 인해 대기가 발생할 수 있으므로, 신중히 생성해야 한다.