Peittävät indeksit ja pelkät indeksiskannaukset
Tutustu siihen, miten peittävät indeksit voivat poistaa taulun käytön tarpeen ja mahdollistaa erittäin tehokkaat pelkät indeksiskannaukset.
Peittävät indeksit ja pelkät indeksiskannaukset on ilmainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunti CoddyKitissä. Tämä on oppitunti 3/4. Voit lukea tästä oppimispolusta kokonaan mitkä tahansa 3 oppituntia ilmaiseksi — sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä käytännön harjoittelun sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. Oppitunti kuuluu PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. PostgreSQL:n suorituskyky ja kyselyjen optimointi-kurssilla on yhteensä 4 oppituntia.
Mitä kattavat indeksit ovat?
Tervetuloa kattavia indeksejä ja vain indeksiä käyttäviä tarkistuksia käsittelevälle oppitunnille! Näillä edistyneillä indeksointitekniikoilla voitte parantaa PostgreSQL-kyselyiden suorituskykyä huomattavasti.
Tavallinen indeksi auttaa PostgreSQL:ää löytämään rivit nopeasti tiettyjen sarakearvojen perusteella. Ajatelkaa sitä kirjan hakemistona: se kertoo, mille sivulle tiettyä aihetta varten pitää siirtyä.
Taulun käytön kustannus
Kun käytätte tavallista indeksiä, PostgreSQL etsii ensin indeksistä rivin sijainnin (sen tuple ID -tunnisteen tai CTID-arvon). Sen jälkeen sen on siirryttävä varsinaiseen tauluun hakemaan kyseisen rivin muut tiedot.
Tämä ylimääräinen vaihe, jota kutsutaan tauluhaun tai keon haun nimellä, voi olla hidas etenkin silloin, kun rivejä on paljon tai taulun tiedot eivät ole muistissa.
Kaiken tarvittavan indeksointi
Kattava indeksi sisältää kaikki kyselyn suorittamiseen tarvittavat sarakkeet, ei vain WHERE-lausekkeen sarakkeita. Tällöin PostgreSQL voi vastata kyselyyn lukemalla vain indeksiä koskematta lainkaan päätaulun tietoihin.
Indeksi kattaa kyselyn tietotarpeet kokonaan.
Kattavan indeksin rakentaminen
Luo kattava indeksi sisällyttämällä siihen kaikki sarakkeet, joita kysely saattaa palauttaa tai joiden perusteella se suodattaa tietoja. PostgreSQL tukee muiden kuin avainsarakkeiden sisällyttämistä INCLUDE-lausekkeella. Tällöin sarakkeet tallennetaan indeksiin, mutta niistä ei tehdä ensisijaisen lajitteluavaimen osia.
Luodaan seuraavaksi taulu ja kattava indeksi.
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
category VARCHAR(50)
);
INSERT INTO products (name, price, category) VALUES
('Laptop', 1200.00, 'Electronics'),
('Keyboard', 75.00, 'Accessories'),
('Mouse', 25.00, 'Accessories'),
('Monitor', 300.00, 'Electronics');
-- Covering index for queries on category that also select name and price
CREATE INDEX idx_products_category_cover_name_price
ON products (category) INCLUDE (name, price);Vain indeksiä käyttävien tarkistusten esittely
Kun kysely voidaan suorittaa kokonaan kattavan indeksin avulla, PostgreSQL tekee Index-Only Scan -skannauksen. Se on erittäin tehokas toiminto, koska tietokantamoottorin ei tarvitse lukea yhtään tietolohkoa päätaulusta eli niin sanotusta heapista.
Tilanne on kuin löytäisitte kaikki tarvitsemanne tiedot suoraan kirjan hakemistosta avaamatta lainkaan varsinaisia lukuja.
MVCC ja Index-Only Scan -skannaukset
PostgreSQL käyttää MVCC-tekniikkaa (Multi-Version Concurrency Control). Jotta Index-Only Scan voidaan suorittaa, PostgreSQL:n on varmistettava, että indeksistä löytyvät riviversiot ovat nykyisen transaktion näkyvissä.
Se tarkistaa tämän taulun visibility map -kartasta. Kyseessä on erityinen tietorakenne, joka seuraa, mitkä taulun sivut sisältävät ainoastaan niin sanottuja all-visible-monikkoja. Jos sivu on merkitty all-visible-tilaan, rivien näkyvyyttä ei tarvitse tarkistaa heapista.
Vahvistaminen EXPLAIN-komennolla
Voitte tarkistaa, käyttääkö PostgreSQL Index-Only Scan -skannausta, tarkastelemalla kyselysuunnitelmaa komennolla EXPLAIN ANALYZE. Etsikää tulosteesta kohta Index Only Scan.
Kokeillaan kyselyä, jonka kattavan indeksin pitäisi pystyä käsittelemään.
EXPLAIN ANALYZE
SELECT name, price
FROM products
WHERE category = 'Electronics';Hyödyt: nopeus ja tehokkuus
Index-Only Scan -skannaukset tarjoavat useita keskeisiä etuja:
- Vähemmän levy-I/O:ta: Levyltä tarvitsee lukea vähemmän tietoa, koska päätaulu ohitetaan.
- Kyselyiden nopeampi suoritus: I/O-toimintojen väheneminen nopeuttaa suoraan kyselyiden vastauksia.
- Välimuistin parempi hyödyntäminen: Indeksit ovat usein pienempiä ja niihin käsiksi päästään useammin, joten muistivälimuisteja hyödynnetään paremmin.
- Samanaikaisuus: Taulun tietolohkoihin kohdistuu vähemmän kilpailua.
Kompromissit ja huomioon otettavat seikat
Vaikka kattavat indeksit ovat tehokkaita, ne eivät ratkaise kaikkia ongelmia:
- Suuremmat indeksit: Kun indeksiin lisätään sarakkeita, sen fyysinen koko kasvaa ja levytilaa kuluu enemmän.
- Hitaammat kirjoitukset: Päätaulun päivitykset, lisäykset ja poistot edellyttävät myös indeksin päivittämistä, mikä voi olla hitaampaa suurilla indekseillä.
- Ylläpito: Suurten indeksien
VACUUM-käsittely voi kestää kauemmin.
Käyttäkää niitä harkitusti paljon luettavissa kyselyissä, joissa käsitellään usein tiettyä sarakejoukkoa.
Käytännön esimerkki kattavasta indeksistä
Oletetaan, että käytössänne on `users`-taulu, josta haette usein käyttäjien nimet ja sähköpostiosoitteet rekisteröitymispäivän perusteella. `registration_date`-sarakkeen indeksi, joka sisältää myös `name`- ja `email`-sarakkeet, olisi erinomainen ehdokas kattavaksi indeksiksi.
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
registration_date DATE NOT NULL
);
INSERT INTO users (username, email, registration_date) VALUES
('alice', 'alice@example.com', '2023-01-15'),
('bob', 'bob@example.com', '2023-02-20'),
('charlie', 'charlie@example.com', '2023-01-25');
CREATE INDEX idx_users_regdate_cover_name_email
ON users (registration_date) INCLUDE (username, email);
EXPLAIN ANALYZE
SELECT username, email
FROM users
WHERE registration_date BETWEEN '2023-01-01' AND '2023-01-31';Pikatarkistus: indeksiskannaukset
Tunnistakaa oppimanne perusteella oikeat väittämät PostgreSQL:n Index-Only Scan -skannauksista.
Kertaus: kattavat indeksit
Olette oppineet, että kattavat indeksit sisältävät kaikki kyselyn tarvitsemat sarakkeet, mikä mahdollistaa erittäin tehokkaat Index-Only Scan -skannaukset. Nämä skannaukset ohittavat päätaulun, vähentävät levy-I/O:ta merkittävästi ja nopeuttavat paljon luettavia kyselyitä.
Muistakaa varmistaa niiden käyttö komennolla EXPLAIN ANALYZE ja huomioida indeksin kasvavan koon sekä mahdollisen kirjoitusten hidastumisen aiheuttamat kompromissit. Tehokasta optimointia!
Opi SQL 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
- 22
- Oppitunnit
- 88
Usein kysytyt kysymykset
Onko oppitunti ”Peittävät indeksit ja pelkät indeksiskannaukset” ilmainen?
Kyllä — voit lukea täällä verkossa kokonaan ilmaiseksi mitkä tahansa PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolun 3 oppituntia, myös oppitunnin “Peittävät indeksit ja pelkät indeksiskannaukset”. Sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä interaktiiviset harjoitukset sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. PostgreSQL:n suorituskyky ja kyselyjen optimointi-kurssilla on yhteensä 4 oppituntia.
Mitä opin oppitunnilla ”Peittävät indeksit ja pelkät indeksiskannaukset”?
Tutustu siihen, miten peittävät indeksit voivat poistaa taulun käytön tarpeen ja mahdollistaa erittäin tehokkaat pelkät indeksiskannaukset. Harjoittelet PostgreSQL:n suorituskyky ja kyselyjen optimointi-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.
Tarvitsenko kokemusta aloittaakseni PostgreSQL:n suorituskyky ja kyselyjen optimointi-opiskelun?
Aiempi kokemus ei ole tarpeen. CoddyKitin PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 3/4.
Kuinka kauan ”Peittävät indeksit ja pelkät indeksiskannaukset”-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ä PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunnilla?
Kyllä. Jokainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-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
- Hash-, GIN- ja GiST-indeksit
- Osittaiset indeksit ja lausekeindeksit
- Peittävät indeksit ja pelkät indeksiskannaukset
- BRIN-indeksit suurille peräkkäisille tietomäärille