Есть ли недостатки в использовании общего varchar(255) для всех текстовых полей?
У меня есть contacts таблица, которая содержит такие поля, как postcode, first name, last name, town, country, phone number и т.д., все из которых определены как VARCHAR(255) хотя ни одно из этих полей никогда не приблизится к 255 символам. (Если вам интересно, это так, потому что миграции Ruby on Rails отображают поля String в VARCHAR(255) по умолчанию, и я никогда не удосужился переопределить его).
Поскольку VARCHAR будет хранить только количество фактических символов поля (вместе с длиной поля), есть ли какое-то явное преимущество (производительность или иное), например, при использовании: VARCHAR(16) над VARCHAR(255)?
Кроме того, большинство из этих полей имеют индексы. Влияет ли больший размер VARCHAR на поле на размер или производительность индекса вообще?
К вашему сведению, я использую MySQL 5.
7 ответов
В хранилище, VARCHAR(255) достаточно умен, чтобы хранить только ту длину, которая вам нужна в данном ряду, в отличие от CHAR(255) который всегда будет хранить 255 символов.
Но так как вы пометили этот вопрос MySQL, я упомяну специфический для MySQL совет: поскольку строки копируются из уровня механизма хранения в уровень SQL, VARCHAR поля конвертируются в CHAR чтобы получить преимущество работы со строками фиксированной ширины. Таким образом, строки в памяти дополняются до максимальной длины вашего объявленного VARCHAR колонка.
Когда ваш запрос неявно генерирует временную таблицу, например, при сортировке или GROUP BY, это может использовать много памяти. Если вы используете много VARCHAR(255) поля для данных, которые не должны быть такими длинными, это может сделать временную таблицу очень большой.
Вам также может быть интересно знать, что это поведение "заполнения" означает, что строка, объявленная с набором символов utf8, дополняет до трех байтов на символ, даже для строк, которые вы храните с однобайтовым содержимым (например, символы ascii или latin1). И также набор символов utf8mb4 заставляет строку заполнять до четырех байтов на символ в памяти.
Так что VARCHAR(255) в utf8 для хранения короткой строки, такой как "Нет мнения", требуется 11 байт на диске (десять символов младшей кодировки, плюс один байт для длины), но это занимает 765 байт в памяти и, следовательно, во временных таблицах или отсортированных результатах.
Я помог пользователям MySQL, которые по незнанию часто создавали временные таблицы объемом 1,5 ГБ и заполняли свое дисковое пространство. У них было много VARCHAR(255) столбцы, которые на практике хранят очень короткие строки.
Лучше всего определять столбец на основе типа данных, которые вы собираетесь хранить. У него есть преимущества для обеспечения ограничений, связанных с приложением, как уже упоминали другие люди. Но он имеет физические преимущества, чтобы избежать потери памяти, которую я описал выше.
Трудно понять, какой самый длинный почтовый адрес, конечно, поэтому многие люди выбирают длинный VARCHAR это, безусловно, дольше, чем любой адрес. И 255 является обычным, потому что это максимальная длина VARCHAR для которого длина может быть закодирована одним байтом. Это был также максимум VARCHAR длина в MySQL старше 5.0.
В дополнение к размеру и соображениям производительности при установке размера varchar (и, возможно, более важно, поскольку хранение и обработка становятся дешевле с каждой секундой), недостатком использования varchar(255) "просто потому, что" является снижение целостности данных.
Хорошо определить максимальные ограничения для строк , чтобы предотвратить попадание в СУБД длиннее, чем ожидалось, строк и вызвать переполнение буфера или исключения / ошибки при извлечении и разборе значений из базы данных, которые длиннее (больше байтов), чем ожидалось.
Например, если у вас есть поле, которое принимает двухсимвольные строки для аббревиатур стран, то у вас нет никаких оснований ожидать, что ваши пользователи (в этом контексте, программисты) будут вводить полные названия стран. Поскольку вы не хотите, чтобы они вводили "Антигуа и Барбуда" (AG) или "Острова Херда и МакДоналда" (HM), вы не разрешаете это на уровне базы данных. Кроме того, вполне вероятно, что некоторые программисты еще не RTFMed проектной документации (которая, безусловно, существует), чтобы знать, не делать этого.
Задайте для поля принимать два символа и дайте СУБД справиться с ним (либо изящно, обрезая, либо неблагоразумно отклоняя их SQL с ошибкой).
Примеры реальных данных, у которых нет причин превышать определенную длину:
- Канадские почтовые индексы имеют формат A1A1A1 и всегда имеют длину 6 символов, даже для Санта-Клауса (6 символов исключают пробел, который можно указать для разборчивости).
- адреса электронной почты - до 64 байт до @, до 255 байт после. Никогда больше, чтобы не сломать Интернет.
- Североамериканские телефонные номера не должны содержать более 10 цифр (исключая код страны).
- Компьютеры под управлением (последние версии) Windows не могут иметь имена компьютеров длиннее 63 байтов, хотя более 15 не рекомендуется и нарушит ферму серверов Windows NT.
- Сокращения штатов состоят из 2 символов (например, коды стран указаны выше)
- Номера для отслеживания UPS имеют длину 18, 12, 11 или 9 символов. Числа из 18 символов начинаются с "1Z", а числа из 11 символов начинаются с "T", что заставляет задуматься, как они доставляют все эти пакеты, если они не знают разницы между буквами и цифрами.
И так далее...
Потратьте время, чтобы подумать о ваших данных и их ограничения. Если вы архитектор, разработчик или программист, это ваша работа, в конце концов.
Используя varchar(n) вместо varchar (255), вы устраняете проблему, когда пользователи (конечные пользователи, программисты, другие программы) вводят неожиданно длинные данные , которые позже будут преследовать ваш код.
И я не говорил, что вы не должны также реализовывать это ограничение в коде бизнес-логики, используемом вашим приложением.
Я с тобой. Суетливое внимание к деталям является болью в шее и имеет ограниченную ценность.
Давным-давно диск был ценным товаром, и мы привыкли к пулям, чтобы оптимизировать его. Цена хранения упала в 1000 раз, что делает время, затрачиваемое на сжатие каждого байта, менее ценным.
Если вы используете только поля CHAR, вы можете получить строки фиксированной длины. Это может сэкономить реальное восстановление диска, если вы выбрали точные размеры для полей. Вы можете получить более плотно упакованные данные (меньше операций ввода-вывода для сканирования таблиц) и более быстрые обновления (проще найти открытые пространства в блоке для обновлений и вставок).
Однако, если вы переоцените свои размеры или ваши фактические размеры данных будут переменными, вы потеряете пространство с полями CHAR. Данные будут менее плотно упакованы (что приведет к большему количеству операций ввода-вывода для больших поисков).
Как правило, выигрыш в производительности от попытки задать размер для переменных полей незначителен. Вы можете легко сравнить с помощью VARCHAR(255) по сравнению с CHAR(x), чтобы увидеть, можете ли вы измерить разницу.
Однако иногда мне нужно дать подсказку "маленький", "средний", "большой". Поэтому я использую 16, 64 и 255 для размеров.
В настоящее время я не могу себе представить, что это действительно имеет значение.
Использование полей переменной длины приводит к дополнительным вычислительным затратам, но с избытком процессоров сегодня это даже не стоит рассматривать. Система ввода / вывода настолько медленна, что делает какие-либо вычислительные затраты на эффективную обработку varchars практически несуществующими. Фактически, цена varchar в вычислительном отношении, вероятно, является чистой победой над количеством дискового пространства, сэкономленного при использовании полей переменной длины над полями фиксированной длины. Скорее всего, у вас больше плотности строк.
Теперь сложность полей varchar заключается в том, что вы не можете легко найти запись по ее номеру. Если у вас фиксированный размер строки (с полями фиксированной длины), вычислить дисковый блок, на который указывает идентификатор строки, тривиально. С размером строки переменной длины этот вид выходит из окна.
Итак, теперь вам нужно поддерживать некоторый индекс номера записи, как и любой другой первичный ключ, ИЛИ вам нужно создать надежный идентификатор строки, который кодирует детали (например, блок и т. Д.) В идентификатор. Однако, если вы сделаете это, идентификатор придется пересчитать, если строка будет перемещена в постоянное хранилище. Ничего страшного, просто нужно переписать все записи индекса и убедиться, что вы либо а) никогда не выставляете его потребителю, либо б) никогда не утверждаете, что число достоверно.
Но поскольку у нас сегодня есть поля varchar, единственное значение varchar(16) по сравнению с varchar(255) состоит в том, что DB будет применять ограничение в 16 символов для varchar(16). Если предполагается, что модель БД фактически представляет физическую модель данных, то наличие длин полей может иметь значение. Однако, если это просто "хранилище", а не "модель И хранилище", в этом нет никакой необходимости.
Тогда вам просто нужно различить текстовое поле, которое индексируется (например, varchar), и что-то, чего нет (например, текстовое поле или поле CLOB). Индексируемые поля, как правило, имеют ограничение по размеру для облегчения индекса, в то время как поля CLOB этого не делают (в пределах разумного).
По моему опыту, если вы разрешите тип данных из 255 символов, какой-то глупый пользователь (или опытный тестировщик) действительно заполнит это.
Тогда у вас возникают всевозможные проблемы, в том числе, сколько места вы оставляете для этих полей в отчетах и на экране в вашем приложении. Не говоря уже о возможности превышения лимита на число строк в вашей базе данных (если у вас было более нескольких из этих 255 символьных полей).
Гораздо проще выбрать разумный лимит в начале, а затем применить его через приложение и базу данных.
В контексте mysql это может стать важным при работе с индексами в указанных столбцах varchar, так как mysql имеет максимум. ограничение в 767 байт на индексную строку.
Это означает, что при добавлении индекса в несколько столбцов varchar 255 вы можете довольно быстро / даже быстрее достичь этого предела для столбцов utf8 или utf8mb4, как указано в ответах выше.
Хорошей практикой является выделение лишь небольшого количества того, что вам нужно. Номера телефонов никогда не станут такими большими.
Одна из причин в том, что если вы не проверяете большие записи, несомненно, кто-то будет использовать все, что есть. Тогда у вас может закончиться место в вашем ряду. Я не уверен насчет лимита MySQL, но 8060 - это максимальный размер строки в MS SQL.
Более нормальное значение по умолчанию будет 50 imho, а затем увеличится, где необходимость доказывает это.