Реляционные базы данных в примерах [Святослав Куликов] (pdf) читать онлайн

Реляционные базы
данных в примерах
Практическое пособие
для программистов и тестировщиков

Версия книги 1.0.10 от 11.01.2021

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

Содержание
ПРЕДИСЛОВИЕ .......................................................................................................................................... 4
РАЗДЕЛ 1: ОСНОВЫ БАЗ ДАННЫХ ........................................................................................................ 5
1.1.

ДАННЫЕ И БАЗЫ ДАННЫХ .......................................................................................................... 5

1.2.

МОДЕЛИРОВАНИЕ БАЗ ДАННЫХ ................................................................................................ 7

1.3.

РЕЛЯЦИОННАЯ МОДЕЛЬ ДАННЫХ ........................................................................................... 18

РАЗДЕЛ 2: ОТНОШЕНИЯ, КЛЮЧИ, СВЯЗИ, ИНДЕКСЫ ..................................................................... 20
2.1.

ОТНОШЕНИЯ ................................................................................................................................. 20

2.1.1.
2.1.2.
2.2.

КЛЮЧИ ............................................................................................................................................ 34

2.2.1.
2.2.2.
2.3.

ОБЩИЕ СВЕДЕНИЯ О КЛЮЧАХ ...................................................................................................... 34
СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ КЛЮЧЕЙ....................................................................................... 49

СВЯЗИ ............................................................................................................................................. 56

2.3.1.
2.3.2.
2.3.3.
2.4.

ОБЩИЕ СВЕДЕНИЯ ОБ ОТНОШЕНИЯХ ........................................................................................... 20
СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ОТНОШЕНИЙ ................................................................................ 26

ОБЩИЕ СВЕДЕНИЯ О СВЯЗЯХ ...................................................................................................... 56
ССЫЛОЧНАЯ ЦЕЛОСТНОСТЬ И КОНСИСТЕНТНОСТЬ БАЗЫ ДАННЫХ ................................................ 71
СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ СВЯЗЕЙ ....................................................................................... 77

ИНДЕКСЫ ..................................................................................................................................... 105

2.4.1.
2.4.2.

ОБЩИЕ СВЕДЕНИЯ ОБ ИНДЕКСАХ ............................................................................................... 105
СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ИНДЕКСОВ ................................................................................. 138

РАЗДЕЛ 3: НОРМАЛИЗАЦИЯ И НОРМАЛЬНЫЕ ФОРМЫ................................................................ 160
3.1.

АНОМАЛИИ ОПЕРАЦИЙ С ДАННЫМИ .................................................................................... 160

3.1.1.
3.1.2.
3.2.

ОБЩИЕ ВОПРОСЫ НОРМАЛИЗАЦИИ ..................................................................................... 173

3.2.1.
3.2.2.
3.2.3.
3.2.4.
3.3.

ТЕОРЕТИЧЕСКИЙ ОБЗОР АНОМАЛИЙ ОПЕРАЦИЙ С ДАННЫМИ ...................................................... 160
СПОСОБЫ ВЫЯВЛЕНИЯ АНОМАЛИЙ ОПЕРАЦИЙ С ДАННЫМИ ........................................................ 165
ТЕОРИЯ ЗАВИСИМОСТЕЙ ........................................................................................................... 173
ТРЕБОВАНИЯ НОРМАЛИЗАЦИИ В КОНТЕКСТЕ ПРОЕКТИРОВАНИЯ ................................................. 210
ПРОЦЕСС НОРМАЛИЗАЦИИ С ПРАКТИЧЕСКОЙ ТОЧКИ ЗРЕНИЯ ...................................................... 222
ДЕНОРМАЛИЗАЦИЯ.................................................................................................................... 232

НОРМАЛЬНЫЕ ФОРМЫ ............................................................................................................. 239

3.3.1.
3.3.2.
3.3.3.
3.3.4.
3.3.5.
3.3.6.
3.3.7.
3.3.8.
3.3.9.

ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА ................................................................................................... 240
ВТОРАЯ НОРМАЛЬНАЯ ФОРМА ................................................................................................... 245
ТРЕТЬЯ НОРМАЛЬНАЯ ФОРМА .................................................................................................... 251
НОРМАЛЬНАЯ ФОРМА БОЙСА-КОДДА ......................................................................................... 257
ЧЕТВЁРТАЯ НОРМАЛЬНАЯ ФОРМА .............................................................................................. 262
ПЯТАЯ НОРМАЛЬНАЯ ФОРМА ..................................................................................................... 267
ДОМЕННО-КЛЮЧЕВАЯ НОРМАЛЬНАЯ ФОРМА............................................................................... 272
ШЕСТАЯ НОРМАЛЬНАЯ ФОРМА................................................................................................... 276
ИЕРАРХИЯ НОРМАЛЬНЫХ ФОРМ И НЕКАНОНИЧЕСКИЕ НОРМАЛЬНЫЕ ФОРМЫ ............................... 279

РАЗДЕЛ 4: ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ ................................................................................. 283
4.1.

ПРОЕКТИРОВАНИЕ НА ИНФОЛОГИЧЕСКОМ УРОВНЕ ........................................................ 283

4.1.1.
4.1.2.
4.1.3.
4.2.

ЦЕЛИ И ЗАДАЧИ ПРОЕКТИРОВАНИЯ НА ИНФОЛОГИЧЕСКОМ УРОВНЕ............................................. 283
ИНСТРУМЕНТЫ И ТЕХНИКИ ПРОЕКТИРОВАНИЯ НА ИНФОЛОГИЧЕСКОМ УРОВНЕ ............................ 285
ПРИМЕР ПРОЕКТИРОВАНИЯ НА ИНФОЛОГИЧЕСКОМ УРОВНЕ........................................................ 293

ПРОЕКТИРОВАНИЕ НА ДАТАЛОГИЧЕСКОМ УРОВНЕ ......................................................... 302

4.2.1.
4.2.2.
4.2.3.

ЦЕЛИ И ЗАДАЧИ ПРОЕКТИРОВАНИЯ НА ДАТАЛОГИЧЕСКОМ УРОВНЕ.............................................. 302
ИНСТРУМЕНТЫ И ТЕХНИКИ ПРОЕКТИРОВАНИЯ НА ДАТАЛОГИЧЕСКОМ УРОВНЕ ............................. 303
ПРИМЕР ПРОЕКТИРОВАНИЯ НА ДАТАЛОГИЧЕСКОМ УРОВНЕ......................................................... 306

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

© EPAM Systems, RD Dep, 2021

Стр: 2/420

Реляционные базы данных в примерах
4.3.

ПРОЕКТИРОВАНИЕ НА ФИЗИЧЕСКОМ УРОВНЕ ................................................................... 313

4.3.1.
4.3.2.
4.3.3.
4.4.

ЦЕЛИ И ЗАДАЧИ ПРОЕКТИРОВАНИЯ НА ФИЗИЧЕСКОМ УРОВНЕ ..................................................... 313
ИНСТРУМЕНТЫ И ТЕХНИКИ ПРОЕКТИРОВАНИЯ НА ФИЗИЧЕСКОМ УРОВНЕ .................................... 318
ПРИМЕР ПРОЕКТИРОВАНИЯ НА ФИЗИЧЕСКОМ УРОВНЕ ................................................................ 320

ОБРАТНОЕ ПРОЕКТИРОВАНИЕ ............................................................................................... 330

4.4.1.
4.4.2.
4.4.3.

ЦЕЛИ И ЗАДАЧИ ОБРАТНОГО ПРОЕКТИРОВАНИЯ ......................................................................... 330
ИНСТРУМЕНТЫ И ТЕХНИКИ ОБРАТНОГО ПРОЕКТИРОВАНИЯ......................................................... 331
ПРИМЕР ОБРАТНОГО ПРОЕКТИРОВАНИЯ .................................................................................... 334

РАЗДЕЛ 5: ДОПОЛНИТЕЛЬНЫЕ ОБЪЕКТЫ И ПРОЦЕССЫ БАЗ ДАННЫХ ................................. 339
5.1.

ПРЕДСТАВЛЕНИЯ ....................................................................................................................... 339

5.1.1.
5.1.2.
5.2.

ПРОВЕРКИ .................................................................................................................................... 346

5.2.1.
5.2.2.
5.3.

ОБЩИЕ СВЕДЕНИЯ О ТРИГГЕРАХ................................................................................................ 349
СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ТРИГГЕРОВ ................................................................................ 354

ХРАНИМЫЕ ПОДПРОГРАММЫ ................................................................................................. 362

5.4.1.
5.4.2.
5.5.

ОБЩИЕ СВЕДЕНИЯ О ПРОВЕРКАХ ............................................................................................... 346
СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ПРОВЕРОК ................................................................................. 347

ТРИГГЕРЫ .................................................................................................................................... 349

5.3.1.
5.3.2.
5.4.

ОБЩИЕ СВЕДЕНИЯ О ПРЕДСТАВЛЕНИЯХ..................................................................................... 339
СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ПРЕДСТАВЛЕНИЙ ....................................................................... 343

ОБЩИЕ СВЕДЕНИЯ О ХРАНИМЫХ ПОДПРОГРАММАХ .................................................................... 362
СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ХРАНИМЫХ ПОДПРОГРАММ......................................................... 365

ТРАНЗАКЦИИ ............................................................................................................................... 373

5.5.1.
5.5.2.

ОБЩИЕ СВЕДЕНИЯ О ТРАНЗАКЦИЯХ ........................................................................................... 373
УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ .................................................................................................... 384

РАЗДЕЛ 6: ОБЕСПЕЧЕНИЕ КАЧЕСТВА БАЗ ДАННЫХ .................................................................... 387
6.1.

ОБЕСПЕЧЕНИЕ КАЧЕСТВА БАЗ ДАННЫХ НА СТАДИИ ПРОЕКТИРОВАНИЯ .................. 387

6.1.1.
6.1.2.
6.2.

ОБЩИЕ ПОДХОДЫ К ОБЕСПЕЧЕНИЮ КАЧЕСТВА БАЗ ДАННЫХ НА СТАДИИ ПРОЕКТИРОВАНИЯ......... 387
ТЕХНИКИ И ИНСТРУМЕНТЫ ОБЕСПЕЧЕНИЯ КАЧЕСТВА БАЗ ДАННЫХ НА СТАДИИ
ПРОЕКТИРОВАНИЯ .................................................................................................................... 390

ОБЕСПЕЧЕНИЕ КАЧЕСТВА БАЗ ДАННЫХ НА СТАДИИ ЭКСПЛУАТАЦИИ ....................... 393

6.2.1.
6.2.2.

ОБЩИЕ ПОДХОДЫ К ОБЕСПЕЧЕНИЮ КАЧЕСТВА БАЗ ДАННЫХ НА СТАДИИ ЭКСПЛУАТАЦИИ ............. 393
ТЕХНИКИ И ИНСТРУМЕНТЫ ОБЕСПЕЧЕНИЯ КАЧЕСТВА БАЗ ДАННЫХ НА СТАДИИ ЭКСПЛУАТАЦИИ ... 396

6.3. ДОПОЛНИТЕЛЬНЫЕ ВОПРОСЫ ОБЕСПЕЧЕНИЯ КАЧЕСТВА ДАННЫХ И КАЧЕСТВА БАЗ
ДАННЫХ .................................................................................................................................................. 400
6.3.1.
6.3.2.

ТИПИЧНЫЕ ЗАБЛУЖДЕНИЯ ОТНОСИТЕЛЬНО ДАННЫХ .................................................................. 400
ТИПИЧНЫЕ ОШИБКИ ПРИ РАБОТЕ С БАЗАМИ ДАННЫХ И СПОСОБЫ ИХ УСТРАНЕНИЯ ..................... 404

РАЗДЕЛ 7: ПРИЛОЖЕНИЯ .................................................................................................................... 407
7.1.

ОПИСАНИЕ БАЗЫ ДАННЫХ ДЛЯ ВЫПОЛНЕНИЯ САМОСТОЯТЕЛЬНЫХ ЗАДАНИЙ ..... 407

7.2.

КОД БАЗЫ ДАННЫХ ДЛЯ ВЫПОЛНЕНИЯ САМОСТОЯТЕЛЬНЫХ ЗАДАНИЙ .................. 411

РАЗДЕЛ 8: ЛИЦЕНЗИЯ И РАСПРОСТРАНЕНИЕ ................................................................................ 420

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

© EPAM Systems, RD Dep, 2021

Стр: 3/420

Предисловие

Предисловие
Эта книга посвящена практическому взгляду на реляционную теорию и проектирование реляционных баз данных. Здесь не рассматриваются такие фундаментальные основы, как реляционная алгебра и реляционное счисление, но с множеством примеров и пояснений показаны основные понятия и подходы, необходимые
для проектирования баз данных.
Этот материал в первую очередь будет полезен тем, кто:
 никогда не изучал базы данных;
 когда-то изучал базы данных, но многое забыл;
 хочет систематизировать имеющиеся знания.
Все схемы баз данных в этой книге приведены в нотации UML 2.1, созданы с
использованием Sparx Enterprise Architect и (если речь идёт об уровнях проектирования, для которых это актуально) ориентированы на MySQL 8.0, Microsoft SQL
Server 2019, Oracle 18c. Скорее всего, приведённые решения будут успешно работать на более новых версиях этих СУБД, но не на более старых.
Исходные материалы (схемы, скрипты и т.д.) можно получить по этой ссылке:
http://svyatoslav.biz/relational_databases_book_download/src_rdb.zip
Условные обозначения, используемые в этой книге:
Определения и иная важная для запоминания информация. Большинство определений будут приведены пусть и в адаптированной, но достаточно строгой форме.
Для облегчения понимания в каждом определении будет приведена его
упрощённая форма (иногда упрощение будет граничить с некорректностью, потому всё же стоит ориентироваться на более строгую формулировку, а упрощённую использовать лишь как подсказку).
Дополнительные сведения или отсылка к соответствующим источникам. Всё то, что полезно знать. При этом оригинальные (англоязычные)
определения будут приведены в сносках.
Предостережения и частые ошибки. Недостаточно показать, «как правильно», часто большую пользу приносят примеры того, как поступать не
стоит.
Задания для самостоятельной проработки. Настоятельно рекомендуется выполнять их (даже если вам кажется, что всё очень просто; и особенно — если задание выглядит сложным и непонятным).
Материал книги построен таким образом, что его можно как изучать последовательно, так и использовать как быстрый справочник (на все необходимые пояснения в тексте даны ссылки).
В дополнение к тексту данной книги рекомендуется пройти бесплатный онлайн-курс, содержащий серию видео-уроков, тестов и заданий для самоподготовки.
Приступим!

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

© EPAM Systems, RD Dep, 2021

Стр: 4/420

Раздел 1: Основы баз данных

Раздел 1: Основы баз данных
1.1. Данные и базы данных
Традиционно начнём с определений, которые едва ли откроют для вас чтото новое, но позволят нам в дальнейшем избежать многократных пояснений одних
и тех же мыслей.
Данные (data1) — поддающееся различной интерпретации представление
информации в формализованном виде, пригодном для передачи, связи,
или обработки.
Упрощённо: информация, организованная по определённым правилам.
Ключевых моментов здесь два.
Во-первых, информация должна быть представлена в формализованном
виде (иными словами, подчиняться неким правилам). Для наглядности приведём
пример неформализованного и формализованного представления информации:
Неформализованное представление
Формализованное представление
«У нас в отделе работают Иванов и
employee
Петров, и у Петрова телефон не- pass
name
department
phone
давно поменялся, был 123-44-55, стал 178 Иванов И.И.
NULL
Отд-1
999-87-32. А ещё Сидоров выйдет на 223 Петров П.П.
Отд-1
999-87-32
работу в следующем месяце, но дозво- 243 Сидоров С.С. Отд-1
333-55-66
318
Сидоров
С.С.
Отд-2
333-55-66
ниться до него тяжело будет, т.к. он
на 333-55-66 не часто сидит, а чаще
там другой Сидоров отвечает, но он
из другого отдела.»
Во-вторых, различная интерпретация позволяет нам по-разному воспринимать одни и те же данные в разном контексте. Например, поле pass (пропуск) может быть интерпретировано так:
• номер пропуска сотрудника;
• поле с уникальным значением;
• число;
• естественный первичный ключ отношения;
• кластерный индекс{109} таблицы;
• и т.д.
Когда данных становится достаточно много, появляется необходимость в их
организации в более сложные структуры.
База данных (database2) — совокупность данных, организованных в соответствии с концептуальной структурой, описывающей характеристики
этих данных и взаимоотношения между соответствующими сущностями и
поддерживающей одну или более областей применения.
Упрощённо: большой объём данных, взаимосвязь между которыми построена по специальным правилам.

1 Data — reinterpretable representation of information in a formalized manner suitable for communication, interpretation, or processing
2

(ISO/IEC 2382:2015, Information technology — Vocabulary).
Database — collection of data organized according to a conceptual structure describing the characteristics of these data and the
relationships among their corresponding entities, supporting one or more application areas (ISO/IEC 2382:2015, Information
technology — Vocabulary).

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

© EPAM Systems, RD Dep, 2021

Стр: 5/420

Данные и базы данных

Итак, база данных — это большая совокупность данных, подчинённых дополнительным правилам. Такие правила зависят от вида базы данных, а за их соблюдение отвечает система управления базами данных (СУБД).
Система управления базами данных, СУБД (database management system3, DBMS) — система (базирующаяся на программном и аппаратном
обеспечении) для описания, создания, использования, контроля и управления базами данных.
Упрощённо: программное средство, управляющее базами данных.
Стоит отметить важный для начинающих факт (т.к. очень часто можно
услышать просьбу «показать СУБД»): подавляющее большинство СУБД
не имеет никакого «человеческого интерфейса», представляет собой сервис (демон в *nix-системах) и взаимодействует с внешним миром по специальным протоколам (чаще всего, построенным поверх TCP/IP). Такие
известные продукты как MySQL Workbench, Microsoft SQL Server Management Studio, Oracle SQL Developer и им подобные — это не СУБД, это лишь
клиентское программное обеспечение, позволяющее нам взаимодействовать с СУБД.
Итак, СУБД — это специальное программное обеспечение, предназначенное
для управления базами данных. Поскольку в этой книге мы будем говорить исключительно о реляционных базах данных и реляционных же СУБД, можно сразу отметить, что взаимодействие с такими СУБД происходит путём выполнения SQLзапросов.
Изучение языка SQL и его диалектов выходит за рамки данной книги, но
если вы знакомы с общими концепциями, можно расширить свои знания,
ознакомившись с материалом книги4 «Работа с MySQL, MS SQL Server и
Oracle в примерах».
Задание 1.1.a: для расширения кругозора найдите самостоятельно информацию о том, каким образом происходит взаимодействие клиентских
приложений с нереляционными СУБД, т.е. что в таких СУБД «заменяет»
собой SQL-запросы.
Задание 1.1.b: на основе информации, представленной в приложении
1{407}, а также на основе раздаточного материала{4} сформируйте даталогическую модель базы данных «Банк» для MySQL. Если на текущий момент
это задание окажется слишком сложным, отложите его на некоторое
время и продолжите изучение материала книги.
Задание 1.1.c: на основе информации, представленной в приложении
2{411}, а также на основе раздаточного материала{4} сформируйте физическую модель базы данных «Банк» для MySQL. Если на текущий момент
это задание окажется слишком сложным, отложите его на некоторое
время и продолжите изучение материала книги.

3

Database Management System, DBMS — system, based on hardware and software, for defining, creating, manipulating, controlling, managing, and using databases (ISO/IEC 2382:2015, Information technology — Vocabulary).
4 «Работа с MySQL, MS SQL Server и Oracle в примерах» (С.С. Куликов) [http://svyatoslav.biz/database_book/]

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

© EPAM Systems, RD Dep, 2021

Стр: 6/420

Моделирование баз данных

1.2. Моделирование баз данных
Традиционно этот раздел в большинстве книг, посвящённых базам данных,
пролистывают, не читая. Прошу на этот раз всё же обратить на него внимание —
будет максимально кратко и по сути. И поможет лучше понять, откуда взялись те
или иные идеи в следующих разделах.
Модель базы данных (database model, data model5) — способ описания
базы данных с помощью формализованного (в т.ч. графического) языка на
некотором уровне абстракции.
Упрощённо: описание базы данных, по которому она потом будет создана (по аналогии с проектом здания, по которому оно потом будет
построено).
Особый интерес в данном определении представляет упоминание уровня
абстракции. Получается, что у одной и той же базы данных может быть несколько
моделей, отличающихся уровнем детализации и целью (например, общее описание данных и их взаимосвязей, описание структур данных, описание способов хранения и обработки данных и т.д.)
Отсюда мы переходим к понятию уровней (этапов) моделирования и проектирования баз данных, т.е. к построению нескольких взаимосвязанных моделей
базы данных, каждая из которых призвана служить своей особой цели.
Вы можете найти много различных (иногда — противоречащих друг другу)
классификаций уровней моделирования баз данных. Такая неразбериха
обусловлена тем, что автор каждой классификации основывался на
взгляде, актуальном для своего времени и своих технологий и по-своему
связанном с общими вопросами моделирования информационных систем. Нижеприведённая классификация не является исключением, и тоже
подвержена искажениям, связанным с точкой зрения автора.
Единственное, что нерушимо объединяет все классификации — это идея
возможности нисходящего{10} и восходящего{11} проектирования, а также
тот факт, что с каждым следующим, более низким уровнем моделирования возрастает детализация модели и усиливается её привязка к СУБД
конкретного типа, конкретного производителя, конкретной версии и т.д.
Также стоит отметить, что во многих случаях тяжело чётко обозначить границу между уровнями, то есть они плавно перетекают друг в друга (как
минимум в момент соответствующих подготовительных действий в процессе проектирования).
Прежде, чем мы рассмотрим каждый уровень (см. схему на рисунке 1.2.a),
необходимо дать ещё одно предельно важное пояснение, без которого вся эта информация может оказаться бесполезной.
Как правило, студенты технических вузов, изучающие базы данных и проектирующие их в рамках лабораторных, курсовых и дипломных работ, искренне не
понимают, зачем нужны какие-то уровни, если можно просто «взять и спроектировать базу».
Можно. В том случае, если база примитивная (до нескольких десятков таблиц), предметная область ограничивается уровнем сложности университетской ра-

5

Data model — pattern of structuring data in a database according to the formal descriptions in its information system and according
to the requirements of the database management system to be applied (ISO/IEC 2382:2015, Information technology — Vocabulary).

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

© EPAM Systems, RD Dep, 2021

Стр: 7/420

Моделирование баз данных

боты, исходные требования просты и не меняются. Т.е. если вы проектируете однотипные и/или простые базы данных, вся эта «научная заумь» вам не понадобится. (Особенно, если вы сами и заказчик, и исполнитель, и ничто не мешает опустить руки и поменять требования, как только возникнет достаточно сложная задача.)
Но! Представьте, что предметная область вам совершенно неизвестна
(например, вы проектируете не очередную базу данных библиотеки, а информационное хранилище для исследовательского центра кардиохирургии).
Добавьте к этому тот факт, что сам заказчик (в лице представителей этого
центра) не сильно разбирается в информационных технологиях, и многое объясняет вам «на пальцах», причём в процессе работы многократно выясняется, что
что-то было забыто, что-то вы не так поняли, что-то поменялось, чего-то не знал
сам заказчик и т.д.
Причём в этой ситуации, как правило, не удастся отказаться от реализации
того или иного требования просто потому, что оно слишком сложное и/или вы не
очень понимаете, как его реализовать.
Также добавьте к этому тот факт, что база данных может состоять из сотен
(и даже тысяч) таблиц, что к ней предъявляются достаточно жёсткие требования
по надёжности, производительности и иным показателям качества.
Учтите, что проектировать такую базу вы будете не в одиночку, а в составе
команды (и всем участникам надо будет гарантированно понимать друг друга, согласовывать свои действия, подменять друг друга).
Из соображений оптимизма остановим это перечисление (хотя его можно
продолжить на несколько страниц).
В такой ситуации структурированный, управляемый, регламентированный
подход к моделированию и проектированию критически необходим — без него создать работоспособную базу данных не получится. Никак.
Возвращаемся к уровням моделирования.

Рисунок 1.2.a — Обобщённая схема уровней моделирования базы данных

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

© EPAM Systems, RD Dep, 2021

Стр: 8/420

Моделирование баз данных

Инфологический (концептуальный) уровень (conceptual level6) моделирования ставит своей целью создание т.н. концептуальной модели
(conceptual model7), отражающей основные сущности предметной области, их атрибуты и связи (возможно, пока не все) между сущностями.
Упрощённо: описание предметов и явлений реального мира, данные о
которых потом будут помещены в базу данных.
Если продлить рисунок 1.2.a «вверх», к ещё меньшему уровню детализации,
от непосредственно работы с базами данных мы перейдём к области бизнес-анализа (business analysis), выявления общих требований заказчика и параметров
предметной области.
Очень подробно и наглядно весь соответствующий материал изложен в
книге Карла Вигерса «Разработка требований к программному обеспечению» (Karl E. Wiegers, «Software Requirements»).
Как предметную область целиком, так и непосредственно данные на этом
уровне можно описывать различными моделями (например, семантической, графовой, «сущность-связь»). В качестве способа представления модели чаще всего используется UML или простое словесное описание (что особенно удобно для обсуждения модели с представителем заказчика, не являющимся IT-специалистом).
Подробности реализации этой задачи будут рассмотрены в главе «Проектирование на инфологическом уровне{283}».
Даталогический уровень (часто его называют просто «логическим», logical level8) моделирования детализирует инфологическую модель, превращая её в логическую схему (logical schema9), на которой ранее выявленные сущности, атрибуты и связи оформляются согласно правилам моделирования для выбранного вида базы данных (возможно, даже с учётом
конкретной СУБД).
Упрощённо: описание предметов и явлений реального мира по правилам
выбранной СУБД.
Во многих средствах проектирования баз данных, поддерживающих разделение лишь на «логическое» и «физическое» проектирование именно этот уровень
называется «логическим».
Здесь уже появляются вполне привычные таблицы{20}, поля{20}, ключи{34},
{56}
связи , часть индексов{105} и представлений{339} — всё то, что и составляет суть базы
данных. Поскольку реализация этих объектов зависит от вида базы данных (и даже
конкретной СУБД), здесь уже нельзя строить строго абстрактную модель, и приходится учитывать типичные особенности выбранных базы данных и СУБД.
В качестве способа представления модели на этом уровне чаще всего будет
использоваться UML или постепенно утрачивающие популярность нотация IDEF1X,
нотация Чена и им подобные.
Подробности реализации этой задачи будут рассмотрены в главе «Проектирование на даталогическом уровне{302}».
6 Conceptual level — level of consideration at which all aspects deal with the interpretation and manipulation of information describing

a particular universe of discourse or entity world in an information system (ISO/IEC 2382:2015, Information technology — Vocabulary).
7 Conceptual model — representation of the characteristics of a universe of discourse by means of entities and entity relationships
(ISO/IEC 2382:2015, Information technology — Vocabulary).
8 Logical level — level of consideration at which all aspects deal with a database and its architecture, consistent with a conceptual
schema and the corresponding information base, but abstract from its physical implementation (ISO/IEC 2382:2015, Information
technology — Vocabulary).
9 Logical schema — part of the database schema that pertains to the logical level (ISO/IEC 2382:2015, Information technology —
Vocabulary).

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

© EPAM Systems, RD Dep, 2021

Стр: 9/420

Моделирование баз данных

Стоит отметить, что даталогических моделей много (документальная,
фактографическая, теоретико-графовая, теоретико-множественная, объектно-ориентированная, основанная на инвертированных файлах и т.д.).
И каждая из них породила свой вид баз данных (иногда — несколько видов).
Данная книга посвящена реляционным базам данных, но остальные (основные) хотя бы перечислим: картотеки, сетевые базы данных, иерархические базы данных, многомерные базы данных, объектно-ориентированные базы данных, дедуктивные базы данных, NoSQL базы данных и т.д.
Физический уровень (physical level10) моделирования продолжает детализацию и позволяет создать т.н. физическую схему (physical schema11), на
которой максимально учитываются технические особенности работы конкретной СУБД и её возможности по организации и управлению структурами разрабатываемой базы данных и данными в ней.
Упрощённо: описание составных частей базы данных таким образом,
чтобы на его основе можно было автоматически сгенерировать SQLкод для создания базы данных.
Если продлить рисунок 1.2.a «вниз», к ещё большему уровню детализации,
от непосредственно работы с базами данных мы перейдём к области системного
администрирования (конфигурирования СУБД, операционной системы, сетевой инфраструктуры и т.д.)
На этом уровне модель данных может быть представлена так же, как и на
предыдущем (даталогическом) — чаще всего, в виде UML, но одной лишь графической формы здесь недостаточно, потому в ход идут SQL-скрипты, словесные описания необходимых изменений и настроек, фрагменты конфигурационных файлов,
подготовленные cmd/bash-скрипты, reg-файлы и т.д.
Подробности реализации этой задачи будут рассмотрены в главе «Проектирование на физическом уровне{313}».
Ранее было упомянуто, что любая классификация уровней моделирования
допускает как нисходящее, так и восходящее проектирование. Поясним подробнее.
Нисходящее проектирование (top-down12 design) — в контексте проектирования баз данных часто называется «проектированием от предметной
области», предполагает движения от самого высокого уровня моделирования (инфологического) вниз к самому низкому (физическому).
Упрощённо: начинаем общаться с заказчиком, а потом думаем, как реализовать его требования.

10

Physical level — level of consideration at which all aspects deal with the physical representation of data structures and with
mapping them on corresponding storage organizations and their access operations in a data processing system (ISO/IEC
2382:2015, Information technology — Vocabulary).
11 Physical schema — part of the database schema that pertains to the physical level (ISO/IEC 2382:2015, Information technology
— Vocabulary).
12 Top-down — pertaining to a method or procedure that starts at the highest level of abstraction and proceeds towards the lowest
level (ISO/IEC 2382:2015, Information technology — Vocabulary).

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

© EPAM Systems, RD Dep, 2021

Стр: 10/420

Моделирование баз данных

Восходящее проектирование (bottom-up13 design) — в контексте проектирования баз данных часто называется «проектированием от запросов»,
предполагает движения от самого низкого уровня моделирования (физического) вверх к самому высокому (инфологическому).
Упрощённо: смотрим, что и как у нас получается реализовать, и потом
думаем, как с помощью этого выполнить требования заказчика.
На практике, чтобы получившаяся база данных удовлетворяла всем ключевым требованиям (будут рассмотрены очень скоро{11}), оба направления проектирования применяются попеременно. Отсюда следует, что процесс проектирования
является итерационным, и невозможно «сделать и забыть» модель на каком-то
уровне, полностью переключившись на другой.
Если попытаться совершить такую ошибку, выбрав строго одно из направлений проектирования (да ещё и не пересматривая пройденный путь с целью обнаружения и исправления ошибок), почти гарантированно будет нарушено одно из следующих требований, предъявляемых к любой базе данных (и, соответственно, её
моделям):
• адекватность предметной области;
• удобство использования;
• производительность;
• защищённость данных.
Канонических определений здесь не существует, но сами идеи крайне
важны, потому мы рассмотрим их подробно и с примерами.
С более строгой трактовкой некоторых из рассматриваемых здесь требований можно ознакомиться в книге «Основы баз данных» (Кузнецов С.Д.)
Адекватность предметной области выражается в том, что база данных
должна позволять выполнять все необходимые операции, которые объективно
нужны в реальной жизни в контексте той работы, для которой предназначена база
данных.
Лучше всего выполнить это требование позволяет моделирование на инфологическом уровне (т.к. этот уровень ближе всего находится к предметной области
и требованиям заказчика), но для выявления некоторых ошибок неизбежно придётся анализировать и модели на других уровнях. А суть этого требования наиболее наглядно отражают примеры его нарушения.
Сначала рассмотрим совершенно тривиальный пример. Допустим, для хранения информации о человеке было спроектировано отношение, представленное
следующей схемой (рисунок 1.2.b):

Рисунок 1.2.b — Тривиальный пример нарушения адекватности
предметной области
13

Bottom-up — pertaining to a method or procedure that starts at the lowest level of abstraction and proceeds towards the highest
level (ISO/IEC 2382:2015, Information technology — Vocabulary).

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

© EPAM Systems, RD Dep, 2021

Стр: 11/420

Моделирование баз данных

Сразу же бросается в глаза, что cpu_frequency (частота процессора) не
является характеристикой человека (по крайней мере, на сегодняшний день). А потому совершенно непонятно, что писать в это поле.
Если немного подумать, также становится очевидным, что в этом отношении
не хватает многих свойств, ведь характеристики человека не заканчиваются на фамилии, имени и отчестве.
Рассмотрим чуть более близкий к реальности пример ошибки проектирования (здесь даже обойдёмся без рисунка). Допустим, мы разрабатываем базу данных для автоматизации работы деканата университета. Соответствующее приложение уже введено в эксплуатацию, прошло какое-то время, и тут нам звонят заказчики и спрашивают: «А как отметить, что студент ушёл в академический отпуск?»
И мы вдруг понимаем, что… никак. Что мы не подумали о такой ситуации, и
ни приложение (с которым работают сотрудники деканата), ни наша база данных
не имеют возможности сохранить соответствующие данные.
Эта ситуация является неприятной (придётся много переделывать), но она
меркнет по сравнению со следующей — худшей из того, что может случиться.
Рассмотрим третий (по-настоящему страшный) пример, продолжив тему автоматизации работы деканата. Допустим, что информация о взаимосвязи предметов, преподавателей и студентов хранилась в базе данных, представленной (упрощённо) следующей схемой (рисунок 1.2.c):

Рисунок 1.2.c — Упрощённая схема базы данных, в которой скрыта ошибка
Все три отношения попарно соединены связями «многие ко многим{59}», ведь
действительно:
• каждый студент изучает много предметов, и каждый предмет изучает много
студентов;
• каждый предмет может вести много преподавателей, и каждый преподаватель может вести много предметов;
• каждый студент обучается у многих преподавателей, и каждый преподаватель обучает многих студентов.
Допустим, разработанный продукт успешно введён в эксплуатацию, прошло
много лет, и тут заказчик звонит и интересуется, как для очень важного отчёта показать список студентов, у которых профессор Иванов вёл высшую математику.
Никак. Эта информация не сохранялась в базе данных. Там есть лишь информация о том, что профессор Иванов (вместе с ещё тремя десятками коллег) вёл
высшую математику (и ещё восемь предметов). Есть информация о том, какие студенты учились у профессора Иванова, и какие студенты изучали высшую математику. Но никак невозможно определить, что вот именно этот конкретный студент
изучал именно высшую математику (а не другой предмет) именно у профессора
Иванова (а не у кого-то из его коллег). (Альтернативная схема, устраняющая данную проблему, будет рассмотрена далее{166}.)
Реляционные базы данных в примерах

© EPAM Systems, RD Dep, 2021

Стр: 12/420

Моделирование баз данных

Этот пример потому и назван самым страшным, что тут ничего нельзя исправить. Даже если мы доработаем приложение и базу данных, требуемая заказчиком
информация всё равно останется утерянной навсегда. А ведь заказчик был полностью уверен, что она есть и доступна.
В принципе, при грамотном подходе к сбору требований такие ошибки (как в
примерах про деканат) выявляются и при нисходящем проектировании, но намного
легче заметить их при восходящем проектировании, когда мы задумываемся о том,
какие конкретно задачи будет решать пользователь, и к выполнению каких запросов к базе данных это приведёт.
Существует ещё одна форма нарушения адекватности предметной области
— нарушения нормализации, которые приводят к возникновению аномалий операций с данными{160}. Но этот вопрос настолько важен и обширен, что ему посвящён
отдельный раздел{160}.
Удобство использования (в контексте проектирования баз данных) не связано с удобством для конечного пользователя (который может даже не знать, что в
мире существуют какие-то базы данных). Этот термин относится к использованию
базы данных в следующих ситуациях:
• при взаимодействии с приложениями (в основном, здесь упор делается на
простоту и лёгкость написания безошибочных запросов);
• в процессе её дальнейшего развития (и тогда говорят о таких показателях
качества, как поддерживаемость и сопровождаемость).
Таким образом, речь идёт об «удобстве для программиста». Почему это
важно? Чем лучше база данных отвечает данному требованию, тем проще и быстрее программисты могут организовать взаимодействие с ней, допуская минимум
ошибок и с меньшими затратами решая вопросы быстродействия, безопасности и
т.д.
Прекрасной иллюстрацией разницы в удобстве использования базы данных
в зависимости от её модели, может быть пример с определением номера дня недели и номера недели в месяце на основе указанной даты (см. очень подробное
описание этой задачи с решением в соответствующей книге14).
Пока дата хранится в виде единого значения, приходится использовать запрос на целый экран, причём для каждой СУБД в нём появляется своя «магия» в
виде числовых констант, слабо документированных параметров функций и тому подобных крайне опасных с точки зрения возможности допустить ошибку моментов.
Но модель базы данных можно изменить. Можно хранить искомые значения
в самой таблице (и вычислять их триггерами{349}) или создать представление{339} (и
«замаскировать» соответствующий запрос в нём). Тогда для программиста, организующего взаимодействие приложения с базой данных задача сведётся к тривиальному SELECT длиной в пару строк (схематично идея показана на рисунке 1.2.d).

14

«Работа с MySQL, MS SQL Server и Oracle в примерах» (С.С. Куликов), задача 7.4.1.a [http://svyatoslav.biz/database_book/]

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

© EPAM Systems, RD Dep, 2021

Стр: 13/420

Моделирование баз данных
MS SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

Запрос до правки модели

MS SQL

WITH [iso_week_data] AS
(SELECT CASE
WHEN DATEPART(iso_week,
CONVERT(VARCHAR(6), [sb_start], 112) + '01') >
DATEPART(dy,
CONVERT(VARCHAR(6), [sb_start], 112) + '01')
THEN 0
ELSE DATEPART(iso_week,
CONVERT(VARCHAR(6), [sb_start], 112) + '01')
END AS [real_iso_week_of_month_start],
CASE
WHEN DATEPART(iso_week, [sb_start]) >
DATEPART(dy, [sb_start])
THEN 0
ELSE DATEPART(iso_week, [sb_start])
END AS [real_iso_week_of_this_date],
[sb_start], [sb_id]
FROM [subscriptions])
SELECT [sb_id], [sb_start],
CASE
WHEN DATEPART(dw,
DATEADD(day, -1, CONVERT(VARCHAR(6), [sb_start], 112)
+ '01')) 5

Было
Стало
c_id
c_name
c_table
c_product_count c_product_count
1
Принтеры
ctg_part_printer 4
2
2
Процессоры ctg_part_cpu
3
3
На этом основные операции, связанные с практическим использованием связей, можно считать рассмотренными. Хотя, конечно, в следующих разделах мы ещё
неоднократно к ним вернёмся, т.к. сложно представить более фундаментальную
для работы с реляционными базами данных тему.
Задание 2.3.g: реализуйте на практике идею, описанную выше в примечании{89} о случаях, когда в «таблице связи» необходимо учитывать количество установленных связей. Для управления счётчиком используйте
триггеры. Примеры предметных областей, для которых актуальна такая
схема (но лучше — придумайте свою):
• пользователи и музыкальные композиции (счётчик хранит количество
прослушиваний);
• владельцы дисконтных карт и магазины (счётчик хранит количество посещений);
• студенты и предметы (счётчик хранит количество посещений).

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

© EPAM Systems, RD Dep, 2021

Стр: 103/420

Создание и использование связей

Задание 2.3.h: представьте, что в некотором приложении нужно отслеживать и фиксировать, сколько сообщений пользователи отправили друг
другу (любой пользователь может отправить любому другому пользователю (но не самому себе) любое количество сообщений). Создайте фрагмент схемы базы данных для хранения соответствующей информации и
реализуйте ограничение, не позволяющее внести в базу данных факт отправки пользователем сообщения самому себе.
Задание 2.3.i: стоит ли в базе данных «Банк»{407} в каких бы то ни было
таблицах, реализующих связи «многие ко многим», реализовать описанное ранее решение{89} со «счётчиком связей»? Если вы считаете, что «да»,
внесите соответствующие правки в модель.

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

© EPAM Systems, RD Dep, 2021

Стр: 104/420

Общие сведения об индексах

2.4. Индексы
2.4.1. Общие сведения об индексах
В полном развёрнутом виде материал этой главы должен занимать несколько тысяч страниц. Потому просьба принять во внимание, что здесь
будет представлена лишь самая основная информация, необходимая для
формирования представления о том, как работают индексы.
Также стоит отметить, что индексы почти полностью лежат в области физического уровня{313} проектирования базы данных, и реляционная теория
их почти не затрагивает.
В общем виде определение понятия «индекс» может быть сформулировано
следующим образом:
Индекс (index65) — специальная структура базы данных, используемая
для ускорения поиска записей и физического доступа к записям.
Упрощённо: механизм, значительно ускоряющий поиск необходимой информации в базе данных (по аналогии: для человека карта города является таким «индексом», позволяющим быстро найти нужное здание).
Индексы широко используются при проектировании баз данных в силу следующих преимуществ:
• размер индексов значительно меньше размера индексированных данных,
что позволяет размещать их в оперативной памяти для ускорения доступа;
• структура индексов специальным образом оптимизирована для выполнения
операций поиска;
• как следствие двух предыдущих пунктов, индексы значительно (иногда — на
несколько порядков) ускоряют поиск данных.
Однако, у индексов есть и недостатки, которые обязательно следует учитывать, чтобы не создавать индексы там, где они не нужны:
• когда индексов становится много, они занимают ощутимый объём оперативной памяти;
• наличие индексов ощутимо замедляет операции модификации данных
(вставки, удаления, обновления), т.к. при изменении данных СУБД необходимо обновить индексы, приведя их в соответствие с новыми значениями индексированных данных.
Отсюда логически вытекает вопрос: как понять, что тот или иной индекс нужен? Есть несколько признаков, говорящих в пользу создания индекса:
• операции чтения из таблицы выполняются гораздо чаще, чем операции модификации;
• поле или совокупность полей часто фигурируют в запросах в секции WHERE;
• исследование показало, что наличие индекса повышает производительность
запросов;
• необходимо обеспечить уникальность значения поля (или совокупности полей), не являющегося первичным ключом (в таком случае строится т.н. уникальный индекс{108});
• поле (или совокупность полей) является внешним ключом — в таком случае
индексы могут значительно ускорить выполнение JOIN-запросов.

65

Index — a specific kind of physical access path (an implementation construct, intended to improve the speed of access to data as
physically stored). («The New Relational Database Dictionary», C.J. Date)

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

© EPAM Systems, RD Dep, 2021

Стр: 105/420

Общие сведения об индексах

Разновидностей индексов очень много, и сейчас мы рассмотрим основные
примеры (см. рисунок 2.4.a). Также отметим, что в зависимости от СУБД и выбранного метода доступа{32} список поддерживаемых индексов и особенности их реализации могут очень сильно различаться.
Индекс
По количеству полей
Простой

Составной

По уникальности записей
Уникальный

Неуникальный

По соотношению с расположением записей
Кластерный
Первичный

Некластерный

По структуре хранения

Разделённый

Неразделённый

По степени детализации
Плотный

Неплотный

По базовой структуре

По сложности иерархии
Одноуровневый

Многоуровневый

По соотношению с SQL-запросом
Покрывающий

Непокрывающий

По специфическим функциям

Колоночный
Со включёнными столбцами

На вычисляемых столбцах
На значениях функции
С фильтром
Пространственный

На основе B-дерева

Полнотекстовый

На основе T-дерева

Доменный

На основе R-дерева

XML-индекс

На основе хэш-таблицы

И т.д. – постоянно появляются
новые...

На основе битовой маски
И т.д. – таких алгоритмов очень
много...

Рисунок 2.4.a — Виды индексов и их взаимосвязь
Реляционные базы данных в примерах

© EPAM Systems, RD Dep, 2021

Стр: 106/420

Общие сведения об индексах

По количеству полей индексы классифицируются совершенно аналогично
ключам{34}, а именно:
Простой индекс (simple index, single-column index66) — индекс, построенный на одном поле таблицы.
Упрощённо: индекс, включающий информацию о содержимом только одного поля таблицы.
Составной индекс (composite index67) — индекс, построенный на двух и
более полях таблицы.
Упрощённо: индекс, включающий информацию о содержимом двух и более полей таблицы.
В некоторых случаях (с определённой долей допущения) понятия «простой
ключ{39}»/«простой индекс» и «составной ключ{39}»/«составной индекс» могут выступать как синонимы68.
Как простой, так и составной индексы могут использоваться для обеспечения
уникальности значений альтернативных ключей{37} (тогда это будет уникальный индекс{108}) или ускорения поиска записей по значению индексированных полей (такой
индекс может быть как уникальным{108}, так инеуникальным{108}).
Для составных индексов в полной мере характерна подробно рассмотренная
ранее{51} проблема последовательности полей: то поле, по которому поиск часто
будет выполняться отдельно от других полей, должно быть первым.
Любая современная СУБД поддерживает использование как простых, так и
составных индексов.
Поскольку очень часто звучит вопрос о том, как составной индекс организован физически, поясним этот момент на примере сравнения простого и составного
индекса на основе сбалансированного дерева{116}. На рисунке 2.4.b структура такого
дерева представлена упрощённо, а более подробное пояснение приведено на рисунке 2.4.i далее{116}.
В общем случае для индексирования второго (третьего и т.д.) полей применяется две стратегии:
• построение комбинированных ключей, когда значения дополнительных полей хранятся рядом со значениями основных полей — такой подход технически проще, но может привести к ощутимому увеличению затрат памяти,
если в хранимых данных наблюдается ситуация, при которой одному значению первого индексируемого поля соответствует много различных значений
последующих полей;
• использование вложенных деревьев (фактически, каждый узел основного дерева является корнем дополнительного вложенного дерева) — такой подход
технически сложнее, но позволяет сэкономить память в случае, когда одному
значению первого индексируемого поля соответствует много различных значений последующих полей.

66

Single-column index — an index that is created based on only one table column («SQL Indexes»). [https://www.tutorialspoint.com/sql/sql-indexes.htm]
67 Composite index — an index on two or more columns of a table («SQL Indexes»). [https://www.tutorialspoint.com/sql/sqlindexes.htm]
68 Говоря чуть строже, «ключ» подразумевает уникальность значений в столбце, а «индекс» может быть и «неуникальным»,
т.е. не требовать соблюдения этого правила.

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

© EPAM Systems, RD Dep, 2021

Стр: 107/420

Общие сведения об индексах
Простой индекс на основе сбалансированного
дерева

Составной индекс на основе сбалансированного
дерева (с использованием комбинации ключей)

413
23
11

413 A

109
56

642
135

567

23

876

978

11

C

A 109

X

56

135

B

642 F 642 K

F

567

Z

642 G 876 X 978

N

Составной индекс на основе сбалансированного дерева (с использованием вложенных деревьев)

413

...

G

D

642

K

567
N

D

Q

O

...

F
C

Z

R

Рисунок 2.4.b — Структура простого и составного индекса на основе B-дерева
По уникальности записей индексы также можно сравнить с ключами{34} в
том смысле, что понятия «уникальный индекс» и «ключ» даже на уровне синтаксиса
SQL некоторыми СУБД рассматриваются как синонимы. В то же время стоит помнить, что реляционная теория оперирует понятием «ключ», а «индекс» появляется
на уровне реализации базы данных в конкретной СУБД.
Уникальный индекс (unique index69) — индекс, построенный на содержащем уникальные значения поле (полях) таблицы.
Упрощённо: индекс, построенный на поле, являющемся первичным или
альтернативным ключом таблицы.
Неуникальный индекс (non-unique index, index70) — индекс, построенный
на не содержащем уникальные значения поле (полях) таблицы.
Упрощённо: «просто индекс» (для неуникального индекса в синтаксисе
SQL даже нет отдельного ключевого слова, просто пишется INDEX).
Как уникальный, так и неуникальный индекс может быть простым{107} или составным{107}.
Задачей неуникального индекса является только ускорение операций поиска
по индексируемым полям, в то время как уникальный индекс связан с контролем
уникальности значений индексируемого поля (полей).
Разные СУБД в этом контексте предлагают разные возможности:
• в MySQL единственным способом обеспечения гарантированной уникальности значений поля (полей), не являющегося первичным ключом, является создание на этом поле (полях) уникального индекса;

69
70

Unique index — an index on the basis of some superkey.
Non-unique index, index — см. index{99}.

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

© EPAM Systems, RD Dep, 2021

Стр: 108/420

Общие сведения об индексах

•

•

в MS SQL Server включение свойства уникальности значения поля приводит
к созданию на этом поле уникального индекса (и наоборот — создание на
поле уникального индекса приводит к включению свойства уникальности значений этого поля);
в Oracle уникальность значений поля может быть обеспечена как с помощью
уникального индекса, так и без него, но из соображений повышения производительности рекомендуется строить на поле с уникальными значениями уникальный индекс.

Любая современная СУБД поддерживает использование как уникальных, так
и неуникальных индексов.
По соотношению с расположением записей индексируемой таблицы индексы делятся на кластерные (и первичные — как их подвид) и некластерные.
Кластерный индекс (clustered index71) — индекс, построенный на поле
(возможно, с неуникальными значениями), по которому произведено физическое упорядочивание данных в файле.
Упрощённо: по значению индексированного поля данные таблицы физически отсортированы на диске; значения поля могут повторяться.
Первичный индекс (primary index72) — индекс, построенный на поле с
уникальными значениями, по которому произведено физическое упорядочивание данных в файле.
Упрощённо: по значению индексированного поля данные таблицы физически отсортированы на диске; значения поля не могут повторяться.
Некластерный индекс (non-clustered index73) — индекс, построенный на
поле, по которому не произведено физическое упорядочивание данных в
файле.
Упрощённо: «просто индекс», принципы упорядочивания в котором никак не связаны с физическим расположением данных на диске.
Для начала поясним, как связаны между собой «первичный ключ{38}» и «первичный индекс{109}».
В теории «первичный ключ» относится к связям{56}, ссылочной целостности{71}
и нормализации{160} — т.е. теоретическим основам реляционных баз данных, а «первичный индекс» относится к способу организации данных на диске и методам доступа{32} — т.е. к физическому уровню проектирования и способам реализации конкретной СУБД.
•
•
•

На практике в различных СУБД ситуация такова:
MySQL всегда строит первичный индекс на первичном ключе;
MS SQL Server позволяют построить «кластерный уникальный» (фактически
— первичный) индекс на поле (полях), не являющемся первичным ключом;
Oracle позволяют использовать для индексации первичного ключа даже неуникальные индексы, а упорядочивание данных таблицы (именно в такой
формулировке, т.е. Oracle не оперирует понятием «кластерного индекса», а
явно говорит об «индексно-организованной таблице») по первичному ключу
не производить.

71

Clustered index — an index that is used when ordering field is not a key field — that is, if numerous records in the file can have
the same value for the ordering field. (“Fundamentals of Database Systems”, Ramez Elmasri, Shamkant Navathe)
72 Primary index — an index that is specified on the ordering key field of an ordered file of records. (“Fundamentals of Database
Systems”, Ramez Elmasri, Shamkant Navathe)
73 Non-clustered index — см. index{99}.

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

© EPAM Systems, RD Dep, 2021

Стр: 109/420

Общие сведения об индексах

Вкратце: чаще всего «первичный ключ» и «первичный индекс» будут синонимами, но исключения возможны (и нередки).
Теперь посмотрим, как это выглядит. Начнём с кластерного и первичного индексов (рисунок 2.4.c).
Первичный индекс

Ключ
индекса

242
312
328
421
437
561
769

Смещение (в
байтах) начала
физической
записи в файле

0
1073
2095
3110
4217
5319
6497

Кластерный индекс

Индексируемое поле
с уникальными
значениями
Остальные
данные
Ключ
индекса

242

Иванов И.И.

312

Петров П.П.

328

Сидоров С.С.

421

Смещение (в
байтах) начала
блока физических
записей в файле

Индексируемое поле
с неуникальными
значениями
Остальные
данные

242

Иванов И.И.

242

Петров П.П.

242

Сидоров С.С.

Блэк Д.Д.

421

Блэк Д.Д.

437

Смит Д.Д.

421

Смит Д.Д.

561

Сидоров С.С.

561

Сидоров С.С.

769

Доу Д.Д.

769

Доу Д.Д.

242
421
561
769

0
3110
5319
6497

Рисунок 2.4.c — Схематичное представление первичного и кластерного индекса
Ключевое отличие состоит в том, что первичный индекс содержит данные о
расположении каждой записи с уникальным значением индексируемого поля, а
кластерный — о начале блока записей с одинаковыми значениями индексируемого поля. Если не вдаваться в технические особенности реализации алгоритмов
построения и использования таких индексов — в остальном они идентичны.
Первичный индекс также может содержать данные о расположении не
каждой записи, а блока записей, см. далее «неплотный индекс{114}».
Некластерные же индексы отличаются от кластерных тем, что последовательность записей в индексе и физическом файле не совпадает. Как правило, это
приводит к усложнению структуры самого индекса из-за необходимости хранения
нескольких разных адресов записей с совпадающими значениями индексируемого
поля.
Схематичное представление некластерного индекса представлено на рисунке 2.4.d.

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

© EPAM Systems, RD Dep, 2021

Стр: 110/420

Общие сведения об индексах
Кластерный индекс

Некластерный индекс

Смещение (в
байтах) начала
блока физических
записей в файле

Индексируемое
некластерным индексом
поле с неуникальными
значениями

Блок смещений (в
байтах) начал
физических
записей в файле
Ключ
некластерного
индекса

Ключ
кластерного
индекса

242
421
561
769

0
3110
5319
6497

242

Иванов И.И.

242

Петров П.П.

242

Сидоров С.С.

421

Блэк Д.Д.

421

Смит Д.Д.

561

Сидоров С.С.

769

Доу Д.Д.

5319

3110
6497
0
1073
2095
4217

Блэк Д.Д.
Доу Д.Д.
Иванов И.И.
Петров П.П.
Сидоров С.С.
Смит Д.Д.

Значения упорядочены!

Индексируемое кластерным
индексом поле с
неуникальными значениями

Рисунок 2.4.d — Схематичное представление некластерного индекса (в сравнении
с кластерным)
Как видно на рисунке 2.4.d, ключи некластерного индекса упорядочены (в
данном случае — по алфавиту) по значению индексируемого поля, но сами данные
в таблице остались упорядоченными по другому полю (на котором построен кластерный индекс).
В случае неуникальности значений индексируемого поля (у нас таким значением является «Сидоров С.С.» в некластерном индексе необходимо строить блок
(цепочку) смещений для каждой записи в таблице, индексируемое поле которой содержит соответствующее значение.
Любая современная СУБД поддерживает использование как кластерных, так
и некластерных индексов, но в некоторых случаях (например, в MySQL) кластерный
индекс строится автоматически (на первичном ключе, или первом уникальном индексе, или специально добавленном поле74), т.е. не всегда есть возможность создать произвольный кластерный индекс.
См. подробное описание первичного, кластерного и вторичного (некластерного) индекса в книге «Fundamentals of Database Systems» (Ramez
Elmasri, Shamkant Navathe), 6-е издание, глава 18.1 «Types of Single-Level
Ordered Indexes».

74

«Clustered and Secondary Indexes» [https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html]

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

© EPAM Systems, RD Dep, 2021

Стр: 111/420

Общие сведения об индексах

По структуре хранения индексы (как и таблицы во многих методах доступа ) бывают разделёнными и неразделёнными:
{32}

Разделённый индекс (partitioned index75) — индекс, хранимый и обрабатываемый в виде отдельных частей (разделов, фрагментов) с целью повышения производительности.
Упрощённо: индекс, разделённый на несколько фрагментов.
Неразделённый индекс (non-partitioned index, index76) — индекс, хранимый и обрабатываемый как единая структура данных.
Упрощённо: «просто индекс» (без применения специальных команд по
умолчанию все индексы создаются как неразделённые).
Идея разделения индексов очень близка к идее разделения таблиц — способа хранения данных таблицы в виде нескольких частей (что позволяет размещать их на отдельных физических накопителях и обрабатывать параллельно).
В зависимости от СУБД между разделением таблиц и индексов может быть
следующая взаимосвязь:
• MySQL позволяет разделять таблицы и индексы только синхронно (т.е.
нельзя построить неразделённый индекс на разделённой таблице или разделённый индекс на неразделённой таблице77);
• MS SQL Server позволяет использовать любые комбинации разделения индексов и таблиц (т.е. строить разделённые и неразделённые индексы как на
разделённых, так и на неразделённых таблицах78);
• Oracle позволяет использовать любые комбинации разделения индексов и
таблиц (т.е. строить разделённые и неразделённые индексы как на разделённых, так и на неразделённых таблицах79).
В подавляющем большинстве случаев (СУБД и методов доступа{32}) разделение кластерных индексов{109} и/или кластерных таблиц не поддерживается.
Поскольку особенности реализации разделённых индексов очень сильно зависят от выбранной СУБД (и даже её версии), мы ограничимся схематичным представлением нескольких вариантов разделения индексов и таблиц на примере того,
как это реализовано в Oracle (рисунок 2.4.e).
Обязательно тщательно ознакомьтесь с документацией по вашей СУБД
и/или выбранному методу доступа. Необдуманное использование разделённых индексов и/или таблиц может значительно снизить производительность СУБД и привести к иным нежелательным последствиям.

75

Partitioned index — an index broken into multiple pieces. («Understanding Partitioned Indexes in Oracle 11g», Richard Niemiec).
[https://logicalread.com/oracle-11g-partitioned-indexes-mc02/]
76 Non-partitioned index, index — см. index{99}.
77 «Overview of Partitioning in MySQL» [https://dev.mysql.com/doc/refman/8.0/en/partitioning-overview.html]
78 «Create Partitioned Tables and Indexes» [https://technet.microsoft.com/en-us/library/ms188730(v=sql.130).aspx]
79 «Partitioning Concepts» [https://docs.oracle.com/cd/E11882_01/server.112/e25523/partition.htm]

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

© EPAM Systems, RD Dep, 2021

Стр: 112/420

Общие сведения об индексах
Неразделённая таблица, неразделённый
индекс (классический вариант)

Разделённая таблица, разделённый индекс

Единый раздел индекса

Разделы индекса

...

...

...

...

...

...

...

...

Единый раздел таблицы

...

...

...

Единый раздел индекса

...

...

...

Разделённая таблица, неразделённый индекс

Разделы индекса

...

...

...

Разделы таблицы

Неразделённая таблица, разделённый индекс

...

...

...

...

...

...

...

Единый раздел таблицы

...

...

...

...

Разделы таблицы

Рисунок 2.4.e — Схематичное представление вариантов использования разделённых индексов и таблиц

Диск-1

Диск-2

...

...

...

...
Диск-4

...

Диск-3
...

...
Диск-5

...
Диск-6

Отдельные процессы СУБД,
выполняющие операции ввода/
вывода и обработки данных

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

Рисунок 2.4.f — Схематичное представление цели создания разделённых индексов и таблиц
Большинство современных СУБД поддерживает использование как разделённых, так и неразделённых индексов, но особенности их реализации и доступные
возможности сильно отличаются в различных СУБД и методах доступа{32}.

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

© EPAM Systems, RD Dep, 2021

Стр: 113/420

Общие сведения об индексах

По степени детализации индексы бывают плотными и неплотными:
Плотный индекс (dense index80) — индекс, содержащий указатель на расположение записи для каждого значения индексируемого поля.
Упрощённо: в индексе хранятся адреса каждой записи таблицы.
Неплотный индекс (sparse index81) — индекс, содержащий указатель на
расположение блока записей для каждого значения (в случае их неуникальности) или группы значений (в случае их уникальности) индексируемого поля.
Упрощённо: в индексе хранятся адреса блоков (групп) записей.
Различие плотных и неплотных индексов проще всего пояснить в контексте
ранее рассмотренных первичного{109}, кластерного{109} и некластерного{109} индексов.
Несмотря на то, что в теории первичные индексы могут быть неплотными
(сейчас это будет рассмотрено), на практике почти у всех СУБД эти индексы являются плотными.
Проясним ситуацию с плотными и неплотными первичными индексами. По
определению{109} такие индексы строятся на полях таблицы, содержащих уникальные значения. Но сам индекс (как показано на рисунке 2.4.g) может содержать указатели как на каждую отдельную запись, так и на блок записей (в таком случае конкретные значения индексируемого поля будут лежать в диапазоне «от текущего
значения в индексе включительно, до следующего значения в индексе»).
Плотный первичный индекс

Ключ
индекса

242
312
328
421
437
561
769

Смещение (в
байтах) начала
физической
записи в файле

0
1073
2095
3110
4217
5319
6497

Неплотный первичный индекс

Индексируемое поле
с уникальными
значениями
Остальные
данные
Ключ
индекса

242

Иванов И.И.

312

Петров П.П.

328

Сидоров С.С.

421

Блэк Д.Д.

437

Смещение (в
байтах) начала
блока физических
записей в файле

Индексируемое поле
с уникальными
значениями
Остальные
данные

242

Иванов И.И.

312

Петров П.П.

328

Сидоров С.С.

421

Блэк Д.Д.

Смит Д.Д.

437

Смит Д.Д.

561

Сидоров С.С.

561

Сидоров С.С.

769

Доу Д.Д.

769

Доу Д.Д.

242
312
421
561
769

0
1073
3110
5319
6497

Рисунок 2.4.g — Плотный и неплотный первичный индекс

80

Dense index — an index that has an entry for every search key value (and hence every record) in the data file. («Fundamentals
of Database Systems», Ramez Elmasri, Shamkant Navathe)
81 Sparse index — an index that has entries for only some of the search values. («Fundamentals of Database Systems», Ramez
Elmasri, Shamkant Navathe)

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

© EPAM Systems, RD Dep, 2021

Стр: 114/420

Общие сведения об индексах

К преимуществам неплотных индексов относится их меньший размер и возможность более редкого обновления. Но это преимущество меркнет на фоне недостатка, состоящего в необходимости обращения к таблице для поиска конкретной
записи, значение индексированного поля которой попало в тот или иной блок. Потому на практике разработчики СУБД предпочитают использовать плотные первичные индексы.
Стоит отметить, что кластерные (не первичные) индексы не так страдают от
только что упомянутого недостатка: т.к. индексируемое поле в соответствующем
блоке записей будет иметь одинаковые значения, для дальнейшего выполнения
некоторых операций (например, извлечения остальных данных, не покрытых индексом) всегда придётся выполнять обращение к файлу таблицы, а для выполнения
других операций (например, подсчёта количества уникальных значений) хранимой
в индексе информации оказывается достаточно.
Из только что приведённых рассуждений должно быть очевидно, что некластерные индексы обязаны быть плотными, т.к. для них неактуально понятие «блока
записей», обладающих упорядоченностью или неким общим признаком значений
индексируемого поля.
Таким образом, кластерные индексы можно считать классическим случаем
неплотных индексов, а некластерные — плотных (рисунок 2.4.h).
Некластерный индекс (классический случай
плотного индекса)

Индексируемое кластерным
индексом поле с
неуникальными значениями
Смещение (в
байтах) начала
блока физических
записей в файле

Индексируемое
некластерным индексом
поле с неуникальными
значениями

Блок смещений (в
байтах) начал
физических
записей в файле
Ключ
некластерного
индекса

Ключ
кластерного
индекса

242
421
561
769

0
3110
5319
6497

Здесь
представлены
адреса блоков
записей

242

Иванов И.И.

242

Петров П.П.

242

Сидоров С.С.

421

Блэк Д.Д.

421

Смит Д.Д.

561

Сидоров С.С.

769

Доу Д.Д.

5319

3110
6497
0
1073
2095
4217

Блэк Д.Д.
Доу Д.Д.
Иванов И.И.
Петров П.П.
Сидоров С.С.
Смит Д.Д.

Значения упорядочены!

Кластерный индекс (классический случай
неплотного индекса)

Здесь
представлены
адреса каждой
записи

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

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

© EPAM Systems, RD Dep, 2021

Стр: 115/420

Общие сведения об индексах

По базовой структуре индексы могут быть представлены очень большим
количеством разновидностей, основными из которых являются:
Индекс на основе B-дерева (сбалансированного дерева) (B-tree82 index) — индекс, структурно организованный с использованием B-дерева
(сбалансированного дерева), оптимизированный для выполнения поиска
на основе диапазонов и для операций с большими блоками данных. Допускает хранение части индекса во внешней памяти.
Упрощённо: одна из основных форм организации индексов в большинстве СУБД и методов доступа. (Буква «B» в названии индекса идёт от
слова «balanced».)
Индекс на основе T-дерева (T-tree83 index) — индекс, структурно организованный с использованием T-дерева (разновидности сбалансированного
дерева, в котором вместо самих данных хранятся указатели на адреса
данных в памяти), оптимизированный для выполнения операций в случае,
когда и индекс и данные целиком находятся в оперативной памяти.
Упрощённо: индекс для СУБД и методов доступа, предполагающих хранение всего объёма обрабатываемых данных в оперативной памяти.
(Буква «T» в названии индекса идёт от графической формы представления вершин T-дерева в статье, в которой оно было впервые представлено.)
Индекс на основе R-дерева (R-tree84 index) — индекс, структурно организованный с использованием R-дерева (специальной формы представления географических и геометрических данных), оптимизированный для
выполнения операций со специфическими типами данных, хранящих географические координаты или информацию о геометрических фигурах.
Упрощённо: индекс для ускорения обработки географических и геометрических данных (Буква «R» в названии индекса идёт от слова
«rectangle».).
Индекс на основе хэш-таблицы (hash-table85 index) — индекс, структурно
организованный с использованием хэш-таблицы (специальной структуры
для хранения пар ключ-значение), оптимизированный для выполнения поиска на основе строгого сравнения и обработки относительно редко изменяемых данных.
Упрощённо: одна из основных форм организации индексов в большинстве СУБД и методов доступа (наряду с индексами на основе Bдеревьев).

82

B-tree — a self-balancing tree data structure that keeps data sorted and allows searches, sequential access, insertions, and
deletions in logarithmic time. («Wikipedia») [https://en.wikipedia.org/wiki/B-tree]
83 T-tree — a balanced index tree data structure optimized for cases where both the index and the actual data are fully kept in memory,
just as a B-tree is an index structure optimized for storage on block oriented secondary storage devices like hard disks. («Wikipedia») [https://en.wikipedia.org/wiki/T-tree]
84 R-tree — a tree data structure used for spatial access methods, i.e., for indexing multi-dimensional information such as geographical
coordinates, rectangles or polygons. «Wikipedia» [https://en.wikipedia.org/wiki/R-tree]
85 Hash-table — a data structure used to implement an associative array, a structure that can map keys to values. A hash table uses
a hash function to compute an index into an array of buckets or slots, from which the desired value can be found. «Wikipedia»
[https://en.wikipedia.org/wiki/Hash_table]

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

© EPAM Systems, RD Dep, 2021

Стр: 116/420

Общие сведения об индексах

Индекс на основе битовой маски (bitmap86 index) — индекс, структурно
организованный с использованием битовой маски (специальной структуры для хранения информации о наличии в поле того или иного значения), оптимизированный для работы со столбцами, количество различных
значений в которых относительно невелико.
Упрощённо: индекс хранит в очень компактной форме признак наличия
в некоторой ячейке таблицы одного из значений, присутствующих в соответствующем столбце.
Описание самих структур данных и алгоритмов их обработки выходит далеко
за рамки данной книги, но всё же приведём схематичное представление каждого из
перечисленных индексов (рисунки 2.4.i, 2.4.j, 2.4.k, 2.4.l, 2.4.m), после чего перейдём к рассмотрению областей их применения.

86

Bitmap — an array data structure that compactly stores bits. It can be used to implement a simple set data structure. A bitmap is
effective at exploiting bit-level parallelism in hardware to perform operations quickly. «Wikipedia» [https://en.wikipedia.org/wiki/Bit_array]

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

© EPAM Systems, RD Dep, 2021

Стр: 117/420

Общие сведения об индексах
Вариант реализации дерева с равноправными узлами (каждый узел хранит адрес записи)
Максимальный размер блока
задаётся как свойство индекса или
изменяется динамически; влияет на
глубину рекурсии

1092

109

642
0

567

876

978
7843

5522

2081

8903

135

3219

4315

56

6711

11

Смещение (в
байтах) начала
физической
записи в файле

Ключ
индекса

9962

23

413

642

413

135

876

23

567

11

987

56

109

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Данные таблицы на диске
Вариант реализации дерева с неравноправными узлами (адрес записи хранится только в
листовых узлах)
135

Нелистовые узлы не содержат
адресов записей, а лишь
используются для построения
самого дерева

413

Листовые узлы содержат
адреса записей

876

978
7843

642

876

3219

567

0

413

5522

135

567

1092

109

413

2081

56

9962

23

4315

6711

11

56

8903

11

642

413

135

876

23

567

11

987

56

109

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Данные таблицы на диске

Рисунок 2.4.i — Схематичное представление индекса на основе B-дерева

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

© EPAM Systems, RD Dep, 2021

Стр: 118/420

Общие сведения об индексах

643

98

19ae
NULL
NULL
NULL

11

ffa6
5e8h
f184
cc4b

Ключ
индекса

35ca
78e1
22a7
NULL

109

Размер блока фиксирован и
задаётся как свойство индекса или
изменяется динамически; влияет на
глубину рекурсии; в блоке явно
хранится только наименьшее
значение ключа

c4bb
fa18
f3c8
25b7

Указатель на
область памяти, в
которой хранится
запись

642

413

135

876

23

567

11

987

56

109

89

98

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Данные таблицы в оперативной памяти

Рисунок 2.4.j — Схематичное представление индекса на основе T-дерева

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

© EPAM Systems, RD Dep, 2021

Стр: 119/420

Общие сведения об индексах

A1 A2 A3

A4 A7

Размер блока фиксирован и задаётся как
свойство индекса или изменяется
динамически; влияет на глубину рекурсии;
в листовых блоках хранятся параметры
индексированных областей

R7 R10

5602

6794

R3

9919

R2 R6 R9

1173

A8 A9

8959

3427

R1 R5

0

R8

4511

R4

7917

A5 A6

2316

Размер блока фиксирован и задаётся как
свойство индекса или изменяется
динамически; влияет на глубину рекурсии;
во внутренних блоках хранятся параметры
областей разбиения

Смещение (в байтах)
начала физической
записи в файле

R5

R3

R10

R8

R1

R7

R9

R4

R2

R6

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Индексируемые области
пространства и области разбиения

Данные таблицы на диске

A1

A4

A7

A3
R4

R8

A8
R3

A2 A5

A9

R1

R7

R5

A6

R2

R6

R10
R9

Рисунок 2.4.k — Схематичное представление индекса на основе R-дерева

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

© EPAM Systems, RD Dep, 2021

Стр: 120/420

Общие сведения об индексах
Хэшфункция

Значение хэшфункции

Петров П.П.

1111
2222
3333
4444
5555

Иванов И.И.

Доу Д.Д.

F(x)

3110
6497
0
2095
4217

Петров П.П.
Сидоров С.С.

Сидоров С.С.

Блэк Д.Д.

Смит Д.Д.

Сидоров С.С.

Доу Д.Д.

1073
5319

Петров П.П.

Блэк Д.Д.
Доу Д.Д.
Иванов И.И.
Сидоров С.С.
Смит Д.Д.

Иванов И.И.

Хэшируемые
значения (при
добавлении и поиске
данных)

Блок индексируемых значений и смещений (в байтах) начал
физических записей в файле

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Остальные
данные...

Данные таблицы на диске (или в оперативной памяти)

Рисунок 2.4.l — Схематичное представление индекса на основе хэш-таблицы

Индексируемые
значения

Битовое
представление

Доу Д.Д.
Иванов И.И.
Петров П.П.
Сидоров С.С.

0001
0010
0100
1000

L1
L2
L3

0001
0010
0100

Механизм
совмещения
поиска по
нескольким
полям

Merge

Битовая маска по
каждой записи
таблицы

0001
0100
0001
0010
0001
0001
0100

0010
0100
1000
0001
1000
1000
0001

Данные таблицы на
диске

L1
L3
L1
L2
L1
L1
L3

Иванов И.И.
Петров П.П.
Сидоров С.С.
Доу Д.Д.
Сидоров С.С.
Сидоров С.С.
Доу Д.Д.

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

Рисунок 2.4.m — Схематичное представление индекса на основе битовой маски

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

© EPAM Systems, RD Dep, 2021

Стр: 121/420

Общие сведения об индексах

Теперь рассмотрим основные области применения каждой разновидности
представленных в данной классификации индексов. Обратите внимание: здесь
речь не идёт о преимуществах и недостатках, т.к. они очень сильно зависят от конкретной ситуации.
Индекс на основе…

B-дерева

T-дерева

R-дерева

Хэш-таблицы

Битовой
маски

Основная область
применения…

Типичная
форма реализации
большинства индексов, эффективны при
операциях
=, >, ≥, = 2
AND `u_id`