[Database] 인덱스(Index)

땡글이·2023년 5월 4일

목차

인덱스란?

B-Tree 인덱스

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

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

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

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

클러스터링 인덱스

유니크 인덱스

외래키

인덱스란?

인덱스는 가장 쉽게 비유하자면, 책의 마지막에 있는 "찾아보기" 혹은 "색인"으로 비유할 수 있습니다. 책의 내용은 데이터 파일에 해당하고, 알아낸 책 페이지 번호는 데이터 파일에 저장된 레코드의 주소로 비유됩니다. 그리고 "찾아보기"와 인덱스의 또다른 공통점은 정렬입니다. 인덱스도 정렬되어 데이터를 관리하고, "찾아보기"도 책 번호를 정렬해서 보여줍니다.

근데 정렬을 하게되면, 정렬하기 위한 비용이 발생합니다. 즉, 인덱스는 정렬을 통해 읽기(SELECT) 성능은 향상시키지만, 쓰기(INSERT, UPDATE, DELETE) 성능은 희생시킵니다.

하지만, 인덱스는 읽기 성능의 향상 이외에도, UPDATE의 동시성을 위한 기능도 제공하고 있습니다. 이에 대한 설명은 해당 포스팅을 참고해주세요.

B-Tree 인덱스

B-Tree 인덱스는 가장 범용적으로 사용되는 인덱스 알고리즘입니다. 일반적으로 DBMS에서 B-Tree의 변형인 B+-Tree 또는 B*-Tree 도 사용됩니다.

B-Tree 인덱스 구조 및 특성

B-Tree 는 트리 구조로 최상위에 루프 노드(Root nod), 중간에 브랜치 노드(Branch node), 최하단에 리프 노드(Leaf node) 로 이루어져 있습니다.

그리고 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있습니다. 아래의 그림은 B-Tree 인덱스의 각 노드와 데이터 파일의 관계를 나타냅니다.

위의 그림과 같이 인덱스의 키 값은 모두 정렬되어 있지만, 데이터 파일의 레코드는 정렬되어 있지 않습니다. 많은 사람들이 INSERT 된 순서대로 저장된다고 생각하지만, 아닙니다. 레코드를 전혀 삭제하지 않거나 변경하지 않는다면 순서대로 저장되지만, 레코드가 삭제되어 빈 공간이 생기면 INSERT는 빈 공간을 활용해 저장됩니다.

InnoDB 테이블은 기본적으로 레코드가 클러스터링되어 디스크에 저장되므로 PK 순으로 정렬되어 저장됩니다.
"클러스터링" 이란 비슷한 값을 최대한 모아서 저장하는 방식을 의미합니다.

인덱스의 리프 노드는 데이터 파일에 저장된 레코드 주소를 가집니다. InnoDB 테이블에선 리프 노드가 레코드 주소 값으로 논리적인 주소인 PK 값을 가집니다. (PK는 제외, 클러스터링 인덱스(PK)의 리프 노드는 실제 데이터 파일의 주소를 가리킵니다)

그렇기에 InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 PK를 저장하고 있는 B-Tree를 다시 한번 검색해야 합니다.

단, MyISAM 테이블은 실제 레코드 주소 값(ROWID)을 가리킵니다.
InnoDB 테이블에선 PK가 ROWID 의 역할을 합니다.

ROWID?

MyISAM 테이블에서 저장되는 레코드는 모두 ROWID 라는 물리적인 주솟값을 가지는데, MyISAM 테이블에 대한 B-Tree 인덱스의 리프노드는 ROWID를 가리킵니다.

논리적인 주소를 가져서 오히려 성능저하가 아닌가?

그렇다고 볼 수 있다. InnoDB 스토리지 엔진에서는 세컨더리 인덱스의 페이지에서 클러스터링 인덱스를 다시 한 번 검색해야하기 때문입니다.
그런데 왜 이렇게 구현되었을까? 검색 성능을 높이기 위함입니다. MyISAM에서는 insert, update 되더라도 데이터가 처음에 저장된 물리적인 위치는 바뀌지 않습니다. 그렇기 때문에 추후에 insert, update가 발생하게 되면 데이터들의 물리적인 위치가 이곳저곳에 흩뿌려지는 상황이 발생합니다. 그래서 랜덤I/O가 발생하게 됩니다.
하지만 InnoDB의 경우에는 insert, update, delete 시에 클러스터링 인덱스의 값이 바뀌면 데이터의 물리적인 위치를 바꾸면서 데이터베이스의 쓰기 성능을 희생시키면서 검색 성능을 향상시켰습니다. 이렇게 클러스터링 인덱스에 따라 순차적으로 물리적인 위치를 가지게 되면 순차I/O가 발생해 검색 성능의 향상을 얻게 됩니다.
랜덤I/O와 순차I/O에 대해서는 해당 포스팅을 참고해주세요. :)

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

테이블의 레코드를 저장하거나 변경하는 경우 인덱스 키 추가나 삭제 작업이 발생합니다. 인덱스 키 추가나 삭제는 내부적으로 어떻게 동작하는지 살펴보겠습니다.

인덱스 키 추가

새로운 키 값이 B-Tree 에 저장될 때, InnoDB의 경우에는 인덱스 키 추가 작업을 지연시켜서 나중에 처리할 수 있습니다. 하지만, PK나 유니크 인덱스의 경우에는 중복 체크가 필요하기 때문에, 즉시 B-Tree에 추가하거나 삭제해줘야 합니다.

인덱스 추가를 지연시킬 수 있는 방법은 InnoDB의 체인지 버퍼를 통해 가능합니다.

자세한 내용은 체인지버퍼에 대한 포스팅을 참고해주세요.

인덱스 키 삭제

B-Tree의 키 값 삭제는 해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서, 삭제 마크를 해주면 작업이 완료됩니다. 하지만 인덱스 키 삭제를 하기 위해 마킹을 하는 작업도 디스크 I/O에 해당하므로 비싼 작업입니다.

하지만 InnoDB는 이 또한 버퍼링하여 지연 처리할 수 있습니다. 이 지연처리 또한 체인지버퍼를 통해 가능합니다.

인덱스 키 변경

사실 인덱스의 키 값은 그 값에 따라 저장될 리프 노드의 위치가 결정되므로 단순히 인덱스 상의 키 값을 변경하는 것은 불가능합니다.

그래서 B-Tree의 키 값 변경 작업은 먼저 키 값을 삭제한 후에, 다시 새로운 키 값을 추가하는 형태로 처리됩니다. 이 작업도 위와 마찬가지로 버퍼링되어 지연처리될 수 있고 이를 가능하게 해주는 것은 체인지버퍼입니다.

인덱스 키 검색

INSERT, UPDATE, DELETE 작업을 할 때 인덱스 관리에 따르는 추가비용을 감당하면서 인덱스를 구축하는 이유는 빠른 검색을 위해서입니다.

인덱스 검색은 루프 노드에서 시작해서 브랜치 노드를 거쳐 리프 노드까지 이동하면서 비교 작업을 수행합니다. 이 과정을 트리 탐색이라고 합니다.

인덱스 트리 탐색은 SELECT 에서만 사용되는 것이 아니라, UPDATE, DELETE 를 처리하기 위해서 항상 해당 레코드를 먼저 검색해야할 경우에도 사용됩니다.

B-Tree를 이용한 인덱스 트리 탐색은 언제 사용될까?

100% 일치를 따지는 조건절
값의 앞부분 일치를 따지는 조건절
부등호(<, >) 비교 조건

그럼 B-Tree를 이용한 인덱스 트리 탐색은 언제 사용되지 못할까?

키 값의 뒷부분 일치를 따지는 조건절
키 값에 변형이 가해진 경우의 조건절

인덱스에 대한 오해

그리고 인덱스에 대해 많은 사람들이 오해하고 있는 부분이 있습니다. 인덱스는 SELECT만을 위한 것이 아닙니다.
UPDATE나 DELETE 문장이 실행될 때, 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠그기 때문에, 인덱스의 설계는 매우 중요합니다. 해당 포스팅의 "인덱스와 잠금" 부분을 참고해주세요.

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

B-Tree 인덱스는 인덱스를 구성하는 칼럼의 크기와 레코드의 건수, 그리고 유니크한 인덱스 키 값의 개수 등에 의해 검색이나 변경 작업의 성능이 영향을 받습니다.

인덱스 키 값의 크기

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

그리고 인덱스도 마찬가지로 페이지 단위로 관리되며, 루트와 브랜치, 리프 노드를 구분한 기준이 바로 페이지 단위입니다.

B-Tree는 Binary Tree가 아닌 Balanced Tree입니다. 즉, 자식 노드의 개수는 가변적입니다. 자식 노드를 몇 개까지 가질 수 있는지는 인덱스의 페이지 크기와 키 값의 크기에 따라 결정됩니다.

페이지 크기를 16KB이고, 인덱스 키의 크기가 16바이트라 하면, 총 $585개 (16 * 1024 / (16 + 12))$ 의 키를 저장할 수 있습니다.
- 12바이트는 인덱스 키와 대응되는 자식노드주소 혹은 데이터들을 의미합니다.
위의 상황에서 인덱스 키의 크기가 32바이트로 되면? 총 $372개(16 * 1024 / (32 + 12))$ 의 키를 저장할 수 있습니다.

만약, 실행한 SELECT 쿼리가 500개의 레코드를 읽어야할 때, 전자는 하나의 인덱스 페이지만 조회하면 되지만, 후자는 2개의 인덱스 페이지를 조회해야해서 2번 이상 디스크로부터 읽어와야합니다.

결국, 인덱스를 구성하는 키 값의 크기가 커지면, 디스크로부터 읽어야하는 횟수가 늘어나고, 그만큼 느려진다는 것을 의미합니다.

선택도 (카디날리티)

인덱스에서 선택도(Selectivity) 또는 카디날리티(Cardinality)는 거의 같은 의미로 사용되며, 모든 인덱스 키 값 가운데 유니크한 값의 개수를 의미합니다.

인덱스 키 값 가운데 중복된 값이 많아질수록(선택도가 낮아지면), 검색 대상이 많아져서 인덱스의 효율성이 줄어들게 됩니다.

그래서 인덱스를 설계할 때에 선택도(카디날리티) 또한 고려해야할 중요한 요소이니 여러 요소들과 복합적으로 고려해서 판단해 인덱스를 설계해야 합니다.

읽어야하는 레코드의 건수

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고, 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업입니다.

예를 들어 테이블의 레코드가 100만건 있을 때 50만 건을 읽어야 하는 쿼리가 있다면, 이 작업은 전체 테이블을 모두 읽어서 필요없는 50만 건을 버리는 것이 효율적일지, 인덱스를 통해 50만 건만 읽어오는 것이 효율적일지 판단해야합니다.

일반적인 DBMS의 옵티마이저는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업으로 예측한다고 합니다.

즉, 인덱스르 통해 읽어야할 레코드의 건수가 전체 테이블의 20~25%ㄹ르 넘어서면 인덱스를 사용하지 않고 모두 직접 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리하는 것이 효율적입니다.

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

MySQL이 인덱스를 이용하는 대표적인 방법 4가지에 대해 살펴보겠습니다.

인덱스 레인지 스캔 (Index Range scan)

인덱스 레인지 스캔(Index Range scan)은 인덱스 접근 방법 중 가장 대표적인 접근 방법입니다. 뒤에 언급될 인덱스 스캔 방식들에 비하면 빠른 방법입니다.

인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식입니다.

위의 그림은 인덱스 레인지 스캔의 기본적인 동작 과정입니다.

루프 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아들어가서 레코드의 시작 지점 찾는다 (이 과정을 스캔이라고 함)
시작 노드를 찾으면, 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아서 다시 스캔
최종적으로 멈춰야 할 위치에 다다르면, 지금까지 읽은 레코드를 사용자에게 반환하고, 쿼리를 끝냄

하지만 위의 동작과정은 실제 인덱스만을 읽는 경우를 서술한 것입니다.

실제로는 리프 노드를 스캔하면서 실제 데이터 파일의 레코드를 읽어와야 하는 경우도 많습니다. 해당 과정을 살펴보겠습니다.

(루트 노드와 브랜치 노드로부터 시작 지점은 구했다고 가정)
리프 노드에서 참조하고 있는 데이터 파일의 주소를 알아냄
알아낸 데이터 파일 주소마다 랜덤 I/O 발생

즉, 인덱스 레인지 스캔 과정을 정리해보자면 다음과 같습니다.

인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다 (이를 인덱스 탐색(Index seek)이라고 함)
탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다 (이를 인덱스 스캔(Index scan)이라고 함. 위의 과정과 통틀어서 인덱스 스캔이라 하기도 함)
읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

인덱스 풀 스캔 (Index Full scan)

인덱스 풀 스캔(Index Full Scan)은 인덱스 레인지 스캔과 마찬가지로, 인덱스를 사용하지만 인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식입니다.

대표적으로 쿼리의 조건절에서 사용된 컬럼이 인덱스의 첫 번째 칼럼이 아닌 경우 인덱스 풀 스캔 방식을 사용합니다. 또한, 쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우 주로 이 방식이 사용됩니다.

예를 들어, (A,B,C) 컬럼 순으로 인덱스가 만들어졌는데, 조건절에서 B 컬럼이나 C컬럼으로 검색하는 경우가 이에 해당합니다.
A 컬럼에 대해 인덱스 풀 스캔 발생

하지만, 인덱스뿐만이 아니라 데이터 파일의 레코드까지 읽어야 한다면 절대 인덱스 풀 스캔을 사용하지 않습니다. 왜? 레코드까지 읽게 되면 테이블 풀 스캔보다도 성능이 안좋아지기 때문입니다.

즉, 인덱스만을 사용하는 쿼리에서 인덱스 풀 스캔이 발생합니다. 그리고 일반적으로도 인덱스만을 사용해서 쿼리를 처리하는 것이 가장 좋습니다. 데이터 레코드까지 탐색해야 한다면, 레코드마다 랜덤 I/O가 발생해 성능 저하가 야기됩니다!

인덱스 풀 스캔은 인덱스 레인지 스캔보다는 느리지만, 테이블 풀 스캔보다는 효율적입니다.

루스 인덱스 스캔(Loose Index scan)

루스 인덱스 스캔(Loose Index scan)은 말 그대로 듬성듬성하게 인덱스를 읽는 것을 의미합니다.

루스 인덱스 스캔(Loose Index scan)은 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리합니다.

일반적으로 GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우에 사용됩니다.

인덱스 스킵 스캔(Index Skip scan)

데이터베이스 서버에서 인덱스의 핵심은 값이 정렬되어 있다는 점입니다. 이로 인해 인덱스를 구성하는 컬럼의 순서가 매우 중요합니다.

시나리오를 가정해보며, 인덱스 스킵 스캔에 대해 알아보겠습니다.

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

우선, (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';

해당 인덱스를 효율적으로 사용하려면, WHERE 절에 gender에 대한 비교 조건이 필수입니다. 그래서 첫 번째 쿼리는 인덱스를 사용하지 못하지만, 두 번째 쿼리는 인덱스를 사용할 수 있습니다.

조금 더 자세히 얘기하자면, 첫번째 쿼리는 인덱스는 사용하지만 효율적으로 사용하지 못하는 인덱스 풀 스캔을 하게 됩니다.

MySQL 8.0 버전부터는 옵티마이저가 gender 컬럼을 건너뛰어서 birth_date 컬럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔(Index Skip scan) 최적화 기능이 도입됐습니다.

루스 인덱스 스캔과 비슷하지만, 루스 인덱스 스캔은 GROUP BY 작업을 처리하기 위해 인덱스를 사용하는 경우에만 적용 가능
인덱스 스킵 스캔은 WHERE 조건절의 검색을 위해 사용 가능하도록 용도가 확장됨

위의 그림들은, 첫번째 쿼리의 동작과정과 실행계획을 보여주는 그림들입니다. 쿼리의 실행계획에서 "type" 값이 "range"로 표시되어 있는데, 이는 필요한 부분만 읽었다는 것을 의미합니다.

그리고 Extra 컬럼에 대해 "Using index for skip scan" 이라는 문구가 표시되어있는데, 인덱스 스킵 스캔을 활용해서 데이터를 조회했다는 것을 의미합니다.

MySQL 옵티마이저는 gender 컬럼에서 유니크한 값('M', 'F')을 모두 조회해서 주어진 쿼리에 대해 gender 컬럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리합니다.

그렇기에 첫 번째 쿼리는 다음과 같이 2개의 쿼리로 실행된 것입니다.

-- 이전 쿼리
mysql> SELECT * FROM employees WHERE birth_date >= '1965-02-01';

-- 옵티마이저가 바꾼 쿼리
mysql> mysql> SELECT * FROM employees WHERE gender = 'M' AND birth_date >= '1965-02-01';
mysql> mysql> SELECT * FROM employees WHERE gender = 'F' AND birth_date >= '1965-02-01';

인덱스 스킵 스캔은 MySQL 8.0 버전에 새로이 도입된 기능이라 아직 다음과 같은 단점들이 있습니다.

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

즉, 인덱스 스킵 스캔은 인덱스의 선행 컬럼이 가진 유니크한 값의 개수가 소량일 때만 적용 가능한 최적화라는 점을 기억해야 합니다.

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

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

그래야만 쿼리의 조건을 최적화하거나, 역으로 쿼리에 맞게 인덱스를 최적으로 생성할 수 있습니다.

그래서 어떤 조건에서 인덱스를 사용할 수 있고 어떤 조건에서 사용할 수 없는지 알아보겠습니다.

비교 조건의 종류와 효율성

동등 비교 인지 아니면 크다 또는 작다 같은 범위 조건인지에 따라 각 인덱스 컬럼의 활용 형태가 달라지며, 그 효율 또한 달라집니다. 다음 예제를 한 번 살펴보겠습니다.

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

이 쿼리를 위해, dept_emp 테이블에 각각 컬럼의 순서만 다른 두 가지 케이스로 인덱스를 생성했다고 가정해보겠습니다.

케이스 A의 인덱스 : (dept_no, emp_no)
케이스 B의 인덱스 : (emp_no, dept_no)

위의 쿼리를 실행하면, 케이스 A의 인덱스는 인덱스를 이용해 쭉 읽기만 하면 됩니다. 즉, 조건을 만족하는 레코드가 5건이면 5건의 레코드를 찾는 데 필요한 5번의 비교 작업만 수행한 것이므로 상당히 효율적으로 인덱스를 사용합니다.

하지만, 케이스 B의 인덱스는 모든 레코드에 대해 dept_no 가 'd002'인지 비교하는 과정을 거쳐야합니다. 이처럼 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 취사선택하는 작업을 필터링이라고 합니다. 필터링을 통해, 비교 작업이 더 늘어나게 되므로 성능이 저하됩니다. 인덱스만을 이용해서 원하는 레코드를 얻는 것이 가장 좋은 방법이고 가장 빠릅니다.

케이스 A 인덱스에서의 두 조건(dept_no='d002', emp_no >= 10114)과 같이 작업의 범위를 결정하는 조건을 작업 범위 결정 조건이라 합니다.

케이스 B 인덱스의 dept_no='d002' 조건과 같이 비교 작업의 범위를 줄이지 못하고 단순히 거름종이 역할만 하는 조건을 필터링 조건 또는 체크 조건이라고 표현합니다.

즉, 작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높이지만, 체크 조건은 많다고 해서 쿼리의 처리 성능을 높이지는 못합니다.

인덱스의 가용성

B-Tree 인덱스의 특징은 왼쪽 값에 기준(Left-most)해서 오른쪽 값이 정렬되어있다는 것입니다.

여기서 왼쪽이란 하나의 컬럼 인덱스뿐만 아니라, 다중 컬럼 인덱스에 대해서도 함께 적용됩니다.
A INDEX (first_name), B INDEX (dept_no, emp_no)

인덱스는 하나의 컬럼으로 검색해도 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능하다는 특징을 가집니다.

또한, 다중 컬럼 인덱스에서도 왼쪽 컬럼의 값을 모르면, 인덱스 레인지 스캔을 사용할 수 없습니다.

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

위의 쿼리는 인덱스 레인지 스캔 방식으로 인덱스를 이용할 수 없습니다. 왜? 왼쪽 값이 주어지지 않았기 때문입니다. B-Tree 인덱스는 왼쪽 기준(Left-most) 정렬 기반의 인덱스라, 정렬 우선순위가 낮은 뒷부분의 값만으로는 인덱스의 효과를 얻을 수 없습니다.

mysql> SELECT * FROM dept_emp WHERE emp_no>=10144;

인덱스가 (dept_no, emp_no) 순으로 생성되었다면, 인덱스의 선행 컬럼인 dept_no 조건 없이 emp_no 값으로만 검색하면 인덱스를 효율적으로 활용할 수 없습니다.

2개의 예제를 WHERE 절로만 살펴봤지만, 인덱스의 왼쪽 값 기준 규칙은 ORDER BY, GROUP BY 절에도 마찬가지로 똑같이 적용됩니다.

가용성과 효율성 판단

B-Tree 인덱스의 특성상 다음 조건에서는 사용되지 못합니다. 여기서 사용되지 못한다는 작업 범위 결정 조건으로 사용할 수 없다는 것을 의미하고, 경우에 따라서는 체크 조건으로 인덱스를 사용할 수 있습니다.

NOT-EQUAL 로 비교된 경우
LIKE '%??' 형태로 뒷부분으로 문자열 패턴이 비교된 경우
스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우
NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
데이터 타입이 서로 다른 비교 (char 형과 int 형 비교)
문자열 데이터 타입의 콜레이션이 다른 경우

시나리오 가정

INDEX (column_1, column_2, column_3, ... column_n) 가 있다고 가정해보겠습니다.

-- // 인덱스 사용X.
mysql> .. WHERE column_1 <> 2

-- // 인덱스 사용O. column_1 부터 column_2까지 작업 범위 결정조건으로 사용됨
mysql> .. WHERE column_1 = 1 AND column_2 > 10

-- // 인덱스 사용O. column_1, column_2, column_3까지 작업 범위 결정조건으로 사용됨
mysql> .. WHERE column_1 IN (1,2) AND column_2 = 2 AND column_3 <= 10

-- // 인덱스 사용O. column_1, column_2, column_3까지 작업 범위 결정조건으로 사용됨. 
-- // column_4 는 체크 조건으로 활용됨.
mysql> .. WHERE column_1 IN (1,2) AND column_2 = 2 AND column_3 IN (10,20,30) 
		AND column_4 <> 100

-- // 인덱스 사용O. column_1, column_2, column_3, column_4까지 작업 범위 결정조건으로 사용됨. 
mysql> .. WHERE column_1 = 1 AND column_2 IN (2,4) AND column_3 = 30 
		AND column_4 LIKE '김승%'

-- // 인덱스 사용O. column_1, column_2, column_3, column_4, column_5까지 작업 범위 결정조건으로 사용됨. 
mysql> .. WHERE column_1 = 1 AND column_2 = 2 AND column_3 = 3 
		AND column_4 = '김승환' AND column_5 = '서울'

클러스터링 인덱스

MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것들끼리 묶어서 저장하는 형태로 구현됩니다.

이는 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에 착안한 것입니다.

그리고 MySQL에서 클러스터링 인덱스는 프라이머리 키(PK)에 대해서만 적용되는 내용입니다. 즉, PK 값이 비슷한 레코드들끼리 묶어서 저장하는 것을 클러스터링 인덱스입니다.

정리하자면, 프라이머리 키(PK) 값에 의해 레코드의 저장 위치가 결정된다는 것을 의미합니다. 또한 PK 값이 변경되면 해당 레코드의 물리적인 저장 위치가 변경됩니다.

일반적으로 InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 PK 기반의 검색이 매우 빠르며, 대신 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느립니다.

주의🔥 PK 에 대한 B-Tree에서는, 리프 노드의 "데이터"는 실제 레코드의 칼럼 값들이며, 세컨더리 인덱스 페이지의 리프 노드는 PK 값을 가진다.

세컨더리 인덱스에 미치는 영향

MyISAM 이나 MEMORY 스토리지 엔진으로 만들어진 테이블은 클러스터링 기능을 제공하지 않아서 INSERT 될 때 처음 저장된 공간에서 절대 이동하지 않습니다.

그래서, 세컨더리 인덱스 페이지에서도 실제 레코드의 저장위치인 ROWID 를 저장합니다.

그렇다면 InnoDB 테이블에서 세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있다면 어떻게 될까요?
클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경되고 그 때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야 할 것입니다. 이런 오버헤드를 줄이기위해, InnoDB는 레코드가 저장된 실제 주소가 아니라 논리적인 주소로 프라이머리 키(PK) 값을 저장합니다.

클러스터링 인덱스의 장단점

장점

PK로 검색할 때 처리 성능이 매우 빠름
- 특히 범위 검색의 경우에 매우 빠름.
- 왜? 실제 레코드의 위치도 비슷한 곳에 저장되어 있기 때문!
테이블의 모든 세컨더리 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많다
- 이를 커버링 인덱스라고 함

단점

테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
- 인덱스의 크기가 커지면, 페이지에 담을 수 있는 레코드의 개수가 적어지고 하나의 명령으로 여러 개의 페이지를 읽게 됨
세컨더리 인덱스를 통해 검색할 때 PK 로 다시 한 번 검색해야하므로 처리 성능이 느려짐
INSERT 할 때, 레코드의 저장위치가 결정되기 때문에, 저장위치를 결정하는 작업이 추가적으로 실행돼 처리 성능 느려짐
PK를 변경할 때, 레코드를 DELETE 하고, INSERT 하는 작업이 필요하기 때문에 처리 성능이 느림.

클러스터링 인덱스 키의 크기

앞의 클러스터링 인덱스의 장단점 에서 언급했듯이 클러스터링 인덱스(PK)가 커지면, 세컨더리 인덱스는 PK 값을 포함하므로 세컨더리 인덱스의 크기도 자동으로 커집니다.

아래 예시를 보겠습니다. 아래 예시는 5개의 세컨더리 인덱스를 가지는 테이블의 PK 가 10바이트인 경우와 50바이트 인경우를 비교한 예시입니다.
예시에서 알 수 있듯이, PK는 40바이트만 커졌지만, 전체적으로는 191MB 나 증가했습니다. 만약 1000만 레코드라면, 1.9GB가 증가합니다. 그렇기에 PK의 크기에 대해서도 상황을 고려해서 선택해야 합니다.

PK 크기	레코드당 증가하는 인덱스 크기	100만 건 레코드 저장시 증가하는 인덱스 크기
10바이트	10바이트 * 5 = 50바이트	50바이트 * 1,000,000 = 47MB
50바이트	50바이트 * 5 - 250바이트	250바이트 * 1,000,000 = 238MB

유니크 인덱스

유니크는 사실 인덱스라기보다는 제약 조건에 가깝습니다. MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없습니다.

즉, 유니크 제약을 설정하면 자동으로 인덱스가 생성됩니다.
MySQL에서 PK는 기본적으로 NULL을 허용하지 않는 유니크 속성이 자동으로 부여됩니다.

유니크 인덱스와 세컨더리 인덱스의 비교

인덱스 읽기와 쓰기 성능 관점에서 유니크 인덱스와 유니크하지 않은 세컨더리 인덱스를 비교해보겠습니다.

인덱스 읽기

많은 사람이 유니크하지 않은 세컨더리 인덱스는 몇 건 더 레코드를 읽어야하고, 유니크 인덱스는 1건의 레코드만 읽으면 되기 때문에 유니크 인덱스가 더 빠르다고 생각하지만, 반은 맞고 반은 틀립니다.

유니크하지 않은 세컨더리 인덱스에서 한 번 더 해야하는 작업은 디스크 읽기가 아니라 CPU 에서 컬럼 값을 비교하는 작업이기 때문에 이는 성능상 영향이 거의 없습니다.

즉, 디스크 읽기가 아닌 InnoDB 버퍼 풀에서 읽어야할 레코드가 조금 더 많아도 성능 상 차이가 크게 차이나지 않습니다.

인덱스 쓰기

새로운 레코드가 INSERT 되거나 인덱스 컬럼의 값이 변경되는 경우에는 인덱스 쓰기 작업이 필요합니다.

유니크 인덱스의 키 값을 쓸 때에는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요해서 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느립니다.

중복체크가 오래 걸리는 이유는 무엇인가?

MySQL에서 유니크 인덱스에서 중복된 값을 체크할 때에는 읽기 잠금을 사용하고, 쓰기를 할 때에는 쓰기 잠금을 사용하는데 이 과정에서 데드락(Deadlock) 이 자주 발생합니다.

또한 인덱스 키의 저장을 버퍼링하기 위해 체인지버퍼(Change Buffer) 가 사용되는데, 유니크 인덱스는 중복 체크를 해야하기 때문에 버퍼링하지 못합니다.

외래키

MySQL 에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스까지 생성됩니다.

외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없습니다.

InnoDB의 외래키 관리에는 중요한 두 가지 특징이 있습니다.

테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)가 발생
외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합(잠금 대기)를 발생시키지 않음

시나리오로 이해해보기

예시를 들며 이해해보겠습니다.

자식 테이블의 변경이 대기하는 경우

작업번호	커넥션-1	커넥션-2
1	BEGIN;
2	UPDATE tb_parent SET fd='cc' WHERE id = 2;
3		BEGIN;
4		UPDATE tb_child SET pid=2 WHERE id=100;
5	ROLLBACK;
6		Query OK, 1 row affected

(작업번호 2) 1번 커넥션에서 id가 2인 레코드에 대해 쓰기 잠금을 획득
(작업번호 4) 부모 테이블의 변경 작업이 완료될 때까지 대기
(작업번호 5) 1번 커넥션에서 잠금 해제하고, 대기 중이던 작업 처리
(작업번호 6) 2번 커넥션에서 작업 마무리

부모 테이블의 변경 작업이 대기하는 경우

작업번호	커넥션-1	커넥션-2
1		BEGIN;
2		UPDATE tb_child SET fd='cc' WHERE id=100;
3	BEGIN;
4	DELETE FROM tb_parent WHERE id=1;
5		ROLLBACK;
6	Query OK, 1 row affected

(작업번호 2) 2번 커넥션에서 부모 키 "1"을 참조하는 자식 테이블의 레코드를 변경하는 명령으로 tb_child 테이블의 레코드에 대해 쓰기 잠금 획득
(작업번호 4) 1번 커넥션이 tb_parent 테이블에서 id가 1인 레코드를 삭제하는 쿼리는 tb_child 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 대기
- 외래키의 특성(ON DELETE CASCADE)이 활성화된 경우, 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되기 위해 대기하는 것입니다.
(작업번호 5) 2번 커넥션에서 잠금 해제하고, 대기 중이던 작업 처리
(작업번호 6) 1번 커넥션에서 작업 마무리

외래 키를 물리적으로 생성하려면, 이러한 현상으로 인한 잠금 경합까지 고려해 모델링을 진행하는 것이 좋습니다.
외래키를 생성하면, 자식 테이블에 레코드가 추가되면, 해당 참조키가 부모 테이블에 있는지 확인하게 됩니다. 이런 체크 작업을 위해 연관 테이블에 읽기 잠금을 걸게 됩니다.

하지만 이렇게 잠금이 다른 테이블로 확장되면 그만큼 전체적으로 쿼리의 동시처리에 영향을 미치므로 동시처리성이 떨어지게 됩니다. 따라서 모델링을 할 때, 동시처리 성능과 같은 부분들도 고려해서 모델링을 해야합니다.

Reference

Real MySQL 8.0.1
https://velog.io/@evelyn82ny/B-Tree-index-feat-difference-from-B-plus-Tree
https://velog.io/@hyunrrr/인덱스Index-정복기-2

땡글이

꾸벅 🙇‍♂️ 매일매일 한발씩 나아가자잇!

이전 포스트

순차(Sequential) I/O와 랜덤(Random) I/O

다음 포스트