切换服务器时Mysql查询爬行

Question

上周，我在从开发测试转移时遇到了一个问题，其中一个在开发中完美运行的查询正在我的测试服务器上爬行。

通过在查询中的索引之一添加 FORCE INDEX 已修复此问题。

现在我已将相同的数据库加载到生产服务器中(并且它正在使用 FORCE INDEX 命令运行，并且速度再次变慢。

知道什么会导致这样的事情发生吗？测试和生产都运行相同的操作系统和 mysql 版本(与开发不同)。

这是查询及其解释。

 EXPLAIN SELECT showsdate.bid, showsdate.bandid, showsdate.date, showsdate.time,
    -> showsdate.title, showsdate.name, showsdate.address, showsdate.rank, showsdate.city, showsdate.state,
    ->  showsdate.lat, showsdate.`long` , tickets.link, tickets.lowprice, tickets.highprice, tickets.source
    -> , tickets.ext, artistGenre, showsdate.img
    -> FROM tickets
    -> RIGHT OUTER JOIN (
    -> SELECT shows.bid, shows.date, shows.time, shows.title, artists.name, artists.img, artists.rank, artists
    -> .bandid, shows.address, shows.city, shows.state, shows.lat, shows.`long`, GROUP_CONCAT(genres.genre SEPARATOR
    ->  ' | ') AS artistGenre
    -> FROM shows FORCE INDEX (biddate_idx)
    -> JOIN artists ON shows.bid = artists.bid JOIN genres ON artists.bid=genres.bid
    -> WHERE  `long` BETWEEN -74.34926984058 AND -73.62463215942 AND lat BETWEEN 40.39373515942 AND 41.11837284058
    ->  AND shows.date >= '2009-03-02'  GROUP BY shows.bid, shows.date ORDER BY  shows.date, artists.rank  DESC
    ->  LIMIT 0, 30
    -> )showsdate ON showsdate.bid = tickets.bid AND showsdate.date = tickets.date;
+----+-------------+------------+--------+---------------+-------------+---------+------------------------------+--------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys | key         | key_len | ref                          | rows   | Extra                                        |
+----+-------------+------------+--------+---------------+-------------+---------+------------------------------+--------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL        | NULL    | NULL                         |     30 |                                              |
|  1 | PRIMARY     | tickets    | ref    | biddate_idx   | biddate_idx | 7       | showsdate.bid,showsdate.date |      1 |                                              |
|  2 | DERIVED     | genres     | index  | bandid_idx    | bandid_idx  | 141     | NULL                         | 531281 | Using index; Using temporary; Using filesort |
|  2 | DERIVED     | shows      | ref    | biddate_idx   | biddate_idx | 4       | activeHW.genres.bid          |      5 | Using where                                  |
|  2 | DERIVED     | artists    | eq_ref | bid_idx       | bid_idx     | 4       | activeHW.genres.bid          |      1 |                                              |
+----+-------------+------------+--------+---------------+-------------+---------+------------------------------+--------+----------------------------------------------+

Answer 1

当你问这个关于开发 -> 测试之间差异的问题时，我想我插话了。

您是否尝试过重建索引并重新计算统计信息？一般来说，强制索引是一个坏主意，因为优化器通常会对使用哪些索引做出很好的选择。然而，这是假设它有良好的统计数据可供使用，并且索引没有严重分散。

预计到达时间:

要重建索引，请使用:

REPAIR TABLE tbl_name QUICK;

重新计算统计数据:

ANALYZE TABLE tbl_name;