MySQL Index

인덱스?

예전 신입 면접 필수질문 리스트에서 봐왔던 데이터베이스의 아주 중요한 지식 중 하나이다.

앞전에도 계속 설명했듯이 InnoDB 를 사용하고 있다면 인덱스에 대한 지식이 정말 중요하다.

 

일단 Index 가 성능과 밀접한 연관이 있는 이유를 설명하기 위해서는 간단하게라도 디스크 I/O를 설명해야 한다.

 

랜덤 I/O 그리고 순차 I/O

일단 랜덤 I/O가 일어나면 원하는 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽어오는 작업이 발생한다. 그럼 순차 I/O 는 어떨까? 

순차 I/O 도 마찬가지로 원하는 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽어오는 작업이 발생한다.

 

그런데 두 읽기 방식에는 어떤 차이가 있을까? 

바로 System Call 을 몇번 하는가에 차이가 있다.

RealMySQL 사진 첨부

위에 보이는 그림과 같이 3개의 데이터를 메모리에 담기 위해서 순차 I/O 는 시스템콜을 한번 불렀지만

랜덤 I/O 는 SystemCall 을 3배나 더 많이 호출했다.

즉, 디스크의 헤더가 3배더 많이 움직인 것이다.

아까도 말했듯이 우리는 이 디스크의 헤더의 움직임을 최소화 시키는 것이 성능적으로 최적화를 할 수 있는 방법으로 공부했었다.

 

우리가 주로 인덱스를 사용하면 랜덤 I/O 방식으로 대다수가 처리된다.

그래서 우리가 해야할 것은 랜덤 I/O 자체를 줄여주는 것이다. 즉 쿼리가 꼭 필요하게 읽어야 되는 정보만 읽도록 도와주는 것이다.

이것이 바로 쿼리 튜닝이라고 할 수 있다고 생각한다.

 

그렇다면 우리는 어떻게 인덱스를 이용해서 성능 최적화를 해낼수 있을까?

인덱스

인덱스는 항상 책의 목차페이지나, 단어 사전의 단어와 페이지가 Mapping 되어 있는 페이지에 비교된다.

단어사전의 단어와 페이지가 Mapping 되어있는 페이지가 사실 내 생각엔 MySQL의 인덱스와 엄청 흡사하다고 생각한다.

 

일단 Index 는 항상 정렬되어 있는 자료구조이다. 즉 Sorted List 형태의 자료구조를 지니게 된다.

Sorted List 의 단점은 데이터의 삽입/수정/삭제 가 일어나게 되면 순서를 재 정렬해야 하기에 이 부분에서 오버헤드가 발생한다.

장점으로는 항상 정렬되어 있기에 읽어올때 상당히 빠르다는 것이다. 

 

쉽게 생각하면, 우리가 사전에서 알파벳 순으로 정렬되어 있을때 훨씬 찾기 빠른걸 생각하면 된다.

 

그래서 결론적으로는 INSERT / UPDATE / DELETE 비용을 증가시키지만, SELECT 시의 비용이 절감된다는 것이다.

따라서 INDEX 를 잘 사용해야 하는 이유가 위와 같다. SELECT 비중이 엄청 낮은데 인덱스를 마구잡이로 걸게되면 CUD 비용이 증가하여 오히려 성능적으로 악영향을 끼칠 수 있게 된다.

B- Tree 인덱스

B-Tree 인덱스는 데이터베이스의 인덱싱 알고리즘 가운데 가장 많이 사용되는 자료구조형태 중 하나이다.

인덱스는 아래와 같은 형태를 뛰게 된다.

직접그림

일단 루트노드는 제일 상단에 위치하는 노드이다. 

루트노드는 자식노드를 가르키는데 여기서는 브랜치 노드를 가르키고 있다.

 

브랜치노드는 루트노드도 아니고 리프노드도 아닌 노드들을 의미한다.

즉, 최상단과 최하단의 중간 부분은 모조리 브랜치 노드라고 생각하면 편하다.

 

브랜치노드와 루트노드 모두 자식페이지를 가르켜야 하기때문에 자식페이지의 주소를 알고있다.

 

리프노드는 위에서 그림을 보면 알수 있듯이, 실제 물리 저장장치를 찾아가기 위한 주소값을 적어두었다.

파일 시스템의 데이터는 순서대로 정렬되어 있지 않을 수 있다. (다만 InnoDB 처럼 클러스터를 구성하고 저장하는 경우에는 key 값으로 정렬되어 있을 수 있다.. 언제나 예외는 있다.)

인덱스 키 추가 오버헤드

위의 그림이나 설명에서도 알수 있듯이, 인덱스를 사용하고 있다면 새로운 값의 추가로 인해 인덱스에 키를 추가해야 할 일이 생길 것이다.

이때마다 현제 페이지의 사이즈가 부족하다면 리프노드가 분리되야 하는 등 여러가지 오버헤드가 발생할 수 있는 작업들이 생긴다. 

그래서 보통 인덱스에 키를 추가하는 작업은 1.5 배 정도 작업이 더 든다고  한다.

물론 평균적인 것이다. 인덱스를 잘 못 사용하고 있다면 이보다 더 큰 비용을 소모할 수도 있다.

 

MyISAM 이나 Memory 저장소 엔진을 사용하게 되면 보통 바로 B-Tree 에 인덱스 값을 반영하게 되서

해당 인덱스 키 추가 작업이 끝날때까지 Client 에 쿼리 결과를 늦게 전달해주게 된다.

 

그래서 delay-key-write 파라미터를 설정하게 되면 지연처리를 할 수 있는데, 이는 또 동시작업되는 환경에서는 적절하진 않다.

그래서 InnoDB 에서는 이를 적절히 처리하는데.. 이 방법에 대해서 알아보도록 하자.

InnoDB 의 인덱스 오버헤드 작업

대부분의 기업이 MySQL 5.7 버전 정도를 사용하고 있는 것으로 알고 있는데 (아니라면 죄송합니다😅 )

MySQL 5.5 버전 이상 부터 DELETE 쿼리에도 버퍼를 통해서 인덱스 키 삭제 작업을 지연처리가 가능하게 변했다.

그래서 "innodb_change_buffering" 이라는 파라미터를 통해서 키 추가작업과 키 삭제작업 중 어떤 것을 지연처리할지 설정할 수 있다.

인덱스 키 삭제

인덱스 키 삭제의 경우, 해당 키값이 저장된 B-Tree 리프노드를 찾아 그냥 삭제만 하면 된다.

이렇게 삭제 된 공간은 방치되거나 재활용 될수 있다.

이 과정또한 디스크 I/O 가 발생하는 작업이다.

인덱스 키 추가

인덱스 키 추가의 경우 아까 추가 오버헤드에서 설명했던 내용을 다시보면 될듯하다.

인덱스 키 변경

이건 만약 인덱스가 가르키는 데이터의 값이 수정이 일어나 리프노드 페이지의 위치를 변경해야 한다면 일어날 수 있는 작업이다.

요것 또한 변경하는 것만으로는 작업이 불가능해서, 삭제 -> 추가의 과정을 진행하게 된다. 

인덱스 키 검색

우리가 위의 오버헤드 비용을 감수하고도 인덱스를 운용하는 이유는 빠른 검색을 위해서이다.

사실 위에서 UPDATE, DELETE 가 느려질 수 있다고 했는데 사실 빠를 수도 있다. 

그 이유는 우리가 특정 ROW 를 DELETE 하거나 UPDATE 한다고 했을때, 그 ROW 를 찾아야 만 한다.

이 과정에서 사용하기 좋은 적절한 인덱스가 있다면 검색에 인덱스를 이용하게 된다.

 

근데 앞전에도 설명한적이 있듯이 인덱스를 잘못 운용하면 UPDATE / DELETE 시에 무수히 많은 ROW 에 LOCK 이 걸릴 수 있다.

그럼 어느 곳에 인덱스를 걸어야 하는가?

이건 상황에 따라 다른데 어떤 경우에 인덱스가 효율적인지를 알아보면 될 듯 하다.

선택도(기수성)

데이터베이스를 공부해보았다면 Cardinality 라는 말을 들어본 적이 있을 것이다.

단어의 간략한 뜻은 모든 인덱스키값 가운데 유니크한 값의 수를 의미한다. 

쉽게 예를 들면 주민등록번호 100개가 있으면 100개가 전부 유니크 할테고, 사람 이름을 유니크로 잡으면 100개가 모두 유니크하지 않을 것이다.

 

이 경우에 주민등록번호는 카디널리티가 높다고 표현하며, 사람의 이름은 카디널리티가 주민번호에 비해 상대적으로 낮다고 표현한다.

인덱스는 카디널리티가 높을 수록 검색 대상이 줄어들기 때문에 그만큼 성능상의 이점을 볼 수 있다.

 

예를 들어 1만건의 country   데이터가 있는 테이블이 있다고 해보자.

 

A Case : country 컬럼의 유니크한 값의 수가 10개

B Case : country 컬럼의 유니크한 값의 수가 1000개 

 

이렇게 됬을때 A Case 의 경우에는 하나의 값을 찾기 위해서는 평균적으로 1000개의 ROW 를 탐색할 것이다.

반대로 B Case 의 경우에는 하나의 값을 찾기 위해 10개 ROW 를 탐색할 것이다.

 

즉 A Case 와 B Case 는 999개의 쓸모없는 ROW VS 9개의 쓸모없는 ROW 를 봐야한다는 성능적 차이가 있다.

계속해서 언급했듯이 우리는 랜덤 I/O 를 감소시켜야 한다.

즉 최대한 필요한 ROW 만 볼 수 있도록 인덱스를 설계하거나, 쿼리를 설계해야 한다는 뜻이다.

 

끝마치며

오늘은 인덱스의 구조? 그리고 기본적인 것들에 대해서 공부한 것들을 정리해 보았다.

사실 인덱스는 현업에서도 진짜 중요하게 잘 걸어야 하니깐.. DBA 가 없는 회사를 가게된다면 자신이 잘 판단해야 하는 상황이 올 수 있다.

내용을 공부했다면 TEST DB 를 하나 만들어서 직접 테스트 해보길 바란다.

 

그래야 감이 점점 온다. 어떤 상황에 인덱스를 걸어야 하는지

꼭 직접 해보길 바란다!