Milloin indeksit haittaavat: kirjoitukset ja selektiivisyys
Kirjoitusten lisääntyminen ja syy siihen, miksi matalan selektiivisyyden sarakkeen indeksi on hyödytön.
Milloin indeksit haittaavat: kirjoitukset ja selektiivisyys on ilmainen Valmistautuminen ohjelmointihaastatteluihin-oppitunti CoddyKitissä. Tämä on oppitunti 4/4. Voit lukea koko oppitunnin alta ilmaiseksi ja harjoitella sen jälkeen käytännössä selaimessa sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla. Oppitunti kuuluu Valmistautuminen ohjelmointihaastatteluihin-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. Valmistautuminen ohjelmointihaastatteluihin-kurssilla on yhteensä 4 oppituntia.
Kysymyksen taustalla oleva kysymys
Kun kolmessa aiemmassa oppitunnissa on käsitelty indeksien hyötyjä, haastattelijat kääntävät asian toisin päin: ”Miksi ette vain indeksoi jokaista saraketta?” Vahva hakija selittää, että indekseillä on todellisia kustannuksia sekä kirjoituksille että välimuistille ja tallennustilalle ja että kyselysuunnittelija ei välttämättä koskaan käytä joitakin indeksejä.
Tässä oppitunnissa käsitellään kahta pääsyytä, joiden vuoksi indeksi voi haitata suorituskykyä: kirjoitusvahvistus ja vähäinen selektiivisyys.
Jokainen indeksi hidastaa kirjoituksia
Indeksi on pidettävä synkronoituna taulun kanssa. Jokaisen INSERT-, DELETE- ja indeksoituun sarakkeeseen kohdistuvan UPDATE-toiminnon on päivitettävä myös indeksirakennetta. Tätä kutsutaan kirjoitusvahvistukseksi: yhdestä rivimuutoksesta seuraa yksi taulun kirjoitus sekä yksi kirjoitus jokaiseen muutoksen kohteena olevaan indeksiin.
Kahdeksan indeksiä sisältävän taulun kirjoitustyö on suunnilleen yhdeksänkertainen indeksoimattomaan tauluun verrattuna. Kirjoituspainotteisissa tai suuren läpimenon tauluissa tämä on merkittävä kustannus.
Esimerkki: kirjoitusten kustannus
Kuvitelkaa tapahtumataulu, johon syötetään tuhansia rivejä sekunnissa. Jokainen ylimääräinen indeksi lisää jokaisen lisäyksen työtä: indeksisivuja jaetaan, lehtitasoja päivitetään ja välimuistista kilpaillaan.
Vain lisäämiseen tarkoitetussa, kirjoituspainotteisessa taulussa oikea ratkaisu on usein harvat indeksit tai ei indeksejä lainkaan perusavaimen lisäksi sekä raskaiden lukujen suorittaminen sen sijaan replikassa tai tietovarastossa.
-- Each of these indexes adds cost to EVERY insert below
CREATE INDEX ix_events_user ON events (user_id);
CREATE INDEX ix_events_type ON events (event_type);
CREATE INDEX ix_events_ts ON events (created_at);
INSERT INTO events (user_id, event_type, created_at)
VALUES (42, 'click', now()); -- now updates table + 3 indexesMitä selektiivisyys tarkoittaa
Selektiivisyys kertoo, kuinka hyvin sarake erottaa rivit toisistaan eli kuinka suureen osaan riveistä tyypillinen arvo täsmää. Suuri selektiivisyys tarkoittaa, että kutakin arvoa vastaa vain muutama rivi, kuten sähköpostiosoitetta tai UUID:tä. Vähäinen selektiivisyys tarkoittaa, että kutakin arvoa vastaa monta riviä, kuten totuusarvoa tai kolmen vaihtoehdon tilaa.
Indekseistä on eniten hyötyä suuren selektiivisyyden sarakkeissa, joissa haku karsii lähes kaikki rivit. Vähäisen selektiivisyyden sarakkeissa näin ei usein ole.
Miksi vähän selektiivinen indeksi on hyödytön
Oletetaan, että is_active on tosi 90 prosentilla käyttäjistä. Indeksihaku palauttaisi 90 prosenttia taulusta, ja näin suurelle rivimäärälle moottori tekisi heap fetchin jokaiselle riville. Se olisi hitaampaa kuin taulun peräkkäinen lukeminen yhtenä läpikäyntinä.
Siksi kyselysuunnittelija jättää indeksin oikeaoppisesti käyttämättä ja suorittaa peräkkäisen skannauksen. Indeksi aiheuttaa tällöin vain kirjoituskustannuksia ja vie tallennustilaa tuottamatta lainkaan hyötyä lukuihin.
-- 90% of rows match: the planner will likely skip this index
CREATE INDEX ix_users_active ON users (is_active);
SELECT * FROM users WHERE is_active = true;Suuntaa-antava raja
Hyödyllinen nyrkkisääntö, jonka voitte mainita ääneen: kun predikaatti täsmää yli noin 5–20 prosenttiin taulun riveistä, peräkkäinen skannaus on yleensä indeksiskannausta nopeampi, koska satunnaiset heap fetchit maksavat enemmän kuin sivujen lukeminen peräkkäin.
Tarkka raja riippuu rivien koosta, välimuistista ja tallennuslaitteen nopeudesta. Siksi kyselysuunnittelija käyttää päätöksessään tilastoja kiinteän luvun sijaan.
Osittaiset indeksit apuun
Jos kyselyissä käytetään aina vain vinoutuneen sarakkeen harvinaisia arvoja, osittainen indeksi (Postgres) indeksoi vain kyseiset rivit. Se on pieni, hyvin selektiivinen ja edullinen ylläpitää.
Jos yksi prosentti tilauksista on pending-tilassa ja juuri niitä kysellään jatkuvasti, indeksoikaa vain ne. Indeksi pysyy pienenä, ja kyselysuunnittelija käyttää sitä mielellään.
-- Index only the rare, frequently-queried rows
CREATE INDEX ix_orders_pending
ON orders (created_at)
WHERE status = 'pending';Vanhentuneet tilastot johtavat kyselysuunnittelijaa harhaan
Optimoija päättää indeksi- ja skannauksen välillä saraketilastojen perusteella. Jos tilastot ovat vanhentuneet massalatauksen tai suuren päivityksen jälkeen, optimoija voi arvioida selektiivisyyden väärin ja valita väärän suoritusohjelman.
Kun haastattelija sanoo: ”Indeksi on olemassa, mutta sitä ei käytetä”, erinomaiseen vastaukseen kuuluu tilastojen päivittäminen komennolla ANALYZE ennen kuin indeksiä itseään syytetään.
ANALYZE orders; -- refresh planner statisticsMuita tapoja, joilla indeksit haittaavat
Täydentäkää vastausta vähemmän tunnetuilla kustannuksilla:
- Tallennustila ja välimuisti: indeksit vievät levytilaa ja kilpailevat muistista syrjäyttäen hyödyllisiä datasivuja.
- Tarpeettomat ja päällekkäiset indeksit: niitä ylläpidetään, mutta niitä ei koskaan valita käyttöön.
- Paisuminen: raskaat päivitykset pirstovat B-puita, ja ne on korjattava komennolla
REINDEX. - Optimoijan hämmennys: liian monet samankaltaiset indeksit hidastavat suunnittelua ja tekevät siitä arvaamattomampaa.
Käyttämättömien indeksien löytäminen
Perustellaksenne käytännön siivouksen mainitkaa, että Postgres seuraa indeksien käyttöä. Indeksit, joiden idx_scan = 0, ovat poistoehdokkaita: ne kuluttavat kirjoituskapasiteettia ja tilaa, mutta eivät koskaan palvele yhtäkään lukua.
SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;Miten asian voi ilmaista työhaastattelussa
Täydellinen ja tasapainoinen yhteenveto:
”Indeksit aiheuttavat kirjoitusvahvistusta: jokainen lisäys, päivitys ja poisto ylläpitää niitä. Lisäksi ne kuormittavat tallennustilaa ja välimuistia. Niistä on hyötyä vain suuren selektiivisyyden predikaateissa; sarakkeessa, johon suurin osa riveistä täsmää, kyselysuunnittelija suosii oikeutetusti peräkkäistä skannausta, joten indeksi on pelkkä ylimääräinen kustannus. Vinoutuneille sarakkeille käytän osittaista indeksiä, pidän tilastot ajan tasalla ANALYZE-komennolla ja poistan käyttämättömät indeksit.”
Pikatarkistus
Päättäkää, minkä indeksin kustannukset ovat todennäköisimmin hyötyä suuremmat.
Kertaus: milloin indeksit haittaavat
Tärkeimmät kohdat:
- Jokainen indeksi lisää kirjoitusvahvistusta sekä tallennustila- ja välimuistikustannuksia.
- Indeksit auttavat suuren selektiivisyyden sarakkeissa; vähäisen selektiivisyyden sarakkeissa kyselysuunnittelija suosii peräkkäistä skannausta.
- Kun osumia on yli noin 5–20 prosentissa riveistä, skannaus voittaa yleensä.
- Käyttäkää osittaista indeksiä vinoutuneille sarakkeille, joista kysellään vain harvinaisia arvoja.
- Pidäkää tilastot ajan tasalla komennolla
ANALYZEja poistakaa käyttämättömät indeksit (idx_scan = 0).
Tähän päättyy indeksointistrategian kurssi: rakentakaa indeksit sinne, missä ne ansaitsevat ylläpitokustannuksensa, ja todistakaa hyöty suoritusohjelmalla.
Opi Valmistautuminen ohjelmointihaastatteluihin tekoälytuutorin avulla — ilmaiseksi
Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.
- Kurssit
- 90
- Oppitunnit
- 360
Usein kysytyt kysymykset
Onko oppitunti ”Milloin indeksit haittaavat: kirjoitukset ja selektiivisyys” ilmainen?
Kyllä – oppitunnin ”Milloin indeksit haittaavat: kirjoitukset ja selektiivisyys” koko tekstin voi lukea täällä verkossa ilmaiseksi. Jos haluat harjoitella interaktiivisesti sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla sekä avata koko Valmistautuminen ohjelmointihaastatteluihin-kurssin, päivitä CoddyKit PROhon. Valmistautuminen ohjelmointihaastatteluihin-kurssilla on yhteensä 4 oppituntia.
Mitä opin oppitunnilla ”Milloin indeksit haittaavat: kirjoitukset ja selektiivisyys”?
Kirjoitusten lisääntyminen ja syy siihen, miksi matalan selektiivisyyden sarakkeen indeksi on hyödytön. Harjoittelet Valmistautuminen ohjelmointihaastatteluihin-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.
Tarvitsenko kokemusta aloittaakseni Valmistautuminen ohjelmointihaastatteluihin-opiskelun?
Aiempi kokemus ei ole tarpeen. CoddyKitin Valmistautuminen ohjelmointihaastatteluihin-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 4/4.
Kuinka kauan ”Milloin indeksit haittaavat: kirjoitukset ja selektiivisyys”-oppitunnin suorittaminen kestää?
Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.
Voinko kirjoittaa ja suorittaa koodia tällä Valmistautuminen ohjelmointihaastatteluihin-oppitunnilla?
Kyllä. Jokainen Valmistautuminen ohjelmointihaastatteluihin-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.
Kaikki tämän kurssin oppitunnit
- B-puu-indeksit ja niiden hyödyt
- Yhdistelmäindeksin sarakejärjestys
- Peittävät indeksit ja Index-Only Scanit
- Milloin indeksit haittaavat: kirjoitukset ja selektiivisyys