Quelle est la différence entre utf8_general_ci et utf8_unicode_ci ?

Question

Entre utf8_general_ci et utf8_unicode_ci Y a-t-il des différences en termes de performances ?

Answer 1

Il existe au moins deux différences importantes :

• Précision de triage

  utf8_unicode_ci est basé sur la norme Unicode pour le tri, et trie avec précision dans un très large éventail de langues.

  utf8_general_ci est très proche du tri Unicode correct dans de nombreuses langues courantes, mais présente un certain nombre d'inexactitudes dans certaines langues, ce qui le rend impropre à un tri correct dans ces langues.

• Performance

  utf8_general_ci est plus rapide pour les comparaisons et le tri, parce qu'il prend un tas de raccourcis liés aux performances.

  utf8_unicode_ci utilise un algorithme de comparaison beaucoup plus complexe qui vise à assurer un tri correct dans un très large éventail de langues. Cela rend plus lent le tri et la comparaison d'un grand nombre de champs.

Unicode définit des ensembles complexes de règles sur la façon dont les caractères doivent être triés. Ces règles doivent tenir compte des conventions propres à chaque langue ; tout le monde ne trie pas ses caractères dans ce que nous appellerions "l'ordre alphabétique".

• En ce qui concerne les langues latines (c'est-à-dire "européennes"), il n'y a pas beaucoup de différence entre le tri Unicode et le tri simplifié utf8_general_ci dans MySQL, mais il y a quand même quelques différences :

  Par exemple, la collation Unicode trie "ß" comme "ss", et "Œ" comme "OE" comme le voudraient normalement les personnes utilisant ces caractères, alors que utf8_general_ci les trie comme des caractères uniques (vraisemblablement comme "s" et "e" respectivement).

• Dans les langues non-latines, telles que les langues asiatiques ou les langues avec des alphabets différents, il peut y avoir beaucoup de plus les différences entre le tri Unicode et le tri simplifié utf8_general_ci. L'adéquation de utf8_general_ci dépendra fortement de la langue utilisée. Pour certaines langues, il sera tout à fait inadéquat.

Certains caractères Unicode sont définis comme ignorables, ce qui signifie qu'ils ne doivent pas compter dans l'ordre de tri et que la comparaison doit passer au caractère suivant. utf8_unicode_ci les gère correctement.

Que devez-vous utiliser ?

Il n'y a presque plus de raison d'utiliser utf_general_ci, car nous avons laissé derrière nous le point où la vitesse du CPU est suffisamment basse pour que la différence de performance soit importante. Votre base de données sera presque certainement limitée par d'autres goulots d'étranglement que celui-ci de nos jours. La différence de performance ne sera mesurable que dans des situations extrêmement spécialisées, et si c'est votre cas, vous le savez déjà. Si vous êtes confronté à un tri lent, dans la plupart des cas, il s'agit d'un problème avec vos index/plan de requête. La modification de votre fonction de collation ne devrait pas figurer en tête de liste des problèmes à résoudre.

Lorsque j'ai écrit cette réponse à l'origine (il y a plus de 4 ans), j'ai dit que si vous le souhaitiez, vous pouviez utiliser utf8_general_ci la plupart du temps, et n'utiliser utf8_unicode_ci que lorsque le tri était suffisamment important pour justifier le coût des performances. Cependant, le coût des performances n'est plus vraiment pertinent (et il ne l'était peut-être pas non plus à l'époque). Il est plus important de trier correctement, quelle que soit la langue utilisée par vos utilisateurs.

J'ajouterai que, même si vous savez que votre application ne prend en charge que la langue anglaise, elle peut avoir à traiter des noms de personnes, qui contiennent souvent des caractères utilisés dans d'autres langues et qu'il est tout aussi important de trier correctement. L'utilisation des règles d'Unicode pour tout permet d'avoir la certitude que les personnes très intelligentes d'Unicode ont travaillé très dur pour que le tri fonctionne correctement.

Answer 2

Je voulais savoir quelle est la différence de performance entre l'utilisation de utf8_general_ci et utf8_unicode_ci, mais je n'ai pas trouvé de benchmarks sur Internet, alors j'ai décidé de faire des benchmarks moi-même.

J'ai créé un tableau très simple de 500 000 lignes :

CREATE TABLE test(
  ID INT(11) DEFAULT NULL,
  Description VARCHAR(20) DEFAULT NULL
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

Puis je l'ai rempli de données aléatoires en exécutant cette procédure stockée :

CREATE PROCEDURE randomizer()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE random CHAR(20) ;

  theloop: loop
    SET random = CONV(FLOOR(RAND() * 99999999999999), 20, 36);

    INSERT INTO test VALUES (i+1, random);

    SET i=i+1;

    IF i = 500000 THEN
      LEAVE theloop;
    END IF;

  END LOOP theloop;
END

J'ai ensuite créé les procédures stockées suivantes pour effectuer des tests de référence (SELECT simple, SELECT avec LIKE, et tri (SELECT avec ORDER BY) :

CREATE benchmark_simple_select()
BEGIN
  DECLARE i INT DEFAULT 0;

  theloop: loop

    SELECT * FROM test WHERE Description = 'test' COLLATE utf8_general_ci;

    SET i = i + 1;

    IF i = 30 THEN
      LEAVE theloop;
      END IF;

  END LOOP theloop;

END

CREATE PROCEDURE benchmark_select_like()
BEGIN
  DECLARE i INT DEFAULT 0;

  theloop: loop

    SELECT * FROM test WHERE Description LIKE '%test' COLLATE utf8_general_ci;

    SET i = i + 1;

    IF i = 30 THEN
      LEAVE theloop;
      END IF;

  END LOOP theloop;

END

CREATE PROCEDURE benchmark_order_by()
BEGIN
  DECLARE i INT DEFAULT 0;

  theloop: loop

    SELECT * FROM test WHERE ID > FLOOR(1 + RAND() * (400000 - 1)) ORDER BY Description COLLATE utf8_general_ci LIMIT 1000;

    SET i = i + 1;

    IF i = 10 THEN
      LEAVE theloop;
      END IF;

  END LOOP theloop;

END

Dans les procédures stockées ci-dessus, la collation utf8_general_ci est utilisée, mais bien sûr, pendant les tests, j'ai utilisé à la fois utf8_general_ci et utf8_unicode_ci.

J'ai appelé chaque procédure stockée 5 fois pour chaque collation (5 fois pour utf8_general_ci et 5 fois pour utf8_unicode_ci) et j'ai ensuite calculé les valeurs moyennes.

Voici les résultats :

benchmark_simple_select() avec utf8_general_ci : 9957 ms
benchmark_simple_select() avec utf8_unicode_ci : 10271 ms
Dans ce benchmark, l'utilisation de utf8_unicode_ci est plus lente que utf8_general_ci de 3,2%.

benchmark_select_like() avec utf8_general_ci : 11441 ms
benchmark_select_like() avec utf8_unicode_ci : 12811 ms
Dans ce benchmark, l'utilisation de utf8_unicode_ci est plus lente que utf8_general_ci de 12%.

benchmark_order_by() avec utf8_general_ci : 11944 ms
benchmark_order_by() avec utf8_unicode_ci : 12887 ms
Dans ce benchmark, l'utilisation de utf8_unicode_ci est plus lente que utf8_general_ci de 7,9%.

Answer 3

Ce poste le décrit très bien.

En résumé, utf8_unicode_ci utilise l'algorithme de collation Unicode tel que défini dans les normes Unicode, tandis que utf8_general_ci est un ordre de tri plus simple qui donne des résultats de tri "moins précis".

Answer 4

Voir le manuel mysql, Jeux de caractères Unicode section :

Pour tout jeu de caractères Unicode, les opérations effectuées en utilisant la _general_ci sont plus rapides que celles effectuées avec la collation _unicode_ci. Par exemple, les comparaisons pour la collation utf8_general_ci sont plus rapides, mais légèrement moins correctes que que les comparaisons pour utf8_unicode_ci. Le site raison en est que utf8_unicode_ci supporte des mappings tels que comme les expansions ; c'est-à-dire que lorsqu'on est comparé comme égal à combinaisons d'autres caractères. Pour Par exemple, en allemand et dans certaines autres langues, "ß" est égal à "ss". utf8_unicode_ci supporte également les les contractions et les caractères ignorables. utf8_general_ci est une ancienne collation qui ne prend pas en charge les expansions, contractions ou les caractères ignorables. Elle ne peut effectuer que des comparaisons biunivoques entre les caractères.

Donc, pour résumer, utf_general_ci utilise un ensemble de comparaisons plus petit et moins correct (selon la norme) que utf_unicode_ci qui devrait mettre en œuvre l'ensemble de la norme. L'ensemble general_ci sera plus rapide car il y a moins de calculs à faire.

Answer 5

Il existe des tableaux pour rassembler les caractéristiques : http://collation-charts.org/mysql60/mysql604.utf8_general_ci.european.html et http://collation-charts.org/mysql60/mysql604.utf8_unicode_ci.european.html .

Pour enregistrer des valeurs comme 'é' et 'e' dans une colonne unique, vous devez définir sa collation à 'ut8_bin' pour éviter les erreurs de duplication.

Je ne vois pas vraiment d'avantages à utiliser 'utf8_unicode_ci' dans l'usage quotidien.