[Real MySQL 8.0 1권] 08. 인덱스 (2)

유혁·2026년 5월 4일

Real MySQL 8.0

목록 보기

13/22

본 포스트는 Real MySQL 8.0 1권을 읽은 뒤 정리하는 글입니다.

8.3 B-Tree 인덱스

B-Tree는 데이터베이스의 인덱싱 알고리즘 가운데 가장 일반적으로 사용되고 먼저 도입된 알고리즘이며, 아직도 가장 범용적인 목적으로 사용되는 인덱스 알고리즘이다. B-Tree는 일반적으로 DBMS에서 사용하는 B+-Tree, B*-Tree와 같은 여러 가지 변형된 알고리즘이 존재한다.

B-Tree의 "B"는 "Binary"의 약자가 아닌 "Balanced"를 의미한다.

8.3.1 구조 및 특성

B-Tree는 트리 구조의 최상위에 하나의 루트 노드(Root node)가 존재하고 그 하위에 자식 노드가 붙어 있는 형태다. 트리 구조 가장 하위에 있는 노드를 리프 노드(Leaf node), 루트 노드와 리프 노드가 아닌 중간의 노드를 브랜치 노드(Branch node)라고 한다. 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.

위 이미지는 B-Tree 인덱스의 각 노드와 데이터 파일의 관계를 보여준다. 인덱스의 키 값은 모두 정렬돼 있지만, 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장돼 있다. 데이터 파일의 레코드는 항상 INSERT된 순서대로 저장되는 것이 아니라 레코드가 삭제되어 빈 공간이 생긴 자리에 INSERT는 삭제된 공간을 재활용하도록 DBMS가 설계되어 있다.

위 그림은 MyISAM테이블의 인덱스와 데이터 파일의 관계로, "레코드 주소"는 MyISAM 테이블의 생성 옵션에 따라 레코드가 테이블에 INSERT된 순번이거나 데이터 파일 내의 위치다.

다음으로 위 그림은 InnoDB 테이블의 인덱스의 데이터 파일의 관계를 보여주는데, 여기서는 프라이머리 키가 ROWID의 역할을 한다. 두 스토리지 엔진의 가장 큰 차이점은 세컨더리 인덱스를 통해 데이터 파일의 레코드를 찾아가는 방법으로, MyISAM 테이블은 세컨더리 인덱스가 물리적인 주소를 가지는 반면 InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다고 볼 수 있다.

따라서 InnoDB 테이블에서 인덱스를 통해 레코드를 읽을 때는 인덱스에 저장돼 있는 프라이머리 키 값을 이용해 프라이머리 키 인덱스를 한 번 더 검색한 후, 프라이머리 키 인덱스의 리프 페이지에 저장돼 있는 레코드를 읽는다. 이와 같은 작업으로 InnoDB 스토리지 엔진을 사용하는 테이블은 성능이 떨어질 것처럼 보이지만 MyISAM 인덱스 구조와 InnoDB 인덱스 구조는 각각 장단점을 가지고 있다.

8.3.2 B-Tree 인덱스 추가 및 삭제

인덱스 키 추가

B-Tree에 저장될 때는 저장될 키 값을 이용해 B-Tree상의 적절한 위치를 검색해야 한다. 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다. 리프 노드가 꽉 차서 더는 저장할 수 없을 때는 리프 노드가 분리(Split)돼야 하는데, 이는 상위 브랜치 노드까지 처리 범위가 넓어져 상대적으로 쓰기 작업에 비용이 많이 소모된다.

MyISAM이나 MEMORY 스토리지 엔진을 사용하는 테이블에서는 INSERT 문장이 실행되면 즉시 새로운 키 값을 B-Tree 인덱스에 변경하지만, InnoDB는 필요에 따라 인덱스 키 작업을 지연시켜 나중에 처리할 수 있다. 하지만 프라이머리 키나 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree에 추가하거나 삭제한다.

인덱스 키 삭제

B-Tree의 키값의 삭제는 해당 키 값이 저장된 리프 노드를 찾아서 삭제 마크만 하면 작업이 완료된다. 이렇게 삭제 마킹된 인덱스 키 공간은 계속 방치하거나 재활용할 수 있다. 인덱스 키 삭제로 인한 마킹 작업 또한 디스크 쓰기가 필요하므로 디스크 I/O가 필요한 작업이다. MySQL 5.5 이상 버전의 InnoDB 스토리지 엔진에서는 이 작업 또한 버퍼링되어 지연 처리될 수도 있다.

인덱스 키 변경

B-Tree의 키 값 변경 작업은 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리된다. 키 값의 변경 때문에 발생하는 키 값의 삭제와 추가 작업은 위에서 설명한 절차대로 처리된다.

인덱스 키 검색

인덱스를 검색하는 작업은 B-Tree 루트 노드부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하며 비교 작업을 수행하는 트리 탐색 과정을 거친다. 인덱스 트리 탐색은 SELECT에서만 사용하는 것이 아닌 UPDATE, DELETE를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우에도 사용된다. B-Tree 인덱스를 이용하는 검색은 100% 일치 또는 값의 일부분만 일치하는 경우에 사용할 수 있다. 부등호 비교 조건에서도 활용할 수 있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없다. 또한 인덱스의 키값에 변형이 가해진 후 비교되는 경우에는 이미 변형된 값은 B-Tree 인덱스에 존재하는 값이 아니므로 절대 빠른 검색 기능을 사용할 수 없다. 따라서 함수나 연산을 수행한 결과로 정렬한다거나 검색하는 작업은 B-Tree의 장점을 이용할 수 없으므로 주의해야 한다.

InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락(갭락)이 검색을 수행한 후 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어 UPDATE나 DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠글 수 있어 인덱스를 설계할 때 주의해야 한다.

8.3.3 B-Tree 인덱스 사용에 영향을 미치는 요소

인덱스 키 값의 크기

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 또는 블록(Block)이라고 하며, 디스크의 모든 읽기 및 쓰기 작없의 최소 단위이자 버퍼 풀에서 데이터를 버퍼링하는 기본 단위로, 인덱스도 페이지 단위로 구분된다.

일반적으로 DBMS의 B-Tree는 자식 노드의 개수가 가변적인 구조로, MySQL의 B-Tree의 자식 노드 개수는 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다. 인덱스의 키가 16바이트라고 가정하면 아래 이미지같이 인덱스 페이지가 구성될 것이다. 자식 노드 주소라는 것은 여러 가지 복합적인 정보가 담긴 영역이며, 페이지의 종류별로 대략 6바이트에서 12바이트까지 다양한 크기 값을 가질 수 있다. 여기서는 편의상 자식 노드 주소 영역이 평균적으로 12바이트로 구성된다고 가정한다.

위 이미지의 경우 하나의 인덱스 페이지(페이지 기본값 16KB)에 $16*1024/(16+12)=585$ 개의 키(자식노드)를 가질 수 있다.

인덱스의 키 값의 크기가 두 배인 32바이트로 늘어났다고 가정한다면 한 페이지에 인덱스 키를 $16*1024(32+12) = 372$ 개 저장할 수 있다.

만약 SELECT쿼리가 레코드 500개를 읽어야 하는 경우 키 값의 크기가 32바이트라면 최소한 2번 이상 디스크로부터 읽어야 한다. 따라서 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다는 것을 의미한다.

B-Tree 깊이

B-Tree의 깊이(Depth)는 MySQL에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결되는 문제다. 인덱스 키 값의 크기가 커지면 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어지고, 그 때문에 같은 레코드 건수라 하더라고 B-Tree의 깊이(Depth)가 깊어져서 디스크 읽기가 더 많이 필요하게 된다는 것을 의미한다.

즉 인덱스 키 값의 크기는 가능하면 작게 만드는 것이 좋으며, 실제로 아무리 대용량 데이터베이스라도 B-Tree의 깊이(Depth)가 5단계 이상까지 깊어지는 경우는 흔치 않다.

선택도(기수성)

인덱스에서 선택도(Selectivity)또는 기수성(Cardinality)은 거의 같은 의미로 사용되며, 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 전체 인덱스 키 값은 100개인데, 그중에서 유니크한 값의 수는 10개라면 기수성은 10이다. 인덱스 키 값 가운데 중복된 값이 많을수록 기수성과 선택도는 떨어진다. 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.

선택도가 좋지 않다고 하더라도 정렬이나 그루핑과 같은 작업을 위해 인덱스를 만드는 것이 훨씬 나은 경우도 많다. 인덱스가 항상 검색에만 사용되는 것은 아니므로 여러가지 용도를 고려해 적절히 인덱스를 설계할 필요가 있다.

읽어야 하는 레코드의 건수

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다. 따라서 인덱스를 이용한 읽기의 손익 분기점이 얼마인지 판단할 필요가 있다.

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

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

인덱스 레인지 스캔

인덱스 레인지 스캔은 인덱스의 접근 방법 중 가장 대표적인 접근 방식으로, 뒤에서 설명할 두 가지 접근 방식보다는 빠른 방법이다. 인덱스를 통해 한 건만ㅇ 읽는 경우와 한 건 이상을 읽는 경우를 각각 다른 이름으로 구분하지만, 이번 절에서는 모두 묶어서 인덱스 레인지 스캔이라고 표현했다. 다음 쿼리를 예제로 살펴보자.

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

인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식으로, 검색하려는 값의 수나 검색 결과 레코드 건수와 관계없이 레인지 스캔이라고 표현한다. 위 이미지의 화살표처럼 루트 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어가야만 비로소 필요한 레코드의 시작 지점을 찾을 수 있으며, 이후에는 리프 노드의 레코드만 순서대로 읽으면 된다. 이처럼 차례대로 쭉 읽는 것을 스캔이라고 표현한다. 스캔 중 리프노드의 끝까지 읽으면 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아 다시 스캔하고, 스캔을 멈춰야 할 위치에 다다르면 지금까지 읽은 레코드를 사용자에게 반환하고 쿼리를 끝낸다.

위 이미지는 인덱스 레인지 스캔을 통한 데이터 레코드 읽기 과정으로, B-Tree 인덱스에서 루트와 브랜치 노드를 이용해 스캔 시작 위치를 검색하고, 그 지점부터 필요한 방향(오름차순 또는 내림차순)으로 인덱스를 읽어나가는 것을 확인할 수 있다. 중요한 것은 어떤 방식으로 스캔하든 관계없이, 해당 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다는 것이다. 이는 별도의 정렬 과정이 수반되는 것이 아니라 인덱스 자체의 정렬 특성 때문에 자동으로 그렇게 된다.

또 한 가지 중요한 것은 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다는 것이다. 이때 리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데, 레코드 한 건 단위로 랜덤 I/O가 한 번씩 발생한다. 위 이미지처럼 3건의 레코드가 검색 조건에 일치한 경우 랜덤 I/O가 최대 3번 필요한 것이다. 그래서 인덱스를 통해 데이터 레코드를 읽는 작업은 비용이 많이 드는 작업으로 분류된다.

인덱스 레인지 스캔 과정을 3단계로 분류하면 아래와 같다.

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

쿼리가 필요로 하는 데이터에 따라 3번 과정은 필요하지 않을 수도 있는데, 이를 커버링 인덱스라고 한다. 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되기 때문에 랜덤 읽기가 상당히 줄어들고 성능은 그만큼 빨라진다.

인덱스 풀 스캔

인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다. 위 이미지는 인덱스 풀 스캔의 예로, 인덱스 리프 노드의 제일 앞 또는 뒤로 이동한 후, 리프 노드를 연결하는 링크드 리스트를 따라서 처음부터 끝까지 스캔한다. 쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우 주로 이 방식이 사용된다.

루스 인덱스 스캔

루스 인덱스 스캔이란 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미하며, 루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시(SKIP)하고 다음으로 넘어가는 형태로 처리한다. 일반적으로 GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우에 사용된다.

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

위 쿼리에서 사용된 dept_emp 테이블은 dept_no와 emp_no라는 두 개의 칼럼으로 인덱스가 생성 및 정렬되어 있어 위 이미지와 같이 dept_no 그룹별로 첫 번째 레코드의 emp_no 값만 읽으면 된다. 즉 인덱스에서 WHERE 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다.

인덱스 스킵 스캔

mysql> ALTER employees 
		ADD INDEX ix_gender_birthdate (gender, birth_date);
        
-- // 인덱스를 사용하지 못하는 쿼리
mysql> SELECT * FROM employees WHERE birth_date>='1965-02-01';

-- // 인덱스를 사용할 수 있는 쿼리
mysql> SELECT * FROM employees
		WHERE gender='M' AND birth_date>='1965-02-01';

기존에는 gender 칼럼과 birth_date 칼럼으로 생성된 인덱스를 생성했을 때, 인덱스의 조건을 모두 가진 두 번째 SELECT 쿼리는 인덱스를 효율적으로 사용할 수 있지만, 첫 번째 SELECT 쿼리는 gender 칼럼에 대한 비교 조건이 없어 인덱스를 사용할 수가 없었다.

MySQL 8.0 버전부터는 옵티마이저가 인덱스 내의 필요한 부분만 읽어 검색이 가능하게 해주는 인덱스 스킵 스캔(Index skip scan) 최적화 기능이 도입되었다. 위 이미지는 ix_gender_birthdate 인덱스에 대한 인덱스 스킵 스캔 처리 과정을 보여준다. 우선 gender 칼럼에서 유니크한 값('M', 'F')을 모두 조회해서 주어진 쿼리에 gender 칼럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리한다.

인덱스 스킵 스캔은 두 가지 제약 조건을 만족하는 경우 사용하는 것이 좋으며, 충족하지 않는 경우에는 오히려 쿼리의 처리 성능이 더 느려질 수 있다.

WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 함
쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야 함(커버링 인덱스)

8.3.5 다중 칼럼(Multi-column) 인덱스

두 개 이상의 칼럼으로 구성된 인덱스를 다중 칼럼 인덱스(복합 칼럼 인덱스)라고 하며, 2개 이상의 칼럼이 연결됐다고 해서 Concatenated Index라고도 한다.

위 그림은 다중 칼럼 인덱스의 구조로, 편의상 루트 노드는 생략했으나 실제로 데이터 레코드 건수가 작은 경우에는 브랜치 노드가 없는 경우도 존재한다. 이 이미지에서 중요한 것은 두 번째 칼럼이 첫 번째 칼럼에 의존해서 정렬되어 있다는 것으로, 두 번째 칼럼의 정렬은 첫 번째 칼럼이 똑같은 레코드에서만 의미가 있다는 것이다. 다중 칼럼 인덱스에서는 인덱스 내에서 각 칼럼의 위치(순서)가 상당히 중요하다.

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

인덱스의 정렬

MySQL 5.7 버전까지는 칼럼 단위로 정렬 순서를 혼합해서 인덱스를 생성할 수 없었으나, 8.0 버전부터는 다음과 같은 형태의 정렬 순서를 혼합한 인덱스도 생성할 수 있게 됐다.

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

인덱스의 스캔 방향

인덱스 생성 시점에 오름차순 또는 내림차순으로 정렬이 결정되지만 쿼리가 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다. 오름차순으로 생성된 인덱스를 읽으면 정순으로 읽으면 오름차순으로 정렬된 결과가, 역순으로 읽으면 내림차순으로 정렬된 결과를 얻게 되는 것이다. MySQL 옵티마이저는 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어 낸다.

내림차순 인덱스

오름차순 인덱스(Ascending index): 작은 값의 인덱스 키가 B-Tree 왼쪽으로 정렬된 인덱스
내림차순 인덱스(Descending index): 큰 값의 인덱스 키가 B-Tree 왼쪽으로 정렬된 인덱스
인덱스 정순 스캔(Forward index scan): 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드의 왼쪽 페이지부터 오른쪽으로 스캔
인덱스 역순 스캔(Backward index scan): 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드의 오른쪽 페이지부터 왼쪽으로 스캔

MySQL 서버의 InnoDB 스토리지 엔진에서 정순 스캔과 역순 스캔은 페이지(블록)간의 양방향 연결 고리를 통해 전진하느냐 후진하느냐의 차이만 있지만, 실제 내부적으로는 InnoDB에서 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수밖에 없는 두 가지 이유가 있다.

페이지 잠금이 인덱스 정순 스캔에 적합한 구조
페이지 내에서 인덱스 레코드가 단방향으로 연결된 구조 (아래 이미지)

따라서 일반적으로 인덱스를 ORDER BY ... DESC 하는 쿼리가 소량의 레코드에 드물게 실행되는 경우라면 내림차순 인덱스를 굳이 고려할 필요는 없다.

많은 쿼리가 인덱스의 앞쪽 혹은 뒤쪽만 집중적으로 읽어서 인덱스의 특정 페이지 잠금이 병목이 될 것으로 예상된다면 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 완화하는데 도움이 될 것이다.

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

비교 조건의 종류와 효율성

다중 칼럼 인덱스에서 각 컬럼의 순서와 그 칼럼에 사용된 조건이 동등 비교인지, 범위 조건인지에 따라 각 인덱스 칼럼의 활용 형태 및 효율이 달라진다. 아래 예제를 살펴보자.

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

위 SELECT 쿼리를 실행했을 때, 칼럼의 순서가 다른 두 개의 인덱스 처리 과정을 이미지로 나타냈다. 케이스 A의 인덱스는 dept_no='d002' AND emp_no>=10144인 레코드를 찾은 뒤 dept_no가 d002가 아닐 때까지 인덱스를 쭉 읽으면 된다. 이 경우에는 읽은 5건의 레코드가 모두 사용자가 원하는 결과로 상당히 효율적으로 인덱스를 이용한 것임을 알 수 있다.

그러나 케이스 B의 인덱스는 emp_no>=10144 AND dept_no='d002'인 레코드 7건을 찾은 이후 dept_no가 'd002'인 조건을 만족하는 레코드 5건을 가져온다. 즉, 5건의 레코드를 찾기 위해 7번의 비교 과정을 거친 것이다.

위처럼 다중 인덱스의 정렬 방식에 따라 효율의 차이가 발생한다. 케이스 A 인덱스에서 2번째 칼럼인 emp_no는 비교 작업의 범위를 줄이는데 도움을 주지만, 케이스 B 인덱스에서 2번째 칼럼인 dept_no는 작업의 범위를 좁히는 데 아무런 도움을 주지 못하고, 단지 쿼리의 조건에 맞는지 검사하는 용도로만 사용됐다.

작업의 범위를 결정하는 조건을 작업 범위 결정 조건, 비교 작업의 범위를 줄이지 못하고 단순리 거름종이 역할만 하는 조건을 필터링 조건 또는 체크 조건이라고 표현한다. 작업 범위 결정 조건은 많을수록 쿼리의 처리 성능을 높이지만 체크 조건은 많다고 해서 쿼리의 처리 성능을 높이지 못하며, 오히려 쿼리 실행을 더 느리게 만들 때가 많다.

인덱스의 가용성

B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬돼 있다는 점으로, 이는 다중 칼럼 인덱스의 칼럼에 대해서도 동일하게 적용된다. 이러한 특징으로 인해, 하나의 칼럼으로 검색하는 경우에 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능하다. 또한 다중 칼럼 인덱스에서도 왼쪽 칼럼의 값을 모르면 인덱스 레인지 스캔을 사용할 수 없다.

가용성과 효율성 판단

작업 범위 결정 조건으로 사용할 수 없는 조건

NOT-EQUAL로 비교된 경우(<>, NOT IN, NOT BETWEEN, IS NOT NULL)
LIKE '%??'
스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
데이터 타입이 서로 다른 비교
문자열 데이터 타입의 콜레이션이 다른 경우

MySQL에서는 NULL 값도 인덱스에 저장되어, 다음과 같은 WHERE 조건도 작업 범위 결정 조건으로 인덱스를 사용한다.
mysql> .. WHERE column IS NULL ..

다중 칼럼으로 만들어진 아래와 같은 인덱스가 있을 때 어떤 조건에서 사용될 수 있고, 어떤 경우에 절대 사용할 수 없는지 살펴보자.

INDEX ix_text (column_1, column_2, ..., column_n )

작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우

column_1 칼럼에 대한 조건이 없는 경우
column_1 칼럼의 비교 조건이 인덱스 사용 불가 조건 중 하나인 경우

작업 범위 결정 조건으로 인덱스를 사용하는 경우 ( $2 < i < n$ )

column_1 ~ column_(i-1) 칼럼까지 동등 비교 형태(=, IN)로 비교
column_i 칼럼에 대해 다음 연사자 중 하나로 비교
- 동등 비교(=, `IN)
- 크다 작다 형태 (>, <)
- LIKE로 좌측 일치 패턴(LIKE '~~%')

유혁

백엔드 개발자

이전 포스트

[Real MySQL 8.0 1권] 08. 인덱스 (1)

다음 포스트