Collazioni database controllano il modo in cui MySQL confronta e ordina le stringhe di caratteri e influenzano direttamente il carico della CPU, l'utilizzo dell'indice e l'I/O. Se scelgo una collazione lenta o mescolo le impostazioni, le query si allungano, si verificano conversioni e si rischiano errori di „mix illegale“.
Punti centrali
- Charset/Collazione: Combinazioni errate forzano le conversioni e rallentano il processo.
- Indici: L'opzione "Case-Insensitive" riduce la selettività, mentre l'opzione "Case-Sensitive" accelera le corrispondenze.
- Unicode: utf8mb4 è più preciso, ma richiede più CPU.
- Coerenza: Le impostazioni uniformi impediscono il filesort e le scansioni complete.
- Sintonizzazione: combinare la selezione della collazione con la memoria, il pooling e la progettazione delle query.
Cosa sono le collazioni e perché incidono sulle prestazioni
Uso Collazioni, per definire regole di confronto e ordinamento per le stringhe. Sono collegate al charset del database che determina la codifica dei caratteri, ad esempio utf8mb4 o latin1. Se scelgo una collazione Unicode più precisa come utf8mb4_unicode_ci, i costi di calcolo per ogni confronto aumentano. Nelle misurazioni con MySQL 8.0, i carichi di lavoro OLTP con i nuovi collation Unicode sono risultati in parte più lenti di 10-16 %, ma i confronti per le lingue e gli emoji sono più accurati (fonte [2]). Per i carichi di lavoro puramente orientati alla velocità, sono sufficienti regole semplici come utf8_general_ci, ma forniscono risultati meno precisi (fonte [2]).
Charset vs. Collation: piccole differenze, grande effetto
Il sito Charset determina il modo in cui MySQL memorizza i byte, mentre la collazione decide come MySQL confronta questi byte. Se mescolo collazioni in JOIN o condizioni WHERE, MySQL converte al volo, il che è notevolmente più costoso con tabelle di grandi dimensioni (fonte [2]). Ciò richiede CPU, genera tabelle temporanee e può portare a un ordinamento dei file su disco. Per questo motivo mantengo rigorosamente uniformi il livello dell'app, il database, le tabelle e le colonne. Per un'ottimizzazione più ampia, includo il tema della collazione nelle mie misure per Ottimizzare il database SQL in.
Versioni e impostazioni predefinite: cosa è cambiato tra la versione 5.7 e la versione 8.0
Durante l'aggiornamento faccio attenzione a Impostazioni predefinite: MySQL 8.0 utilizza come impostazione predefinita utf8mb4 e in molte build su utf8mb4_0900_ai_ci. Le installazioni più vecchie utilizzano spesso latin1_swedish_ci oppure utf8_general_ci. La modifica non cambia solo la codifica, ma anche l'ordine di ordinamento e le regole di uguaglianza. Ciò comporta che ORDER BY-I risultati sono diversi, UNICO-Gli indici entrano nuovamente in conflitto o improvvisamente compaiono duplicati che prima erano „uguali“ (o viceversa). Pertanto, pianifico gli aggiornamenti in modo da verificare in anticipo: SELECT @@character_set_server, @@collation_server, @@collation_database; e imposto consapevolmente i valori predefiniti nel sistema di destinazione. Allo stesso tempo, verifico come utf8mb4_0900_ai_ci di fronte a utf8mb4_unicode_ci nelle mie query reali, perché le varianti 0900 (basate su ICU) spesso comportano regole più precise ma più costose (fonte [2]).
Indici e piani di query: dove i collation rallentano
Le collazioni controllano la Utilizzo dell'indice con. Case-insensitive (_ci) amplia la ricerca, ma riduce la selettività: l'ottimizzatore ricorre meno spesso all'indice. Case-sensitive (_cs) accelera le corrispondenze esatte, ma non è adatto a tutte le esigenze. Se una colonna cambia la collazione, cambiano le regole di confronto e quindi il piano: il filesort compare più spesso, in parte con tabelle temporanee (fonte [1], [3]). Maggiori informazioni sull'effetto dell'indice sono disponibili in „Indici: vantaggi e rischi“ ab.
Errori frequenti e soluzioni dirette
Il messaggio Mix illegale indica quasi sempre collazioni miste. Risolvo il problema a breve termine con COLLATE nella query, mentre a lungo termine uniformino le colonne. Se si verificano filesort e latenze elevate, controllo le colonne ORDER BY e adatto la collazione alla definizione dell'indice (fonte [3]). Nei JOIN con colonne TEXT/VARCHAR, faccio attenzione che le collazioni siano identiche, altrimenti le conversioni costringono l'ottimizzatore a piani scadenti. La coerenza spesso porta a guadagni immediatamente misurabili in millisecondi.
La gerarchia MySQL: dal server alla stampa
MySQL riconosce i collation su cinque livelli: server, database, tabella, colonna, espressione. Il livello più basso prevale, quindi eventuali discrepanze possono causare sorprese. Controllo le impostazioni con `SELECT SCHEMA_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA`, `SHOW TABLE STATUS` e `SHOW FULL COLUMNS`. Se una query colpisce `col1 COLLATE utf8mb4_unicode_ci = col2`, i diversi collation delle colonne valutano il confronto, il che richiede tempo (fonte [1]). Prima di apportare modifiche, eseguo backup e provo la ricodifica in staging per evitare distorsioni dei dati.
Impostazioni di connessione e sessione: dove si verificano i bug
Molti problemi non si verificano nello schema, ma nella Sessione. Controllo le variabili character_set_client, character_set_connection, risultati_set_caratteri e collation_connection. Gli ORM utilizzano in parte SET NAMES e sovrascrivono così le impostazioni predefinite del server; le distribuzioni miste portano a conversioni „invisibili“. Mi attengo a regole chiare: l'app invia UTF-8 (utf8mb4), la connessione imposta SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci; oppure lo imposta tramite l'opzione del driver. Per il debug utilizzo MOSTRA VARIABILI COME 'collation%'; e SELECT COLLATION(colonna), COERCIBILITY(colonna) rispettivamente COLLATION('letterale'). Se i valori differiscono, di solito riesco a individuare rapidamente la causa delle tabelle temporanee e degli errori di mancata corrispondenza (fonte [1]).
Maiuscolo/minuscolo: quando scegliere l'una o l'altra opzione
Con _ci Ignoro le maiuscole/minuscole, il che aumenta la facilità d'uso. In cambio, la selettività diminuisce e le ricerche LIKE accedono meno frequentemente agli indici in modo pulito. Con _cs Effettuo confronti precisi, ottengo risposte più rapide, ma perdo in termini di praticità. Per login, token o ID utilizzo _cs, per i campi di ricerca spesso _ci. Tengo entrambi ben separati per evitare abusi e conversioni.
Sottigliezze: regole relative all'accento, alla larghezza e al binario (_ai, _as, _bin)
Distinguo più della semplice distinzione tra maiuscole e minuscole. _ai (insensibile all'accento) tratta „é“ ed „e“ come uguali; _as (accent-sensitive) le distingue. Nelle lingue dell'Asia orientale gioca inoltre un ruolo importante la Larghezza un rotolo (larghezza intera/mezza larghezza), mentre _bin esegue semplici confronti di byte: è il metodo più veloce, ma privo di logica linguistica. Per log, hash e ID utilizzo _bin oppure _cs, per le ricerche degli utenti spesso _ai, in modo che errori di battitura e accenti non abbiano importanza. Testo consapevolmente alcuni esempi: SELECT 'straße' = 'strasse' COLLATE utf8mb4_0900_ai_ci; forniture VERO, mentre ... COLLATE utf8mb4_0900_as_cs; FALSE . Tali regole determinano il numero di righe comprese in una scansione dell'intervallo dell'indice e quindi la latenza e l'I/O.
Leggere correttamente i benchmark: la precisione costa CPU
Collazioni Unicode come utf8mb4_unicode_ci e utf8mb4_0900_ai_ci coprono correttamente lingue, segni diacritici ed emoji. La logica di confronto è più complessa, il che comporta un maggiore consumo di CPU per ogni confronto. Negli scenari OLTP con molti confronti di stringhe, le misurazioni mostrano tempi di esecuzione più lunghi di 10-16 %, a seconda del carico di lavoro e delle dimensioni del set di dati (fonte [2]). Le tabelle di piccole dimensioni ne risentono meno, mentre le ricerche e gli ordinamenti di ampia portata ne risentono maggiormente. Decido in base al caso specifico e tengo conto delle esigenze degli utenti.
Dimensione dell'indice, limiti del prefisso e requisiti di memoria
Con utf8mb4 Pianifico consapevolmente la larghezza dell'indice, poiché un carattere può occupare fino a 4 byte. InnoDB limita la lunghezza delle chiavi dell'indice (storicamente 767 byte, nelle versioni più recenti e nei formati di riga effettivamente fino a 3072 byte). Ciò influisce su VARCHAR-colonne, indici compositi e indici di copertura. Verifico quindi: 191 caratteri (191×4≈764 byte) sono sufficienti per e-mail o URL? Nelle configurazioni 5.7 questa era spesso la scelta più sicura, nella 8.0 posso spesso arrivare a 255, purché gli indici compositi non siano eccessivi. Se necessario, imposto Indici prefisso: CREATE INDEX idx_email ON users(email(191)); Questo consente di risparmiare spazio, ma riduce la selettività; misuro l'effetto con SPIEGAZIONE ANALISI e il log delle query lente (fonte [3]). Le chiavi più grandi inoltre ingombrano il buffer pool: per ogni byte aggiuntivo aumentano la pressione sulla cache e l'I/O – le decisioni di collazione hanno quindi un impatto sui costi di memoria.
Ottimizzazione dell'hosting: considerare insieme collazione, buffer e pooling
Aumento la innodb_buffer_pool_size, in modo che gli indici e i dati più utilizzati rimangano in memoria. Con il connection pooling riduco il sovraccarico per ogni richiesta e i livelli proxy riducono i picchi. Per i formati di file, la dimensione del redo log e la dimensione della pagina, adatto il carico di lavoro target. Inoltre, scelgo consapevolmente il motore di archiviazione; uno sguardo a InnoDB vs. MyISAM mostra differenze tipiche nelle transazioni, nei blocchi e nella sicurezza contro i crash. Senza collazioni coerenti, parte di questa ottimizzazione va persa.
Best practice: selezione in base allo scenario di utilizzo
Per le moderne applicazioni web utilizzo utf8mb4 come set di caratteri, perché offre emoji e copertura Unicode completa. Se ho bisogno della massima precisione per l'ordinamento in più lingue, utilizzo utf8mb4_unicode_ci o utf8mb4_0900_ai_ci. Per la pura velocità nei confronti semplici, utf8_general_ci è spesso più veloce, ma accetta imprecisioni (fonte [2]). Mantengo la strategia di collazione coerente a livello di server, schema, tabella e colonna. I test con EXPLAIN ANALYZE e Slow-Query-Log confermano la decisione (fonte [3]).
| Collazione | Precisione | Velocità | Supporto Emoji | Adatto per |
|---|---|---|---|---|
| utf8_general_ci | Basso | Alto | No | Ricerca rapida |
| utf8_unicode_ci | Alto | Medio | No | App Unicode |
| utf8mb4_unicode_ci | Molto alto | Basso | Sì | Web moderno |
| utf8mb4_0900_ai_ci | Il più alto | Medio | Sì | Multilingua |
Passo dopo passo: conversione senza interruzioni
Inizio con Inventario: Quali schemi, tabelle e colonne utilizzano quali collazioni? Successivamente, eseguo il backup dei dati, esporto le tabelle critiche e creo delle prove in staging. La conversione viene eseguita con `ALTER TABLE … CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;`, iniziando dalle tabelle meno utilizzate. Per le tabelle di grandi dimensioni pianifico finestre di manutenzione o utilizzo strumenti di migrazione online come Percona Toolkit (fonte [1], [2]). Dopo la conversione, controllo EXPLAIN, il log delle query lente e confronto le latenze.
Diagnosi: le domande giuste da porre al database
Controllo SCHEMI e `SHOW FULL COLUMNS` per rendere visibili le discrepanze. Se si verificano filesort e tabelle temporanee, non aumento ciecamente sort_buffer_size, ma elimino il collation mismatch. Con EXPLAIN vedo se un indice è efficace o se vengono eseguite scansioni complete. Con Performance Schema misuro tmp_disk_tables e sort_merge_passes per rilevare gli I/O relativi all'ordinamento. In questo modo trovo i colli di bottiglia che derivano direttamente dai confronti tra stringhe (fonte [3]).
GROUP BY, DISTINCT e UNIQUE: conseguenze semantiche della collazione
Le collazioni definiscono quando i valori sono considerati „uguali“. Ciò influisce su Deduplicazione e Regole di unicità. Passo da _cs all'indirizzo _ci o da _as all'indirizzo _ai, può essere un UNICO-Index segnalano improvvisamente delle collisioni. Prima delle migrazioni cerco potenziali conflitti: SELECT col, COUNT(*) FROM t GROUP BY col COLLATE utf8mb4_0900_ai_ci HAVING COUNT(*) > 1;. In questo modo vedo quali righe coincidono nella collazione di destinazione. Lo noto anche con GRUPPO PER e DISTINCT: Il numero di gruppi dipende dal regolamento e quindi anche dal piano (più o meno sforzo di ordinamento/hash). Per le tabelle dei report può essere utile un collation volutamente „approssimativo“ che genera meno gruppi; nel caso di ID di cassa e login è rischioso.
Modelli di progettazione: binario, colonne generate e indici funzionali
Io mi separo Rappresentazione e Ricerca: La colonna visibile rimane in una collazione „bella“ (ad es. utf8mb4_0900_ai_ci), aggiungo un colonna generata normalizzata per confronti performanti, ad esempio in minuscolo e binario. Esempio: ALTER TABLE user ADD name_search VARCHAR(255) GENERATED ALWAYS AS (LOWER(name)) STORED, ADD INDEX idx_name_search (name_search); Con un _bin- o _cs-Collazione su ricerca_nome ottengo corrispondenze precise e veloci su WHERE nome_ricerca = LOWER(?). In MySQL 8.0 posso anche utilizzare la Collazione nell'indice indicare: CREATE INDEX idx_name_ai ON user (name COLLATE utf8mb4_0900_ai_ci); In questo modo, ad esempio, la colonna rimane. _cs, mentre l'indice è stato volutamente _ai utilizza – pratico per ricerche „fuzzy“ senza scansione completa. Documento questi modelli nello schema in modo che il generatore di query dell'app utilizzi la colonna o l'indice corretto.
LIKE, prefissi e testo completo: cosa accelera davvero
All'indirizzo PIACERE-Le ricerche sono soggette alle normali regole di collazione. Un carattere jolly iniziale (LIKE 'c') impedisce l'utilizzo dell'indice, indipendentemente dalla qualità della collazione scelta. Modifico quindi i modelli di ricerca in modo da utilizzare i prefissi (MI PIACE 'abc%') e presta attenzione alla compatibilità della collazione, in modo che MySQL non effettui conversioni intermedie. Per testi liberi di grandi dimensioni utilizzo TESTO COMPLETO-Indici; la tokenizzazione è in gran parte indipendente dalla collazione, ma la codifica dei caratteri e la normalizzazione influenzano i risultati. Negli ambienti CJK sono utili i parser NGRAM; nelle lingue occidentali evito collazioni troppo „approssimative“, in modo che lo stemming/stopwords non creino troppa confusione. Anche in questo caso vale la regola: la coerenza dal campo alla connessione evita tabelle temporanee e filesort (fonte [3]).
Pratica: mantenere veloci WordPress, negozi online e API
I sistemi di contenuti e shop traggono vantaggio da utf8mb4_unicode_ci, perché ordinano in modo pulito slug, categorie e contenuti utente. Mi assicuro che i plugin non creino collazioni divergenti. Nelle API e nei percorsi di autenticazione, imposto _cs per i token per garantire corrispondenze esatte tramite l'indice. Per i report con ORDER BY su campi di testo di grandi dimensioni, combino la coerenza della collazione e gli indici di copertura appropriati. Inoltre, per aumentare la velocità di trasmissione, seguo i consigli di Ottimizzare il database SQL in funzione.
Riassunto compatto
Scelgo Collazioni Consapevole: velocità, precisione e aspettative degli utenti determinano la decisione. Impostazioni uniformi impediscono conversioni, ordinamenti di file e piani inefficienti. Le varianti Unicode forniscono risultati migliori, ma richiedono più CPU; misurazioni con MySQL 8.0 mostrano perdite di 10-16 % in caso di carichi di lavoro intensivi con stringhe (fonte [2]). Con un design dello schema pulito, indici, buffer pool e pooling, l'istanza MySQL è in grado di scalare in modo affidabile. Chi esegue controlli, test e consolidamenti sistematici riduce la latenza e aumenta notevolmente le prestazioni di collazione di MySQL.
