MySQL优化器 limit影响的case

测试的用例中,因为limit的大小不同,而产生了完全不同的执行计划:

1. 测试case:  

create table t1 (
  f1 int(11) not null,
  f2 int(11) not null,
  f3 int(11) not null,
  f4 tinyint(1) not null,
  primary key (f1),
  unique key (f2, f3),
  key (f4)
) engine=innodb;

insert into t1 values
(1,1,991,1), (2,1,992,1), (3,1,993,1), (4,1,994,1), (5,1,995,1),
(6,1,996,1), (7,1,997,1), (8,1,998,1), (10,1,999,1), (11,1,9910,1),
(16,1,9911,1), (17,1,9912,1), (18,1,9913,1), (19,1,9914,1), (20,1,9915,1),
(21,1,9916,1), (22,1,9917,1), (23,1,9918,1), (24,1,9919,1), (25,1,9920,1),
(26,1,9921,1), (27,1,9922,1);

2. 两个不同limit的sql生成的执行计划:

  

  

3. 分析过程:

  

step 1.  获取可用的key,并计算rows

    update_ref_and_keys

      get_quick_record_count

  结果: f2, f4可用, 其分配的quick_rows=[22, 22]

  

  从上面的cardinality来看,f2,f4的过滤性都是2, 这样扫描f2,需要11行,然后根据primary回聚簇表扫描,有需要11行,所有,使用f2, f4索引的扫描需要22行。

   

step2:穷举下执行计划,找到cost最低的

    best_access_path

      best_extension_by_limited_search

   结果: 全表扫描的代价比较低,records=17, cost=2, 所以最后join->best_position[0]记录的就是全表扫描的执行计划。

   

step3:limit的影响

      在make_join_select的过程,对于limit进行处理,理由是:如果有limit,并且比当前best_position的记录数小,我们尝试是否有可用的index,减少扫描代价

      所以,在limit=10的时候,进行test_quick_select查找,并使用f2的索引。而limit=20的查询,不满足条件,所以继续使用全表扫描。

    

相关注释和代码如下:      

/*
        We plan to scan all rows.
        Check again if we should use an index.
        We could have used an column from a previous table in
        the index if we are using limit and this is the first table
      */
      if ((cond &&
              !tab->keys.is_subset(tab->const_keys) && i > 0) ||
          (!tab->const_keys.is_clear_all() && i == join->const_tables &&
           join->unit->select_limit_cnt <
           join->best_positions[i].records_read &&
           !(join->select_options & OPTION_FOUND_ROWS)))

  

step4: order by的影响

      函数:test_if_skip_sort_order

      limit=10:ref_key=f2:   判断有一个primary key的index可以覆盖order by查询, 但走pk的代价高于ref_key=f2。

       limit=20:ref_key=0:     判断有一个primary key的index可以覆盖order by查询,而且当前使用的是全表扫描,代价小于全表,所以选择pk。

 

所以,两个limit值不同的查询,导致了不同的执行计划。

 

posted @ 2014-07-30 16:39  xpchild  阅读(672)  评论(0编辑  收藏  举报