首页 文章

版本5.7与5.6的MySQL性能

提问于
浏览
7

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

我正在将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秒 .

2 回答

  • 2

    哇!这是我第一次看到Profiling中有用的东西 . 动态创建索引是Oracle的一项新优化功能 . 但看起来这不是本案的最佳方案 .

    首先,我建议你在http://bugs.mysql.com提交一个错误 - 他们不喜欢有回归,特别是这种恶劣 . 如果可能,请提供 EXPLAIN FORMAT=JSON SELECT... 和"Optimizer trace" . (我不接受将晦涩的可调参数作为可接受的答案进行调整,但感谢您发现它们 . )

    回到帮助你......

    • 如果您不需要 LEFT ,请不要使用它 . 当'right'表中没有匹配的行时,它返回 NULLs ;你的情况会发生吗?

    • 请提供 SHOW CREATE TABLE . 同时,我猜你没有 INDEX(include_in_menu, is_active, path) . 前两个可以按任何顺序排列; path 需要持久 .

    • INDEX(category_id, is_system, store_id, id_path) ,最后是 id_path .

    • 您的查询似乎有一个适合转换为子查询的模式:

    (注意:这甚至保留了 LEFT 的语义 . )

    SELECT  `main_table`.`entity_id` , main_table.`name` , main_table.`path` ,
            `main_table`.`is_active` , `main_table`.`is_anchor` ,
            ( SELECT  `request_path`
                FROM  url_rewrite
                WHERE  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/%' 
            ) as request_path
        FROM  `catalog_category_flat_store_1` AS `main_table`
        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
        LIMIT  0, 1000
    

    (建议的索引也适用于此 . )

  • 0

    这只是对@Nigel Ren的评论不是答案

    在这里你可以看到LIKE也使用索引 .

    mysql> SELECT *
        -> FROM testdb
        -> WHERE
        -> vals LIKE 'text%';
    +----+---------------------------------------+
    | id | vals                                  |
    +----+---------------------------------------+
    |  3 | text for line number 3                |
    |  1 | textline 1 we rqwe rq wer qwer q wer  |
    |  2 | textline 2 asdf asd fas f asf  wer 3  |
    +----+---------------------------------------+
    3 rows in set (0,00 sec)
    
    mysql> EXPLAIN
        -> SELECT *
        -> FROM testdb
        -> WHERE
        -> vals LIKE 'text%';
    +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    | id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
    +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    |  1 | SIMPLE      | testdb | NULL       | range | vals          | vals | 515     | NULL |    3 |   100.00 | Using where; Using index |
    +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    1 row in set, 1 warning (0,01 sec)
    
    mysql>
    

    sample with LEFT()

    mysql> SELECT *
        -> FROM testdb
        -> WHERE
        -> LEFT(vals,4) = 'text';
    +----+---------------------------------------+
    | id | vals                                  |
    +----+---------------------------------------+
    |  3 | text for line number 3                |
    |  1 | textline 1 we rqwe rq wer qwer q wer  |
    |  2 | textline 2 asdf asd fas f asf  wer 3  |
    +----+---------------------------------------+
    3 rows in set (0,01 sec)
    
    mysql> EXPLAIN
        -> SELECT *
        -> FROM testdb
        -> WHERE
        -> LEFT(vals,4) = 'text';
    +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    | id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
    +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    |  1 | SIMPLE      | testdb | NULL       | index | NULL          | vals | 515     | NULL |    5 |   100.00 | Using where; Using index |
    +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    1 row in set, 1 warning (0,01 sec)
    
    mysql>
    

相关问题