В чем разница между utf8_general_ci и utf8_unicode_ci?
Есть ли различия в производительности между utf8_general_ci и utf8_unicode_ci ?
8 ответов
Для тех, кто все еще задает этот вопрос в 2020 году или позже, есть более новые варианты, которые могут быть лучше, чем оба из них. Например, utf8mb4_0900_ai_ci .
Все эти сопоставления предназначены для кодировки символов UTF-8. Различия заключаются в том, как текст сортируется и сравнивается.
_unicode_ci и _general_ci — это два разных набора правил для сортировки и сравнения текста в соответствии с нашими ожиданиями. В новых версиях MySQL также представлены новые наборы правил, такие как _0900_ai_ci для эквивалентных правил, основанных на Unicode 9.0 — и без эквивалентного варианта _general_ci . Люди, читающие это сейчас, вероятно, должны использовать одно из этих новых сопоставлений вместо _unicode_ci или _general_ci . Описание этих старых параметров сортировки, приведенное ниже, предоставлено только для интереса.
В настоящее время MySQL переходит от старой, ошибочной реализации UTF-8. На данный момент вам нужно использовать utf8mb4 вместо utf8 для части кодировки символов, чтобы убедиться, что вы получаете фиксированную версию. Версия с дефектами остается для обратной совместимости, хотя и устарела.
Ключевые различия
utf8mb4_unicode_ci основан на официальных правилах Unicode для универсальной сортировки и сравнения, которая обеспечивает точную сортировку на большом количестве языков.
utf8mb4_general_ci — это упрощенный набор правил сортировки, цель которого — работать как можно лучше, используя множество сокращений, предназначенных для повышения скорости. Он не соответствует правилам Unicode и в некоторых ситуациях приводит к нежелательной сортировке или сравнению, например, при использовании определенных языков или символов.
На современных серверах это повышение производительности будет практически незначительным. Он был разработан в то время, когда серверы имели крошечную долю производительности ЦП современных компьютеров.
Преимущества utf8mb4_unicode_ci перед utf8mb4_general_ci
utf8mb4_unicode_ci , который использует правила Юникода для сортировки и сравнения, применяет довольно сложный алгоритм для правильной сортировки в широком диапазоне языков и при использовании широкого диапазона специальных символов. Эти правила должны учитывать соглашения, специфичные для языка; не все сортируют своих персонажей в так называемом «алфавитном порядке».
Что касается латинских (т.е. «европейских») языков, то между сортировкой Unicode и упрощенной сортировкой utf8mb4_general_ci в MySQL нет большой разницы, но все же есть несколько отличий:
Например, сортировка Unicode сортирует «ß» как «ss» и «Œ» как «OE», как обычно хотят люди, использующие эти символы, тогда как utf8mb4_general_ci сортирует их как отдельные символы (предположительно, как «s» и «е» соответственно).
Некоторые символы Unicode определены как игнорируемые, что означает, что они не должны учитываться в порядке сортировки, и вместо этого сравнение должно переходить к следующему символу. utf8mb4_unicode_ci обрабатывает их правильно.
В нелатинских языках, таких как азиатские языки или языки с разными алфавитами, может быть намного больше различий между сортировкой Unicode и упрощенной сортировкой utf8mb4_general_ci . Пригодность utf8mb4_general_ci будет сильно зависеть от используемого языка. Для некоторых языков этого будет недостаточно.
Что использовать?
Почти наверняка нет причин использовать utf8mb4_general_ci , поскольку мы оставили точку, в которой скорость процессора достаточно низка, чтобы разница в производительности была бы существенной. Ваша база данных почти наверняка будет ограничена другими узкими местами, кроме этого.
В прошлом некоторые люди рекомендовали использовать utf8mb4_general_ci , за исключением случаев, когда точная сортировка была достаточно важной, чтобы оправдать затраты на производительность. Сегодня затраты на производительность практически исчезли, и разработчики более серьезно относятся к интернационализации.
Есть аргумент, что если для вас важнее скорость, чем точность, вы можете вообще не выполнять сортировку. Сделать алгоритм быстрее, если вам не нужна его точность, — тривиальная задача. Итак, utf8mb4_general_ci — это компромисс, который, вероятно, не нужен по соображениям скорости и, вероятно, также не подходит по соображениям точности.
Еще я добавлю, что даже если вы знаете, что ваше приложение поддерживает только английский язык, ему все равно может потребоваться иметь дело с именами людей, которые часто могут содержать символы, используемые на других языках, в которых так же важно правильно сортировать . Использование правил Unicode для всего помогает добавить уверенности в том, что очень умные люди, работающие с Unicode, очень много работали, чтобы сортировка работала должным образом.
Что означают части
Во-первых, ci предназначен для нечувствительности к регистру сортировки и сравнения. Это означает, что он подходит для текстовых данных, и регистр не важен. Другими типами сопоставления являются cs (с учетом регистра) для текстовых данных, где регистр важен, и bin для случаев, когда кодировка должна совпадать, побитно, что подходит для полей, которые действительно закодированные двоичные данные (включая, например, Base64). Сортировка с учетом регистра приводит к некоторым странным результатам, а сравнение с учетом регистра может привести к дублированным значениям, различающимся только регистром букв, поэтому сопоставления с учетом регистра не подходят для текстовых данных — если регистр важен для вас, то в противном случае игнорируемая пунктуация и так далее, вероятно, также имеет значение, и двоичное сопоставление может быть более подходящим.
Далее unicode или general относятся к определенным правилам сортировки и сравнения — в частности, способу нормализации или сравнения текста. Существует множество различных наборов правил для кодировки символов utf8mb4, причем unicode и general — это два набора правил, которые стараются хорошо работать на всех возможных языках, а не на одном конкретном. Различия между этими двумя наборами правил и являются предметом этого ответа. Обратите внимание, что unicode использует правила Unicode 4.0. Последние версии MySQL добавляют наборы правил unicode_520 , используя правила Unicode 5.2, и 0900 (отбрасывая часть «unicode_»), используя правила Unicode 9.0.
И, наконец, utf8mb4 , конечно же, внутренняя кодировка символов. В этом ответе я говорю только о кодировках на основе Unicode.
Я хотел знать, в чем разница в производительности между использованием utf8_general_ci и utf8_unicode_ci , но я не нашел никаких тестов, перечисленных в Интернете, поэтому я решил создать тесты самостоятельно.
Я создал очень простую таблицу с 500 000 строками:
Затем я заполнил его случайными данными, запустив эту хранимую процедуру:
Затем я создал следующие хранимые процедуры для сравнения простых SELECT , SELECT с LIKE и сортировки ( SELECT с ORDER BY ):
В приведенных выше хранимых процедурах используется сопоставление utf8_general_ci , но, конечно, во время тестов я использовал как utf8_general_ci , так и utf8_unicode_ci .
Я вызвал каждую хранимую процедуру 5 раз для каждого сопоставления (5 раз для utf8_general_ci и 5 раз для utf8_unicode_ci ), а затем вычислил средние значения.
benchmark_simple_select()
- с utf8_general_ci : 9 957 мс
- с utf8_unicode_ci : 10 271 мс
В этом тесте использование utf8_unicode_ci медленнее, чем utf8_general_ci на 3,2%.
benchmark_select_like()
- с utf8_general_ci : 11 441 мс
- с utf8_unicode_ci : 12 811 мс
В этом тесте использование utf8_unicode_ci медленнее, чем utf8_general_ci на 12%.
benchmark_order_by()
- с utf8_general_ci : 11 944 мс
- с utf8_unicode_ci : 12 887 мс
В этом тесте использование utf8_unicode_ci медленнее, чем utf8_general_ci на 7,9%.
В этом сообщении это очень хорошо описано.
Вкратце: utf8_unicode_ci использует алгоритм сортировки Unicode, как определено в стандартах Unicode, тогда как utf8_general_ci — это более простой порядок сортировки, который приводит к «менее точным» результатам сортировки.
См. Руководство по mysql, раздел Наборы символов Юникода :
Для любого набора символов Unicode операции, выполняемые с использованием сопоставления _general_ci, быстрее, чем операции для сопоставления _unicode_ci. Например, сравнения для сортировки utf8_general_ci быстрее, но немного менее корректны, чем сравнения для utf8_unicode_ci. Причина этого в том, что utf8_unicode_ci поддерживает сопоставления, такие как расширения; то есть, когда один символ сравнивается как равный с комбинациями других символов. Например, в немецком и некоторых других языках «ß» равно «ss». utf8_unicode_ci также поддерживает сокращения и игнорируемые символы. utf8_general_ci — это устаревшее сопоставление, которое не поддерживает расширения, сокращения или игнорируемые символы. Он может производить только однозначное сравнение между персонажами.
Подводя итог, utf_general_ci использует меньший и менее правильный (согласно стандарту) набор сравнений, чем utf_unicode_ci, который должен реализовывать весь стандарт. Набор general_ci будет быстрее, потому что требуется меньше вычислений.
Вкратце:
Если вам нужен лучший порядок сортировки — используйте utf8_unicode_ci (это предпочтительный метод),
Но если вас крайне интересует производительность — используйте utf8_general_ci , но знайте, что он немного устарел.
Различия в производительности очень незначительны.
Некоторые детали (PL)
Как мы можем прочитать здесь ( Питер Гулуцан ) есть разница в сортировке / сравнении польской буквы» Ł «(L со штрихом — html esc: Ł ) ( нижний регистр: «ł» — html esc: ł ) — имеем следующее предположение:
На польском языке буква Ł стоит после буквы L и перед M . Ни одна из этих кодировок не лучше или хуже — это зависит от ваших потребностей.
Есть две большие разницы в сортировке и сопоставлении символов:
Сортировка :
- utf8mb4_general_ci удаляет все акценты и сортирует один за другим, что может привести к неверным результатам сортировки.
- utf8mb4_unicode_ci сортирует точно.
Соответствие символов
Они по-разному соответствуют персонажам.
Например, в utf8mb4_unicode_ci у вас есть i != ı , но в utf8mb4_general_ci он содержит ı=i .
Например, представьте, что у вас есть строка с name=»Yılmaz» . Затем
Вернет строку, если совмещен с utf8mb4_general_ci , но если он совмещен с utf8mb4_unicode_ci , он не вернет строку!
С другой стороны, у нас есть эти a=ª и ß=ss в utf8mb4_unicode_ci , чего нет в utf8mb4_general_ci . Итак, представьте, что у вас есть строка с name=»ªßi» , тогда
Вернет строку, если совместное размещение равно utf8mb4_unicode_ci , но не вернет строку, если для сопоставления установлено значение utf8mb4_general_ci .
What's the difference between utf8_general_ci and utf8_unicode_ci?
Between utf8_general_ci and utf8_unicode_ci , are there any differences in terms of performance?
8 Answers 8
For those people still arriving at this question in 2020 or later, there are newer options that may be better than both of these. For example, utf8_unicode_520_ci .
All these collations are for the UTF-8 character encoding. The differences are in how text is sorted and compared.
_unicode_ci and _general_ci are two different sets of rules for sorting and comparing text according to the way we expect. Newer versions of MySQL introduce new sets of rules, too, such as _unicode_520_ci for equivalent rules based on Unicode 5.2, or the MySQL 8.x specific _0900_ai_ci for equivalent rules based on Unicode 9.0 (and with no equivalent _general_ci variant). People reading this now should probably use one of these newer collations instead of either _unicode_ci or _general_ci . The description of those older collations below is provided for interest only.
MySQL is currently transitioning away from an older, flawed UTF-8 implementation. For now, you need to use utf8mb4 instead of utf8 for the character encoding part, to ensure you are getting the fixed version. The flawed version remains for backward compatibility, though it is being deprecated.
Key differences
utf8mb4_unicode_ci is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.
utf8mb4_general_ci is a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations, such as when using particular languages or characters.
On modern servers, this performance boost will be all but negligible. It was devised in a time when servers had a tiny fraction of the CPU performance of today’s computers.
Benefits of utf8mb4_unicode_ci over utf8mb4_general_ci
utf8mb4_unicode_ci , which uses the Unicode rules for sorting and comparison, employs a fairly complex algorithm for correct sorting in a wide range of languages and when using a wide range of special characters. These rules need to take into account language-specific conventions; not everybody sorts their characters in what we would call ‘alphabetical order’.
As far as Latin (ie "European") languages go, there is not much difference between the Unicode sorting and the simplified utf8mb4_general_ci sorting in MySQL, but there are still a few differences:
For examples, the Unicode collation sorts "ß" like "ss", and "Œ" like "OE" as people using those characters would normally want, whereas utf8mb4_general_ci sorts them as single characters (presumably like "s" and "e" respectively).
Some Unicode characters are defined as ignorable, which means they shouldn’t count toward the sort order and the comparison should move on to the next character instead. utf8mb4_unicode_ci handles these properly.
In non-latin languages, such as Asian languages or languages with different alphabets, there may be a lot more differences between Unicode sorting and the simplified utf8mb4_general_ci sorting. The suitability of utf8mb4_general_ci will depend heavily on the language used. For some languages, it’ll be quite inadequate.
What should you use?
There is almost certainly no reason to use utf8mb4_general_ci anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by other bottlenecks than this.
In the past, some people recommended to use utf8mb4_general_ci except when accurate sorting was going to be important enough to justify the performance cost. Today, that performance cost has all but disappeared, and developers are treating internationalization more seriously.
There’s an argument to be made that if speed is more important to you than accuracy, you may as well not do any sorting at all. It’s trivial to make an algorithm faster if you do not need it to be accurate. So, utf8mb4_general_ci is a compromise that’s probably not needed for speed reasons and probably also not suitable for accuracy reasons.
One other thing I’ll add is that even if you know your application only supports the English language, it may still need to deal with people’s names, which can often contain characters used in other languages in which it is just as important to sort correctly. Using the Unicode rules for everything helps add peace of mind that the very smart Unicode people have worked very hard to make sorting work properly.
What the parts mean
Firstly, ci is for case-insensitive sorting and comparison. This means it’s suitable for textual data, and case is not important. The other types of collation are cs (case-sensitive) for textual data where case is important, and bin , for where the encoding needs to match, bit for bit, which is suitable for fields which are really encoded binary data (including, for example, Base64). Case-sensitive sorting leads to some weird results and case-sensitive comparison can result in duplicate values differing only in letter case, so case-sensitive collations are falling out of favor for textual data — if case is significant to you, then otherwise ignorable punctuation and so on is probably also significant, and a binary collation might be more appropriate.
Next, unicode or general refers to the specific sorting and comparison rules — in particular, the way text is normalized or compared. There are many different sets of rules for the utf8mb4 character encoding, with unicode and general being two that attempt to work well in all possible languages rather than one specific one. The differences between these two sets of rules are the subject of this answer. Note that unicode uses rules from Unicode 4.0. Recent versions of MySQL and MariaDB add the rulesets unicode_520 using rules from Unicode 5.2, and MySQL 8.x adds 0900 (dropping the "unicode_" part) using rules from Unicode 9.0.
And lastly, utf8mb4 is of course the character encoding used internally. In this answer I’m talking only about Unicode based encodings.
Как перейти с utf8 на utf8mb4 в MySQL
Если ваша версия СУБД MySQL 5.5.3 и выше, то вам необходимо использовать кодировку utf8mb4, вместо utf8. Об этом упоминается здесь и здесь.
Следовательно, больше нет необходимости использовать ни utf8_general_ci, ни utf8_unicode_ci.
utf8mb4_general_ci или utf8mb4_unicode_ci
В настоящее время для баз данных и таблиц MySQL рекомендуется использовать кодировку utf8mb4_unicode_ci.
Настройка кодировки utf8mb4 для СУБД MySQL
Исходя из вышеизложенного нам необходимо произвести настройку основных параметров кодировки СУБД MySQL.
В конфигурационном файле MySQL ( my.ini (windows)/ my.cnf (Linux)) необходимо изменить кодировку на utf8mb4:
Проверяем корректность работы применимых настроек:
Кодировка и сравнение для базы данных, таблиц и столбцов в MySQL
Запросы для измениния кодировки и сравнения для базы данных, таблиц и столбцов на utf8mb4 .
Для базы данных:
Для таблицы:
Для столбцов:
Восстановление и оптимизация всех таблиц
После обновления версии MySQL сервера и применения действий по смене кодировки и сравнений, необходимо произвести восстановление и оптимизацию всех баз данных и таблиц. Для этого вы можете выполнить следующие запросы для каждой таблицы: