Исследование структуры базы данных в СУБД Microsoft SQL server

Система управления базами данных Microsoft SQL Server

В качестве СУБД использовался Microsoft SQL Server 2005, установка Enterprise Edition.

Производитель СУБД – Microsoft – позиционирует данный продукт как «решение для управления корпоративными данными и их анализа». Следует признать, что MS SQL Server уже несколько лет является одной из лучших современных многопользовательских СУБД, наиболее оптимальным образом используя возможности операционной системы Windows и обеспечивая высокую масштабируемость и надежность [1, с. 37].

Основные объекты СУБД Microsoft SQL Server

Ядром СУБД MS SQL Server является реляционная база данных. Начиная с версии 2005, управление всеми объектами, из которых состоит БД, выполняется с помощью объединенной универсальной консоли – SQL Server Management Studio. Коротко рассмотрим те объекты, которые в дальнейшем будут использоваться в рамках данной работы.

Таблицы (Tables)

Таблица – это наиболее важный объект БД. Таблицы состоят из так называемых доменных данных (столбцы) и объектных данных (строки). Таблица содержит также метаданные, которые описывают структуру данных в таблице. Каждый столбец имеет набор правил, описывающих, что может храниться в этом столбце [1, с. 49].

Представления (Views)

Представление – виртуальные таблицы, определяемые некоторым SQL-запросом (возможно, в запросе описано соединение нескольких таблиц, фильтрация и т.д.) [1, с. 325–326]. С точки зрения пользователя представление выглядит так же, как таблица, то есть его так же, как таблицу можно использовать в различных SQL-запросах (в том числе и для создания других представлений). Некоторые ограничения налагаются только при операциях вставки / редактирования / удаления данных из представлений в случае, когда представление описано многотабличным запросом.

Индексы (Indexes)

Индекс – это объект, который существует только в структуре конкретной таблицы (или представления). Индекс представляет собой своего рода предметный указатель по одной или нескольким колонкам таблицы [1, с. 277–278]. В основном индексы используются как средство увеличения производительности (при этом большое значение имеет как набор колонок, выбранных для индексирования, так и их порядок). Однако MS SQL Server поддерживает также специальные уникальные индексы (UNIQUE), которые гарантируют уникальность совокупности значений по указанным колонкам в таблице. Индексы делятся на кластеризованные (строки физически упорядочиваются по колонкам индекса) и некластеризованные (упорядочиваются не сами строки, а указатели на них). По понятным причинам, кластеризованный индекс в таблице может быть только один.

Хранимые процедуры (Stored Procedures)

На основе хранимых процедур основана программная функциональность SQL Server. Хранимая процедура – это последовательность операторов на языке Transact-SQL (T-SQL), объединенных в некоторый именованный логический модуль, заранее скомпилированный и оптимизированный [3, с. 383]. Хранимая процедура может иметь входные и выходные параметры, возвращать результаты выполнения одного или нескольких SQL-запросов, модифицировать данные, создавать новые объекты базы данных.

Определенные пользователем функции (User Define Functions)

Функции во многом схожи с хранимыми процедурами. Различие состоит в том, что функцию можно использовать в любом SQL-запросе (или любом другом месте) аналогично системной функции языка T-SQL, а также в разделе FROM, как представление (если функция возвращает таблицу) [1, с. 414]. «Платой» за это являются следующие ограничения:

1. функция может иметь сколько угодно входных параметров, но только один выходной (как уже было сказано, это может быть как скалярное значение, так и таблица);
2. результат функции должен быть строго детерминирован, то есть при его вычислении запрещено использовать такие функции, как GETDATE(), RND и т.д.

Пользователи (Users) и роли (Roles)

Каждый, кто входит в систему SQL Server, должен идентифицироваться. MS SQL поддерживает два вида такой идентификации (аутентификации): Windows Authentication (для идентификации используются данные, указанные при входе в Windows) и SQL Server Authentication (при входе в систему SQL Server явно указывается имя пользователя и пароль) [1, с. 55]. Выбор между ними, как правило, определяется принятым в компании способом разделения функций администратора домена и администратора SQL-сервера, а также взглядами и привычками разработчиков клиентского программного обеспечения. Каждый пользователь принадлежит к одной или более ролям. Каждой роли разрешены (или запрещены) определенные действия, список которых может настраиваться с большой гибкостью.

Мы не будем описывать, а просто перечислим такие важные объекты БД MS SQL Server, как триггеры, ограничения, группы файлов, диаграммы, правила, определенные пользователем типы данных, полнотекстовые каталоги, так как с ними редко приходится работать в рамках решения поставленной задачи.

Возможности языка T-SQL в Microsoft SQL Server 2005 и выше

В этом разделе мы перечислим возможности языка T-SQL в MS SQL Server 2005 [2]).

Общие табличные выражения (Common Table Expression, CTE)

Это специальный набор записей, который определяется при выполнении запроса и может быть использован в этом запросе (например, для самосоединения — join) [3]. Работа с CTE очень похожа на работу с вложенными запросами, однако к данным в CTE можно обращаться несколько раз в рамках одного запроса. CTE также предоставляет механизм для выполнения рекурсивных запросов. Рекурсия достигается за счет того, что CTE могут ссылаться сами на себя. Для того чтобы предотвратить переполнение памяти, по умолчанию максимальный уровень рекурсии имеет значение 100, но этот параметр можно изменить, явно указав в запросе нужную величину с помощью опции (hint) MAXRECURSION (если указано значение 0, то рекурсия будет выполняться либо до условия завершения, то есть когда очередная итерация не добавит в набор данных ни одной строки, либо до переполнения стека).

Общие табличные выражения реализованы как часть ключевого слова WITH и могут использоваться с операторами SELECT, INSERT, UPDATE и DELETE. Общие табличные выражения являются оптимальным инструментом разработки рекурсивных SQL запросов.

Оконные функции (window function) выполняют вычисления над списком строк в таблице, которые как-то относятся к текущей строке. Это сравнимо с типом вычислений, которые могут быть выполнены с помощью какой-либо агрегатной функции. Но в отличие от обычных агрегатных функций, использование оконной функции не заставляет строки группироваться в одну; строки сохраняют свои отдельные значения. Другими словами, оконная функция позволяет получить доступ более чем только к текущей строке результата запроса.

Любой вызов оконной функции всегда содержит предложение OVER, за которым следует имя оконной функции и аргумент(ы). В этом и заключается её синтаксическое отличие от обычной функции или агрегатной функции. Предложение OVER точно определяет какие строки в запросе разбиваются для обработки оконной функцией. Список PARTITION BY внутри OVER задаёт деление строк на группы или разбиения, которые разделяют те же самые значения выражения(й) PARTITION BY. Для каждой строки, оконной функция обсчитывает только строки, которые попадают в то же самое разбиение, что и текущая строка.

Начиная с MS SQL 2014 внутри предложения OVER допустимо использовать сортировку ORDER BY, что позволяет вычислять накопительный итог минимум/максимум с максимальной возможной производительностью.

Дополнительные инструментальные средства

Помимо ядра, в MS SQL Server содержится большое число дополнительных инструментальных средств. В работе были использованы возможности служб SSIS (SQL Server Integration Services) для однократных (или редких) операций непосредственного импорта больших объемов данных из внешних источников (использовались форматы *.xls и *.csv) в таблицы БД, минуя клиентский модуль, а также специальной программы SQL Server Profiler.

Документация Book Online

Вместе с СУБД Microsoft SQL Server поставляется также документация Book Online (BOL), которую, начиная с версии 2005, разработчики считают одним из наиболее важных и полезных инструментальных средств, находящихся в их распоряжении. Реальность сегодня такова, что ни прочтение каких-либо книг и руководств, посвященных SQL Server, ни обучение на специальных курсах не дает даже полного представления относительно того, что может делать данная СУБД, не говоря уж о запоминании всего того, что может потребоваться в работе. Один из ведущих экспертов в данной области Роберт Виейра говорит прямо: «Опыт эксплуатации СУБД Microsoft SQL Server показывает: не следует даже пытаться запомнить все, что к ней относится. Запоминайте только то, что сможете твердо усвоить. Помните то, что является незыблемой основой вашей работы» [1, с. 65].

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

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

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

Исследование существующей базы данных

Исследование базы данных

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

Цель и инструменты исследования базы данных

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

• техническая документация;
• разработчики БД.

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

• работа с утилитой SQL Server Profiler;
• исследование системных таблиц.

Работа с утилитой SQL Server Profiler

В состав MS SQL Server входит специальная утилита – SQL Server Profiler, которая используется для сбора характеристик работы программы с целью выявления проблемных ситуаций и анализа проблем производительности приложений во время их эксплуатации [1, с. 876–877]. Таким образом, решаются три основные задачи:

• решение проблемы быстродействия (выявление пользователей базы данных, снижающих её производительность);
• исправление ошибок (поиск «узких» мест программного кода);
• исследование структуры базы данных (что, где и как связано).

Самое главное при настройке трассировки в SQL Server Profiler – правильно выбрать условия фильтрации событий. В случае, если пользователи работают в базе данных круглосуточно, то возможность получить ресурс в монопольное использование с целью исследования хотя бы на краткое время полностью отсутствует. Поэтому требуется настроить трассировку таким образом, чтобы в ней отображались события, инициируемые конкретным сотрудником на конкретном рабочем месте.

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

Окно настройки трассировки приведено на рисунке 1, а пример трассировки – на рисунке 2.

Исследование системных таблиц и представлений

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

К сожалению, не всегда учебники и руководства, посвященные MS SQL Server, хотя бы кратко описывают назначение и структуру системных таблиц и представлений. В итоге многие начинающие разработчики либо вообще не подозревают об их существовании, либо не считают, что факт их наличия может им пригодиться. Однако, вся информация по этому вопросу содержится в BOL. Правда, в описании ряда колонок значится традиционное: «Reserved for SQL Server internal use only. Future compatibility is not guaranteed», что в переводе означает: «Зарезервировано только для внутреннего использования. Совместимость в будущем не гарантируется», или неформально: «Если догадаетесь, что здесь хранится, можете использовать на свой страх и риск».

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

В рамках исследования структуры БД обычно используются следующие системные представления [1, с. 216, 336]:

• sys.objects –список всех объектов базы данных;
• sys.columns – информация о всех колонках всех таблиц базы данных;
• sys.types – информация о всех типах данных, поддерживаемых MS SQL Server;
• sys.sql_modules – тексты всех хранимых процедур, пользовательских функций и представлений.

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

—Все функции, хранимые процедуры и представления,
—использующие колонку driver_id
SELECT OBJECT_NAME(OBJECT_ID) AS Name
FROM sys.sql_modules
WHERE definition LIKE '%driver_id%'
—————————————————-
Name
———————————————————————————————-
driver_vehicle
order_chart_ext
finished_order_chart
vehicle_chart
finished_order_by_driver
finished_order_by_driver_groupped
—Все таблицы, содержащие колонку client_id
SELECT sys.objects.name AS NameTable
FROM sys.columns
JOIN sys.objects ON sys.objects.OBJECT_ID=sys.columns.OBJECT_ID
      AND type='U'
WHERE sys.columns.name='client_id'
ORDER BY 1
——————————————————
NameTable
——————————————————
account_client
accounting_transaction
blacklist
client
client_complaint
orders
phone
—Поиск всех вхождений заданной подстроки
—во всех таблицах
DECLARE @str AS VARCHAR(50)
SELECT @str='Название фирмы' —Сюда пишем то, что хотим найти!
SET NOCOUNT ON
DECLARE @tables TABLE(Name SYSNAME)
INSERT @tables (Name)
    SELECT Name
      FROM sys.objects
    WHERE Type='U'
    ORDER BY Name
DECLARE @Name AS SYSNAME, @Cmd AS VARCHAR(8000)
WHILE EXISTS (SELECT * FROM @tables)
BEGIN
      SELECT TOP 1 @Name=Name
      FROM @tables
      SELECT @Cmd='WHERE 1
 
При оптимизации разработанных хранимых процедур (особенно ресурсоемких) часто использовался анализ ожидаемого и фактического плана выполнения. На рисунке 3 приведен фрагмент фактического плана выполнения процедуры формирования отчета о выполнении плана _RPT_PLAN_FACT. Анализ плана показывает, что при выполнении максимально трудоемкой операции запроса (87%) используется индексный поиск (Index seek), что обеспечивает максимальное быстродействие.
Таким образом, становится понятной структура базы данных, что позволяет решить задачи по внесению изменений, корректировке или созданию новых клиентских приложений.