如果我选择多个列，Mysql的EXPLAIN显示'using filesort‘，如果没有选择，则显示'using index’

Question

我有一个类似于下面的表(为了便于解释，我删除了很多字段)。orderId是一个整型的非唯一值，用于显示行的重要性(返回数据时按其排序)

CREATE TABLE `my_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orderId` int(11) NOT NULL,
  `slug` varchar(50) NOT NULL,
  `imageFilename` varchar(255) NOT NULL,
  `thumb200` varchar(255) NOT NULL,
  `thumb600` varchar(255) NOT NULL,
  `md5hash` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `orderId` (`orderId`),
  KEY `thumb600` (`thumb600`),
  KEY `slug` (`slug`),
  KEY `multiple_1` (`md5hash`,`orderId`,`thumb200`,`thumb600`),
  KEY `md5hash` (`md5hash`),
  KEY `thumb200` (`thumb200`),
  KEY `thumb200_2` (`thumb200`,`orderId`),
  KEY `orderId_2` (`orderId`,`thumb600`,`thumb200`),
  KEY `thumb600_2` (`thumb600`,`orderId`),
  KEY `thumb600_3` (`thumb600`,`orderId`),
  KEY `orderId_3` (`orderId`,`thumb600`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

是的--有很多关于orderId的索引。这是一种尝试修复当前问题的混合方法，也是因为我在WHERE语句中使用了这些字段。

无论如何，如果我运行这个命令：

explain SELECT id FROM my_images
 ORDER BY orderId asc

它输出这个(“使用索引”)

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  my_images   index   NULL    orderId 4   NULL    174553  Using index

这是意料之中的- orderId是一个索引。

但如果我这样做：

explain SELECT id,   thumb600, imageFilename,  slug FROM my_images
 ORDER BY orderId asc

它输出以下内容(使用filesort)：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  my_images   ALL NULL    NULL    NULL    NULL    174553  Using filesort

奇怪的是，如果我这样做

explain SELECT id,  md5hash  FROM my_images
 ORDER BY orderId asc

上面写着“使用索引；使用文件排序”

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  my_images   index   NULL    multiple_1  775 NULL    174553  Using index; Using filesort

我注意到的原因是因为我看到了一个很慢的查询，基本上是‘select (一堆字段) order by orderId limit 40003’，这花费了很长时间，在运行explain命令之后看到了这一点。如何才能使其始终使用索引？我必须创建一个包含我选择的所有字段的索引吗？如果是这样，那么索引中字段的顺序是否重要？

Answer 1

如果选择索引未覆盖的列，引擎将不得不进行表查找以获取这些列的值。

由于您无论如何都要选择整个表(而不是使用WHERE或LIMIT)，因此顺序扫描该表，然后对其进行排序比进行大量的键查找要快得多。

此外，MySQL不能执行后期行查找，这意味着它总是在计算偏移量之前先查找表，即使在使用索引时也是如此。

如果您是按InnoDB表中的非主键字段排序，请尝试如下操作：

SELECT  i.*
FROM    (
        SELECT  id
        FROM    my_images
        ORDER BY
                orderId
        LIMIT   4000, 3
        ) q
JOIN    my_images i
ON      i.id = q.id

请参阅我的博客中的这篇文章以获得解释：

Answer 2

添加索引顺序id，然后添加要用作索引的列

问题是，当您添加其他列时，文件排序就像便签一样，它必须重新收集它们

但是，如果在索引中有列(它必须是一个包含4-6列的索引)，因为每个查询将只使用一个索引。这些柱子会顺势而行。