如何调查和优化缓慢的MySQL查询? [英] How to investigate and optimise a slow MySQL query?

问题描述

我有一个在MySQL上运行的drupal 7站点.该网站上的某些页面加载速度非常慢.

I have a drupal 7 site running on MySQL. Some pages on the site are excruciatingly slow to load.

我调查了加载时间，并确定了罪魁祸首查询，在某些页面上执行该查询通常需要10秒钟.在一种情况下，甚至要花70秒钟！

I investigated load times and have identified the culprit query, which is regularly taking 10s to execute on some pages. In one case it even took 70s!

该查询来自视图"，该视图根据站点分类法从站点的其他位置生成相关内容的简短列表.

The query is from a "view" that generates a short list of related content from elsewhere in the site based on the site taxonomy.

这是一个慢页上的示例(带有参数):

This is an example (with arguments) from one slow page:

SELECT node.nid AS nid, node.title AS node_title, node.created AS node_created, 'podcasts:panel_pane_3' AS view_name, RAND() AS random_field 
FROM node node 
LEFT JOIN (SELECT td.*, tn.nid AS nid 
    FROM taxonomy_term_data td 
    LEFT JOIN taxonomy_vocabulary tv ON td.vid = tv.vid 
    LEFT JOIN taxonomy_index tn ON tn.tid = td.tid 
    WHERE (tv.machine_name IN ('listen')) ) taxonomy_term_data_node 
ON node.nid = taxonomy_term_data_node.nid 
LEFT JOIN taxonomy_index taxonomy_index ON node.nid = taxonomy_index.nid 
WHERE (( (taxonomy_index.tid IN ('472', '350', '742', '681', '3907', '1541', '411', '636', '990', '7757', '680', '743', '11479', '8106', '566', '2230', '11480', '766')) 
  AND (node.nid != '191314' OR node.nid IS NULL) ) 
  AND(( (node.status = '1') 
  AND (node.type IN ('article', 'experiment', 'interview', 'podcast', 'question')) ))) 
ORDER BY random_field ASC, node_created DESC 
LIMIT 5 OFFSET 0

从最初的研究中，我认为可能是添加索引的情况，但是有关表的列似乎已有索引条目.

From initial research I thought it would be a case of adding indices, but the columns of the tables concerned seem to have existing index entries.

因此，我不确定该如何进行，如果有人可以帮助我，我真的会很重视一些指导吗?

I'm therefore uncertain how to proceed and would really value some guidance if anyone can help me please?

PS-我确实要求MySQL自我解释，这是生成的内容:

PS - I did ask MySQL to Explain itself and this is what was generated:

推荐答案

感谢大家的指导.但是，我已经在剑桥大学的安迪·巴蒂(Andy Batey)的帮助下解决了这个问题.

Thanks for the guidance above, everyone. However, I have solved this with the help of Andy Batey at Cambridge University.

线索是比较上面的查询在MySQL v5.5(非常快的结果)和v5.7(非常慢的结果)上运行时生成的EXPLAIN语句；他们的查询在两个平台上的处理方式大不相同.

The clue was comparing the EXPLAIN statements generated when the query above was run on MySQL v5.5 (very fast results) versus v5.7 (very slow results); they query was being handled quite differently on the two platforms.

关键是将其添加到my.cnf:

The key was adding this to my.cnf:

optimizer_switch='derived_merge=off'

现在，本机查询的执行时间为50ms或更短，而之前的查询时间为12s或更长时间.

Now the native query executes in 50ms or less, compared with 12s or longer before.

我希望这对遇到此升级问题的其他人有所帮助.

I hope this helps anyone else who runs into this upgrade problem.

这篇关于如何调查和优化缓慢的MySQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！