Ключи и индексы в Firebird/InterBase.
Индексы в БД используются в двух целях:
1) обеспечение поддержания ссылочной целостности (индексы по первичным, внешним и альтернативным ключам);
2) повышение скорости поиска и сортировки данных.
Построение индексов для реализации первой цели происходит практически не зависимо от воли разработчика; СУБД просто создает индексы по определениям всех ключей. А вот реализация второй цели полностью зависит от разработчика БД и здесь имеется одна проблема, требующая компромиссных решений.
Дело в том, что повышение скорости поиска и сортировки с использованием индексов приводит, с другой стороны, к увеличению объема БД и снижению скорости изменения информации, хранящейся в ней, поскольку изменение содержимого индексируемых столбцов требует изменения индексов. Поэтому при создании индексов обычно исходят из следующих компромиссных соображений:
Индексы рекомендуется создавать по столбцам, для которых:
1) часто производится поиск в БД (столбцы часто перечисляются в предложении WHERE оператора SELECT);
2) часто строится объединение таблиц в операторе SELECT;
3) часто производится сортировка (предложение ORDER BY оператора SELECT).
Не рекомендуется создавать индексы по столбцам, которые:
1) редко используются для поиска, объединения и сортировки;
2) часто меняют значение, что приводит к необходимости часто изменять индекс;
3) содержат небольшое число вариантов значений (например, ‘T’ и ‘F’).
|
|
|
Замечание 1. Если часто используется поиск или сортировка по нескольким столбцам таблицы, то целесообразно создать общий индекс по этим столбцам (например, R1, R2, R3, R4). Этот же индекс будет использоваться и для поиска по любому подмножеству столбцов следующих подряд, начиная с ведущего (например, R1, R2 или R1, R2, R3; но не R2, R3 или R1, R3). Однако этот индекс не будет использоваться для поиска по столбцам, следующим в обратном порядке (R2, R1).
Замечание 2. При частом использовании в предложении WHERE поиска по нескольким столбцам, объединенным оператором OR, например:
WHERE R1=значение_1 OR R2= значение_2 OR R3= значение_3
лучше создать отдельные индексы по столбцам R1, R2 и R3, поскольку составной индекс по этим столбцам будет в данном случае использоваться только для поиска по столбцу R1.
Индексы создаются с помощью оператора вида:
CREATE [UNIQUE] [ASC|DESC] INDEX имя_индекса
ON имя_таблицы (столбец1[,столбец2...]);
где слово UNIQUE означает уникальный индекс;
ASC – индекс в порядке возрастания значений (по умолчанию);
DESC – индекс в порядке убывания значений.
Удаление индекса выполняется оператором вида:
DROP INDEX имя_индекса;
Нельзя удалить индекс, который используется в данный момент времени другими пользователями для выполнения запросов. Нельзя удалить индекс, созданный по определению ключей. В этом случае следует использовать оператор ALTER TABLE для удаления соответствующего ограничения.
|
|
|
Всякое изменение в таблице (удаление или добавление записи, изменение значений индексных полей) должно, по идее, влечь за собой перестроение индекса. Однако этот процесс может потребовать значительного времени, и поэтому в СУБД используются другие приемы, которые позволяют отражать изменения в индексе без его полной перестройки. Но эти приемы по мере накопления изменений приводят к изменению структуры индекса, увеличению его глубины (максимальное число обращений к индексу для поиска нужной записи). Если изменений в таблице очень много, то глубина индекса может стать неприемлемо большой и его использование для поиска и сортировки станет нецелесообразным. Поэтому иногда, по мере накопления изменений, необходимо полностью перестраивать индекс. Для этого используется пара операторов:
ALTER INDEX имя_индекса INACTIVE; - деактивирует индекс;
ALTER INDEX имя_индекса ACTIVE; - перестраивает индекс и активирует его.
Нельзя деактивировать используемый в данный момент времени индекс, а так же индекс, построенный по определению ключей.
|
|
|
Замечание 1. С теми же целями можно использовать пару операторов DROP INDEX и CREATE INDEX.
Замечание 2. Полное перестроение индексов выполняется и при восстановлении БД из резервной копии.
Замечание 3. Рекомендуется отключать индексы на время внесения массовых изменений в таблицы, например при добавлении множества новых записей.
Оптимизатор запросов при поступлении каждого нового запроса принимает решение о том, какие индексы следует использовать для их быстрейшего выполнения. Решение принимается на основе так называемого показателя полезности индекса. При частом внесении изменений в таблицы показатель полезности может перестать соответствовать действительному положению дел в БД и поэтому его следует периодически пересчитывать с помощью оператора:
SET STATISTICS INDEX имя_индекса;
Замечание. SET STATISTICS не перестраивает индекс, а лишь пересчитывает показатель его полезности, тем самым облегчая оптимизатору запросов правильный выбор индексов.
Оператор SELECT.
Оператор SELECT является самым важным и самым используемым оператором языка SQL. Утверждение «язык SQL – это оператор SELECT» недалеко от истины, поскольку SQL – это язык запросов, а оператор SELECT и является средством формулирования запросов.
|
|
|
Именно этот оператор демонстрирует отличия локальных и серверных БД. В локальной БД извлечение любой информации требует детального знания структуры БД и подробного описания алгоритма получения необходимой информации. В серверных же БД с помощью оператора SELECT формулируется запрос к SQL-серверу о том, какая информация должна быть получена. А каким образом эта информация будет получена, какие алгоритмы ее извлечения будут использованы и как они связаны со структурой БД – все это забота SQL-сервера, его внутренняя работа, скрытая от клиента.
Вообще, локальные базы данных ориентированы на работу с отдельными записями и имеют соответствующие средства навигации (перемещения) по наборам данных для перехода к конкретной записи. Серверные БД ориентированы на работу с множествами записей, удовлетворяющих некоторым требованиям выборки, и имеют средства для указания (выбора) этого множества.
Одним из следствий такого подхода является отсутствие в серверных БД возможности свободной навигации по наборам данных. Так, например, нет другой возможности перейти на произвольную запись НД, как только перебором всех предыдущих записей (имеется ввиду именно перемещение по НД, хотя выбрать нужную запись не составит труда, если указать ее уникальные идентифицирующие признаки). Более того, обычно возможно движение по НД только от начала к концу, но не наоборот. Для реализации возможности произвольного двунаправленного движения необходимо обеспечить буферизацию данных на локальном рабочем месте.
Итак, вся работа в серверных БД ориентирована на действия с некоторыми множествами записей, а средством указания этих множеств является оператор SELECT.
Простейшая форма оператора SELECT
SELECT {* | <значение 1>[,<значение 2>,...]}
FROM <таблица 1>[,<таблица 2>,...];
где <значение 1>, <значение 2>, ... – обычно имена столбцов;
<таблица 1>, <таблица 2>, ... – имена таблиц.
Приведенный оператор извлекает значения указанных столбцов из всех строк указанных таблиц. Если из таблицы необходимо извлечь значения всех столбцов, то вместо перечисления их имен можно использовать символ ‘*’.
Например, для извлечения всего содержимого таблицы лиц:
SELECT * FROM Lica;
что эквивалентно:
SELECT Nlic, Name, Tip, Podr FROM Lica;
Предложение WHERE
Обычно из таблиц извлекают не все строки, а только те из них, которые удовлетворяют определенному условию поиска. С этой целью в оператор SELECT добавляется предложение вида:
WHERE <условие поиска>
где в наиболее простом случае условие поиска имеет вид:
<условие поиска> = <имя столбца><оператор>константа
где <оператор>={<|>|<+|>+|!<|!>|=|<>|!=}, а константа может быть строковым или числовым значением.
Например: извлечь из таблицы наличия все строки, касающиеся предмета с условным номером 3:
SELECT * FROM Nalichie WHERE Predmet=3;
Для задания более сложных условий поиска можно использовать логические операторы: AND, OR, NOT.
Замечание. Всегда следует помнить о важном отличии использования логических операторов в SQL от использования их в других языках программирования: в SQL наивысший приоритет имеют операции сравнения. Поэтому сначала выполняются операторы сравнения, а потом уже логические. Такая особенность SQL избавляет от необходимости использования множества скобок.
Например: извлечь из таблицы наличия строку, касающуюся наличия у лица с условным номером 5 предмета с условным номером 12:
SELECT * FROM Nalichie WHERE Lico=5 AND Predmet=12;
Как видите, здесь нет необходимости в скобках.
Если необходимо проверить наличие значения (NOT NULL) для некоторого столбца, то используется конструкция вида:
<имя столбца> IS [NOT] NULL
Например, дать список всех подотчетных лиц (для них указывается подразделение):
SELECT * FROM Lica WHERE Podr IS NOT NULL;
Дата добавления: 2018-05-31; просмотров: 1467; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!