Как выбрать оптимизированные типы данных для столбцов [innodb конкретный]?
Я узнаю об использовании типов данных для баз данных.
Например:
- Который лучше для электронной почты? varchar[100], символ [100], или tinyint (шутка)
- Который лучше для имени пользователя? я должен использовать интервал, bigint, или varchar? Объяснить. Некоторые мои друзья говорят, что, если мы используем интервал, bigint, или другой числовой тип данных, это будет лучше (Facebook делает это). Как u=123400023 относится к пользователю 123400023, скорее затем user=thenameoftheuser. Так как числа занимают меньше времени для выборки.
- Который лучше для номеров телефона? Сообщения (как в блогах или announcments)? Или возможно даты (я использую дату и время для этого)? возможно, некоторые имеют, делают исследование, которое хотело бы совместно использовать.
- Цена продукта (я использую десятичное число (11,2), не знайте о Вас парней)?
- Или что-либо еще, как что Вы имеете в виду, "Я использую последовательный тип данных для blablabla".
Почему я упоминаю innodb конкретно?
Если Вы не используете типы таблицы InnoDB (см. Главу 11, "Усовершенствованный MySQL", для получения дополнительной информации), столбцы CHAR быстрее к доступу, чем VARCHAR.
Дб Inno имеет некоторое различие, которое я не знаю. Я считал это отсюда.
2 ответа
Краткое описание:
(только мои мнения)
- для адреса электронной почты -
VARCHAR (255) - для имени пользователя -
VARCHAR (100)илиVARCHAR (255) - для id_username - используйте
INT(если вы не планируете использовать более 2 миллиардов пользователей в своей системе) - телефонные номера -
INTилиVARCHARили, возможно,CHAR(зависит от того, хотите ли вы сохранить форматирование) - сообщения -
ТЕКСТ - даты -
ДАТАили] DATETIME(обязательно включайте время для таких вещей, как сообщения или электронные письма) - деньги -
DECIMAL (11,2) - разное - см. Ниже
Что касается использования InnoDB, потому что VARCHAR должен быть быстрее, я бы не стал беспокоиться об этом или о скорости в целом. Используйте InnoDB, потому что вам нужно выполнять транзакции и / или вы хотите использовать ограничения внешнего ключа (FK) для целостности данных. Кроме того, InnoDB использует блокировку на уровне строк, тогда как MyISAM использует только блокировку на уровне таблицы. Следовательно, InnoDB может обрабатывать более высокие уровни параллелизма лучше, чем MyISAM. Используйте MyISAM, чтобы использовать полнотекстовые индексы и немного снизить накладные расходы.
Что еще важнее для скорости, чем тип двигателя: поместите индексы в столбцы, по которым вам нужно быстро выполнять поиск. Всегда помещайте индексы в свои столбцы ID / PK, такие как id_username, о котором я упоминал.
Подробнее:
Вот несколько вопросов о типах данных MySQL и дизайне базы данных (предупреждение, больше, чем вы просили):
И пара вопросов о том, когда для использования движка InnoDB:
Я просто использую tinyint почти для всего (серьезно).
Правка - Как хранить "сообщения":
Ниже приведены некоторые ссылки с более подробной информацией, но вот краткая версия. Для хранения «сообщений» вам понадобится место для длинной текстовой строки. Максимальная длина CHAR составляет 255, так что это не вариант, и, конечно же, CHAR будет тратить неиспользуемые символы по сравнению с VARCHAR , который имеет переменную длину CHAR .
До MySQL 5.0.3 максимальная длина VARCHAR составляла 255, поэтому у вас останется TEXT . Однако в более новых версиях MySQL вы можете использовать VARCHAR или TEXT . Выбор сводится к предпочтениям, но есть пара отличий. VARCHAR и TEXT максимальная длина теперь равна 65 535, но вы можете установить собственный максимум на VARCHAR . Допустим, вы думаете, что ваши сообщения должны быть максимум 2000, вы можете установить VARCHAR (2000) .Если вы каждый раз сталкиваетесь с лимитом, вы можете ALTER таблицу позже и увеличить его до VARCHAR (3000) . С другой стороны, ТЕКСТ фактически хранит свои данные в BLOB (1). Я слышал, что могут быть различия в производительности между VARCHAR и TEXT , но я не видел никаких доказательств, поэтому вы можете изучить это подробнее, но вы всегда можете измените эту незначительную деталь в будущем.
Что еще более важно, поиск в этом столбце «сообщения» с использованием полнотекстового индекса вместо LIKE будет намного быстрее (2). Однако вы должны использовать механизм MyISAM для использования полнотекстового индекса, потому что InnoDB не поддерживает его . В базе данных MySQL у вас может быть разнородное сочетание механизмов для каждой таблицы, поэтому вам просто нужно будет использовать MyISAM в своей таблице «сообщений». Однако, если вам абсолютно необходимы «сообщения» для использования InnoDB (для транзакций), настройте триггер для обновления копии MyISAM вашей таблицы «сообщений» и используйте копию MyISAM для всех ваших полнотекстовых поисков.
См. Внизу некоторые полезные цитаты.
(3) "Значения в столбцах VARCHAR являются строки переменной длины. Длина можно указать как значение от 0 до 255 до MySQL 5.0.3 и от 0 до 65 535 в 5.0.3 и более поздних версиях.
До MySQL 5.0.3, если вам нужны данные тип, для которого конечные пробелы не удалено, рассмотрите возможность использования BLOB или TEXT тип.
Когда значения CHAR сохраняются, они с заполнением справа пробелами до указанная длина. Когда значения CHAR равны извлечены, конечные пробелы удаленный.
До MySQL 5.0.3,конечные пробелы удаляются из значений, когда они хранится в столбце VARCHAR; это означает, что пробелы также отсутствуют из извлеченных значений. "
Наконец, вот отличный пост о плюсах и минусах VARCHAR по сравнению с TEXT. Он также говорит о проблеме производительности:
Есть несколько точек зрения на ваш вопрос.
Из точки обзора проекта всегда лучше выбирать тип данных, который лучше всего выражает количество, которое вы хотите моделировать. То есть правильно определить домен данных и размер данных, чтобы недопустимые данные не могли быть сохранены в базе данных. Но MySQL силен не в этом, и особенно не в sql_mode по умолчанию ( http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html ). Если это работает для вас, попробуйте ТРАДИЦИОННЫЙ sql_mode, который является сокращением для многих желаемых флагов.
С точки зрения перформанса вопрос совсем другой. Например, что касается хранения тел сообщений электронной почты, вы можете прочитать http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/ , а затем подумать об этом .
Устранение дублирования и короткие ключи могут быть большим выигрышем.Например, в проекте, который я видел, таблица журнала хранила информацию HTTP User-Agent. Путем простой замены каждой строки пользовательского агента в таблице журнала числовым идентификатором строки пользовательского агента в таблице поиска размер набора данных был значительно (более чем на 60%) уменьшен. Путем дальнейшего синтаксического анализа пользовательского агента и последующего сохранения набора идентификаторов (операционная система, тип браузера, индекс версии) размер набора данных был уменьшен до 1% от исходного размера.
Наконец, существует ряд правил, которые могут помочь вам обнаружить ошибки при разработке схемы.
Например, все, что имеет идентификатор в имени и не является целым числом без знака, вероятно, является ошибкой (особенно в контексте innodb).
Например, все, что имеет в названии цену или стоимость и не является неподписанным, является потенциальным источником мошенничества (мошенник создает статью с отрицательной ценой и покупает ее).
Например, все, что работает с денежными данными и не использует тип данных DECIMAL соответствующего размера, вероятно, выполняет вычисления неправильно (DECIMAL выполняет BCD, десятичные бумажные вычисления с правильной точностью и округлением, DOUBLE и FLOAT - нет) .