Ms sql server секционирование таблиц. Создание физической модели базы данных: проектирование производительности
Создание физической модели базы данных: проектирование производительности
Секционирование представлений
В СУБД Oracle есть возможность секционировать представления. Основная идея секционирования представлений проста. Пусть физическая таблица разбита на несколько таблиц (необязательно с помощью методов секционирования таблиц) в соответствии с критерием разбиения, который делает обработку запроса более производительной. Критерий разбиения будем называть предикатом секционирования. Тогда можно создать и настроить представления таким образом, чтобы с их помощью обращение к данным этих таблиц было проще для пользователя. Секция представления определяется в соответствии с диапазоном значений ключа секционирования . Запросы, которые используют диапазон значений для выборки данных из секций представления, будут получать доступ только к тем секциям, которые соответствуют диапазонам значений ключа секционирования .
Секции представления могут быть определены предикатами секционирования , заданными либо при помощи ограничения CHECK , либо с использованием предложения WHERE . Покажем, как могут быть применены оба приема, на примере несколько модифицированной таблицы “Продажи” (Sales), которую мы рассматривали в предыдущем разделе. Допустим, что данные о продажах для календарного года размещаются в четырех отдельных таблицах, каждая из которых соответствует кварталу года — Q1_Sales, Q2_Sales, Q3_Sales и Q4_Sales.
Секционирование представлений с помощью ограничения CHECK . С помощью команды ALTER TABLE можно добавить ограничения на колонку “Дата продажи” (s_date) каждой таблицы, чтобы ее строки соответствовали одному из кварталов года. Созданное затем представление sales дает возможность обращаться к этим таблицам, как к одной, так и ко всем вместе.
Преимуществом такого секционирования представлений является то, что предикат ограничения CHECK не оценивается для каждой строки запроса. Такие предикаты исключают вставку в таблицы строк, не соответствующих критерию предиката. Строки, соответствующие предикату секционирования , извлекаются из базы данных быстрее.
Секционирование представлений с помощью предложения WHERE . Создадим представление для тех же таблиц, что и в примере выше.
Метод секционирования представлений с помощью предложения WHERE имеет некоторые недостатки. Во-первых, критерий секционирования проверяется во время выполнения для всех строк во всех секциях, которые охватываются запросом. Во-вторых, пользователи могут ошибочно вставить строку не в ту секцию, т.е. вставить строку, относящуюся к первому кварталу, в третий квартал, что приведет к неправильной выборке данных по этим кварталам.
У этого приема есть и достоинство по сравнению с использованием ограничения CHECK . Можно разместить секцию, соответствующую предикату WHERE , на удаленной базе данных. Фрагмент определения преставления приведен ниже.
Принимая решение о создании секционированных представлений , необходимо помнить о следующих факторах.
- Секционирование представлений позволяет операциям DML, таким как загрузка данных, создание индексов и удаление данных, работать на уровне секции, а не целой базовой таблицы.
- Доступ к одной из секций не оказывает никакого действия на данные в других секциях.
- СУБД Oracle обладает необходимыми встроенными возможностями для распознавания секционированных представлений .
- Секционирование представлений очень полезно при работе с таблицами, содержащими большое количество исторических данных.
Секционирование таблиц в СУБД семейства MS SQL Server
Создание секционированных таблиц
В СУБД семейства MS SQL Server также поддерживается секционирование таблиц, индексов и представлений. Однако, в отличие от СУБД семейства Oracle, секционирование в СУБД семейства MS SQL Server выполняется по унифицированной схеме.
В MS SQL Server все таблицы и индексы в БД считаются секционированными, даже если они состоят всего лишь из одной секции. Фактически, секции представляют собой базовую организационную единицу в физической архитектуре таблиц и индексов . Это означает, что логическая и физическая архитектура таблиц и индексов , включающая несколько секций, полностью отражает архитектуру таблиц и индексов , состоящих из одной секции.
Секционирование таблиц и индексов задается жестко на уровне строк ( секционирование по столбцам не допускается) и позволяет осуществлять доступ через единую точку входа (имя таблицы или имя индекса ) таким образом, что в коде приложения не требуется знать число секций. Секционирование может осуществляться на базовой таблице, а также на связанных с ней индексах .
Для создания секционированной таблицы в СУБД MS SQL Server используются следующие объекты БД: функции секционирования и схемы секционирования . Эти объекты позволяют разделять данные на конкретные сегменты и управлять их местоположением в БД или ХД. Например, можно распределить данные по нескольким дисковым массивам в зависимости от даты поступления данных или других отличительных признаков. Следует отметить, что таблицу можно секционировать по одному из ее столбцов, и каждая секция должна содержать данные, которые не могут храниться в других секциях.
Функции секционирования
При секционировании таблицы сначала нужно определить правило, по которому данные будут разделяться на сегменты. Для сопоставления отдельных строк данных с разными сегментами служит функция секционирования .
Строки данных могут сегментироваться по колонке любого типа, кроме следующих: text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), псевдонимы типов данных и пользовательские типы данных среды CLR. Однако функция секционирования должна распределять каждую строку данных только в одну секцию таблицы ; иными словами, в результате применения функции одна и та же строка не может принадлежать нескольким секциям одновременно.
Чтобы секционировать таблицу, в ней необходимо создать или выбрать колонку секционирования ( ключ секционирования ). Ключ секционирования можно создать в схеме таблицы в момент создания таблицы либо добавить позднее путем модификации таблицы. Столбец может принимать значения NULL, но все строки, содержащие значения NULL, будут по умолчанию помещаться в самую левую секцию таблицы . Этого можно избежать, указав при создании функции секционирования , что значения NULL должны помещаться в самую правую секцию таблицы . Выбор левой или правой секций – важное решение проектирования, проявляющееся при изменении схемы секционирования , добавлении дополнительных секций или удалении существующих.
При создании функции секционирования можно выбрать функции LEFT или RIGHT . Разница между секциями LEFT и RIGHT состоит в размещении данных по секциям. Функция LEFT распределяет данные по принципу от самого низкого значения до самой высокой величины (то есть по возрастанию). Функция RIGHT распределяет данные по принципу от самого высокого значения до самого низкого (то есть по убыванию). Рассмотрим пример.
Возьмем следующие примеры определения функций секционирования с использованием LEFT и RIGHT :
В первой функции ( Left_Partition ) значения 1, 10 и 100 размещаются соответственно в первой, второй и третьей секциях. Во второй функции ( Right_Partition ) эти значения размещаются во второй, третьей и четвертой секциях.
При создании секционированной таблицы важно, чтобы секции получились сбалансированными по кардинальности. Это позволяет оценить, сколько дискового пространства потребуется для каждой секции. Использование параметров LEFT и RIGHT определяет, куда будут размещаться данные, что, в свою очередь, задает размер секции и размеры индексов , созданных на ней.
Определить номер секции, в которую попадут те или иные данные, можно с помощью функции $PARTITION , как показано ниже:
Первая команда SELECT возвращает значение 2, вторая – значение 3.
Схемы секционирования
После создания функции секционирования и выбора способа разделения данных по секциям следует решить, где будет создаваться каждая секция в файловой системе. Для создания связи секций с их размещением в файловой системе используются схемы секционирования . Схемы секционирования управляют тем, каким образом отдельные секции хранятся на диске, путем использования файловых групп для размещения каждой секции на дисковой подсистеме. Схемы секционирования можно настроить таким образом, чтобы все секции располагались в единой файловой группе, чтобы каждая секция располагалась в своей файловой группе или чтобы несколько секций использовали общие файловые группы. Последний способ дает администратору базы данных широкие возможности рассредоточения операций ввода-вывода на диске.
В примере 20.17 показаны некоторые из способов, позволяющих присвоить схеме секционирования одну или несколько файловых групп. Следует помнить, что файловые группы, используемые схемой секционирования , должны существовать в базе данных перед созданием схемы.
Выполним присвоение файловых групп схеме секционирования . Сначала приведем пример размещения всех секций таблицы в одной файловой группе с именем PRIMARY.
Чтобы разместить все секции в различных файловых группах, нужно выполнить следующую команду:
Чтобы разместить несколько секций в различных файловых группах, нужно выполнить следующую команду:
Если создать указанные в примере функции секционирования и использовать приведенную схему секционирования для создания таблицы, то впоследствии можно будет определить, где будут размещаться отдельные строки данных в секционированных таблицах.
Рассмотрим в качестве примера схему типа “звезда” с таблицей фактов “Продажи” (SALES), как показано на рис. 20.6. Создадим секционированную таблицу “Продажи” (SALES).
Сначала мы должны создать функцию секционирования :
MyPartitionFunctionLeft — это название функции разделения, datetime — тип данных ключа секционирования , а RANGE LEFT указывает, как делить значения данных, которые связаны с датами FOR VALUES .
Ключ секционирования имеет тип данных date , т.е. это колонка “Дата события” (Date_of_Event). В команде, приведенной выше, деление строк на непересекающиеся группы построено по принципу разбиения их на двухлетние группы. Разделение на секции RANGE LEFT делит данные в диапазонах значений, показанных на рис. 20.7.
Каждая область значений в секции имеет границы, которые определены в операторе FOR VALUES . Если дата продажи была 23 июня 2006 года, то строка будет храниться в секции 2 (P2).
Теперь создадим схему секционирования . Схема секционирования отображает секции на различные файловые группы (с именами MyFilegroup1, MyFilegroup2, MyFilegroup3, MyFilegroup4 ) , как показано в следующей команде:
MyPartitionScheme – это имя схемы секционирования , а имя MyPartitionFunction определяет функцию секционирования . Эта команда отображает данные в секции, которые связаны с одной или несколькими файловыми группами. Строки с данными со значениями колонки “Дата продажи” (Date_of_Event date) до 1/01/05 связаны с MyFilegroup1 . Строки этой колонки со значениями, большими или равными 1/01/05 и до 1/01/07, назначены MyFilegroup2 . Строки со значениями, большими или равными 1/01/07 и до момента 1/01/09, связаны с MyFilegroup3 . Все остальные строки со значениями, большими или равными 1/01/09, связаны с MyFilegroup4 .
Для каждого набора граничных значений (которые задаются условием FOR VALUES функции секционирования ) количество секций будет равно “Количество граничных значений” + 1 секция. Предыдущее предложение CREATE PARTITION SCHEME включает три ограничения и четыре секции. Независимо от того, созданы ли секции с RANGE RIGHT или RANGE LEFT , количество секций всегда будет равно “Количество граничных значений” + 1, вплоть до 1000 секций на таблицу.
Теперь мы можем создать секционированную таблицу фактов “Продажи” (SALES). Создание секционированной таблицы мало чем отличается от создания обычной таблицы, нужно только сослаться на имя схемы секционирования в условии ON , как показано в команде ниже.
Определяя имя схемы секционирования , проектировщик указывает, что эта таблица является секционированной . Очевидно, схема секционирования , функция секционирования и файловые группы должны существовать в БД до того, как будет создаваться таблица.
Можно объединять только две смежные секции. Чтобы слить две секции, выполните команду:
Здесь секция 1 (P1) объединится с секцией P2. Это означает, что секция P2 будет содержать все строки со значением колонки “Дата продажи” (Date_of_Event) до значения даты 1/01/07. В системной таблице sys.partitions секции будут перенумерованы, начиная с единицы (не с нуля). Секции P1 и P2 станут P1, секция P3 станет P2 и P4 станет P3.
Секционирование индексов в СУБД семейства MS SQL Server
В СУБД семейства MS SQL Server предусмотрена возможность создавать секционированные индексы . Это позволяет проектировщику проектировать структуру индекса на основе разделенных данных, а не на основе всех данных таблицы. Создание секционированных индексов влечет за собой создание отдельных сбалансированных деревьев на секционированных индексах . В результате разделения индексов создаются индексы меньшего размера, и администратору БД или ХД становится проще их обслуживать во время изменения, добавления и удаления данных.
При создании секционированных индексов можно создавать выровненные или невыровненные индексы. Выровненные индексы подразумевают прямую связь с секционированными данными таблицы. В случае с невыровненными индексами выбирается другая схема секционирования .
Из этих двух методов предпочтителен выровненный индекс , который выбирается по умолчанию, если после создания секционированной таблицы индексы создаются без указания другой схемы секционирования . Использование выровненных индексов предоставляет необходимую гибкость для создания дополнительных секций в таблице, а также позволяет переводить принадлежность той или иной секции на другую таблицу. Для решения большинства задач, связанных с секционированием , достаточно применить для индексов схему секционирования таблицы.
Создадим секционированный некластеризованный индекс на секционированной таблице “Продажи” (SALES) из предыдущего примера 20.18. Некластеризованный индекс выравнивается с таблицей; в качестве ключа некластеризованного индекса используется ключ секционирования таблицы.
Для того чтобы создать невыровненный некластеризованный индекс на секционированной таблице “Продажи” (SALES) из примера 20.18, можно поступить следующим образом. Сначала создадим функцию секционирования для индекса .
Затем разместим все секции индекса в одной файловой группе с именем PRIMARY , выполним команду
Теперь выполним команду создания индекса , как показано ниже.
В этом некластеризованном индексе в качестве ключа индекса используется колонка “Идентификатор покупателя” (Cust_ID), которая не является ключом секционирования таблицы “Продажи” (SALES).
Решения о секционировании индексов принимаются проектировщиком ХД на стадии проектирования или администратором ХД на стадии эксплуатации ХД. Целью секционирования индексов является либо обеспечение производительности запросов, либо упрощение процедур сопровождения индекса .
Проектирование базы данных
Проектирование базы данных является очень важным этапом в жизненном цикле базы данных, который предшествует всем другим этапам, за исключением этапа сбора и анализа требований. Если создать проект базы данных, руководствуясь единственно интуицией и без какого-либо плана, то получившаяся база данных, скорей всего, не будет отвечать требованиям пользователя в плане производительности.
Другим последствием плохого проекта базы данных будет чрезмерная избыточность данных (data redundancy), которая сама по себе имеет два недостатка: наличие аномалий в данных и повышенные требования дискового пространства.
Нормализацией данных (data normalization) называется процесс, в котором таблицы базы данных проверяются на наличие определенных зависимостей между столбцами таблицы. Если таблица содержит такие зависимости, она разбивается на несколько таблиц (обычно две), что позволяет избавиться от зависимостей между столбцами. Если одна из этих таблиц все еще содержит зависимости, процесс нормализации повторяется до тех пор, пока все зависимости не будут разрешены.
Процесс удаления избыточных данных в таблицах основан на теории функциональных зависимостей. Функциональная зависимость означает, что по значению одного столбца можно всегда однозначно определить соответствующее значение другого столбца. (То же самое действительно и для групп столбцов.) Функциональная зависимость между столбцами A и B обозначается как A->B, что означает, что значение в столбце B можно всегда определить по соответствующему значению в столбце A. (Данная формула читается как “B функционально зависит от A”.)
В примере ниже демонстрируется функциональная зависимость между двумя атрибутами таблицы Employee базы данных SampleDb:
Имея однозначное значение табельного номера сотрудника, можно определить его фамилию (и все прочие соответствующие столбцы). Такой тип функциональной зависимости, когда столбец зависит от первичного ключа таблицы, называется тривиальной функциональной зависимостью.
Другой тип функциональной зависимости называется многозначной зависимостью. В отличие от только что описанной функциональной зависимости, многозначная зависимость задается для многозначных атрибутов. Это означает, что, используя известное значение одного атрибута (столбца), можно однозначно определить набор значений другого многозначного атрибута. Многозначная зависимость обозначается как ->->.
В примере ниже показана многозначная зависимость, действующая между двумя атрибутами объекта book:
Значение ISBN книги всегда определяет всех ее авторов. Поэтому атрибут Authors многозначно зависит от атрибута ISBN.
Нормальные формы
Нормальные формы применяются в процессе нормализации данных и, следственно, при проектировании баз данных. Теоретически, существует, по крайней мере, пять разных нормальных форм, из которых наиболее важными для практического применения являются первые три. Третью нормальную форму таблицы можно получить, выполнив проверку на первую и вторую нормальные формы в качестве промежуточных этапов. Цель получения хорошего проекта базы данных обычно достигается, если все таблицы базы данных находятся в третьей нормальной форме.
Многозначная зависимость применяется для проверки таблиц на четвертую нормальную форму. Поэтому данный тип зависимости не будет использоваться.
Первая нормальная форма
Первая нормальная форма (First Normal Form, 1NF) означает, что таблица не содержит многозначных или составных атрибутов. (Составной атрибут содержит другие атрибуты, поэтому его можно разделить на меньшие части.) По определению, все реляционные таблицы находятся в первой нормальной форме, т.к. значение любой ячейки должно быть атомарным, т.е. однозначным.
В таблице ниже демонстрируется первая нормальная форма (1NF) на примере таблицы Works_on базы данных SampleDb. Строки этой таблицы можно сгруппировать, используя табельный номер сотрудника:
Проектирования структуры базы данных в MS SQL Server
Логическое имя – это имя, используемое в SQL-выражениях. Логическое имя должно соответствовать правилам SQL Server на идентификаторы и быть заключено в одинарные кавычки. Вот эти правила:
Имя объекта должно иметь длину в пределах от 1 до 30 символов. Первым символом должна быть буква.
Имя может содержать буквы, цифры и символы #, $,
Зарезервированные слова Transact-SQL запрещены.
Физическое имя – это буква дисковода, полный путь и имя файла устройства).
Номер виртуального устройства – это идентификатор устройства. Он может принимать значения от 0 до 255. Идентификатор 0 зарезервирован для устройства master.
С помощью параметра SIZE устанавливается размер устройства. Размер измеряется в страницах.
Параметр VSTART необходим только в определенных случаях. Он представляет собой стартовое смещение в страницах.
Для удаления устройства используется системная процедура spdropdevice. Ей требуется 2 параметра. Первым параметром передается имя устройства, а вторым – ключевое слово DELFILE. В том случае, если ключевое слово DELFILE не указывается в качестве параметра, файл не удаляется с жесткого диска – его придется удалять средствами операционной системы.
Когда размер устройства перестанет удовлетворять вашим запросам, его можно увеличить с помощью команды DISK RESIZE.
Команду создания баз данных CREATE DATABASE может выдать только системный администратор, поэтому перед тем, как выполнять примеры, которые находятся в ном разделе, выясните, имеются ли у вас достаточные полномочия.
Когда необходимо увеличить размер БД, можно использовать команду ALTER DATABASE. Ее синтаксис:
Для того, чтобы уменьшить размер БД, используется команда DBCC SHRINKDB.
DBCC SHRINKDB (facultet, 5012)
Системные типы данных
Вупагу, Varbinary, Varchar,
Числовые типы данных
Doubleprecision 1 8-15
Индексы очень важны для обеспечения требуемого порядка вывода данных, поддержания связей между таблицами и ускорения выборки данных. В SQL Server можно создать 2 типа индексов кластерные (clustered) и некластерные (non-clustered).
Ограничение UNIQUE будет следить за уникальностью данных и комбинации составляющих его столбцов.
Синтаксис ограничения для создания уникального индекса: [CONSTRAINT ]
UNIQUE [CLUSTERED | NONCLUSTERED]
Программная реализация базы данных с помощью SQL
Для создания таблицы в окне «ObjectExplorer» выбираем созданную базу данных, и в раскрывшемся списке правой кнопкой мыши нажимаем на пункте «Tables», в котором находим и выделяем пункт «Newtable. ». В рабочей области СУБД MS SQL Server откроется форма длясоздании таблицы
В столбце ColumnName указывается название поля таблицы, в столбце Data Туре – указывается тип столбца. В столбце AllowNullsустанавливается флажок, предоставляющий возможность оставлять ячейку не заполненной (пустой).
При создании таблицы для последующей связи с другими таблицами необходимо задавать ключевые и индексные поля.
Первоначально создадим таблицу University. Далее в окне «ColumnProperties» находим пункт «IdentitySpecification»
Сущности и связи.
Рассмотрим сущности на основе которых создана база данных University.
Приведем список сущностей базы данных:
Университеты города; Факультеты университетов; Специальности; Студенты каждой специальности
Каждая сущность является отдельной таблицей и независима от других объектов базы данных и обладает собственными свойствами.
Для представления информации о базе можно использовать диаграммы сущность -cвязь(ER-Diagrams). Обычно каждая таблица в такой диаграмме Представлена в виде прямоугольника, содержащего названия столбцов.
Идея создания диаграммы, в окне “ObjectExplorer” выбираем нашу базу данных, нажимаем правой клавишей мыши на вкладке DatabaseDiagram, и выбираем пункт «NewDatabaseDiagram», в котором добавляем все таблицы нашей базы данных
Следует отметить, что одна из задач проектирования базы состоит и обеспечения способа идентификации различных объектов, другими словами система должна отличать друг от друга отдельные строки таблицы. Для связи данных между таблицами необходимо создать отношения. Отношения бывают:
Один к одному – определение связи «один к одному» полностью соот-нстствует ее названию. Связью «один к одному» называется такая связь, н I наличия которой следует, что ссли имеется какая-то одна строка в одной таблице, то должна быть точно одна соответствующая ей строка в другой таблице.
Связь «Один ко многим» является типичной связью между таблицами, где одна таблица по вторичному ключу ссылается на вторую таблицу с индексным ключом.
Связь «Многие ко многим» характеризуется тем что на обеих сторонах связи может присутствовать несколько согласующих строк
Дата добавления: 2018-05-13 ; просмотров: 294 ;