mysql - 是否可以优化我的 SQL 请求？如果是的话怎么办？

Question

我的 Nexuiz Statistics Web 项目中的 MySQL 请求有问题。我不想实时生成统计表，请求会浪费时间(对于 310000 个条目，请求需要 3 到 7 秒)这是我要优化的 SQL 请求等。

SELECT n.name, d.times_kill, d.times_dead, d.kd, d.times_cap, d.points
FROM
( SELECT f.player player, f.times_kill, f.times_dead, f.kd, g.times_cap, ((f.points * (7 / 3)) + POW(g.times_cap, 1.5)) points
FROM
    ( SELECT k.player player, k.times_kill, d.times_dead, (k.times_kill / d.times_dead) kd, ((k.times_kill / d.times_dead) * k.times_kill) points
    FROM
        ( SELECT player, count( * ) times_kill
        FROM `nexstat`.`dc_events`
          WHERE event = 'KILL' AND server = '2'
          GROUP BY player
          ORDER BY times_kill DESC
        ) k
        JOIN
        ( SELECT player, count( * ) times_dead
        FROM `nexstat`.`dc_events`
          WHERE event = 'DEAD' AND server = '2'
          GROUP BY player
          ORDER BY times_dead DESC
        ) d
        ON d.player = k.player
        ORDER BY points DESC
    ) f
    JOIN
    ( SELECT player, count( * ) times_cap
    FROM `nexstat`.`dc_events`
        WHERE event = 'CAP' AND server = '2'
        GROUP BY player ORDER BY times_cap DESC
    ) g
    ON f.player = g.player
) d
JOIN
( SELECT * FROM `nexstat`.`dc_players` WHERE main = 1
) n
ON d.player = n.id
GROUP BY id
ORDER BY points DESC

这里的数据库模型:

CREATE TABLE IF NOT EXISTS `dc_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `player` int(11) NOT NULL,
  `event` enum('CAP','KILL','DEAD','DROP','PICKUP','CHANGE','JOIN','LEAVE') NOT NULL,
  `param0` int(11) DEFAULT NULL,
  `server` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `event` (`event`),
  KEY `server` (`server`),
  KEY `player` (`player`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `dc_players` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `main` tinyint(1) NOT NULL DEFAULT '1',
  `name` varchar(512) NOT NULL,
  `website` varchar(512) NOT NULL,
  `email` varchar(512) NOT NULL,
  `clan` varchar(512) NOT NULL,
  `country` varchar(2) NOT NULL,
  UNIQUE KEY `name` (`name`),
  KEY `website` (`website`),
  KEY `email` (`email`),
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

如果有人帮助我会很酷;)

Answer 1

这可能有助于简化您的查询。由于我不完全理解它的逻辑，我可能会离开某个地方。但是，我认为下面的内容本质上是您的大查询所做的，但没有所有派生表。额外的 ORDER BY 子句会让您付出很多代价，因为它们会迫使 MySQL 反复遍历数据，而不是只在最后做一次。它们也不会帮助您回答问题，因为数据只是按最外层查询中的点重新排序。

SELECT dc_player.name AS player_name,
       times_kill,
       times_dead,
       times_cap,
       times_kill / times_dead AS kd,
       ((((times_kill / times_dead) * times_kill) * (7 / 3)) + POW(times_cap, 1.5)) AS points
FROM (
    SELECT player,
           SUM(IF(event='KILL',1,0)) AS times_kill,
           SUM(IF(event='DEAD',1,0)) AS times_dead,
           SUM(IF(event='CAP',1,0)) AS times_cap
    FROM dc_events
    GROUP BY player
) AS events
JOIN dc_player
  ON dc_player.id = events.player
ORDER BY points;

您为什么在 2012 年使用 MyISAM？ InnoDB 现在是默认的，它支持更多的索引选项和参照完整性(你会想要的，因为你在这里有一个外键关系)。您至少需要在 dc_events.player 上有一个索引(InnoDB 外键默认是一个索引)。