데이터베이스의 물리적 저장
DBMS 역시 운영체제에서 실행되는 응용 프로그램이다.
(데이터베이스 파일도 운영체제의 파일 시스템에 저장되는 것)
실제 데이터가 저장되는 곳은 보조기억장치(HDD,SSD)이다.
하드디스크의 구조
플레이트:{
트랙1:{
섹터1,
섹터2,
...
섹터n,
},
트랙2,
...,
트랙n,
}
// 회전하는 플레이트를 하드디스크의 액세스 암과 헤더가 접근하여 원하는 섹터에서 데이터를 가져온다
// 하드디스크에서 저장된 데이터를 읽어오는 데 걸리는 시간은
// 1.모터에 의해서 분당 회전하는 속도(RPM:Revolutions Per Minute)
// 2.데이터를 읽을 때 엑세스 암이 이동하는 시간(latency time)
// 3.주기억장치(RAM)로 읽어오는 시간(transfer time)
// 에 영향을 받는다.액세스 시간(디스크의 입출력 시간) : 탐색시간 + 회전 지연시간 + 데이터 전송시간
-
탐색 시간
액세스 헤드를 트랙에 이동시키는 시간
-
회전 지연시간
섹터가 액세스 헤드에 접근하는 시간
-
데이터 전송시간
데이터를 주기억장치(RAM) 로 읽어오는 시간
이렇게 디스크에 액세스하는 시간이 컴퓨터 시스템의 연산 속도를 따라가지 못하기 때문에 이러한 속도 차이에서 오는 문제를 해결하기 위해 주기억장치(RAM)에 DBMS가 사용하는 공간 중 일부를 버퍼 풀로 만들어 사용하기도 한다.
DB는 버퍼에 자주 사용하는 데이터를 저장해두고 LRU알고리즘을 이용하여 사용빈도가 높은 데이터 위주로 저장하고 관리하는 것이다.
이렇게 할 경우, 데이터 검색 시 DBMS는 버퍼 풀에 저장된 데이터부터 먼저 읽어 들인 후 작업을 진행한다.
LRU(Least Recently Used)는 최근에 사용되지 않은 순서대로 기억 장소에서 할당을 제외하는 알고리즘이다
인덱스와 B-tree
인덱스 : 원하는 데이터를 빨리 찾기 위해 튜플의 키 값에 대한 물리적 위치를 기록해둔 자료구조
일반적인 RDBMS의 인덱스는 대부분 B-tree구조로 되어 있다.
루트노드:{
내부 노드1:{
리프노드1,
리프노드2,
...,
리프노드n,
},
내부 노드2,
...,
내부 노드n
}-
리프 노드 : 모두 같은 레벨에 존재하는 균형 트리
-
B-tree의 각 노드는 키 값과 포인터를 가진다.
-
키 값은 오름차순으로 저장되어 있으며,
-
키 값 좌우에 있는 포인터는 각각 키 값보다 작은 값과 큰 값을 가진 다음 노드를 가리킨다
(따라서 키 값을 비교하여 다음 단계의 노드를 쉽게 찾을 수 있다) -
모든 검색은 루트 노드에서부터 시작하여 내부 노드를 지나 리프 노드까지 내려가면서 이루어진다
-
B-tree는 키 값이 새로 추가되거나 삭제될 경우 동적으로 노드를 분할하거나 통합하여 항상 균형 상태를 유지한다
-
리프 노드에는 해당 데이터의 저장 위치에 대응하는 rowid(RID : Row IDentify/테이블 행에 대한 논리적 위치)를 가지고 있어 찾고자 하는 행을 바로 찾을 수 있다.
-
다만, 데이터의 변경이나 추가가 잦을 경우 B-tree의 모양을 유지하기 위해 노드의 분할 및 이동이 자주 발생하는 문제가 있다.
-
인덱스는 테이블에서 한 개 이상의 속성을 이용하여 생성한다.
-
빠른 검색과 함께 효율적인 레코드 접근이 가능하다
-
순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지한다
-
저장된 값들은 테이블의 부분집합이 된다
-
일반적으로 B-tree 형태의 구조를 가진다
-
데이터의 수정, 삭제 등의 변경이 발생하면 인덱스의 재구성이 필요하다
MySQL 인덱스
MySQL의 인덱스는 클러스터 인덱스와 보조 인덱스로 나누어지며 모두 B-tree인덱스를 기본으로 한다.
클러스터 인덱스(248pg)
클러스터 인덱스는 연속된 키 값의 레코드를 묶어서 같은 블록에 저장하는 방법으로 테이블당 하나만 생성할 수 있으며, B-tree인덱스의 리프 노드에서 페이지의 주소 값 대신 테이블의 열 자체가 저장되는 형태이다.
클러스터 인덱스는 인덱스의 리프 노드들이 정렬된 상태로 저장된 테이블 자체가 된다.
ex) 루트 노드
| key | node |
|---|---|
| 1 | 1 |
| 4 | 2 |
| 7 | 3 |
| 10 | 4 |
예를 들어 기본키값 8에 해당하는 행을 찾는다고 하면, 8은 7보다 크고 10보다 작으므로 node3(리프노드)으로 이동하게 된다.
(루트 노드 밑에 내부 노드 없이 바로 리프 노드네)
이 때의 node3은 기본키 7부터 기본키 9까지의 테이블이다.
이 테이블에서 순서대로 데이터에 접근하는 것이다.
-
이렇듯 테이블의 데이터가 키 값에 따라 정렬된 형태로 저장되어 있기에, 키 값에 의한 동등 및 범위(BETWEEN) 검색 모두에 유리하다
-
또한 인덱스 페이지가 단순해져 인덱스 저장 시 차지하는 공간도 작다
-
기본키를 생성하면 클러스터 인덱스는 자동으로 생성된다.
-
기본키를 지정하지 않으면 먼저 나오는 UNIQUE속성에 대하여 클러스터 인덱스를 생성한다.
-
기본키도 없고 UNIQUE 속성도 없는 테이블은 MySQL이 자체적으로 생성한 행번호(RowID)를 이용하여 클러스터 인덱스를 생성한다
(즉, 클러스터 인덱스는 무조건 존재한다)
보조 인덱스
보조 인덱스의 경우 속성의 값으로 B-tree인덱스를 구성하며 리프 노드의 각 행은 해당 페이지의 주소 값을 저장한다.
실무에서는 데이터가 갱신 또는 삭제되면서 앞서 클러스터 인덱스의 경우처럼 순서대로 저장된다기보단 무작위로 저장될 확률이 높다
이럴 때 보조 인덱스를 생성하면 키 값에 의한 동등 검색에 용이하다.
ex) 루트 노드
| key | node |
|---|---|
| C | 1 |
| java | 2 |
ex) 리프 노드
| node-2 | |
|---|---|
| java | 7 |
| javascript | 5 |
예를 들어 name속성이 있다고 치고, name이 javascript인 값을 찾는다고 가정하자.
알파벳 순서로 정렬했을 때 javascript는 java뒤에 위치하므로 2번 node(리프노드)로 접근한다.
이렇게 리프노드에 접근하면 javascript의 기본키가 5임을 알 수 있는 것이다.
-
이러한 보조 인덱스는 테이블당 여러 개를 만들 수 있다
-
테이블의 속성 하나만이 아니라 여러 개의 속성을 결합하여 인덱스를 구성할 수도 있다.
ex)어떠한 출판사의, 얼마짜리 책 -
저장 순서가 뒤섞여있기 때문에 범위 검색에서는 성능이 나오지 않는다
(인덱스 구성 시 자료의 저장 및 질의 형태에 따라 신중해야 한다) -
클러스터 인덱스를 제외한 모든 인덱스는 보조 인덱스이다
-
보조 인덱스의 각 레코드는 보조 인덱스 속성과 기본키 속성 값을 갖고 있다.
-
보조 인덱스를 검색하여 기본키 속성 값을 찾은 다음 클러스터 인덱스로 가서 해당 레코드를 찾는다.
실제 인덱스의 작동?(250~251pg)
보통 클러스터 인덱스와 보조 인덱스는 함께 사용된다.
ex) bookid와 bookname의 빠른 검색이 필요한 경우
기본키인 bookid의 경우 클러스터 인덱스가 자동으로 생성되고, bookname은 보조 인덱스를 직접 생성해준다
이렇게 구성해줄 경우 bookid를 검색할 때는 클러스터 인덱스만을 사용하고, bookname을 검색할 때는 보조 인덱스로 bookid를 찾은 다음 다시 bookid에 대한 클러스터 인덱스를 사용한다
이는 클러스터 인덱스로 저장된 데이터의 순서를 가능한 유지하면서 데이터의 삽입과 삭제에 대한 인덱스 관리 비용을 줄이기 위해서이다.
인덱스의 생성
인덱스는 데이터 검색을 빠르게 하기 위해 생성하는 것이지만, 선택도가 높은 경우 인덱스가 없는 것이 오히려 더 빠르다.
선택도 : 1/서로 다른 값의 개수
(즉, 값이 분산되어 있을수록 선택도가 낮다)
-
인덱스는 WHERE절에 자주 사용되는 속성이어야 한다
-
인덱스는 조인에 자주 사용되는 속성이어야 한다
-
단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다
(테이블 당 4~5개 권장) -
속성이 가공되는 경우 사용하지 않는다
-
속성의 선택도가 낮을 수록 유리하다
(즉, 기본키처럼 UNIQUE한 값일 수록 유리하다는 것)
CREATE [UNIQUE] INDEX [인덱스이름]
ON 테이블이름 (컬럼 [ASC | DESC] ,반복);
-- UNIQUE는 속성 값에 대하여 중복이 없는 유일한 인덱스를 생성하는 것-- 생성된 인덱스를 확인하는 명령어
SHOW INDEX FROM 테이블이름;MySQL Workbench에서 sql문을 실행한 후, 메뉴의 Query => Explain Current Statement를 실행하면 인덱스를 활용하여 결과를 출력하는 과정이 반환된다.
인덱스의 재구성과 삭제
- 인덱스의 재구성
ANALYZE TABLE 테이블이름;B-tree인덱스는 데이터의 수정, 삭제, 삽입이 잦을 경우 (삭제된 레코드의 인덱스 값 자리가 비게 되는) 단편화(fragmentation)현상이 나타나게 되고 이는 곧 검색 성능 저하로 이어진다.
이때 재구성 명령어를 통해 인덱스를 최적화(재생성)해주면 좋다.
- 인덱스의 삭제
DROP INDEX 인덱스 이름 ON 테이블 이름;