Mengindeks data JSON

Berlaku untuk: SQL Server 2016 (13.x) dan Azure SQL DatabaseAzure SQL Managed Instance yang lebih baru

Anda dapat mengoptimalkan kueri Anda melalui dokumen JSON menggunakan indeks standar. SQL Server tidak memiliki indeks JSON kustom.

• Saat ini, di Json SQL Server bukan jenis data bawaan.
• Saat ini, jenis data JSON tersedia di Azure SQL Database.

Indeks bekerja dengan cara yang sama pada data JSON di varchar/nvarchar atau jenis data json asli.

Indeks database meningkatkan performa operasi filter dan pengurutan. Tanpa indeks, SQL Server harus melakukan pemindaian tabel penuh setiap kali Anda mengkueri data.

Mengindeks properti JSON dengan menggunakan kolom komputasi

Saat Anda menyimpan data JSON di SQL Server, biasanya Anda ingin memfilter atau mengurutkan hasil kueri menurut satu atau beberapa properti dokumen JSON.

Contoh

Dalam contoh ini, asumsikan bahwa AdventureWorks.SalesOrderHeader tabel memiliki Info kolom yang berisi berbagai informasi dalam format JSON tentang pesanan penjualan. Misalnya, berisi data yang tidak terstruktur tentang pelanggan, sales person, alamat pengiriman dan penagihan, dan sebagainya. Anda dapat menggunakan nilai dari Info kolom untuk memfilter pesanan penjualan untuk pelanggan.

Secara default, kolom yang digunakan tidak ada, kolom Info dapat dibuat dalam AdventureWorks database dengan kode berikut. Contoh berikut tidak berlaku untuk AdventureWorksLT rangkaian database sampel.

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; 

Kueri untuk dioptimalkan

Berikut adalah contoh jenis kueri yang ingin Anda optimalkan dengan menggunakan indeks.

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

Contoh indeks

Jika Anda ingin mempercepat filter atau ORDER BY klausul melalui properti dalam dokumen JSON, Anda bisa menggunakan indeks yang sama dengan yang sudah Anda gunakan di kolom lain. Namun, Anda tidak dapat langsung mereferensikan properti dalam dokumen JSON.

1. Pertama, buat "kolom virtual" yang mengembalikan nilai yang ingin Anda gunakan untuk pemfilteran.
2. Kemudian, buat indeks pada kolom virtual tersebut.

Contoh berikut membuat kolom komputasi yang dapat digunakan untuk pengindeksan. Kemudian membuat indeks pada kolom komputasi baru. Contoh ini membuat kolom yang mengekspos nama pelanggan, yang disimpan di $.Customer.Name jalur dalam data JSON.

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

CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)  

Pernyataan ini akan mengembalikan peringatan berikut:

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.

Fungsi JSON_VALUE mungkin mengembalikan nilai teks hingga 8000 byte (misalnya, sebagai jenis nvarchar(4000 ). Namun, nilai yang lebih panjang dari 1700 byte tidak dapat diindeks. Jika Anda mencoba memasukkan nilai di kolom komputasi terindeks yang lebih panjang dari 1700 byte, operasi bahasa manipulasi data (DML) akan gagal.

Untuk performa yang lebih baik, cobalah untuk mentransmisikan nilai yang Anda ekspos menggunakan kolom komputasi ke dalam jenis data terkecil yang berlaku. Gunakan jenis int dan datetime2 alih-alih jenis string.

Info selengkapnya tentang kolom komputasi

Kolom komputasi tidak bertahan. Kolom komputer dihitung hanya ketika indeks perlu dibangun kembali. Ini tidak menempati ruang tambahan dalam tabel.

Penting bagi Anda untuk membuat kolom komputasi dengan ekspresi yang sama dengan yang Anda rencanakan untuk digunakan dalam kueri Anda - dalam contoh ini, ekspresinya adalah JSON_VALUE(Info, '$.Customer.Name').

Anda tidak perlu menulis ulang kueri Anda. Jika Anda menggunakan ekspresi dengan JSON_VALUE fungsi , seperti yang diperlihatkan dalam kueri contoh sebelumnya, SQL Server melihat bahwa ada kolom komputasi yang setara dengan ekspresi yang sama dan menerapkan indeks jika memungkinkan.

Rencana eksekusi untuk contoh ini

Berikut adalah rencana eksekusi untuk kueri dalam contoh ini.

Alih-alih pemindaian tabel penuh, SQL Server menggunakan pencarian indeks ke dalam indeks non-kluster dan menemukan baris yang memenuhi kondisi yang ditentukan. Kemudian menggunakan pencarian kunci dalam SalesOrderHeader tabel untuk mengambil kolom lain yang dirujuk dalam kueri - dalam contoh ini, SalesOrderNumber dan OrderDate.

Optimalkan indeks lebih lanjut dengan kolom yang disertakan

Jika Anda menambahkan kolom yang diperlukan dalam indeks, Anda dapat menghindari pencarian tambahan ini dalam tabel. Anda dapat menambahkan kolom ini sebagai kolom standar yang disertakan, seperti yang diperlihatkan dalam contoh berikut, yang memperluas contoh sebelumnya CREATE INDEX .

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

Dalam hal ini, SQL Server tidak perlu membaca data tambahan dari SalesOrderHeader tabel karena semua yang dibutuhkan disertakan dalam indeks JSON non-kluster. Jenis indeks ini adalah cara yang baik untuk menggabungkan JSON dan data kolom dalam kueri dan untuk membuat indeks optimal untuk beban kerja Anda.

Indeks JSON adalah indeks sadar kolabasi

Fitur penting indeks melalui data JSON adalah indeks sadar kolab. Hasil JSON_VALUE fungsi yang Anda gunakan saat membuat kolom komputasi adalah nilai teks yang mewarisi kolasenya dari ekspresi input. Oleh karena itu, nilai dalam indeks diurutkan menggunakan aturan kolase yang ditentukan dalam kolom sumber.

Untuk menunjukkan bahwa indeks sadar kolabasi, contoh berikut membuat tabel koleksi sederhana dengan kunci primer dan konten 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)
 ) 

Perintah sebelumnya menentukan kolase Sirilik Serbia untuk kolom .json Contoh berikut mengisi tabel dan membuat indeks pada properti nama.

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)

Perintah sebelumnya membuat indeks standar pada kolom vNamekomputasi , yang mewakili nilai dari properti JSON $.name . Di halaman kode Sirilik Serbia, urutan huruf adalah А, , , ВГБ, Д, Ђ, Е, dll. Urutan item dalam indeks sesuai dengan aturan Sirilik Serbia karena hasil JSON_VALUE fungsi mewarisi kolasenya dari kolom sumber. Contoh berikut mengkueri koleksi ini dan mengurutkan hasil berdasarkan nama.

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

Jika Anda melihat rencana eksekusi aktual, Anda akan melihat bahwa paket tersebut menggunakan nilai yang diurutkan dari indeks non-kluster.

Meskipun kueri memiliki klausa ORDER BY , rencana eksekusi tidak menggunakan operator Urutkan. Indeks JSON sudah dipesan sesuai dengan aturan Sirilik Serbia. Oleh karena itu SQL Server dapat menggunakan indeks nonclustered tempat hasil sudah diurutkan.

Namun, jika Anda mengubah kolaborasi ORDER BY ekspresi - misalnya, jika Anda menambahkan COLLATE French_100_CI_AS_SC setelah JSON_VALUE fungsi - Anda mendapatkan rencana eksekusi kueri yang berbeda.

Karena urutan nilai dalam indeks tidak sesuai dengan aturan kolase Prancis, SQL Server tidak dapat menggunakan indeks untuk memesan hasil. Oleh karena itu, ini menambahkan operator Sortir yang mengurutkan hasil menggunakan aturan kolatasi Prancis.

Video Microsoft

Catatan

Beberapa tautan video di bagian ini mungkin tidak berfungsi saat ini. Microsoft memigrasikan konten sebelumnya di Channel 9 ke platform baru. Kami akan memperbarui tautan saat video dimigrasikan ke platform baru.

Untuk pengenalan visual dukungan JSON bawaan di SQL Server dan Azure SQL Database, lihat video berikut ini:

• JSON sebagai jembatan antara NoSQL dan dunia relasional

Konten terkait

• Mengoptimalkan pemrosesan JSON dengan OLTP dalam memori
• Data JSON dalam SQL Server
• Jenis data JSON