Perjalanan penemuan PostgreSQL

Postgres mempunyai beberapa jenis indeks, dan setiap versi baharu nampaknya menambah beberapa yang baharu. Setiap jenis indeks berguna, tetapi jenis yang hendak digunakan bergantung pada (1) jenis data, kadangkala (2) data asas dalam jadual dan (3) jenis carian yang dilakukan. Dalam kandungan berikut, kami akan memperkenalkan jenis indeks yang boleh anda gunakan dalam Postgres, dan bila anda harus menggunakan jenis indeks yang mana. Sebelum kami bermula, berikut ialah senarai jenis indeks yang akan kami tunjukkan kepada anda:

B-Tree
Indeks Songsang Am (GIN)
Pokok Pencarian Terbalik Umum (GIST)
GiST pembahagian ruang (SP-GiST)
Indeks Julat Blok (BRIN)
Hash
Sekarang mari kita mulakan dengan pengindeksan.

Dalam Postgres, indeks B-Tree ialah indeks yang paling biasa anda gunakan

Jika anda mempunyai ijazah dalam sains komputer, pengindeksan B-Tree mungkin merupakan indeks pertama yang anda pelajari. Indeks B-tree mencipta pokok yang sentiasa mengekalkan keseimbangannya. Apabila ia mencari sesuatu berdasarkan indeks, ia berjalan pada pokok untuk mencari kunci dan mengembalikan data yang anda cari. Menggunakan indeks adalah jauh lebih pantas daripada imbasan berjujukan kerana ia hanya perlu membaca beberapa halaman (apabila anda hanya memulangkan beberapa rekod) berbanding mengimbas beribu-ribu rekod secara berjujukan.

Jika anda menjalankan penyataan CREATE INDEX standard, ia akan mencipta indeks B-tree untuk anda. Indeks B-tree bernilai pada kebanyakan jenis data, seperti teks, nombor dan cap masa. Jika anda baru mula menggunakan indeks dalam pangkalan data anda, dan tidak menggunakan terlalu banyak ciri lanjutan Postgres pada pangkalan data anda, menggunakan indeks B-Tree standard mungkin merupakan pilihan terbaik anda.

Indeks GIN untuk lajur berbilang nilai

Indeks Songsang Umum, biasanya dipanggil GIN, kebanyakannya sesuai untuk jenis data apabila satu lajur mengandungi berbilang nilai.

Menurut dokumentasi Postgres:

"GIN direka bentuk untuk mengendalikan situasi di mana entri yang diindeks ialah nilai kompaun dan pertanyaan yang diproses oleh indeks perlu mencari nilai yang berlaku dalam entri kompaun. Contohnya, entri ini mungkin dokumen dan pertanyaan boleh mencari aksara tertentu yang terkandung dalam dokumen .”

Jenis data yang paling biasa termasuk dalam julat ini ialah:

hStore
Susunan
Julat
JSONB
Salah satu perkara yang paling memuaskan tentang indeks GIN ialah keupayaan mereka untuk memahami data yang disimpan dalam nilai komposit. Walau bagaimanapun, kerana indeks GIN memerlukan pengetahuan khusus tentang struktur data setiap jenis individu yang ditambahkan, tidak semua jenis data disokong oleh indeks GIN.

Indeks GiST untuk baris dengan nilai bertindih

Indeks Generalized Inverted Seach Tree (GiST) kebanyakannya sesuai apabila data anda bertindih dengan baris data lain dalam lajur yang sama. Penggunaan terbaik indeks GiST adalah jika anda mengisytiharkan jenis data geometri dan anda ingin mengetahui sama ada dua poligon mengandungi beberapa titik. Dalam satu kes titik tertentu mungkin terkandung dalam kotak, manakala pada masa yang sama, titik lain hanya wujud dalam poligon. Jenis data biasa yang diindeks menggunakan GiST ialah:

Jenis geometri
Jenis teks yang memerlukan carian teks penuh
Terdapat banyak had tetap pada saiz indeks GiST, jika tidak, indeks GiST mungkin menjadi sangat besar. Pada kos ini, indeks GiST adalah lossy (tidak tepat).

Mengikut dokumentasi rasmi:

“Indeks GIST adalah lossy, yang bermaksud bahawa indeks mungkin menghasilkan padanan palsu, jadi adalah perlu untuk menyemak baris jadual sebenar untuk menghapuskan padanan palsu (PostgreSQL akan melakukan tindakan ini secara automatik apabila perlu)”

Ini tidak bermakna anda akan mendapat keputusan palsu, ini hanya bermakna Postgres melakukan sedikit kerja tambahan untuk menapis hasil palsu ini sebelum ia mengembalikan data kepada anda.

Petua istimewa: Indeks GIN dan GiST selalunya boleh digunakan pada jenis data yang sama. Biasanya seseorang mempunyai prestasi yang baik tetapi mengambil banyak ruang cakera, dan sebaliknya. Apabila bercakap tentang GIN lwn. GiST, tiada penyelesaian yang sesuai untuk semua yang akan berfungsi dalam setiap situasi, tetapi peraturan di atas harus digunakan untuk situasi yang paling biasa.

Indeks SP-GIST untuk data yang lebih besar

Indeks GiST (SP-GiST) pembahagian ruang menggunakan pokok pembahagian ruang daripada Penyelidikan Purdue. Indeks SP-GiST sering digunakan apabila data anda mempunyai faktor pengelompokan semula jadi dan bukan pokok yang seimbang. Nombor telefon ialah contoh yang bagus (sekurang-kurangnya nombor telefon AS). Mereka mempunyai format berikut:

3 digit kod kawasan
Nombor awalan 3 digit (berkaitan dengan pertukaran telefon lama)
Nombor baris 4 digit
Ini bermakna terdapat faktor pengelompokan semula jadi pada tiga digit pertama set pertama, diikuti dengan set kedua tiga digit, dan kemudian nombor diagihkan sama rata. Walau bagaimanapun, dalam sesetengah kod kawasan nombor telefon, terdapat keadaan tepu yang lebih tinggi daripada yang lain. Hasilnya boleh menjadi pokok yang sangat tidak seimbang. Oleh kerana terdapat faktor pengagregatan semula jadi di hadapan dan data tidak diagihkan secara sama rata, data seperti nombor telefon mungkin merupakan kes yang baik untuk SP-GiST.

Indeks BRIN untuk data yang lebih besar

Indeks Julat Sekatan (BRIN) memfokuskan pada beberapa situasi seperti SP-GiST, ia paling sesuai digunakan apabila data mempunyai susunan semula jadi dan saiz data selalunya besar. Jika anda mempunyai satu bilion rekod dalam susunan kronologi, BRIN mungkin berguna. Jika anda menanyakan set besar data yang dikumpulkan secara semula jadi, seperti beberapa kod zip, BRIN boleh membantu anda memastikan bahawa kod zip yang serupa disimpan di lokasi yang rapat pada cakera.

Apabila anda mempunyai pangkalan data yang sangat besar diisih mengikut tarikh atau poskod, indeks BRIN membolehkan anda melangkau atau mengecualikan beberapa data yang tidak diperlukan dengan cepat. Selain itu, indeks BRIN agak kecil berbanding dengan saiz data keseluruhan, jadi apabila anda mempunyai set data yang besar, indeks BRIN boleh berprestasi lebih baik.

Hash index, akhirnya tidak lagi takut terhempas

Indeks hash telah wujud di Postgres selama bertahun-tahun, namun, sehingga Postgres 10 dikeluarkan, terdapat kaveat besar mengenai penggunaannya, ia tidak dilog WAL. Ini bermakna jika pelayan anda ranap dan anda tidak boleh gagal untuk bersedia atau memulihkan daripada arkib menggunakan cth. wal-g, maka anda akan kehilangan indeks itu sehingga anda membinanya semula. Dengan keluaran Postgres 10, mereka kini dilog WAL, jadi anda boleh mempertimbangkan untuk menggunakannya semula, tetapi persoalan sebenar ialah, adakah anda

Indeks cincang kadangkala memberikan carian yang lebih pantas daripada indeks B-Tree, dan juga pantas dibuat. Masalah terbesar ialah ia terhad kepada operasi perbandingan "kesamaan" sahaja, jadi anda hanya boleh menggunakannya untuk carian padanan tepat. Ini menjadikan indeks cincang kurang fleksibel daripada indeks B-Tree yang biasa digunakan, dan anda tidak seharusnya menganggapnya sebagai pengganti, tetapi sebagai indeks untuk kes khas.

Yang manakah anda patut gunakan?

Banyak yang kami perkenalkan, kalau takut sikit, biasalah. Jika anda mengetahui perkara ini sebelum ini, CREATE INDEX akan sentiasa mencipta indeks untuk anda menggunakan B-Tree, dan berita baiknya ialah Postgres berprestasi sangat baik atau sangat baik untuk kebanyakan pangkalan data. :) Jika anda sedang mempertimbangkan untuk menggunakan lebih banyak ciri Postgres, berikut ialah helaian cheat apabila menggunakan jenis indeks Postgres yang lain:

B-Tree - sesuai untuk kebanyakan jenis data dan pertanyaan
GIN - untuk JSONB/hstore/arrays
GiST - untuk carian teks penuh dan jenis data geometri
SP-GiST - sesuai untuk set data yang besar dengan faktor pengagregatan semula jadi tetapi pengedaran tidak sekata
BRIN - sesuai untuk set data yang sangat besar dengan susunan berurutan
Hash - baik untuk operasi kesamarataan, tetapi biasanya indeks B-Tree masih diperlukan.
Jika anda mempunyai sebarang soalan atau maklum balas tentang artikel ini, jangan ragu untuk menyertai saluran slack kami.

Atas ialah kandungan terperinci Perjalanan penemuan PostgreSQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!