8장 인덱스 (1)

디스크 읽기 방식

데이터를 읽는 절차 : 디스크의 플래터를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 후 데이터를 읽는것

(HDD 기준) 3개의 페이지를 읽는 과정에서 
랜덤 I/O : 3번의 시스템 콜을 요청, 디스크의 헤드를 3번 움직임
순차 I/O : 1번의 시스템 콜 만으로 접근 가능

 

인덱스 레인지 스캔은 주로 랜덤 I/O 를 사용, 풀 테이블 스캔은 순차 I/O 를 사용

큰 테이블의 레코드 대부분을 읽는 작업에선 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하는게 유리할 수 있음

 

쿼리를 튜닝하는 것은 결국 랜덤 I/O 를 줄여주는 것

 

---

인덱스

B-Tree 알고리즘 : 칼럼의 값을 변형하지 않고 인덱싱
Hash 인덱스 : 값을 변형해서 인덱싱, 전방(Prefix) 일치와 같이 값의 일부만 검색, 혹은 범위 검색에는 사용할 수 없음

 

MySQL 에서의 B-Tree

MyISAM 엔진 : 실제 레코드가 정렬돼 있지 않고 임의의 순서로 저장됨
InnoDB 엔진 : 실제 레코드가 클러스터되어 디스크에 저장되므로 기본적으로 PK 순서로 정렬되어 저장됨

 

인덱스의 키 추가 및 삭제

• 인덱스 키 추가 : 저장될때 적절한 위치를 검색, 레코드를 추가하는 작업 비용을 1 이라고 했을때 인덱스에 키를 추가하는 작업 비용을 1.5 정도로 계산, InnoDB 에서 추가 작업은 지연시켜 처리할 수 있지만 PK 나 유니크 인덱스의 경우 중복체크가 필요하므로 즉시 처리됨
• 인덱스 키 삭제 : B-Tree 의 리프 노드를 찾아 삭제 마크만 하면 됨, InnoDB  에서 버퍼링 되어 지연 처리 가능
• 인덱스 키 변경 : 키 값에 따라 저장될 리프 노드의 위치가 결정되므로 키 값이 변경되는 경우 인덱스상의 키 값만 변경하는게 불가능, 해당 작업은 1. 키 값을 삭제 2. 다시 새로운 키를 추가 하는 방법으로 처리됨. 해당 작업도 체인지 버퍼를 이용해 지연 처리 가능함.
• 인덱스 키 검색 : 인덱스는 빠른 검색을 위한것, SELECT 시에 당연히 사용되며 UPDATE, DELETE 시에도 찾은 후 처리하므로 인덱스가 사용됨. 인덱스는 100% 일치, 앞부분만 일치, 부등호조건에서 활용가능. 뒷부분만 검색하거나 키 값에 변형이 가해진 경우엔 사용이 불가능
  InnoDB 에선 레코드 잠금이나 넥스트 키락이 검색을 수행한 인덱스를 잠근 후 레코드를 잠그기때문에 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠그게 됨.

 

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

InnoDB 엔진에서 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 라고 한다

페이지 크기의 기본값은 16KB 인데 인덱스의 키가 16Byte, 자식노드의 주소 12Byte 로 총 30Byte일 경우 한 페이지에 16*1024 / (16+12) = 585 개의 인덱스를 저장가능

키 값이 커질경우 디스크로부터 읽어야 하는 횟수가 늘어나고 그만큼 느려진다.

키 값의 길이가 길어질경우 인덱스의 크기가 커지고 메모리에 캐시할 수 있는 레코드가 감소하므로 메모리 효율이 떨어진다.

 

B-Tree 의 깊이

키 값의 크기가 커지면 커질수록 한 페이지에 담을 수 있는 인덱스의 키 값의 갯수가 적어지고 깊이가 깊어져 더 많은 디스크 읽기가 실행됨. 보통 5단계를 넘진 않는다

 

---

선택도(기수성)

기수성(Cardinality) 는 모든 인덱스 키 값 중에 유니크한 값의 수를 의미
인덱스 키 값 중 중복된 값이 많아지면 -> 기수성은 낮아짐 -> 선택도가 낮아짐 -> 검색할 양이 많아짐 -> 느리게 처리됨

 

읽어야 하는 레코드의 건수

인덱스를 통해 1건을 읽는것이 테이블에서 직접 1건을 읽는 것 보다 4~5배 비용이 더 많이드는것으로 예측한다

인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 사용하지 않고 테이블 풀 스캔으로 처리하는것이 효율적