Примечание: этот пост был первоначально опубликован 3 ноября 2017 года на сайте Spring Engineering. Теперь он переиздан здесь после того, как ShopRunner приобрел Spring, Inc.
Для моей команды в Spring одним из преимуществ перехода с Go на Python была возможность использовать зрелую, проверенную на практике библиотеку ORM, такую как SQLAlchemy. Когда система, которую вы собираете, вырастет до пятидесяти таблиц, вы научитесь ценить этот мощный инструмент.
Дело в том, что даже с самыми мощными инструментами нельзя забывать об основах. Вам всегда нужно помнить, как ваш выбранный ORM-движок работает под капотом и как база данных интерпретирует ваши намерения.
Загадочный случай Джейн Доу
Однажды я открыл свой почтовый ящик, чтобы узнать о невинно выглядящей ошибке в нашей внутренней системе управления заказами. Это повлияло на инструмент поиска заказов, используемый нашей службой поддержки клиентов. В некоторых редких случаях система просто возвращала не все данные. Пример запроса был очень простым: «отдайте мне все заказы клиента Джейн Доу».
Команда обслуживания клиентов знала, что Джейн Доу разместила как минимум два заказа в нашей системе. Однако в тот день явился только один из них.
Поэтому я отправил запрос к API, чтобы узнать, что скрывается под капотом, и вот что я увидел:
{
"count": 2,
"orders": [
{
"id": "C101",
"line_items": [
"Pink Dress",
"Blue Blouse",
"Yellow Hat",
"Red Pants"
]
}
]
}
Что ж, API действительно нашло оба заказа. Но вернули только одну. Какого черта?!
Число «2» относится ко всем заказам на всех страницах результатов, предполагая, что каким-то образом какая-то часть системы «знала» об отсутствующих записях. Я снова вызвал API, на этот раз явно попросив его предоставить вторую страницу результатов. Вот что у меня получилось:
{
"count": 2,
"orders": [
{
"id": "C101",
"line_items": [
"Pink Dress",
"Blue Blouse",
"Yellow Hat",
"Red Pants"
]
},
{
"id": "C102",
"line_items": [
"Black Shoes"
]
}
]
}
У нас заказ C101 присутствует как на первой, так и на второй странице. Вторая страница, однако, также включает недостающий заказ C102.
На этом этапе самое время представить две важные детали. Во-первых, давайте поприветствуем модель предметной области. Он сильно упрощен (как и примеры ответов API), но этого должно быть достаточно, чтобы продемонстрировать концепцию:
У нас есть заказ, состоящий из нескольких позиций. Когда какая-то часть заказа отправляется, мы прикрепляем к ней выполнение. Мы используем термин «выполнение» вместо «отгрузка», потому что считаем, что использование сложных слов заставляет нас казаться действительно умными.
Вторая важная часть - это код, который используется для построения запроса:
Этот код выбирает записи из сущности Order, объединяя их с помощью LineItems и Fulfillments. Затем он применяет фильтры для различных полей, где это применимо. Некоторым из вас может быть уже очевидно, что не так, но ошибка раскрывается во всей красе только после включения ведения журнала запросов в SQLAlchemy. Вы можете сделать это с помощью следующего оператора:
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
Когда мы просим первую страницу заказов Джейн, мы получаем один объект. Но теперь, когда мы включили ведение журнала SQL-оператора, мы знаем, что после выполнения подстановки параметров выполняемый запрос выглядит следующим образом:
SELECT orders.id AS orders_id, orders.customer_name AS orders_customer_name FROM orders JOIN line_items ON orders.id = line_items.order_id LEFT OUTER JOIN fulfillments ON orders.id = fulfillments.order_id WHERE orders.customer_name = 'Jane Doe' LIMIT 5 OFFSET 0
Давайте выполним его вручную и посмотрим на результат. Вот первая страница:
+-------------+------------------------+ | orders_id | orders_customer_name | |-------------+------------------------| | C101 | Jane Doe | | C101 | Jane Doe | | C101 | Jane Doe | | C101 | Jane Doe | | C101 | Jane Doe | +-------------+------------------------+
А вот второй (OFFSET 5):
+-------------+------------------------+ | orders_id | orders_customer_name | |-------------+------------------------| | C101 | Jane Doe | | C101 | Jane Doe | | C101 | Jane Doe | | C102 | Jane Doe | +-------------+------------------------+
Теперь мы можем легко объяснить одну загадку: как нас укусил ORM-движок SQLAlchemy. Он не позволяет запросу возвращать более одного объекта с одним и тем же идентификатором. Под капотом он использует карту идентичности, привязанную к сеансу. Если база данных возвращает более одной строки с одним и тем же первичным ключом, они просто дедуплицируются. Обычно эта функция очень полезна; однако он может легко скрыть скрытую ошибку или проблему с производительностью.
В нашем случае это были они оба.
Но есть еще один элемент этой головоломки, который необходимо прояснить: как мы вообще оказались с дублированными строками. Ответ, конечно же, «присоединяется». Чтобы сделать его более очевидным, давайте изменим наш запрос, чтобы он возвращал больше данных и избавился от разбиения по страницам:
SELECT orders.id, customer_name AS customer, product_name, tracking_number FROM orders JOIN line_items ON orders.id = line_items.order_id LEFT OUTER JOIN fulfillments ON orders.id = fulfillments.order_id WHERE orders.customer_name = 'Jane Doe'; +------+------------+----------------+-------------------+ | id | customer | product_name | tracking_number | |------+------------+----------------+-------------------| | C101 | Jane Doe | Red Pants | 12345 | | C101 | Jane Doe | Yellow Hat | 12345 | | C101 | Jane Doe | Blue Blouse | 12345 | | C101 | Jane Doe | Pink Dress | 12345 | | C101 | Jane Doe | Red Pants | 54321 | | C101 | Jane Doe | Yellow Hat | 54321 | | C101 | Jane Doe | Blue Blouse | 54321 | | C101 | Jane Doe | Pink Dress | 54321 | | C102 | Jane Doe | Black Shoes | <null> | +------+------------+----------------+-------------------+
Видите ли, независимо от того, какие данные вы фактически возвращаете в операторе SELECT, база данных сначала выполняет соединение, и это может привести к комбинаторному взрыву вариантов. В нашем случае достаточно отправить четыре продукта в двух упаковках, чтобы заполнить страницу результатов одним заказом.
Руководство инженера по решению проблем во взаимоотношениях
Несмотря на использование сложных слов из словаря электронной коммерции, мы больше не выглядим умными, поэтому давайте вернемся к коду и попробуем исправить этот беспорядок.
Кто-то может подумать, что при применении объединений достаточно быть более разборчивым: объединение с таблицей t выполняется только в том случае, если вы применяете фильтр к t. К сожалению, это решает проблему лишь частично или, точнее говоря, зарывает ее глубже. Это связано с тем, что это решение снова идет не так, как только вы применяете фильтр, который принимает более одного значения:
SELECT orders.id, customer_name AS customer, product_name
FROM orders JOIN line_items ON orders.id = line_items.order_id
WHERE product_name in ('Red Pants', 'Blue Blouse');
+------+------------+----------------+
| id | customer | product_name |
|------+------------+----------------|
| C101 | Jane Doe | Blue Blouse |
| C101 | Jane Doe | Red Pants |
+------+------------+----------------+
О, Боже. Нам понадобится лодка побольше.
Сейчас нам нужны подзапросы. Идея состоит в том, чтобы преобразовать наши объединения в конструкции SQL, которые выглядят примерно так:
SELECT orders.id AS orders_id, orders.customer_name AS orders_customer_name
FROM orders
WHERE EXISTS (
SELECT *
FROM line_items
WHERE line_items.order_id = orders.id
AND line_items.product_name IN ('Blue Blouse', 'Red Pants')
)
LIMIT 5 OFFSET 0
Таким образом, мы можем применить фильтр к «многим» отношениям, не помещая эти данные в первую очередь в результирующий набор. В SQLAlchemy есть много способов добиться этого. Вот один вариант:
Но мальчик, это некрасиво. Нет лучшего способа? Да, есть! SQLAlchemy предоставляет инструмент, который создает аналогичный запрос, но его гораздо проще использовать: метод any() поля коллекции. Его использование предполагает, что ваши отношения правильно определены в ваших модельных классах (но они в любом случае, не так ли?). Вы только посмотрите:
Теперь совершенно ясно, чего мы хотим достичь - даже более ясно, чем в чистом SQL.
Эпилог (и захватывающий тоже)
Преимущество описанного подхода не только в правильности: на карту поставлена и производительность. В более реальном случае мы обнаружили, что некоторые из наших запросов приводили к возврату более ста записей (!) Для каждого заказа. В результате этой модификации мы не только исправили ошибку, но и сократили среднее время, необходимое для этого запроса, с 345 мс до 193 мс.
Тем не менее, производительность функции поиска в корзине была менее чем удовлетворительной. Почему? Ну вот тема для другого рассказа.
Эта статья написана с использованием PostgreSQL 9.3 и SQLAlchemy 1.1.4.
