mysql - mysql数据库中的性能问题全索引扫描

Question

我有一个数据库，其中包含一个名为 QuizMatches 的表。该表具有以下结构:

CREATE TABLE `QuizMatches` (
  `QuizMatchesGuid` binary(16) NOT NULL,
  `DateStarted` datetime NOT NULL,
  `LatestChanged` datetime NOT NULL,
  `HostFBUserToken` varchar(250) NOT NULL,
  `GuestFBUserToken` varchar(250) NOT NULL,
  `ArrayOfQuestionIDs` varchar(200) NOT NULL,
  `ArrayOfQuestionResponseTimesAndAnswersHost` varchar(900) NOT NULL,
  `ArrayOfQuestionResponseTimesAndAnswersGuest` varchar(900) NOT NULL,
  `MatchFinished` int(1) NOT NULL DEFAULT '0',
  `Category` varchar(45) NOT NULL,
  `JsonQuestions` varchar(4000) NOT NULL DEFAULT '[]',
  `DateFinished` datetime NOT NULL,
  `LatestPushSentDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`QuizMatchesGuid`),
  KEY `HostFBUserTokenIX` (`HostFBUserToken`),
  KEY `GuestFBUserTokenIX` (`GuestFBUserToken`),
  KEY `MatchFinishedIX` (`MatchFinished`),
  KEY `LatestChangedIX` (`LatestChanged`),
  KEY `LatestPushSentDateIX` (`LatestPushSentDate`),
  KEY `DateFinishedIX` (`LatestChanged`,`HostFBUserToken`,`GuestFBUserToken`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

此表中有大量行，并且被多个客户端大量使用，尤其是执行如下查询:

SELECT HEX(QuizMatchesGuid) AS QuizMatchesGuid, DateStarted,
    LatestChanged, HostFBUserToken, GuestFBUserToken,
    ArrayOfQuestionIDs, ArrayOfQuestionResponseTimesAndAnswersHost,
    ArrayOfQuestionResponseTimesAndAnswersGuest, JsonQuestions
FROM CrystalDBQuiz.QuizMatches
ORDER BY LatestChanged DESC
LIMIT 10 

主要问题似乎是数据库执行了全索引扫描。我尝试了不同的索引组合，但没有成功。

如果我对上面的 SELECT 查询运行 EXPLAIN，我会收到以下信息:

           id: 1
  select_type: SIMPLE
        table: 'QuizMatches'
         type: index
possible_keys: NULL
          key: 'LatestChangedIX'
      key_len: 8
          ref: NULL
         rows: 10
        Extra: 

有没有办法像上面的例子那样针对这个数据库表优化 SELECTS？

Answer 1

如果您正在使用 LIMIT 语句进行分页，我建议您使用 LatestChanged 值来进行分页。所以你的查询将变成

SELECT HEX(QuizMatchesGuid) AS QuizMatchesGuid, DateStarted, LatestChanged, 
HostFBUserToken, GuestFBUserToken, ArrayOfQuestionIDs,
ArrayOfQuestionResponseTimesAndAnswersHost, 
ArrayOfQuestionResponseTimesAndAnswersGuest, JsonQuestions 
FROM CrystalDBQuiz.QuizMatches 
WHERE LatestChanged<[lastValue]
ORDER BY LatestChanged DESC
LIMIT 10