Kai kalbame apie PostgreSQL ir užklausų optimizavimą, dauguma žmonių iš karto galvoja apie B-Tree indeksus, GIN, GiST ir pan. Tačiau BRIN indeksai dažnai lieka šešėlyje, nepaisant to, kad kai kuriais atvejais jie yra nepaprastai naudingi. Jie gali žymiai padidinti našumą, ypač dirbant su didelėmis lentelėmis ir ribota vieta diske. Šiandien paaiškinsiu, kaip veikia BRIN ir kada jis šviečia.

Kas yra BRIN?

BRIN reiškia Block Range Index. Nors įprasti indeksai siekia saugoti išsamią informaciją apie kiekvieną eilutę, BRIN laikosi kitokio požiūrio. Jis saugo puslapių grupių, vadinamų „zonomis“, suvestinę informaciją, o ne indeksuoja kiekvieną eilutę atskirai.

Kad būtų paprasčiau, BRIN padalija lentelę į blokus (kiekviena zona pagal numatytuosius nustatymus paprastai apima 32 puslapius) ir toje zonoje išsaugo minimalias ir didžiausias stulpelio reikšmes (arba kitas apibendrintas charakteristikas, priklausomai nuo operatoriaus klasės). Kai pateikiate duomenų užklausą, BRIN nustato atitinkamas zonas, o likusias praleidžia.

Ar tai 100% tikslus? Ne visai – vis tiek turėsite nuskaityti eilutes pasirinktose zonose. Tačiau šis filtravimas yra žymiai greitesnis nei visos lentelės nuskaitymas.

Kada BRIN yra geras pasirinkimas?

Štai keli scenarijai, kuriuose BRIN puikiai tinka:

  1. Didžiuliai stalai
    Kai dirbate su lenteles, kuriose yra milijonai ar milijardai eilučių, tradiciniai B-Tree indeksai gali išsipūsti ir užimti daug vietos. Kita vertus, BRIN išlieka lengvas ir kompaktiškas.

  2. Natūralių duomenų koreliacija
    Jei jūsų duomenys turi natūralią tvarką, BRIN gali būti ypač efektyvus. Pavyzdžiui, jei turite order_date stulpelyje an orders lentelę, tikėtina, kad senesni užsakymai saugomi lentelės pradžioje, o naujesni – pabaigoje. Užklausoms, nukreiptoms į konkretų mėnesį, BRIN greitai susiaurina atitinkamas zonas.

  3. Vietos diske taupymas
    BRIN indeksai yra nepaprastai kompaktiški. Užuot saugoję visas atskiras reikšmes, jie naudoja apibendrintus metaduomenis kiekvienai zonai, o tai sumažina saugojimo reikalavimus.

BRIN apribojimai

Kaip ir bet kuris įrankis, BRIN turi kompromisų:

  • Netikslumas: BRIN tiksliai nenustato duomenų vietų. Tai tik nurodo zonas, kuriose duomenys gali būti, todėl tose zonose reikia toliau filtruoti.
  • Išsklaidyti duomenys: Jei stulpelių reikšmės yra išsklaidytos atsitiktinai, BRIN negalės veiksmingai pašalinti nesusijusių zonų, todėl tai bus mažiau naudinga.

BRIN indekso sukūrimas

Štai trumpas pavyzdys, kaip sukurti BRIN indeksą:

CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date)
WITH (pages_per_range = 32);

The pages_per_range parametras nustato puslapių skaičių kiekvienoje zonoje. Numatytasis yra 128 puslapiai, bet galite jį koreguoti. Mažesnė reikšmė padidina indekso tikslumą, bet taip pat padidina indekso dydį. Eksperimentuodami su šiuo parametru galite rasti geriausią duomenų balansą.

Suvestinių automatizavimas

Galite naudoti plėtinius, pvz autosummarize automatizuoti suvestinių duomenų atnaujinimą, nebereikia skambinti rankiniu būdu brin_summarize_new_values.

BRIN operatoriaus klasės: Minmax ir įtraukimas

BRIN naudoja operatorių klases, kad nustatytų, kaip apibendrinti duomenis. Du pagrindiniai metodai yra šie:

  • Minmax klasės: Išsaugokite minimalias ir didžiausias vertes kiekvienoje zonoje. Idealiai tinka užsakytiems tipams, pvz., skaičiams, datoms ar eilutėms.
  • Įtraukimo klasės: Tvarkykite sudėtingesnius tipus, pvz., geometriją, IP tinklus ar diapazonus. Šiose klasėse saugomi „masiniai“ atvaizdai, tokie kaip ribojantys langeliai arba diapazonų sąjungos, leidžiančios atlikti sudėtingų duomenų struktūrų operacijas.

Kodėl įtraukimo klasės yra svarbios

Įtraukimo klasės gali efektyviai apdoroti sudėtingus duomenis. Pavyzdžiui, jie gali identifikuoti zonas, kuriose yra objektų, kertančių konkrečią sritį, todėl jos gali būti naudingos naudojant specializuotus naudojimo atvejus, pvz., erdvinius duomenis.


BRIN indeksai yra galinga, tačiau nepakankamai naudojama PostgreSQL funkcija. Nors jie netinka kiekvienai situacijai, jie gali žymiai pagerinti našumą dirbant su dideliais duomenų rinkiniais ir natūraliai sutvarkytais duomenimis. Eksperimentuokite su BRIN, kad atskleistumėte jo potencialą ir optimizuotumėte duomenų bazės užklausas.

Vardas Duomenų tipas Operatoriai
abstime_minmax_ops susilaikymas <<= = >= >
int8_minmax_ops bigint <<= = >= >
bit_minmax_ops šiek tiek <<= = >= >
varbit_minmax_ops šiek tiek įvairus <<= = >= >
box_inclusion_ops dėžutė << &< && &> >> ~= @> <@
bytea_minmax_ops baitas <<= = >= >
bpchar_minmax_ops charakteris < <= = >= >
char_minmax_ops „char” <<= = >= >
data_minmax_ops data <<= = >= >
float8_minmax_ops dvigubas tikslumas <<= = >= >
inet_minmax_ops inet <<= = >= >
network_inclusion_ops inet && >>= <<= = >> <<
int4_minmax_ops sveikasis skaičius <<= = >= >
interval_minmax_ops intervalas <<= = >= >
macaddr_minmax_ops macaddr <<= = >= >
name_minmax_ops pavadinimas <<= = >= >
numeric_minmax_ops skaitinis < <= = >= >
pg_lsn_minmax_ops pg_lsn <<= = >= >
oid_minmax_ops oid <<= = >= >
range_inclusion_ops bet kokio tipo diapazonas << &< && &> >> @> <@ -
float4_minmax_ops tikras < <= = >= >
reltime_minmax_ops retime <<= = >= >
int2_minmax_ops smallint <<= = >= >
text_minmax_ops tekstą <<= = >= >
tid_minmax_ops tid < <= = >= >
timestamp_minmax_ops laiko žyma be laiko juostos <<= = >= >
timestampz_minmax_ops laiko žyma su laiko juosta <<= = >= >
time_minmax_ops laikas be laiko juostos < <= = >= >
timetz_minmax_ops laikas su laiko juosta <<= = >= >
uuid_minmax_ops uuid < <= = >= >

Daugeliui tipų yra minmax operacijos, o egzotiškesnėms struktūroms – įtraukimo operacijos. Tai yra, galite indeksuoti ne tik paprastus skaičius ir eilutes, bet ir sudėtingus tipus. Pavyzdžiui, box_inclusion_ops leis ieškoti objektų tam tikroje geografinėje vietovėje, greitai iškirsdamas zonas, kuriose nėra tinkamų objektų.

Optimizavimo pavyzdys

Tarkime, kad yra užsakymų lentelė su daugybe eilučių:

CREATE TABLE orders (
    id            BIGSERIAL PRIMARY KEY,
    order_date    DATE NOT NULL,
    customer_id   BIGINT NOT NULL,
    total_amount  NUMERIC(10, 2) NOT NULL
);

Duomenų krūvos įterpimas:

INSERT INTO orders (order_date, customer_id, total_amount)
SELECT
    (DATE '2023-01-01' + (RANDOM()*365)::INT),
    (RANDOM()*1000000)::BIGINT,
    (RANDOM()*1000)::NUMERIC(10,2)
FROM generate_series(1,10000000) g;

Dabar sukuriame BRIN indeksą:

CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date date_minmax_ops)
WITH (pages_per_range = 64);

date_minmax_ops sako: mes saugosime minimalią ir maksimalią datą kiekvienai 64 puslapių zonai.

Kai klausiate:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date BETWEEN '2023-06-01' AND '2023-06-15';

Planas parodys, kad PostgreSQL pirmiausia patikrins BRIN indeksą, nustatys, kuriose zonose gali būti norimos datos, o likusias dalis praleis. Jei duomenys apytiksliai surūšiuoti pagal datą (pvz., kaip įterpti), papildomų zonų skaičius bus sumažintas.

Parametrų subtilybės

pages_per_range

  • Mažesnės vertybės: Pateikite tikslesnį filtravimą (kiekviena zona yra mažesnė, todėl mažiau šiukšlių), bet padidinkite indekso dydį.
  • Didesnės vertės: Sumažinkite indekso dydį, bet pabloginkite tikslumą.
  • Rekomendacija: patikrinkite tikrus duomenis, kad rastumėte optimalų balansą.

autovacuum ir brin_summarize_new_values

Individualizuotos operatoriaus klasės

Jei jokia standartinė operatoriaus klasė neatitinka jūsų poreikių, galite parašyti savo. Tai pažangesnis metodas, reikalaujantis:

  • Įdiekite C funkcijas, kad apibūdintumėte, kaip:
    • Suvestinės vertės,
    • Sujungti zonas ir
    • Patikrinkite sankryžas su užklausomis.
  • Tai leidžia indeksuoti unikalias ar egzotiškas duomenų struktūras, tačiau tai sudėtinga, labai techninė užduotis.

Išvada

BRIN indeksai nepakeis B-Tree indeksų visur. Tačiau tais atvejais, kai duomenys yra sutvarkyti arba turi natūralią koreliaciją su jų fizine vieta, BRIN gali užtikrinti nuostabų našumą su minimaliu indekso dydžiu.

Jei tekste radote klaidą, siųskite pranešimą autoriui pažymėdami klaidą ir paspausdami Ctrl-Enter.



Source link

By admin

Draugai: - Marketingo paslaugos - Teisinės konsultacijos - Skaidrių skenavimas - Fotofilmų kūrimas - Karščiausios naujienos - Ultragarsinis tyrimas - Saulius Narbutas - Įvaizdžio kūrimas - Veidoskaita - Nuotekų valymo įrenginiai -  Padelio treniruotės - Pranešimai spaudai -