问题详情放这了

http://www.itpub.net/forum.php?mod=viewthread&tid=2150410&page=1&extra=#pid23735133

执行计划可以贴图吗？ 你那太乱了，看不了执行计划

看看optimizer的参数设置，表的碎片，从库的磁盘配置。

快实例的参数配置
mysql> show variables like '%optimizer%'
-> ;
+------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on |
| optimizer_trace | enabled=off,one_line=off |
| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit | 1 |
| optimizer_trace_max_mem_size | 16384 |
| optimizer_trace_offset | -1 |
| range_optimizer_max_mem_size | 8388608 |
+------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.01 sec)

慢实例的参数配置
mysql> show variables like '%optimizer%';
+------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on |
| optimizer_trace | enabled=off,one_line=off |
| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit | 1 |
| optimizer_trace_max_mem_size | 16384 |
| optimizer_trace_offset | -1 |
| range_optimizer_max_mem_size | 8388608 |
+------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set, 1 warning (0.00 sec)

执行计划主要就是驱动表不一致。 

在不考虑过滤条件的情况下。w是最佳的驱动表。

但这个语句明显有m0表的过滤。  mysql为什么驱动表选错，这个就需要更深的看了。比如m0的过滤条件需要扫描多少行？   看统计信息  两个实例虽行数一致，但统计信息是不一致的。看刷一下统计信息会不会计划一致。

3011执行快的trace.txt ，3011执行慢的trace.txt  这2个文件的下面部分不一样

attaching_conditions_to_tables (已选执行计划改造原有的where条件语句)
refine_plan （改善之后的执行计划）

至于为什么会不一样，需要看你的2个从库my.cnf配置文件参数，还有服务器性能方面参数等

你看看我楼上的回复

快：
m0->m->w cost_for_plan": 3.13e6 确实是该连接顺序胜出
w->m->m0 “cost_for_plan”: 3.96e6
m-> “cost_for_plan”: 4.87e6
慢：
m0->w->m “cost_for_plan”: 3.6e11
m0->m->w “cost_for_plan”: 5.51e6
w->m->m0 3.99e6 确实是该连接顺序胜出

CB0计算比较奇怪：
rows1.0 MEMORY_ROW_READ_COST? +rows0.2 ROW_EVALUATE_COST;
按说应该算MEMORY_BLOCK_READ_COST*pages
”快的“在连接第三个表的还算错了，搞不懂

如下比较：
快：wm_warehousestockpigextend_NumericalOrderDetail_index “cost”: 130500 应该估算错了
慢：“index”: “wm_warehousestockpigextend_NumericalOrderDetail_index”,“cost”: 2.58e6
快：
“table”: "ms_transferhourse m0"
“rows_to_scan”: 275094,
“access_type”: “range”,
“range_details”: {
“used_index”: “idx_DataDate”
},
“resulting_rows”: 275094,
“cost”: 385133,
“chosen”: true
2750941.2+2750940.2=330112+55018
“table”: "ms_transferhoursedetail m",
“access_type”: “ref”,
“index”: “idx_NumericalOrder”,
“rows”: 7.9064,
“cost”: 2.61e6,

2750947.90641.2= 2.61e6

                          "access_type": "ref",
                          "index": "wm_warehousestockpigextend_NumericalOrderDetail_index",
                          "rows": 1,
                          "cost": 130500,
                          "chosen": true

慢：
“table”: "ms_transferhourse m0"
{
“rows_to_scan”: 248352,
“access_type”: “range”,
“range_details”: {
“used_index”: “idx_DataDate”
},
“resulting_rows”: 248352,
“cost”: 347694,
“chosen”: true
2483521.2+2483520.2=347694
“table”: "ms_transferhoursedetail m"
{
“access_type”: “ref”,
“index”: “idx_NumericalOrder”,
“rows”: 8.7572,
“cost”: 2.61e6,
“chosen”: true
}

                          "access_type": "ref",
                          "index": "wm_warehousestockpigextend_NumericalOrderDetail_index",
                          "rows": 1.0132,
                          "cost": 2.58e6,
                          "chosen": true
                        }

CBO计算把随机读和顺序读在做多表关联时，都计cost 1;按实际IO对比，相差100倍左右，再考虑clusterratio 80%左右顺序，即20%随机读，在做连接索引查找时候产生的的随机读占20%，运行时间应该被低估20倍，循环次数多4倍的话，差不多多80倍运行时间（前提是时间消耗都在连接产生的随机读上）