我注意到我不确定如何处理的特定性能问题。
我正在以非常相似的规格将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倍,但要使MySQL 5.6在0.04秒内执行,这仍然是较慢的,我认为这些都不是一个好主意,因为我'我不确定是否有其他影响。
也很难修改查询,因为它是由Magento框架生成的,并且需要自定义Magento Codebase,我想避免。我什至不确定这是否是唯一的查询。
我已经包含了我的MySQL安装的次要版本。我现在正在尝试将MySQL 5.7.17更新为5.7.18(最新构建(,以查看是否有任何更新的性能。
升级到MySQL 5.7.18后,我没有看到任何改进。为了使系统恢复到稳定的高性能状态,我们决定降级到MySQL 5.6.30。降级后,我们看到了即时改进。
上述查询在New Server在0.036秒内执行的MySQL 5.6.30执行。
哇!这是我第一次看到分析中有用的东西。动态创建索引是Oracle的新优化功能。但看来这不是这种情况的最佳计划。
首先,我建议您在http://bugs.mysql.com上提起一个错误 - 他们不喜欢进行回归,尤其是这种令人毛骨悚然的回归。如果可能,请提供EXPLAIN FORMAT=JSON SELECT...
和"优化器跟踪"。(我不接受调整晦涩的可调节材料作为可接受的答案,但感谢您发现它们。(
回到帮助您...
- 如果您不需要
LEFT
,请不要使用它。当"右"表中没有匹配行时,它将返回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
(建议的索引也适用。(
这不是@nigel ren评论的答案
在这里您可以看到它也可以使用索引。
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>
luett((
示例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>