[RealMysql 8.0] 인덱스 8 ~ 8.3.3.4

• 디스크 읽기 방식
  • 컴퓨터의 CPU나 메모리 처럼 전기적 특성을 띤 장치의 성능은 짧은 시간 동안 매우 빠른 속도로 발전 했지만 디스크 같은 기계식 장치의 성능은 상당히 제한적으로 발전했다.
  • 데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이냐가 관건일 때가 상당히 많다.
• 하드 디스크 드라이브(HDD) 솔리드 스테이트 드라이브(SDD)
  • 순차 I/O 에서는 ssd 가 hdd 보다 조금 빠르거나 비슷한 성능을 보이기도 하지만
  • 랜덤 I/O 에서는 ssd 가 훨씬 빠르다
  • 데이터베이스는 순차 I/O 작업은 그다지 비중이 크지않고 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 dmbs용 스토리지에 최적이라 볼 수 있다.
• 랜덤 I/O와 순차 I/O
  • 하드 디스크 드라이브의 플래터(원판)를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것은 랜덤 I/O, 순차 I/O 둘다 같지만 랜덤 I/O 는 디스크 헤더를 n 번을 움직여야 한다.
  • 디스크에 데이터를 쓰고 읽는데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정된다.
  • 디스크 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다.
  • 일반적으로 쿼리를 튜닝한다는 것은 랜덤 I/O 자체를 줄여주는 것이 목적
    • 랜덤 I/O를 줄인다는것은 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.
• 인덱스란?
  • 책의 색인 목차 정도로 설명.
  • 모든 데이터를 검색하여 원라는 결과를 가져오려면 상당한 시간이 걸릴 것이다.
  • 칼럼(또는 칼럼들)의 값과 해당 레코드가 저장된 주소를 키와 값으로 인덱스를 만들어 둔다.
  • 정렬이 중요하다.
  • SortedList 는 DBMS의 인덱스와 같은 자료구조
  • ArrayList 는 데이터 파일과 같은 자료 구조를 사용한다.
  • 인덱스를 열할별로 구분해 본다면 프라이머리 키(Primary key)와 보조 키(세컨더리 인덱스)로 구분할 수 있다.
    • 프라이머리 키: 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스.레코드를 식별할 수 있는 기준값이 되기 때문에 식별자 라고도 불림.null 값과 중복을 허용하지 않는다.
    • 프라이머리 키 를 제외하고 모든 인덱스는 세컨더리 인덱스로 분류한다.유니크 인덱스는 프라이머리 키와 성격이 비슷하고 대체해서 사용할 수 있어 대체키 라고도 하고 그냥 세컨더리 인덱스라 분류하기도 한다.
  • 데이터 저장 방식은 보통 B-Tree 인덱스, Hash 인덱스를 사용한다.
  • 데이터 중복 여부로 분류하면 유니크 인덱스, 유니크하지 않는 인덱스로 구분할 수 있다.
  • 유니크 인덱스는 같은 값이 1개만 존재하는지 1개이상인지 의미한다.옵티마이저는 유니크 인덱스인지 아닌지에 따라 성능이 좌우되므로 옵티마이저 한테는 중요한 문제이다.(유니크 인덱스라 하면 1개의 코드만 찾고 더이상 찾지 않아도 되기 때문)
  • 인덱스 기능별로 분류한다면 전문 검색용 인덱스나 공감 검색용 인덱스 등을 예로 들 수 있다.
• B-Tree 인덱스
  • B-tree만 공부해도 몇페이지는 될듯하다..간략히 알아보자..
  • 데이터베이스의 인덱싱 알고리즘 가운데 가장 일반적으로 사용된다.
  • B 의 약자는 Balanced 이다.
• 구조 및 특성
  • 루트 - 브랜치 - 리프 노드로 구성되어 있다.
  • 인덱스의 키 값은 모두 정렬돼 있지만, 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저종돼 있다.
    • 대부분 RDBMS 의 데이터 파일에서 레코드는 특정 기준으로 정렬되지 않고 임의의 순서로 저장된다.
    • InnoDB 테이블에서 레코드는 클러스터 되어 디스크에 저장되므로 기본적으로 프라이머리 키 순서로 정렬되어 저장된다.
      • 따로 설정하지 않았다면 디폴트 값은 클러스터링 테이블이 생성된다.
• B-Tree 인덱스 키 추가 및 삭제
  • 테이블 레코드를 저장하거나 변경하는 경우 인덱스 키 추가나 삭제 작업이 발생한다.
• 인덱스 키 추가
  • MyISAM, MEMORY 스토리지 엔진을 사용하는 테이블은 insert 시 문장이 실행되면 즉시 새로운 키 값을 B-Tree 인덱스에 변경한다.
  • InnoDB 스토리지 엔진같은경우 필요하다면 인덱스 키 추가 작업을 지연시키기도 하지만, 프라이머리 키나 유니크 인덱스의 경우 중복 체크가 필요하기때문에 즉시 추가하거나 삭제한다.
• 인덱스 키 삭제
  • 해당 키 값이 저장된 B-Tree의 리프노드를 찾아서 그냥 삭제 마크하면 작업 완료된다.
  • 5.5이상의 InnoDB 는 이역시 지연처리 할 수 있다.
• 인덱스 키 변경
  • 기존 인덱스 키 값을 삭제한 후 새로운 인덱스 키 값을 추가하는 작업으로 처리되고 InnoDB 스토리지 엔진을 사용하는 테이블에 대해서는 이 작업 모두 체인지 버퍼를 활용해 지연 처리 될 수 있다.
  • 체인지 버퍼: https://jeong0427.tistory.com/82
• B-Tree 인덱스 사용에 영향을 미치는 요소
  • 인덱스를 구성하는 칼럼의 크기, 레코드 건수, 유니크한 인덱스 키 값의 개수 등에 의해 검색이나 변경 작업의 성능이 영향을 받는다.
• 인덱스 키 값의 크기
  • Innodb 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 또는 블록이라 한다.
• B-Tree 깊이
  • 깊이를 직접 제어할 방법은 없고 인덱스의 키값에 의해 변경되기도하지만 크기는 가능하면 작게 만드는 것이 좋다.
• 선택도(기수성)
  • 인덱스에서 선택도 또는 기수성은 거의 같은 의미로 사용
  • 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미 인덱스 키 값중에 중복된 값이 많아지면 기수성이 낮아지고 검색할 대상도 많아져 그만큼 느려진다.
• 읽어야 하는 레코드 건수
  • 인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다.
  • 일반적인 DBMS의 옵티마이저에서는 인덱스를 통한 1건 읽는것은 테이블에서 직접 레코드 1건의 읽는 것보다 4~5배 정보 비용이 더 많이 드는 작업으로 예측한다.
  • 인덱스를 통해 읽어야 할 레코드의 건수(옵티마이저가 예상한 건수)가 전체 테이블 레코드의 20~25프로를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리하는 것이 효율적이다.