我怎样才能优化这个MySQL查询？

Question

我有一个表，其中存储pupil_id，类别和生效日期（除其他事项外）。日期可以是过去，现在或未来。我需要一个查询来从表格中提取学生的当前状态。

以下查询的工作原理：

SELECT * 
FROM pupil_status 
WHERE (status_pupil_id, status_date) IN (
    SELECT status_pupil_id, MAX(status_date) 
    FROM pupil_status 
    WHERE status_date < NOW() -- to ensure we ignore the "future status" 
    GROUP BY status_pupil_id); 

在MySQL，该表被定义如下：

CREATE TABLE IF NOT EXISTS `pupil_status` (
    `status_id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `status_pupil_id` int(10) unsigned NOT NULL, -- a foreign key 
    `status_category_id` int(10) unsigned NOT NULL, -- a foreign key 
    `status_date` datetime NOT NULL, -- effective date/time of status change 
    `status_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    `status_staff_id` int(10) unsigned NOT NULL, -- a foreign key 
    `status_notes` text NOT NULL, -- notes detailing the reason for status change 
    PRIMARY KEY (`status_id`), 
    KEY `status_pupil_id` (`status_pupil_id`,`status_category_id`), 
    KEY `status_pupil_id_2` (`status_pupil_id`,`status_date`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1409 ; 

然而，随着950级的学生和刚刚超过1400点的状态在表中，查询采用0.185秒来处理。现在也许可以接受，但是当桌子膨胀时，我担心可扩展性。生产系统可能会有超过10000名学生，每个学生都有15-20个状态。

有没有更好的方法来写这个查询？是否有更好的索引来帮助查询？请告诉我。

Answer 1

有以下几件事你可以尝试

1使用INNER JOIN代替WHERE

SELECT * 
FROM pupil_status ps 
INNER JOIN 
    (SELECT status_pupil_id, MAX(status_date) 
    FROM pupil_status 
    WHERE status_date < NOW() 
    GROUP BY status_pupil_id) X 
ON ps.status_pupil_id = x.status_pupil_id 
AND ps.status_date = x.status_date 

2具有可变和存储的值NOW（） - 我不知道数据库引擎将NOW（）的呼叫优化为只有一个呼叫，但是如果不呼叫，则可能会有所帮助

这些是一些建议，但您需要比较查询计划并查看是否有任何可观的改进或不。 根据您对查询计划的索引使用情况，上面的robob的建议也可以派上用场

Answer 2

查看当您使用每个具有15-20个状态的10000个学生加载系统时查询需要多长时间。

只有在需要太长时间的情况下才会重构。