SQL性能

Question

我有一个可以工作的SQL语句，但处理起来需要很长时间

我有一张a_log表和一张people表。我需要在people表中找到最后一个活动以及与给定person的每个id相关联的用户。

SELECT p.`id`, activity, appphone, appname, dateadd 
FROM people p
LEFT JOIN a_log a
ON p.id = a.id 
WHERE (  dateadd >= '2011/09/13 00:00' and dateadd <= '2011/09/13 23:59') 
AND (a.date_time = (SELECT MAX(a1.date_time)
       FROM activity_log a1
       WHERE a.id = a1.id
       GROUP BY id) OR date_time IS NULL)
    ORDER BY `id` desc limit 0, 100

我对a_log表中的date_time字段和id字段有一个非唯一索引，对people表中的dateadd字段有一个主索引和一个非唯一索引

如何才能缩短此查询的执行时间？谢谢

Answer 1

这对你来说应该是可行的：

SELECT p.`id`, p.activity, p.appphone, p.appname, p.dateadd, a.*
FROM people p
LEFT OUTER JOIN
(
    SELECT a.id, a.date_time, a.user  
    FROM activity_log a 
    INNER JOIN 
    (
        SELECT id, MAX(date_time) as date_time
                    FROM activity_log 
                    GROUP BY id
    ) a1  ON a.id = a1.id and a.date_time = a1.date_time
) a ON p.id = a.id 
WHERE (  dateadd >= '2011/09/13 00:00' and dateadd <= '2011/09/13 23:59'   and `agent_or_underwriter` != 'agent') 
ORDER BY p.id desc limit 0, 100

Answer 2

在较高的级别(不知道表的模式、索引、RDBMS、磁盘结构等)。

我会尝试通过运行两个查询来删除子查询(一个用于获取最大日期，另一个用于运行外部查询)。

我也会(正如马克提到的)，看一下执行计划(使用mysql use Explain：http://dev.mysql.com/doc/refman/5.5/en/explain-output.html )，这应该会让你站在正确的起点上开始优化。

如果你尝试这两点，你应该能处于一个更好的位置。

Answer 3

尝试使用派生表而不是子查询。应该会让事情变得更快。

SELECT p.id, activity, appphone, appname, dateadd  
FROM   people p 
       LEFT JOIN a_log a 
       LEFT JOIN (
           SELECT ID, MAX(date_time) MaxDate 
             FROM activity_log
             GROUP BY ID
       ) a1
          ON a.ID = a1.ID
WHERE  a.date_time IS NULL OR a.date_time = ISNULL(a1.MaxDate, a.date_time) --??
ORDER BY id desc limit 0, 100