메모리 최적화 테이블의 인덱스

아티클
07/12/2024

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

행을 함께 연결하는 인덱스이므로 모든 메모리 최적화 테이블에는 하나 이상의 인덱스가 있어야 합니다. 메모리 최적화 테이블에서는 모든 인덱스도 메모리 최적화됩니다. 메모리 최적화 테이블의 인덱스가 디스크 기반 테이블의 기존 인덱스와 다른 여러 가지 방식이 있습니다.

데이터 행은 페이지에 저장되지 않으므로 페이지 또는 익스텐트 컬렉션, 테이블의 모든 페이지를 가져오기 위해 참조할 수 있는 파티션 또는 할당 단위가 없습니다. 사용 가능한 인덱스 유형 중 하나에 대한 인덱스 페이지의 개념이 있지만 디스크 기반 테이블의 인덱스와는 다르게 저장됩니다. fillfactor가 없으므로 페이지 내에서 기존 유형의 조각화가 발생하지 않습니다.
데이터 조작 중에 메모리 최적화 테이블의 인덱스에 대한 변경 내용은 디스크에 기록되지 않습니다. 데이터 행 및 데이터 변경 내용만 트랜잭션 로그에 기록됩니다.
데이터베이스가 다시 온라인 상태가 되면 메모리 최적화 인덱스가 다시 작성됩니다.

메모리 최적화 테이블의 모든 인덱스는 데이터베이스 복구 중에 인덱스 정의를 기반으로 만들어집니다.

인덱스는 다음 중 하나여야 합니다.

해시 인덱스
메모리 최적화 비클러스터형 인덱스(B-트리의 기본 내부 구조 의미)

해시 인덱스는 메모리 최적화 테이블의 해시 인덱스에서 자세히 설명합니다.
비클러스터형 인덱스는 메모리 최적화 테이블의 비클러스터형 인덱스에서 자세히 설명합니다.
Columnstore 인덱스는 다른 문서에서 설명합니다.

메모리 최적화 인덱스의 구문

메모리 최적화 테이블에 대한 각 CREATE TABLE 문에는 INDEX를 통해 명시적으로 또는 PRIMAY KEY 또는 UNIQUE 제약 조건을 통해 암시적으로 인덱스가 포함되어야 합니다.

기본 DURABILITY = SCHEMA_AND_DATA로 선언하려면 메모리 최적화 테이블에 기본 키가 있어야 합니다. 다음 CREATE TABLE 문의 PRIMARY KEY NONCLUSTERED 절은 두 가지 요구 사항을 충족합니다.

CREATE TABLE 문에서 하나의 인덱스의 최소 요구 사항을 충족하는 인덱스를 제공합니다.

SCHEMA_AND_DATA 절에 필요한 기본 키를 제공합니다.

CREATE TABLE SupportEvent  
(  
    SupportEventId   int NOT NULL  
        PRIMARY KEY NONCLUSTERED,  
    ...  
)  
    WITH (  
        MEMORY_OPTIMIZED = ON,  
        DURABILITY = SCHEMA_AND_DATA);

참고 항목

SQL Server 2014(12.x) 및 SQL Server 2016(13.x)은 메모리 최적화 테이블 또는 테이블 유형당 8개의 인덱스로 제한됩니다. SQL Server 2017(14.x) 및 Azure SQL 데이터베이스부터 메모리 최적화 테이블 및 테이블 형식과 관련된 인덱스 수에 더 이상 제한이 없습니다.

구문의 코드 샘플

이 하위 섹션에는 메모리 최적화 테이블에 다양한 인덱스를 만드는 구문을 보여주는 Transact-SQL 코드 블록이 포함되어 있습니다. 이 코드는 다음 작업을 보여줍니다.

메모리 최적화 테이블을 만듭니다.
ALTER TABLE 문을 사용하여 두 인덱스를 추가합니다.

몇 개의 데이터 행을 삽입합니다.

DROP TABLE IF EXISTS SupportEvent;  
go  

CREATE TABLE SupportEvent  
(  
    SupportEventId   int               not null   identity(1,1)  
    PRIMARY KEY NONCLUSTERED,  

    StartDateTime        datetime2     not null,  
    CustomerName         nvarchar(16)  not null,  
    SupportEngineerName  nvarchar(16)      null,  
    Priority             int               null,  
    Description          nvarchar(64)      null  
)  
    WITH (  
    MEMORY_OPTIMIZED = ON,  
    DURABILITY = SCHEMA_AND_DATA);  
go  

    --------------------  

ALTER TABLE SupportEvent  
    ADD CONSTRAINT constraintUnique_SDT_CN  
    UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName);  
go  

ALTER TABLE SupportEvent  
    ADD INDEX idx_hash_SupportEngineerName  
    HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64);  -- Nonunique.  
go  

    --------------------  

INSERT INTO SupportEvent  
    (StartDateTime, CustomerName, SupportEngineerName, Priority, Description)  
    VALUES  
    ('2016-02-23 13:40:41:123', 'Abby', 'Zeke', 2, 'Display problem.'     ),  
    ('2016-02-24 13:40:41:323', 'Ben' , null  , 1, 'Cannot find help.'    ),  
    ('2016-02-25 13:40:41:523', 'Carl', 'Liz' , 2, 'Button is gray.'      ),  
    ('2016-02-26 13:40:41:723', 'Dave', 'Zeke', 2, 'Cannot unhide column.');  
go

중복 인덱스 키 값

중복된 인덱스 키 값이 있으면 메모리 최적화 테이블의 성능이 저하될 수 있습니다. 항목 체인을 트래버스하는 시스템의 중복 항목은 대부분의 인덱스 읽기 및 쓰기 작업에 대한 것입니다. 중복 항목 체인이 100개 항목을 초과하면 성능 저하를 측정할 수 있습니다.

중복 해시 값

이 문제는 해시 인덱스의 경우 더 두드러집니다. 해시 인덱스의 경우 다음과 같은 고려 사항으로 인해 더 문제가 됩니다.

해시 인덱스의 작업당 비용이 낮습니다.
해시 충돌 체인과 큰 중복 체인의 간섭이 발생합니다.

인덱스의 중복을 줄이려면 다음 조정을 시도합니다.

비클러스터형 인덱스를 사용합니다.
인덱스 키의 끝에 열을 추가하여 중복 횟수를 줄입니다.
- 예를 들어, 기본 키에도 있는 열을 추가할 수 있습니다.

해시 충돌에 대한 자세한 내용은 메모리 최적화 테이블에 대한 해시 인덱스를 참조하세요.

개선 예

다음은 인덱스의 성능 비효율성을 방지하는 방법의 예입니다.

CustomerId에 기본 키가 있는 Customers 테이블과 CustomerCategoryID 열의 인덱스를 고려합니다. 일반적으로 많은 고객이 특정 범주에 속합니다. 따라서 인덱스의 지정된 키 내에 CustomerCategoryID에 대한 많은 중복 값이 있습니다.

이 시나리오에서는 (CustomerCategoryID, CustomerId)에 비클러스터형 인덱스를 사용하는 것이 좋습니다. 이 인덱스는 CustomerCategoryID와 관련된 조건자를 사용하는 쿼리에 사용할 수 있지만 인덱스 키에는 중복이 포함되지 않습니다. 따라서 인덱스 유지 관리의 비효율성은 중복된 CustomerCategoryID 값 또는 인덱스 내의 추가 열로 인해 발생하지 않습니다.

다음 쿼리는 샘플 데이터베이스 WideWorldImporters에서 Sales.Customers 테이블의 CustomerCategoryID에 대한 인덱스의 평균 중복 인덱스 키 값 수를 보여줍니다.

SELECT AVG(row_count) FROM
    (SELECT COUNT(*) AS row_count 
	    FROM Sales.Customers
	    GROUP BY CustomerCategoryID) a

사용자 고유의 테이블 및 인덱스에 대한 인덱스 키 중복의 평균 수를 평가하려면 Sales.Customers를 테이블 이름으로 바꾸고 CustomerCategoryID를 인덱스 키 열 목록으로 바꿉니다.

각 인덱스 형식을 사용하는 시기 비교

특정 쿼리의 특성에 따라 가장 적합한 인덱스 형식이 결정됩니다.

기존 애플리케이션에서 메모리 최적화 테이블을 구현할 때는 해당 기능이 디스크 기반 테이블의 기존 클러스터형 및 비클러스터형 인덱스의 기능과 더 유사하므로 비클러스터형 인덱스로 시작하는 것이 좋습니다.

비클러스터형 인덱스 사용에 대한 권장 사항

비클러스터형 인덱스는 다음의 경우 해시 인덱스 전체에서 선호됩니다.

쿼리 시 인덱싱된 열에 ORDER BY 절이 포함됨
다중 열 인덱스의 선행 열만 테스트하는 위치 쿼리
쿼리에서 WHERE 절을 다음과 같이 사용하여 인덱싱된 열 테스트
- 부등식: WHERE StatusCode != 'Done'
- 값 범위 검색: WHERE Quantity >= 100

다음 모든 SELECT에서 비클러스터형 인덱스는 해시 인덱스보다 선호됩니다.

SELECT CustomerName, Priority, Description 
FROM SupportEvent  
WHERE StartDateTime > DateAdd(day, -7, GetUtcDate());  

SELECT StartDateTime, CustomerName  
FROM SupportEvent  
ORDER BY StartDateTime DESC; -- ASC would cause a scan.

SELECT CustomerName  
FROM SupportEvent  
WHERE StartDateTime = '2016-02-26';

해시 인덱스 사용 권장 사항

해시 인덱스는 주로 포인트 조회에 사용되고 범위 검색에는 사용되지 않습니다.

다음 예제와 같이 쿼리에서 같음 조건자를 사용하고 WHERE 절이 모든 인덱스 키 열에 매핑되는 경우 해시 인덱스가 비클러스터형 인덱스보다 선호됩니다.

SELECT CustomerName 
FROM SupportEvent  
WHERE SupportEngineerName = 'Liz';

여러 열 인덱스

다중 열 인덱스는 비클러스터형 인덱스 또는 해시 인덱스일 수 있습니다. 인덱스 열이 col1 및 col2라고 가정합니다. 다음 SELECT 문을 고려할 때 비클러스터형 인덱스만 쿼리 최적화 프로그램에서 유용합니다.

SELECT col1, col3  
FROM MyTable_memop  
WHERE col1 = 'dn';

해시 인덱스에는 해당 키의 각 열에 대한 같음 테스트를 지정하는 WHERE 절이 필요합니다. 그렇지 않으면 해시 인덱스는 쿼리 최적화 프로그램에 유용하지 않습니다.

WHERE 절이 인덱스 키의 두 번째 열만 지정하는 경우에는 두 인덱스 유형 모두 유용하지 않습니다.

인덱스 사용 시나리오를 비교하기 위한 요약 테이블

다음 표에서는 다양한 인덱스 형식에서 지원하는 모든 작업을 나열합니다. 예는 인덱스가 요청을 효율적으로 처리할 수 있음을 의미하며 아니요는 인덱스를 사용하여 요청을 효과적으로 충족할 수 없음을 의미합니다.

연산	메모리 최적화, hash	메모리 최적화, 비클러스터형	디스크 기반, (비)클러스터형
색인 검색은 모든 테이블 행을 검색합니다.	예	예	예
같음 조건자(=)에서 인덱스 검색	예 (전체 키 필요)	예	예
부등식 및 범위 조건자에 대한 인덱스 검색 (>, <, <=, >=, `BETWEEN`)	아님 (인덱스 검색의 결과)	예 ¹	예
인덱스 정의와 일치하는 정렬 순서로 행을 검색합니다.	예	예	예
인덱스 정의의 역순과 일치하는 정렬 순서로 행을 검색합니다.	아니요	아니요	예

¹ 메모리 최적화 비클러스터형 인덱스인 경우 전체 키가 인덱스 검색을 수행할 필요가 없습니다.

자동 인덱스 및 통계 관리

Adaptive Index Defrag와 같은 솔루션을 사용하여 하나 이상의 데이터베이스에 대한 인덱스 조각 모음 및 통계 업데이트를 자동으로 관리합니다. 이 절차는 다른 매개 변수 사이에서 조각화 수준에 따라 인덱스를 다시 작성하거나 다시 구성할지 여부를 자동으로 선택하고 통계를 선형 임계값으로 업데이트합니다.

참고 항목

SQL Server 인덱스 디자인 가이드
메모리 최적화 테이블의 해시 인덱스
메모리 최적화 테이블의 비클러스터형 인덱스
Adaptive Index Defrag

다음을 통해 공유

메모리 최적화 테이블의 인덱스

메모리 최적화 인덱스의 구문

구문의 코드 샘플

중복 인덱스 키 값

중복 해시 값

개선 예

각 인덱스 형식을 사용하는 시기 비교

비클러스터형 인덱스 사용에 대한 권장 사항

해시 인덱스 사용 권장 사항

여러 열 인덱스

인덱스 사용 시나리오를 비교하기 위한 요약 테이블

자동 인덱스 및 통계 관리

참고 항목

피드백

추가 리소스