为什么“解释"命令在同一 SQL 语句上产生不同的结果? [英] Why 'explain' command yields different results on same SQL statement?

问题描述

我将 MySQL 数据库从一个环境迁移到另一个环境，并发现在迁移后，特定查询的运行速度比原来慢得多.

I migrated a MySQL database from one environment to another, and discovered that after migrating, a particular query runs extremely slower than it was.

我正在调查带有 'explain' 关键字的语句(如下)，发现 'explain' 命令在不同的服务器上给出不同的输出.

I was investigating the statement with 'explain' keyword (below) and found that the 'explain' command gives different output, on different servers.

explain select distinct j.job,f.path,p.path  
from fixes f
join  jobs j on  f.job=j.id 
join  paths p  on p.id =f.path  
where p.path like '//depot1/Dev\-trunk/%' ;

对于原始的，它给出:

    1   SIMPLE  p   range   PRIMARY,path    path    752     **19**  Using where; Using index; Using temporary
    1   SIMPLE  f   ref path,job    path    8   p.p.id  **6448356** 
    1   SIMPLE  j   eq_ref  PRIMARY PRIMARY 8   p.f.job **1**   

并且相同的语句在新环境中产生不同的输出:

And the same statement yields different output on the new environment:

    1   SIMPLE  j   index   PRIMARY job 62      **68486**   Using index; Using temporary
    1   SIMPLE  f   ref path,job    job 8   p.j.id  **812** 
    1   SIMPLE  p   eq_ref  PRIMARY,path    PRIMARY 8   p.f.path    **1**   Using where

推荐答案

仅供参考，事实证明，在迁移数据库时，引擎优化器会使用一些统计信息来决定最佳策略，例如连接表，这显然没有被复制.

FYI, it turns out that when migrating the database, there are some stats used for the engine optimizer to decide the best strategy to e.g. join tables, that apparently was not copied.

解决方案是在每个表上运行 ANALYZE 以重建"此类信息.所以在对每个表做ANALYZE后，再次运行explain命令，输出一致，查询速度大幅提升.

The solution is to run ANALYZE on each of the tables to 'rebuild' such information. So after doing the ANALYZE to each of the table, and run the explain command again, the output agrees and the query speed increases dramatically.

这篇关于为什么“解释"命令在同一 SQL 语句上产生不同的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！