mysql 不存在，非常慢 - 如何改进？

Question

我有一个小问题，关于尝试优化我在数据库中的表上运行的这个命令。该表有超过 934,83​​6 行，并且每天增长约 12,000 行。

它保存着每天拍摄的坦克快照。我想要实现的是看到快照中的差异。即查看玩家是否购买了任何新坦克。

每个 account_id 的实际快照数据只有 100 到 250 行。

表 wot_snapshots:

CREATE TABLE `wot_snapshots` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `snapshot` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=latin1

表wot_tanks_all:

CREATE TABLE `wot_tanks_all` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `snapshot` int(11) NOT NULL,
  `account_id` int(11) NOT NULL,
  `tank_id` int(6) NOT NULL,
  `wn8` float DEFAULT NULL,
  `spotted` int(11) NOT NULL,
  `avg_damage_blocked` decimal(6,2) NOT NULL,
  `capture_points` int(11) NOT NULL,
  `explosion_hits` int(11) NOT NULL,
  `piercings` int(11) NOT NULL,
  `xp` int(11) NOT NULL,
  `survived_battles` int(11) NOT NULL,
  `dropped_capture_points` int(11) NOT NULL,
  `damage_dealt` int(11) NOT NULL,
  `hits_percents` int(11) NOT NULL,
  `draws` int(11) NOT NULL,
  `battles` int(11) NOT NULL,
  `damage_received` int(11) NOT NULL,
  `frags` int(11) NOT NULL,
  `direct_hits_received` int(11) NOT NULL,
  `hits` int(11) NOT NULL,
  `battle_avg_xp` int(11) NOT NULL,
  `wins` int(11) NOT NULL,
  `losses` int(11) NOT NULL,
  `piercings_received` int(11) NOT NULL,
  `no_damage_direct_hits_received` int(11) NOT NULL,
  `shots` int(11) NOT NULL,
  `explosion_hits_received` int(11) NOT NULL,
  `tanking_factor` decimal(2,2) NOT NULL,
  `mark_of_mastery` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=934837 DEFAULT CHARSET=utf8

查询:

SELECT t1.tank_id, wot_tanks.short_name_i18n FROM wot_tanks_all t1
JOIN wot_tanks ON t1.tank_id = wot_tanks.tank_id
WHERE NOT EXISTS (SELECT tank_id
              FROM wot_tanks_all t2
              WHERE t1.tank_id = t2.tank_id AND account_id = 527080765 AND    snapshot = 60)
AND account_id = 527080765 AND snapshot = 93

输出:

tank_id short_name_i18n
8465    Panther II
53505   T-127
54865   Light VIC

目前运行时间约为 30 秒。我最好在 mysql 中完成这一切还是将其中一些转移到 PHP 中？

非常感谢任何建议和帮助

谢谢，杰森

编辑:这是 Google 刚刚整理的。仍在学习!

Answer 1

坦克id上没有索引，所以加入会很慢，我重写了它以消除自加入

SELECT t1.tank_id, wot_tanks.short_name_i18n FROM wot_tanks_all t1
JOIN wot_tanks 
ON t1.tank_id = wot_tanks.tank_id
GROUP BY t1.tank_id,wot_tanks.short_name_i18n
HAVING SUM(account_id = 527080765 AND    snapshot = 60)=0
AND SUM(account_id = 527080765 AND snapshot = 93)>0

这些索引将加快速度

ALTER TABLE wot_tanks_all ADD KEY(account_id ,snapshot )
ALTER TABLE wot_tanks_all ADD KEY(tank_id )