34강 인덱스와 B-tree
34.1. 만능형 B-tree
- B-tree
- 균형잡힌 범용성으로 가장 많이 사용됨
- 검색 알고리즘으로 성능이 뛰어난 것은 아님
- 잘 잡힌 균형이 많이 사용되는 이유
- B+tree
- 검색 성능이 뛰어남
- 루트와 리프 거리를 가능한 일정하게 유지
- 트리의 깊이도 3-4 정도로 유지
- 데이터가 정렬된 상태로 유지
34.2.기타 인덱스
- 비트맵 인덱스
- 해시 인덱스
35강 인덱스와 B-tree
35.1. 카디널리티와 선택률
- 카디널리티
- 값의 균형을 나타냄
- 카디널리티가 높다 → 모든 레코드에 다른 값이 들어있다
- 선택률
- 특정 필드값을 지정했을 때 전체 테이블에서 몇 개의 레코드가 선택되는지
35.2. 인덱스를 사용하는 것이 좋은지 판단하려면
- 카디널리티가 높은 것
- 선택률이 낮은 것
36강 인덱스로 성능 향상이 어려운 경우
36.1. 압축 조건이 존재하지 않음
SELECT order_id, receive_date
FROM Orders;- WHERE 구가 없음 → 인덱스로 작성할 필드가 없음
36.2. 레코드를 제대로 압축하지 못하는 경우
레코드를 제대로 압축하지 못하는 경우
SELECT order_id, receive_date
FROM Orders
WHERE process_flg='5';
1 : 200만 건
2 : 500만 건
3 : 500만 건
4 : 500만 건
5 : 8,300만 건- 풀 스캔보다 느려질 수 있음
- process_flg='5'를 했을 때 선택률이 83%
입력 매개변수에 따라 선택률이 변동하는 경우(1)
SELECT order_id, receive_date
FROM Orders
WHERE receive_date BETWEEN :start_date AND :end_date;- 기간의 범위 검색
- 입력에 따라 선택률이 높아지거나 낮아짐
입력 매개변수에 따라 선택률이 변동하는 경우(2)
SELECT order_id, receive_date
FROM Orders
WHERE shop_id = :sid;- 상점 규모에 따라 주문 건수가 다름
36.3 인덱스를 사용하지 않는 검색 조건
중간 일치, 후방 일치의 LIKE 연산자
SELECT order_id, receive_date
FROM Orders
WHERE shop_name LIKE '%대공원%';- LIKE 연산자의 경우 인덱스는 전방 일치(’대공원%’)만 적용 가능
- 중간 일치, 후방 일치 → 풀 스캔
색인 필드로 연산하는 경우
SELECT order_id, receive_date
FROM Orders
WHERE col_1 * 1.1 > 100;- 검색 조건의 우변에 식을 사용하면 인덱스 사용 가능
- ex)
WHERE col_1 > 100/1.1;
IS NULL을 사용하는 경우
SELECT order_id, receive_date
FROM Orders
WHERE col_1 IS NULL;- IS NULL은 인덱스 사용 불가
- 인덱스 데이터의 NULL은 존재하지 않기 때문
부정형을 사용하는 경우
SELECT *
FROM SomeTable
WHERE col_1 <> 100;37강 인덱스를 사용할 수 없는 경우 대처법
37.1. 외부 설정으로 처리
- UI 설계로 처리
- ‘점포 ID’로 검색하면 ‘주문일’도 함께 입력하게 설계
- 테이블에 더 많은 압축 가하기
37.2. 외부 설정 처리 시 주의점
- 성능과 사용성의 트레이드오프 타협점을 찾기
37.3. 데이터 마트로 대처
- 특정 쿼리에서 필요한 데이터만 저장하는 작은 테이블
- 접근 대상 테이블의 크기를 작게 해서 I/O 양을 줄이기 위함
37.4. 데이터 마트를 채택할 시 주의점
- 데이터 신선도
- 데이터 마트 == 원 테이블의 복사본
- 원 테이블과의 동기화가 필수
- 동기 사이클이 짧을수록 데이터의 신선도가 높음
- 하지만 성능적 문제가 발생
- 데이터 마트 크기
- 데이터 테이블의 목적 → 테이블 크기를 작게해서 I/O 줄이기
- 원래 테이블에서 크기를 줄일 수 없으면 데이터 마트가 큰 효과가 없음
- GROUP BY 절로 집계를 마치고 데이터 마트를 만들면 필드 수와 레코드 수를 줄일 수 있음
- 데이터 마트 수
- 과도한 데이터 마트 수는 쓸데없이 동기화가 일어나는 ‘좀비 마트’가 생기거나 관리의 어려움 유발
- 데이터 마트에 지나치게 의존하면 안 됨
- 배치 윈도우
- 데이터 마트 생성에도 시간이 걸림 → 배치 윈도우 압박
37.5. 인덱스 온리 스캔으로 대처
- SQL문이 접근하려는 대상의 I/O 감소를 목적으로 함
- 데이터 마트의 데이터 동기 문제를 해결
- 2개의 필드를 커버하는 인덱스를 생성하여 인덱스 온리 스캔 사용
CREATE INDEX CoveringIndex ON Orders (order_id, receive_date);
37.6. 인덱스 온리 스캔의 주의사항
- DBMS에 따라 사용할 수 없는 경우도 있음
- 한 개의 인덱스에 포함될 수 있는 필드 수는 제한이 있음
- 인덱스 크기는 무한이 아님
- 필드 수, 크기에 제한이 있음
- 갱신 오버 헤드 증가
- 인덱스 → 테이블의 갱신 부하를 증가시킴
- 검색 성능은 증가하지만 갱신 성능은 감소되는 트레이드오프 발생
- 정기적인 인덱스 리빌드가 필요
- 인덱스 온리 → 검색 성능이 인덱스 크기에 의존
- 인덱스의 정기적인 크기 모니터링과 리빌드가 필요
- SQL 구문에 새로운 필드가 추가되면 사용할 수 없음
