PostgreSQL:n suorituskyky ja kyselyjen optimointi · Oppitunti

Indeksien luominen ja käyttäminen

Käytännön opas indeksien luomiseen, niiden syntaksin ymmärtämiseen ja kyselyiden suorituskyvyn parantamiseen niiden avulla.

Oppitunti 2/412 vaihetta

Indeksien luominen ja käyttäminen on ilmainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunti CoddyKitissä. Tämä on oppitunti 2/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.

Tervetuloa indeksien luomiseen

Edellisessä oppitunnissa opimme B-Tree-indekseistä, jotka ovat PostgreSQL:n yleisin indeksityyppi. Nyt siirrytään käytäntöön! Tässä oppitunnissa opastamme indeksien luomisessa, käyttämisessä ja hallinnassa kyselyiden suorituskyvyn parantamiseksi.

Opitte syntaksin, näette käytännön esimerkkejä ja ymmärrätte, milloin ja miten indeksejä kannattaa käyttää tehokkaasti.

CREATE INDEX -lause

Indeksin luomisen perussyntaksi on suoraviivainen. Määritätte taulun, indeksoitavat sarakkeet ja valinnaisesti indeksityypin (vaikka B-Tree on oletusarvoinen ja yleisin).

CREATE INDEX: Komento uuden indeksin luomiseen.
index_name: Indeksille valitsemanne yksilöllinen nimi.
table_name: Taulu, jolle indeksi rakennetaan.
column_name(s): Indeksiin sisällytettävät sarakkeet.

Yksinkertainen indeksin luontiesimerkki

Luodaan yksinkertainen products-taulu, lisätään siihen tietoja ja luodaan sitten yksisarakkeinen indeksi. Tätä asetelmaa käytetään seuraavissa esimerkeissä.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  product_name VARCHAR(100),
  price DECIMAL(10, 2),
  category VARCHAR(50)
);

INSERT INTO products (product_name, price, category) VALUES
('Laptop', 1200.00, 'Electronics'),
('Mouse', 25.00, 'Electronics'),
('Keyboard', 75.00, 'Electronics'),
('Desk Chair', 150.00, 'Furniture'),
('Monitor', 300.00, 'Electronics'),
('Webcam', 50.00, 'Electronics'),
('Gaming PC', 1500.00, 'Electronics'),
('Office Desk', 250.00, 'Furniture'),
('Bookshelf', 80.00, 'Furniture'),
('Smartwatch', 199.99, 'Wearables');

CREATE INDEX idx_products_category ON products (category);

Tarkista uusi indeksisi

Kun indeksi on luotu, on hyvä varmistaa, että se on olemassa. PostgreSQL tarjoaa useita tapoja tarkastella taulun rakennetta ja siihen liittyviä indeksejä.

Kirjoittakaa psql-komentoriviasiakasohjelmassa \d products, niin näette products-taulun tiedot, mukaan lukien sen indeksit.
Vaihtoehtoisesti voitte tehdä kyselyn järjestelmäluettelon tauluihin, kuten pg_indexes-tauluun: SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'products';

Milloin indeksit auttavat kyselyissä

Indeksit ovat tehokkaimmillaan silloin, kun PostgreSQL:n on löydettävä tietyt rivit nopeasti ilman koko taulun läpikäyntiä. Voitte ajatella niitä oppikirjan hakemistona, joka osoittaa suoraan olennaisille sivuille.

WHERE-lausekkeet: tietojen suodattaminen indeksoitujen sarakkeiden perusteella (esimerkiksi WHERE category = 'Electronics').
ORDER BY-lausekkeet: tietojen järjestäminen indeksoitujen sarakkeiden perusteella.
JOIN-ehdot: taulujen yhdistäminen indeksoitujen sarakkeiden perusteella.

Ilman indeksiä PostgreSQL saattaa suorittaa suurissa tauluissa hitaan "sekventiaalisen haun".

Yhden sarakkeen indeksi käytännössä

Käytetään EXPLAIN-komentoa tarkastelemaan, miten idx_products_category-indeksi voi auttaa kyselyssä, joka suodattaa category-sarakkeen perusteella. EXPLAIN näyttää kyselysuunnitelman ja paljastaa, käytetäänkö indeksiä.

-- Query that benefits from idx_products_category
EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

-- Another example where the index might help with sorting
EXPLAIN SELECT product_name, price FROM products WHERE category = 'Furniture' ORDER BY price DESC;

Yhdistelmäindeksit (usean sarakkeen indeksit)

Joskus kyselyt suodattavat tai järjestävät tietoja usean sarakkeen perusteella samanaikaisesti. Tällaisissa tapauksissa yhdistelmäindeksistä (eli usean sarakkeen indeksistä) voi olla paljon hyötyä.

Yhdistelmäindeksi sisältää useamman kuin yhden sarakkeen, ja sarakkeiden järjestyksellä on merkitystä! Sijoittakaa useimmin käytetyt tai selektiivisimmät sarakkeet (eli sarakkeet, joissa on eniten yksilöllisiä arvoja) ensimmäisiksi.

Yhdistelmäindeksin luominen

Luodaan yhdistelmäindeksi sekä category- että price-sarakkeelle. Tästä olisi hyötyä kyselyissä, jotka suodattavat ensin luokan perusteella ja suodattavat tai järjestävät sitten hinnan perusteella kyseisen luokan sisällä.

DROP INDEX IF EXISTS idx_products_category_price;
CREATE INDEX idx_products_category_price ON products (category, price);

-- Query benefiting from the composite index
EXPLAIN SELECT product_name, price
FROM products
WHERE category = 'Electronics' AND price > 100
ORDER BY price DESC;

Milloin indeksiä ei kannata luoda

Indeksit eivät ole aina ratkaisu kaikkeen. Niihin liittyy kompromisseja:

Tallennustila: indeksit kuluttavat levytilaa, suurissa tauluissa mahdollisesti paljonkin.
Kirjoitustoimintojen lisäkustannus: jokainen indeksoituun sarakkeeseen kohdistuva INSERT-, UPDATE- tai DELETE-toiminto edellyttää indeksin päivittämistä, mikä voi hidastaa kirjoitustoimintoja.
Liiallinen indeksointi: liian monet indeksit voivat hämmentää kyselysuunnittelijaa tai kasvattaa kirjoitustoimintojen lisäkustannusta, mikä heikentää suorituskykyä kokonaisuudessaan.

Välttäkää sellaisten sarakkeiden indeksointia, joissa on hyvin vähän yksilöllisiä arvoja (esimerkiksi boolean-tyyppinen is_active-sarake).

Indeksin poistaminen

Jos indeksiä ei enää tarvita tai olette luonut paremman indeksin, voitte poistaa sen helposti DROP INDEX -komennolla. Tämä vapauttaa tallennustilaa ja vähentää tietokannan kirjoitustoimintojen lisäkustannusta.

DROP INDEX IF EXISTS idx_products_category_price;
DROP INDEX IF EXISTS idx_products_category;

-- You can also drop the table if you want to clean up completely
-- DROP TABLE IF EXISTS products;

Indeksitietovisa

Olette oppinut luomaan ja hallitsemaan indeksejä. Testataan ymmärrystänne!

Vastaa interaktiivisessa oppitunnissa

Kertaus: indeksien luominen ja käyttäminen

Hienoa työtä! Tässä oppitunnissa opitte käytännössä, miten PostgreSQL:n indeksejä luodaan ja hallitaan:

Käytimme CREATE INDEX -komentoa indeksien lisäämiseen tauluihin.
Tutustuimme sekä yhden sarakkeen indekseihin että yhdistelmäindekseihin (usean sarakkeen indekseihin).
Opitte, milloin indekseistä on hyötyä (WHERE, ORDER BY ja JOIN) ja millaisia kompromisseja niihin liittyy (kirjoitustoimintojen lisäkustannus ja tallennustila).
Opitte myös lopuksi varmistamaan indeksien olemassaolon ja poistamaan niitä DROP INDEX -komennolla.

Seuraavaksi perehdymme tarkemmin parhaisiin käytäntöihin, joiden avulla päätetään, mitkä sarakkeet kannattaa indeksoida ja miten yleiset ongelmat voidaan välttää!

Aloita maksutta

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

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Indeksien luominen ja käyttäminen” ilmainen?

Kyllä — voit lukea täällä verkossa kokonaan ilmaiseksi mitkä tahansa PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolun 3 oppituntia, myös oppitunnin “Indeksien luominen ja käyttäminen”. 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 ”Indeksien luominen ja käyttäminen”?

Käytännön opas indeksien luomiseen, niiden syntaksin ymmärtämiseen ja kyselyiden suorituskyvyn parantamiseen niiden avulla. 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 2/4.

Kuinka kauan ”Indeksien luominen ja käyttäminen”-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

← Takaisin: PostgreSQL:n suorituskyky ja kyselyjen optimointi