PostgreSQL ali MySQL: isti JSON zahteva drugačno indeksiranje

Pri izbiri med PostgreSQL in MySQL za podatke JSON je odločilno, kaj aplikacija išče. PostgreSQL ponuja indeks GIN za iskanje po različnih delih dokumenta jsonb, MySQL pa večvrednostni indeks za elemente izbranega polja JSON. Za pogosto uporabljeno posamezno vrednost imata obe bazi še drugačne možnosti. Isti dokument zato zahteva načrt indeksiranja glede na poizvedbo, ne zgolj glede na obliko shranjenih podatkov.

Ločeno je treba presoditi transakcije: koliko branj, sprememb in sočasnih zahtevkov sestavlja dejansko opravilo aplikacije. Rezultat preizkusa plačilnih prenosov pove nekaj o tem poteku, ne pa o hitrosti iskanja po poljih JSON. Uporaben izbor baze poveže ustrezne indekse z merjenjem celotne obremenitve.

Iskanje po različnih delih dokumenta

Če pogoji segajo v različne ključe in vrednosti, je v PostgreSQL izhodišče splošni indeks GIN nad stolpcem jsonb. Dokumentacija PostgreSQL za jsonb opisuje podporo privzetega razreda jsonb_ops za obstoj ključa, vsebovanost in določene poizvedbe jsonpath. Tak indeks je prilagodljiv, ker hrani indeksne vnose iz celotnega dokumenta; ta obseg je hkrati razlog, da za pogosto uporabljeno ozko pot ni vedno najboljša možnost.

Natančen pogoj WHERE je pomemben. Operator ? preverja obstoj ključa ali elementa na vrhnji ravni vrednosti, na katero je uporabljen. Splošni indeks nad dokumentom lahko podpre pogoj vsebovanosti, kot je iskanje določene oznake v gnezdenem polju tags. Pri pogoju, ki najprej izlušči tags in nato uporabi ?, pa operator ni uporabljen neposredno na indeksiranem stolpcu. Za takšno poizvedbo lahko PostgreSQL uporabi izrazni indeks GIN nad izluščenim poljem.

Ta ožji indeks hrani podatke iz izbrane poti, zato je lahko manjši in hitrejši za iskanje po njej. Druga možnost je razred jsonb_path_ops za indeks nad dokumentom: običajno je manjši od privzetega razreda in podpira vsebovanost ter določene poizvedbe jsonpath, ne podpira pa operatorjev za obstoj ključa. Izbira med razredoma je zato odvisna od operatorjev, ki jih aplikacija res uporablja, ne samo od tega, da so podatki shranjeni kot JSON.

Iskanje elementa v polju JSON

Za poizvedbo »poišči zapise, katerih polje oznak vsebuje določeno oznako« MySQL v InnoDB ponuja večvrednostni indeks. MySQLova dokumentacija za CREATE INDEX določa, da je ta možnost na voljo od različice 8.0.17: izraz CAST(... AS ... ARRAY) izbrano polje pretvori v tipizirano polje, funkcijski indeks na samodejno ustvarjenem navideznem stolpcu pa lahko shrani več vnosov za eno vrstico. Indeks je namenjen pogojem z MEMBER OF(), JSON_CONTAINS() in JSON_OVERLAPS().

Pot in vrsta elementov morata ustrezati podatkom ter pogoju poizvedbe. Če je izbrano polje prazno, zanj v večvrednostnem indeksu ni vnosa; vrednosti JSON null v indeksiranem polju niso dovoljene. Indeks tudi ne zagotavlja razvrščanja rezultatov in ne more biti pokrivni indeks, iz katerega bi baza odgovorila brez dostopa do vrstice. Poizvedba, ki poleg članstva v polju potrebuje še urejanje ali dodatne stolpce, zato zahteva presojo celotnega izvedbenega načrta.

Obe bazi lahko pomagata pri iskanju elementov polja, vendar indeks ne nastane iz istega izraza SQL. PostgreSQL lahko uporabi vsebovanost nad dokumentom ali izrazni GIN nad izluščenim poljem. MySQL indeksira elemente določene poti kot ločene vnose. Pri prenosu iste sheme med bazama je zato treba prenesti tudi namen pogoja in preveriti, ali je njegova nova oblika primerna za izbrani indeks.

Filtriranje po eni vrednosti

Če aplikacija pogosto filtrira ali ureja po eni izluščeni vrednosti, splošno iskanje po dokumentu in članstvo v polju nista pravi merili. V pogojnem primeru z vrednostjo status v dokumentu je pomembno, kako baza dostopa prav do tega izraza. Opis izraznih indeksov PostgreSQL omogoča indeksiranje rezultata izraza, hkrati pa pojasnjuje strošek: ob vstavljanju in ustreznih posodobitvah je treba izraz izračunati ter indeks vzdrževati.

MySQL lahko izluščeno vrednost shrani v ustvarjeni stolpec in ga indeksira. Optimizator lahko tak indeks upošteva tudi, ko poizvedba stolpca ne navede po imenu, vendar se morata izraz in vrsta rezultata ujemati z njegovo definicijo. Pri nizih, pridobljenih iz JSON, MySQLova pravila za indekse ustvarjenih stolpcev posebej obravnavajo JSON_UNQUOTE(), ki odstrani narekovaje iz izluščene vrednosti. Majhna razlika v zapisu ali vrsti primerjane vrednosti lahko zato spremeni možnost uporabe indeksa.

Ta primer pokaže tudi mejo primerjave samo po imenu funkcije. Pogoj za enakost, razpon ali vrstni red potrebuje ustrezno vrsto vrednosti; polje oznak pa potrebuje preverjanje članstva. Če aplikacija izvaja obe vrsti poizvedb nad istim dokumentom, en indeks za JSON še ne pomeni, da sta obe učinkovito pokriti. Dodatni indeksi izboljšajo dostop pri branju, vendar povečajo delo pri spremembah vrstic.

Kaj meri preizkus plačilnega sistema

V preizkusu SQLpipe je PostgreSQL 17.0 pri 64 sočasnih zahtevkih v simulaciji plačilnega sistema, kjer posamezen prenos poseže v šest tabel, po avtorjevem izračunu dosegel za 474 odstotkov večjo prepustnost kot MySQL 9.1.0. Plačilni potek zajema preverjanje uporabnikov in kartic, spremembo stanj računov ter zapis plačila. To je meritev konkretnih različic, nastavitev in kombinacije opravil.

Primerjava je koristna kot opozorilo, da hitrost posameznega branja ni isto kot zmogljivost celotne transakcije pri sočasnem delu. Plačilni preizkus ne meri opisanih pogojev nad dokumenti JSON, zato njegovega odstotka ni mogoče uporabiti kot napoved za aplikacijo, ki pretežno išče oznake ali filtrira po izluščeni vrednosti. Tudi razmerje med branji in zapisi spremeni pomen izbire: indeks, ki pomaga pogostemu iskanju, je treba vzdrževati ob spremembah podatkov.

Meritev na lastni shemi

Odločitev je najzanesljivejša, ko izhaja iz dejanskih oblik poizvedb. Za iskanje po različnih delih dokumenta je smiselno primerjati splošni in ciljni indeks GIN; za članstvo v znanem polju preveriti ustrezni večvrednostni indeks v MySQL; za posamezno vrednost pa primerjati indeksiran izraz z indeksiranim ustvarjenim stolpcem. Pri vsakem pogoju je pomemben izvedbeni načrt: ali baza uporabi predvideni indeks, koliko vrstic mora še pregledati in koliko časa porabi za odgovor.

Meritev naj nato zajame reprezentativne podatke, delež branj in zapisov ter pričakovano sočasnost. Poleg časa poizvedbe štejejo zakasnitev celotnega opravila, prepustnost in strošek vzdrževanja indeksov. Tako se pokaže, ali prednost pri iskanju po JSON ostane pomembna tudi med transakcijami, ki jih bo aplikacija dejansko izvajala.

Preberite tudi:

• Normirani ali navadni s. p.: dejanski stroški lahko obrnejo izračun
• Kickstarter ali Indiegogo: enaka 5-odstotna provizija, različne možnosti uspeha
• Deel ali Remote v Sloveniji: 100 $ razlike še ne odloči izbire