mysql - 优化MySQL查询，选择内选择，多个相同

Question

我需要帮助优化我创建的这个 MySQL 语句。它完全符合我的要求，但是我感觉它会很慢，因为我在语句中进行了多次选择，而且我还多次查询了 achievements_new。这是我第一次做这样的主要声明，我已经习惯了简单的 SELECT FROM WHERE 风格废话。

我可能会做一些解释，这是为了我网站的排行榜风格。

--第一个变量输出是根据所示公式计算的排名，(Log + Log + # of achievements)。

--Wepvalue是该id拥有的武器的值(value)总和。 playerweapons包含所有的武器，weaponprices将类型转换为价格，然后SUM计算出值(value)。

--Account 就是解锁的成就数量。 也许这可以通过排名输出以某种方式进行优化？

--achievements_new 和playerweapons 中的id 是playerdata 中id 的外键

SELECT
    (
        IFNULL(LOG(1.5, cashearned),0) +
        IFNULL(LOG(1.3, roundswon), 0) +
        (
            SELECT COUNT(*)
            FROM achievements_new
            WHERE `value` = -1 AND achievements_new.id = playerdata.id
        )
    ) as rank,
    nationality,
    nick,
    steamid64,
    cash,
    playtime,
    damage,
    destroyed,
    (
        SELECT SUM(price)
        FROM weaponprices
        WHERE weapon IN
        (
            SELECT class
            FROM playerweapons
            WHERE playerweapons.id = playerdata.id
        )
    ) as wepvalue,
    (
        SELECT COUNT(*)
        FROM achievements_new
        WHERE `value` = -1 AND achievements_new.id = playerdata.id
    ) as achcount,
    lastplayed
FROM playerdata
ORDER BY rank DESC

表结构:

玩家数据:

CREATE TABLE IF NOT EXISTS `playerdata` (
    `id` int(11) unsigned NOT NULL,
  `steamid64` char(17) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `nick` varchar(32) NOT NULL DEFAULT '',
  `cash` int(32) unsigned NOT NULL DEFAULT '0',
  `playtime` int(32) unsigned NOT NULL DEFAULT '0',
  `nationality` char(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `damage` int(32) unsigned NOT NULL DEFAULT '0',
  `destroyed` int(32) unsigned NOT NULL DEFAULT '0',
  `cashearned` int(10) unsigned NOT NULL,
  `roundswon` smallint(5) unsigned NOT NULL,
  `lastplayed` datetime NOT NULL,
) ENGINE=InnoDB

achievements_new:

CREATE TABLE IF NOT EXISTS `achievements_new` (
  `id` int(10) unsigned NOT NULL,
  `achkey` enum(<snip - lots of values here>) NOT NULL,
  `value` mediumint(8) NOT NULL DEFAULT '0'
) ENGINE=InnoDB

玩家武器:

CREATE TABLE IF NOT EXISTS `playerweapons` (
  `id` int(10) unsigned NOT NULL,
  `class` varchar(30) CHARACTER SET ascii NOT NULL
) ENGINE=InnoDB

武器价格:

CREATE TABLE IF NOT EXISTS `weaponprices` (
  `weapon` varchar(30) NOT NULL,
  `price` int(10) unsigned NOT NULL
) ENGINE=InnoDB

提前致谢!

Answer 1

尝试类似下面的查询。
我使用 LEFT JOIN 而不是加入，因为可能有玩家没有成就或武器。如果你不需要这些播放器，你可以使用 JOIN

SELECT
    IFNULL(LOG(1.5, p.cashearned),0) +
        IFNULL(LOG(1.3, p.roundswon), 0) +
        SUM(CASE WHEN ac.id IS NOT NULL THEN 1 ELSE 0 END)/COUNT(pw.id) as rank
    p.nationality,
    p.nick,
    p.steamid64,
    p.cash,
    p.playtime,
    p.damage,
    p.destroyed,
    --SUM(CASE WHEN pw.id IS NOT NULL THEN pw.price ELSE 0 END) as wepvalue,
    --wpn.price as wepvalue,
    SUM(CASE WHEN pw.id IS NOT NULL THEN wp.price ELSE 0 END)/COUNT(ac.id) as wepvalue,
    SUM(CASE WHEN ac.id IS NOT NULL THEN 1 ELSE 0 END)/COUNT(pw.id) as achcount,
    lastplayed
FROM playerdata as p
    JOIN playerweapons as pw ON pw.id = p.id
    JOIN weaponprices as wp ON pw.class = wp.weapon
    LEFT JOIN achievements_new as ac ON ac.id = p.id AND ac.value = -1
    --LEFT JOIN playerweapons as pw ON pw.id = p.id
    --LEFT JOIN weaponprices as wp ON pw.class = wp.weapon
    --LEFT JOIN (   SELECT 
                      --pw.id as player,
                      --SUM(wp.price) as price
                  --FROM weaponprices as wp
                      --JOIN playerweapons as pw ON pw.class = wp.weapon 
                  --GROUP BY pw.id
              --) as wpn ON wpn.player = p.id
GROUP BY 
    p.nationality,
    p.nick,
    p.steamid64,
    p.cash,
    p.playtime,
    p.damage,
    p.destroyed,
    p.lastplayed