MySQL — Cheat Sheet

Tag: mysql, database, sql, rdbms, backend

Livello: intermedio

TL;DR: Sintesi rapida su architettura, indici, ottimizzazione delle query e comandi essenziali in MySQL.

Concetti chiave

Architettura client-server multi-thread.

Storage Engine predefinito: InnoDB (supporta transazioni ACID, chiavi esterne, row-level locking).

MyISAM è obsoleto: privo di transazioni e usa table-level locking.

Indici B-Tree: struttura dati primaria per accelerare le ricerche e l'ordinamento.

Normalizzazione: processo per ridurre la ridondanza dei dati (1NF, 2NF, 3NF).

ACID: Atomicity, Consistency, Isolation, Durability garantite da log di redo e undo.

Isolation Levels: Repeatable Read è il default, previene phantom reads con i next-key locks.

Domande tipiche e risposte pronte

Q: Qual è la differenza tra DELETE, TRUNCATE e DROP?R: Uso DELETE per rimuovere righe specifiche, e posso farne il rollback. TRUNCATE svuota la tabella resettando l'auto-increment (non transazionale). DROP elimina l'intera struttura.

Q: Come ottimizzi una query lenta?R: Utilizzo EXPLAIN per analizzare il piano di esecuzione, verifico se c'è una full table scan e aggiungo indici mirati. Evito SELECT *.

Q: Cos'è il problema N+1 e come lo risolvi?R: Accade quando eseguo una query principale e N query per i record correlati. Lo risolvo usando le JOIN o l'eager loading dell'ORM.

Q: Qual è la differenza tra INNER JOIN e LEFT JOIN?R: L'INNER JOIN restituisce solo i record corrispondenti in entrambe le tabelle. Il LEFT JOIN restituisce tutti i record a sinistra, usando NULL a destra se mancano.

Insidie e errori comuni

Usare SELECT * in produzione: consuma memoria e larghezza di banda inutilmente.

Mancanza di indici sulle colonne usate frequentemente in WHERE, JOIN o ORDER BY.

Creare troppi indici: rallentano drasticamente le operazioni di INSERT, UPDATE e DELETE.

Ignorare il character set: è raccomandato usare utf8mb4 per supportare interamente lo standard Unicode.

Concatenare stringhe per le query invece di usare i prepared statements (rischio SQL Injection).

Snippet e comandiSQL

```-- Creare un database con character set corretto

CREATE DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Aggiungere un indice a una colonna esistente

ALTER TABLE users ADD INDEX idx_email (email);

-- Analizzare il piano di esecuzione di una query

EXPLAIN SELECT id, name FROM users WHERE status = 'active';

-- Gestione transazionale sicura

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

-- In caso di errore: ROLLBACK;

```