[MySQL] 인덱스 정리

인덱스란?

• 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가지고 오려면 시간이 오래 걸립니다. 따라서 컬럼의 값과 해당 레코드가 저장된 주소를 key-value 형태로 인덱스를 만들어 두는 것입니다.
• DBMS의 인덱스는 주어진 컬럼의 값을 기준으로 정렬해서 보관합니다.
• 인덱스는 INSERT, UPDATE, DELETE의 성능을 희생하고, 그 대신 읽기(Select)의 성능을 높이는 기능이다.
  • 새로운 키를 추가하는 과정에서 B-Tree의 리프 노드가 꽉 찼을 경우, 리프 노드가 분리되어야 하는데 해당 작업은 상위 노드들까지 영향을 미치기 때문에 비용이 많이 드는 작업이기 때문입니다.
  • 인덱스는 SELECT 뿐만이 아니라 UPDATE, DELETE를 처리하기 위해 해당 레코드를 검색하는 경우에도 사용됩니다.
  • 하지만 SELECT 쿼리 문장의 WHERE 조건절에 사용되는 컬럼이라 해서 전부 인덱스를 생성하면, Command의 성능이 떨어지고, 인덱스의 크기가 비대해서 오히려 역효과가 날 수 있습니다.

 

인덱스의 중요성을 알기 위해 salaries 테이블로 간단한 테스트를 진행해 보겠습니다.

salaries 테이블

salaries 테이블의 index

해당 상태에서 인덱스가 걸리지 않은 salary 컬럼으로 검색을 진행해 보았습니다.

SELECT * FROM salaries WHERE salary = 77303;

검색 시간이 5s를 넘어가는 것을 알 수 있습니다. 여러번 실행해 보았을 때, 대부분 4s 후반에서 5s의 검색 시간이 걸렸습니다.

해당 쿼리의 실행 계획을 확인해 보니, type이 ALL인 것을 확인해볼 수 있습니다.

 

해당 테이블에 salary 컬럼을 인덱스로 추가해 보겠습니다.

그리고 다시 아래의 검색을 진행해 보았습니다.

SELECT * FROM salaries WHERE salary = 77303;

검색 시간이 126ms이므로, 1/40만큼 시간이 단축되었습니다.

실행 계획을 다시 확인해 보니, type이 ref로 변한 것을 알 수 있습니다.

 

해당 결과에서 알 수 있듯이 인덱스는 조회 성능에서 매우 중요한 것임을 알 수 있습니다.

---

B-Tree 인덱스

인덱스 알고리즘은 여러 가지가 있지만, 대표적으로 B-Tree와 Hash 알고리즘이 있습니다.

• B-Tree 인덱스 알고리즘
  • 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘
  • 인덱스의 Key 값은 정렬되어 있지만, 데이터 파일의 레코드는 정렬되어 있지 않고 임의의 순서로 저장돼 있다.
    • 하지만 InnoDB 테이블에서 레코드는 클러스터되어 디스크에 저장되므로 기본적으로 프라이머리 키 순서로 정렬되어 저장된다.
• Hash 인덱스 알고리즘
  • 컬럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘. 매우 빠른 검색을 지원.
  • 값을 변형해서 인덱싱하므로, 전방(Prefix) 일치와 같이 값의 일부만 검색하거나 범위 검색할 때는 해시 인덱스를 사용할 수 없다.

 

B-Tree는 최상단에 Root Node가 존재하고, Branch Node, Leaf Node(가장 하단) 순으로 하위에 자식 노드가 붙어 있는 형태입니다.

B-Tree 인덱스의 구조

• 인덱스 검색은 Root ➔ Branch ➔ Leaf ➔ 디스크 저장소 순서대로 진행됩니다.
• 데이터베이스 성능 튜닝은 디스크 I/O를 얼마나 줄이느냐가 관건입니다.
• 인덱스는 테이블의 키 컬럼만 가지고 있기 때문에, 나머지 컬럼을 읽기 위해서는 데이터 파일에서 해당 레코드를 찾아가야 합니다.
• InnoDB는 프라이머리 키를 주소처럼 사용하기 때문에, 인덱스를 통해 레코드를 읽을 때는 데이터 파일에서 바로 찾을 수 없습니다.
  • 따라서 프라이머리 키 인덱스를 한번 더 검색한 후, 프라이머리 키 인덱스의 Leaf 페이지에 저장돼 있는 레코드를 읽습니다.

B-Tree 인덱스 스캔

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

• 검색해야 할 인덱스의 범위가 결정됐을 때 사용합니다.
• 검색을 시작해야 하는 위치를 찾으면, 그때부터는 리프 노드의 레코드만 순서대로 읽으면 됩니다.
  • 이때 디스크 저장소에서 레코드를 읽어 오는데, 레코드 한 건마다 랜덤 디스크 I/O가 발생합니다.
    • 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 됩니다.
  • 즉, 인덱스를 통해 데이터 레코드를 읽는 것은 비용이 많이 드는 작업이기 때문에, 데이터를 통해 읽어야 하는 레코드가 20 ~ 25%를 넘으면 인덱스를 통해 읽는 것은 비효율적인 방식입니다.

인덱스 풀 스캔 (Index Full Scan)

• 인덱스의 처음부터 끝까지 탐색하는 방식입니다.
• 쿼리가 인덱스에 명시된 컬럼만으로 처리가 가능한 경우(테이블 풀 스캔보다는 효율적이므로)에 사용된다.

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

//인덱스가 (gender, birth_date)인 경우

SELECT * FROM employees WHERE birth_date >= '2010-01-01'

• MySQL 8.0 이전에는 인덱스가 (gender, birth_date)인 경우 위의 쿼리는 인덱스를 효율적(인덱스 풀 스캔)으로 사용할 수가 없었습니다.
  • 하지만 MySQL 8.0 이후 부터는 옵티마이저가 gender 컬럼을 건너뛰어서 birth_date 컬럼만으로도 효율적인 인덱스 검색이 가능해주는 인덱스 스킵 스캔 최적화가 도입되었습니다.
  • MySQL 옵티마이저는 gender 컬럼에서 유니크한 값을 모두 조회하고, 주어진 쿼리에 gender 컬럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리합니다.
• 인덱스 스킵 스캔은 인덱스 선행 컬럼이 가진 유니크한 값의 개수가 적을 때만 가능한 최적화이다.
  • 유니크한 값이 많을 경우, 인덱스에서 스캔을 시작해야 할 지점을 찾는 데에 비용이 많이 들게 된다.

B-Tree 인덱스 - 인덱스를 사용할 수 없는 경우

1. NOT-EQUAL로 비교한 경우 (”<>”, “NOT IN”, “NOT BETWEEN”, “IS NOT NULL”)
2. LIKE ‘%??’, LIKE ‘%??%’
3. 데이터 타입이 서로 다른 비교 (WHERE char_column = 10)
4. 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교하는 경우
   • WHERE SUBSTRING(column, 1, 1) = ‘X’

다중 컬럼 인덱스(Multi-Column Index)

인덱스 스킵 스캔의 예제에서 봤던 **(gender, birth_date)**처럼 2개 이상의 컬럼을 포함하는 인덱스를 다중 컬럼 인덱스라 합니다.

• 다중 컬럼 인덱스에서 중요한 점은 **(gender, birth_date)**처럼 2개의 컬럼으로 인덱스를 구성한 경우, 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있다는 점입니다.
  • 따라서 인덱스 내에서 각 컬럼의 순서가 중요합니다.

클러스터링 인덱스

• 프라이머리 키값이 비슷한 레코드끼리 묶어서 저장하는 것을 말합니다.
• 테이블당 1개만 생성 가능합니다. (프라이머리 키)
• 프라이머리키가 없는 경우, InnoDB는 아래의 우선 순위로 프라이머리키를 대체할 키를 찾습니다.
  • 프라이머리키가 있으면, 기본적으로 프라이머리 키를 클러스터링 인덱스로 선택
  • NOT NULL 옵션의 UNIQUE INDEX 중에서 첫 번째 인덱스를 선택
  • 자동으로 UNIQUE한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후, 해당 컬럼을 인덱스로 선택
• 클러스터링 인덱스는 엄청난 혜택이므로, 해당 인덱스를 사용할 수 있도록 프라이머리 키를 명시적으로 지정하는 것이 좋습니다.
• row의 위치를 알고 있다.

클러스터링 인덱스 장단점

• 장점
  • 프라이머리키로 검색할 때 처리 성능이 매우 빠르다.
  • 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스 만으로 처리 될 수 있는 경우가 많다. (커버링 인덱스)
• 단점
  • 세컨더리 인덱스를 통해 검색할 때, 프라이머리 키로 다시 한번 검색해야 해서 성능이 느리다. (row의 위치를 모르기 때문)
  • 테이블의 모든 세컨더리 인덱스가 클러스터링 인덱스를 갖기 때문에, 클러스터링 키 값의 크기가 클 경우 인덱스의 크기가 커지게 된다.

---

쿼리 튜닝 경험

번외로 프로젝트에서 데이터를 증설한 후, 쿼리 실행 계획 type이 변경되는 일을 경험했었는데요.

기존 테이블은 100개 이하의 적은 데이터들이 저장되어 있었습니다. 밑의 사진은 기존 테이블에서 쿼리 실행 계획을 확인했을 때의 결과입니다.

인덱스 풀 스캔을 타고 있는 경우도 있어서 효율적이라고 할 수는 없겠지만, 적어도 테이블 풀 스캔은 없었습니다.

하지만 이후 데이터가 10-20만 개 정도 증설된 테이블에서 같은 쿼리를 다시 실행하니, (데이터 증설 땡큐 베루스)

type이 ALL(테이블 풀 스캔)인 경우가 있었습니다. 실행 결과도 2s에 가까울 정도로 매우 매우 느렸습니다.

 

따라서 쿼리 개선을 진행했고, 이후 type이 index와 ALL인 경우가 제거되었습니다. (index, ALL  ➔ ref)

쿼리 조회 시간도 1.8s  ➔ 0.25s로 7배 이상 개선되었습니다.

추측으로는 기존 쿼리에서는 옵티마이저가 테이블 풀 스캔을 하는 것이 더 효율적이라고 판단한 것 같습니다.

 

데이터가 많아진 경우에는 같은 쿼리라도 쿼리 실행 계획이 달라질 수 있으니, 서비스 배포 전에 테스트를 진행해보면 좋을 것 같습니다. 

 

 

---

References

우아한테크코스 강의

백은빈 이성욱, Real MySQL 8.0, 2021