尽管 EXPLAIN 显示了良好的计划，但 MySQL 的连接查询速度很慢

Question

我有以下场景：在 MySQL 数据库中，我有 2 个 MyISAM 表，一个有 420 万行，另一个有 3.2 亿行。以下是表的架构：

Table1（4.2M 行）

F1 INTEGER UNSIGNED NOT NULL PRIMARY KEY
f2 varchar(40)
f3 varchar(40)
f4 varchar(40)
f5 varchar(40)
f6 smallint(6)
f7 smallint(6)
f8 varchar(40)
f9 varchar(40)
f10 smallint(6)
f11 varchar(10)
f12 tinyint(4)
f13 smallint(6)
f14 text

Table2（320M 行）

F1 INTEGER UNSIGNED NOT NULL PRIMARY KEY
f2 INTEGER UNSIGNED NOT NULL

Table2 位于不同的数据库中，但我使用查询这两个表的存储过程。两个表之间的关系是，对于Table1.F1，最多可能有大约。 Table2.F1（外键）中有 100 行匹配，并且将为这些匹配的键返回 Table2.f2 的值。 我在 Table1 上有一个索引 IX1(f2(15),f3(10)) ，在表 2 中有一个索引 IX2(F1,f2) 和 IX3(f2)

我正在运行的查询如下：

SELECT g.F1
FROM DB1.Table1 g 
INNER JOIN DB2.Table2 gp ON g.F1 = gp.F1 
WHERE (gp.f2 = 452677825) AND
(g.f2 = 'A string value') LIMIT 0,56

此查询有时非常快(<1s)，但更改 g.F2 比较的字符串值会导致查询甚至需要 11 秒以上，有时甚至 30 秒。我不明白为什么会这样。以下是执行的 SELECT 上的 EXPLAIN 的输出。

1, 'SIMPLE', 'g', 'ref', 'PRIMARY,IX1', 'IX1', '17', 'const', 901, 'Using where'
1, 'SIMPLE', 'gp', 'ref', 'IX3,IX2', 'IX2', '8', 'DB1.g.F1,const', 1, 'Using index'

这似乎是一个相当不错的执行计划。解释的顶行中的行数最多为 2000，但我不明白为什么这需要比几分之一秒更长的时间才能返回结果。我还对查询运行了探查器，并注意到查询 99.9% 的时间都花在“发送数据”阶段。谁能解释一下为什么会这样，以及可以采取什么措施来优化查询？

提前致谢， 蒂姆

Answer 1

我不是这方面的专家，但这里有一些想法：

当 g.F2 更改时查询速度需要更长的时间是因为缓存。 MySQL 会保存每个查询的结果（直到缓存已满），但新查询在空缓存上运行，因此需要更长的时间。您不应该基于此进行优化。 （参见如何测量准确）

我无法从您的信息中判断出g或gp表是否具有更大的特异性（看起来像gp？）在 where 子句中，但您可能想尝试使用子查询。 （参见如何强制首先执行内部查询）

关于分析，您可能会遇到物理阈值，例如超出内存分配（使用交换对性能来说是灾难性的），这在解释，或者在这种情况下 explain 是否只是错误的。

Answer 2

如果可以的话，您可能想尝试调整 my.cnf，您想要使用的属性是 key_buffer_size。 MyISAM 索引存储在 .MYI 文件中，如果您找到这些文件并合计文件大小（例如 ls -lh /var/lib/mysql/dbname/*.MYI），您可以粗略估计密钥缓冲区需要多大才能适应MySQL 文档确实建议不要超过系统内存的 25%。

Answer 3

两个表之间的关系是，对于Table1.F1，最多可能有大约。 Table2.F1 中有 100 行

澄清一下，Table1.F1 和 Table2.F1 之间的关系是一对一还是一对多？对我来说，这个语句意味着一对多，但从模式来看，每个字段都是主（即唯一）键。

无论如何，我怀疑 g.f2(15) 的统一并不统一，并且当遇到统计异常值时，性能会相应下降。

结果是否

SELECT f2(15) AS f2_15, COUNT(*) AS cnt
FROM Table1
GROUP BY f2(15) 
ORDER BY cnt DESC

显示出一些显着的异常值？