Медленный запрос MySql с порядком по лимиту с индексом

У меня есть запрос, сгенерированный 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), container.Id (PK), и я не понимаю, почему mysql, похоже, выполняет полную сортировку перед возвратом необходимых записей, хотя уже есть индекс в столбце идентификатора.

Кроме того, эти данные отображаются в сетке, которая поддерживает фильтры, сортировку и разбиение на страницы, и крайне необходимо правильное использование индексов.

Вот определения таблиц:

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, чтобы упростить его (сначала у него были ужасные подзапросы, которые выполнялись еще медленнее).

Подсказки запросов (как в force index) здесь тоже не решение, потому что EF не поддерживает такие возможности.

В основном я надеюсь найти некоторые оптимизации на уровне базы данных.

Для тех, кто не заметил теги, речь идет о базе данных MySql.


mysql entity-framework
person Mihai Caracostea    schedule 06.11.2015    source источник
comment
Можете ли вы попробовать STRAIGHT_JOIN, чтобы заставить порядок присоединения к таблице. Надеемся, что это позволит использовать индекс в файлах.id для сортировки.   -  person Kickstart    schedule 06.11.2015
comment
@Kickstart Действительно, Straight_Join работает! Результат мгновенный. Но как насчет использования этого с Entity Framework? Любые подсказки?   -  person Mihai Caracostea    schedule 06.11.2015
comment
Боюсь, нет опыта. Однако, возможно, стоит запустить ANALYZE TABLE.... на каждой из таблиц. Возможно, детали устарели, поэтому выбран неэффективный порядок соединения.   -  person Kickstart    schedule 06.11.2015
comment
@Kickstart Я запустил ANALYZE TABLE на обеих таблицах, но все равно без удовольствия.   -  person Mihai Caracostea    schedule 06.11.2015
comment
Если в контейнере всего 9 строк, можете ли вы удалить KEY Files_ContainerId_FK (ContainerId)?   -  person Neville Kuyt    schedule 06.11.2015
comment
@NevilleK MySQL Workbench сообщает мне, что я также должен удалить внешний ключ для контейнеров. Что неприемлемо.   -  person Mihai Caracostea    schedule 06.11.2015

Ответы (2)


arrow_upward
0
arrow_downward

MySQL использует только один индекс на таблицу. Прямо сейчас он предпочитает использовать индекс внешнего ключа, чтобы объединение было эффективным, но это означает, что сортировка не использует индекс.

Попробуйте создать составной индекс для ContainerId, filedID

person Neville Kuyt    schedule 06.11.2015
comment
Я уже пробовал составной индекс для id, containerid, но mysql использует его только в том случае, если я его заставлю. Результаты такие, как и ожидалось, но я не могу использовать подсказки запроса из структуры сущностей. Вы уверены, что правильно расставили столбцы? Используя этот индекс, даже если я заставлю его, это не повлияет. - person Mihai Caracostea; 06.11.2015

arrow_upward
0
arrow_downward

По сути, это ваш запрос:

SELECT e1.*
FROM `files` e1 INNER JOIN
     `containers` e2
     ON e1.`ContainerId` = e2.`Id`
ORDER BY e1.`Id` ASC
LIMIT 0, 10;

Вы можете попробовать создать индекс на files(id, ContainerId). Это может вдохновить MySQL на использование составного индекса, ориентированного на order by.

Вероятно, было бы более вероятно, если бы запрос был сформулирован так:

SELECT e1.*
FROM `files` e1
WHERE EXISTS (SELECT 1 FROM containers e2 WHERE e1.`ContainerId` = e2.`Id`)
ORDER BY e1.`Id` ASC
LIMIT 0, 10;

Есть один способ, который действительно работает, чтобы использовать индексы. Однако это зависит от чего-то в MySQL, работа которого не задокументирована (хотя на практике работает). Следующее будет читать данные по порядку, но влечет за собой накладные расходы на материализацию подзапроса, но не для сортировки:

SELECT e1.*
FROM (SELECT e1.*
      FROM files e1
      ORDER BY e1.id ASC
     ) e1
WHERE EXISTS (SELECT 1 FROM containers e2 WHERE e1.`ContainerId` = e2.`Id`)
LIMIT 0, 10;
person Gordon Linoff    schedule 06.11.2015
comment
Я уже пробовал составной индекс для id, containerid, но mysql использует его только в том случае, если я его заставлю. Результаты такие, как и ожидалось, но я не могу использовать подсказки запроса из структуры сущностей. - person Mihai Caracostea; 06.11.2015