Как оптимизировать запрос через Sqlalchemy?

Ссылка скопирована
MySQL Python SQLAlchemy
1 ответ 
query = (             select(                 func.coalesce(A.id, "").label("card_1"),                 func.coalesce(A.number, "").label("card_2"),                 func.coalesce(B.name, "").label("card_3"),                 func.coalesce(C.name, "").label("card_4"),                 func.coalesce(A.name, "").label("card_5"),                 func.coalesce(A.phone, "").label("card_6"),                 func.coalesce(A.balance, "").label("card_7"),                 func.coalesce(A.balance, "").label("card_8"),                 func.coalesce(A.active, "").label("card_9"),                 func.coalesce(D.name, "0").label("card_10"),                 func.coalesce(A.type, "").label("card_11") if filter_body.is_active == 1 else None,             ).filter(                 A.company_id.in_(filter_body.prj_ids)             ).join(                 B, A.company_id == B.id             ).join(                 C, A.voc_id == C.id             ).outerjoin(                 D, A.plan == D.id             ).offset(filter_body.offset).limit(filter_body.limit)         )         async with self._async_session_scope() as s:             result = await s.execute(query)         return result.fetchall()

query = ( select( func.coalesce(A.id, "").label("card_1"), func.coalesce(A.number, "").label("card_2"), func.coalesce(B.name, "").label("card_3"), func.coalesce(C.name, "").label("card_4"), func.coalesce(A.name, "").label("card_5"), func.coalesce(A.phone, "").label("card_6"), func.coalesce(A.balance, "").label("card_7"), func.coalesce(A.balance, "").label("card_8"), func.coalesce(A.active, "").label("card_9"), func.coalesce(D.name, "0").label("card_10"), func.coalesce(A.type, "").label("card_11") if filter_body.is_active == 1 else None, ).filter( A.company_id.in_(filter_body.prj_ids) ).join( B, A.company_id == B.id ).join( C, A.voc_id == C.id ).outerjoin( D, A.plan == D.id ).offset(filter_body.offset).limit(filter_body.limit) ) async with self._async_session_scope() as s: result = await s.execute(query) return result.fetchall()

У меня есть такой запрос (label название заменил), он с большим количеством запросов работает очень долго, несколько тысяч записей вытягивается более минуты. Если даже брать за раз не все данные на базе, а брать по 1000 например, через limit и offset. Может ли кто-нибудь посоветовать какие можно сделать оптимизации по коду?

Дополнительно:

Может ли кто-нибудь посоветовать какие можно сделать оптимизации по коду?

скажи структуру таблиц, на первый взгляд тут слишком много coaelse. Зачем это?
А в остальном можно сравнить explain если фильтр перетащить в join.
Чтобы тебе именно по оптимизации запроса помогли, вытащи из orm формируемый sql-запрос и прикрепи к вопросу.

  • chemdev,

    Может coalesce оказывать влияние на производительность? Сделано оно для этого, чтобы удобно валидировать данные как дефолтное значение. Фильтр по сути с главной таблицы идет, от которой join идет. По структуре есть все эти поля. У них нет relationship, только id. Меня интересует ли использование orm relationship как ForeignKey, даст больше производительность?

    Запрос:

    'SELECT coalesce(A.id, :coalesce_1) AS card_id, coalesce(A.serial_number, :coalesce_2) AS card_number, coalesce(B.name, :coalesce_3) AS prj_name, coalesce(C.name, :coalesce_4) AS client_name, coalesce(A.user_name, :coalesce_5) AS card_owner_name, coalesce(A.user_phone_number, :coalesce_6) AS card_owner_phone_number, coalesce(A.balance_current, :coalesce_7) AS current_balance, coalesce(A.balance_bonus, :coalesce_8) AS bonus_balance, coalesce(A.active, :coalesce_9) AS card_status, coalesce(D.name, :coalesce_10) AS tariff_plan_name, NULL AS anon_1 nFROM cashless_card JOIN company ON cashless_card.company_id = company.id JOIN voc ON cashless_card.voc_id = voc.id LEFT OUTER JOIN tariff_plan ON cashless_card.tariff_plan = tariff_plan.id WHERE cashless_card.company_id IN (__[POSTCOMPILE_company_id_1])'

  • Может coalesce оказывать влияние на производительность?

    да, ведь это дополнительные вычисления, другое дело понять, является ли это узким горлышком.
    Приведи create table для таблиц.
    Также хочу отметить, что необходимость преобразования каждого столбца намекает на ошибку проектирования. Я не понял значение этих фраз:

    удобно валидировать данные как дефолтное значение

    У них нет relationship, только id.

  • chemdev,

    я имел в виду что у самих табличек через orm, нет
    relationship('A', foreign_keys=[a_id], primaryjoin='A.id == B.currency_id'), вместо этого есть только поле Integer, в котором записана id.

    удобно валидировать данные как дефолтное значение - Значение может прийти null с какого-то поля, и вместо того, чтобы его показывать, используется coalesce, чтобы показать пустую стрингу

  • WishesFire,

    Значение может прийти null с какого-то поля, и вместо того, чтобы его показывать, используется coalesce, чтобы показать пустую стрингу

    Зачем это делать на бэкенде?
    Приведи create table для таблиц.

  • WishesFire,

    Может coalesce оказывать влияние на производительность?

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

  • chemdev,

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

    С учётом того, что функция применяется даже к полю A.id, которое почти наверняка синтетический первичный ключ таблицы, тут проблемы посерьёзнее будут...

    • Ответы:

    Если отформатировать запрос, чтобы было видно, что происходит, получим следующее:

    SELECT coalesce(A.id, :coalesce_1) AS card_id,         coalesce(A.serial_number, :coalesce_2) AS card_number,         coalesce(B.name, :coalesce_3) AS prj_name,         coalesce(C.name, :coalesce_4) AS client_name,         coalesce(A.user_name, :coalesce_5) AS card_owner_name,         coalesce(A.user_phone_number, :coalesce_6) AS card_owner_phone_number,         coalesce(A.balance_current, :coalesce_7) AS current_balance,         coalesce(A.balance_bonus, :coalesce_8) AS bonus_balance,         coalesce(A.active, :coalesce_9) AS card_status,         coalesce(D.name, :coalesce_10) AS tariff_plan_name,         NULL AS anon_1  FROM cashless_card  JOIN company ON cashless_card.company_id = company.id  JOIN voc ON cashless_card.voc_id = voc.id  LEFT OUTER JOIN tariff_plan ON cashless_card.tariff_plan = tariff_plan.id  WHERE cashless_card.company_id IN (__[POSTCOMPILE_company_id_1])

    SELECT coalesce(A.id, :coalesce_1) AS card_id, coalesce(A.serial_number, :coalesce_2) AS card_number, coalesce(B.name, :coalesce_3) AS prj_name, coalesce(C.name, :coalesce_4) AS client_name, coalesce(A.user_name, :coalesce_5) AS card_owner_name, coalesce(A.user_phone_number, :coalesce_6) AS card_owner_phone_number, coalesce(A.balance_current, :coalesce_7) AS current_balance, coalesce(A.balance_bonus, :coalesce_8) AS bonus_balance, coalesce(A.active, :coalesce_9) AS card_status, coalesce(D.name, :coalesce_10) AS tariff_plan_name, NULL AS anon_1 FROM cashless_card JOIN company ON cashless_card.company_id = company.id JOIN voc ON cashless_card.voc_id = voc.id LEFT OUTER JOIN tariff_plan ON cashless_card.tariff_plan = tariff_plan.id WHERE cashless_card.company_id IN (__[POSTCOMPILE_company_id_1])

    Почти очевидно, что в таблицах company, voc и tariff_plan поле по имени id - это первичный ключ... но хотелось бы подтверждения.

    Впрочем, видно, что для оптимизации запроса в таблице cashless_card крайне желательно наличие индекса (company_id). Следует также проверить эффективность индекса (company_id, voc_id, tariff_plan), и аналогичного с переставленными местами последними 2 полями (первым ставить то, которое более селективно).

    Кроме того, эффективность запроса сильно зависит от количества идентификаторов в списке, который передаётся в условие WHERE cashless_card.company_id IN (__[POSTCOMPILE_company_id_1]). Если их более десятка, есть смысл преобразовать этот список в набор данных (синтетическая UNION-таблица) и использовать как ещё один источник данных запроса.

    Ну и надо понимать, что наличие LIMIT/OFFSET только замедляет выполнение запроса. Причём чем больше OFFSET, тем сильнее это замедление.

    • id это первичный ключ. индексы есть до этих полей

      C offset это лучшее решение, чем вытаскивать все записи за один запрос

    • WishesFire,

      индексы есть до этих полей

      Это вот к чему сказано? Три индекса по отдельным полям и один композитный по трём полям - это две большие разницы. И я указываю на необходимость именно композитного индекса, ибо одна копия таблицы-источника не может использовать более чем один индекс, соответственно три отдельных индекса бессмысленны, использоваться может лишь один из них.

      C offset это лучшее решение, чем вытаскивать все записи за один запрос

      Ну тут хозяин - барин. Если на каждое листание идёт новый запрос, с другим OFFSET - ну, наверное, да. Хотя можно было бы подумать о выборке в temporary table и дальнейший просмотри исключительно через неё, будет куда как быстрее. А если запрос выполняется единожды, а потом листается кэшированное локально содержимое - то вовсе даже не очевидно.

    • Akina,

      Меня больше интересует использование union, список фильтрации большой, как это лучше сделать?
      там просто список id, [1, 2, 3, ...]

    • Akina, 
      project_queries = []         for project_id in filter_body.prj_ids:             project_query = query.filter(CashlessCard.company_id == project_id)             project_queries.append(project_query)         final_query = union(*project_queries)

      project_queries = [] for project_id in filter_body.prj_ids: project_query = query.filter(CashlessCard.company_id == project_id) project_queries.append(project_query) final_query = union(*project_queries)

      задумка такая?

    • WishesFire,

      Меня больше интересует использование union, список фильтрации большой, как это лучше сделать?
      там просто список id, [1, 2, 3, ...]

      В результате вместо WHERE cashless_card.company_id IN (1,2,3)должно получиться

      ... INNER JOIN ( SELECT 1 AS company_id UNION ALL              SELECT 2 UNION ALL              SELECT 3              ) AS criteria ON cashless_card.company_id = criteria.company_id

      ... INNER JOIN ( SELECT 1 AS company_id UNION ALL SELECT 2 UNION ALL SELECT 3 ) AS criteria ON cashless_card.company_id = criteria.company_id

      А уж как это отобразить в твоём фреймворке- я лично фиг знает.

      Ещё вариант - создать temporary table, слить в неё список и INNER JOIN этой таблицы. Дополнительный профит - эту таблицу можно проиндексировать.

    • WishesFire, откуда вообще берется filter_body? Сколько записей в filter_body.prj_ids когда тот запрос начинает так сильно тормозить, и сколько записей всего в основной таблице?

    1. Использовать индексы https://docs.sqlalchemy.org/en/21/core/metadata.ht...

    2. Выбор только необходимых столбцов это достигается через `select`
    https://docs.sqlalchemy.org/en/21/orm/query.html#s...

    3. Можно птимизация использования функций:

    Функции, используемые в запросах, могут сделать их медленными. Проверьте, какие функции вы используете и как они влияют на производительность. В SQLAlchemy это функции из модуля `func`.

    https://docs.sqlalchemy.org/en/21/core/sqlelement....

    Тоже будет полезно:https://docs.sqlalchemy.org/en/21/orm/query.html#s...
    https://docs.sqlalchemy.org/en/21/faq/performance....

    query = (     select(         func.coalesce(A.id, "").label("card_1"),         func.coalesce(A.serial_number, "").label("card_2"),         func.coalesce(B.name, "").label("card_3"),         func.coalesce(C.name, "").label("card_4"),         func.coalesce(A.user_name, "").label("card_5"),         func.coalesce(A.user_phone_number, "").label("card_6"),         func.coalesce(A.balance_current, "").label("card_7"),         func.coalesce(A.balance_bonus, "").label("card_8"),         func.coalesce(A.active, "").label("card_9"),         func.coalesce(D.name, "0").label("card_10"),         func.coalesce(A.type, "").label("card_11") if filter_body.is_active == 1 else None,     )     .filter(A.company_id.in_(filter_body.prj_ids))     .join(B, A.company_id == B.id)     .join(C, A.voc_id == C.id)     .outerjoin(D, A.tariff_plan == D.id)     .offset(filter_body.offset)     .limit(filter_body.limit) )  async with self._async_session_scope() as s:     result = await s.execute(query) return result.fetchall()

    query = ( select( func.coalesce(A.id, "").label("card_1"), func.coalesce(A.serial_number, "").label("card_2"), func.coalesce(B.name, "").label("card_3"), func.coalesce(C.name, "").label("card_4"), func.coalesce(A.user_name, "").label("card_5"), func.coalesce(A.user_phone_number, "").label("card_6"), func.coalesce(A.balance_current, "").label("card_7"), func.coalesce(A.balance_bonus, "").label("card_8"), func.coalesce(A.active, "").label("card_9"), func.coalesce(D.name, "0").label("card_10"), func.coalesce(A.type, "").label("card_11") if filter_body.is_active == 1 else None, ) .filter(A.company_id.in_(filter_body.prj_ids)) .join(B, A.company_id == B.id) .join(C, A.voc_id == C.id) .outerjoin(D, A.tariff_plan == D.id) .offset(filter_body.offset) .limit(filter_body.limit) ) async with self._async_session_scope() as s: result = await s.execute(query) return result.fetchall()

    Нужно решить такую задачу?

    Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.

    Заказать помощь
    Лучший ответ
    1
    Ольга Сеть Ответ

    Для оптимизации запросов через Sqlalchemy можно использовать несколько подходов. Ниже приведены некоторые из них:

    1. Используйте индексы: Создание индексов на столбцах, по которым вы часто выполняете запросы, может значительно ускорить выполнение запросов. Вы можете добавить индексы к столбцам с помощью атрибута `index=True` при описании модели.

    ```

    class User(Base):
    
    __tablename__ = 'users'
    

        id = Column(Integer, primary_key=True)
    
    name = Column(String)
    
    email = Column(String, index=True)
    
```
    

    2. Используйте отложенную загрузку: Если у вас есть связи между таблицами, которые не всегда нужны, можно использовать отложенную загрузку. Это позволит избежать избыточных запросов к базе данных.
    

    ```
    

    class User(Base):
    
    __tablename__ = 'users'
    

        id = Column(Integer, primary_key=True)
    
    name = Column(String)
    
    email = Column(String)
    

        posts = relationship("Post", lazy='select')
    
```
    

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

    ```
    

    from sqlalchemy.orm import sessionmaker
    
from sqlalchemy.orm import scoped_session
    

    Session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine, expire_on_commit=False))
    

    session = Session()
    

    # включение кэширования запросов
    
session = session.enable_cache(True)
    
```
    

    4. Используйте сессии: Использование сессий позволяет управлять транзакциями и оптимизировать выполнение запросов.
    

    ```
    

    from sqlalchemy.orm import sessionmaker
    

    Session = sessionmaker(bind=engine)
    
session = Session()
    

    # выполнение запроса
    
result = session.query(User).filter(User.name == 'John').all()
    
```
    

    5. Используйте агрегатные функции: Если вам необходимо выполнить запрос с агрегатными функциями, используйте методы Sqlalchemy для этого.
    

    ```
    

    from sqlalchemy import func
    

    # запрос с использованием агрегатной функции
    
result = session.query(func.count(User.id)).scalar()
    
```
    

    Надеюсь, эти советы помогут вам оптимизировать запросы через Sqlalchemy и улучшить производительность вашего приложения.
    Другие ответы (0)

    Пока нет других ответов. Будьте первым, кто поможет автору.

    Ответить на вопрос

    комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *

    Вам также может быть интересно

    VPN Как правильно настроить vless для Android TV? 1 ответ Pyrogram Как правильно зарегистрировать юзер бота в Telegram? 1 ответ печатные-платы Как заставить запускаться программу M3.exe от компании Hanxing AOI в инспекционной машине на Windows 7 Pro? 1 ответ code-review Нужен ли код-ревью React/TypeScript проекта? 1 ответ