Перейти к содержимому 
Сравнение баз данных управляют тем, как MySQL сравнивает и сортирует строки, и напрямую влияют на загрузку ЦП, использование индексов и ввод-вывод. Если я выберу медленную сортировку или смешаю настройки, запросы будут выполняться дольше, возникнут преобразования и появится угроза ошибок „Illegal mix“.
Центральные пункты
- Набор символов/сортировкаНеправильные комбинации вызывают преобразования и замедляют работу.
- Индексы: Нечувствительность к регистру снижает избирательность, чувствительность к регистру ускоряет сопоставление.
- Юникод: utf8mb4 более точный, но требует больше ресурсов процессора.
- Последовательность: Единые настройки предотвращают сортировку файлов и полное сканирование.
- Тюнинг: Объединение сортировки с памятью, пулом и дизайном запросов.
Что такое сортировки – и почему они влияют на производительность
Я использую Сравнения, чтобы определить правила сравнения и сортировки строк. Они связаны с кодировка базы данных , который определяет кодировку символов, например utf8mb4 или latin1. Если я выбираю более точную сортировку Unicode, такую как utf8mb4_unicode_ci, вычислительные затраты на каждое сравнение увеличиваются. В измерениях с MySQL 8.0 рабочие нагрузки OLTP с новыми сортировками Unicode работали на 10–16 % медленнее, но зато сравнения для языков и эмодзи были более точными (источник [2]). Для чисто скоростных рабочих нагрузок подходят простые правила, такие как utf8_general_ci, но они дают менее точные результаты (источник [2]).
Набор символов и сортировка: небольшие различия, большой эффект
Der Набор символов определяет, как MySQL хранит байты, а сортировка определяет, как MySQL сравнивает эти байты. Если я смешиваю сортировки в JOIN или условиях WHERE, MySQL конвертирует их на лету, что заметно дороже при больших таблицах (источник [2]). Это требует ресурсов ЦП, создает временные таблицы и может привести к сортировке файлов на диске. Поэтому я поддерживаю строгую унификацию на уровне приложения, базы данных, таблиц и столбцов. Для более широкой оптимизации я включаю тему сортировки в свои меры по Оптимизация базы данных SQL в.
Версии и настройки по умолчанию: что изменилось между версиями 5.7 и 8.0
При обновлении я обращаю внимание на По умолчанию: MySQL 8.0 по умолчанию использует utf8mb4 и во многих сборках на utf8mb4_0900_ai_ci. Старые установки часто используют латинский1_шведский_ci или utf8_general_ci. Это изменение влияет не только на кодировку, но и на порядок сортировки и правила равенства. В результате ORDER BY-Результаты выглядят иначе, УНИКАЛЬНЫЙ-индексы вновь сталкиваются или внезапно появляются дубликаты, которые ранее были „одинаковыми“ (или наоборот). Поэтому я планирую обновления таким образом, чтобы заранее проверить: SELECT @@character_set_server, @@collation_server, @@collation_database; и сознательно устанавливаю значения по умолчанию в целевой системе. Одновременно я тестирую, как utf8mb4_0900_ai_ci по отношению к utf8mb4_unicode_ci в моих реальных запросах, поскольку варианты 0900 (на основе ICU) часто содержат более точные, но и более дорогие правила (источник [2]).
Индексы и планы запросов: где сортировки замедляют работу
Сравнения управляют Использование индекса с. Case-insensitive (_ci) расширяет поиск, но снижает селективность — оптимизатор реже использует индекс. Case-sensitive (_cs) ускоряет точные совпадения, но подходит не для всех требований. Если столбец меняет сортировку, меняются правила сравнения и, следовательно, план — файловая сортировка появляется чаще, частично с временными таблицами (источник [1], [3]). Более подробную информацию о влиянии индекса я раскрываю в „Индексы: преимущества и риски“ от.
Частые ошибки и прямые решения
Сообщение Незаконный микс почти всегда указывает на смешанные сортировки. Я решаю эту проблему в краткосрочной перспективе с помощью COLLATE в запросе, а в долгосрочной перспективе унифицирую столбцы. Если возникают файловые сортировки и высокие задержки, я проверяю столбцы ORDER BY и адаптирую сортировку к определению индекса (источник [3]). При JOIN с столбцами TEXT/VARCHAR я обращаю внимание на идентичные сортировки, иначе преобразования вынуждают оптимизатор создавать плохие планы. Согласованность часто приносит сразу ощутимую выгоду в миллисекундах.
Иерархия MySQL: от сервера до выражения
MySQL поддерживает сортировки на основе пять уровней: сервер, база данных, таблица, столбец, выражение. Нижний уровень выигрывает, поэтому отклонения приводят к неожиданностям. Я проверяю настройки с помощью `SELECT SCHEMA_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA`, `SHOW TABLE STATUS` и `SHOW FULL COLUMNS`. Если запрос `col1 COLLATE utf8mb4_unicode_ci = col2`, разные сортировки столбцов оценивают сравнение, что занимает время (источник [1]). Перед внесением изменений я делаю резервные копии и тестирую перекодировку в стадии подготовки, чтобы избежать искажения данных.
Настройки соединения и сеанса: где возникают ошибки
Многие проблемы возникают не в схеме, а в Сессия. Я проверяю переменные набор_символов_клиента, набор_символов_соединения, результаты набора символов и collation_connection. ORM частично используют НАЗВАНИЯ НАБОРОВ и перезаписывают настройки по умолчанию сервера; смешанные развертывания приводят к „невидимым“ преобразованиям. Я следую четким правилам: приложение отправляет UTF-8 (utf8mb4), соединение устанавливает SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci; или устанавливаю его с помощью опции драйвера. Для отладки я использую SHOW VARIABLES LIKE 'collation%'; и SELECT COLLATION(столбец), COERCIBILITY(столбец) соответственно COLLATION('литерал'). Если значения отличаются, я обычно быстро нахожу причину временных таблиц и ошибок несоответствия (источник [1]).
Нечувствительность к регистру и чувствительность к регистру: когда какой вариант подходит
С _ci я игнорирую регистр, что повышает удобство использования. Зато снижается селективность, и поиски LIKE реже обращаются к индексам. С помощью _cs Я провожу точные сравнения, получаю более быстрые запросы очков, но теряю удобство. Для логинов, токенов или идентификаторов я использую _cs, для полей поиска часто _ci. Я четко разделяю оба, чтобы предотвратить злоупотребления и конвертации.
Тонкости: правила акцента, ширины и двоичного кодирования (_ai, _as, _bin)
Я различаю больше, чем просто регистр символов. _ai (нечувствительный к регистру) рассматривает „é“ и „e“ как одинаковые; _as (accent-sensitive) различает их. В восточноазиатских языках также играет роль Ширина рулон (полной/половинной ширины), в то время как _bin выполняет чистое сравнение байтов – самый быстрый способ, но без языковой логики. Для журналов, хэшей и идентификаторов я использую _bin или _cs, для пользовательских поисков часто _ai, чтобы опечатки и акценты не имели значения. Я сознательно тестирую примеры: SELECT 'straße' = 'strasse' COLLATE utf8mb4_0900_ai_ci; поставки TRUE, в то время как ... COLLATE utf8mb4_0900_as_cs; FALSE такие правила определяют, сколько строк будет включать сканирование диапазона индекса, а значит, и задержку и ввод-вывод.
Правильное чтение тестов: точность требует затрат процессорного времени
Сортировки Unicode, такие как utf8mb4_unicode_ci и utf8mb4_0900_ai_ci правильно отображают языки, диакритические знаки и эмодзи. Логика сравнения более сложна, что требует большего количества ресурсов ЦП для каждого сравнения. В сценариях OLTP с большим количеством сравнений строк измерения показывают увеличение времени выполнения на 10–16 % в зависимости от рабочей нагрузки и размера набора данных (источник [2]). Это менее заметно для небольших таблиц, но более заметно для широких поисков и сортировок. Я принимаю решение в зависимости от конкретного случая и учитываю требования пользователей.
Размер индекса, ограничения префикса и потребность в памяти
С utf8mb4 Я сознательно планирую ширину индекса, поскольку один символ может занимать до 4 байт. InnoDB ограничивает длину ключей индекса (исторически 767 байт, в более новых версиях и форматах строк фактически до 3072 байт). Это влияет на VARCHAR-столбцы, составные индексы и покрывающие индексы. Поэтому я проверяю: достаточно ли 191 символа (191×4≈764 байт) для электронной почты или URL? В настройках 5.7 это часто было безопасным выбором, в 8.0 я часто могу увеличить до 255 — при условии, что составные индексы не выходят за рамки. При необходимости я устанавливаю Префиксные индексы: CREATE INDEX idx_email ON users(email(191)); Это экономит место, но снижает избирательность; я измеряю эффект с помощью EXPLAIN ANALYZE и журналом медленных запросов (источник [3]). Кроме того, большие ключи увеличивают буферный пул: с каждым дополнительным байтом увеличивается нагрузка на кэш и ввод-вывод, поэтому решения о сортировке влияют на расходы на хранение.
Настройка хостинга: сортировка, буфер и пулинг в комплексе
Я повышаю innodb_buffer_pool_size, чтобы индексы и горячие данные оставались в памяти. С помощью пула соединений я снижаю накладные расходы на каждый запрос, а прокси-слои уменьшают всплески. Для форматов файлов, размера журнала повторения и размера страницы я настраиваю целевую рабочую нагрузку. Кроме того, я сознательно выбираю механизм хранения; взгляните на InnoDB против MyISAM показывает типичные различия в транзакциях, блокировках и защите от сбоев. Без согласованных сортировок часть этой настройки теряет свою эффективность.
Лучшие практики: выбор в зависимости от сценария использования
Для современных веб-приложений я использую utf8mb4 в качестве набора символов, поскольку он поддерживает эмодзи и полный набор Unicode. Если мне нужна максимальная точность сортировки в нескольких языках, я использую utf8mb4_unicode_ci или utf8mb4_0900_ai_ci. Для простой скорости при простых сравнениях utf8_general_ci часто быстрее, но допускает неточности (источник [2]). Я поддерживаю стратегию сортировки на уровне сервера, схемы, таблицы и столбца. Тесты с EXPLAIN ANALYZE и Slow-Query-Log подтверждают правильность решения (источник [3]).
| Сравнение | Точность | Скорость | Поддержка эмодзи | Подходит для |
|---|---|---|---|---|
| utf8_general_ci | Низкий | Высокий | Нет | Быстрый поиск |
| utf8_unicode_ci | Высокий | Средний | Нет | Приложения Unicode |
| utf8mb4_unicode_ci | Очень высокий | Низкий | Да | Современный веб |
| utf8mb4_0900_ai_ci | Самый высокий | Средний | Да | Многоязычный |
Шаг за шагом: переход без простоев
Я начинаю с Инвентаризация: Какие схемы, таблицы и столбцы используют какие коллизации? Затем я сохраняю данные, экспортирую критические таблицы и создаю репетиции в Staging. Переход осуществляется с помощью `ALTER TABLE … CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;`, начиная с малоиспользуемых таблиц. Для больших таблиц я планирую окна обслуживания или использую онлайн-инструменты миграции, такие как Percona Toolkit (источник [1], [2]). После перехода я проверяю EXPLAIN, журнал медленных запросов и сравниваю задержки.
Диагностика: правильные вопросы к базе данных
Я проверяю СХЕМЫ и `SHOW FULL COLUMNS`, чтобы увидеть отклонения. Если возникают файловые сортировки и временные таблицы, я не увеличиваю sort_buffer_size вслепую, а устраняю несоответствие сортировки. С помощью EXPLAIN я вижу, работает ли индекс или происходит полное сканирование. С помощью Performance Schema я измеряю tmp_disk_tables и sort_merge_passes, чтобы обнаружить связанные с сортировкой операции ввода-вывода. Таким образом я нахожу узкие места, которые непосредственно связаны со сравнением строк (источник [3]).
GROUP BY, DISTINCT и UNIQUE: семантические последствия сортировки
Сравнения определяют, когда значения считаются „равными“. Это влияет на Дедупликация и правила уникальности. Я перехожу с _cs на сайте _ci или от _as на сайте _ai, может быть УНИКАЛЬНЫЙ-индекс внезапно сообщает о коллизиях. Перед миграцией я ищу потенциальные конфликты: SELECT col, COUNT(*) FROM t GROUP BY col COLLATE utf8mb4_0900_ai_ci HAVING COUNT(*) > 1;. Так я вижу, какие строки совпадают в коллизации цели. Я также обращаю на это внимание при ГРУППА ПО и DISTINCT: Количество групп зависит от набора правил, а значит, и от плана (больше или меньше сортировки/хэширования). Для таблиц отчетов может быть целесообразно использовать намеренно „грубую“ сортировку, которая создает меньше групп; для идентификаторов касс и логинов это рискованно.
Шаблоны проектирования: двоичные, сгенерированные столбцы и функциональные индексы
Я отделяю Представительство и Поиск: видимая колонка остается в „красивой“ сортировке (например,. utf8mb4_0900_ai_ci), к этому я добавляю генерируемый столбец , которая нормализована для высокопроизводительных сравнений — например, в нижнем регистре и в двоичном формате. Пример: ALTER TABLE user ADD name_search VARCHAR(255) GENERATED ALWAYS AS (LOWER(name)) STORED, ADD INDEX idx_name_search (name_search); С _bin- или _cs-Сравнение на имя_поиск я получаю точные и быстрые совпадения при WHERE name_search = LOWER(?). В MySQL 8.0 я также могу использовать Сортировка в индексе указать: CREATE INDEX idx_name_ai ON user (name COLLATE utf8mb4_0900_ai_ci); Таким образом, столбец остается, например,. _cs, в то время как индекс намеренно _ai – удобно для „нечеткого“ поиска без полного сканирования. Я документирую эти шаблоны в схеме, чтобы генератор запросов приложения использовал правильный столбец или индекс.
LIKE, префиксы и полный текст: что действительно ускоряет работу
На сайте LIKE-Поиск осуществляется по обычным правилам сортировки. Ведущий подстановочный знак (LIKE 'c') препятствует использованию индекса, независимо от того, насколько хорошо выбрана сортировка. Поэтому я изменяю шаблоны поиска таким образом, чтобы использовались префиксы (LIKE 'abc%') и обращаю внимание на совместимость сортировки, чтобы MySQL не производил конвертацию. Для больших свободных текстов я использую ПОЛНЫЙ ТЕКСТ-Индексы; токенизация в значительной степени не зависит от сортировки, но кодировка символов и нормализация влияют на результаты поиска. В средах CJK помогают парсеры NGRAM; в западных языках я избегаю „грубых“ сортировок, чтобы стемминг/стоп-слова не смешивались слишком сильно. Здесь также действует правило: согласованность от поля до соединения предотвращает временные таблицы и сортировку файлов (источник [3]).
Практика: обеспечение быстрой работы WordPress, интернет-магазинов и API
Системы контента и магазинов получают следующие преимущества utf8mb4_unicode_ci, потому что сортировка тегов, категорий и пользовательского контента выполняется правильно. Я слежу за тем, чтобы плагины не создавали отклоняющиеся сортировки. В API и путях аутентификации я устанавливаю для токенов _cs, чтобы обеспечить точное совпадение через индекс. В отчетах с ORDER BY на больших текстовых полях я комбинирую согласованность сортировки и подходящие индексы покрытия. В дополнение к этому, для увеличения пропускной способности я смотрю советы из Оптимизация базы данных SQL Вперёд.
Компактное резюме
Я выбираю Сравнения Сознательно: скорость, точность и ожидания пользователей определяют решение. Единые настройки предотвращают преобразования, сортировку файлов и неэффективные планы. Варианты Unicode дают лучшие результаты, но требуют больше ресурсов ЦП; измерения с MySQL 8.0 показывают потери 10–16 % при интенсивных нагрузках на строки (источник [2]). Благодаря четкому дизайну схемы, индексам, буферному пулу и пулингу, экземпляр MySQL надежно масштабируется. Систематическая проверка, тестирование и консолидация снижают задержку и заметно повышают производительность сортировки MySQL.
