Проектирование и управление базами данных: как сделать правильно с первого раза

Источник: ca

Мишель Пуле

В мире баз данных (БД) фраза "сделать правильно" звучит довольно неопределенно. Кто может судить, что такое на самом деле "правильно"? Честно говоря, назвать схему БД "плохой" или "неправильной" гораздо проще, чем подтвердить ее "правильность". Дизайн схемы - это основа хорошей работы БД. Если схема не учитывает требования к производительности, администрированию, резервному копированию и восстановлению БД, то многие возможности работы будут недоступны.

Возможность

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

Преимущества

Моделирование и проектирование БД - это основа правильного управления БД и ее производительности. Если подойти к этому правильно с самого начала, то конечный результат будет значительно лучше. Вот некоторые преимущества правильного подхода.

Исключение опасных подходов к обработке данных.
Минимальное обслуживание и сопровождение схемы БД.
Оптимизация извлечения данных для графиков и отчетов.
Обеспечение точности и надежности данных.
Поддержка корпоративных целей и развитие бизнес-инициатив. Это позволит компании быстрее и точнее реагировать на новые возможности рынка и повысит ее конкурентоспособность.

РАЗДЕЛ 1

Проблема: "правильный" дизайн

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

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

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

РАЗДЕЛ 2

Возможность: идентификация существующих проблем

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

Плохой дизайн записей будет жить вечно

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

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

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

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

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

Ясно, что в подобной ситуации никакое мощное аппаратное обеспечение, увеличение размера блока или любое количество дополнительных индексов не спасло бы этих людей от проблем с производительностью. Учитывая, что в этой компании были склонны рассматривать их реляционную СУБД как обычный сервер с неструктурированным файлом, увеличение размера блока только обострило бы их проблемы с производительностью. Физический ввод-вывод, наверное, является самой ресурсоемкой операцией на компьютере. И нет ничего, что могло бы по затратности превзойти считывание и запись одной записи при каждом физическом или логическом вводе-выводе. Если бы эти ребята провели мониторинг своей системы, они бы увидели исключительно высокую активность по записи и чтению данных с жестких дисков. Также они бы увидели "пробуксовку" - высокий уровень обращения на чтение и запись к файлу подкачки даже при легкой или средней транзакционной нагрузке. Эти характеристики являются симптомами проблем соответственно с физическими и логическими операциями ввода-вывода. Такие проблемы нужно решать в источнике, даже если это означает переделку схемы работающей БД. С помощью лучших методик по нормализации данных разделите данные на наборы связанных таблиц, исходя из смысла и функциональности. Связи между таблицами необходимо реализовать с использованием декларативной ссылочной целостности, и создать соответствующие индексы. При необходимости создайте представления, которые имитируют старый формат записи. Это нужно для поддержки существующих приложений, которые запрашивают данные из БД. Одновременно следует переписать эти приложения. Они должны использовать что-нибудь помимо "select *". Если в изначальном дизайне таблиц и записей есть недостатки, что необходимо предпринять некоторые серьезные шаги по исправлению ситуации. Плохой дизайн записей - это главный пример неправильных действий уже на исходном уровне. От этого, как и от любых других изначальных проблем, можно избавиться переделкой дизайна.

Плохая производительность приложения вызвана недостатками в реализации дизайна

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

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

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

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

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

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

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

Ограниченный дизайн ограничивает работу организации

Чтобы все сделать правильно с самого начала, нужно понимать бизнес-требования. Этого не было сделано в одном проекте, который включал в себя полную переделку дизайна БД, поддерживавшей розничную торговлю через Интернет. В данном случае у компании было четыре веб-узла для розничной торговли через Интернет. Планировалось увеличить это количество и, возможно, развивать бизнес в сторону франчайзинга. Четыре узла для розничной торговли совместно использовали одну БД и основу кода приложения. Из-за особенностей схемы принятые на раннем этапе проектирования БД решения сдерживали рост организации. Схема БД, спроектированная сторонней организацией, до этого исправно служила компании. Но она не учитывала планы расширения бизнеса. Не будучи техническими специалистами, руководство никогда не осознавало эти недостатки дизайна. Между тем дизайн жестко ограничивал количество магазинов и тип услуг, которые могла предлагать компания. Без фундаментальной реструктуризации БД и переписывания программного кода было невозможно расширить возможности и функциональность этой системы для поддержки инициатив по развитию бизнеса.

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

Во вторую очередь нужно в контрольном списке учесть физическую реализацию, а особенно метод размещения файлов БД на диске. Необходимо использовать средство заблаговременного контроля и управления БД. В данном случае я использовала профайлер для тестирования производительности и скорости выполнения запросов до и после физической реструктуризации. Полученные мной перед реструктуризацией результаты не были обнадеживающими. В исходной БД все объекты хранились в одной группе файлов. Эта БД содержала все типы данных - как табличные (числа, текстовые строки, время и дату), так и тест с изображениями (длинные описания продуктов и ОЧЕНЬ МНОГО картинок). Единая группа файлов размещалась на одном физическом/логическом диске. Такая конфигурация неблагоприятно влияла на производительность и ухудшала работу интерфейса для Интернет-покупателей. При реструктуризации подобной БД - которая содержит большой объем двоичных изображений очень длинных текстовых строк - наилучшим выходом является отделить текст и изображения от табличных данных. Сохраните текст и изображения в отдельном наборе таблиц. Затем используйте отношения для связи каждого изображения или длинной текстовой строки с соответствующей табличной записью (или записями). Также следует отделить системные таблицы (каталог) от табличных данных и изображений. Каждый из этих трех типов таблиц следует поместить в отдельную группу файлов или табличное пространство. Затем каждую из этих файловых групп поместите на отдельный диск. Используя эти простые принципы физического дизайна, вы значительно повысите производительность БД.

Третьим пунктом контрольного списка ставим индексирование для повышения производительности. Если есть отношение между двумя таблицами вида "один ко многим", то на стороне "многих" этой связи будет связь по внешнему ключу. В большинстве систем БД первичный ключ индексируется автоматически. Для внешнего ключа это не всегда так. Чтобы обеспечить наилучшую работу объединений, обязательно проиндексируйте каждый внешний ключ в БД. Следующий кандидат на индексацию - это любой столбец, который будет использоваться для сортировки, т.е. столбец, который будет постоянно использоваться в выражении "order by" запроса на SQL. Также стоит проиндексировать столбцы, которые будут использоваться для ограничения возвращаемого набора данных. Это, например, те столбцы, которые постоянно фигурируют в выражениях "where".

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

РАЗДЕЛ 3

Преимущества правильного выполнения

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

Преимущества хорошего основания. Когда проект готов и планы закончены, настает время приступить к созданию БД. Вы бы никогда не стали строить дом без прочного фундамента, поэтому в первую очередь следует рассмотреть вопрос основания. Основой для приложений и систем служит БД. Чтобы БД была хорошей основой структуры приложений и программ, создавайте ее прямо с самого начала.
Преимущества хорошего дизайна записей. Хорошо нормализованная БД облегчает работу с данными. Когда вам нужно изменить данные в хорошо нормализованной БД, это можно сделать сразу, правильно и быстро. В хорошо нормализованной БД извлечение данных происходит быстрее, поскольку относительно малая длина записей позволяет считывать с жесткого диска или записывать на него много записей в одной физической операции ввода-вывода. Виртуальные (т.е. происходящие в памяти) операции тоже ускоряются благодаря укороченной длине записи. На одной странице может разместиться больше записей, поэтому за один заход можно переместить или обработать больше записей. Такие ресурсоемкие задачи, как дисковые операции ввода-вывода для данных в хранилище или для страничного файла, значительно сократятся. Для оптимизации операций БД и времени отклика практикуйте хороший дизайн записей с самого начала.
Преимущества правильной реализации дизайна. Когда вы реализуете дизайн, любой недочет может иметь долгосрочные негативные последствия. Если вернуться к нашей аналогии со строительством дома, то недостатки в электропроводке при строительстве могут служить причиной короткого замыкания. Это дорого обойдется или вообще может привести к несчастному случаю. Можно создать прекрасный дизайн, но если реализовать его неправильно, то месяцы или годы спустя вам придется залечивать симптомы или ликвидировать более поверхностные проблемы (а не их реальные причины). Это будут попытки компенсировать пороки реализации, которые были заложены в самом начале. Чтобы избежать опасного кода и техник, реализуйте все правильно с самого начала.
Преимущества определения, документирования и реализации бизнес-требований. Если ваша БД не поддерживает бизнес-требования, она будет (в лучшем случае) разочаровывать с самого начала, а в худшем случае может стоить вам работы. Чтобы не иметь дело с БД, которая не поддерживает требования, убедитесь в том, что вы понимаете не просто сегодняшние, но и будущие требования. Узнавайте, документируйте и утверждайте бизнес-требования на завтра, на следующий год и даже на несколько лет вперед. Только понимая планы компании на будущее, можно правильно реализовать дизайн для сегодняшней работы.

РАЗДЕЛ 4

Заключение

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

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

Ссылки по теме