MySQL. Библиотека профессионала - Аткинсон Л

92 Глава 7. Проектирование баз данных

Создание диаграмм

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

Создание диаграммы — это итерационный процесс. Получив первую версию диа граммы, вы обязательно обнаружите, что какие то объекты и отношения пропущены. Продолжайте улучшать модель до тех пор, пока она не будет завершена. Затем срав ните полученную диаграмму с исходными требованиями и определите ее полноту.

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

Не все из нас художники, поэтому для составления диаграмм лучше пользоваться специализированными приложениями. Наиболее популярны пакет Visio компании Microsoft(www.microsoft.com/office/visio)и его открыто распространяемый эквивалент Dia (www.lysator.liu.se/~alla/dia). В последнем применяется библиотека GTK, поэтому существуют его версии для Windows.

Пакеты Visio и Dia предназначены для создания диаграмм общего назначения. Программа ArgoUML (http://argouml.tigris.org) строит диаграммы UML. Она написана на Java, поэтому может применяться в любой операционной системе, где есть вирту альная машинаJava.

Пакет ERWin компании Computer Associates (www.ca.com) позволяет моделировать базы данных. Он может подключаться к СУБД и строить диаграммы существующих баз данных, а также создавать базы данных на основании диаграмм. Аналогичные функции выполняетпакет DeZign компании Datanamic (www.datanamic.com). К сожале нию, оба они доступны только в Windows.

Реализация модели

Когда проект базы данных завершен, переходят к ее реализации. В соответствии с диаграммами создаются инструкции CREATE TABLE. Будьте готовы вернуться к этапу проектирования для внесения очередных модификаций: переход на язык SQL часто открывает различные упущения.

В спецификации проекта были описаны столбцы каждой таблицы. Для них необ ходимо подобрать типы данных, поддерживаемые в SQL. Дополнительно можно соз дать индексы, хотя зачастую схема их использования настолько сложна, что придется прибегнуть к инструкции EXPLAIN (описана в главе 13, "Инструкции SQL").

Все SQL инструкции, создающие базу данных, должны быть сведены в одном или нескольких текстовых файлах, а не вводиться в интерактивном режиме. Это позволит стирать базу данных и создавать ее "с нуля". Эти же файлы послужат в качестве доку ментации. Перед каждой инструкцией CREATE TABLE вставьте комментарии, пояс няющие назначение таблицы. Комментарии игнорируются синтаксическим анализа тором, поэтому они не сохраняются в базе данных, зато дают возможность человеку,

сопровождающему базу данных, понять ее структуру. Синтаксис комментариев опи сан в начале главы 13, "Инструкции SQL".

Можно также пользоваться опцией COMMENT инструкции CREATE TABLE. Такие комментарии хранятся вместе с таблицей. Для их просмотра необходимо ввести ин струкцию SHOW TABLE STATUS.

При переходе от этапа проектирования к этапу реализации необходимо придер живаться пяти ключевых принципов:

•первичные ключи следует помечать спецификатором NOT NULL;

•пользуйтесь флагом AUTO_INCREMENT для автоматического создания иденти фикаторов;

•внешние ключи должны ссылаться на первичные ключи;

•отношения "многие ко многим" должны устанавливаться через промежуточную таблицу;

•таблицы, между которыми существуют отношения "один к одному", лучше объ единять.

Как правило, всякая таблица должна иметь первичный ключ. Вспомните из гла вы 5, "Реляционная модель", что столбцы первичных ключей необходимо объявлять со спецификатором NOT NULL. В большинстве таблиц первичный ключ представляет собой отдельный столбец целочисленных значений, так как зачастую трудно сказать заранее, будут ли значения других полей уникальными. Например, всем клиентам можно присвоить целочисленные идентификаторы, что позволит различать тезок, но самим клиентам эти идентификаторы не нужны, они могут даже не догадываться об их существовании. В MySQL такие абстрактные первичные ключи реализуются в виде столбцов счетчиков.

Внешние ключи участвуют в отношениях "один ко многим". Таблица, с которой существует "множественная" связь, будет содержать внешний ключ, указывающий на первичный ключ противоположной таблицы. MySQL не проверяет правильность значений внешних ключей, т.е. предложения FOREIGN KEY инструкции CREATE TABLE являются необязательными. Но есть ряд причин, по которым их все же стоит указывать. Во первых, это послужит целям документации. Во вторых, это позволит некоторым приложениям читать схему базы данных и строить на ее основы диаграм мы. Наконец, впоследствии может понадобиться передать схему в другую СУБД, где внешние ключи играют более важную роль.

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

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

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

94 Глава 7. Проектирование баз данных

ся выполнить изменения в нарушение этих правил, будет выдано сообщение об ошибке. А раз код проверки уже заложен в программы, то дублирование его в СУБД приведет лишь к снижению производительности.

Выбор правильных типов столбцов — это творческий процесс, частично связан ный с анализом исходных требований. Если необходимо заботиться об экономии дис кового пространства, следует выбирать минимально возможные типы, охватывающие заданный диапазон значений. Например, если известно, что в таблице будет пример но 500 строк, то трехзначного первичного ключа окажется достаточно. Специфика ция INT (3) является некорректной, поскольку для типа INT MySQLвсегда использует 32 разрядное целое число. Тип TINYINT занимает один байт, что недостаточно. В рассматриваемом случае подойдет тип SMALLINT, т.е. спецификация типа должна выглядеть так: UNSIGNED SMALLINT (3).

Значения в столбцах счетчиках должны быть беззнаковыми, потому что отрица тельные целые числа будут трактоваться как большие положительные числа. Следо вательно, вставка в столбец отрицательного числа приведет к тому, что следующее число окажется очень большим. Таким образом, спецификация типа поля счетчика всегда должна включать ключевое слово UNSIGNED.

Столбцы, хранящие короткие значения одинаковой длины, например коды дета лей, должны иметь тип CHAR. Правда, если в таблице есть поля переменной длины, значения CHAR могут автоматически приводиться к типу VARCHAR. Строковые поля длиной более 255 символов должны иметь тип BLOB или TEXT.

Столбцы, хранящие денежные величины, должны иметь тип DECIMAL. Это тип чисел с фиксированным количеством цифр после запятой. В MySQL десятичные чис ла хранятся в строковом виде и не подлежат аппроксимации, в отличие от других ти пов чисел с плавающей запятой. Эффект аппроксимации приводит к тому, что числа, отличающиеся на очень незначительную величину, оказываются равными. В случае сравнения денежных сумм это может означать незапланированную потерю денег. Значения типа DECIMAL преобразуются из строковой формы в числа с плавающей за пятой при выполнении математических операций. Чтобы защитить себя от ошибок аппроксимации, выполняйте этиоперации в приложениях.

Индексы можно создавать не только для первичных и внешних ключей, но и для обычных столбцов. Индексы ускоряют поиск записей в запросах с объединением, группировкой или упорядочением записей. Наличие индекса позволяет сразу же пе рейти к нужной записи, а не сканировать всю таблицу по одной записи за раз. Однако трудно предугадать заранее, какие запросы будут выполняться тем или иным прило жением. Поэтому может понадобиться проанализировать запросы с помощью инст рукции EXPLAIN. Этот процесс рассматривается в главе 26, "Оптимизация".

Тестирование

Тестирование бывает двух видов: на предмет поиска недочетов и на предмет про верки полноты реализации. Недочеты являются следствием человеческих ошибок. Полной считается система, удовлетворяющая всем исходным требованиям.

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

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

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

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

В пакет MySQL входят специальные утилиты тестирования, позволяющие изме рять производительность сервера. Они представляют собой Perl сценарии, находя щиеся в каталоге sql bench дистрибутива.

Планирование жизненного цикла

Даже после ввода в эксплуатацию системе необходимы регулярный мониторинг и сопровождение. Исходные требования меняются, от системы ожидается все более высокая производительность. У любой системы есть свой жизненный цикл. Должен быть составлен план, описывающий действия, выполняемые в ходе нормальной экс плуатации системы, ее обновления и последующего списания.

В процессе эксплуатации системе необходим административный контроль. Жур нальные файлы разрастаются и могут потребовать усечения. Следует регулярно соз давать резервные копии на случай возможной аварии. Внешние события, например сбой питания, могут вызвать повреждение файлов MySQL, в которых хранятся таб лицы, и их придется восстанавливать. Все эти вопросы рассматриваются в главе 23, "Администрирование баз данных".

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

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

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

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

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

Нормализация определяется в виде набора правил, известных как нормальные фор мы. После своей статьи, посвященной реляционной алгебре, доктор Кодд в 1972 г. опубликовал работу под названием "Дальнейшая нормализация реляционной модели баз данных" ("Further Normalization of the Data Base Relational Model"). В этом доку менте были описаны первые три нормальные формы. В последующих работах докто ра Кодда и других авторов были определены три другие нормальные формы. Каждая нормальная форма основана на предыдущей, поэтому, н апример, третья форма более желанна, чем вторая.

Устранение избыточности не обязательно означает повышение производитель ности. Накладные расходы на выполнение операций объединения весьма значитель ны, поэтому разработчики иногда сознательно идут на нарушение правил нормализа ции. Это называется денормализацией.

98 Глава 8. Нормализация

Зачем нужна нормализация

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

Нормализация приводит к улучшению целостности данных. Потребность поддер жания целостности в приложениях уменьшается, следовательно, повышается их про изводительность. Растет и производительность самой СУБД. Сокращается число зна чений NULL, обработка которых незначительно, но все же влияет на производитель ность, и повышается эффективность индексов.

Нормализация чаще всего выполняется на этапе проектирования, поэтому под ру кой не оказывается готовых данных, на которых можно было бы выполнить провер ку. Разработчики обычно создают разного рода заменители, например таблицы с не большим числом строк. Но в действительности необходимо учитывать весь домен значений столбца, что требует немалого воображения. Иногда задача состоит в нор мализации рабочей базы данных, но, как правило, приходится трансформировать схему, получаемую на основании диаграмм и инструкций CREATE TABLE.

В табл. 8.1 показано последовательное изменение правил для первых пяти уровней нормализации. Есть еще как минимум две нормальные формы, но их практическая ценность весьма сомнительна. В большинстве случаев достаточно третьей нормаль

Первая нормальная форма

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

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

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

Рассмотрим инструкцию CREATE TABLE, приведенную в листинге 8.1. Это таблица музыкального каталога. Она может хранить описание любой пластинки, любого ком пакт диска и любой кассеты, имеющихся в коллекции. Указываются дата приобрете ния носителя и его нынешняя цена.

CurrentValue DECIMAL(11,2) # нынешняя стоимость

);

Главный принцип первой нормальной формы заключается в том, что любая за пись таблицы должна содержать описание одной сущности. Этому правилу удовле творяет практически любая таблица, поскольку столбцы выбираются сознательно. В нашем случае в таблице находятся описания предметов музыкальной коллекции. Но если присмотреться, то окажется, что столбцы можно разделить на две группы. Столбцы первой группы (Artist, Released, Title, Format, Label, Genre, Length, CurrentValue) описывают любой экземпляр музыкального носителя, а не только тот, что имеется в коллекции. Столбцы второй группы (Purchased, Cost) относятся к одному конкретному экземпляру. Может показаться, что таблицу следует разбить на две части. Но прежде давайте познакомимся с двумя другими принципами первой нормальной формы.

Второй принцип требует наличия первичного ключа. С технической точки зрения созданная выше таблица не имеет ключа: я просто не указал его в инструкции CREATE TABLE. Вспомните из главы 7, "Проектирование баз данных", что в качестве первичного ключа можно использовать столбец счетчик. Есть ли альтернативы? Допустим, столбцы Artist и Released формируют составной ключ. Тогда исполнитель не должен одно временно выпускать две разные работы. Это неразумное ограничение, потому что опи санная ситуация возможна. Тогда предположим, что первичный ключ образуют столб цы Artist, Released и Title. Кажется глупым, чтобы исполнитель одновременно выпустил две работы с одинаковым названием. Но не забывайте о формате. Очень часто бывает, что альбом выходит накомпакт дисках и кассетах. Таким образом, можно про должить включать столбцы в первичный ключ. По сути, несложно привести разумные доводы в пользу того, почему ключом следует сделать всю совокупность столбцов. Это чересчур непрактично, поэтому мы воспользуемся полем счетчиком.

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

100 Глава 8. Нормализация

типа VARCHAR. Например, в столбце Format может быть записано "78,LP". Это озна чает, что носитель представляет собой долгоиграющую пластинку на 78 оборотов. Та кой формат хранения не оптимален, поскольку пропадает возможность осуществлять отбор записей отдельно по каждому из этих критериев. Попробуйте найти все пластин ки в коллекции. Можно, конечно, применить оператор LIKE, описанный в главе 10, "Типы данных, переменные и выражения". В MySQL есть даже тип SET, обозначающий неупорядоченный набор значений, но его поддержка ограничена. Следует очень вни мательно контролировать ситуации, когда в ячейку заносится несколько значений.

В листинге 8.2 демонстрируется добавление к таблице первичного ключа, реали зованного в виде счетчика. Это разумное решение, поскольку большинство столбцов не зависит друг от друга. В данном примере это не отражено, но подразумевается, что в поле Format заносится одно значение. Если один и тот же альбом приобретается в разных форматах, для него создаются дополнительные записи.

Теперь таблица находится в первой нормальной форме, но многие проблемы все еще не решены. Основная из них — дублирование информации в когда один и тот же альбом доступен в разных форматах. Как минимум, имя исполнителя и назва ние альбома будут повторяться.

Вторая нормальная форма

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

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

Рассмотрим таблицу, представленную в листинге 8.3. В ней отслеживается количе ство часов, за которые разработчики, действующие по контракту, выставляют счета по тем или инымпроектам. У каждого проекта есть числовой а у раз