책너두 (Real MySQL 8.0 1권) 20일차 (~229p)

요약

• 랜덤 I/O와 순차 I/O에 대한 차이와 각각의 장단점을 이해하게 됨.
  • 인덱스 테이블 스캔 → 랜덤 I/O, 풀 테이블 스캔 → 순차 I/O
  • 쿼리 튜닝 == 랜덤 I/O 를 줄이는 것
• 인덱스가 무엇이며, 여러가지 기준으로 종류를 나누는 것을 이해하게 됨.
  • 데이터 저장 성능을 희생하고 데이터 읽기 속도를 높인다.
• 데이터 저장의 대표 방식은 B-Tree 인덱스 알고리즘에 대해 이해하게 됨.
  • 스토리지 엔진별로 데이터 파일 접근 방식이 다르다.
  • 노드를 기준으로 “트리 탐색” 한다.
  • 인덱스 추가, 변경, 삭제 작업은 추가 비용을 감당해야 함.
  • 인덱스를 이용한 검색 작업은 빠른 속도로 이용할 수 있음.
• 인덱스의 키 값의 크기가 검색 성능을 높이는 데에 영향을 준다는 사실을 이해하게 됨.
• B-Tree 깊이가 깊어질 수록 디스크 접근 횟수가 늘어나는 사실을 이해하게 됨.
  • 인덱스의 키 값이 영향을 줌. (인덱스의 키 값이 클수록 깊이가 깊어지게 됨.)
• 선택도, 카디널리티에 따른 인덱스의 성능 차이를 이해하게 됨.
  • 유니크한 값이 많을 수록 불필요한 레코드 조회를 피할 수 있음.

발췌

• 결론적으로 DBMS에서 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다.
• SELECT 쿼리 문장의 WHERE 조건절에 사용되는 칼럼이라고 해서 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히려 역효과만 불러올 수 있다.
• 인덱스의 리프 노드는 하상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다. (220p)

메모

랜덤 I/O 와 순차 I/O

• 랜덤 I/O 는 하드 디스크 드라이브의 플래터(원판)를 돌려서, 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미함.
  • 사실, 순차 I/O 또한 이 작업 과정은 같음.

• 왼쪽은 순차 I/O 를, 오른쪽은 랜덤 I/O 를 표현한 그림임.
  • 순차 I/O는 3개의 페이지 (3 * 16KB) 를 디스크에 기록하기 위해 1번 시스템 콜을 요청함.
  • 랜덤 I/O는 3개의 페이지를 디스크에 기록하기 위해 3번의 시스템 콜을 요청함.
    • 즉, 디스크에 기록해야 할 위치를 찾기 위해 순차 I/O 는 디스크의 헤드를 1번 움직였고, 랜덤 I/O는 디스크 헤드를 3번 움직임.
    • 디스크에 데이터를 읽고 쓰는데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정됨.
      • 즉, 위그림에서 순차 I/O 는 랜덤 I/O 보다 거의 3배 정도 빠르다고 볼 수 있음.
    • 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다고 볼 수 있음.
      • 그래서 여러 번 읽고 쓰기를 요청하는 랜덤 I/O 작업이 작업 부하가 훨씬 더 큼.
      • 데이터베이스 대부분의 작업은 이러한 작은 데이터를 빈번히 읽고 쓰기에 MySQL 서버는 그룹 커밋이나 바이너리 로그 버퍼, 또는 InnoDB 로그 버퍼 등의 기능이 내장되어 있음.
• 디스크 원판이 없는 SSD 는 랜덤 I/O와 순차 I/O의 차이가 없을것으로 예측되지만, 실제로 그렇지 않음.
  • SSD 드라이브에서도 랜덤 I/O는 여전히 순차 I/O보다 전체 스루풋(Throughput)이 떨어짐.
    • 그래서 SSD 드라이브의 사양에도 항상 순차 I/O와 랜덤 I/O 성능 비교를 구분해서 명시함.

랜덤 I/O나 순차 I/O 모두 파일에 쓰기를 실행하면 반드시 동기화(fsync 또는 flush 작업)가 필요함. 그런데 순차 I/O인 경우에도 이러한 파일 동기화 작업이 빈번히 발생하면 랜덤 I/O와 같이 비효율적인 형태로 처리될 때가 많음.
기업용으로 사용하는 데이터베이스 서버에는 캐시 메모리가 장착된 RAID 컨트롤러를 일반적으로 사용함. RAID 컨트롤러의 캐시 메모리는 아주 빈번한 파일 동기화 작업이 호출되는 순차 I/O를 효율적으로 처리될 수 있게 변환하는 역할을 함.

• 사실 쿼리를 튜닝해서 랜덤 I/O를 순차 I/O로 바꿔서 실행할 방법은 많지 않음.
• 일반적으로 쿼리 튜닝이라고 하면 랜덤 I/O 자체를 줄여주는 것이 목적임.
  • 랜덤 I/O를 줄인다는 것은 쿼리를 처리하는데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미함.

인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용함. 풀 테이블 스캔은 순차 I/O를 사용함.
그래서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있음. 이는 순차 I/O가 랜덤 I/O보다 훨씬 빨리 많은 레코드를 읽어올 수 있기 때문임.
이러한 형태는 OLTP(On-Line Transaction Processing) 성격의 웹 서비스보다는 데이터 웨어하우스나 통계 작업에서 자주 사용됨.

인덱스란?

• 인덱스는 책의 마지막 페이지에 있는 “찾아보기”가 인덱스에 비유됨.
  • 책의 내용은 데이터 파일에 해당한다고 볼 수 있음.
  • “찾아보기”를 통해 알아낸 페이지 번호는 데이터 파일에 저장된 레코드의 주소에 비유됨.
• DBMS도 데이터베이스 테이블의 모든 데이터를 검색하여 원하는 결과를 가져오기엔 시간이 오래 걸림
  • 그래서, 칼럼(또는 칼럼들)의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍(Key-Value pair)으로 삼아 인덱스를 만들어 둠.
• 책의 “찾아보기”와 DBMS 인덱스의 공통점 중, 중요한 것은 정렬임.
  • 최대한 빠르게 찾아갈 수 있게, “ㄱ”, “ㄴ”, “ㄷ” 순서로 정렬하고 있음.
  • DBMS의 인덱스도 마찬가지로 칼럼의 값을 주어진 순서로 미리 정렬해서 보관한다.
• 인덱스를 프로그래밍 언어의 자료구조로 살펴보자.
  • SortedList 는 DBMS 의 인덱스와 같은 자료구조임.
    • 저장되는 값을 항상 정렬된 상태로 유지하는 자료구조임.
    • DBMS 의 인덱스도 SortedList와 마찬가지로 저장되는 칼럼의 값을 이용해 항상 정렬된 상태를 유지함.
    • SortedList 자료구조는 데이터가 저장될 떄마다 항상 값을 정렬해야 하므로 저장하는 과정이 복잡하고 느림.
      • 하지만 이미 정렬되어 있기에 아주 빨리 원하는 값을 찾을 수 있음.
      • DBMS의 인덱스도 인덱스가 많은 테이블은 당연히 INSERT, UPDATE, DELETE 문장 처리가 느림.
        • 하지만 정렬된 “찾아보기”용 표(인덱스)를 가지고 있기에 SELECT 문장은 매우 빠르게 처리 가능함.
  • ArrayList는 데이터 파일과 같은 자료 구조를 사용함.
    • 값을 저장되는 순서 그대로 유지하는 자료구조임.
    • 데이터 파일은 ArrayList와 같이 저장된 순서대로 별도의 정렬 없이 그대로 저장함.
• 결론적으로 DBMS 에서 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고, 그 대신 데이터의 읽기 속도를 높이는 기능임.
  • 테이블의 인덱스를 하나 더 추가할지 말지는 데이터의 저장 속도를 어디까지 희생할 수 있는지, 읽기 속도를 얼마나 더 빠르게 만들어야 하느냐에 따라 결정해야 함.
  • SELECT 쿼리 문장의 WHERE 조건절에 사용되는 칼럼이라고 해서 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히려 역효과만 불러올 수 있음.
• 인덱스는 데이터를 관리하는 방식(알고리즘)과, 중복 값 허용 여부 등에 따라 여러 가지로 나눌 수 있음.
  • 인덱스를 역할별로 구분한다면 프라이머리 키(Primary Key)와 보조 키(세컨더리 인덱스, Secondary Key)로 구분할 수 있음.
    • 프라이머리 키는 그 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스임.
      • 이 칼럼(때로, 칼럼의 조합)은 테이블에서 해당 레코드를 식별할 수 있는 기준값이 됨.
        • 우리는 이를 식별자라고 부름.
      • 프라이머리 키는 NULL 값을 허용하지 않고 중복을 허용하지 않는게 특징임.
    • 프라이머리 키를 제외한 나머지 모든 인덱스는 세컨더리 인덱스(Secondary Index)로 분류함.
      • 유니크 인덱스는 프라이머리 키와 성격이 비슷하고 프라이머리 키를 대체해서 사용할 수 있어서 대체 키라고도 함.
        • 별도로 분류하기도 하고 그냥 세컨더리 인덱스로 분류하기도 함.
• 데이터 저장 방식(알고리즘)별로 구분할 경우 상당히 많은 분류가 가능.
  • 대표적으로 B-Tree 인덱스와 Hash 인덱스로 구분 가능함.
  • 최근에는 Fractal-Tree 인덱스나 로그 기반의 Merge-Tree 인덱스와 같은 알고리즘을 사용하는 DBMS 도 개발되고 있음.
    • B-Tree 알고리즘은 가장 일반적으로 사용되는 인덱스 알고리즘임.
      • 상당히 오래전에 도입된 알고리즘임. → 그만큼 성숙해진 상태임
      • B-Tree 인덱스는 칼럼 값을 변형하지 않고 원래 값을 이용해 인덱싱하는 알고리즘임.
      • MySQL 서버에서는 위치 기반 검색을 지원하기 위해 R-Tree 인덱스 알고리즘도 있지만, 결국 R-Tree 인덱스는 B-Tree의 응용 알고리즘으로 볼 수 있음.
    • Hash 인덱스는 알고리즘을 칼럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘임.
      • 매우 빠른 검색을 지원함.
      • 하지만, 값을 변형해서 인덱싱하므로 전방(Prefix) 일치와 같이 값의 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없음.
      • Hash 인덱스는 주로 메모리 기반의 데이터베이스에서 많이 사용함.
• 데이터의 중복 허용 여부로 분류하면 유니크 인덱스(Unique)와 유니크하지 않은 인덱스(Non-Unique)로 구분할 수 있음.
  • 인덱스가 유니크한지 아닌지는 단순히 같은 값이 1개만 존재하는지 1개 이상 존재할 수 있는지를 의미함.
    • 근데, 실제 DBMS 의 쿼리를 실행해야 하는 옵티마이저에게는 상당히 중요한 문제가 됨.
    • 유니크 인덱스에 대해 동등 조건(Equal, =)으로 검색한다는 것은 1건의 레코드만 찾으면 다른 데이터는 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 냄.
    • 뿐만 아니라, 유니크 인덱스로 인한 MySQL 처리 방식의 변화나 차이점이 상당히 많음.
      • 이 부분은 인덱스와 쿼리의 실행 계획을 살펴보면서 배울 예정임.
• 인덱스의 기능별로 분류하면 전문 검색용 인덱스나 공간 검색용 인덱스 등으로 나눌 수 있음.
  • 이 외에도 수없이 많은 인덱스가 있지만 MySQL 사용할 때는 이 두 가지만으로 충분함.

B-Tree 인덱스

• B-Tree는 데이터베이스의 인덱싱 알고리즘 중, 가장 일반적으로 사용되고 가장 먼저 도입돤 알고리즘임.
  • 그리고 아직도 가장 범용적인 목적으로 사용되는 알고리즘임.
• B-Tree에는 여러 가지 변형된 형태의 알고리즘이 있음.
  • 일반적으로 DBMS에서는 주로 B+-Tree, B*-Tree 가 사용됨.
• B-Tree에서 B의 약자는 “Balanced” 임. (Binary가 아님)
• B-Tree는 칼럼의 원래 값을 변형시키지 않고 인덱스 구조체 내에서는 항상 정렬된 상태로 유지함. (물론 값의 앞부분만 잘라서 관리하고 있음.)
• 전문 검색과 같은 특수한 요건이 아니면 대부분 인덱스는 거의 B-Tree 를 사용할 정도로 일반적인 용도에 적합한 알고리즘임

구조 및 특성

• B-Tree 기본 구조를 알아야 B-Tree 인덱스를 제대로 사용할 수 있다.
  • B-Tree는 트리 구조의 최상위에 하나의 “루트 노드(Root node)”가 존재하고 그 하위에 자식 노드가 붙어 있는 형태임.
  • 트리 구조의 가장 하위에 있는 노드를 “리프 노드(Leaf node)” 라고 함.
  • 트리 구조에서 루트 노드도 아니고 리프 노드도 아닌 중간 노드를 “브랜치 노드(Branch node)” 라고 함.
  • 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리됨.
    • 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있음.

• 위 그림은 B-Tree 인덱스의 각 노드와 데이터 파일의 관계를 표현한 것임.
• 인덱스의 키 값은 모두 정렬돼 있지만, 데이터 파일의 레코드는 정렬되어 있지 않고 임의의 순서로 저장되어 있음.
  • 데이터 파일의 레코드는 INSERT 된 순서대로 저장되는 것으로 생각하지만, 그렇지 않음.
    • 만약 테이블의 레코드를 전혀 삭제하거나 변경하지 않고 INSERT만 수행한다면 맞을 수 있음.
    • 하지만 레코드가 삭제되어 빈 공간이 생기면 그다음의 INSERT는 가능한 한 삭제된 공간을 재활용하도록 DBMS가 설계되기 때문에 항상 INSERT된 순서로 저장되는것은 아님.

대부분의 RDBMS의 데이터 파일에서 레코드는 특정 기준으로 정렬되지 않고, 임의의 순서로 저장됨. 하지만 InnoDB 테이블에서 레코드는 클러스터되어 디스크에 저장되므로 기본적으로 프라이머리 키 순서로 정렬되어 저장됨. → 이는 오라클의 IOT(Index Organized Table)나 MS-SQL의 클러스터 테이블과 같은 구조를 말함.
다른 DBMS에서는 클러스터링 기능이 선택사항이지만 InnoDB 에서는 사용자가 별도 명령이나 옵션 선택없이 디폴트로 클러스터링 테이블이 생성됨.
클러스터링이란 비슷한 값을 최대한 모아서 저장하는 방식을 의미함. → 자세한 내용은 뒤에서 다시 살펴봄.

• 인덱스는 테이블의 키 칼럼만 가지고 있음
  • 나머지 칼럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 함.
  • 이를 위해, 인덱스의 리프 노드는 데이터 파일에 저장된 레코드의 주소를 가짐.

• 위 그림은 B-Tree의 리프노드와 MyISAM 테이블 데이터 레코드를 보여준다.
  • “레코드 주소”는 MyISAM 테이블의 생성 옵션에 따라 레코드가 테이블에 INSERT 된 순번이거나 파일 내의 위치(Offset)임.
    • MyISAM 스토리지 엔진에서의 인덱스 구조는 4.3.3절을 참조하자.

• 위 그림은 B-Tree의 리프노드와 InnoDB 테이블 데이터 레코드를 보여준다.
  • InnoDB 스토리지 엔진을 사용하는 테이블에서는 프라이머리 키가 ROWID의 역할을 함.
• 두 스토리지 엔진의 인덱스에서 가장 큰 차이점은 세컨더리 인덱스를 통해 데이터 파일의 레코드를 찾아가는 방법에 있음.
  • MyISAM 테이블은 세컨더리 인덱스가 물리적인 주소를 가짐.
  • InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 떄문에 논리적인 주소를 가진다고 볼 수 있음.
• InnoDB 테이블에서 인덱스를 통해 레코드를 읽을 때, 위 그림처럼 데이터 파일을 바로 찾아가지 못함.
  • 인덱스에 저장된 프라이머리 키 값을 이용해 프라이머리 키 인덱스를 한 번 더 검색한다.
  • 프라이머리 키 인덱스의 리프 페이지에 저장되어 있는 레코드를 읽는다.
    • 즉, InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽이 위해서는 반드시 프라이머리 키를 저장하고 있는 B-Tree를 다시 한번 검색해야 함.
• 위 스토리지 엔진의 인덱스 구조는 각각 장단점이 있음. → 8.8절 클러스터링 인덱스에서 살펴본다.

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

• 테이블의 레코드를 저장하거나 변경하는 경우, 인덱스 키 추가나 삭제 작업이 발생함.
  • 인덱스 키 추가, 삭제가 어떻게 처리되는지 알면 쿼리의 성능을 쉽게 예측 가능함.

인덱스 키 추가

• 새로운 키 값이 B-Tree에 저장될 때 테이블의 스토리지 엔진에 따라 새로운 키 값이 즉시 인덱스에 저장될 수도 있고 그렇지 않을 수도 있음.
  • B-Tree에 저장될 때는 저장될 키 값을 이용해 B-Tree상의 적절한 위치를 검색해야 함.
  • 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장 한다.
    • 리프 노드가 꽉 차서 더 저장할 수 없을때는 리프 노드가 분리(Split)돼야 함.
    • 이는 상위 브랜치 노드까지 처리의 범위가 넓어짐.
      • 이 작업 때문에 B-Tree는 상대적으로 쓰기 작업(새로운 키 추가하는 작업)에 비용이 많이드는 것으로 알려져 있음.
• 인덱스 추가로 인한 INSERT, UPDATE 문장이 어떤 영향을 받는지 명확하게 이해하기 위해서는 다음을 확인해야 함.
  • 테이블의 칼럼 수
  • 칼럼의 크기
  • 인덱스 칼럼의 특성
    • 대략적으로 계산하는 방법은, 작업 비용을 1이라고 가정하면 해당 테이블의 인덱스에 키를 추가하는 작업 비용을 1.5 정도로 예측하는 것임.
    • 일반적으로 테이블에 인덱스가 3개가 있다면(테이블의 모든 인덱스가 B-Tree 라는 가정하에)
      • 테이블에 인덱스가 하나도 없는 경우, 작업비용은 1임
      • 인덱스가 3개 있다면 5.5 정도의 비용(1.5*3 + 1) 정도로 예측함.
      • 중요한건, 이 비용의 대부분이 메모리와 CPU에서 처리하는 시간이 아니라 디스크로부터 인덱스 페이지를 읽고 쓰기를 해야 해서 걸리는 시간이다.
• MyISAM 이나 MEMORY 스토리지 엔진을 사용하는 테이블에서는 INSERT 문장 실행시 즉시 새로운 키 값을 B-Tree 인덱스에 변경함.
• InnoDB 스토리지 엔진은 이 작업을 족므 더 지능적으로 처리함.
  • 필요하면 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있음.
  • 프라이머리 키나 유니크 인덱스의 경우, 중복 체크가 필요하므로 즉시 B-Tree에 추가하거나 삭제한다.
    • InnoDB 스토리지 엔진의 체인지 버퍼에 대해 4.2.10절을 참조하자.

인덱스 키 삭제

• B-Tree의 키 값이 삭제되는 경우는 상당히 간단함.
  • 해당 키 값이 저장된 B-Tree 리프 노드를 찾아서 그냥 삭제 마크만 하면 작업이 완료됨.
  • 삭제 카밍된 인덱스 키 공간은 계쏙 그대로 방치하거나 재활용할 수 있음.
  • 인덱스 키 삭제로 인한 마킹 작업 또한 디스크 쓰기가 필요하므로 이 작업 역시 디스크 I/O가 필요한 작업임.
  • 5.5버전 이상부터 InnoDB 스토리지 엔진에서는 이 작업 또한 버퍼링되어 지연 처리될 수 있음.
    • 처리가 지연된 인덱스 키 삭제 또한 사용자에게 특별한 악영향 없이 MySQL 서버가 내부적으로 처리함. → 특별히 걱정할 건 없음.
• MyISAM 이나 MEMORY 스토리지 엔진의 테이블에서는 체인지 버퍼 같은 기능이 없으므로 인덱스 키 삭제가 완료된 후 쿼리 실행이 완료됨.

인덱스 키 변경

• 인덱스 키 값은 그 값에 따라 저장될 리프 노드의 위치가 결정됨.
  • B-Tree의 키 값 변경이 일어나는 경우, 단순히 인덱스상의 키 값만 변경이 불가능함.
  • B-Tree의 키 값 변경 작업은 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리됨.
  • 키 값 변경 때문에 발생하는 B-Tree 인덱스 키 값의 삭제와 추가 작업은 앞에 설명한 절차대로 처리됨.

인덱스 키 검색

• INSERT, UPDATE, DELETE 작업 시, 인덱스 관리에 따른 추가 비용을 감당하면서 인덱스를 구축하는 이유는 바로 빠른 검색을 때문임.
• 인덱스 검색하는 작업을 “트리 탐색”이라고 부름.
  • B-Tree 루트 노드부터 시작해서 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행함.
  • 인덱스 트리 탐색은 SELECT 에서만 사용하는 것이 아니라 UPDATE, DELETE 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우에도 사용함.
• B-Tree 인덱스를 이용한 검색은 100% 일치, 또는 값의 앞부분(Left-most part)만 일치하는 경우에 사용 가능함.
• 부등호(<, >) 비교 조건에서도 인덱스를 활용할 수 있다.
• 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없음.
• 인덱스를 이용한 검색에서, 인덱스의 키 값 변형이 가해진 후 비교 되는 경우에는 절대 B-Tree의 빠른 검색 기능을 사용할 수 없음.
  • 이미 변형된 값은 B-Tree 인덱스에 존재하는 값이 아님.
  • 따라서 함수나 연산을 수행한 결과로 정렬하거나 검색하는 작업은 B-Tree 장점을 이용할 수 없으므로 주의해야 함.
• InnoDB 스토리지 엔진에서 인덱스는 더 특별한 의미가 있음.
  • InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락(갭락)은 검색을 수행한 인덱스를 잠근 후, 테이블의 레코드를 잠그는 방식으로 구현되어 있음.
    • 따라서 UPDATE, DELETE 문장이 실행될 때, 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠금. (이게 무슨말일까..)
    • 심지어 테이블의 모든 레코드를 잠금 수도 있음.
      • 그만큼 InnoDB 스토리지 엔진에서는 인덱스 설계가 중요하고 많은 부분에 영향을 미침.

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

• 칼럼의 크기
• 레코드의 건수
• 유니크한 인덱스 키 값의 갯수
  • 등에 의해 검색이나 변경 작업의 성능이 영향을 받음.

인덱스 키 값의 크기

• InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 또는 블록(Block)이라고 함.
  • 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 됨.
  • 또, 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터 버퍼링하는 기본 단위이기도 함.
• 인덱스도 결국 페이지 단위로 관리됨.
• 앞서 설명한 루트, 브랜치, 리프 노드를 구분한 기준도 바로 페이지 단위임.
• 이진(Binary) 트리는 각 노드가 자식 노드를 2개만 가짐.
  • DBMS의 B-Tree가 이진 트리면 인덱스 검색이 상당히 비효율적임.
  • 일반적인 DBMS이ㅡ B-Tree 자식 노드의 갯수는 가변적임.
• MySQL B-Tree 자식노드는 몇개 까지 가능할까?
  • 인덱스의 페이지 크기와 키 값의 크기에 따라 결정됨.
  • 5.7 버전부터 InnoDB 스토리지 엔진의 페이지 크기를 innodb_page_size 시스템 변수를 이용해 4KB ~ 64KB 사이의 값을 선택할 수 있음.
    • 기본 값은 16KB임.

• 인덱스의 키가 16바이트라고 가정하면 위 그림처럼 인덱스 페이지를 구성할 수 있음.
• 자식 노드 주소는 여러 가지 복합 정보가 담긴 영역임.
• 페이지의 종류별로 대략 6바이트에서 12바이트까지 다양한 크기 값을 가질 수 있음.
  • 위 그림에서는 자식 노드 주소 영역이 평균 12 바이트로 구성된다고 가정함.
• 위 그림의 경우, 하나의 인덱스 페이지(16KB)에
  • 16 * 1024 / (16 + 12) = 585 개를 저장할 수 있음.
  • 최종적으로, 이 경우는 자식 노드를 585개를 가질 수 있는 B-Tree 가 되는 것임.
• 만약, 인덱스 키 값이 커지면?
  • 위 예시에서 키 값의 크기가 32 바이트로 늘어났다고 가정하면,
  • 한 페이지에 인덱스 키를
    • 16 * 1024 / (32 + 12) = 372개 저장할 수 있음.
• SELECT 쿼리가 레코드 500개를 읽어야 한다면, 전자의 경우, 인덱스 페이지 한번으로 해결될 수 있음.
  • 후자라면 최소 2번 이상 디스크로부터 읽어야 함.
    • 즉, 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고 그만큼 느려짐.
• 인덱스 키 값의 길이가 길어진다는 것은 전체적인 인덱스 크기가 커지는 것을 의미함.
  • 인덱스를 캐시해 두는 InnoDB 버퍼 풀이나 MyISAM의 키 캐시 영역은 크기가 제한적임.
    • 하나의 레코드를 위한 인덱스 크기가 커지면 커질수록 메모리에 캐시해 둘 수 있는 레코드 수는 줄어듬.
      • 자연히 메모리 효율은 떨어지는 결과를 얻게 됨.

B-Tree 깊이

• B-Tree 인덱스의 깊이(Depth)는 상당히 중요함.
  • 그렇지만 직접 제어할 방법은 없음.
• B-Tree 깊이는 MySQL 에서 값을 검색할 때 몇번이나 랜덤하게 디스크를 읽어야 하는지와 직결되는 문제임.
• 인덱스 키 값의 크기가 커질 수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어짐.
  • 그 때문에 같은 레코드 건수를 조회한다 하더라도 B-Tree의 깊이(Depth)가 깊어져서 디스크 읽기가 더 많이 필요하게 된다.
• 그래서, 인덱스 키 값의 크기는 가능한 작게 만드는 것이 좋음.
  • 실제로, 아무리 대용량 데이터베이스라도 B-Tree 깊이(Depth)가 5단계 이상까지 깊어지는 경우는 흔치 않음.

선택도(기수성)

• 인덱스에서 선택도(Selectivity) 또는 기수성(Cardinality)은 거의 같은 의미로 사용됨.
  • 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미함.
  • 인덱스 키 값 가운데 중복값이 많으면 카디널리티가 낮아짐.
  • 즉, 인덱스는 카디널리티가 높을 수록 유니크해지므로 검색 대상이 줄어들고 그만큼 빠르게 처리됨.
• 관련 예씨가 228~229p 에 잘 설명되어 있음.

선택도가 좋지 않더라도 정렬, 그루핑 작업을 위해 인덱스를 만드는 것이 훨씬 나은 경우가 많음. 인덱스가 항상 검색에만 사용되는 것은 아니므로 여러 용도를 고려해서 적절히 인덱스를 설계할 필요가 있음.