Индексы баз данных
Таблицы баз данных. Создание, удаление, изменение
Основные характеристики MS SQL Server. Системные базы данных, таблицы и хранимые процедуры. Базы данных и файлы
СУБД MS SQL Server - -это распределенная СУБД, реализующая клиент-серверную технологию. Задача сервера – управление хранением данных и осуществление контроля целостности. Задача клиента – осуществление контроля вводимых данных, управление интерфейсом пользователя, приведение данных к нужному формату для пользователя. Язык – Transact SQL,являющийся расширением языка SQL (позволяет создавать хранимые процедуры, триггеры, такие объекты баз данных как пользовательские типы, правила, значения по умолчанию, средства работы с транзакциями). Операционная система Windows NT. СУБД MS SQL Server позволяет определять до 32767 баз данных, внутри каждой базы данных до 2 миллиардов таблиц. В каждой таблице до 250 столбцов; нет ограничения на количество строк в таблице. Для каждой таблицы можно определить до 250 индексов.
Основные службы:
1) SQL Server Management Studio – главная утилита администрирования и работы с данными
2) SQL Server Agent – служба автоматизации административных задач
3) SQL Server Browser – обозреватель, предоставляющий клиентским компьютерам информацию о соединениях с SQL Server
4) SQL Server Profiler – утилита управления отчетами, генерируемыми службами
При работе SQL Server возникает задача сохранения информации о том, как хранятся данные в базе. Информация о хранении данных называется метаданными, которые хранятся в словаре данных. Словарь данных реализуется в виде системных таблиц и заполняется при выполнении команд Create и Alter. Базы данных делятся на системные и пользовательские ,базы данных.
В системных базах данных хранятся данные, используемые системой для управления другими данными. В SQL Server имеет четыре вида системных баз данных: master, model, tempdb, msdb.
Master -системный каталог, хранящий следующую информацию: учётные записи пользователей, текущие процессы, сообщение о системных ошибках, сведения о базах данных на сервере, размер каждой базы, активные блокировки, доступные устройства баз данных для каждого пользователя, доступные резервные (dump) устройства, системные процедуры для администратора.
Model -шаблон для вновь создаваемых на сервере баз данных SQL Server создаёт копию базы данных model. В model добавляют объекты, которые используются в каждой вновь создаваемой базе данных.
Tempdb–системная база данных, которая обеспечивает место для размещения на диске временных таблиц и других объектов временного хранения, таких как курсоры, результаты группировки данных и другие. Для доступа к этой базе не надо специальных разрешений и она удаляется при прерывании связи с SQL Server.
Msdb-поддерживает службу SQL Server Agent – планировщика задач (оповещения и задания, данные об операторах) и управления ошибочными ситуациями, в эту базу входят таблицы – sysalerts – таблица о всех определённых пользователем событиях, Sysoperators – информация о всех операторах и другое при работе с интернет и электронной почтой.
Каждая системная и все пользовательские базы данных имеют системные таблицы, где заносится информация о SQL Server в целом и о каждой базе данных в отдельности. Все системные таблицы начинаются с SYS.
Каждая пользовательская база данных состоит из двух и более файлов, каждый из которых может использоваться лишь одной базой. У файлов существует два имени – логическое и физическое. Файлы делятся на три типа: первичные файлы(используются для хранения данных и информации, определяющих начальные действия с базой, база данных содержит лишь один первичный файл - primary, стандартное расширение- .mdf); вторичные файлы – secondary (одна или несколько областей для хранения данных, могут использоваться для распределения операций чтения/записи по нескольким дискам, стандартное расширение – ndf); файлы журналов – log (содержат журналы транзакций базы данных, база данных содержит хотя бы один файл журнала транзакций, стандартной расширение – ldf). Каждый файл может принадлежать лишь одной базе данных. Каждая база данных содержит, по крайней мере, один первичный файл и один файл журнала.
Файловая группа – это способ создания набора файлов. Файл может относиться только к одной группе(файлы журналов не могут входить в файловые группы). Файловые группы используются для распределения нагрузки, связанной с выполнением чтения и записи, по нескольким дискам для таблиц, которые размещены на этой файловой группе. Если группа содержит больше одного файла, чтение и запись для этой группы распределяется между файлами групп. Каждая база данных имеет первичную файловую группу под названием Primary. Эта группа содержит первичный файл данных и все остальные файлы данных, для которых специально не указано, что они относятся к другим файловым группам. Данные могут размещаться в любой файловой группе. Все данные, для которых специально не указано, что они будут храниться в определенной файловой группе, будут размещены в файловой группе по умолчанию. Любую файловую группу можно сделать файловой группой по умолчанию в любой момент времени. Файл и файловая группа могут использоваться только одной базой данных. Файл может быть членом только одной файловой группы. Вы должны хранить данные и записи журнала в разных файлах. Файлы данных и файлы журнала не могут принадлежать к одной файловой группе, так как файлы журнала вообще не могут входить в какие либо файловые группы. В одной базе данных может быть одновременно до 256 файловых групп. Для повышения производительности при работе с базой данных необходимо придерживаться следующих рекомендаций:
- файлы и файловые группы размещаются по возможно большему числу локальных дисков (не рекомендуется использовать сетевые диски, так как обращение к ним происходит медленнее, чем к локальным и быстродействие снижается);
- объекты, увеличивающиеся в размерах наиболее быстро, желательно размещать в различных файловых группах;
- таблицы, которые обычно используются вместе в запросах, размещайте в различных файловых группах, что позволит увеличить производительность за счет распараллеливания операций обращения к дискам;
- таблицы, к которым обращаются наиболее часто, и их некластерные индексы размещайте в различных файловых группах, так как при этом увеличивается производительность за счет распараллеливания операций обращения к дискам;
- не размещайте файлы журналов на тех же физических дисках, где размещены файлы и файловые группы или другие файлы, не относящиеся к SQL Server.
Журнал транзакций используется для последовательной записи изменений всех изменений, вносимых в базу данных. Главная цель журнала - обеспечение целостности данных в базе, даже если какая-то транзакция не была завершена. После перезагрузки сервера произойдет откат всех незавершенных транзакций, и изменения будут отменены. В результате данные в базе данных вернуться к тому же состоянию, в котором они находились до начала незавершенной транзакции. При успешном завершении транзакции записи помечаются в журнале как завершенные, и отката назад уже не произойдет.
Журнал транзакций состоит из множества виртуальных файлов журнала. Виртуальный файл журнала – это сегмент файла журнала. Когда журнал транзакций обрезается, при этом удаляется один или более виртуальных файлов журнала. Файл журнала состоит из двух или более виртуальных файлов. Минимальный размер виртуального файла журнала – 256 Кбайт. SQL Server создает новые виртуальные файлы, когда журнал увеличивается в размере. Количество и размер виртуальных файлов зависят от размера приращения (growth increment) для файла журнала. Больший размер приращения определяет и большой размер виртуальных файлов. Создание базы данных осуществляется либо через главную утилиту администрирования, используя инструментальные средства, либо с помощью команды CREATE DATABASE.
CREATE DATABASE <имя базы данных>
[ ON [PRIMARY] ( [NAME = <логическое имя файла>, ]
[ , FILENAME = <имя файла ОС>
[, SIZE =< размер>]
[ MAXSIZE = {<максимальный размер>| UNLIMITED}]
[ FILEGROWTH = <приращение> ] ) |
{ FILEGROUP <имя группы файлов> FILEDEFINITIONS}
[ , …n ] ]
[ LOG ON { [ NAME = <логическое имя файла> ,}
[FILENAME = <имя файла ОС>]
[, SIZE =< размер> ]
[, MAXSIZE = {<максимальный размер> | UNLIMITED}]
[, FILEGROWTH = <приращение> ] )
[ , …n ]
[FOR LOAD| FOR ATTACH].
В случае, если при создании базы данных не указан первичный файл данных и/или файл журнала, то отсутствующий файл (или файлы) создаются с именем по умолчанию. Физические файлы будут находиться в стандартном каталоге. Первичному файлу приписывается имя: имя базы.mdf, а файлу журнала – имя базы.ldf. Если размер файлов не задан, то при создании размер первичного файла совпадает с размером первичного устройства базы данных model, а размер файла журнала и вторичных файлов равен 1 Мбайт. Он может быть и больше, если размер первичного файла базы данных model превышает 1 Мбайт. Хотя имена и размеры файлов указывать не обязательно, на практике это нужно делать. SQL Server создает базу в два этапа. На первом этапе база данных model копируется в новую базу данных, а на втором этапе инициализируется все неиспользуемое пространство.
Команда CREATE DATABASE имеет следующие параметры:
PRIMARY – файл определяется как первичное устройство;
NAME – логическое имя, по умолчанию совпадающее с базой данных;
FILENAME – полное имя файла на диске;
SIZE – исходный размер файла. Минимальный размер файла журнала равен 512 Кбайт;
MAXSIZE – максимальный размер файла;
UNLIMITED – размер файла не ограничивается
FILEGROWTH – приращение файлов в мегабайтах (МВ), килобайтах (КВ) или процентах (%). По умолчанию приращение равно 10%.
FOR LOAD – обеспечивает обратную совместимость со сценариями SQL Server, написанными для предыдущих версий.
FOR ATTACH – указывает, что файлы базы даныных уже существуют.
Пользователь, создавший базу данных, является её владельцем. Все параметры конфигурации базы данных копируются из базы данных model, если только при создании базы данных не был указан параметр FOR ATTACH. Удаление базы данных осуществляется командой
DROP DATABASE <имя базы данных> [,…n]
При удалении базы удаляются файлы, используемые ими. Нельзя удалить базу, используемую в данный момент. После удаления базы данных её файлы не могут быть использованы.
Изменение владельца базы данных осуществляется с помощью системной хранимой процедуры:
Sp_changedbowner [@loginame=] ‘учетная запись пользователя для входа’[,[@map=]флаг уничтожения псевдонима]
Владельцем базы данных можно сделать любую учетную запись, которая в настоящий момент не является пользователем базы данных. Только пользователи с правами системного администратора имеют право менять собственника базы данных. Невозможно изменить собственника системных баз данных. Перед изменением собственника базы данных вы должны быть подключены к этой базе данных. Использование хранимой процедуры показано на примере:
USE MyDB
Go
Sp_changedbowner @loginame=’NewDBO’
go
Для смены имени используется хранимая процедура
Sp_renamedb “старое имя”,”новое имя”
Базу данных может переименовать только системный администратор. Предварительно база данных должна быть переведена в однопользовательский режим.
Изменение определений базы данных и её размеров осуществляется командой:
ALTER DATABASE <имя базы данных>
{ADD FILE <спецификация> [,…n] [TO FILEROUP <имя группы> ]
| ADD LOG FILE <спецификация> [,…n]
| REMOVE FILE <логическое имя файла>
| ADD FILEGROUP <имя группы>
| REMOVE FILEGROUP <имя группы>
| MODIFY FILE <спецификация>
| MODIFY FILEGROUP <имя группы> < свойство группы>
}
<спецификация> ::=
( NAME = <логическое имя файла>
[,FILENAME= <имя файла ОС>]
[, SIZE = <размер> ]
[, MAXSIZE = {<максимальный размер> | UNLIMITED}]
[, FILEGROWTH = <приращение>])
Данная команда позволяет включать файлы в существующие группы или добавлять в базу данных новые файлы журналов. При добавлении нового файла данных без указания группы, файл включается в первичную группу. Команда выполняет также удаление пустых файлов. Первичная группа всегда должна содержать как минимум один первичный файл и один файл журнала в базе данных. При удалении файла удаляется физический файл. Осуществляет добавление новых групп файлов. При добавлении новой группы в нее не включаются никакие файлы. Осуществляет удаление групп файлов. Если в группе присутствуют какие-либо файлы, они должны быть пустыми; в противном случае удаление группы невозможно. Удалить первичную группу файлов невозможно.
Используя команду ALTER DATABASE для модификации файлов, необходимо указывать логическое имя файла. Спецификация позволяет изменить все остальные параметры, но в одной команде ALTER DATABASE можно указать один параметр. Если изменяется размер файла, новый размер не может быть меньше текущего.
При модификации группы файлов можно задавать свойства READONLY, READWRITE, DEFAULT. Свойство READONLY говорит о том, что группа файлов доступна только для чтения. Оно предотвращает возможную модификацию объектов из этой группы. Для первичной группы установка этого свойства невозможна. Свойство READONLY может устанавливаться только пользователями, обладающими монопольным доступом к базе данных. Свойство READWRITE обладает противоположным действием. Оно разрешает обновление объектов в группе. Свойство READWRITE может устанавливаться только пользователями, обладающими монопольным доступом к базе данных. Свойство DEFAULT говорит о том, что группа назначается стандартной группой для базы данных. При установке свойства DEFAULT это свойство отменяется для группы, которая была назначена стандартной ранее. При создании базы стандартной группой назначается первичная группа файлов. Если в командах CREATE NABLE, ALTER TABLE, CREATE INDEX не указана группа файлов, новые таблицы и индексы создаются в стандартной группе. Непосредственное перемещение файлов из одной группы в другую невозможно. Для этого нужно сначала удалить файл, а затем включить его в новую группу.
Уменьшение размера баз данных вручную выполняется командой
DBCC SHRINKDATABASE ( <имя базы> [,< процент>]
[, { NOTRUNCATE| TRANCATEONLY}])
При сжатии базы данных можно указать необязательный параметр – процент свободного места, которое вы хотите оставить в базе данных. Параметр NOTRUNCATE отменяет стандартное поведение и оставляет свободное место в файлах операционной системы после сжатия файла. Параметр TRANCATEONLY возвращает операционной системе все неиспользуемое место в файлах данных и сокращает файл до последнего экстента. Команда не пытается переместить записи в невыделенные страницы. При наличии параметра NOTRUNCATE значение «процент» игнорируется. Параметр NOTRUNCATE не позволяет сократить файл ниже минимального размера. В целом команда не сжимает файл больше минимума, необходимого для хранения данных в файле. Если параметр NOTRUNCATE используется вместе с параметром «процент», то последствия ограничиваются перемещением используемых страниц в начало файла. База данных не может быть меньше базы model.
Многие параметры конфигурации SQL Server настраиваются на уровне баз данных с помощью системной хранимой процедуры:
Sp_dboption [ ‘база данных’ ] [, ‘имя параметра’] [, ‘значение’]
Например, параметр:
OFFLINE – если его значение равно true, то база данных находится в отключенном состоянии и не может никем использоваться;
READ ONLY – если его значение равно true, пользователи могут только читать данные из базы, не модифицируя её;
SINGLE – задает одно или многопользовательский режим работы системы.
Просмотр информации о базе данных, её конфигурации:
Sp_helpdb [имя базы]
Просмотр сведений о файлах связанных с текущей базой данных:
Sp_helpfile [[@filename=]’имя ’]
Просмотр информации о группе файлов текущей базы данных:
Sp_helhfilegroup [[@filegroupname=]’имя’]
Команда создания таблиц соответствует стандарту языка SQL. Имена таблиц ограничиваются 128 символами. Они должны быть уникальными по отношению к владельцу. Таблица может содержать до 1024 столбцов. Имя столбца имеет длину до 128 символов и уникально в пределах таблицы (допускаются в имени служебные символы _, @, #). Типы полей также соответствуют стандарту. В качестве одного из видов полей может использоваться счетчик. Столбец счетчика (IDENTITY) – это автоматизированный столбец, генерирующий уникальные значения с некоторым приращением. Столбцы счетчика не могут содержать неопределенные значения, и должны относиться к числовым типам данных - int, smallint, tinyint, numeric(p,0),decimal(p,0). Если при объявлении столбца не указаны начальное значение и приращение, столбец действует как счетчик с начальным значением 1 и приращением 1. Недостаточный размер счетчика может вызвать проблемы. В примере тип tinyint допускает максимальное значение 255, при достижении максимума таблица будет отвергать все дальнейшие операции вставки.
При изменении созданной таблицы к числу возможных модификаций относится добавление столбцов, изменение типов данных в существующих столбцах, объявление первичных ключей и так далее. Нельзя удалить ограничение первичного ключа при наличии ограничения внешнего ключа, ссылающегося на него. Команда изменения выглядит следующим образом:
ALTER TABLE <имя таблицы> <параметры>
Добавить столбцы после определения таблицы можно с помощью команды:
ALTER TABLE <имя таблицы> ADD <имя поля> <тип поля> <ограничения>
Удаление столбцов осуществляется командой:
ALTER TABLE <имя таблицы> DROP COLUMN < имя поля>
Удаление из таблиц ограничений:
ALTER TABLE <имя таблицы> DROP CONSTRAINT <имя ограничения>
Команда удаления таблицы выглядит следующим образом:
DROP TABLE <имя таблицы>
Удаленная таблица навсегда пропадает из базы данных, то есть действие команды удаления невозможно отменить. Удаляются не только пользовательские, но и системные таблицы. Таблицу, на которую ссылаются какие-либо ограничения, удалить нельзя. Перед удалением таблицы эти ограничения необходимо отключить или удалить. Таблица может быть удалена только владельцем. Удаленную таблицу нельзя восстановить. Кроме рассмотренных пользовательских таблиц и системных таблиц, существуют временные таблицы. Имена системных таблиц начинается с префикса sys. Временные таблицы отличаются от обычных тем, что они не предназначены для постоянного хранения данных. Временные таблицы могут быть локальными и глобальными. Локальные временные таблицы доступны только одному пользователю во время сеанса, глобальные временные таблицы доступны всем пользователям сети во время сеанса. Имена временных таблиц начинаются на # (локальные временные таблицы) и на ##(глобальные временные таблицы). Имена временных таблиц ограничиваются 116 символами. Временные таблицы удалятся при отключении пользователя от базы данных. Временные таблицы используются так, как будто они входят в текущую базу данных, однако в действительности данные хранятся в TEMPDB. Временные таблицы удаляются как обычные таблицы во время сеанса работы пользователя.
В SQL Server существуют индексы двух типов: кластерные и некластерные индексы. Одна из особенностей кластерного индекса заключается в том, что один элемент индекса соответствует странице данных. Страница представляет собой физический блок для хранения данных объемом 8 Кбайт. После того как сервер определит страницу, он находит на странице нужную запись. Кластерный индекс реализует механизм индексно-последовательного доступа, причем индекс построен в виде бинарного дерева. После того как сервер определит страницу, он находит на странице нужную запись. Поскольку кластерный индекс позволяет выйти на первую запись результата и организовать последующий перебор таблицы в поисках остальных записей без возврата на уровень индекса, кластерные индексы отлично подходят для выборки интервалов данных. К этой категории относятся внешние ключи (объединения), списки имен и любые другие совокупности данных с последовательными ключами. Благодаря структуре кластерных индексов поиск в них обычно выполняется быстрее, чем при выборке произвольных записей для соответствующих некластерных индексов. Но это условное правило, зависящее от количества указателей, необходимых для индекса каждого типа, а также от их ширины. Иногда кластерный индекс работает значительно быстрее, а иногда вообще не дает выигрыша в скорости, хотя для большинства запросов кластерные индексы работают быстрее некластерных.
Некластерный индекс реализует механизм индексно-произвольного метода доступа. Его важнейшая особенность состоит в том, что один элемент индекса приходится на одну запись. Каждой записи в таблице назначается идентификатор. Поскольку в некластерном индексе каждый указатель соответствует одной записи, а также потому, что их указатели имеют больший размер, чем у кластерных индексов (поскольку содержат идентификатор записи, а не просто идентификатор страницы), некластерные индексы обычно занимают намного больше места. Для повышения быстродействия кластерный индекс следует создавать раньше некластерных. Создание кластерных индексов требует физической сортировки записей, а некластерные индексы содержат указатели на страницы и записи, которые приходится модифицировать при перемещении записей. (При создании кластерного индекса понадобится свободное место в базе данных в объеме отсортированной копии с индексом, или примерно 120-150% объема таблицы).
Рекомендуется помнить:
- выбор индекса определяется соображениями быстродействия, а не первичным ключом или другими обстоятельствами;
- для оптимизации физического размещения данных желательно иметь сгруппированный индекс в большинстве таблиц;
- с ростом количества индексов (больше 5) затраты становятся чрезмерными;
- в системах OLTP с оперативной обработкой транзакций создается минимальное количество индексов, что позволяет ускорить выполнение команд UPDATE, INSERT, DELETE.
Команды ведения данных при работе с таблицами соответствуют стандарту языка SQL.