MySQL Index 설계 방법 With Cardinality(무조건 Cardinality가 높아야 할까에 대한 고찰)

MySQL Index 설계 방법 With Cardinality(무조건 Cardinality가 높아야 할까에 대한 고찰)

Cardinality 란 무엇인가?

• 컬럼에 저장된 고유한 값의 개수
  • 즉, 전체 데이터의 개수에 비해 고유한 값의 수가 얼마나 되는지를 나타낸다.
    • 카디널리티가 높다 : 고유한 값이 많다(겹치지 않는 데이터가 많다.)
    • 카디널리티가 낮다 : 고유한 값이 적다(겹치는 데이터가 많다.)

그러면 이게 어떤 역할일까?

• 인덱스를 사용하는 목적은 검색 속도의 상승이다.
• 인덱스를 만들 때에 Cardinality 를 고려하면(인덱스를 통해 검색할 데이터의 선택도를 고려한다면) 검색 속도를 상승시킬 수 있다.
• 옵티마이저는 쿼리 문법을 분석하고 의미를 해석할 때, 테이블 크기, 행 수, 컬럼의 Cardinality, 값의 분포, 인덱스 구조 등 을 통해 통계 정보를 참조한다.
  • 즉 실행계획을 만들 때에도 Cardinality 는 실행 계획의 일부로 활용된다.

그러면 Cardinality가 높은게 좋을까 낮은게 좋을까?

• 사실 내가 이 글을 쓰게 된 이유이자, 예전에 인덱스에 대해 공부할 때 의아하게 생각했던 부분이다.
• 상황 별로 한번 고려해보자.
• 일단은 보통의 경우 Cardinality가 높은 것이 좋다고 하고, 무조건 이게 높다고 좋다고 한다.
  • 근데 나는 그게 아니라고 생각해서... 고민해 보고 글을 써본다.

단일 '=' 검색

• Cardinality가 높으면 좋다.
• 인덱스를 통해 하나의 행을 빠르게 찾아낸다. 선택도가 매우 높기 때문.

단일 '<' '>' 검색

• Cardinality가 높으면 좋다.
• B-Tree 의 경우는 정렬되어 있기 때문에 범위 검색을 한다고 해도 순서대로 찾을 수 있기 때문에 좋다.
• 또, 인덱스는 보통 필요한 데이터를 로드해서 사용한다.
  • 이 때 Cardinality가 낮다면, 그에 해당하는 것들을 모두 로드해야 하기 때문에 옵티마이저가 Full Table Scan을 선택할 수도 있다(인덱스를 타더라도 선택도가 너무 낮기 때문)

다중 조건 '=' '>' '<'가 섞였거나 등등...

• 이게 중요한데, 이 때에는 상황마다 매우 다르다.
• 쿼리 패턴을 잘 봐야 한다.
• 솔직히 이 때에는 Cardinality보다는 쿼리 패턴이 훨씬 중요하다고 생각한다.
  • 즉 복합 조건에서는 단순히 개별 컬럼의 카디널리티만 비교하는 것만으로는 최적의 인덱스 순서를 결정하기 어렵다.

뭉뚱그려서 말하기는 조금 곤란할 수 있으니 몇몇 상황을 가정해 본다.

• 국가(한국 / 미국 / 캐나다 정도로 가정), 회사(네카라쿠배당토, 현대차, MANGA 로 구성되었다고 가정), 나이(나이는 1세~99세의 사람들이 균일하게 분포되는 것으로 가정), 이름 네가지 필드를 통해 다중 인덱스를 구성한다고 할 때 어떻게 구성해야 할까?
  • 보통 말하는대로 Cardinality 가 높은 것으로 구성한다. 라고 한다면 아마 "이름-나이-회사-국가" 로 구성되어야 할 것이다.

그러면 6가지 정도의 가정을 해본다.

1. 류찬을 찾는다.
2. 20대를 찾는다.
3. 카카오를 다니는 사람을 10 명 정도 찾고 싶다.
4. 한국에 카카오를 다니는 류찬을 찾고 싶다.
5. 한국에 카카오를 다니는 20대를 N명 찾고 싶다. (근데 조건에 해당하는 데이터는 10명밖에 없을 때)

모든 경우에 Cardinality 가 중요할까?

1. 류찬을 찾는다 (단일 검색)

• 복합 인덱스를 굳이굳이 쓸 필요도 없어 보이지만 쓴다면 Cardinality 가 가장 높은 "이름" 이 앞에 있는 것이 좋다.
• 애초에 복합 인덱스가 필요가 없다.

2. 20대를 찾는다. (단일 검색)

• 이름이 있으면 이 복합 인덱스를 사용할 일이 없다.
• 사실 그냥 나이를 가지는 단일 인덱스를 하나 설계하면 간단히 해결된다.
• 1, 2의 경우는 복합 인덱스가 의미가 없다.

3. 카카오를 다니는 사람을 10 명 정도 찾고 싶다. (복합 검색)

• 여기서는 Cardinality 가 높은 이름이 앞에 있으면 오히려 손해다.
  • 이름에서 너무 분포가 많이 되어, 회사 인덱스가 의미가 없어지기 때문.
• 회사 필드가 가장 앞에 있는 것이 좋다.

4. 한국에 카카오를 다니는 류찬을 찾고 싶다.

• 이렇게 된다면 오히려 Cardinality 가 낮은 것부터 가는 것이 더 효율적으로 보인다.
• 국가-회사-이름 으로 가는 것이다. (나이는 사용되지 않는다.)
• 그렇게 해야만 데이터를 찾을 때에 범위를 효과적으로 나눌 수 있다.

5. 한국에 카카오를 다니는 20대를 나이의 오름차순으로 N명 찾고 싶다. (근데 조건에 해당하는 데이터는 10명밖에 없을 때)

• 이게 핵심이다!!
• 이게 아주 골때리는 경우라고 할 수 있다.
• 옵티마이저는 limit 만큼 데이터를 가져올 수 있는지 가져오는 단계에서는 알 수 없다.
• 여기서도 경우의 수를 나누어 보자.
  • 1명을 구한다고 한다면?
    • Cardinality가 높은 것부터 구성되는 경우(나이-회사-국가)
      • 20살부터 카카오를 다니는 사람 중 한국인을 찾는다.
        • Cardinality가 높은 20살에서 index scan이 발생하기 때문에 효율적이다.
          • limit 만큼 구성되면 더이상 찾지 않는다.
    • Cardinality가 낮은 것부터 구성되는 경우(국가-회사-나이)
      • 국가를 찾고, 회사를 찾아들어간 후 나이를 하나씩 찾는다.
        • 이 때에는 Cardinality가 낮아 최초 검색 단계에서 로드하는 데이터가 많을 수 있지만 복합 인덱스의 범위를 모두 타면 나이까지 도달하기 때문에 효율적이다.
  • 30명을 구한다고 한다면? (즉 모든 데이터를 다 살펴도 조건을 만족하지 못함)
    • Cardinality가 높은 것부터 구성되는 경우(`나이-회사-국가)
      • 20살 ~ 29살까지 모든 데이터에 대해 index full scan이 발생한다.
        • 나이의 Cardinality가 높기 때문에 괜찮을 수 있다고 생각할 수 있지만, 복합 인덱스를 사용하는 장점이 퇴색된다.
        • 20살을 찾고 -> 카카오/한국 필터링 진행.
        • 21살을 찾고 -> 카카오/한국 필터링 진행.
        • 반복~
      • 심지어 여기서는 '나이' 가 균일하게 분포되었을 것이라 가정했지만, 만약 20대의 데이터 갯수가 많다면(Cardinality는 높지만 선택하는 데이터의 선택도가 낮은 경우) 더 많은 데이터를 로드하는 문제가 발생할 수 이있다(skewed distribution)
    • Cardinality가 낮은 것부터 구성되는 경우(국가-회사-나이)
      • 앞서 국가-회사는 구해 두었고 나이에 대해 검색하기 때문에 index 검색의 대상이 되는 데이터가 확실히 적다.
      • 한국/카카오로 검색 대상을 줄인 후에 '나이'를 통한 조건 스캔이 가능하기 때문

결론

• 단일 검색의 경우 거의 무조건 Cardinality가 높은 것을 인덱스로 잡는 것이 좋다.
• 다중 검색의 경우 상황을 고려해 보는 것이 좋다.
  • 근데 이 때에도 전부 등치 검색일 때('=')에는 어지간하면 Cardinality가 높은게 앞으로 가는게 좋을 것이다.
    • 생각해보면 당연한게, 모두 등치 검색이라면 앞에서 최대한 선택도를 높이는 것이 중요하기 때문.
  • 범위 검색이 섞인다면(모두 범위 검색이라고 해도) Cardinality 보다는 쿼리 패턴을 살펴보는 것이 더 중요하다.
    • 결국 해당하는 범위가 데이터 수를 얼마나 줄일 수 있을지를 고려해 보는 것이 좋다.
    • 특히 full scan 이 발생할 수 있는 경우(전체검색 or limit이 있지만 전체 데이터보다 limit 한계가 많은 경우 등)에는 필드의 Cardinality 보다는 조건의 선택도를 더 고민해야 할 것이다.
• 당연한 얘기일수 있지만 인덱스 설계 후에는 실제로 확인을 해보자.