Последствия для производительности MySQL VARCHAR

Есть ли разница в производительности в MySQL между размерами varchar? Например, varchar(25) и varchar(64000). Если нет, есть ли причина не объявлять все varchars с максимальным размером, чтобы гарантировать, что вы не исчерпали комнату?

37 голосов | спросил BenV 8 Jpm1000000pmSat, 08 Jan 2011 23:39:18 +030011 2011, 23:39:18

6 ответов


25

Вы должны понимать компромиссы использования CHAR vs VARCHAR

С полями CHAR вы выделяете то, что вы получаете. Например, CHAR (15) выделяет и хранит 15 байтов, независимо от того, как персонажи вы помещаете в поле. Строковые манипуляции просты и понятны, поскольку размер поля данных полностью предсказуем.

С полями VARCHAR вы получаете совершенно другую историю. Например, VARCHAR (15) фактически динамически распределяет до 16 байт, до 15 для данных и, по меньшей мере, 1 дополнительный байт для хранения длины данных. Если у вас есть строка «hello» для хранения, которая будет занимать 6 байтов, а не 5. Обработка строк должна всегда выполнять некоторую проверку длины во всех случаях.

Компромисс более очевиден, когда вы делаете две вещи:
1. Сохранение миллионов или миллиардов строк
2. Индексирование столбцов, которые являются либо CHAR, либо VARCHAR

TRADEOFF # 1

Очевидно, что VARCHAR имеет преимущество, поскольку данные переменной длины будут создавать меньшие строки и, следовательно, меньшие физические файлы.

TRADEOFF # 2

Поскольку поля CHAR требуют меньше манипуляции с строками из-за фиксированной ширины поля, поиск индекса по полю CHAR в среднем на 20% быстрее, чем поля VARCHAR. Это не какая-то догадка с моей стороны. Книга MySQL Database Design and Tuning продемонстрировала что-то удивительное в таблице MyISAM, чтобы доказать это. Пример в книге сделал примерно следующее:

ALTER TABLE tblname ROW_FORMAT=FIXED;

Эти директивы вынуждают VARCHAR вести себя как CHAR. Я сделал это на своей предыдущей работе еще в 2007 году и взял таблицу за 300 ГБ и ускорил поиск индекса на 20%, не изменив ничего. Он работал как опубликованный. Однако он сделал таблицу почти вдвое больше, но это просто возвращается к компромиссу № 1.

Вы можете проанализировать хранящиеся данные, чтобы узнать, что MySQL рекомендует для определения столбцов. Просто запустите следующую таблицу:

SELECT * FROM tblname PROCEDURE ANALYSE();

Это будет проходить по всей таблице и рекомендовать определения столбцов для каждого столбца на основе содержащихся в нем данных, минимальных значений полей, максимальных значений полей и т. д. Иногда вам просто нужно использовать здравый смысл при планировании CHAR vs. VARCHAR. Вот хороший пример:

Если вы храните IP-адреса, маска для такого столбца не более 15 символов (xxx.xxx.xxx.xxx). Я бы прыгнул прямо в CHAR (15) в одно мгновение, потому что длина IP-адресов не будет меняться так сильно, и добавленная сложность манипуляций с строками контролируется дополнительным байтом. Вы все равно можете выполнить АНАЛИЗ ПРОЦЕДУРЫ () против такого столбца. Он может даже рекомендовать VARCHAR. В этом случае мои деньги по-прежнему будут на CHAR над VARCHAR.

Вопросы CHAR vs VARCHAR могут быть разрешены только при правильном планировании. С великой силой приходит большая ответственность (клише, но правда)

ответил RolandoMySQLDBA 25 MarpmFri, 25 Mar 2011 18:31:30 +03002011-03-25T18:31:30+03:0006 2011, 18:31:30
12

Ответ на это на самом деле довольно сложный. Короткий вариант: есть разница .

  1. При создании временных таблиц для фильтрации результатов (например, GROUP BY) будет выделена полная длина.

  2. Проводной протокол (отправка строк клиенту) скорее всего выделит большую длину.

  3. Механизм хранения может /не может реализовывать правильный varchar.

Для (2) я признаю, что проводной протокол не является тем, с чем я хорошо знаком, но общий совет здесь - попытаться применить хотя бы некоторые минимальные усилия, чтобы угадать длину.

ответил Morgan Tocker 1 PMpFri, 01 Apr 2011 17:53:41 +040053Friday 2011, 17:53:41
5

Большинство ответов в этой теме - 5 лет, написанных до того, как InnoDB и utf8 были дефолтами. Итак, позвольте мне начать ...

Когда запросу нужна внутренняя временная таблица, он пытается использовать таблицу MEMORY. Но MEMORY нельзя использовать, если

  • TEXT /BLOB, даже не TINYTEXT.
  • VARCHAR больше, чем какая-либо сумма, возможно, 512 в текущей версии.

Также обратите внимание, что VARCHARs превращаются в CHARs. Таким образом, VARCHAR(255) с CHARACTER SET utf8 расширяется до 765 байт, независимо от того, что находится в столбце. Затем это может быть вызвано:

  • Если таблица MEMORY становится больше, чем max_heap_table_size или tmp_table_size, она будет преобразована в MyISAM и потенциально разлива на диск.

Итак, VARCHAR(25) с большей вероятностью останется MEMORY, следовательно, будет быстрее. (255) не так хорош, а (64000) плох.

(В будущем временные таблицы, вероятно, будут InnoDB, и часть этого ответа потребуется пересмотреть.)

ответил Rick James 2 J000000Saturday16 2016, 08:33:23
4

Я понимаю, что меньшие поля могут быть включены в индекс напрямую, тогда как более длинные они не могут. Из-за этого ограничения, если вы хотите, чтобы строки были индексируемыми, я бы сказал, чтобы они были короче. В противном случае нет, поскольку они оба являются varchar, тогда операции ops, такие как сортировка или сравнение, будут работать в одинаковое время, независимо от того, являются ли поля 25 или MAX.

ответил jcolebrand 9 Jam1000000amSun, 09 Jan 2011 09:29:31 +030011 2011, 09:29:31
4

Столбец varchar, размер которого делает запросы во всей таблице более вероятными для использования временных таблиц. Согласно книге высокой производительности MySQL. Когда оптимизатор пытается увидеть, может ли он запускать этот запрос в памяти или ему нужна временная таблица, он смотрит на размер строки на основе определения таблицы, то есть для скорости он не пытается увидеть, сколько из 64K символов вы действительно используете. Вот почему авторы рекомендуют вам не вытягивать это определение за пределы фактических возможных значений, которые будут отображаться в столбце. Очевидно, что если вы настроите больше запросов на временные таблицы (даже если фактический размер данных может поместиться в ОЗУ), вы теперь понесли штрафы ввода-вывода, которых вы могли бы избежать.

ответил TechieGurl 19 +04002011-10-19T20:16:39+04:00312011bEurope/MoscowWed, 19 Oct 2011 20:16:39 +0400 2011, 20:16:39
2
  

убедитесь, что вы не исчерпали номер

Эта фраза подразумевает, что вы задаете вопрос, потому что вы не уверены в данных, которые вы будете хранить в базе данных. Если это правда, вам будет хорошо, чтобы узнать, как только сможете, потому что вам понадобится это для планирования емкости. Если вы, возможно, получаете элементы данных с 7000 символами, например, вам нужно знать, потому что это может иметь последствия для производительности для любой СУБД.

Тем не менее, я предпочитаю иметь размеры столбцов, связанные с ожидаемым содержимым. Например, телефонный номер вряд ли будет длиннее 50 символов, даже если вы укажете код страны и расширение. Аналогично, почтовый индекс или почтовый код будут, скорее всего, 20 или менее символов.

ответил Larry Coleman 9 Jam1000000amSun, 09 Jan 2011 05:02:09 +030011 2011, 05:02:09

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132