MySQL使用文件排序的慢查询 [英] MySQL slow query using filesort

问题描述

我在使用MySQL查询时遇到速度问题.这些表的定义如下:

I have speed problems with a MySQL query. The tables definitions are as follows:

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对结果进行排序.我有一个关于countup和updated_time的综合索引.我想摆脱文件排序的问题，但是找不到令人满意的方法.

The Student table has got about 500,000 records and the college table has 915 rows. A third table is used to hold all the locations of the colleges. My query needs to retrieve all the students for a particular location and then sort the results by countup and updated_time. I have a composite index on countup and updated_time. I would like to get rid of the filesort but I have not been able to find a satisfactory method.

我已经考虑过将college_location移到学生表中，以便可以将其组合为一个复合索引.有更好的解决方案吗?

I have considered moving the college_location into the student table so that it can be combined into a composite index. Is there a better solution?

推荐答案

以下查询将删除使用临时"；使用文件排序.从解释中可以看出，这样应该更有效.

Query below will remove Using temporary; Using filesort. from the explain so this should run better in thoery..

MySQL优化器是愚蠢的，所以诀窍是强制优化器想要您想要的，这是基于college.college_location = 1的派生表. 因此，您可以使用student表对结果进行INNER JOIN. 这样，MySQL可以使用排序键

MySQL optimizer is dumb so the trick is to force the optimizer want you want and that is an derived table based on college.college_location = 1. So you can INNER JOIN the result with the student table. And this way MySQL can use the sort key

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

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

Or you can use this if you think it makes more sense or is easier to read. The performance should be the same because the explain explained to me that it is the same.

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

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

New strategy divide and conquer method Fire more querys to the database what will make use off correct indexes. And remove the need for an temporary table and filesort.

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

这篇关于MySQL使用文件排序的慢查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！