20 можно ли обеспечить уникальность значения для неключевого поля

Обновлено: 07.07.2024

Каждая запись в таблице, входящей в РСУБД, должна иметь первичный ключ (ПК) – набор атрибутов, уникально идентифицирующий её в таблице. Случай, когда таблица не имеет первичного ключа, имеет право на существование, однако в данной статье не рассматривается.

Естественный Ключ (ЕК) – набор атрибутов описываемой записью сущности, уникально её идентифицирующий (например, номер паспорта для человека) или
Суррогатный Ключ (СК) – автоматически сгенерированное поле, никак не связанное с информационным содержанием записи. Обычно в роли СК выступает автоинкрементное поле типа INTEGER.

СК должны использоваться, только если ЕК не существует. Если же ЕК существует, то идентификация записи внутри БД осуществляется по имеющемуся ЕК;
СК должны добавляться в любую таблицу, на которую существуют ссылки (REFERENCES) из других таблиц, и связи между ними должны организовываться только при помощи СК. Разумеется, поиск записи и представление её пользователю по прежнему производятся на основании ЕК.

Когда появляются СК?

Для понимания места и значения СК рассмотрим этап проектирования, на котором они вводятся в структуру БД, и методику их введения.

Для ясности рассмотрим БД из 2-х отношений – Города (City) и Люди (People) Предполагаем, что город характеризуется Hазванием (Name), все города имеют разные названия, человек характеризуется Фамилией (Family), номером паспорта (Passport) и городом проживания (City). Также полагаем, что каждый человек имеет уникальный номер паспорта. Hа этапе составления инфологической модели БД её структура одинакова и для ЕК и для СК.

CREATE TABLE City(
Name VARCHAR(30) NOT NULL PRIMARY KEY
);

CREATE TABLE People(
Passport CHAR(9) NOT NULL PRIMARY KEY,
Family VARCHAR(20) NOT NULL,
City VARCHAR(30) NOT NULL REFERENCES City(Name)
);

CREATE TABLE City(
/*
В разных диалектах языка SQL автоинкрементное поле будет выражено по-разному –
например, через IDENTITY, SEQUENCE или GENERATOR.
Здесь мы используем условное обозначение AUTOINCREMENT.
*/
Id INT NOT NULL AUTOINCREMENT PRIMARY KEY
Name VARCHAR(30) NOT NULL UNIQUE
);

CREATE TABLE People(
Id INT NOT NULL AUTOINCREMENT PRIMARY KEY,
Passport CHAR(9) NOT NULL UNIQUE,
Family VARCHAR(20) NOT NULL,
CityId INT NOT NULL REFERENCES City(Id)
);

Все условия, диктуемые предметной областью (уникальность имени города и номера паспорта) продолжают присутствовать в БД, только обеспечиваются не условием PRIMARY KEY, а условием UNIQUE;
Ключевого слова AUTOINCREMENT ни в одном из известных мне серверов нет. Это просто обозначение, что поле генерируется автоматически.

В таблицу добавляется поле INTEGER AUTOINCREMENT;
Оно объявляется PRIMARY KEY;
Старый PRIMARY KEY (ЕК) заменяется на UNIQUE CONSTRAINT ;
Если в таблице есть REFERENCES на другие таблицы, то поля, входящие в REFERENCES, заменяются на одно поле типа INTEGER, составляющее первичный ключ (как People.City заменена на People.CityId).

Зачем всё это надо?

Возникает резонный вопрос – а зачем? Действительно, вводить в таблицы какие-то поля, что-то заменять, зачем? Итак, что мы получаем, проделав эту "механическую" операцию.

Упрощение сопровождения

Это область, где СК демонстрируют наибольшие преимущества. Поскольку операции связи между таблицами отделены от логики "внутри таблиц" – и то и другое можно менять независимо и не затрагивая остального.

Hапример, выяснилось, что города имеют дублирующиеся названия. Решено ввести в City еще одно поле – Регион (Region) и сделать ПК (City, Region). В случае ЕК – изменяется таблица City, изменяется таблица People – добавляется поле Region (да, да, для всех записей, про размеры молчу), переписываются все запросы, в том числе на клиентах, в которых участвует City, в них добавляются строка AND XXX.Region = City.Region.

Да, чуть не забыл, большинство серверов сильно не любят ALTER TABLE на поля, входящие в PRIMARY KEY и FOREIGN KEY.

В случае СК – добавляется поле в City, изменяется UNIQUE CONSTRAINT. Всё.

Еще пример – в случае СК изменение списка полей в SELECT никогда не заставляет переписывать JOIN. В случае ЕК – добавилось поле, не входящее в ПК связанной таблицы – переписывайте.

Еще пример – поменялся тип данных поля, входящего в ЕК. И опять переделки кучи таблиц, заново оптимизация индексов.

В условиях меняющегося законодательства это достоинство СК само по себе достаточно для их использования.

Уменьшение размера БД

Предположим в нашем примере, что средняя длина названия города – 10 байт. Тогда на каждого человека в среднем будет приходиться 10 байт для хранения ссылки на город (реально несколько больше за счёт служебной информации на VARCHAR и гораздо больше за счёт индекса по People.City, который придётся построить, чтобы REFERENCES работала эффективно). В случае СК – 4 байта. Экономия – минимум 6 байт на человека, приблизительно 10 Мб для г. Hовосибирска. Очевидно, что в большинстве случаев уменьшение размера БД – не самоцель, но это, очевидно, приведет и к росту быстродействия.

Звучали аргументы, что БД может сама оптимизировать хранение ЕК, подставив вместо него в People некую хэш-функцию (фактически создав СК сама). Hо ни один из реально существующих коммерческих серверов БД так не делает, и есть основания полагать, что и не будет делать. Простейшим обоснованием такого мнения является то, что при подобной подстановке банальные операторы ADD CONSTRAINT … FOREIGN KEY или DROP CONSTRAINT … FOREIGN KEY будут приводить к нешуточной перетряске таблиц, с ощутимым изменением всей БД (надо будет физически добавить или удалить (с заменой на хэш-функцию)) все поля, входящие в CONSTRAINT.

Увеличение скорости выборки данных

База данных нормализована;
Записей в таблицах много (десятки тысяч и более);
Запросы преимущественно возвращают ограниченные наборы данных (максимум единицы процентов от размера таблицы).

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

Казалось бы, ЕК дает более простой запрос с меньшим количеством таблиц, который выполнится быстрее. Hо и тут не всё так просто: размеры таблиц для ЕК – больше (см. выше) и дисковая активность легко съест преимущество, полученное за счёт отсутствия JOIN`а. Ещё сильнее это скажется, если при выборке данных используется их фильтрование (а при сколько-либо существенном объеме таблиц оно используется обязательно). Дело в том, что поиск, как правило, осуществляется по информативным полям типа CHAR, DATETIME и т.п. Поэтому часто бывает быстрее найти в справочной таблице набор значений, ограничивающий возвращаемый запросом результат, а затем путем JOIN`а по быстрому INTEGER-индексу отобрать подходящие записи из большой таблицы. Например,

(CК) SELECT P.Family, C.Name
FROM People P INNER JOIN City C ON P.CityId = C.Id
WHERE C.Name = 'Иваново';

В случае ЕК – будет INDEX SCAN большой таблицы People по CHARACTER-индексу. В случае СК – INDEX SCAN меньшей CITY и JOIN по эффективному INTEGER индексу.

А вот если заменить = 'Иваново' на LIKE '%ваново', то речь пойдет о торможении ЕК относительно СК на порядок и более.

Аналогично, как только в случае с ЕК понадобится включить в запрос поле из City, не входящее в её первичный ключ – JOIN будет осуществлятся по медленному индексу и быстродействие упадет ощутимо ниже уровня СК. Выводы каждый может делать сам, но пусть он вспомнит, какой процент от общего числа его запросов составляют SELECT * FROM ЕдинственнаяТаблица. У меня – ничтожно малый.

Да, сторонники ЕК любят проводить в качестве достоинства "информативность таблиц", которая в случае ЕК растет. Ещё раз повторю, что максимальной информативностью обладает таблица, содержащая всю БД в виде flat-file. Любое "повышение информативности таблиц" есть увеличение степени дублирования в них информации, что не есть хорошо.

Увеличение скорости обновления данных

INSERT

Hа первый взгляд ЕК быстрее – не надо при INSERT генерировать лишнего поля и проверять его уникальность. В общем-то так оно и есть, хотя это замедление проявляется только при очень высокой интенсивности транзакций. Впрочем и это неочевидно, т. к. некоторые серверы оптимизируют вставку записей, если по ключевому полю построен монотонно возрастающий CLUSTERED индекс. В случае СК это элементарно, в случае ЕК – увы, обычно недостижимо. Кроме этого, INSERT в таблицу на стороне MANY (который происходит чаще) пойдет быстрее, т. к. REFERENCES будут проверяться по более быстрому индексу.

UPDATE

При обновлении поля, входящего в ЕК, придётся каскадно обновить и все связанные таблицы. Так, переименование Ленинграда в Санкт-Петербург потребует с нашем примере транзакции на несколько миллионов записей. Обновление любого атрибута в системе с СК приведет к обновлению только одной записи. Очевидно, что в случае распределенной системы, наличия архивов и т.п. ситуация только усугубится. Если обновляются поля не входящие в ЕК – быстродействие будет почти одинаковым.

Еще о CASCADE UPDATE

Далеко не все серверы БД поддерживают их на декларативном уровне. Аргументы "это у вас сервер кривой" в этом случае вряд ли корректны. Это вынуждает писать отдельную логику для обновления, что не всегда просто (приводился хороший пример – при отсутствии CASCADE UPDATE обновить поле, на которое есть ссылки, вообще невозможно – надо отключать REFERENCES или создавать копию записи, что не всегда допустимо (другие поля могут быть UNIQUE)).

DELETE

В случае СК будет выполняться быстрее, по той простой причине, что проверка REFERENCES пойдет по быстрому индексу.

А есть ли хорошие ЕК?

Hичто не вечно под Луной. Самый, казалось бы, надежный атрибут вдруг отменяется и перестаёт быть уникальным (далеко ходить не буду – рубль обычный и рубль деноминированный, примерам несть числа). Американцы ругаются на неуникальность номера социального страхования, Microsoft – на китайские серые сетевые платы с дублирующимися MAC-адресами, которые могут привести к дублированию GUID, врачи делают операции по смене пола, а биологи клонируют животных. В этих условиях (и учитывая закон неубывания энтропии) закладывать в систему тезис о неизменности ЕК – закладывать под себя мину. Их надо выделять в отдельный логический слой и по возможности изолировать от остальной информации. Так их изменение переживается куда легче. Да и вообще, однозначно ассоциировать сущность с каким-то из атрибутов этой сущности – ну, странно, что-ли. Hомер паспорта ещё не есть человек. СК же – это некая субстанция, именно и означающая сущность. Именно сущность, а не какой-то из её атрибутов.

Типичные аргументы сторонников ЕК

В системе с СК не осуществляется контроль правильности ввода информации

Это не так. Контроль не осуществлялся бы, если бы на поля, входящие в ЕК не было наложено ограничение уникальности. Очевидно, что если предметная область диктует какие-то ограничения на атрибуты ЕК, то они будут отражены в БД в любом случае.

В системе с ЕК меньше JOIN`ов, следовательно, запросы проще и разработка удобнее

CREATE VIEW PeopleEK AS
SELECT P.Family, P.Passport, C.Name
FROM People P INNER JOIN City C ON P.CityId = C.Id

И можно иметь все те же прелести. С более, правда, высоким быстродействием. При этом неплохо упомянуть, что в случае ЕК многим придется программировать каскадные операции, и, не дай Бог в распределённой среде, бороться с проблемами быстродействия. Hа фоне этого "короткие" запросы уже не кажутся столь привлекательными.

Введение ЕК нарушает третью нормальную форму

Вспомним определение: Таблица находится в третьей нормальной форме (3НФ), если она удовлетворяет определению 2НФ, и ни одно из её неключевых полей не зависит функционально от любого другого неключевого поля.

То есть, речи о ключевых полях там не идёт вообще. Поэтому добавление ещё одного ключа в таблицу ни в коей мере не может нарушить 3НФ. Вообще, для таблицы с несколькими возможными ключами имеет смысл говорить не о 3 НФ, а о Нормальной Форме Бойса-Кодда, которая специально введена для таких таблиц.

Итак, Таблица находится в нормальной форме Бойса-Кодда (НФБК), если и только если любая функциональная зависимость между его полями сводится к полной функциональной зависимости от возможного ключа.

Таким образом, таблица, имеющая СК, легко может быть нормализована хоть до 5НФ. Точнее будет сказать, что СК к нормализации не имеют никакого отношения. Более того, введение СК уменьшает избыточность данных в БД, что вообще хорошо согласуется с идеологией нормализации. В сущности, нормализация и есть уменьшение информативности отдельных таблиц по определенным правилам. Только СК устраняют аномалии не внутри таблицы, а на межтабличном уровне (типа устранения каскадных обновлений). Так сказать, система с СК – святее Папы Римского :-). В самом деле ситуация, когда при изменении одного из полей таблицы приходится изменять содержимое этого же поля в других записях ЭТОЙ ЖЕ таблицы, рассматривается как аномалия обновления. Но в системе с ЕК придется проделать то же самое В СВЯЗАННОЙ таблице при изменении ключевого атрибута на стороне 1 отношения 1:N. Очевидно, что эта ситуация с точки зрения физической реализации БД ничем не лучше. В системе с СК таких ситуаций не возникает.

Таблицы в системе с ЕК информативнее

Максимальной информативностью обладает таблица, содержащая всю БД в виде flat-file. Любое "повышение информативности таблиц" есть увеличение степени дублирования в них информации, что не обязательно есть хорошо. Да и вообще термин "Информативность таблицы" сомнителен. Видимо, более важна информативность БД, которая в обоих случаях одинакова.

Заключение

В общем-то, выводы очевидны – введение СК позволяет получить лучше управляемую, более компактную и быстродействующую БД. Разумеется, это не панацея. В некоторых случаях (например, таблица на которую нет REFERENCES и в которую осуществляется интенсивная вставка данных и т. п.) более верно использовать ЕК или не использовать ПК вообще (последнее категорически противопоказано для многих РСУБД и средств разработки клиентских приложений). Но речь шла именно о типовой методике, которую надо рекомендовать к применению в общем случае. Уникальные ситуации могут потребовать уникальных же решений (иногда и нормализацией приходится поступаться).

Данная статья излагает взгляд автора на проблему, регулярно обсуждающуюся в группах новостей, посвящённых разработке приложений с использованием РСУБД.

О сущности проблемы

Каждая запись в таблице, входящей в РСУБД, должна иметь первичный ключ (ПК) — набор атрибутов, уникально идентифицирующий её в таблице. Случай, когда таблица не имеет первичного ключа, имеет право на существование, однако в данной статье не рассматривается.

В качестве первичного ключа может использоваться —
Естественный Ключ (ЕК) — набор атрибутов описываемой записью сущности, уникально её идентифицирующий (например, номер паспорта для человека);
или
Суррогатный Ключ (СК) — автоматически сгенерированное поле, никак не связанное с информационным содержанием записи. Обычно в роли СК выступает автоинкрементное поле типа INTEGER.

Есть два мнения:

СК должны использоваться, только если ЕК не существует. Если же ЕК существует, то идентификация записи внутри БД осуществляется по имеющемуся ЕК;
СК должны добавляться в любую таблицу, на которую существуют ссылки (REFERENCES) из других таблиц, и связи между ними должны организовываться только при помощи СК. Разумеется, поиск записи и представление её пользователю по прежнему производятся на основании ЕК.

Естественно, можно представить себе и некое промежуточное мнение, но сейчас дискуссия ведётся в рамках двух вышеизложенных.

Когда появляются СК

Для ясности рассмотрим БД из 2-х отношений — Города (City) и Люди (People) Предполагаем, что город характеризуется Hазванием (Name), все города имеют разные названия, человек характеризуется Фамилией (Family), номером паспорта (Passport) и городом проживания (City). Также полагаем, что каждый человек имеет уникальный номер паспорта. Hа этапе составления инфологической модели БД её структура одинакова и для ЕК и для СК.

Для ЕК все готово. Для СК делаем еще один этап и преобразуем таблицы следующим образом:

Обращаю внимание, что:

Все условия, диктуемые предметной областью (уникальность имени города и номера паспорта) продолжают присутствовать в БД, только обеспечиваются не условием PRIMARY KEY, а условием UNIQUE;
Ключевого слова AUTOINCREMENT ни в одном из известных мне серверов нет. Это просто обозначение, что поле генерируется автоматически.

В общем случае алгоритм добавления СК выглядит следующим образом:

В таблицу добавляется поле INTEGER AUTOINCREMENT;
Оно объявляется PRIMARY KEY;
Старый PRIMARY KEY (ЕК) заменяется на UNIQUE CONSTRAINT ;
Если в таблице есть REFERENCES на другие таблицы, то поля, входящие в REFERENCES, заменяются на одно поле типа INTEGER, составляющее первичный ключ (как People.City заменена на People.CityId).

Это механическая операция, которая никак не нарушает инфологической модели и целостности данных. С точки зрения инфологической модели эти две базы данных эквивалентны.

Зачем всё это надо

Упрощение сопровождения

Hапример — выяснилось, что города имеют дублирующиеся названия. Решено ввести в City еще одно поле — Регион (Region) и сделать ПК (City, Region). В случае ЕК — изменяется таблица City, изменяется таблица People — добавляется поле Region (да, да, для всех записей, про размеры молчу), переписываются все запросы, в том числе на клиентах, в которых участвует City, в них добавляются строка AND XXX.Region = City.Region.

Да, чуть не забыл, большинство серверов сильно не любят ALTER TABLE на поля, входящие в PRIMARY KEY и FOREIGN KEY.

В случае СК — добавляется поле в City, изменяется UNIQUE CONSTRAINT. Всё.

Еще пример — в случае СК изменение списка полей в SELECT никогда не заставляет переписывать JOIN. В случае ЕК — добавилось поле, не входящее в ПК связанной таблицы — переписывайте.

Еще пример — поменялся тип данных поля, входящего в ЕК. И опять переделки кучи таблиц, заново оптимизация индексов…

В условиях меняющегося законодательства это достоинство СК само по себе достаточно для их использования.

Уменьшение размера БД

Предположим в нашем примере, что средняя длина названия города — 10 байт. Тогда на каждого человека в среднем будет приходиться 10 байт для хранения ссылки на город (реально несколько больше за счёт служебной информации на VARCHAR и гораздо больше за счёт индекса по People.City, который придётся построить, чтобы REFERENCES работала эффективно). В случае СК — 4 байта. Экономия — минимум 6 байт на человека, приблизительно 10 Мб для г. Hовосибирска. Очевидно, что в большинстве случаев уменьшение размера БД — не самоцель, но это, очевидно, приведет и к росту быстродействия.

Увеличение скорости выборки данных

Вопрос достаточно спорный, однако, исходя из предположений, что:

База данных нормализована;
Записей в таблицах много (десятки тысяч и более);
Запросы преимущественно возвращают ограниченные наборы данных (максимум единицы процентов от размера таблицы).

быстродействие системы на СК будет ощутимо выше. И вот почему:

ЕК могут потенциально дать более высокое быстродействие, когда:

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

Т.е., в нашем примере это запрос типа:

В случае СК этот запрос будет выглядеть как

Казалось бы, ЕК дает более простой запрос с меньшим количеством таблиц, который выполнится быстрее. Hо и тут не всё так просто: размеры таблиц для ЕК — больше (см. выше) и дисковая активность легко съест преимущество, полученное за счёт отсутствия JOIN`а. Ещё сильнее это скажется, если при выборке данных используется их фильтрование (а при сколько-либо существенном объеме таблиц оно используется обязательно). Дело в том, что поиск, как правило, осуществляется по информативным полям типа CHAR, DATETIME и т.п. Поэтому часто бывает быстрее найти в справочной таблице набор значений, ограничивающий возвращаемый запросом результат, а затем путем JOIN`а по быстрому INTEGER-индексу отобрать подходящие записи из большой таблицы. Например:

будет выполняться в разы медленнее, чем

В случае ЕК — будет INDEX SCAN большой таблицы People по CHARACTER-индексу. В случае СК — INDEX SCAN меньшей CITY и JOIN по эффективному INTEGER индексу.

А вот если заменить = ‘Иваново’ на LIKE ‘%ваново’, то речь пойдет о торможении ЕК относительно СК на порядок и более.

Аналогично, как только в случае с ЕК понадобится включить в запрос поле из City, не входящее в её первичный ключ — JOIN будет осуществлятся по медленному индексу и быстродействие упадет ощутимо ниже уровня СК. Выводы каждый может делать сам, но пусть он вспомнит, какой процент от общего числа его запросов составляют SELECT * FROM ЕдинственнаяТаблица. У меня — ничтожно малый.

Увеличение скорости обновления данных

Hа первый взгляд ЕК быстрее — не надо при INSERT генерировать лишнего поля и проверять его уникальность. В общем-то так оно и есть, хотя это замедление проявляется только при очень высокой интенсивности транзакций. Впрочем и это неочевидно, т.к. некоторые серверы оптимизируют вставку записей, если по ключевому полю построен монотонно возрастающий CLUSTERED индекс. В случае СК это элементарно, в случае ЕК — увы, обычно недостижимо. Кроме этого, INSERT в таблицу на стороне MANY (который происходит чаще) пойдет быстрее, т.к. REFERENCES будут проверяться по более быстрому индексу.

Еще о CASCADE UPDATE

В случае СК будет выполняться быстрее, по той простой причине, что проверка REFERENCES пойдет по быстрому индексу.

А есть ли хорошие ЕК?

Hичто не вечно под Луной. Самый, казалось бы, надежный атрибут вдруг отменяется и перестаёт быть уникальным (далеко ходить не буду — рубль обычный и рубль деноминированный, примерам несть числа). Американцы ругаются на неуникальность номера социального страхования, Microsoft — на китайские серые сетевые платы с дублирующимися MAC-адресами, которые могут привести к дублированию GUID, врачи делают операции по смене пола, а биологи клонируют животных. В этих условиях (и учитывая закон неубывания энтропии) закладывать в систему тезис о неизменности ЕК — закладывать под себя мину. Их надо выделять в отдельный логический слой и по возможности изолировать от остальной информации. Так их изменение переживается куда легче. Да и вообще: однозначно ассоциировать сущность с каким-то из атрибутов этой сущности — ну, странно, что-ли. Hомер паспорта ещё не есть человек. СК же — это некая субстанция, именно и означающая сущность. Именно сущность, а не какой-то из её атрибутов.

Типичные аргументы сторонников ЕК

В системе с СК не осуществляется контроль правильности ввода информации

В системе с ЕК меньше JOIN`ов, следовательно, запросы проще и разработка удобнее

Да, меньше. Hо, в системе с СК тривиально пишется:

Введение СК нарушает третью нормальную форму

Итак: Таблица находится в нормальной форме Бойса-Кодда (НФБК), если и только если любая функциональная зависимость между его полями сводится к полной функциональной зависимости от возможного ключа.

Таким образом, таблица, имеющая СК, легко может быть нормализована хоть до 5НФ. Точнее будет сказать, что СК к нормализации не имеют никакого отношения. Более того, введение СК уменьшает избыточность данных в БД, что вообще хорошо согласуется с идеологией нормализации. В сущности, нормализация и есть уменьшение информативности отдельных таблиц по определенным правилам. Только СК устраняют аномалии не внутри таблицы, а на межтабличном уровне (типа устранения каскадных обновлений). Так сказать, система с СК — святее Папы Римского :-). В самом деле – ситуация, когда при изменении одного из полей таблицы приходится изменять содержимое этого же поля в других записях ЭТОЙ ЖЕ таблицы, рассматривается как аномалия обновления. Но в системе с ЕК придется проделать то же самое В СВЯЗАННОЙ таблице при изменении ключевого атрибута на стороне 1 отношения 1:N. Очевидно, что эта ситуация с точки зрения физической реализации БД ничем не лучше. В системе с СК таких ситуаций не возникает.
Таблицы в системе с ЕК информативнее

Заключение

В общем-то, выводы очевидны – введение СК позволяет получить лучше управляемую, более компактную и быстродействующую БД. Разумеется, это не панацея. В некоторых случаях (например, таблица на которую нет REFERENCES и в которую осуществляется интенсивная вставка данных и т.п.) более верно использовать ЕК или не использовать ПК вообще (последнее категорически противопоказано для многих РСУБД и средств разработки клиентских приложений). Но речь шла именно о типовой методике, которую надо рекомендовать к применению в общем случае. Уникальные ситуации могут потребовать уникальных же решений (иногда и нормализацией приходится поступаться).

Ограничения являются средством, с помощью которого БД может принуждать к выполнению бизнес-правил и гарантировать что данные соответсвуют модели сущность-связь определённой системным анализом определяя структуры данных приложения. Например, бизнес-аналитик в компании решил что каждый покупатель и каждый заказ должен определяться уникальным числом, что нельзя создать заказ до создания покупателя и каждый заказ должен иметь валидную дату и сумму большую нуля. Это может быть достигнуто путём создания первичных ключей для столбца CUSTOMER_ID таблицы CUSTOMERS и столбца ORDER_ID таблицы ORDERS, создания внешнего ключа для таблица ORDERS ссылающегося на таблицу CUSTOMERS, создания ограничения “not null” для столбца ORDER_DATE (тип данных DATE сам проверит удовлятворяет ли введённое значение типа дата или нет) и ограничения проверки для столбца ORDER_AMOUNT таблицы ORDERS.

Если какой либо DML запрос к таблице с ограничениями нарушит ограничение, тогда весь запрос будет отменем автоматически. Надо помнить что DML запрос может обновлять много строк и частично обновление было успешным до нарушения ограничения для конкретной строки. Если запрос это часть транзакции состоящей из нескольких запросов, то результат выполнения запросов которые уже выполнились успешно остаётся но не подтверждённым.

A constraint violation will force an automatic rollback of the entire statement that hit the problem, not just the single action within the statement, and not the entire transaction.

Типы ограничений

Ограничения поддерживаемые Orace это

UNIQUE
NOT NULL
PRIMARY KEY
FOREIGN KEY
CHECK

У ограничений есть имя. Хорошей практикой считается именовать их согласно конвенции имен, но если явно не указывать имя, Oracle будет использовать сгенерированное имя.

Ограничение уникальности

Ограничение уникальности назначает столбец (или группу столбцов) для которых значение должно быть разным для каждой строки в таблице. Если ограничение настроено для одного столбца, это называется столбец-ключ (key column). Если ограничение состоит из нескольких столбцов (называется составной ключ (composite key)), столбцы не должны быть одинакового типы и располагаться последовательно в таблице.

Странностью уникального ограничения можно считать возможность добавления значения NULL в столбцы ключа; более того можно иметь неограниченное количество строк со значение NULL в столбцах ключа. Таким образом запрос строк по ключу ограничения гарантирует одну строку для значения – пока не используется NULL, в таком случае все строки со значением NULL будут результатом запроса.

It is possible to insert many rows with NULLs in a column with a unique constraint. This is not possible for a column with a primary key constraint.

Ограничения уникальности работают с помощью индексов. Когда мы определяем ограничение, Oracle проверит существует ли индекс для столбцов ограничения, и если индекс не сущетвует, он будет создан. Затем когда происходи попытка вставки новой строки, Oracle просматривает индекс на существование значения ключа; если значение сущесвтует – строка будет отклонена. Индекс ограничения – B* Tree индекс, и он не хранит значения NULL, вот почему ограничение не работает для NULL (т.е. можно добавлять много строк со значением NULL). Как мы обсуждали выше, целью индексов является не только обеспечение работоспособности механизма ограничений, но и повышение производительности запросов с секцией WHERE и объединением таблиц. Если в запросе используется WHERE key_column IS NULL – Oracle не может использовать индекс (потому что индекс не хранит значения NULL) и всегда будет использовано полное чтение таблицы.

Ограничения NOT-NULL

Ограничения NOT-NULL принуждают указать данные для столбцов ключа. Эти ограничения накладываются на столбец и иногда их называют обязательными для заполнения столбцами (mandatory columns). Если несколько столбцов должны иметь значения, то вы не можете создать одно ограничение для группы – необходимо создавать ограничения для каждого столбца.

Любая попытка записи строки без указания значения для обязательного столбца вернёт ошибку. Возможно обойти эту проверку указав директиву DEFAULT при создании таблицы.

Первичный ключ

Первичный ключ является средством для поиска любой одной строки в таблице. Реляционная парадигма утверждает что каждая таблица должна иметь первичный ключ: столбец (или группу столбцов) которые можно использовать для выделения каждой строки. Oracle позволяет создавать таблицы (как и некоторые другие РСУБД) без первичного ключа.

Реализацией первичного ключа является комбинация ограничений целостности и обязательности. Значение в столбцах ключе должно быть уникальным и не NULL. Так же как и для ограничения уникальности индекс должен существовать для столбцов ключа, иначе он будет создан. У таблицы может быть только один первичный ключ. Но у таблицы может быть сколько угодно ограничений уникальности и not-null ограничений. Если у вас есть несколько столбцов которые должны быть уникальны и обязательны вы можете использовать часть из них для создания первичного ключа, а для остальных создать ограничения уникальности и обязательности. Например таблица сотрудников: все сотрудники должны иметь уникальный номер (первичный ключ) и также значения email, номер соц. страха должны быть уникальны и обязательны для заполнения.

Unique and primary key constraints need an index. If one does not exist, one will be created automatically.

Внешние ключи

Внешний ключ определяет отношение родитель-наследник. Это ограничение назначает столбец (или столбцы) в дочерней таблице которые должны соответствовать столбцу(ам) первичного ключа родительской таблицы. Соответствующие столбцы необязательно должны иметь одинаковые названия, но они должны быть одного типа данных. Внешний ключ определяет отношения один-ко-многим в третьей нормальной форме.

Если у родительской таблицы есть ограничение уникальности вместе (или вместо) первичного ключа, эти столбцы тоже могут использоваться как основа для внешнего ключа, даже если они не обязательны (nullable).

A foreign key constraint is defined on the child table, but a unique or primary key constraint must already exist on the parent table.

Как и ограничение уникальности внешний ключ допускает значение NULL в столбцах ключа. Вы можете вставить строки в дочернюю таблицу со значением NULL в столбцах внешнего ключа – даже если в родительской таблице нет строки со значением NULL. Это создаст строки призраки (orphaned rows) и может вызвать определённые недоразмения. Как правило все столбцы ограничения уникальности и все столбцы внешнего ключа лучше определять вместе с ограничениями not-null: так же это обычно требование бизнеса.

При попытки вставить строку в дочернюю таблицу со значением ключа которого нет в родительской таблице – произойдёт ошибка выполнения запроса. Также попытка удаления строки в родительской таблице вернёт ошибку если существуют строки в дочерней которые ссылаются на эту строке. Существует два способа обойти это ограничение. Во-первых, ограничения могут быть созданы с директивой ON DELETE CASCADE. Это значит что если мы удаляется строка из родительской таблицы – Oracle автоматически найдёт все строки ссылающиеся на эту строку в дочерней таблице и удалит их тоже. Менее кардинальный способ – использование директивы ON DELETE SET NULL. В этом случае если строка родительской таблицы удаляется – Oracle найдёт все соответствующие строки в дочерней таблице и установит значение ключа в NULL. Это значит что строки в дочерней таблице станут строками-призраками – но они будут существовать в таблице. Если столбцы внешнего ключа имеют ограничение not-null – то удаление из родительской таблицы вернёт ошибку. Нельзя ни удалить ни выполнить операцию TRUNCATE для родительской таблицы, даже если нет ни одной строки в дочерней таблице. Даже если использованы ON DELETE SET NULL или ON DELETE CASCADE.

Отдельным видом внешнего ключа является внешний ключ ссылающийся сам на себя (self-referencing). Такой ключ определяет отношение где родительский и дочернии строки находятся в одной таблице. Хорошим примером является таблица сотрудников, в которой есть столбец MANAGER_ID. И менеджер и сотрудник являются сотрудниками. Если первичный ключ EMPLOYEE_ID то для внешнего ключа используем EMPLOYEE_ID как родительский столбец и MANAGER_ID как дочерний. Если у сотрудника нет менеджера, то такая строка указывает сама на себя.

Ограничения проверки значения (Check constraints)

Ограничения проверки значения используются для проверки данных на удовлетворение простым правилам, к примеру введённое значение должно попадать в период значений. Правилом должно быть выражение результатом проверки которого будет либо ПРАВДА либо ЛОЖЬ. Правила могут использовать литералы, или другие столбцы той же строки и они могут использовать некоторые функции. Сколько угодно правил можно применить к одному ограничение проверки значения, но нельзя использовать подзапросы или такие функции как SYSDATE.

The not-null constraint is in fact implemented as a preconfigured check constraint.

Определение ограничений

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

Расмотрим два запроса создания таблицы

1 create table dept(

2 deptno number(2,0) constraint dept_deptno_pk primary key

3 constraint dept_deptno_ck check (deptno between 10 and 90),

4 dname varchar2(20) constraint dept_dname_nn not null);

5 create table emp(

6 empno number(4,0) constraint emp_empno_pk primary key,

7 ename varchar2(20) constraint emp_ename_nn not null,

8 mgr number (4,0) constraint emp_mgr_fk references emp (empno),

10 hiredate date,

11 deptno number(2,0) constraint emp_deptno_fk references dept(deptno)

12 on delete set null,

13 email varchar2(30) constraint emp_email_uk unique,

14 constraint emp_hiredate_ck check (hiredate >= dob + 365*16),

15 constraint emp_email_ck

16 check ((instr(email,’@’) > 0) and (instr(email,’.’) > 0)));

Первая таблица создаётся с именем DEPT и предназначена для хранения одной строки для каждого департамента.
DEPTNO – числовое поле, 2 цифры, нет точек после запятой. Первичный ключ таблицы – имя ограничения DEPT_DEPTNO_PK.
Второе ограничение столбца DEPTNO это проверка значения на период от 10 до 90. Имя этого ограничения DEPT_DEPTNO_CK.
Столбец DNAME – это строка нефиксированной длины, обязательна для заполнения – ограничение DEPT_DNAME_NN.
Вторая табилца это EMP, предназаченная для хранения строки для каждого сотрудника.
Столбец EMPNO типа данных NUMERIC, 4 цифры без дробной части первичный ключ EMP_EMPNO_PK. ENAME
Столбец ENAME строка произвольной длины до 20 символов с not null ограничением EMP_ENAME_NN
Столбец MGR определён так же как и EMPNO плюс является внешним ключом таблицы к самой себе по полям MGR-EMP_NO. Нет ограничения на not-null поэтому может быть NULL
DOB – дата рождения сотрудника, тип данных дата нет ограничений
HIREDATE – дата приёма на работу, нет ограничений
DEPTNO департамент, определение столбца такое же как столбец DEPTNO таблицы DEPT. Внешний ключ к таблице департаментов: нельзя вставить строку со значением которого нет в таблице DEPT. Но значение может быть
Внешний ключ EMP_DEPTO_FK определён с директивой ON DELETE SET NULL, т.е. при удалении строки из таблицы DEPT значение столбца DEPTNO во всех относящиеся к удаляемой строке будет обновлено в значение NULL.
EMAIL – строка переменной длины и имеет ограничение уникальности (но может быть NULL)
Определение дополнительного ограничения EMP_HIREDATE_CK. Дата приёма на работу должна быть не раньше чем 16 лет после даты рождения. Данное ограничение невозможно создать в строке определения столбца HIREDATE, так как ограничение ссылается на другой столбец
Определение дополнительного ограничения проверки значения для строки EMAIL. Вызываются функции которые проверяют наличие @ и “.” в значении и если хотя бы одна функция вернёт ЛОЖЬ то строка будет отклонена.

Эти примеры показывают некоторые возможности определения ограничения при создании таблицы. Остальные возможности это

Управление созданием индексов для внешних ключей и ограничений уникальности

Определение времени проверки ограничения: во время вставки (значение по умолчанию) или позже, в момент подтверждения транзакции

Включено ли ограничение (по умолчанию) или отключено

Возможно создать таблицу без ограничений а затем добавить ограничения командой ALTER TABLE. Результат будет одинаковый но определение таблицы будет разбито на несколько запросов вместо одного.

Состояния ограничений

В любой момент времени, каждое ограничение включено или выключена и проверяется или не проверяется. Любая комбинация этих двух состояний возможна

ENABLEVALIDATE – Невозможно добавить строку которая нарушает ограничение и все строки таблицы удовлетворяют ограничениям
DISABLENOVALIDATE – Любые данные возможно ввести и в таблицу уже могут быть данные нарушающие ограничения
ENABLENOVALIDATE – В таблице могут существовать данные не удовлетворяющие ограничениям, но все добавляемые данные должны удовлетворять ограничениям
DISABLEVALIDATE – невозможная ситуация: все данные в таблице удовлетворяют ограничениям, но новые данные должны неудовлетворять. Т.е. нельзя вставить данные в таблицу.

Идеальной ситуацией (и состоянием по умолчанию при определении ограничения) является ENABLE VALIDATE. Это гарантирует что данные корректны и некорректные данные не могут быть добавлены.

Статус DISABLE NOVALIDATE полезен когда загружается много данных в таблицу одним запросом. Данные могут содержать плохие значения, но вместо ошибки выполнения запроса этот статус позволит загрузить данные. Сразу после загрузки данных, переведите состояние в ENABLE NOVALIDATE чтобы предотвратить вставку других неправильных данных пока вы будете проверять уже добавленные данные на удовлетворение ограничениям. И затем опять установите правильное состояние ENABLE VALIDATE.

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

alter table sales_archive modify constraint sa_nn1 disable novalidate;

insert into sales_archive select * from sales_current;

alter table sales_archive modify constraint sa_nn1 enable novalidate;

update sales_archive set channel=’NOT KNOWN’ where channel is null;

alter table sales_archive modify constraint sa_nn1 enable validate;

Проверка ограничений

set constraint sa_nn1 deferred;

insert into sales_archive select * from sales_current;

update sales_archive set channel=’NOT KNOWN’ where channel is null;

set constraint sa_nn1 immediate;

Для поддержки отложенного (DEFERRED) ограничения оно должно быть создано используя синтаксис

alter table sales_archive add constraint sa_nn1

check (channel is not null) deferrable initially immediate;

То есть невозможно сделать ограничение отложенным если директива deferrable не была указана при создании. В нашем случае ограничение SA_NN1 по умолчанию проверяется на момент вставки строки (или изменения), но проверку можно отложить до подтверждения транзакции. Часто отложенные ограничения используются для внешних ключей. Если операции обновления и изменения затрагивают и дочернюю и родительскую таблицу, и если внешний ключ не отложенный то операция может выполниться с ошибкой в зависимости от порядка обработки строк.

Изменение состояния ограничения затрагивает все сессии. Изменение времени проверки ограничения (IMMEDIATE или DEFERRED) затрагивает текущую сессию, но начальное состояние применяется ко всем сессиям.

By default, constraints are enabled and validated, and they are not deferrable.

Создание базы данных всегда начинается с разработки структуры ее таблиц. Структура должна быть такой, чтобы при работе с базой требовалось вводить в нее как можно меньше данных. Если ввод каких-то данных приходится повторять неоднократно, базу делают из нескольких связанных таблиц. Структуру каждой таблицы разрабатывают отдельно.

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

Уникальное поле — это поле, значения в котором не могут повторяться.

Если из таблицы Прокат известно, что клиент Новиков просрочил возврат взятого диска, то он должен уплатить штраф. Но в таблице Клиенты фирмы может быть несколько разных Новиковых, и компьютер не разберется, кто же из них должен платить штраф. Это означает, что поле Фамилия не является уникальным и потому его нельзя использовать для связи между таблицами. Поле номера телефона — более удачный кандидат на звание уникального поля, но, как вы понимаете, и одним телефоном могут пользоваться несколько разных людей.

Если ни одно поле таблицы не приемлемо в качестве уникального, его можно создать искусственно. В нашем примере в таблице Клиенты фирмы создано поле Шифр, которое образовано первыми тремя буквами фамилии и последними двумя цифрами номера телефона. Его и использовали для связи между таблицами.

Скорее всего, поле Шифр окажется уникальным, и проблем со связями между таблицами не возникнет, но было бы неплохо, если бы компьютер мог просигнализировать в том случае, если вдруг записи в этом поле повторятся. Для этого существует понятие ключевое поле. При создании структуры таблиц одно поле (или одну комбинацию полей) можно назначить ключевым. С ключевыми полями компьютер работает особо. Он проверяет их уникальность и быстрее выполняет сортировку по таким полям. Ключевое поле — очевидный кандидат для создания связей. Иногда ключевое поле называют первичным ключом.

Если при создании таблицы автор не задал ключевое поле, система управления базой данных вежливо напомнит о том, что поле первичного ключа таблице не помешает

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

Структура связей между таблицами называется схемой данных

Лекция 3
СУБД Access

Системы управления базами данных (СУБД) — это программные средства, с помощью которых можно создавать базы данных, наполнять их и работать с ними. В мире существует немало различных систем управления базами данных. Многие из них на самом деле являются не законченными продуктами, а специализированными языками программирования, с помощью которых каждый, освоивший данный язык, может сам создавать такие структуры, какие ему удобны, и вводить в них необходимые элементы управления. К подобным языкам относятся Clipper, Paradox, FoxPro и другие.

Положение изменилось с появлением в составе пакета Microsoft Office системы управления базами данных Access. Ранние версии этой программы имели номера Access 2.0 и Access 95.

С помощью Access обычные пользователи получили удобное средство для создания и эксплуатации достаточно мощных баз данных без необходимости что-либо программировать. В то же время работа с Access не исключает возможности программирования. При желании систему можно развивать и настраивать собственными силами. Для этого надо владеть основами программирования на языке Visual Basic.

Еще одним дополнительным достоинством Access является интегрированность этой программы с Excel, Word и другими программами пакета Office. Данные, созданные в разных приложениях, входящих в этот пакет, легко импортируются и экспортируются из одного приложения в другое.

Объекты Access

Исходное окно Access отличается простотой и лаконичностью. Шесть вкладок этого окна представляют шесть видов объектов, с которыми работает программа.

Учитывая, что индексация настолько важна, что ваш набор данных увеличивается по размеру, может кто-нибудь объяснить, как индексирование работает на уровне агностики базы данных?

Информацию о запросах для индексации поля можно найти Как индексировать столбец базы данных.

Почему это необходимо?

Когда данные хранятся на дисковых накопителях, они хранятся в виде блоков данных. Доступ к этим блокам осуществляется целиком, что делает их доступным для атомарного доступа к диску. Блоки диска структурированы так же, как и связанные списки; оба содержат раздел для данных, указатель на расположение следующего node (или блока), и оба они не должны храниться смежно.

В связи с тем, что количество записей можно сортировать только в одном поле, мы можем заявить, что поиск в поле, которое не сортируется, требует линейного поиска, для которого требуется N/2 доступ к блокам (в среднем), где N - количество блоков, в которых находится таблица. Если это поле является неключевым полем (т.е. Не содержит уникальных записей), тогда все табличное пространство необходимо искать в N доступе к блоку.

В то время как в отсортированном поле может использоваться двоичный поиск, у него есть log2 N доступ к блокам. Кроме того, поскольку данные сортируются с учетом неключевого поля, остальная часть таблицы не нуждается в поиске повторяющихся значений, как только будет найдено более высокое значение. Таким образом, увеличение производительности является существенным.

Что такое индексирование?

Индексирование - это способ сортировки нескольких записей в нескольких полях. Создание индекса в поле в таблице создает другую структуру данных, которая содержит значение поля, и указатель на запись, к которой она относится. Затем эта структура индекса сортируется, что позволяет выполнять двоичные поиски.

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

Как это работает?

Во-первых, давайте нарисуем примерную схему таблицы базы данных;

Примечание: char использовался вместо varchar, чтобы обеспечить точный размер на диске. Эта тестовая база данных содержит пять миллионов строк и не указана. Теперь будет проанализирована производительность нескольких запросов. Это запрос с использованием идентификатора (поля отсортированного ключа) и одного с использованием firstName (несимвольное несортированное поле).

Пример 1 - отсортированные или несортированные поля

Учитывая нашу примерную базу данных r = 5,000,000 записей фиксированного размера, дающих длину записи R = 204 байтов, и они хранятся в таблице с использованием механизма MyISAM, который использует размер блока по умолчанию B = 1,024 байт. Коэффициентом блокировки таблицы будет bfr = (B/R) = 1024/204 = 5 записей на блок диска. Общее количество блоков, необходимых для хранения таблицы, составляет N = (r/bfr) = 5000000/5 = 1,000,000 .

Для линейного поиска в поле id потребуется среднее число N/2 = 500,000 для доступа к блоку, чтобы найти значение, учитывая, что поле id является ключевым полем. Но так как поле id также сортируется, может быть проведен двоичный поиск, требующий среднего из log2 1000000 = 19.93 = 20 доступа к блокам. Мгновенно мы видим, что это радикальное улучшение.

Теперь поле firstName не сортируется и не поле ключа, поэтому бинарный поиск невозможен, и значения не уникальны, и, следовательно, таблица потребует поиска до конца для точного доступа к блоку N = 1,000,000 . Именно в этой ситуации индексирование направлено на исправление.

Учитывая, что индексная запись содержит только проиндексированное поле и указатель на исходную запись, разумно, что она будет меньше, чем многопольная запись, на которую указывает. Таким образом, для самого индекса требуется меньше блоков диска, чем исходная таблица, поэтому требуется меньше доступа к блокам для итерации. Схема для индекса в поле firstName приведена ниже;

Примечание. Указатели в MySQL имеют длину 2, 3, 4 или 5 байтов в зависимости от размера таблицы.

Пример 2 - индексирование

Учитывая нашу примерную базу данных r = 5,000,000 записей с длиной записи индекса R = 54 байт и использованием размера блока по умолчанию B = 1,024 байт. Блокирующим фактором индекса будет bfr = (B/R) = 1024/54 = 18 записей на блок диска. Общее количество блоков, необходимых для хранения индекса, составляет N = (r/bfr) = 5000000/18 = 277,778 .

Теперь поиск с использованием поля firstName может использовать индекс для повышения производительности. Это позволяет выполнять двоичный поиск индекса со средним значением доступа к блоку log2 277778 = 18.08 = 19 . Чтобы найти адрес фактической записи, для которой требуется дополнительный доступ к блоку для чтения, приведение общего количества к 19 + 1 = 20 блочным доступам, далеко от 1000 000 запросов блоков, необходимых для поиска совпадения firstName в таблице без индексирования.

Когда он должен использоваться?

Учитывая, что для создания индекса требуется дополнительное дисковое пространство (277 778 дополнительных блоков из приведенного выше примера, увеличение на 28%), и что слишком много индексов могут вызывать проблемы, связанные с ограничениями размера файловой системы, следует тщательно подумать выберите правильные поля для индексации.

Так как индексы используются только для ускорения поиска подходящего поля в записях, то понятно, что поля индексирования, используемые только для вывода, будут просто потерей дискового пространства и времени обработки при выполнении операции вставки или удаления, и поэтому этого следует избегать. Также, учитывая характер бинарного поиска, важна мощность или уникальность данных. Индексирование в поле с мощностью 2 разделило бы данные пополам, тогда как мощность 1000 вернула бы приблизительно 1000 записей. При такой низкой мощности эффективность сводится к линейной сортировке, и оптимизатор запросов избежит использования индекса, если мощность составляет менее 30% от номера записи, что делает индекс ненужным.

бинарный поиск может быть сделан, когда данные уникальны, я прав? хотя вы упомянули, что минимальное количество элементов является важным, алгоритм не будет простым двоичным поиском, как это приближение (~ log2 n) повлияет на время процесса?

@AbhishekShivkumar: Отличный вопрос! Я думаю, что в индексной таблице будет столько строк, сколько в таблице данных. И так как это поле будет иметь только 2 значения (логическое с true / false) и скажем, что вы хотите запись со значением true, то вы можете только вдвое сократить результирующий набор при первом проходе, во втором проходе все ваши записи имеют значение true, так что есть нет никаких оснований для дифференциации, теперь вы должны искать в таблице данных линейно - следовательно, он сказал, что при определении индексированного столбца следует учитывать количество элементов. В этом случае не стоит индексировать такой столбец. Надеюсь я прав :)

не должно быть числа обращений к блоку в среднем случае (N+1)/2 . Если мы суммируем количество обращений к блокам для всех возможных случаев и делим его на количество случаев, то мы имеем N*(N+1)/(2*n) которое получается (N+1)/2 ,

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

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

Также было бы замечательно, если бы было объяснение того, как индексация работает на нескольких полях, ответ объясняет только индексацию одного поля, firstName

Во втором примере, почему мы должны хранить firstName ? Разве указатель не достаточно? Нам просто нужно просто изменить функцию сравнения на (*pointer).firstName .

Просто чтобы быть чистым, когда мы говорим, что Creating an index on a field in a table creates another data structure which holds the field value, and pointer to the record it relates to. это для некластеризованного индекса. Для кластерного индекса, который может быть только один для таблицы, это не так, поскольку исходные данные хранятся в отсортированном порядке, используя их.

Как мы можем запустить двоичный поиск на отсортированных данных, если они не хранятся в смежных блоках?

Я попытался выполнить индексацию по неключевому полю, все обстоит хуже для запроса select. Как я понимаю это?

Индекс может быть хеш-индексом, необязательно должен быть отсортирован. То, что вы упомянули, является просто своего рода указателем.

Спасибо за блестящее, подробное объяснение. Если бы я мог изложить собственное объяснение (возможно, неточное и неправильное): индексирование по firstName похоже на добавление списка отсортированных идентификаторов к firstName, которые соответствуют записям в этом поле, что включает бинарный поиск?

Может ли кто-нибудь объяснить, почему поиск по полю, которое не отсортировано, но содержит уникальные записи, требует в среднем N/2 обращений к блоку, а поиск по полю, не являющемуся ключевым (не содержит уникальных записей), требует N обращений к блокам? Разве они оба не требуют N, так как это линейный поиск?

@mouscous Предположим, у вас есть список уникальных чисел в случайном порядке. Если вы хотите знать, есть ли значение там, вы смотрите на 1-е, затем на 2-е, 3-е и т. Д., Пока не найдете соответствие. После обнаружения нет необходимости продолжать поиск, значение является уникальным и присутствует только один раз. Иногда у вас есть совпадение на ранней стадии, иногда требуется некоторое время, чтобы найти его, но в среднем вы пройдете половину списка. Если значения не являются уникальными, вы можете найти их на первом месте, но это может быть еще один случай в дальнейшем! Чтобы найти их все, вам нужно каждый раз просматривать весь список. Таким образом, Н.

В первый раз, когда я прочитал это, это было очень полезно для меня. Спасибо.

С тех пор я получил некоторое представление о недостатке создания индексов: если вы записываете в таблицу ( UPDATE или INSERT ) с одним индексом, у вас фактически есть две операции записи в файловой системе. Один для данных таблицы и другой для данных индекса (и его использование (и - если кластеризованное - использование табличных данных)). Если таблица и индекс расположены на одном жестком диске, это требует больше времени. Таким образом, таблица без индекса (кучи) позволит быстрее выполнять операции записи. (если у вас было два индекса, у вас было бы три операции записи и т.д.)

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

Другая проблема с индексами - их фрагментация с течением времени при вставке данных. REORGANIZE помогает, вы должны написать процедуры, чтобы сделать это.

В некоторых сценариях куча более полезна, чем таблица с индексами,

Кроме того, дифференциация между кластерными и некластеризованными индексами весьма важна.

Я думаю, что эти проблемы индексации могут быть решены путем поддержки двух разных баз данных, таких как Master и Slave. Где Мастер может быть использован для вставки или обновления записей. Без индексации. И раб может быть использован для чтения с правильным индексированием правильно .

нет, неправильно, извините. обновляется не только содержимое таблиц, но также структура и содержимое индекса (b-дерево, узлы). ваша концепция хозяина и раба здесь не имеет смысла. что может быть осуществимо, хотя репликация или зеркалирование во вторую базу данных, в которой выполняется аналитика, чтобы отвлечь эту нагрузку от первой базы данных. эта вторая база данных будет содержать копии данных и индексов на этих данных.

Я . Попробуйте прочитать мой комментарий и понять его правильно. Я также сказал то же самое, я говорил о главном и подчиненном (что бы то ни было) как о «воспроизведении или отражении во второй базе данных, в которой выполняется аналитика для отвода этой рабочей нагрузки от первой базы данных. Эта вторая база данных будет содержать копии данных и индексов на эти данные "

Индекс - это просто структура данных, которая быстрее выполняет поиск конкретного столбца в базе данных. Эта структура обычно является b-деревом или хеш-таблицей, но может быть любой другой логической структурой.

Для получения дополнительной информации я рекомендую следующее: Как работают индексы базы данных? И как помогают индексы?

+1 раз за миллион за этот ответ, так как я нашел этот список, пытаясь найти простое объяснение, что такое индексирование по сути.

Теперь скажем, что мы хотим запустить запрос, чтобы найти все сведения о любых сотрудниках, которые называются "Abc?

Что произойдет без индекса?

Программное обеспечение базы данных в буквальном смысле должно смотреть каждую отдельную строку в таблице Employee, чтобы узнать, является ли Employee_Name для этой строки "Abc. И поскольку мы хотим, чтобы каждая строка с именем" Abc внутри нее ", мы не можем просто перестать смотреть, как только найдем только одну строку с именем" Abc ", потому что могут быть другие строки с именем Abc, Таким образом, каждая строка до последней строки должна быть найдена - это означает, что тысячи строк в этом сценарии должны быть проверены базой данных, чтобы найти строки с именем" Abc". Это то, что называется полным сканированием таблицы

Как индекс базы данных может помочь производительности

Весь смысл иметь индекс - ускорить поисковые запросы, существенно сократив количество записей/строк в таблице, которые необходимо изучить. Индекс представляет собой структуру данных (чаще всего это B-дерево), которая хранит значения для определенного столбца в таблице.

Как работает индекс B-деревьев?

Причина, по которой B-деревья являются самой популярной структурой данных для индексов, объясняется тем, что они эффективны во времени - потому что поиск, удаление и вставка могут выполняться в логарифмическом времени. И еще одна важная причина, по которой B-деревья чаще используются, заключается в том, что данные, которые хранятся внутри B-дерева, могут быть отсортированы. СУРБД обычно определяет, какая структура данных фактически используется для индекса. Но в некоторых сценариях с определенными СУБД вы можете указать, какую структуру данных вы хотите использовать в своей базе данных при создании самого индекса.

Как работает индекс таблицы хешей?

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

Например, запрос, который мы обсуждали ранее, может извлечь выгоду из хеш-индекса, созданного в столбце Employee_Name. Способ работы хэш-индекса будет состоять в том, что значение столбца будет ключом в хэш-таблице, а фактическое значение, сопоставленное этому ключу, будет просто указателем на данные строки в таблице. Поскольку хеш-таблица в основном представляет собой ассоциативный массив, типичная запись будет выглядеть примерно так: "Abc = > 0x28939", где 0x28939 - это ссылка на строку таблицы, где Abc хранится в памяти. Поиск значения типа "Abc" в индекс хеш-таблицы и возврат ссылки на строку в памяти, очевидно, намного быстрее, чем сканирование таблицы, чтобы найти все строки со значением "Abc" в столбце Employee_Name.

Недостатки хэш-индекса

Хэш-таблицы не сортируют структуры данных, и есть много типов запросов, с которыми хэш-индексы не могут даже помочь. Например, предположим, что вы хотите узнать всех сотрудников, которым менее 40 лет. Как вы могли это сделать с помощью индекса хеш-таблицы? Ну, это невозможно, потому что хеш-таблица хороша только для поиска пар значений ключа - это означает запросы, которые проверяют равенство

Что именно находится в индексе базы данных? Итак, теперь вы знаете, что индекс базы данных создается в столбце в таблице и что индекс сохраняет значения в этом конкретном столбце. Но важно понимать, что индекс базы данных не сохраняет значения в других столбцах одной и той же таблицы. Например, если мы создаем индекс в столбце Employee_Name, это означает, что значения столбца Employee_Age и Employee_Address также не сохраняются в индексе. Если бы мы просто сохранили все остальные столбцы в индексе, то это было бы похоже на создание другой копии всей таблицы, которая занимала бы слишком много места и была бы очень неэффективной.

Как база данных знает, когда использовать индекс?Когда запускается запрос типа "SELECT * FROM Employee WHERE Employee_Name = 'Abc", база данных проверяет, есть ли индекс для столбца (ов), который запрашивается. Предполагая, что столбец Employee_Name имеет индекс, созданный на нем, база данных должна будет решить, действительно ли имеет смысл использовать индекс для поиска искомых значений - поскольку существуют некоторые сценарии, где на самом деле менее эффективно использовать индекс базы данных, и более эффективно просто сканировать всю таблицу.

Какова стоимость индекса базы данных?

Это занимает пространство - и чем больше ваш стол, тем больше ваш индекс. Еще один показатель производительности с индексами - это тот факт, что всякий раз, когда вы добавляете, удаляете или обновляете строки в соответствующей таблице, те же операции должны выполняться с вашим индексом. Помните, что индекс должен содержать то же самое до минутных данных, как и все столбцы (столбцы) таблицы, которые охватывает индекс.

Как правило, индекс должен создаваться только в таблице, если данные в индексированном столбце будут запрашиваться часто.

Читайте также: