如何优化这个MySQL查询？

Question

我有一个存储学生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个状态。是否有更好的方法来编写此查询？是否有更好的索引可帮助查询？请告诉我。

- Philip

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

创建一个变量并存储 NOW() 函数返回的值- 我不确定数据库引擎是否将此调用优化为单个调用，但如果没有，这可能会有所帮助。

这些是一些建议，但您需要比较查询计划并查看是否有任何显着的改进。 根据查询计划中索引的使用情况，robob上面的建议也可能会很有用。

- Jagmag

---

谢谢！我没有意识到WHERE...IN和INNER JOIN之间会有如此惊人的差异。对于10000名学生的200000个状态，查询返回时间为0.08秒，而我的上面的查询需要超过5分钟（然后我就等不及了）。我没有改变键，所以不确定是否必要。 - Philip

Answer 2

了解在系统加载每个学生具有15-20个状态的情况下，查询需要多长时间。

仅在查询时间过长时进行重构。

- jeffo

---

看起来太明智了;-)我会快速生成一些随机数据，看看会发生什么... - Philip