Preface
이번 장에선 데이터의 검색 속도와 연관된 인덱스에 대해 공부했다.
인덱스의 개념 자체는 일상생활에서도 흔히 쓰이므로 쉽게 이해할 수 있었지만, 클러스터형 인덱스와 보조 인덱스의 차이를 완벽히 이해하기까지는 시간이 조금 걸렸다.
이번 장을 공부하며 한 가지 궁금했던 점이 있는데,
두 인덱스를 혼합하여 사용할 때 보조 인덱스의 리프 페이지는 클러스터형 인덱스의 키 값을 가진다는 부분이다.
만약 보조 인덱스의 리프 페이지가 주소값을 가지게 되면 추후 데이터 입력 시 클러스터형 인덱스의 리프 페이지가 재구성되어 데이터 페이지의 번호 및 오프셋이 대폭 변경된다고 하는데,
이런 문제점을 피하기 위해 데이터 페이지를 각각 따로 두면 되지 않을까?
위의 의문점에 대해 이런 저런 생각을 해봤는데, 아마도 데이터 페이지를 2개 사용하여 DB의 저장 공간을 차지하는 것이 보조 인덱스를 검색할 때 클러스터형 인덱스를 거치는 것보다 시스템 성능에 비효울적이기 때문에 후자의 방법을 사용하는 것 같다는 결론을 내렸다.
물론 내 개인적인 의견일 뿐 정확한 이유는 모르겠다.
남들 다 걸릴 땐 멀쩡하다가 이제 와서 코로나 걸려버렸다. ㅋㅋ
나도 이제 화석이라 유행 못따라가는건가;
내 주변 사람들은 아프지도 않고 별 증상도 없었다고 하는데, 나는 목요일부터 이틀 간 열도 심하고 목도 아파서 엄청 고생했다.
다음주 부턴 기말고사 준비를 해야 해서 개발 공부할 시간도 없는데 아직 MySQL은 끝날 기미가 보이지 않는다.
종강 전에 SQL을 마치고 바로 Web 공부를 시작할 계획이었지만, 아무래도 계획이 조금씩 뒤로 밀릴 것 같아 걱정이다.
6월 15일은 돼야 모든 시험이 끝날 것 같은데...
방학 땐 방에 틀어박혀 개발 공부만 하자.
1. 인덱스의 개념
- 인덱스: 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조
- 전체 테이블 검색(Full Table Scan): 인덱스를 사용하지 않고 데이터를 검색하는 방법
- 인덱스의 존재 유무와 별개로 사용할지 말지는 MySQL이 스스로 판단한다.
- 인덱스 사용의 장점
1) 검색 속도가 빨라질 수 있다.
2) 해당 쿼리의 부하가 감소하여 시스템 전체의 성능이 향상될 수 있다.
- 인덱스 사용의 단점
1) DB의 공간을 차지한다. (대략 DB 크기의 10%정도에 해당하는 추가 공간 필요)
2) 처음 인덱스를 생성하는데 시간이 많이 소요될 수 있다.
3) 데이터의 변경 작업(Insert, Update, Delete)이 자주 일어나는 경우에는 오히려 성능이 나빠질 수도 있다.
2. 인덱스의 종류와 자동 생성
- 클러스터형(Clustered) 인덱스
1) 테이블당 한 개만 생성할 수 있다.
2) 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬한다.
3) Primary Key로 지정하면 자동으로 해당 열에 클러스터형 인덱스가 생성된다.
- 보조(Secondary) 인덱스
1) 테이블당 여러 개를 생성할 수 있다.
2) Unique로 지정하면 자동으로 해당 열에 보조 인덱스가 생성된다.
- 튜닝: SQL 서버가 기존보다 더욱 좋은 성능을 내도록 하는 전반적인 방법론
1) 응답시간을 빨리하는 것
→ 전체적인 시스템의 성능은 오히려 나빠질 수도 있다.
2) 서버의 부하량을 최소화하는 것
→ 서버가 처리하는 총 작업량을 줄임으로써 시스템의 전체적인 성능을 향상시킨다.
- 테이블 생성 시에 제약 조건 Primary Key 또는 Unique를 사용하면 인덱스가 자동 생성된다.
→ 보통 전자는 클러스터형 인덱스, 후자는 보조 인덱스를 생성한다.
- SHOW INDEX FROM 테이블이름;의 결과 분석
1) Non_unique: 0이면 Unique인덱스를, 1이면 Nonunique인덱스를 의미
2) Key_name: Index_name과 같은 의미로 인덱스의 이름
→ PRIMARY KEY로 표기되면 보통 클러스터형 인덱스를 의미하고, 보조 인덱스는 열의 이름 또는 키 이름으로 표시된다.
- Index의 종류
1) Unique Index: 인덱스 값들이 서로 중복되지 않는 인덱스
2) Nonunique Index: 인덱스 데이터가 중복되는 인덱스
- UNIQUE에 NOT NULL이 포함되면 클러스터형 인덱스로 지정된다. (이름은 보조 인덱스의 이름)
→ Primary Key로 설정한 열이 있으면 그 열에 클러스터형 인덱스가 생성된다.
- 많은 데이터가 입력된 후에 Alter문으로 Unique나 Primary를 지정하면 인덱스를 구성하는 데 많은 시간이 걸릴 수도 있다.
3. 인덱스의 내부 작동
- B-Tree(Balanced Tree): 균형 트리
- 노드: 트리 구조에서 데이터가 존재하는 공간
- 루트 노드: 노드의 가장 상위 노드
- 리프 노드: 노드의 가장 하위 노드
- 중간 수준 노드: 루트 노드와 리프 노드의 중간에 끼인 노드
- 페이지: 16KByte 크기의 최소한의 저장 단위
1) MySQL이 B-Tree를 사용할 때 노드에 해당되는 것
2) 아무리 작은 데이터를 한 개만 저장하더라도 한 개 페이지(16KByte)를 차지하게 된다는 의미
- 인덱스를 구성하면 데이터의 변경 작업 시에 성능이 나빠진다.
→ 페이지 분할 작업이 발생되기 때문이다.
- 페이지 분할: 한 개의 페이지에 데이터를 모두 저장할 수 없는 경우 새 페이지를 생성한 후 데이터를 분할하는 것
- 클러스터형 인덱스는 리프 페이지가 데이터 페이지다.
1) 데이터의 검색 속도가 보조 인덱스보다 빠르다.
→ 특히 범위 검색 시에 우수한 성능: 데이터 페이지가 정렬되어 있기 때문
- 보조 인덱스 생성 방법
1) 데이터 페이지를 건드리지 않고, 별도의 장소에 인덱스 페이지를 생성
2) 인덱스 페이지의 리프 페이지에 인덱스로 구성한 열을 정렬
※ 데이터 페이지를 정렬하는 것이 아님
3) 데이터 위치 포인터(주소값: 페이지 번호 + #오프셋)를 생성
- 보조 인덱스에 데이터를 입력할 경우, 데이터 페이지의 뒤쪽 빈 부분에 삽입된다.
→ 페이지 분할 X
- 클러스터형 인덱스에 데이터를 입력할 경우 데이터 페이지를 정렬해야 하므로 페이지 분할이 발생한다.
→ 시스템 성능에 주는 부하가 크다.
- 클러스터형 인덱스의 특징
1) 인덱스 생성 시에는 데이터 페이지 전체가 다시 정렬된다.
2) 인덱스 자체의 리프 페이지가 곧 데이터이다.
3) 보조 인덱스보다 검색 속도는 빠르지만, 데이터의 입력/수정/삭제는 느리다.
4) 성능이 좋지만 테이블당 한 개만 생성할 수 있다.
- 보조 인덱스의 특징
1) 인덱스의 생성 시에는 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성한다.
2) 인덱스 자체의 리프페이지는 데이터가 아니라 데이터가 위치하는 주소값이다.
3) 클러스터형보다 검색 속도는 느리지만, 데이터의 입력/수정/삭제는 빠르다.
4) 여러 개 생성할 수 있다.
- OLTP(On-Line Transaction Processing): 네트워크 상의 온라인 사용자들의 DB에 대한 일괄 트랜잭션 처리
- OLAP(On-Line Analytical Processing): 데이터를 분석하고 의미있는 정보로 치환하거나, 복잡한 모델링을 가능하게끔 하는 분석 방법
- 트랜잭션: DB의 상태를 변경시키기 위해 수행하는 작업의 단위
- 클러스터형 인덱스와 보조 인덱스가 혼합되어 있을 경우
1) 보조 인덱스의 리프 페이지는 클러스터형 인덱스의 키 값을 가지게 된다.
→ 보조 인덱스를 검색한 후에는 모두 다시 클러스터형 인덱스의 루트 페이지부터 검색한다.
'DB & SQL > 이것이 MySQL이다' 카테고리의 다른 글
| DB 및 테이블의 정보를 출력하는 명령어 (0) | 2022.05.29 |
|---|---|
| 인덱스 (2) (0) | 2022.05.29 |
| 뷰 (0) | 2022.05.21 |
| 테이블 (0) | 2022.05.20 |
| SQL 프로그래밍 (0) | 2022.05.17 |
댓글