Все таблицы MySQL в моем веб-приложении на PHP — это MyISAM с кодировкой utf8. Поскольку записи могут быть сгенерированы из сопутствующего приложения в автономном режиме, ключи моей таблицы генерируются случайным образом, буквенно-цифровые VARCHAR; для этих полей задано двоичное значение с кодировкой utf8_bin, поэтому они могут быть чувствительны к регистру.
Недавно я решил изменить кодировку всех своих текстовых полей, чтобы поддерживать смайлики, которые некоторые пользователи любят вводить. Я пошел дальше и изменил все поля utf8 на utf8mb4, включая ключи. Я сразу же начал видеть проблемы с производительностью, когда сложные запросы SELECT для одной из больших таблиц занимали более минуты, а затем другие запросы выстраивались в очередь в ожидании блокировок таблицы. Я изменил кодировку поля первичного ключа в этой таблице обратно на utf8, и производительность вернулась к нормальной. Пару дней спустя я снова изменил это поле на utf8mb4, запросы снова начали ставиться в очередь, и я вернул его обратно, чтобы восстановить нормальную производительность.
Так что это проходит гладко:
`ID` varchar(8) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ''
Но это вызывает проблемы:
`ID` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT ''
Все, что я прочитал, говорит, что utf8 и utf8mb4 должны иметь одинаковую производительность, но я вижу явную разницу в моем случае. Имеет ли это смысл?
Сохранение ключевых полей на utf8 не является проблемой, поскольку я не предвижу использовать там больше, чем простые буквенно-цифровые символы. Но мне хотелось бы, чтобы все поля были установлены в одну и ту же кодировку только для согласованности и простоты обслуживания (не нужно забывать устанавливать для пользовательских полей одну кодировку, а для ключевых полей — другую кодировку).
По поводу комментария от @MandyShaw
Когда я работаю с базой данных с приложением Sequel Pro Mac, консоль постоянно показывает пары SET NAMES 'utf8'
а также SET NAMES 'utf8mb4'
записи, так что это говорит о том, что не все установлено правильно. Однако вот что я имею в настоящее время:
MySQL [(none)]> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+
Я прочитал это character_set_system
не может быть изменено с utf8 и character_set_filesystem
должен быть двоичным
Кодировка соединения Sequel Pro была установлена на Autodetect, но когда я явно изменяю ее на utf8mb4, а затем открываю новое соединение, я все еще вижу все эти изменения кодировки в консоли.
Есть ли что-то еще, что мне нужно изменить, чтобы последовательно использовать эту кодировку?
utf — это действительно utfmb3 и может использовать максимум 3 байта на символ, тогда как utfmb4 может использовать 4 байта на символ. Для столбцов VARCHAR это обычно не имеет большого значения, поскольку MySQL будет хранить столько байтов, сколько необходимо (если только вы не создали таблицы MyISAM с ROW_FORMAT = FIXED).
Однако во время выполнения запроса MySQL может создавать временные таблицы в механизме хранения MEMORY, который не поддерживает строки переменной длины. Эти временные таблицы имеют максимальный размер, и если этот размер будет превышен, временные таблицы будут преобразованы в таблицы в MyISAM / InnoDB (в зависимости от вашей версии MySQL). Переменная состояния Created_tmp_disk_tables
будет увеличиваться каждый раз, когда это происходит. Если это так, попробуйте посмотреть, поможет ли это увеличить стоимость max_heap_table_size
а также tmp_table_size
,
Альтернативно, обновите до MySQL 8.0, где новый внутренний механизм хранения, который поддерживает строки переменной длины, используется для внутренних временных таблиц.
Других решений пока нет …