인덱스
==========================================================
▶ 테이블에 대한 동작의 속도를 높이는 자료 구조
● 특정 열의 행을 빠르게 찾는데 사용된다.
◎SQL 서버가 더욱 좋은 성능을 내도록하는 데이터베이스 튜닝 중 하나이다.
▶ 장점
● 검색속도 상향 -> 쿼리 부하 감소 -> 시스템 전체 성능 향상
▶ 단점
● 자주 반복되는 컬럼에 사용시 풀 스캐닝보다 비효율적이다.
◎ 반복될 때마다 index를 조회하는 오버헤드 발생.
● DB 크기의 약 10% 정도의 추가 공간이 필요하다.
● DML등 데이터 변경 작업이 자주 일어나는 경우 성능이 오히려 떨어진다.
인덱스의 내부 작동
===========================================================
▶ B-tree 구조
● B-tree의 노드를 MYSQL에선 16kb 차지하는 메모리 공간 단위 페이지라고 한다.
◎ InnoDB(가장 많이 쓰이는 MySQL 저장엔진)에서는 디스크 I/O와 메모리 관리를 하려고 데이터를 “페이지”라는 단위로 묶어 서 다룬다고 한다.
- 기본적으로 InnoDB의 페이지 크기는 16KB로 고정되어 있습니다.
- 한 번 디스크에서 데이터를 읽거나 쓸 때, 최소 단위가 이 16KB 페이지 단위입니다. -출처 GPT-
● B-tree 구조를 사용하여 빠르게 SELECT문을 실행할 수 있다.
● CUD 작업시 페이지 분할이 이루어질 수 있으며, 다수의 페이지 분할이 이루어진다면 부담스러운 작업이 될 수 있다.
◎ CUD란? <DB에서 자주사용하는 쿼리 중 하나인 Create,Update,Delete를 얘기한다> 번외로 CRUD라는 단어도 있다.
▶ 그렇다면 해시테이블이 아닌 B-tree가 주로 사용이 되는걸까?
● 가장 와닿는 이유 중 하나는 해시테이블은 범위 검색이 불가능하지만, B-tree는 가능하기 때문에 인덱스 생성시 주로 B-tree를 사용하는 것이다.
● 해시테이블의 특성상 해시 테이블에 저장된 데이터들은 정렬되어 있지 않다. 따라서 범위검색이 불가능하지만
B-tree는 데이터가 모두 정렬되어 있기 때문에 연산자들 뿐만 아니라 Like까지 포함한 범위 검색에서 빠르게 작업을 수행할 수 있다.
◎ B⁺-Tree의 리프 노드들이 마치 링크드 리스트처럼 서로 포인터로 이어져 있어서, 키 값이 오름차순(또는 내림차순)으로 순차적으로 연결되어 있다고 보면 된다.
● 그렇다고 해시테이블이 안좋다는게 아니다. O(1)의 시간 복잡도를 가져 빠르게 처리하지만 equailty<비교>연산에서는 O(logN)의 시간 복잡도를 가지는 B-tree 인덱스보다 해시 인덱스가 효율적이다.
● 노드가 나눠지는 과정<데이터가 나눠지는 과정>
◎ InnoDB 같은 경우에는 B-tree 구조를 사용하는데 인덱스 같은 경우에는 페이지(노드)라는 단위가 존재한다.
해당 페이지는 16kb까지 저장이 가능해서 노드별로 데이터를 저장하며 여러 노드를 만들어나간다.
그렇게 만들어진 노드를 순차적으로 탐색하며 범위에 있는 값을 찾으면 해당 노드를 타서 탐색을 반복하면서 진행하여 내가 찾 는 값 까지 도달을 하게 된다.
인덱스의 종류
===========================================================
MySQL에서 사용되는 인덱스의 종류는 크게 클러스터<범위의 요소를 모은 단위체>형, 보조 인덱스로 나뉜다.
▶ 클러스터형 인덱스
● 영어사전과 유사
◎ 데이터를 열에 맞추어 오름차순으로 자동 정렬.
● 자동 생성
◎ 인덱스 지정 없을 시 기본키 기반으로 클러스터형 인덱스가 자동 생성
◎ UNIQUE + NOT NULL도 클러스터형 인덱스를 자동 생성 < 기본키가 없는 경우고,기본키가 존재한다면 보조 인덱스로 생성● 인덱스 자체의 리프 페이지가 곧 데이터다. 즉, 인덱스 자체에 데이터가 포함된다. <보조 인덱스보다 성능이 좋음
● 탐색비용은 다른 B-tree 인덱스와 동일
● 하나만 존재가 가능하다.
● 리프 노드<페이지> 자체가 실제 데이터 페이지이기 때문에, PK 단일 조회나 PK 범위 조회 시에는 I/O가 오히려 더 적게 발생해 읽기 성능이 좋다.
▶ 언클러스터형 인덱스
● 우편 배달과 유사(?)
◎ 노드에 키와 데이터 주소를 저장한다. 키를 통해 접근을 할때 해당 데이터 주소를 통해 접근 후 I/O를 진행한다.
우편 같은 경우에는 우체부님(키) 우편물을 전달받고 해당 주소에 전달을 해주시니 비슷한 느낌인 것 같다. <클러스터는 전자메 일 같은 느낌?>
◎ 클러스터드에 비해서 DML 을 사용시 부하가 적다. 왜냐면 언클러스터드 인덱스는 실제 데이터 이동 없이 노드 분할이나 부모 노드 갱신” 등의 작업이 발생.
◎ 또한 클러스터드 인덱스는 하나만 생성 가능하지만 언클러스터드 인덱스는 여러개 생성이 가능하다.
● 단점
◎ 클러스터드 인덱스는 물리 데이터 자체를 노드에 저장을해서 I/O가 빠르지만 언클러스터드 인덱스는 노드에 저장된 주소에 이 동 후 I/O를 시도해서 속도가 느린편이다.
◎ 인덱스를 많이 만들 수 있는 만큼 테이블이 많이 무거워진다. 각각의 인덱스별로 키 삽입/삭제/수정 시 인덱스 트리를 유지*관리 를 해야 하기 때문.<특히 자주 변경되는 컬럼에 인덱스를 걸면 오히려 성능이 저하될 수있다.>
결론
===========================================================
| 리프 노드(Leaf) | 실제 데이터 페이지 (행 전체 저장) | (키값, 데이터 페이지 주소orRowID) 페어 저장 |
| 데이터 물리 정렬 | 인덱스 키 순서대로 테이블 전체가 물리 정렬 | 데이터는 정렬되지 않음(Heap이나 다른 순서) |
| 단일 행 조회(Exact Seek) | 리프에서 곧바로 데이터 읽기 → I/O 1회 | 리프에서 포인터 얻고 → 테이블 본체로 I/O 2회 |
| 범위 조회(Range Scan) | 순차적 연속 스캔 → Sequential I/O 효율적 | 인덱스 순차 스캔 후 → 여러 번 랜덤 I/O 발생 가능성 |
| 쓰기 오버헤드(DML) | 데이터 삽입/UPDATE 시 물리적 순서 유지해야 하므로페이지 스플릿/단편화 관리 필요 → 오버헤드 큼 | 데이터 순서 유지 불필요, 인덱스만 수정 → 오버헤드 상대적으로 작음 |
| 생성 개수 제한 | 테이블당 1개만 생성 가능 | 여러 개 생성 가능 |
| 저장 공간 | 테이블 자체가 정렬되어야 하므로, 페이지 단편화 시 리빌드 필요 | 인덱스 자체가 별도 구조이므로, 필요 시 인덱스만 리빌드 가능 |
| 대표 사용 사례 | PK, 범위 조회가 많은 컬럼, 정렬이 빈번한 상황 | 자주 변경되는 컬럼, 다양한 단일 조회(Search Key), Covering Index 필요 시 |
============================================================================================
즉 Index는 많은 데이터를 가지고 있는 테이블에서 빠른 읽기를 진행하기 위한 자료구조이며 인덱스가 지정된 컬럼들은 최소한의 DML을 사용하는게좋다!
============================================================================================