в ms sql server что относится к объектам физического уровня
Структура современной СУБД на примере Microsoft SQL Server 2008
10.3. Архитектура базы данных. Физический уровень
Физический уровень это представление данных в памяти ЭВМ. Как уже отмечалось в «Физические модели данных (внутренний уровень)» основными понятиями, используемыми для представления структуры хранения (физического уровня) являются понятия файла (физического) и единицы обмена между внешней и оперативной памятью (физической записи или страницы). Рассмотрим, как представлены соответствующие понятия в СУБД Microsoft SQL Server 2008 (http://msdn.microsoft.com).
Файлы и файловые группы
На физическом уровне база данных в Microsoft SQL Server 2008 представляется набором файлов операционной системы. Данные и сведения журналов транзакций всегда размещаются в разных файлах. Отдельные файлы используются только одной базой данных. Файловые группы представляют собой именованные коллекции файлов и используются для упрощения размещения данных и выполнения задач администрирования, например резервного копирования и восстановления.
Базы данных SQL Server содержат файлы трех типов:
Первичный файл данных является отправной точкой базы данных. Он указывает на остальные файлы базы данных. В каждой базе данных имеется один первичный файл данных. Для имени первичного файла данных рекомендуется использовать расширение MDF.
Ко вторичным файлам данных относятся все файлы данных, за исключением первичного файла данных. Базы данных могут вообще не содержать вторичных файлов данных, или содержать один или несколько вторичных файлов данных. Для имени вторичного файла данных рекомендуется использовать расширение NDF.
Файлы журналов содержат все сведения журналов, используемые для восстановления базы данных. В каждой базе данных должен быть по меньшей мере один файл журнала, но их может быть и больше. Для имен файлов журналов рекомендуется использовать расширение MDF, NDF и LDF. Однако эти расширения помогают пользователю идентифицировать различные виды файлов и правильно их использовать.
В SQL Server расположение всех файлов базы данных записывается в первичный файл базы данных и в специальную служебную структуру СУБД SQL Server, называемую базой данных master. В большинстве случаев при работе с базой данных компонент СУБД (SQL Server Database Engine ) использует сведения о размещении файлов, хранимые в базе данных master. Однако в некоторых случаях (например, при восстановлении базы данных master из копии, при определенным образом проводимом присоединении базы данных) компонент Database Engine использует сведения о расположении файлов из первичного файла, чтобы инициализировать записи о расположении файлов в базе данных master.
Файлы SQL Server имеют два имени:
Изначально можно указать максимальный размер каждого файла. Если максимальный размер файла не указан, файлы SQL Server могут автоматически увеличиваться в размерах, превосходя первоначально заданные показатели, пока не займут все доступное место на диске. При определении файла пользователь может указывать требуемый шаг роста. Каждый раз при заполнении файла его размер увеличивается на указанный шаг роста. Если в файловой группе имеется несколько файлов, их автоматический рост начинается лишь по заполнении всех файлов. Затем файлы увеличиваются в размерах по кольцевому списку. Эта функция особенно полезна в случаях, когда SQL Server используется в качестве базы данных, внедренной в приложение, где пользователь не имеет удобного доступа к системному администратору. По мере необходимости пользователь может предоставить файлам возможность увеличиваться в размерах автоматически, тем самым снимая с администратора часть забот по наблюдению за свободным пространством базы данных и по распределению дополнительного пространства вручную.
Из объектов баз данных и файлов можно формировать файловые группы, используемые для решения задач распределения и административного управления. Файлы журналов не могут входить в состав файловых групп. Управление пространством журнала отделено от управления пространством данных. Файл не может входить в состав нескольких файловых групп. Таблицы, индексы и данные больших объектов могут быть ассоциированы с указанной файловой группой. В этом случае все их страницы будут размещены внутри файловой группы; либо таблицы и индексы могут быть секционированы. Данные секционированных таблиц и индексов разделяются на блоки, каждый из которых может быть помещен в отдельную файловую группу базы данных. В каждой базе данных одна файловая группа назначается файловой группой по умолчанию. Если при создании таблицы или индекса файловая группа не указывается, предполагается, что все страницы будут распределяться из файловой группы по умолчанию. В каждый момент времени лишь одна файловая группа может быть файловой группой по умолчанию.
Страницы и экстенты
Основной единицей хранилища данных и обмена информацией между внешней и оперативной памятью в SQL Server является страница. Место на диске, предоставляемое для размещения файла данных (MDF- или NDF-файл) в базе данных, логически разделяется на страницы с непрерывным перечислением от 0 до n. Дисковые операции ввода-вывода выполняются на уровне страницы. А именно, SQL Server считывает или записывает целые страницы данных. В SQL Server размер страницы составляет 8 КБ. Это значит, что в одном мегабайте базы данных SQL Server содержится 128 страниц. Каждая страница начинается с 96-байтового заголовка, который используется для хранения системных данных о странице. Эти данные включают номер страницы, тип страницы, объем свободного места на странице и идентификатор единицы распределения объекта, которому принадлежит страница. В файлах данных базы данных SQL Server используется 8 типов страниц (данные с типами данных небольших размеров, данные с типами данных больших размеров, записи индекса, сведения о размещении экстентов, сведения о размещении страниц и доступном на них свободном месте и т. д.).
Экстент — это коллекция, состоящая из восьми физически непрерывных страниц или 64 Кб; они используются для эффективного управления страницами. Все страницы хранятся в экстентах. Таким образом, в одном мегабайте базы данных SQL Server содержится 16 экстентов.
Чтобы сделать распределение места эффективным, SQL Server не выделяет целые экстенты для таблиц с небольшим объемом данных. SQL Server имеет два типа экстентов:
Новая таблица или индекс — это обычно страницы, выделенные из смешанных экстентов. При увеличении размера таблицы или индекса до восьми страниц эти таблица или индекс переходят на использование однородных экстентов для последовательных единиц распределения. При создании индекса для существующей таблицы, в которой содержится достаточно строк, чтобы сформировать восемь страниц в индексе, все единицы распределения для индекса находятся в однородных экстентах. Пример размещения объектов в смешанном и однородном экстентах приводится на рис. 10.2.
Страницы файлов данных
Страницы файлов данных SQL Server нумеруются последовательно; первая страница файла получает нулевой номер (0). Каждый файл базы данных имеет уникальный цифровой идентификатор. Чтобы уникальным образом определить страницу базы данных, необходимо использовать как идентификатор файла, так и номер этой страницы. На рис. 10.3. показаны номера страниц базы данных, содержащей первичный файл данных объемом в 4 МБ и вторичный файл данных объемом в 1 МБ.
Первая страница каждого файла (страница с номером 0) — это страница заголовка файла; она содержит сведения об атрибутах данного файла. Страницы с номерами 1,2,3 будут описаны ниже.
Структура современной СУБД на примере Microsoft SQL Server 2008
Организация таблиц и индексов
Таблицы и индексы хранятся в виде коллекции страниц размером 8 КБ.
Страницы таблиц и индексов содержатся в одной или нескольких секциях. Секция — это пользовательская единица организации данных. По умолчанию таблица или индекс имеет единственную секцию, которая содержит все страницы таблицы или индекса. Секция располагается в одной файловой группе. Таблица или индекс, имеющие одну секцию, эквивалентны организационной структуре таблиц и индексов предыдущих версий SQL Server.
В каждой секции кучи или индекса содержится по крайней мере одна единица распределения IN_ROW_DATA. Кроме того, в зависимости от схемы кучи или индекса, там могут содержаться единицы распределения LOB_DATA или ROW_OVERFLOW_DATA.
Следующая иллюстрация показывает организацию таблицы ( рис. 10.4).
Куча — это последовательность строк таблицы, которые не имеют кластеризованного индекса. Строки данных хранятся без определенного порядка, и какой-либо порядок в последовательности страниц данных отсутствует. Страницы данных не связаны в связный список.
Управление работой с экстентами и свободным местом
Структуры данных SQL Server, управляющие использованием экстента и отслеживанием свободного места, обладают относительно простой структурой. Сведения о свободном месте плотно упакованы, поэтому эти данные содержат относительно небольшое количество страниц. Это приводит к увеличению скорости из-за уменьшения необходимых операций чтения диска для получения сведений о размещении. Также увеличивается вероятность того, что страницы размещения будут оставаться в памяти и повторных операций чтения не потребуется. Большая часть сведений о размещении не связана по цепочке друг с другом. Это упрощает управление сведениями о размещении. Каждое действие по размещению или освобождению страницы может выполняться быстро. Это сокращает конфликты между одновременными задачами использования и освобождения страниц.
SQL Server использует два типа карт для записи сведений об использовании экстентов:
На GAM-страницах записано, какие экстенты были задействованы. В каждой карте GAM содержатся сведения об использовании 64 000 экстентов или о размещении почти 4 ГБ данных. В карте GAM приходится по одному биту на каждый экстент в покрываемом им интервале. Если бит равен 1, то экстент свободен; если бит равен 0, то экстент задействован.
На SGAM-страницах записано, какие экстенты в текущий момент используются в качестве смешанных экстентов и имеют как минимум одну неиспользуемую страницу. В каждой карте SGAM содержится сведения об использовании 64 000 экстентов или о размещении почти 4 ГБ данных. В карте SGAM приходится по одному биту на каждый экстент в покрываемом им интервале. Если бит равен 1, то экстент используется как смешанный экстент и имеет свободную страницу. Если бит равен 0, то экстент не используется как смешанный экстент, или он является смешанным экстентом, но все его страницы используются.
Отслеживание свободного места
На страницы PFS (Page Free Space) записывается состояние размещения каждой страницы, информация о том, была ли отдельная страница использована или нет, а также количество свободного места на каждой странице. В PFS на каждую страницу приходится по одному байту, хранящему информацию о том, была ли страница использована или нет, а если была — то пустая она, или ее заполнение находится в промежутке от 1 до 50 процентов, от 51 до 80 процентов, от 81 до 95 процентов или от 96 до 100 процентов.
Создание физической модели хранилища данных
Изучив материал настоящей лекции, вы будете знать:
Объекты физической модели данных
Введение
В предыдущих лекциях мы изучали различные аспекты методов логического проектирования ХД. Методы логического проектирования основываются на абстрактном рассмотрении данных. Логическая модель никак не связана с конкретной реализацией модели в БД СУБД.
Далее, при изложении материала, мы будем предполагать, что имеем дело с реляционными или объектно-реляционными СУБД и соответствующими им диалектами SQL.
Можно выделить два этапа создания физической модели данных [48]. Основными целями первого этапа являются:
Главной целью второго этапа является обеспечение требуемого уровня производительности. Для достижения этой цели необходимо учитывать как особенности реализации СУБД, для которой создается физическая модель, так и особенности функционирования будущей информационной системы в целом. Обычно производительность БД измеряется в терминах производительности транзакций (transaction performance).
Для повышения производительности транзакций могут быть модифицированы объекты, созданные на первом этапе, или созданы новые объекты БД. Другими словами, разработка физической модели представляет собой итерационный процесс, причем итераций (создание объектов – анализ транзакций – модификация объектов – анализ транзакций) может быть несколько. Вопросы повышения производительности транзакций будут рассмотрены в одной из следующих лекций.
Иерархия объектов реляционной базы данных
Одной из главных задач, которые обязан решить проектировщик на стадии проектирования физической модели ХД, является задача превращения объектов логической модели данных в объекты реляционной БД. Для решения этой задачи проектировщику необходимо знать: а) какими объектами располагает реляционная база данных в принципе; б) какие объекты поддерживает конкретная СУБД, которая выбрана для реализации базы данных.
Таким образом, мы предполагаем, что решение о выборе СУБД уже принято руководителем ИТ-проекта и согласовано с заказчиком базы данных, т.е. СУБД задана. Проектировщик ХД должен ознакомиться с документацией, в которой описан диалект SQL, поддерживаемый выбранной СУБД. В настоящей лекции предполагается, что была выбрана СУБД семейства MS SQL Server компании Microsoft, хотя подавляющая часть материала охватывает объекты в любой промышленной реляционной СУБД.
Следует отметить, что ни одна из групп объектов стандарта SQL-92 не связана со структурами физического хранения информации в памяти компьютеров.
Основные объекты реляционной базы данных
Обычно процедура создания каталога определяется реализацией СУБД на конкретной операционной платформе. Под каталогом понимается группа схем. На практике каталог часто ассоциируется с физической базой данных как набором физических файлов операционной системы, которые идентифицируются ее именем.
Далее объекты БД будут определяться в контексте СУБД MS SQL Server 2005/2008. Такой подход принят потому, что проектирование физической модели БД выполняется для конкретной среды ее реализации.
Представление (View) — это поименованная динамически поддерживаемая СУБД выборка из одной или нескольких таблиц базы данных. Оператор выборки ограничивает видимые пользователем данные. Обычно СУБД гарантирует актуальность представления : его формирование производится каждый раз, когда представление используется. Иногда представления называют виртуальными таблицами.
Пользователь (User) — это объект, обладающий возможностью создавать или использовать другие объекты базы данных и запрашивать выполнение функций СУБД, таких как организация сеанса работы, изменение состояние базы данных и т.д.
Синоним ( Synonym ) — это альтернативное имя (псевдоним) объекта реляционной базы данных, которое позволяет иметь доступ к данному объекту. Синоним может быть общим и частным. Общий синоним позволяет всем пользователям базы данных обращаться к соответствующему объекту по его псевдониму. Синоним позволяет скрыть от конечных пользователей полную квалификацию объекта в базе данных.
Определенные пользователем типы данных ( User-defined data types ) представляют собой определенные пользователем типы атрибутов (домены), которые отличаются от поддерживаемых (встроенных) СУБД типов. Они определяются на основе встроенных типов.
Правила (Rules) – это декларативные выражения, ограничивающие возможные значения данных. Для формулировки правила используются допустимые предикатные выражения SQL.
Индекс (Index) — это объект базы данных, создаваемый для повышения производительности выборки данных и контроля уникальности первичного ключа (если он задан для таблицы).
Секция (Partition) — это объект базы данных, который позволяет представить объект с данными в виде совокупности подобъектов, отнесенных к различным табличным пространствам. Таким образом, секционирование позволяет распределять очень большие таблицы на нескольких жестких дисках.
Данные объекты реляционной базы данных представляют собой программы, т.е. исполняемый код. Этого код обычно называют серверным кодом (server-side code), поскольку он выполняется компьютером, на котором установлено ядро реляционной СУБД. Планирование и разработка такого кода является одной из задач проектировщика реляционной базы данных.
Хранимая процедура (Stored procedure) — это объект базы данных, представляющий поименованный набор команд SQL и/или операторов специализированных языков обработки программирования базы данных.
Функция (Function) — это объект базы данных, представляющий поименованный набор команд SQL и/или операторов специализированных языков обработки программирования базы данных, который при выполнении возвращает значение — результат вычислений.
Для эффективного управления разграничением доступа к данным поддерживается объект роль.
Домены в физической модели данных
В логической модели данных среда реализации не учитывается. В ней определяются атрибуты и их возможные значения, такие как строка, число или дата, в идеале атрибуту может назначаться домен. Домен — это просто тип атрибута, например, «Деньги» или «Рабочий день». Проектировщик может включить ряд проверок допустимости или правил обработки, например, требование, что значение должно быть положительным, ненулевым и иметь максимум два десятичных разряда (это полезно для вычисления сумм рублевых платежей, выставляемых банком на другой банк).
В контексте проектирования физической модели реляционной БД домен – это выражение, определяющее разрешенные значения для колонок (атрибутов) отношения. При описании таблицы реляционной БД каждой колонке назначается определенный тип данных. Практически, основу определения домена составляет тип данных, содержащихся в колонке, поскольку большинство встроенных типов задают разрешенный интервал значений данных.
Пример 11.1. Колонку в базе данных можно описать следующим образом:
Тема 11. Физические модели баз данных
Изучить принципы использования файловой структуры при хранении баз данных в MS SQL Server 2000.
Оглавление
Задания
1. Системы хранения данных в MS SQL Server 2000
По умолчанию при создании новой базы данных предлагается расположить ее в двух файлах (рис. 1).
В общем случае сложные и большие базы данных в MS SQL Server 2000 могут располагаться более чем в двух файлах. В таких случях эти файлы объединяются в группы.
Все файлы базы данных разделяются на три типа:
По умолчанию под новую базу данных отведено 2 Мбайта: 1 Мбайт под данные, 1 Мбайт под журнал транзакций. Желательно располагать эти файлы не только на разных логических дисках, но и, прежде всего, на разных физических дисках. Это обеспечит возможность наиболее полного и быстрого восстановления базы данных после сбоев.
Файлы данных могут быть объединены в две группы файлов. Первая группа называется Primary File Group, вторая User Define File Group. Одна из этих групп назначается группой по-умолчанию. При добавлении новые данные равномерно размещаются в файлах группы по-умолчанию, если явным образом не указано, в какой файл должны заноситься данные.
2. Индексные файлы
MS SQL Server 2000 поддерживает три типа индексов:
В операции создания индекса пользователь в явном виде задает тип индекса, который будет создан.
Некластерный индекс это самостоятельный объект базы данных. Он хранится отдельно от данных. Для одной таблицы может быть определено несколько некластерных индексов. Каждый из них создается для одного или нескольких столбцов.
В некластерном индексе значения индексируемых столбцов хранятся в упорядоченном виде, и каждому значению соответствует ссылка на строку исходной таблицы. Таким образом, некластерный индекс фактически соответствует плотному индексу, однако в некластерном индексе индексируемые значения могут повторяться, поэтому он создается не для первичного или возможного ключа, а для произвольного столбца таблицы, по которому необходимо обеспечить наиболее быстрый поиск.
В MS SQL Server при создании некластерного индекса можно явно задать порядок сортировки значений индексируемых столбцов.
Любой индекс создается следующей командой:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX_name ON table(column [ASC| DESC] [. n])
[WITH
[PAD_INDEX]
[[,] FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]]
[ON filegroup]
[SORT_IN_TEMPDB]
По умолчанию при создании таблицы создается кластерный индекс для первичного ключа данной таблицы. В каждой подчиненной таблице, кроме кластерного индекса по первичному ключу, создаются некластерные индексы для каждого внешнего ключа (рис. 5).
Характеристики индексов, созданных по умолчанию, можно посмотреть и при необходимости отредактировать, нажав кнопку Edit (рис. 6).
Удаляется индекс следующей командой:
Предположим, мы создали дополнительный индекс для таблицы «Книги» по полю «Год издания» YEARIZD :
CREATE
INDEX [books_YEAR] ON [dbo].[Books] ([YEARIZD])
IF EXISTS (SELECT name FROM sysindexes
WHERE name = ‘books_YEAR’)
DROP INDEX books.books_YEAR
3. Дополнительная статистика по индексу
Ниже приведены команды создания дополнительной статистики и ее обновления:
CREATE STATISTICS statistics_name
ON
