MySQL 8.0 인덱스에 대한 고찰

일단 데이터베이스의 성능 튜닝의 관건은 어떻게 디스크 I/O를 줄일 것이냐 일 때가 상당히 많다. CPU나 메모리는 대부분 전자식 장치지만 하드 디스크 드라이브는 기계식 장치이다. 그래서 데이터베이스 서버에서는 항상 디스크 장치가 병목이 된다. 물론 플래시 메모리를 장착하여 디스크의 성능을 높인 SSD를 장착한다면 병목은 하드 디스크 보다는 줄어들겠지만, 디스크의 헤더를 움직이지 않은 순차 데이터 처리의 경우에는 두 디스크가 큰 성능 차이를 보이진 않는다.

 

인덱스란?

인덱스는 디스크 혹은 메모리에 저장되어 있는 레코드에 빠르게 접근하기 위해 "정렬된" 데이터들이다. 인덱스는 정렬되어 있어야 한다 라는 중요한 특성을 가진다. 인덱스가 정렬되어 있지 않다면 인덱스의 데이터가 늘어남에 따라 탐색속도는 점점 저하될 것이다. 따라서 인덱스는 보통 B-Tree, InnoDB는 B+-Tree로 인덱스를 정렬하여 저장한다.

 

B-Tree는 BST를 기반으로 노드에 하나 이상의 값을 저장할 수 있는 구조로 이루어진 트리이다. B+-Tree는 리프 노드를 LinkedList 형식으로 연결하여 순차 탐색 속도를 높였다.

 

인덱스가는 정렬되어 있으니 탐색속도가 아주 빠르다는 장점을 가지고 있지만 이 세상의 모든 기술이 그러하듯이 인덱스도 장점만 존재하진 않는다. 정렬되어 있는 데이터에 CUD 작업이 수행된다면, 데이터를 재정렬하는 작업을 수행하거나, 데이터를 삽입하는 데 오랜 시간이 걸릴 것이다. 이렇게 인덱스는 데이터의 SELECT 속도를 높이고 INSERT, UPDATE, DELETE 성능을 희생하는 기능이다.

정리: 인덱스는 정렬되어 있기에 데이터 수정 작업의 성능을 희생하고 탐색 속도를 높이는 기능이다. 따라서 저장 속도를 어느 정도까지 희생할 수 있는지, 읽기 속도를 얼마나 더 빠르게 만들어야 하는지에 따라 인덱스의 개수가 결정된다.

 

 

인덱스를 사용하면 왜 검색속도가 올라갈까?

여러가지 이유가 있겠지만 가장 큰 장점은 테이블 풀 스캔을 하지 않아도 된다는 것이다. 테이블 풀 스캔이란 디스크에 접근하여 저장되어 있는 순차적인 데이터를 탐색하는 방식이다. 순차적인 데이터는 특정 데이터가 없음을 증명해야 할 때, 모든 데이터를 탐색해야 하는 비효율적인 성능이 나온다. 테이블에 1억개의 데이터가 있다면, 데이터가 없다는 사실을 판단하기 위해 1억번의 비교 연산이 필요하다는 것이다. 이런 경우 인덱스를 사용하면 비교적 크기가 작고, 정렬되어 있고, 빠른 탐색이 가능한 데이터 셋에서 탐색 속도를 높일 수 있는 것이다.

 

 

인덱스와 키의 크기의 상관관계

인덱스는 InnoDB 버퍼풀의 최소 관리 단위인 페이지 단위로 관리된다. 페이지의 기본 값은 16KB이다. 만약 인덱스의 키가 16바이트이고, 자식 노드의 주소가 12바이트라면 해당 인덱스는 몇 개의 자식 노드를 가질 수 있을까? 16*1024/(16+12) = 585개를 저장할 수 있다. 인덱스의 키가 커짐(혹은 많아짐)에 따라 한 페이지에 저장할 수 있는 자식 노드의 개수는 줄어드는 것이다.

 

만약 내가 작성한 SELECT 쿼리가 레코드 500개를 읽어야 한다면 위의 경우에는 인덱스가 포함된 하나의 페이지만 읽으면 해결될 수도 있지만 그렇지 않은 경우, 최소한 2번 이상 디스크 I/O(인덱스를 가져오기 위한)를 발생한다.

정리: 인덱스 키의 크기와 개수도 잘 고려해야 한다.

 

선택도(Selectivity, 기수성;Cardinarlity)

인덱스에 포함된 컬럼의 개수가 100개일 때, 여기서 유니크한 레코드의 개수가 10개라면 기수성은 10이다. 인덱스에서 중복된 값이 많아지면 많아질수록 선택도가 떨어진다. 인덱스는 선택도가 높을수록 검색 대상이 줄어들기에 검색 속도가 빨라진다는 특징이 있다. (검색 대상이 줄어들면 그만큼 디스크 랜덤 I/O 횟수가 줄어들기 때문)

 

정리: 인덱스는 유니크한 값이 많은 컬럼에 걸자. (회원 테이블의 email과 패스워드에 인덱스를 걸어 로그인 시 커버링 인덱스가 가능하도록 할 것, 혹은 오피스너에서 예약 가능한 회의실을 조회하는 쿼리에서 해당 쿼리는 예약가능한 상태인 회의실의 ID와 이름만 조회하면 되므로, 회의실 상태와, 이름을 인덱스로 설정하여 커버링 인덱스가 가능하도록 한다. 예약 가능한 회의실은 통상적으로 30% 이기에 인덱스의 손익 분기점을 넘지 못하지만 커버링 인덱스를 통해 디스크 I/O가 발생하지 않으므로 성능의 병목을 해결하기에 나쁘지 않은 방법이라고 생각한다. 하지만 고려할 점은 상태라는 값은 수정이 빈번하기에 인덱스의 재정렬으로 인한 오버헤드가 발생할 것으로 사료된다. 그렇기에 직접 부하 테스트 진행 후 결정해야 할 것으로 보인다.)

 

 

 

인덱스 사용 방식

인덱스를 걸어놨다고 해서 무조건 조회 성능이 좋아지는 것은 아니다. 인덱스를 잘 사용할 수 있는 쿼리를 작성해야 하고, 쿼리가 인덱스를 잘 사용하고 있는지 실행계획을 확인해야 한다.

만약 컬럼 A, B, C를 인덱스 키로 지정했을 때, 쿼리의 where 절의 조건은 B, C 를 비교하여 검색하는 경우 인덱스를 효율적으로 사용하지 못하고 모든 인덱스의 정보를 탐색하는 인덱스 풀 스캔으로 동작한다.

 

인덱스 레인지 스캔

인덱스 레인지 스캔은 말 그대로 인덱스를 통해 조회할 레코드의 범위를 모두 결정할 수 있는 인덱스 사용 방식이다. 여기서 인덱스에서 디스크 I/O는 최대 탐색해야 할 레코드의 개수만큼 발생한다. 디스크를 바로 읽는 방법보다 인덱스를 통해 디스크의 레코드를 읽는 작업이 4 ~ 5배 정도 느리기에 읽어야 할 데이터의 개수가 전체 레코드의 20~25% 라면 통상적으로 인덱스를 통해 레코드를 읽는 것과 디스크에 직접 접근 하는 것의 큰 차이가 없다.

 

루스 인덱스 스캔

루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 진행되지만 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형식으로 처리한다.

select dept_no, MIN(emp_no)
from dept_emp
where dept_no between 'd002' and 'd004'
group by dept_no;

인덱스가 ASC로 정렬되어 있다고 가정했을 때, 인덱스를 탐색하여 dept_no = ‘d002’를 만나면 해당 값을 쿼리 결과에 저장한 뒤 해당 인덱스를 스킵한다. 같은 방식으로 d004까지 탐색한 뒤 결과를 반환하는 방식이다.

 

커버링 인덱스

커버링 인덱스란 클라이언트의 질의가 인덱스에 존재하는 값으로 만족할 수 있는 경우 디스크 I/O를 발생시키지 않고 버퍼 풀에서 바로 결과 값을 반환하는 방식을 말한다. 때문에 성능이 아주 우수하다.

 

 

 

인덱스와 잠금

InnoDB의 락은 레코드를 잠그는 것이 아닌 인덱스를 잠근다. 인덱스가 PK 밖에 없다면 하나의 레코드만 잠기는 것이고, 여러 레코드를 포함하는 인덱스가 있는 경우 변경해야 할 레코드를 찾기 위해 인덱스 값에 해당하는 모든 레코드를 잠근다.

 

왜 이렇게 동작할까? 효율성 때문이라고 하는데 뭔가 납득될만한 레퍼런스는 찾지 못했다. 감히 내가 예상해보자면, 인덱스에 락을 건다는 것은 인덱스 정보가 InnoDB 버퍼풀에 올라왔다는 것을 의미한다. 즉, 다른 트랜잭션이 해당 인덱스를 통해 디스크 I/O를 발생시키지 않고도 레코드에 락이 걸려있는지 빠르게 파악할 수 있기 때문이 아닐까? 사실 잘 모르겠다.

 

 

 

결론

• 인덱스는 디스크 혹은 메모리에 저장되어 있는 데이터에 빠르게 접근하기 위해 정렬되어 있는 데이터이다.
• 디스크 I/O를 줄이는 것이 쿼리 튜닝의 관건인 경우가 많다. 따라서 우리는 인덱스를 통해 디스크 I/O를 줄인다.
• 인덱스는 B+-Tree 기반으로 정렬(삽입, 수정, 탐색 모두 O(NlogN))되어 있기에 데이터 수정 작업을 희생하고 탐색 속도를 높이는 기능이다.
• 디스크에 있는 레코드를 인덱스를 통해 검색하는 경우 직접 디스크를 탐색하는 속도보다 4~5배 정도 느리다. 따라서 탐색해야 하는 레코드의 수가 전체 레코드 수의 20~25%이상 이라면 인덱스의 사용보다는 디스크 풀 스캔이 더 효율적일 수 있다. 
• 인덱스는 InnoDB 버퍼 풀에 담기기에 인덱스 키의 크기가 큰 경우 하나의 페이지에 담기지 않는 경우도 존재한다. 따라서 키의 크기와 개수를 잘 결정해야 한다.
• MySQL InnoDB는 레코드를 잠그는 방식이 아닌 인덱스를 잠그는 방식으로 동작한다. 따라서 트랜잭션 내에서 실제로 하나의 레코드만 수정하는 작업을 진행하지만 인덱스 잠금에 의해 여러 레코드가 잠길 수 있다.

 

 

 

Reference

• Real MySQL 8.0 - 1 (백은빈, 이성욱)
• Real MySQL 8.0 - 2 (백은빈, 이성욱)
• 면접 전에 알고 가면 좋을 것들 - 신입 Java 백엔드 개발자편 (널널한 개발자)