[데이터베이스] MySQL 인덱스 - 2

B-Tree 인덱스 사용에 미치는 요소에 대해서 계속 알아보겠습니다.

선택도(기수성)

인덱스에서 선택도 또는 기수성은 거의 같은 의미로 사용됩니다. 모든 인덱스 키값 가운데 유니크한 값의 수를 의미합니다.

인덱스 키 값 가운데 중복된 값이 많다면 기수성은 낮아지고 선택도 또한 낮아집니다. 인덱스는 선택도가 높을수록 검색대상이 줄어들기 떄문에 그만큼 빨리 처리가 됩니다.

 

Select *
FROM tb_test
WHERE country = 'Korea' and city = 'Seoul'

country 라는 컬럼과 city컬럼이 포함된 tb_test 테이블을 예로 들겠습니다.
tb_test의 레코드 건수는 1만건이고 country컬럼으로만 인덱스가 생성된 상태에서 비교해보겠습니다.

 

A 케이스 : Coutnry 컬럼의 유니크한 갯수가 10개라면 어떻게 처리될까요?? 해당 케이스의 경우 평균1000건이 조회가 될껍니다.

B 케이스 : Country 컬럼의 유니크한 갯수가 1000개라면 평균 10건이 조회가 될껍니다.

 

불필요하게 A케이스는 999개의 레코드를 더 읽은것 이지만 B케이스 경우 9건만 더 읽게 됩니다.

이처럼 인덱스에서 유니크한 값의 개수는 인덱스 혹은 쿼리의 효율성에 큰 영향을 미칩니다.

읽어야 하는 레코드의 건수

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업입니다. 테이블에 레코드가 100만건이 저장돼 있는데, 그중에서 50만건을 읽어야합니다.

 

이 작업은 전체 테이블을 모두 읽어서 필요없는 50만건을 버리는것이 효율적인지? 인덱스를 통해 필요한 50만건만 읽는것이 효율적인지 판단해야합니다.

 

보통 일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드를 1건 읽는것이 테이블에서 직접 레코드 1건을 읽는것 보다 4~5배 비용이 더 많은 작업으로 예측합니다. 즉, 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 사용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는 방식으로 처리하는 것이 효율적입니다.

 

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

어떤 경우에 인덱스를 사용하게 유도할지, 또는 사용하지 못하게 할지 판단하려면 MySQL이 어떻게 인덱스를 이용해서 실제 레코드를 읽어 내는지 알아야합니다. MYSQL이 인덱스를 이용하는 세가지 방법을 알아보겠습니다.

인덱스 레인지 스캔

인덱스 레인지 스캔은 인덱스의 접근 방법 가운데 가장 대표적인 접근 방식입니다.

인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식입니다. 검색하려는 값의 수나 검색 결과 레코드 건수와 관계없이 레인지 스캔이라고 표현합니다.

 

시작해야 할 위치 찾으면 그때부터 리프노드의  레코드만 읽으면 됩니다.

만약 페이지 끝까지 읽으면 리프 노드간의 링크를 이용해 다음 리프 노드를 찾아서 다시 스캔합니다.

아래의 그림은실제 인덱스만 읽는 경우를 보여줍니다. 하지만 리프노드를 스캔하면서 실제 데이터 파일의 레코드를 읽어 와야하는 경우도 많은데 이 과정을 알아보겠습니다.

SELECT * FROM employees WHERE first_name BETWEEN 'Bbbe' AND 'GAD';

 

실제로 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서레코드를 읽어오는 과정이 필요하다는 것입니다.

리프 노드에 저장된 레코드 주소로 데이터 파일을 읽어오는데 레코드 한건 단위로 랜덤 I/O가 동작합니다.

 

그래서 인덱스를 통해 데이터를 불러오는 작업은 비용이 많은 작업으로 분류됩니다. 인덱스를 통해 읽어야할 데이터 레코드가 20~25%가 넘는다면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는것이 더 효율적인 처리 방식이 됩니다.

 

인덱스 레인지 스캔은 다음과 같이 크게 3단계를 거칩니다.

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

쿼리가 필요한 데이터에 따라 3번 과정은 필요하지 않을 수 있는데 이를 커버링 인덱스라고 합니다.

MYSQL 에서는 1번과 2번 단계의 과정을 얼마나 수행했는지 확인할 수 있습니다.

SHOW STATUS LIKE 'Handler_%'

커버링 인덱스 : 쿼리에서 필요한 모든 컬럼이 인덱스에 포함되어 있어, 테이블에 접근하지 않고 인덱스만으로 결과를 반환할 수 있는 인덱스

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT,
  email VARCHAR(100)
);​

CREATE INDEX idx_user_name_age ON user(name, age);​

SELECT name, age FROM user WHERE name = 'Alice';

이 경우 name은 검색 조건이고 name, age는 조회 대상 컬럼이므로 idx_user_name_age 인덱스만 보고 결과를 반환할 수 있습니다.

인덱스 풀 스캔

인덱스 레인지 스캔과 마찬가지로 인덱스를 사용하지만 인덱스 레인지 스캔과 달리 인덱스의 처음부터 끝까지 모드 읽는 방식입니다. 대표적으로 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번쨰 컬럼이 아닌 경우 인덱스 풀 스캔 방식이 사용됩니다.

 

일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 효율적입니다. 쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우 주로 이 방식이 사용됩니다.인덱스 뿐아니라 레코드를 읽어야한다면 절대 이 방식으로 처리되지 않습니다.

 

루스 인덱스 스캔

루스 인덱스 스캔이란 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미합니다.

루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않는 인덱스 키값은 무시하고 다음으로 넘어가는 형태로 처리 합니다. 일반적으로 GROUP BY 또는 집합 함수 가운데 MAX 또는 MIN 함수에 대해 최적화를 하는 경우 사용됩니다.

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

인덱스 스킵 스캔

MySQL 8.0부터 도입된 옵티마이저 기능으로, 복합 인덱스의 선두(LEFTMOST) 컬럼에 조건이 없어도, 뒤(RIGHT) 컬럼 조건만으로도 인덱스를 이용하여 쿼리를 최적화할 수 있는 방식입니다.

CREATE INDEX idx_col1_col2 ON my_table(col1, col2);

SELECT * FROM my_table WHERE col2 = 'some_value';

이전 MySQL 버전에서는 col1 조건이 없으므로 이 인덱스를 사용할 수 없어 인덱스 풀 스캔이 동작하였지만
MySQL 8.0.17 이상에서는 Index Skip Scan을 통해 idx_col1_col2를 활용할 수 있음.

MySQL이 내부적으로 선두 컬럼의 가능한 값을 루프 돌면서 후속 컬럼 조건 (col2 = ...)을 만족하는 인덱스 레코드를 찾는 방식입니다.

SELECT * FROM my_table WHERE col1 = 'A' AND col2 = 'X';
SELECT * FROM my_table WHERE col1 = 'B' AND col2 = 'X';
SELECT * FROM my_table WHERE col1 = 'C' AND col2 = 'X';

출처 :

https://product.kyobobook.co.kr/detail/S000001766482

Real MySQL 8.0 (1권) | 백은빈 - 교보문고