MySQL 인덱스

DB의 성능을 높이기 위한 핵심

메모리가 디스크보다 빠르기 때문에 데이터베이스에서는 디스크 접근 횟수를 최대한 줄이고 최대한 메모리에 있는 데이터로 요청을 처리하는 것이 좋다. 즉, 메모리 캐시 히트율을 높이는 것이다.

 

데이터베이스에서는 쓰기 작업 시에도 디스크에 바로 쓰는 것이 아닌 메모리에 써둔 후 일괄적으로 디스크에 저장한다. 

이때 메모리에 데이터를 써둔 상태에서 서버가 다운되면 해당 데이터가 유실되는 것이 아닌가 걱정할 수 있다.

하지만 WAL(Write Ahead Log) 방식을 사용해서 디스크에 데이터를 쓰는 것이 아닌 쿼리 로그를 미리 저장해두고 데이터가 유실되면 해당 로그를 통해 쿼리를 실행시켜 데이터의 저장을 보장할 수 있다. 

 

 

인덱스

인덱스는 정렬된 자료구조를 가지고 빠르게 탐색 범위를 줄일 수 있는 목차의 역할을 한다. 

인덱스를 사용하게 되면 해당 인덱스 컬럼과 PK값을 가지고 인덱스 컬럼으로 정렬된 인덱스 테이블이 생성된다.

이때 인덱스 테이블은 메모리에 생성되어 디스크 접근 보다 더 빠른 조회가 가능하다.

 

하지만 인덱스 테이블이 따로 생성되는 것이고 관리할 대상이 늘어난 것이기 때문에 쓰기 작업 발생 시 인덱스 테이블과 실제 테이블에 대해 2번의 쓰기가 발생하여 성능이 떨어질 수 있기 때문에 조회와 쓰기의 빈도를 고려해서 적절하게 사용해야한다.

 

 

인덱스 자료구조 비교

MySQL에서 인덱스는 B+Tree를 사용한다. 그 이유를 알아보기 위해 다른 자료형을 사용했을 때와 비교해보자 

HashMap

• Key Value 형식이기 때문에 단건 검색에 유리하다 O(1)
• HashMap은 정렬이 없기 때문에 범위 탐색은 O(N) 시간을 가진다.
• value 값을 꺼내서 확인해야하기 때문에 Like%와 같은 부분 검색이 불가능하다 

List

• 정렬되지 않은 리스트의 탐색은 O(N) 정렬된 리스트의 탐색은 O(logN)
• 정렬되지 않은 리스트: O(N), 정렬된 리스트: O(logN)의 정렬 비용을 가진다.
• 삽입, 삭제 시 기존 데이터를 앞뒤로 밀어야하기 때문에 삽입, 삭제 비용이 크다.

Tree

• 트리 높이에 따라 시간 복잡도가 결정된다. (높이를 최소화하는 것이 중요하다)
• 트리가 한쪽으로 치우치지 않도록 균형을 잡아주는 트리를 사용해야한다 (= B+ Tree, Red-Black Tree)

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

 

범위 탐색에서 HashMap 보다 유리하고, 삽입, 삭제에도 List 보다 유리하기 때문에 Tree 자료구조가 사용되며 그 중에서도 B+Tree는 이진 트리처럼 2개의 노드로만 구성되는 것이 아니기 때문에 높이 최소화에 도움이된다.

또한 리프 노드(최하단)를 제외한 나머지 노드들은 실제 데이터가 아닌 레퍼런스이기 때문에 레퍼런싱 속도도 우수하다.

 

 

인덱싱 과정

Cherry를 검색하고자 할 때 과일 이름 순으로 정렬된 인덱스 테이블에서 아래 과정으로 Cherry의 PK를 찾을 수 있다.

1. Apple < Cherry < Durian  => Apple로 접근
2. Apple <  Carrot < Cherry => Carrot 접근
3. Carrot < Cherry = Cherry => Cherry의 PK를 찾음

 

클러스터 인덱스

MySQL의 클러스터 인덱스는 PK이다.

• 클러스터 인덱스는 데이터의 위치를 결정하는 키 값으로 PK인덱스 테이블에 PK에 해당하는 데이터의 주소가 있다.
  • 익덱스 테이블에 PK를 들고있는 이유는 데이터가 추가, 삭제될 때 데이터의 주소가 변경되더라도 데이터의 주소가 아닌 PK 값을 가지고 있다면 인덱스 테이블을 변경할 필요가 없기 때문이다.  
  • 인덱스에 해당하는 PK 값을 찾은 후 PK 인덱스 테이블에서 데이터의 주소를 찾을 수 있다.
  • 반대로 말하면 인덱스(클러스터 인덱스가 아닌 보조 인덱스)만으로는 데이터의 주소를 모르기 때문에 찾을 수 없다. 
• PK 인덱스 테이블에서 새로운 키가 들어온다면 기존 키들의 주소도 변경되기 때문에 쓰기 작업에서 성능 이슈가 발생할 수 있다
  • 예를 들어 클러스터 인덱스 1,2,3,5가 있을 때 4가 추가된다면 5가 뒤로 밀리면서 주소도 변경된다.
  • 순서가 바뀔 수 있는 PK 값을 사용하면 밀리는 양이 많아질 수 있기 때문에 PK 설정은 중요하다.
    • A.I를 사용하면 PK 값이 작성 순서대로 지정되기 때문에 해당 문제에 대해 적합하다.
    • 인덱스 테이블에 PK가 필수로 들어가기 때문에 PK의 사이즈가 인덱스 테이블의 사이즈를 결정한다.
    • PK가 A.I 일 때랑 UUID일 때 성능의 차이
• 클러스터 인덱스는 PK 값이 정렬된 테이블을 가지고 있기 때문에 PK 범위 검색에 유리하고, 인덱스 검색에서 보조 인덱스가 PK를 가지고 있기 때문에 실제 테이블에서 데이터를 가져올 필요 없이 PK 인덱스 테이블만으로 데이터를 찾을 수 있기 때문에 메모리에서 데이터를 찾아줄 수 있다.
  • 메모리에 저장하기 때문에 PK 인덱스 테이블을 수용할 수 있는 용량이 크지않다.

 

 

복합 인덱스

두개의 컬럼을 하나로 묶은 인덱스로 앞선 인덱스에 대해 먼저 검사한 후 두 번째 인덱스에 대새 검사한다.

 

아래 예시에서 한국의 바나나를 찾는다면, 바나나를 먼저 찾고 한국을 찾는다.

만약 과일의 데이터보다 원산지의 데이터가 더 많다면 원산지를 먼저 찾고 과일을 찾도록 반대로 인덱스를 설정하는 것이 더 효율적이다.

오른쪽이 복합 인덱스

 

 

인덱스 사용 시 주의사항

• 인덱스 컬럼을 가공하면 인덱스가 동작하지 않는다.
  • age 컬럼이 인덱스일 때 WHERE age * 10 = 1과 같이 * 연산을 통해 가공이 들어가면 인덱스가 동작하지 않는다.
• 하나의 쿼리에는 하나의 인덱스만 적용된다.
  • age, name 두 가지 인덱스가 있을 때 옵티마이저에 의해 더 효율적인 검색이 가능한 인덱스만 적용된다.
• WHERE, ORDER BY, GROUP BY와 같은 쿼리문을 사용할 때 WHERE에서 인덱스를 탔더라도 GROUP BY나 ORDER BY에서 인덱스를 타지 않을 수 있다.
• 인덱스는 성별처럼 경우의 수가 적은 컬럼 보다 경우의 수가 많은 컬럼에 적용하는 것이 좋다. 
  • 성별의 경우 2개 밖에 없기 때문에 인덱스를 사용해서 걸러낼 수 있는 데이터의 양이 많지 않다. 따라서 인덱스를 사용하기 전 보다 더 성능이 안좋아지게 된다.

** 위와 같은 문제를 해결하기 위해 explain을 통해 어떤 인덱스를 탔는지, 시간이 얼마나 걸리는지를 확인해보는 등의 검사가 필요하다.

 

 

요약

인덱스는 목차를 통해 범위 검색의 이점과 메모리에서 데이터를 찾아줄 수 있다는 장점이 있다.

하지만 인덱스를 사용하면 쓰기 작업 시 일을 두번 해야하기 때문에 읽기와 쓰기 사이의 트레이드 오프이다.

인덱싱 과정은 인덱스 테이블에서 PK값을 찾은 후 PK 인덱스 테이블에서 데이터의 주소를 찾는 방식이다.