Книга: «Антипаттерны SQL. Как избежать ловушек при работе с базами данных»

Привет, Хаброжители!

Язык SQL необходим для работы со структурированными данными. Программисты, прекрасно знающие свой любимый язык (Java, Python или Go), не могут разбираться во всем, и часто не являются экспертами в SQL. Это приводит к появлению антипаттернов — решений, которые на первый взгляд кажутся правильными, но со временем создают все больше проблем.

Научитесь выявлять и обходить многие из этих распространенных ловушек! Проведите рефакторинг унаследованного кошмара и превратите его в жизнеспособную модель данных!

Примеры SQL-кода основаны на версии MySQL 8.0, но в тексте также упоминаются другие популярные РСУБД. В примерах кода используется Python 3.9+ или Ruby 2.7+.

О втором издании

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

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

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

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

Были также обновлены примеры кода, чтобы соответствовать последним версиям MySQL и Python — самым популярным современным языкам базы данных с открытым исходным кодом и динамического программирования.

Для кого эта книга

Книга «Антипаттерны SQL» подойдет каждому, кто работает с SQL, то есть практически всем, от новичков до матерых профессионалов. Темы, рассматриваемые в книге, будут полезны разработчикам любого уровня.

Возможно, вы уже изучали синтаксис SQL. Вы знаете все секции инструкции SELECT и можете приступать к работе. Постепенно вы будете повышать свои навыки в SQL, читая код, книги и блоги. Но освоите ли вы тем самым лучшие практики или только загоните себя в угол?

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

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

Как распознать антипаттерн

Если ваши коллеги по команде произносят следующие фразы, это может указывать на применение антипаттерна «Полиморфная связь»:

• «В этой схеме можно связать тег (или другой атрибут) с любым другим ресурсом в базе данных».
  
  Как и в случае с EAV, следует с осторожностью относиться к любым заявлениям о неограниченной гибкости, потому что, скорее всего, такое решение нарушает какие-нибудь правила.
• «В нашей архитектуре базы данных нельзя объявлять внешние ключи».
  
  Еще один тревожный признак. Внешние ключи — фундаментальная особенность реляционных баз данных, а у решения, которое неспособно корректно работать со ссылочной целостностью, очень много проблем.
• «Для чего нужен столбец entity_type? А, он сообщает, на что указывает тот, другой столбец».
  
  Любой внешний ключ должен ссылаться на одну таблицу во всех строках данных

Фреймворк Ruby on Rails поддерживает полиморфные связи, объявляя классы Active Record с атрибутом :polymorphic. Например, связывание Comments с Bugs и FeatureRequests может выполняться следующим образом:

Polymorphic/recog/commentable.rb

class Comment < ActiveRecord::Base
  belongs_to :commentable, :polymorphic => true
end

class Bug < ActiveRecord::Base
  has_many :comments, :as => :commentable
end

class FeatureRequest < ActiveRecord::Base
  has_many :comments, :as => :commentable
end

Фреймворк Hibernate для Java поддерживает отношения наследования между сущностями, используя разнообразные объявления схем.

Комбинирование данных с метаданными

Возможно, вы заметили некоторое сходство между антипаттерном «Полиморфная связь» и антипаттерном «Сущность — атрибут — значение», описанным в предыдущей главе. В обоих случаях имя объекта метаданных хранится в виде строкового значения. В EAV имя столбца атрибута хранится в виде строки в столбце attr_name. В «Полиморфной связи» имена родительских таблиц хранятся в столбце issue_type. Иногда такой подход называется комбинированием данных с метаданными. Эта же концепция встречается в другой форме в главе 8 «Многостолбцовые атрибуты».

Допустимые применения антипаттерна

Старайтесь избегать антипаттерна «Полиморфная связь» — используйте ограничения (например, внешние ключи) для обеспечения ссылочной целостности. Антипаттерн «Полиморфная связь» слишком сильно зависит от кода приложения (а не от метаданных).

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

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

Решение: упрощение отношений

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

Обратные ссылки

Одно из решений для этого антипаттерна упрощается, если задуматься над природой проблемы: полиморфная связь имеет обратное направление.

Создание таблиц пересечений

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

Polymorphic/soln/reverse-reference.sql

CREATE TABLE BugsComments (
  issue_id BIGINT UNSIGNED NOT NULL,
  comment_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (issue_id, comment_id),
  FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments(comment_id)
);
CREATE TABLE FeaturesComments (
  issue_id BIGINT UNSIGNED NOT NULL,
  comment_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (issue_id, comment_id),
  FOREIGN KEY (issue_id) REFERENCES FeatureRequests(issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments(comment_id)
);

Это решение снимает необходимость в столбце Comments.issue_type. Теперь метаданные обеспечивают целостность данных, а приложение перестает зависеть от правильности кода управления связями.

Стоп-сигнал

У этого решения есть потенциальный недостаток: оно разрешает связи, которые вы, возможно, разрешать не захотите. Таблицы пересечений обычно моделируют связи «многие ко многим», поэтому отдельный комментарий может быть связан с несколькими ошибками или несколькими запросами на добавление функций. Однако вы, скорее всего, хотите, чтобы каждый комментарий относился только к одной ошибке или запросу. Это правило можно реализовать (по крайней мере частично) объявлением ограничения UNIQUE для столбца comment_id каждой таблицы пересечений.

Polymorphic/soln/reverse-unique.sql

CREATE TABLE BugsComments (
  issue_id BIGINT UNSIGNED NOT NULL,
  comment_id BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY (comment_id),
  PRIMARY KEY (issue_id, comment_id),
  FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments(comment_id)
);

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

Поиск в обе стороны

Чтобы получить комментарии для конкретной ошибки или функции, просто используйте таблицу пересечений:

Polymorphic/soln/reverse-join.sql

SELECT *
FROM BugsComments AS b
  JOIN Comments AS c USING (comment_id)
WHERE b.issue_id = 1234;

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

Polymorphic/soln/reverse-join.sql

SELECT *
FROM Comments AS c
  LEFT OUTER JOIN (
    BugsComments JOIN Bugs AS b USING (issue_id)
  ) USING (comment_id)
  LEFT OUTER JOIN (
    FeaturesComments JOIN FeatureRequests AS f USING (issue_id)
  ) USING (comment_id)
WHERE c.comment_id = 9876;

Слияние

Иногда нужно представить результат запроса к нескольким родительским таблицам так, словно родители хранятся в одной таблице (см. раздел «Наследование от общей таблицы» в главе 6). Это можно сделать одним из двух способов.

Сначала рассмотрим запрос с использованием UNION:

Polymorphic/soln/reverse-union.sql

SELECT b.issue_id, b.description, b.reporter, b.priority, b.status,
    b.severity, b.version_affected,
    NULL AS sponsor
  FROM Comments AS c
    JOIN (BugsComments JOIN Bugs AS b USING (issue_id))
      USING (comment_id)
  WHERE c.comment_id = 9876;

UNION
  SELECT f.issue_id, f.description, f.reporter, f.priority, f.status,
    NULL AS severity, NULL AS version_affected,
    f.sponsor
  FROM Comments AS c
    JOIN (FeaturesComments JOIN FeatureRequests AS f USING (issue_id))
      USING (comment_id)
  WHERE c.comment_id = 9876;

Этот запрос должен гарантированно возвращать одну строку, если в приложении один комментарий связан только с одной родительской таблицей. Так как результаты запросов могут объединяться конструкцией UNION только в том случае, если их столбцы совпадают по количеству и типу данных, необходимо добавить NULL-заполнители для столбцов, уникальных для каждой родительской таблицы. Столбцы должны перечисляться в одинаковом порядке в обоих запросах, задействованных в UNION.

Также рассмотрите следующий запрос, использующий функцию SQL COALESCE(). Функция возвращает свой первый аргумент, отличный от NULL. Так как в запросе используется внешнее соединение, комментарий, относящийся к функции и не имеющий совпадающих строк в Bugs, вернет все поля из b.* в виде NULL. Точно так же все поля f.* будут содержать NULL, если комментарий относится к ошибке, а не к функции. Поля, присутствующие только в одной из родительских таблиц, просто перечисляются в запросе; если они не актуальны для соответствующей родительской таблицы, они возвращаются в виде NULL.

Polymorphic/soln/reverse-coalesce.sql

SELECT c.*,
  COALESCE(b.issue_id, f.issue_id ) AS issue_id,
  COALESCE(b.description, f.description) AS description,
  COALESCE(b.reporter, f.reporter ) AS reporter,
  COALESCE(b.priority, f.priority ) AS priority,
  COALESCE(b.status, f.status ) AS status,
  b.severity,
  b.version_affected,
  f.sponsor

FROM Comments AS c
  LEFT OUTER JOIN (BugsComments JOIN Bugs AS b USING (issue_id))
    USING (comment_id)
  LEFT OUTER JOIN (FeaturesComments JOIN FeatureRequests AS f USING (issue_id))
    USING (comment_id)
WHERE c.comment_id = 9876;

Оба запроса довольно сложны, поэтому они отлично подходят для выделения в сущность представления базы данных (VIEW), чтобы проще использовать их в создаваемом приложении.

Создание общей супертаблицы

В объектно-ориентированном полиморфизме к двум подтипам можно обращаться похожим образом, потому что они имеют общий супертип. В SQL антипаттерн «Полиморфная связь» не задействует критическую сущность — общий супертип. Проблема решается созданием базовой таблицы, которая расширяется всеми родительскими таблицами (см. раздел «Наследование с таблицами классов» в главе 6). Добавьте в дочернюю таблицу Comments внешний ключ, ссылающийся на базовую таблицу. Ниже приведены диаграмма и код возможной реализации.

Polymorphic/soln/super-table.sql

CREATE TABLE Issues (
  issue_id SERIAL PRIMARY KEY
);

CREATE TABLE Bugs (
  issue_id BIGINT UNSIGNED PRIMARY KEY,
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
  . . .
);

CREATE TABLE FeatureRequests (
  issue_id BIGINT UNSIGNED PRIMARY KEY,
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
  . . .
);

CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  issue_id BIGINT UNSIGNED NOT NULL,
  author BIGINT UNSIGNED NOT NULL,
  comment_date DATETIME,
  comment TEXT,
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
  FOREIGN KEY (author) REFERENCES Accounts(account_id),
);

Обратите внимание: первичные ключи Bugs и FeatureRequests также являются внешними ключами. Они ссылаются на значение суррогатного ключа, сгенерированное в таблице Issues, вместо того чтобы генерировать новое значение для себя.

Для заданного комментария можно получить связанную с ним ошибку или функцию, используя относительно простой запрос. Включать в этот запрос таблицу Issues вообще не придется, если только вы не определяете в ней столбцы атрибутов. Кроме того, поскольку значение первичного ключа таблицы Bugs и ее родительской таблицы Issues одинаковы, можно связать Bugs непосредственно с Comments. Две таблицы можно соединить даже при отсутствии ограничения внешнего ключа, связывающего их напрямую, при условии, что вы используете столбцы, представляющие сопоставимую информацию в базе данных.

Polymorphic/soln/super-join.sql

SELECT *
FROM Comments AS c
  LEFT OUTER JOIN Bugs AS b USING (issue_id)
  LEFT OUTER JOIN FeatureRequests AS f USING (issue_id)
WHERE c.comment_id = 9876;

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

Polymorphic/soln/super-join.sql

SELECT *
FROM Bugs AS b
  JOIN Comments AS c USING (issue_id)
WHERE b.issue_id = 1234;

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

В каждом табличном отношении должна быть одна дочерняя и одна родительская таблица.

Об авторе

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

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

Более подробно с книгой можно ознакомиться на сайте издательства:

» Оглавление
» Отрывок

По факту оплаты бумажной версии книги на e-mail высылается электронная книга.
Для Хаброжителей скидка 25% по купону — SQL