使用文件排序的MySQL慢查询

Question

我在使用 MySQL 查询时遇到了速度问题。表格定义如下:

CREATE TABLE IF NOT EXISTS `student` (
  `student_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `forename` varchar(30) NOT NULL,
  `updated_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `surname` varchar(50) NOT NULL,
  `student_college` int(11) DEFAULT NULL,
  `countup` smallint(5) unsigned DEFAULT NULL, 
  PRIMARY KEY (`student_id`),
  KEY `countup` (`countup`),
  KEY `student_sort` (`countup`,`updated_time`),
  KEY `student_college` (`student_college`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

和

CREATE TABLE IF NOT EXISTS `college` (
  `college_id` int(11) NOT NULL AUTO_INCREMENT,
  `college_name` varchar(100) NOT NULL DEFAULT 'Centre Name',
  `college_location` int(11) DEFAULT NULL,
  PRIMARY KEY (`college_id`),
  KEY `college_location` (`college_location`),
  KEY `college_name` (`college_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询如下:

SELECT *
FROM student
JOIN college ON student.student_college = college.college_id
WHERE  
college_location = 1
ORDER BY student.countup desc, student.updated_time desc
LIMIT 15;

我得到以下解释:

id select_type table   type possible_keys              key              key_len    ref                           rows Extra
1  SIMPLE      college ref  "PRIMARY,college_location" college_location 5          const                         915  Using where; Using temporary; Using filesort
1  SIMPLE      student ref  student_college            student_college  5          speed_test.college.college_id 50   Using where

Student 表有大约 500,000 条记录，college 表有 915 行。第三张表用于保存学院的所有位置。我的查询需要检索特定位置的所有学生，然后按计数和更新时间对结果进行排序。我有一个关于 countup 和 updated_time 的复合索引。我想摆脱文件排序，但一直找不到满意的方法。

我考虑过将 college_location 移动到 student 表中，以便将其组合成一个复合索引。有更好的解决方案吗？

Answer 1

下面的查询将删除 Using temporary；使用文件排序。从解释来看，这应该在理论中运行得更好..

MySQL 优化器是愚蠢的，所以诀窍是强制优化器想要你想要的，这是一个基于 college.college_location = 1 的派生表。所以你可以 INNER JOIN 结果与学生表。这样MySQL就可以使用排序键

SELECT 
 *
FROM 
 student
INNER JOIN (
    SELECT 
     college_id
    FROM 
     college
    WHERE
     college.college_location = 1  
  ) college
ON student.student_college = college.college_id
ORDER BY
    student.countup DESC
  , student.updated_time DESC

注意大写锁定的新索引

查看演示 http://sqlfiddle.com/#!2/05c8a/1

或者，如果您认为它更有意义或更易于阅读，您可以使用它。性能应该是相同的，因为解释向我解释说它是相同的。

SELECT 
 * 
FROM (
  SELECT 
    college_id
  FROM 
    college
  WHERE
    college.college_location = 1  
) 
  college

INNER JOIN
 student 

ON
 student.student_college = college.college_id

ORDER BY
    student.countup DESC
  , student.updated_time DESC

查看演示 http://sqlfiddle.com/#!2/05c8a/23

新的策略分治法向数据库发出更多查询，这将使用正确的索引。并消除对临时表和文件排序的需要。

SET @college_ids = NULL; 

SELECT
  GROUP_CONCAT(college_id)
FROM
  college
WHERE
  college_location = 1
GROUP BY
  college_location ASC
INTO @college_ids;

SELECT 
 *
FROM 
 student
WHERE 
 student.student_college IN(@college_ids)
ORDER BY
    student.countup DESC
  , student.updated_time DESC
;

查看演示 http://sqlfiddle.com/#!2/454b3/61