Я только недавно присоединился к новой компании и обнаружил, что дизайн базы данных немного проблематичен. Многие поля базы данных не имеют NOT NULL. Для пациентов с прогрессирующим обсессивно-компульсивным расстройством это просто невыносимо, поэтому у меня есть эта статья.
Основываясь на большей части текущего состояния разработки, мы установим все поля вNOT NULLИ дайте форме значение по умолчанию.
Обычно значение по умолчанию обычно устанавливается следующим образом:
-
Для формирования мы обычно используем 0 в качестве значения по умолчанию.
-
строка, пустая строка по умолчанию
-
время, по умолчанию
1970-01-01 08:00:01, или по умолчанию0000-00-00 00:00:00, но добавляются параметры подключенияzeroDateTimeBehavior=convertToNull, лучше не использовать этот формат времени по умолчанию, если это рекомендуется.
Однако рассмотрите следующие причины, почему он должен быть установлен в NOT NULL?
Вот такой отрывок из высокопроизводительного Mysql:
Старайтесь избегать NULL
Многие таблицы содержат столбцы, допускающие значение NULL (пустые), даже если приложению не требуется хранить значения NULL, поскольку значение NULLable является атрибутом столбцов по умолчанию. Обычно лучше указать NOT NULL для столбца, если вам действительно не нужно хранить значения NULL.
Если запрос содержит столбцы, допускающие значение NULL, его сложнее оптимизировать для MySql, поскольку столбцы, допускающие значение NULL, усложняют индексирование, статистику индексов и сравнение значений. Столбцы, которые могут быть NULL, будут использовать больше места для хранения и потребуют специальной обработки в MySql. Когда столбец, допускающий значение NULL, индексируется, для каждой записи индекса требуется дополнительный байт, что в MyISAM может даже привести к тому, что индекс фиксированного размера (например, индекс только с одним целочисленным столбцом) станет индексом переменного размера.
Обычно выигрыш в производительности от изменения столбца, допускающего NULL, на NOT NULL невелик, поэтому (при настройке) нет необходимости сначала искать и исправлять это в существующей схеме, если не установлено, что это вызывает проблему. Однако, если вы планируете построить индекс для столбца, вам следует избегать столбцов, предназначенных для определения значений NULL.
Конечно, есть исключения, например, стоит упомянуть, что InnoDB использует отдельный бит (бит) для хранения значений NULL, поэтому она имеет хорошую эффективность использования места для разреженных данных. Но это не относится к MyISAM.
В описании в книге упоминается несколько основных проблем, проблему MyISAM я пока отложу в сторону, здесь буду рассматривать InnoDB как соображение.
- Если NOT NULL не установлен, NULL является значением столбца по умолчанию.Если это не требуется само по себе, попробуйте не использовать NULL
- Использование NULL создает больше проблем, таких как индексация, статистика индекса и вычисление значения усложняются.Если вы используете индекс, вы должны избегать установки столбца в NULL
- Если это индексный столбец, это вызовет проблемы с местом для хранения, потребует дополнительной специальной обработки и приведет к увеличению занимаемого места для хранения.
- Для разреженных данных и лучшей эффективности использования пространства разреженные данные относятся кМногие значения имеют значение NULL, и только несколько строк имеют значения, отличные от NULL, для столбцов.Случай
По умолчанию
Для MySql, если для него активно не задано значение NOT NULL, значение по умолчанию равно NULL при вставке данных.
Значение NULL и NOT NULL разное, NULL может думать, что значение этого столбца неизвестно, а NULL может думать, что мы знаем значение, но оно пустое.
Например, запись в таблицеnameполе равно NULL, мы можем предположить, чтоне знаю, как зовут, иначе, если это пустая строка, можно считать, чтоМы знаем, что без имени он ноль.
Для большинства программ нет особой необходимости в том, чтобы поле было NULL, а значение NULL вызовет проблемы, такие как нулевые указатели в программе.
Для статус-кво чаще всего используютMyBatisслучае, я рекомендую использовать сгенерированный по умолчаниюinsertSelectiveметод или чисто ручной метод вставки могут избежать проблемы, связанной с тем, что значение по умолчанию не вступает в силу или ошибка вставки вызвана добавлением поля NOT NULL.
расчет стоимости
Агрегатная функция неточна
Для столбцов со значениями NULL значения NULL игнорируются при использовании агрегатных функций.
Теперь у нас есть стол,nameПоле по умолчанию равно NULL, в это времяnameпровестиcountРезультат 1, что неверно.
count(*)это подсчет количества строк в таблице,count(name)Это для выполнения статистики по ненулевым столбцам в таблице.
= недействительный
Для столбцов со значениями NULL невозможно использовать=Выражение оценивается, и следующееnameЗапрос недействителен и должен использоватьсяis NULL.
Работает с другими значениями
NULL, и любая другая операция со значением является NULL, включая значение выражения, также равно NULL.
userВторая запись таблицыageравно NULL, поэтому+1После этого он по-прежнему NULL,nameNULL, продолжайтеconcatПосле операции результат по-прежнему равен NULL.
Вы можете посмотреть на пример ниже, результат любой операции NULL станет NULL, поле Представьте, если ваш дизайн не NULL тщательно выполняет различные операции, и, наконец, результат. . .
отчетливый, группа по заказу
заdistinctиgroup by, все значения NULL считаются равными, дляorder byНапример, возрастающий NULL будет первым
другие проблемы
В таблице есть только одна запись с именем, и в это время запрашивается имя!=aОжидаемый результат должен состоять в том, чтобы найти оставшиеся две записи и обнаружить, что они не соответствуют ожидаемому результату.
проблема с индексом
Чтобы проверить влияние полей NULL на индекс, соответственноnameиageДобавить индекс.
В Интернете есть много высказываний о том, что если NULL не может использовать индекс, это описание не является точным.Согласно описанию в официальном документе [3], использование равно NULL, а запрос диапазона может использовать индекс как обычно, а фактическая проверка результат Похоже на то, см. следующий пример.
И тогда мы продолжаем вставлять некоторые тестовые данные в базу данных, значения новых столбцов, когда индекс увеличивается, найденный неэффективным.
Мы знаем, что выполнение запроса SQL примерно так:
Сначала коннектор отвечает за подключение к указанной базе данных, затем смотрите, есть ли этот оператор в кеше запросов, и если есть, возвращайте результат напрямую.
Если попадания в кэш нет, анализатор должен выполнить синтаксический и лексический анализ оператора SQL, чтобы определить, является ли оператор SQL допустимым.
Теперь, когда мы подошли к оптимизатору, более разумно выбрать, какой индекс использовать, и определить план выполнения оператора SQL.
Наконец, исполнитель отвечает за выполнение оператора, запрашивая, есть ли у него разрешение или нет, и возвращая результат выполнения.
Из приведенных выше результатов простого теста мы видим, что наличие NULL в столбце индекса сделает оптимизатор более сложным и трудным для оптимизации при выборе индекса.
место хранения
Ряд записей в базе данных также хранится в строке в конечном файле диска.Для InnoDB существует 4 формата хранения строк:REDUNDANT,COMPACT,DYNAMICиCOMPRESSED.
Формат хранения строк InnoDB по умолчанию:COMPACT, формат хранения показан ниже, пунктирная линия может не существовать.
Список длины полей переменной длины: Если полей несколько, они хранятся в обратном порядке. У нас есть только одно поле, поэтому мы не учитываем так много. Формат хранения шестнадцатеричный. Если поля переменной длины нет, эта часть не нужна.
Список значений NULL: используется для хранения значения NULL в нашей записи. Если значений NULL несколько, они также сохраняются в обратном порядке и должны быть целым числом, кратным 8 битам. Если 8 бит недостаточно, старшие биты заполнены 0. 1 означает NULL, 0 не означает NULL. Если оба НЕ NULL, то это существует.
ROW_ID: Уникальный флаг строки записей, ROW_ID автоматически генерируется, когда первичный ключ не указан, используется в качестве первичного ключа.
TRX_ID: идентификатор транзакции.
ROLL_PRT: указатель отката.
Последнее — это значение каждого столбца.
Чтобы прояснить проблему этого формата хранения, я сделал таблицу для тестирования, в этой таблице есть толькоc1Поле НЕ NULL, и все остальные могут быть NULL.
список переменной длины поля:c1иc3Длина значения поля равна 1 и 2 соответственно, поэтому длина, преобразованная в шестнадцатеричный формат, равна0x01 0x02, после обратного порядка0x02 0x01.
Список значений NULL: поскольку есть столбцы, допускающие NULL, поэтомуc2,c3,c4Они равны 010 соответственно.После обратного порядка они остаются прежними.При этом старшие биты заполняются 0 и полными 8 битами.В результате получается00000010.
Остальные поля нас пока не интересуют, результат последней записи - это результат первой записи, результат после кодирования мы, конечно, здесь не рассматриваем.
Это формат полных данных строки данных, наоборот, если мы установим все поля в NOT NULL и вставим данныеa,bb,ccc,ddddЕсли это так, формат хранения должен быть следующим:
Хотя мы обнаружили, что сам NULL не занимает места для хранения, если есть NULL, он займет еще один байт места для бита флага.
Справочные документы статьи: