Mon L'étrange voyage

3. 결합인덱스 사용 시, 인덱스 구성의 첫인덱스가 where절의 첫 조건에 적용하지 않았을 경우

두 개 이상의 컬럼을 합쳐서 인덱스를 만들 때 결합인덱스라고 한다. 

결합인덱스 컬럼 선택의 기준점은

1. where 절에서 and 조건으로 자주 사용되며, 각각 사용할때보다 두 개 이상의 컬럼이 결합될 때 분포도가 좋아지는 컬럼
   ※ 분포도(Cardinality) : 해당 컬럼의 중복도가 적을 경우 분포도가 높고, 중복도가 높을경우 분포도가 낮다
   흔히, 우리가 Table의 ID 는 중복되는 값이 없으므로 분포도가 높다고 할 수 있고, Name 같은 컬럼은 ID에 비해 중복되는 값이 많으므로 분포도가 높다고 할 수 있다. 
2. 테이블 조인을 위한 연결고리로 사용되는게 빈번한 컬럼
3. 자주 정렬되는 컬럼인지 고려
4. 쿼리 작성시 '=' 으로 지정할 수 있는 컬럼이 선행되어야 한다. 

index를 구성할 때, A,B,C 로 구성한다고 했을 때

#첫번째 케이스
select *
  from test
 where A=99
   and B=99
   and C=99;
   
#두번째 케이스
select *
  from test
 where A=99
   and B between 10 and 99
   and C=99;
   
#세번째 케이스
select *
  from test
 where A between 10 and 99
   and B between 10 and 99
   and C=99;

첫번째, 두번째 케이스는 인덱스를 타지만, 세번째 케이스는 인덱스를 타지 않는다.

물론 두번째 케이스도 완벽하게 인덱스를 타는건 아니다. 실행계획을 떠보면 알겠지만 범위 조건은 해당 컬럼은 인덱스를 타지만, 그 뒤 인덱스 컬럼들은 인덱스를 타지 않고 filter access를 하게 된다. (https://khdscor.tistory.com/51)

쿼리 툴에서는  force index를 통해 강제로 힌트를 줄 수 있지만 ,JPA/Hibernate에서는 Query Hint 조정이 불가능
실제로 JPA/Hibernate에서의 힌트는 Hibernate의 작동 방식에 대한 Hint이지 Query Hint가 아니다.

실무를 하다보면 요즘에는 NoSQL, QueryDSL 등의 좋은 기술들로 인해 직접 복잡한 쿼리를 짤 경우는 많지는 않지만

쿼리를 만들고 나서 실행계획을 보면 내가 의도했던 인덱스를 타지 않은 경우들이 종종있다.

실제 쿼리를 보면 잘 짠거같은데 인덱스를 제대로 타지않는 경우에 대해서 공부를 하다보니 내가 미처 몰랐던 부분들이 많았던 것 같다. 

인덱스가 제대로 동작하지 않는 케이스

1. 조건문에서 인덱스의 컬럼을 변형시킨 경우

이 경우는 정말 실수하기 좋은 케이스이다. 

만약 내가 생년월일(birth_date) 라는 char형 컬럼을 인덱스로 만들었다고 가정했을 때,

아래와 같이 쿼리문을 짜면 조건문의 값이 int형이기 때문에 Stirng으로 만든 인덱스가 타지 않는다. 

select *
  from test_table
 where birth_date = 20000101

물론,  항상 이 말이 맞는건 아니다.

Why? 예외적인 케이스가 항상 존재하기 때문이다. 

예외적인 케이스는 Oracle,Mysql 에서는 묵시적인 형변환이 이뤄진다. 

그런데 또, 이런 묵시적인 형변환( char -> int, int -> char 등등의 변환)은 항상 작동하는건 아니다!

묵시적인 형변환은 데이터 형식의 우선순위에 따라 우선순위가 낮은 데이터 형식이 먼저 우선순위가 높은 데이터 형식으로 변환된다.

묵시적인 형변환관련 함수

TO_NUMBER(문자열)
TO_CHAR(숫자 혹은 날짜, [FORMAT])
TO_DATE(문자열, FORMAT)

그래서 이번에는 나이(age) 라는 int형 컬럼을 인덱스로 만들었다고 가정했을때,

아래와 같은 쿼리문은 조건문에서 형변환이 이루어졌기때문에 인덱스가 타지 않을거라고 생각이 되지만

막상 실행계획을 보면 인덱스가 의도한대로 잘 탄다.

그 이유는 앞서 말한이유로 char가 int 보다 우선순위가 낮기때문에 묵시적 형변환이 이루어지면서 인덱스를 탄 케이스이다.

select *
  from test_table
 where age='99';

2. 인덱스 컬럼 자체를 변형한 경우

위의 케이스와는 조금 비슷하면서 다른 케이스이다. 이 케이스는 조건문에서 사용된 컬럼 자체를 변형시킬 경우 인덱스가 작동하지 않는다.

이유는 인덱스 컬럼을 가공하게 되면 가공되는 값에 맞는 새로운 인덱스를 생성해서 사용하기 때문이다. 
인덱스가 생성되는 원리를 알면 이해가 더 쉽다. ( https://jeong-pro.tistory.com/242 참고 )

# 케이스1
select *
  from test_table
 where substr(birth_date,1,4) = '2000';

# 케이스2
select *
  from test_table
 where age+99 = 100;

# 케이스3  
select *
  from test_table
 where LOWER('name') = 'petric';

1. 실행계획이란?

• SQL이 실행되어 데이터를 처리하고 할 때, 옵티마이저에서 수립되는 SQL 실행방법론 
• 가장 안에서부터, 가장 위에서 부터 차례차례 읽으면 된다.

2. 옵티마이저(Optimizer) 란?

• 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진
• 옵티마이저의 종류
  • 규칙 기반 옵티마이저 ( RBO )  : 오라클8 이하의 버전에 기본으로 설정되어있음
  • 비용 기반 옵티마이저 : 오라클 10 이후 버전에 주로 사용됨
    SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식, 여기서 비용은 SQL문을 처리하기 위해 예상되는
    소요시간, 자원사용량을 의미   

출처 : https://myjamong.tistory.com/236

출처 : https://coding-factory.tistory.com/743

쿼리의 수행시간을 단축시킬 수 있는 가장 좋은 방법은 INDEX 생성 혹은 INDEX 힌트를 쓰는게 아닐까 생각합니다.

그러면, INDEX는 말로만 매일 인덱스, 인덱스하는데 정작 INDEX 가 무엇이고 어떤식으로 작동하는지 알기는 쉽지 않아서

공부도 할겸 같이 공유를 해볼까합니다.

1. INDEX 의 정의

인덱스란 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다. 

쉽게 말하면, 책의 색인과 같다고 보면된다.

( * 색인은 책에 포함 된 키워드와 주요 용어를 찾는 포인터 역할을하는 페이지를 의미합니다. )

오라클에서 제공하는 인덱스 종류는 총 4 가지. ( 인덱스의 종류가 이렇게 많은줄 몰랐다..)

• B*TREE 인덱스
• 비트맵 인덱스(BITMAP INDEX)
• REVERSE KEY 인덱스
• 함수 기반 인덱스(FUNCTION BASE INDEX)

대부분, B*TREE 인덱스를 주로 사용한다고 한다. 그래서 공부도 B*TREE 인덱스만.. 하기로

2. B*TREE  인덱스의 구성

• 루트 블록(ROOT BLOCK) : 분기 값 저장 
• 브랜치 블록(BRANCH BLOCK) : 분기 값 저장
• 리프 블록(LEAF BLOCK) : 인덱스 키 값 + ROWID 저장

3. INDEX를 사용하면 좋은 경우

• 규모가 작지 않은 테이블
• INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
• JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼
• 데이터의 중복도가 낮은 컬럼  ( * cardinality : 전체 행에 대한 특정 컬럼의 중복 수치를 나타내는 지표 )
  : 예를 들면 스포츠라고 하면 축구,농구,배구등등 여러가지들을 다 포함해서 스포츠라고하지만
    Messi 라는 축구선수는 딱 한명입니다.  이럴때 전자는 중복도가 높기 때문에 cardinality가 낮다고 하고
    후자는 중복도가 낮기때문에 cardinality 가 높다고 할 수 있습니다.

인덱스가 무조건적으로 좋다곤 할 수 없습니다.

테이블 조회하는 속도랑 성능을 향상시킬 수 도 있지만 , 잘못사용할 경우 FULL로 테이블을 읽을 때보다 느려질 수 있습니다.

또한, INDEX REBUILD 등의 후속조치가 필요할 수 있습니다.

다음에는 인덱스 스캔에 대해서 공부를 더 해보겠습니다.