为什么相同的确切查询会产生2个不同的MySQL解释结果? [英] Why does the same exact query produce 2 different MySQL explain results?

问题描述

我有一个简单的SELECT * From tv Where Client = 'ABCD'查询，当我执行EXPLAIN EXTENDED时，它给了我两个不同的结果.执行查询时，其中一个花费几毫秒，而另一个花费约3秒.为什么会给出两种不同的解释结果?是什么导致速度缓慢?

I have a simple SELECT * From tv Where Client = 'ABCD' query and when I do an EXPLAIN EXTENDED, it gives me two different results. When executing the query, one of them take a few milliseconds, while the other takes about 3 seconds. Why would it give two different explain results and what is causing the slowness?

慢速查询:

快速查询:

推荐答案

Q 为什么相同的精确查询会产生两个不同的MySQL解释结果?

Q Why does the same exact query produce 2 different MySQL explain results?

A 因为有些不同.如果不在查询中，则在两个表或数据库实例之间.

A Because something is different. If not in the query, then between the two tables, or database instances.

所有这些都应进行审查，以找出不同之处:

All of these should be reviewed, to find the difference:

• 它们是否在相同版本的MySQL(SHOW VARIABLES LIKE '%version%')上运行
• 实例是否运行相同的字符集(SHOW [GLOBAL] VARIABLES LIKE 'character_set%')
• 表列是否使用相同的字符集(SHOW CREATE TABLE)
• 两个表是否使用相同的存储引擎? (SHOW CREATE TABLE)
• 如果主键是复合键，列的顺序是否相同(SHOW CREATE TABLE)
• 统计信息是否最新且准确?
• 由于大量的插入，更新，删除活动，表之一是否碎片化了?
• 两个服务器上的MyISAM密钥缓存或InnoDB缓冲区大小是否相同?

• Are they running on the same version of MySQL (SHOW VARIABLES LIKE '%version%')
• Are the instances running the same characterset (SHOW [GLOBAL] VARIABLES LIKE 'character_set%')
• Are the table columns using the same characterset (SHOW CREATE TABLE)
• Are both tables using the same storage engine? (SHOW CREATE TABLE)
• If the primary key is a composite key, are the columns in the same order (SHOW CREATE TABLE)
• Are statistics up to date and accurate?
• Is one of the tables fragmented, due to a lot of insert,update,delete activity?
• Is the MyISAM key cache or the InnoDB buffers the same size on both servers?

这篇关于为什么相同的确切查询会产生2个不同的MySQL解释结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！