Indice basato sull'espressione di sqlite

Nei database SQLite o SQL, vengono utilizzati gli indici per fare riferimento ad alcune colonne di una tabella. Questi indici possono essere composti da una o più colonne e possono essere univoci. Insieme a ciò, possiamo creare indici su diverse espressioni contenenti colonne diverse di qualsiasi tabella. Queste espressioni potrebbero essere definite nella clausola di condizione Where dell'istruzione selezionata. Pertanto, abbiamo deciso di spiegare l'uso delle espressioni per creare indici e come influenzano i risultati.

Iniziamo con questo articolo aprendo il terminale di shell di Ubuntu 20.04 rapidamente. Usa il tasto di scelta rapida "Ctrl+alt+T" per farlo. Dopo il successo del lancio del terminale Shell, è necessario aggiornare il sistema Linux mediante l'uso dei comandi di aggiornamento e aggiornamento con l'aiuto di un pacchetto APT. Successivamente, aprire il database SQLite utilizzando l'istruzione "SQLite3". In questo momento, finora non abbiamo tabelle nel database.e. usando il .comando tabelle.

Per creare gli indici sulle espressioni, abbiamo bisogno di una tabella nel nostro database. Abbiamo creato una nuova tabella denominata "dettaglio" nel nostro database contenente un totale di 3 colonne di tipo intero, i.e. ID, vendita al dettaglio e vendita. Il tavolo è vuoto in questo momento, io.e. Secondo il comando seleziona.

Crea dettagli della tabella (ID ID INT PRIMARY KEY NON NULL, RETTAGLIO INT, SALE INT);
Seleziona * da dettaglio;

Dobbiamo aggiungere alcuni record nella tabella di dettaglio in modo da poter creare gli indici sulle colonne successive. Pertanto, abbiamo utilizzato le istruzioni di inserimento con la parola chiave seguita dal nome della tabella "dettaglio" e dai nomi delle colonne "ID, vendita al dettaglio, vendita". I nomi delle colonne sono seguiti dai valori delle parole chiave e dai record da inserire nelle colonne. Abbiamo inserito un totale di 10 record all'interno di tre colonne da questa tabella; Ognuno è unico come mostrato:

Inserisci i dettagli (ID, vendita al dettaglio, vendita) (1, 340, 400);
Inserisci i dettagli (ID, vendita al dettaglio, vendita) (2, 380, 420);
Inserisci i dettagli (ID, vendita al dettaglio, vendita) (3, 205, 230);
Inserisci i dettagli (ID, vendita al dettaglio, vendita) (4, 98, 110);
Inserisci i dettagli (ID, vendita al dettaglio, vendita) (5, 311, 340);
Inserisci i dettagli (ID, vendita al dettaglio, vendita) (6, 100, 120);
Inserisci i dettagli (ID, vendita al dettaglio, vendita) (7, 512, 540);
Inserisci i dettagli (ID, vendita al dettaglio, vendita) (8, 634, 670);
INSERT IN DETTAGLIE (ID, vendita al dettaglio, vendita) Valori (9, 54, 67);
Inserisci i dettagli (ID, vendita al dettaglio, vendita) (10, 934, 990);

Dopo aver utilizzato l'istruzione Seleziona con "*" per la visualizzazione di tutti i record della colonna dalla tabella dei dettagli, abbiamo mostrato i 10 record di recente inseriti nella tabella.

Seleziona * da dettaglio;

Prima di creare un indice sulle espressioni, diamo un'occhiata alla semplice domanda per recuperare i record con l'uso delle espressioni. Inoltre, utilizziamo l'ordine per opzione all'interno della query per organizzare i risultati in base all'ordine decrescente di una lunghezza di una colonna al dettaglio. Finora sono stati visualizzati un totale di 8 record.

Seleziona * Da dettaglio dove lunghezza (vendita al dettaglio)> 2 ordine per lunghezza (vendita al dettaglio);

Usiamo la stessa query all'interno dell'istruzione del pianificatore di query SQLite per vedere come funziona SQLite per eseguire questa query. Abbiamo scoperto che deve scansionare l'intero "dettaglio" della tabella utilizzando il b-albero temporaneo (albero bilanciato) sull'ordine per clausola.

Spiega il piano di query Seleziona * Da dettaglio dove lunghezza (vendita al dettaglio)> 2 ordine per lunghezza (vendita al dettaglio) Desc;

Creiamo un indice sulla stessa espressione "lunghezza" sulla colonna di vendita al dettaglio di un dettaglio della tabella. Utilizziamo l'istruzione Crea indice per creare l'indice "INDICE_RETERAIL".

Crea index_Retail su Detail (Long (Retail));

Dopo aver creato l'indice nella tabella dei dettagli, abbiamo usato il pianificatore di query per vedere la struttura di lavoro della query. Mostra che la query ha lavorato sull'indice basato sull'espressione per recuperare i risultati richiesti invece di cercare l'intera tabella.

Spiega il piano di query Seleziona * Da dettaglio dove lunghezza (vendita al dettaglio)> 2 ordine per lunghezza (vendita al dettaglio) Desc;

Dopo aver utilizzato l'istruzione Seleziona sulla stessa espressione per recuperare i record, i dati vengono ora visualizzati nell'ordine decrescente di lunghezza della colonna di vendita al dettaglio.

Creiamo un'altra query selezionata con un'espressione. Questa volta, l'espressione della classe Where mostrerà solo i record in cui la somma della colonna di vendita e vendita è maggiore di 700.

Seleziona * Da dettaglio in cui vendita al dettaglio+vendita> 700;

Abbiamo utilizzato le precedenti istruzioni selezionate insieme alle singole colonne e all'espressione "vendita al dettaglio+> 700" che contiene, nell'istruzione del pianificatore di query. Il risultato del planner di query mostra che il database SQLite deve cercare in tutta la tabella di dettaglio per recuperare i risultati richiesti con questa espressione abbastanza inefficiente.

Spiegare il piano di query Seleziona ID, vendita al dettaglio, vendita, vendita al dettaglio+vendita da dettagli in cui vendita al dettaglio+vendita> 700;

Creiamo un nuovo indice sull'espressione "vendita al dettaglio+vendita" utilizzata nelle precedenti istruzioni selezionate. Quindi, abbiamo utilizzato l'istruzione Crea Indice per generare un nuovo indice "INDICE_DETAIL" sull'espressione "vendita al dettaglio+vendita" di una tabella di dettaglio. L'indice di nuova produzione "index_detail" può essere visto nell'elenco degli indici secondo ".ISTRUZIONE DETTAGLIO DETTAGLIO DELLA.

Crea index_detail in dettaglio (vendita al dettaglio+vendita);

Ora, proviamo le istruzioni del pianificatore di query per le stesse istruzioni selezionate contenenti espressioni al dettaglio+700 al suo interno che vengono utilizzate nella clausola Where dell'istruzione Seleziona. Dopo aver usato il pianificatore di query, abbiamo scoperto che il piano SQLite cercherà solo le voci specifiche di un dettaglio della tabella secondo l'indice "index_detail" contenente un'espressione.

Spiegare il piano di query Seleziona ID, vendita al dettaglio, vendita, vendita al dettaglio+vendita da dettagli in cui vendita al dettaglio+vendita> 700;

Modifichiamo l'espressione scambiando i nomi delle colonne tra loro senza cambiare la propria conoscenza. Ogni volta che usi le stesse istruzioni selezionate con il piccolo cambiamento nella sua espressione, io.e. "Sale+Retail> 700", visualizza gli stessi 5 risultati poiché la logica è corretta e lo stesso viene utilizzato nella precedente istruzione selezionata.

Seleziona * Da dettaglio dove vendita+vendita al dettaglio> 700;

Dopo aver utilizzato l'istruzione selezionata precedentemente modificata all'interno dell'istruzione del pianificatore di query, abbiamo scoperto che il database SQLite deve cercare l'intera tabella "dettaglio" per questa query basata sull'espressione. Questo perché non abbiamo creato un indice per l'espressione appena modificata "vendita+vendita al dettaglio> 700" nella query selezionata.

Spiega il piano di query seleziona ID, vendita al dettaglio, vendita, vendita+vendita al dettaglio da dettagli in cui vendita+vendita al dettaglio> 700;

Conclusione

Questo articolo riguarda la creazione di un indice su espressioni diverse. Abbiamo provato a utilizzare l'istruzione del pianificatore di query per spiegare la differenza tra l'uso di una semplice espressione all'interno di un'istruzione selezionata senza indice e l'uso di un'espressione all'interno di un'istruzione selezionata basata su un indice.