索引 JSON 資料Index JSON data

適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later

在 SQL Server 和 SQL Database 中,JSON 並非內建的資料類型,而且 SQL Server 並沒有自訂 JSON 索引。In SQL Server and SQL Database, 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

將 JSON 資料儲存於 SQL Server 時,通常都要依 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 資料表含有 Info 資料行,其中包含關於銷售訂單的各種資訊 (JSON 格式)。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 preceding example query, 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.

顯示此範例執行計劃的螢幕擷取畫面。Screenshot showing the execution plan for this example.

SQL Server 會使用索引搜尋非叢集索引,並尋找滿足指定條件的資料列,而不會掃描整個資料表。Instead of a full table scan, SQL Server uses an index seek into the nonclustered index and finds the rows that satisfy the specified conditions. 接著,其會在 SalesOrderHeader 資料表中使用索引鍵查詢,以擷取查詢中參考的其他資料行 - 在此範例中為 SalesOrderNumberOrderDateThen 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

若您在索引中新增必要資料行,則可避免資料表執行此額外查詢作業。If you add required columns in the index, you can avoid this additional lookup in the table. 您可新增這些資料行作為標準內含資料行 (如下列範例所示),以擴充之前的 CREATE INDEX 範例。You can add these columns as standard included columns, as shown in the following example, which extends the preceding CREATE INDEX example.

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

在此情況下,SQL Server 不必讀取來自 SalesOrderHeader 資料表的其他資料,這是因為在非叢集 JSON 索引中,已包含其所需的所有必要資料。In this case SQL Server doesn't have to read additional data from the SalesOrderHeader table because everything it needs is included in the nonclustered JSON index. 此索引類型是在查詢中合併 JSON 與資料行資料,以及針對工作負載建立最佳化索引的理想方法。This type of index 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 that the indexes are collation-aware, 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)

上述命令會在計算資料行 vName 上建立標準索引,其代表來自 JSON $.name 屬性的值。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 nonclustered index.

顯示執行計劃的螢幕擷取畫面,該計劃會使用來自非叢集索引的排序值。Screenshot showing an execution plan that uses sorted values from the non-clustered index.

雖然查詢具有 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 nonclustered index where results are already sorted.

不過,若您變更 ORDER BY 運算式的定序 (例如在 JSON_VALUE 函式後方新增 COLLATE French_100_CI_AS_SC),則會得到不同的查詢執行計畫。However, if you change the collation of the ORDER BY expression - for example, if you add COLLATE French_100_CI_AS_SC after the JSON_VALUE function - you get a different query execution plan.

顯示不同執行計劃的螢幕擷取畫面。Screenshot showing a different 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. 因此,其會使用法文定序規則新增 Sort 運算子來排序結果。Therefore, it adds a Sort operator that sorts results using French collation rules.

深入了解 SQL Server 和 Azure SQL Database 中的 JSONLearn more about JSON in SQL Server and Azure SQL Database

Microsoft 影片Microsoft videos

如需 SQL Server 和 Azure SQL Database 中內建 JSON 支援的觀看式簡介,請參閱下列影片:For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos: