MySQL로 배우는 데이터베이스 개론과 실습 (2)

책/MySQL로 배우는 데이터베이스 개론과 실습

MySQL로 배우는 데이터베이스 개론과 실습 (2)

atmosg 2024. 11. 17. 00:28

13. 데이터의 물리적 저장

# 인덱스란

데이터를 빠르게 찾을 수 있도록 만든 데이터 구조(자료구조) 그 자체
DB에선 좀더 구체화해서 튜플의 키 값에 대한 물리적 위치를 기록해 둔 자료구조를 뜻함
통상 B-tree (Balanced-tree) 구조로 돼있음

# DB의 물리적 저장

생각해보면 DB는 데이터를 저장(INSERT)할 때 어디에 어떻게 저장할까?
워드를 생각해보면 각 문서는 .docx란 확장자의 파일 형태로 하드에 저장됨
DBMS의 경우엔 각 데이터를 저마다의 고유한 방식으로 저장 및 관리함
다만, 방식이야 다양하다만 최종적으론 보조기억장치(HDD, SSD)에 저장됨

# MySQL의 물리적 저장

DB의 데이터는 저장 시 최종적으로 보조기억장치에 저장된다고 했음
그리고 이때 어떤 형태로 저장할 것인지도 정해져야 하는데(.docx 파일처럼) 이는 DBMS마다 다름
MySQL의 InnoDB 엔진 기준, 테이블 생성 시 정의된 내용에 따라 데이터를 논리적으로 구분 지은 후 각각의 파일 형태로 저장함
아래는 InnoDB가 저장하는 주요 파일임
참고로 MySQL 파일이 저장된 폴더는 SHOW VARIABLES LIKE 'datadir'; 명령어로 확인 가능함

# 액세스 시간(access time)

디스크의 데이터 입출력 시 소요되는 시간
대부분 보조기억장치로 하드디스크를 사용하기 때문에 액세스 시간은 다음과 같이 표현됨
액세스 시간 =
탐색시간(seek time, 액세스 헤드를 트랙에 이동시키는 시간) +
회전 지연시간(rotational latency time, 섹터가 액세스 헤드에 접근하는 시간) +
데이터 전송시간(data transfer time, 데이터를 주기억장치로 읽어오는 시간)

# 버퍼 풀(Buffer Pool Memory)

요새 컴퓨터는 정말 빠르지만 하드디스크는 액세스 속도가 처참할 정도로 느림
이러한 속도 문제를 줄이고자 DBMS는 주기억장치 공간 중 일부를 버퍼 풀로 만들어 사용함
즉, DB는 자주 사용하는 데이터를 버퍼 풀에 위치시키고 LRU 알고리즘을 이용해 데이터를 저장 및 관리함
따라서 데이터 검색 시 DBMS는 버퍼 풀에 저장된 데이터를 우선 읽어들인 후 작업을 진행함

14. 인덱스

# 인덱스란

데이터를 빠르게 찾을 수 있도록 만든 데이터 구조(자료구조) 그 자체
DB에선 좀더 구체화해서 튜플의 키 값에 대한 물리적 위치를 기록해 둔 자료구조를 뜻함
통상 B-tree (Balanced-tree) 구조로 돼있음
MySQL에선 인덱스를 클러스터 인덱스(cluster index)와 보조 인덱스(secondary index)로 구분해서 사용함

# B-tree (Balanced tree)

데이터 검색 시간 단축을 위해 고안된 자료구조
루트 노드, 내부 노드, 리프 노드로 구성되며, 리프 노드가 모두 같은 레벨에 존재하는 균형 트리임
각 노드는 키와 포인터를 가지며, 키는 오름차순으로 저장됨
리프 노드엔 찾고자 하는 데이터의 저장 위치를 가리키는 rowid(RID, Row IDentify)가 담기게 됨

# 인덱스의 특징

테이블에서 한 개 이상의 속성으로 생성됨
저장된 값들은 테이블의 부분집합이 됨
정렬된 속성과 데이터의 위치만 보유하므로 테이블 대비 공간 차지가 적음
빠른 검색, 효율적 레코드 접근이 가능함
대신 데이터 수정/삭제 등의 변경이 발생하면 인덱스 재구성이 필요함

# 클러스터 인덱스

연속된 키 값의 레코드를 묶어서 같은 블록에 저장하는 구조의 인덱스
(테이블 생성 시 기본키(PK)를 생성하면 자동으로 생성됨)
테이블당 하나만 생성할 수 있는 특별한 인덱스임
리프 노드에 페이지 주소 값 대신 테이블의 열 자체가 저장됨
데이터가 키 값에 따라 정렬돼있어서 키 값에 의한 동등 및 범위(BETWEEN) 검색 모두에 유리
인덱스 페이지도 단순해서 인덱스 저장 공간도 적게 차지함

# 보조 인덱스

하나 혹은 여러 개의 속성 값으로 B-tree를 구성하여 만들어지는 인덱스
리프 노드엔 찾고자 하는 데이터의 페이지 주소 값이 저장됨
보조 인덱스는 필요에 따라 여러 개 만들어 사용이 가능함
보조 인덱스도 당연히 속성 값에 의한 동등 검색에 유리함
다만 아래 그림처럼 실제 테이블의 자료 순서는 일정치 않을 수 있어서 범위 검색은 원하는 수준의 속도가 안나올 수 있음

# MySQL 인덱스

보통 클러스터 인덱스와 보조 인덱스는 함께 사용됨
굳이 두 개를 혼합사용하는 이유는 다음과 같음
① 클러스터 인덱스로 저장된 데이터의 순서를 최대한 유지하면서
② 데이터 삽입/삭제에 대한 인덱스 관리 비용을 낮추기 위해
아래는 bookid(클러스터 인덱스) bookname(보조인덱스)를 어떻게 혼합사용 하는지 나타내는 예시

※ MySQL 인덱스 표 정리

인덱스	설명
클러스터 인덱스	테이블 생성 시 기본키를 지정하면 자등으로 생성됨 기본키를 지정하지 않으면 먼저 나오는 UNIQUE 속성에 대해 클러스터 인덱스를 생성함 PK, UNIQUE 속성 둘 다 없는 테이블은 MySQL이 자체적으로 생성한 행번호(rowID)를 이용해 클러스터 인덱스를 생성함
보조인덱스	클러스터 인덱스가 아닌 모든 인덱스는 보조 인덱스임 보조 인덱스의 각 레코드는 보조 인덱스 속성과 기본키 속성값을 갖고 있음 (위에 그림 참고) 보조 인덱스를 검색해 기본키 속성값을 찾은 후 클러스터 인덱스로 가서 해당 하는 레코드를 찾아냄

15. 인덱스 생성 및 변경

# 인덱스 생성하기

기본 문법

CREATE [UNIQUE] INDEX [인덱스 이름]
ON 테이블이름 (컬럼 [ASC | DESC] [{, 컬럼 [ASC | DESC]}, ...]);

Q1. Book 테이블의 bookname 열을 대상으로 ix_Book을 생성하시오
```
CREATE INDEX ix_Book On Book(bookname);
```
Book 테이블의 publisher, price 열을 대상으로 ix_Book2를 생성하시오
```
CREATE INDEX ix_Book2 ON Book(publisher, price);
```
위에서 생성된 인덱스는 SHOW INDEX FROM Book; 으로 확인 가능
MySQL이 인덱스를 활용해서 SQL 문을 처리하는지 확인하려면 [Query] - [Explain Current Statment]를 누르면 됨 (MySQL워크벤치 기준)

# 인덱스 생성시 주의사항

인덱스를 쓴다고 데이터 검색이 무조건 빨라지는건 아님
선택도(selectivity)가 높으면 인덱스가 없는 편이 차라리 더 빠를 수 있음

# 선택도(Selectivity)

특정 열(column)에서 얼마나 고유한 값들이 존재하는지 나타낸 값
선택도 = 열의 고유 값 갯수 / 전체 행(Row) 갯수
선택도가 높다는 건 각각의 값이 고유하므로 인덱스 효과가 좋다는 뜻임
아래 표에서 'city' 열의 선택도는 0.5임 (고유값 2개 NewYork, LA / 총 4개 행)

id	name	city
1	Alice	New York
2	Bob	New York
3	Carol	Los Angeles
4	Dave	New York

# 단편화(Fragentation)

데이터베이스나 파일 시스템에서 공간이 비효율적으로 사용되는 현상을 말함
데이터가 연속적으로 저장되지 못하고 비는 공간이 생기는 경우 발생함
인덱스의 경우, 잦은 데이터 삽입/수정/삭제에 의해 단편화 현상이 발생할 수 있음
단편화는 검색 성능 저하, 저장공간 낭비, 백업 및 복구 시간 증가 같은 문제들이 발생함

# 단편화 해결하기 (인덱스 재구성&삭제)

우선 단편화 확인을 위해 ANALYZE TABLE table_name; 명령어를 사용함
최적화 명령어로 OPTIMIZE TABLE table_name; 명령어 사용
혹은 DROP INDEX ix_Book On Book; 처럼 인덱스를 제거해버리는 것도 방법