MySQL MyISAM 排序查询使其变慢

Question

我在 Windows Server 2008(具有 4GB RAM)上使用 MySQL 5.1，并具有以下配置:

我有 2 个 MyISAM 表。其中一个位于 1 个数据库 (DB1) 中，有 14 列，其中大部分为 varchar。该表大约有 5,000,000 行，是下面的 DB1.games 表。它的主键为 GameNumber (int(10))。

另一个表是 DB2.gameposition，由 GameNumber 列(链接到DB1.games)和位置代码(int(10))。该表大约有 400,000,000 行，并且在 PositionCode 上有一个索引 IX_PositionCode。

这两个数据库位于同一服务器上。

我想在 DB2.gameposition 上运行查询来查找特定的 PositionCode，并按链接 DB1.games.Yr 字段(smallint(6) - 这代表年份)对结果进行排序。这个排序结果是我最近才介绍的。 DB1.games 中有该 Yr 字段的索引。

在我的存储过程中，我执行以下操作:

CREATE TEMPORARY TABLE tblGameNumbers(GameNumber INT UNSIGNED NOT NULL PRIMARY KEY);

INSERT INTO tblGameNumbers(GameNumber) 
SELECT DISTINCT gp.GameNumber 
FROM DB2.gameposition gp 
WHERE PositionCode = var_PositionCode LIMIT 1000;

我只是得到 1000 来加快速度

然后将其加入到 DB1.games 表中。

为了从中生成 EXPLAIN，我取出临时表(我在存储过程中使用)并重构它，如下面的内部子查询所示:

EXPLAIN 
SELECT * 
FROM DB1.games g 
INNER JOIN (SELECT DISTINCT gp.GameNumber 
            FROM DB2.gameposition gp 
            WHERE PositionCode = 669312116 LIMIT 1000
   ) B ON g.GameNumber = B.GameNumber 
ORDER BY g.Yr DESC 
LIMIT 0,28

运行上面的 EXPLAIN，我看到以下内容:

1, 'PRIMARY', '', 'ALL', '', '', '', '', 1000, 'Using temporary; Using filesort'1, 'PRIMARY', 'g', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'B.GameNumber', 1, ''2, 'DERIVED', 'gp', 'ref', 'IX_PositionCode', 'IX_PositionCode', '4', '', 1889846, 'Using temporary'

在引入 ORDER BY 子句之前，查询几乎是即时的。现在，有时它很快(取决于不同的 PositionCode)，但有时可能需要长达 10 秒的时间才能返回行。在我引入排序之前，它几乎总是瞬时的。不幸的是，我不太擅长解释 EXPLAIN 输出。或者如何让查询更快。

任何帮助将不胜感激!

提前致谢，蒂姆

Answer 1

如果没有 order by，您的 limit 意味着返回前 28 个结果，然后查询停止。使用 order by，需要检索所有结果，以便对它们进行排序并返回前 28 个结果。

解释显示了 MySql 正在做什么:

sort 5000000 games records by yr
for each games record from sorted list
    get the games record by primary key (to get all the columns)
    read gamepositions by position code
        if it does not match gamenumber, discard it
        when 1000 matches found, stop reading
    end read
end for

试试这个:

select distinct ... from gameposition gp
inner join games g on g.gamenumber = gp.gamenumber
where gp.positioncode = ...
order by g.yr limit ...