@WishesFire

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

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. Может ли кто-нибудь посоветовать какие можно сделать оптимизации по коду?
  • Вопрос задан
  • 327 просмотров
Пригласить эксперта
Ответы на вопрос 2
@Akina
Сетевой и системный админ, SQL-программист.
Если отформатировать запрос, чтобы было видно, что происходит, получим следующее:

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, тем сильнее это замедление.
Ответ написан
maccree
@maccree
freelance backend developer
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....



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()
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы