이 항목에서는 해시 인덱스와 관련된 일반적인 문제를 해결하고 해결하는 데 중점을 줍니다.
검색을 수행하려면 해시 인덱스 키 열의 하위 집합이 필요합니다.
문제: 해시 값을 계산하고 해시 테이블에서 해당 행을 찾기 위해서는 모든 인덱스 키 열에 대한 값이 필요한 해시 인덱스를 사용해야 합니다. 따라서 쿼리에 WHERE 절의 인덱스 키 하위 집합에 대한 같음 조건자만 포함된 경우 SQL Server는 인덱스 검색을 사용하여 WHERE 절의 조건자에서 해당 행을 찾을 수 없습니다.
반면 디스크 기반 비클러스터형 인덱스 및 메모리 최적화 비클러스터형 인덱스와 같은 순서가 지정된 인덱스는 인덱스의 선행 열인 경우 인덱스 키 열의 하위 집합에서 인덱스 검색을 지원합니다.
증상: 따라서 SQL Server는 일반적으로 더 빠른 작업인 인덱스 검색이 아닌 전체 테이블 검사를 실행해야 하므로 성능이 저하됩니다.
문제 해결 방법: 성능 저하 외에도 쿼리 계획을 살펴보면 인덱스 탐색 대신 스캔이 나타나는 것을 확인할 수 있습니다. 쿼리가 매우 간단한 경우 쿼리 텍스트 및 인덱스 정의를 검사하면 검색에 인덱스 키 열의 하위 집합이 필요한지 여부도 표시됩니다.
다음 테이블 및 쿼리를 고려합니다.
CREATE TABLE [dbo].[od]
(
o_id INT NOT NULL,
od_id INT NOT NULL,
p_id INT NOT NULL,
CONSTRAINT PK_od PRIMARY KEY NONCLUSTERED HASH (o_id, od_id) WITH (BUCKET_COUNT = 10000)
)
WITH (MEMORY_OPTIMIZED = ON)
SELECT p_id
FROM dbo.od
WHERE o_id=1
테이블에는 두 열(o_id, od_id)에 해시 인덱스가 있지만 쿼리에는 같음 조건자(o_id)가 있습니다. 쿼리에 인덱스 키 열의 하위 집합에만 같음 조건자가 있으므로 SQL Server는 PK_od 사용하여 인덱스 검색 작업을 수행할 수 없습니다. 대신 SQL Server는 전체 인덱스 검색으로 되돌려야 합니다.
해결 방법: 가능한 여러 가지 해결 방법이 있습니다. 다음은 그 예입니다.
인덱스를 비클러스터형 해시 대신 비클러스터형으로 재생성합니다. 메모리 최적화 비클러스터형 인덱스는 순서가 지정되므로 SQL Server는 선행 인덱스 키 열에서 인덱스 검색을 수행할 수 있습니다. 예제의 결과 기본 키 정의는 .입니다
constraint PK_od primary key nonclustered.WHERE 절의 열과 일치하도록 현재 인덱스 키를 변경합니다.
쿼리의 WHERE 절에 있는 열과 일치하는 새 해시 인덱스를 추가합니다. 이 예제에서 결과 테이블 정의는 다음과 같습니다.
CREATE TABLE dbo.od ( o_id INT NOT NULL, od_id INT NOT NULL, p_id INT NOT NULL, CONSTRAINT PK_od PRIMARY KEY NONCLUSTERED HASH (o_id,od_id) WITH (BUCKET_COUNT=10000), INDEX ix_o_id NONCLUSTERED HASH (o_id) WITH (BUCKET_COUNT=10000) ) WITH (MEMORY_OPTIMIZED=ON)
지정된 인덱스 키 값에 대해 중복 행이 많은 경우 메모리 최적화 해시 인덱스가 최적으로 수행되지 않습니다. 예를 들어 열 o_id 고유 값 수가 테이블의 행 수보다 훨씬 작으면 인덱스를 추가하는 것이 최적이 아닙니다(o_id). 대신 인덱스 PK_od 형식을 해시에서 비클러스터형으로 변경하는 것이 더 나은 솔루션입니다. 자세한 내용은 해시 인덱스에 대한 올바른 버킷 수 확인(Determining the Correct Bucket Count)을 참조하세요.