SQLite는 어떤 인덱스를 사용해야 합니까?
Source: Dev.to
예시: 두 인덱스 중 선택하기
여러 인덱스가 있는 테이블을 고려해 보세요:
-- Example table definition
CREATE TABLE table1 (
id INTEGER PRIMARY KEY,
x INTEGER,
y INTEGER,
z TEXT
);
-- Indexes on separate columns
CREATE INDEX i1 ON table1(x);
CREATE INDEX i2 ON table1(y);이제 이 쿼리를 살펴보세요:
SELECT z FROM table1 WHERE x = 5 AND y = 6;SQLite에는 두 가지 선택지가 있습니다:
- 인덱스
i1사용:x = 5인 행을 찾은 뒤y = 6을 필터링합니다. - 인덱스
i2사용:y = 6인 행을 찾은 뒤x = 5를 필터링합니다.
두 접근법 모두 유효하지만 비용 차이가 크게 날 수 있습니다. SQLite는 각 옵션이 요구하는 작업량을 추정하고 가장 낮은 비용을 가진 것을 선택합니다. 결정은 다음과 같은 휴리스틱에 기반합니다:
- 예상 매치 행 수
- 인덱스의 선택도
- 조회 후 필요한 필터링 양
통계 데이터가 있으면 SQLite는 더 나은 결정을 내립니다. 여기서 sqlite_stat1 테이블이 등장합니다—이 테이블은 특정 컬럼 값에 일반적으로 연결된 행 수 정보를 저장해 SQLite가 어느 인덱스가 결과 집합을 가장 많이 줄일지 추정할 수 있게 합니다. 일반적으로 반환 행이 적을 것으로 예상되는 인덱스가 선호됩니다.
선택을 무시하기
때때로 특정 인덱스를 사용하지 않도록 SQLite를 유도하고 싶을 수 있습니다. SQLite는 단항 + 연산자를 이용한 미묘한 메커니즘을 제공합니다:
SELECT z FROM table1 WHERE +x = 5 AND y = 6;+ 연산자는 기능적으로 아무 효과가 없지만, SQLite가 컬럼 x에 대한 인덱스를 사용하지 못하게 합니다. 이렇게 하면 옵티마이저가 y 컬럼의 i2와 같은 다른 인덱스를 고려하도록 강제합니다. 쿼리 의미를 바꾸지 않고 옵티마이저에 영향을 주는 가벼운 방법입니다.
WHERE와 ORDER BY 균형 맞추기
인덱스 선택은 행 필터링뿐만 아니라 정렬 요구사항도 고려합니다.
ORDER BY 예시
SELECT * FROM table1 WHERE x = 5 ORDER BY y;이제 SQLite는 트레이드오프에 직면합니다:
x에 인덱스 사용: 효율적으로 필터링한 뒤 결과 집합을y기준으로 정렬합니다.y에 인덱스 사용:ORDER BY절을 직접 만족시키지만 선택도가 낮은 필터링 비용이 발생할 수 있습니다.
SQLite는 두 옵션을 평가하고 전체 실행 속도가 가장 빠른 것을 선택합니다. 때때로 비용이 큰 정렬을 피하기 위해 약간 덜 효율적인 필터를 포기하기도 합니다. 적절한 인덱스가 ORDER BY 절을 만족시키지 못하면 SQLite는 임시 정렬기(일시적인 메모리 구조)를 사용해 결과를 수동으로 정렬해야 합니다. 과정은 대략 다음과 같습니다:
open sorter
where-begin
extract required columns
build a record
generate sort key
insert into sorter
where-end
sort
for each sorted entry
extract data
return result
close sorter이 방법은 동작하지만 추가 메모리 사용, 정렬을 위한 추가 처리, 대용량 데이터셋에서의 성능 오버헤드가 발생합니다. 따라서 SQLite는 가능한 경우 항상 ORDER BY에 인덱스를 사용하려고 합니다.
결정 간 상호 작용
- WHERE 절이 후보 인덱스를 결정합니다.
- 조인 순서가 테이블 접근 시점을 결정합니다.
- 인덱스 선택이 접근 방식을 결정합니다.
- ORDER BY가 어떤 인덱스가 선호되는지에 영향을 줄 수 있습니다.
하나의 결정이 영향을 줄 수 있는 항목:
- 스캔된 행 수
- 정렬 필요 여부
- 전체 실행 시간
SQLite가 이러한 결정을 내리는 방식을 이해하면 더 좋은 인덱스를 설계하고 옵티마이저에 맞는 쿼리를 작성할 수 있습니다.
다음 부분에서는 GROUP BY와 MIN/MAX 최적화를 살펴볼 예정이며, SQLite가 데이터를 효율적으로 집계하고 요약하기 위해 추가 전략을 적용하는 방법을 다룹니다.