💡 가장 일반적이면서 중요한 인덱스 방법은 B-tree 다. (사실 B+tree 다)
34강. 인덱스와 B-tree
인덱스 종류 3가지
1. 만능형: B-tree
- 데이터를 트리 구조로 저장하는 형태 인덱스
- 뛰어난 범용성
- 균형이 잘 잡혀 있음
- 사실 B+tree를 채택해서 사용
- 트리 리프 노드에만 키 값을 저장
- B-tree 보다 검색 효율 높은 알고리즘
- DB, 파일 시스템에서 사용
- Root ↔ Leaf 거리를 일정하게 유지
- 안정적인 검색 성능
- 데이터양 증가해도 검색 속도 급격히 악화되지 않음
- 트리 깊이가 대체로 3~4 레벨
- 정렬 상태 유지
- 이분탐색으로 검색 효율 높음
- 집약함수 사용 시 정렬 생략 가능해짐
- 등호, 부등호 검색 조건에 모두 활용 가능
2. 비트맵 인덱스
- 데이터를 비트 플래그로 변환해서 저장하는 인덱스
카디널리티(필드값의 분포도)낮은 필드에서 효율- 갱신 오버헤드 너무 큼
3. 해시 인덱스
- 키를 해시 분산해 등가 검색 효율 높이기
- 거의 안 씀
35강. 인덱스를 잘 활용하려면
1. 카디널리티와 선택률
- 인덱스 작성 기준 요소
카디널리티
- 값의 균형(분포)
- 높은 카디널리티
- 모든 레코드에 다른 값이 들어있는 필드
- 유일 키 필드
- 낮은 카디널리티
- 모든 레코드에 같은 값이 들어있는 필드
선택률
- 테이블 전체에서 몇 개의 레코드 선택되는지 비율
클러스터링 팩터
- 저장소에 같은 값이 어느정도 물리적으로 뭉쳐 존재하는지 여부
- 공간지역성
- 높은 클러스터링 팩터
- 같은 값이 분산되어 퍼져 있음
- 낮은 클러스터링 팩터
- 같은 값이 일정 구역에 몰려 있음
- 인덱스 스캔에 유리
- 접근할 데이터양이 적어지기 때문
2. 인덱스 쓰는게 좋은 건지 판단
- 한 번의 선택으로 레코드가 조금만 선택되도록 하는 필드가 좋은 인덱스 필드 후보
- 높은 카디널리티 필드
- 낮은 선택률 필드 (5~10% 정도)
36강. 인덱스로 성능 향상 어려운 경우
- 적절한 인덱스 작성을 위해선 SQL 검색 조건, 결합 조건 바탕으로
데이터 효율 압축 가능한 조건찾아야 함.
1. 압축 조건이 존재하지 않음
- 레코드 압축하는 where 절이 존재하지 않는 경우
- 인덱스로 작성할 필드 자체가 없는 것
2. 레코드를 제대로 압축하지 못하는 경우
- 레코드 압축 가능한 where 절 존재하지만, 그닥 압축되지 않는 경우
- 선택률이 높은 경우
- _flg 혹은 _status 이름의 필드는 대체로 특정 종류(종류는 다양성이 낮음)에 대한 필드이므로 인덱스 후보로 적절치 않음
- 입력 매개변수에 따라 선택률이 변동하는 경우 인덱스가 실제로 효용이 있는지 판단하기 어려움
- start_date, end_date 사이 레코드 검색 SQL
- shop_id 기준 레코드 검색 SQL
3. 인덱스를 사용하지 않는 검색 조건
- 레코드 압축 where 절이 있는데도 인덱스 사용 불가한 경우
중간 일치, 후방 일치의 LIKE 연산자
- LIKE 연산자 사용하는 경우
전방 일치 (’대공원%’) 에는 인덱스 사용 가능중간 일치 (’%대공원%’), 후방 일치 (’%대공원’) 에는 인덱스 사용 불가
색인(인덱스) 필드로 연산하는 경우
select * from some_table
where col_1 * 1.1 > 100; -- 인덱스 필드에 연산을 사용해서 인덱스 검색 불가
where col_1 > 100/1.1; -- 인덱스 필드에 연산 사용 안 했으므로 인덱스 검색 가능색인 필드에 함수를 사용하는 경우
- 인덱스 내부에 존재하는 값은
col_1이지length(col_1)이 아님
select * from some_table
where length(col_1) = 10;IS NULL 을 사용하는 경우
- 인덱스 필드 데이터는 null 이 없기 때문
select * from some_table
where col_1 is null;부정형을 사용하는 경우
- <>
- !=
- NOT IN
select * from some_table
where col_1 <> 100;37강. 인덱스를 사용할 수 없는 경우 대처법
- 인덱스 사용할 수 없는 경우
- 인덱스를 사용하는 경우 테이블 풀 스캔보다 성능이 안 나오는 경우
1. 외부 설정으로 처리 - 깊고 어두운 강 건너기
UI 설계로 처리
- 처음부터 인덱스 효용이 없는 쿼리가 실행되지 않도록 애플리케이션에서 제한
- 입력 제한을 둔다던지 등등
인덱스 설계는 테이블 정의와 SQL 만 봐서는 할 수 있는 작업이 아님
2. 데이터 마트로 대처
- 데이터 마트 / 마트 / 개요 테이블
- 특정 쿼리에서
필요한 데이터만 저장하는 상대적으로 작은 크기의 테이블 접근 대상 테이블 크기 작게→I/O 양 줄이기
데이터 신선도
- 데이터 동기 시점 문제
- 데이터 신선도가 중요한 경우라면 데이터 마트는 좋은 선택지가 아님.
데이터 마트 크기
- 원래 테이블과 데이터 마트 크기가 별 차이 나지 않는다면 의미 없음
- GROUP BY 절로 집계 마치고 데이터 마트 만들면,
- 필드, 레코드 수 줄이기 가능
- GROUP BY 에 필요한 정렬, 해시 처리도 사전에 끝내기 가능
데이터 마트 수
- 관리하기 어려움
- 좀비 마트
배치 윈도우
- 데이터 신선도 관리 혹은 데이터 마트 만드는 시간 관련해서 추가 작업 필요
- 배치 윈도우, Job Net
3. 인덱스 온리 스캔으로 대처
- SQL 구문 대상 I/O 감소 목적
- 데이터 마트의 데이터 동기 문제 해결
인덱스를 사용한 고속화 방법- 필요한 필드를 인덱스만으로 커버할 수 있는 경우에 테이블 접근을 생략하는 기술
- 다시 말해서 접근하려는 필드가 인덱스로 모두 커버 가능한 경우 테이블 접근하지 않고, 인덱스 접근 만으로 쿼리 실행
- 원래 테이블에 비해 크기가 굉장히 작아질 수 있음
- 컬럼 지향 데이터베이스
- 로우 지향 데이터베이스인 대부분의 RDB 에서 컬럼 지향 데이터베이스를 의사적으로 구현
- 만약에 SQL 프로젝션(조회 대상)이 * 인 경우 결국 모든 컬럼을 다 조회해야하므로, 인덱스 온리 스캔은 의미없이 추가적인 작업만 필요한 무의미한 작업이 됨
DBMS 에 따라 사용할 수 없는 경우도 있다
- 현재 대부분의 DBMS 가 인덱스 온리 스캔 지원
- 다만 옛날 버전의 경우 없을 수 있음
한 개의 인덱스에 포함할 수 있는 필드 수에 제한이 있다
- 한 개의 인덱스에는 필드 수 제한이 있음
- 또, 인덱스 크기가 너무 커지면 인덱스 의미가 없음
갱신 오버 헤드가 커진다
- 인덱스는 테이블 갱신 부하 증가시킴
- 인덱스가 커질수록 검색이 좋아지는 대신 갱신 성능은 떨어짐
정기적인 인덱스 리빌드가 필요
- 검색 성능이 인덱스 크기에 거의 비례
- 일반적인 인덱스보다 크기에 민감
- 정기적인 리빌드로 인덱스가 작게 유지해야함.
SQL 구문에 새로운 필드가 추가된다면 사용할 수 없다
- 새로운 요구사항에 따라 인덱스가 걸리지 않은 필드가 프로젝션에 추가되면, 인덱스 온리 스캔 사용 불가
- 대체로 프로젝션은 자주 변경될 가능성이 크므로 애플리케이션 유지 보수에 약한 타입의 튜닝
- 다만, B+tree 인덱스(일반 인덱스)로 고속화 어려운 상황에도 큰 성능 개선이 가능한 효자
정리
- B+tree 인덱스는
카디널리티, 선택률에 따라 성능 결정높은 카디널리티+낮은 선택률=좋은 인덱스 필드
- 선택률 제어위해 UI 설계 변경도 고려해야함.
- 선택률 높은 경우 인덱스 온리 스캔 고려
연습문제
💡 데이터 마트 사용한 성능 개선을 시도하는 경우, 구현방법과 각 방법의 장단점 생각해보기- Table To Table 갱신
- 원본 테이블 조회 결과를 삽입/갱신하는 간단한 방법
- SQL 코딩으로 제어할 수 있는 레벨까지 차분 갱신 수행
- SQL 코딩으로 튜닝 가능
- 머티리얼라이즈 뷰 (MV)
- DBMS 기능으로 조회 결과 삽입/갱신을 자동화하는 방법
- Oracle, DB2, PostgreSQL 지원
- DBMS 레벨에 따라 차분 갱신 레벨 다름
- 코딩으로 제어할 수 없으므로 튜닝 가능성 없음
개발하고 말테야