optimization - 优化 MySQL 查询

Question

我们有一个查询目前正在杀死我们的数据库，我知道必须有一种方法来优化它。我们有 3 个表:

1. items - 项目表，其中每个项目都有关联的 object_id、长度、difficulty_rating、rating、avg_rating & status
2. lists - 列表表格，基本上是由我们的用户创建的项目列表
3. list_items - 包含 2 列的表:list_id、item_id

我们一直在使用以下查询来显示一个简单的 HTML 表格，该表格显示每个列表和与列表相关的许多属性，包括所包含列表项的属性平均值:

select object_id, user_id, slug, title, description, items, 
       city, state, country, created, updated,
       (select AVG(rating) from items
          where object_id IN 
              (select object_id from list_items where list_id=lists.object_id) 
            AND status="A"
       ) as 'avg_rating',
       (select AVG(avg_rating) from items
          where object_id IN 
              (select object_id from list_items where list_id=lists.object_id) 
            AND status="A"
       ) as 'avg_avg_rating',
       (select AVG(length) from items 
          where object_id IN 
              (select object_id from list_items where list_id=lists.object_id) 
            AND status="A"
       ) as 'avg_length',
       (select AVG(difficulty_rating) from items 
          where object_id IN
              (select object_id from list_items where list_id=lists.object_id) 
            AND status="A"
       ) as 'avg_difficulty' 
    from lists
    where user_id=$user_id AND status="A" 
    order by $orderby LIMIT $start,$step

我们之所以没有在 1 个查询中将其分解以获取所有列表和后续查找以提取每个列表的平均值，是因为我们希望用户能够对平均值列进行排序(即 'order按 avg_difficulty')。

希望我的解释是有道理的。必须有一种更有效的方法来做到这一点，我希望那里的 MySQL 大师可以为我指明正确的方向。谢谢!

Answer 1

看起来你可以用连接替换所有的子查询:

SELECT     l.object_id,
           l.user_id,
           <other columns from lists>
           AVG(i.rating) as avgrating,
           AVG(i.avg_rating) as avgavgrating,
           <other averages>
FROM       lists l
LEFT JOIN  list_items li 
ON         li.list_id = l.object_id
LEFT JOIN  items i 
ON         i.object_id = li.object_id
           AND i.status = 'A'
WHERE      l.user_id = $user_id AND l.status = 'A' 
GROUP BY   l.object_id, l.user_id, <other columns from lists>

这将为数据库引擎节省大量工作。