Indeksowanie danych JSON

Dotyczy: SQL Server 2016 (13.x) i nowszych Azure SQL DatabaseAzure SQL Managed Instance

Zapytania można zoptymalizować w dokumentach JSON przy użyciu indeksów standardowych. Program SQL Server nie ma niestandardowych indeksów JSON.

• Obecnie w programie SQL Server json nie jest wbudowanym typem danych.
• Typ danych JSON jest obecnie w wersji zapoznawczej dla usług Azure SQL Database i Azure SQL Managed Instance (skonfigurowany z Always-up-to-date update policy).

Indeksy działają tak samo na danych JSON w typach danych varchar/nvarchar lub natywnym json.

Indeksy baz danych zwiększają wydajność operacji filtrowania i sortowania. Bez indeksów program SQL Server musi wykonać pełne skanowanie tabeli za każdym razem, gdy wykonujesz zapytania dotyczące danych.

Indeksowanie właściwości JSON przy użyciu kolumn obliczeniowych

Podczas przechowywania danych JSON w programie SQL Server zazwyczaj chcesz filtrować lub sortować wyniki zapytania według co najmniej jednej właściwości dokumentów JSON.

Przykład

W tym przykładzie przyjęto założenie, że tabela AdventureWorks.SalesOrderHeader zawiera kolumnę Info zawierającą różne informacje w formacie JSON dotyczące zamówień sprzedaży. Na przykład zawiera dane bez struktury dotyczące klienta, osoby sprzedaży, wysyłki i adresów rozliczeniowych itd. Wartości z kolumny Info można użyć do filtrowania zamówień sprzedaży dla klienta.

Domyślnie używana kolumna Info nie istnieje, można ją utworzyć w bazie danych AdventureWorks przy użyciu następującego kodu. Poniższe przykłady nie mają zastosowania do serii AdventureWorksLT przykładowych baz danych.

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; 

Zapytanie w celu optymalizacji

Oto przykład typu zapytania, które chcesz zoptymalizować przy użyciu indeksu.

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

Przykładowy indeks

Jeśli chcesz przyspieszyć filtry lub klauzule ORDER BY dla właściwości w dokumencie JSON, możesz użyć tych samych indeksów, które są już używane w innych kolumnach. Nie można jednak bezpośrednio odwoływać się do właściwości w dokumentach JSON.

1. Najpierw utwórz "kolumnę wirtualną", która zwraca wartości, których chcesz użyć do filtrowania.
2. Następnie utwórz indeks w tej kolumnie wirtualnej.

Poniższy przykład tworzy obliczoną kolumnę, która może służyć do indeksowania. Następnie tworzy indeks dla nowej obliczonej kolumny. W tym przykładzie zostanie utworzona kolumna, która uwidacznia nazwę klienta, która jest przechowywana w ścieżce $.Customer.Name w danych JSON.

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

CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)  

Ta komunikat zwróci następujące ostrzeżenie:

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.

Funkcja JSON_VALUE może zwracać wartości tekstowe do 8000 bajtów (na przykład jako typ nvarchar(4000)). Nie można jednak indeksować wartości dłuższych niż 1700 bajtów. Jeśli spróbujesz wprowadzić wartość w indeksowanej kolumnie obliczeniowej, która jest dłuższa niż 1700 bajtów, operacja języka manipulowania danymi (DML) zakończy się niepowodzeniem.

Aby uzyskać lepszą wydajność, spróbuj rzutować wartość uwidacznianą przy użyciu obliczonej kolumny na najmniejszy odpowiedni typ danych. Użyj int i datetime2 zamiast typów ciągów znaków.

Więcej informacji o obliczonej kolumnie

Obliczona kolumna nie jest utrwalona. Kolumna komputera obliczana tylko wtedy, gdy indeks musi zostać ponownie skompilowany. Nie zajmuje dodatkowego miejsca w tabeli.

Ważne jest, aby utworzyć obliczoną kolumnę z tym samym wyrażeniem, które ma być używane w zapytaniach — w tym przykładzie wyrażenie jest JSON_VALUE(Info, '$.Customer.Name').

Nie musisz ponownie pisać zapytań. Jeśli używasz wyrażeń z funkcją JSON_VALUE, jak pokazano w poprzednim przykładowym zapytaniu, program SQL Server zobaczy, że istnieje równoważna kolumna obliczeniowa z tym samym wyrażeniem i jeśli jest to możliwe, zastosuje indeks.

Plan wykonania dla tego przykładu

Oto plan wykonania zapytania w tym przykładzie.

Zamiast pełnego skanowania tabeli program SQL Server używa indeksu wyszukiwania do indeksu nieklastrowanego i znajduje wiersze spełniające określone warunki. Następnie użyje wyszukiwania klucza w tabeli SalesOrderHeader, aby pobrać inne kolumny, do których odwołuje się zapytanie — w tym przykładzie SalesOrderNumber i OrderDate.

Optymalizuj indeks bardziej, uwzględniając dołączone kolumny

Jeśli dodasz wymagane kolumny w indeksie, możesz uniknąć tego dodatkowego wyszukiwania w tabeli. Możesz dodać te kolumny jako standardowe dołączone kolumny, jak pokazano w poniższym przykładzie, co rozszerza poprzedni przykład CREATE INDEX.

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

W takim przypadku program SQL Server nie musi odczytywać dodatkowych danych z tabeli SalesOrderHeader, ponieważ wszystko, czego potrzebuje, jest uwzględniane w indeksie JSON nieklastrowanym. Ten typ indeksu to dobry sposób łączenia danych JSON i kolumn w zapytaniach oraz tworzenia optymalnych indeksów dla obciążenia.

Indeksy JSON są indeksami obsługującymi sortowanie

Ważną funkcją indeksów w danych JSON jest to, że indeksy uwzględniają reguły sortowania. Wynikiem funkcji JSON_VALUE, którą używasz przy tworzeniu obliczonej kolumny, jest tekstowa wartość, która dziedziczy sortowanie z wyrażenia wejściowego. W związku z tym wartości w indeksie są uporządkowane przy użyciu reguł sortowania zdefiniowanych w kolumnach źródłowych.

Aby zademonstrować, że indeksy obsługują sortowanie, poniższy przykład tworzy prostą tabelę kolekcji z kluczem podstawowym i zawartością 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)
 ) 

Poprzednie polecenie określa porządek sortowania serbską cyrylicą dla kolumny json. Poniższy przykład wypełnia tabelę i tworzy indeks dla właściwości 'name'.

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)

Powyższe polecenia tworzą indeks standardowy dla obliczonej kolumny vName, która reprezentuje wartość z właściwości $.name JSON. Na stronie kodowej serbskiej cyrylicy kolejność liter to А, Б, В, Г, Д, Ђ, Еitp. Kolejność elementów w indeksie jest zgodna z regułami serbskiej cyrylicy, ponieważ wynik funkcji JSON_VALUE dziedziczy sortowanie z kolumny źródłowej. Poniższy przykład wykonuje zapytanie dotyczące tej kolekcji i sortuje wyniki według nazwy.

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

Jeśli spojrzysz na rzeczywisty plan wykonania, zobaczysz, że używa ona posortowanych wartości z indeksu nieklastrowanego.

Mimo że zapytanie ma klauzulę ORDER BY, plan wykonywania nie używa operatora sortowania. Indeks JSON jest już uporządkowany zgodnie z serbskimi cyrylicami. W związku z tym program SQL Server może używać indeksu nieklastrowanego, w którym wyniki są już sortowane.

Jeśli jednak zmienisz sortowanie wyrażenia ORDER BY — na przykład jeśli dodasz COLLATE French_100_CI_AS_SC po funkcji JSON_VALUE — otrzymasz inny plan wykonywania zapytania.

Ponieważ kolejność wartości w indeksie nie jest zgodna z regułami sortowania francuskiego, program SQL Server nie może używać indeksu do porządkowania wyników. W związku z tym dodaje operator sortowania, który sortuje wyniki przy użyciu reguł sortowania francuskiego.

Klipy wideo firmy Microsoft

Aby zapoznać się z wizualnym wprowadzeniem do wbudowanej obsługi kodu JSON w programie SQL Server i usłudze Azure SQL Database, zobacz następujące wideo:

• JSON jako most między światami NoSQL i relacyjnymi

Powiązana zawartość

• Optymalizowanie przetwarzania JSON z OLTP pamięciowym
• Dane JSON w SQL Server
• typ danych JSON