DBMS의 쿼리 성능을 위해서 빼놓을 수 없는게 바로 Index이다. 각 인덱스마다 특성도 존재하며 차이점도 존재하기에 이를 이해하는 것이 중요하다. 또한, MySQL 서버의 옵티마이저가 발전하면서 성능이 개선되었다고 해도 Index에 대한 깊은 이해는 쿼리 튜닝의 기본이라고 볼 수 있다.
디스크 읽기 방식
Index에 대해서 알아보기 전에 디스크 읽기 방식을 알아볼 필요가 있다. 컴퓨터의 CPU나 메모리는 매우 빠른 속도로 발전했지만, 디스크 같은 기계 장치는 상당히 제한적으로 발전했다. 그렇기에 저장 매체는 컴퓨터에서 가장 느린 부분 중 하나이며, 데이터베이스의 쿼리 튜닝은 디스크 I/O를 어떻게 줄이느냐가 관건이다.
HDD와 SSD
HDD와 SSD는 데이터 저장 방식과 읽기/쓰기 성능에서 차이가 있다. HDD는 회전하는 자기 디스크와 기계적인 읽기/쓰기 헤드를 이용해 데이터를 저장하며, 물리적인 이동이 필요하므로 랜덤 접근 성능이 떨어진다. 반면 SSD는 플래시 메모리를 이용하여 데이터를 저장하며, 기계적인 부품이 없어 랜덤 접근 속도가 빠르고 대량의 작은 데이터를 처리하는 성능이 우수하다.
DBMS에서는 HDD에 유리한 순차 I/O는 비중이 크지 않고, 랜덤 I/O를 통해서 작은 데이터를 읽고 쓰는 작업이 대부분이기에, SSD는 DBMS에 최적이라고 볼 수 있다.
랜덤 I/O와 순차 I/O
그렇다면 랜덤 I/O와 순차 I/O는 어떤 차이가 있을까?
위 사진을 보면, 3개의 페이지를 기록하기 위해서 랜덤 I/O는 세 번의 System Call을 요청하고, 순차 I/O는 한번의 System Call을 요청했다. 만약 저장장치가 디스크라고 한다면, 순차 I/O는 디스크를 한 번 움직인 것이고 랜덤 I/O는 세 번 움직인 것이다.
디스크의 I/O 성능은 디스크 헤더를 움직여서 올바른 위치에 옮기는 단계에서 결정나는데, 결국 이러한 상황에서는 순차 I/O가 랜덤 I/O 보다 3배 더 빠르다고 볼 수 있다. DBMS 대부분 데이터를 빈번하게 읽고 쓰기 때문에, 랜덤 I/O 의 부하를 줄여주고자 MySQL 에서는 그룹 커밋이나 로그 버퍼, 버퍼 풀 등의 기능이 내장된 것이다.
SSD는 디스크를 가지지 않기에 랜덤 I/O나 순차 I/O나 별 차이 없을 것이라 생각할 수도 있다. 하지만, 실제로는 랜덤 I/O가 순차 I/O보다 전체 throughput이 떨어지며, SSD 사양에도 랜덤과 순차 I/O 성능이 명시적으로 적혀있다.
그렇다고 쿼리를 튜닝해서 랜덤 I/O를 순차 I/O로 바꾸는 것은 쉽지 않고, 보통 쿼리 튜닝은 그냥 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.
👾 인덱스 레인지 스캔과 풀 테이블 스캔
인덱스 레인지 스캔은 테이블 레코드 데이터를 읽기 위해 랜덤 I/O를 사용하며 풀 테이블 스캔은 순차 I/O를 사용한다. 그래서 테이블이 크고 레코드 대부분을 읽어야 할 때에는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다.
인덱스란?
인덱스는 간단하게 말하자면 데이터 검색을 빠르게 하기 위해 특정 열에 대한 정렬된 구조를 유지하는 데이터 구조이다. 하지만, 데이터가 저장, 수정될때 때에 마다 항상 값을 정렬하고 인덱스에도 반영해줘야 하기 때문에 데이터 변경 작업에는 불리하다. 즉, DBMS에서 인덱스는 데이터 저장 성능을 희생하고 데이블 읽기 속도를 높이는 기능인데, 그래서 인덱스를 마구잡이로 추가하는 것은 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 역효과를 볼 수 있다.
인덱스는 역할별로 구분한다면 Primary Key와 Secondary Key 인덱스로 나눌 수 있다.
PK 는 우리가 흔히 알고 있는 레코드를 대표하는 식별자를 의미한다. 테이블에서 PK 를 생성하면 Index에 PK 에 관한 인덱스가 생긴것을 볼 수 있다. 이 PK 인덱스를 제외한 나머지의 인덱스들을 Secondary Index 라고 한다.
또한, 데이터 중복 허용 여부로 구분하면 유니크 인덱스와 아닌 인덱스로 구분할 수 있다. 그냥 단순히 중복 값을 허용 하는지에 대한 차이밖에 없지만, 이는 쿼리 옵티마이저에게는 꽤 중요하게 작용할 수 있는데, 예를 들어 Equal 조건으로 검색을 한다면 옵티마이저는 1건의 레코드만 찾으면 더 이상 찾지 않아도 된다.
B-Tree 인덱스
B-Tree는 인덱스 중에 가장 일반적으로 사용되고, 가장 범용적인 목적으로 사용되는 인덱스이다.
구조 및 특성
B-Tree 인덱스는 균형 트리 구조를 가지며, 루트 노드, 내부 노드, 리프 노드로 구성된다. 이때, 모든 리프 노드는 항상 같은 깊이에 위치하여 검색 성능이 일정하게 유지된다.
이때, 기본적으로 대부분의 index에서 인덱스의 키 값은 정렬된 상태를 유지하고 있지만 데이터 파일의 레코드는 정렬되어 있지는 않다. 그래서 데이터 파일의 레코드는 삽입된 순서대로 저장되어 있는 것으로 생각할 수 있지만, 실제로는 레코드가 삭제되어 빈 공간이 생기면 해당 공간을 재사용하도록 설계되기에 항상 삽입 순서대로 저장되는 것은 아니다.
여기서 알고 있으면 좋을 MyISAM과 InnoDB의 차이점은 MyISAM의 레컨더리 인덱스는 물리적인 주소를 가지지만, InnoDB는 PK를 사용하여 논리적인 주소를 가진다.
그래서 InnoDB에서 인덱스를 통해 레코드를 읽을 때에는 아래 사진과 같이 세컨더리 인덱스를 통해 얻은 PK값을 이용해 프라이머리 키 인덱스를 한번 더 검색한 후, 프라이머리 키의 리프 페이지에 있는 레코드를 읽는다.
즉, InnoDB에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 Primary Key index를 한번 검색해야 한다. 이는 성능이 떨어져 보일 수 있지만, 각각의 장점이 존재하며 더 자세하게 클러스터링 인덱스에 대해서 다룰 예정이다.
인덱스 키 추가 및 삭제
테이블의 레코드를 저장하거나 변경하는 경우 인덱스에도 키 추가, 삭제 작업이 발생한다.
인덱스 키 추가
이때, 인덱스에 키가 추가되는 경우 적절한 리프 노드에 삽입되며, 가득 차면 Split이 발생하여 트리 깊이가 증가하는 방식으로 이루어진다. 이때, 정확한 작업 비용을 추정하기는 어렵지만 대략적으로는 단순히 테이블에 레코드를 추가하는 작업을 1이라고 가정하면, 테이블의 인덱스에 키에 추가하는 작업으로는 1.5 정도로 예측한다고 한다. 테이블에 인덱스가 3개인 경우 → 5.5(1.5 * 3 + 1)
인덱스도 디스크로부터 페이지를 읽고 쓰기를 해야하기 때문에 이러한 시간이 걸리게 된다. 그래서 이전에 소개했던 InnoDB에서는 Change Buffer같은 캐싱을 통해서 성능을 개선한다.
인덱스 키 삭제
인덱스에 키가 삭제되는 경우에는 리프 노드를 찾아 삭제 마크만 하면 작업이 완료된다. 이렇게 마크 작업을 하는 것도 디스크 I/O를 사용하는 것이고 그렇기에 이 마크 작업도 Change Buffer를 통해 버퍼링이 가능하다.
인덱스 키 변경
인덱스의 키 값은 그 값에 따라 리프 노드의 위치가 결정되므로, 키 값이 변경되면 단순히 키 값만 변경하는 것은 불가하다. 그래서 키 값을 삭제한 후, 다시 키 값을 추가하는 형태로 처리되는데 이 작업은 위에 소개한 추가, 삭제 작업과 동일하다.
또한, Change Buffer를 통한 지연처리가 가능하다.
인덱스 키 검색
이렇게 인덱스를 유지하기 위해 추가 비용이 발생하지만, 인덱스를 사용하는 이유는 빠른 검색을 위해서다. 이때, 루트 노드 부터 시작해서 리프 노드 까지 이동하면서 값을 찾는데 이는 무조건 O(logN)의 시간 복잡도가 걸린다. Balanced Tree인 특성과 비교적 낮은 높이를 가지는 B-Tree가 인덱스로 사용되는 가장 큰 이유다.
InnoDB 스토리지 엔진에서는 인덱스는 단순 검색 뿐 아니라, 레코드 락과 갭 락이 인덱스를 통해 레코드를 잠그는 형태로 이루어져 있다. 그렇기에 UPDATE, DELETE 요청에서 적절히 타고 들어갈 Index가 없다면 필요없는 레코드까지 불필요하게 잠금이 걸릴 수 있고 이는 MySQL에서 더욱 index 설계가 중요한 이유이다.
인덱스에 영향을 미치는 요소
인덱스 키 값의 크기
인덱스 키 크기가 클수록 한 페이지에 저장할 수 있는 인덱스 항목의 수가 줄어들어 성능이 저하될 수 있다. B-Tree 인덱스는 데이터를 저장하는 가장 기본 단위인 페이지 단위로 관리하여 저장하는데, 이 페이지는 4KB~64KB의 크기를 가질 수 있으며 기본적으로 16KB이다.
이때, 페이지의 크기가 16KB라고 하고 인덱스의 키가 INT로 설정되어 4 Byte를 가진다고 하면 한 페이지에 저장 가능한 키 개수는 약 4000개 정도일 것이다. 하지만, 인덱스의 키가 16Byte를 가진다면 1000개, 64 Byte라면 250개 정도를 한 페이지에 키를 저장할 수 있을 것이다. 이때, 한 페이지에 저장된 키는 가질 수 있는 자식 노드와 마찬가지이므로 Tree 깊이에 많은 차이가 있을 것이다.
그래서, SELECT 쿼리가 데이터 약 2000개를 읽어야한다면 4 Byte 인덱스 키에서는 한 번의 디스크 읽기로 가능하지만, 16 Byte 인덱스 키에서는 최소한 2번은 읽어야한다. 결국 인덱스의 키 값이 커지면 트리의 깊이가 증가하여 성능이 저하될 수 있다는 것이다.
또한, 인덱스의 키 값이 커진다는 것은 인덱스의 크기가 커지는 것이고 이는 버퍼 풀과 같은 메모리에 캐시해 둘 수 있는 레코드의 수가 줄어드는 것을 의미한다.
기수성(Cardinality)
Cardinality는 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 즉, 전체 인덱스 키 값은 100개인데, 그 중에서 유니크한 값의 수는 10개라면 Cardinality는 10이다. 이때, Cardinality가 높을수록 인덱스 검색 효율이 증가한다.
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
status ENUM('ACTIVE', 'INACTIVE', 'BANNED')
);
CREATE INDEX idx_name ON users(name);위의 예시 테이블에서 레코드의 개수는 총 100만개이며 name에 대한 인덱스만 존재한다고 하자. 그리고 name 컬럼의 유니크한 값의 수가 각각 10만개(A 케이스), 1만개(B 케이스)인 예제를 생각해보자.
SELECT * FROM users WHERE name = 'John' AND status = 'ACTIVE';위의 쿼리를 실행시키면 A케이스에 대해서는 평균 10건, B케이스에 대해서는 100건이 조회될 수 있음을 알 수 있다. 이때, 만약 WHERE name = 'John' AND status = 'ACTIVE' 조건을 만족하는 레코드가 1건만 있었다면 B 케이스의 인덱스는 적합하지 않을 것이다. B케이스는 1건의 레코드를 위해 99건의 필요없는 레코드를 읽었고, A케이스는 1건의 레코드를 위해 9건의 필요없는 레코드를 읽었다.
이렇기에 같은 쿼리 결과를 얻지만, Cardinality에 따라 내부적으로 실제 수행한 작업은 다르다는 것을 알 수 있다.
읽어야하는 레코드의 수
인덱스를 통해 레코드를 읽는것은 테이블의 레코드를 직접 읽는 것보다 높은 비용을 초래한다. 만약 100만개의 레코드가 저장되어 있는 테이블에 50만개의 레코드를 읽는 쿼리가 있다고 하면, 인덱스를 사용해서 읽는 것이 비효율 적일 수 있다.
그렇다면 인덱스를 사용하는 것이 얼마의 비용을 초래하는지 알 필요가 있는데, 일반적인 DBMS의 옵타마이저에서 인덱스를 통해 읽는 것이 4~5배 정도 비용이 더 드는 작업으로 예측한다고 한다. 즉, 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 사용하지 않고 풀 테이블 스캔 후 필터링을 하는 것이 효율적인 것이다.
그래서 앞선 예제에서 MySQL 옵티마이저는 인덱스를 이용하지 않고 풀테이블 스캔을 진행할 것이다.
B-Tree 인덱스 스캔
그럼 실제로 MySQL이 어떻게 인덱스를 사용해서 레코드를 읽어 내는지 확인해보도록 하자.
인덱스 레인지 스캔
인덱스를 활용하여 일정한 범위 내에서 데이터를 검색하는 방식이다. 인덱스가 정렬된 상태로 저장되기 때문에, 데이터를 빠르게 찾을 수 있다.
SELECT first_name FROM employees WHERE first_name BETWEEN ‘Ebbe’ AND ‘Gad’; 
위의 예시에서 먼저 Ebbe를 찾기 위해 B-Tree 인덱스를 따라 리프 노드 까지 탐색한 후, 리프 노드를 찾으면 해당 노드에서 부터 읽기 시작한다. 일단 시작해야할 위치를 찾으면 그때부터는 리프 노드의 레코드만 쭉 읽으면서 Gad까지 데이터를 읽어간다.
이게 가능한 이유가 인덱스가 정렬되어 있기 때문이며, 시작 위치를 검색하고 그 이후로부터 쭉 읽어나가기 때문에 인덱스를 구성하는 컬럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다.
하지만, 리프 노드의 페이지를 다 찾으면 끝이 아니라 일치하는 건들에 대해서 데이터 파일에서 레코드를 읽어오는 과정이 필요하다. 이때, 한건 한건에 대해서 랜덤 I/O가 일어나며, 그래서 인덱스를 통해 레코드를 읽는 작업이 단순히 레코드를 읽는 것보다 비용이 많이 드는 작업으로 분류된다.
아래 예제 쿼리를 본다면 first_name에 대해서만 적용되어 있는 인덱스(idx_first_name)에 대해서 type = range (인덱스 레인지 스캔) 그리고 Extra = Using index (커버링 인덱스) 가 적용 되었음을 확인할 수 있다.
👀 인덱스 스캔 흐름
- 인덱스에서 조건이 만족하는 값이 저장된 위치를 찾는다. (단일 레코드에 대한 조회이면 여기서 끝이 날 것이다.)
- 이후 필요한만큼 인덱스를 쭉 읽는다.
- 해당 키와 레코드 주소를 통해 페이지에서 레코드를 읽어온다.
인덱스 풀 스캔
인덱스 풀 스캔은 전체 인덱스를 처음부터 끝까지 읽는 방식이다. 대표적으로 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 사용된다. 예를 들어, 인덱스는 (A,B,C)로 이루어져있지만 조건은 B나 C로 검색하는 경우이다.
인덱스에서 선두 컬럼의 조건이 없다면 기본적으로 테이블 풀 스캔을 사용할텐데, 인덱스 크기는 테이블 크기보다는 작으므로 테이블 풀 스캔보다 인덱스 풀 스캔이 일반적으로 효율적이다. 그렇다고 해도 스캔 후 테이블에 추가 접근하면 당연히 테이블 풀 스캔이 성능이 좋을 것 이기에, 인덱스에 명시된 컬럼으로 조건을 처리할 수 있는(커버링 인덱스)의 경우에만 주로 이 방식이 사용된다.
위와 같이 인덱스의 처음 혹은 끝에서 시작해서 끝 혹은 처음까지 쭉 스캔하는 방식이다.
아래 쿼리에서 first_name에 대해서만 적용되어 있는 인덱스(idx_first_name)에 대해서 type = index (인덱스 풀 스캔) 그리고 Extra = Using index (커버링 인덱스) 가 적용 되었음을 확인할 수 있다.
루스 인덱스 스캔
루스 인덱스 스캔은 모든 인덱스 항목을 읽지 않고 필요한 항목만 선택적으로 읽는 방식이다.
인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요없는 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다. 가장 대표적인 사용 예시가 GROUP BY이다.
아래 쿼리 예제를 통해 idx_department_salary 인덱스에 대해서 type = range (인덱스 레인지 스캔) 그리고 Extra = Using index for group-by (루스 인덱스 스캔) 가 적용 되었음을 확인할 수 있다.
CREATE INDEX idx_department_salary ON employees(department, salary);위의 인덱스를 추가한 상태이며, 해당 인덱스에서는 department 별로 salary 값을 정렬된 상태로 유지하고 있다. 결국 각 department 그룹에서 가장 작은 단 하나의 salary 값만 가져오고 나머지 값들은 읽지 않는 방식으로 스캔하는 것이다.
인덱스 스킵 스캔
인덱스의 핵심은 값이 정렬되어 있다는 것이다, 그렇기에 인덱스를 구성하는 컬럼의 순서가 굉장히 중요하다. 보통 다중 컬럼 인덱스를 만들면 왼쪽에서 오른쪽 순서대로 검색하는 것이 일반적이다.
예를 들어 아래와 같은 테이블 예시를 살펴보면
CREATE TABLE users (
id INT PRIMARY KEY,
status ENUM('ACTIVE', 'INACTIVE', 'BANNED'),
name VARCHAR(100)
);
CREATE INDEX idx_status_name ON users(status, name);이 경우, 아래 쿼리는 status 컬럼을 명시적으로 조건으로 사용했기 때문에 인덱스를 활용할 수 있다.
SELECT * FROM users WHERE status='ACTIVE' AND name = 'John';하지만, 만약 status 조건이 없이 name만으로 검색하려고 하면 어떻게 될까?
SELECT * FROM users WHERE name = 'John';일반적으로 idx_status_name 인덱스는 status 컬럼이 없는 상태에서는 비효율적이라 MySQL은 인덱스를 사용하지 않고 테이블 풀 스캔을 수행할 수도 있다.
이때, status 컬럼을 건너뛰어서 name 컬럼만으로도 인덱스 검색을 가능하게 해주는 것이 인덱스 스킵 스캔이다. 루스 인덱스 스캔과 비슷하게 보일 수 있지만, 루스 인덱스 스캔은 GROUP BY, MIN(), MAX() 에 사용되며, 인덱스 스킵 스캔은 다중 컬럼 인덱스에서 첫 컬럼을 건너뛰며 검색하는 것이다.
MySQL 옵티마이저는 내부적으로 status 컬럼에서 유니크한 값을 모두 조회해서 주어진 쿼리에 status 컬럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리한다.
--- SELECT * FROM users WHERE name = 'John';
SELECT * FROM users WHERE status='ACTIVE' AND name = 'John';
SELECT * FROM users WHERE status='INACTIVE' AND name = 'John';
SELECT * FROM users WHERE status='BANNED' AND name = 'John';status가 ENUM이라서 가능한 것은 아니고, 어떤 타입이더라도 인덱스에 존재하는 모든 값을 먼저 추출하고 그 결과를 이용해 인덱스 스킵 스캔을 수행한다.
하지만, 한계점도 존재하는데 위 예시만 보더라도 유추할 수 있지만 WHERE 절에 조건이 없는 인덱스 선행 컬럼의 유니크한 값의 개수가 적어야한다. 유니크한 값의 개수가 많아진다면 당연히, 인덱스에서 스캔해야할 시작 지점을 검색하는 작업이 많이 필요하기 때문이다. 그리고 아직은 커버링 인덱스인 경우에만 적용이 가능하다.
| 구분 | 루스 인덱스 스캔 | 인덱스 스킵 스캔 |
|---|---|---|
| 사용되는 쿼리 | GROUP BY, MIN(), MAX() | WHERE 절에서 다중 컬럼 인덱스의 첫 번째 컬럼을 건너뛰고 검색 |
| 주요 특징 | 일부 인덱스 값만을 이용하여 검색하여 불필요한 데이터 읽기 줄임 | 첫 번째 컬럼을 건너뛰고 두 번째 컬럼부터 검색 가능 |
| 주요 사용 사례 | GROUP BY, DISTINCT 최적화 | 다중 컬럼 인덱스에서 첫 번째 컬럼 없이 검색할 때 |
| 버전 | MySQL 5.x 이상 | MySQL 8.0.22 이상 |
커버링 인덱스
스캔에 대해서 설명하면서 계속해서 커버링 인덱스를 언급하였는데, 이는 쿼리가 필요로 하는 모든 데이터를 인덱스만으로 조회할 수 있도록 설계된 인덱스를 뜻하며 이 인덱스에 대해서는 인덱스 페이지를 가져온후 더 많은 데이터를 얻기 위해 테이블 레코드 접근을 추가로 하는 작업은 필요없다.
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
first_name VARCHAR(100),
last_name VARCHAR(100),
department VARCHAR(50),
salary INT
);
CREATE INDEX idx_first_name ON employees(first_name);위와 같은 테이블에서 아래와 같은 쿼리를 수행하면, 인덱스 → 테이블 조회 과정을 거치게 될 것이다.
SELECT first_name, last_name FROM employees WHERE first_name = 'John';first_name을 조회하기 위해 인덱스를 탐색하지만, last_name은 인덱스에 포함되지 않았기 때문에, 데이터 파일에서의 레코드 존재가 필요하다.
하지만, 아래와 같은 쿼리에 대해서는 데이터 파일에 추가적인 접근이 필요 없을 것이다.
SELECT first_name FROM employees WHERE first_name = 'John';추가로, 첫 번째 쿼리 상황에 대해서 first_name과 last_name을 동시에 포함하는 인덱스를 생성한다면?
CREATE INDEX idx_first_last ON employees(first_name, last_name);SELECT first_name, last_name FROM employees WHERE first_name = 'John';이제 first_name과 last_name이 모두 idx_first_last인덱스에 포함되어 있기에 테이블을 조회하지 않고 데이터를 바로 반환 가능할 것이다.
실제 커버링 인덱스를 사용하고 안하고의 차이는 꽤 발생할 수 있으며 그렇기에 커버링 인덱스에 대해서 알고 있는 것이 중요하다.
다중 컬럼 인덱스
얼핏 계속 언급했지만 실제 서비스 DBMS를 사용하다 보면 2개 이상의 컬럼을 포함하는 인덱스를 더 많이 사용한다. 이렇게 여러개의 컬럼을 포함하는 인덱스를 다중 컬럼 인덱스라고 한다.
해당 인덱스에서 중요한 점은 각 인덱스의 후행 컬럼은 바로 앞의 컬럼에 의존해서 정렬되어 있다는 것이다. 즉, 간단하게 말하면 두 번째 컬럼의 정렬은 첫 번째 컬럼이 똑같은 레코드에서만 의미가 있다. 그렇기 때문에 인덱스 내에서 각 칼럼의 순서가 굉장히 중요해진다.
인덱스의 정렬 및 스캔 방향
인덱스를 생성할 때, 정렬 규칙에 따라 오름차순이거나 내림차순으로 정렬되어 저장한다. 하지만, 오름차순 인덱스에 대해서 오름차순 쿼리만 가능한 것은 아니다. 당연하게도 끝에서부터 읽으면 내림차순으로 정렬된 인덱스랑 같으며, MySQL 쿼리 옵티마이저가 적절한 스캔 방향을 선택한다.
CREATE INDEX idx_department_salary ON employees(department ASC, salary DESC);또한, 위의 같이 정렬 순서를 혼합한 인덱스도 생성 가능하다.
인덱스 스캔 방향
name 컬럼에 대해 인덱스가 포함된 employees 테이블이 존재한다고 하자.
SELECT * FROM employees ORDER BY name ASC LIMIT 1;기본적으로 인덱스는 오름차순 정렬이므로, ASC 키워드를 사용하면 처음부터 끝으로 순차적으로 인덱스를 검색한다.
SELECT * FROM employees ORDER BY name DESC LIMIT 1;하지만 내림차순 접근도 가능한데, 이 경우에는 인덱스를 역순으로 스캔하여 데이터를 가져온다.
즉, 인덱스는 오름차순 또는 내림차순으로 정렬이 결정되어 있지만, 그 인덱스를 사용하는 시점에서 인덱스를 어떻게 읽냐에 따라 각기 다른 정렬 효과를 얻을 수 있다. 쿼리 옵티마이저는 필요한 상황에 따라 인덱스의 읽기 방향을 전환해서 사용하도록 계획을 만든다.
내림차순 인덱스
CREATE INDEX idx_name_desc ON employees(name DESC);
CREATE INDEX idx_name_asc ON employees(name ASC);그렇다면, 내림차순 인덱스는 왜 필요한 것일까? 기본 오름차순 인덱스에서도 거꾸로 읽을 수 있다면 굳이 내림차순 인덱스는 만들지 않아도 될 것처럼 보인다.
하지만 실제 100만건의 레코드가 있는 테이블에서 아래 쿼리와 같은 상황에서는 말이 달라진다.
SELECT * FROM employees FORCE INDEX(idx_name_asc) ORDER BY name ASC LIMIT 1 OFFSET 1000000;
SELECT * FROM employees FORCE INDEX(idx_name_asc) ORDER BY name DESC LIMIT 1 OFFSET 1000000;첫 번째 쿼리는 그냥 가장 큰 레코드 1건을, 그리고 두 번째 쿼리는 테이블의 모든 레코드를 스캔해서 1건을 꺼내와야한다.
또한, 단순히 Double linked list를 통해서 전진하냐 후진하냐 밖에 차이만 있지만, 실제 내부 구조를 보면 그렇지 않다. 아래 두 가지 특징때문에 내림차순 인덱스가 필요하다.
1. 페이지 잠금은 인덱스 순방향 스캔에 적합한 구조
InnoDB 스토리지 엔진에서는 페이지 잠금 과정에서 데드락을 방지하기 위해서 B-Tree의 왼쪽에서 오른쪽 순서로만 잠금을 획득하도록 하고 있다. 그래서 순방향 스캔에 대해서는 간단하지만, 역방향 스캔에 대해서 잠금을 얻는 과정은 복잡하다.
2. 페이지 내에서 인덱스 레코드는 단방향으로만 연결된 구조
Double linked list로 연결된 B-Tree의 리프 페이지 구조와는 달리, 페이지 내부의 레코드들은 Single linked list 구조로 구성되어 있다. 즉, 페이지를 찾아가는 것은 방향에 덜 구애받지만 찾은 페이지 내에서 레코드를 찾는 것은 다른 얘기인 것이다.
이때, Single linked list는 인덱스 정렬에 따라 방향이 구성되는데, 오름차순 인덱스에 대해서 순방향 스캔은 인덱스를 그대로 따라가면 되지만, 역방향 스캔에 대해서는 그렇지 않다.
코드에 대한 간략한 주석과 더 깊은 설명에 대해서는 MySQL Ascending index vs Descending index 포스팅에서 다루고 있는데, 이를 참조하면 더 깊은 이해에 좋을 것 같다.
B-Tree 인덱스의 가용성과 효율성
그렇다면 어떠한 경우와 방식으로 인덱스를 사용할 수 있는지 구분할 줄 알아야한다. 그래야 쿼리의 조건을 최적화 하거나, 쿼리에 따라 인덱스를 생성할 수 있기 때문이다.
비교 연산자(=, <, >, BETWEEN 등)에 따라 인덱스의 사용 여부가 결정된다. 일반적으로 동등 비교(=)는 인덱스를 가장 효과적으로 활용할 수 있으며, 범위 검색(<, >, BETWEEN)은 인덱스 레인지 스캔을 유발할 수 있다. 또한, 인덱스 컬럼의 순서와 컬럼에 사용된 조건이 어떤 비교 연산자인지에 따라서 효율이 달라질 수 있다.
SELECT * FROM dept_emp WHERE dept_no='d002' AND emp_no >= 10114;INDEX(dept_no, emp_no), INDEX(emp_no, depth_no) 각각에 대해서 위 쿼리가 어떻게 실행될 지에 대해서 알아보자.
첫 번째 케이스에 대해서는 dept_no='d002' AND emp_no >= 10114 조건의 레코드를 찾고 dept_no=’d002’ 가 아닌곳까지 쭉 인덱스를 읽으면 된다. 어차피 dept_no='d002'라는 조건을 만족해야 조건에 맞는 레코드를 찾을 수 있는데 이미 첫번째 인덱스는 dept_no로 정렬되어 있기 때문이다.
두 번째 케이스에 대해서는 emp_no >= 10114 AND dept_no='d002' 인 레코드를 쭉 찾고 찾은 레코드들에서 dept_no='d002'가 만족하는지 필터링 해야한다. 인덱스는 emp_no를 통해 정렬이 되어있고, 쿼리 옵티마이저는 emp_no범위를 만족하는 레코드가 dept_no=’d002’인지 모른다. 그래서 우선 emp_no 범위에 맞게 모든 레코드를 확인해보아야 하는 것이다. 즉 여기서 dept_no는 비교 작업 범위를 줄이는데 아무런 도움도 하지 못하였고, 쿼리 조건에 맞는지 검사하는 용도로만 사용되었다.
추가적으로, LIKE 검색의 경우, 와일드카드 위치에 따라 인덱스 사용 여부가 달라질 수 있다. 예를 들어 LIKE 'abc%'는 인덱스를 사용할 수 있지만, LIKE '%abc'는 인덱스를 사용할 수 없다. 왼쪽부터 한글자씩 비교해가면서 일치하는 레코드를 찾아야하는데, %abc는 왼쪽이 고정되지 않았기에 찾을 수 없는 것이다.
참조
Real MySQL 8.0 (1권)
https://escapefromcoding.tistory.com/777
MySQL Ascending index vs Descending index
https://www.alibabacloud.com/blog/deep-dive-into-mysql-indexing-strategies_601595
https://ksh-coding.tistory.com/123
https://utsman.github.io/mysql/2017/07/26/mysql-index/
https://rachel0115.tistory.com/entry/MySQL-%EC%9D%B8%EB%8D%B1%EC%8A%A4-INDEX-%EC%A0%95%EB%A6%AC-%EB%8F%99%EC%9E%91-%EB%B0%A9%EC%8B%9D-%EC%83%9D%EC%84%B1-%EC%82%AD%EC%A0%9C-%EC%84%A4%EA%B3%84
