← Cloudflare D1 / d1 / best-practices

Použití indexů

Indexy umožňují D1 zlepšit výkon dotazů nad indexovanými sloupci u běžných (často používaných) dotazů tím, že snižují množství dat (počet řádků), která musí databáze při spuštění dotazu prohledat.

Indexy jsou užitečné:

• Když chcete zlepšit výkon čtení u sloupců, které se pravidelně používají v predikátech, například WHERE email_address = ? nebo WHERE user_id = 'a793b483-df87-43a8-a057-e5286d3537c5' - e-mailové adresy, uživatelská jména, ID uživatelů a data jsou vhodnou volbou sloupců pro indexování v typických webových aplikacích nebo službách.
• Pro vynucení omezení jedinečnosti na sloupci nebo sloupcích, například e-mailové adresy nebo ID uživatele, pomocí CREATE UNIQUE INDEX.
• Pokud dotazujete více sloupců současně: (customer_id, transaction_date).
• Pro sloupce používané k propojení tabulek, například sloupec ON orders.customer_id = customers.id v JOIN. Vhodný index dokáže zamezit opakovanému prohledávání nebo vytváření dočasného indexu. Použijte EXPLAIN QUERY PLAN k ověření plánu dotazu.

Indexy se automaticky aktualizují při vložení, aktualizaci nebo smazání dat v tabulce a sloupcích, na které odkazují. Po zápisu do tabulky, na kterou index odkazuje, ho není nutné ručně aktualizovat.

Chcete-li vytvořit index na tabulce D1, použijte CREATE INDEX příkaz SQL a zadejte tabulku a sloupec (nebo sloupce), nad kterými se má index vytvořit.

Vezměme si například následující orders tabulku, možná budete chtít vytvořit index na customer_id. Téměř všechny dotazy nad touto tabulkou filtrují podle customer_id, a vytvořením indexu pro něj byste dosáhli zlepšení výkonu.

CREATE TABLE IF NOT EXISTS orders (
    order_id INTEGER PRIMARY KEY,
    customer_id STRING NOT NULL, -- for example, a unique ID aba0e360-1e04-41b3-91a0-1f2263e1e0fb
    order_date STRING NOT NULL,
    status INTEGER NOT NULL,
    last_updated_date STRING NOT NULL
)

Chcete-li vytvořit index na customer_id sloupec spusťte proti své databázi následující příkaz:

CREATE INDEX IF NOT EXISTS idx_orders_customer_id ON orders(customer_id)

Dotazy, které odkazují na customer_id sloupec nyní bude těžit z indexu:

-- Uses the index: the indexed column is referenced by the query.
SELECT * FROM orders WHERE customer_id = ?

-- Does not use the index: customer_id is not in the query.
SELECT * FROM orders WHERE order_date = '2023-05-01'

Ve složitějších případech můžete ověřit, zda D1 použilo index, a to analýza dotazu přímo.

Po vytvoření indexu spusťte PRAGMA optimize příkaz pro zlepšení výkonu databáze.

PRAGMA optimize spouští ANALYZE příkaz na každé tabulce v databázi, který shromažďuje statistiky o tabulkách a indexech. Tyto statistiky umožňují query planner ke generování co nejefektivnějšího plánu dotazu při provádění uživatelského dotazu.

Další informace najdete v tématu PRAGMA optimize.

Indexy databáze i jejich definici SQL zobrazíte dotazem na sqlite_schema systémovou tabulku:

SELECT name, type, sql FROM sqlite_schema WHERE type IN ('index');

Výsledkem bude výstup podobný tomuto:

┌──────────────────────────────────┬───────┬────────────────────────────────────────┐
│ name                             │ type  │ sql                                    │
├──────────────────────────────────┼───────┼────────────────────────────────────────┤
│ idx_users_id                     │ index │ CREATE INDEX idx_users_id ON users(id) │
└──────────────────────────────────┴───────┴────────────────────────────────────────┘

Tuto tabulku ani existující index nelze upravit. Chcete-li upravit index, nejprve ji smažte a vytvořte nový index s aktualizovanou definicí.

Ověřte, že byl pro dotaz použit index tak, že na jeho začátek přidáte EXPLAIN QUERY PLAN ↗. Tím se vypíše plán dotazu pro následující příkaz, včetně toho, které indexy (pokud nějaké) byly použity.

Pokud například předpokládáte users tabulka má email_address TEXT sloupec a vytvořili jste index CREATE UNIQUE INDEX idx_email_address ON users(email_address), jakýkoli dotaz s predikátem na email_address by mělo použít váš index.

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email_address = '[email protected]';
QUERY PLAN
`--SEARCH users USING INDEX idx_email_address (email_address=?)

Zkontrolujte USING INDEX <INDEX_NAME> výstup z plánovače dotazů, potvrzující, že byl použit index.

Jde také o poměrně běžný případ použití indexu. Vyhledání uživatele podle e-mailové adresy bývá velmi častým typem dotazu u přihlašovacích (autentizačních) systémů.

Použití indexu může snížit počet řádků, které dotaz načte. Použijte meta objekt pro odhad využití. Více informací najdete v „Mohu použít index ke snížení počtu řádků, které dotaz načte?“ a „Jak mohu odhadnout svou (případnou) fakturu?“.

D1 účtuje podle počtu přečtených a zapsaných řádků, nikoli počtem řádků, které dotaz vrátí. Dotaz, který prohledá celou tabulku, aby vrátil jediný řádek, je zpoplatněn za každý řádek, který prochází. Přidání indexu, díky kterému D1 přejde přímo k potřebným řádkům, je proto jedním z nejúčinnějších způsobů, jak snížit latenci a náklady.

Většina neočekávaně vysokých vyúčtování za D1 pochází z malého počtu často spouštěných dotazů, z nichž každý čte (nebo zapisuje) mnohem více řádků, než kolik jich vrátí. Následující tabulka uvádí běžné vzory, které zasahují příliš mnoho řádků, a způsoby, jak je opravit. Použijte EXPLAIN QUERY PLAN ke kontrole, zda dotaz provádí úplné SCAN (čte každý řádek) nebo SEARCH ... USING INDEX (přeskočí na odpovídající řádky).

U vícesloupcového indexu (indexu, který zahrnuje více sloupců) dotazy index využijí, pouze pokud určí buď all sloupců, nebo podmnožinu sloupců za předpokladu, že všechny sloupce „vlevo“ jsou také součástí dotazu.

Mějme index na CREATE INDEX idx_customer_id_transaction_date ON transactions(customer_id, transaction_date), následující tabulka ukazuje, kdy se index použije (a kdy ne):

Poznámky:

• Pokud byste místo toho vytvořili index nad třemi sloupci: customer_id, transaction_date, a shipping_status totiž dotaz, který používá oba customer_id a transaction_date by použil index, protože zahrnujete všechny sloupce „zleva“.
• Se stejným indexem dotaz, který používá pouze transaction_date a shipping_status by ne použije index, protože jste nepoužili customer_id (nejlevější sloupec) v dotazu.

Částečné indexy jsou indexy nad podmnožinou řádků v tabulce. Definují se pomocí WHERE klauzuli při vytváření indexu. Částečný index může být užitečný k vynechání určitých řádků, například těch, kde jsou hodnoty NULL nebo tam, kde se řádky s konkrétní hodnotou vyskytují napříč dotazy.

• Konkrétním příkladem částečného indexu by byl index na tabulce se sloupcem order_status INTEGER sloupec, kde 6 může představovat "order complete" v kódu vaší aplikace.
• To by umožnilo dotazy na objednávky, které jsou nevyřízené, ve zpracování nebo odeslané, což jsou pravděpodobně jedny z nejčastějších dotazů (uživatelé kontrolující stav své objednávky).
• Částečné indexy zároveň brání tomu, aby index časem neomezeně rostl. Index nemusí obsahovat řádek pro každou dokončenou objednávku, protože dokončené objednávky se dotazují mnohem méně často než objednávky rozpracované.

Částečný index, který z indexu vyřadí dokončené objednávky, by vypadal následovně:

CREATE INDEX idx_order_status_not_complete ON orders(order_status) WHERE order_status != 6

Částečné indexy mohou být rychlejší při čtení (index obsahuje méně řádků) i při zápisu (do indexu se zapisuje méně dat) než úplné indexy. Částečný index lze také kombinovat s vícesloupcový index.

Použijte DROP INDEX k odstranění indexu. Odstraněné indexy nelze obnovit.

Při vytváření indexů mějte na paměti následující:

• Indexy nejsou vždy zdarma z hlediska výkonu. Vytvářejte je pouze na sloupcích, které odpovídají vašim nejčastěji dotazovaným sloupcům. Samotné indexy je nutné udržovat. Při zápisu do indexovaného sloupce musí databáze zapsat jak do tabulky, tak do indexu. Výkonnostní přínos indexu a snížení počtu čtených řádků však téměř vždy tento dodatečný zápis vyváží.
• Nelze vytvářet indexy, které odkazují na jiné tabulky nebo používají nedeterministické funkce, protože takový index by nebyl stabilní.
• Indexy nelze upravovat. Chcete-li do indexu přidat nebo z něj odebrat sloupec, odebrat index a poté vytvořte nový index s novými sloupci. Tyto změny schématu aplikujte jednorázově prostřednictvím verzovaného systému, například Migrace D1.
• Indexy přispívají k celkové úložné kapacitě, kterou databáze vyžaduje: index je ve skutečnosti sám o sobě tabulkou.