06-2. 인덱스의 내부 작동
👉🏻 트리 자료구조
- 나무를 거꾸로 뒤집어 놓은 자료구조
✅ 루트
- 나무(트리)의 맨 위
✅ 줄기
- 중간
✅ 리프
- 잎사귀
👉🏻 균형 트리
🧩 노드
- 데이터가 저장되는 공간
- 노드 하나에 데이터가 4건 들어가는 경우에 대한 예시
- 중간 노드는 없는 상황
- 맨 위가 루트
- 거기에 바로 잎사귀가 달려있는 구조
- 노드를 MySQL에서는 페이지라고 한다
- 루트 페이지, 리프 페이지 ..
- 한 페이지에 글자를 네 글자 쓸 수 있는 상황인 것
🧩 전체 테이블 검색 Full Table Scan
- 루트가 없고 리프 페이지만 있는 상황
- 데이터가 9건
- 각 페이지에 3건씩 골고루
- 인덱스가 없는 상태
- 3페이지짜리 책에 책 뒤 찾아보기가 없는 상황
- M을 찾으려면 처음부터 끝까지 하나씩 찾아봐야함
- 3페이지만에 찾을 수 있음
- 굉장히 안 좋은 것
- 만약 1000만 페이지짜리였으면 1000만페이지를 다 뒤져야함
- 시간이 매우 오래 걸림
✅ 인덱스 추가
- 리프페이지는 그대로 있고 루트페이지 추가
- 각 리프페이지의 제일 상단에 있는 데이터를 루트페이지로 가져옴
- 루트페이지가 있을 경우 루트페이지부터 읽어야해
- L다음이 M이니까 예측이 되어 바로 L이 있는 페이지로 가서 읽는다
- 총 두 페이지를 읽은 셈
- 굉장히 효율적인 것
* 인덱스가 있든 없든 데이터를 찾는 결과는 똑같다, 속도의 차이일 뿐
🧩 균형트리의 페이지 분할
- 인덱스를 구성하면 SELECT문은 빨라진다
- 인덱스를 구성하면 데이터 변경 작업(INSERT, UPDATE, DELETE)시 성능이 나빠진다
- JJJ하나 내리고 III 삽입
- 이것 자체는 문제가 되지 않음
✅ GGG를 입력하려면
- FFF 밑에 넣어야하는데 더이상 자리가 없다
- 네 페이지로 바뀌고 루트 페이지도 약간의 변화가 있음
- 다행히 루트 페이지는 남는 공간이 있어 페이지 분할이 일어나지는 않음
- 리프페이지에서만 새로운 페이지가 추가됨
- 이것이 느려지는 작동이다
✅ PPP, QQQ 넣기
- PPP는 남은 공간에 들어갈 수 있음
- 하지만 QQQ가 들어갈 공간이 없음, QQQ 삽입을 위해 페이지 분할
- 하지만 이제 루트페이지에 헤더를 올릴 자리가 없어, 루트페이지도 분할이 일어남
- 루트는 무조건 한 페이지만 있어야해
- 그래서 위에 새로운 루트페이지가 만들어짐
- 세 페이지의 분할이 일어남 (매우 느려지는 것)
* 인덱스가 있으면 느려지는 이유 = 페이지 분할
👉🏻 인덱스 구조
🧩 클러스터형 인덱스
🎮 클러스터형 인덱스 테이블 만들기
- 일단은 인덱스 없이 만들기
- 데이터 10건 넣기
- 차례대로 데이터가 차 있음
- 페이지 하나 당 4건의 데이터
- 마지막 두 건은 비어 있음
- PRIMARY KEY 지정하기 = 클러스터형 인덱스 생성
- mem_id 기준으로 정렬됨
- 알파벳 순으로 정렬 (영어사전처럼 만들기)
- (위에 데이터 10건 그냥 넣었을 때 그림이랑 비교해보기)
- 루트페이지에는 최상단 데이터가 들어감
🧩 보조 인덱스
🎮 보조 인덱스 테이블 만들기
- 보조 인덱스 테이블 만들기
- 일단은 인덱스 지정 없이
- 데이터 넣기
- 테이블 수정
- UNIQUE KEY 지정하면 보조 인덱스가 생성됨
- 차례 안 바뀜!
- 데이터 안 바뀜
- 책 뒤의 찾아보기가 따로 만들어지는 것!
- 책 뒤의 찾아보기는 정렬되어 있다
- 마찬가지로 mem_id를 가져올 때 정렬되어 가져온다
- 페이지번호 + 위치 번호
- 루트페이지는 하나여야하기 때문에 루트 페이지가 새로 생성된다
- UNIQUE 키를 설정한 순간 이 복잡한 일이 일어나는 것
👉🏻 인덱스에서 데이터 검색하기
🎮 'SPC'의 이름
- SELECT * FROM table WHERE mem_id = 'SPC'
✅ 클러스터형 인덱스
- 클러스터형 인덱스는 영어사전
- 사전 자체가 알파벳순이라 내용 자체도 인덱스라고 생각하기
- S는 M과 T 사이에 있으니까 M이 있는 페이지에서 찾을것 (한 페이지 읽음)
- 1001페이지에서 네 번째 (두 페이지 읽음)
✅ 보조 인덱스
- O 다음에 있을 것이니 O가 있는 페이지에서 읽기 (한 페이지 읽음)
- 200번 페이지에서 SPC 찾음 (두 페이지 읽음)
- 1002번 페이지 첫 번째에서 SPC 찾음 (세 페이지 읽음)
- 나오는 결과는 똑같아
* 클러스터형 인덱스가 더 효율적이다
- 극단적인 예라 그렇지 3000만 페이지가 될 수 있음
출처: https://youtu.be/vWTDuoSG-YQ?si=LXRvF0J0-42lLKMT
'MySQL > 혼공SQL' 카테고리의 다른 글
| 18강. 스토어드 프로시저(stored procedure)의 개념과 사용 방법(입출력 매개변수의 활용 방법) (1) | 2023.12.06 |
|---|---|
| 17강. 인덱스의 생성과 제거 문법(CREATE INDEX, DROP INDEX) (2) | 2023.12.06 |
| 15강. 인덱스의 개념과 장단점, 클러스터형 인덱스와 보조 인덱스 (0) | 2023.11.29 |
| 14강. 가상의 테이블: 뷰(생성, 수정, 삭제) (2) | 2023.11.27 |
| 13강. SQL 테이블 제약조건(기본키, 외래키, 고유키) (1) | 2023.11.24 |