B-Tree 인덱스

• 데이터베이스의 인덱싱 알고리즘 가운데 가장 일반적으로 사용되고, 가장 먼저 도입된 알고리즘
• 아직도 가장 범용적인 목적으로 사용되는 인덱스 알고리즘
  • 전문 검색과 같은 특수한 요건이 아닌 경우, 대부분 인덱스는 거의 B-Tree를 사용한다.
• “B”는 Balanced를 의미한다.
• 칼럼의 원래 값을 변형시키지 않고 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다.
• 여러 가지 변형된 형태의 알고리즘이 있다.
  • B+-Tree
  • B*-Tree

구조 및 특성

구조

• 트리 구조의 최상위에 하나의 루트 노드(Root Node)가 존재하고, 그 하위에 자식 노드가 붙어 있는 형태다.
  • 트리 구조의 가장 하위에 있는 노드: 리프 노드 (Leaf Node)
  • 트리 구조에서 루트 노드도 리프 노드도 아닌 중간 노드: 브랜치 노드 (Branch Node)
• 인덱스는 테이블의 키 칼럼만 가지고 있다.
  • 나머지 칼럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 한다.
• 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.
  • MyISAM: 레코드가 테이블에 INSERT된 순번이나 데이터 파일 내의 위치(Offset)인 레코드 주소
  • InnoDB: 프라이머리 키

특성

• 인덱스의 키 값은 모두 정렬되어 있다.
• 데이터 파일의 레코드(실제 데이터 레코드)는 임의의 순서로 저장되어 있다.
  • 정렬되어 있지 않다.
  • 항상 INSERT된 순서대로 저장되는 것은 아니다.
• InnoDB 테이블에서 레코드는 클러스터되어 디스크에 저장되므로, 기본적으로 프라이머리 키 순서로 정렬되어 저장된다.
• MyISAM 스토리지 엔진은 인덱스를 통해 데이터 파일을 바로 찾아간다.
• InnoDB 스토리지 엔진은 인덱스에 저장되어 있는 프라이머리 키 값을 이용해 프라이머리 키 인덱스를 한 번 더 검색한 후, 프라이머리 키 인덱스의 리프 페이지에 저장되어 있는 레코드를 읽는다.
  • 즉, 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 프라이머리 키를 저장하고 있는 B-Tree를 다시 한 번 검색해야 한다.

B-Tree 인덱스 키 추가 및 삭제

• 테이블의 레코드를 저장하거나 변경하는 경우 인덱스 키 추가나 삭제 작업이 발생한다.

인덱스 키 추가

• 새로운 키 값이 B-Tree에 저장될 때 스토리지 엔진에 따라 달라진다.
  • MyISAM이나 MEMORY 스토리지 엔진: 즉시 새로운 키 값을 B-Tree 인덱스에 변경
  • InnoDB 스토리지 엔진: 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있다.
  • 프라이머리 키나 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree에 추가하거나 삭제한다.
• 상대적으로 새로운 키를 추가하는 작업에 비용이 많이 든다.
  • 저장될 키 값을 이용해 B-Tree 상의 적절한 위치 검색
  • 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장
  • 리프 노드가 꽉 차서 더는 저장할 수 없을 경우 리프 노드가 분리(Split)되어, 상위 브랜치 노드까지 처리의 범위가 넓어진다.
• 인덱스를 추가하는 비용 계산
  • 테이블에 레코드를 추가하는 작업 비용을 1이라고 가정
  • 해당 테이블의 인덱스에 키를 추가하는 작업 비용을 1.5로 예측
  • 일반적으로 테이블에 인덱스가 3개 있다면 $1.5*3+1$ 정도의 비용으로 예측
  • 테이블에 인덱스가 하나도 없다면 $1$ 의 작업 비용
  • 이 비용의 대부분은 디스크로부터 인덱스 페이지를 읽고 쓰기를 해야 해서 걸리는 시간이다.

인덱스 키 삭제

• 해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 삭제 마크한다.
• 삭제 마킹된 인덱스 키 공간은 계속 방치하거나 재활용할 수 있다.
• 이 작업 역시 디스크 쓰기가 필요하므로, 디스크 I/O가 필요하다.
• MySQL 5.5 이상 버전의 InnoDB 스토리지 엔진에서는 이 작업 또한 버퍼링되어 지연 처리될 수도 있다.
  • 사용자에게 특별한 악영향 없이 MySQL 서버가 내부적으로 처리
• MyISAM이나 MEMORY 스토리지 엔진의 테이블에서는 인덱스 키 삭제가 완료된 후 쿼리 실행이 완료된다.
  • 체인지 버퍼와 같은 기능이 없기 때문이다.

인덱스 키 변경

• 단순히 인덱스상의 키 값만 변경하는 것은 불가능하다.
  • 인덱스의 키 값은 그 값에 따라 저장될 리프 노드의 위치가 결정되기 때문이다.
• 먼저 기존 인덱스 키 값을 삭제한 후, 다시 새로운 인덱스 키 값을 추가한다.
• InnoDB 스토리지 엔진의 테이블에 대해서는 이 작업 모두 체인지 버퍼를 활용해 지연 처리될 수 있다.

인덱스 키 검색

• INSERT, UPDATE, DELETE 작업을 할 때 인덱스 관리에 따르는 추가 비용을 감당하면서 인덱스를 구축하는 이유는 빠른 검색을 위해서다.
• 인덱스를 검색하는 작업은 B-Tree의 루트 노드 → 브랜치 노드 → 리프 노드까지 이동하면서 비교 작업을 수행한다.
  • 이 과정을 트리 탐색이라고 한다.
  • 인덱스 트리 탐색은 SELECT뿐만 아니라 UPDATE나 DELETE를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우에도 사용된다.
• 100% 일치 또는 값의 앞부분(Left-most part)만 일치하는 경우에 사용할 수 있다.
• 부등호(<, >) 비교 조건에서도 인덱스를 활용할 수 있다.
• 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없다.
• 인덱스의 키 값에 변형이 가해진 후 비교되는 경우 절대 B-Tree의 빠른 검색 기능을 사용할 수 없다.
  • 이미 변형된 값은 B-Tree 인덱스에 존재하는 값이 아니다.
  • 예) 함수나 연산을 수행한 결과로 정렬하거나 검색하는 작업
• InnoDB 스토리지 엔진에서는 인덱스의 설계가 중요하고 많은 부분에 영향을 미친다.
  • InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어 있다.
  • UPDATE나 DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다.
  • 테이블의 모든 레코드를 잠글 수도 있다.

B-Tree 인덱스 사용에 영향을 미치는 요소

• B-Tree 인덱스는 다음 요소 등에 의해 검색이나 변경 작업의 성능이 영향을 받는다.
  • 인덱스를 구성하는 칼럼의 크기와 레코드의 건수
  • 유니크한 인덱스 키 값의 개수

인덱스 키 값의 크기

• InoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 또는 블록(Block)이라고 한다.
  • 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위
  • 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 하다.
• 인덱스도 페이지 단위로 관리된다.
  • 루트와 브랜치, 리프 노드를 구분한 기준이 페이지 단위다.
• 일반적으로 DBMS의 B-Tree는 자식 노드의 개수가 가변적인 구조다.
  • MySQL의 B-Tree는 자식 노드의 개수가 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다.
  • MySQL 5.7 버전부터는 innodb_page_size 시스템 변수를 이용해 4KB~64KB 사이의 값으로 InnoDB 스토리지 엔진의 페이지 크기를 선택할 수 있다. (기본 크기: 16KB)
• 인덱스 키 값이 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다.
• 인덱스 키 값의 길이가 길어지면 전체적인 인덱스의 크기가 커진다.
  • 인덱스를 캐시해 두는 InnoDB의 버퍼 풀이나 MyISAM의 키 캐시 영역은 크기가 제한적이기 때문에, 하나의 레코드를 위한 인덱스 크기가 커지면 커질수록 메모리에 캐시해 둘 수 있는 레코드의 수는 줄어든다.
  • 자연히 메모리의 효율이 떨어진다.

B-Tree 깊이

• B-Tree 인덱스의 깊이(Depth)는 상당히 중요하지만 직접 제어할 방법은 없다.
• 깊이는 MySQL에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결된 문제다.
• 인덱스 키 값의 크기는 가능하면 작게 만드는 것이 좋다.
• 인덱스 키 값의 크기가 커지면 커질수록 다음 현상이 일어난다.
  • 하나의 인덱스 페이지가 담을 수 있는 키 값의 개수가 적어진다.
  • 같은 레코드 건수라 하더라도 B-Tree의 깊이가 깊어져서 디스크 읽기가 더 많이 필요하다.
• 아무리 대용량 데이터베이스라도 깊이가 5단계 이상까지 깊어지는 경우는 흔치 않다.

선택도(기수성)

• 인덱스에서 선택도(Selectivity) 또는 기수성(Cardinality)은 거의 같은 의미로 사용된다.
• 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다.
• 인덱스 키 값 가운데 중복된 값이 많을수록 기수성은 낮아지고 선택도 또한 떨어진다.
• 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.

읽어야 하는 레코드의 건수

• 인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 읽는 것보다 높은 비용이 드는 작업이다.
• 인덱스를 이용한 손익 분기점이 얼마인지 판단할 필요가 있다.
  • 일반적인 DMBS의 옵티마이저에서는 인덱스를 통하는 것이 직접 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업으로 예측한다.
  • 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면, 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리하는 것이 효율적이다.
  • 인덱스를 통해 읽어야 할 레코드의 건수는 옵티마이저가 판단한 예상 건수이다.
• 많은 레코드(전체 레코드의 20~25% 이상)를 읽을 때는 강제로 인덱스를 사용하도록 힌트를 추가해도 성능상 얻을 수 있는 이점이 없다.

B-Tree 인덱스를 통한 데이터 읽기

MySQL이 인덱스를 이용하는 대표적인 방법 세 가지

• 인덱스 레인지 스캔
• 인덱스 풀 스캔
• 루스 인덱스 스캔
• 인덱스 스킵 스캔

인덱스 레인지 스캔

• 가장 대표적인 인덱스의 접근 방법
• (이번 장에서는) 인덱스를 통해 레코드를 한 건만 읽는 경우와 한 건 이상을 읽는 경우를 모두 묶어 표현했다.
  • 원래 두 경우는 각각 다른 이름으로 구분한다.
• 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다.
  • 검색하려는 값의 수나 검색 결과 레코드 건수와 관계없이 레인지 스캔이라고 표현
• 인덱스 레인지 스캔의 과정
  1. 인덱스에서 조건을 만족하는 값이 지정된 위치를 찾는다. → 인덱스 탐색 (Index seek)
     • 루트 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어가야만 비로소 필요한 레코드의 시작 지점을 찾을 수 있다.
  2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. → 인덱스 스캔 (Index scan)
     • 필요한 방향(오름차순 또는 내림차순)으로 인덱스를 읽는다.
  3. 2번에서 읽어들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.
     • 레코드 한 건 단위로 랜덤 I/O가 한 번씩 일어난다.
     • 쿼리가 필요로 하는 데이터에 따라 이 과정은 필요하지 않을 수 있다. → 커버링 인덱스
     • 커버링 인덱스로 처리되는 쿼리를 디스크의 레코드를 읽지 않아도 되기 때문에 랜덤 읽기가 상당히 줄어들고 성능은 그만큼 빨라진다.
• 인덱스를 통해 데이터 레코드를 읽는 작업은 비용이 많은 드는 작업이다.
• 인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 더 효율적이다.

-- 1번 단계와 2번 단계의 작업이 얼마나 수행할 수 있는지 확인
SHOW STATUS LIKE 'Handler_%';

-- -------------------------------
-- Handler_read_key: 1번 단계가 실행된 횟수
-- Handler_read_next: 2번 단계에서 정순으로 읽은 레코드 건수
-- Handler_read_prev: 2번 단계에서 역순으로 읽은 레코드 건수
-- Handler_read_first: 인덱스 첫 번째 레코드를 읽은 횟수
-- Handler_read_last: 인덱스 마지막 레코드를 읽은 횟수

인덱스 풀 스캔

• 인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식이다.
  • 예) 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우
  • 일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로, 직접 테이블을 처음부터 끝까지 읽는 것보다 인덱스만 읽는 것이 효율적이다.
• 주로 쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우 사용된다.
  • 데이터 레코드까지 모두 읽어야 한다면 절대 이 방식으로 처리되지 않는다.
• 인덱스 풀 스캔의 처리 방식
  1. 인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동
  2. 인덱스 리프 노드를 연결하는 링크드 리스트(Linked list)를 따라서 처음부터 끝까지 스캔
• 인덱스 레인지 스캔보다는 빠르지 않지만, 테이블 풀 스캔보다는 적은 디스크 I/O로 쿼리를 처리할 수 있어 효율적이다.

루스(Loose) 인덱스 스캔

• 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다.
• 오라클과 같은 DBMS의 “인덱스 스킵 스캔” 기능과 작동 방식이 비슷하지만, MySQL에서는 이를 “루스 인덱스 스캔”이라고 한다.
  • MySQL 5.7 버전까지는 많이 제한적이었다.
  • MySQL 8.0 버전부터 다른 상용 DBMS에서 지원하는 인덱스 스킵 스캔과 같은 최적화를 조금씩 제공하기 시작했다.
• 인덱스 레인지 스캔과 인덱스 풀 스캔은 루스 인덱스 스캔과 상반된 의미에서 타이트(Tight) 인덱스 스캔으로 분류한다.
• 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시(SKIP)하고 다음으로 넘어가는 형태로 처리한다.
  • 일반적으로 GROUP BY 또는 MAX() , MIN() 함수에 대해 최적화를 하는 경우 사용된다.

인덱스 스킵 스캔

• 옵티마이저가 첫 번째 칼럼을 건너뛰어서 두 번째 칼럼만으로 인덱스 검색이 가능하게 해주는 최적화 기능이다.
  • 인덱스를 구성하는 칼럼의 순서가 (A, B)로 걸려있을 때 WHERE 조건절에는 두 칼럼에 대한 비교 조건이 필수였다.
  • 인덱스 스킵 스캔을 비활성화하고 WHERE 조건절에 B만 사용했을 때, 인덱스를 효율적으로 이용할 수 없다. (인덱스 풀 스캔을 했다는 의미)
• MySQL 8.0 버전에 도입됐다.
  • 비슷한 최적화를 수행하는 루스 인덱스 스캔은 GROUP BY 작업을 처리하기 위해 인덱스를 사용하는 경우에만 적용할 수 있었다.
  • 인덱스 스킵 스캔은 WHERE 조건절의 검색을 위해 사용 가능하도록 용도가 훨씬 넓어졌다.
• MySQL 옵티마이저는 조건이 없는 인덱스의 선행 칼럼의 유니크한 값을 모두 조회해서 주어진 쿼리에 해당 칼럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리한다.
  1. 예를 들어 gender 칼럼(M, F)과 birthday 칼럼이 인덱스이고 gender 칼럼이 WHERE 절에 없다면
  2. gender의 유니크한 값인 M과 F를 조회 후
  3. 주어진 쿼리에 gender='M' 과 gender='F' 를 각각 추가하여 실행한다.
• 다음과 같은 단점이 있다.
  • WHERE 조건절에 조건이 없은 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 함
  • 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야 함 (커버링 인덱스)

다중 칼럼(Multi-Column) 인덱스

• 실제 서비스용 데이터베이스에서는 2개 이상의 칼럼을 포함하는 인덱스가 더 많이 사용된다.
• 두 개 이상의 칼럼으로 구성된 인덱스를 다중 칼럼 인덱스 또는 복합 칼럼 인덱스라고 한다.
  • 2개 이상의 칼럼이 연결되었다고 해서 Concatenated Index라고도 한다.
• 인덱스의 N번째 키 값은 N-1 번째 키 값에 대해서 정렬돼 있다.
  • 두 번째 칼럼의 정렬은 첫 번째 칼럼이 똑같은 레코드에서만 의미가 있다.
  • 만약 칼럼이 4개인 인덱스라면, 세 번째 칼럼은 두 번째 칼럼에 의존해서 정렬되고 네 번째 칼럼은 세 번째 칼럼에 의존해서 정렬된다.
• 다중 칼럼 인덱스에서는 인덱스 내에서 각 칼럼의 위치(순서)가 상당히 중요하며, 그것을 아주 신중히 결정해야 한다.

B-Tree 인덱스의 정렬 및 스캔 방향

• 인덱스를 생성할 때 설정한 정렬 규칙에 따라서 인덱스의 키 값은 항상 오름차순이거나 내림차순으로 정렬되어 저장된다.
• 인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정된다.

인덱스의 정렬

• 일반적인 상용 DBMS에서는 인덱스를 생성하는 시점에 인덱스를 구성하는 각 컬럼의 정렬을 오름차순 또는 내림차순으로 설정할 수 있다.
  • MySQL 5.7 버전까지는 정렬 순서를 혼합해서 인덱스를 생성할 수 없었다.
  • MySQL 8.0 버전부터는 정렬 순서를 혼합한 인덱스도 생성할 수 있다.
  • ex) CREATE INDEX ix_test ON test (column1 ASC, coulmn2 DESC)

인덱스의 스캔 방향

• 인덱스 생성 시점에 정렬이 결정되지만, 쿼리가 그 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다.
  • MySQL 옵티마이저는 이를 알고 있다.
  • 오름차순으로 생성된 인덱스를 정순으로 읽음 → 레코드는 자동으로 오름차순으로 정렬된 결과
  • 오름차순으로 생성된 인덱스를 역순으로 읽음 → 레코드는 자동으로 내림차순으로 정렬된 결과
• MySQL 옵티마이저는 최적화가 필요한 경우에도 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어 낸다.
  • ORDER BY 처리나 MIN(), MAX() 함수 등의 최적화가 필요한 경우
• 2개 이상의 칼럼으로 구성된 복합 인덱스에서 각각의 칼럼이 내림차순과 오름차순이 혼합된 경우, MySQL 8.0의 내림차순 인덱스로만 해결될 수 있다.

인덱스의 정렬과 용어

내용의 이해도를 높이기 위해 간단히 용어를 정리했다.

• 오름차순 인덱스(Ascending index)
  • 작은 값의 인덱스 키가 B-Tree 왼쪽으로 정렬된 인덱스
• 내림차순 인덱스(Descending index)
  • 큰 값의 인덱스 키가 B-Tree 왼쪽으로 정렬된 인덱스
• 인덱스 정순 스캔 (Forward index scan)
  • 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드의 왼쪽 페이지부터 오른쪽으로 스캔
• 인덱스 역순 스캔 (Backward index scan)
  • 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드의 오른쪽 페이지부터 왼쪽으로 스캔

인덱스의 정렬을 선택하는 기준

• InnoDB 스토리지 엔진에서 인덱스 역순 스캔은 인덱스 정순 스캔에 비해 느리다.
  • 페이지 잠금이 인덱스 정순 스캔에 적합한 구조다.
  • 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조다.
• 인덱스를 ORDER BY ... DESC 하는 쿼리가 소량의 레코드에 드물게 실행되는 경우라면, 다음 두 가지 모두 적절한 선택이 될 수 있다.
  • 오름차순 인덱스: INDEX (a ASC, b ASC)
  • 내림차순 인덱스: INDEX (a DESC, b DESC)
• 인덱스를 ORDER BY ... DESC 하는 쿼리가 많은 레코드를 조회하면서 빈번하게 실행될 경우
  • 내림차순 인덱스가 더 효율적
• 많은 쿼리가 인덱스의 앞쪽 혹은 뒤쪽만 집중적으로 읽어서 인덱스의 특정 페이지 잠금이 병목이 될 것으로 예상될 경우
  • 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 완화하는데 도움이 될 것이다.

B-Tree 인덱스의 가용성과 효율성

비교 조건의 종류와 효율성

• 다중 칼럼 인덱스에서 각 칼럼의 순서와 그 칼럼에 사용된 조건이 동등 비교(=)인지 범위 조건(>, <)인지에 따라 각 인덱스 칼럼의 활용 형태와 효율이 달라진다.

-- 두 가지 케이스의 인덱스
-- (1) INDEX (dept_no, emp_no)
-- (2) INDEX (emp_no, dept_no)

-- 다음 쿼리를 실행했을 경우
SELECT * FROM dept_emp
WHERE dept_no='d002' AND emp_no >= 10114;

• (1)번은 dept_no='d002' AND emp_no>=10114인 레코드를 찾고, dept_no가 'd002'가 아닐 때까지 인덱스를 쭉 읽는다
  • 두 번째 칼럼인 emp_no는 비교 작업의 범위를 좁히는 데 도움을 준다.
• (2)번은 emp_no>=10144 AND dept_no='d002'인 레코드를 찾고, 모든 레코드에 대해 dept_no가 'd002'인지 비교한다.
  • 최종적으로 dept_no='d002' 조건을 필터링하는 레코드를 가져온다.
  • 필터링: 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하면서 취사선택하는 작업
  • 두 번째 칼럼인 dept_no는 비교 작업의 범위를 좁히는데 아무런 도움을 주지 못하고, 단지 쿼리의 조건에 맞는지 검사하는 용도로만 사용되었다.
• 작업 범위 결정 조건
  • 작업의 범위를 결정하는 조건
  • 예) dept_no='d002' 와 emp_no >= 10144
• 필터링 조건 (또는 체크 조건)
  • 비교 작업의 범위를 줄이지 못하고 단순히 거름종이 역할만 하는 조건
  • 예) dept_no='d002'
• 작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높인다.
• 체크 조건은 많으면 오히려 쿼리 실행을 더 느리게 만들 때가 많다.

인덱스의 가용성

• B-Tree 인덱스는 왼쪽 값에 기준해서(Left-most) 오른쪽 값이 정렬되어 있다는 특징이 있다.
  • 여기서 왼쪽이란 다중 칼럼 인덱스의 칼럼에 대해서도 함께 적용된다.
• 하나의 칼럼에서도, 다중 칼럼 인덱스에서도 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔을 사용할 수 없다.
  • 안 좋은 예1) WHERE column Like '%keyword'
  • 안 좋은 예2) 인덱스가 (dept_no, emp_no) 순서일 때 선행 칼럼(dept_no) 조건 없이 검색할 경우. WHERE empt_no>=10144
• 인덱스의 왼쪽 값 규칙은 GROUP BY나 ORDER BY 절에도 똑같이 적용된다.

가용성과 효율성 판단

기본적으로 B-Tree 인덱스의 특성상 다음 조건에서는 작업 범위 결정 조건으로 사용할 수 없다.

경우에 따라 체크 조건으로 인덱스를 사용할 수는 있다.

• NOT-EQUAL로 비교된 경우
  • <>
  • NOT IN
  • NOT BETWEEN
  • IS NOT NULL
• LIKE ‘%??’ 형태(뒷부분 일치)로 문자열 패턴 비교
• 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
  • ex) WHERE SUBSTRING(column, 1, 1) = 'X'
• NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
• 데이터 타입이 서로 다른 비교
  • 인덱스 칼럼의 타입을 변환해야 비교가 가능한 경우
• 문자열 데이터 타입의 콜레이션이 다른 경우

일반적인 DBMS와는 다르게 MySQL에서는 NULL 값도 인덱스에 저장된다.

• ...WHERE column IS NULL ... 조건도 작업 범위 결정 조건으로 인덱스를 사용한다.

다중 칼럼으로 만들어진 인덱스의 경우

• 작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
  • column_1 칼럼에 대한 조건이 없는 경우
  • column_1 칼럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우
• 작업 범위 결정 조건으로 인덱스를 사용하는 경우 (i는 2보다 크고 n보다 작은 임의의 값을 의미)
  • column1 ~ coulmn(i-1) 칼럼까지 동등 비교 형태(= 또는 IN)로 비교
  • column_i 칼럼에 대해 동등 비교(= 또는 IN), 크기 비교(> 또는 < ), LIKE 좌측 일치 패턴 중 하나로 비교

Reference

참고 서적

📔 Real MySQL 8.0