mysql explain : inner join analysis ; <eq_ref> better than <ref>

explain
select t.order_sn, t.cust_code, ti.tms_order_other_info_id, sp.province_name, sc.city_name, sr.region_name, st.town_name, t.buyer_address 
  from tms_order t inner join tms_order_other_info ti on t.tms_order_id=ti.tms_order_id
  inner join crm_cust c on t.cust_code=c.cust_code
  left join sb_province sp on t.buyer_state=sp.province_code
  inner join sb_city sc on t.buyer_city=sc.city_code
  inner join sb_region sr on t.buyer_area_id=sr.region_code
  left join sb_town st on t.buy_town = st.town_code 
  where t.created_office = 'VIP_NH' and c.created_office='VIP_NH'
  #and (t.cust_code='NHLDP053' or 'NHLDP053' = '' )
  and t.is_autopicked in (1, 3) 
  and t.order_sub_type = 11 
  and t.order_status in (1,2) 
  and t.cust_code is not null 
  and t.add_time > date_sub(now(),interval 10 day) 
  and c.is_showpoint = 1 and ifnull(ti.has_matchpoint, 0) = 0 

  order by t.cust_code, t.created_dtm_loc limit 500

上面的tms_order等表,是千万级数量数据.

上面的执行效率正常,执行计划也正常,

而把left join sb_province sp 换成 inner join sb_province sp 后::  效率就直线下降

explain
select t.order_sn, t.cust_code, ti.tms_order_other_info_id, sp.province_name, sc.city_name, sr.region_name, st.town_name, t.buyer_address 
  from tms_order t inner join tms_order_other_info ti on t.tms_order_id=ti.tms_order_id
  inner join crm_cust c on t.cust_code=c.cust_code
  inner join sb_province sp on t.buyer_state=sp.province_code
  inner join sb_city sc on t.buyer_city=sc.city_code
  inner join sb_region sr on t.buyer_area_id=sr.region_code
  left join sb_town st on t.buy_town = st.town_code 
  where t.created_office = 'VIP_NH' and c.created_office='VIP_NH'
  #and (t.cust_code='NHLDP053' or 'NHLDP053' = '' )
  and t.is_autopicked in (1, 3) 
  and t.order_sub_type = 11 
  and t.order_status in (1,2) 
  and t.cust_code is not null 
  and t.add_time > date_sub(now(),interval 10 day) 
  and c.is_showpoint = 1 and ifnull(ti.has_matchpoint, 0) = 0 
  order by t.cust_code, t.created_dtm_loc limit 500

sp表的type就变成ALL了,并且执行效率直线下降,why?????

是因为inner join的方式 ,优化会执行的过程是这样的:

->先range analysis 分析所有表的大概记录数,其中sp表是几十行数量级的也就是最小的,它就会作为距动表,放在最左边,决定了sp表的顺序

->计算全表扫描与使用索引的IO成本,决定sp表的访问方式,是用不用索引,用什么类型的索引,在这里它是用 ref索引,

->根据这个排序,穷举分析他们的执行计划成本,可见sp表数据太小,先join它成本是最小的,(也可以理解为优化器在inner join中一般不用数据量太小的表的索引)

另外:

发现除了st表使用eq_ref索引,其他的表却使用ref索引,  就是说,st表的扫描比其他表的快...why???

是因为,sp表所引用的索引,它是一个uniqe key,

(eq_ref:从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。)

(ref: 该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。)

(ref_or_null: 这种连接类型类似 ref，不同的是mysql会在检索的时候额外的搜索包含null 值的记录。这种连接类型的优化是从mysql4.1.1开始的，它经常用于子查询。)

效率 eq_ref > ref > ref_or_null