After much work I finally got a rather complicated query to work very smootly and return results very quickly.
经过大量工作后,我终于得到了一个相当复杂的查询,可以非常流畅地工作并且很快返回结果。
It was running well on both dev and testing, but now testing has slowed considerably. The explain query which takes 0.06 second on dev and was about the same in testing is now 7 seconds in testing.
它在开发和测试方面运行良好,但现在测试速度已大大减慢。解释查询在开发上需要0.06秒并且在测试中大致相同,现在测试时间为7秒。
The explains are slightly different, and I'm not sure why this would be The explain from dev
解释略有不同,我不知道为什么这将是dev的解释
-+---------+------------------------------+------+------------------------------ ---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+-------------------------+------------ -+---------+------------------------------+------+------------------------------ ---+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 5 | | | 1 | PRIMARY | tickets | ref | biddate_idx | biddate_idx | 7 | showsdate.bid,showsdate.date | 78 | | | 2 | DERIVED | shows | ALL | biddate_idx,latlong_idx | NULL | NULL | NULL | 3089 | Using temporary; Using fileso rt | | 2 | DERIVED | genres | ref | bandid_idx | bandid_idx | 4 | activehw.shows.bid | 2 | Using index | | 2 | DERIVED | artists | eq_ref | bid_idx | bid_idx | 4 | activehw.genres.bid | 1 | Using where | +----+-------------+------------+--------+-------------------------+------------
and in the testing
并在测试中
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+-------------------------+-------------+---------+------------------------------+--------+----------------------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 5 | | | 1 | PRIMARY | tickets | ref | biddate_idx | biddate_idx | 7 | showsdate.bid,showsdate.date | 78 | | | 2 | DERIVED | genres | index | bandid_idx | bandid_idx | 139 | NULL | 531281 | Using index; Using temporary; Using filesort | | 2 | DERIVED | artists | eq_ref | bid_idx | bid_idx | 4 | activeHW.genres.bid | 1 | | | 2 | DERIVED | shows | eq_ref | biddate_idx,latlong_idx | biddate_idx | 7 | activeHW.artists.bid | 1 | Using where | +----+-------------+------------+--------+-------------------------+-------------+---------+------------------------------+--------+----------------------------------------------+ 5 rows in set (6.99 sec)
The order of the tables is different, even though the queries are exactly the same. Is this what would cause the slowdown? if so, how would I fix it? The dev is windows, testing is centOs. both running same version of mysql 5.0, and like I said, testing was running perfectly and I haven't made any structural changes to the database.
即使查询完全相同,表的顺序也不同。这会导致经济放缓吗?如果是的话,我该如何解决?开发是windows,测试是centOs。两个都运行相同版本的mysql 5.0,就像我说的,测试运行完美,我没有对数据库进行任何结构更改。
I ran mysqlcheck and all tables came back ok.
我运行mysqlcheck,所有表都回来了。
6 个解决方案
#1
3
The first plan doesn't use index on shows.
第一个计划不使用节目索引。
If you are sure this index will help you, force it:
如果您确定此索引可以帮助您,请强制它:
SELECT ...
FROM ..., shows FORCE INDEX (biddate_idx) , ...
WHERE ...
Meanwhile, collect statistics for your tables.
同时,收集表格的统计数据。
#2
5
MySQL looks at the data in the tables as well as the query itself to decide which execution plan to use.
MySQL查看表中的数据以及查询本身,以确定要使用的执行计划。
If the data is the same in both databases, I'd suggest using ANALYZE or OPTIMIZE on all the tables in your query.
如果两个数据库中的数据相同,我建议在查询中的所有表上使用ANALYZE或OPTIMIZE。
#3
3
I would try regenerating statistics and rebuilding the indexes for all the tables and see if that fixes your problem - it's likely that is why the plans would be different.
我会尝试重新生成统计信息并重建所有表的索引,看看是否能解决您的问题 - 这可能就是为什么计划会有所不同。
There are lots of other things it could be (memory, disk, os differences, other loads, etc) but I'm assuming those probably aren't the issue since you mentioned that it ran fine before.
它可能有许多其他的东西(内存,磁盘,操作系统差异,其他负载等),但我认为那些可能不是问题,因为你之前提到它运行良好。
#4
2
I do have a similar issue on my master and slave server. Explain plan is different between these servers. The main issue, I am forcing an index on master server according to slave, even then the index is not being used, even after forcing it.
我的主服务器和从服务器上也有类似的问题。这些服务器之间的解释计划不同。主要问题是,我根据slave强制主服务器上的索引,即使这样,即使在强制它之后索引也没有被使用。
The only different I found between these server is that the slave version is slightly higher than the master.
我在这些服务器之间找到的唯一不同是slave版本略高于master版本。
I did analyze table on few tables and it came OK.I couldn't understand why mysql is not using the index even after I force it.
我确实在几张桌子上分析了表格,结果很好。我无法理解为什么即使在强制它之后mysql也没有使用索引。
Any help would be much appreciated.
任何帮助将非常感激。
#5
0
Are you sure these are from the same query? The explains aren't just slightly different, there are considerable differences between them:
你确定这些来自同一个查询吗?解释不仅略有不同,它们之间存在很大差异:
- The WHERE clause is hitting different tables (artists on dev, shows on testing)
- The number of rows it's hitting in genres is different (2 on dev, 531281 on testing).
- Other miscellaneous differences between the first and second explains (stuff in EXTRA mainly).
WHERE子句命中不同的表(dev上的艺术家,测试时显示)
它在类型中击中的行数是不同的(开发时为2,测试时为531281)。
第一个和第二个之间的其他杂项差异解释(主要是EXTRA中的东西)。
#6
0
We just experienced a very similar problem with a newly built master taking several minutes to execute the same query that old master (with less power) completed in a fraction of a second. We ran repair table quick on two of the myisam tables in the query and now the new master executes the query at least as fast as the old one.
我们刚刚遇到了一个非常类似的问题,一个新建的主人花了几分钟执行相同的查询,以便在几分之一秒内完成旧主人(功率较小)。我们在查询中的两个myisam表上快速运行修复表,现在新主服务器执行查询至少与旧查询一样快。
Thanks!
#1
3
The first plan doesn't use index on shows.
第一个计划不使用节目索引。
If you are sure this index will help you, force it:
如果您确定此索引可以帮助您,请强制它:
SELECT ...
FROM ..., shows FORCE INDEX (biddate_idx) , ...
WHERE ...
Meanwhile, collect statistics for your tables.
同时,收集表格的统计数据。
#2
5
MySQL looks at the data in the tables as well as the query itself to decide which execution plan to use.
MySQL查看表中的数据以及查询本身,以确定要使用的执行计划。
If the data is the same in both databases, I'd suggest using ANALYZE or OPTIMIZE on all the tables in your query.
如果两个数据库中的数据相同,我建议在查询中的所有表上使用ANALYZE或OPTIMIZE。
#3
3
I would try regenerating statistics and rebuilding the indexes for all the tables and see if that fixes your problem - it's likely that is why the plans would be different.
我会尝试重新生成统计信息并重建所有表的索引,看看是否能解决您的问题 - 这可能就是为什么计划会有所不同。
There are lots of other things it could be (memory, disk, os differences, other loads, etc) but I'm assuming those probably aren't the issue since you mentioned that it ran fine before.
它可能有许多其他的东西(内存,磁盘,操作系统差异,其他负载等),但我认为那些可能不是问题,因为你之前提到它运行良好。
#4
2
I do have a similar issue on my master and slave server. Explain plan is different between these servers. The main issue, I am forcing an index on master server according to slave, even then the index is not being used, even after forcing it.
我的主服务器和从服务器上也有类似的问题。这些服务器之间的解释计划不同。主要问题是,我根据slave强制主服务器上的索引,即使这样,即使在强制它之后索引也没有被使用。
The only different I found between these server is that the slave version is slightly higher than the master.
我在这些服务器之间找到的唯一不同是slave版本略高于master版本。
I did analyze table on few tables and it came OK.I couldn't understand why mysql is not using the index even after I force it.
我确实在几张桌子上分析了表格,结果很好。我无法理解为什么即使在强制它之后mysql也没有使用索引。
Any help would be much appreciated.
任何帮助将非常感激。
#5
0
Are you sure these are from the same query? The explains aren't just slightly different, there are considerable differences between them:
你确定这些来自同一个查询吗?解释不仅略有不同,它们之间存在很大差异:
- The WHERE clause is hitting different tables (artists on dev, shows on testing)
- The number of rows it's hitting in genres is different (2 on dev, 531281 on testing).
- Other miscellaneous differences between the first and second explains (stuff in EXTRA mainly).
WHERE子句命中不同的表(dev上的艺术家,测试时显示)
它在类型中击中的行数是不同的(开发时为2,测试时为531281)。
第一个和第二个之间的其他杂项差异解释(主要是EXTRA中的东西)。
#6
0
We just experienced a very similar problem with a newly built master taking several minutes to execute the same query that old master (with less power) completed in a fraction of a second. We ran repair table quick on two of the myisam tables in the query and now the new master executes the query at least as fast as the old one.
我们刚刚遇到了一个非常类似的问题,一个新建的主人花了几分钟执行相同的查询,以便在几分之一秒内完成旧主人(功率较小)。我们在查询中的两个myisam表上快速运行修复表,现在新主服务器执行查询至少与旧查询一样快。
Thanks!