Indexování dat JSON
platí pro:
SQL Server 2016 (13.x) a novější azure SQL Databaseazure SQL Managed Instance
Dotazy na dokumenty JSON můžete optimalizovat pomocí standardních indexů. SQL Server nemá vlastní indexy JSON.
- V současné době v SQL Serveru json není integrovaný datový typ.
- Datový typ JSON je aktuálně ve verzi Preview pro Azure SQL Database a Azure SQL Managed Instance (konfigurováno s Always-up-to-datem zásad aktualizace).
Indexy fungují stejně jako u dat JSON v varchar/nvarchar nebo nativní datového typu json.
Indexy databází zlepšují výkon operací filtrování a řazení. Bez indexů musí SQL Server při každém dotazování na data provádět úplnou kontrolu tabulek.
Indexování vlastností JSON pomocí počítaných sloupců
Při ukládání dat JSON na SQL Serveru obvykle chcete filtrovat nebo řadit výsledky dotazu podle jedné nebo více vlastností dokumentů JSON.
Příklad
V tomto příkladu předpokládejme, že tabulka AdventureWorks.SalesOrderHeader obsahuje sloupec Info, který obsahuje různé informace ve formátu JSON o prodejních objednávkách. Obsahuje například nestrukturovaná data o zákazních, prodejních osobách, dodacích a fakturačních adresách atd. Hodnoty ze sloupce Info můžete použít k filtrování prodejních objednávek pro zákazníka.
Ve výchozím nastavení sloupec Info, který je použitý, neexistuje, lze ho vytvořit v databázi AdventureWorks s následujícím kódem. Následující příklady se nevztahují na AdventureWorksLT řadu ukázkových databází.
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;
Dotaz pro optimalizaci
Tady je příklad typu dotazu, který chcete optimalizovat pomocí indexu.
SELECT SalesOrderNumber,
OrderDate,
JSON_VALUE(Info, '$.Customer.Name') AS CustomerName
FROM Sales.SalesOrderHeader
WHERE JSON_VALUE(Info, '$.Customer.Name') = N'Aaron Campbell'
Příklad indexu
Pokud chcete zrychlit své filtry nebo klauzule ORDER BY pro vlastnost v dokumentu JSON, můžete použít stejné indexy, které už používáte v jiných sloupcích. V dokumentech JSON však nemůžete přímo odkazovat na vlastnosti.
- Nejprve vytvořte "virtuální sloupec", který vrátí hodnoty, které chcete použít k filtrování.
- Pak vytvořte index pro tento virtuální sloupec.
Následující příklad vytvoří počítaný sloupec, který lze použít k indexování. Pak vytvoří index pro nový vypočítaný sloupec. Tento příklad vytvoří sloupec, který zveřejňuje jméno zákazníka, které je uloženo v cestě $.Customer.Name v datech JSON.
ALTER TABLE Sales.SalesOrderHeader
ADD vCustomerName AS JSON_VALUE(Info,'$.Customer.Name')
CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)
Tento příkaz vrátí následující upozornění:
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.
Funkce JSON_VALUE může vracet textové hodnoty až 8 000 bajtů (například jako nvarchar(4000) typ). Hodnoty delší než 1700 bajtů však nelze indexovat. Pokud se pokusíte zadat hodnotu do indexovaného počítaného sloupce, který je delší než 1700 bajtů, operace jazyka pro manipulaci s daty (DML) selže.
Pokud chcete dosáhnout lepšího výkonu, zkuste přetypovat hodnotu, kterou zpřístupňujete, pomocí počítaného sloupce na nejmenší použitelný datový typ. Používejte typy int a datetime2 místo typů řetězců.
Další informace o počítaném sloupci
Počítaný sloupec se neuchová. Výpočtový sloupec se vypočítá pouze v případech, kdy je potřeba znovu vytvořit index. Nezabírá další místo v tabulce.
Je důležité vytvořit počítaný sloupec se stejným výrazem, který plánujete použít v dotazech – v tomto příkladu je výraz JSON_VALUE(Info, '$.Customer.Name').
Dotazy nemusíte přepisovat. Pokud používáte výrazy s funkcí JSON_VALUE, jak je znázorněno v předchozím ukázkovém dotazu, SQL Server zjistí, že existuje ekvivalentní počítaný sloupec se stejným výrazem a pokud je to možné, použije index.
Plán provádění pro tento příklad
Tady je plán provádění dotazu v tomto příkladu.
Místo úplné kontroly tabulek sql Server použije vyhledávání indexu do neclusterovaného indexu a najde řádky, které splňují zadané podmínky. Potom pomocí klíčového vyhledávání v tabulce SalesOrderHeader načte další sloupce, na které odkazuje dotaz – v tomto příkladu SalesOrderNumber a OrderDate.
Optimalizace indexu dále pomocí zahrnutých sloupců
Pokud do indexu přidáte požadované sloupce, můžete se vyhnout tomuto dalšímu vyhledávání v tabulce. Tyto sloupce můžete přidat jako standardní zahrnuté sloupce, jak je znázorněno v následujícím příkladu, který rozšiřuje předchozí CREATE INDEX příklad.
CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)
INCLUDE(SalesOrderNumber,OrderDate)
V tomto případě SQL Server nemusí číst další data z tabulky SalesOrderHeader, protože vše, co potřebuje, je součástí neclusterovaného indexu JSON. Tento typ indexu je vhodný způsob, jak kombinovat data JSON a sloupce v dotazech a vytvářet optimální indexy pro vaši úlohu.
Indexy JSON jsou indexy pracující s kolací.
Důležitou funkcí indexů nad daty JSON je, že indexy jsou ohleduplné ke kolaci. Výsledek funkce JSON_VALUE, kterou použijete při tvorbě počítaného sloupce, je textová hodnota, která přebírá řazení z původního výrazu. Hodnoty v indexu jsou proto seřazené pomocí pravidel kolace definovaných ve zdrojových sloupcích.
Abychom ukázali, že indexy zohledňují řazení, následující příklad vytvoří jednoduchou tabulku kolekcí s primárním klíčem a obsahem 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)
)
Předchozí příkaz určuje srbské cyrilické řazení pro sloupec json. Následující příklad naplní tabulku a vytvoří index na vlastnosti 'název'.
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)
Předchozí příkazy vytvoří standardní index vypočítaného sloupce vName, který představuje hodnotu z vlastnosti $.name JSON. Na znakové stránce srbské cyrilice je pořadí písmen А, Б, В, Г, Д, Ђ, Е, atd. Pořadí položek v indexu je v souladu s pravidly Srbské Cyriliky, protože výsledek funkce JSON_VALUE dědí kolaci ze zdrojového sloupce. Následující příklad dotazuje tuto kolekci a seřadí výsledky podle názvu.
SELECT JSON_VALUE(json,'$.name'),*
FROM JsonCollection
ORDER BY JSON_VALUE(json,'$.name')
Pokud se podíváte na skutečný plán provádění, uvidíte, že používá seřazené hodnoty z neclusterovaného indexu.
I když dotaz obsahuje klauzuli ORDER BY, plán provádění nepoužívá operátor Sort. Index JSON je již seřazený podle pravidel srbštiny cyrilice. Sql Server proto může použít neclusterovaný index, ve kterém jsou výsledky již seřazené.
Pokud ale změníte kolaci výrazu ORDER BY – například když za funkci JSON_VALUE přidáte COLLATE French_100_CI_AS_SC – získáte jiný plán provádění dotazů.
Vzhledem k tomu, že pořadí hodnot v indexu nevyhovuje francouzským pravidlům kolace, SQL Server nemůže index použít k seřazení výsledků. Proto přidá operátor třídění, který seřadí výsledky podle pravidel francouzské kolace.
Microsoft videa
Poznámka
Některé odkazy na video v této části nemusí v tuto chvíli fungovat. Microsoft migruje obsah dříve na channel 9 na novou platformu. Po migraci videí na novou platformu aktualizujeme odkazy.
Pro vizuální úvod k vestavěné podpoře JSON v SQL Serveru a Azure SQL Database si prohlédněte následující videa: