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

Нет, Петров, твою должностную инструкцию мы обсудим, когда у тебя появится должность, а с ней и обязанности. Сегодня речь пойдёт об инструкциях для ORM. И хотя звучит это не так важно, как ДИ, порой последствия бывают куда серьёзнее.

Проблемный SQL Ссылка на заголовок

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

SELECT parent_records.*,
               ledgers_1.*
FROM parent_records, ledgers
LEFT OUTER JOIN ledgers AS ledgers_1 ON parent_records.ledger_id = ledgers_1.id
WHERE parent_records.owner_id = :owner_id
      AND ledgers.balance > 0
     AND lower(parent_records.active_window) < :now
ORDER BY lower(parent_records.active_window) DESC
LIMIT 1;

Васечкин, в чём проблема этого запроса? Верно, FROM parent_records, ledgers

Вот эта маааленькая запятая даёт нам Декартово произведение. Каким-то образом мы дали команду базе данных: «А собери-ка нам все записи из parent_records для конкретного пользователя, но со всеми записями из ledgers». И если записей для пользователя было 50, а всех записей в ledgers было 1 000 000, то база зачитает 50М записей. И это ещё до сортировки и лимитирования. И да, Маша, LIMIT 1 нас тут никак не спасает.

Проблемный код Ссылка на заголовок

Мониторинг медленных запросов любезно сообщил нам не только сгенерированный SQL-запрос, но имя python-метода, в котором этот запрос был подготовлен. Смотрим в код и не понимаем — а в чём, собственно, проблема?

record = db_session.query(
    ParentRecord
).filter(
    ParentRecord.owner_id == owner_id,
    Ledger.balance > 0,
    unc.lower(ParentRecord.active_window) < now,
).order_by(
    func.lower(ParentRecord.active_window).desc()
).first()

Ведь мы связали модель ParentRecord с моделью Ledger с помощью специальной инструкции для ORM:

class ParentRecord(Base):
  __tablename__ = "parent_records"

  id = sa.Column(sa.BigInteger, primary_key=True)
  owner_id = sa.Column(sa.Integer, nullable=False, index=True)
  active_window = sa.Column(TSTZRANGE, nullable=False)
  ledger_id = sa.Column(sa.BigInteger, sa.ForeignKey("ledgers.id"), nullable=True)

  # eager load
  ledger = relationship(Ledger, lazy="joined", primary_join="ParentRecord.ledger_id == Ledger.id" )


class Ledger(Base):
  __tablename__ = "ledgers"

  id = sa.Column(sa.BigInteger, primary_key=True)
  balance = sa.Column(sa.Numeric(15, 2), nullable=False)

Почему же явное указание lazy="joined" не дало нужного результата? Почему наша ORM — SQL Alchemy — добавила в запрос ничем не ограниченное обращение к таблице ledgers?

Причина проблемы Ссылка на заголовок

Инструкция lazy=“joined» для отношений — это стратегия загрузки данных, а не стратегия выборки данных. В проблемном запросе возник конфликт двух механизмов:

  • С одной стороны, описав модель ParentRecord, мы проинструктировали ORM: «Когда будешь загружать модель ParentRecord, добавь LEFT OUTER JOIN к таблице ledgers, чтобы атрибут .ledger был заполнен и готов к использованию». SQL Alchemy выполнила нашу инструкцию, добавив внутренний алиас ledgers_1.
  • С другой стороны, добавив условие выборки Ledger.balancer > 0, мы сказали буквально «ну и выбери заодно все записи из таблицы ledgers, проходящие этот фильтр». Что и было сделано.

Две таблицы. Одна инструкция JOIN. Один кошмар.

Исправленный код Ссылка на заголовок

Теперь, понимая, как именно SQL Alchemy интерпретировала данные ей инструкции, изменим код, генерирующий SQL:

record = db_session.query(
    ParentRecord
).outerjoin(
    Ledger, Ledger.id ==ParentRecord.ledger_id,
 ).options(
    contains_eager(ParentRecord.ledger)
 ).filter(
    ParentRecord.owner_id ==owner_id,
    Ledger.balance >0,
    func.lower(ParentRecord.active_window) <now,
 ).order_by(
    func.lower(ParentRecord.active_window).desc()
).first()
  • outerjoin(Ledger… добавляет один, явный, не скрытый алиасами LEFT OUTER JOIN на таблицу ledgers.
  • инструкция options(contains_eager(ParentRecord.ledger… говорит загрузчику данных «не нужно добавлять внутренний join на таблицу ledgers, используй уже имеющийся явный join из пункта выше».
  • условие filter(Ledger.balance >0… теперь накладывается на уже присоединённую таблицу ledgers.

Исправленный SQL Ссылка на заголовок

В результате имеем одно явное соединение таблиц, служащее как для фильтрации данных, так и для загрузки данных в модель:

SELECT parent_records.*, ledgers.*
FROM parent_records
LEFT OUTER JOIN ledgers
 ON ledgers.id = parent_records.ledger_id
WHERE parent_records.owner_id = :owner_id
 AND ledgers.balance > 0
 AND lower(parent_records.active_window) < :now
ORDER BY lower(parent_records.active_window) DESC
LIMIT 1;

План выполнения запроса показывает десятки миллисекунд. Порядок ускорения можете прикинуть сами, не говоря уже о сохранённых нервах DevOps-департамента.

Выводы Ссылка на заголовок

  • Настройка связей между моделями SQL Alchemy добавляет внутренний (служебный) join, закрытый алиасом.
  • Простое указание связанной таблицы в секции .filter( не означает автоматический join.
  • ORM не защищает от неэффективных запросов — ORM облегчает их случайное создание.

Ну и самое главное, мои маленькие любители программирования — всегда проверяйте сгенерированный ORM запрос. Даже если код кажется вам тривиальным.

На сегодня всё, все свободны, до новых встреч!