mysql - 带索引的 order by limit 的缓慢 MySql 查询

Question

我有一个由 Entity Framework 生成的查询，如下所示:

SELECT
`Extent1`.`Id`, 
`Extent1`.`Name`, 
`Extent1`.`ExpireAfterUTC`, 
`Extent1`.`FileId`, 
`Extent1`.`FileHash`, 
`Extent1`.`PasswordHash`, 
`Extent1`.`Size`, 
`Extent1`.`TimeStamp`, 
`Extent1`.`TimeStampOffset`
FROM `files` AS `Extent1` INNER JOIN `containers` AS `Extent2` ON `Extent1`.`ContainerId` = `Extent2`.`Id`
 ORDER BY 
`Extent1`.`Id` ASC LIMIT 0,10

它运行缓慢得令人痛苦。我在 files.Id (PK)、files.ContainerId(FK)、containers.Id(PK) 上有索引，我不明白为什么 mysql 似乎在返回所需记录之前进行了完整排序，即使已经有Id 列上的索引。

此外，这些数据显示在支持过滤、排序和分页的网格中，因此非常需要充分利用索引。

以下是表定义:

CREATE TABLE `files` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `FileId` varchar(100) NOT NULL,
  `ContainerId` int(11) NOT NULL,
  `ContainerGuid` binary(16) NOT NULL,
  `Guid` binary(16) NOT NULL,
  `Name` varchar(1000) NOT NULL,
  `ExpireAfterUTC` datetime DEFAULT NULL,
  `PasswordHash` binary(32) DEFAULT NULL,
  `FileHash` tinyblob NOT NULL,
  `Size` bigint(20) NOT NULL,
  `TimeStamp` double NOT NULL,
  `TimeStampOffset` double NOT NULL,
  `FilePostId` int(11) NOT NULL,
  `FilePostGuid` binary(16) NOT NULL,
  `AttributeId` int(11) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `FileId_UNIQUE` (`FileId`),
  KEY `Files_ContainerId_FK` (`ContainerId`),
  KEY `Files_AttributeId_FK` (`AttributeId`),
  KEY `Files_FileId_index` (`FileId`),
  KEY `Files_FilePostId_index` (`FilePostId`),
  KEY `Files_Guid_index` (`Guid`),
  CONSTRAINT `Files_AttributeId_FK` FOREIGN KEY (`AttributeId`) REFERENCES `attributes` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `Files_ContainerId_FK` FOREIGN KEY (`ContainerId`) REFERENCES `containers` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `Files_FilePostsId_FK` FOREIGN KEY (`FilePostId`) REFERENCES `fileposts` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=977942 DEFAULT CHARSET=utf8;


CREATE TABLE `containers` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  `Guid` binary(16) NOT NULL,
  `AesKey` binary(32) NOT NULL,
  `FileCount` int(10) unsigned NOT NULL DEFAULT '0',
  `Size` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `Containers_Guid_index` (`Guid`),
  KEY `Containers_Name_index` (`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8;

您会注意到文件表中还有一些其他关系，我省略这些关系只是为了简化查询而不影响观察到的行为。

这也是 EXPLAIN EXTENDED 的输出:

+----+-------------+---------+-------+----------------------+-----------------------+---------+----------------------------------+-------+----------+----------------------------------------------+
| id | select_type |  table  | type  |    possible_keys     |          key          | key_len |               ref                | rows  | filtered |                    Extra                     |
+----+-------------+---------+-------+----------------------+-----------------------+---------+----------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | Extent2 | index | PRIMARY              | Containers_Guid_index |      16 | NULL                             |     9 | 100.00   | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | Extent1 | ref   | Files_ContainerId_FK | Files_ContainerId_FK  |       4 | netachmentgeneraltest.Extent2.Id | 73850 | 100.00   |                                              |
+----+-------------+---------+-------+----------------------+-----------------------+---------+----------------------------------+-------+----------+----------------------------------------------+

文件表有大约 900000 条记录(并且还在计数)，容器有 9 条记录。仅当存在 ORDER BY 时才会出现此问题。另外，我在修改查询方面无法做太多事情，因为它是由 Entity Framework 生成的。我尽可能多地使用 LINQ 查询来简化它(起初它有一些可怕的子查询，执行速度甚至更慢)。

查询提示(如强制索引)也不是这里的解决方案，因为 EF 不支持此类功能。

我主要希望找到一些数据库级别的优化。

对于那些没有发现标签的人来说，有问题的数据库是 MySql。

Answer 1

MySQL 每个表仅使用一个索引。目前，它更喜欢使用外键索引，因此连接效率很高，但这意味着排序不使用索引。

尝试在ContainerId、filedID上创建复合索引