即使EXPLAIN显示好的计划，MySQL的联接查询仍然很慢 [英] MySQL slow query with join even though EXPLAIN shows good plan

问题描述

我有以下情形:在一个MySQL数据库中，我有2个MyISAM表，一个表有420万行，另一个表有3.2亿行.以下是表的架构:

表1(420万行)

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

表2(3.2亿行)

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

Table2在另一个数据库中，但是我正在使用一个存储过程来查询两个表.这两个表之间的关系是，对于Table1.F1，最多可能有大约. Table2.F1(外键)中匹配的100行，并将为这些匹配的键返回Table2.f2的值. 我在表1上有一个索引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％的时间.谁能解释为什么会这样，以及如何优化查询?

预先感谢， 蒂姆

解决方案

我不是该领域的专家，但是这里有一些想法:

g.F2更改时，查询速度花费更长的时间是由于缓存. MySQL将保存每个查询的结果(直到缓存已满)，但是新查询将在空缓存上运行，因此需要更长的时间.您不应该基于此进行优化. (请参见如何衡量准确)

在您的信息中，我无​​法确定g或gp表在where子句中是否具有更高的特异性(好像是gp ??)，但是您可能想尝试一个子查询. (请参见如何强制内部查询首先执行)

关于性能分析，很可能您达到了物理阈值，例如超出ram分配(使用交换对于性能而言是灾难性的)，这在explain中是不明显的，或者在这种情况下explain是否只是错误. /p>

I have the following scenario: In a MySQL database, I have 2 MyISAM tables, one with 4.2 million rows, and another with 320 million rows. The following is the schema for the tables:

Table1 (4.2M rows)

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 rows)

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

Table2 is in a different database but I am using a stored procedure which queries the two tables. The relation between the two tables is that for Table1.F1 there may be up to approx. 100 rows in Table2.F1 (foreign key) which match, and the value for Table2.f2 will be returned for these matched keys. I have an index IX1(f2(15),f3(10)) on Table1 and an index IX2(F1,f2) and IX3(f2) in Table 2

The queries I am running are the following:

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

This query is sometimes very fast (<1s) but changing the string value that g.F2 is compared to leads to queries which take even over 11 and sometimes even 30 seconds. I cannot understand why this is so. The following is the output of the EXPLAIN on the SELECT that is executed.

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'

which seems to be quite a good execution plan. The number of rows in the top row of the explain goes to 2000 at most, but I do not see why this should take any longer than a fraction of a second to return results. I also ran profiler on the query and noticed that the queries are spending 99.9% of the time on the "Sending data" stage. Can anyone please explain why this is so, and what can be done to optimise the query?

Thanks in advance, Tim

解决方案

I'm not an expert in this area, but here are a few thoughts:

Query speed taking longer when g.F2 changes is because of caching. MySQL will save the results for each query (until the cache is full), but new queries are run on an empty cache, so they take longer. You shouldn't optimize based on this. (See How to measure accurately)

I can't tell from your information whether the g or gp table has greater specificity (seems like gp?) in the where clause, but you may want to try a subquery instead. (See How to force the inner query to execute first)

Regarding profiling, it's possible you're hitting a physical threshold like exceeding ram allocation (using swap is disastrous for performance) that would not be obvious from explain, or whether explain is just wrong in this case.

这篇关于即使EXPLAIN显示好的计划，MySQL的联接查询仍然很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！