JSON 데이터 인덱싱Index JSON data

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

SQL Server 2016에서 JSON은 기본 제공 데이터 형식이 아니며 SQL Server에는 사용자 지정 JSON 인덱스가 없습니다.In SQL Server 2016, JSON is not a built-in data type, and SQL Server does not have custom JSON indexes. 그러나 표준 인덱스를 사용하여 JSON 문서에 대한 쿼리를 최적화할 수 있습니다.You can optimize your queries over JSON documents, however, by using standard indexes.

데이터베이스 인덱스는 필터 및 정렬 작업의 성능을 향상합니다.Database indexes improve the performance of filter and sort operations. 인덱스를 사용하지 않으면 SQL Server는 데이터를 쿼리할 때마다 전체 테이블을 검색해야 합니다.Without indexes, SQL Server has to perform a full table scan every time you query data.

계산된 열을 사용하여 JSON 속성 인덱싱Index JSON properties by using computed columns

SQL Server에 JSON 데이터를 저장하는 경우 JSON 문서 속성 하나 이상을 기준으로 쿼리 결과를 필터링하거나 정렬하는 것이 일반적입니다.When you store JSON data in SQL Server, typically you want to filter or sort query results by one or more properties of the JSON documents.

예제Example

이 예제에서는 AdventureWorks SalesOrderHeader 테이블에 판매 주문에 대한 다양한 정보가 JSON 형식으로 포함되어 있는 Info 열이 있다고 가정합니다.In this example, assume that the AdventureWorks SalesOrderHeader table has an Info column that contains various information in JSON format about sales orders. 예를 들어 이 열은 고객, 영업 사원, 배송 및 대금 청구 주소 등에 대한 정보를 포함합니다.For example, it contains information about customer, sales person, shipping and billing addresses, and so forth. Info 열의 값을 사용하여 고객의 판매 주문을 필터링하려고 합니다.You want to use values from the Info column to filter sales orders for a customer.

최적화할 쿼리Query to optimize

다음은 인덱스를 사용하여 최적화할 쿼리 형식의 예제입니다.Here's an example of the type of query that you want to optimize by using an index.

SELECT SalesOrderNumber,
    OrderDate,
    JSON_VALUE(Info, '$.Customer.Name') AS CustomerName
FROM Sales.SalesOrderHeader
WHERE JSON_VALUE(Info, '$.Customer.Name') = N'Aaron Campbell' 

예제 인덱스Example index

JSON 문서에서 속성에 대한 ORDER BY 절 또는 필터링의 속도를 향상하려면 다른 열에서 이미 사용 중인 동일한 인덱스를 사용할 수 있습니다.If you want to speed up your filters or ORDER BY clauses over a property in a JSON document, you can use the same indexes that you're already using on other columns. 그러나 JSON 문서에서는 속성을 직접 참조할 수 없습니다.However, you can't directly reference properties in the JSON documents.

  1. 먼저 필터링에 사용할 값을 반환하는 “가상 열”을 만들어야 합니다.First, you have to create a "virtual column" that returns the values that you want to use for filtering.
  2. 그런 다음 해당 가상 열에 인덱스를 만들어야 합니다.Then you have to create an index on that virtual column.

다음 예제에서는 인덱싱에 사용할 수 있는 계산 열을 만듭니다.The following example creates a computed column that can be used for indexing. 그런 다음 새 계산 열에서 인덱스를 만듭니다.Then it creates an index on the new computed column. 이 예제에서는 JSON 데이터의 $.Customer.Name 경로에 저장된 고객 이름을 표시하는 열을 만듭니다.This example creates a column that exposes the customer name, which is stored in the $.Customer.Name path in the JSON data.

ALTER TABLE Sales.SalesOrderHeader
ADD vCustomerName AS JSON_VALUE(Info,'$.Customer.Name')

CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)  

계산 열에 대한 자세한 정보More info about the computed column

계산 열은 지속형이 아닙니다.The computed column is not persisted. 인덱스를 다시 작성해야 하는 경우에만 계산됩니다.It's computed only when the index needs to be rebuilt. 테이블에서 추가 공간을 차지하지 않습니다.It does not occupy additional space in the table.

쿼리에서 사용할 동일한 식으로 계산 열을 만드는 것이 중요합니다. 이 예제의 식은 JSON_VALUE(Info, '$.Customer.Name')입니다.It's important that you create the computed column with the same expression that you plan to use in your queries - in this example, the expression is JSON_VALUE(Info, '$.Customer.Name').

쿼리를 다시 작성할 필요가 없습니다.You don’t have to rewrite your queries. 위 예제 쿼리와 같이 JSON_VALUE 함수가 포함된 식을 사용하는 경우 SQL Server는 같은 식을 사용하는 동일한 계산 열이 있는지 확인한 후 해당하는 경우 인덱스를 적용합니다.If you use expressions with the JSON_VALUE function, as shown in the example query above, SQL Server sees that there's an equivalent computed column with the same expression and applies an index if possible.

이 예제에 대한 실행 계획Execution plan for this example

다음은 이 예제의 쿼리 실행 계획입니다.Here's the execution plan for the query in this example.

실행 계획Execution plan

SQL Server는 전체 테이블을 검색하지 않고 비클러스터형 인덱스에서 인덱스 검색하여 지정된 조건을 충족하는 행을 찾습니다.Instead of a full table scan, SQL Server uses an index seek into the non-clustered index and finds the rows that satisfy the specified conditions. 그런 다음 SalesOrderHeader 테이블에서 키 조회를 사용하여 쿼리에서 참조된 다른 열(이 예제에서는 SalesOrderNumberOrderDate)을 가져옵니다.Then it uses a key lookup in the SalesOrderHeader table to fetch the other columns that are referenced in the query - in this example, SalesOrderNumber and OrderDate.

포괄 열을 사용하여 추가로 인덱스 최적화Optimize the index further with included columns

인덱스에 필요한 열을 추가하는 경우 테이블에서 이러한 조회를 추가로 수행할 필요가 없습니다.You can avoid this additional lookup in the table if you add required columns in the index. 위에 표시된 CREATE INDEX 예제를 확장하는 다음 예제처럼 이러한 열을 표준형 포괄 열로 추가할 수 있습니다.You can add these columns as standard included columns, as shown in the following example, which extends the CREATE INDEX example shown above.

CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)
INCLUDE(SalesOrderNumber,OrderDate)

이 경우 비클러스터형 JSON 인덱스에 필요한 모든 사항이 있기 때문에 SQL Server는 SalesOrderHeader 테이블에서 데이터를 추가로 읽을 필요가 없습니다.In this case SQL Server doesn't have to read additional data from the SalesOrderHeader table because everything it needs is included in the non-clustered JSON index. 이것은 쿼리에서 JSON과 열 데이터를 결합하고 작업에 대한 최적의 인덱스를 생성하기 위한 좋은 방법입니다.This is a good way to combine JSON and column data in queries and to create optimal indexes for your workload.

JSON 인덱스는 데이터 정렬 인식 인덱스입니다.JSON indexes are collation-aware indexes

JSON 데이터에 대한 중요한 인덱스 기능은 인덱스의 데이터 정렬 인식 기능입니다.An important feature of indexes over JSON data is that the indexes are collation-aware. 계산 열을 만들 때 사용하는 JSON_VALUE 함수의 결과는 입력 식에서 데이터 정렬을 상속하는 텍스트 값입니다.The result of the JSON_VALUE function that you use when you create the computed column is a text value that inherits its collation from the input expression. 따라서 인덱스의 값은 원본 열에 정의된 데이터 정렬 규칙을 사용하여 정렬됩니다.Therefore, values in the index are ordered using the collation rules defined in the source columns.

이것을 보여주기 위해 다음 예제에서는 기본 키와 JSON 콘텐츠가 있는 단순한 컬렉션 테이블을 만듭니다.To demonstrate this, the following example creates a simple collection table with a primary key and JSON content.

CREATE TABLE JsonCollection
 (
  id INT IDENTITY CONSTRAINT PK_JSON_ID PRIMARY KEY,
  json NVARCHAR(MAX) COLLATE SERBIAN_CYRILLIC_100_CI_AI
  CONSTRAINT [Content should be formatted as JSON]
  CHECK(ISJSON(json)>0)
 ) 

이전 명령은 JSON 열에 대하여 세르비아어 키릴 자모 데이터 정렬을 지정합니다.The preceding command specifies the Serbian Cyrillic collation for the JSON column. 다음 예제에서는 테이블을 자동으로 채우고 이름 속성에 대한 인덱스를 만듭니다.The following example populates the table and creates an index on the name property.

INSERT INTO JsonCollection
VALUES
(N'{"name":"Иво","surname":"Андрић"}'),
(N'{"name":"Андрија","surname":"Герић"}'),
(N'{"name":"Владе","surname":"Дивац"}'),
(N'{"name":"Новак","surname":"Ђоковић"}'),
(N'{"name":"Предраг","surname":"Стојаковић"}'),
(N'{"name":"Михајло","surname":"Пупин"}'),
(N'{"name":"Борислав","surname":"Станковић"}'),
(N'{"name":"Владимир","surname":"Грбић"}'),
(N'{"name":"Жарко","surname":"Паспаљ"}'),
(N'{"name":"Дејан","surname":"Бодирога"}'),
(N'{"name":"Ђорђе","surname":"Вајферт"}'),
(N'{"name":"Горан","surname":"Бреговић"}'),
(N'{"name":"Милутин","surname":"Миланковић"}'),
(N'{"name":"Никола","surname":"Тесла"}')
GO

ALTER TABLE JsonCollection
ADD vName AS JSON_VALUE(json,'$.name')

CREATE INDEX idx_name
ON JsonCollection(vName)

앞의 명령은 JSON $.name 속성의 값을 나타내는 계산 열 vName에 표준 인덱스를 만듭니다.The preceding commands create a standard index on the computed column vName, which represents the value from the JSON $.name property. 세르비아어-키릴 자모 코드 페이지에서 문자 순서는 ‘А’,’Б’,’В’,’Г’,’Д’,’Ђ’,’Е’ 등의 순서입니다. JSON_VALUE 함수의 결과는 원본 열에서 데이터 정렬을 상속하므로 인덱스에서 항목의 순서는 세르비아어 키릴 자모 규칙을 따릅니다.In the Serbian Cyrillic code page, the order of the letters is ‘А’,’Б’,’В’,’Г’,’Д’,’Ђ’,’Е’, etc. The order of items in the index is compliant with Serbian Cyrillic rules because the result of the JSON_VALUE function inherits its collation from the source column. 다음 예제에서는 이 컬렉션을 쿼리하고 이름을 기준으로 결과를 정렬합니다.The following example queries this collection and sorts the results by name.

SELECT JSON_VALUE(json,'$.name'),*
FROM JsonCollection
ORDER BY JSON_VALUE(json,'$.name')

실제 실행 계획을 보면 비클러스터형 인덱스에서 정렬된 값을 사용함을 확인할 수 있습니다.If you look at the actual execution plan, you see that it uses sorted values from the non-clustered index.

실행 계획Execution plan

쿼리에 ORDER BY 절이 있지만 실행 계획은 Sort 연산자를 사용하지 않습니다.Although the query has an ORDER BY clause, the execution plan doesn't use a Sort operator. JSON 인덱스는 이미 세르비아어 키릴 자모 규칙에 따라 정렬됩니다.The JSON index is already ordered according to Serbian Cyrillic rules. 따라서 SQL Server는 결과가 이미 정렬된 비클러스터형 인덱스를 사용합니다.Therefore SQL Server can use the non-clustered index where results are already sorted.

그러나 ORDER BY 식의 데이터 정렬을 변경하면(예: JSON_VALUE 함수 뒤에 COLLATE French_100_CI_AS_SC 입력) 다른 쿼리 실행 계획이 제공됩니다.However, if we change collation of the ORDER BY expression - for example, if we put COLLATE French_100_CI_AS_SC after the JSON_VALUE function - we get a different query execution plan.

실행 계획Execution plan

인덱스 값 순서는 프랑스어 데이터 정렬 규칙을 따르지 않으므로 SQL Server는 정렬 결과에 대한 인덱스를 사용할 수 없습니다.Since the order of values in the index is not compliant with French collation rules, SQL Server can't use the index to order results. 따라서 프랑스어 데이터 정렬 규칙을 사용하여 결과를 정렬하는 정렬 연산자를 추가합니다.Therefore, it adds a Sort operator that sorts results using French collation rules.

SQL Server의 기본 제공 JSON 지원에 대한 자세한 정보Learn more about the built-in JSON support in SQL Server

많은 특정 솔루션, 사용 사례 및 권장 사항은 Microsoft 프로그램 관리자인 Jovan Popovic이 제공하는 SQL Server 및 Azure SQL Database의 기본 제공 JSON 지원에 대한 블로그 게시물을 참조하세요.For lots of specific solutions, use cases, and recommendations, see the blog posts about the built-in JSON support in SQL Server and in Azure SQL Database by Microsoft Program Manager Jovan Popovic.