이전 글에서 Index 자료 구조 중 하나인 B+Tree 를 다뤘다. 이번 글에서는 B+Tree 기반의 Index 가 가지는 특징과 사용 시 고려할 점들에 대해서 다룰 예정이다.
insert, update, delete (Command)의 성능을 희생하고 대신 select (Query)의 성능을 향상시킵니다.
위 인용문과 같이 B+Tree 기반의 Index (적절히) 사용 시 데이터 조회 성능이 향상되지만 반대로 데이터 삽입, 삭제 등에는 어느정도의 희생이 수반된다. 새로운 데이터가 추가되거나 삭제될 때 인덱스 자료 구조에도 인덱스 키 추가 및 삭제 작업이 발생한다. 이렇게 데이터 추가 또는 삭제는 조회와 달리 자료 구조가 갖는 특징을 위배하게 만드는 경우가 있는데, 이러한 경우에는 추가 및 삭제 연산 외 자료 구조의 특징에 맞게 내부 데이터를 재배치 하는 작업이 추가로 발생할 수 있다. 재배치 작업은 위 인용 내용의 성능 희생과 밀접한 관련이 있다.
예를 들어, B+Tree 에서 [ 그림 1 ], [ 그림 2 ] 와 같이 특정 값을 갖는 키를 추가 또는 삭제할 때 일련의 재배치 과정이 수반될 수 있다. B+Tree 와 같이 균형을 유지하는 자료 구조 (AVL Tree, Red Black Tree 등)는 추가보다 삭제할 때 조금 더 복잡한 재배치 과정이 일어난다. B+Tree 관련 연산 과정에 대한 자세한 내용은 아래 참조한 링크를 통해 확인할 수 있다.
이러한 재배치 과정은 결국 빠른 Search 를 위한 것이고, Index 를 사용하는 목적과도 같은 맥락을 갖는다. 다만 B+Tree 기반 Index 에서 Search 시 100% 일치 또는 Index 키의 앞부분(Left-most part)만 일치하는 경우에 사용할 수 있는데, 이는 B+Tree 가 Index 로 사용하는 키 (또는 컬럼)을 기준으로 정렬하는 특징을 갖고 있기 때문이다. 따라서 Index 로 사용하는 키 값에 대한 변형 또는 Index 키의 뒷부분(Right-most part) 을 기준으로는 Index 자료 구조를 활용할 수 없다.
where salary * 10 > 150000; # Index 키 `salary` 값에 변형 (= * 10) 이 있는 경우
where salary > 150000 / 10; # 위와 같은 결과를 얻지만 Index 자료 구조를 활용할 수 있는 경우
예를 들어, 위와 같이 Index 로 지정한 키 값에 대한 변형이 있는 경우에는 Index 자료 구조를 활용할 수 없으므로 주의할 필요가 있다.
Index 사용과 관련해 여러 고려할 요소가 있다. 앞서 위에서 다룬 Index 사용 배경을 통해, 결국 Index 사용은 조회 시 성능을 높이기 위한 것으로 이해할 수 있다. 따라서 우리는 조회 성능을 저해할 수 있는 요소에는 어떤 점이 있는지 또는 반대로 조금 더 좋은 성능을 낼 수 있는 조건에는 어떤 점이 있는지에 대해 이해할 필요가 있다.
◎ Index 키 값의 크기 & B+Tree 깊이
이전 글 에서 B-Tree 와 B+Tree 의 차이를 비교하면서 B+Tree 가 갖는 장점으로 내부 노드에서 데이터 포인터를 관리하지 않아 해당 공간만큼 Index 키를 추가로 더 관리할 수 있다고 했다. 수치상으로 비교 했을 때 [ 그림 3 ] 의 한 페이지(= 노드)에서는 약 585 개의 Index 키를 가질 수 있지만 [ 그림 4 ] 에서는 약 1024 개의 Index 키를 가질 수 있었다.
위 내용과 같은 맥락으로 Index 키 값의 크기 자체도 한 페이지에서 관리할 수 있는 Index 키의 수에 영향을 끼친다.
B+Tree 깊이는 MySQL 에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결되는 문제다. 결론적으로 인덱스 키 값의 크기가 커지면 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어지고, 그 때문에 같은 레코드 건수라 하더라도 B+Tree 의 깊이가 깊어져서 디스크 읽기가 더 많이 필요하게 된다는 것을 의미한다.
그리고 위 인용 내용과 같이 결과적으로 Index 키 값의 크기가 커지면 Index 자료 구조인 B+Tree 의 깊이가 깊어지고 이는 결과적으로 더 많은 I/O 요청이 수반된다는 점에서 읽기 성능에 좋지 않은 영향을 준다.
◎ Cardinality
Cardinality 가 무엇인지 얘기하기 전에 비유를 통해 어떤 내용을 내포하고 있는지 알아보자. 예를 들어, 전교생이 2,100명인 고등학교가 있다. 이 학교의 각 학년 별로 학생 수가 모두 700명으로 동일하고, 전체 학생의 성별이 5:5 (= 각 1,000 명) 이라고 가정해보자.
위의 학교에서 우리가 어떤 학생을 찾아야 하는데, 우리에게 주어진 정보가 성별과 학년 정보만 있다고 생각해보자. 만약 우리가 찾아야하는 학생이 남학생이라고 해보자. 우리는 그러면 여학생을 제외한 나머지 1,000 명의 남학생을 대상으로만 앞으로 찾아가면 된다. 만약 학년 정보를 먼저 활용하면 어떨까? 각 학년 별로 700 명씩 있다고 했으니, 학년 정보를 활용했다면 학년 정보를 기준으로 700 명의 학생을 대상으로 검색 대상을 줄일 수 있을 것이다.
Cardinality 는 위에서 예로 든 상황과 동일한 맥락을 갖고 있다. Cardinality 는 한 테이블에서 Unique 한 값을 갖는 수를 의미한다. 성별 정보를 기준으로 Cardinality 를 계산하면 2 가 나온다. (생물학적 성별 기준) 또 다른 예로 주민번호와 같이 모두 Unique 한 값을 갖는 경우에는 데이터 수가 N 이라고 하면, Cardinality 도 N 의 값을 갖는다.
Index 를 지정할 때 해당 컬럼을 기준으로 Cardinality 가 성별 기준으로 한 것처럼 낮은 값을 가지는 것이 유리할까? 아니면 반대로 Cardinality 값이 큰 값을 가지는 것이 유리할까?
결론부터 말하면 Cardinality 가 큰 값을 가지는 것으로 Index 를 지정하는 것이 보다 효율적이다. Index 를 통해 일차적으로 데이터를 필터링 했을 때 유효 데이터가 있는 범위를 더 구체적으로 한정할 수 있으면, 더 적은 I/O 요청을 통해 원하는 데이터를 찾을 수 있기 때문이다. 따라서 한 테이블 내 여러 컬럼 중 어떤 컬럼을 Index 로 지정할 것인지에 대한 결정 시 Cardinality 값을 고려할 필요가 있다.
Real MySQL 8.0 | 8장 인덱스 mysql 인덱스 정리 및 팁 Insertion on a B+ Tree Deletion from a B+ Tree