Сколько памяти занимает varchar

Обновлено: 03.07.2024

У меня есть таблица MySQL, где строки вставляются динамически. Поскольку я не могу быть уверен в длине строк и не хочу их обрезать, я делаю их varchar(200), который обычно намного больше, чем мне нужно. Есть ли большой хит производительности в предоставлении поля varchar намного больше длины, чем необходимо?

нет, в том смысле, что если значения, которые вы храните в этом столбце, всегда (скажем) меньше 50 символов, объявляя столбец как varchar(50) или varchar(200) имеет такую же производительность.

существует одно возможное влияние на производительность: в MySQL, временные таблицы и MEMORY таблиц магазине VARCHAR столбец как столбец фиксированной длины, заполненный до максимальной длины. Если вы проектируете VARCHAR столбцы намного больше, чем самый большой размер, который вам нужен, вы будете потреблять больше памяти, чем вам нужно. Это влияет на эффективность кэша, скорость сортировки и т. д.

VARCHAR идеально подходит для ситуации, которую вы описываете, потому что она означает "переменный символ" - предел, основанный на вашем примере, будет 200 символов, но что-то меньше принимается и не будет заполнять выделенный размер столбца.

VARCHAR также занимает меньше места - значения хранятся в виде однобайтового или двухбайтового префикса длины плюс данные. Префикс length указывает количество байтов в значении. Столбец использует один байт длины, если значения не требуют больше более 255 байт, два байта длины, если для значений может потребоваться более 255 байт.

для получения дополнительной информации, сравнивающей MySQL CHAR с типами данных VARCHAR, см. этой ссылке.

размер производительности! Чем меньше размер, тем лучше. Не сегодня и не завтра, но когда-нибудь ваши столы вырастут до размеров, когда дело дойдет до серьезных узких мест, независимо от того, какой дизайн вы выложили. Но вы можете предвидеть некоторые из тех потенциальных узких мест на этапе проектирования, которые могут произойти первыми, и попытаться расширить время, когда ваша БД будет работать быстро и счастливо, пока вам не понадобится переосмыслить свою схему или масштабировать по горизонтали, добавив больше серверов.

в вашем случае есть много утечек производительности, с которыми вы можете столкнуться: большие соединения почти невозможны с long varchar столбцы. Индексация на этих столбцах - настоящий убийца. Ваш диск должен хранить данные. Одна страница памяти может содержать меньше строк, а сканирование таблиц будет намного медленнее. Также кэш запросов вряд ли поможет вам здесь.

вы должны спросить себя: сколько вставок в год может произойти? Какова средняя длина? Мне действительно нужно больше 200 символов или я могу поймать это в своем приложение front-end, даже информируя пользователей о максимальной длине? Могу ли я разделить таблицу на узкую для быстрого индексирования и сканирования и другую для хранения дополнительных, менее часто необходимых данных расширяющегося размера? Могу ли я ввести возможные данные varchar в категории и таким образом извлечь некоторые данные в несколько меньших столбцов, возможно, int или bool-типа и сузить столбец varchar таким образом?

здесь можно многое сделать. Возможно, лучше всего сделать первое предположение и после этого перепроектируйте шаг за шагом используя реальные измеренные данные по представления. Удача.

производительность? Нет. Дисковое хранилище? Да, но это дешево и много. Если ваша база данных не вырастет до терабайтного масштаба, вы, вероятно, в порядке.

некоторые из вас ошибаются, думая, что varchar(200) занимает больше размера таблицы на диске, чем varchar(20) . Это не так. Только когда вы выходите за пределы 255 символов, mysql использует дополнительный байт для определения длины varchar данные поля.

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

когда размер каждого поля известен заранее, MySQL точно знает, сколько байтов находится между каждым полем / строкой и может переслать страницу вперед без чтения всех данных. Использование переменных символов уменьшает эту возможность оптимизации.

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

будучи varchar, а не просто char, размер основан на внутреннем поле, чтобы указать его фактическую длину и саму строку. Поэтому использование varchar (200) не очень отличается от использования varchar (150), за исключением того, что у вас есть потенциал для хранения больше.

и вы должны учитывать, что происходит при обновлении, когда строка растет. Но если это редкость, тогда все должно быть хорошо.

согласно данных названия этого имеет тип varchar, т. е. переменной символы хранения данных, MySQL и движок сам выделяет память используется в хранимых данных, поэтому производительность не ударил по моим сведениям.

вы должны попытаться просмотреть столбец varchar так же, как и столбец char в большинстве сценариев, и установить длину консервативно. Вам не обязательно всегда думать о модификаторе var как о чем-то, что влияет на принятие решений на максимальную длину. Это действительно следует рассматривать как подсказку производительности, а не то, что поставляемые строки будут разной длины.

Это не директива, которой должны строго следовать внутренние базы данных, ее можно полностью игнорировать. Однако позаботьтесь об этом, поскольку иногда реализация может протекать (например, фиксированная длина и прокладка), хотя это не должно быть в идеальном мире.

Если у вас есть varchar(255), то у вас нет гарантии, что производительность будет всегда вести себя по-разному с char(255) во всех обстоятельствах.

может показаться простым установить его на что-то вроде 255, 65535 и т. д. В соответствии с рекомендациями, приведенными в руководстве о требованиях к хранению. Это дает впечатление, что любое значение между 0 (Да, это вещь) и 255 будет иметь такое же влияние. Однако это не то, что можно полностью гарантировать.

требования к хранению, как правило, верны или являются хорошим показателем для достойных и зрелых постоянных хранилищ с точки зрения хранения строк. Это не такой сильный индикатор для таких вещей, как индексы.

иногда это сложный вопрос, как долго должен быть кусок строки, чтобы настроить его на самую высокую границу вы знаете, что это должно быть внутри, но это не влияет. К сожалению, это часто остается пользователю работать, и это действительно несколько произвольно. Вы не можете сказать, что никогда не увеличивайте строку, потому что, возможно, есть случаи, когда вы не совсем уверены.

вы должны убедиться, что запросы MySQL выдают ошибку, когда строка слишком длинная, а не усеченная, так что, по крайней мере, вы знаете, может ли она быть слишком короткой от выбросов ошибок. Изменение размера столбцов для их увеличения или сокращения дорогостоящая операция DDL, это следует иметь в виду.

набор символов также следует учитывать, где длина и производительность вступают в игру. Длина относится к этому, а не к байтам. Если использовать utf8, например, (не MB4), то varchar(255) действительно varbinary (3 * 255). Трудно понять, как такие вещи действительно будут развиваться без запуска тестов и глубокого изучения исходного кода/документации. Из-за этого существует возможность для чрезмерной длины, чтобы иметь неожиданно раздутый удар. это относится не только к производительности. Если вам однажды нужно изменить набор символов столбца varchar на более крупный, вы можете в конечном итоге нажать какой-то предел без обращения, если вы позволите безвозмездно длинным строкам присутствовать, чего можно было бы избежать. Это обычно довольно нишевая проблема, но она возникает, недавно это была значительная проблема с введением utf8mb4 для MySQL и индексов, которые имеют ограничение на ключ длина.

Если окажется, что MAX(LENGTH (column)) всегда

различные двигатели, некоторые могут игнорировать его вообще.
размеры буфера, например обновление или вставка может должны выделить полные 255 (хотя я не проверил исходный код, чтобы доказать это, это только гипотетически).
индексы, это будет сразу очевидно, если вы попытаетесь сделать составной ключ из множества столбцов varchar (255).
промежуточные таблицы и, возможно, результирующие наборы. Учитывая то, как работают транзакции, не всегда возможно использовать фактическую максимальную длину строк в столбце В отличие от определенной предел.
внутренние предиктивные оптимизации могут принимать максимальную длину в качестве входных данных.
изменения в версиях реализации базы данных.

Как правило, действительно не нужно, чтобы varchar был длиннее, чем он должен быть в любом случае, проблемы с производительностью или нет, поэтому я рекомендую придерживаться этого, когда вы можете. Принятие дополнительных усилий для выборки размера ваших данных, обеспечения истинного предела или выяснения истинного предела путем запроса / исследования является идеальным подход.

когда вы не можете, если вы хотите сделать что-то вроде varchar(255) для случаев, когда сомневаетесь, я рекомендую делать науку. Это может состоять из дублирования таблицы, уменьшения размера столбца var char, а затем копирования данных в него из оригинала и просмотра размера данных индекса/строки (индексируйте столбец, а также попробуйте его в качестве первичного ключа, который может вести себя по-разному в InnoDB, поскольку строки упорядочены первичным ключом). По крайней мере, так ты знайте, если у вас есть влияние на IO, который, как правило, является одним из самых чувствительных узких мест. Тестирование на использование памяти сложнее, это трудно проверить, что исчерпывающе. Я бы рекомендовал тестировать потенциальные наихудшие случаи (запросы с большим количеством промежуточных результатов в памяти, проверить с explain для больших временных таблиц и т. д.).

Если вы знаете, что в таблице не будет много строк, вы не будете использовать столбец для соединений, индексов (особенно составных, уникальных) и т. д., то вы скорее всего, не будет много проблем.

Каким образом резервируется память для типа varchar? Например, есть таблица, в которой столбец обьявлен как varchar(128). Что происходит, если я вставляю, строку, которая, скажем, содержит лишь 20 символов? Будет ли память для остальных 108 символов зарезервирована СУБД? Также интересно поведение при вставке null. И, главный вопрос, насколько важно иметь тип минимального размера в целях рационального использования памяти (то есть не допускать явного излишества при создании схемы БД)?

Будет ли память для остальных 108 символов зарезервирована СУБД? Нет. интересно поведение при вставке null. Как правило - отдельный байт. насколько важно иметь тип минимального размера в целях рационального использования памяти В пределах 2^(8n)-1 - пофиг. @Akina то есть, я могу создавать столбцы varchar(1024), вставлять туда по одному символу для каждой строки и по памяти никакой раницы с varchar(1) не будет? Нет разницы между VARCHAR(1) и VARCHAR(255). Нет разницы между VARCHAR(256) и VARCHAR(65535). А вот между VARCHAR(255) и VARCHAR(256) разница есть, целый байт. @Akina, это вы про mysql. В postgresql нет лимита в 65535 байт и потому нет двухбайтовой записи. Но и для mysql, к слову, важна оговорка про кодировку. Потому что длина строки в байтах и именно на строки до максимум 255 байт берётся один байт под хранение длины строки, а varchar(что-то) - в символах.

На самом деле вы затронули не очень простой и не самый короткий вопрос. Мой ответ касается строго PostgreSQL , с ссылками на его исходный код и детали реализации (потому что могу).

Цифра в скобках varchar - это только ограничение. Не влияет совершенно ни на что, кроме получения ошибки за попытку записать что-то превышающее этот лимит. И сразу важнейшая оговорка: эта цифра - длина в символах, не в байтах. Вероятно читателю уже понятно, что формат хранения не может зависеть от этой цифры. Не дело это, когда для разных символов одной длины нам надо хранить различающийся в 4 раза объём данных (бывают более длинные кодировки? может быть, но я не знаю. Юникод до 4 байт на символ пока ещё влезает).

Для хранения текстов базово действует правило:

The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1. Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1 GB.

в общих чертах используется стиль хранения данных с записью длины строки в байтах в начале записи
строки короче 126 байт кодируются в виде структуры varattrib_1b: 1 байт для хранения длины строки (в байтах), но из которых 1 бит зарезервирован как маркер того, что это 1-байтовый формат хранения.
если в 126 байт строка не влезла - то используем длинную форму varattrib_4b: здесь уже используется 4 байта на заголовок, что позволяет хранить существенно более длинные строки. Опять же некоторое биты зарезервированы, но до 1гб данных сохранить возможно.

Однако особые приключения начинаются позже. PostgreSQL манипулирует данными только фиксированными блоками по 8кб (обычно, настройка времени компиляции СУБД). Как 1гб данных записать в таком случае? На помощь приходит огромный фокус ушами (а у слона уши-то большие, так что всё с нами ясно, с таким-то логотипом): длинные строки нарезаются на части и отправляются в отдельную TOAST таблицу. И здесь используется другая форма заголовка, varattrib_1b_e . Где вместо данных хранится идентификатор по которому нужные данные можно прочитать из TOAST .

Плюс к тому, длинные строки могут сжиматься самой базой. Длинной считаются 1/4 блока (2кб то есть). Могут сжиматься после перемещения в TOAST , а могут - до. То есть прямо на месте сжали и сохранили, если сжатые данные влезли в 2кб. (через alter table в некоторых пределах можно этой логикой управлять)

И так мы приходим ко второй форме уже знакомого varattrib_4b - Compressed-in-line формат. Теперь после уже известных 4 байт заголовка с длиной данных на диске ещё хранится в других 4 байтах длина несжатого текста.

И весь фокус в том, что всё это многообразие никак не зависит от объявления таблицы. Формат используется тот, который подходит для нужных данных.

В общем я старательно запутал с internals кухней. Так что отдельно и по конкретным вопросам:

Что происходит, если я вставляю, строку, которая, скажем, содержит лишь 20 символов?

20 символов меньше порога в 126 символов. Поэтому база сможет использовать короткую форму с заголовком в 1 байт. Следовательно строка займёт. От 21 до 81 байта. Смотря какую кодировку ваша база использует и какие это символы. Например,

Строка "привет" в UTF8 занимает 12 байт, поэтому pg_column_size насчитает 13 байт итог.

Будет ли память для остальных 108 символов зарезервирована СУБД?

Нет, не будет. Если говорить именно о varchar, а не char. В этом и заключается их различие. Притом именно для PostgreSQL никаких бенефитов от такого поведения char нет. Это просто требование стандарта.

я могу создавать столбцы varchar(1024), вставлять туда по одному символу для каждой строки и по памяти никакой раницы с varchar(1) не будет?

Да, верно. Число - это ограничение на данные. Формат хранения от него не зависит.

И, главный вопрос, насколько важно иметь тип минимального размера в целях рационального использования памяти (то есть не допускать явного излишества при создании схемы БД)?

В моей практике бывала ситуация, когда по ошибке в поле вместо 10 символов прилетает 10 мегабайт непойми чего от приложения. По ошибке в момент записи место ошибки в приложении искать банально удобнее.

Про NULL отдельно

Все NULL в PostgreSQL независимо от типа поля хранятся идентично - в битовой маске после заголовка tuple (одной строки таблицы). Использование памяти: 1 байт на каждые 8 полей которые могут быть NULL. (с округлением вверх, конечно)

При вставке NULL проставляется 1 бит соответствующий этому полю в битовой маске и затем данные этого поля не пишутся вовсе никак. Поле NULL, и одного бита в битовой маске более чем достаточно, нет нужды хранить что-то ещё.

Символьные типы данных имеют фиксированный (char) или переменный (varchar) размер. Начиная с SQL Server 2019 (15.x) при использовании параметров сортировки с поддержкой UTF-8 эти типы данных хранят весь диапазон символьных данных Юникод и используют кодировку UTF-8. Если указаны параметры сортировки без поддержки UTF-8, эти типы данных хранят только подмножество символьных данных, поддерживаемых соответствующей кодовой страницей указанных параметров сортировки.

Аргументы

char [ ( n ) ] — строковые данные фиксированного размера. n определяет размер строки в байтах и должно иметь значение от 1 до 8000. Для однобайтовых кодировок, таких как Latin, размер при хранении равен n байт, а количество хранимых символов — тоже n. Для многобайтовых кодировок размер при хранения тоже равен n байт, но количество хранимых символов может быть меньше n. Синонимом по стандарту ISO для типа char является character. Дополнительные сведения о кодировках см. в статье Однобайтовые и многобайтовые кодировки.

varchar [ ( n | max ) ] — строковые данные переменного размера. Используйте значение n для определения размера строки в байтах (допускаются значения от 1 до 8000) или используйте max для указания предельного размера столбца вплоть до максимального размера хранилища, что составляет 2^31-1 байт (2 ГБ). Для однобайтовых кодировок, таких как Latin, размер при хранении равен n байт + 2 байта, а количество хранимых символов — n. Для многобайтовых кодировок размер при хранении тоже равен n байт + 2 байта, но количество хранимых символов может быть меньше n. Синонимами по стандарту ISO для типа varchar являются типы charvarying или charactervarying. Дополнительные сведения о кодировках см. в статье Однобайтовые и многобайтовые кодировки.

Remarks

Часто ошибочно считают, что в типах данных CHAR(n) и VARCHAR(n) число n указывает на количество символов. Однако на самом деле число n в CHAR(n) и VARCHAR(n) — это длина строки в байтах (0–8000). n никогда не определяет количество хранимых символов. То же самое верно и в отношении типов NCHAR(n) и NVARCHAR(n). Причина этого заблуждения в том, что при использовании однобайтовых кодировок размер данных типов CHAR и VARCHAR при хранении равен n байт, а количество символов — тоже n. Однако в случае с многобайтовыми кодировками, такими как UTF-8, в старших диапазонах Юникода (128–1 114 111) один символ занимает два или несколько байтов. Например, в столбце, определенном как CHAR(10), Компонент Database Engine может хранить 10 символов, использующих однобайтовую кодировку (диапазон Юникода 0–127), но меньше 10 символов при использовании многобайтовой кодировки (диапазон Юникода 128–1 114 111). Дополнительные сведения о хранении символов Юникода и их диапазонах см. в разделе Различия в хранении UTF-8 и UTF-16.

Если значение n в определении данных или инструкции объявления переменной не указано, длина по умолчанию равна 1. Если значение n не указано при использовании функций CAST и CONVERT, длина по умолчанию равна 30.

Объектам, в которых используются типы данных char и varchar, назначаются параметры сортировки базы данных по умолчанию, если только иные параметры сортировки не назначены с использованием предложения COLLATE. Параметры сортировки контролируют кодовую страницу, используемую для хранения символьных данных.

В SQL Server многобайтовые кодировки включают:

двухбайтовые кодировки (DBCS) для некоторых языков Восточной Азии, использующих кодовые страницы 936 и 950 (китайский), 932 (японский) или 949 (корейский).
UTF-8 с кодовой страницей 65001. Применимо к: SQL Server (начиная с SQL Server 2019 (15.x))

Если у вас есть сайты, поддерживающие несколько языков, примите к сведению следующие рекомендации:

Для поддержки Юникода и минимизации проблем с преобразованием символов рекомендуем использовать параметры сортировки с поддержкой UTF-8 (начиная с SQL Server 2019 (15.x)).
Если используется более ранняя версия Компонент SQL Server Database Engine, то чтобы избежать проблем с преобразованием символов, рекомендуем использовать типы данных Юникода nchar или nvarchar.

Если вы используете char или varchar, мы рекомендуем:

Если размеры записей данных столбцов постоянны, используйте char.
Если размеры записей данных столбцов значительно изменяются, используйте varchar.
использовать varchar(max) , если размеры записей данных в столбцах существенно отличаются и длина строки может превышать 8000 байт.

Если SET ANSI_PADDING равно OFF при выполнении CREATE TABLE или ALTER TABLE, столбец char, определенный как NULL, обрабатывается как varchar.

Для каждого ненулевого столбца varchar(max) или nvarchar(max) требуется 24 байта дополнительного фиксированного выделения, которые учитываются в максимальном размере строки в 8060 байт во время операции сортировки. Это может создать неявное ограничение в ряде ненулевых столбцов varchar(max) или nvarchar(max), которые могут быть созданы в таблице. При создании таблицы или во время вставки данных не возникает особых ошибок (кроме обычного предупреждения о том, что максимальный размер строки превышает максимально допустимое значение в 8060 байт). Такой размер строки может вызывать ошибки (например, ошибку 512) во время некоторых обычных операций, таких как обновление ключа кластеризованного индекса, или сортировки полного набора столбцов, которая происходит только во время выполнения операции.

Преобразование символьных данных

При преобразовании символьного выражения в символьный тип данных другой длины значения, слишком длинные для нового типа данных, усекаются. Тип uniqueidentifier считается символьным типом, используемым при преобразовании из символьного выражения, поэтому на него распространяются правила усечения при преобразовании в символьный тип. См подраздел «Примеры» ниже.

Если символьное выражение преобразуется в символьное выражение другого типа данных или размера, например из char(5) в varchar(5) или из char(20) в char(15) , то преобразованному значению присваиваются параметры сортировки входного значения. Если несимвольное выражение преобразуется в символьный тип данных, то преобразованному значению присваиваются параметры сортировки, заданные по умолчанию в текущей базе данных. В любом случае необходимые параметры сортировки можно присвоить с помощью предложения COLLATE.

Преобразование кодовых страниц поддерживается для типов данных char и varchar, однако поддержка типа данных text не предусмотрена. Как и в ранних версиях SQL Server, о потере данных во время преобразования кодовых страниц не сообщается.

Символьные выражения, которые преобразуются в приближенный тип данных numeric, могут содержать необязательную экспоненциальную нотацию (символ e нижнего регистра или E верхнего регистра, за которым следуют необязательный знак плюс (+) или минус (–) и число).

Символьные выражения, преобразуемые в точный тип данных numeric, должны состоять из цифр, десятичного разделителя и необязательного знака плюс (+) или минус (–). Начальные пробелы не учитываются. Разделители в виде запятой запрещены (например, десятичный разделитель в числе 123 456,00).

Кроме того, символьные выражения, преобразуемые в типы данных money или smallmoney, могут содержать необязательный десятичный разделитель и обозначение валюты. Разрешаются разделители в виде запятой, например 123 456,00 руб.

Когда пустая строка преобразовывается в тип int, ее значение становится равным 0 . Когда пустая строка преобразовывается в дату, ее значением становится значение даты по умолчанию, то есть 1900-01-01 .

Примеры

A. Отображение значения по умолчанию n при использовании в объявлении переменной

В приведенном ниже примере показано, что значение по умолчанию n равно 1 для типов данных char и varchar , если они используются в объявлении переменной.

Б. Отображение значения по умолчанию n при использовании функций CAST и CONVERT с типом данных varchar

В приведенном ниже примере показано, что значение по умолчанию n равно 30, если типы данных char или varchar используются с функциями CAST и CONVERT .

В. Преобразование данных для отображения

В следующем примере два столбца преобразуются в символьные типы, после чего к ним применяется стиль, применяющий к отображаемым данным конкретный формат. Тип money преобразуется в символьные данные. К нему применяется стиль 1, отображающий значения с запятыми между каждой группой из трех цифр, отсчитывая влево от десятичной точи, и каждой группой из двух цифр, отсчитывая вправо от десятичной точки. Тип datetime преобразуется в символьные данные. К нему применяется стиль 3, отображающий данные в формате дд/мм/гг. В предложении WHERE тип money приводится к символьному типу для выполнения операции сравнения строк.

Г. Преобразование данных uniqueidentifier

В следующем примере значение uniqueidentifier преобразуется в тип данных char .

Следующий пример показывает усечение данных, когда значение является слишком длинным для преобразования в заданный тип данных. Так как тип данных uniqueidentifier ограничен 36 символами, все символы, выходящие за пределы этой длины, будут усечены.

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

Например, в столбцах DATETIME и TIMESTAMP можно хранить один и тот же тип данных: дату и время, с точностью до секунды. Однако тип TIMESTAMP требует вдвое меньше места, позволяет работать с часовыми поясами и обладает специальными средствами автоматического обновления. С другой стороны, диапазон допустимых значений для него намного уже.

На что обратить внимание при выборе типа данных:

Стараться использовать типы данных минимального размера, достаточного для их правильного хранения и представления. Как правило, меньшие по размеру типы данных быстрее, поскольку занимают меньше места на диске, в памяти и в кэше процессора.
Чем проще, тем лучше. C точки зрения системы, сравнение целых чисел проще сравнения символов, поскольку из-за различных кодировок и правил сортировки сравнение символов усложняется. Поэтому значения даты и времени лучше хранить во встроенных типах данных MySQL, а не в строках, а для IP-адресов имеет смысл использовать целочисленные типы данных.
Стараться избегать значений NULL. Для MySQL оптимизация запросов, содержащих допускающие NULL столбцы, вызывает дополнительные сложности, поскольку из-за них усложняются индексы, статистика индексов и сравнение значений. Столбец, допускающий NULL, занимает больше места на диске и требует специальной обработки внутри MySQL. Если есть необходимость отобразить в таблице факт отсутствия значения, можно обойтись без использования NULL. Вместо этого, к примеру, можно использовать 0, специальное значение или пустую строку.
Имеет смысл использовать в связанных столбцах одни и те же типы данных. Использование различных типов данных в связанных столбцах может замедлить обработку запроса.

Скорость при соединении столбцов типа VARCHAR и ENUM:

Соединение VARCHAR с VARCHAR

Соединение VARCHAR с ENUM

Соединение ENUM с VARCHAR

Соединение ENUM с ENUM

В целях совместимости MySQL поддерживает различные псевдонимы, например INTEGER, BOOL — это псевдонимы (синонимы) одного и того же типа данных. Данный факт может сбить с толку, но не оказывает влияния на производительность.

Числовые типы

BIT — можно использовать для хранения одного или нескольких значений true/false в одном столбце. BIT(1) определяет поле, содержащее один бит, BIT(2) — два бита и т. д. Максимальная длина столбца типа BIT равна 64 битам. До версии MySQL 5.0 слово BIT было синонимом TINYINT. Поведение типа BIT зависит от подсистемы хранения.

Хранение целых чисел

TINYINT(N) (синоним INTEGER, BOOL, BOOLEAN) — 8 бит;
SMALLINT(N) — 16 бит;
MEDIUMINT(N) — 24 бита;
INT(N) — 32 бита;
BIGINT(N) — 64 бита.

СУБД MySQL позволяет указывать для целых чисел «размер», например INT(11). Для большинства приложений это не имеет значения: диапазон возможных значений этим не ограничивается. Однако данный параметр говорит некоторым интерактивным инструментам MySQL, сколько позиций необходимо зарезервировать для вывода числа. С точки зрения хранения и вычисления INT(1) и INT(20) идентичны.

Целочисленный тип данных длиной N бит позволяет хранить значения от -2(N-1) до 2(N-1)-1.

Целые типы данных могут иметь необязательный атрибут UNSIGNED, запрещающий отрицательные значения и приблизительно вдвое увеличивающий верхний предел положительных значений. Например, тип TINYINT UNSIGNED позволяет хранить значения от 0 до 255, а не от -128 до 127.

Знаковые и беззнаковые типы требуют одинакового пространства и обладают одинаковой производительностью.

Необязательный атрибут ZEROFILL заполнит нулями свободные позиции слева. Например с TINYINT(3) ZEROFILL, величина 2 будет записана, как 002.

Тип данных	Бит	По умолчанию	UNSIGNED
TINYINT	8	-128 — 127	0 — 255
SMALLINT	16	-32768 — 32767	0 — 65535
MEDIUMINT	24	-8388608 — 8388607	0 — 16777215
INT	32	-2147483648 — 2147483647	0 — 4294967295
BIGINT	64	-9223372036854775808 — 9223372036854775807	0 — 18446744073709551615

Хранение дробных чисел

Все типы допускают приближенные математические вычисления с плавающей точкой, но в случае с FLOAT и DOUBLE операции выполняются быстрее, так как процессор выполняет их естественным для него образом.

FLOAT(M,D) — число с плавающей точкой небольшой точности, задействует 4 байта.
DOUBLE(M,D) (синонимы REAL, DOUBLE PRECISION) — число с плавающей точкой двойной точности. Задействует 8 байт, имеет большую точность и больший диапазон значений.
DECIMAL(M,D) (синонимы DEC, NUMERIC, FIXED) — дробное число, хранящееся в виде строки, если десятичное значение равно 0, значение не будет иметь десятичной запятой или дробной части. Предназначен для хранения точных дробных чисел (можно хранить большие целые числа, не помещающиеся в типе BIGINT). Имеет смысл использовать только тогда, когда нужны точные результаты при вычислениях с дробными числами, — например, при хранении финансовых данных. Задействует больше пространства.

M — количество отводимых под число символов. D — количество символов дробной части.

Для типов с плавающей точкой можно указать максимально разрешенное количество цифр до и после десятичной запятой. В случае с DECIMAL это влияет на объем пространства, требуемого для хранения данных столбца. При указании точности, в некоторых случаях, MySQL незаметно для пользователя может выбирать другой тип данных или будет округлять значения при сохранении. Поэтому рекомендуют указывать желаемый тип, но не точность.

FLOAT и DOUBLE могут иметь параметр UNSIGNED, запрещающий отрицательные числа, но диапазон значений от этого не изменится.

Число типа DECIMAL в MySQL 5.0 и более новых версиях может содержать до 65 цифр. В более ранних версиях MySQL тип DECIMAL имел предел 254 цифры и хранил значения в виде неупакованных строк (один байт на цифру). Однако эти версии СУБД не умели использовать такие большие числа в вычислениях, поскольку тип DECIMAL был просто форматом хранения. При выполнении каких-либо операций значения DECIMAL преобразовывались в тип DOUBLE.

Строковые типы

В типах CHAR и VARCHAR строки рассматриваются как последовательности символов, поэтому, при использовании многобайтных кодировок, например UNICODE, размер строки в байтах будет больше, чем в символах.

VARCHAR(N) — хранит символьные строки переменной длины и является наиболее общим строковым типом данных. Значение N может принимать значения от 0 до 65535 (до версии MySQL 5.0.3 значение N могло быть от 0 до 255). Строки этого типа могут занимать меньше места, чем строки фиксированной длины CHAR. Происходит это потому, что в VARCHAR используется лишь то количество места, которое действительно необходимо (за исключением таблиц у которых задан фиксированный размер строк). В типе VARCHAR используется один или два дополнительных байта для хранения длины строки: один байт, если максимальная длина строки в столбце не превышает 255 байт, и два байта в случае более длинных строк. Т.е. тип VARCHAR(10) может занимать до 11 байт. Тип VARCHAR(1000) занимает до 1002 байт, поскольку в данном случае для хранения информации о длине строки требуется два байта. VARCHAR увеличивает производительность за счет меньшего потребления места на диске. Однако поскольку строки имеют переменную длину, они способны увеличиваться при обновлении, что вызывает дополнительную нагрузку. Если строка становится длиннее и больше не помещается в ранее отведенное для нее место, то ее дальнейшее поведение зависит от подсистемы хранения. Обычно имеет смысл использовать тип VARCHAR при соблюдении хотя бы одного из следующих условий: максимальная длина строки в столбце значительно больше средней; обновление поля выполняется редко, так что фрагментация не представляет проблемы; либо используется сложная кодировка, например UTF-8, в которой для хранения одного символа используется переменное количество байтов.
CHAR(N) — имеет фиксированную длину, от 0 до 255 байт. При сохранении коротких значений CHAR они дополняются справа пробелами до указанной длины. Тип CHAR полезен, когда требуется сохранять очень короткие строки или все значения имеют приблизительно одинаковую длину. Например, CHAR является хорошим выбором для хранения MD5-сверток паролей пользователей, которые всегда имеют одинаковую длину. Тип CHAR также имеет преимущество над VARCHAR для часто меняющихся данных, поскольку строка фиксированной длины не подвержена фрагментации. В случае очень коротких столбцов тип CHAR также эффективнее, чем VARCHAR.

При создании таблицы нельзя комбинировать столбцы типов CHAR и VARCHAR. Если такое произойдет, то MySQL изменит тип столбцов CHAR на тип VARCHAR.

Представим что строковый тип применяется для хранения значений Y и N. В случае использования CHAR(1) значение займет один байт, тогда как для типа VARCHAR(1) потребуется два байта из-за наличия дополнительного байта длины строки.

Значение	CHAR(4)	Требуется хранилище	VARCHAR(4)	Требуется хранилище
''	' '	4 байта	''	1 байт
'ab'	'ab '	4 байта	'ab'	3 байта
'abcd'	'abcd'	4 байта	'abcd'	5 байт
'abcdefgh'	'abcd'	4 байта	'abcd'	5 байт

Двоичные строки

Для совместимости со старыми версиями MySQL введены два специальных типа данных: BINARY и VARBINARY, которые эквивалентны типам CHAR и VARHAR, однако строка в них рассматривается как последовательность байтов, а не символов. К BINARY строкам не применимы кодировки и сортируются они как обычные последовательности байтов. Эти типы могут быть полезны, когда нужно сохранять двоичные данные, и вы хотите, чтобы MySQL сравнивал значение как байты, а не как символы. При этом, двоичное сравнение может оказаться значительно проще и быстрее символьного.

VARBINARY — хранит бинарные строки переменной длины.
BINARY — хранит бинарные строки фиксированной длины.

Текстовые и бинарные типы

Предназначены для хранения больших объемов двоичных или символьных данных.

MySQL обрабатывает значения BLOB и TEXT как отдельные объекты. Единственное различие между семействами BLOB и TEXT заключается в том, что типы BLOB хранят двоичные данные без учета схемы упорядочения и кодировки, а с типами TEXT ассоциированы схемы упорядочения и кодировка.

Семейство TEXT используется для хранения непосредственно текста:

Cемейство BLOB — для хранения изображений, звука, электронных документов и т.д.:

MySQL не может индексировать данные этих типов по полной длине и не может использовать для сортировки индексы.

Я хотел бы знать, каков максимальный размер для типа MySQL VARCHAR.

Я читал, что максимальный размер ограничен размером строки, который составляет около 65 КБ. Я попытался установить поле, varchar(20000) но оно говорит, что оно слишком большое.

Я мог бы установить это varchar(10000) . Какой точный максимум я могу установить?

Имейте в виду, что MySQL имеет максимальный размер строки

Максимальное значение размера строки во внутреннем представлении таблицы MySQL составляет 65 535 байт, не считая BLOB и TEXT. Столбцы BLOB и TEXT вносят только 9-12 байт в ограничение размера строки, поскольку их содержимое хранится отдельно от остальной части строки. Узнайте больше об ограничениях на количество столбцов в таблице и размер строки.

Максимальный размер, который может занимать один столбец, отличается до и после MySQL 5.0.3

Значения в столбцах VARCHAR являются строками переменной длины. Длина может быть указана как значение от 0 до 255 до MySQL 5.0.3 и от 0 до 65 535 в 5.0.3 и более поздних версиях. Эффективная максимальная длина VARCHAR в MySQL 5.0.3 и более поздних версиях зависит от максимального размера строки (65 535 байт, который распределяется между всеми столбцами) и используемого набора символов.

Однако обратите внимание, что ограничение ниже, если вы используете многобайтовый набор символов, такой как utf8 или utf8mb4.

Используйте TEXT типы по порядку, чтобы преодолеть ограничение размера строки.

Четырьмя типами TEXT являются TINYTEXT, TEXT, MEDIUMTEXT и LONGTEXT. Они соответствуют четырем типам BLOB и имеют одинаковую максимальную длину и требования к хранению.

Подробнее о типах BLOB и TEXT

Даже больше

Ознакомьтесь с более подробной информацией о требованиях к хранению типов данных, которые касаются требований к хранению для всех типов данных.

Я стараюсь избегать столбцов TEXT, поскольку они могут создавать временные таблицы при их наличии и сортировке Если я возьму varchar (200) для имени и сохраню только 6 символов в этом поле, то сколько байтов будет занято имя? @PareshGami - 6 + 1 = 7 символов! В отличие от CHAR, значения VARCHAR сохраняются как префикс длиной 1 байт или 2 байта плюс данные. больше .

Согласно онлайн-документам , существует ограничение в 64 КБ, и вы можете определить размер строки, используя:

Вы должны иметь в виду, что длины столбцов не являются однозначным отображением их размера. Например, CHAR(10) CHARACTER SET utf8 требуется три байта для каждого из десяти символов, поскольку эта конкретная кодировка должна учитывать свойство «три байта на символ» utf8 (это кодировка MySQL, utf8 а не «настоящий» UTF-8, который может иметь до четырех байтов). ).

Но, если ваш размер строки приближается к 64 КБ, вы можете изучить схему вашей базы данных. Это редкая таблица, которая должна быть настолько широкой в правильно настроенной (3NF) базе данных - это возможно, но не очень часто.

Если вы хотите использовать больше, вы можете использовать BLOB или TEXT . Они не учитываются при ограничении строки в 64 КБ (кроме небольшого административного следа), но вам необходимо знать о других проблемах, связанных с их использованием, таких как невозможность сортировки с использованием всего текстового блока за пределами определенного числа. символов (хотя это может быть настроено вверх), заставляя временные таблицы быть на диске, а не в памяти, или настраивая буферы связи клиента и сервера для эффективной обработки размеров.

У вас все еще есть несоответствие байтов / символов (так что в MEDIUMTEXT utf8 столбце может храниться «только» около полумиллиона символов (16M-1)/3 = 5,592,405 ), но это все равно значительно расширяет ваш диапазон.

Читайте также: