高级查询运行缓慢

Question

每当我运行这个查询时，它大约需要25-30秒才能运行。正如您所看到的，这里最高级的事情是计算子查询中的两个合并。

SELECT
    g.name,
    g.id,
    (
        SELECT
            COALESCE (
                SUM(result2 / result1) * (
                    SUM(IF(result2 != 0, 1, 0)) * 0.1
                ),
                0
            ) AS res
        FROM
            gump.war gwr
        WHERE
            started = 1
        AND (UNIX_TIMESTAMP(time) + 7 * 24 * 60 * 60) > UNIX_TIMESTAMP()
        AND gwr.guild1 = g.id
        AND gwr.winner = g.id
    ) + (
        SELECT
            COALESCE (
                SUM(result1 / result2) * (
                    SUM(IF(result1 != 0, 1, 0)) * 0.1
                ),
                0
            ) AS res1
        FROM
            gumb.war gwr
        WHERE
            started = 1
        AND (UNIX_TIMESTAMP(time) + 7 * 24 * 60 * 60) > UNIX_TIMESTAMP()
        AND gwr.guild2 = g.id
        AND gwr.winner = g.id
    ) AS avg
FROM
    gumb.guild g
ORDER BY
    avg DESC,
    g.point DESC,
    g.experience DESC LIMIT 10;

表结构/模式：

CREATE TABLE `guild` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(12) NOT NULL DEFAULT '',
  `owner` int(10) unsigned NOT NULL DEFAULT '0',
  `level` tinyint(2) DEFAULT NULL,
  `experience` int(11) DEFAULT NULL,
  `win` int(11) NOT NULL DEFAULT '0',
  `draw` int(11) NOT NULL DEFAULT '0',
  `loss` int(11) NOT NULL DEFAULT '0',
  `point` int(11) NOT NULL DEFAULT '0',
  `account` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

CREATE TABLE `war` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `guild1` int(10) unsigned NOT NULL DEFAULT '0',
  `guild2` int(10) unsigned NOT NULL DEFAULT '0',
  `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `type` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `price` int(10) unsigned NOT NULL DEFAULT '0',
  `score` int(10) unsigned NOT NULL DEFAULT '0',
  `started` tinyint(1) NOT NULL DEFAULT '0',
  `winner` int(11) NOT NULL DEFAULT '-1',
  `result1` int(11) NOT NULL DEFAULT '0',
  `result2` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

Answer 1

索引肯定会有所帮助，对JOIN标准和WHERE子句中使用的字段进行索引的影响最大。

通用语法示例：

CREATE INDEX idx_col1col2 ON tbl_Test (Col1, Col2)

您可能不希望将使用的每个字段都塞进一个索引中，也不应该为每个字段创建索引。

有许多资源可以帮助您了解如何构建索引，下面是几个项目：MySQL CREATE INDEX Syntax MySQL Index Optimization