Study/Real-MySQL

8.3 B-Tree 인덱스(2)

hongeeii 2023. 12. 26. 10:29
728x90
반응형

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

스토리지 엔진이 어떻게 인덱스를 이용해서 실제 레코드를 읽어내는지 알아야한다.

인덱스 레인지스캔

인덱스 레인지 스캔은 인덱스의 접근 방법 가운데 가장 대표적인 접근 방식이다.
인덱스를 통해 레코드를 한 건만 읽는 경우(index unique scan)와 한 건 이상을 읽는 경우(index range scan)를 각각 다른 이름으로 구분하지만
지금은 모두 묶어서 인덱스 레인지 스캔이라고 표현한다.

'''
SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';
'''
image

위와 같은 쿼리에서 루트노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어가야만
필요한 레코드 시작 지점을 알 수 있다.
일단 시작해야 할 위치를 찾으면 리프노드의 레코드만 순서대로 읽으면 된다.
그리고 최종적으로 스캔을 멈춰야 할 위치에 다다르면 지금까지 읽은 레코드를 사용자에게 반환하고 쿼리를 끝낸다.

위는 실제 인덱스 만을 읽는 경우고, 실제 데이터 파일의 레코드를 읽어와야 하는 경우를 살펴보자.

image
B-Tree 인덱스에서 스캔 시작위치에서부터 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어온다.
이때 레코드 한 건 한 건 단위로 랜덤 I/O가 일어난다.

  • 인덱스 레인지 스캔 단계
  1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. (인덱스 탐색)
  2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 읽는다. (인덱스 스캔)
  3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽어온다.
커버링 인덱스

커버링 인덱스는 쿼리를 충족시키는데 필요한 모든 데이터를 갖고 있는 인덱스를 말한다.
커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지않아도 되기 때문에 랜덤 읽기가 상당히 줄어들다.

인덱스 풀 스캔

image
인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식을 말한다.
대표적으로 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 칼러럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다.
예를 들면, 인덱스는 (A, B, C)칼럼의 순서로 만들어져 있지만 쿼리의 조건절은 B칼럼이나 C칼럼으로 시작되는 경우다.
이 방식은 인덱스 레인지 스캔보다는 빠르진 않지만 테이블 풀 스캔보다는 효율적이다.
커버링 인덱스일 경우, 테이블 전체를 읽는 것보다는 적은 I/O로 쿼리를 처리할 수 있다.

루스(Loose) 인덱스 스캔

Oracle DBMS의 '인덱스 스킵 스캔'이라는 기능과 작동방식은 비슷하지만 MYSQL에선 루스 인덱스 스캔이라고 부른다.
말 그대로 느슨하게, 듬성듬성하게 인덱스를 읽는 것을 의미한다. 

SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dep_no BETWEEN 'd002' AND 'd004'
GROUP BY dept_no;

image

이 쿼리에서 사용된 dept_emp 테이블은 (dept_no, emp_no)로 인덱스가 생성되어 있다.(정렬이 되어있다)
dept_no 그룹별로 첫 번째 레코드의 emp_no 값만 읽으면 된다.
즉 인덱스에서 WHERE 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저가 알고 있기 떄문에 조건에 만족하지 않는 레코드는 무시된다.

인덱스 스킵 스캔

데이터베이스 서버에서 인덱스의 핵심은 값이 정렬되어 있다는 것이며 인덱스를 구성하는 칼람의 순서가 매우 중요하다. 

ALTER TABLE employees
ADD INDEX ix_gender_birthdate (gender, birth_date);

이 인덱스를 사용하려면 WHERE 조건절에 gender 칼럼에 대한 비교 조건이 필수다. 

// 인덱스를 사용하지 못하는 쿼리
SELECT * FROM employees WHERE birth_date >= '1995-08-14';

// 인덱스를 사용할 수 있는 쿼리
SELECT * FROM employees WHERE gender = 'M' AND birth_date >= '1995-08-14';

gender 칼람과 birth_Date컬럼의 조건을 모두 가진 두번째 쿼리는 인덱스를 효율적으로 사용할 수 있지만,
gender 칼람에 대한 비교 조건이 없는 첫번째 쿼리는 인덱스를 사용할 수 없다.
주로 이런 경우는 birth_date 칼럼부터 시작하는 인덱스를 새로 생성해야한다.
MYSQL 8.0버전부터는 옵티마이저가 gender 칼럼을 건너뛰어서 birth_date 칼럼만으로 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능이 도입됐다.
루스 인덱스 스캔은 GROUP BY 작업을 처리하기 위해 인덱스를 사용하는 경우에만 적용된다.
인덱스 스킨 스캡은 WHERE 조건절에 검색을 위해 사용가능하도록 용도가 훨씬 넓어졌다.

image

skip_scan을 off 시키고, 위 쿼리의 실행계획을 보면 type이 index인 것을 볼 수 있다.
type 칼람이 index라고 표시된 것은 인덱스를 처음부터 끝까지 모두 읽었다(풀 인덱스 스캔)의 의미이다.
만약 예제 쿼리가 employees 테이블의 모든 칼럼을 가져와야 했다면 테이블 풀 스캔을 실행했을 것이다.
image

skip_scan을 on 시키고 다시 한번 실행 계획을 확인해 보자.
image

이번엔 쿼리의 실행 계획에서 type 칼럼의 값이 range로 표시됐는데, 인덱스에서 꼭 필요한 부분만 읽었다는 뜻이다.
또한 Extra칼럼에 Using index for skip 이라는 문구가 표기 됐는데, 이는 ix_gender_birthdate 인덱스에 대해 인덱스 스킵 스캔을 활용해 데이터를 조회했다는 것을 의미한다.
MYSQL 옵티마이저는 gender 칼럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리한다.
gender 칼럼은 'M'과 'F'값만 가지는 ENUM 타입의 칼럼이다.
따라서 gender 칼럼에 대해 가능한 값 2개(M과 F)를 구한다음, 내부적으로 옵티마이저는 2개의 쿼리를 실행하는 것과 비슷한 형태의 최적화를 실행하게 된다. 

SELECT gender, birth_date FROM employees WHERE gender='W' AND birth_date >= '1965-02-01';
SELECT gender, birth_date FROM employees WHERE gender='M' AND birth_date >= '1965-02-01';

칼럼이 어떤 타입이더라도 MYSQL은 인덱스를 루스 인덱스 스캔과 동일한 방식으로 읽으면서 인덱스에 존재하는 모든 값을 먼저 추출하고 그 결과를 이용해 인덱스 스킵 스캔을 실행한다.

인덱스 스킵 스캔은 8.0버전에 새로 도입된 기능이라 아직 제한이 있다.

  • WHERE 조건절에 조건이 없는 인덱스에 선행 칼럼의 유니크한 값의 개수가 적어야함.

  • 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야 함(커버링 인덱스)

    image
    두번째 제약으로 인해, 테이블 풀 스캔이 일어나는 것을 확인할 수 있다.

다중 컬럼 인덱스

두 개 이상으 칼럼으로 구성된 인덱스를 다중 칼럼 인덱스(복합 컬럼 인덱스, Concatenated Index)라고 한다.

image

인덱스의 두번째 칼럼은 첫번쨰 칼럼에 의존되어 정렬돼 있다.
다중 칼럼 인덱스는 인덱스 내에서 각 칼럼의 위치가 상당히 중요하다.

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

인덱스의 정렬

MYSQL 8.0 버전부터는 다음과 같은 형태의 정렬 순서를 혼합한 인덱스도 생성할 수 있게 됐다. 

CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);

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

쿼리의 WHERE 조건이나 GROUP BY, ORDER BY 절이 어떤 경우에 어떤 방식으로 인덱스를 사용할 수 있는지 식별할 수 있어야 한다.

비교 조건의 종류와 효율성

동등 비교(=)인지 크다/작다(>,<)같은 범위 조건인지에 따라 인덱스 칼럼의 효율정도가 달라진다. 

SELECT * FROM dept_emp
WHERE dept_no='d002' AND emp_no >= 10114;

  • case A : INDEX(dept_no, emp_no)
    dept_no='d002' AND emp_no >= 10114 인 레코드를 찾고 그 이후에는 dept_no가 'd002'가 아닐 때까지 인덱스를 그냥 쭉 읽기만 하면 된다.

  • case B : INDEX(emp_no, dept_no)
    emp_no >= 10114 AND dept_no='d002'인 레코드를 찾고 그 이후 모든 레코드에 대해 dept_no가 'd002'인지 비교하는 과정을 거쳐야 한다.

image

인덱스의 가용성

B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬돼 있다는 것이다. 

SELECT * FROM employees WHERE first_name LIKE '%mer';

이 쿼리는 인덱스 레인지 스캔으로 인덱스를 이용할 수 없다.
first_name 칼럼에 저장된 값의 왼쪽부터 한 글자씩 비교해가며 레코드를 찾아야하는데, '%mer'은 왼쪽부분이 고정되어 있지 않기 떄문이다.('mer%' 는 인덱스 레인지 스캔 가능)

가용성과 효율성 판단

B-Tree 인덱스의 특성상 다음과 같은 조건에서는 사용할 수 없다.

NOT-EQUALS로 비교된 경우 (<>, NOT IN, NOT BETWEEN, IS NOT NULL)

WHERE columm <> 'N'
WHERE columm NOT IN(10,11,12)
WHERE columm IS NOT NULL

LIKE '%??' (앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교되는 경우

WHERE columm LIKE '%석홍'
WHERE columm LIKE '_석홍'

스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우

WHERE SUBSTRING(column, 1, 1) = 'X'

데이터 타입이 서로 다른 비교(인덱스 칼럼의 타입을 변환해야 비교가 가능한 경우)

WHERE char_column = 10

예제

// 다음 쿼리는 인덱스를 사용할 수 없음
... WHERE columm <> 2

// 다음 쿼리는 column1과 column2까지 범위 결정 조건으로 사용됨
WHERE columm1 = 1 AND column2 > 10

// 다음 쿼리는 column1, column2, column3까지 범위 결정 조건으로 사용됨
WHERE column1 IN (1,2) AND column2 = 2 AND column3 <=10

// 다음 쿼리는 column1, column2, column3까지 범위 결정 조건으로, column4는 체크 조건으로 사용
WHERE column1 IN (1,2) AND column2 = 2 AND column3 <=10 AND column4 <> 100

// column1, column2, column3, column4까지 범위 결정조건으로 사용 됨
WHERE column1 IN (1,2) AND column2 = 2 AND column3 <=10 AND column4 LIKE '민석%'
728x90
반응형