mysql - 不可能在一个非常简单的订单查询中避免 'using filesort'

Question

我正在尝试创建一个选择查询，但是当我使用解释查询时，mysql 总是额外使用“使用文件排序”。

我尝试了最简单的查询，但问题并没有消失。我的表“Partidas”的结构是:

CREATE TABLE IF NOT EXISTS `Partidas` (
  `IdUsuario` int(11) NOT NULL,
  `IdPartida` int(11) NOT NULL,
  `TipoPartida` tinyint(4) NOT NULL,
  `Facil` tinyint(1) NOT NULL DEFAULT '0',
  `Normal` tinyint(1) NOT NULL DEFAULT '0',
  `Dificil` tinyint(1) NOT NULL DEFAULT '0',
  `FchPartida` date NOT NULL,
  `PuntosPartida` mediumint(9) NOT NULL,
  `IdPartidaTemp` bigint(20) NOT NULL,
  `ComplPers` tinyint(1) NOT NULL,
  `SoloMulti` tinyint(2) NOT NULL,
  PRIMARY KEY (`IdUsuario`,`IdPartida`),
  KEY `IX_PARTIDAS_RECORDS` (`TipoPartida`,`FchPartida`,`PuntosPartida`),
  KEY `IX_PARTIDAS_ORDEN2` (`FchPartida`),
  KEY `IX_PARTIDAS_COMPLPERS` (`ComplPers`,`FchPartida`,`PuntosPartida`),
  KEY `IX_PARTIDAS_SOLOMULTI` (`SoloMulti`,`FchPartida`,`PuntosPartida`),
  KEY `IX_PARTIDAS_DIFICULTAD` (`Facil`,`Normal`,`Dificil`,`SoloMulti`,`FchPartida`,`PuntosPartida`),
  KEY `IX_PARTIDAS_COMPMULTI` (`ComplPers`,`SoloMulti`,`FchPartida`,`PuntosPartida`),
  KEY `IX_PARTIDAS_COMPLPERS_SIMPLE` (`ComplPers`,`PuntosPartida`),
  KEY `IX_PARTIDAS_SOLOMULTI_SIMPLE` (`SoloMulti`,`PuntosPartida`),
  KEY `IX_PARTIDAS_FECHA` (`FchPartida`),
  KEY `IX_PARTIDAS_PUNTOS` (`PuntosPartida`),
  KEY `PRUEBA_PARTIDAS` (`PuntosPartida`,`TipoPartida`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

该表大约有 1000-5000 行(数据非常少)，但始终使用文件排序。我用于测试的查询是:

explain select *
from Partidas
order by PuntosPartida
limit 0, 50;

结果是:

| id | select_type | table              | type  | possible_keys | key       | key_len | ref  | rows | Extra          |
|  1 | SIMPLE      | Partidas           | ALL   | NULL          | NULL      | NULL    |NULL  | 1041 | Using filesort |

但如果在查询中我更改了限制，例如限制 0,5；那么结果也改变了

| id | select_type | table              | type  | possible_keys | key                | key_len | ref  | rows | Extra       |
|  1 | SIMPLE      | Partidas           | index | NULL          | IX_PARTIDAS_PUNTOS | 3       |NULL  |  5   |             |

在mysql配置中，buffer和sort的变量是:

-myisam sort buffer size: 2MB
-sort buffer size: 2MB
-key buffer size: 1GB

但我尝试更改这些值(将其增加到 8MB)并且结果相同

谢谢你的帮助

Answer 1

我猜这是查询优化器在做它的工作。 This article here显示“优化器更喜欢全表扫描，它甚至没有考虑扫描索引作为相关选择(possible_keys:NULL)”

您可以强制它使用索引，但执行时间可能会变慢(如文章中所述)。

select *
from Partidas FORCE INDEX(IX_PARTIDAS_PUNTOS)
order by PuntosPartida
limit 0, 50; 

您也可以read more here关于如何避免表扫描(“使用文件排序”)