[Real MySQL 8.0] 인덱스 살펴보기 2/2
B-Tree 인덱스를 통한 데이터 읽기
어떤 경우에 인덱스를 사용하도록 유도할지, 사용하지 못하게 할지 판단하려면 MySQL이 어떻게 인덱스를 이용해 실제 레코드를 읽어 내는지 알아야 한다. 이제 MySQL이 인덱스를 이용하는 방법 세 가지를 알아보자.
인덱스 레인지 스캔
인덱스 레인지 스캔은 가장 대표적인 인덱스 접근 방식으로, 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다. 뒤에 설명할 두 가지 인덱스 접근 방식보다는 빠른 방법이다.
다음 쿼리를 예제로 살펴보자.
SELECT * FROM employees WHERE first.name BETWEEN 'Ebbe' AND 'Gad';위 쿼리가 실행되면 아래 그림과 같이 루트 노드에서부터 브랜치 노드를 거쳐 최종적으로 리프 노드까지 찾아 들어가 필요한 레코드의 시작점을 찾는다. (두꺼운 화살표는 실제 스캔하는 범위)
시작 지점을 찾으면 그때부터는 리프 노드의 레코드만 차례대로 쭉 읽으면 된다. 만약 리프 노드의 끝까지 읽으면 리프 노드 간 링크를 이용해 다음 리프 노드를 찾아 다시 스캔을 시작한다.
위 그림은 실제 인덱스만 읽는 경우를 나타낸다. 하지만 B-Tree 인덱스의 리프 노드를 스캔하면서 실제 데이터 파일의 레코드를 읽어와야 하는 경우도 많은데, 이 과정을 더 자세히 나타내면 다음과 같다.
인덱스는 정렬되어있기 때문에 인덱스의 컬럼의 정순 또는 역순으로 레코드를 가져온다. 또한, 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는데 이때 한 건당 랜덤 I/O가 발생한다.
커버링 인덱스
인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽어오는 과정이 필요 없는 인덱스다. 디스크를 읽지 않으므로 랜덤 읽기가 줄어들어 성능이 빨라진다.
인덱스 풀 스캔
인덱스의 처음부터 끝까지 모두 읽는 방식을 말한다.
대표적으로 쿼리 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다.
// (A, B, C)라는 인덱스가 있다고 가정
SELECT * FROM employees WHERE B = 'b' AND C = 'c';
--> 조건절에 사용된 컬럼이 인덱스의 첫 번째(A) 컬럼이 아니므로 인덱스 풀 스캔이 일어난다.인덱스 풀 스캔의 처리 방식을 그림으로 살펴보면 다음과 같다.
- 인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한다.
- 해당 위치에서 리프 노드를 연결하는 링크드 리스트를 따라 처음부터 끝까지 스캔한다.
인덱스 풀 스캔은 테이블 전체를 읽는 것보다 적은 디스크 I/O가 발생하기 때문에 테이블 전체를 읽는 것보다는 낫다고 볼 수 있지만 인덱스를 사용하는 측면에서는 효율적인 방식은 아니다. 즉, 인덱스를 효율적으로 사용하지 못하는 것으로 볼 수 있다.
루스 인덱스 스캔
MySQL의 루스 인덱스 스캔은 오라클의 인덱스 스킵 스캔과 작동 방식이 비슷하다. 루스 인덱스 스캔은 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다.
인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요하지 않은 인덱스 키 값은 무시하고 넘어가는 형태로 처리된다. 일반적으로 GROUP BY 또는 MAX(), MIN() 함수에 대해 최적화를 하는 경우 사용한다.
MySQL 5.7 버전까지는 기능이 많이 제한적이었지만, MySQL 8.0 버전부터 최적화를 지원하기 시작했다.
인덱스 스킵 스캔
인덱스 스킵 스캔은 MySQL 8.0 버전에 추가된 최적화 기능으로 조건절에 첫 번째 인덱스가 없어도 두 번째 인덱스만으로 인덱스를 검색할 수 있게 해주는 기능이다.
예시로 앞에서 예로 들었던 쿼리를 다시 한번 살펴보자.
// (A, B, C)라는 인덱스가 있다고 가정
SELECT * FROM employees WHERE B = 'b' AND C = 'c';
--> 조건절에 사용된 컬럼이 인덱스의 첫 번째(A) 컬럼이 아니므로 인덱스 풀 스캔이 일어난다.앞에서 공부했던 인덱스의 경우 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 인덱스를 사용할 수 없었다. 하지만 MySQL 8.0 버전에 인덱스 스킵 스캔 최적화 기능이 도입되면서 옵티마이저가 A 컬럼을 건너뛰어서 B, C 컬럼만으로도 인덱스 검색이 가능하게 되었다.
GROUP BY의 인덱스 처리에만 사용할 수 있었던 루스 인덱스 스캔과는 다르게 인덱스 스킵 스캔은 WHERE 조건절의 검색에 사용 가능해져, 그 용도가 훨씬 넓어졌다.
다중 컬럼 인덱스
지금까지 살펴본 인덱스는 모두 1개의 컬럼만 포함된 인덱스였다. 하지만 실제 서비스에서는 2개 이상의 컬럼을 포함하는 인덱스가 더 많이 사용된다.
두 개 이상의 컬럼으로 구성된 인덱스를 다중 컬럼 인덱스라고 하며, 그 구조는 다음과 같다.
리프 노드의 인덱스를 자세히 살펴보면 다중 컬럼 인덱스의 중요한 포인트가 나온다.
바로 인덱스의 두 번째 컬럼이 첫 번째 칼럼에 의존해 정렬되어 있다는 것이다. 이는 두 번째 컬럼이 첫 번째 컬럼이 똑같은 레코드에서만 의미가 있다는 것을 나타낸다. 이처럼 다중 컬럼 인덱스에서는 컬럼의 위치가 상당히 중요하기 때문에 이를 아주 신중히 결정해야 한다.
B-Tree 인덱스의 정렬 및 스캔 방향
인덱스를 생성할 때 설정한 정렬 규칙에 따라 인덱스의 키 값이 정렬된다. 하지만 인덱스가 오름차순으로 생성됐다고 해서 항상 오름차순으로만 읽는 것은 아니다. 인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정된다.
인덱스 스캔 방향
MySQL 옵티마이저는 인덱스의 스캔 방향을 전환해서 사용하도록 실행 계획을 만들어 낸다. 인덱스는 생성 시점에 오름차순 또는 내림차순으로 정렬 방향이 결정되지만 쿼리가 그 인덱스를 사용하는 시점에 인덱스 스캔 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다.
인덱스는 항상 정렬되어있으므로 최솟값부터 스캔을 시작하면 오름차순으로, 최댓값부터 읽으면 내림차순으로 값을 가져올 수 있기 때문이다. 따라서 ORDER BY 처리나 MIN() 또는 MAX() 함수 등의 최적화가 필요한 경우 인덱스를 읽는 순서만 변경해서 인덱스 생성 시 지정한 정렬 규칙에 대한 문제점을 해결할 수 있다.
내림차순 인덱스
InnoDB 스토리지 엔진에서는 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수밖에 없는 두 가지 이유가 있다.
- 페이지 잠금이 인덱스 정순 스캔에 적합한 구조
- 페이지 내에 인덱스 레코드가 단방향으로만 연결된 구조
InnoDB 스토리지 엔진에서 정순 스캔과 역순 스캔은 페이지 간의 양방향 연결 고리를 통해 전진하느냐 후진하느냐의 차이만 있지만 위와 같은 이유로 인해 인덱스 정순 스캔이 더 빠른 성능을 가지게 된다.
B-Tree 인덱스의 가용성과 효율성
우리는 어떤 조건에서 인덱스를 사용할 수 있고 어떨 때 사용할 수 없는지 식별할 수 있어야 한다. 그래야만 쿼리를 최적화하거나, 쿼리에 맞게 인덱스를 최적으로 생성할 수 있기 때문이다.
비교 조건의 종류와 효율성
다음 예제로 비교 조건에 따라 인덱스의 효율이 어떻게 달라지는지 살펴보자.
SELECT * FROM dept_emp
WHERE dept_no= 'd002' AND emp_no >= 10114 ;- 케이스 A : INDEX(dept_no, emp_no)
- 케이스 B : INDEX(emp_no, dept_no)
케이스 A인 경우, 먼저 dept_no가 ‘d002’이고 emp_no가 10114보다 큰 레코드를 찾는다. 이후에는 dept_no가 ‘d002’가 아닐 때까지 인덱스를 쭉 읽기만 하면 된다.
비교 작업을 꼭 필요한 부분에만 수행한 것으로 상당히 효율적인 인덱스라고 볼 수 있다.
반면에 케이스 B의 경우, emp_no가 10114 보다 큰 레코드이고 dept_no가 ‘d002’인 레코드를 찾는다. 이후 찾은 모든 레코드에 dept_no가 ‘d002’인지 비교하는 작업을 수행한다.
케이스 B는 케이스 A에 비해 많은 비교 작업을 수행해야 한다. 왜 이런 현상이 발생할까?
그 이유는 다중 컬럼 인덱스에서는 2번째 컬럼이 1번째 컬럼에 의존해 다시 정렬되기 때문이다. 즉, 케이스 A는 2번째 컬럼인 emp_no가 비교 작업의 범위를 줄이는데(작업 범위 결정 조건) 도움을 주지만 케이스 B는 범위를 좁히지 못하고 단순히 비교 용도(필터링 조건)로만 사용되기 때문이다.
이처럼 작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높여준다. 하지만 필터링 조건은 많다고 해서 성능을 높이지 못하고 오히려 느리게 만들 때가 있으니 주의하자.
인덱스의 가용성
B-Tree 인덱스의 특징은 왼쪽 값에 의존해 오른쪽 값이 정렬돼 있다는 것이다. 이 말은 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 검색이 불가능하다는 것을 나타낸다.
SELECT * FROM employees WHERE first_name LIKE '%mer1';위 쿼리는 인덱스 레인지 스캔을 사용할 수 없다. 그 이유는 first_name 컬럼에 저장된 값의 왼쪽부터 비교해가며 일치하는 레코드를 찾아야 하는데, 조건 값의 왼쪽 부분(’%mer1’)이 정해져 있지 않기 때문이다.
또 다른 예로 인덱스가 (dep_no, emp_no)로 생성되어 있다면 아래의 쿼리는 인덱스를 효율적으로 사용하지 못한다.
SELECT * FROM dept_emp WHERE emp_no>=10144;다중 컬럼 인덱스로 생성된 인덱스이므로 dept_no를 먼저 정렬한 후, 다시 emp_no 컬럼값으로 정렬돼 있기 때문이다. 이렇듯 B-Tree의 특성상 인덱스를 효율적으로 사용할 수 없는 조건들이 존재한다. 그 조건들은 다음과 같다.
- NOT-EQUAL로 비교된 경우 (NOT IN, NOT BETWEEN, IS NOT NULL)
- WHERE column <> 'N'
- WHERE column NOT IN (10,11,12)
- WHERE column IS NOT NULL
- LIKE %xxx 형태 문자열 패턴 비교인 경우
- WHERE column LIKE '%test‘
- WHERE column LIKE '%test%‘
- WHERE column LIKE '_test‘
- 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우
- WHERE SUBSTRING(column,1, 1) = 'X'
- WHERE DAYOFMONTH(column) = 1
- 인덱스 컬럼의 타입을 변환해야 비교가 가능한 경우
- WHERE char_column = 10 → char를 int와 비교
- 문자열 데이터 타입의 콜레이션이 다른 경우
- WHERE utf8_bin_char_column = euckr_bin_char_column