mysql - MySQL中神秘的 "using_filesort"

Question

这是我的 MySQL 表:

CREATE TABLE IF NOT EXISTS `ank` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL DEFAULT '0',
  `skr` int(11) NOT NULL,
  `sxx` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sxx` (`sxx`,`skr`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `ank`
--

INSERT INTO `ank` (`id`, `uid`, `skr`, `sxx`) VALUES
(1, 34846, 1443865873, 1),
(2, 34847, 1443777739, 1),
(3, 34848, 1443777741, 2),
(4, 34849, 1443777743, 3),
(5, 34850, 1443777745, 0),
(6, 34851, 1443777747, 0);

这个简单的查询就可以了:

explain SELECT id, skr FROM ank force index (sxx) where sxx='1' order by skr DESC limit 0, 30;

+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | ank   | ref  | sxx           | sxx  | 1       | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

但是当我使用 where sxx IN (1, 2, 3) 时而不是sxx='1' ，它总是使用文件排序。我尝试了所有方法 - 在 SQl 语句中包括按列结束排除顺序、包括所有列、不包括所有列、尝试按主列排序，但总是相同的结果:using_filesort.

explain SELECT id, skr FROM ank force index (sxx) where sxx IN (1, 2, 3) order by skr DESC limit 0, 30;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | ank   | range | sxx           | sxx  | 1       | NULL |    3 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+

但最奇怪的是，如果我用 GROUP_CONCAT 连接同一个表，using_filesort消失。

explain SELECT n.id, n.skr, GROUP_CONCAT(f.uid) fll
    -> FROM ank n
    -> force index (sxx)
    -> left join ank f on n.uid=f.uid
    -> where
    -> n.sxx IN (1, 2, 3)
    -> order by n.skr DESC limit 0, 30;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | n     | range | sxx           | sxx  | 1       | NULL |    3 | Using where |
|  1 | SIMPLE      | f     | ALL   | NULL          | NULL | NULL    | NULL |    6 |             |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

我不需要在此查询中加入同一个表。是否有其他方法可以避免 using_filesort当我使用where sxx IN (1, 2, 3)时？

Answer 1

使用文件排序并不像听起来那么糟糕。这意味着 MySQL 通过在对行进行排序之前将行收集到临时数据结构中来满足您的查询。当您使用时

  where sxx='1' order by skr DESC

使用 (sxx,skr) 上的索引可以满足您的整个查询。为什么？查询引擎跳转到索引中正确的位置。首先，它跳转到索引中包含 sxx 值 1 的部分。然后跳转到该部分索引中skr的最高值。然后它逐行遍历索引，值向下。 Shazam:您的结果集出现，并且已经按正确的顺序排列。您的 order by 子句由索引的固有顺序满足。

如果你给出这个子句，MySQL 就不能像这样利用索引

  where sxx IN ('1','2','3') order by skr DESC

甚至这个

 where sxx BETWEEN '1' AND '3' order by skr DESC

相反，它必须 (1) 收集正确的行并 (2) 对它们进行排序。这就是查询规划器说使用文件排序时的含义。现在，如果它还显示使用临时，则意味着收集的行对于 RAM 来说太大，并且排序发生在您的硬盘驱动器上。 这会导致性能问题。

阅读本文，不要担心使用文件排序，除非您遇到很大的性能问题。 https://www.percona.com/blog/2009/03/05/what-does-using-filesort-mean-in-mysql/