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

이 항목은 다음에 적용됩니다.예SQL Server(2014부터)예Azure SQL Database아니요Azure SQL Data Warehouse 아니요병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2014)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

이 문서에서는 메모리 액세스에 최적화된 테이블에서 사용할 수 있는 인덱스 유형을 설명합니다.This article describes the types of indexes that are available for a memory-optimized table. 이 문서에서는 다음 내용을 다룹니다.The article:

  • TRANSACT-SQL 구문을 보여 주는 짧은 코드 예제를 제공합니다.Provides short code examples to demonstrate the Transact-SQL syntax.
  • 메모리 액세스에 최적화된 인덱스가 기존의 디스크 기반 인덱스와 어떻게 다른지 설명합니다.Describes how memory-optimized indexes differ from traditional disk-based indexes.
  • 메모리 액세스에 최적화된 인덱스 유형이 각각 최고인 상황에 대해 설명합니다.Explains the circumstances when each type of memory-optimized index is best.

해시 인덱스는 밀접하게 관련된 문서에서 자세히 설명합니다.Hash indexes are discussed in more detail in a closely related article.

Columnstore 인덱스에 대해서는 다른 문서에서 설명합니다.Columnstore indexes are discussed in another article.

1.A. 메모리 액세스에 최적화된 인덱스 구문Syntax for memory-optimized indexes

메모리 액세스에 최적화된 테이블의 각 CREATE TABLE 문에는 인덱스 선언을 위해 1-8절이 포함되어야 합니다.Each CREATE TABLE statement for a memory-optimized table must include between 1 and 8 clauses to declare indexes. 인덱스는 다음 중 하나여야 합니다.The index must be one of the following:

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

기본 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.
<span data-ttu-id="c257b-120">CREATE TABLE SupportEvent</span><span class="sxs-lookup"><span data-stu-id="c257b-120">CREATE TABLE SupportEvent</span></span>  
<span data-ttu-id="c257b-121">(</span><span class="sxs-lookup"><span data-stu-id="c257b-121">(</span></span>  
    <span data-ttu-id="c257b-122">SupportEventId   int NOT NULL</span><span class="sxs-lookup"><span data-stu-id="c257b-122">SupportEventId   int NOT NULL</span></span>  
        <span data-ttu-id="c257b-123">PRIMARY KEY NONCLUSTERED,</span><span class="sxs-lookup"><span data-stu-id="c257b-123">PRIMARY KEY NONCLUSTERED,</span></span>  
    <span data-ttu-id="c257b-124">...</span><span class="sxs-lookup"><span data-stu-id="c257b-124">...</span></span>  
<span data-ttu-id="c257b-125">).</span><span class="sxs-lookup"><span data-stu-id="c257b-125">)</span></span>  
    <span data-ttu-id="c257b-126">WITH (</span><span class="sxs-lookup"><span data-stu-id="c257b-126">WITH (</span></span>  
        <span data-ttu-id="c257b-127">MEMORY_OPTIMIZED = ON,</span><span class="sxs-lookup"><span data-stu-id="c257b-127">MEMORY_OPTIMIZED = ON,</span></span>  
        <span data-ttu-id="c257b-128">DURABILITY = SCHEMA_AND_DATA);</span><span class="sxs-lookup"><span data-stu-id="c257b-128">DURABILITY = SCHEMA_AND_DATA);</span></span>  

A.1 구문에 대한 코드 샘플A.1 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.
<span data-ttu-id="c257b-135">DROP TABLE IF EXISTS SupportEvent;</span><span class="sxs-lookup"><span data-stu-id="c257b-135">DROP TABLE IF EXISTS SupportEvent;</span></span>  
<span data-ttu-id="c257b-136">go</span><span class="sxs-lookup"><span data-stu-id="c257b-136">go</span></span>  

<span data-ttu-id="c257b-137">CREATE TABLE SupportEvent</span><span class="sxs-lookup"><span data-stu-id="c257b-137">CREATE TABLE SupportEvent</span></span>  
<span data-ttu-id="c257b-138">(</span><span class="sxs-lookup"><span data-stu-id="c257b-138">(</span></span>  
  <span data-ttu-id="c257b-139">SupportEventId   int               not null   identity(1,1)</span><span class="sxs-lookup"><span data-stu-id="c257b-139">SupportEventId   int               not null   identity(1,1)</span></span>  
    <span data-ttu-id="c257b-140">PRIMARY KEY NONCLUSTERED,</span><span class="sxs-lookup"><span data-stu-id="c257b-140">PRIMARY KEY NONCLUSTERED,</span></span>  

  <span data-ttu-id="c257b-141">StartDateTime        datetime2     not null,</span><span class="sxs-lookup"><span data-stu-id="c257b-141">StartDateTime        datetime2     not null,</span></span>  
  <span data-ttu-id="c257b-142">CustomerName         nvarchar(16)  not null,</span><span class="sxs-lookup"><span data-stu-id="c257b-142">CustomerName         nvarchar(16)  not null,</span></span>  
  <span data-ttu-id="c257b-143">SupportEngineerName  nvarchar(16)      null,</span><span class="sxs-lookup"><span data-stu-id="c257b-143">SupportEngineerName  nvarchar(16)      null,</span></span>  
  <span data-ttu-id="c257b-144">Priority             int               null,</span><span class="sxs-lookup"><span data-stu-id="c257b-144">Priority             int               null,</span></span>  
  <span data-ttu-id="c257b-145">Description          nvarchar(64)      null</span><span class="sxs-lookup"><span data-stu-id="c257b-145">Description          nvarchar(64)      null</span></span>  
<span data-ttu-id="c257b-146">).</span><span class="sxs-lookup"><span data-stu-id="c257b-146">)</span></span>  
  <span data-ttu-id="c257b-147">WITH (</span><span class="sxs-lookup"><span data-stu-id="c257b-147">WITH (</span></span>  
    <span data-ttu-id="c257b-148">MEMORY_OPTIMIZED = ON,</span><span class="sxs-lookup"><span data-stu-id="c257b-148">MEMORY_OPTIMIZED = ON,</span></span>  
    <span data-ttu-id="c257b-149">DURABILITY = SCHEMA_AND_DATA);</span><span class="sxs-lookup"><span data-stu-id="c257b-149">DURABILITY = SCHEMA_AND_DATA);</span></span>  
<span data-ttu-id="c257b-150">go</span><span class="sxs-lookup"><span data-stu-id="c257b-150">go</span></span>  

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

<span data-ttu-id="c257b-151">ALTER TABLE SupportEvent</span><span class="sxs-lookup"><span data-stu-id="c257b-151">ALTER TABLE SupportEvent</span></span>  
  <span data-ttu-id="c257b-152">ADD CONSTRAINT constraintUnique_SDT_CN</span><span class="sxs-lookup"><span data-stu-id="c257b-152">ADD CONSTRAINT constraintUnique_SDT_CN</span></span>  
    <span data-ttu-id="c257b-153">UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName);</span><span class="sxs-lookup"><span data-stu-id="c257b-153">UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName);</span></span>  
<span data-ttu-id="c257b-154">go</span><span class="sxs-lookup"><span data-stu-id="c257b-154">go</span></span>  

<span data-ttu-id="c257b-155">ALTER TABLE SupportEvent</span><span class="sxs-lookup"><span data-stu-id="c257b-155">ALTER TABLE SupportEvent</span></span>  
  <span data-ttu-id="c257b-156">ADD INDEX idx_hash_SupportEngineerName</span><span class="sxs-lookup"><span data-stu-id="c257b-156">ADD INDEX idx_hash_SupportEngineerName</span></span>  
    <span data-ttu-id="c257b-157">HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64);  -- Nonunique.</span><span class="sxs-lookup"><span data-stu-id="c257b-157">HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64);  -- Nonunique.</span></span>  
<span data-ttu-id="c257b-158">go</span><span class="sxs-lookup"><span data-stu-id="c257b-158">go</span></span>  

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

<span data-ttu-id="c257b-159">INSERT INTO SupportEvent</span><span class="sxs-lookup"><span data-stu-id="c257b-159">INSERT INTO SupportEvent</span></span>  
    <span data-ttu-id="c257b-160">(StartDateTime, CustomerName, SupportEngineerName, Priority, Description)</span><span class="sxs-lookup"><span data-stu-id="c257b-160">(StartDateTime, CustomerName, SupportEngineerName, Priority, Description)</span></span>  
  <span data-ttu-id="c257b-161">VALUES</span><span class="sxs-lookup"><span data-stu-id="c257b-161">VALUES</span></span>  
    <span data-ttu-id="c257b-162">('2016-02-25 13:40:41:123', 'Abby', 'Zeke', 2, 'Display problem.'</span><span class="sxs-lookup"><span data-stu-id="c257b-162">('2016-02-25 13:40:41:123', 'Abby', 'Zeke', 2, 'Display problem.'</span></span>     <span data-ttu-id="c257b-163">),</span><span class="sxs-lookup"><span data-stu-id="c257b-163">),</span></span>  
    <span data-ttu-id="c257b-164">('2016-02-25 13:40:41:323', 'Ben' , null  , 1, 'Cannot find help.'</span><span class="sxs-lookup"><span data-stu-id="c257b-164">('2016-02-25 13:40:41:323', 'Ben' , null  , 1, 'Cannot find help.'</span></span>    <span data-ttu-id="c257b-165">),</span><span class="sxs-lookup"><span data-stu-id="c257b-165">),</span></span>  
    <span data-ttu-id="c257b-166">('2016-02-25 13:40:41:523', 'Carl', 'Liz' , 2, 'Button is gray.'</span><span class="sxs-lookup"><span data-stu-id="c257b-166">('2016-02-25 13:40:41:523', 'Carl', 'Liz' , 2, 'Button is gray.'</span></span>      <span data-ttu-id="c257b-167">),</span><span class="sxs-lookup"><span data-stu-id="c257b-167">),</span></span>  
    <span data-ttu-id="c257b-168">('2016-02-25 13:40:41:723', 'Dave', 'Zeke', 2, 'Cannot unhide column.');</span><span class="sxs-lookup"><span data-stu-id="c257b-168">('2016-02-25 13:40:41:723', 'Dave', 'Zeke', 2, 'Cannot unhide column.');</span></span>  
<span data-ttu-id="c257b-169">go</span><span class="sxs-lookup"><span data-stu-id="c257b-169">go</span></span>  

2.B. 메모리 액세스에 최적화된 인덱스 특성Nature of memory-optimized indexes

메모리 액세스에 최적화된 테이블에서 모든 인덱스 또한 메모리 액세스에 최적화되어 있습니다.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.

각 메모리 액세스에 최적화된 인덱스는 활성 메모리에만 존재합니다.Each memory-optimized index exists only in active memory. 인덱스는 디스크에 표시되지 않습니다.The index has no representation on the disk.

  • 데이터베이스가 다시 온라인이 될 때 메모리 액세스에 최적화된 인덱스가 다시 빌드됩니다.Memory-optimized indexes are rebuilt when the database is brought back online.

SQL UPDATE 문에서 메모리 액세스에 최적화된 테이블의 데이터를 수정하는 경우 인덱스에 대한 해당 변경 내용이 로그에 기록되지 않습니다.When an SQL UPDATE statement modifies data in a memory-optimized table, corresponding changes to its indexes are not written to the log.

메모리 액세스에 최적화된 인덱스의 항목에는 테이블 행에 대한 직접 메모리 주소가 포함되어 있습니다.The entries in a memory-optimized index contain a direct memory address to the row in the table.

  • 반면 디스크의 기존 B-트리 인덱스의 항목에는 연결된 테이블 행에 대한 메모리 주소를 찾는 데 시스템에서 처음 사용해야 하는 키 값이 포함됩니다.In contrast, entries in a traditional B-tree index on disk contain a key value that the system must first use to find the memory address to the associated table row.

메모리 액세스에 최적화된 인덱스에는 디스크 기반 인덱스와 마찬가지로 고정된 페이지가 없습니다.Memory-optimized indexes have no fixed pages as do disk-based indexes.

  • fillfactor가 없으므로 페이지 내에서 기존 유형의 조각화가 발생하지 않습니다.They do not accrue the traditional type of fragmentation within a page, so they have no fillfactor.

3.C. 중복된 인덱스 키 값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, UPDATE 및 DELETE 작업에 영향을 줄 수 있습니다.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 indices, due both to the lower cost per operation for hash indices 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.

CustomerId에 대한 기본 키가 있고 CustomerCategoryID 열에 대한 인덱스가 있는 Customers 테이블을 예로 들어봅시다.Consider, as an example, 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 thus 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.

4.D. 각 인덱스 유형을 사용하는 경우 비교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.

D.1 비클러스터형 인덱스의 장점D.1 Strengths of nonclustered indexes

비클러스터형 인덱스는 다음의 경우 해시 인덱스 전체에서 선호됩니다.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 != '완료'An inequality: WHERE StatusCode != 'Done'
    • 값 범위: 수량 위치 > = 100A value range: WHERE Quantity >= 100

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

SELECT col2 FROM TableA  
    WHERE StartDate > DateAdd(day, -7, GetUtcDate());  

SELECT col3 FROM TableB  
    WHERE ActivityCode != 5;  

SELECT StartDate, LastName  
    FROM TableC  
    ORDER BY StartDate;  

SELECT IndexKeyColumn2  
    FROM TableD  
    WHERE IndexKeyColumn1 = 42;  

D.2 해시 인덱스의 장점D.2 Strengths of hash indexes

해시 인덱스 는 다음과 같은 상황에서 비클러스터형 인덱스에서 선호됩니다.A hash index is preferable over a nonclustered index when:

  • 쿼리에서 WHERE 절을 다음과 같이 모든 인덱스 키 열에서 같음으로 사용하여 인덱싱된 열 테스트Queries test the indexed columns by use of a WHERE clause with an exact equality on all index key columns, as in the following:
<span data-ttu-id="c257b-210">SELECT col9 FROM TableZ</span><span class="sxs-lookup"><span data-stu-id="c257b-210">SELECT col9 FROM TableZ</span></span>  
    <span data-ttu-id="c257b-211">WHERE Z_Id = 2174;</span><span class="sxs-lookup"><span data-stu-id="c257b-211">WHERE Z_Id = 2174;</span></span>  

D.3 인덱스 장점을 비교할 요약 테이블D.3 Summary table to compare index strengths

다음 표에서 다른 인덱스 유형에서 지원 되는 모든 작업을 나열 합니다.The following table lists all operations that are supported by the different index types.

연산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.)
Yes 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

이 표에서 테이블에서 "예"는 인덱스가 요청을 효율적으로 처리할 수 있음을 의미하며 "아니요"는 인덱스를 사용하여 요청을 효과적으로 충족할 수 없음을 의미합니다.In the table, Yes means that the index can efficiently service the request, and No means that the index cannot efficiently satisfy the request.