Classements de bases de données contrôlent la manière dont MySQL compare et trie les chaînes de caractères, et elles influencent directement la charge CPU, l'utilisation des index et les E/S. Si je choisis un classement lent ou que je mélange les paramètres, les requêtes s'allongent, des conversions sont nécessaires et des erreurs „ Illegal mix “ peuvent survenir.
Points centraux
- Jeu de caractères/classement: Les combinaisons incorrectes forcent les conversions et ralentissent le processus.
- Indices: La fonction « Case-Insensitive » réduit la sélectivité, tandis que « Case-Sensitive » accélère les correspondances.
- Unicode: utf8mb4 est plus précis, mais coûte plus de CPU.
- Consistance: Les paramètres uniformes empêchent le tri des fichiers et les analyses complètes.
- Tuning: Combiner la sélection de collationnement avec la mémoire, le regroupement et la conception de requêtes.
Que sont les collations et pourquoi elles peuvent nuire ou améliorer les performances ?
J'utilise Collations, pour définir des règles de comparaison et de tri pour les chaînes. Elles sont associées au jeu de caractères de la base de données qui détermine le codage des caractères, par exemple utf8mb4 ou latin1. Si je choisis un classement Unicode plus précis, tel que utf8mb4_unicode_ci, les coûts de calcul par comparaison augmentent. Dans les mesures effectuées avec MySQL 8.0, les charges de travail OLTP avec les nouveaux classements Unicode étaient parfois 10 à 16 fois plus lentes, mais les comparaisons pour les langues et les emojis étaient plus précises (source [2]). Pour les charges de travail purement axées sur la vitesse, des règles simples telles que utf8_general_ci sont efficaces, mais elles fournissent des résultats moins précis (source [2]).
Charset vs. Collation : petites différences, grands effets
Le Jeu de caractères détermine comment MySQL stocke les octets, tandis que le classement détermine comment MySQL compare ces octets. Si je mélange les classements dans les JOIN ou les conditions WHERE, MySQL convertit à la volée, ce qui est nettement plus coûteux pour les grandes tables (source [2]). Cela coûte du CPU, génère des tables temporaires et peut entraîner un tri de fichiers sur le disque. C'est pourquoi je veille à ce que le niveau de l'application, la base de données, les tables et les colonnes soient strictement uniformes. Pour une optimisation plus large, j'intègre le thème du classement dans mes mesures visant à Optimiser la base de données SQL un.
Versions et paramètres par défaut : ce qui a changé entre les versions 5.7 et 8.0
Lors de la mise à niveau, je fais attention à Défauts: MySQL 8.0 utilise par défaut utf8mb4 et dans de nombreuses versions sur utf8mb4_0900_ai_ci. Les installations plus anciennes utilisent souvent latin1_swedish_ci ou utf8_general_ci. Le changement modifie non seulement le codage, mais aussi l'ordre de tri et les règles d'égalité. Cela conduit à ce que ORDER BY-Les résultats sont différents, UNIQUE-Les index entrent à nouveau en collision ou des doublons qui étaient auparavant „ identiques “ apparaissent soudainement (ou inversement). Je planifie donc les mises à niveau de manière à vérifier au préalable : SELECT @@character_set_server, @@collation_server, @@collation_database; et je définis délibérément les valeurs par défaut dans le système cible. Je teste en même temps comment utf8mb4_0900_ai_ci en face de utf8mb4_unicode_ci dans mes requêtes réelles, car les variantes 0900 (basées sur ICU) comportent souvent des règles plus précises, mais plus coûteuses (source [2]).
Index et plans de requête : où les collations ralentissent
Les collations contrôlent les Utilisation de l'index avec. La recherche insensible à la casse (_ci) élargit la recherche, mais réduit la sélectivité : l'optimiseur utilise alors moins souvent l'index. La recherche sensible à la casse (_cs) accélère les correspondances exactes, mais ne convient pas à toutes les exigences. Si une colonne change de classement, les règles de comparaison changent et donc le plan – le tri de fichiers apparaît plus fréquemment, parfois avec des tables temporaires (source [1], [3]). Je donne plus d'informations sur l'effet de l'index dans „Indices : avantages et risques“.
Erreurs fréquentes et solutions directes
Le message Mélange illégal indique presque toujours des collations mixtes. Je résous ce problème à court terme avec COLLATE dans la requête, et à long terme, j'uniformise les colonnes. En cas de tri de fichiers et de latences élevées, je vérifie les colonnes ORDER BY et j'adapte la collation à la définition de l'index (source [3]). Pour les JOIN avec des colonnes TEXT/VARCHAR, je veille à ce que les collations soient identiques, sinon les conversions obligent l'optimiseur à utiliser de mauvais plans. La cohérence apporte souvent des gains immédiats et mesurables en millisecondes.
La hiérarchie MySQL : du serveur à l'impression
MySQL connaît les collations sur cinq niveaux: serveur, base de données, tableau, colonne, impression. Le niveau le plus bas l'emporte, c'est pourquoi les divergences peuvent entraîner des surprises. Je vérifie les paramètres avec « SELECT SCHEMA_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA », « SHOW TABLE STATUS » et « SHOW FULL COLUMNS ». Si une requête `col1 COLLATE utf8mb4_unicode_ci = col2` rencontre des collations de colonnes différentes, la comparaison prend du temps (source [1]). Avant d'effectuer des modifications, je crée des sauvegardes et teste le recodage en phase de test afin d'éviter toute distorsion des données.
Paramètres de connexion et de session : où apparaissent les bugs
De nombreux problèmes ne surviennent pas dans le schéma, mais dans la Session. Je vérifie les variables character_set_client, character_set_connection, character_set_results et collation_connection. Les ORM utilisent en partie SET NAMES et remplacent ainsi les paramètres par défaut du serveur ; les déploiements mixtes entraînent des conversions „ invisibles “. Je m'en tiens à des règles claires : l'application envoie UTF-8 (utf8mb4), la connexion définit SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci ; ou le laisse définir par l'option du pilote. Pour le débogage, j'utilise AFFICHER LES VARIABLES DE TYPE ' collation% ' ; et SELECT COLLATION(colonne), COERCIBILITY(colonne) respectivement COLLATION('littéral'). Si les valeurs divergent, je trouve généralement rapidement la cause des tables temporaires et des erreurs d'inadéquation (source [1]).
Insensible à la casse ou sensible à la casse : quand choisir l'une ou l'autre option ?
Avec _ci j'ignore la casse, ce qui améliore la convivialité. En revanche, la sélectivité diminue et les recherches LIKE accèdent moins souvent aux index de manière propre. Avec _cs Je fais des comparaisons précises, j'obtiens des requêtes de points plus rapides, mais je perds en commodité. Pour les identifiants, les jetons ou les ID, j'utilise _cs, pour les champs de recherche, j'utilise souvent _ci. Je sépare clairement les deux afin d'éviter les abus et les conversions.
Subtilités : règles d'accentuation, de largeur et binaires (_ai, _as, _bin)
Je fais plus que simplement distinguer la casse. _ai (insensible à l'accent) traite „ é “ et „ e “ comme identiques ; _as (sensible à l'accent) les distingue. Dans les langues d'Asie orientale, la Largeur un rouleau (pleine largeur/demi-largeur), tandis que _bin effectue des comparaisons d'octets purs – c'est le plus rapide, mais sans logique linguistique. Pour les journaux, les hachages et les identifiants, j'utilise _bin ou _cs, pour les recherches fréquentes des utilisateurs _ai, afin que les fautes de frappe et les accents n'aient pas d'importance. Je teste délibérément des exemples : SELECT ' rue ' = ' rue ' COLLATE utf8mb4_0900_ai_ci ; fournit TRUE, alors que ... COLLATE utf8mb4_0900_as_cs ; FAUX . Ces règles déterminent le nombre de lignes comprises dans un balayage d'index, et donc la latence et les E/S.
Bien lire les benchmarks : la précision coûte cher au CPU
Collations Unicode telles que utf8mb4_unicode_ci et utf8mb4_0900_ai_ci couvrent correctement les langues, les caractères diacritiques et les emojis. La logique de comparaison est plus complexe, ce qui coûte plus de CPU par comparaison. Dans les scénarios OLTP avec de nombreuses comparaisons de chaînes, les mesures montrent des durées d'exécution plus longues de 10 à 16 %, en fonction de la charge de travail et de la taille de l'ensemble de données (source [2]). Les petites tables sont moins affectées, tandis que les recherches et les tris étendus le sont davantage. Je décide au cas par cas et tiens compte des exigences des utilisateurs.
Taille de l'index, limites du préfixe et espace mémoire requis
Avec utf8mb4 Je planifie délibérément la largeur de l'index, car un caractère peut occuper jusqu'à 4 octets. InnoDB limite la longueur des clés d'index (historiquement 767 octets, dans les versions plus récentes et les formats de ligne, effectivement jusqu'à 3072 octets). Cela a un impact sur VARCHAR-colonnes, index composites et index couvrants. Je vérifie donc : 191 caractères (191×4≈764 octets) sont-ils suffisants pour les e-mails ou les URL ? Dans les configurations 5.7, c'était souvent le choix sûr, mais dans la version 8.0, je peux souvent aller jusqu'à 255, tant que les index composites ne dépassent pas les limites. Si nécessaire, je définis Index préfixés: CREATE INDEX idx_email ON users(email(191)); Cela permet de gagner de la place, mais réduit la sélectivité ; je mesure l'effet avec EXPLAIN ANALYZE et le journal des requêtes lentes (source [3]). Les clés plus volumineuses gonflent également le pool de tampons : chaque octet supplémentaire augmente la pression sur le cache et les E/S. Les décisions de collationnement ont donc un impact sur les coûts de stockage.
Optimisation de l'hébergement : penser ensemble collation, tampon et mise en commun
Je relance la innodb_buffer_pool_size, afin que les index et les données chaudes restent en mémoire. Grâce au pooling de connexions, je réduis la surcharge par requête, et les couches proxy diminuent les pics. Pour les formats de fichiers, la taille du redo log et la taille de page, j'adapte la charge de travail cible. De plus, je choisis délibérément le moteur de stockage ; un coup d'œil à InnoDB vs MyISAM montre les différences typiques en matière de transactions, de verrous et de sécurité en cas de plantage. Sans collations cohérentes, une partie de ce réglage est perdue.
Meilleures pratiques : sélection en fonction du scénario d'utilisation
Pour les applications web modernes, j'utilise utf8mb4 comme jeu de caractères, car il prend en charge les emojis et offre une couverture Unicode complète. Si j'ai besoin d'une précision maximale pour le tri dans plusieurs langues, j'utilise utf8mb4_unicode_ci ou utf8mb4_0900_ai_ci. Pour une vitesse pure dans les comparaisons simples, utf8_general_ci est souvent plus rapide, mais accepte les imprécisions (source [2]). Je maintiens une stratégie de collation cohérente au niveau du serveur, du schéma, des tables et des colonnes. Des tests avec EXPLAIN ANALYZE et Slow-Query-Log confirment cette décision (source [3]).
| Collation | Précision | Vitesse | Prise en charge des emojis | Convient pour |
|---|---|---|---|---|
| utf8_general_ci | Faible | Haute | Non | Recherches rapides |
| utf8_unicode_ci | Haute | Moyens | Non | Applications Unicode |
| utf8mb4_unicode_ci | Très élevé | Faible | Oui | Web moderne |
| utf8mb4_0900_ai_ci | Le plus haut niveau | Moyens | Oui | Multilingue |
Étape par étape : transition sans interruption
Je commence avec Inventaire: Quels schémas, tables et colonnes utilisent quelles collations ? Ensuite, je sauvegarde les données, j'exporte les tables critiques et je crée des répétitions dans Staging. La conversion s'effectue avec `ALTER TABLE … CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;`, en commençant par les tables peu utilisées. Pour les grandes tables, je planifie des fenêtres de maintenance ou j'utilise des outils de migration en ligne tels que Percona Toolkit (source [1], [2]). Après la conversion, je vérifie EXPLAIN, le journal des requêtes lentes et je compare les latences.
Diagnostic : poser les bonnes questions à la base de données
Je vérifie SCHÉMAS et « SHOW FULL COLUMNS » pour mettre en évidence les divergences. En cas de tri de fichiers et de tables temporaires, je n'augmente pas aveuglément la taille du tampon de tri (sort_buffer_size), mais je supprime la discordance de collation. Avec EXPLAIN, je peux voir si un index est utilisé ou si des analyses complètes ont lieu. Avec Performance Schema, je mesure tmp_disk_tables et sort_merge_passes afin d'identifier les E/S liées au tri. Je trouve ainsi les goulots d'étranglement qui proviennent directement des comparaisons de chaînes (source [3]).
GROUP BY, DISTINCT et UNIQUE : conséquences sémantiques du classement
Les collations définissent quand les valeurs sont considérées comme „ identiques “. Cela affecte Déduplication et Règles d'unicité. Je passe de _cs à l'adresse suivante : _ci ou de _as à l'adresse suivante : _ai, un UNIQUE-Index signalent soudainement des collisions. Avant toute migration, je recherche les conflits potentiels : SELECT col, COUNT(*) FROM t GROUP BY col COLLATE utf8mb4_0900_ai_ci HAVING COUNT(*) > 1;. Je vois ainsi quelles lignes coïncident dans le classement cible. Je tiens également compte de cela dans GROUP BY et DISTINCT: Le nombre de groupes dépend des règles, et donc aussi du plan (plus ou moins d'efforts de tri/hachage). Pour les tableaux de rapport, un classement délibérément „ grossier “ peut être utile, car il génère moins de groupes ; pour les identifiants de caisse et les identifiants de connexion, cela peut être risqué.
Modèles de conception : binaire, colonnes générées et index fonctionnels
Je sépare Présentation et Recherche: La colonne visible reste dans un classement „ agréable “ (par exemple. utf8mb4_0900_ai_ci), j'ajoute une colonne générée normalisée pour des comparaisons performantes, par exemple en minuscules et en binaire. Exemple : ALTER TABLE user ADD name_search VARCHAR(255) GENERATED ALWAYS AS (LOWER(name)) STORED, ADD INDEX idx_name_search (name_search); Avec un _bin- ou bien _cs-Collation sur recherche_par_nom j'obtiens des correspondances exactes et rapides pour WHERE nom_recherche = LOWER(?). Dans MySQL 8.0, je peux également utiliser la Collation dans l'index Indiquer : CREATE INDEX idx_name_ai ON user (name COLLATE utf8mb4_0900_ai_ci) ; Ainsi, la colonne reste par exemple. _cs, alors que l'indice est délibérément _ai – pratique pour les recherches „ floues “ sans analyse complète. Je documente ces modèles dans le schéma afin que le générateur de requêtes de l'application utilise la bonne colonne ou le bon index.
LIKE, préfixes et texte intégral : ce qui accélère vraiment
À l'adresse suivante : LIKELes règles normales de classement s'appliquent aux recherches. Un caractère générique en tête (LIKE 'c') empêche l'utilisation d'index, quelle que soit la qualité du classement choisi. Je reformule donc les modèles de recherche de manière à utiliser des préfixes (LIKE ' abc% ') et veille à la compatibilité de collation afin que MySQL n'effectue pas de conversion intermédiaire. Pour les textes libres volumineux, j'utilise TEXTE INTÉGRAL-Indices ; la tokenisation est largement indépendante du classement, mais le codage des caractères et la normalisation influencent les résultats. Dans les environnements CJK, les analyseurs NGRAM sont utiles ; dans les langues occidentales, j'évite les classements trop „ grossiers “ afin que le stemming/les mots vides ne mélangent pas trop les résultats. Ici aussi, la cohérence du champ à la connexion évite les tables temporaires et le tri des fichiers (source [3]).
Pratique : maintenir la rapidité de WordPress, des boutiques et des API
Les systèmes de contenu et de boutique en ligne bénéficient des avantages suivants utf8mb4_unicode_ci, car les slugs, les catégories et le contenu utilisateur sont triés proprement. Je veille à ce que les plugins ne créent pas de collations divergentes. Dans les API et les chemins d'authentification, j'utilise _cs pour les jetons afin de garantir des correspondances exactes via l'index. Pour les rapports avec ORDER BY sur de grands champs de texte, je combine la cohérence de la collation et des index de couverture appropriés. En complément, je consulte les conseils de Optimiser la base de données SQL sur.
Résumé concis
Je choisis Collations Conscient : la vitesse, la précision et les attentes des utilisateurs déterminent la décision. Des paramètres uniformes empêchent les conversions, le tri des fichiers et les plans inefficaces. Les variantes Unicode fournissent de meilleurs résultats, mais coûtent plus cher en termes de CPU ; les mesures effectuées avec MySQL 8.0 montrent des pertes de 10 à 16 % pour les charges de travail intensives sur les chaînes de caractères (source [2]). Grâce à une conception de schéma claire, des index, un pool de tampons et un regroupement, l'instance MySQL s'adapte de manière fiable. Une vérification, un test et une consolidation systématiques permettent de réduire la latence et d'augmenter sensiblement les performances de collationnement MySQL.
