Задать вопрос
@xiiicool

Как оптимизировать sql запрос?

В запросе используется оконная функция которая достаточно сильно тормозит запрос, на все поля в partition и order сделаны индексы, сервер 16 ядер 70 гиг оперативки
select rr.*
from (select rr.*,
             case
                 when rr.startAt > max(rr.endAt)
                                       over (partition by rr.value,
                                           rr.rateTimemaskUUID, rr.billIncUUID, rr.notes,
                                           rr.name,
                                           rr.destinationUUID
                                           order by rr.startAt, rr.endAt, rr.dialCode
                                           rows between unbounded preceding and 1 preceding)
                     or max(rr.endAt)
                            over (partition by rr.value,
                                rr.rateTimemaskUUID, rr.billIncUUID, rr.notes,
                                rr.name, rr.destinationUUID
                                order by rr.startAt, rr.endAt, rr.dialCode
                                rows between unbounded preceding and 1 preceding) is null then 1
                 else 0
                 end as is_new_rate
      from rates rr
               left join products p on (p.uuid = rr.productUUID)
               left join destinations d on (d.uuid = rr.destinationUUID)
      WHERE p.carrierUUID = 'd255b155-b8b3-479c-847d-d93ef4545f8a'
        AND (
          p.carrierUUID in
          (select c.uuid
           from carriers c
           where c.countryUUID = '25e5740d-f556-40fb-bc51-7a99b35630b3')
          )
        AND p.direction = '1') rr


explain выглядит так
[
  {
    "id": 1,
    "select_type": "PRIMARY",
    "table": "<derived2>",
    "partitions": null,
    "type": "ALL",
    "possible_keys": null,
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": 1984,
    "filtered": 100,
    "Extra": null
  },
  {
    "id": 2,
    "select_type": "DERIVED",
    "table": "c",
    "partitions": null,
    "type": "const",
    "possible_keys": "PRIMARY,carriers_countryUUID_foreign_idx",
    "key": "PRIMARY",
    "key_len": "144",
    "ref": "const",
    "rows": 1,
    "filtered": 100,
    "Extra": "Using temporary; Using filesort"
  },
  {
    "id": 2,
    "select_type": "DERIVED",
    "table": "p",
    "partitions": null,
    "type": "ref",
    "possible_keys": "PRIMARY,carrierUUID",
    "key": "carrierUUID",
    "key_len": "145",
    "ref": "const",
    "rows": 15,
    "filtered": 10,
    "Extra": "Using index condition; Using where"
  },
  {
    "id": 2,
    "select_type": "DERIVED",
    "table": "rr",
    "partitions": null,
    "type": "ref",
    "possible_keys": "rates_productUUID_foreign_idx",
    "key": "rates_productUUID_foreign_idx",
    "key_len": "145",
    "ref": "billing_api.p.uuid",
    "rows": 1322,
    "filtered": 100,
    "Extra": null
  },
  {
    "id": 2,
    "select_type": "DERIVED",
    "table": "d",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY",
    "key": "PRIMARY",
    "key_len": "144",
    "ref": "billing_api.rr.destinationUUID",
    "rows": 1,
    "filtered": 100,
    "Extra": "Using index"
  }
]
  • Вопрос задан
  • 225 просмотров
Подписаться 2 Простой 10 комментариев
Пригласить эксперта
Ваш ответ на вопрос

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

Похожие вопросы
xRocket Москва
от 3 000 до 4 000 $
div. Ставрополь
от 50 000 до 120 000 ₽
Lachestry Таганрог
от 170 000 до 200 000 ₽
10 дек. 2024, в 15:33
800 руб./в час
10 дек. 2024, в 14:57
40000 руб./за проект
10 дек. 2024, в 14:35
6000 руб./за проект