mysql - 高级查询运行缓慢

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