Jaka jest różnica między utf8_general_ci i utf8_unicode_ci?

Pomiędzy utf8_general_cii utf8_unicode_ciczy są jakieś różnice pod względem wydajności?

Te dwa zestawienia dotyczą kodowania znaków UTF-8. Różnice polegają na sposobie sortowania i porównywania tekstu.

Uwaga: W MySQL musisz utf8mb4raczej używać niż utf8. Myląco utf8jest wadliwą implementacją UTF-8 z wczesnych wersji MySQL, która pozostaje tylko dla kompatybilności wstecznej. Naprawiona wersja otrzymała nazwę utf8mb4.

Uwaga: Nowsze wersje MySQL mają zaktualizowane reguły sortowania Unicode, dostępne pod nazwami, na przykład utf8mb4_0900_ai_ci dla równoważnych reguł opartych na Unicode 9.0 - i bez równoważnego _general wariantu. Osoby czytające to teraz powinny prawdopodobnie użyć jednego z tych nowszych zestawień zamiast jednego _unicode lub _general . Wiele z tego, co napisano poniżej, nie jest już tak interesujące, jeśli zamiast tego możesz użyć jednego z nowszych zestawień.

Kluczowe różnice

• utf8mb4_unicode_ci opiera się na oficjalnych regułach Unicode dotyczących uniwersalnego sortowania i porównywania, które dokładnie sortują w szerokim zakresie języków.

• utf8mb4_general_cito uproszczony zestaw reguł sortowania, który ma na celu jak najlepiej wykonać, przy jednoczesnym zastosowaniu wielu skrótów mających na celu poprawę prędkości. Nie jest zgodny z zasadami Unicode i spowoduje niepożądane sortowanie lub porównanie w niektórych sytuacjach, na przykład podczas używania określonych języków lub znaków.

  Na nowoczesnych serwerach ten wzrost wydajności będzie prawie znikomy. Został on opracowany w czasach, gdy serwery miały niewielki ułamek wydajności procesora dzisiejszych komputerów.

Korzyści z utf8mb4_unicode_ciponadutf8mb4_general_ci

utf8mb4_unicode_ci, który korzysta z reguł Unicode do sortowania i porównywania, stosuje dość złożony algorytm do poprawnego sortowania w szerokim zakresie języków i przy użyciu szerokiej gamy znaków specjalnych. Zasady te muszą uwzględniać konwencje specyficzne dla języka; nie wszyscy sortują swoje postacie w tak zwanym „porządku alfabetycznym”.

Jeśli chodzi o języki łacińskie (tj. „Europejskie”), nie ma dużej różnicy między sortowaniem Unicode a uproszczonym utf8mb4_general_cisortowaniem w MySQL, ale wciąż istnieje kilka różnic:

• Na przykład sortowanie w Unicode sortuje „ß” jak „ss”, a „Œ” jak „OE” tak, jak ludzie używający tych znaków normalnie by tego chcieli, a utf8mb4_general_cisortuje je jako pojedyncze znaki (przypuszczalnie odpowiednio „s” i „e”) .

• Niektóre znaki Unicode są zdefiniowane jako ignorowalne, co oznacza, że ​​nie powinny się liczyć w kolejności sortowania, a porównanie powinno przejść do następnego znaku. utf8mb4_unicode_ciradzi sobie z nimi poprawnie.

W językach innych niż łacińskie, takich jak języki azjatyckie lub języki z różnymi alfabetami, może występować znacznie więcej różnic między sortowaniem w Unicode a utf8mb4_general_cisortowaniem uproszczonym . Przydatność utf8mb4_general_cizależy w dużej mierze od użytego języka. W przypadku niektórych języków będzie to dość nieodpowiednie.

Czego powinieneś użyć?

Niemal na pewno nie ma już powodu, aby z niego korzystać utf8mb4_general_ci, ponieważ pozostawiliśmy za sobą punkt, w którym szybkość procesora jest na tyle niska, że ​​różnica w wydajności byłaby ważna. Twoja baza danych prawie na pewno będzie ograniczona innymi wąskimi gardłami.

W przeszłości niektóre osoby zalecały stosowanie, utf8mb4_general_ciz wyjątkiem przypadków, gdy dokładne sortowanie miało być na tyle ważne, aby uzasadnić koszt wydajności. Dzisiaj ten koszt wydajności prawie zniknął, a programiści poważniej podchodzą do internacjonalizacji.

Trzeba wysunąć argument, że jeśli prędkość jest dla Ciebie ważniejsza niż dokładność, równie dobrze możesz w ogóle nie dokonywać sortowania. Usprawnienie algorytmu jest trywialne, jeśli nie jest potrzebny, aby był dokładny. Tak, utf8mb4_general_cijest to kompromis, który chyba nie potrzebne ze względów prędkości i prawdopodobnie również nie nadaje się ze względu na dokładność.

Dodam jeszcze jedną rzecz, że nawet jeśli wiesz, że aplikacja obsługuje tylko język angielski, może nadal wymagać rozpoznawania nazwisk osób, które często mogą zawierać znaki używane w innych językach, w których równie ważne jest prawidłowe sortowanie . Używanie reguł Unicode do wszystkiego pomaga dodać spokój, że bardzo inteligentni ludzie Unicode pracowali bardzo ciężko, aby sortowanie działało poprawnie.

Co oznaczają części

Po pierwsze, cisłuży do sortowania i porównywania bez rozróżniania wielkości liter . Oznacza to, że nadaje się do danych tekstowych, a wielkość liter nie ma znaczenia. Inne typy zestawiania są cs(z rozróżnianiem wielkości liter) dla danych tekstowych bin, w których wielkość liter jest ważna, i tam , gdzie kodowanie musi się zgadzać, bit po bicie, co jest odpowiednie dla pól, które są naprawdę zakodowanymi danymi binarnymi (w tym na przykład Base64). Sortowanie z rozróżnianiem wielkości liter prowadzi do dziwnych wyników, a porównywanie z rozróżnianiem wielkości liter może powodować, że zduplikowane wartości różnią się tylko wielkością liter, więc sortowanie z rozróżnianiem wielkości liter nie jest korzystne dla danych tekstowych - jeśli wielkość liter jest dla Ciebie ważna, to w przeciwnym razie ignorowana interpunkcja i tak dalej jest prawdopodobnie również znaczący, a sortowanie binarne może być bardziej odpowiednie.

Dalej unicodelub generalodnosi się do konkretnych zasad sortowania i porównywania - w szczególności sposobu normalizacji lub porównania tekstu. Istnieje wiele różnych zestawów reguł dla kodowania znaków utf8mb4, ze unicodei generalbędąc dwa, że próba pracy dobrze we wszystkich możliwych językach niż jeden specyficzny. Różnice między tymi dwoma zestawami reguł są przedmiotem tej odpowiedzi. Zauważ, że unicodeużywa reguł z Unicode 4.0. Najnowsze wersje MySQL dodają zestawy reguł unicode_520przy użyciu reguł z Unicode 5.2 i 0900(upuszczając część „Unicode_”) przy użyciu reguł z Unicode 9.0.

I na koniec, utf8mb4oczywiście kodowanie znaków jest używane wewnętrznie. W tej odpowiedzi mówię tylko o kodowaniu opartym na Unicode.

Chciałem wiedzieć, jaka jest różnica w wydajności między używaniem utf8_general_cii utf8_unicode_ci, ale nie znalazłem żadnych testów porównawczych w Internecie, więc postanowiłem sam je stworzyć.

Stworzyłem bardzo prostą tabelę z 500 000 wierszy:

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

Następnie wypełniłem je losowymi danymi, uruchamiając tę ​​procedurę składowaną:

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

Następnie utworzyłem następujące procedury składowane, aby przeprowadzić proste testy porównawcze SELECT, SELECTz LIKEi sortowanie ( SELECTz ORDER BY):

CREATE PROCEDURE 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;

W procedurach przechowywanych stosuje się powyższe utf8_general_cizestawienie, ale oczywiście podczas testów użyłem zarówno utf8_general_cii utf8_unicode_ci.

Każdą procedurę przechowywaną wywołałem 5 razy dla każdego zestawienia (5 razy dla utf8_general_cii 5 razy dla utf8_unicode_ci), a następnie obliczyłem wartości średnie.

Moje wyniki to:

benchmark_simple_select()

• z utf8_general_ci: 9,957 ms
• z utf8_unicode_ci: 10 271 ms

W tym teście używanie utf8_unicode_cijest wolniejsze niż utf8_general_cio 3,2%.

benchmark_select_like()

• z utf8_general_ci: 11441 ms
• z utf8_unicode_ci: 12,811 ms

W tym teście używanie utf8_unicode_cijest wolniejsze niż utf8_general_cio 12%.

benchmark_order_by()

• z utf8_general_ci: 11944 ms
• z utf8_unicode_ci: 12887 ms

W tym teście używanie utf8_unicode_cijest wolniejsze niż utf8_general_cio 7,9%.

Ten post opisuje to bardzo ładnie.

W skrócie: utf8_unicode_ci używa algorytmu sortowania Unicode zdefiniowanego w standardach Unicode, podczas gdy utf8_general_ci jest prostszym porządkiem sortowania, co powoduje „mniej dokładne” wyniki sortowania.

Zobacz instrukcję mysql, sekcja Zestawy znaków Unicode :

W przypadku dowolnego zestawu znaków Unicode operacje wykonywane przy użyciu sortowania _general_ci są szybsze niż w przypadku sortowania _unicode_ci. Na przykład porównania dla zestawienia utf8_general_ci są szybsze, ale nieco mniej poprawne, niż porównania dla utf8_unicode_ci. Powodem tego jest to, że utf8_unicode_ci obsługuje mapowania, takie jak rozszerzenia; to znaczy, gdy jeden znak porównuje się jako równy kombinacjom innych znaków. Na przykład w języku niemieckim i niektórych innych językach „ß” jest równe „ss”. utf8_unicode_ci obsługuje również skurcze i znaki ignorowalne. utf8_general_ci to starsze zestawienie, które nie obsługuje rozszerzeń, skurczów ani ignorowalnych znaków. Może dokonywać tylko porównań między postaciami.

Podsumowując, utf_general_ci używa mniejszego i mniej poprawnego (zgodnie ze standardem) zestawu porównań niż utf_unicode_ci, który powinien implementować cały standard. Zestaw general_ci będzie szybszy, ponieważ jest mniej obliczeń do zrobienia.

Krótko mówiąc:

Jeśli potrzebujesz lepszego porządku sortowania - użyj utf8_unicode_ci(jest to preferowana metoda),

ale jeśli jesteś całkowicie zainteresowany wydajnością - użyj utf8_general_ci, ale wiedz, że jest trochę przestarzała.

Różnice pod względem wydajności są bardzo niewielkie.

Niektóre szczegóły (PL)

Jak możemy przeczytać tutaj ( Peter Gulutzan ), istnieje różnica w sortowaniu / porównywaniu polskiej litery „Ł” (L z pociągnięciem - html esc:) Ł(małe litery: „ł” - html esc:) ł- przyjmujemy następujące założenie:

utf8_polish_ci      Ł greater than L and less than M
utf8_unicode_ci     Ł greater than L and less than M
utf8_unicode_520_ci Ł equal to L
utf8_general_ci     Ł greater than Z

W języku polskim litera Łjest po literze Li przed nią M. Żadne z tych kodów nie jest lepsze ani gorsze - zależy to od twoich potrzeb.

Istnieją dwie duże różnice w sortowaniu i dopasowywaniu znaków:

Sortowanie :

• utf8mb4_general_ci usuwa wszystkie akcenty i sortuje jeden po drugim, co może powodować niepoprawne wyniki sortowania.
• utf8mb4_unicode_ci sortuje dokładne.

Dopasowanie postaci

Pasują do znaków inaczej.

Na przykład w utf8mb4_unicode_citobie masz i != ı, ale w utf8mb4_general_cinim się trzyma ı=i.

Wyobraź sobie na przykład, że masz wiersz name="Yılmaz". Następnie

select id from users where name='Yilmaz';

zwróci wiersz, jeśli kolokacja jest utf8mb4_general_ci, ale jeśli zostanie skolokowany utf8mb4_unicode_ci, nie zwróci wiersza!

Z drugiej strony mamy, że a=ªi ß=ssw utf8mb4_unicode_ciktórej nie jest w przypadku utf8mb4_general_ci. Więc wyobraź sobie, że masz wiersz z name="ªßi", a następnie

select id from users where name='assi';

zwróci wiersz, jeśli kolokacja jest utf8mb4_unicode_ci, ale nie zwróci wiersza, jeśli kolokacja jest ustawiona na utf8mb4_general_ci.

Pełna lista dopasowań dla każdej kolokacji znajduje się tutaj .

Zgodnie z tym postem, MySQL 5.7 ma znacznie wyższą wydajność w porównaniu z utf8mb4_general_ci zamiast utf8mb4_unicode_ci: https://www.percona.com/blog/2019/02/27/charset-and-collation-settings-impact -on-mysql-performance /