5.7和5.6的mysql_版本5.7与5.6的MySQL性能

我注意到一个特殊的性能问题,我不确定如何处理.

我正在将Web应用程序从一台服务器迁移到另一台服务器,其规格非常相似.新服务器通常优于旧服务器.

旧服务器正在运行MySQL 5.6.35

新服务器正在运行MySQL 5.7.17

新旧服务器都具有几乎相同的MySQL配置.

新旧服务器都运行完全相同的完全相同的数据库.

有问题的Web应用程序是Magento 1.9.3.2.

在Magento中,具有以下功能

Mage_Catalog_Model_Category :: getChildrenCategories()

用于列出给定特定类别的所有直接子类别.

在我的情况下,此函数最终向下查询：

SELECT `main_table`.`entity_id`

, main_table.`name`

, main_table.`path`

, `main_table`.`is_active`

, `main_table`.`is_anchor`

, `url_rewrite`.`request_path`

FROM `catalog_category_flat_store_1` AS `main_table`

LEFT JOIN `core_url_rewrite` AS `url_rewrite`

ON url_rewrite.category_id=main_table.entity_id

AND url_rewrite.is_system=1

AND url_rewrite.store_id = 1

AND url_rewrite.id_path LIKE 'category/%'

WHERE (main_table.include_in_menu = '1')

AND (main_table.is_active = '1')

AND (main_table.path LIKE '1/494/%')

AND (`level` <= 2)

ORDER BY `main_table`.`position` ASC;

虽然此查询的结构对于任何Magento安装都是相同的,但Magento安装到Magento安装之间的值与该功能正在查看的类别之间显然会有轻微差异.

我的catalog_category_flat_store_1表有214行.

我的url_rewrite表有1,734,316行.

这个查询,当它自己直接执行到MySQL时,在MySQL版本之间执行的方式差异很大.

我正在使用SQLyog来分析此查询.

在MySQL 5.6中,上述查询在0.04秒内执行.此查询的配置文件如下所示：https://codepen.io/Petce/full/JNKEpy/

在MySQL 5.7中,上述查询在1.952秒内执行.此查询的配置文件如下所示：https://codepen.io/Petce/full/gWMgKZ/

正如您所看到的,几乎完全相同的设置上的相同查询实际上慢了2秒,我不确定为什么.

出于某种原因,MySQL 5.7不希望使用表索引来帮助生成结果集.

那些有更多经验/知识的人可以解释这里发生了什么,以及如何解决这个问题？

我认为这个问题与MYSQL 5.7优化器的工作方式有关.出于某种原因,它似乎认为全表扫描是要走的路.我可以通过将max_seeks_for_key设置得非常低(如100)或将range_optimizer_max_mem_size设置为非常低以强制它发出警告来大幅提高查询性能.

执行其中任何一项操作都会将查询速度提高近10倍,然后降低到0.2秒,但是,这仍然比MYSQL 5.6在0.04秒内执行的速度要慢,而且我认为这些都不是一个好主意,因为我不是确定是否会有其他影响.

由于它是由Magento框架生成的,因此修改查询也非常困难,并且需要定制我想避免的Magento代码库.我甚至不确定它是否是唯一受影响的查询.

我已经为我的MySQL安装包含了次要版本.我现在正在尝试将MySQL 5.7.17更新到5.7.18(最新版本)以查看是否有任何性能更新.

升级到MySQL 5.7.18后,我没有看到任何改进.为了使系统恢复到稳定的高性能状态,我们决定降级回MySQL 5.6.30.在进行降级后,我们看到了即时的改进.

上述查询在新服务器上的MySQL 5.6.30中执行,执行时间为0.036秒.