Bagaimana untuk Mengindeks Tatasusunan JSON dengan Cekap dalam PostgreSQL untuk Mendapatkan Elemen Lebih Pantas?

Indeks tatasusunan JSON dengan cekap dalam PostgreSQL untuk mendapatkan semula elemen yang lebih pantas

Mengenal pasti dan mendapatkan semula elemen dengan pantas dalam tatasusunan JSON dalam jadual PostgreSQL boleh mencabar. Untuk prestasi optimum, adalah penting untuk memahami teknologi pengindeksan yang sesuai untuk keperluan data dan pertanyaan khusus anda.

Menggunakan JSONB dalam PostgreSQL 9.4

PostgreSQL 9.4 memperkenalkan jsonb jenis data JSON binari, yang sangat meningkatkan kemungkinan pengindeksan. Gunakan jsonb untuk mencipta indeks GIN secara langsung pada tatasusunan JSON.

<code class="language-sql">CREATE TABLE tracks (id serial, artists jsonb);
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);</code>

Ini membolehkan pertanyaan yang boleh menggunakan indeks GIN, contohnya:

<code class="language-sql">SELECT * FROM tracks WHERE artists @> '["The Dirty Heads"]';</code>

di mana @> mewakili pengendali "mengandungi" jsonb.

Sebagai alternatif, anda boleh menggunakan kelas operator GIN jsonb_path_ops (bukan lalai, biasanya lebih kecil dan lebih pantas):

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists jsonb_path_ops);</code>

Optimumkan untuk nilai nama unik

Jika lajur artists anda hanya mengandungi tatasusunan nilai yang dinamakan secara unik, adalah lebih cekap untuk menyimpan nilai sebagai primitif teks JSON, bukannya objek. Ini menghapuskan keperluan untuk kunci berlebihan.

<code class="language-sql">CREATE TABLE tracks (id serial, artistnames jsonb);
INSERT INTO tracks VALUES (2, '["The Dirty Heads", "Louis Richards"]');
CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames);</code>

Contoh pertanyaan:

<code class="language-sql">SELECT * FROM tracks WHERE artistnames ? 'The Dirty Heads';
SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"'::jsonb;</code>

Mengendalikan json dalam PostgreSQL 9.3

Untuk versi PostgreSQL sebelum 9.4, fungsi invarian dan indeks GIN boleh digunakan.

Buat fungsi:

<code class="language-sql">CREATE OR REPLACE FUNCTION json2arr(_j json, _key text)
RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT elem->>_key FROM json_array_elements(_j) elem)';</code>

Kemudian buat indeks fungsi:

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (json2arr(artists, 'name'));</code>

Gunakan pertanyaan yang sepadan dengan ungkapan indeks:

<code class="language-sql">SELECT * FROM tracks WHERE '{"The Dirty Heads"}'::text[]</code>

Sentiasa pastikan bahawa fungsi yang digunakan dalam indeks tidak boleh diubah untuk membolehkan pengindeksan fungsi. Pengendali utama...

Artikel itu telah menulis semula teks asal, menggunakan bahasa yang lebih ringkas dan melaraskan beberapa ayat, tetapi mengekalkan kandungan teras teks asal dan lokasi gambar.

Atas ialah kandungan terperinci Bagaimana untuk Mengindeks Tatasusunan JSON dengan Cekap dalam PostgreSQL untuk Mendapatkan Elemen Lebih Pantas?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!