mysql - 需要帮助优化

Question

我有一个 MySQL 查询需要帮助优化。它本质上相当简单，但运行大约需要 0.5 秒，而且会经常运行。

SET @RankNumber:=0; 
SELECT NumberRank,TankName, Color
FROM
(
   SELECT @RankNumber := @RankNumber + 1 AS NumberRank, TankName, Color 
   FROM
   (
      SELECT TankID, MapID, Color, TankName
      FROM MAPDATA
      INNER JOIN Tank ON Tank.ID = MapData.TankID
      WHERE MapID = 3
      ORDER BY Rank DESC, TotalPP DESC
   ) Query1
) final
WHERE Color = 3
LIMIT 10;

基本上我在做什么，我给每个记录一个数字(RankNumber)，然后我从数据库中选择 map 为 3 的所有坦克，然后我给他们每个人在 map 上的数字按等级和经验值排序。然后我过滤掉除了我想要的颜色之外的所有颜色，这就是为什么我基本上有三个级别，所以我可以获得每一行。

您有前 10 名，这是简单的 1-10，然后您有顶级紫色，它可能看起来更像 1、7、10、25、36 等等，因为前 10 名并不是所有颜色的前 10 名。合理？这需要 0.5 秒才能完成，人们可能每分钟击中 10-30 次。

1   PRIMARY <derived2>  ALL                 31220   Using where
2   DERIVED <derived3>  ALL                 31220   
3   DERIVED Tank    ALL PRIMARY,idx_TankID              9863    Using temporary; Using filesort
3   DERIVED MAPDATA ref PRIMARY,MapID,TankID,idx_mapdata    PRIMARY 8   microtanksbeta.Tank.ID  1   

这是我对该查询的解释。任何有助于加快速度的帮助将不胜感激。如果有一种方法我可以告诉它把这个查询保存在内存中或者一些有用的东西，这样它就不会那么慢了。

显示创建表

'MapData', 'CREATE TABLE `mapdata` (
  `MapID` int(11) NOT NULL,
  `TankID` int(11) NOT NULL,
  `Color` tinyint(4) NOT NULL,
  `X` int(11) DEFAULT ''-1'',
  `Y` int(11) DEFAULT ''-1'',
  `Rank` tinyint(4) NOT NULL DEFAULT ''0'',
  `Health` int(11) NOT NULL DEFAULT ''1000'',
  `Armors` tinyint(4) NOT NULL DEFAULT ''0'',
  `Duals` tinyint(4) NOT NULL DEFAULT ''0'',
  `Missiles` tinyint(4) NOT NULL DEFAULT ''0'',
  `Homings` tinyint(4) NOT NULL DEFAULT ''0'',
  `Radars` tinyint(4) NOT NULL DEFAULT ''0'',
  `Beacons` tinyint(4) NOT NULL DEFAULT ''0'',
  `HasRankKill` bit(1) NOT NULL DEFAULT b''0'',
  `TotalPP` bigint(20) NOT NULL DEFAULT ''0'',
  `RankPP` bigint(20) NOT NULL DEFAULT ''0'',
  `KillCount` int(11) NOT NULL DEFAULT ''0'',
  `DeathCount` int(11) NOT NULL DEFAULT ''0'',
  `TimePlayed` time NOT NULL DEFAULT ''00:00:00'',
  `EnabledEquipment` tinyint(4) NOT NULL DEFAULT ''0'',
  `Prestige` tinyint(4) NOT NULL DEFAULT ''0'',
  PRIMARY KEY (`MapID`,`TankID`,`Color`),
  KEY `MapID` (`MapID`),
  KEY `TankID` (`TankID`),
  KEY `idx_mapdata` (`MapID`,`Color`,`TankID`),
  CONSTRAINT `mapdata_ibfk_1` FOREIGN KEY (`MapID`) REFERENCES `maps` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `mapdata_ibfk_2` FOREIGN KEY (`TankID`) REFERENCES `tank` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1'


'Tank', 'CREATE TABLE `tank` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Username` text NOT NULL,
  `TankName` text NOT NULL,
  `KillAward` int(11) DEFAULT ''0'',
  `DeathAward` int(11) DEFAULT ''0'',
  `TimeAward` int(11) DEFAULT ''0'',
  `Stars` int(11) DEFAULT ''0'',
  `Trophy` int(11) DEFAULT ''0'',
  `Dot` int(11) DEFAULT ''0'',
  `Cartographer` int(11) DEFAULT ''0'',
  `OtherAwards` int(11) DEFAULT ''0'',
  `Banned` bit(1) DEFAULT b''0'',
  `BanExpiration` datetime DEFAULT NULL,
  `BanExplination` text,
  `Deleted` bit(1) DEFAULT b''0'',
  `DeletedDate` datetime DEFAULT NULL,
  `CreationDate` datetime NOT NULL DEFAULT ''2011-05-24 00:00:00'',
  PRIMARY KEY (`ID`),
  KEY `idx_TankName` (`TankName`(40)),
  KEY `idx_UNTankName` (`Username`(60),`TankName`(40)),
  KEY `idx_TankID` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=23239 DEFAULT CHARSET=latin1'

Answer 1

我看不到 3 个级别有任何用处，2 个级别就足够了。此外，我已将连接从内部选择中移出以使其更快。

SET @RankNumber:=0; 
SELECT inner.NumberRank, tank.TankName, inner.Color
 FROM (SELECT @RankNumber := @RankNumber + 1 AS NumberRank, TankID, Color
         FROM MAPDATA
        WHERE MapID = 3
        ORDER BY Rank DESC, TotalPP DESC
      ) inner
INNER JOIN Tank ON Tank.ID = inner.TankID
WHERE inner.Color = 3
LIMIT 10;

确保内部查询单独使用索引并且速度很快。尝试一下，看看是否得到相同的结果，是否更快。