Пожалуйста, указывайте первоисточник при перепечатке, спасибо!
Тип varchar используется для хранения строк переменной длины и является наиболее распространенным строковым типом данных. Он более экономичен, чем типы с фиксированной длиной, потому что он использует только необходимое пространство (например, более короткие строки занимают меньше места).
Эта статья основана на базе данных MySQL для обсуждения.
Версия MySQL: Версия сервера: 5.7.16 MySQL Community Server (GPL).
1. Проблема распределения длины varchar
Всегда был вопрос о распределении длины строк переменной длины в базе данных: я не знаю, как долго это должно быть выделено.
Сегодня давайте посмотрим, на чем основана длина varchar?
-
Некоторые говорят: «Пока есть атрибут, назначай как можно дольше». ---- которыйВыделяйте только то пространство, которое вам действительно нужно;
-
Другие говорили: «Длина атрибута должна быть кратна 4, так как это позволяет
内存对齐". ---- которыйДлины, выделенные как кратные 4, могут быть内存对齐; -
Другие экстремалы думают: «Поскольку для varchar чем короче используется строка, тем меньше места используется, поэтому нет проблем в том, чтобы установить длину больше, и выделить длину 4000, что позволяет избежать возникновения превышения длины. проблема в том, что данные не могут быть сохранены", то естьВыделяет большую длину независимо от длины атрибута, например: 4000.
Так какое утверждение верно?
Возьмем пример.Предположим, мы хотим создать таблицу для хранения трех атрибутов в профиле CSDN: никнейм, регион и профиль. Ниже приведены три версии оператора построения таблицы, созданные для разных операторов.
-- 版本 1：只分配真正需要的空间
create table user_info (
user_id long primary key,
nickname varchar(20),
region varchar(100),
introduction varchar(500)
);
-- 版本 2：在版本 1 的基础上调整为 4 的倍数
create table user_info (
user_id long primary key,
nickname varchar(32),
region varchar(128),
introduction varchar(512)
);
-- 版本 3：长度都取 4000
create table user_info (
user_id long primary key,
nickname varchar(4000),
region varchar(4000),
introduction varchar(4000)
);
Изучив множество данных, я пришел к выводу:版本 1является лучшим, т.е.Выделяйте только то пространство, которое вам действительно нужно.
2. Анализ заключения
Вот почему:
(1) varchar требует 1 или 2 дополнительных байта для записи длины строки
Если максимальная длина столбца меньше или равна 255 байтам, для его представления используется только 1 байт, в противном случае используются 2 байта. При использовании набора символов latin1 для столбца varchar(10) требуется 11 байт памяти. Для столбца varchar(1000) требуется 1002 байта, потому что 2 байта необходимы для хранения информации о длине.
(2)内存对齐Некоторый выигрыш в производительности действительно есть, но определение длины поля как кратного 4Не могу выполнить выравнивание памяти
Для типа varchar строка имеет переменную длину, ее фактическая длина хранения:1 或 2 个额外字节 + 字符串实际长度. Приведу несколько примеров:
а. Сохранить в varchar(8)"abcdefgh", фактическая длина хранения должна быть1 + 8 = 9байт, первый байт записывает длину строки;
б) хранить в varchar(8)"abc", фактическая длина хранения1 + 3 = 4байт.
в. Хранить в varchar(1000)"abc", фактическая длина хранения2 + 3 = 5байт.
Когда дело доходит до выравнивания памяти, давайте кратко упомянем структуру страницы данных механизма хранения MySQL InnoDB: страница — это базовая единица управления пространством хранения MySQL, она состоит из одной или нескольких строк, а строка соответствует записи в таблице. , Чтение данных также основано на странице, а не на построчном чтении. Подробнее см.:Структура страницы данных InnoDB.
(3) MySQL необходимо создавать неявные временные таблицы (также разделенные на временные таблицы памяти и временные таблицы диска) при решении определенных типов запросов.
Для полей во временной таблице MySQL выделит пространство фиксированной длины, достаточное для хранения. Это пространство фиксированной длины должно быть достаточно длинным, чтобы разместить в нем самую длинную строку. Например, если это столбец varchar, ему необходимо выделить всю его длину. Если используется набор символов UTF-8, MySQL зарезервирует три слова для каждого персонажа Фестиваль.
Предположим, мы инициируем создание неявной временной таблицы при запросе user_info.Для столбца псевдонима, если мы используем版本 1Для оператора построения таблицы MySQL нужно выделить только 20 единиц памяти для каждой записи в соответствии с полной длиной, и если это版本 3Если да, то каждой записи нужно выделить длину 4000! ! ! Очевидно, что это очень плохой дизайн.
MySQL создаст временные таблицы в следующих ситуациях (но это не тема этой статьи!)
-
ОБЪЕДИНЕНИЕ запрос;
-
Используйте алгоритм TEMPTABLE или представление в запросе UNION;
-
Когда предложения ORDER BY и GROUP BY различны;
-
В объединении таблиц столбец ORDER BY отсутствует в управляющей таблице;
-
запрос DISTINCT и добавление ORDER BY;
-
Когда в SQL используется параметр SQL_SMALL_RESULT;
-
подзапросы в FROM;
-
Таблицы, созданные во время подзапроса или полусоединения;
(4) Проблема предварительного выделения памяти
Внимание, это под вопросом! Я не нашел конкретного исходного кода или документации для этого утверждения, но если есть предварительно выделенная память, это должно иметь смысл После нахождения основы я обновлю этот пункт.
Это похоже на третий пункт, за исключением того, что третий пункт касается внутреннего ядра базы данных, а здесь клиент получает данные из ядра базы данных. Проще говоря, для varchar (1000) исполнитель клиента не знает, как долго данные из моей строки, прежде чем он получит данные, сохраненные механизмом хранения, длина может быть только 1, а длина может быть 800 , то что нам делать?Мы можем только сначала выделить максимальное пространство, чтобы избежать проблемы неспособности поместиться.На самом деле, varchar с короткими реальными данными действительно приведет к пустой трате места. Например: если у меня есть 1000 varchar(1000), но каждый хранит только один символ, то реальный размер данных на самом деле составляет всего 1 КБ, но мне нужно 1 М памяти, чтобы вместить его.
(5) Длина поля может играть определенную ограничительную роль
Например, максимальная длина поля не превышает 10 байт, тогда мы можем установить его равным varchar(10), если он превышает 10 байт, это означает, что это грязные данные, которые могут накладывать определенные ограничения на фактические данные. .
(6) База данных имеет ограничения на длину поля индекса, и при построении индекса по длинному столбцу данных может быть сообщено об ошибке, например
mysql> create table long_length_table(
-> long_column1 varchar(2000),
-> long_column2 varchar(2000),
-> long_column3 varchar(2000),
-> long_column4 varchar(2000),
-> long_column5 varchar(2000)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> create table short_length_table(
-> short_column1 varchar(10),
-> short_column2 varchar(10),
-> short_column3 varchar(10),
-> short_column4 varchar(10),
-> short_column5 varchar(10)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> create index short_length_table_idx on short_length_table(short_column1, short_column2, short_column3, short_column4);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index long_length_table_idx on long_length_table(long_column1, long_column2, long_column3, long_column4);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
3. Резюме
При сохранении «ab» в varchar(30) и varchar(1000) используемое пространство для хранения не сильно отличается, за исключением того, что один использует 1 байт для записи длины строки, а другой использует 2 байта для записи. Но в некоторых случаях производительность varchar(1000) будет намного хуже, чем varchar(30), а потребление памяти возрастет в геометрической прогрессии.
Далее регулировка длины кратна 4 (内存对齐) повысит производительность тоже не верно.
Итак, вывод такой:Выделяйте только то пространство, которое вам действительно нужно
PS: В будущем я, наконец, могу уверенно сказать другим: длина varchar не должна быть слишком большой, просто соответствовать требованиям длины; и не нужно заставлять длину быть кратной 4, что не влияет на улучшение производительности.^_^
Использованная литература:
(1) «Высокопроизводительный MySQL», третье издание.
(2)MySQL - varchar length and performance
(3)What are the current best practices concerning varchar sizing in SQL Server?
(4)Best practices for SQL varchar column length
(5)Would using varchar(5000) be bad compared to varchar(255)?
(6)What are the optimum varchar sizes for MySQL?
(7)MySQL: Disk Space Exhaustion for Implicit Temporary Tables