СУБД для программиста: базы данных изнутри


Сергей Тарасов





                СУБД для программиста




Базы данных изнутри













СОЛОН-Пресс


2020

  УДК 621.396.218
  ББК 32.884.1
     Т 19

  Тарасов С. В. СУБД для программиста. Базы данных изнутри. — М.: СОЛОН-Пресс, 2020. — 320 с.: ил.

  ISBN 978-2-7466-7383-0
  EAN 9782746673830

  Книга охватывает различные этапы разработки и сопутствующие им ситуации из практики программистов приложений, работающих с системами управления базами данных. Даются рекомендации по выбору решений как в проектировании (архитектуре), так и в программировании автоматизированных информационных систем уровня предприятия. Приводятся примеры для различных СУБД и моделей: Microsoft SQL Server, PostgreSQL, Firebird, Oracle, XML, NoSQL.
  Для программистов, студентов и других специалистов в области информационных технологий, а также всех интересующихся темой разработки приложений баз данных.




Сайт журнала «Ремонт & Сервис»: www.remserv.ru
Сайт издательства «СОЛОН-Пресс»: www.solon-press.ru

По вопросам приобретения обращаться:
ООО «СОЛОН-Пресс»
Тел: (495) 617-39-64, (495) 617-39-65
E-mail: kniga@solon-press.ru, www.solon-press.ru


ISBN 978-2-7466-7383-0
EAN 9782746673830

© Тарасов С. В., 2020
© «СОЛОН-Пресс», 2020

Посвящается моему отцу...




               Цивилизованный человек отличается от дикаря главным образом благоразумием, или, если применить немного более широкий термин, предусмотрительностью. Цивилизованный человек готов ради будущих удовольствий перенести страдания в настоящем, даже если эти удовольствия довольно отдалены.
              Б. Рассел, «История западной философии»

Содержание
Введение........................................................7
Основные понятия................................................9
  База данных и СУБД............................................9
  Типы приложений: транзакционная и аналитическая обработка....11
  Клиент-серверные и встроенные СУБД...........................14
    Сноска. Firebird 2.5: состояние............................19
  Основные модели данных: иерархическая, сетевая, реляционная.22
    Иерархическая модель.......................................22
    Сетевая модель.............................................28
    Реляционная модель.........................................33
  Другие подходы и модели данных...............................37
    Модель «Сущность-атрибут-значение» (EAV)...................37
    Неполно структурированные модели данных....................46
    Документ-ориентированная модель и NoSQL....................48
    Многомерные модели данных..................................53
    О применимости NoSQL.......................................56
  Множественная и навигационная обработка, менеджеры записей..61
  Объектная модель и объектно-реляционная проекция.............65
  SQL как универсальный входной язык...........................75
Проектирование.................................................78
  Терминология уровней.........................................78
  Первичные и прочие ключи.....................................83
  Внешние ключи и связи........................................87
  Нормализация и денормализация................................89
    1НФ........................................................90
    2НФ........................................................91
    3НФ........................................................92
  Деморализуем... то есть денормализуем: «звезда» и «снежинка».93
  Типовая архитектура данных аналитических приложений..........98
  Переносимость между СУБД....................................100
    Абстрагирование от СУБД...................................101
    Абстрагирование от входного языка СУБД....................102
    Использование подмножества входного языка.................104
  Типовые структуры...........................................104

4

    Моделирование связей разных типов........................105
    Хронологические данные...................................109
    Иерархические данные и деревья в SQL.....................115
  Интернационализация/локализация данных и проброс контекста.130
  Метаданные.................................................138
  Реестр объектов и аудит....................................143
  Безопасность и доступ к данным.............................145
  Проектирование физического хранения........................151
    Физическая организация памяти............................152
    Оперативная и долговременная память......................155
    Дисковые массивы.........................................157
    Оперативная память.......................................160
    Индексация данных........................................161
    Секционирование данных...................................163
  Неполно структурированные данные и высокая нагрузка........165
    Относительность понятия высокой нагрузки.................165
    Особенности использования РСУБД и НСМД (NoSQL)...........168
  Нужно ли моделировать?.....................................172
    Моделирование против ручного кодирования: пример.........174
  Большие данные как состояние отрасли.......................181
Программирование с испытаниями...............................187
  Типы соединений в SQL на примерах..........................187
  Исходники и синхронизация структур.........................190
  Некоторые особенности программирования.....................200
    Параметризация запросов и SQL-инъекции...................200
    Сравнение с неопределёнными (пустыми) значениями.........203
    Работа со строками.......................................204
    Работа с датами..........................................207
    Генерация идентификаторов записей........................209
  Транзакции, изоляция и блокировки..........................214
    Уровни SQL-92............................................215
    Блокировки...............................................219
    Взаимные блокировки процессов (deadlock).................222
    Версии данных............................................225
    Проявления эффектов изоляции.............................227

5

    Толстые транзакции......................................232
  Загрузка данных...........................................233
    Пакетная загрузка.......................................234
    Вставка в толстой транзакции............................240
  РСУБД и неполно структурированные данные..................241
    Поддержка XML...........................................242
    Поддержка JSON..........................................250
    Выводы..................................................253
  Постраничные выборки......................................254
    Обзор способов постраничной выборки.....................256
    Тестирование способов постраничной выборки..............260
    Выводы..................................................271
  SQL и модульное тестирование..............................271
    Место модульного тестирования в системе испытаний.......271
    Особенности разработки на процедурных расширениях SQL...273
    Пример задачи для модульного теста......................273
    Создаём специализированный макроязык....................276
    Остановиться и оглянуться...............................283
  Производительность SQL-запросов...........................284
    Общие рекомендации......................................284
    Анализ плана выполнения запроса.........................286
  Поиск узких мест..........................................291
  Основы нагрузочного тестирования..........................297
    Инструменты и методы....................................297
    Учёт степени параллелизма...............................301
  SQL Server и MongoDB на простом тесте.....................304
    Тест вставки записей....................................304
    Запросы и хронометраж...................................308
    Выводы..................................................315
  Тестовые и демонстрационные базы данных...................315
Заключение..................................................317
Литература..................................................318

6

    Введение

  Разработка приложений баз данных распространена не только в «корпоративном секторе» автоматизации производственных процессов предприятий и их отделов. Из классического определения «программы — это алгоритмы плюс данные» следует, что сколь верёвочке не виться, пройдя через цепочку служб, запрос в итоге обрабатывается системой управления базами данных (СУБД) или её неполнофункциональным аналогом. Активно развивающийся рынок мобильных устройств широко использует встраиваемые (embedded) СУБД, ранее применявшиеся в основном для управления оборудованием.
  Если программист сознательно не ограничивает себя разработкой служб и человеко-машинных интерфейсов, взаимодействующих исключительно с другими службами, то вскоре возникает необходимость непосредственной работы с какой-либо СУБД, вероятнее всего реляционной.
  Как правило, обладателям профильного образования преподаватели читали соответствующий теоретический курс в вузе [2], сопровождаемый практическими занятиями, пересекающийся по тематике с другими предметами. И, тем не менее, приступая к производственным задачам, вчерашний студент быстро ощутит отличие академических подходов от открывающегося взгляду пейзажа строек в недрах корпоративного софтостроения или в глубоком тылу веб-служб.
  Тяжелее пришедшим в программирование из других областей деятельности. Окунувшись в реальность без багажа теории, соответствующего, как говорят американцы, бэкграунда, трудно сформировать в голове целостную картину, охватывающую важные детали происходящего, пропуская несущественные. Не хватает ни времени, ни мотивации читать достаточно скучные, толстые монографии вроде многократно переизданного Дейта [1], когда надо решать текущие задачи. Программисту становится не до вопросов философии кунг-фу, освоить бы побыстрее основные удары и блоки, чтобы получать поменьше оплеух от брыкающейся техники, исполненных значимости системных администраторов и недовольного начальства. Хорошо, если удастся выкроить часок-другой и потренироваться в сиквеле¹ на примерах из книжки Грабера [3]...

    Сиквелом на жаргоне программистов называется язык SQL (Structured Query Language). Первоначальная его версия так и называлась SEQUEL. Впрочем, если вы будете говорить просто «эс-ку-эль», это не повлияет на ваш профессионализм.

7

  Справиться с растущим потоком информации, не пропуская её через фильтры теоретического багажа, становится всё труднее. Например, последние годы активно пропагандируют NoSQL, может быть там все будет проще, не надо задумываться о нормальных формах и тренировать мозги на непривычную множественную обработку? Или может быть лучше работать через проекцию таблиц на объекты и не использовать прямой доступ к базе данных?
  Книга «Софтостроение изнутри» [13] посвятила немалое количество сюжетов теме «как это не надо делать» и прогрессирующей в среде программистов некомпетентности в области баз данных, приводящей к катастрофическим для проектов последствиям на более поздних стадиях. В отличие от предтечи, настоящее издание будет носить ровно противоположный характер, следуя принципу «как это лучше сделать». Опираясь на опыт работы в продуктовом софтостроении и в технической экспертизе СУБД-решений, автор постарается в рамках повествования помочь вам не утонуть в информационном потоке и разобраться в часто возникающих на практике проблемах, не отрывая их от теории.
  Почему не блог, а книга? Действительно, в Сети можно найти немало интересных статей. Однако, во-первых, чтобы найти нужную информацию по правильно заданным ключевым словам, а, во-вторых, оценить достоверность найденной публикации, нужно уже иметь определённый уровень компетенции, который кроме как чтением книг и практикой не поднять. Поэтому рекомендую и начинающим, и программистам с небольшим (2-3 года) опытом не увлекаться малоосмысленным копированием кода со страниц в Сети и прочим натягиванием глобуса на Меркаторову проекцию.
  Цель книги не в том, чтобы заменить чтение упомянутых выше монографий или других книг из прилагаемого списка литературы, но подготовить и подвести к нему осознанно, исходя из нужд решения практических задач. Попытаться выстроить мост между бескомпромиссным академическим гранитом и производственными зыбучими песками, бесследно засасывающих неосмотрительных путников своими половинчатыми решениями и постоянной текучкой.
  Все-таки, нет ничего практичнее, чем хорошая теория.

8

            Основные понятия


              «...пока мы не знаем закона природы, он, существуя и действуя помимо, вне нашего познания, делает нас рабами «слепой необходимости». В. Ульянов (Ленин)

        База данных и СУБД

  Основополагающие термины достаточно подробно рассмотрены в любой из монографий [1,2,4,5]. Тем не менее, чтобы однозначно понимать суть в ходе чтения, нам нужно выработать общий словарь. С этой целью я буду по мере необходимости приводить минимальный набор определений, составляющий основу дальнейшего изложения.
База данных (БД) — структурированное поименованное хранилище информации.
  Из самого первого определения должно быть понятно, что содержащий начисленные квартальные премии сотрудников файл формата CSV² является хоть и очень простой, но базой данных, а текстовый файл с научным описанием мышей-полёвок ей не является.
Система управления базами данных (СУБД) — специализированное программное обеспечение, обеспечивающее доступ к базе данных как к совокупности её структурных единиц.
  Из второго определения также должно быть понятно, что открыв упомянутый CSV-файл в текстовом редакторе, мы хоть и видим базу данных, но не работаем с ней посредством СУБД. Если же мы откроем файл в приложении LibreOffice Calc, то данная программа превращается хоть и в очень простую, но СУБД, позволяющую нам работать со структурированным текстом, как с таблицей на уровне колонок, строк и значений, а также посчитать итоги, средние и крайние величины. Положив

² CSV (от англ. Comma-Separated Values — значения, разделённые запятыми) — текстовый формат, предназначенный для представления табличных данных. Каждая строка файла — это одна строка таблицы. Значения отдельных колонок разделяются разделительным символом (запятой).

9

файл в разделяемый по локальной сети каталог, получаем примитивную многопользовательскую СУБД на основе все того же приложения.
  Исторически, в устройстве СУБД выделяли три уровня, предложенных ещё в 1975 году в отчёте ANSI/X3/SPARC [1,2,5,7]:
   •  внешний уровень наиболее близок к приложениям и пользователям, он связан со специфичными для них способами представления данных;
   •  логический уровень, также называемый концептуальным, для описаний данных, не зависящих от физической реализации;
   •  внутренний уровень, называемый также физическим, наиболее близок к физическому хранилищу данных, он связан со способами хранения на физических устройствах.
  Внутренний уровень не всегда связан с файловой системой. Наиболее развитые современные СУБД представляют собой по сути специализированную операционную систему³, способную управлять физическими устройствами хранения, кешем данных, процессами и потоками, оперативной памятью, асинхронным запуском и внутренним планировщиком задач минуя собственно операционную систему компьютера.
  Вернёмся к примеру с CSV-файлом и приложением LibreOffice Calc, наглядно показывающему, что даже в примитивной СУБД все три упомянутых уровня можно чётко выделить:
   •  внутренний уровень представлен собственно файлом формата CVS со спецификацией его полного имени в файловой системе, кодовой страницы и символа разделителя;
   •  логический уровень представлен объектом «Электронная таблица» (spreadsheet), позволяющим описать данные в терминах листов, колонок и строк независимо от того, работаем ли мы с CSV-файлом или с файлом в другом формате, возможно даже двоичном;

³ См. например информацию по SQLOS в составе Microsoft SQL Server

10

   •  внешний уровень не только позволяет пользователям проводить над данными специфичные операции путём создания встроенных программ на бейсик-подобном языке, но и разграничить доступ к ним на уровне видимости, чтения и записи.
  Тем не менее, LibreOffice Calc является не СУБД, а приложением для работы с электронными таблицами. Сама по себе всякая электронная таблица уже является БД, но далеко не всякая БД является электронной таблицей. Поскольку целью книги не является обзор способов организации простейших БД и примитивных СУБД, то на этом примере мы пока остановимся и перейдём к более насущным вопросам.

        Типы приложений: транзакционная и аналитическая обработка

  Начиная разговор о транзакционной обработке, нельзя обойтись без соответствующего определения.
Транзакцией в СУБД называется совокупность операций над данными, являющаяся неделимой (атомарной).
  Исторически, транзакции пришли в СУБД из области финансов и бухгалтерии, где нельзя снять деньги с одного счета, не зачислив их на другой, а сама операция, проводка, так и называется — transaction. На этом хрестоматийном примере часто объясняют сам принцип транзакционной обработки. Другой пример, тоже родом откуда-то из 1950-60-х годов, из области пассажирских перевозок. Когда необходимо заказать билет для путешествия транзитом, транзакция состоит, как минимум, из двух операций: резервирования билета до пункта пересадки и резервирования билета до места назначения. В случае если одна из операций невозможна, отменяется вся транзакция целиком.
  Любое приложение, реализующее аналогичные вышеописанным прикладные функции, требующие использования механизма транзакций, относится к типу транзакционных. Отличительные особенности таких приложений следующие.

11

   •  Обработка идёт в режиме реального или приближенного к реальному времени. Время отклика системы при запросе оператора не превышает единиц секунд.
   •  Запросы представляют собой интенсивный поток коротких операций по вставке, изменению и удалению небольшого числа записей в БД. Эти операции могут быть как одиночными транзакциями, так и объединяться в более крупные транзакции.
  В реляционной СУБД любой оператор SQL является одиночной транзакцией по умолчанию. Некоторые реализации, например Firebird, по умолчанию открывают новую транзакцию при выполнении каждого оператора SQL, оставляя её «висеть» до выдачи команды завершения. Это так называемый режим неявных транзакций (implicit transaction). Для управления соответствующим поведением, СУБД обычно имеет опцию включения и отключения неявных транзакций, например, set implicit_transaction on|off в SQL Server.
  Основными аббревиатурами, касающимися транзакций с которыми вам придётся сталкиваться будут OLTP (On-Line Transaction Processing) — собственно интерактивная транзакционная обработка, и ACID (AtomicityConsistency-Isolation-Durability) — принципы неделимости, целостности, изолированности и надёжности. О них мы поговорим в дальнейшем, рассмотрев на примерах принципы изоляции.
  В противоположность транзакционной, аналитическая обработка данных имеет другие отличительные признаки.
   •  Данные находятся в режиме чтения, за исключением моментов их обновления.
   •  Выборки представляют собой одиночные тяжёлые запросы: поиски и расчёты по множеству произвольных критериев могут охватывать значительную часть данных в базе.
   •  Время отклика системы не регламентировано, нередко пользователь имеет возможность прервать слишком долго

12

      выполняющийся запрос, чтобы упростить его или разделить расчёты на более короткие этапы.
   •  Размеры базы данных, как правило, на порядок и больше превышают таковые для транзакционной.
  Аналогичной аббревиатурой для интерактивной аналитической обработки является OLAP (On-Line Analytical Processing). Соответственно, интерактивное приложение, работающее с СУБД в режиме OLAP, относится к аналитическим.
  Важное здесь слово «интерактивная». Действительно, в отличие от транзакционной, аналитическая обработка может вестись и в пакетном режиме: пользователь формулирует задачу в понятных приложению терминах, ставит обработку в очередь и, например, утром следующего дня получает результаты. Таковой, например, является заблаговременная ночная генерация регулярных отчётов, рассылаемых по утрам или с другой заданной периодичностью группам пользователей (см. например Microsoft SQL Server Reporting Services).
  Необходимо понимать, почему возникло разделение на транзакционную и аналитическую обработку, ведь со стороны внешнего уровня архитектуры СУБД они не слишком отличаются. В обоих случаях запросы, да, немного разные, ну и что?
  Основная проблема заключается в том, что СУБД, ориентированные на транзакционную обработку, менее эффективны при работе с аналитическими запросами и наоборот. Универсальных архитектур и их реализаций, одинаково легко справляющихся как с большим потоком мелких запросов и транзакций, так и с гораздо меньшим числом массивных тяжёлых выборок по большому диапазону, не существует. Пока, по крайней мере. Ещё сложнее реализовать оба типа обработки одновременно в одной и той же БД. Поэтому возникла и уже долгое время⁴ существует упомянутая

    Первые тиражируемые системы аналитической обработки данных относятся к 1970 году (Express), но окончательно, как самостоятельное направление, OLAP оформилось только в начале 1990-х годов.

13