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

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

NOT NULL,

Name

WorkType VARCHAR(16),

Rate

PRIMARY

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

На рис. 8.1 изображена схема отношений между тремя таблицами.

Рис. Диаграмма табличных связей

Третья нормальная форма

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

Рассмотрим таблицу contractor в листинге 8.4. Столбец WorkType описывает вид выполняемой работы. Одни разработчики пишут программы на С, другие — гото вят иллюстрации. Эти задачи оплачиваются по разному. По сути, зная вид работы, можно определить процентную ставку. Правда, здесь применяется правило бизнес логики: работа конкретного вида всегда оплачивается одинаково.

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

CREATE TABLE worktype

ID

Нормальная форма

Rate

PRIMARY

Нормальная форма Бойса Кодда

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

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

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

Решение, переводящее таблицу в форму Бойса Кодда, заключается в уда лении из нее столбца Cabinet. Столбцов Title и Employee достаточно для

идентификации записей. Новые определения таблиц приведены в листинге8.7.

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

CREATE TABLE software

Четвертая нормальная форма

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

Рассмотрим диаграмму на рис. 8.2. Программа работает с ресурсами двух видов: библиотеками функций и конфигурационными файлами. Это, в частности, справед ливо для утилит MySQL. Большинство из них использует клиентскую библиотеку MySQL, а также библиотеку языка С, храня при этом конфигурационные уста новки в

Рис. 8.2. Многоцелевая промежуточная таблица

В листинге 8.8 описаны таблицы, соответствующие этой диаграмме.

CREATE TABLE program

105

CREATE TABLE resource

Суть проблемы локализована в таблице resource и заключается в том, что в столбцах внешних ключей разрешены значения NULL. Это подразумевает их необяза тельность. С логической точки зрения необязательная связь между программой и ее ресурсами порождает неопределенность записей в таблице. Предположим, програм ма работает с одной библиотекой и одним файлом ресурсов. Тогда в таблицу следует добавить строку с соответствующими значениями в столбцах Library и Но можно поступить иначе: добавить две записи, в которых одно из значений уста навливается явно, а другое равно NULL.

Когда программа перестает работать с библиотекой, возникает неприятная про блема. Внесение изменений в таблицу зависит от того, каким образом в нее добавля лись записи. Потребуется либо инструкция UPDATE, либо DELETE.

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

Денормализация

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

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

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

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

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

вобозримом будущем в США не предвидятся внутриполитические реформации.

Влистинге 8.9 показана таблиц, взятых из листинга 8.5. Там, как вы помните, они находились в третьей нормальной форме. На этот раз к таблице

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

CREATE TABLE contractor

Объединения, включающие более трех таблиц, сложнее выполнять человеку, чем СУБД. Не стоит недооценивать способность MySQL эффективно выполнять объеди нения при наличии правильного набора индексов. Крупные объединения можно оп тимизировать путем добавления индексов или изменения порядка таблиц в инструк ции SELECT. Это сложнои утомительно, но зато это можно сделать один раз, после чего программа всегда будет работать оптимальным образом.

Во многих СУБД поддерживаются подчиненные запросы: инструкции SELECT, размещаемые в скобках. В MySQL понятие подчиненного запроса отсутствует, но есть временные таблицы. Можно выполнить часть объединения и поместить его результат во временную таблицу. В главе "Инструкции SQL", приводится описание инструк ции CREATE TABLE, где рассказывается о том, как создать таблицу по результатам ра боты инструкции SELECT.

ТРАНЗАКЦИИ И ПАРАЛЛЕЛЬНЫЕ ВЫЧИСЛЕНИЯ

В этой главе.

Параллельные запросы Транзакции Блокировки Последовательности

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

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

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

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

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

Глава 9. Транзакции и параллельные вычисления

Параллельныезапросы

Параллелизм — это сложная проблема для СУБД. MySQL является многопотоковой программой, поэтому вынуждена справляться с множественными запросами на под ключение. Но помимо проблемы планирования возникает еще и проблема одновре менного доступа к данным.

Представим себе двух менеджеров, пытающихся выяснить количество единиц од ного и того же товара на складе. Первый менеджер вводит инструкцию SELECT и оп ределяет, что на складе осталось 150 единиц. Второй менеджер получает те же самые данные. Первый менеджер оформляет покупку 30 ти единиц товара, поэтому он вво дит инструкцию устанавливая объем складского запаса равным 120 ти едини цам. А тем временем второй менеджер, ничего не подозревая, оформляет аналогич ный заказ на 10 единиц и тоже вводит инструкцию UPDATE. Он думает, что уменьшает количество товара на 10 единиц, а на самом деле — увеличивает на 20. В результате в базу данных вносится недостоверная информация.

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

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

UPDATE item

SET Inventory Inventory 30

WHERE ID 3

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

Транзакции

Транзакция — это совокупность одной или нескольких SQL инструкций, имеющая начало и конец. В конце транзакции происходит либо ее отмена, либо завершение. Отмена транзакции называется откатом (rollback), так как происходит последова тельная отмена всех сделанных изменений. Завершение транзакции называется фик сацией (commit).

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

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

Транзакции 111

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

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

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

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

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