mysql在不同的服务器上解释不同的结果，相同的查询，相同的数据库 [英] mysql explain different results on different servers, same query, same db
问题描述
经过大量的工作，我终于得到了一个相当复杂的查询，可以非常顺畅地工作并非常快速地返回结果.
After much work I finally got a rather complicated query to work very smootly and return results very quickly.
它在开发人员和测试中都运行良好，但是现在测试速度大大降低了. 解释性查询在开发人员身上需要0.06秒，而在测试中大致相同，现在在测试中为7秒.
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.
说明略有不同，我不确定为什么会这样 开发者的解释
The explains are slightly different, and I'm not sure why this would be The explain from 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
|
+----+-------------+------------+--------+-------------------------+------------
和测试中
| 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)
即使查询完全相同，表的顺序也不同. 这会导致减速吗?如果是这样，我该如何解决? 开发人员是Windows，测试是centOs. 两者都运行相同版本的mysql 5.0，并且就像我说的那样，测试运行良好，并且我没有对数据库进行任何结构上的更改.
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.
我运行mysqlcheck，所有表恢复正常.
I ran mysqlcheck and all tables came back ok.
推荐答案
第一个计划不使用shows上的索引.
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.
这篇关于mysql在不同的服务器上解释不同的结果，相同的查询，相同的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
