MySQL - 如何优化这个查询？

Question

以下查询可以正常工作，但在10条记录时非常缓慢（2秒）。分析显示它正在创建一个临时表，但我不确定为什么。

基本上，我将当前用户与acl组连接起来，以获取他们所在的所有组，然后将这些组连接到公司，以获取他们所在的所有公司，然后将这些公司连接到订单，以获取所有订单。

如果我删除此行：

ORDER BY orders.created_on DESC 

然后查询在0.06秒内执行（超出可接受范围）...

帮忙想想如何进行优化？非常感谢:)

SELECT
    orders.uuid,
    companies.name as company_name
FROM
    users u
JOIN    
    users_acl_groups g on   u.uuid = g.user_uuid
JOIN
    users_acl acl on (acl.user_uuid = u.uuid or acl.group_uuid = g.group_uuid) 
JOIN 
    companies on acl.item_uuid = companies.uuid
JOIN
    orders on companies.uuid = orders.company_uuid
WHERE
    u.uuid = 'DDEC8073-5056-C000-01ED583A51CBCA32' and orders.status <> ''
ORDER BY orders.created_on DESC 

limit 0, 10;

更新，查询的解释...

1 SIMPLE orders 全部扫描 9403 使用临时表; 使用文件排序

1 SIMPLE acl 全部扫描 1859 使用where语句; 使用连接缓存

1 SIMPLE g 全部扫描 2005 使用where语句; 使用连接缓存

1 SIMPLE companies 等值连接 主键 PRIMARY 52 table.orders.company_uuid 1

1 SIMPLE u 全部扫描 33595 使用where语句; 去重; 使用连接缓存

- Brett

---

修改了您的标题，因为猜测它的语气过于命令和苛刻，当您的问题措辞得体时，这可能会导致您的投票下降。 - Matt Mitchell

尝试使用应用于除UID以外的任何列的连接来使用相同的查询。尝试使用int、float、string和UID进行操作，并注意时间。如果发现任何变化，请告诉我们。 - Shantanu Gupta

你在 orders.created_on 上有索引吗？在你的查询中展示 EXPLAIN 的输出。也许你可以从其他索引中受益。哪个 EXPLAIN 告诉我们这一点。 - nos

是的，EXPLAIN 可以帮助解释查询执行计划。看起来，如果没有 ORDER BY，查询会从用户开始，逐步连接小表直到订单；而有了 ORDER BY 后，则需要先进行大量的笛卡尔积操作，连接订单、公司、acl（也许是 tmp 表的内容），最后再与筛选后的用户表进行连接。 - pascal

users.uuid 是否被索引了？（可能是唯一索引）很奇怪查询没有尝试减少较大的表... - pascal

Answer 1

你考虑过采用“事实表”式设计作为去范式步骤吗？

基本上，这是一种多对多的交汇表，例如：

CREATE TABLE user_order_fact (
  user_uuid ...
  order_uuid ...
  order_created_on ...
  order_status ...
  company_name ...,
  primary key (user_uuid, order_uuid),
  key (user_uuid, order_status, order_created_on, order_uuid, company_name)
);

... fill with data ...

SELECT
    order_uuid,
    company_name
FROM
    user_order_fact
WHERE
    user_uuid = 'DDEC8073-5056-C000-01ED583A51CBCA32' and order_status <> ''
ORDER BY order_created_on DESC 

limit 0, 10;

我猜测这是复合索引。你需要不断试验直到找到正确的方法。基本上，你需要让优化器计划报告它正在使用索引。

当然，这种存储方式会导致数据冗余和非规范化，因此你需要设置一些触发器来保持与规范化表的同步。

- Bill Karwin

---

嗯，也许PK只是order_uuid。我不能保证这是最好的设计，只是想让你感受一下我的意思。 - Bill Karwin

Answer 2

一些想法：

您实际上没有在查询中选择 orders.created_on。因此，在该列上进行排序没有意义。也许，选择它（SELECT orders.created_on ...）会提高性能（只是猜测 - 我不知道我在这里说什么）。

如果您的查询返回的记录不是非常庞大，您总是可以在应用程序中排序。

有时更明智的做法是使用 N 个小查询而不是 1 个大的 SQL 查询。 伪代码：

user_id = get_one("SELECT uuid FROM users WHERE ...");
group_ids = get_many("SELECT uuid FROM groups WHERE user_uuid = " + user_id);
comps_ids = get_many("SELECT DISTINCT item_uuid FROM acls WHERE user_uuid = " + user_id + " OR group_uuid IN " + groups_ids.to_q());
orders = get_many("SELECT * FROM orders WHERE company_uuid IN " + comps_ids.as_q() + " WHERE status <> '' ORDER BY created_on");

- petraszd

Answer 3

确保 "orders.created_on" 有一个索引... 如果有的话，那么 Bill 在顶部的方法将是最好的，但需要一些工作。

- Nick

---

我认为是这样的？关键字created_on(created_on)。 - Brett

Answer 4

不了解现有索引或每个表的数量，很难回答。

此外，如果没有关于模型的更多信息...查询是否返回所有结果？

所有用户都属于一个组吗？似乎不是...并且查询不会返回组外的用户。

一个组可以属于另一个组，需要递归查询吗？

- pascal

---

我一直在尝试解决那个递归查询的问题，但是运气不太好 :( 表格很小，总共只有10,000条记录。目前没有索引。 - Brett

@Brett：在这种情况下，如果存在索引，它们将不起作用，因为NOT运算符和Like运算符不使用索引。 - Shantanu Gupta

Answer 5

我不确定为什么需要2秒钟，这个查询只获取10条记录，但是我在这里看到的是：

1. acl.user_uuid = u.uuid or acl.group_uuid = g.group_uuid

   基于UID的连接，可能您也将其用作主键，如上所述。

2. ORDER BY orders.created_on。在日期上使用Order by不如使用PK或任何整数值更为适宜。

3. orders.status <> '' 如果在表上使用任何索引，则此查询中不能使用索引，因为NOT运算符和Like运算符在任何查询中使用时都不使用索引。

4. 表中存在的记录数量可能是另一个原因，但仅由于上述因素。否则，它也可以处理大容量。

我认为最重要的贡献因素是在连接中使用UID，因此您的查询中可以看到所有三个避免条件，这可能会使您的查询变慢。

- Shantanu Gupta

---

你好，谢谢你的回复。在第2条中，minimized是什么意思？ - Brett