#mysql #innodb
#mysql #innodb
Вопрос:
Я изменил «innodb_file_format» с «Antelope» на «Barracuda» по следующим причинам.
- Чтобы избежать ограничения размера строки
- Чтобы избежать ограничения размера индекса столбца
При изменении формата файла я выбрал «row_format» как «динамический». Это работает нормально.
Но я хотел бы изменить «row_format» с «динамического» на «сжатый» для сжатия данных. Может кто-нибудь мне сказать
- Имеет ли row_format отношение к ИНДЕКСАМ СТОЛБЦОВ и ВСТАВКАМ ДАННЫХ в таблицы? Если да, что рекомендуется и почему?
- Приведет ли сжатый формат к снижению производительности?
Ответ №1:
Использование ДИНАМИЧЕСКОГО или СЖАТОГО означает, что InnoDB хранит поля varchar / text / blob, которые не помещаются на странице, полностью за пределами страницы. Но за исключением тех столбцов, которые затем насчитывают только 20 байт на столбец, ограничение на размер строки InnoDB не изменилось; оно по-прежнему ограничено примерно 8000 байтами на строку.
InnoDB поддерживает только индексы по 767 байт на столбец. Вы можете увеличить это значение на 3072 байта, установив innodb_large_prefix=1 и используя либо ДИНАМИЧЕСКИЙ, либо СЖАТЫЙ формат строки.
Использование формата СЖАТОЙ строки не приводит к тому, что InnoDB поддерживает более длинные индексы.
Что касается производительности, это один из тех случаев, когда «это зависит». Сжатие обычно представляет собой компромисс между размером хранилища и загрузкой процессора для сжатия и распаковки. Это правда, что для работы со сжатыми данными требуется немного больше ЦП, но вы должны иметь в виду, что серверы баз данных обычно ожидают ввода-вывода и имеют свободные ресурсы ЦП.
Но не всегда — если вы выполняете сложные запросы к данным, находящимся в буферном пуле, вы можете быть ограничены процессором больше, чем вводом-выводом. Таким образом, это зависит от многих факторов, например, от того, насколько хорошо ваши данные помещаются в ОЗУ, типа выполняемых вами запросов и количества запросов в секунду, а также от характеристик оборудования. Слишком много факторов, чтобы кто-то еще мог отвечать за ваше приложение на вашем сервере. Вам просто нужно это протестировать.
Повторите свой комментарий:
Одна из возможностей заключается в том, что индекс не помещается в пул буферов. Производительность значительно снижается, если поиск по индексу должен загружать страницы и удалять страницы во время каждого запроса SELECT. Анализ EXPLAIN не может сказать вам, помещается ли индекс в пул буферов.
Я не знаю, сколько столбцов или какие типы данных столбцов в вашем индексе, но если вы индексируете длинные столбцы varchar, вам следует рассмотреть возможность использования префиксных индексов (или уменьшения длины столбцов).
Вы также можете получить больше оперативной памяти и увеличить размер пула буферов.
Комментарии:
1. Билл, спасибо за ответ. Позвольте мне объяснить мой случай здесь. У меня есть таблица POS из 22 миллионов, в которой мы запускаем 500 тысяч простых запросов joins для получения данных POS. После анализа запросов я добавил несколько составных индексов, которые дали хорошие улучшения производительности. Аналогичным образом я попытался добавить еще один составной индекс, который превышал максимальный размер индекса 767 байт, поэтому перенес таблицу POS (только) в Barracuda и добавил составной индекс и запросы, требующие времени. Не уверен, почему большие индексы снижают производительность. Я установил пул буферов объемом 16 ГБ.
Ответ №2:
СЖАТЫЙ будет сжимать данные. Текст будет сжат очень хорошо. У меня есть несколько таблиц, и я использовал ДИНАМИЧЕСКИЙ раньше, перешел на СЖАТЫЙ.
Я использую MySQL 5.7
Таблица:
- идентификатор (int)
- some_other_id (int)
- текст (длинный текст) — utf8mb4_unicode_ci ~ 500 КБ / строка в среднем
- updated_at (int)
- created_at (int)
Он использует на 80% меньше места при СЖАТИИ по сравнению с ДИНАМИЧЕСКИМ. До: 80 ГБ, после: огромная экономия 16 ГБ, хотя мне не так нужны эти данные.
Другие таблицы были не такими драматичными, но это сэкономило ~ 50% там, где есть несколько текстовых полей. Например. другой от 6,4 Гб -> 3,1 Гб с 1,5 Млн строк.
Я не перешел на СЖАТЫЕ таблицы меньшего размера, которые в основном сохраняют целые числа / бит и тому подобное. Эти таблицы уже невелики по объему, поэтому для них не нужно использовать больше процессора.