디스크 읽기 방식
데이터베이스의 성능 튜닝은 디스크 I/O를 줄이느냐가 관건
하드디스크 드라이브 HDD / 솔리드 스테이트 드라이브 SSD
SSD
플래시메모리 사용 -> 데이터를 빨리 읽고 쓸수 있음
전원이 공급되지 않아도 데이터가 삭제되지 않음
HDD보다 랜덤 I/O 작업이 빠름
DBMS용 스토리지에 최적화됨
랜덤 I/O 순차 I/O
순차 I/O는 3개의 페이지(3*16KB)를 디스크에 기록하기 위해 1번의 시스템 콜을 요청
랜덤 I/O는 3개의 페이지를 디스크에 기록하기 위해 3번 시스템 콜을 요청
순차 I/O는 랜덤 I/O 보다 3배 더 빠름
디스크의 성능은 시스템 콜을 줄이고 한번에 기록하느냐에 따라 결정 됨
램덤 I/O는 여러번 시스템 콜을 요청 하기 때문에 작업 부하가 더 큼
사실 순차 I/O를 램덤 I/O로 변경하기는 쉽지 않기 때문에 램덤 I/O를 줄여주는 것이 쿼리 튜닝이라고 봄
풀테이블 스캔 : 순차 I/O
인덱스 레인지 스캔 : 랜덤 I/O
인덱스
DBMS 는 칼럼 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스를 만듦
칼럼값을 주어진 순서로 미리 정렬해서 보관함
SotedList ArrayList
ArrayList는 테이터 파일과 같은 자료구조
저장되는 순서 그대로 유지
SotedList는 DBMS의 인덱스와 같은 자료구조
저장되는 값을 항상 정렬된 상태로 유지
데이터가 저장될 때 마다 값을 정렬해야하므로 저장하는 과정이 복잡하고 느리지만, 이미 정렬돼어 있어 읽기가 빠름
INSERT, UPDATE, DELETE 처리는 조금 느리지만, SELECT는 빨리 처리 가능함
인덱스는 데이터를 관리하는 방식(알고리즘)과 중복 값의 허용 여부 등에 따라 분류됨
역할별로 구분
프라이머리 키, 보조키(세컨더리 키)로 구분
데이터 저장 방식(알고리즘)으로 구분
B-Tree, Hash 구분
데이터 중복 허용 여부 구분
유니크 인덱스, 유니크하지 않은 인덱스 구분
B-Tree 인덱스
데이터 베이스의 인덱싱 알고리즘 가운데 가장 일반적으로 사용, 가장 먼저 도입된 알고리즘, 범용적인 목적으로 사용중
DBMS는 B+-Tree, B-Tree를 사용
B-Tree의 B는 Binary이진이 아닌 Balanced 임
B-Tree는 칼럼의 원래 값을 변형시키지 않고, 인덱스 구조체 내에서는 항상 정렬된 상태로 유지
B-Tree 구조 및 특성
최상위에 하나의 루트노드가 존재, 하위에 자식노드가 붙어있는 형태, 트리구조의 가장 하위에 있는 노드를 리프노드, 중간 노드를 브랜치 노드라고 함
데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리, 리프노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있음
인덱스의 키 값은 모두 정렬돼 있지만, 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장되어 있음
레코드가 삭제되어 빈공간이 생기면 그 다음의 INSERT 된 데이터는 빈공간을 재활용 하기 때문에 데이터가 INSERT 순으로 저장되지 않음
다만, InnoDB는 기본적으로 프라이머리 키 순서로 저장됨
인덱스는 테이블의 키 칼럼만 가지고 있으므로 나머지 칼럼을 읽으려면 데이터 파일에서 해당 레코드를 찾음
리프노드는 데이터 파일에 저장된 레코드의 주소를 가짐
InnoDB은 프라이머리 키가 ROWID 역할을 함
프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가짐
InnoDB는 인덱스를 통해 레코드를 읽을 때 바로 데이터 파일을 읽지 못함, 프라이머리 키에 저장된 레코드를 읽고 해당 레코드를 가지고 인덱스 키를 찾음
B-Tree 인덱스 키 추가 및 삭제
테이블의 레코드를 저장하거나 변경하는 경우 인덱스 키 추가나 삭제 작업이 발생함
인덱스 키 추가
새로운 값이 B-Tree에 저장될 때 테이블에 저장 될 때 즉시 인덱스에 저장될 수도, 그렇지 않을 수 있음
B-Tree에 저장 될 때는 저장될 키 값을 이용해 B-Tree상의 적절한 위치를 검색함
저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프노드에 저장함
B-Tree는 상대적으로 쓰기 작업에 비용이 많이 듬
인덱스 키 삭제
B-Tree의 키 삭제는 간단함 해당 키 값이 저장된 B-Tree릐 리프 노드를 찾아 그냥 삭제 마크만 하면 작업 완료
삭제 마킹된 인덱스 키 공간은 방치하거나 재활용 할 수 있음
인덱스 키 변경
인덱스의 키 값은 그 값에 따라 저장 될 리프 노드의 위치가 결정되므로 B-Tree의 키 값이 변경 되는 경우 먼저 키 값을 삭제 후 다시 새로운 키 값을 추가함
인덱스 키 검색
인덱스 검색하는 작업은 B-Tree의 루트 노드부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행함 : 트리 탐색
B-Tree 인덱스를 이용한 검색은 100% 일치 또는 앞부분만 일치 하는경우 사용 가능, 부등호 비교 조건에서도 사용 가능
다만 인덱스를 구성하는 키 갚의 뒷부분은 사용 불가, 인덱스 키 값에 변형이 가해진 경우 사용 불가함(함수나 연산으로 변현된 값은 인덱스를 사용 할 수 없음)
B-Tree 인덱스 사용에 영향을 미치는 요소
인덱스를 구성하는 칼럼의 크기와 레코드의 건수, 유니크한 인덱스 키 값의 개수 등에 의해 검색이나 변경작업의 성능이 영향을 받음
인덱스 키 값의 크기
디스크에 데이터를 저장하는 가장 기본 단위 : page, Block 디스크의 모든 읽기 쓰기 작업의 최소 단위
인덱스는 페이지 단위로 관리되며, 루트, 브랜치, 리프노드를 구분한 기준이 페이지 단위임
일반적으로 DBMS의 B-Tree는 자식 노드의 개수가 가변적인 구조
자식 노드는 인덱스의 페이지 크기와 키 값의 크기에 다라 결정됨 기본은 16KB
자식노드 주소는 여러가지 복합적인 정보가 담긴 영역
하나의 인덱스 페이지(16KB)에는 16*1024/(16+12) 인 585개 저장 가능함(자식노드 585개를 가질 수 있는 B-Tree)
SELECT 쿼리가 레코드 500개를 읽는다면 인덱스 페이지 1번으로 해결 가능하지만, 1000개를 조회 한다면 2번 이상 디스크로부터 읽어야함
인덱스를 구성하는 키의 크기가 커지만, 디스크로 부터 읽어야 할 수가 늘어나고 그만큼 느려짐
B-Tree의 깊이
B-Tree의 깊이는 MySQL에서 값을 검색할 때 몇번이나 랜덤하게 디스크를 읽어야하는지와 직결되는 문제
인덱스 키 값의 크기가 커질 수록 하나의 인덱스 페이지에 담을 수 있는 인덱스 키 값의 개수가 적어지고, 같은 레코드 건수라도 B-Tree의 깊이가 깊어져서 디스크 읽기가 더 필요함
B-Tree 깊이는 최대한 작을 수록 좋음
선택도(기수성)
선택도, 기수성 같은 의미로 사용됨
인덱스 키 값 가운데 유니크한 값의 수를 의미
ex) 전체 인덱스 기 캆을 100개 인데 그중에서 유니크 키값이 10이라면 기수성은 10
중복된 값이 많아질수록 기수성은 낮아지고, 선택도는 떨어짐
선택도가 높을 수록 검색 대상이 줄어들기 때문에 그만큼 빨라짐
선택도가 좋지 않더라도 정렬이나 그루핑과 같은 작업을 위해 인덱스를 만드는것이 훨씬 나은 경우도 있음
인덱스에서 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을 줌
읽어야 하는 레코드의 건수
인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는것보다 높은 비용이 듦
100만건 레코드 중 50만건을 읽을 때 전체 테이블 모두 읽고 50만건을 버릴지, 인덱스로 50만건만 읽을지 판단을 해야함
일반적인 DBMS의 옵티마이저는 인덱스를 통해 레코드 1건을 읽는 것은 직접 읽는 것보다 4~5배 비용이 듬
인덱스를 통해 읽어야 할 레코드가 25% 이상일 경우 그냥 테이블 전체 조회를 하는것이 더 효율적임