Index Scan VS Full Scan
총 데이터의 개수에서 20-25%가 넘어가면 Index를 사용하지 않고 Full Scan을 하는게 좋습니다.
인덱스를 통해 레코드를 1건 읽는 것이 직접 레코드를 읽는 것에 4~5배 비용이 더 많이 듭니다.
책을 읽으며 재미있는 부분을 발견해서 직접 테스트해봅니다.
실험
- 200만건 데이터 준비
- Columns BigInt id PK, Varchar name
- id : 1 ~ 200만
Where id < N의 조건으로 조회를 하는데
FORCE INDEX(PRIMARY)orIGNORE INDEX(PRIMARY)로 테스트합니다.
1. 20만건(10%) 조회
PK 사용 : 0.08
PK 제외 : 0.26
2. 40만건(20%) 조회
PK 사용 : 0.13
PK 제외 : 0.28
3. 80만건(40%) 조회
PK 사용 : 0.21
PK 제외: 0.31
4. 150만건(75%) 조회
PK 사용 : 0.36
PK 제외 : 0.38 ~ 0.47
조건 범위가 좁혀질수록 속도가 빠르며 또한 20만건에서는 3배의 차이를 보였으나 150만건으로 갈수록 시간 간격이 좁혀지는 특징이 보입니다.
그러나 150만건 조회의 실행 계획에서 Rows column의 값은 차이가 컸으며 해당 Records 만큼 SLock이 걸릴 것으로 예상을 합니다.
Clustered Index로 조회한 것과 아닌 것의 차이가 존재할 수도 있습니다.
B-Tree Index를 통한 데이터 읽기
1. Index Range Scan
인덱스 접근 방식의 가장 대표적인 접근 방식으로 뒤에 나올 2가지 방식보다 빠른 방식
"ABE"와 "ROUA"사이의 이름을 가진 모든 member 찾기
SELECT * FROM member WHERE name BETWEEN "ABE" AND "ROUA";
Leaf Node는 ABE Index Key부터 시작하여 ROUA Index Key까지 스캔 합니다.
ABE Index Key이전에는 Root, Branch Node에서 탐색합니다.
또한 Index값만 조회하는 것이 아닌 해당 레코드의 추가적인 컬럼을 조회시 랜덤 I/O가 일어나며 이러한 문제를 해결하기 위해 Index Covering같은 기법이 존재합니다.
2. Index Full Scan
Index Range Scan과 동일하게 Index를 사용하지만 Index의 처음부터 끝까지 모두 읽는 방식을 뜻합니다.
대표적으로 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 사용되며
예를 들어, 인덱스는 (A, B, C)순서로 만들었지만 조건절은 B컬럼 혹은 C컬럼 아니면 역순으로 사용되는 경우입니다.
3. Index Loose Scan
해당 방식은 중간을 건너뛰며 듬성 듬성 느슨하게 읽어서 Loose라 표현하며 위의 두개의 방식을 상반된 의미로 Tight Scan이라 부릅니다.
일반적으로 집계 함수를 최적화를 하는 경우에 사용됩니다.
Index Loose Scan을 사용가능한 경우
Table t1(c1, c2, c3, c4)가 있으며 idx(c1, c2, c3)가 있는 경우
SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;Group By 최적화
https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html
4. Index Skip Scan
DB Server Index의 핵심은 값이 정렬돼 있다는 것입니다.
인덱스를 구성하는 칼럼의 순서는 매우 중요합니다.
만약 Product Table이 존재하며 복합 인덱스가 name, created_at순으로 걸린다면 조건에 따라 인덱스 사용여부가 갈립니다.
ALTER TABLE Product ADD INDEX ix_name_created_at (name, created_at);
created_at을 먼저 where절의 1번째로 사용하면 index를 사용할 수 없어서 created_at컬럼부터 시작하는 인덱스를 새로 생성하는 방식이 필요했습니다.
하지만 8.0부터 옵티마이저가 Index 순서와 다르게 사용해도 Index 검색이 가능하게 해주는 Index Skip Scan최적화 기능이 도입되었습니다.
이는 Index Loose Scan같이 생략된 Index 열마다 scan을 해줍니다.
하지만 8.0에 새로 도입된 기능이여서 조건이 존재합니다.
- where절의 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크 값의 개수가 적어야 함
- 쿼리가 커버링 인덱스의 조건에 부합해야 함
조건절에 없는 선행되는 인덱스 컬럼의 유니크한 값의 개수가 너무 많다면
Full Table Scan으로 계획을 수립합니다.
