使用mysql查询大表

Question

我管理一家房地产网站。我有一个被禁止的用户表(小表)和一个名为advert_views的表，它跟踪每个用户查看的每个列表(目前是130万行，而且还在增长)。advert_views表还记录了观看的每个广告的IP地址)。

我想获得被禁止的用户使用的IP地址，并检查这些被禁止的用户中是否有任何人开设了新帐户。我运行了以下查询：

SELECT adviews.user_id AS 'banned user_id', 
       adviews.client_ip AS 'IPs used by banned users', 
       adviews2.user_id AS 'banned users that opened a new account'
FROM banned_users
LEFT JOIN users on users.email_address = banned_users.email_address  #since I don't store the user_id in banned_users
LEFT JOIN advert_views adviews ON adviews.user_id = users.id AND adviews.user_id IS NOT NULL # users may view listings when not logged in but they have restricted access to the information on the listing
LEFT JOIN (SELECT client_ip,
                  user_id 
                  FROM advert_views 
                  WHERE user_id IS NOT NULL   
                ) adviews2 
                ON adviews2.client_ip = adviews.client_ip
WHERE banned_users.rec_status = 1 and adviews.user_id <> adviews2.user_id
GROUP BY adviews2.user_id

我在advert_views表和users表上应用了一个索引，如下所示：

enter image description here

我的查询需要半小时才能执行。有没有办法提高我的查询速度？

谢谢!克里斯

Answer 1

首先:为什么要从外部连接表？或者更好:为什么要尝试外部连接表？左连接意味着即使在没有匹配的情况下也可以从表中获取数据。但是，您的结果可能包含所有值都为null的行。(但这不会发生，因为where子句中的adviews.user_id <> adviews2.user_id会忽略所有外部联接的行。)不要让DBMS做不必要的工作。如果你想要内部连接，那么就不要使用外部连接。(尽管执行时间的差异不会很大。)

下一步:选择from banned_users，但仅用于检查是否存在。你不应该这么做。请改用EXISTS或IN子句。(这主要是为了可读性和不产生重复的结果。这可能不会加快速度。)

SELECT av1.user_id AS 'banned user_id', 
       av2.client_ip AS 'IPs used by banned users', 
       av2.user_id AS 'banned users that opened a new account'
FROM adviews av1
JOIN adviews av2 ON av2.client_ip = av1.client_ip AND av2.user_id <> av1.user_id
WHERE av1.user_id IN 
(
  SELECT user_id 
  FROM users 
  WHERE email_address IN (select email_address from banned_users where rec_status = 1)
)
GROUP BY av2.user_id;

您可以将内部IN子句替换为联接。这在很大程度上是个人偏好的问题，但也是因为在过去，MySQL有时在IN子句上表现不佳，所以很多人都习惯了加入。

WHERE av1.user_id IN 
(
  SELECT u.user_id 
  FROM users u
  JOIN banned_users bu ON bu.email_address = u.email_address
  WHERE bu.rec_status = 1
)

最后，考虑删除GROUP BY子句。它将您的结果减少到每次重用user_id一行，显示其相关的禁用user_ids之一(如果有多个，可任意选择)。我不知道你们的桌子。您是否每次重用user_id都会获得很多记录？如果没有，请删除该子句。

关于索引，我建议：

• banned_users(rec_status，email_address)
• users(email_address，user_id)
• adviews(user_id，client_ip)
• adviews(client_ip，user_id)