ния или смерти, а к примеру, только год рождения. В столбце date хранить такую дату рождения не получается, так как в ранних версиях СУБД MySQL столбец такого типа может хранить только полную дату. В качестве одного из вариантов решения такой проблемы можно подсказать следующее: хранить год, месяц и день в отдельных столбцах. В случае неполной даты можно будет хранить известное значение в соответствующем столбце, а остальные столбцы оставить пустыми. Начиная с СУБД MySQL версии 3.23, формат типа date позволяет хранить даты с нулевыми значениями дня или месяца. Такие размытые даты можно использовать для представления значений неполных дат.

Каков диапазон значений?

После того как была выбрана категория типов данных для столбца, нужно определиться с диапазоном допустимых значений. Это поможет в выборе оптимального типа данных Если ваши данные принимают значения в диапазоне от О до 1000, лучше всего подойдет тип smallint или тип bigint. Если же ваши данные принимают значения до двух миллионов, лучше всего подойдут типы от mediumint до bigint.

Можно просто выбрать самый большой из возможных типов и больше ни о чем не беспокоиться (например, тип bigint, если говорить о типах, упомянутых в предыдущих абзацах) Однако следует использовать минимальный тип изо всех максимально больших. Таким образом, можно минимизировать объем дискового пространства, необходимого для хранения таблиц Кроме того, это позволит повысить производительность Это происходит потому, что короткие столбцы всегда обрабатываются быстрее длинных

В случае когда диапазон значений неизвестен, можно немного погадать или для того, чтобы не думать - выбрать наихудший вариант - просто воспользоваться самым вместительным типом bigint. (Напомню, что не все потеряно, даже если в момент создания таблицы была допущена ошибка в выборе типа Для увеличения размера столбца можно воспользоваться оператором alter table.)

В главе 1 для проекта Контроль успеваемости была создана таблица score. Она содержит столбец score, предназначенный для хранения результатов тестов и викторин. Для простоты таблица была создана с типом int, но теперь очевидно, что значения результатов лежат в пределах от О до 100, и оптимальным типом будет tinyint unsigned, потому что для него требуется меньше памяти.

Диапазон значений данных влияет также на перечень параметров, указываемых при объявлении типа столбца. При всех положительных значениях можно воспользоваться параметром unsigned.

Строковые данные не имеют понятия диапазона значений, как это имеет место для цифровых типов. Но они характеризуются длиной. А максимальная длина, в свою очередь, зависит от типа. Так, типы char,

varchar, tinytext ИЛИ tinybolb могут иметь строки, содержащие не более 256 символов Для хранения более длинных строк можно воспользоваться типами text или blob.

Строковые данные, являющиеся перечнем фиксированных значений, можно представить типом enum или set. Эти типы имеют внутреннее цифровое представление. Так как над этим типом производятся цифровые операции, то он будет эффективнее любого другого строкового типа Кроме того, эти типы данных позволяют сэкономить память

Здесь надо сделать небольшое отступление, связанное с определением диапазона данных в момент постановки задачи, и подчеркнуть, что чаще всего приходилось сталкиваться с такими описаниями диапазонов значений данных, как всегда и никогда (например, всегда меньше 1000 или никогда не принимает отрицательных значений ) Особенно будьте внимательны на этапе постановки задачи, когда в лексиконе постановщика появляются такие термины. Необходимо убедиться, что имеется в действительности в виду, говоря всегда или никогда . Опыт показывает, что говоря всегда люди часто имеют в виду почти всегда .

Предположим, что вы проектируете таблицу. Постановщик задачи говорит: Результаты наших тестов всегда лежат в диапазоне от 0 до 100 . На основании такого утверждения вы, как опытный разработчик, выбираете тип tinyint с параметром unsigned. Затем вы узнаете, что в случае пропуска экзамена по болезни присваивается значение -1. Вот так! Но об этом никто не говорил. Для представления такого значения можно воспользоваться пустым значением, а если нет, придется отказаться от использования параметра unsigned. Все это можно сделать постфактум с помощью оператора alter table.

Иногда решения по выбору типов данных можно сделать на основании простого вопроса: Существуют хоть какие-то исключения? Если таковые проявляются, пусть даже однажды, их надо предусмотреть. Конечно, есть отдельные персоны, которые считают, что исключения, возникающие не очень часто, можно не принимать во внимание. Но в процессе создания базы данных такой образ мыслей недопустим. Вопрос должен заключаться не в том, как часто возникают исключительные ситуации, а возникают ли они вообще. Если они возникают, то их необходимо предусмотреть.

Как повысить производительность и эффективность

Выбор типа данных влияет на производительность СУБД MySQL. Рекомендую принимать во внимание доводы, приведенные ниже. Они помогут вам увеличить производительность обработки таблиц СУБД MySQL.

Обработка цифровых и строковых данных

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

Можно воспользоваться преимуществом числовых операций, работая со строками, применив строковые типы ENUM или SET, которые имеют внутреннее цифровое представление.

Рассмотрим альтернативное представление строк. Производительность можно увеличить, представив строковые значения в виде чисел. Например, IP-адрес можно представлять в строковой нотации. Альтернативным решением может быть представление каждой тетрады в одном байте четырехбайтового типа INT UNSIGNED. Это позволяет сэкономить дисковое пространство и ускорит процедуру просмотра данных. Однако такое представление IP-адреса затруднит проверку по шаблонам. Это значит, что в процессе выбора типа данных необходимо комплексно рассматривать все аспекты применения данных

Типы данных большого и малого размера

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

В целом, чем меньше таблица, тем быстрее она будет обрабатываться, так как обработка запросов ускоряется из-за уменьшения количества операций ввода/вывода на диске. Для повышения производительности рекомендуется использовать типы фиксированной длины, выбирать типы минимальных размеров, достаточных для хранения данных. Например, не применяйте тип BIGINT, если вполне достаточно типа MEDIUMINT. Не используйте тип DOUBLE, если достаточно типа FLOAT. Для типов с переменной длиной это тоже справедливо. Так, тип BLOB резервирует 2 байта для хранения размера, в то время как тип LONGBLOB уже требует 4 байта для этих же целей. Это означает, что при хранении данных, размер которых никогда не превысит 64 Кбайт, использование типа BLOB сэкономит 2 байта на каждое значение (аналогичные выводы применимы в отношении типа TEXT).

Типы фиксированной и переменной длины

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

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