Переводчик Е. Матвеев

 

Билл Карвин

Антипаттерны SQL. Как избежать ловушек при работе с базами данных. — СПб.: Питер, 2024.

 

ISBN 978-5-4461-2178-6

© ООО Издательство "Питер", 2024

 

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

 

Отзывы о книге «Антипаттерны SQL»

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

Шломи Ноах (Shlomi Noach). Инженер баз данных, PlanetScale

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

Дэниел Нихтер (Daniel Nichter). Администратор баз данных, автор книги Efficient MySQL Performance, Block Inc.

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

Сэмюэл Маллен (Samuel Mullen). Старший менеджер отдела разработки, ActiveProspect

Книга под завязку набита полезными сведениями. Как новички, так и люди с опытом наверняка узнают из нее что-нибудь новое. Я, например, узнал немало.

Стивен Грим (Steven Grimm). Техлид, Terraformation

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

Макс Тилфорд (Max Tilford). Сеньор-разработчик, Firstup

«Антипаттерны SQL» Билла Карвина — не только одна из лучших книг о базах данных, которую я прочитал. Это еще и определенно один из лучших образцов технической литературы, которые мне попадались. Я трижды прочел ее от корки до корки, и то, что я из нее узнал, помогает мне каждый день.

Пим Бруверс (Pim Brouwers). Старший архитектор программного обеспечения, Ассоциация игроков Национальной хоккейной лиги

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

Алекс Острем (Alex Ostrem). Инженер по надежности баз данных и разработке программного обеспечения, Etsy

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

Дженнифер Песек (Jennifer Pesek). Библиограф-консультант

Посвящаю своей жене Джен, моей самой надежной поддержке и опоре

Благодарности

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

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

У меня было много преподавателей, которые знакомили меня с теорией и практикой computer science, но я хочу особо поблагодарить двоих: доктора Кевина Керпласа (Dr. Kevin Karplus) и доктора Дэна Скрипчера (Dr. Dan Scripture). Они разработали учебный курс для технических писателей; этот курс показал мне всю важность практики составления документации в области, требующей внимания для изложения сложных идей.

Меня вдохновляли многие руководители и коллеги. Кейт Рейнольдс (Keith Reynolds) поручил мне первые проекты на языке C, которые научили меня, что программирование — это по большей части работа с чужим кодом. Дэвид Бреденберг (David Bredenberg) на собственном примере показал, что не стоит жалеть времени на то, чтобы помогать другим разработчикам. А благодаря коучингу Реа Бэррона (Rhea Barron) я повысил свои навыки написания технических текстов до нового уровня.

Я благодарен научным редакторам, которые любезно пожертвовали свое время на работу над вторым изданием книги: Рональду Брэдфорду (Ronald Bradford), Жану-Франсуа Ганю (Jean-François Gagné), Стивену Гримму (Steven Grimm), Сэмюэлу Маллену, Алексу Остраму, Дженнифер Песек, Максу Тилфорду и Пиму ван дер Валу. Их отзывы сделали эту книгу лучше. Спасибо вам!

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

Я бесконечно благодарен своей жене Джен Дуайер (Jan Dwyer) — состоявшемуся писателю и разработчику баз данных. Она была первой, кто оценивал черновики обоих изданий этой книги. Ее вклад и мнение были бесценны, и без ее постоянной помощи, моральной поддержки и вдохновения я бы не справился.

Введение

Эта книга посвящена SQL — популярному языку программирования для работы с данными. А точнее, она посвящена ошибкам при использовании SQL.

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

Моя первое знакомство с SQL обернулось отказом от работы. Я только что окончил колледж, и ко мне обратился менеджер, который работал в университете и знал меня по летней практике. Он планировал запустить собственный стартап, который занимался бы разработкой ПО, и ему требовалась система управления базами данных, работающая на разных платформах UNIX на основе shell-скриптов. Ему нужен был программист вроде меня, который написал бы код для распознавания и выполнения ограниченного подмножества языка SQL.

«Нам не нужна полная поддержка языка — это слишком сложно. Только одной инструкции SQL — SELECT».

В колледже SQL не преподавали. Но я занимался разработкой полноценных приложений shell-скриптов и немного знал о парсерах и языках предметных областей. И я склонялся к тому, чтобы принять предложение. Что сложного в парсинге одной инструкции такого специализированного языка, как SQL?

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

Я отказался от предложения запрограммировать парсер SQL и ядро РСУБД в shell-скриптах. Руководитель недооценил масштаб проекта — возможно, он просто не понимал, что делает РСУБД.

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

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

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

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

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

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

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

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

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

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

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

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

О книге

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

Производительность, масштабируемость и оптимизация — важные характеристики приложений баз данных, особенно в веб-среде, но это не главные темы моей книги. Вот некоторые издания, которые можно порекомендовать по темам производительности и масштабируемости: «SQL Performance Tuning» [GP03], «High Performance MySQL, 4th Edition» [BT21], «Efficient MySQL Performance» [Nic21] и «Effective MySQL Optimizing SQL Statements» [Bra11].

Каждая РСУБД на основе SQL использует собственные инструменты и команды, но эта книга не справочник по командам и не сборник рецептов.

Фреймворки доступа к данным и библиотеки объектно-реляционного отображения — довольно полезные инструменты, но и они не занимают центральное место в книге.

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

Билл Карвин. Октябрь 2022 г.

Условные обозначения

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

Шрифты

Ключевые слова SQL записываются в верхнем регистре моноширинным шрифтом, чтобы они выделялись в тексте, например SELECT.

В именах таблиц SQL, также оформленных моноширинным шрифтом, каждое слово записывается с прописной буквы, например Accounts или BugsProducts. Имена столбцов SQL, также оформленные моноширинным шрифтом, записываются в нижнем регистре, а слова разделяются символами подчеркивания, например account_name.

Строковые литералы записываются курсивом, например bill@example.com.

Терминология

Название языка SQL произносится «эс-ку-эль», а не «сиквэл». Впрочем, оба варианта встречаются достаточно часто, так что окружающие в любом случае поймут, что вы имеете в виду.

В SQL термины «запрос» (query) и «инструкция» (statement) считаются отчасти взаимозаменяемыми; под ними понимается любая завершенная команда SQL, которую можно выполнить. В книге для большей ясности термином «запрос» обозначаются инструкции SELECT, в остальных случаях используется термин «инструкция».

Онлайн-ресурсы

Примеры и исходный код, приведенные в книге, находятся по ссылке на веб-сайте Pragmatic Bookshelf1. На этом же сайте можно сообщить о найденных ошибках в англоязычном издании и отправить свои предложения.

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

---

О научном редакторе русского издания

Александр Петраки — старший инженер-разработчик компании КРОК. Занимается проектированием архитектуры высоконагруженных приложений и выполняет реализацию back-end части на Java и Spring с применением СУБД MySQL, PostgreSQL, Oracle, JanusGraph.

От издательства

Мы выражаем огромную благодарность компании КРОК за помощь в работе над русскоязычным изданием книги и их вклад в повышение качества перевод­ной литературы.

Ваши замечания, предложения, вопросы отправляйте по адресу comp@piter.com (издательство «Питер», компьютерная редакция).

Мы будем рады узнать ваше мнение!

На веб-сайте издательства www.piter.com вы найдете подробную информацию о наших книгах.

Глава 1. Что такое антипаттерн?

Эксперт — это человек, который совершил все возможные ошибки в очень узкой специальности.

Нильс Бор

Антипаттерн — прием, предназначенный для решения одной проблемы, но при этом часто создающий другие. Существует множество способов применения антипаттернов, но у них у всех есть общее. Разработчик может прийти к антипаттерну самостоятельно, либо воспользовавшись советом коллеги, либо прочитав о нем в книге или в статье. Многие антипаттерны из области объектно-ориентированного проектирования и управления проектами представлены в сборнике паттернов Portland Pattern Repository2, а также в книге 1998 года AntiPatterns [BMMM98].

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

Типы антипаттернов

Книга включает в себя четыре части в соответствии с категориями антипаттернов.

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

• Антипаттерны физического проектирования баз данных. Определив, какие данные необходимо сохранить, вы реализуете управление данными настолько эффективно, насколько позволяет технология используемой РСУБД. На этой стадии определяются таблицы и индексы, а также выбираются типы данных. При этом используется язык определения данных SQL с такими инструкциями, как CREATE TABLE.

• Антипаттерны запросов. Данные необходимо добавить в БД, а потом прочитать их. Запросы SQL записываются на языке манипулирования данными с такими инструкциями, как SELECT, UPDATE и DELETE.

• Антипаттерны разработки приложений. Язык SQL предназначен для использования в контексте приложений, написанных на другом языке, таком как C++, Java, PHP, Python или Ruby. Существуют правильные и неправильные способы применения SQL в приложении, и в этой части книги описываются некоторые распространенные ошибки.

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

Кроме того, в книге разбираются мини-антипаттерны. К этой категории относятся другие ошибки, часто совершаемые разработчиками при работе с SQL. Мини-антипаттерны рассматриваются не так подробно, как основные антипаттерны.

Многие антипаттерны имеют забавные или образные названия. Такие имена часто присваивают паттернам проектирования (как «правильным», так и «неправильным», то есть антипаттернам), чтобы их было проще запоминать.

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

Анатомия антипаттерна

Глава, посвященная каждому антипаттерну, состоит из следующих подразделов:

• Цель. Это проблема, которую необходимо решить. Антипаттерны должны стать ее решением, но в конечном итоге порождают больше проблем, чем решают.

• Антипаттерн. В этом разделе описывается природа типичного решения, а также непредвиденные последствия, из-за которых оно становится антипаттерном.

• Как распознать антипаттерн. Существуют признаки, которые помогают выявить антипаттерны в проекте. На присутствие антипаттерна могут указывать сложности, с которыми вы сталкиваетесь, или фразы, которые произносите вы или ваши коллеги.

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

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

Диаграммы «объект — отношение»

Для наглядного представления реляционных баз данных чаще всего используются диаграммы «объект — отношение» (entity-relationship dia­gram). На таких диаграммах таблицы изображаются в виде блоков, а отношения — в виде линий, соединяющих блоки; при этом условные знаки на концах линии описывают кратность отношения. Вот несколько примеров диаграмм «объект —отношение»:

Пример базы данных

Многие темы в книге поясняются на примере базы данных вымышленного приложения для отслеживания ошибок.

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

Introduction/setup.sql

CREATE TABLE Accounts (

  account_id SERIAL PRIMARY KEY,

  account_name VARCHAR(20),

  first_name VARCHAR(20),

  last_name VARCHAR(20),

  email VARCHAR(100),

  password_hash CHAR(64),

  portrait_image BLOB,

  hourly_rate NUMERIC(9,2)

);

CREATE TABLE BugStatus (

  status VARCHAR(20) PRIMARY KEY

);

CREATE TABLE Bugs (

  bug_id SERIAL PRIMARY KEY,

  date_reported DATE NOT NULL DEFAULT (CURDATE()),

  summary VARCHAR(80),

  description VARCHAR(1000),

  resolution VARCHAR(1000),

  reported_by BIGINT UNSIGNED NOT NULL,

  assigned_to BIGINT UNSIGNED,

  verified_by BIGINT UNSIGNED,

  status VARCHAR(20) NOT NULL DEFAULT 'NEW',

  priority VARCHAR(20),

  hours NUMERIC(9,2),

  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),

  FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id),

  FOREIGN KEY (verified_by) REFERENCES Accounts(account_id),

  FOREIGN KEY (status) REFERENCES BugStatus(status)

);

CREATE TABLE Comments (

  comment_id SERIAL PRIMARY KEY,

  bug_id BIGINT UNSIGNED NOT NULL,

  author BIGINT UNSIGNED NOT NULL,

  comment_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  comment TEXT NOT NULL,

  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),

  FOREIGN KEY (author) REFERENCES Accounts(account_id)

);

CREATE TABLE Screenshots (

  bug_id            BIGINT UNSIGNED NOT NULL,

  image_id          BIGINT UNSIGNED NOT NULL,

  screenshot_image  BLOB,

  caption           VARCHAR(100),

  PRIMARY KEY (bug_id, image_id),

  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)

);

CREATE TABLE Tags (

  bug_id BIGINT UNSIGNED NOT NULL,

  tag VARCHAR(20) NOT NULL,

  PRIMARY KEY (bug_id, tag),

  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)

);

CREATE TABLE Products (

  product_id SERIAL PRIMARY KEY,

  product_name VARCHAR(50)

);

CREATE TABLE BugsProducts(

  bug_id BIGINT UNSIGNED NOT NULL,

  product_id BIGINT UNSIGNED NOT NULL,

  PRIMARY KEY (bug_id, product_id),

  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),

  FOREIGN KEY (product_id) REFERENCES Products(product_id)

);

Диаграмма «объект — отношение» для базы данных ошибок:

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

Эта глава кратко познакомила вас с антипаттернами, и теперь вы знаете их основные признаки. Перейдем к изучению отдельных антипаттернов и проблем, которые они создают.

---

Часть I. Антипаттерны логического проектирования баз данных

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