У меня есть довольно большой запрос SQL, который замедляется операторами GROUP BY и ORDER BY. Как лучше всего оптимизировать?

Question

У меня есть довольно большой запрос SQL, который замедляется операторами GROUP BY и ORDER BY. Как лучше всего оптимизировать?

Как лучше всего оптимизировать этот запрос, поскольку для его выполнения требуется до 12 секунд?

Приносим извинения за размер запроса, он выполняется почти мгновенно без операторов group by и order by.

Я новичок в оптимизации SQL, кодирую всего около года. Я не писал этот запрос, но мне нужно его исправить.

SELECT
`products`.*,
`product_alternative`.`alternative_product_code`,
`product_alternative`.`id` as `product_alternative_id`,
`vat_rate`.`rate` AS `vat_rate`,
`product_images`.`filename` AS `product_image_file`,
`product_docs`.`filename` AS `product_doc_file`,
`suppliers`.`supplier_code`,
`suppliers`.`name` AS `supplier_name`,
`commission`.`commission` AS `supplier_commission`,
`categories`.`name` AS `category_name`,
`sub_categories`.`name` AS `subcategory_name`,
IF(`products`.`product_doc_id` = 0,
 NULL,
 CONCAT(
"/product-docs/",
`products`.`id`
)) AS `product_doc_url`,IF(`products`.`product_image_id` = 0, NULL, CONCAT(
"/product-images/",
`products`.`id`,
"/original/"
)) AS `original_image_url`,IF(`products`.`product_image_id` = 0, NULL, CONCAT(
"/product-images/",
`products`.`id`,
"/medium/"
)) AS `medium_image_url`,IF(`products`.`product_image_id` = 0, NULL, CONCAT(
"/product-images/",
`products`.`id`,
"/thumb/"
)) AS `thumb_image_url`,CASE
WHEN
    `group_favourite`.`id` IS NOT NULL
    THEN 1 ELSE 0
END
AS `group_favourite`
,CASE
WHEN
    `product_favourite`.`org_id` != 0
    AND `product_favourite`.`org_id` IS NOT NULL
    THEN 1 ELSE 0
END
AS `favourite`
,CASE
WHEN
    `product_favourite`.`org_id` != 0
    AND `product_favourite`.`org_id` IS NOT NULL
    THEN 1 ELSE 0
END
AS `favourite`,
`product_favourite`.`needs_authorisation`,

 IF(`customer_personal_favourite`.`id` IS NOT NULL,1,0) AS `is_personal_favourite`
 FROM
`products` 

LEFT JOIN `vat_rate` ON `products`.`vat_rate_id` = `vat_rate`.`id`
LEFT JOIN `product_images` ON `products`.`product_image_id` = `product_images`.`id`
LEFT JOIN `product_docs` ON `products`.`product_doc_id` = `product_docs`.`id`

LEFT JOIN `orgs` AS `suppliers` ON `products`.`supplier_id` = `suppliers`.`id`

LEFT JOIN `commission` ON
    `suppliers`.`id` = `commission`.`org_id`
    AND `commission`.`status` = 1
    AND DATE(`commission`.`start_date`) <= DATE('2020-07-15')
    AND (
        DATE(`commission`.`end_date`) >= DATE('2020-07-15')
        OR `commission`.`end_date` = '0000-00-00'
    )

LEFT JOIN `categories` AS `categories` ON
    `products`.`category_id` = `categories`.`id`


LEFT JOIN `categories` AS `sub_categories` ON
    `products`.`sub_category_id` = `sub_categories`.`id`

LEFT JOIN `product_attribute_product` ON
    `product_attribute_product`.`product_id` = `products`.`id`
LEFT JOIN `product_alternative` ON
    `product_alternative`.`product_original_id` = `products`.`original_id`
JOIN `orgs` AS `customer` ON
`customer`.`id` IN (320)

LEFT JOIN `product_supplier_account` ON
`product_supplier_account`.`product_id` = `products`.`id`

JOIN `supplier_account` AS `default_supplier_account` ON
`default_supplier_account`.`supplier_id` = `products`.`supplier_id`
AND `default_supplier_account`.`is_default` = 1
AND `default_supplier_account`.`is_deleted` = 0

JOIN `customer_to_supplier` ON
`customer_to_supplier`.`supplier_id` = `products`.`supplier_id`
AND `customer_to_supplier`.`customer_id` IN (`customer`.`id`)
AND `customer_to_supplier`.`allow_access` = 1
AND `customer_to_supplier`.`status` = 1

JOIN `customer_to_supplier_account` ON
    `customer_to_supplier_account`.`customer_to_supplier_id` = `customer_to_supplier`.`id`
    AND (
        `customer_to_supplier_account`.`supplier_account_id` =                                    `product_supplier_account`.`supplier_account_id`
        OR `customer_to_supplier_account`.`supplier_account_id` = `default_supplier_account`.`id`
    )
    AND `customer_to_supplier_account`.`allow_access` = 1
    AND `customer_to_supplier_account`.`is_deleted` = 0
LEFT JOIN `statement_products_categories` ON
`statement_products_categories`.`product_id` = `products`.`original_id`
LEFT JOIN `product_favourite` ON
    `product_favourite`.`product_id` = `products`.`original_id`
    AND `product_favourite`.`org_id` IN (`customer`.`id`)
    LEFT JOIN `group_favourite` AS `group_favourite` ON
`products`.`original_id` = `group_favourite`.`product_id`
AND `group_favourite`.`group_id` IN (37)
LEFT JOIN `budget` AS `category_budget` ON
    `category_budget`.`org_id` IN (`customer`.`id`)
    AND `category_budget`.`budget_type_id` = 1
    AND `category_budget`.`category_id` = `products`.`category_id`
    AND
        `category_budget`.`start_date` <= CURDATE()
        AND (
            `category_budget`.`final_date` >= CURDATE()
            OR `category_budget`.`final_date` IS NULL
        )
LEFT JOIN `budget` AS `supplier_budget` ON
    `supplier_budget`.`org_id` IN (`customer`.`id`)
    AND `supplier_budget`.`budget_type_id` = 3
    AND `supplier_budget`.`supplier_id` = `products`.`supplier_id`
    AND
        `supplier_budget`.`start_date` <= CURDATE()
        AND (
            `supplier_budget`.`final_date` >= CURDATE()
            OR `supplier_budget`.`final_date` IS NULL
        )
LEFT JOIN `budget` AS `product_budget` ON
    `product_budget`.`org_id` IN (`customer`.`id`)
    AND `product_budget`.`budget_type_id` = 2
    AND
        `product_budget`.`start_date` <= CURDATE()
        AND (
            `product_budget`.`final_date` >= CURDATE()
            OR `product_budget`.`final_date` IS NULL
        )
LEFT  JOIN `customer_personal_favourite` ON
`customer_personal_favourite`.`org_id` = `customer`.`id`
AND `customer_personal_favourite`.`product_original_id` = `products`.`original_id`

WHERE
0 OR (1  AND `products`.`status` = 1  AND ((
    `products`.`parent_id` = 0
    OR `products`.`parent_id` IS NULL
)   OR (
    `product_favourite`.`org_id` IS NOT NULL
    AND `product_favourite`.`org_id` != 0
)  )  AND `products`.`id` = `products`.`current_id` )
GROUP BY `products`.`id`
ORDER BY
`favourite` DESC,

`active_date` ASC,
    `products`.`code` ASC,
    `products`.`name` ASC,
    `products`.`parent_id` ASC

 LIMIT 0, 20

0

sql mysql database optimization large-query

Источник

user12081004 15 июл '20 в 17:35

2 ответа

Другие вопросы по тегам sql mysql database optimization large-query

user1766831 16 июл '20 в 03:36 2020-07-16 03:36 · Answer 1 · 2020-07-16 03:36

      AND  DATE(`commission`.`start_date`) <= DATE('2020-07-15')
      AND  ( DATE(`commission`.`end_date`) >= DATE('2020-07-15')
              OR  `commission`.`end_date` = '0000-00-00'

есть несколько проблем:

ORсложно оптимизировать. Найдите способ избежать этого - например, удалив такие строки или используя дату окончания, которая находится далеко в будущем, а не в прошлом.
Не скрывайте столбец при вызове функции.
Посмотрим SHOW CREATE TABLE.
Тебе не нужно DATE() вокруг буквального "2020-07-15".

С другой стороны, это нормально: product_budget.start_date <= CURDATE ()

ORDER BY `favourite` DESC,
         `active_date` ASC,

Если у вас нет MySQL 8.0, ни один индекс не сможет обработать ORDER BYкоторый смешивает DESC и ASC. (Использование индекса может оказаться невозможным по другим причинам.)

WHERE 0 OR (1  AND ...

Постройте запрос динамически, а не используйте подобные кладжи.

LEFT JOIN ... ON ... foo = 1

ON следует указать, как связаны таблицы. foo = 1 больше похоже на то, что должно быть в WHEREпункт (для фильтрации). Перемещение этого междуON а также WHEREбудет, вероятно, изменить набор строк производится. Но я сомневаюсь, должно ли это бытьJOIN (вместо того LEFT JOIN) или тест должен быть в WHERE, или действительно ли это так, как вы это написали.

Пожалуйста предоставьте EXPLAIN SELECT ... Строки с самыми большими "строками", вероятно, будут таблицами, на которых нужно сосредоточиться.

GROUP BY  `products`.`id`

может привести к искажению вывода. Прочтите про "only_full_group_by".

Это ведет к потенциальному ускорению.

Сделайте минимальный объем работы, чтобы получить списокid чтобы удовлетворить ORDER BY а также LIMIT.
затем JOIN ко всем остальным таблицам, включая обратно в products, чтобы получить другие столбцы. Это, вероятно, ускорит процесс и избавит от вышеуказанной ошибки.

Где практические изменения LEFT JOIN в подзапрос:

 SELECT ...
     `vat_rate`.`rate` AS `vat_rate`,
     ...
 LEFT JOIN  `vat_rate`  ON `products`.`vat_rate_id` = `vat_rate`.`id`
 ...

->

    SELECT ...
        ( SELECT `rate` FROM vat_rate WHERE id = `products`.`vat_rate_id`
        )  AS `vat_rate`,
        ...
    ...

Сделайте большую часть работы, а затем возвращайтесь за дополнительными предложениями.

user8283259 16 июл '20 в 02:50 2020-07-16 02:50 · Answer 2 · 2020-07-16 02:50

Однажды я столкнулся с этой проблемой, и в то время я решил написать об этом статью, и я скомпилировал из разных источников и оптимизировал свой запрос, если кто-то хочет увидеть, как я этого добился. Пожалуйста, взгляните на это. https://junaidtechblog.wordpress.com/2019/09/04/optimize-sql-query-groupby-having/