mysql - 慢查询检索最近的记录

Question

我在从历史表中检索最新记录时遇到困难。我目前正在处理检索的一部分。最终结果将是最新历史记录条目与客户表的连接。

客户表是:

CREATE TABLE Clients
 (
        id                      int(8) auto_increment not null primary key, 
        LastName                varchar(50), 
        First                  varchar(50),
        CanContact             TINYINT(1)
);

客户历史记录(约 300,000 条记录):

CREATE TABLE ClientHistory
(
        id                      int(8) auto_increment not null primary key, 
        Client                  int(8),
        Act_Date                datetime,
        Activity                int(8)
);
create index idx_ClientHistory_Client on ClientHistory(Client);
create index idx_ClientHistory_Act_Date on ClientHistory(Act_Date);

问题 1:按客户从 ClientHistory 组中选择 id、MAX(Act_Date)、注释。这会检索最新的日期，但 id 和 Notes 实际上来自最旧的记录。

mysql> select id, Client, Act_Date, Activity from ClientHistory where client=18176;
+--------+--------+---------------------+----------+
| id     | Client | Act_Date            | Activity |
+--------+--------+---------------------+----------+
| 346725 |  18176 | 2015-09-04 09:29:35 |        4 |
| 346726 |  18176 | 2015-09-04 09:29:39 |        3 |
| 346727 |  18176 | 2015-09-04 09:44:29 |        4 |
| 346728 |  18176 | 2015-09-04 09:44:36 |        3 |
| 346737 |  18176 | 2015-09-04 10:35:10 |        4 |
| 346739 |  18176 | 2015-09-04 10:49:43 |        4 |
| 346740 |  18176 | 2015-09-04 10:54:57 |       29 |
| 346787 |  18176 | 2015-09-04 15:37:15 |       23 |
| 346788 |  18176 | 2015-09-04 15:37:20 |       23 |
| 346789 |  18176 | 2015-09-04 15:37:23 |       23 |
| 346790 |  18176 | 2015-09-04 15:37:27 |       23 |
| 346791 |  18176 | 2015-09-04 15:37:30 |       23 |
| 346792 |  18176 | 2015-09-04 15:37:34 |       20 |
| 346793 |  18176 | 2015-09-04 15:37:51 |       13 |
| 346798 |  18176 | 2015-09-04 15:50:51 |       25 |
| 346808 |  18176 | 2015-09-04 16:10:55 |       13 |
+--------+--------+---------------------+----------+
16 rows in set (0.00 sec)

mysql> select id, Client, Act_Date, MAX(Act_Date), Activity from ClientHistory where client=18176;
+--------+--------+---------------------+---------------------+----------+
| id     | Client | Act_Date            | MAX(Act_Date)       | Activity |
+--------+--------+---------------------+---------------------+----------+
| 346725 |  18176 | 2015-09-04 09:29:35 | 2015-09-04 16:10:55 |        4 |
+--------+--------+---------------------+---------------------+----------+
1 row in set (0.00 sec)

mysql> select id, Client, Act_Date, Activity from ClientHistory where client=18176 group by Client order by Act_Date desc;
+--------+--------+---------------------+----------+
| id     | Client | Act_Date            | Activity |
+--------+--------+---------------------+----------+
| 346725 |  18176 | 2015-09-04 09:29:35 |        4 |
+--------+--------+---------------------+----------+
1 row in set (0.00 sec)

问题 2 下面的查询非常慢(约 6 秒):

select * from Clients
LEFT JOIN ( 
    select c1.* from ClientHistory as c1
        LEFT JOIN ClientHistory as c2 on c1.client = c2.client and c1.Act_Date < c2.Act_Date where c2.Client is null
        ) as ctemp on Clients.id = ctemp.Client
        where CanContact=true order by LastName, First;

罪魁祸首是:

select c1.* from ClientHistory as c1
        LEFT JOIN ClientHistory as c2 on c1.client = c2.client and c1.Act_Date < c2.Act_Date where c2.Client is null;
And the explain:
explain select c1.* from ClientHistory as c1 LEFT JOIN ClientHistory as c2 on c1.client = c2.client and c1.Act_Date < c2.Act_Date where c2.Client is null;
+----+-------------+-------+------+-----------------------------------------------------+--------------------------+---------+-----------+--------+-------------+
| id | select_type | table | type | possible_keys                                       | key                      | key_len | ref       | rows   | Extra       |
+----+-------------+-------+------+-----------------------------------------------------+--------------------------+---------+-----------+--------+-------------+
|  1 | SIMPLE      | c1    | ALL  | NULL                                                | NULL                     | NULL    | NULL      | 330120 | NULL        |
|  1 | SIMPLE      | c2    | ref  | idx_ClientHistory_Client,idx_ClientHistory_Act_Date | idx_ClientHistory_Client | 5       | c1.Client |      8 | Using where |
+----+-------------+-------+------+-----------------------------------------------------+--------------------------+---------+-----------+--------+-------------+
2 rows in set (0.00 sec)

我很困惑。

Answer 1

问题1的答案:

SELECT id, MAX(Act_Date), Notes from ClientHistory group by Client

不符合 ANSI 标准。您必须列出在 GROUP BY 子句中未应用函数的所有列

在这种情况下，可能会返回多于 1 条记录，因为单个客户端的 MAX(Act_Date) 可能是多个。如果您的数据不是这种情况，您可以使用:

SELECT Client,MAX(id), MAX(Act_Date) from ClientHistory group by Client

Notes 是一个更难检索的字段，但现在您有了 id，您可以通过 id 将表重新连接起来来检索它。