将mysql从5.5升级到5.6后的缓慢查询

Question

我们正在将mysql从5.5升级到5.6，现在有些查询非常慢。

之前花了0.005秒的查询现在需要49秒。

对5.6的查询跳过了索引，似乎：

+----+-------------+-------+-------+----------------------------------------------------+---------+---------+------+--------+-------------+
| 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上没有跳过：

+----+-------------+-------+-------------+----------------------------------------------------+----------------------------------------------------+---------+------+------+----------------------------------------------------------------------------------------------+
| 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转储中创建的。

当我在5.6上进行导入时，是否没有构造这些索引？如何强制创建索引？

查询：

SELECT  `pens`.* FROM `pens`  WHERE (slug_hash = 'style' OR slug = 'style') ORDER BY `pens`.`id` DESC LIMIT 1

编辑:删除架构

Answer 1

最后，上述公认的答案是正确的。

来自@RandomSeed的帮助让我朝着正确的方向思考。基本上，在5.6中创建的优化计划与5.5中的优化计划有很大的不同，因此您可能不得不像我一样重新工作您的查询。

我最终没有使用FORCE INDEX，而是删除了查询的部分，直到我确定是什么导致5.6遗漏了索引。然后，我重新设计了应用程序逻辑来处理这个问题。

Answer 2

v5.6中的缓慢查询是由于引擎无法或决定不合并两个相关索引(index_contents_on_slug_hash、index_contents_on_slug)以处理查询。请记住，每个表只能使用一个索引。为了能够利用同一表上的多个索引，需要在运行时将这些索引预合并到一个(内存中)中。这就是执行计划中index_merge和Using union(...)通知的含义。显然，这会消耗时间和记忆。

快速修复(可能也是首选的解决方案)：在slug和slug_hash上添加两列索引。

ALTER TABLE pens ADD INDEX index_contents_on_slug_and_slug_hash (
    slug, slug_hash
);

现在，您的新服务器可能无法合并这些索引，因为它会导致索引太大，无法适应缓冲区。对于key_buffer_size (如果表是MyISAM)或innodb_buffer_pool_size (如果InnoDB)，新服务器的值可能比以前的安装小得多。