mysql - 如何优化 MySQL 选择查询或使其更快
问题描述
我有一个选择查询,它一次从 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 秒,但我似乎无法找出优化此查询的最佳方法。
解决方案
该查询对我来说看起来不错。您选择所有数据并汇总其中的一些数据。这需要时间。您的解释计划显示 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
是唯一的)。
推荐阅读
- git - git config 如何在同一台服务器上使用 2 个用户名
- r - R - saemixData 函数在函数环境中找不到数据“文件错误(文件,“rt”):无法打开连接”
- python - 使用 `setup.py` + `pip` 跨平台支持`data_files`
- javascript - AngularJS DateRangePicker CSS不起作用
- oracle - 如何在 oracle sql developer 中选择昨天从 12 AM 到 6 AM 以及从 11 PM 到 12 AM 的记录?
- sql - 将行转换为列,将列转换为没有枢轴的行
- c - strcpy() 函数参数
- asp.net - angular js:ng-class 使用
- apktool - 使用 cmd "java -jar apktool.jar b D:\smali > nul" 获取 apk
- opencv - 改进 python opencv 中特定颜色的圆形检测