MySQL: как выбрать подходящий тип поля при создании таблицы

MySQL

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

написать впереди

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

  • Целочисленный тип
  • реальный тип
  • Тип строки
  • Дата и время
  • тип перечисления

Целочисленный тип

Целочисленные типы включают TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, пространство для хранения и диапазон значений:

тип Место для хранения (в битах) Диапазон значений
TINYINT 8 -128 ~ 127
SMALLINT 16 -32768 ~ 32767
MEDIUMINT 24 -8388608 ~ 8388607
INT 32 -2147483648 ~ 2147483647
BIGINT 64 слишком большой

Диапазон значений: -2^(N-1) ~ 2^N, где N — размер дискового пространства.

Целочисленные типы имеют необязательное свойство UNSIGNED, запрещающее отрицательные значения. Установка свойства UNSIGNED может удвоить верхний предел положительных чисел, а диапазон значений составляет 0 ~ 2 ^ (N-1) + 2 ^ N.

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

реальный тип

FLOAT

С плавающей запятой одинарной точности, используя 8 бит

DOUBLE

Двойная точность с плавающей запятой с использованием 16-битной памяти

DECIMAL

Потеря точности произойдет, если для вычисления используются float и double.Причины потери точности можно найти в этой статье:Босс, почему я должен вычитать свою зарплату за то, что храню сумму в плавающем состоянии?Когда требуется точное вычисление, можно использовать DECIMAL.Дополнительное пространство и вычислительные затраты, поэтому используйте тогда и только тогда, когда требуется точное вычисление

Тип строки

1. ВАРЧАР и ЧАР

varchar и char — очень, очень часто используемые строковые типы.

VARCHAR

VARCHAR используется для хранения строк переменной длины.При использовании этого типа для хранения строк вам нужно использовать 1 или 2 дополнительных байта для записи длины строки:

  • Максимальная длина столбца меньше или равна 255 => используется 1 байт
  • Длина столбца больше 255 => использовать 2 байта

Сценарии, в которых в качестве типа хранилища используется VARCHAR:

  • Несколько обновлений столбцов => частые обновления столбцов склонны к разбиению страниц
  • Длина столбца не фиксирована => VARCHAR использует только необходимое пространство при сохранении, поэтому это сэкономит место
CHAR

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

Сценарии, в которых CHAR является наиболее часто используемым типом хранения

  • Колонны имеют почти фиксированную длину
  • Длина столбца короткая => VARCHAR нужны дополнительные байты для хранения длины
  • Столбцы часто обновляются

2. Типы BLOB и TEXT

Оба типа BLOB и TEXT используются для хранения больших данных, таких как содержание статей.

BLOB

Хранится в двоичном режиме, подразделение BLOB может быть разделено на TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB.

TEXT

Используя хранилище символов, подразделение TEXT можно разделить на TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

Когда значения BLOB и TEXT слишком велики, хранилище InnoDB будет использовать область внешнего хранилища для хранения значения, а затем сохранит 1-4-байтовый указатель на внешнее хранилище.

тип даты и времени

Обычно используемые типы даты: DATETIME и TIMESTAMP.

DATETIME

Используя 8-байтное хранилище, можно хранить широкий диапазон значений, от 1001 до 9999.

TIMESTAMP

Используйте 4 байта хранилища, диапазон хранения меньше, чем DATETIME, от 1970 до 2038.

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

Как выбрать DATETIME и TIMESTAMP

Раньше были медленные онлайн-запросы из-за типа времени. В этой статье описаны причины медленных запросов:очень счастлив! Наконец-то ступил на яму медленного запроса, BIGINT рекомендуется для поиска временного диапазона, сортировки, группировки и других операций. Если нет операции для полей типа времени, рекомендуется использовать TIMESTAMP. Вы можете обратиться к этой статье:Сравнение эффективности запросов типов времени базы данных mysql datetime, bigint и timestamp
Найдено в stackoverflow следующим образом:

тип перечисления

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

использование перечисления

Оператор создания таблицы:

 CREATE TABLE `dataset_enum` (
  `name` varchar(48) DEFAULT NULL,
  `status` enum('NEW','UPLOADING','USING','DELETING') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Вставить заявление:

insert into dataset_enum(name, status) values("t4", "DELETING")

Для нижнего слоя поля состоянияцелые числа хранятсявместо струн под капотом будетподдерживать отображение числовой строки

Оператор запроса и сортировка по полю статуса:

select * from dataset_enum order by status;

результат поиска:

+------+-----------+
| name | status    |
+------+-----------+
| t1   | NEW       |
| t2   | UPLOADING |
| t4   | DELETING  |
+------+-----------+

инструкция:

  • Результат сортировки основан на целых числах, хранящихся внутри, а не на определенных строках.
  • Базовое хранилище представляет собой целое число, которое преобразуется в строку в соответствии с отношением отображения, поэтому оно будетЕсть определенная стоимость

Зачем использовать TINYINT вместо ENUM

Когда таблица создавалась раньше, для замены общих строк выбирался тип TINYINT, а преобразование выполнял прикладной уровень. Когда я увидел тип ENUM, я был немного сбит с толку. Почему я выбрал не ENUM, а TINY? Я искал причину в Интернете, как показано ниже:

Причины резюмируются следующим образом:

  • Неудобно мигрировать и имеет слабую масштабируемость, например знакомая БД PostgreSQL не поддерживает тип ENUM.
  • При добавлении или удалении строк в поле ENUM таблица будет перестроена, что требует больших затрат времени и производительности.
  • есть яма Возьмите предыдущую таблицу dataset_enum в качестве примера для вставки данных:
    mysql> insert into dataset_enum values("t1", "NEW"), ("t2", 2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
    Данные успешно вставлены Данные запроса:
    mysql> select * from dataset_enum;
+------+-----------+
| name | status    |
+------+-----------+
| t1   | NEW       |
| t2   | UPLOADING |
+------+-----------+
    Числовые типы также могут быть вставлены после преобразования
  • Невозможно связать с другими таблицами


Справочная статья:
Should I use the datetime or timestamp data type in MySQL?
8 Reasons Why MySQL's ENUM Data Type Is Evil Почему так много людей предпочитают использовать tinyint вместо enum?
«Высокопроизводительный MySQL»

Категории