데이터베이스 인덱스에 대해서 설명해주세요.

인덱스는 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조로 백과사전의 색인과 같습니다.

저장되는 컬럼의 값을 사용하여 항상 정렬된 상태를 유지하는 것이 특징입니다.

하지만, 이러한 특징으로 인해 인덱스는 INSERT,UPDATE,DELETE의 성능이 희생된다는 단점이 있습니다.

인덱스는 어떤 자료 구조로 이루어져있나요? 🤔

MySQL InnoDB를 기준으로는 B+Tree와 같은 변형 B-Tree 자료구조를 이용해서 인덱스를 구현합니다. B-Tree 인덱스는 컬럼의 값을 변형하지 않고 인덱스 구조체 내에서 항상 정렬된 상태로 유지합니다.

B-Tree(Balanced-Tree)에서는 크게 3가지 노드가 존재합니다. 최상위에 하나의 루트 노드가 존재하며, 가장 하위 노드인 리프 노드가 존재합니다.

이 두 노드의 중간에는 브랜치 노드가 존재합니다. 최하위 노드인 리프노드에는 실제 데이터 레코드를 찾아가기 위한 주소값을 가지고 있습니다.

InnoDB 스토리지 엔진에서는 세컨더리 인덱스(프라이머리 인덱스를 제외한 모든 인덱스)의 리프노드에는 레코드의 PK가 저장됩니다. 따라서 세컨더리 인덱스 검색에서는 레코드를 읽기 위해 PK를 가지고 있는 B-Tree를 다시 한번 검색해야합니다.

MySQL 스캔 방식은 어떤 게 있나요? 😀

MySQL에는 크게 인덱스 레인지 스캔, 인덱스 풀 스캔, 루스 인덱스 스캔 방식이 있습니다.

인덱스 레인지 스캔

인덱스 레인지 스캔은 검색할 인덱스 범위가 결정되었을 경우 사용하며 가장 빠릅니다.

• 인덱스에서 조건을 만족하는 값이 저장된 시작 리프 노드를 찾습니다.(index seek)
• 시작 리프 노드부터 필요한 만큼 인덱스를 차례대로 읽습니다. (index scan)
• 인덱스 키와 레코드 주소를 이용해 저장된 페이지를 가져오고 레코드를 읽어옵니다.

레코드를 읽어오는 과정에서 랜덤 IO가 발생할 수 있습니다. 읽어야할 데이터 레코드가 전체 20-25%의 경우에는 풀 테이블 스캔(순차 IO를 이용)이 더욱 좋을 수 있습니다.

먼저 루트 노드에서 시작해서 브랜치 노드를 거쳐 원하는 인덱스가 저장된 리프 노드(페이지8)로 이동합니다.

읽고 쓰는 단위는 페이지이므로 원하는 인덱스를 찾기 위해서는 8번 페이지의 처음부터 시작해서 시작점(Lemon)을 찾는데, 이를 탐색이라고 합니다.

시작점을 찾았으면 이후에는 순서대로 검색 범위의 마지막 인덱스(Mango)까지 읽으면 되는데, 이를 스캔이라고 합니다. 인덱스가 정렬이 되어 있기 때문에 스캔을 하다가 페이지의 끝에 도달하면 페이지 간의 링크를 이용해 다음 페이지(9)로 넘어갑니다.

만약 쿼리가 인덱스나 PK외 레코드의 다른 값을 필요로 하면 테이블로부터 레코드를 조회해야 하며, 이때는 PK를 이용해 랜덤 I/O를 통해 레코드들을 조회해야 합니다. 만약 인덱스나 PK만 필요로 한다면 해당 작업은 실행되지 않는데, 이를 커버링 인덱스라고 합니다. 커버링 인덱스는 디스크 랜덤 I/O 작업을 줄일 수 있어서 성능이 훨씬 뛰어납니다.

인덱스 풀 스캔

인덱스 풀 스캔은 인덱스를 사용하지만 인덱스를 처음부터 끝까지 모두 읽는 방식입니다.

• 인덱스를 ABC 순서로 만들었는데 조건절에 B 혹은 C로 검색하는 경우 사용됩니다.
• 인덱스를 생성하는 목적은 아니지만, 그래도 풀 테이블 스캔보다는 낫습니다. (데이터 레코드까지 읽지 않는 경우)

인덱스 풀 스캔이 사용되는 이유는 테이블에는 다른 레코드들도 포함되어 있으니 비효율적이기 때문입니다.

해당 방식이 아주 빠르지는 않지만 테이블 풀 스캔보다는 적은 디스크 I/O로 쿼리를 처리할 수 있습니다. 그렇다고 하여 인덱스 풀 스캔을 위해서 인덱스를 생성해서는 안되며, 인덱스 풀 스캔은 일반적으로 “인덱스를 사용한다”고 하지 않습니다.

루트 인덱스 스캔

루스 인덱스 스캔은 듬성듬성하게 인덱스를 읽는 것을 의미합니다. (앞서 언급한 인덱스 레인지, 인덱스 풀 스캔은 타이트 인덱스 스캔으로 분류됩니다.)

• 중간에 필요하지 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리합니다.
• group by, max(), min() 함수에 대해 최적화하는 경우에 사용됩니다.

SELECT 
    dept_no, MIN(emp_no)
FROM
    dept_emp 
WHERE
    dept_no BETWEEN 'D002' AND 'D004'
GROUP BY 
    dept_no;

위의 쿼리에서 emp_no는 dept_no에 의존하여 정렬되므로, dept_no 그룹 별로 가장 처음의 emp_no 값만 읽으면 된다. 옵티마이저는 이러한 부분을 알고 있기 때문에, 처음이 아닌 emp_no는 무시하고 넘어간다.

이러한 부분은 인덱스의 정렬 특성 때문이므로, 인덱스를 구성하는 컬럼의 순서는 상당히 중요합니다. 앞선 인덱스 레인지 스캔과 인덱스 풀 스캔은 모든 인덱스를 스캔하므로 타이트 인덱스 스캔으로 묶이는데, 이와는 반대되는 스캔 방식입니다.