Indeks XML (SQL Server)

Artikel
08/04/2023

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance

Indeks XML dapat dibuat pada kolom tipe data xml . Mereka mengindeks semua tag, nilai, dan jalur atas instans XML di kolom dan menguntungkan performa kueri. Aplikasi Anda mungkin mendapat manfaat dari indeks XML dalam situasi berikut:

Kueri pada kolom XML umum dalam beban kerja Anda. Biaya pemeliharaan indeks XML selama modifikasi data harus dipertimbangkan.
Nilai XML Anda relatif besar dan bagian yang diambil relatif kecil. Membangun indeks menghindari penguraian seluruh data pada run time dan pencarian indeks manfaat untuk pemrosesan kueri yang efisien.

Dimulai dengan SQL Server 2022 (16.x) dan versi yang lebih baru, dan di Azure SQL Database dan Azure SQL Managed Instance, Anda dapat menggunakan kompresi XML untuk memadatkan data XML di luar baris untuk kolom dan indeks XML. Pemadatan XML mengurangi persyaratan kapasitas penyimpanan data.

Indeks XML termasuk dalam kategori berikut:

Indeks XML utama
Indeks XML sekunder

Indeks pertama pada kolom jenis xml harus merupakan indeks XML utama. Menggunakan indeks XML utama, jenis indeks sekunder berikut didukung: PATH, VALUE, dan PROPERTY. Bergantung pada jenis kueri, indeks sekunder ini mungkin membantu meningkatkan performa kueri.

Catatan

Anda tidak dapat membuat atau mengubah indeks XML kecuali opsi database diatur dengan benar untuk bekerja dengan tipe data xml . Untuk informasi selengkapnya, lihat Menggunakan Pencarian Teks Lengkap dengan Kolom XML.

Instans XML disimpan dalam kolom jenis xml sebagai objek biner besar (BLOB). Instans XML ini bisa besar, dan representasi biner tersimpan dari instans jenis data xml bisa hingga 2 GB. Tanpa indeks, objek besar biner ini di-shredding pada waktu proses untuk mengevaluasi kueri. Penghancur ini bisa memakan waktu. Misalnya, pertimbangkan kueri di bawah ini:

;WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1;

Untuk memilih instans XML yang memenuhi kondisi dalam WHERE klausa, objek besar biner XML (BLOB) di setiap baris tabel Production.ProductModel dihancurkan pada waktu proses. Kemudian, ekspresi (/PD:ProductDescription/@ProductModelID[.="19"]) dalam exist() metode dievaluasi. Penghancuran run-time ini bisa mahal, tergantung pada ukuran dan jumlah instans yang disimpan di kolom.

Jika mengkueri objek besar biner XML (BLOB) umum di lingkungan aplikasi Anda, ini membantu mengindeks kolom jenis xml . Namun, ada biaya yang terkait dengan mempertahankan indeks selama modifikasi data.

Indeks XML utama

Indeks XML utama mengindeks semua tag, nilai, dan jalur dalam instans XML di kolom XML. Untuk membuat indeks XML utama, tabel tempat kolom XML terjadi harus memiliki indeks berkluster pada kunci utama tabel. SQL Server menggunakan kunci utama ini untuk menghubungkan baris dalam indeks XML utama dengan baris dalam tabel yang berisi kolom XML.

Indeks XML utama adalah representasi BLOB XML yang dihancurkan dan dipertahankan di kolom tipe data xml . Untuk setiap objek besar biner XML (BLOB) di kolom, indeks membuat beberapa baris data. Jumlah baris dalam indeks kira-kira sama dengan jumlah simpul dalam objek besar biner XML. Saat kueri mengambil instans XML lengkap, SQL Server menyediakan instans dari kolom XML. Kueri dalam instans XML menggunakan indeks XML utama, dan dapat mengembalikan nilai skalar atau subtrees XML dengan menggunakan indeks itu sendiri.

Setiap baris menyimpan informasi node berikut:

Nama tag seperti elemen atau nama atribut.
Nilai simpul.
Jenis node seperti node elemen, node atribut, atau node teks.
Informasi pesanan dokumen, diwakili oleh pengidentifikasi simpul internal.
Jalur dari setiap simpul ke akar pohon XML. Kolom ini dicari untuk ekspresi jalur dalam kueri.
Kunci utama tabel dasar. Kunci utama tabel dasar diduplikasi dalam indeks XML utama untuk gabungan kembali dengan tabel dasar, dan jumlah kolom maksimum dalam kunci utama tabel dasar dibatasi hingga 15.

Informasi simpul ini digunakan untuk mengevaluasi dan membuat hasil XML untuk kueri tertentu. Untuk tujuan pengoptimalan, nama tag dan informasi jenis node dikodekan sebagai nilai bilangan bulat, dan kolom Jalur menggunakan pengodean yang sama. Selain itu, jalur disimpan dalam urutan terbalik untuk memungkinkan jalur yang cocok ketika hanya akhiran jalur yang diketahui. Contohnya:

//ContactRecord/PhoneNumber di mana hanya dua langkah terakhir yang diketahui

ATAU

/Book/*/Title di mana karakter * kartubebas ditentukan di tengah ekspresi.

Prosesor kueri menggunakan indeks XML utama untuk kueri yang melibatkan Metode Tipe Data xml dan mengembalikan nilai skalar atau subtrees XML dari indeks utama itu sendiri. (Indeks ini menyimpan semua informasi yang diperlukan untuk membangun kembali instans XML.)

Misalnya, kueri berikut mengembalikan informasi ringkasan yang disimpan di CatalogDescriptionkolom jenis xml dalam ProductModel tabel. Kueri mengembalikan <Summary> informasi hanya untuk model produk yang deskripsi katalognya juga menyimpan <Features> deskripsi.

;WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('  /PD:ProductDescription/PD:Summary') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

Mengenai indeks XML utama, alih-alih menghancurkan setiap instans objek besar biner XML dalam tabel dasar, baris dalam indeks yang sesuai dengan setiap objek besar biner XML dicari secara berurutan untuk ekspresi yang ditentukan dalam exist() metode . Jika jalur ditemukan di kolom Jalur dalam indeks, <Summary> elemen bersama dengan subtreesnya diambil dari indeks XML utama dan dikonversi menjadi objek besar biner XML sebagai hasil dari query() metode .

Indeks XML utama tidak digunakan saat mengambil instans XML lengkap. Misalnya, kueri berikut mengambil dari tabel seluruh instans XML yang menjelaskan instruksi manufaktur untuk model produk tertentu.

USE AdventureWorks2022;

SELECT Instructions
FROM   Production.ProductModel
WHERE  ProductModelID = 7;

Indeks XML sekunder

Untuk meningkatkan performa pencarian, Anda dapat membuat indeks XML sekunder. Indeks XML utama harus terlebih dahulu ada sebelum Anda dapat membuat indeks sekunder. Ini adalah jenisnya:

Indeks XML sekunder PATH
NILAI indeks XML sekunder
Indeks XML sekunder PROPERTI

Berikut adalah beberapa panduan untuk membuat satu atau beberapa indeks sekunder:

Jika beban kerja Anda menggunakan ekspresi jalur secara signifikan pada kolom XML, indeks XML sekunder PATH kemungkinan akan mempercepat beban kerja Anda. Kasus yang paling umum adalah penggunaan exist() metode pada kolom XML dalam klausa WHERE Transact-SQL.
Jika beban kerja Anda mengambil beberapa nilai dari instans XML individual dengan menggunakan ekspresi jalur, jalur pengklusteran dalam setiap instans XML dalam indeks PROPERTI mungkin berguna. Skenario ini biasanya terjadi dalam skenario tas properti ketika properti objek diambil dan nilai kunci utamanya diketahui.
Jika beban kerja Anda melibatkan kueri untuk nilai dalam instans XML tanpa mengetahui elemen atau nama atribut yang berisi nilai tersebut, Anda mungkin ingin membuat indeks VALUE. Ini biasanya terjadi dengan pencarian sumbu turunan, seperti //author[last-name="Howard"], di mana <author> elemen dapat terjadi pada tingkat hierarki apa pun. Ini juga terjadi dalam kueri wildcard, seperti /book [@* = "novel"], di mana kueri mencari <book> elemen yang memiliki beberapa atribut yang memiliki nilai "novel".

Indeks XML sekunder PATH

Jika kueri Anda umumnya menentukan ekspresi jalur pada kolom jenis xml , indeks sekunder PATH mungkin dapat mempercepat pencarian. Seperti yang dijelaskan sebelumnya dalam artikel ini, indeks utama sangat membantu ketika Anda memiliki kueri yang menentukan exist() metode dalam klausa WHERE. Jika Anda menambahkan indeks sekunder PATH, Anda juga dapat meningkatkan performa pencarian dalam kueri tersebut.

Meskipun indeks XML utama menghindari harus menghancurkan objek besar biner XML pada waktu proses, indeks mungkin tidak memberikan performa terbaik untuk kueri berdasarkan ekspresi jalur. Karena semua baris dalam indeks XML utama yang sesuai dengan objek besar biner XML dicari secara berurutan untuk instans XML besar, pencarian berurutan mungkin lambat. Dalam hal ini, memiliki indeks sekunder yang dibangun pada nilai jalur dan nilai simpul dalam indeks utama dapat mempercepat pencarian indeks secara signifikan. Dalam indeks sekunder PATH, nilai jalur dan simpul adalah kolom kunci yang memungkinkan pencarian yang lebih efisien saat mencari jalur. Pengoptimal kueri dapat menggunakan indeks PATH untuk ekspresi seperti yang diperlihatkan dalam hal berikut:

/root/Location yang hanya menentukan jalur

ATAU

/root/Location/@LocationID[.="10"] di mana jalur dan nilai simpul ditentukan.

Kueri berikut ini memperlihatkan di mana indeks PATH berguna:

;WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') AS Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1;

Dalam kueri, ekspresi /PD:ProductDescription/@ProductModelID jalur dan nilai "19" dalam exist() metode sesuai dengan bidang kunci indeks PATH. Ini memungkinkan pencarian langsung dalam indeks PATH dan memberikan performa pencarian yang lebih baik daripada pencarian berurutan untuk nilai jalur dalam indeks utama.

NILAI indeks XML sekunder

Jika kueri berbasis nilai, misalnya, /Root/ProductDescription/@*[. = "Mountain Bike"] atau //ProductDescription[@Name = "Mountain Bike"], dan jalur tidak sepenuhnya ditentukan atau menyertakan kartubebas, Anda mungkin mendapatkan hasil yang lebih cepat dengan membangun indeks XML sekunder yang dibangun pada nilai simpul dalam indeks XML utama.

Kolom kunci indeks VALUE adalah (nilai dan jalur simpul) dari indeks XML utama. Jika beban kerja Anda melibatkan kueri nilai dari instans XML tanpa mengetahui elemen atau nama atribut yang berisi nilai, indeks VALUE mungkin berguna. Misalnya, ekspresi berikut akan mendapat manfaat dari memiliki indeks VALUE:

//author[LastName="someName"] di mana Anda mengetahui nilai <LastName> elemen, tetapi induk dapat terjadi di <author> mana saja.
/book[@* = "someValue"] di mana kueri mencari <book> elemen yang memiliki beberapa atribut yang memiliki nilai "someValue".

Kueri berikut mengembalikan ContactID dari Contact tabel. Klausa WHERE menentukan filter yang mencari nilai di AdditionalContactInfokolom jenis xml . ID kontak dikembalikan hanya jika informasi kontak tambahan yang sesuai objek besar biner XML menyertakan nomor telepon tertentu. telephoneNumber Karena elemen mungkin muncul di mana saja dalam XML, ekspresi jalur menentukan sumbu descendent-or-self.

;WITH XMLNAMESPACES (
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo' AS CI,
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS ACT
)

SELECT ContactID
FROM Person.Contact
WHERE AdditionalContactInfo.exist('//ACT:telephoneNumber/ACT:number[.="111-111-1111"]') = 1;

Dalam situasi ini, nilai pencarian untuk <number> diketahui, tetapi dapat muncul di mana saja dalam instans XML sebagai anak dari telephoneNumber elemen . Kueri semacam ini mungkin mendapat manfaat dari pencarian indeks berdasarkan nilai tertentu.

Indeks sekunder PROPERTI

Kueri yang mengambil satu atau beberapa nilai dari masing-masing instans XML mungkin mendapat manfaat dari indeks PROPERTI. Skenario ini terjadi saat Anda mengambil properti objek dengan menggunakan value() metode jenis xml dan kapan nilai kunci utama objek diketahui.

Indeks PROPERTI dibangun pada kolom (PK, jalur, dan nilai simpul) dari indeks XML utama di mana PK adalah kunci utama tabel dasar.

Misalnya, untuk model 19produk , kueri berikut mengambil ProductModelID nilai atribut dan ProductModelName menggunakan value() metode . Alih-alih menggunakan indeks XML utama atau indeks XML sekunder lainnya, indeks PROPERTI dapat memberikan eksekusi yang lebih cepat.

;WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.value('(/PD:ProductDescription/@ProductModelID)[1]', 'int') AS ModelID,
  CatalogDescription.value('(/PD:ProductDescription/@ProductModelName)[1]', 'varchar(30)') AS ModelName
FROM Production.ProductModel
WHERE ProductModelID = 19;

Kecuali untuk perbedaan yang dijelaskan nanti dalam artikel ini, membuat indeks XML padakolom jenis xml mirip dengan membuat indeks pada kolom jenis non-xml. Pernyataan DDL Transact-SQL berikut dapat digunakan untuk membuat dan mengelola indeks XML:

Pemadatan XML

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance.

Mengaktifkan kompresi XML mengubah format penyimpanan fisik data yang terkait dengan jenis data XML ke format biner terkompresi, tetapi tidak mengubah sintaks data XML atau semantik. Perubahan aplikasi tidak diperlukan saat satu atau beberapa tabel diaktifkan untuk pemadatan XML.

Hanya tipe data XML yang dipengaruhi oleh pemadatan XML. Data XML dikompresi dengan Algoritma Pemadatan Xpress. Indeks XML yang ada dikompresi menggunakan pemadatan data. Pemadatan data diaktifkan secara internal untuk indeks XML saat pemadatan XML diaktifkan.

Pemadatan XML dapat diaktifkan berdampingan dengan kompresi data pada tabel yang sama.

Indeks XML tidak mewarisi properti pemadatan tabel. Untuk memadatkan indeks, Anda harus secara eksplisit mengaktifkan kompresi XML pada indeks XML.

Indeks XML sekunder tidak mewarisi properti pemadatan indeks XML utama.

Secara default, pengaturan pemadatan XML untuk indeks XML diatur ke NONAKTIF saat indeks dibuat.

Mendapatkan informasi tentang indeks XML

Entri indeks XML muncul dalam tampilan sys.indexes katalog dengan indeks type3. Kolom nama berisi nama indeks XML.

Indeks XML juga direkam dalam tampilan sys.xml_indexeskatalog . Ini berisi semua kolom sys.indexes dan beberapa kolom tertentu yang berguna untuk indeks XML. Nilai NULL dalam kolom secondary_type menunjukkan indeks XML utama; nilai P, R dan V merupakan singkatan dari indeks XML sekunder PATH, PROPERTY, dan VALUE.

Penggunaan ruang indeks XML dapat ditemukan dalam fungsi bernilai tabel sys.dm_db_index_physical_stats. Ini menyediakan informasi, seperti jumlah halaman data yang ditempati, ukuran baris rata-rata dalam byte, dan jumlah rekaman, untuk semua jenis indeks. Ini juga termasuk indeks XML. Informasi ini tersedia untuk setiap partisi database. Indeks XML menggunakan skema partisi dan fungsi partisi yang sama dari tabel dasar.