RDB Index (B+tree)
B-tree 인덱스
: 뛰어난 성능을 보장 하지 않지만, 계속해서 데이터가 변경되는 환경에서 균형이 잘잡혔다는 장점이 있다.
B+tree 인덱스
- 리프노드에만 키값을 저장
- B- tree의 수정버전, 보다 검색에 효율적
- 루트와 리프거리를 가능한 일정하게 유지
- 정렬상태 유지 → 이분탐색 가능
- 사실상 대게의 RDB에서 사용하는 인덱스 구조 (Oracle, PostgreSQL, MySQL에서 채택 중)
- 등가/범위 검색 모두 가능
기타 인덱스
- 비트맵 인덱스
- 데이터를 비트 플래그로 변환해서 저장
- 카디널리티가 낮은 필드에 대해 효과를 발휘
- 그러나 갱신시 오버헤드가 큼 → 갱신이 적은 BI/DWH 용도로 사용
*카디널리티 : 중복도가 낮으면, 카디널리티가 높다.ex. 이름은 주민등록번호에 비해 카디널리티카 낮다.
*BI(Business Intelligence, 비즈니스 결정을 최적화 하기 위한 행위), DWH(data warehousing)
- 해시 인덱스
- 키를 해시 분산
- 등가검색은 빠르나, 범위검색을 할 수 없음
- 거의 사용되지 않음, 지원하는 구현도 일부
인덱스 & 성능향상
▶︎ 인덱스 필드 집합의 조건 :
① 카디널리티⬆️
② 선택률⬇️ → 5~10%이하 권장, 10%초과시 풀스캔이 유리할 수 있다.
카디널리티와 선택률
- 카디널리티
- 값의 균형
- 카디널리티가 높다 = 레코드마다 다른 값이 들어 있다
- 선택률
- 테이블 전체에서 몇개의 레코드가 선택 되는지 (ex. 100 레코드중 1개라면, 1%)
- 클러스터링 팩터
- 같은 값이 어느 정도 물리적으로 뭉쳐 존재하는지
- 높을수록 분산, 낮을수록 뭉침 →
낮을수록 접근 데이터량이 적어서 좋다! - 구현에 의존(어떤 구현인가에 따라서 데이터의 뭉침정도가 결정)
→ 인덱스 설계시 테이블의 정의와 SQL만 봐서는 불가능. 검색조건과 결합조건으로 고려해야함. (이는 즉, 어플리케이션의 구현에 의해 인덱스의 설계가 영향을 받는다고 할 수 있음)
인덱스로 성능향상이 어려운 경우
1) 압축 조건이 존재하지 않음
SELECT col1
FROM tableA;- tableA의 모든 데이터를 검색
- 레코드를 압축할 WHERE절이 없음
- 실무에서 많지 않은 케이스, 있더라도 온라인 관련업무 아닌 배치성 업무
2) 레코드를 제대로 압축하지 못하는 경우
① 카디널리티가 매우 낮은 필드 (선택률이 매우 높음)
SELECT col1
FROM tableA
WHERE flag = '1'; // flag에 가능한 값 (1, 0)- 비교적 실무에서 자주 발생
flag = '1'인 데이터가 전체 데이터의 80%, 선택률이 80% →이는 풀스캔보다 더 성능저하를 발생시킬 수 있다.- 이와 같이 타입을 결정하는 필드들은 인덱스 필드로 적합하지 않다.
- 검색만이 아닌 결합필드로 사용시에도 성능이슈 발생
② 입력 매개변수에 따라 선택률이 변동
SELECT col1
FROM tableA
WHERE reg_date BETWEEN :s_date AND :e_date;- s_date 과 e_date의 기간 차이에 따라서 선택률의 큰 차이를 가져온다.
- 옵티마이저가 선택률에 따라, 인덱스 스캔과 풀스캔을 해준다면 좋겠지만, 안해줌.
3) 인덱스를 사용하지 않는 검색 조건
① LIKE - 중간/후방일치
SELECT col1
FROM tableA
WHERE col_nm LIKE '%대공원%';- LIKE의 경우 전방일치('대공원%')의 경우만, 인덱스를 사용할 수 있다.
중간/후방일치의 경우 모두 풀스캔
② 색인필드로 연산
// NOPE!!
SELECT col1
FROM tableA
WHERE col1*1.1 > 100;
// OK!!
SELECT col1
FROM tableA
WHERE col1 > 100/1.1;col1*1.1 > 100의 경우 인덱스를 사용하지 못함 → 우변으로 이항해서col1 > 100/1.1으로 사용하면, 인덱스 사용 가능
③ IS NULL 사용
SELECT col1
FROM tableA
WHERE col_nm IS NULL;- 일반적으로 색인필드에는 NULL 미존재
④ 함수 사용
SELECT col1
FROM tableA
WHERE LENGTH(col_nm) = 10;- 이는 색인필드에 연산하는 이유와 같다. 색인된 필드는 오롯이 col_nm이지 LENGTH함수를 적용한 것이 아니므로, 인덱스가 적용되지 않는다.
⑤ 부정형 사용
SELECT col1
FROM tableA
WHERE col_nm <> 100;- 부정형(<>, !=, NOT IN) 사용시 인덱스 사용하지 못함
인덱스로 성능향상이 어려운 경우 해결책
1) 애플리케이션 설정
1-1) 외부설정으로 처리
- 외부설정 =
애플리케이션 로직을 통해 입력제한을 주는 것이다.
ex)- 기간은 1개월 단위로만 입력 가능 (12개월을 조회한다고 하면, 1개월 단위로 12개의 쿼리를 날리는 것이 성능상 유리할수 있다.)
- 점포ID로 조회시 주문일도 반드시 입력
성능과사용성의 트레이드오프를 통해 타협점을 찾아야한다.DB엔지니어와애플리케이션 엔지니어사이의 커뮤니케이션을 통해, 설계단계에서 DB성능을 고려한 협의가 필요하다.
1-2) 데이터 마트에 의한 처리
데이터 마트 = 개요 테이블 = 집계 테이블- 원래 테이블의 부분집합
GROUP BY를 통한 집계를 많이 사용- 사용시 주의할점
- 데이터의 신선도
- 데이터 마트의 크기
‣ 테이블의 크기를 줄여 I/O사용량을 줄이는건데, 기존과 비슷하다면? 무의미하다. - 데이터 마트수
‣ 지나치게 의존한다면,좀비마트발생으로 저장소 용량 압박, 백업 또는 스냅샷 시간이 오랜걸리는 문제가 발생한다.
2) 인덱스 온리스캔
- 테이블을 스캔하지 않고, 인덱스만 스캔하는것
- (INDEX FAST FULL SCAN) 인덱스를 사용한 풀스캔, 테이블에 접근안함 → 옵티마이저의 판단으로 동작
- INDEX_FFS : 인덱스 온리스캔 사용을 원하지 않을 경우 힌트
I/O감소와실시간 데이터제공의 이점이 있다.커버링 인덱스→ SELECT구문의 필드를 커버하는 인덱스가 존재하면인덱스 온리스캔가능- 사용시 주의할점
- 한 개의 인덱스에 포함 가능한 필드 수의 제한이 있음
- 갱신 오버헤드가 커짐
- 정기적인 인덱스 리빌드 → 검색성능이 인덱스 크기에 의존
- SQL구문에 필드 추가시 사용불가 →이는 실행계획의 변동을 야기, 애플리케이션 유지 보수에 좋지 않음
Reference
Later never comes 👩🏻💻