MySQL在5.5中长期运行的查询在5.6中非常快 [英] MySQL long running query in 5.5 is very fast in 5.6

问题描述

我有一个复杂的数据库架构. 我在MySQL 5.6中建立了一个大查询，该查询连接了20个表. 当我在Mac上的MySQL 5.6的本地副本上运行查询时，我得到1-2毫秒的响应时间. 当我在Ubuntu 5.5上的MySQL 5.5上运行相同的查询(通过mysql转储使用相同的数据)时，我得到60秒的响应时间. 我对慢速查询进行了分析，发现在统计"阶段几乎花了整整60秒的时间. 统计"阶段显然是MySQL在确定如何最好地执行所有联接.

I have a complex database schema. I built a big query in MySQL 5.6 which joins 20 tables. When I run the query on a local copy of MySQL 5.6 on my Mac I am getting 1-2 millisecond response times. When I run the same query (with the same data via a mysql dump) on MySQL 5.5 on Ubuntu I am getting 60 second response times. I ran profiling on the slow query and discovered that almost the full 60 seconds is being spent in the 'statistics' stage. The 'statistics' stage is apparently where MySQL is determining how best to perform all the joins.

MySQL 5.6如何在毫秒内完成此操作，而在5.5中又要花整整一分钟的时间?

How can MySQL 5.6 perform this in milliseconds and in 5.5 it is taking a full minute?

关于首先要解决什么的任何想法?

Any ideas as to what to address first?

推荐答案

MySQL 5.6(和MariaDB)优化器在子查询和联接方面做得更好.我想您的查询在5.5中没有得到很好的优化.运行:

MySQL 5.6 (and MariaDB) optimizer does a better job with subqueries and joins. I guess that your query is not well optimized in 5.5. Run:

EXPLAIN SELECT ...

在两个MySQL版本上的

，以了解MySQL应该做什么以及5.5中真正要做什么.然后，尝试强制使用5.6使用的索引.

on both MySQL versions, to understand what MySQL should do, and what really does in 5.5. Then, try to FORCE the use of the index used by 5.6.

编辑:如果它是子查询，则可以将其重写为联接.在某些情况下，MySQL 5.6内部执行此操作.

if it is a subquery, you can rewrite it as a join. MySQL 5.6 does this internally in some cases.

这篇关于MySQL在5.5中长期运行的查询在5.6中非常快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！