Vai al contenuto 
MySQL diventa lento quando le query sono costruite male, gli indici sono mancanti, la configurazione non è adeguata o le risorse sono scarse: è proprio qui che inizio a ottimizzare le prestazioni di mysql efficacemente. Vi mostrerò i passaggi diagnostici specifici e le soluzioni pratiche per individuare le cause reali ed eliminare i colli di bottiglia in modo mirato.
Punti centrali
- Domande e progettare correttamente gli indici
- Configurazione Adattarsi al carico di lavoro
- Risorse Monitoraggio e scala
- Monitoraggio e utilizzare i registri lenti
- Manutenzione e aggiornamenti del piano
Perché MySQL è lento: Riconoscere le cause
Per prima cosa distinguo tra problemi di query, mancanza di Indicierrori di configurazione e limiti di risorse. SELECT inefficienti, catene di JOIN selvagge e SELECT * aumentano la quantità di dati e prolungano il tempo di esecuzione. Senza indici adeguati, MySQL deve eseguire la scansione di tabelle di grandi dimensioni, il che rallenta notevolmente la situazione in caso di traffico intenso. Un innodb_buffer_pool_size troppo piccolo costringe il sistema a leggere costantemente dal disco, aumentando la latenza. Inoltre, le versioni obsolete o la cache delle query attivata nelle versioni più recenti rallentano il funzionamento di innodb_buffer_pool. Prestazioni non necessario.
Controllare rapidamente: Sintomi e valori misurati
Inizio con il log delle query lente, lo schema delle prestazioni e le metriche di sistema per identificare i problemi principali. Freni si può notare. Una CPU elevata con un I/O basso indica spesso query o indici mancanti. Molti IOPS con una CPU bassa indicano una dimensione del buffer pool troppo piccola o dati frammentati. Un valore elevato di Handler_read_rnd_next indica frequenti scansioni complete della tabella. Anche l'aumento delle latenze durante i picchi di carico rivela colli di bottiglia nei thread, nelle connessioni o nello storage.
Conoscere i blocchi, le transazioni e l'isolamento
Mi occupo subito dei blocchi perché anche gli indici perfetti non sono molto utili se le sessioni si bloccano a vicenda. Le transazioni lunghe mantengono le vecchie versioni nel log degli annullamenti, aumentano la pressione sul pool di buffer ed estendono Tempi di attesa per il blocco. Controllo i deadlock (SHOW ENGINE INNODB STATUS), i tempi di attesa e gli oggetti interessati nello schema delle prestazioni (data_locks, data_lock_waits). Gli schemi tipici sono indici mancanti sulle colonne JOIN (blocchi ad ampio raggio), sequenza di accesso incoerente su più tabelle o grandi lotti di UPDATE/DELETE senza LIMIT.
Scelgo il livello di isolamento in modo appropriato: READ COMMITTED riduce i gap lock e può attenuare gli hotspot, mentre REPEATABLE READ offre snapshot più sicuri. Per i lavori di manutenzione, utilizzo pacchetti di transazioni più piccoli in modo che il Group Commit abbia effetto e i lock rimangano brevi. Dove possibile, uso NOWAIT o SKIP LOCKED per i lavori in background per evitare di rimanere bloccati nelle code. Ho deliberatamente impostato i tempi di attesa dei lock (innodb_lock_wait_timeout) in modo che l'applicazione riconosca rapidamente gli errori e possa riprovare in modo pulito.
Leggere e utilizzare correttamente EXPLAIN
Con EXPLAIN riconosco il modo in cui MySQL esegue la query e se un risultato significativo è stato ottenuto. Percorso di accesso esiste. Presto attenzione al tipo (ad esempio, ALL o ref), alla chiave, alle righe e ad altri elementi, come l'uso di filesort o l'uso di temporanei. Ogni riga senza indice è un candidato per la messa a punto. Quindi controllo le condizioni WHERE, JOIN e ORDER e creo gli indici adatti. La seguente piccola matrice mi aiuta a classificare più rapidamente i segnali tipici e a ricavare le contromisure.
| Segnale | Causa probabile | Strumento/Controllo | Azione rapida |
|---|---|---|---|
| tipo = TUTTI | Scansione completa della tabella | SPIEGARE, Log lento | Indice su colonne WHERE/JOIN |
| Utilizzo di filesort | Ordinamento senza indice di corrispondenza | SPIEGARE Extra | Indice su ordine ORDER BY |
| Utilizzo di un sistema temporaneo | Tabella intermedia per GROUP BY | SPIEGARE Extra | Indice combinato, semplificare l'aggregazione |
| Valore elevato delle righe | Filtro troppo tardivo/troppo sfocato | Spiega le righe | Ordine WHERE e indice più selettivo |
| Handler_read_rnd_next alto | Molte scansioni sequenziali | MOSTRA STATO | Aggiungere indici, riscrivere la query |
Stabilizzare i piani: Statistiche, istogrammi e suggerimenti
Assicuro una buona pianificazione mantenendo le statistiche aggiornate e modellando in modo realistico la selettività. ANALYZE TABLE aggiorna le statistiche di InnoDB; per i dati fortemente distorti, creo istogrammi per le colonne critiche in modo che l'ottimizzatore possa stimare meglio le cardinalità. Se il piano salta tra gli indici, controllo le statistiche persistenti, aggiorno gli istogrammi in modo specifico o li rimuovo se sono dannosi. In casi eccezionali, imposto suggerimenti all'ottimizzatore (ad esempio, USE INDEX, JOIN_ORDER) o rendo inizialmente invisibile un indice per verificarne gli effetti senza rischi. Uso EXPLAIN ANALYZE per vedere i tempi di esecuzione reali a livello di operatore e scoprire errori di valutazione.
Accelerare le interrogazioni: passi concreti
Per prima cosa riduco la quantità di dati: solo le colonne necessarie, filtri WHERE chiari, filtri significativi. LIMITE. Quindi semplifico le sottoquery annidate o le sostituisco con JOIN con indici adeguati. Dove possibile, sposto le funzioni costose sulle colonne in WHERE in campi precalcolati. Divido i report più frequenti in query più piccole con cache a livello di applicazione. Per un'introduzione compatta ai metodi, rimando a queste pagine Strategie MySQLche raggruppano in modo strutturato proprio questi passaggi.
Pratica con gli ORM e il livello applicativo
Disinnesco le tipiche trappole ORM: Riconosco le query N+1 tramite voci di registro lente raggruppate e le sostituisco con JOIN esplicite o funzioni di caricamento batch. Sostituisco le SELECT * con proiezioni snelle. Costruisco la paginazione come metodo di ricerca (WHERE id > last_id ORDER BY id LIMIT n) invece di grandi OFFSET, che diventano sempre più lenti all'aumentare dell'offset. Uso i prepared statement e la cache dei piani di query in modo che il parser lavori meno. Configuro i pool di connessioni in modo che non inondino il database con migliaia di connessioni inattive e non spingano l'applicazione in coda; imposto timeout rigidi per porre fine ai blocchi in anticipo.
Indici: creare, controllare, riordinare
Imposto gli indici specificamente sulle colonne che compaiono in WHERE, JOIN e ORDER BY e faccio attenzione ai valori di Sequenza. Scelgo gli indici compositi in base alla selettività e al piano di utilizzo delle query più frequenti. Evito la sovraindicizzazione perché ogni indice aggiuntivo rallenta le operazioni di scrittura. Identifico gli indici inutilizzati tramite le statistiche di utilizzo e li rimuovo dopo averli testati. Per i campi TEXT o JSON, controllo gli indici parziali o funzionali se la versione li supporta.
Progettazione di schemi, chiavi primarie e formati di memorizzazione
Penso già alle prestazioni nel modello dei dati: InnoDB memorizza fisicamente i dati in base alla chiave primaria (indice clusterizzato). Le chiavi monotone (AUTO_INCREMENT, ULID con time share) evitano la suddivisione delle pagine e riducono la frammentazione. Le chiavi UUIDv4 pure disperdono la casualità nel B-tree e peggiorano la località della cache; se ho bisogno di UUID, uso varianti con componenti ordinabili o le memorizzo in forma binaria (UUID_TO_BIN) per indici più compatti. Scelgo tipi di dati piccoli e adeguati (INT vs. BIGINT, DECIMAL vs. FLOAT per i soldi) per risparmiare RAM e I/O. Per l'Unicode, scelgo utf8mb4 con una collazione pragmatica (ad esempio, _0900_ai_ci) e verifico se si desidera effettuare confronti senza distinzione tra maiuscole e minuscole.
Il formato delle righe (DINAMICO) aiuta a utilizzare in modo efficiente lo storage fuori pagina; se necessario, divido le righe molto ampie in tabelle di dettaglio sottili e fredde. Per JSON, imposto colonne generate (virtuali/persistenti) e le indicizzo in modo specifico, invece di ripetere la logica di ricerca non strutturata in ogni query. La compressione è utile per le tabelle molto grandi, se la CPU è disponibile; misuro l'equilibrio tra i costi di decompressione e i risparmi di I/O sull'hardware di destinazione.
Configurazione personalizzata: InnoDB e altro
Di solito imposto innodb_buffer_pool_size a 50-70 % di RAM in modo che le frequenti Dati nella memoria. Regolo innodb_log_file_size in base al carico di scrittura e agli obiettivi di recupero. Uso innodb_flush_log_at_trx_commit per controllare la durata rispetto alla latenza, a seconda del rischio accettato. Regolo i parametri dei thread e delle connessioni in modo che non ci siano code. Disattivo costantemente la cache delle query obsolete nelle versioni attuali.
Rendere più efficiente il carico di scrittura
Ho raggruppato le scritture in transazioni controllate, invece di fare l'autocommitting a ogni INSERT. Questo riduce i fsync e consente i commit di gruppo. Per i dati in massa, uso metodi di massa (elenco multiplo di VALUES o LOAD DATA), sovrascrivo temporaneamente i controlli delle chiavi esterne e gli indici secondari se l'integrità lo consente, e poi li ricostruisco. Scelgo deliberatamente i parametri di binlog: il formato ROW è più stabile per la replica, sync_binlog controlla la durata; in combinazione con innodb_flush_log_at_trx_commit trovo un compromesso accettabile tra sicurezza e throughput. Controllo anche innodb_io_capacity(_max), in modo che i thread di flush non soffochino l'I/O né lo rallentino.
Risorse e hardware: quando scalare?
Prima di aggiungerne di nuovi, verifico se la messa a punto del software è stata esaurita. Hardware comprare. Se le ottimizzazioni non sono sufficienti, scaliamo la RAM, utilizziamo lo storage SSD/NVMe e aumentiamo i core della CPU per il parallelismo. Misuro separatamente la latenza di rete e il throughput dello storage per scegliere la giusta vite di regolazione. Per i picchi di carico più elevati, pianifico una riduzione orizzontale tramite repliche. Questo fornisce una buona panoramica per gli scenari più impegnativi Guida per carichi elevatiche mi piace usare come lista di controllo.
Funzionamento nel cloud: IOPS, crediti e limiti
Tengo conto delle specificità del cloud: lo storage a blocchi legato alla rete ha IOPS e throughput limitati, che controllo e riservo. I tipi di istanza con crediti CPU si bloccano sotto carico continuo; scelgo classi di prestazioni costanti per i database produttivi. I burst buffer dei volumi si nascondono solo a breve termine; gli IOPS/throughput previsti sono obbligatori per prestazioni prevedibili. Misuro il jitter della latenza e pianifico lo spazio di manovra in modo che i checkpoint e i backup non finiscano nelle zone rosse. Dal lato del sistema operativo, controllo le impostazioni del file system e dello scheduler, NUMA e le pagine enormi trasparenti in modo che InnoDB possa funzionare in modo coerente.
Stabilire un monitoraggio permanente
Utilizzo uno schema delle prestazioni, metriche relative al sistema e un sistema centralizzato di Cruscotto per individuare le tendenze. Eseguo continuamente il log delle query lente e raggruppo le query simili. Gli allarmi per latenza, interruzioni, numero di connessioni e picchi di I/O segnalano i problemi in anticipo. Le curve storiche mi mostrano se una modifica ha realmente migliorato le prestazioni. Senza il monitoraggio, la messa a punto rimane un'istantanea e perde il suo effetto con il nuovo codice.
Test, rollout e protezione dalla regressione
Non applico mai le modifiche "alla cieca": prima misuro la linea di base, poi regolo una vite di regolazione in modo isolato e misuro di nuovo. Per gli scenari reali, utilizzo snapshot di dati di produzione (anonimizzati) e generatori di carico che mappano i carichi di lavoro tipici. Il replay delle query aiuta a vedere gli effetti sui piani e sulle latenze. Quando eseguo il roll-out, mi affido ai canaries e ai feature flags, in modo da poter tornare indietro immediatamente in caso di problemi. Per le modifiche allo schema, utilizzo procedure online (ad esempio con strumenti collaudati), monitoro i ritardi di replica e ho un chiaro piano di rollback. I checksum tra il primario e le repliche garantiscono la coerenza dei dati.
Utilizzare correttamente il partizionamento e la cache
Suddivido le tabelle molto grandi per data o chiave per facilitare la scansione e la manutenzione. alleviare. Conservo i dati caldi in partizioni più piccole e memorizzo i dati freddi in aree di memoria a cui si accede meno frequentemente. A livello di applicazione, riduco le query ripetute con cache in-memory. Se ne vale la pena, memorizzo le aggregazioni frequenti come viste materializzate o tabelle precompilate. Integro una panoramica strutturata di strategie per carichi elevati con modelli collaudati nelle operazioni quotidiane.
Decisioni architettoniche per la crescita
Alleggerisco gli accessi in scrittura attraverso la replica con gli slave di lettura per i report e le API che richiedono un sacco di Leggi. Lo sharding per gruppi di clienti o regioni può essere utile per le applicazioni globali. Sposto i lavori batch su worker asincroni invece di abusare di MySQL come coda. Separo le tabelle critiche con schemi di accesso diversi per evitare gli hotspot. Per esigenze estreme, controllo forme di archiviazione specializzate per alcuni tipi di dati.
Ottimizzazione della replica in dettaglio
Mantengo stabile la replica utilizzando i GTID, regolando correttamente le dimensioni del binlog e le strategie di flush e attivando la parallelizzazione sulle repliche. Aumento i replica_parallel_workers (o i thread di applicazione) nella misura in cui il carico di lavoro consente transazioni indipendenti. La replica semisincrona può ridurre la perdita di dati, ma aumenta la latenza - lo decido in base allo SLA e alla velocità di scrittura. Monitoro il ritardo della replica perché altrimenti i carichi di lavoro in lettura vedono dati obsoleti; per "leggere le scritture" instrado temporaneamente le sessioni di scrittura sul primario o uso finestre di ritardo nella logica dell'applicazione. Pianifico DDL lunghi in modo che binlog e le repliche non rimangano indietro.
Manutenzione e aggiornamenti
Mantengo aggiornata la versione di MySQL e i plugin per poter Errore ed evitare i vecchi freni. Rimuovo le tabelle inutilizzate dopo la chiarificazione per snellire le statistiche e i backup. Gli archivi o i rollup conservano solo le cronologie rilevanti, in modo che le scansioni rimangano veloci. ANALYZE/OPTIMIZE regolari su tabelle selezionate mi aiutano a tenere sotto controllo le statistiche e la frammentazione. Raccolgo altri consigli pratici in questi compatti Suggerimenti per l'SQL per la vita di tutti i giorni.
Riassumendo brevemente
Trovo i colli di bottiglia facendo delle interrogazioni, Indiciconfigurazione e risorse insieme. EXPLAIN, i log lenti e il monitoraggio mi forniscono dati affidabili invece di una sensazione di pancia. Piccoli accorgimenti come la rimozione di SELECT *, l'impostazione di indici combinati o di un pool di buffer più ampio producono rapidamente effetti evidenti. Decido quindi se sono necessarie modifiche all'hardware o all'architettura. Se procedete in questo modo, potrete velocizzare il vostro database MySQL e mantenerlo in funzione senza problemi.
