메모리 액세스에 최적화된 테이블의 인덱스Indexes on Memory-Optimized Tables

이 항목 적용 대상: 예SQL Server예Azure SQL 데이터베이스없습니다Azure SQL 데이터 웨어하우스 없습니다 병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

행을 함께 연결하는 인덱스이므로 모든 메모리 최적화 테이블에는 하나 이상의 인덱스가 있어야 합니다.All memory-optimized tables must have at least one index, because it is the indexes that connect the rows together. 메모리 최적화 테이블에서는 모든 인덱스 또한 메모리 최적화되어 있습니다.On a memory-optimized table, every index is also memory-optimized. 메모리 최적화 테이블의 인덱스와 디스크 기반 테이블의 인덱스는 여러 방식에서 다릅니다.There are several ways in which an index on a memory-optimized index differs from a traditional index on a disk-base table:

  • 데이터 행은 페이지에 저장되지 않으므로 테이블의 모든 페이지를 가져오기 위해 참조할 수 있는 페이지 또는 익스텐트의 모음과 파티션 또는 할당 단위가 없습니다.Data rows are not stored on pages, so there is no collection of pages or extents, no partitions or allocation units that can be referenced to get all the pages for a table. 사용 가능한 인덱스 형식 중 하나에 대한 인덱스 페이지 개념이 있지만 디스크 기반 테이블의 인덱스와 다르게 저장됩니다.There is the concept of index pages for one of the available types of indexes, but they are stored differently than indexes for disk-based tables. fillfactor가 없으므로 페이지 내에서 기존 유형의 조각화가 발생하지 않습니다.They do not accrue the traditional type of fragmentation within a page, so they have no fillfactor.
  • 데이터 조작 중에 메모리 최적화 테이블의 인덱스에 대한 변경 내용은 디스크에 기록되지 않습니다.Changes made to indexes on memory-optimized tables during data manipulation are never written to disk. 데이터 행 및 데이터 변경 내용만 트랜잭션 로그에 기록됩니다.Only the data rows, and changes to the data, are written to the transaction log.
  • 데이터베이스가 다시 온라인이 될 때 메모리 액세스에 최적화된 인덱스가 다시 빌드됩니다.Memory-optimized indexes are rebuilt when the database is brought back online.

메모리 최적화 테이블의 모든 인덱스는 데이터베이스 복구 중에 인덱스 정의를 기반으로 만들어집니다.All indexes on memory-optimized tables are created based on the index definitions during database recovery.

인덱스는 다음 중 하나여야 합니다.The index must be one of the following:

  • 해시 인덱스Hash index
  • 메모리 최적화 비클러스터형 인덱스(B-트리의 기본 내부 구조를 의미)Memory-optimized Nonclustered index (meaning the default internal structure of a B-tree)

해시 인덱스는 메모리 최적화 테이블의 해시 인덱스에서 자세히 설명합니다.Hash indexes are discussed in more detail in Hash Indexes for Memory-Optimized Tables. 비클러스터형 인덱스는 메모리 최적화 테이블의 비클러스터형 인덱스에서 자세히 설명합니다.Nonclustered indexes are discussed in more detail in Nonclustered Index for Memory-Optimized Tables.
Columnstore 인덱스에 대해서는 다른 문서에서 설명합니다.Columnstore indexes are discussed in another article.

메모리 최적화 인덱스 구문Syntax for memory-optimized indexes

메모리 최적화 테이블에 대한 각 CREATE TABLE 문은 인덱스를 통해 명시적으로 또는 PRIMAY KEY 또는 UNIQUE 제약 조건을 통해 암시적으로 인덱스를 포함해야 합니다.Each CREATE TABLE statement for a memory-optimized table must include an index, either explicitly through an INDEX or implicitly through a PRIMAY KEY or UNIQUE constraint.

기본 DURABILITY = SCHEMA_AND_DATA를 사용하여 선언하려면 메모리 최적화 테이블에 기본 키가 있어야 합니다.To be declared with the default DURABILITY = SCHEMA_AND_DATA, the memory-optimized table must have a primary key. 다음 CREATE TABLE 문의 PRIMARY KEY NONCLUSTERED 절은 두 가지 요구 사항을 충족합니다.The PRIMARY KEY NONCLUSTERED clause in the following CREATE TABLE statement satisfies two requirements:

  • CREATE TABLE 문에서 하나의 인덱스의 최소 요구 사항을 충족할 인덱스를 제공합니다.Provides an index to meet the minimum requirement of one index in the CREATE TABLE statement.
  • SCHEMA_AND_DATA 절에 필요한 기본 키를 제공합니다.Provides the primary key that is required for the SCHEMA_AND_DATA clause.

    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 2014 (12.x)SQL Server 2016(13.x)SQL Server 2016 (13.x)에는 메모리 최적화 테이블 또는 테이블 형식당 8개의 인덱스 제한이 있습니다. and SQL Server 2016(13.x)SQL Server 2016 (13.x) have a limit of 8 indexes per memory-optimized table or table type. SQL Server 2017(14.x)SQL Server 2017 (14.x) 이상 및 Azure SQL 데이터베이스Azure SQL Database에서는 메모리 최적화 테이블 및 테이블 형식에 해당하는 인덱스 수 제한이 없어집니다.Starting with SQL Server 2017(14.x)SQL Server 2017 (14.x) and in Azure SQL 데이터베이스Azure SQL Database, there is no longer a limit on the number of indexes specific to memory-optimized tables and table types.

구문에 대한 코드 샘플Code sample for syntax

이 하위 섹션에는 메모리 최적화 테이블에 다양한 인덱스를 만드는 구문을 보여 주는 하는 TRANSACT-SQL 코드 블록이 포함되어 있습니다.This subsection contains a Transact-SQL code block that demonstrates the syntax to create various indexes on a memory-optimized table. 코드에서는 다음을 보여 줍니다.The code demonstrates the following:

  1. 메모리 최적화 테이블을 만듭니다.Create a memory-optimized table.
  2. ALTER TABLE 문을 사용하여 두 인덱스를 추가합니다.Use ALTER TABLE statements to add two indexes.
  3. 데이터 행을 몇 개 삽입합니다.INSERT a few rows of data.

    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 
    

중복된 인덱스 키 값Duplicate index key values

중복된 인덱스 키 값은 메모리 최적화 테이블에 대한 작업의 성능에 영향을 줄 수 있습니다.Duplicate index key values can impact the performance of operations on memory-optimized tables. 대부분의 인덱스 작업에서 중복 체인을 순회해야 하므로 중복이 많으면(예: 100 이상) 인덱스 유지 관리 작업이 비효율적입니다.Large numbers of duplicates (e.g., 100+) make the job of maintaining an index inefficient because duplicate chains must be traversed for most index operations. 메모리 최적화 테이블에서 INSERT, UPDATEDELETE 작업에 영향을 줄 수 있습니다.The impact can be seen in INSERT, UPDATE, and DELETE operations on memory-optimized tables.

이 문제는 해시 인덱스의 경우 더 자주 발생합니다. 해시 인덱스에 대한 작업당 소요되는 더 적은 비용과 해시 충돌 체인이 있는 큰 중복 체인의 간섭 모두가 원인입니다.This problem is more visible in the case of hash indexes, due both to the lower cost per operation for hash indexes and the interference of large duplicate chains with the hash collision chain. 인덱스에서 중복을 줄이려면 비클러스터형 인덱스를 사용하고 중복 수를 줄이기 위해 인덱스 키의 끝에 별도 열을 추가합니다(예: 기본 키에서 추가).To reduce duplication in an index, use a nonclustered index and add additional columns (for example from the primary key) to the end of the index key to reduce the number of duplicates. 해시 충돌에 대한 자세한 내용은 메모리 최적화 테이블의 해시 인덱스를 참조하세요.For more information about hash collisions, see Hash Indexes for Memory-Optimized Tables.

예를 들어, CustomerId의 기본 키가 있는 Customers 테이블과 열 CustomerCategoryID의 인덱스를 고려합니다.For example, consider a Customers table with a primary key on CustomerId and an index on column CustomerCategoryID. 일반적으로 특정 범주에 많은 고객이 있으므로 지정된 키에 대한 중복 값이 CustomerCategoryID의 인덱스에 많습니다.There will typically be many customers in a given category, and thus many duplicate values for a given key in the index on CustomerCategoryID. 이 시나리오에서는 (CustomerCategoryID, CustomerId)에 대한 비클러스터형 인덱스를 사용하는 것이 모범 사례입니다.In this scenario, best practice is to use a nonclustered index on (CustomerCategoryID, CustomerId). 이 인덱스는 CustomerCategoryID를 포함하는 조건자를 사용하는 쿼리에 사용할 수 있고 중복을 포함하지 않으므로 인덱스 유지 관리의 비효율성을 야기하지 않습니다.This index can be used for queries that use a predicate involving CustomerCategoryID, and does not contain duplication, and therefore does not cause inefficiency in index maintenance.

다음 쿼리는 샘플 데이터베이스 CustomerCategoryID WideWorldImporters Sales.Customers의 테이블 에 있는에 대한 인덱스와 관련해 중복 인덱스 키 값의 평균 개수를 보여 줍니다.The following query shows the average number of duplicate index key values for the index on CustomerCategoryID in table Sales.Customers, in the sample database WideWorldImporters.

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

고유 테이블 및 인덱스와 관련해 인덱스 키 중복 항목의 평균 개수를 구하려면 Sales.Customers 를 테이블 이름으로 바꾸고 CustomerCategoryID 를 인덱스 키 열 목록으로 바꿉니다.To evaluate the average number of index key duplicates for your own table and index, replace Sales.Customers with your table name, and replace CustomerCategoryID with the list of index key columns.

각 인덱스 유형을 사용하는 경우 비교Comparing when to use each index type

특정 쿼리의 특성에서 인덱스 유형이 적합한 선택인지 결정됩니다.The nature of your particular queries determines which type of index is the best choice.

해당 기능은 디스크 기반 테이블에서 기존 클러스터형 및 비클러스터형 인덱스의 기능과 유사하므로 기존 응용 프로그램에서 메모리 최적화 테이블을 구현할 때 일반적으로 비클러스트형 인덱스로 시작하는 것이 좋습니다.When implementing memory-optimized tables in an existing application, the general recommendation is to start with nonclustered indexes, as their capabilities more closely resemble the capabilities of traditional clustered and nonclustered indexes on disk-based tables.

비클러스터형 인덱스 사용에 대한 권장 사항Recommendations for nonclustered index use

비클러스터형 인덱스는 다음의 경우 해시 인덱스 전체에서 선호됩니다.A nonclustered index is preferable over a hash index when:

  • 쿼리 시 인덱싱된 열에 ORDER BY 절이 포함됨Queries have an ORDER BY clause on the indexed column.
  • 다중 열 인덱스의 선행 열만 테스트하는 위치 쿼리Queries where only the leading column(s) of a multi-column index is tested.
  • 쿼리에서 WHERE 절을 다음과 같이 사용하여 인덱싱된 열 테스트:Queries test the indexed column by use of a WHERE clause with:
    • 같지 않음: WHERE StatusCode != 'Done'An inequality: WHERE StatusCode != 'Done'
    • 값 범위 검색: WHERE Quantity >= 100A value range scan: WHERE Quantity >= 100

다음 모든 SELECT 문에서는 비클러스터형 인덱스가 해시 인덱스 전체에서 선호됩니다.In all the following SELECTs, a nonclustered index is preferable over a hash index:

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

SELECT CustomerName, Priority, Description 
FROM SupportEvent  
WHERE CustomerName != 'Ben';  

SELECT StartDateTime, CustomerName  
FROM SupportEvent  
ORDER BY StartDateTime;  

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

해시 인덱스 사용에 대한 권장 사항Recommendations for hash index use

해시 인덱스는 주로 포인트 조회에 사용되고 범위 검색에는 사용되지 않습니다.Hash indexes are primarily used for point lookups and not for range scans.

해시 인덱스는 쿼리에서 같음 조건자를 사용할 경우 비클러스터형 인덱스보다 선호되고 WHERE 절은 다음 예제와 같이 모든 인덱스 키 열에 매핑됩니다.A hash index is preferable over a nonclustered index when queries use equality predicates, and the WHERE clause maps to all index key columns, as in the following example:

SELECT CustomerName 
FROM SupportEvent  
WHERE SupportEngineerName = 'Liz';

여러 열 인덱스Multi-column index

여러 열 인덱스는 비클러스터형 인덱스 또는 해시 인덱스일 수 있습니다.A multi-column index could be a nonclustered index or a hash index. 인덱스 열이 col1 및 col2라고 가정해 보겠습니다.Suppose the index columns are col1 and col2. 다음 SELECT 문이 주어진 경우 비클러스터형 인덱스만 쿼리 최적화 프로그램에 유용합니다.Given the following SELECT statement, only the nonclustered index would be useful to the query optimizer:

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

해시 인덱스는 WHERE 절이 있어야 해당 키의 각 열에 대해 같음 테스트를 지정할 수 있습니다.The hash index needs the WHERE clause to specify an equality test for each of the columns in its key. 그러지 않으면 해시 인덱스는 쿼리 최적화 프로그램에 유용하지 않습니다.Else the hash index is not useful to the query optimizer.

WHERE 절이 인덱스 키의 두 번째 열만 지정하는 경우에는 두 인덱스 형식 모두 유용하지 않습니다.Neither index type is useful if the WHERE clause specifies only the second column in the index key.

인덱스 사용 시나리오를 비교하기 위한 요약 테이블Summary table to compare index use scenarios

다음 표에서 다른 인덱스 유형에서 지원 되는 모든 작업을 나열 합니다.The following table lists all operations that are supported by the different index types. 는 인덱스가 요청을 효율적으로 처리할 수 있음을 의미하며 아니요는 인덱스를 사용하여 요청을 효과적으로 충족할 수 없음을 의미합니다.Yes means that the index can efficiently service the request, and No means that the index cannot efficiently satisfy the request.

연산Operation 메모리 액세스에 최적화됨,Memory-optimized,
해시hash
메모리 액세스에 최적화됨,Memory-optimized,
비클러스터형nonclustered
디스크 기반,Disk-based,
(비)클러스터형(non)clustered
색인 검색은 모든 테이블 행을 검색합니다.Index Scan, retrieve all table rows. Yes Yes Yes
같음 조건자(=)에서 인덱스 검색Index seek on equality predicates (=). Yes
(전체 키는 필수)(Full key is required.)
Yes Yes
같지 않음 및 범위 조건자에서 인덱스 검색Index seek on inequality and range predicates
(>, <, <=, >=, BETWEEN).(>, <, <=, >=, BETWEEN).
아니요No
(인덱스 검색의 결과)(Results in an index scan.)
1Yes 1 Yes
인덱스 정의와 일치하는 정렬 순서로 행을 검색합니다.Retrieve rows in a sort order that matches the index definition. 아니요No Yes Yes
인덱스 정의의 역순과 일치하는 정렬 순서로 행을 검색합니다.Retrieve rows in a sort-order that matches the reverse of the index definition. 아니요No 아니요No Yes

1 메모리 최적화 비클러스터형 인덱스의 경우 인덱스 검색을 수행하는 데 전체 키가 필요하지 않습니다.1 For a memory-optimized Nonclustered index, the full key is not required to perform an index seek.

자동 인덱스 및 통계 관리Automatic index and statistics management

Adaptive Index Defrag와 같은 솔루션을 사용하여 하나 이상의 데이터베이스에 대한 인덱스 조각 모음 및 통계 업데이트를 자동으로 관리합니다.Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. 이 절차는 다른 매개 변수 사이에서 조각화 수준에 따라 인덱스를 다시 작성하거나 다시 구성할지 여부를 자동으로 선택하고 통계를 선형 임계값으로 업데이트합니다.This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

참고 항목See Also

SQL Server 인덱스 디자인 가이드 SQL Server Index Design Guide
메모리 최적화 테이블의 해시 인덱스 Hash Indexes for Memory-Optimized Tables
메모리 최적화 테이블의 비클러스터형 인덱스 Nonclustered Indexes for Memory-Optimized Tables
Adaptive Index DefragAdaptive Index Defrag