mysql - 优化具有大量数据的 SQL 查询

Question

首先:我寻找过类似的问题，但没有发现任何东西可以解释为什么我所做的事情是不好的做法，所以我发布了一个新问题，即使这是非常基本的东西。

我收到了一些投诉，称我给数据库带来了太大的压力，但我不知道如何优化，因此我在这里寻求帮助。

我在底部附上了我正在处理的结构的图片。我想做的是:我需要获取已执行特定 userAction 的人员列表(假设为“viewProduct”，然后添加有关该人员的用户年龄、国家/地区和大洲的信息。

我写了以下内容:

select  u.userId, u.userAge, c.countryName, co.name, ul.createdTime,
        ul.userAction
    from  user_log as ul
    left join  user as u  ON u.userId = ul.userId
    left join  country as c  ON c.id = u.userCountryId
    left join  continent as co  ON co.id = c.continentId
    where  ul.createdTime > '2016-06-01'
      and  u.userAge > 40
      and  (ul.userAction like 'viewProduct'
              or  ul.userAction like 'storeProduct'
              or  ul.userAction like 'addProduct'
           );

这显然还不够好，所以我真诚地希望有人能够帮助我们提高效率。我不太习惯处理条目数以亿计的表。

提前谢谢您!

db structure

Answer 1

查询中有几个问题:

1. 如果您没有限制且没有索引 - MySQL 将检查 user_log 中的所有 100000000 条记录以给出结果。要减少处理的记录量，请将索引添加到“ul.createdTime”和“ul.userAction”。这甚至可以通过组合索引(createdTime、userAction)来实现。
2. “ul.userAction like 'viewProduct'”等于“ul.userAction = 'viewProduct'”。整个构造可以重构为“ul.userAction in ('viewProduct', 'storeProduct', 'addProduct')”
3. 将“u.userAge > 40”移至连接条件。而且“u.userAge”上的索引也会有很大帮助。
4. 我不确定，这取决于您的数据，但有时“inner join user as u”可能比“left join user as u”工作得更快。所以，你可以在这里玩。 (但是查询逻辑会改变)

向包含 100000000 条记录的表添加索引是一项漫长的任务。因此，出于测试目的，我将仅复制最后 1000000 条记录的“user_log”并测量其性能。然后我将添加所有提到的索引并测试此查询:

select u.userId, u.userAge, c.countryName, co.name, ul.createdTime,     ul.userAction 
from user_log as ul
left join user as u
    on u.userId = ul.userId and u.userAge > 40
left join country as c
    on c.id = u.userCountryId
left join continent as co
on co.id = c.continentId
where ul.createdTime > '2016-06-01' and 
ul.userAction in('viewProduct', 'storeProduct', 'addProduct');

..并比较性能。

请告诉我们，这对您有帮助吗？