Jak vytvářet indexy v PostgreSQL

Indexy jsou specializované vyhledávací tabulky používané motory pro vyhledávání v databankách k urychlení výsledků dotazů. Index je odkaz na informace v tabulce. Pokud například jména v seznamu kontaktů nejsou v abecedním pořadí, budete muset jít dolů po každém řádku a prohledávat každé jméno, než se dostanete na konkrétní telefonní číslo, které hledáte. Index zrychluje příkazy SELECT a fráze WHERE a provádí zadávání dat v příkazech UPDATE a INSERT. Bez ohledu na to, zda jsou indexy vloženy nebo odstraněny, nemá to žádný vliv na informace obsažené v tabulce. Indexy mohou být speciální stejným způsobem, jakým omezení UNIQUE pomáhá vyhnout se replikám záznamů v poli nebo v sadě polí, pro která index existuje.

Obecná syntaxe

Následující obecná syntaxe se používá k vytvoření indexů.

>> CREATE INDEX index_name ON table_name (column_name);

Chcete-li začít pracovat na indexech, otevřete pgAdmin Postgresql z pruhu aplikace. Níže naleznete možnost „Servery“. Klepněte pravým tlačítkem na tuto možnost a připojte ji k databázi.

Jak vidíte, databáze „Test“ je uvedena v možnosti „Databáze“. Pokud žádný nemáte, klikněte pravým tlačítkem na „Databáze“, přejděte na možnost „Vytvořit“ a pojmenujte databázi podle svých preferencí.

Rozbalte možnost „Schémata“ a najdete zde možnost „Tabulky“. Pokud žádný nemáte, klikněte na něj pravým tlačítkem myši, přejděte do části Vytvořit a kliknutím na možnost Tabulka vytvořte novou tabulku. Protože jsme již vytvořili tabulku 'emp', můžete ji vidět v seznamu.

Zkuste načíst dotaz v editoru dotazů a načíst záznamy z tabulky „emp“, jak je znázorněno níže.

>> VÝBĚR * Z veřejné.emp OBJEDNAT PODLE „id“ ASC;

Následující údaje budou v tabulce „emp“.

Vytvořte indexy s jedním sloupcem

Rozbalte tabulku „emp“ a vyhledejte různé kategorie, např.G., Sloupce, omezení, indexy atd. Klikněte pravým tlačítkem na „Rejstříky“, přejděte na možnost „Vytvořit“ a kliknutím na „Rejstřík“ vytvořte nový index.

Vytvořte index pro danou tabulku „emp“ nebo eventuální zobrazení pomocí dialogového okna Rejstřík. Zde jsou dvě karty: „Obecné“ a „Definice.„Na kartě„ Obecné “vložte do pole„ Název “konkrétní název nového indexu. Pomocí rozevíracího seznamu vedle položky „Tablespace“ vyberte „tabulkový prostor“, pod kterým bude nový index uložen.„Stejně jako v oblasti„ Komentář “zde vytvářejte komentáře k rejstříku. Chcete-li zahájit tento proces, přejděte na kartu Definice.

Zde zadejte „metodu přístupu“ výběrem typu indexu. Poté, abyste vytvořili svůj index jako „jedinečný“, je zde uvedeno několik dalších možností. V oblasti „Sloupce“ klepněte na znaménko „+“ a přidejte názvy sloupců, které se mají použít pro indexování. Jak vidíte, indexování aplikujeme pouze na sloupec „Telefon“. Začněte výběrem sekce SQL.

Záložka SQL zobrazuje příkaz SQL, který byl vytvořen vašimi vstupy v dialogu Rejstřík. Kliknutím na tlačítko „Uložit“ vytvořte rejstřík.

Opět přejděte na možnost „Tabulky“ a přejděte k tabulce „emp“. Obnovte volbu „Indexy“ a najdete v ní nově vytvořený index „index_on_phone“.

Nyní provedeme příkaz EXPLAIN SELECT, abychom zkontrolovali výsledky indexů pomocí klauzule WHERE. Výsledkem bude následující výstup, který říká: „Seq Scan on emp.„Možná se divíte, proč se to stalo, když používáte indexy.

Důvod: Plánovač Postgres se může z různých důvodů rozhodnout, že nebude mít index. Stratég dělá většinu času ta nejlepší rozhodnutí, i když důvody nejsou vždy jasné. Je v pořádku, pokud se v některých dotazech používá indexové vyhledávání, ale ne ve všech. Položky vrácené z jedné tabulky se mohou lišit v závislosti na pevných hodnotách vrácených dotazem. Protože k tomu dochází, sekvenční skenování je téměř vždy rychlejší než indexové skenování, což naznačuje, že plánovač dotazů měl pravděpodobně pravdu při určování, že náklady na spuštění dotazu tímto způsobem jsou sníženy.

Vytvořte více indexů sloupců

Chcete-li vytvořit indexy s více sloupci, otevřete prostředí příkazového řádku a zvažte následující tabulku 'student', abyste mohli začít pracovat na indexech s více sloupci.

>> SELECT * FROM Student;

Napište do něj následující dotaz CREATE INDEX. Tento dotaz vytvoří index s názvem „new_index“ ve sloupcích „sname“ a „age“ tabulky „student“.

>> CREATE INDEX new_index ON Student (sname, age);

Nyní uvedeme vlastnosti a atributy nově vytvořeného indexu 'new_index' pomocí příkazu '\ d'. Jak vidíte na obrázku, jedná se o index typu btree, který byl aplikován na sloupce „sname“ a „age“.

>> \ d new_index;

Vytvořte JEDINEČNÝ index

Chcete-li vytvořit jedinečný index, předpokládejte následující tabulku „emp“.

>> SELECT * FROM emp;

Spusťte dotaz CREATE UNIQUE INDEX v shellu, za kterým následuje název indexu 'empind' ve sloupci 'name' v ​​tabulce 'emp'. Ve výstupu vidíte, že jedinečný index nelze použít na sloupec s duplicitními hodnotami názvu.

>> VYTVOŘIT jedinečný INDEX empind ON emp (name);

Nezapomeňte použít jedinečný index pouze na sloupce, které neobsahují žádné duplikáty. U tabulky „emp“ můžete předpokládat, že pouze sloupec „id“ obsahuje jedinečné hodnoty. Použijeme na něj tedy jedinečný index.

>> VYTVOŘIT jedinečný INDEX empind ON emp (id);

Níže jsou uvedeny atributy jedinečného indexu.

>> \ d empid;

Drop index

Příkaz DROP se používá k odebrání indexu z tabulky.

>> DROP INDEX empind;

Závěr

Zatímco indexy jsou navrženy tak, aby zlepšily efektivitu databází, v některých případech není možné použít index. Při použití indexu je třeba vzít v úvahu následující pravidla:

• U malých stolů by neměly být indexy odhozeny.
• Tabulky se spoustou rozsáhlých dávkových operací upgradu / aktualizace nebo přidání / vložení.
• U sloupců s podstatným procentem hodnot NULL nelze indexy míchat-
• Prodej.
• Je třeba se vyhnout indexování u pravidelně manipulovaných sloupců.