为什么mysql在同一查询的2个不同服务器上显示2个不同的结果 [英] Why does mysql show 2 different results on 2 different servers for the same query

问题描述

我有相同的MySQL SQL语句运行在2个不同的数据库（我的本地机器和我的生产机器）。在我的本地机器上的一个运行更快，而生产的一个是缓慢的。以下是每个项目的EXPLAIN结果。

I have the same MySQL SQL statement running on 2 different databases (my local machine and my production machine). The one on my local machine runs faster while the one on production is slow. Here are the EXPLAIN results on each.

本地计算机

生产机器

我宁愿不发布精确的查询，如果我不必。

I'd rather not post the exact query if I don't have to.

我能区分的唯一的区别是，我的本地机器运行版本5.6，而生产服务器运行5.5。此外，我的服务器上的数据是3天，这是不是很多记录。具体来说，我正在寻找解释的第2行，其中一种类型读取ref，其他读取ALL，有超过28k行被读取的差异。这两个结构之间的唯一区别是没有被查询使用的TimeModified字段

The only difference I can tell between the 2 is that my local machine is running version 5.6 while the production server is running 5.5. Also, the data on my server is 3 days old which isn't many records. Specifically, I'm looking at row 2 of the explain where one type reads "ref" and the other reads "ALL" and there's a difference of over 28k rows being read. The only difference between the 2 structures is the TimeModified field which isn't being used by the query

推荐答案

在MySQL 5.5及更早版本，派生表从不有索引。导出表的唯一访问方式是全扫描。 （这是您在5.5服务器的EXPLAIN输出中看到的 ALL 。）

In MySQL 5.5 and earlier, a derived table never had indexes. The only way a derived table would be accessed was by a full scan. (That's the ALL you see in the EXPLAIN output from the 5.5 server.)

使用MySQL 5.6.3，MySQL可以向派生表添加索引，这可以提高性能。 （请注意5.6服务器的EXPLAIN输出中的索引名称： < auto_key1> ）。

With MySQL 5.6.3, MySQL has the ability to add an index to a derived table, which can improve performance. (Note the name of the index: <auto_key1> in the EXPLAIN output from the 5.6 server).

参考： https：// dev。 mysql.com/doc/refman/5.6/en/subquery-optimization.html

这篇关于为什么mysql在同一查询的2个不同服务器上显示2个不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！