Indexování v databázi

[Arthnon]

Zdravím,
snažím se pochopit, jak správně navolit indexy (respektivě kde je správně použít a jak...)

1.) Řekněme, že se uživatel přihlašuje pomocí svého emailu. V databázi je defaultně nastavený index na ID uživatele. Měl by být nataven index na atribut email v databázi? Je to správně či ne?
2.) V aplikaci dochází často k vyhledávání nějakého objektu (knihy, dokumentu etc) pomocí titulu. Ten SQL dotaz používá:
SELECT * FROM kniha WHERE id=1 AND titul LIKE %vyhledávaný_požadavek%;  Měl by být použit index na ten titul na knize? Případně jak by to mělo být uděláno, když je používáno pro vyhledávání LIKE?
3.) V aplikaci dochází k vyhledávání vydaných knih v nějakém časovém horiozontu (měsíc/rok). Takže SQL dotaz potom vypadá:
SELECT * FROM kniha WHERE year(datum_vydani)=:uživatelsky_zadaná_rok AND month(datum_vydani) = :uživatelsky_zadaný_měsíc. Měl by být v tomhle případě použit index na datum_vydaní?

Mohl bych poprosit, zda byste mi mohli v jednoduchosti říct, kdy bych měl používat indexy? Na jaké atributy? Nebo prostě platí, že by to mělo být na všechny atributy, které jsou často dotazované uživateli?

Mockrát děkuji

[Reklama]

[Tomáš Procházka]

Use the index, Luke

https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning

[alex6bbc]

1) indexy neco stoji kdyz se musi vygenerovat (insert, update).
2) index ma smysl, kdyz se neco vyznamne meni zaznam od zaznamu, treba id.
3) index zlepsuje vyhledavani, bez indexu by to bylo O(N) s indexem je to O(logN).

[RDa]

1.) Řekněme, že se uživatel přihlašuje pomocí svého emailu. V databázi je defaultně nastavený index na ID uživatele. Měl by být nataven index na atribut email v databázi? Je to správně či ne?

ano, pripadne tam mit rovnou Unique

2.) V aplikaci dochází často k vyhledávání nějakého objektu (knihy, dokumentu etc) pomocí titulu. Ten SQL dotaz používá:
SELECT * FROM kniha WHERE id=1 AND titul LIKE %vyhledávaný_požadavek%;  Měl by být použit index na ten titul na knize? Případně jak by to mělo být uděláno, když je používáno pro vyhledávání LIKE?

sice existuji fulltext index slouzici pro neco takoveho, ale pokud jde o velkou DB a velky pocet pozadavku, muzes si to vyresit aplikacne (tj vygenerovat mezivrstvu po slovech, s/bez diakritiky a ta). Taky na to existuji knihovny co tohle resi.

3.) V aplikaci dochází k vyhledávání vydaných knih v nějakém časovém horiozontu (měsíc/rok). Takže SQL dotaz potom vypadá:
SELECT * FROM kniha WHERE year(datum_vydani)=:uživatelsky_zadaná_rok AND month(datum_vydani) = :uživatelsky_zadaný_měsíc. Měl by být v tomhle případě použit index na datum_vydaní?

Vzhledem k tomu ze month() nevraci cast indexu datumu (mensi/vetsi nez), tak to akcelerovano indexama nebude. Zde bude lepsi znova resit aplikacne tu dotazovanou podminku - tj. mit extra sloupce s rokem i mesicem, resp. v tomto pripade by postacilo mit sloupec s hodnotou YYYYMM, a nad tim index, pak je to porovnani vuci nemu primo.

[Filip Jirsák]

Dobrý index výrazně urychluje vyhledávání, zároveň údržba indexu něco stojí. Obecně by tedy indexy měly být na všem, kde se vyhledává (což nejsou jen uživatelské dotazy, ale také podmínky v UPDATE, kontrola unikátnosti nebo referenční integrity apod.) Index nedává smysl v případě, kdy se z tabulky vybírá velký podíl záznamů – pokud z tabulky o 1000 záznamech budete vybírat 700, index nejspíš nebude mít smysl.

Dál je potřeba řešit, kdy může databáze index použít. Např. pro LIKE s procentem jinde než na konci bude databáze obvykle potřebovat nějaký fulltextový index. Často nepůjde použít index, pokud s ním v dotazu porovnáváte výsledek funkce (ale můžete zaindexovat i ten výsledek funkce).

V tom vašem případě byste tedy na e-mail měl mít index (dokonce unikátní, jak píše RDa). Pro název titulu byste musel použít fulltextový index. A u toho data vydání nejspíš databáze nepochopí, že pro váš dotaz může použít index – lepší by bylo použít BETWEEN podmínku a hledat datum od začátku do konce měsíce, pak index půjde použít.

[Reklama]

[Ondrej Nemecek]

Jen doplním viz níže.

1.) Řekněme, že se uživatel přihlašuje pomocí svého emailu. V databázi je defaultně nastavený index na ID uživatele. Měl by být nataven index na atribut email v databázi? Je to správně či ne?

Ano, dát index na email.

2.) V aplikaci dochází často k vyhledávání nějakého objektu (knihy, dokumentu etc) pomocí titulu. Ten SQL dotaz používá:
SELECT * FROM kniha WHERE id=1 AND titul LIKE %vyhledávaný_požadavek%;  Měl by být použit index na ten titul na knize? Případně jak by to mělo být uděláno, když je používáno pro vyhledávání LIKE?

Při použití title LIKE '%vyhledávaný_požadavek%' index nepomůže, pomohl by pokud by se hledal celý titul title = ''. Pro efektivní hledání v textu je buď fulltext který umí třeba Mysql https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html nebo GIN, GiST a trigramové indexy, které umí je třeba Postgres https://www.postgresql.org/docs/9.5/textsearch-indexes.html https://www.postgresql.org/docs/current/pgtrgm.html

3.) V aplikaci dochází k vyhledávání vydaných knih v nějakém časovém horiozontu (měsíc/rok). Takže SQL dotaz potom vypadá:
SELECT * FROM kniha WHERE year(datum_vydani)=:uživatelsky_zadaná_rok AND month(datum_vydani) = :uživatelsky_zadaný_měsíc. Měl by být v tomhle případě použit index na datum_vydaní?

Indexování datum_vydani moc nepomůže, jelikož na datum_vydani voláte funkci year, potřebujete zaindexovat až výsledek tohoto volání, nikoli vstupní argument. Pokud databáze umí funkční indexy  máte to vyřešené, jinak musíte řešit buď dalším sloupcem (nehezké řešení) nebo úpravou dotazu tak, aby hledat dle rozsahu data (porovnání datumu je rychlé).

[luvar]

Nebudem opakovat uz povedane, ale dam hint k trojke.

3.) V aplikaci dochází k vyhledávání vydaných knih v nějakém časovém horiozontu (měsíc/rok). Takže SQL dotaz potom vypadá:
SELECT * FROM kniha WHERE year(datum_vydani)=:uživatelsky_zadaná_rok AND month(datum_vydani) = :uživatelsky_zadaný_měsíc. Měl by být v tomhle případě použit index na datum_vydaní?

Dve možnosti:

Osobne by som upravil podmienku na iny selekt, ktory ale vrati to, co pozadujete a pouzije i datumovy index. Nieco, co pouzije nezmeneny stlpec datum_vydani. Napriklad:

SELECT * FROM kniha WHERE datum_vydani between ':uživatelsky_zadaná_rok-:uživatelsky_zadaný_měsíc-01' AND ':uživatelsky_zadaná_rok-:uživatelsky_zadaný_měsíc-31';

Tam treba ale ošetriť odfiltrovanie záznamov, kde nieje deň 31. Ak si dobre pamatám, tak dátum 2020.02.31 je vpodstate postgrečkom pretransformované na 2020.03.02 (druhý marec).

ALEBO:

vytvoriť "computed index", napríklad v štýle:

CREATE INDEX idx_rok_a_mesiac_vydania ON kniha (date_trunc('year', datum_vydani)) INCLUDE (date_trunc('month', datum_vydani));

Vyhodou je, ze povodny selekt pojde bezozmeny a vyuzije sa jeden index (obsahujuci dva stlpce).

[Arthon]

Zdravím, pokud mám dotaz zobrazený dole, kde :minDate je objekt typu Date (minimální den v měsíci v daném roce) a :maxDate je objekt typu Date (maximálně den v měsíci v daném roce), tak jak by měli být provedeny indexy na ten datum? Měl by být index pouze uploadDatetime A zvlášt activeStartTime nebo by měl být vytvořen kompozitní index na uploadDatetime a activeStartTime (tedy tohle by tvořilo jeden index...)

Kód [Vybrat] Rozbalit

TypedQuery<Document> q3 = entityManager.createQuery("SELECT d FROM Document d " +
                "INNER JOIN d.documentsForUsers ud WHERE (d.documentState.id != 0 AND" +
                " ud.sharingType = 1" +
                " AND ud.user.email=:email AND ud.approval != 2 AND" +
                " d.uploadDatetime between :minDate and :maxDate)" +
                " OR(d.documentState.id = 1 AND ud.sharingType.id=2" +
                " AND ud.user.email=:email" +
                " AND current_timestamp > d.activeStartTime AND ud.approval != 2" +
                " AND d.activeStartTime between :minDate AND :maxDate) ORDER BY CASE WHEN ud.sharingType=1 THEN d.uploadDatetime "+
                                        " ELSE d.activeStartTime END DESC", Document.class);

Ta samá otázka u toho druhého dotaz. Jak v tomto případě vytvořit index na datumy? Udělat kompozitní dva uploadDatetime + approvalEndTime a druhý activeStartTime + activeEndTime?

Kód [Vybrat] Rozbalit

TypedQuery<Document> q2 = entityManager.createQuery("SELECT d FROM Document d " +
                        "INNER JOIN d.documentsForUsers ud WHERE (d.documentState.id = 2 AND ud.sharingType = 1" +
                        " AND ud.user.email=:email" +
                        " AND current_timestamp between d.uploadDatetime AND d.approvalEndTime AND ud.approval=2)" +
                        " OR(d.documentState.id = 1 AND ud.sharingType.id=2 AND ud.user.email=:email AND current_timestamp " +
                        "between d.activeStartTime AND d.activeEndTime AND ud.approval=2) ORDER BY CASE WHEN ud.sharingType=1 THEN d.uploadDatetime " +
                        "ELSE d.activeStartTime END DESC"
                , Document.class);

Děkuji