들어가며
해당 강의를 보면서 공부한 내용을 정리하였습니다.
해당 글은 mysql을 기준으로 작성하였습니다.
인덱스 유무의 따른 성능 차이
인덱스가 걸려있지 않는 경우
- full sacn(= table scan)
- O(N)
인덱스가 걸려있는 경우
- full sacn보다 더 빠르게 찾을 수 있다.
- O(logN) (B-tree based index)
인덱스의 유무는 데이터베이스에서 데이터 검색 속도에 큰 영향을 미치며, 인덱스가 있는 경우 데이터를 빠르게 찾을 수 있습니다. 인덱스가 없는 경우에는 모든 데이터를 순차적으로 스캔하여 검색해야 하므로 시간이 오래 걸릴 수 있습니다.
Index 사용 이유
- 특정 조건을 만족하는 로우(들)을 빠르게 조회하기 위해
- 빠르게 정렬(order by)하거나 그룹핑(group by) 하기 위해
쿼리 예시
SELETE * FROM customers WHERE first_name='Minsoo';
DELETE FROM logs WHERE log_datetime < '2020-01-10';
UPDATE employee SET salary = salary * 1.5 WHERE dept_id = 1001;
SELETE * FROM employee E JOIN department D ON E.dept_id = D.id;
Index 거는 문법
테이블에 걸려있는 인덱스 파악하기
B트리 기반 인덱스 동작 방식
아래 내용은 실제로는 트리 구조로 저장되지만, 설명을 위해 테이블 형태로 표현됩니다.
WHERE a = 9;
index 테이블을 Full scan 하는 것이 아닌 이진 검색(Binary Search)이 이루어집니다.
인덱스 테이블의 가운데 값을 기준으로 탐색을 시작하며, 가운데 값보다 큰 경우에는 위쪽을, 작은 경우에는 아래쪽을 탐색하면서 a가 9인 값을 찾아갑니다.
만족하는 값을 찾았다면, 인덱스 테이블의 포인터(ptr) 값을 이용해 해당 멤버 테이블의 행에 접근합니다.
WHERE a = 7 AND b = 95;
a에 대한 index 테이블에서 Binary Search를 통해 a = 7인 row에 접근합니다.
그 후, ptr 값을 통해 members table에 접근한 뒤 b값도 만족하는 지 확인합니다.
만약, 조건을 만족하는 row가 여러개라면 a에 대한 index 테이블에서 a값을 만족하는 로우를 대상으로 다시 Binary Search가 이루어집니다.
현재 상황에서는 a = 7 조건에 대해서 인덱스 테이블을 통해 빠르게 찾을 수 있습니다.
하지만 b = 95 조건에 대해서는 인덱스 테이블의 ptr 값을 통해 멤버 테이블에 접근해 해당 행의 b 값과 조건의 b 값을 비교하고 조건을 만족하는 행만 선택합니다.
만약 a = 7 조건을 만족하는 행들이 수백개, 수천개가 존재하는 경우,모든 행들을 하나씩 확인하면서 조건을 모두 만족하는 행만을 선택해야 하기 때문에 성능 저하가 발생할 수 있습니다.
이러한 문제를 해결하기 위해서는 a,b를 하나로 묶은 Index가 필요합니다.
CREATE INDEX(a,b)
해당 인덱스는 먼저 a를 기준으로 정렬한 뒤, 그 안에서 b를 기준으로 정렬합니다.
따라서, 복합 인덱스에서는 인덱스 속성의 순서가 중요합니다.
a,b index 테이블에서 a = 7 AND b = 95 조건을 만족하는 row를 Binary Search를 통해 찾습니다.
만족하는 행을 찾으면 ptr 값을 이용해 멤버 테이블에 접근합니다.
그 후, 더 조건을 만족하는 행이 있는지 확인하기 위해 기준 행을 기반으로 다시 이진 검색을 수행합니다.
a=7, b=95 값을 가지는 행의 아래쪽은 a=9이므로 더 확인할 필요가 없습니다.
위쪽 또한 a=7이지만 b=80이므로 더 확인할 필요가 없습니다. a를 기준으로 정렬한 뒤, 그 안에서 b를 기준으로 정렬했기 때문입니다.
where b = 95;
a, b 인덱스 테이블에서 where b = 95; 조건을 찾을 때 성능이 나오지 않을 수 있습니다.
그 이유는 a, b 인덱스 테이블은 먼저 a를 기준으로 정렬한 뒤, 그 안에서 b를 기준으로 정렬했기 때문입니다.
따라서 where b = 95; 조건을 찾을 때는 a, b 인덱스가 사용되지 않거나 사용되더라도 성능 향상이 제한될 수 있습니다.
