8장 인덱스 (2)

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

인덱스 레인지 스캔

• 검색해야 할 인덱스의 범위가 결정됐을때 사용하는 방식, 일단 시작해야 할 위치를 찾으면 그때부터 리프노드의 레코드만 순서대로 읽으면 됨
• 인덱스 자체의 정렬 특성으로 인해 해당 인덱스를 구성하는 칼럼의 정순/역순으로 정렬된 상태로 레코드를 가져옴
• 레코드 한 건 한 건 단위로 랜덤 I/O가 일어남 ( 인덱스는 정렬 돼 있을지라도 해당 인덱스의 레코드 주소는 재각각일 것이기 때문)
• 인덱스를 통해 읽어야 할 레코드가 20~25%를 넘으면 테이블에서 직접 읽는게 효율적임

인덱스 레인지 스캔의 처리

1. 인덱스 탐색 : 인덱스에서 조건을 만족하는 값이 저장된 위치 탐색
2. 인덱스 스캔 : 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 읽음
3. 레코드 읽기 : 인덱스 키와 레코드 주소로 레코드가 저장된 페이지로 가서 레코드를 읽어옴 ( 커버링 인덱스에서는 필요 X )

 

인덱스 풀 스캔

인덱스의 처음부터 끝까지 모두 읽는 방식, 대표적으로 쿼리의 WHERE 절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닐경우이면서 커버링 인덱스일때 사용된다

리프노드를 따라 처음부터 끝까지 스캔하는 방식인데 절대 효율적인 인덱스 사용 방식이 아님

 

 

루스 인덱스 스캔

인덱스를 느슨하고 듬성듬성 읽는 방식. 중간에 필요없는 인덱스는 SKIP 하고 읽는데 GROUP BY / MAX / MIN 절에서 주로 사용됨

 

인덱스 스킵 스캔

인덱스의 핵심은 정렬되어있는것. WHERE 조건의 선행칼럼의 경우를 따져 쿼리를 구성해 스캔한다.

 

사용 조건

1. WHERE 조건절이 조건이 없는 선행 칼럼의 유니크한 값의 갯수가 적어야함
2. 커버링인덱스 ( 레코드 접근이 필요한 경우 테이블 풀스캔 한다.)

 

---

다중(복합) 칼럼 인덱스

두 개 이상의 칼럼으로 구성된 인덱스. 이 경우 인덱스의 N 번째 칼럼은 N-1 번째 칼럼에 의존적으로 정렬되어 있다. 

즉 인덱스를 구성시 칼럼의 순서가 중요함을 의미함

 

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

인덱스는 오름차순이나 내림차순으로 정렬되어있기 때문에 반대의 경우가 필요할 경우 거꾸로 읽어서 처리가 가능하다.

다중 칼럼 인덱스의 경우 각각의 칼럼의 정렬조건이 혼합된 경우엔 내림차순 인덱스로만 내림차순 인덱스로만 처리

 

InnoDB 에서 인덱스 역순 스캔이 정순 스캔보다 느린 이유

• 페이지 잠금이 인덱스 정순 스캔에 적합한 구조이기 때문
• 페이지 내의 인덱스 레코드가 단방향으로 연결되어있기 때문

 

---

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

비교 조건의 종류와 효율성

다중 칼럼 인덱스의 경우 ex) (A, B), B의 순서는 A의 순서에 종속적이기에 인덱스 순서에 따라 인덱스 효율성에 차이가난다

즉 A 에 대한 조건 없이 B 에 대한 검색은 인덱스를 효율적으로 사용할 수 없음

WHERE A LIKE '%xx' 같이 왼쪽 값이 가변적인 값의 경우 인덱스를 효율적으로 사용할 수 없다. 인덱스는 왼쪽 값을 기준으로 정렬되기 때문이다

 

인덱스를 효율적으로 사용할 수 없는 경우

• NOT_EQUAL 로 비교된 경우
• LIKE '%xx' 와 같이 뒷부분 일치일 경우
• 인덱스 칼럼이 변형된 후 비교에 사용된 경우
• 데이터 타입이 다를 경우
• 문자열 데이터 타입의 콜레이션이 다를 경우

 

다중 칼럼 인덱스에서 인덱스를 사용할 수 없는 경우 ( INDEX index_test(A,B,C ....) )

• A에 대한 조건이 없는 경우
• A의 비교 조건이 인덱스를 효율적으로 사용할 수 없는 경우일때

다중 칼럼 인덱스에서 인덱스를 사용하는 경우

• 동등 비교 ( = , IN 절)
• 대소비교 ( > , < )
• LIKE 좌측 일치 패턴 ( LIKE 'xx%' )

MySQL 에선 인덱스에 NULL 값을 허용한다. 즉 NULL 값도 작업 범위 결정 조건으로 사용된다.