mysql - 如何优化 MySQL 选择查询或使其更快

Question

我有一个选择查询，它一次从 MySQL 5.5 数据库中选择超过 50k 条记录，而且这个数量预计会增长。该查询包含多个子查询，执行时间超过 120 秒。

最初，一些 sale_items 和 stock 表没有比 ID 键更多的信息，所以我添加了一些:

SELECT
  `p`.`id` AS `id`,
  `p`.`Name` AS `Name`,
  `p`.`Created` AS `Created`,
  `p`.`Image` AS `Image`,
  `s`.`company` AS `supplier`,
  `s`.`ID` AS `supplier_id`,
  `c`.`name` AS `category`,
  IFNULL((SELECT
    SUM(`stocks`.`Total_Quantity`)
  FROM `stocks`
  WHERE (`stocks`.`Product_ID` = `p`.`id`)), 0) AS `total_qty`,
  IFNULL((SELECT
    SUM(`sale_items`.`quantity`)
  FROM `sale_items`
  WHERE (`sale_items`.`product_id` = `p`.`id`)), 0) AS `total_sold`,
  IFNULL((SELECT
    SUM(`sale_items`.`quantity`)
  FROM `sale_items`
  WHERE ((`sale_items`.`product_id` = `p`.`id`) AND `sale_items`.`Sale_ID` IN (SELECT
    `refunds`.`Sale_ID`
  FROM `refunds`))), 0) AS `total_refund`
  FROM ((`products` `p`
    LEFT JOIN `cats` `c`
      ON ((`c`.`ID` = `p`.`cat_id`)))
    LEFT JOIN `suppliers` `s`
      ON ((`s`.`ID` = `p`.`supplier_id`)))

这是解释结果

+----+--------------------+------------+----------------+------------------------+------------------------+---------+---------------------------------
| id | select_type        | table      | type           | possible_keys          | key                    | key_len | ref     | rows  | Extra                    |
+----+--------------------+------------+----------------+------------------------+------------------------+---------+---------------------------------
|  1 | PRIMARY            | <derived2> | ALL            | NULL                   | NULL                   | NULL    | NULL      | 20981 |                          |
|  2 | DERIVED            | p          | ALL            | NULL                   | NULL                   | NULL    | NULL      | 20934 |                          |
|  2 | DERIVED            | c          | eq_ref         | PRIMARY                | PRIMARY                | 4       | p.cat_id      |     1 |                          |
|  2 | DERIVED            | s          | eq_ref         | PRIMARY                | PRIMARY                | 4       | p.supplier_id |     1 |                          |
|  5 | DEPENDENT SUBQUERY | sale_items  | ref            | sales_items_product_id | sales_items_product_id | 5       | p.id   |    33 | Using where              |
|  6 | DEPENDENT SUBQUERY | refunds    | index_subquery | IDX_refunds_sale_id    | IDX_refunds_sale_id    | 5       | func    |     1 | Using index; Using where |
|  4 | DEPENDENT SUBQUERY | sale_items  | ref            | sales_items_product_id | sales_items_product_id | 5       | p.id   |    33 | Using where              |
|  3 | DEPENDENT SUBQUERY | stocks     | ref            | IDX_stocks_product_id  | IDX_stocks_product_id  | 5       | p.id    |     1 | Using where              |
+----+--------------------+------------+----------------+------------------------+------------------------+---------+---------------------------------

我希望查询最多花费不到 3 秒，但我似乎无法找出优化此查询的最佳方法。

Answer 1

查询看起来不错。您选择所有数据并聚合其中的一些数据。这需要时间。您的解释计划显示 ID 上有索引，这很好。乍一看，我们似乎无能为力...

不过，您可以做的是提供覆盖索引，即包含您需要的表中所有列的索引，以便可以直接从索引中获取数据。

create index idx1 on cats(id, name);
create index idx2 on suppliers(id, company);
create index idx3 on stocks(product_id, total_quantity);
create index idx4 on sale_items(product_id, quantity, sale_id);

这确实可以提高您的查询。

您可以尝试什么 关于查询本身是将子查询移动到 FROM 子句。 MySQL 的优化器不是很好，所以虽然它应该得到相同的执行计划，但它很可能偏爱 FROM 子句。

SELECT
  p.id,
  p.name,
  p.created,
  p.image,
  s.company as supplier,
  s.id AS supplier_id,
  c.name AS category,
  COALESCE(st.total, 0) AS total_qty,
  COALESCE(si.total, 0) AS total_sold,
  COALESCE(si.refund, 0) AS total_refund
FROM products p
LEFT JOIN cats c ON c.id = p.cat_id
LEFT JOIN suppliers s ON s.id = p.supplier_id
LEFT JOIN
(
  SELECT SUM(total_quantity) AS total
  FROM stocks 
  GROUP BY product_id
) st ON st.product_id = p.id
LEFT JOIN
(
  SELECT
    SUM(quantity) AS total,
    SUM(CASE WHEN sale_id IN (SELECT sale_id FROM refunds) THEN quantity END) as refund
  FROM sale_items
  GROUP BY product_id
) si ON si.product_id = p.id;

(如果 sale_id 在 refunds 中是唯一的，那么您甚至可以将它加入到 sale_items 中。再说一遍:这通常不会有什么不同，但在 MySQL 中它可能仍然存在。MySQL 曾经臭名昭着的处理 IN 子句比 FROM 子句差很多。现在可能不是这样了，我不知道. 您可以尝试 - 如果 refunds.sale_id 是唯一的)。