<h1>Confronto delle prestazioni di MySQL: lavorare con e senza indici</h1>
<p>In questo articolo esamineremo come gli indici influenzano le prestazioni delle query in MySQL, confrontando l'esecuzione delle operazioni di filtro e JOIN con e senza indici.</p>
<h2>Cosa sono gli indici e perché sono necessari?</h2>
<p>Gli indici in MySQL sono strutture dati speciali che accelerano la ricerca e il recupero dei dati dalle tabelle. Senza indici, MySQL deve eseguire una scansione completa della tabella, simile a leggere un intero libro per trovare una sola parola. Con gli indici, la ricerca diventa simile all'utilizzo dell'indice di un libro.</p>
<h3>Esempio 1: Filtro dei dati per un singolo campo</h3>
<p><strong>Configurazione dell'ambiente di test</strong></p>
<p>Creiamo una tabella di test e popoliamola con dati:</p>
<div class="code">-- Creare il database di test
CREATE DATABASE test_indexes;
USE test_indexes;
-- Creare una tabella senza indici
CREATE TABLE users_no_index (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Creare una tabella simile con indice
CREATE TABLE users_with_index (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_age (age)
);
-- Popolare le tabelle con dati di test (100.000 record ciascuna)
DELIMITER $$
CREATE PROCEDURE GenerateTestData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100000 DO
INSERT INTO users_no_index (name, email, age)
VALUES (CONCAT('User', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100));
INSERT INTO users_with_index (name, email, age)
VALUES (CONCAT('User', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100));
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL GenerateTestData();</div>
<h3>Confronto delle prestazioni di filtro</h3>
<p><strong>Query senza indice:</strong></p>
<div class="code">-- Analizzare la query senza indice
EXPLAIN SELECT * FROM users_no_index WHERE age = 25;</div>
<p><strong>Risultati EXPLAIN:</strong></p>
<ul>
<li>type: ALL (scansione completa della tabella)</li>
<li>rows: 100000 (100.000 righe controllate)</li>
<li>Extra: Using where</li>
</ul>
<p><strong>Tempo di esecuzione:</strong> ~120 ms</p>
<p><strong>Query con indice:</strong></p>
<div class="code">-- Analizzare la query con indice
EXPLAIN SELECT * FROM users_with_index WHERE age = 25;</div>
<p><strong>Risultati EXPLAIN:</strong></p>
<ul>
<li>type: ref (ricerca tramite indice)</li>
<li>rows: ~1000 (solo ~1000 righe controllate)</li>
<li>key: idx_age (indice utilizzato)</li>
<li>Extra: Using index condition</li>
</ul>
<p><strong>Tempo di esecuzione:</strong> ~5 ms</p>
<p><strong>Conclusioni sul filtro</strong></p>
<ul>
<li><strong>Senza indice:</strong> MySQL esegue una scansione completa della tabella, controllando ogni riga</li>
<li><strong>Con indice:</strong> MySQL utilizza l'indice B-tree per trovare rapidamente le righe richieste</li>
<li><strong>Differenza di prestazioni:</strong> da 20 a 30 volte più veloce</li>
</ul>
<h3>Esempio 2: Operazioni JOIN con e senza indici</h3>
<p><strong>Preparazione delle tabelle correlate</strong></p>
<div class="code">-- Tabella ordini senza indici
CREATE TABLE orders_no_index (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
amount DECIMAL(10,2),
order_date DATE,
status VARCHAR(20)
);
-- Tabella ordini con indice
CREATE TABLE orders_with_index (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
amount DECIMAL(10,2),
order_date DATE,
status VARCHAR(20),
INDEX idx_user_id (user_id)
);
-- Popolare le tabelle ordini
DELIMITER $$
CREATE PROCEDURE GenerateOrderData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 50000 DO
INSERT INTO orders_no_index (user_id, amount, order_date, status)
VALUES (FLOOR(RAND() 100000) + 1, RAND() 1000,
DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY),
'completed');
INSERT INTO orders_with_index (user_id, amount, order_date, status)
VALUES (FLOOR(RAND() 100000) + 1, RAND() 1000,
DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY),
'completed');
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL GenerateOrderData();</div>
<h3>Confronto delle operazioni JOIN</h3>
<p><strong>JOIN senza indici:</strong></p>
<div class="code">-- JOIN senza indici
EXPLAIN
SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount
FROM users_no_index u
JOIN orders_no_index o ON u.id = o.user_id
WHERE u.age BETWEEN 25 AND 35
<p><strong>Risultati EXPLAIN:</strong></p>
<ul>
<li>Per entrambe le tabelle: type: ALL</li>
<li>rows: 100000 * 50000 = 5.000.000.000 confronti potenziali</li>
<li>Extra: Using where; Using temporary; Using filesort</li>
</ul>
<p><strong>Tempo di esecuzione:</strong> ~4500 ms</p>
<p><strong>JOIN con indici:</strong></p>
<div class="code">-- JOIN con indici
EXPLAIN
SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount
FROM users_with_index u
JOIN orders_with_index o ON u.id = o.user_id
WHERE u.age BETWEEN 25 AND 35
<p><strong>Risultati EXPLAIN:</strong></p>
<ul>
<li>Per users: type: range (utilizza l'indice age)</li>
<li>Per orders: type: ref (utilizza l'indice user_id)</li>
<li>rows: significativamente meno</li>
<li>Uso più efficiente delle tabelle temporanee</li>
</ul>
<p><strong>Tempo di esecuzione:</strong> ~150 ms</p>
<p><strong>Conclusioni sulle operazioni JOIN</strong></p>
<ul>
<li><strong>Senza indici:</strong> MySQL è costretto a eseguire cicli annidati con scansioni complete delle tabelle</li>
<li><strong>Con indici:</strong> MySQL utilizza efficacemente gli indici per trovare rapidamente i record correlati</li>
<li><strong>Differenza di prestazioni:</strong> 30 volte o più</li>
</ul>
<h3>Quando utilizzare gli indici?</h3>
<p><strong>Si consiglia di creare indici per:</strong></p>
<ul>
<li>Campi utilizzati frequentemente nelle condizioni WHERE</li>
<li>Campi coinvolti nelle operazioni JOIN</li>
<li>Campi utilizzati per l'ordinamento (ORDER BY)</li>
<li>Campi utilizzati in GROUP BY</li>
<li>Campi univoci o chiavi primarie</li>
</ul>
<p><strong>Quando gli indici potrebbero essere inefficienti</strong>:</p>
<ul>
<li>Tabelle con frequenti operazioni INSERT/UPDATE/DELETE</li>
<li>Tabelle piccole (meno di 1000 righe)</li>
<li>Colonne con bassa selettività (pochi valori unici)</li>
</ul>
<h3>Migliori pratiche per lavorare con gli indici</h3>
<ul>
<li><strong>Indicizzare consapevolmente</strong> — ogni indice rallenta le operazioni di scrittura</li>
<li><strong>Utilizzare indici composti</strong> per combinazioni di campi utilizzate frequentemente</li>
<li><strong>Monitorare la selettività</strong> — gli indici su campi con pochi valori unici sono meno efficaci</li>
<li><strong>Analizzare regolarmente l'utilizzo degli indici</strong>:</li>
</ul>
<div class="code">-- Analizzare l'utilizzo degli indici
SELECT * FROM sys.schema_unused_indexes;</div>
<p>5. <strong>Ottimizzare gli indici esistenti</strong>:</p>
<div class="code">-- Analizzare le prestazioni delle query
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age = 25;</div>
<div class="code">-- Verificare la frammentazione degli indici
ANALYZE TABLE users_with_index;</div>
<h2>Conclusione</h2>
<p>Gli indici sono uno strumento potente per ottimizzare le prestazioni di MySQL. Un uso corretto può accelerare l'esecuzione delle query di decine di volte, specialmente per le operazioni di filtro e JOIN. Tuttavia, è importante ricordare l'equilibrio — un'indicizzazione eccessiva può rallentare le operazioni di scrittura. Il monitoraggio regolare e l'analisi delle prestazioni aiuteranno a trovare la configurazione ottimale degli indici per la propria applicazione.</p>
<p>Testate con i vostri dati, poiché l'efficacia degli indici dipende fortemente dalle caratteristiche specifiche dei vostri dati e dai modelli di query nella vostra applicazione.</p>