Corso MariaDB / Creare indici singoli e composti e scegliere l'ordine delle colonne

Un indice è una struttura aggiuntiva che può aiutare a trovare e ordinare le righe; l'ordine delle colonne in un indice composto influenza le query che può supportare. Esaminare condizioni, ordinamento, colonne richieste e indici esistenti, confrontando i benefici di lettura con i costi di scrittura e spazio.

Considerare l'indice una struttura da mantenere

Le chiavi ordinate possono restringere la ricerca rispetto all'esame di tutte le righe, ma INSERT, UPDATE e DELETE devono mantenere anche gli indici. Aggiungerne altri non è automaticamente vantaggioso. Considerare insieme frequenza delle query, righe filtrate, relazioni JOIN e ordinamento necessario.

Query o regola Ordine candidato delle chiavi Controllo
Ordini recenti di un cliente customer_id, ordered_at, order_id Uguaglianza del cliente, intervallo temporale e criterio per gli orari uguali
Tutti gli ordini per data Può servire il tempo come prima colonna Esigenza diversa dalla ricerca che inizia dal cliente
Codice prodotto univoco UNIQUE(sku) Integrità dei dati e ricerca
Supporto di una chiave esterna esistente Controllare SHOW INDEX Evitare duplicazioni involontarie

La guida MariaDB alla progettazione degli indici per SELECT discute scelta delle colonne e costi. Usare il piccolo esempio seguente per verificare struttura e uguaglianza dei risultati, senza considerarlo una dimostrazione di maggiore velocità.

Controllare gli indici già disponibili

Collegarsi al server di esercitazione come course_user e selezionare shopcourse. La guida al client e ai file SQL UTF-8 descrive salvataggio ed esecuzione con SOURCE C:/mariadb-lab/lesson.sql;. Sostituire il percorso con quello reale ed eseguire SQL nel client MariaDB.

Prima di creare c39_customers, c39_products, c39_orders e c39_items, controllare SELECT VERSION(), DATABASE(); e SHOW TABLES;. Se gli oggetti esistono, esaminarne definizioni e righe. Usare soltanto oggetti di prova eliminabili, conservare le tabelle applicative e accertarsi che il nuovo nome di indice non esista.

USE shopcourse;
CREATE TABLE c39_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c39_order_customer FOREIGN KEY (customer_id)
  REFERENCES c39_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c39_item_order FOREIGN KEY (order_id)
  REFERENCES c39_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c39_item_product FOREIGN KEY (product_id)
  REFERENCES c39_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c39_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c39_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c39_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c39_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Il codice conserva nomi e città coreani della fonte. I prodotti 1–4 sono quaderno, penna, tazza e lampada. I clienti 1 e 3 appartengono a Seoul, il cliente 2 a Busan e il cliente 4 a Daejeon. Mantenere i testi UTF-8 e confrontare gli ID. Sono incluse tutte e quattro le tabelle per rendere autonomo l'esempio.

SHOW INDEX FROM c39_orders;

Chiavi primarie ed esterne possono richiedere indici di supporto già presenti. Leggere nomi delle colonne e Seq_in_index, cioè la posizione di ciascuna colonna nell'indice. Nomi diversi non dimostrano strutture diverse. Esaminare gli indici creati automaticamente e i vincoli che ne dipendono. La guida alle chiavi esterne spiega quali relazioni devono rimanere applicate.

Creare l'indice cliente, momento e ID ordine

CREATE INDEX idx_c39_customer_date ON c39_orders(customer_id,ordered_at,order_id);
EXPLAIN SELECT order_id,ordered_at,status FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SELECT order_id FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SHOW INDEX FROM c39_orders;

La query seleziona gli ordini 101 e 102. customer_id limita la ricerca a un cliente, ordered_at definisce il periodo e order_id stabilisce l'ordine in caso di parità temporale. Confrontare il risultato con la stessa SELECT prima dell'aggiunta dell'indice per conservare righe e ordine richiesti.

Dopo la creazione, SHOW INDEX deve mostrare idx_c39_customer_date con customer_id, ordered_at e order_id in queste posizioni. EXPLAIN mostra candidati e scelta per la query effettiva. Con soli quattro ordini l'ottimizzatore può ragionevolmente scegliere una scansione completa: l'esercizio non dimostra un miglioramento di velocità.

Comprendere colonne iniziali, intervalli e copertura

L'utilità di un indice composto dipende generalmente dalle colonne iniziali. Non presumere che una query sulla sola data ottenga lo stesso accesso efficiente per intervallo da un indice che inizia con customer_id. Questo è diverso dall'affermare in assoluto che nessuna lettura dell'indice sia possibile senza uguaglianza sulla prima colonna.

Condizioni di intervallo, direzione dell'ordinamento, prefissi di testo e colonne selezionate possono influenzare il percorso di accesso. Un covering index, o indice di copertura, fornisce le informazioni richieste dalla query e può evitare parte degli accessi alle righe della tabella. Aggiungere indiscriminatamente tutte le colonne aumenta dimensioni e manutenzione. Controllare indice scelto ed Extra di EXPLAIN, oltre alla presenza di CREATE INDEX.

Se due ordini condividono ordered_at, ORDER BY ordered_at,order_id continua a determinarne esplicitamente la sequenza. L'indice aiuta il percorso di accesso, ma il requisito di ordinamento del risultato deve comunque comparire nell'SQL.

Valutare sovrapposizioni e dipendenze dei vincoli

Nomi differenti possono nascondere colonne, unicità o lunghezze di prefisso uguali oppure sovrapposte. Rileggere SHOW INDEX dopo aver creato l'indice composto per vedere come viene supportata la chiave esterna. Eliminare l'unico indice di supporto mantenendo il vincolo può essere rifiutato.

Prima di eliminare un indice, individuare vincoli e altre query che lo usano e predisporre un sostituto se necessario. PRIMARY KEY e UNIQUE applicano anche regole sui dati: un indice non scelto da una singola query non è automaticamente eliminabile. La modifica degli indici cambia la struttura; verificare anche i confini operativi descritti nella guida a ALTER TABLE.

Conservare i risultati confrontando i costi di accesso

L'aggiunta dell'indice non deve cambiare le righe desiderate né il significato dell'ordinamento. Confrontare piani rappresentativi, letture effettive, latenza e costi di scrittura con distribuzioni realistiche dei dati. Registrare ambiente e metodo: una cache più calda nella seconda prova non dimostra che l'indice abbia causato tutta la differenza.

Prima di ripetere, controllare database selezionato, SHOW CREATE TABLE e righe delle quattro tabelle. Salvare ciò che va conservato ed eliminare soltanto gli oggetti c39_ di questo articolo, partendo dalle tabelle figlie. DROP TABLE elimina struttura e dati e non viene annullato da un normale ROLLBACK: separare la pulizia dalle prove sulle transazioni.

DROP TABLE shopcourse.c39_items;
DROP TABLE shopcourse.c39_orders;
DROP TABLE shopcourse.c39_products;
DROP TABLE shopcourse.c39_customers;