Post

인덱스 관련 총정리

Posted Updated
By umbum 8 min read

인덱스 관련 이론

커버링 인덱스 적용하기

커버링 인덱스는 SELECT의 모든 컬럼이 인덱스에 들어있어야 적용된다.

허나 그렇지 않다고 하더라도, Data Filter 보다 Key Filter를 적용하는게 성능에 큰 도움이 된다. Data Filter 이전에 Key Filter를 넣어주면…

  • 데이터 레코드는 최종적으로 결과를 표현 할 때만 필요하기 때문에 데이터 레코드를 읽어와서 조건에 맞지 않으면 버리는 과정을 절약 할 수 있음.
  • 인덱스의 크기는 데이터 레코드의 크기에 비해 훨씬 작기 때문에, 한 block을 읽었을 때 가져올 수 있는 item의 수가 훨씬 많다. 따라서 데이터 레코드를 읽고 filtering하는 것 보다, 인덱스 단계에서 filtering 하는 것이 읽어야 하는 block 수를 절약할 수 있어 더 낫다.

NDV, 선택도, 카디날리티

  • NDV(Number of Distinct Value)
  • 선택도(selectivity)
    • 전체 레코드 중에서 조건절에 의해 선택될 것으로 예상되는 레코드의 비율(%)
    • 인덱스 스캔이 테이블 순차 스캔보다 항상 빠르지는 않다. 보통 선택도(selectivity)가 5~10% 이내인 경우에 인덱스 스캔이 우수하다.
  • 카디날리티(Cardinality)
    • (선택도 * 총 row 수)
    • 즉 해당 predicate로 선택된 row의 수. Optimizer가 plan 짤 때 참고하게 된다.
  • 인덱스는 NDV가 높아야 의미가 있다. (e.g., 성별 같이 NDV가 2이면 인덱스 거는 의미가 없다.)

복합(결합) 인덱스에서 카디날리티 차이에 따른 쿼리 실행 퍼포먼스 차이

범위 질의에서 복합 인덱스는 어떻게 되나?

1
2
3
4
5

SELECT * FROM tbl  
WHERE a > 1 AND a < 5  
AND b < 'K'  
AND c > 10000  
ORDER BY b;

https://d2.naver.com/helloworld/1155 - CUBRID에 대한 설명이지만 대부분의 RDB가 비슷할 것이다.

  • (1, 2, 3 …)은 첫번째 컬럼인 a이고, (AAA, BBB, CCC…) 는 두 번째 컬럼인 b이다. 초록색이 복합 인덱스 (a, b)를 의미한다.
    • Key Range: 인덱스 스캔 범위로 활용하는 조건이다(a > 1 AND a < 5).
    • Key Filter: Key Range에 포함할 수 없지만 인덱스 키로 처리 가능한 조건이다(b < ‘K’).
    • Data Filter: 인덱스를 사용할 수 없는 조건이다. 테이블에서 레코드를 읽어야만 처리 가능한 조건이다(c > 10000).
  • 즉… 인덱스의 선두 컬럼 a가 범위 질의이면, 컬럼 a의 범위에 해당하는 인덱스 블록을 일단 모두 읽어들인 후, 인덱스 블록 데이터에 포함된 인덱스 컬럼 b를 기준으로 filtering해서 조건에 맞지 않는 항목들을 버리는 식으로 동작한다.
    • a, b를 동시에 만족하는 인덱스 블록들만을 한번에 찾아가서 읽어들이는 것이 아니다!
    • 왜 이런 구조일까? 여기까진 잘 모르겠다.
      • 1<a<5 AND b=’DDD’ 조건일 때, 상위 인덱스가 [(2, AAA), (2, KKK), (3, AAA)…] 수준으로 나뉘어져 있다면 인덱스 블록 탐색 범위를 반으로는 줄일 수 있을 것 같은데… [(2, AAA), (3, AAA)…]만 읽으면 되니까.
      • 실제로 돌려보면 이렇게 인덱스를 검색해서 탐색하는 것 보다, 일단 a를 기준으로만 다 읽고 filtering 하는 방식이 더 빠르기 때문일까? 아니면 인덱스가 너무 세밀해서 trade-off가 발생하기 때문일까? 여기까진 잘 모르겠다.
  • a의 범위가 커도 충분히 빠를까? => 그렇지 않다.
    • 컬럼 a가 시간이고, 컬럼 b가 userId라고 가정해보자. 하루치 데이터는 200만건이다. 인덱스 (a, b)는 보조인덱스다.
    • 특정 유저의 1일치 데이터를 가져오려면, 2023-08-01<a<2023-08-02 AND b=’userId’
    • Key Range: 일단 1일치에 해당하는 인덱스 블록을 모조리 가져온다. (보조인덱스 이므로 200만건)
    • Key Filter: 200만건을 b=’userId’로 filtering 한다.
    • 본질적으로 200만건에 대한 필터링이므로, b=’userId’인 row가 1만건이어도 a의 범위가 크면 클 수록 느려진다. (실제로 성능 좋은 Oracle에서 돌려보니 5s 걸렸다)
  • a의 범위가 큰데, 인덱스 순서를 (b, a)로 바꾸면?
    • 위와 동일한 상황에서 인덱스 순서를 (b, a)로 바꾸게 되면
    • Key Range: b=’userId’에 해당하는 인덱스 블록 가져온다. (보조인덱스 이므로 1만건)
    • Key Filter: 1만건을 2023-08-01<a<2023-08-02로 flitering 한다.
  • 즉 범위 질의 하는 경우 NDV에 따른 복합 인덱스의 순서가 매우 중요해진다.

혹시 틀렸다면 정정해주시면 좋겠습니다.

RDBMS, Query design
This post is licensed under CC BY 4.0 by the author.