MySQL Master 和 Slave 执行计划截然不同
我有一个复杂的 MySQL 查询，它连接三个表并将一个表自连接到自身。
有一个主设备和一个从设备具有相同的数据和索引。与从属设备相比，主设备是一个功能强大的设备，但从设备上的查询运行速度要快 10 倍（在主设备轻负载期间）。
执行计划有很大不同。
Master execution plan
1, 'SIMPLE', 'table3_', 'const', 'PRIMARY', 'PRIMARY', '12', 'const', 1, 100.00, 'Using temporary; Using filesort'
1, 'SIMPLE', 'table2_', 'ref', 'PRIMARY,FK376E02E910238FCA', 'FK376E02E910238FCA', '13', 'const', 105, 100.00, 'Using where'
1, 'SIMPLE', 'table0_', 'ref', 'FK57012F937DD0DC02,FK57012F9398CD28D0', 'FK57012F9398CD28D0', '13', 'table2_.ID', 1515, 100.00, 'Using where'
1, 'SIMPLE', 'table1_', 'eq_ref', 'PRIMARY,FKE7E81F1ED170D4C9', 'PRIMARY', '8', 'table0_.FK_ID', 1, 100.00, 'Using where'
Slave execution plan
1, 'SIMPLE', 'table3_', 'const', 'PRIMARY', 'PRIMARY', '12', 'const', 1, 100.00, 'Using filesort'
1, 'SIMPLE', 'table1_', 'ref', 'PRIMARY,FKE7E81F1ED170D4C9', 'FKE7E81F1ED170D4C9', '9', 'const', 187398, 100.00, 'Using where'
1, 'SIMPLE', 'table0_', 'ref', 'FK57012F937DD0DC02,FK57012F9398CD28D0', 'FK57012F937DD0DC02', '9', 'table1_.ID', 1, 100.00, 'Using where'
1, 'SIMPLE', 'table2_', 'eq_ref', 'PRIMARY,FK376E02E910238FCA', 'PRIMARY', '12', 'table0_.FK_ID', 1, 100.00, 'Using where'
这些表以不同的顺序处理，主数据库同时使用临时表和文件排序，而从数据库仅使用文件排序。
哪些因素会导致不同的计划和如此巨大的执行时间？
更新：
这是否可能与索引统计信息有关？我计划在低容量期间在主服务器上运行分析表。 SHOW INDEX 显示 Master 和 Slave 之间的某些键的基数非常不同。
I have a complex MySQL query that joins three tables and self-joins one table to itself.
There is a Master and a Slave that have identical data and indices. The Master is a powerful box compared to the Slave, yet the query runs 10x faster on the Slave (during a period of light load for the Master).
The execution plans are vastly different.
Master execution plan
1, 'SIMPLE', 'table3_', 'const', 'PRIMARY', 'PRIMARY', '12', 'const', 1, 100.00, 'Using temporary; Using filesort'
1, 'SIMPLE', 'table2_', 'ref', 'PRIMARY,FK376E02E910238FCA', 'FK376E02E910238FCA', '13', 'const', 105, 100.00, 'Using where'
1, 'SIMPLE', 'table0_', 'ref', 'FK57012F937DD0DC02,FK57012F9398CD28D0', 'FK57012F9398CD28D0', '13', 'table2_.ID', 1515, 100.00, 'Using where'
1, 'SIMPLE', 'table1_', 'eq_ref', 'PRIMARY,FKE7E81F1ED170D4C9', 'PRIMARY', '8', 'table0_.FK_ID', 1, 100.00, 'Using where'
Slave execution plan
1, 'SIMPLE', 'table3_', 'const', 'PRIMARY', 'PRIMARY', '12', 'const', 1, 100.00, 'Using filesort'
1, 'SIMPLE', 'table1_', 'ref', 'PRIMARY,FKE7E81F1ED170D4C9', 'FKE7E81F1ED170D4C9', '9', 'const', 187398, 100.00, 'Using where'
1, 'SIMPLE', 'table0_', 'ref', 'FK57012F937DD0DC02,FK57012F9398CD28D0', 'FK57012F937DD0DC02', '9', 'table1_.ID', 1, 100.00, 'Using where'
1, 'SIMPLE', 'table2_', 'eq_ref', 'PRIMARY,FK376E02E910238FCA', 'PRIMARY', '12', 'table0_.FK_ID', 1, 100.00, 'Using where'
The tables are processed in different orders and the master DB uses both a temporary table and a filesort, while the slave uses only a filesort.
What factors could cause the differing plans with such vastly different execution times?
UPDATE:
Is it possible this has to do with index statistics? I plan to run an ANALYZE TABLE on the Master during a low-volume period. SHOW INDEX shows very different cardinality for some of the keys between Master and Slave.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
MySQL 根据收集的统计数据优化查询。
查看输出，您会发现它们使用不同的键，您可能必须添加键提示，甚至强制键
FROM table2_ JOIN
应变为
FROM table2_ USE KEY('FK376E02E910238FCA') JOIN
或 FORCE KEY
MySQL optimizes queries based upon collected statistics.
Looking at your output you see that they are using different keys, you might have to add key hints or even force keys
FROM table2_ JOIN
should become
FROM table2_ USE KEY('FK376E02E910238FCA') JOIN
Or FORCE KEY
对我来说，这看起来像是查询优化器中的一个错误。我会报告它。
两台服务器上的 MySQL 版本是否相同？
This looks like a bug in the query optimizer to me. I would report it.
Are both servers on the same version of MySQL?
我遇到了同样的问题，我发现原因是：基数不同。然后我运行分析表，基数相同，问题就消失了。
I met the same issue, and I found the reason was it: different cardinality. And then I ran analysis table, the cardinality were same and the problem had gone.