将mysql从5.5升级到5.6后查询速度慢 [英] Slow query after upgrade mysql from 5.5 to 5.6
问题描述
我们正在将mysql从5.5升级到5.6，现在一些查询速度非常慢。
We're upgrading mysql from 5.5 to 5.6 and some queries are deadly slow now.
之前需要0.005秒的查询现在需要49秒。
Queries that took 0.005 seconds before are now taking 49 seconds.
5.6上的查询正在跳过索引，似乎：
Queries on 5.6 are skipping the indexes, it seems:
+----+-------------+-------+-------+----------------------------------------------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------------------------------------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | pens | index | index_contents_on_slug,index_contents_on_slug_hash | PRIMARY | 4 | NULL | 471440 | Using where |
+----+-------------+-------+-------+----------------------------------------------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)
但不会在5.5上跳过：
But are not being skipped on 5.5:
+----+-------------+-------+-------------+----------------------------------------------------+----------------------------------------------------+---------+------+------+----------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+----------------------------------------------------+----------------------------------------------------+---------+------+------+----------------------------------------------------------------------------------------------+
| 1 | SIMPLE | pens | index_merge | index_contents_on_slug,index_contents_on_slug_hash | index_contents_on_slug_hash,index_contents_on_slug | 768,768 | NULL | 2 | Using union(index_contents_on_slug_hash,index_contents_on_slug); Using where; Using filesort |
+----+-------------+-------+-------------+----------------------------------------------------+----------------------------------------------------+---------+------+------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
这两个DB都是从同一个mysql转储创建的。
Both DBs were created from the same mysql dump.
当我在5.6上导入时，这些索引是不是被构造的？如何强制创建索引？
Are these indexes not being constructed when I do the import on 5.6? How do I force the index creation?
查询：
SELECT `pens`.* FROM `pens` WHERE (slug_hash = 'style' OR slug = 'style') ORDER BY `pens`.`id` DESC LIMIT 1
编辑：删除架构
推荐答案
最终上面接受的答案是正确的。
Ultimately the accepted answer above is correct.
来自@RandomSeed的帮助让我思考正确的方向。基本上5.6中创建的优化计划与5.5中的优化计划有很大不同，所以你可能不得不重做你的查询，就像我一样。
The help from @RandomSeed got me thinking in the right direction. Basically the optimization plans created in 5.6 are significantly different from those in 5.5, so you'll probably have to rework your query, much like I did.
我没有结束使用 FORCE INDEX ，但是删除了部分查询，直到我确定导致5.6错过索引的原因。然后我重新设计应用程序逻辑来处理它。
I did not end up using the FORCE INDEX, but instead removed portions of the query until I determined what was causing 5.6 to miss the index. Then I reworked the application logic to deal with that.
这篇关于将mysql从5.5升级到5.6后查询速度慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
