Corso MariaDB / Migliorare filtri, ordinamento e paginazione delle query

Migliorare una query significa conservarne le righe desiderate allineando filtri, ordinamento e paginazione a percorsi di accesso adatti. Dichiarare confini temporali e criteri univoci per le parità, confrontare OFFSET con la navigazione per chiave e verificare i vantaggi tramite piani effettivi e dati rappresentativi.

Verificare l'uguaglianza dei risultati prima di confrontare i costi

Una query più rapida che esclude un istante di confine o righe con lo stesso valore di ordinamento non è un miglioramento equivalente. Confrontare prima chiavi primarie restituite e sequenza, poi piano, lavoro e latenza. Eliminare un requisito per ridurre il costo cambia il significato della query. La guida a ORDER BY e LIMIT descrive ordine e limiti delle pagine; la guida a EXPLAIN confronta i piani prima e dopo una modifica.

Aspetto Decisione Esempio
Filtro temporale Inizio incluso, confine successivo escluso Un intero giorno di calendario
Ordinamento Sequenza deterministica in caso di parità ordered_at, order_id
Paginazione Esigenze di navigazione e coerenza OFFSET oppure ultimo valore ordinato
Ricerca testuale Schema realmente desiderato Prefisso diverso da sottostringa

La guida MariaDB alla progettazione degli indici collega forme delle query e indici candidati. La correttezza dei risultati rimane il requisito preliminare al confronto dei costi.

Preparare ordini e indice temporale

Collegarsi al server di esercitazione come course_user e selezionare shopcourse. La guida al client e ai file SQL UTF-8 spiega 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 c41_customers, c41_products, c41_orders e c41_items, controllare SELECT VERSION(), DATABASE(); e SHOW TABLES;. Se gli oggetti esistono, esaminarne definizioni e righe. Usare soltanto dati di prova eliminabili e conservare le tabelle applicative. La preparazione comprende tutte le tabelle necessarie.

USE shopcourse;
CREATE TABLE c41_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 c41_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 c41_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_c41_order_customer FOREIGN KEY (customer_id)
  REFERENCES c41_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_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_c41_item_order FOREIGN KEY (order_id)
  REFERENCES c41_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c41_item_product FOREIGN KEY (product_id)
  REFERENCES c41_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c41_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c41_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c41_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 c41_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 usare gli ID nei confronti.

CREATE INDEX idx_c41_time ON c41_orders(ordered_at,order_id);

I quattro ordini si confrontano per momento e ID. Una scansione completa può ancora essere ragionevole per una tabella così piccola. L'esercizio definisce righe e confini corretti; per valutare vantaggi di prestazioni più ampi occorrono dati rappresentativi dell'uso.

Confrontare la funzione di data con un intervallo esplicito

SELECT order_id FROM c41_orders WHERE DATE(ordered_at)='2026-01-10'
ORDER BY order_id;
SELECT order_id FROM c41_orders
WHERE ordered_at>='2026-01-10' AND ordered_at<'2026-01-11' ORDER BY order_id;

Entrambe le query restituiscono l'ordine 101. L'intervallo semiaperto rappresenta un intero giorno: include la mezzanotte del 10 gennaio e termina prima della mezzanotte dell'11 gennaio. Include anche frazioni di secondo se la colonna le conserva, senza dover indovinare l'ultimo istante della giornata.

Da MariaDB 11.1, determinate condizioni DATE e YEAR su colonne temporali indicizzate possono essere trasformate in accessi per intervallo. Una funzione sulla colonna non impedisce quindi sempre l'uso dell'indice. Il riferimento all'ottimizzazione DATE e YEAR di MariaDB specifica tipi di colonna e operatori supportati.

L'intervallo esplicito può rendere più leggibile l'intento su versioni diverse, ma non dimostra da solo maggiore velocità. Confrontare indice e intervallo scelti tramite EXPLAIN. Formattare una data come testo per confrontarla può confondere presentazione e ricerca: verificare anche il tipo del valore.

Usare momento e ID per la pagina successiva

SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
 OR(ordered_at='2026-02-05 12:00:00' AND order_id<103)
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;

La prima pagina in ordine decrescente contiene 104 e 103. Usando momento e ID dell'ordine 103 come ultima posizione, la pagina successiva contiene 102 e 101. La condizione sull'ID impedisce di saltare ordini allo stesso istante. Cambiando direzione di ordinamento, rivedere anche il verso dei confronti.

La navigazione per chiave, o keyset pagination, usa gli ultimi valori ordinati come cursore. Può evitare OFFSET profondi, ma offre un'interfaccia diversa dal salto diretto a qualsiasi numero di pagina. Le modifiche concorrenti possono comunque cambiare l'elenco tra richieste. Stabilire se serve uno snapshot stabile senza presumere che il cursore congeli automaticamente i dati.

Provare il confine con orari uguali mediante la modifica annullabile seguente. Eseguire tutto il blocco nella stessa connessione sulle tabelle di esercitazione.

START TRANSACTION;
UPDATE c41_orders SET ordered_at='2026-02-05 12:00:00' WHERE order_id=104;
SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
   OR (ordered_at='2026-02-05 12:00:00' AND order_id<104)
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
ROLLBACK;

Il primo risultato è 104 e il successivo 103, con lo stesso momento. Un confine basato soltanto sul tempo può saltare 103 e passare direttamente a 102. Dopo ROLLBACK, verificare che l'ordine 104 torni al 10 febbraio alle 13:00:00.

Rivedere colonne restituite, filtri e schemi di ricerca

SELECT * può trasferire testi grandi non necessari, aumentando memoria o accessi alle righe. Ridurre le colonne è diverso dal ridurre le righe ammesse. Il piano può indicare se le informazioni richieste sono disponibili in un indice di copertura.

Nella ricerca B-tree ordinaria, un prefisso LIKE è diverso da una sottostringa cercata con un carattere jolly iniziale. Passare da ricerca interna a prefisso può apparire più veloce cambiando silenziosamente i risultati. Scegliere schema e regole di confronto testuale desiderati, poi valutare una strategia adeguata.

Registrare le condizioni di confronto e i requisiti collegati

Conservare entrambe le query, chiavi risultanti, versione del server, indici, numero e distribuzione delle righe. Separare l'effetto della cache dalle modifiche strutturali e non forzare un indice soltanto per ottenere un piano atteso. Dopo una modifica, verificare gli altri ordinamenti e percorsi di navigazione che condividono lo stesso accesso.

Prima di ripetere, controllare database selezionato, SHOW CREATE TABLE e dati delle quattro tabelle. Salvare ciò che va conservato ed eliminare soltanto gli oggetti c41_ 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.c41_items;
DROP TABLE shopcourse.c41_orders;
DROP TABLE shopcourse.c41_products;
DROP TABLE shopcourse.c41_customers;