SQL Server のインデックスのアーキテクチャとデザイン ガイドSQL Server Index Architecture and Design Guide

適用対象: ○SQL Server ○Azure SQL Database ○Azure SQL Data Warehouse ○Parallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

不完全なデザインのインデックスやインデックスの不備は、データベース アプリケーションのボトルネックの主な原因となります。Poorly designed indexes and a lack of indexes are primary sources of database application bottlenecks. 効率的なインデックスのデザインは、データベースとアプリケーションの高パフォーマンスを実現するための最優先事項です。Designing efficient indexes is paramount to achieving good database and application performance. この SQL ServerSQL Server インデックス デザイン ガイドには、インデックスのアーキテクチャに関する情報と、効果的なインデックスをデザインしてアプリケーションのニーズを満たすために役立つベスト プラクティスが含まれています。This SQL ServerSQL Server index design guide contains information on index architecture, and best practices to help you design effective indexes to meet the needs of your application.

このガイドでは、 SQL ServerSQL Serverで使用できるインデックスの種類に関して一般的な知識があることを前提としています。This guide assumes the reader has a general understanding of the index types available in SQL ServerSQL Server. インデックスの種類に関する全般的な説明については、「 インデックス」を参照してください。For a general description of index types, see Index Types.

このガイドでは、次のインデックスの種類について説明します。This guide covers the following types of indexes:

  • クラスター化インデックスClustered
  • 非クラスター化インデックスNonclustered
  • [一意]Unique
  • フィルター選択されたインデックスFiltered
  • 列ストアColumnstore
  • ハッシュ インデックスHash
  • メモリ最適化された非クラスター化インデックスMemory-Optimized Nonclustered

XML インデックスの詳細については、XML インデックスの概要に関するページを参照してください。For information about XML indexes, see XML Indexes Overview.

空間インデックスについては、「空間インデックスの概要」を参照してください。For information about Spatial indexes, see Spatial Indexes Overview.

フルテキスト インデックスの詳細については、「フルテキスト インデックスの作成」を参照してください。For information about Full-text indexes, see Populate Full-Text Indexes.

インデックスのデザインの基礎Index Design Basics

インデックスとは、テーブルまたはビューに関連付けられたディスク上またはメモリ内の構造で、テーブルやビューからの行の取得を高速化します。An index is an on-disk or in-memory structure associated with a table or view that speeds retrieval of rows from the table or view. インデックスには、テーブル内またはビュー内の 1 つ以上の列から構築されたキーが含まれています。An index contains keys built from one or more columns in the table or view. ディスク上のインデックスの場合、これらのキーは 1 つの構造 (B-Tree) 内に格納されます。SQL Server はこの構造を使用して、キー値に関連した 1 つ以上の行を効率よく迅速に検出できます。For on-disk indexes, these keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

インデックスは、論理的には、行と列があるテーブルとして編成されたデータを格納します。また物理的には、行ストア 1 と呼ばれる行単位のデータ形式、または 列ストア という列単位のデータ形式で格納されます。An index stores data logically organized as a table with rows and columns, and physically stored in a row-wise data format called rowstore 1, or stored in a column-wise data format called columnstore.

データベースとワークロードに適したインデックスの選択は、クエリの速度と更新コストのバランスを取る必要がある複雑な作業です。The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost. インデックス キー内の列数が少ないインデックスを使用すると、ディスク領域とメンテナンスのオーバーヘッドが少なくて済みます。Narrow indexes, or indexes with few columns in the index key, require less disk space and maintenance overhead. これに対して、列数の多いインデックスを使用すると、より多くのクエリに対応できます。Wide indexes, on the other hand, cover more queries. 効率の高いインデックスを決定するには、さまざまなデザインをテストする必要があります。You may have to experiment with several different designs before finding the most efficient index. インデックスは、データベース スキーマやアプリケーションのデザインに影響を与えずに追加、変更、および削除できます。Indexes can be added, modified, and dropped without affecting the database schema or application design. さまざまなデザインのインデックスを積極的にテストするようにしてください。Therefore, you should not hesitate to experiment with different indexes.

SQL ServerSQL Server のクエリ オプティマイザーでは、多くの場合、最も効率的なインデックスが選択されます。The query optimizer in SQL ServerSQL Server reliably chooses the most effective index in the vast majority of cases. インデックスのデザインの全体的な考え方としては、クエリ オプティマイザーでインデックスを選択するための選択肢として、さまざまなデザインのインデックスを用意し、オプティマイザーに決定を任せる必要があります。Your overall index design strategy should provide a variety of indexes for the query optimizer to choose from and trust it to make the right decision. このようにすると、分析時間を短縮でき、さまざまな状況でパフォーマンスを向上できます。This reduces analysis time and produces good performance over a variety of situations. クエリ オプティマイザーで特定のクエリに使用されるインデックスを確認するには、 SQL Server Management StudioSQL Server Management Studioで、 [クエリ] メニューの [実際の実行プランを含める] を選択します。To see which indexes the query optimizer uses for a specific query, in SQL Server Management StudioSQL Server Management Studio, on the Query menu, select Include Actual Execution Plan.

インデックスを使用しても、常にパフォーマンスが向上するわけではありません。また、パフォーマンスが優れていても、常にインデックスが効率的に使用されているわけでもありません。Do not always equate index usage with good performance, and good performance with efficient index use. インデックスを使用すれば常にパフォーマンスが向上するならば、クエリ オプティマイザーのジョブは単純です。If using an index always helped produce the best performance, the job of the query optimizer would be simple. しかし実際には、不適切なインデックスを選択すると、最適なパフォーマンスを実現することはできません。In reality, an incorrect index choice can cause less than optimal performance. したがって、クエリ オプティマイザーでは、パフォーマンスの向上につながる場合にのみインデックスまたはインデックスの組み合わせが選択され、パフォーマンスの低下につながる場合、インデックス付き検索は実行されません。Therefore, the task of the query optimizer is to select an index, or combination of indexes, only when it will improve performance, and to avoid indexed retrieval when it will hinder performance.

1 列ストアは、リレーショナル テーブル データを格納する従来の方法です。1 Rowstore has been the traditional way to store relational table data. SQL ServerSQL Server では、行ストアは、基になるデータ ストレージ形式が、ヒープ、B ツリー (クラスター化インデックス)、またはメモリ最適化テーブルであるテーブルを示します。In SQL ServerSQL Server, rowstore refers to table where the underlying data storage format is a heap, a B-tree (clustered index), or a memory-optimized table.

インデックスのデザインの作業Index Design Tasks

インデックスをデザインするには、次の作業を行うことをお勧めします。The follow tasks make up our recommended strategy for designing indexes:

  1. データベース自体の特性を理解します。Understand the characteristics of the database itself.

    • たとえば、頻繁なデータ変更を伴い、高スループットを維持する必要があるオンライン トランザクション処理 (OLTP) データベースです。For example, is it an online transaction processing (OLTP) database with frequent data modifications that must sustain a high throughput. SQL Server 2014 (12.x)SQL Server 2014 (12.x) 以降は、ラッチフリー デザインが提供されているため、このようなシナリオにはメモリ最適化テーブルとインデックスが特に適しています。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), memory-optimized tables and indexes are especially appropriate for this scenario, by providing a latch-free design. 詳細については、「メモリ最適化テーブルのインデックス」、またはこのガイドの「メモリ最適化非クラスター化インデックスのデザイン ガイドライン」と「ハッシュ インデックスのデザイン ガイドライン」を参照してください。For more information, see Indexes for Memory-Optimized Tables, or Nonclustered Index for Memory-Optimized Tables Design Guidelines and Hash Index for Memory-Optimized Tables Design Guidelines in this guide.
    • また、大規模なデータセットをすばやく処理する必要がある意思決定支援システム (DSS) またはデータ ウェアハウス (OLAP) データベースの例があります。Or an example of a Decision Support System (DSS) or data warehousing (OLAP) database that must process very large data sets quickly. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 以降では、列ストア インデックスは、一般的なデータ ウェアハウスのデータ セットに特に適しています。Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), columnstore indexes are especially appropriate for typical data warehousing data sets. 列ストア インデックスによって、フィルター処理クエリ、集計クエリ、グループ化クエリ、スター結合クエリなどの一般的なデータ ウェアハウス クエリのパフォーマンスを向上することで、ユーザーが快適にデータ ウェアハウスを利用できるようになります。Columnstore indexes can transform the data warehousing experience for users by enabling faster performance for common data warehousing queries such as filtering, aggregating, grouping, and star-join queries. 詳細については、「列ストア インデックス - 概要」、またはこのガイドの「列ストア インデックスのデザイン ガイドライン」を参照してください。For more information, see Columnstore Indexes overview, or Columnstore Index Design Guidelines in this guide.
  2. 最もよく使用されるクエリの特性を理解します。Understand the characteristics of the most frequently used queries. たとえば、よく使用されるクエリの中に、複数のテーブルを結合するクエリがあることを把握していると、使用する最適なインデックスの種類を決定するときに役立ちます。For example, knowing that a frequently used query joins two or more tables will help you determine the best type of indexes to use.

  3. クエリで使用される列の特性を理解します。Understand the characteristics of the columns used in the queries. たとえば、整数データ型を格納する列で、一意の列または NULL 値を許容しない列であれば、インデックスに適しています。For example, an index is ideal for columns that have an integer data type and are also unique or nonnull columns. 適切に定義されたデータのサブセットが含まれている列に対し、 SQL Server 2008SQL Server 2008 以上のバージョンでは、フィルター選択されたインデックスを使用できます。For columns that have well-defined subsets of data, you can use a filtered index in SQL Server 2008SQL Server 2008 and higher versions. 詳細については、このガイドの「 フィルター選択されたインデックスのデザイン ガイドライン 」を参照してください。For more information, see Filtered Index Design Guidelines in this guide.

  4. インデックスの作成時またはメンテナンス時のパフォーマンスを向上させるインデックス オプションを決定します。Determine which index options might enhance performance when the index is created or maintained. たとえば、既存の大きなテーブルにクラスター化インデックスを作成する際には ONLINE インデックス オプションが有益です。For example, creating a clustered index on an existing large table would benefit from the ONLINE index option. ONLINE オプションを使用すると、インデックスの作成中または再構築中に、基になるデータで同時処理を続行できます。The ONLINE option allows for concurrent activity on the underlying data to continue while the index is being created or rebuilt. 詳細については、「 インデックス オプションの設定」を参照してください。For more information, see Set Index Options.

  5. インデックスの最適な格納場所を決定します。Determine the optimal storage location for the index. 非クラスター化インデックスは、基になるテーブルと同じファイル グループまたは別のファイル グループに格納できます。A nonclustered index can be stored in the same filegroup as the underlying table, or on a different filegroup. インデックスの格納場所により、ディスク I/O のパフォーマンスが向上し、その結果クエリのパフォーマンスを向上させることができます。The storage location of indexes can improve query performance by increasing disk I/O performance. たとえば、非クラスター化インデックスを、テーブル ファイル グループとは別のディスク上にあるファイル グループに格納すると、複数のディスクを同時に読み取ることができるため、パフォーマンスが向上します。For example, storing a nonclustered index on a filegroup that is on a different disk than the table filegroup can improve performance because multiple disks can be read at the same time.
    また、クラスター化インデックスと非クラスター化インデックスでは、複数のファイル グループにまたがってパーティション構成を使用できます。Alternatively, clustered and nonclustered indexes can use a partition scheme across multiple filegroups. 大きなテーブルやインデックスをパーティション分割すると、コレクション全体の整合性を維持しながら、データのサブセットに対するアクセスや管理を迅速かつ効率的に行うことができるので、大きなテーブルやインデックスを管理しやすくなります。Partitioning makes large tables or indexes more manageable by letting you access or manage subsets of data quickly and efficiently, while maintaining the integrity of the overall collection. 詳しくは、「 Partitioned Tables and Indexes」をご覧ください。For more information, see Partitioned Tables and Indexes. パーティション分割を検討するときは、インデックスを固定するかどうかを決定します。つまり、基本的にテーブルと同じ方法でパーティション分割するか、または別の方法でパーティション分割するかを決定するということです。When you consider partitioning, determine whether the index should be aligned, that is, partitioned in essentially the same manner as the table, or partitioned independently.

インデックスのデザインの全般的なガイドラインGeneral Index Design Guidelines

経験豊富なデータベース管理者であれば適切なインデックス セットをデザインできますが、それほど複雑でないデータベースとワークロードであっても、この作業はきわめて複雑で、時間がかかり、間違いを犯しやすいものです。Experienced database administrators can design a good set of indexes, but this task is very complex, time-consuming, and error-prone even for moderately complex databases and workloads. 使用するデータベース、クエリ、データ列の特性を理解することが、最適なインデックスをデザインする際に役に立ちます。Understanding the characteristics of your database, queries, and data columns can help you design optimal indexes.

データベースに関する注意点Database Considerations

インデックスをデザインするときは、次のデータベースのガイドラインを考慮してください。When you design an index, consider the following database guidelines:

  • 1 つのテーブルに多数のインデックスがあると、テーブル内のデータが変更された場合にインデックスをすべて調整する必要があるので、INSERTUPDATEDELETE、および MERGE の各ステートメントのパフォーマンスに影響します。Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes. たとえば、列が複数のインデックスで使用されており、この列のデータを変更する UPDATE ステートメントを実行する場合は、その列が含まれている各インデックスも、基になるベース テーブル (ヒープまたはクラスター化インデックス) と同様に更新する必要があります。For example, if a column is used in several indexes and you execute an UPDATE statement that modifies that column's data, each index that contains that column must be updated as well as the column in the underlying base table (heap or clustered index).

    • 頻繁に更新するテーブルにはインデックスをデザインしすぎないようにし、インデックスの幅を狭く、つまり列数を可能な限り少なくします。Avoid over-indexing heavily updated tables and keep indexes narrow, that is, with as few columns as possible.

    • 更新の必要が少なく、容量の大きいテーブルの場合、クエリのパフォーマンスを向上させるにはインデックスを多数使用します。Use many indexes to improve query performance on tables with low update requirements, but large volumes of data. SELECT ステートメントなど、データを変更しないクエリの場合は、多数のインデックスを使用することで、クエリ オプティマイザーが最速のアクセス方法を決定する際に選択できるインデックスが多くなるため、クエリのパフォーマンスを向上できる可能性があります。Large numbers of indexes can help the performance of queries that do not modify data, such as SELECT statements, because the query optimizer has more indexes to choose from to determine the fastest access method.

  • 小さなテーブルではインデックスを作成しない方がよい場合もあります。これは、クエリ オプティマイザーが単純なテーブル スキャンを実行するよりデータのインデックスを検索する方に時間がかかることがあるためです。Indexing small tables may not be optimal because it can take the query optimizer longer to traverse the index searching for data than to perform a simple table scan. そのため、小さなテーブルのインデックスがまったく使用されない可能性があっても、テーブルのデータの変更に合わせてメンテナンスする必要があります。Therefore, indexes on small tables might never be used, but must still be maintained as data in the table changes.

  • ビューが集計、テーブル結合、または集計と結合の組み合わせを使用している場合、ビューにインデックスを設定すると、パフォーマンスが大幅に向上します。Indexes on views can provide significant performance gains when the view contains aggregations, table joins, or a combination of aggregations and joins. クエリで明示的に参照しなくても、クエリ オプティマイザーはそのビューを使用します。The view does not have to be explicitly referenced in the query for the query optimizer to use it.

  • データベース エンジン チューニング アドバイザーを使用してデータベースを分析し、推奨インデックスを作成します。Use the Database Engine Tuning Advisor to analyze your database and make index recommendations. 詳細については、「 Database Engine Tuning Advisor」を参照してください。For more information, see Database Engine Tuning Advisor.

クエリに関する注意点Query Considerations

インデックスをデザインするときは、次のクエリのガイドラインを考慮してください。When you design an index, consider the following query guidelines:

  • クエリの述語や結合条件で頻繁に使用される列に対して非クラスター化インデックスを作成します。Create nonclustered indexes on the columns that are frequently used in predicates and join conditions in queries. これらは、SARGable1 列です。These are your SARGable1 columns. ただし、不要な列を追加しないようにする必要があります。However, you should avoid adding unnecessary columns. インデックス列を追加しすぎると、必要なディスク領域が増え、インデックスのメンテナンスのパフォーマンスも低下する可能性があります。Adding too many index columns can adversely affect disk space and index maintenance performance.

  • クエリの対象にインデックスを含めると、クエリのパフォーマンスを向上できます。これは、クエリの要件を満たすために必要なデータがすべて、インデックス自体に保持されているためです。Covering indexes can improve query performance because all the data needed to meet the requirements of the query exists within the index itself. つまり、要求されたデータの取得に必要なのはインデックス ページだけで、テーブルやクラスター化インデックスのデータ ページは必要ありません。このため、全体的にディスク I/O を削減できます。That is, only the index pages, and not the data pages of the table or clustered index, are required to retrieve the requested data; therefore, reducing overall disk I/O. たとえば、テーブルの列 ab に対するクエリは、このテーブルに列 abc に基づく複合インデックスが作成されていれば、インデックスのみから指定したデータを取得できます。For example, a query of columns a and b on a table that has a composite index created on columns a, b, and c can retrieve the specified data from the index alone.

    重要

    カバリング インデックスは、ベース テーブルにアクセスせず、参照を行わずに、1 つまたは複数の似たクエリの結果を直接解決する非クラスター化インデックスに対する指定です。Covering indexes are the designation for a nonclustered index that resolves one or several similar query results directly with no access to its base table, and without incurring in lookups. そのようなインデックスは、すべての必要な非 SARGable 列をそれ自体のリーフ レベルに持っています。Such indexes have all the necessary non-SARGable columns in its leaf level. つまり、SELECT 句およびすべての WHERE 引数と JOIN 引数によって返される列はインデックスによってカバーされます。This means that the columns returned by either the SELECT clause and all the WHERE and JOIN arguments are covered by the index. テーブル自体の行と列に比べてインデックスが十分に狭い場合 (つまり、列全体の実際のサブセットである)、クエリ実行の I/O がずっと少なくなる可能性があります。There is potentially much less I/O to execute the query, if the index is narrow enough when compared to the rows and columns in the table itself, meaning it is a real sub-set of the total columns. 大きいテーブルの小さい部分を選択し、その小さい部分が固定の述語によって定義されている場合 (たとえば、少数の非 NULL 値のみを含むスパース列など)、カバリング インデックスを検討してください。Consider covering indexes when selecting a small portion of a large table, and where that small portion is defined by a fixed predicate, such as sparse columns that contain only a few non-NULL values, for example.

  • 複数のクエリを使用して同じ行を更新するよりも、1 つのステートメントでできるだけ多くの行を挿入または変更するクエリを作成します。Write queries that insert or modify as many rows as possible in a single statement, instead of using multiple queries to update the same rows. ステートメントを 1 つだけ使用することで、インデックスのメンテナンスを最適化できます。By using only one statement, optimized index maintenance could be exploited.

  • クエリの種類とクエリ内での列の使用方法を評価します。Evaluate the query type and how columns are used in the query. たとえば、完全一致検索クエリで使用される列は、非クラスター化インデックスまたはクラスター化インデックスにする適切な候補になります。For example, a column used in an exact-match query type would be a good candidate for a nonclustered or clustered index.

1 リレーショナル データベースで SARGable とは、インデックスを利用してクエリの実行速度を上げることができる、検索引数可能 (Search ARGument-able) な述語のことです。1 The term SARGable in relational databases refers to a Search ARGument-able predicate that can leverage an index to speed up the execution of the query.

列に関する注意点Column Considerations

インデックスをデザインするときは、次の列のガイドラインを考慮してください。When you design an index consider the following column guidelines:

  • クラスター化インデックスのインデックス キー長は長くならないようにします。Keep the length of the index key short for clustered indexes. また、クラスター化インデックスは一意列や非 NULL 列に作成すると効率的です。Additionally, clustered indexes benefit from being created on unique or nonnull columns.

  • ntexttextimagevarchar(max)nvarchar(max) 、および varbinary(max) データ型の列を、インデックス キー列として指定することはできません。Columns that are of the ntext, text, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot be specified as index key columns. ただし、 varchar(max)nvarchar(max)varbinary(max) 、および xml データ型は、インデックスの非キー列として非クラスター化インデックスに含めることができます。However, varchar(max), nvarchar(max), varbinary(max), and xml data types can participate in a nonclustered index as nonkey index columns. 詳細については、このガイドの '付加列インデックス'に関するセクションを参照してください。For more information, see the section 'Index with Included Columns' in this guide.

  • xml データ型は、XML インデックスでのみキー列にできます。An xml data type can only be a key column only in an XML index. 詳細については、「XML インデックス (SQL Server)」をご覧ください。For more information, see XML Indexes (SQL Server). SQL Server 2012 SP1 では、選択的 XML インデックスと呼ばれる新しい種類の XML インデックスが導入されています。SQL Server 2012 SP1 introduces a new type of XML index known as a Selective XML Index. この新しいインデックスを使用すると、SQL Server に XML 形式で格納されたデータに対するクエリのパフォーマンスが向上するため、XML データの大量のワークロードに対するインデックスの設定がはるかに高速になります。また、インデックス自体のストレージ コストを削減できるため、スケーラビリティも向上します。This new index can improve querying performance over data stored as XML in SQL Server, allow for much faster indexing of large XML data workloads, and improve scalability by reducing storage costs of the index itself. 詳細については、「選択的 XML インデックス (SXI)」を参照してください。For more information, see Selective XML Indexes (SXI).

  • 列の一意性を調べます。Examine column uniqueness. 同じ列の組み合わせに対して一意でないインデックスを作成するよりも一意インデックスを作成する方が、クエリ オプティマイザーに追加情報が提供され、インデックスの利用価値が高まります。A unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that makes the index more useful. 詳細については、このガイドの「 一意インデックスのデザイン ガイドライン 」を参照してください。For more information, see Unique Index Design Guidelines in this guide.

  • 列内のデータの分布を調べます。Examine data distribution in the column. インデックスを設定した列にほとんど一意の値がない場合や、このような列を結合する場合、クエリに時間がかかることがよくあります。Frequently, a long-running query is caused by indexing a column with few unique values, or by performing a join on such a column. これは、データとクエリにかかわる根本的な問題で、通常はこのような状況を特定しなければ解決できません。This is a fundamental problem with the data and query, and generally cannot be resolved without identifying this situation. たとえば、姓がアルファベット順に並んだ電話帳では、対象地域のすべての人が Smith や Jones という姓である場合、特定の人を探すときに役に立ちません。For example, a physical telephone directory sorted alphabetically on last name will not expedite locating a person if all people in the city are named Smith or Jones. データ分布の詳細については、「 統計」を参照してください。For more information about data distribution, see Statistics.

  • スパース列、ほとんどが NULL 値の列、さまざまなカテゴリの値を含む列、および異なる範囲の値を含む列のようなサブセットが明確に定義されている列では、フィルター選択されたインデックスの使用を検討してください。Consider using filtered indexes on columns that have well-defined subsets, for example sparse columns, columns with mostly NULL values, columns with categories of values, and columns with distinct ranges of values. フィルター選択されたインデックスを適切に設計すると、クエリのパフォーマンスが向上し、インデックスのメンテナンス コストとストレージ コストを削減できます。A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce storage costs.

  • インデックスに複数の列が含まれる場合は、列の順序を考慮します。Consider the order of the columns if the index will contain multiple columns. 等しい (=)、より大きい (>)、より小さい (<)、BETWEEN などの検索条件の WHERE 句で使用されるか、結合に含まれる列は、先頭に配置します。The column that is used in the WHERE clause in an equal to (=), greater than (>), less than (<), or BETWEEN search condition, or participates in a join, should be placed first. その他の列は、差異の程度、つまり最も差異の大きいものから最も差異の小さいものの順に配置します。Additional columns should be ordered based on their level of distinctness, that is, from the most distinct to the least distinct.

    たとえば、インデックスが LastName, FirstName として定義されている場合、このインデックスは、検索条件が WHERE LastName = 'Smith' または WHERE LastName = Smith AND FirstName LIKE 'J%'である場合に効果があります。For example, if the index is defined as LastName, FirstName the index will be useful when the search criterion is WHERE LastName = 'Smith' or WHERE LastName = Smith AND FirstName LIKE 'J%'. ただし、クエリ オプティマイザーでは、 FirstName (WHERE FirstName = 'Jane')のみで検索するクエリには、このインデックスが使用されません。However, the query optimizer would not use the index for a query that searched only on FirstName (WHERE FirstName = 'Jane').

  • 計算列のインデックス設定を検討します。Consider indexing computed columns. 詳細については、「 計算列のインデックス」を参照してください。For more information, see Indexes on Computed Columns.

インデックスの特性Index Characteristics

クエリにインデックスを設定することが適切であると判断した場合は、状況に応じて最適な種類のインデックスを選択します。After you have determined that an index is appropriate for a query, you can select the type of index that best fits your situation. インデックスの特性は、次のとおりです。Index characteristics include the following:

  • クラスター化と非クラスター化Clustered versus nonclustered
  • 一意と非一意Unique versus nonunique
  • 単一列と複数列Single column versus multicolumn
  • 昇順と降順 (インデックス内の列の並び)Ascending or descending order on the columns in the index
  • テーブル全体の非クラスター化インデックスとフィルター選択された非クラスター化インデックスFull-table versus filtered for nonclustered indexes
  • 列ストアと行ストアColumnstore versus rowstore
  • メモリ最適化テーブル用のハッシュ インデックスと非クラスター化インデックスHash versus nonclustered for Memory-Optimized tables

インデックスを最初に保存したときの特性をカスタマイズし、FILLFACTOR などのオプションを設定してパフォーマンスやメンテナンスを最適化できます。You can also customize the initial storage characteristics of the index to optimize its performance or maintenance by setting an option such as FILLFACTOR. また、パフォーマンスを最適化するために、ファイル グループやパーティション構成を使用してインデックスの保存場所を決定することもできます。Also, you can determine the index storage location by using filegroups or partition schemes to optimize performance.

ファイル グループまたはパーティション構成に対するインデックス配置Index Placement on Filegroups or Partitions Schemes

インデックスの設計について考えるときは、データベースに関連付けられたファイル グループ上にインデックスを配置することを検討する必要があります。As you develop your index design strategy, you should consider the placement of the indexes on the filegroups associated with the database. ファイル グループまたはパーティション構成を慎重に選択することで、クエリのパフォーマンスを向上できる場合があります。Careful selection of the filegroup or partition scheme can improve query performance.

既定では、インデックスが作成されるベース テーブルと同じファイル グループにインデックスも格納されます。By default, indexes are stored in the same filegroup as the base table on which the index is created. パーティション分割されていないクラスター化インデックスおよびベース テーブルは、常に同じファイル グループに存在します。A nonpartitioned clustered index and the base table always reside in the same filegroup. しかし、次の操作を実行できます。However, you can do the following:

  • ベース テーブルまたはクラスター化インデックスのファイル グループ以外のファイル グループに、非クラスター化インデックスを作成する。Create nonclustered indexes on a filegroup other than the filegroup of the base table or clustered index.
  • 複数のファイル グループにまたがるクラスター化インデックスおよび非クラスター化インデックスをパーティション分割する。Partition clustered and nonclustered indexes to span multiple filegroups.
  • あるファイル グループから別のファイル グループにテーブルを移動する。この操作を行うには、クラスター化インデックスを削除して DROP INDEX ステートメントの MOVE TO 句に新しいファイル グループまたはパーティション構成を指定するか、DROP_EXISTING 句を指定した CREATE INDEX ステートメントを使用します。Move a table from one filegroup to another by dropping the clustered index and specifying a new filegroup or partition scheme in the MOVE TO clause of the DROP INDEX statement or by using the CREATE INDEX statement with the DROP_EXISTING clause.

異なるファイル グループに非クラスター化インデックスを作成した場合、そのファイル グループが独自のコントローラーを持つ異なる物理ドライブを使用していると、パフォーマンスの向上を実現できます。By creating the nonclustered index on a different filegroup, you can achieve performance gains if the filegroups are using different physical drives with their own controllers. データおよびインデックス情報は、複数のディスク ヘッドにより並列で読み込めるようになります。Data and index information can then be read in parallel by the multiple disk heads. たとえば、ファイル グループ Table_Af1 とファイル グループ Index_Af2 が同じクエリで使用される場合、両ファイル グループが競合することなく完全に使用されるため、パフォーマンスが向上します。For example, if Table_A on filegroup f1 and Index_A on filegroup f2 are both being used by the same query, performance gains can be achieved because both filegroups are being fully used without contention. ただし、クエリによって Table_A がスキャンされる場合でも、 Index_A が参照されていないと、ファイル グループ f1 のみが使用されます。However, if Table_A is scanned by the query but Index_A is not referenced, only filegroup f1 is used. この場合、パフォーマンスは向上しません。This creates no performance gain.

アクセスの種類や実行のタイミングは事前には予測できないため、テーブルとインデックスをすべてのファイル グループにわたって分散しておくことをお勧めします。Because you cannot predict what type of access will occur and when it will occur, it could be a better decision to spread your tables and indexes across all filegroups. すべてのデータとインデックスが、すべてのディスクにわたって均等に分散されていれば、どのような方法でデータへのアクセスが行われても、確実にすべてのディスクがアクセスされます。This would guarantee that all disks are being accessed because all data and indexes are spread evenly across all disks, regardless of which way the data is accessed. この方法は、システム管理者にとっても簡単な方法になります。This is also a simpler approach for system administrators.

複数のファイル グループでのパーティション分割Partitions across multiple Filegroups

複数のファイル グループにわたるクラスター化インデックスおよび非クラスター化インデックスをパーティション分割することもできます。You can also consider partitioning clustered and nonclustered indexes across multiple filegroups. パーティション インデックスは、パーティション関数に基づいて、行方向または行ごとにパーティション分割されます。Partitioned indexes are partitioned horizontally, or by row, based on a partition function. パーティション関数では、パーティション分割列と呼ばれる特定の列の値に基づいて、一連のパーティションに各行をどのようにマップするのかを定義します。The partition function defines how each row is mapped to a set of partitions based on the values of certain columns, called partitioning columns. パーティション構成では、一連のファイル グループにパーティションをマップするように指定します。A partition scheme specifies the mapping of the partitions to a set of filegroups.

インデックスをパーティション分割すると、次のような利点があります。Partitioning an index can provide the following benefits:

  • 大きなインデックスがより管理しやすくなるスケーラブルなシステムを提供できる。Provide scalable systems that make large indexes more manageable. たとえば OLTP システムで、大きなインデックスを扱うパーティション対応のアプリケーションを実装できます。OLTP systems, for example, can implement partition-aware applications that deal with large indexes.

  • クエリをより高速かつ効率的に実行できる。Make queries run faster and more efficiently. クエリからインデックスのいくつかのパーティションへアクセスしたときに、クエリ オプティマイザーでは個別のパーティションを同時に処理し、クエリによる影響を受けないパーティションを除外できます。When queries access several partitions of an index, the query optimizer can process individual partitions at the same time and exclude partitions that are not affected by the query.

詳細については、「 Partitioned Tables and Indexes」を参照してください。For more information, see Partitioned Tables and Indexes.

インデックス並べ替え順のデザイン ガイドラインIndex Sort Order Design Guidelines

インデックスを定義する場合、インデックス キー列のデータを昇順と降順のどちらで格納する必要があるかを考慮する必要があります。When defining indexes, you should consider whether the data for the index key column should be stored in ascending or descending order. 昇順は既定の並べ替え順で、以前のバージョンの SQL ServerSQL Serverとの互換性が維持されます。Ascending is the default and maintains compatibility with earlier versions of SQL ServerSQL Server. CREATE INDEX、CREATE TABLE、および ALTER TABLE の各ステートメントの構文では、インデックスと制約の個別の列にキーワード ASC (昇順) と DESC (降順) を使用できます。The syntax of the CREATE INDEX, CREATE TABLE, and ALTER TABLE statements supports the keywords ASC (ascending) and DESC (descending) on individual columns in indexes and constraints.

インデックスにキー値が格納される順序を指定することは、テーブルを参照しているクエリに ORDER BY 句があり、そのインデックスの 1 つ以上のキー列が ORDER BY 句によって異なる方向に指定されている場合に役立ちます。Specifying the order in which key values are stored in an index is useful when queries referencing the table have ORDER BY clauses that specify different directions for the key column or columns in that index. このような場合、インデックスにより、クエリ プランで SORT 操作を実行する必要がなくなるので、クエリをより効率的に実行できるようになります。In these cases, the index can remove the need for a SORT operator in the query plan; therefore, this makes the query more efficient. たとえば、 Adventure Works CyclesAdventure Works Cycles の購買部のバイヤーが、業者から購入する製品の品質を評価する必要がある場合について考えてみます。For example, the buyers in the Adventure Works CyclesAdventure Works Cycles purchasing department have to evaluate the quality of products they purchase from vendors. バイヤーにとって最も関心があるのは、これらの業者から配送された製品の中から、返品率の高い製品を見つけ出すことです。The buyers are most interested in finding products sent by these vendors with a high rejection rate. 次のクエリに示すように、この基準を満たすデータを取得するには、 RejectedQty テーブルの Purchasing.PurchaseOrderDetail 列を降順 (大から小) に並べ替え、 ProductID 列を昇順 (小から大) に並べ替える必要があります。As shown in the following query, retrieving the data to meet this criteria requires the RejectedQty column in the Purchasing.PurchaseOrderDetail table to be sorted in descending order (large to small) and the ProductID column to be sorted in ascending order (small to large).

SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,  
    ProductID, DueDate  
FROM Purchasing.PurchaseOrderDetail  
ORDER BY RejectedQty DESC, ProductID ASC;  

次に示すこのクエリの実行プランは、クエリ オプティマイザーにより SORT 操作が使用され、ORDER BY 句で指定された順序で結果セットが返されたことを示します。The following execution plan for this query shows that the query optimizer used a SORT operator to return the result set in the order specified by the ORDER BY clause.

IndexSort1

作成したインデックスのキー列がクエリの ORDER BY 句で使用するキー列と一致する場合、クエリ プランの SORT 操作を削除できるので、クエリ プランがより効率的になります。If an index is created with key columns that match those in the ORDER BY clause in the query, the SORT operator can be eliminated in the query plan and the query plan is more efficient.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty  
ON Purchasing.PurchaseOrderDetail  
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);  

もう一度クエリを実行した後、次の実行プランは、SORT 操作が削除され、新しく作成された非クラスター化インデックスが使用されたことを示します。After the query is executed again, the following execution plan shows that the SORT operator has been eliminated and the newly created nonclustered index is used.

InsertSort2

データベース エンジンDatabase Engine は、どちらの方向でも同じように効率的に移動します。The データベース エンジンDatabase Engine can move equally efficiently in either direction. (RejectedQty DESC, ProductID ASC) として定義されたインデックスは、ORDER BY 句の列の並べ替え方向が逆転されたクエリで引き続き使用できます。An index defined as (RejectedQty DESC, ProductID ASC) can still be used for a query in which the sort direction of the columns in the ORDER BY clause are reversed. たとえば、ORDER BY 句 ORDER BY RejectedQty ASC, ProductID DESC が含まれたクエリでは、このインデックスを使用できます。For example, a query with the ORDER BY clause ORDER BY RejectedQty ASC, ProductID DESC can use the index.

並べ替え順は、キー列のみに指定できます。Sort order can be specified only for key columns. sys.index_columns カタログ ビューと INDEXKEY_PROPERTY 関数により、インデックス列が昇順と降順のどちらで格納されているかが報告されます。The sys.index_columns catalog view and the INDEXKEY_PROPERTY function report whether an index column is stored in ascending or descending order.

メタデータMetadata

これらのメタデータ ビューを使って、インデックスの属性を表示します。Use these metadata views to see attributes of indexes. 多くのアーキテクチャ情報が、これらのビューの一部に埋め込まれます。More architectural information is embedded in some of these views.

注意

列ストア インデックスの場合、すべての列は付加列としてメタデータに格納されます。For columnstore indexes, all columns are stored in the metadata as included columns. 列ストア インデックスにキー列はありません。The columnstore index does not have key columns.

sys.indexes (Transact-SQL)sys.indexes (Transact-SQL) sys.index_columns (Transact-SQL)sys.index_columns (Transact-SQL)
sys.partitions (Transact-SQL)sys.partitions (Transact-SQL) sys.internal_partitions (Transact-SQL)sys.internal_partitions (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL)sys.dm_db_index_operational_stats (Transact-SQL) sys.dm_db_index_physical_stats (Transact-SQL)sys.dm_db_index_physical_stats (Transact-SQL)
sys.column_store_segments (Transact-SQL)sys.column_store_segments (Transact-SQL) sys.column_store_dictionaries (Transact-SQL)sys.column_store_dictionaries (Transact-SQL)
sys.column_store_row_groups (Transact-SQL)sys.column_store_row_groups (Transact-SQL) sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)
sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) sys.dm_column_store_object_pool (Transact-SQL)sys.dm_column_store_object_pool (Transact-SQL)
sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)sys.dm_db_column_store_row_group_operational_stats (Transact-SQL) sys.dm_db_xtp_hash_index_stats (Transact-SQL)sys.dm_db_xtp_hash_index_stats (Transact-SQL)
sys.dm_db_xtp_index_stats (Transact-SQL)sys.dm_db_xtp_index_stats (Transact-SQL) sys.dm_db_xtp_object_stats (Transact-SQL)sys.dm_db_xtp_object_stats (Transact-SQL)
sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL) sys.dm_db_xtp_table_memory_stats (Transact-SQL)sys.dm_db_xtp_table_memory_stats (Transact-SQL)
sys.hash_indexes (Transact-SQL)sys.hash_indexes (Transact-SQL) sys.memory_optimized_tables_internal_attributes (Transact-SQL)sys.memory_optimized_tables_internal_attributes (Transact-SQL)

クラスター化インデックスのデザイン ガイドラインClustered Index Design Guidelines

クラスター化インデックスは、データ行をそのキー値に基づいて並べ替え、テーブル内に格納します。Clustered indexes sort and store the data rows in the table based on their key values. データ行自体は 1 つの順序でしか並べ替えられないため、1 つのテーブルに設定できるクラスター化インデックスは 1 つだけです。There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. ほとんどの場合、各テーブルには、次の条件を満たす単一または複数の列に基づいて定義されたクラスター化インデックスを作成することをお勧めします。With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following:

  • 頻繁に使用されるクエリに使用可能。Can be used for frequently used queries.

  • 一意性が高い。Provide a high degree of uniqueness.

    注意

    PRIMARY KEY 制約を作成すると、単一または複数の列に基づく一意のインデックスが自動的に作成されます。When you create a PRIMARY KEY constraint, a unique index on the column, or columns, is automatically created. 既定では、クラスター化インデックスが作成されますが、制約を作成する際に非クラスター化インデックスを作成するように指定することもできます。By default, this index is clustered; however, you can specify a nonclustered index when you create the constraint.

  • 範囲クエリで使用可能。Can be used in range queries.

UNIQUE プロパティを指定せずにクラスター化インデックスが作成された場合、データベース エンジンDatabase Engine により、4 バイトの uniqueifier 列が自動的にテーブルに追加されます。If the clustered index is not created with the UNIQUE property, the データベース エンジンDatabase Engine automatically adds a 4-byte uniqueifier column to the table. 必要があれば、各キーを一意にするため、 データベース エンジンDatabase Engine により自動的に uniqueifier 値が行に追加されます。When it is required, the データベース エンジンDatabase Engine automatically adds a uniqueifier value to a row to make each key unique. この列とその値は、内部的に使用されるもので、ユーザーが参照したりアクセスすることはできません。This column and its values are used internally and cannot be seen or accessed by users.

クラスター化インデックスのアーキテクチャClustered Index Architecture

SQL ServerSQL Server では、インデックスは B ツリーとして構成されます。In SQL ServerSQL Server, indexes are organized as B-Trees. インデックス B ツリー内の各ページをインデックス ノードと呼びます。Each page in an index B-tree is called an index node. B ツリーの最上位ノードはルート ノードといいます。The top node of the B-tree is called the root node. インデックス内の最下位ノードをリーフ ノードと呼びます。The bottom nodes in the index are called the leaf nodes. ルート ノードとリーフ ノードの間にあるインデックス レベルは、総称して中間レベルといいます。Any index levels between the root and the leaf nodes are collectively known as intermediate levels. クラスター化インデックスでは、リーフ ノードに基になるテーブルのデータ ページが含まれています。In a clustered index, the leaf nodes contain the data pages of the underlying table. ルート ノードと中間レベル ノードには、インデックス行を保持するインデックス ページが含まれています。The root and intermediate level nodes contain index pages holding index rows. 各インデックス行には、キー値と、B ツリー内の中間レベル ページかインデックスのリーフ レベルのデータ行のいずれかへのポインターが含まれています。Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. インデックスの各レベルのページは、二重にリンクされた一覧でリンクされています。The pages in each level of the index are linked in a doubly-linked list.

クラスター化インデックスの場合、 sys.partitionsにはインデックスで使用されるパーティションごとに 1 つの行が含まれます。この場合、 index_id は 1 と等しくなります。Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. 既定では、クラスター化インデックスのパーティションは 1 つです。By default, a clustered index has a single partition. クラスター化インデックスにパーティションが複数ある場合、各パーティションは、そのパーティションのデータを保持する B ツリー構造になります。When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. たとえば、クラスター化インデックスに 4 つのパーティションがある場合、4 つの B ツリーを持つ構造になります。この場合、パーティションごとに 1 つの B ツリーがあります。For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition.

クラスター化インデックスのデータ型によっては、各クラスター化インデックスの構造に 1 つ以上のアロケーション ユニットが含まれ、そこに特定のパーティションのデータが格納され、管理されます。Depending on the data types in the clustered index, each clustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. 各クラスター化インデックスには、パーティションごとに、少なくとも 1 つの IN_ROW_DATA アロケーション ユニットがあります。At a minimum, each clustered index will have one IN_ROW_DATA allocation unit per partition. また、クラスター化インデックスにラージ オブジェクト (LOB) 列が含まれている場合は、パーティションごとに 1 つの LOB_DATA アロケーション ユニットもあります。The clustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. さらに、行サイズの上限である 8,060 バイトを超える可変長列が含まれている場合は、パーティションごとに 1 つの ROW_OVERFLOW_DATA アロケーション ユニットがあります。It will also have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

データ チェーン内のページとページ内の行は、クラスター化インデックス キーの値に基づいて並べ替えられます。The pages in the data chain and the rows in them are ordered on the value of the clustered index key. 挿入はすべて、挿入される行のキー値が、順序付けられた既存の行の並びの中に正しく収まる位置で行われます。All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows.

次の図は、1 つのパーティション内のクラスター化インデックスの構造を示します。This illustration shows the structure of a clustered index in a single partition.

bokind2

クエリに関する注意点Query Considerations

クラスター化インデックスを作成する前に、データがどのようにアクセスされるかを理解しておいてください。Before you create clustered indexes, understand how your data will be accessed. 次の処理を行うクエリには、クラスター化インデックスを使用することを検討してください。Consider using a clustered index for queries that do the following:

  • BETWEEN、>、>=、<、<= などの演算子を使用して、ある範囲の値を返す。Return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

    クラスター化インデックスを使用して最初の値を持つ行が検索されると、後続のインデックス値がある行は物理的に必ず隣接しています。After the row with the first value is found by using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. たとえば、クエリである範囲内の販売注文番号を持つ行を取得する場合、 SalesOrderNumber 列のクラスター化インデックスを使用すると、最初の販売注文番号を含む行をすばやく検索して、最後の販売注文番号に達するまでテーブル内の後続の行をすべて取得できます。For example, if a query retrieves records between a range of sales order numbers, a clustered index on the column SalesOrderNumber can quickly locate the row that contains the starting sales order number, and then retrieve all successive rows in the table until the last sales order number is reached.

  • 大きな結果セットを返す。Return large result sets.

  • JOIN 句を使用する。通常、これらは外部キー列になります。Use JOIN clauses; typically these are foreign key columns.

  • 複数の ORDER BY 句または GROUP BY 句を使用する。Use ORDER BY or GROUP BY clauses.

    ORDER BY 句または GROUP BY 句の中で指定された列にインデックスが設定されている場合、行が既に並べ替えられているので、 データベース エンジンDatabase Engine によるデータの並べ替えが必要ないことがあります。An index on the columns specified in the ORDER BY or GROUP BY clause may remove the need for the データベース エンジンDatabase Engine to sort the data, because the rows are already sorted. このような場合は、クエリ パフォーマンスが向上します。This improves query performance.

列に関する注意点Column Considerations

通常は、クラスター化インデックス キーの定義に使用する列はできるだけ少なくする必要があります。Generally, you should define the clustered index key with as few columns as possible. 次の 1 つ以上の条件を満たす列を使用するようにしてください。Consider columns that have one or more of the following attributes:

  • 一意な値または多数の異なる値を含む。Are unique or contain many distinct values

    たとえば、従業員 ID は、従業員を一意に識別します。For example, an employee ID uniquely identifies employees. EmployeeID 列にクラスター化インデックスまたは PRIMARY KEY 制約を設定すると、従業員 ID 番号に基づいて従業員情報を検索するクエリのパフォーマンスが向上します。A clustered index or PRIMARY KEY constraint on the EmployeeID column would improve the performance of queries that search for employee information based on the employee ID number. また、 LastName列、 FirstName列、 MiddleName 列を基にクラスター化インデックスを作成することもできます。従業員レコードは、これらの列でグループ化されたりクエリが実行されることが多く、これらの列を組み合わせると高い多様性が生まれます。Alternatively, a clustered index could be created on LastName, FirstName, MiddleName because employee records are frequently grouped and queried in this way, and the combination of these columns would still provide a high degree of difference.

    ヒント

    別の指定をしない場合、PRIMARY KEY 制約を作成するときに、SQL ServerSQL Server によってその制約をサポートするクラスター化インデックスが作成されます。If not specified differently, when creating a PRIMARY KEY constraint, SQL ServerSQL Server creates a clustered index to support that constraint. PRIMARY KEY として一意性を適用するために uniqueidentifier を使用できますが、これは効率的なクラスター化キーではありません。Although a uniqueidentifier can be used to enforce uniqueness as a PRIMARY KEY, it is not an efficient clustering key. PRIMARY KEY として uniqueidentifier を使用する場合は、非クラスター化インデックスとして作成し、IDENTITY などの別の列を使用してクラスター化インデックスを作成することをお勧めします。If using a uniqueidentifier as PRIMARY KEY, the recommendation is to create it as a nonclustered index, and use another column such as an IDENTITY to create the clustered index.

  • 順次アクセスされる。Are accessed sequentially

    たとえば、製品 ID は、 Production.Product データベースの AdventureWorks2012AdventureWorks2012 テーブルにある製品を一意に識別します。For example, a product ID uniquely identifies products in the Production.Product table in the AdventureWorks2012AdventureWorks2012 database. WHERE ProductID BETWEEN 980 and 999など、順次検索が指定されているクエリでは、 ProductID列に基づくクラスター化インデックスによりパフォーマンスが向上する場合があります。Queries in which a sequential search is specified, such as WHERE ProductID BETWEEN 980 and 999, would benefit from a clustered index on ProductID. これは、行がこのキー列を基に並べ替えて格納されている場合があるためです。This is because the rows would be stored in sorted order on that key column.

  • IDENTITY として定義されている。Defined as IDENTITY.

  • テーブルから取得したデータの並べ替えに頻繁に使用される。Used frequently to sort the data retrieved from a table.

    このような列を基にテーブルをクラスター化する (つまり、物理的に並べ替える) と、この列に対してクエリを実行するたびに並べ替えにかかるコストを節約できるため便利です。It can be a good idea to cluster, that is physically sort, the table on that column to save the cost of a sort operation every time the column is queried.

次のような場合は、クラスター化インデックスの使用は適していません。Clustered indexes are not a good choice for the following attributes:

  • 頻繁に変更される列Columns that undergo frequent changes

    データベース エンジンDatabase Engine では各行のデータ値を物理的な順序で維持する必要があるので、データが変更されると行全体が移動します。This causes in the whole row to move, because the データベース エンジンDatabase Engine must keep the data values of a row in physical order. データが頻繁に変更される大規模トランザクション処理システムでは、特にこの点に留意してください。This is an important consideration in high-volume transaction processing systems in which data is typically volatile.

  • 広範なキーWide keys

    広範なキーは、複数の列または複数のサイズの大きな列を組み合わせたものです。Wide keys are a composite of several columns or several large-size columns. クラスター化インデックスのキー値は、すべての非クラスター化インデックスにより、参照キーとして使用されます。The key values from the clustered index are used by all nonclustered indexes as lookup keys. 非クラスター化インデックスのエントリには、クラスター化キー以外に、非クラスター化インデックスのキー列も格納されるため、同じテーブルに非クラスター化インデックスが定義されている場合は、サイズがかなり大きくなります。Any nonclustered indexes defined on the same table will be significantly larger because the nonclustered index entries contain the clustering key and also the key columns defined for that nonclustered index.

非クラスター化インデックスのデザイン ガイドラインNonclustered Index Design Guidelines

非クラスター化インデックスには、インデックス キー値、およびテーブル データの格納場所を指す行ロケーターが含まれています。A nonclustered index contains the index key values and row locators that point to the storage location of the table data. 1 つのテーブルまたはインデックス付きビューに複数の非クラスター化インデックスを作成できます。You can create multiple nonclustered indexes on a table or indexed view. 一般に、非クラスター化インデックスは、頻繁に使用するクエリで、クラスター化インデックスで対応されないクエリのパフォーマンスを向上するようにデザインします。Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index.

クエリ オプティマイザーでデータ値を検索するときは、本の索引を使用する場合と同じように、非クラスター化インデックスを検索してテーブル内でのデータ値の位置を探し、その位置から直接データを取得します。Similar to the way you use an index in a book, the query optimizer searches for a data value by searching the nonclustered index to find the location of the data value in the table and then retrieves the data directly from that location. 非クラスター化インデックスには、クエリの検索対象であるデータ値のテーブル内での位置を正確に記述するエントリが格納されているので、完全一致比較クエリの場合は非クラスター化インデックスが最適です。This makes nonclustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries. たとえば、 HumanResources. Employee テーブルに対してクエリを実行し、ある 1 人の上司に直属するすべての従業員を取得する場合、クエリ オプティマイザーは IX_Employee_ManagerIDをキー列として、非クラスター化インデックス ManagerID を使用することができます。For example, to query the HumanResources. Employee table for all employees that report to a specific manager, the query optimizer might use the nonclustered index IX_Employee_ManagerID; this has ManagerID as its key column. クエリ オプティマイザーはこのインデックスの中から、指定された ManagerIDと一致するすべてのエントリを迅速に検索できます。The query optimizer can quickly find all entries in the index that match the specified ManagerID. インデックスの各エントリのポインターは、テーブル (またはクラスター化インデックス) の、対応するデータが見つかる正確なページおよび行を指しています。Each index entry points to the exact page and row in the table, or clustered index, in which the corresponding data can be found. クエリ オプティマイザーは、インデックスの中からすべてのエントリを検出した後、正確なページおよび行に直接移動してデータを取得できます。After the query optimizer finds all entries in the index, it can go directly to the exact page and row to retrieve the data.

非クラスター化インデックスのアーキテクチャNonclustered Index Architecture

非クラスター化インデックスもクラスター化インデックスと同じ B ツリー構造ですが、次に示す大きな相違点があります。Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

  • 基になるテーブルのデータ行は、非クラスター化キーに基づいた順序で並べ替えられたり格納されたりしません。The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.

  • 非クラスター化インデックスのリーフ レベルは、データ ページではなくインデックス ページで構成されます。The leaf level of a nonclustered index is made up of index pages instead of data pages.

非クラスター化インデックス行内の行ロケーターは、次に示すような、行を指すポインターまたは行のクラスター化インデックス キーのいずれかです。The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

  • テーブルがヒープで、クラスター化インデックスが設定されていない場合、行ロケーターはその行へのポインターです。If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. このポインターは、ファイル識別子 (ID)、ページ番号、およびそのページ上での行の番号で構成されます。The pointer is built from the file identifier (ID), page number, and number of the row on the page. ポインター全体は、RID (行 ID) と呼ばれます。The whole pointer is known as a Row ID (RID).

  • テーブルにクラスター化インデックスがある場合、またはインデックスがインデックス付きビューにある場合は、行ロケーターが行のクラスター化インデックス キーになります。If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row.

非クラスター化インデックスの場合、sys.partitions にはインデックスで使用されるパーティションごとに 1 つの行が含まれます。この場合、index_id は 1 より大きくなります。Nonclustered indexes have one row in sys.partitions with index_id > 1 for each partition used by the index. 既定では、非クラスター化インデックスのパーティションは 1 つです。By default, a nonclustered index has a single partition. 非クラスター化インデックスにパーティションが複数ある場合、各パーティションは、その特定のパーティションに対してインデックス行を保持する B ツリー構造になります。When a nonclustered index has multiple partitions, each partition has a B-tree structure that contains the index rows for that specific partition. たとえば、非クラスター化インデックスに 4 つのパーティションがある場合、4 つの B ツリーを持つ構造になります。この場合、パーティションごとに 1 つの B ツリーがあります。For example, if a nonclustered index has four partitions, there are four B-tree structures, with one in each partition.

非クラスター化インデックスのデータ型によっては、各非クラスター化インデックスの構造に 1 つ以上のアロケーション ユニットが含まれ、そこに特定のパーティションのデータが格納され、管理されます。Depending on the data types in the nonclustered index, each nonclustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. 各非クラスター化インデックスには、インデックス B ツリーのページが格納されているパーティションごとに、少なくとも 1 つの IN_ROW_DATA アロケーション ユニットがあります。At a minimum, each nonclustered index will have one IN_ROW_DATA allocation unit per partition that stores the index B-tree pages. また、非クラスター化インデックスにラージ オブジェクト (LOB) 列が含まれている場合は、パーティションごとに 1 つの LOB_DATA アロケーション ユニットもあります。The nonclustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. さらに、行サイズの上限である 8,060 バイトを超える可変長列が含まれている場合は、パーティションごとに 1 つの ROW_OVERFLOW_DATA があります。Additionally, it will have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

次の図に、1 つのパーティション内の非クラスター化インデックスの構造を示します。The following illustration shows the structure of a nonclustered index in a single partition.

bokind1a

データベースに関する注意点Database Considerations

非クラスター化インデックスをデザインするときは、データベースの特性を考慮してください。Consider the characteristics of the database when designing nonclustered indexes.

  • 更新の必要が少なく、容量の大きいデータベースまたはテーブルの場合、クエリのパフォーマンスを向上させるには非クラスター化インデックスを多数作成するのが適しています。Databases or tables with low update requirements, but large volumes of data can benefit from many nonclustered indexes to improve query performance. テーブル全体の非クラスター化インデックスと比較してクエリのパフォーマンスが向上し、インデックスのストレージ コストとインデックスのメンテナンス コストが削減されるように、適切に定義されたデータのサブセットに対してフィルター選択されたインデックスを作成することを検討してください。Consider creating filtered indexes for well-defined subsets of data to improve query performance, reduce index storage costs, and reduce index maintenance costs compared with full-table nonclustered indexes.

    読み取り専用データが中心の意思決定支援システム アプリケーションおよびデータベースは、非クラスター化インデックスを多数作成するのが適しています。Decision Support System applications and databases that contain primarily read-only data can benefit from many nonclustered indexes. 非クラスター化インデックスを多数作成すると、クエリ オプティマイザーにより最速のアクセス手段が判断される際の選択肢になるインデックスが多く、データベースの更新頻度が低いのでインデックスのメンテナンスによってパフォーマンスが低下することはありません。The query optimizer has more indexes to choose from to determine the fastest access method, and the low update characteristics of the database mean index maintenance will not impede performance.

  • テーブルの更新頻度が高いオンライン トランザクション処理 (OLTP) アプリケーションおよびデータベースに、インデックスを過度に作成することはお勧めしません。Online Transaction Processing (OLTP) applications and databases that contain heavily updated tables should avoid over-indexing. また、インデックスの列数はできる限り抑えてください。Additionally, indexes should be narrow, that is, with as few columns as possible.

    1 つのテーブルに多数のインデックスがあると、テーブル内のデータが変更された場合にインデックスをすべて調整する必要があるので、INSERT、UPDATE、DELETE、および MERGE の各ステートメントのパフォーマンスに影響します。Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes.

クエリに関する注意点Query Considerations

非クラスター化インデックスを作成する前に、データがどのようにアクセスされるかを理解しておいてください。Before you create nonclustered indexes, you should understand how your data will be accessed. 次に示す特徴があるクエリには非クラスター化インデックスを使用することを検討してください。Consider using a nonclustered index for queries that have the following attributes:

  • 複数の JOIN 句または GROUP BY 句を使用する。Use JOIN or GROUP BY clauses.

    結合操作やグループ化操作に使用する列の非クラスター化インデックスを複数作成し、外部キー列にクラスター化インデックスを作成してください。Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.

  • 大きな結果セットを返さないクエリ。Queries that do not return large result sets.

    大きなテーブルから適切に定義された行のサブセットを返すクエリに対応するために、フィルター選択されたインデックスを作成してください。Create filtered indexes to cover queries that return a well-defined subset of rows from a large table.

    ヒント

    通常、CREATE INDEX ステートメントの WHERE 句は、カバーされているクエリの WHERE 句と一致します。Typically the WHERE clause of the CREATE INDEX statement matches the WHERE clause of a query being covered.

  • 完全一致を返すクエリの検索条件 (WHERE 句など) に頻繁に使用される列を含んでいる。Contain columns frequently involved in search conditions of a query, such as WHERE clause, that return exact matches.

    ヒント

    新しいインデックスを追加するときは、コストと利点を検討します。Consider the cost versus benefit when adding new indexes. 既存のインデックスに追加クエリのニーズを統合する方が望ましい場合があります。It may be preferable to consolidate additional query needs into an existing index. たとえば、既存のインデックスに 1 つまたは 2 つの余分なリーフ レベル列を追加すると複数の重要なクエリをカバーできる場合は、重要な各クエリを個別に完全にカバーする 1 つのインデックスを作成するのではなく、そのようにします。For example, consider adding one or two extra leaf level columns to an existing index, if it allows coverage of several critical queries, instead of having one exactly covering index per each critical query.

列に関する注意点Column Considerations

次に示す特徴に 1 つ以上該当する列を考慮してください。Consider columns that have one or more of these attributes:

  • クエリを包括している。Cover the query.

    インデックスにクエリのすべての列が含まれていると、パフォーマンスが向上します。Performance gains are achieved when the index contains all columns in the query. クエリ オプティマイザーではインデックス内ですべての列値を参照できるので、テーブルやクラスター化インデックスのデータにアクセスすることがなく、ディスク I/O 操作が少なくてすみます。The query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations. 列数の多いインデックス キーを作成する代わりに、包括する列を追加するには、付加列インデックスを使用します。Use index with included columns to add covering columns instead of creating a wide index key.

    テーブルにクラスター化インデックスがある場合、クラスター化インデックスに定義された列がテーブルの各非クラスター化インデックスの末尾に自動的に付加されます。If the table has a clustered index, the column or columns defined in the clustered index are automatically appended to the end of each nonclustered index on the table. その結果、非クラスター化インデックスの定義にクラスター化インデックスの列を指定することなく、インデックスにはクエリで使用するすべての列が含まれることになります。This can produce a covered query without specifying the clustered index columns in the definition of the nonclustered index. たとえば、あるテーブルの列 Cにクラスター化インデックスがある場合、列 B および A の非クラスター化インデックスのキー値は列 BA、および Cとなります。For example, if a table has a clustered index on column C, a nonclustered index on columns B and A will have as its key values columns B, A, and C.

  • 姓と名の組み合わせなど、多数の異なる値が格納されている (他の列にクラスター化インデックスが使用されている場合)。Lots of distinct values, such as a combination of last name and first name, if a clustered index is used for other columns.

    1 と 0 のみなど異なる値が少数しかない場合、テーブル スキャンを行う方が通常は効率的なので、ほとんどのクエリではインデックスが使用されません。If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is generally more efficient. このようなデータの場合は、少数の行のみに含まれる異なる値に対してフィルター選択されたインデックスを作成することを検討してください。For this type of data, consider creating a filtered index on a distinct value that only occurs in a small number of rows. たとえば、ほとんどの値が 0 の場合は、クエリ オプティマイザーで 1 を含むデータ行に対してフィルター選択されたインデックスを使用できます。For example, if most of the values are 0, the query optimizer might use a filtered index for the data rows that contain 1.

付加列の使用による非クラスター化インデックスの拡張Use Included Columns to Extend Nonclustered Indexes

非クラスター化インデックスのリーフ レベルに非キー列を追加することにより、非クラスター化インデックスの機能を拡張できます。You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. 非キー列を含めることにより、より多くのクエリをカバーする非クラスター化インデックスを作成できます。By including nonkey columns, you can create nonclustered indexes that cover more queries. これは、非キー列には次の利点があるためです。This is because the nonkey columns have the following benefits:

  • 非キー列には、インデックス キー列として許可されていないデータ型を設定できる。They can be data types not allowed as index key columns.

  • インデックス キー列の数やインデックス キーのサイズを計算するときに、 データベース エンジンDatabase Engine では非キー列が考慮されない。They are not considered by the データベース エンジンDatabase Engine when calculating the number of index key columns or index key size.

クエリ内のすべての列が、キー列または非キー列のいずれかとしてインデックスに含まれるているとき、非キー付加列を含むインデックスにより、クエリ パフォーマンスが大幅に向上します。An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. クエリ オプティマイザーではインデックス内のすべての列値を参照できるので、テーブルやクラスター化インデックスのデータにアクセスすることがなく、ディスク I/O 操作が少なくて済むため、パフォーマンスが向上します。Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

注意

クエリによって参照されるすべての列がインデックスに含まれているときは、一般的に、そのインデックスはクエリをカバーしていると呼ばれます。When an index contains all the columns referenced by the query it is typically referred to as covering the query.

キー列がインデックスのすべてのレベルに格納されている場合は、非キー列はリーフ レベルだけに格納されます。While key columns are stored at all levels of the index, nonkey columns are stored only at the leaf level.

サイズ制限を回避するための付加列の使用Using Included Columns to Avoid Size Limits

非クラスター化インデックスに非キー列を含めることで、現在のインデックス サイズの制限 (最大 16 個のキー列と最大 900 バイトのインデックス キーのサイズ) を超えないようにすることができます。You can include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. インデックス キー列の数やインデックス キーのサイズを計算するときに、 データベース エンジンDatabase Engine では非キー列が考慮されません。The データベース エンジンDatabase Engine does not consider nonkey columns when calculating the number of index key columns or index key size.
たとえば、 Document テーブルにある次の列にインデックスを設定するとします。For example, assume that you want to index the following columns in the Document table:

  • Title nvarchar(50)
  • Revision nchar(5)
  • FileName nvarchar(400)

ncharnvarchar データ型は各文字に 2 バイトを要するため、これら 3 つの列が含まれるインデックスは 900 バイトのサイズ制限を 10 バイト超えます (455 * 2)。Because the nchar and nvarchar data types require 2 bytes for each character, an index that contains these three columns would exceed the 900 byte size limitation by 10 bytes (455 * 2). INCLUDE ステートメントの CREATE INDEX 句を使用することにより、インデックス キーを (Title, Revision) として定義し、 FileName を非キー列として定義できます。By using the INCLUDE clause of the CREATE INDEX statement, the index key could be defined as (Title, Revision) and FileName defined as a nonkey column. その結果、インデックス キーのサイズが 110 バイト (55 * 2) になりましたが、インデックスには必要な列がすべて含まれています。In this way, the index key size would be 110 bytes (55 * 2), and the index would still contain all the required columns. このようなインデックスは、次のステートメントで作成されます。The following statement creates such an index.

CREATE INDEX IX_Document_Title   
ON Production.Document (Title, Revision)   
INCLUDE (FileName);   
付加列インデックスのガイドラインIndex with Included Columns Guidelines

付加列非クラスター化インデックスを設計するときは、次のガイドラインについて考慮してください。When you design nonclustered indexes with included columns consider the following guidelines:

  • 非キー列は、CREATE INDEX ステートメントの INCLUDE 句で定義されます。Nonkey columns are defined in the INCLUDE clause of the CREATE INDEX statement.

  • 非キー列は、テーブルやインデックス付きビューの非クラスター化インデックスにのみ定義できます。Nonkey columns can only be defined on nonclustered indexes on tables or indexed views.

  • textntext、および imageを除く、すべてのデータ型を使用できます。All data types are allowed except text, ntext, and image.

  • 決定的な計算列、および正確または不正確な計算列を、付加列にできます。Computed columns that are deterministic and either precise or imprecise can be included columns. 詳細については、「 計算列のインデックス」を参照してください。For more information, see Indexes on Computed Columns.

  • キー列と同様に、計算列が imagentext、および text の各データ型から派生している場合は、計算列のデータ型が非キー インデックス列として許可されている限り、非キー (付加) 列にできます。As with key columns, computed columns derived from image, ntext, and text data types can be nonkey (included) columns as long as the computed column data type is allowed as a nonkey index column.

  • INCLUDE リストとキー列リストの両方に、列名を指定することはできません。Column names cannot be specified in both the INCLUDE list and in the key column list.

  • INCLUDE リスト内で列名を繰り返すことはできません。Column names cannot be repeated in the INCLUDE list.

列サイズのガイドラインColumn Size Guidelines
  • キー列は少なくとも 1 つ定義する必要があります。At least one key column must be defined. 非キー列の最大数は 1,023 列です。The maximum number of nonkey columns is 1023 columns. これは、テーブルの最大列数から 1 を引いた数です。This is the maximum number of table columns minus 1.

  • 非キーを除くインデックス キー列は、既存のインデックス サイズの制限 (最大 16 個のキー列、インデックス キーの合計サイズ 900 バイト) に従う必要があります。Index key columns, excluding nonkeys, must follow the existing index size restrictions of 16 key columns maximum, and a total index key size of 900 bytes.

  • すべての非キー列の合計サイズは、INCLUDE 句で指定された列のサイズによってのみ制限されます。たとえば、 varchar(max) 列は 2 GB に制限されます。The total size of all nonkey columns is limited only by the size of the columns specified in the INCLUDE clause; for example, varchar(max) columns are limited to 2 GB.

列の変更のガイドラインColumn Modification Guidelines

付加列として定義されたテーブル列を変更するときには、次の制限が適用されます。When you modify a table column that has been defined as an included column, the following restrictions apply:

  • インデックスを先に削除しない限り、非キー列をテーブルから削除できません。Nonkey columns cannot be dropped from the table unless the index is dropped first.

  • 次の操作以外に、非キー列は変更できません。Nonkey columns cannot be changed, except to do the following:

    • 列の NULL 値の許容を NOT NULL から NULL に変更する。Change the nullability of the column from NOT NULL to NULL.

    • varcharnvarchar、または varbinary の各列の長さを拡張します。Increase the length of varchar, nvarchar, or varbinary columns.

      注意

      これらの列の変更の制限は、インデックス キー列にも適用されます。These column modification restrictions also apply to index key columns.

設計上の推奨事項Design Recommendations

検索や参照に使用される列のみがキー列になるように、大きなサイズのインデックス キーを使用して、非クラスター化インデックスを設計し直します。Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. クエリをカバーする他のすべての列を、非キー付加列にします。Make all other columns that cover the query included nonkey columns. その結果、クエリをカバーするために必要なすべての列を含むことができますが、インデックス キー自体は小さく、効率的です。In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

たとえば、次のクエリをカバーするインデックスを設計するとします。For example, assume that you want to design an index to cover the following query.

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode  
FROM Person.Address  
WHERE PostalCode BETWEEN N'98000' and N'99999';  

クエリをカバーするには、インデックスに各列を定義する必要があります。To cover the query, each column must be defined in the index. すべての列をキー列として定義でき、その場合キーのサイズは 334 バイトになります。Although you could define all columns as key columns, the key size would be 334 bytes. 実際に検索条件に使用されている唯一の列は、30 バイトの長さの PostalCode 列なので、より効果的な設計のインデックスにするには、キー列として PostalCode を定義し、他のすべての列を非キー列として含めます。Because the only column actually used as search criteria is the PostalCode column, having a length of 30 bytes, a better index design would define PostalCode as the key column and include all other columns as nonkey columns.

次のステートメントにより、クエリをカバーする付加列インデックスが作成されます。The following statement creates an index with included columns to cover the query.

CREATE INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode)  
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
パフォーマンスに関する考慮事項Performance Considerations

不要な列は追加しないでください。Avoid adding unnecessary columns. キーまたは非キーのインデックス列を追加しすぎると、次のようなパフォーマンス上の問題が発生することがあります。Adding too many index columns, key or nonkey, can have the following performance implications:

  • 1 ページに収まるインデックス行が少なくなります。Fewer index rows will fit on a page. これにより、ディスク I/O が増加しキャッシュ効率が低下します。This could create I/O increases and reduced cache efficiency.

  • インデックスを格納するために、さらに多くのディスク領域が必要になります。More disk space will be required to store the index. 特に、 varchar(max)nvarchar(max)varbinary(max) 、または xml のデータ型を非キー インデックス列として追加すると、必要なディスク領域が大幅に増加します。In particular, adding varchar(max), nvarchar(max), varbinary(max), or xml data types as nonkey index columns may significantly increase disk space requirements. これは、列の値がインデックスのリーフ レベルにコピーされるためです。This is because the column values are copied into the index leaf level. そのため、列の値がインデックスとベース テーブルの両方に存在します。Therefore, they reside in both the index and the base table.

  • インデックスのメンテナンスによって、基になるテーブルやインデックス付きビューに対する変更、挿入、更新、削除にかかる時間が長くなる場合があります。Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.

データ変更によるパフォーマンスへの影響や追加ディスク領域の要件よりも、クエリのパフォーマンスから得られる利点の方が大きいかどうかを判断する必要があります。You will have to determine whether the gains in query performance outweigh the affect to performance during data modification and in additional disk space requirements.

一意インデックスのデザイン ガイドラインUnique Index Design Guidelines

一意インデックスを使用すると、インデックス キーの値が重複することがないので、テーブルのすべての行を一意にすることができます。A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. 一意であることがデータ自体の特性である場合にだけ、一意インデックスを指定します。Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. たとえば、主キーが NationalIDNumber で、 HumanResources.Employee テーブルの EmployeeID列の値が必ず一意になるようにする場合は、 NationalIDNumber 列で UNIQUE 制約を作成します。For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumber column. ユーザーが複数の従業員に対してその列に同じ値を入力しようとすると、エラー メッセージが表示され、重複する値は入力されません。If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.

複数列に一意インデックスを指定すると、インデックス キーの値の組み合わせはそれぞれ一意になります。With multicolumn unique indexes, the index guarantees that each combination of values in the index key is unique. たとえば、 LastName列、 FirstName列、および MiddleName 列の組み合わせに一意インデックスを作成した場合、テーブル内の 2 つの行がこれらの列に対して同じ値の組み合わせを持つことはできません。For example, if a unique index is created on a combination of LastName, FirstName, and MiddleName columns, no two rows in the table could have the same combination of values for these columns.

クラスター化インデックスと非クラスター化インデックスは共に一意インデックスにできます。Both clustered and nonclustered indexes can be unique. 列のデータが一意である場合、1 つのテーブルに 1 つの一意クラスター化インデックスと、複数の一意非クラスター化インデックスを作成できます。Provided that the data in the column is unique, you can create both a unique clustered index and multiple unique nonclustered indexes on the same table.

一意インデックスの利点を次に示します。The benefits of unique indexes include the following:

  • 定義された列のデータの整合性が保証されます。Data integrity of the defined columns is ensured.

  • クエリ オプティマイザーの役に立つ追加情報が提供されます。Additional information helpful to the query optimizer is provided.

PRIMARY KEY 制約または UNIQUE 制約を作成すると、指定した列に一意インデックスが自動的に作成されます。Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. UNIQUE 制約を作成することと、制約とは無関係の一意インデックスを作成することの間に大きな違いはありません。There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. データ検証も同じ方式で行われ、クエリ オプティマイザーでは、制約によって作成された一意インデックスと手動で作成された一意インデックスは区別されません。Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or manually created. ただし、データの整合性を維持することを目的とした列には、列に UNIQUE 制約または PRIMARY KEY 制約を作成する必要があります。However, you should create a UNIQUE or PRIMARY KEY constraint on the column when data integrity is the objective. この作業を行うことで、インデックスの目的が明確になります。By doing this the objective of the index will be clear.

考慮事項Considerations

  • 重複するキー値がデータに存在する場合は、一意インデックス、UNIQUE 制約、または PRIMARY KEY 制約を作成できません。A unique index, UNIQUE constraint, or PRIMARY KEY constraint cannot be created if duplicate key values exist in the data.

  • データが一意のときに一意性を強制する場合は、一意インデックスを作成する方が、同じ組み合わせの列に一意でないインデックスを作成するよりも、より効率的な実行プランを作成できる追加情報がクエリ オプティマイザーに提供されます。If the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. この場合、一意インデックスを作成することをお勧めします (できるだけ UNIQUE 制約を作成することをお勧めします)。Creating a unique index (preferably by creating a UNIQUE constraint) is recommended in this case.

  • 一意非クラスター化インデックスには、付加非キー列を含めることができます。A unique nonclustered index can contain included nonkey columns. 詳細については、 付加列インデックスに関する記述を参照してください。For more information, see Index with Included Columns.

フィルター選択されたインデックスのデザイン ガイドラインFiltered Index Design Guidelines

フィルター選択されたインデックスは、最適化された非クラスター化インデックスであり、適切に定義されたデータのサブセットから選択するクエリに対応する際に特に適しています。A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. フィルター選択されたインデックスは、フィルター述語を使用して、テーブル内の一部の行にインデックスを作成します。It uses a filter predicate to index a portion of rows in the table. フィルター選択されたインデックスを適切にデザインすると、クエリのパフォーマンスが向上し、インデックスのメンテナンス コストを削減して、テーブル全体のインデックスと比較してインデックスのストレージ コストを削減することができます。A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

適用対象: SQL Server 2008SQL Server 2008 から SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

フィルター選択されたインデックスは、テーブル全体のインデックスよりも次の点で優れています。Filtered indexes can provide the following advantages over full-table indexes:

  • クエリのパフォーマンスとプランの品質の向上Improved query performance and plan quality

    フィルター選択されたインデックスを適切にデザインすると、クエリのパフォーマンスと実行プランの品質が向上します。これは、このインデックスが、テーブル全体の非クラスター化インデックスよりも小さく、フィルター選択された統計情報を含むためです。A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. フィルター選択された統計情報は、フィルター選択されたインデックスの行のみを対象としているため、テーブル全体の統計情報よりも正確です。The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

  • インデックスのメンテナンス コストの削減Reduced index maintenance costs

    インデックスのメンテナンスが行われるのは、データ操作言語 (DML) ステートメントがインデックス内のデータに影響を与える場合のみです。An index is maintained only when data manipulation language (DML) statements affect the data in the index. フィルター選択されたインデックスにより、インデックスのメンテナンス コストは、テーブル全体の非クラスター化インデックスと比較して削減されます。これは、フィルター選択されたインデックスは小さく、インデックス内のデータが影響を受けた場合にのみメンテナンスされるためです。A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. 特に、含まれるデータにほとんど影響がない場合は、多数のフィルター選択されたインデックスを作成できます。It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. 同様に、フィルター選択されたインデックスに頻繁に影響を受けるデータのみが含まれている場合は、インデックスのサイズを小さくすると、統計情報の更新コストが削減されます。Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.

  • インデックスのストレージ コストの削減Reduced index storage costs

    テーブル全体のインデックスが不要な場合は、フィルター選択されたインデックスを作成すると、非クラスター化インデックスのディスク ストレージを削減できます。Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. ストレージ要件をあまり増やすことなく、テーブル全体の非クラスター化インデックスを複数のフィルター選択されたインデックスに置き換えることができます。You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

フィルター選択されたインデックスは、クエリが SELECT ステートメントで参照する、適切に定義されたデータのサブセットが列に含まれている場合に役立ちます。Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. 以下に例を示します。Examples are:

  • NULL 以外の値を少数しか含まないスパース列。Sparse columns that contain only a few non-NULL values.

  • 複数のカテゴリのデータを含む異種列。Heterogeneous columns that contain categories of data.

  • 金額、時間、日付など、値の範囲を含む列。Columns that contain ranges of values such as dollar amounts, time, and dates.

  • 列の値の単純な比較ロジックで定義されるテーブル パーティション。Table partitions that are defined by simple comparison logic for column values.

フィルター選択されたインデックスのメンテナンス コストの削減は、そのインデックスに含まれる行数がテーブル全体のインデックスと比較して少ない場合に、最も明確になります。Reduced maintenance costs for filtered indexes are most noticeable when the number of rows in the index is small compared with a full-table index. フィルター選択されたインデックスにテーブル内のほとんどの行が含まれる場合は、テーブル全体のインデックスよりもメンテナンス コストがかかることがあります。If the filtered index includes most of the rows in the table, it could cost more to maintain than a full-table index. この場合は、フィルター選択されたインデックスではなく、テーブル全体のインデックスを使用する必要があります。In this case, you should use a full-table index instead of a filtered index.

フィルター選択されたインデックスは 1 つのテーブルで定義され、単純な比較演算子のみをサポートします。Filtered indexes are defined on one table and only support simple comparison operators. 複数のテーブルを参照するフィルター式や複雑なロジックを含むフィルター式が必要な場合は、ビューを作成する必要があります。If you need a filter expression that references multiple tables or has complex logic, you should create a view.

デザインに関する考慮事項Design Considerations

フィルター選択されたインデックスを効果的にデザインするには、アプリケーションで使用されるクエリを把握し、そのクエリがデータのサブセットとどのように関連するかを理解することが重要です。In order to design effective filtered indexes, it is important to understand what queries your application uses and how they relate to subsets of your data. 適切に定義されたサブセットを持つデータの例として、ほとんどが NULL 値の列、異種カテゴリの値を含む列、および異なる範囲の値を含む列が挙げられます。Some examples of data that have well-defined subsets are columns with mostly NULL values, columns with heterogeneous categories of values and columns with distinct ranges of values. 次のデザインに関する考慮事項では、フィルター選択されたインデックスがテーブル全体のインデックスよりも優れている場合のさまざまなシナリオを示します。The following design considerations give a variety of scenarios for when a filtered index can provide advantages over full-table indexes.

ヒント

非クラスター化列ストア インデックスの定義で、フィルター適用条件の使用をサポートします。The nonclustered columnstore index definition supports using a filtered condition. OLTP テーブルに列ストア インデックスを追加することによるパフォーマンスへの影響を最小限に抑えるには、フィルター条件を使って、用して、運用ワークロードのコールド データのみに、非クラスター化列ストア インデックスを作成します。To minimize the performance impact of adding a columnstore index on an OLTP table, use a filtered condition to create a nonclustered columnstore index on only the cold data of your operational workload.

データのサブセットのフィルター選択されたインデックスFiltered Indexes for subsets of data

クエリに関連する少数の値だけが列に含まれている場合、値のサブセットにフィルター選択されたインデックスを作成できます。When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. たとえば、列の値がほとんど NULL の場合に、クエリで常に NULL 以外の値を選択するときは、NULL 以外のデータ行にフィルター選択されたインデックスを作成できます。For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. 作成したインデックスは、同じキー列に定義されているテーブル全体の非クラスター化インデックスよりも小さく、メンテナンス コストが少なくなります。The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

たとえば、 AdventureWorks2012 データベースには、 Production.BillOfMaterials という 2,679 行のテーブルがあります。For example, the AdventureWorks2012 database has a Production.BillOfMaterials table with 2679 rows. EndDate 列では、NULL 以外の値を含む行は 199 行だけで、他の 2,480 行には NULL が含まれています。The EndDate column has only 199 rows that contain a non-NULL value and the other 2480 rows contain NULL. 次のフィルター選択されたインデックスは、インデックスで定義された列を返し、 EndDateで NULL 以外の値を含む行のみを選択するクエリに対応します。The following filtered index would cover queries that return the columns defined in the index and that select only rows with a non-NULL value for EndDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate  
    ON Production.BillOfMaterials (ComponentID, StartDate)  
    WHERE EndDate IS NOT NULL ;  
GO  

フィルター選択されたインデックス FIBillOfMaterialsWithEndDate は、次のクエリに対して有効です。The filtered index FIBillOfMaterialsWithEndDate is valid for the following query. クエリ実行プランを表示して、クエリ オプティマイザーでフィルター選択されたインデックスが使用されたかどうかを確認できます。You can display the query execution plan to determine if the query optimizer used the filtered index.

SELECT ProductAssemblyID, ComponentID, StartDate   
FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL   
    AND ComponentID = 5   
    AND StartDate > '20080101' ;  

フィルター選択されたインデックスの作成方法およびフィルター選択されたインデックスの述語式の定義方法の詳細については、「 フィルター選択されたインデックスの作成」を参照してください。For more information about how to create filtered indexes and how to define the filtered index predicate expression, see Create Filtered Indexes.

異種データのフィルター選択されたインデックスFiltered Indexes for heterogeneous data

テーブルに異種データの行が含まれている場合、1 つ以上のカテゴリのデータに対してフィルター選択されたインデックスを作成できます。When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data.

たとえば、 Production.Product テーブルに示される製品がそれぞれ ProductSubcategoryIDに割り当てられ、Bikes、Components、Clothing、Accessories の製品カテゴリに関連付けられています。For example, the products listed in the Production.Product table are each assigned to a ProductSubcategoryID, which are in turn associated with the product categories Bikes, Components, Clothing, or Accessories. Production.Product テーブル内にあるこうしたカテゴリの列の値はあまり密接に関連していないので、異種カテゴリとなります。These categories are heterogeneous because their column values in the Production.Product table are not closely correlated. たとえば、 ColorReorderPointListPriceWeightClass、および Style の各列には、各製品カテゴリで固有の特性があります。For example, the columns Color, ReorderPoint, ListPrice, Weight, Class, and Style have unique characteristics for each product category. サブカテゴリ 27 ~ 36 を含む付属品に対して頻繁に使用されるクエリがあるとします。Suppose that there are frequent queries for accessories which have subcategories between 27 and 36 inclusive. 次の例に示すように、付属品のサブカテゴリにフィルター選択されたインデックスを作成することで、付属品に対するクエリのパフォーマンスを向上させることができます。You can improve the performance of queries for accessories by creating a filtered index on the accessories subcategories as shown in the following example.

CREATE NONCLUSTERED INDEX FIProductAccessories  
    ON Production.Product (ProductSubcategoryID, ListPrice)   
        Include (Name)  
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;  

フィルター選択されたインデックス FIProductAccessories は次のクエリに対応します。The filtered index FIProductAccessories covers the following query because the query

これは、クエリ結果がインデックスに含まれ、クエリ プランにベース テーブルの参照が含まれないためです。results are contained in the index and the query plan does not include a base table lookup. たとえば、クエリ述語式 ProductSubcategoryID = 33 はフィルター選択されたインデックスの述語 ProductSubcategoryID >= 27 および ProductSubcategoryID <= 36のサブセットで、クエリ述語の ProductSubcategoryID 列と ListPrice 列はどちらもインデックスのキー列であり、名前は付加列としてインデックスのリーフ レベルに格納されます。For example, the query predicate expression ProductSubcategoryID = 33 is a subset of the filtered index predicate ProductSubcategoryID >= 27 and ProductSubcategoryID <= 36, the ProductSubcategoryID and ListPrice columns in the query predicate are both key columns in the index, and name is stored in the leaf level of the index as an included column.

SELECT Name, ProductSubcategoryID, ListPrice  
FROM Production.Product  
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;  

[キー列]Key Columns

フィルター選択されたインデックスの定義に少数のキーまたは付加列を含めること、およびフィルター選択されたインデックスをクエリ オプティマイザーによってクエリ実行プランで選択するために必要な列だけを組み込むことをお勧めします。It is a best practice to include a small number of key or included columns in a filtered index definition, and to incorporate only the columns that are necessary for the query optimizer to choose the filtered index for the query execution plan. クエリ オプティマイザーでは、フィルター選択されたインデックスがクエリに対応するかどうかに関係なく、フィルター選択されたインデックスがクエリに対して選択されます。The query optimizer can choose a filtered index for the query regardless of whether it does or does not cover the query. ただし、フィルター選択されたインデックスがクエリに対応する場合は、そのインデックスが選択される可能性は高くなります。However, the query optimizer is more likely to choose a filtered index if it covers the query.

場合によっては、フィルター選択されたインデックスは、その式の列をキー列または付加列としてフィルター選択されたインデックスの定義に含めなくても、クエリに対応します。In some cases, a filtered index covers the query without including the columns in the filtered index expression as key or included columns in the filtered index definition. 次のガイドラインでは、フィルター選択されたインデックスの式の列をフィルター選択されたインデックスの定義でキー列または付加列にする必要がある場合について説明します。The following guidelines explain when a column in the filtered index expression should be a key or included column in the filtered index definition. 次の例では、以前に作成したフィルター選択されたインデックス FIBillOfMaterialsWithEndDate を使用します。The examples refer to the filtered index, FIBillOfMaterialsWithEndDate that was created previously.

フィルター選択されたインデックスの式がクエリ述語と同じであり、フィルター選択されたインデックスの式の列がクエリ結果と共に返されない場合、その式の列を、フィルター選択されたインデックスの定義でキー列または付加列にする必要はありません。A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results. たとえば、クエリ述語がフィルター式と同じであり、 FIBillOfMaterialsWithEndDate がクエリ結果と共に返されないため、 EndDate は次のクエリに対応します。For example, FIBillOfMaterialsWithEndDate covers the following query because the query predicate is equivalent to the filter expression, and EndDate is not returned with the query results. FIBillOfMaterialsWithEndDate は、フィルター選択されたインデックスの定義のキー列または付加列として EndDate を必要としません。FIBillOfMaterialsWithEndDate does not need EndDate as a key or included column in the filtered index definition.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL;   

フィルター選択されたインデックスの式と異なるクエリ述語で比較に列が使用される場合は、フィルター選択されたインデックスの式の列を、フィルター選択されたインデックスの定義でキー列または付加列にする必要があります。A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression. たとえば、 FIBillOfMaterialsWithEndDate は、フィルター選択されたインデックスから行のサブセットを選択するので、次のクエリに対して有効です。For example, FIBillOfMaterialsWithEndDate is valid for the following query because it selects a subset of rows from the filtered index. ただし、 EndDate が比較 EndDate > '20040101'で使用されるため、次のクエリには対応していません。この比較は、フィルター選択されたインデックスの式と異なります。However, it does not cover the following query because EndDate is used in the comparison EndDate > '20040101', which is not equivalent to the filtered index expression. クエリ プロセッサでは、 EndDateの値を参照せずにこのクエリを実行することはできません。The query processor cannot execute this query without looking up the values of EndDate. したがって、 EndDate をフィルター選択されたインデックスの定義でキー列または付加列にする必要があります。Therefore, EndDate should be a key or included column in the filtered index definition.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
WHERE EndDate > '20040101';   

フィルター選択されたインデックスの式の列がクエリ結果セットに含まれる場合、その列をフィルター選択されたインデックスの定義でキー列または付加列にする必要があります。A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set. たとえば、 FIBillOfMaterialsWithEndDate はクエリ結果に含まれる EndDate 列を返すので、次のクエリに対応しません。For example, FIBillOfMaterialsWithEndDate does not cover the following query because it returns the EndDate column in the query results. したがって、 EndDate をフィルター選択されたインデックスの定義でキー列または付加列にする必要があります。Therefore, EndDate should be a key or included column in the filtered index definition.

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL;  

テーブルのクラスター化インデックス キーは、フィルター選択されたインデックスの定義でキー列または付加列にする必要はありません。The clustered index key of the table does not need to be a key or included column in the filtered index definition. クラスター化インデックス キーは、フィルター選択されたインデックスなど、すべての非クラスター化インデックスに自動的に含まれます。The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

フィルター述語のデータ変換演算子Data Conversion Operators in the Filter Predicate

フィルター選択されたインデックスでは、その式に指定された比較演算子によって暗黙的または明示的なデータ変換が行われる場合、変換が比較演算子の左辺で行われると、エラーが発生します。If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. 解決方法としては、比較演算子の右辺にデータ変換演算子 (CAST または CONVERT) を含む、フィルター選択されたインデックスの式を記述します。A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.

次の例では、さまざまなデータ型が含まれるテーブルを作成します。The following example creates a table with a variety of data types.

USE AdventureWorks2012;  
GO  
CREATE TABLE dbo.TestTable (a int, b varbinary(4));  

次のフィルター選択されたインデックスの定義では、列 b は、定数 1 と比較するために、整数データ型に暗黙的に変換されます。In the following filtered index definition, column b is implicitly converted to an integer data type for the purpose of comparing it to the constant 1. これにより、フィルター選択された述語の演算子の左辺で変換が行われるため、エラー メッセージ 10611 が生成されます。This generates error message 10611 because the conversion occurs on the left hand side of the operator in the filtered predicate.

CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = 1;  

解決策として、次の例に示すように、右辺の定数を、列 bと同じ型になるように変換します。The solution is to convert the constant on the right hand side to be of the same type as column b, as seen in the following example:

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = CONVERT(Varbinary(4), 1);  

データ変換を比較演算子の左辺から右辺に移動すると、変換の意味が変わることがあります。Moving the data conversion from the left side to the right side of a comparison operator might change the meaning of the conversion. この例では、CONVERT 演算子を右辺に追加したときに、整数の比較から varbinary の比較に変わりました。In the above example, when the CONVERT operator was added to the right side, the comparison changed from an integer comparison to a varbinary comparison.

列ストア インデックスのデザイン ガイドラインColumnstore Index Design Guidelines

columnstore index は、列ストアと呼ばれる列指向データ形式を使用してデータを格納、取得、および管理するためのテクノロジです。A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. 詳細については、「列ストア インデックス - 概要」を参照してください。For more information, refer to Columnstore Indexes overview.

バージョン情報については、「列ストア インデックス - 新機能」を参照してください。For version information, see Columnstore indexes - What's new.

列ストア インデックスのアーキテクチャColumnstore Index Architecture

これらの基本を理解すると、効果的に使用する方法を説明する、その他の列ストアの記事を理解しやすくなります。Knowing these basics will make it easier to understand other columnstore articles that explain how to use them effectively.

データ ストレージでは列ストアと行ストアの圧縮を使用するData storage uses columnstore and rowstore compression

列ストア インデックスの説明では、データ ストレージの形式を強調する目的で行ストア列ストアという用語を使用しています。When discussing columnstore indexes, we use the terms rowstore and columnstore to emphasize the format for the data storage. 列ストア インデックスでは、両方の種類のストレージを使用します。Columnstore indexes use both types of storage.

Clustered Columnstore IndexClustered Columnstore Index

  • 列ストア は、行と列を含むテーブルとして論理的に編成され、列方向のデータ形式で物理的に格納されているデータです。A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

    列ストア インデックスでは、ほとんどのデータを列ストア形式で物理的に格納します。A columnstore index physically stores most of the data in columnstore format. 列ストア形式では、データは列として圧縮および非圧縮されます。In columnstore format, the data is compressed and uncompressed as columns. クエリで要求されない行ごとに、その他の値を非圧縮する必要はありません。There is no need to uncompress other values in each row that are not requested by the query. このため、大規模なテーブルの列全体を高速にスキャンできます。This makes it fast to scan an entire column of a large table.

  • 行ストア は、行と列を含むテーブルとして論理的に編成され、行方向のデータ形式で物理的に格納されているデータです。A rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. これは、ヒープまたはクラスター化された B ツリー インデックスなどのリレーショナル テーブル データを格納する従来の方法です。This has been the traditional way to store relational table data such as a heap or clustered B-tree index.

    また、列ストア インデックスでは、デルタストアという行ストア形式で一部の行を物理的にも格納します。A columnstore index also physically stores some rows in a rowstore format called a deltastore. デルタストア (デルタ行グループとも呼ばれます) は、列ストアへの圧縮に適合させるために、数が少なすぎる行を格納する場所です。The deltastore,also called delta rowgroups, is a holding place for rows that are too few in number to qualify for compression into the columnstore. デルタ行グループはそれぞれ、クラスター化された B ツリー インデックスとして実装されます。Each delta rowgroup is implemented as a clustered B-tree index.

  • デルタストアは、列ストアに圧縮するには数が少なすぎる行を保持する場所です。The deltastore is a holding place for rows that are too few in number to be compressed into the columnstore. デルタストアは、行ストア形式で行を格納します。The deltastore stores the rows in rowstore format.

操作は行グループと列セグメント上で実行されるOperations are performed on rowgroups and column segments

列ストア インデックスでは、行を管理可能な単位にグループ化します。The columnstore index groups rows into manageable units. これらの単位はそれぞれ、行グループと呼ばれます。Each of these units is called a rowgroup. 最適なパフォーマンスを得るため、行グループ内の行数は、高い圧縮率が実現される程度に多く、インメモリ操作の利点を得られる程度に少ないです。For best performance, the number of rows in a rowgroup is large enough to improve compression rates and small enough to benefit from in-memory operations.

たとえば、列ストア インデックスは、行グループで次の操作を実行します。For example, the columnstore index performs these operations on rowgroups:

  • 行グループを列ストアに圧縮します。Compresses rowgroups into the columnstore. 圧縮は、行グループ内の各列セグメントで実行されます。Compression is performed on each column segment within a rowgroup.
  • ALTER INDEX ... REORGANIZE 操作中に行グループをマージします。Merges rowgroups during an ALTER INDEX ... REORGANIZE operation.
  • ALTER INDEX ... REBUILD 操作中に行グループを新規作成します。Creates new rowgroups during an ALTER INDEX ... REBUILD operation.
  • 動的管理ビュー (DMV) の行グループの正常性と断片化に関するレポートを行います。Reports on rowgroup health and fragmentation in the dynamic management views (DMVs).

デルタストアは、デルタ行グループと呼ばれる 1 つ以上の行グループで構成されます。The deltastore is comprised of one or more rowgroups called delta rowgroups. 各デルタ行グループは、1,048,576 個の行が含まれるまで、またはインデックスが再構築されるまで、小規模の一括読み込みと挿入を格納するクラスター化された B ツリー インデックスです。Each delta rowgroup is a clustered B-tree index that stores small bulk loads and inserts until the rowgroup contains 1,048,576 rows, or until the index is rebuilt. デルタ行グループに 1,048, 576 個の行が含まれると、閉じられたと見なされ、列ストアに圧縮するための組ムーバーと呼ばれるプロセスを待ちます。When a delta rowgroup contains 1,048,576 rows it is marked as closed, and waits for a process called the tuple-mover to compress it into the columnstore.

それぞれの列には、行グループごとにその値の一部が含まれます。Each column has some of its values in each rowgroup. これらの値は列セグメントと呼ばれます。These values are called column segments. それぞれの行グループには、テーブルの 1 つの列につき 1 つの列セグメントが含まれます。Each rowgroup contains one column segment for every column in the table. それぞれの列には、行グループごとに 1 つの列セグメントがあります。Each column has one column segment in each rowgroup.

Column segmentColumn segment

列ストア インデックスが行グループを圧縮する場合、各列セグメントを個別に圧縮します。When the columnstore index compresses a rowgroup, it compresses each column segment separately. 列全体を非圧縮する場合、列ストア インデックスでは、それぞれの行グループから列セグメントを 1 つ非圧縮するだけで列全体を非圧縮できます。To uncompress an entire column, the columnstore index only needs to uncompress one column segment from each rowgroup.

小規模の読み込みと挿入はデルタストアに移動されるSmall loads and inserts go to the deltastore

列ストア インデックスは、一度に少なくとも 102,400 個の行を列ストア インデックスに圧縮することで、列ストア インデックスの圧縮とパフォーマンスを向上させています。A columnstore index improves columnstore compression and performance by compressing at least 102,400 rows at a time into the columnstore index. 行を一括で圧縮するために、列ストア インデックスでは、小規模な読み込みを累積し、デルタストアに挿入します。To compress rows in bulk, the columnstore index accumulates small loads and inserts in the deltastore. デルタストア操作は内部で処理されます。The deltastore operations are handled behind the scenes. 列ストア インデックスは、正しいクエリ結果を返すために、列ストアとデルタストアの両方からのクエリ結果を結合します。To return the correct query results, the clustered columnstore index combines query results from both the columnstore and the deltastore.

次の場合に、行はデルタストアに移動されます。Rows go to the deltastore when they are:

  • INSERT INTO ... VALUES ステートメントで挿入された場合。Inserted with the INSERT INTO ... VALUES statement.
  • 一括読み込みの最後で 102,400 未満の場合。At the end of a bulk load and they number less than 102,400.
  • 更新済み。Updated. 更新はそれぞれ、削除および挿入として実装されます。Each update is implemented as a delete and an insert.

また、デルタストアでは、削除済みとしてマークされているが、列ストアから物理的に削除されていない、削除された行の ID の一覧も格納します。The deltastore also stores a list of IDs for deleted rows that have been marked as deleted but not yet physically deleted from the columnstore.

デルタ行グループが満たされた場合、列ストアに圧縮されるWhen delta rowgroups are full they get compressed into the columnstore

クラスター化列ストア インデックスでは、デルタ行グループごとに最大 1,048,576 個の列を収集してから、行グループを列ストアに圧縮します。Clustered columnstore indexes collect up to 1,048,576 rows in each delta rowgroup before compressing the rowgroup into the columnstore. これにより、列ストア インデックスの圧縮が向上します。This improves the compression of the columnstore index. デルタ行グループに 1,048,576 個の行が含まれている場合、列ストア インデックスは列グループが閉じられたと見なします。When a delta rowgroup contains 1,048,576 rows, the columnstore index marks the rowgroup as closed. 組ムーバーというバックグラウンド プロセスでは、閉じられた行グループを見つけて、それを列ストアに圧縮します。A background process, called the tuple-mover, finds each closed rowgroup and compresses it into the columnstore.

インデックスを再構築または再構成するには、ALTER INDEX を使用してデルタ行グループを列ストアに強制的に圧縮することができます。You can force delta rowgroups into the columnstore by using ALTER INDEX to rebuild or reorganize the index. 圧縮中にメモリ負荷がある場合、列ストア インデックスは圧縮行グループ内の行数を減らす可能性があることに注意してください。Note that if there is memory pressure during compression, the columnstore index might reduce the number of rows in the compressed rowgroup.

各テーブル パーティションには、独自の行グループとデルタ行グループが含まれるEach table partition has its own rowgroups and delta rowgroups

パーティション分割の概念は、クラスター化インデックス、ヒープ、および列ストア インデックスのすべてにおいて同じです。The concept of partitioning is the same in both a clustered index, a heap, and a columnstore index. テーブルのパーティション分割では、列の値の範囲に従って、テーブルをより小規模の列のグループに分割します。Partitioning a table divides the table into smaller groups of rows according to a range of column values. 通常、これはデータを管理するために使用されます。It is often used for managing the data. たとえば、データの年ごとにパーティションを作成して、パーティションの切り替えを使用し、データをコストが低いストレージにアーカイブすることができます。For example, you could create a partition for each year of data, and then use partition switching to archive data to less expensive storage. パーティションの切り替えは、列ストア インデックス上で動作するため、データのパーティションを別の場所に移動しやすくなります。Partition switching works on columnstore indexes and makes it easy to move a partition of data to another location.

行グループは常に、テーブル パーティション内に定義されます。Rowgroups are always defined within a table partition. 列ストア インデックスがパーティション分割されると、各パーティションには独自の圧縮行グループとデルタ行グループが含まれます。When a columnstore index is partitioned, each partition has its own compressed rowgroups and delta rowgroups.

各パーティションに複数のデルタ行グループを含めることができるEach partition can have multiple delta rowgroups

各パーティションに複数のデルタ行グループを含めることができます。Each partition can have more than one delta rowgroups. 列ストア インデックスでデータをデルタ行グループに追加する必要があり、デルタ行グループがロックされている場合、列ストア インデックスでは、さまざまなデルタ行グループのロックを取得しようとします。When the columnstore index needs to add data to a delta rowgroup and the delta rowgroup is locked, the columnstore index will try to obtain a lock on a different delta rowgroup. 使用できるデルタ行グループがない場合は、列ストア インデックスでは新しいデルタ行グループが作成されます。If there are no delta rowgroups available, the columnstore index will create a new delta rowgroup. たとえば、パーティションが 10 個のテーブルには、簡単に 20 個以上のデルタ行グループを含めることができます。For example, a table with 10 partitions could easily have 20 or more delta rowgroups.

同じテーブルで列ストア インデックスと行ストア インデックスを結合できるYou can combine columnstore and rowstore indexes on the same table

非クラスター化インデックスには、基になるテーブルの行と列の一部または全体のコピーが含まれています。A nonclustered index contains a copy of part or all of the rows and columns in the underlying table. インデックスはテーブルの 1 つ以上の列として定義され、行のフィルター処理条件をオプションで設定できます。The index is defined as one or more columns of the table, and has an optional condition that filters the rows.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 以降、更新可能な非クラスター化列ストア インデックスを、行ストア テーブルに作成できます。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create an updatable nonclustered columnstore index on a rowstore table. 列ストア インデックスは、データのコピーを格納するため、追加のストレージが必要です。The columnstore index stores a copy of the data so you do need extra storage. ただし、列ストア インデックス内のデータは、行ストア テーブルが必要とするサイズよりも小さいサイズに圧縮されます。However, the data in the columnstore index will compress to a smaller size than the rowstore table requires. これにより、同時に、列ストア インデックスの分析と行ストア インデックスのトランザクションを同時に実行できます。By doing this, you can run analytics on the columnstore index and transactions on the rowstore index at the same time. 行ストア テーブルでデータが変更されると列ストアが更新されます。したがって、両方のインデックスが、同じデータに対して作業を行うことになります。The column store is updated when data changes in the rowstore table, so both indexes are working against the same data.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 以降、列ストア インデックスでは、1 つ以上の非クラスター化行ストア インデックスを使用できます。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can have one or more nonclustered rowstore indexes on a columnstore index. これにより、基になる列ストアで、効率的なテーブル シークを実行できます。By doing this, you can perform efficient table seeks on the underlying columnstore. 他のオプションも使用できます。Other options become available too. たとえば、行ストア テーブルで UNIQUE 制約を使用することで、主キー制約を適用できます。For example, you can enforce a primary key constraint by using a UNIQUE constraint on the rowstore table. 一意でない値は行ストア テーブルに挿入できないため、SQL Server でその値を列ストアに挿入することはできません。Since an non-unique value will fail to insert into the rowstore table, SQL Server cannot insert the value into the columnstore.

パフォーマンスに関する考慮事項Performance considerations

  • 非クラスター化列ストア インデックスの定義で、フィルター適用条件の使用をサポートします。The nonclustered columnstore index definition supports using a filtered condition. OLTP テーブルに列ストア インデックスを追加することによるパフォーマンスへの影響を最小限に抑えるには、フィルター条件を使って、用して、運用ワークロードのコールド データのみに、非クラスター化列ストア インデックスを作成します。To minimize the performance impact of adding a columnstore index on an OLTP table, use a filtered condition to create a nonclustered columnstore index on only the cold data of your operational workload.

  • インメモリ テーブルでは、列ストア インデックスを 1 つ使用できます。An in-memory table can have one columnstore index. これは、テーブルの作成時に作成することも、後で ALTER TABLE (Transact-SQL) を使用して追加することもできます。You can create it when the table is created or add it later with ALTER TABLE (Transact-SQL). SQL Server 2016 (13.x)SQL Server 2016 (13.x) より前のバージョンでは、列ストア インデックスを保持できたのはディスク ベースのテーブルのみでした。Before SQL Server 2016 (13.x)SQL Server 2016 (13.x), only a disk-based table could have a columnstore index.

詳細については、「列ストア インデックス - クエリ パフォーマンス」を参照してください。For more information, refer to Columnstore indexes - Query performance.

設計ガイダンスDesign Guidance

  • 行ストア テーブルで、更新可能な非クラスター化列ストア インデックスを 1 つ使用できます。A rowstore table can have one updateable nonclustered columnstore index. SQL Server 2014 (12.x)SQL Server 2014 (12.x) より前のバージョンでは、非クラスター化列ストア インデックスは読み取り専用でした。Before SQL Server 2014 (12.x)SQL Server 2014 (12.x), the nonclustered columnstore index was read-only.

詳細については、「列ストア インデックス - 設計ガイダンス」を参照してください。For more information, refer to Columnstore indexes - Design Guidance.

ハッシュ インデックスのデザイン ガイドラインHash Index Design Guidelines

すべてのメモリ最適化テーブルには少なくとも 1 つのインデックスが必要です。このインデックスによって行が連結されるためです。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. ハッシュ インデックスは、メモリ最適化テーブルで使用できるインデックスの種類の 1 つです。Hash indexes are one of the possible index types in a memory-optimized table. 詳細については、「メモリ最適化テーブルのインデックス」を参照してください。For more information, see Indexes for Memory-Optimized Tables.

適用対象: SQL Server 2014 (12.x)SQL Server 2014 (12.x) から SQL Server 2017SQL Server 2017Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.

ハッシュ インデックスのアーキテクチャHash Index Architecture

ハッシュ インデックスはポインターの配列で構成され、その配列の各要素はハッシュ バケットと呼ばれます。A hash index consists of an array of pointers, and each element of the array is called a hash bucket.

  • 各バケットは 8 バイトであり、キー エントリのリンク リストのメモリ アドレスを格納するために使用されます。Each bucket is 8 bytes, which are used to store the memory address of a link list of key entries.
  • 各エントリは、インデックス キーの値と、基になるメモリ最適化テーブル内の対応する行のアドレスです。Each entry is a value for an index key, plus the address of its corresponding row in the underlying memory-optimized table.
  • 各エントリは、すべて現在のバケットにチェーンされたエントリのリンク リスト内の次のエントリを指します。Each entry points to the next entry in a link list of entries, all chained to the current bucket.

バケットの数は、インデックスの定義時に指定する必要があります。The number of buckets must be specified at index definition time:

  • テーブルの行数または個別の値の数に対するバケット数の割合が低ければ低いほど、バケットの平均リンク リストは長くなります。The lower the ratio of buckets to table rows or to distinct values, the longer the average bucket link list will be.
  • 短いリンク リストは、長いリンク リストよりも高速で実行されます。Short link lists perform faster than long link lists.
  • ハッシュ インデックスのバケットの最大数は 1,073,741,824 です。The maximum number of buckets in hash indexes is 1,073,741,824.

ヒント

データの適切な BUCKET_COUNT を決定するには、「 ハッシュ インデックスのバケット数の構成」を参照してください。To determine the right BUCKET_COUNT for your data, see Configuring the hash index bucket count.

ハッシュ関数はインデックス キー列に適用され、関数の結果によってキーがどのバケットに分類されるかが決まります。The hash function is applied to the index key columns and the result of the function determines what bucket that key falls into. 各バケットには、ハッシュされたキー値がそのバケットにマップされている行へのポインターがあります。Each bucket has a pointer to rows whose hashed key values are mapped to that bucket.

ハッシュ インデックスに使用するハッシュ関数には、以下の特徴があります。The hashing function used for hash indexes has the following characteristics:

  • SQL ServerSQL Server には、あらゆるハッシュ インデックスに使用するハッシュ関数が 1 つ用意されています。has one hash function that is used for all hash indexes.
  • ハッシュ関数は決定的です。The hash function is deterministic. 入力キー値が同じであれば、常にハッシュ インデックスの同じバケットにマッピングされます。The same input key value is always mapped to the same bucket in the hash index.
  • インデックス キーが違っても、同じハッシュ バケットにマッピングされることがあります。Multiple index keys may be mapped to the same hash bucket.
  • ハッシュ関数はバランスが取られます。つまり、通常、ハッシュ バケット上のインデックス キー値の分布は、平坦な線形分布ではなくポアソン分布またはベル カーブ分布に従います。The hash function is balanced, meaning that the distribution of index key values over hash buckets typically follows a Poisson or bell curve distribution, not a flat linear distribution.
  • ポアソン分布は均等な分布ではありません。Poisson distribution is not an even distribution. インデックス キーの値は、ハッシュ バケットで均等に分散されません。Index key values are not evenly distributed in the hash buckets.
  • 2 つのインデックス キーが同じハッシュ バケットにマッピングされた場合には、ハッシュの競合となります。If two index keys are mapped to the same hash bucket, there is a hash collision. ハッシュの競合が大量に発生した場合には、読み取り操作のパフォーマンスに影響を及ぼすおそれがあります。A large number of hash collisions can have a performance impact on read operations. 現実的な目標は、バケットの 30% に 2 つの異なるキー値が含まれていることです。A realistic goal is for 30% of the buckets contain two different key values.

ハッシュ インデックスとバケットの関係をまとめると、次の図のようになります。The interplay of the hash index and the buckets is summarized in the following image.

hekaton_tables_23dhekaton_tables_23d

ハッシュ インデックスのバケット数の構成Configuring the hash index bucket count

ハッシュ インデックスのバケット数はインデックス作成時に指定しますが、ALTER TABLE...ALTER INDEX REBUILD 構文を使用して変更することができます。The hash index bucket count is specified at index create time, and can be changed using the ALTER TABLE...ALTER INDEX REBUILD syntax.

ほとんどの場合、バケット数は、理想的にはインデックス キーの個別の値の数の 1 から 2 倍の範囲内にします。In most cases the bucket count would ideally be between 1 and 2 times the number of distinct values in the index key.
特定のインデックス キーに値がどれぐらいあるかは、予測できないこともあります。You may not always be able to predict how many values a particular index key may have, or will have. BUCKET_COUNT 値がキー値の実際の数の 10 倍以内であれば、パフォーマンスは通常まだ良好であり、低く見積もるよりは多く見積もりすぎるほうが一般的によい結果が得られます。Performance is usually still good if the BUCKET_COUNT value is within 10 times of the actual number of key values, and overestimating is generally better than underestimating.

少なすぎる バケットには、次の短所があります。Too few buckets has the following drawbacks:

  • 個別のキー値のハッシュの競合の増加。More hash collisions of distinct key values.
  • 個別の値が、異なる個別の値を持つバケットの共有を強いられます。Each distinct value is forced to share the same bucket with a different distinct value.
  • パケットごとの平均チェーン長が増えます。The average chain length per bucket grows.
  • バケット チェーンが長ければ長いほど、インデックスでの等値検索の速度が遅くなります。The longer the bucket chain, the slower the speed of equality lookups in the index.

多すぎる バケットには、次の短所があります。Too many buckets has the following drawbacks:

  • バケット数が高すぎると、空のバケットを増やす結果になることがあります。Too high a bucket count might result in more empty buckets.
  • 空のバケットは、フル インデックス スキャンのパフォーマンスに影響を与えます。Empty buckets impact the performance of full index scans. フル インデックス スキャンが普通に行われる場合は、インデックス キーの個別の値の数に近いバケット数を選択することを検討してください。If those are performed regularly, consider picking a bucket count close to the number of distinct index key values.
  • 空のバケットは、それぞれが使用するのはわずか 8 バイトですが、メモリを使用します。Empty buckets use memory, though each bucket uses only 8 bytes.

注意

バケットを追加しても、重複する値を共有するエントリのチェーンが短くなることはありません。Adding more buckets does nothing to reduce the chaining together of entries that share a duplicate value. 値の重複の割合は、ハッシュが適切なインデックスの種類であるかどうかを決定するために使用され、バケット数を計算するために使用されることはありません。The rate of value duplication is used to decide whether a hash is the appropriate index type, not to calculate the bucket count.

パフォーマンスに関する考慮事項Performance considerations

ハッシュ インデックスのパフォーマンスは次のようになります。The performance of a hash index is:

  • WHERE 句の述語で、ハッシュ インデックス キーの各列の正確な値を指定する場合は極めて良好です。Excellent when the predicate in the WHERE clause specifies an exact value for each column in the hash index key. ハッシュ インデックスは、非等値述語が指定されているとスキャンに戻ります。A hash index will revert to a scan given an inequality predicate.
  • WHERE 句の述語でインデックス キーの値の範囲を探す場合は、よくありません。Poor when the predicate in the WHERE clause looks for a range of values in the index key.
  • WHERE 句の述語で、2 列のハッシュ インデックス キーの最初の列について特定の値を指定し、キーのの列については値を指定しない場合は、よくありません。Poor when the predicate in the WHERE clause stipulates one specific value for the first column of a two column hash index key, but does not specify a value for other columns of the key.

ヒント

述語はハッシュ インデックス キーのすべての列を含める必要があります。The predicate must include all columns in the hash index key. ハッシュ インデックスでは、インデックスに対してシークを実行するための (ハッシュ用の) キーが必要です。The hash index requires a key (to hash) to seek into the index. インデックス キーが 2 列で構成され、WHERE 句で最初の列しか指定されないと、SQL ServerSQL Server でハッシュ用とするキーが不完全になります。If an index key consists of two columns and the WHERE clause only provides the first column, SQL ServerSQL Server does not have a complete key to hash. この場合は、インデックス スキャン クエリ プランが作成されます。This will result in an index scan query plan.

ハッシュ インデックスを使用し、一意のインデックス キーの数が行の数より 100 倍 (またはそれ以上) 多い場合は、大きい行チェーンを回避するために bucket_count を増やすか、代わりに非クラスター化インデックスを使用することをお勧めします。If a hash index is used and the number of unique index keys is 100 times (or more) than the row count, consider either increasing to a larger bucket count to avoid large row chains, or use a nonclustered index instead.

宣言に関する考慮事項Declaration considerations

ハッシュ インデックスは、メモリ最適化テーブルにのみ存在できます。A hash index can exist only on a memory-optimized table. ディスク ベース テーブルには存在できません。It cannot exist on a disk-based table.

ハッシュ インデックスは、次のように宣言できます。A hash index can be declared as:

  • UNIQUE。そうしないと、既定の Non-Unique になります。UNIQUE, or can default to Non-Unique.
  • NONCLUSTERED (既定値)。NONCLUSTERED, which is the default.

CREATE TABLE ステートメント外でハッシュ インデックスを作成する構文の例を次に示します。The following is an example of the syntax to create a hash index, outside of the CREATE TABLE statement:

ALTER TABLE MyTable_memop  
ADD INDEX ix_hash_Column2 UNIQUE  
HASH (Column2) WITH (BUCKET_COUNT = 64);

行のバージョンとガベージ コレクションRow versions and garbage collection

メモリ最適化テーブルでは、行が UPDATE による影響を受ける場合、テーブルで行の更新バージョンが作成されます。In a memory-optimized table, when a row is affected by an UPDATE, the table creates an updated version of the row. 更新トランザクションの間、他のセッションは行の前のバージョンを読み取ることができるため、行ロックに関連するパフォーマンスの低下を回避することができます。During the update transaction, other sessions might be able to read the older version of the row and thereby avoid the performance slowdown associated with a row lock.

ハッシュ インデックスに、更新に対応するための異なるバージョンのエントリも存在することがあります。The hash index might also have different versions of its entries to accommodate the update.

後で前のバージョンが不要になったときに、ガベージ コレクション (GC) スレッドがバケットとそのリンク リストを横断して、前のエントリをクリーンアップします。Later when the older versions are no longer needed, a garbage collection (GC) thread traverses the buckets and their link lists to clean away old entries. GC スレッドのパフォーマンスは、リンク リストのチェーン長が短い場合に優れています。The GC thread performs better if the link list chain lengths are short. 詳細については、「インメモリ OLTP ガベージ コレクション」を参照してください。For more information, refer to In-Memory OLTP Garbage Collection.

メモリ最適化非クラスター化インデックスのデザイン ガイドラインMemory-Optimized Nonclustered Index Design Guidelines

非クラスター化インデックスは、メモリ最適化テーブルで使用できるインデックスの種類の 1 つです。Nonclustered indexes are one of the possible index types in a memory-optimized table. 詳細については、「メモリ最適化テーブルのインデックス」を参照してください。For more information, see Indexes for Memory-Optimized Tables.

適用対象: SQL Server 2014 (12.x)SQL Server 2014 (12.x) から SQL Server 2017SQL Server 2017Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.

インメモリ非クラスター化インデックスのアーキテクチャIn-memory Nonclustered Index Architecture

インメモリ非クラスター化インデックスは、2011 年に Microsoft Research が独自に考案した Bw ツリーというデータ構造を使用して実装されています。In-memory nonclustered indexes are implemented using a data structure called a Bw-Tree, originally envisioned and described by Microsoft Research in 2011. Bw ツリーは、B ツリーのロックおよびラッチフリーのバリエーションです。A Bw-Tree is a lock and latch-free variation of a B-Tree. 詳細については、「The Bw-Tree:A B-tree for New Hardware Platforms」(Bw ツリー: 新しいハードウェア プラットフォーム向けの B ツリー) を参照してください。For more details please see The Bw-Tree: A B-tree for New Hardware Platforms.

大まかに説明すると、Bw ツリーは、ページ ID (PidMap) で整理されたページのマップです。また、ページ ID (PidAlloc) と、ページ マップ内および相互にリンクされているページのセットを割り当て、再利用する機能があります。At a very high level the Bw-Tree can be understood as a map of pages organized by page ID (PidMap), a facility to allocate and reuse page IDs (PidAlloc) and a set of pages linked in the page map and to each other. これら 3 つの上位レベルのサブコンポーネントが、Bw ツリーの基本的な内部構造を構成します。These three high level sub-components make up the basic internal structure of a Bw-Tree.

Bw ツリーの構造は、各ページに並べ替えられたキー値のセットがあり、インデックス内にそれぞれが下位レベルを示すレベルがあり、リーフ レベルがデータ行を示すという点で、通常の B ツリーと似ています。The structure is similar to a normal B-Tree in the sense that each page has a set of key values that are ordered and there are levels in the index each pointing to a lower level and the leaf levels point to a data row. ただし、違いもいくつかあります。However there are several differences.

ハッシュ インデックスと同様に、複数のデータ行 (バージョン) をまとめてリンクできます。Just like hash indexes, multiple data rows can be linked together (versions). レベル間のページ ポインターは論理ページ ID です。これは、ページ マッピング テーブルのオフセットなので、各ページの物理アドレスがあります。The page pointers between the levels are logical page IDs, which are offsets into a page mapping table, that in turn has the physical address for each page.

インデックス ページのインプレース更新はありません。There are no in-place updates of index pages. この目的のために新しいデルタ ページが導入されています。New delta pages are introduced for this purpose.

  • ページの更新のためにラッチやロックは必要ありません。No latching or locking is required for page updates.
  • インデックス ページは固定サイズではありません。Index pages are not a fixed size.

図の各非リーフ レベル ページのキー値は、示された子が含む最大値であり、各行にはそのページの論理ページ ID も含まれます。The key value in each non-leaf level page depicted is the highest value that the child that it points to contains and each row also contains that page logical page ID. リーフレベルのページには、キー値と共に、データ行の物理アドレスが含まれています。On the leaf-level pages, along with the key value, it contains the physical address of the data row.

ポイント ルックアップは、B ツリーと似ていますが、ページは 1 つの方向のみにリンクされているため、SQL Server データベース エンジンSQL Server Database Engine は、B ツリーのように最低値ではなく、各非リーフ ページに子の最大値がある適切なページ ポインターに従います。Point lookups are similar to B-Trees except that because pages are linked in only one direction, the SQL Server データベース エンジンSQL Server Database Engine follows right page pointers, where each non-leaf pages has the highest value of its child, rather than lowest value as in a B-Tree.

リーフレベルのページを変更する必要がある場合は、SQL Server データベース エンジンSQL Server Database Engine はページ自体を変更しません。If a Leaf-level page has to change, the SQL Server データベース エンジンSQL Server Database Engine does not modify the page itself. その代わり、SQL Server データベース エンジンSQL Server Database Engine では、変更を示す差分レコードが作成され、前のページに付加されます。Rather, the SQL Server データベース エンジンSQL Server Database Engine creates a delta record that describes the change, and appends it to the previous page. 次に、前のページのページ マップ テーブル アドレスが、このページの物理アドレスになる差分レコードのアドレスに更新されます。Then it also updates the page map table address for that previous page, to the address of the delta record which now becomes the physical address for this page.

Bw ツリーの構造を管理するために必要な 3 つの操作があります。統合、分割、マージです。There are three different operations that can be required for managing the structure of a Bw-Tree: consolidation, split and merge.

差分の統合Delta Consolidation

差分レコードのチェーンが長くなると、インデックスの検索時に長いチェーンを横断することになるので、結果的に検索のパフォーマンスが遅くなる可能性があります。A long chain of delta records can eventually degrade search performance as it could mean we are traversing long chains when searching through an index. 要素数が既に 16 個のチェーンに新しい差分レコードが追加された場合、差分レコードの変更は参照されるインデックス ページに統合され、統合をトリガーした新しい差分レコードに示される変更を含むページが再構築されます。If a new delta record is added to a chain that already has 16 elements, the changes in the delta records will be consolidated into the referenced index page, and the page will then be rebuilt, including the changes indicated by the new delta record that triggered the consolidation. 新しく構築されたページのページ ID は同じですが、メモリ アドレスは新しくなります。The newly rebuilt page will have the same page ID but a new memory address.

hekaton_tables_23ehekaton_tables_23e

ページの分割Split page

Bw ツリーのインデックス ページは、1 行の格納から最大 8 KB の格納まで必要に応じてサイズが大きくなります。An index page in Bw-Tree grows on as-needed basis starting from storing a single row to storing a maximum of 8 KB. インデックス ページのサイズが 8 KB まで大きくなった後に新しく 1 行追加されると、インデックス ページは分割されます。Once the index page grows to 8 KB, a new insert of a single row will cause the index page to split. 内部ページの場合は、別のキー値とポインターを追加する余地がなくなり、リーフ ページの場合は、すべての差分レコードを組み込んだ後に行のサイズが大きすぎてページに収まらなくなることを意味します。For an internal page, this means when there is no more room to add another key value and pointer, and for a leaf page, it means that the row would be too big to fit on the page once all the delta records are incorporated. リーフ ページのページ ヘッダーの統計情報では、差分レコードを統合するために必要な容量が追跡され、新しい差分レコードが追加されるたびにその情報が調整されます。The statistics information in the page header for a leaf page keeps track of how much space would be required to consolidate the delta records, and that information is adjusted as each new delta record is added.

分割操作は、2 つのアトミック手順で実行されます。A Split operation is done in two atomic steps. 下図では、値が 5 のキーが挿入されるため、リーフページで分割が強制実行されます。現在のリーフレベル ページの末尾を示す非リーフページ (キー値 4) が存在しなくなります。In the picture below, assume a Leaf-page forces a split because a key with value 5 is being inserted, and a non-leaf page exists pointing to the end of the current Leaf-level page (key value 4).

hekaton_tables_23fhekaton_tables_23f

手順 1: P1 と P2 という新しいページを割り当て、新しく挿入された行を含め、以前の P1 ページの行をこれらの新しいページに分割します。Step 1: Allocate two new pages P1 and P2, and split the rows from old P1 page onto these new pages, including the newly inserted row. ページ マッピング テーブルの新しいスロットは、ページ P2 の物理アドレスを格納するために使用されます。A new slot in Page Mapping Table is used to store the physical address of page P2. P1 と P2 というこれらのページは、まだ同時実行の操作にはアクセスできません。These pages, P1 and P2 are not accessible to any concurrent operations yet. さらに、P1 から P2 への論理ポインターがセットされます。In addition, the logical pointer from P1 to P2 is set. 次に、1 つのアトミック手順でページ マッピング テーブルが更新され、ポインターが古い P1 から新しい P1 に変更されます。Then, in one atomic step update the Page Mapping Table to change the pointer from old P1 to new P1.

手順 2: 非リーフ ページは P1 を指しますが、非リーフ ページから P2 への直接ポインターはありません。Step 2: The non-leaf page points to P1 but there is no direct pointer from a non-leaf page to P2. P2 は P1 を介してのみ到達可能です。P2 is only reachable via P1. 非リーフ ページから P2 へのポインターを作成するには、新しい非リーフ ページ (内部インデックス ページ) を割り当て、古い非リーフ ページのすべての行をコピーし、P2 を示す新しい行を追加します。To create a pointer from a non-leaf page to P2, allocate a new non-leaf page (internal index page), copy all the rows from old non-leaf page, and add a new row to point to P2. この手順が完了したら、1 つのアトミック手順で、ページ マッピング テーブルを更新して、ポインターを古い非リーフ ページから新しい非リーフ ページに変更します。Once this is done, in one atomic step, update the Page Mapping Table to change the pointer from old non-leaf page to new non-leaf page.

ページのマージMerge page

DELETE 操作の結果、ページのサイズが最大ページ サイズ (現在は 8 KB) の 10% 未満になるか、ページ上の行数が 1 になると、そのページは連続するページにマージされます。When a DELETE operation results in a page having less than 10% of the maximum page size (currently 8 KB), or with a single row on it, that page will be merged with a contiguous page.

ページから行が削除されると、その削除の差分データが追加されます。When a row is deleted from a page, a delta record for the delete is added. さらに、インデックス ページ (非リーフ ページ) がマージ対象かどうかを判断するための確認が実行されます。Additionally, a check is made to determine if the index page (non-leaf page) qualifies for Merge. この確認で、行を削除した後の残領域が最大ページ サイズの 10% 未満になるかどうかが検証されます。This check verifies if the remaining space after deleting the row will be less than 10% of maximum page size. この条件を満たす場合、マージは 3 つのアトミック手順で実行されます。If it does qualify, the Merge is performed in three atomic steps.

下図では、DELETE 操作でキー値 10 が削除されています。In the picture below, assume a DELETE operation will delete the key value 10.

hekaton_tables_23ghekaton_tables_23g

手順 1: キー値 10 (青色の三角形) を表す差分ページが作成され、非リーフ ページ Pp1 内のそのポインターは新しい差分ページに設定されます。Step 1: A delta page representing key value 10 (blue triangle) is created and its pointer in the non-leaf page Pp1 is set to the new delta page. さらに、特別なマージ差分ページ (緑色の三角形) が作成され、差分ページを示すようにリンクされます。Additionally a special merge-delta page (green triangle) is created, and it is linked to point to the delta page. この段階では、両方のページ (差分ページとマージ差分ページ) は、同時のトランザクションには表示されません。At this stage, both pages (delta page and merge-delta page) are not visible to any concurrent transaction. 1 つのアトミック手順では、ページ マッピング テーブルのリーフレベル ページ P1 へのポインターはマージ差分ページを示すように更新されます。In one atomic step, the pointer to the Leaf-level page P1 in the Page Mapping Table is updated to point to the merge-delta page. この手順の後、Pp1 のキー値 10 のエントリはマージ差分ページを示すようになります。After this step, the entry for key value 10 in Pp1 now points to the merge-delta page.

手順 2: 非リーフ ページ Pp1 のキー値 7 を表す行を削除し、キー値 10 のエントリが P1 を示すように更新する必要があります。Step 2: The row representing key value 7 in the non-leaf page Pp1 needs to be removed, and the entry for key value 10 updated to point to P1. この処理を実行するために、新しい非リーフ ページ Pp2 が割り当てられ、キー値 7 を表す行を除き、Pp1 のすべての行がコピーされます。キー値 10 の行はページ P1 を示すように更新されます。To do this, a new non-leaf page Pp2 is allocated and all the rows from Pp1 are copied except for the row representing key value 7; then the row for key value 10 is updated to point to page P1. この処理が完了すると、1 つのアトミック手順で、Pp1 を示すページ マッピング テーブルのエントリは Pp2 を示すように更新されます。Once this is done, in one atomic step, the Page Mapping Table entry pointing to Pp1 is updated to point to Pp2. Pp1 には到達できなくなります。Pp1 is no longer reachable.

手順 3: リーフレベル ページ P2 と P1 はマージされ、差分ページは削除されます。Step 3: The Leaf-level pages P2 and P1 are merged and the delta pages removed. この処理を実行するために、新しいページ P3 が割り当てられ、P2 と P1 の行がマージされ、差分ページの変更は新しい P3 に含まれます。To do this, a new page P3 is allocated and the rows from P2 and P1 are merged, and the delta page changes are included in the new P3. 次に、1 つのアトミック手順で、ページ P1 を示すページ マッピング テーブルのエントリは、ページ P3 を示すように更新されます。Then, in one atomic step, the Page Mapping Table entry pointing to page P1 is updated to point to page P3.

パフォーマンスに関する考慮事項Performance considerations

非等値述語でメモリ最適化テーブルを照会する場合は、非クラスター化ハッシュ インデックスより非クラスター化インデックスのパフォーマンスが高くなります。The performance of a nonclustered index is better than nonclustered hash indexes when querying a memory-optimized table with inequality predicates.

注意

メモリ最適化テーブルの列は、ハッシュ インデックスと非クラスター化インデックスの両方に含めることができます。A column in a memory-optimized table can be part of both a hash index and a nonclustered index.

ヒント

非クラスター化インデックス キーの列に、多数の重複値がある場合は、更新、挿入、および削除に関してパフォーマンスが低下します。When a column in a nonclustered index key columns have many duplicate values, performance can degrade for updates, inserts, and deletes. このような場合にパフォーマンスを改善する方法の 1 つは、非クラスター化インデックスに列を追加することです。One way to improve performance in this situation is to add another column to the nonclustered index.

その他の情報Additional Reading

CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
CREATE XML INDEX (Transact-SQL)CREATE XML INDEX (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL) CREATE SPATIAL INDEX (Transact-SQL)
インデックスの再構成と再構築 Reorganize and Rebuild Indexes
SQL Server 2008 のインデックス付きビューによるパフォーマンスの向上Improving Performance with SQL Server 2008 Indexed Views
パーティション テーブルとパーティション インデックスPartitioned Tables and Indexes
主キーを作成する Create a Primary Key
メモリ最適化テーブルのインデックスIndexes for Memory-Optimized Tables
列ストア インデックス - 概要Columnstore Indexes overview
メモリ最適化テーブルのハッシュ インデックスのトラブルシューティング Troubleshooting Hash Indexes for Memory-Optimized Tables
メモリ最適化テーブルの動的管理ビュー (Transact-SQL) Memory-Optimized Table Dynamic Management Views (Transact-SQL)
インデックス関連の動的管理ビューおよび関数 (Transact-SQL) Index Related Dynamic Management Views and Functions (Transact-SQL)
計算列のインデックス Indexes on Computed Columns
インデックスと ALTER TABLE Indexes and ALTER TABLE
Adaptive Index DefragAdaptive Index Defrag