索引 JSON 資料

適用於： SQL Server 2016 (13.x) 和更新版本Azure SQL DatabaseAzure SQL 受控執行個體

您可使用標準索引最佳化所有 JSON 文件的查詢。 SQL Server 沒有自訂 JSON 索引。

• 目前在 SQL Server 中，JSON 並非內建資料類型。
• 目前，JSON 資料類型適用於 Azure SQL 資料庫。

索引對 varchar/nvarchar 或 原生 JSON 資料類型中的 JSON 資料的運作方式相同。

資料庫索引可改善篩選和排序作業的效能。 若不使用索引，則 SQL Server 在您每次查詢資料時必須執行完整的資料表掃描。

使用計算資料行的索引 JSON 屬性

將 JSON 資料儲存於 SQL Server 時，通常都要依 JSON 文件的一或多個「屬性」來篩選或排序查詢結果。

範例

在此範例中，假設 AdventureWorks.SalesOrderHeader 資料表含有 Info 資料行，其中包含關於銷售訂單的各種資訊 (JSON 格式)。 例如，其中也包含客戶、銷售人員、收件和帳單地址等非結構化資料。 您可使用 Info 資料行的值來篩選客戶的銷售訂單。

根據預設，所使用的資料行 Info 不存在，可使用下列程式碼，在 AdventureWorks 資料庫中建立。 下列範例不適用於 AdventureWorksLT 系列範例資料庫。

IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('[Sales].[SalesOrderHeader]') AND name = 'Info')
    ALTER TABLE [Sales].[SalesOrderHeader] ADD [Info] NVARCHAR(MAX) NULL
GO
UPDATE h 
SET [Info] =
(
    SELECT [Customer.Name]  = concat(p.FirstName, N' ', p.LastName), 
           [Customer.ID]    = p.BusinessEntityID, 
           [Customer.Type]  = p.[PersonType], 
           [Order.ID]       = soh.SalesOrderID, 
           [Order.Number]   = soh.SalesOrderNumber, 
           [Order.CreationData] = soh.OrderDate, 
           [Order.TotalDue] = soh.TotalDue
    FROM [Sales].SalesOrderHeader AS soh
         INNER JOIN [Sales].[Customer] AS c ON c.CustomerID = soh.CustomerID
         INNER JOIN [Person].[Person] AS p ON p.BusinessEntityID = c.CustomerID
    WHERE soh.SalesOrderID = h.SalesOrderID FOR JSON PATH, WITHOUT_ARRAY_WRAPPER 
)
FROM [Sales].SalesOrderHeader AS h; 

要最佳化的查詢

以下是要透過索引來最佳化的查詢類型的範例。

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

範例索引

若您想要針對 JSON 文件中的屬性加速篩選或 ORDER BY 子句處理，則可使用與其他資料行所用相同的索引。 不過，您無法「直接」參考 JSON 文件中的屬性。

1. 首先，建立「虛擬資料行」來傳回要用於篩選的值。
2. 然後，在該虛擬資料行建立索引。

下列範例會建立可用於索引的計算資料行，然後在該資料行上建立索引。 然後它會在新的計算資料行上建立索引。 此範例會建立公開客戶名稱的資料行，其儲存於 JSON 文件中的 $.Customer.Name 路徑。

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

CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)  

此陳述式會傳回下列警告：

Warning! The maximum key length for a nonclustered index is 1700 bytes.
The index 'vCustomerName' has maximum length of 8000 bytes.
For some combination of large values, the insert/update operation will fail.

例如，JSON_VALUE 函數可能會傳回最多 8000 個位元組的文字值 (例如 nvarchar(4000) 類型)。 不過，超過 1700 個位元組的值無法編制索引。 如果您嘗試在超過 1700 個位元組長，且已編制索引的計算資料行中輸入值，資料操作語言 (DML) 運算將會失敗。

為了獲得更佳的效能，請嘗試將您已使用計算資料行公開的值，轉換成最小的適用資料類型。 使用 int 和 datetime2 類型，而不是字串類型。

計算資料行的詳細資訊

系統不會保存計算資料行。 僅在需要重建索引時才會計算計算資料行。 其不會佔用資料表中的額外空間。

請務必使用您計劃在查詢中所用的相同運算式，建立計算資料行 - 在此範例中，運算式為 JSON_VALUE(Info, '$.Customer.Name')。

您無須重寫查詢。 若您使用具有 JSON_VALUE 函式的運算式 (如之前的範例查詢所示)，則 SQL Server 會發現有一個具有相同運算式的同等計算資料行，並盡可能套用索引。

此範例中的執行計畫

以下是此範例中的查詢執行計畫。

SQL Server 會使用索引搜尋非叢集索引，並尋找滿足指定條件的資料列，而不會掃描整個資料表。 接著，伺服器會在 SalesOrderHeader 資料表中使用索引鍵查詢，以擷取查詢中參考的其他資料行，在此範例中為 SalesOrderNumber 和 OrderDate。

利用內含資料行進一步最佳化索引

若您在索引中新增必要資料行，則可避免資料表執行此額外查詢作業。 您可新增這些資料行作為標準內含資料行 (如下列範例所示)，以擴充之前的 CREATE INDEX 範例。

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

在此情況下，SQL Server 不必讀取來自 SalesOrderHeader 資料表的其他資料，這是因為在非叢集 JSON 索引中，已包含其所需的所有必要資料。 此索引類型是在查詢中合併 JSON 與資料行資料，以及針對工作負載建立最佳化索引的理想方法。

JSON 索引是感知定序的索引

JSON 資料之索引的重要功能是索引能夠感知定序。 您在建立計算資料行時所使用的 JSON_VALUE 函數的結果為文字值，其繼承來自輸入運算式的定序。 因此，在索引中的值會使用來源資料行中定義的定序規則加以排序。

為了示範索引能夠感知定序，下列範例會建立具有主索引鍵與 JSON 內容的簡單集合資料表。

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 資料行指定塞爾維亞文 (斯拉夫) 定序。 下列範例會在名稱屬性上填入資料表並建立索引。

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 屬性的值。 在塞爾維亞文 (斯拉夫) 字碼頁中，字母順序如下：А、Б、В、Г、Д、Ђ、Е 等等。索引中的項目順序會與塞爾維亞文 (斯拉夫) 規則相容，這是因為 JSON_VALUE 函數的結果會繼承其來自來源資料行的定序。 下列範例會查詢此集合物件，並依名稱排序結果。

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

若您查看實際執行計劃，會發現其使用來自非叢集索引的排序值。

雖然查詢具有 ORDER BY 子句，但執行計畫不會使用 Sort 運算子。 JSON 索引已根據塞爾維亞文 (斯拉夫) 規則執行排序。 因此，SQL Server 可在結果已排序的情況下，使用非叢集索引。

不過，若您變更 ORDER BY 運算式的定序 (例如在 JSON_VALUE 函式後方新增 COLLATE French_100_CI_AS_SC)，則會得到不同的查詢執行計畫。

由於索引中的值順序不符合法文定序規則，因此 SQL Server 無法使用索引來排序結果。 因此，其會使用法文定序規則新增 Sort 運算子來排序結果。

Microsoft 影片

注意

本節中的部分影片連結目前可能無法運作。 Microsoft 正在將先前在 Channel 9 上的內容移轉至新的平台。 我們會在影片移轉至新平台時更新連結。

如需 SQL Server 和 Azure SQL Database 中內建 JSON 支援的觀看式簡介，請參閱下列影片：

• NoSQL 與關聯式領域之間的橋樑 JSON

相關內容

• 使用記憶體內部 OLTP 最佳化 JSON 處理
• SQL Server 中的 JSON 資料
• JSON 資料類型