8.1 디스크 읽기 방식
8.1.1 HDD와 SSD
HDD는 기계식 장치로써, 원판을 돌려서 원하는 데이터를 읽어와야 하기 때문에 읽고 쓸때 병목이 발생한다.
SSD(Solid State Drive)는 원판을 제거하고 플래시 메모리를 장착하였다.
- 플래시 메모리
전원이 공급되지 않아도 데이터가 삭제되지 않음 (전자상태를 저장하고 있음)
D-RAM 보다는 느리지만 기계식 하드 디스크 보다는 훨씬 빠르다.
SSD의 장점은 데이터를 원판을 돌려 찾지 않아도 되므로 순차 I/O보다는 랜덤 I/O가 발생할 때 속도가 빠르다.
8.2 인덱스란?
DBMS의 인덱스는 SortedList와 마찬가지로 저장되는 컬럼의 값을 이용해 항상 정렬된 상태를 유지한다. 데이터 파일은 ArrayList와 같이 저장된 순서대로 별도의 정렬없이 그대로 저장해 둔다.
따라서 인덱스는 데이터가 저장될 때마다 항상 값을 정렬해야하므로 저장하는 과정이 복잡하고 느리지만 , 이미 정렬돼 있어서 아주 빨리 원하는 값을 찾아올 수 있다
결론적으로 DBMS에서 인덱스는 데이터의 저장(INSERT,UPDATE,DELETE)성능을 희생하고 대신 데이터의 읽기 속도를 높이는 기능이다.
인덱스 종류
- 프라이머리 키(인덱스)
테이블을 대표하는 컬럼으로 만들어진 인덱스, NULL 값을 허용하지 않으며, 중복을 허용하지 않음
- 세컨더리 인덱스
프라이머리 인덱스를 제외한 모든 인덱스
인덱스 알고리즘
- B-Tree
가장 일반적인 인덱스 알고리즘, 컬럼의 값을 변형하지 않고 원래 값을 이용하여 인덱싱
- Hash 인덱스
컬럼의 값으로 해시값을 계산해서 인덱싱,
장점 : 빠른 검색지원
단점 : 범위 검색, 부분일치 검색 불가능
8.3 B-Tree 인덱스
루트노드 - 브랜치노드 - 리프 노드 구성
InnoDB의 가장 큰 특징은 프리이머리 키를 통해 구성된 클러스터링 인덱스 자체가 데이터 파일 이라는 점이다.
그리고 세컨더리 인덱스의 리프노드에는 실제 데이터의 위치가 아닌 프라이머리키 값이 들어있다.
따라서 세컨더리 인덱스를 사용해 데이터를 찾을때는 세컨더리 인덱스로 프라이머리 키 값을 찾고 , 해당 키 값을 통해 다시 프라이머리 인덱스를 검색해서 데이터를 찾아와야 한다.
데이터 자체가 클러스터링 인덱스 ( = 프라이머리 키) 로 정렬되어 있기 때문에 프라이머리 키 값의 변화가 있을 때마다 데이터의 물리적인 위치 또한 변경되게 된다.
따라서 세컨더리 인덱스의 리프노드에 데이터의 물리적인 주소가 있다면, 프라이머리 키 값 변경에 따라 모든 세컨더리 인덱스의 정보 또한 변경되어야 하기 때문에 세컨더리 인덱스에는 물리주소가 아닌 프라이머리 키 값을 저장한다.
8.3.2 B-tree 인덱스 키 추가 및 삭제
레코드가 삽입되면 B-tree에서 적절한 위치에 삽입되어야 한다. 이 과정에서 리프노드가 split(분리) 될수도 있다. 이는 상위 브랜치 노드까지 처리 범위가 넓어질수 있기 때문에 B-tree는 상대적으로 쓰기 작업에 비용이 많이 든다.
대략 테이블에 레코드를 추가하는 작업 비용을 1이라고 하면 해당 테이블 인덱스에 키를 추가하는 작업 비용을 1.5정도로 예측한다
처리 비용이 크기 때문에 InnoDB는 인덱스 추가 작업을 바로 수행하지 않고 버퍼를 통해 백그라운드로 작업하기도 한다 ( 프라이머리키 / 유니크 인덱스는 중복 체크가 필요하기 때문에 즉시 인덱스 추가 작업이 이루어져야 한다.)
8.3.2.4 인덱스 키 검색
B-Tree 인덱스를 이용한 검색은 100%일치, 값의 앞부분 일치, 비교 조건에서 활용할 수 있다.
인덱스 키 값의 변형이 가해지면 인덱스를 사용할 수 없다(함수,연산)
8.3.3.1 인덱스 키 값의 크기
인덱스 키 값이 커지면 하나의 페이지(16KB)에 저장할 수 있는 인덱스 노드가 적어지게 된다.
따라서 읽어와야할 페이지가 많아지고 , 메모리에 Load 시킬수 있는 데이터가 작아지게 된다.
8.3.3.3 선택도(기수성)
선택도(Selectivility) 와 기수성(Cardinality)는 거의 같은 의미로 사용되며, 유니크한 값의 수를 의미한다. 전체 인덱스 키 값이 100개일때 유니크한 값의 수가 10개라면 Cardinality는 10이 되는 것이다.
인덱스는 선택도가 높을수록(유니크한 값이 많을수록) 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.
8.3.3.4 인덱스 읽기의 손익 분기점
인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다.테이블이 100만건일 때 인덱스를 통해 50만건을 읽어오는 것과 테이블 전체 100만건을 가져오고 필요없는 50만건을 버리는 것중 어는 것이 효율적일지 생각해보아야 한다.
일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측한다. 즉 인덱스를 통해 읽어야할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 사용하지 않고 테이블을 모두 읽어서 필요한 레코드만 필터링 하는 것이 효율적이다.
8.3.4 B-Tree 인덱스를 통한 데이터 읽기
8.3.4.1 레인지 스캔
인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됬을 때 사용하는 방식이다.
레인지 스캔의 순서는 다음과 같다. (세컨더리 인덱스의 경우)
- 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. -> 인덱스 탐색(Index Seek)
- 1번에서 탐색된 위치로부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다 -> 인덱스 스캔 (Index Scan)
- 2번에서 읽어들인 프라이머리키를 통해 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽어온다.
쿼리가 필요로하는 데이터에 따라 3번 과정이 필요하지 않을 수도 있는데, 이를 커버링 인덱스 라고 한다.
- 커버링 인덱스
인덱스 구성 컬럼만을 Select 하는 경우, 인덱스 조회만으로 원하는 데이터를 얻을 수 있으므로 데이터 페이지를 찾아가지 않아도 된다
8.3.4.2 인덱스 풀 스캔
인덱스 레인지 스캔과를 달리 인덱스의 처음부터 끝까지 모두 읽는 것을 인덱스 풀 스캔이라고 한다.
대표적으로는 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫번째 컬럼이 아니고, 커버링 인덱스가 가능한 경우 사용된다.
즉 index(A,B,C) 로 구성된 테이블에서 select B,C 를 하게 되면 인덱스 풀 스캔을 하게 된다.
일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 끝까지 읽는 것보다 인덱스만 읽는 것이 효율적이다. 다만 커버링인덱스가 아니여서 데이터 레코드 까지 읽어야 한다면 절대 이 방식으로 처리되지 않는다.
8.3.4.4 인덱스 스킵 스캔
복합 인덱스 중 첫번째 컬럼이 아닌 두번째 이하의 컬럼 값으로 조건검색을 했을 때는 인덱스를 타지 못했다.
하지만 MySQL 8.0 버전부터는 인덱스의 첫번째 컬럼이 아니더라도 인덱스를 사용할 수 있게 해주는 인덱스 스킵 스캔을 지원한다.
- 인덱스 스킵 스캔을 통한 실행계획이 세워지면 Extra부분에 Using index for skip sacn 이 명시된다.
인덱스 스킵 스캔은 생성된 인덱스를 최대한 활용해서 필요한 부분만 살펴보겠다는 것이기 때문에
인덱스를 구성하는 첫번째 컬럼의 유니크한 값이 작아야 한다. 첫번째 컬럼의 각 유니크한 값 별로 조건에 맞는 컬럼을 재 검색하는 방식이기 때문이다.
8.3.5 다중 컬럼 인덱스
여러개의 컬럼을 가지고 인덱스를 생성하는 경우이다.
CREATE INDEX ( gender, name )
다중 컬럼 인덱스에서 두번째 컬럼은 첫번째 컬럼에 의존해서 정렬되어 있다. 즉 두번째 컬럼의 정렬은 첫번째 컬럼이 똑같은 레코드에서만 의미가 있다.
그렇기 때문에 다중 컬럼 인덱스에서는 각 컬럼의 위치(순서)가 중요하다
