MySQL优化器cost计算

记录MySQL 5.5上，优化器进行cost计算的方法。

第一篇： 单表的cost计算

数据结构：

1. table_share: 包含了表的元数据，其中索引部分：

key_info：一个key的结构体，代表一个索引，包含了：

1. key_length：key的长度
2. key_parts：key一共有多少个column
3. key_part：key中具体的column
4. rec_per_key：相同的key平均有几条记录

例如：

1 2 3 4 5 6

(gdb) p (table->s->key_info->name)      $16 = 0x8ca0ffbd "PRIMARY" (gdb) p (table->s->key_info->key_parts)      $17 = 1 (gdb) p (table->s->key_info->rec_per_key)      $18 = (ulong *) 0x8ca0ffe8

2. JOIN:
    mysql_select函数中，创建了 new JOIN(thd, fields, select_options, result)对象，包含了当前查询的所有组件和各种转换结果，其中 ：

1. prepare：进行一些等价交换之类的变化
2. optimize：选择join的方式和access path
3. exec：根据执行计划运行查询

3. join_tab:
    包含了一个table访问的cost等一些信息，经过优化后，填充这个结构体

cost的计算方法

cost = cpu cost + io cost

1. cpu cost：server层对返回的记录数的compare时间
2. io cost：引擎层根据扫描记录的记录数计算cost

统计信息

这里用到了两部分统计信息：
1. server层的统计信息，保存在table_share中。包括：

1. key_length
2. rec_per_key
3. block_size等

2. innodb层的统计信息，包括：

1. stat_n_rows
2. stat_clustered_index_size
3. stat_sum_of_other_index_size

主要函数调用

make_join_statistics：
--update_ref_and_keys: 添加可以使用的索引。
--get_quick_record_count:
----test_quick_select: 评估每一个join table查询得到的记录数，其中比较不同index的cost, 返回的记录数，选择最优的那个。
choose plan：选择join的顺序

实验过程

1 2 3 4 5 6 7 8 9

CREATE TABLE `xpchild` (   `id` int(11) NOT NULL,   `name` varchar(100) DEFAULT NULL,   `c1` int(11) DEFAULT NULL,   `c2` int(11) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `xpchild_name` (`name`),   KEY `xpchild_id_c1` (`id`,`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

实验1： 单值查询

1	explain select * from xpchild where id =100;

函数调用栈：

make_join_statistics：
    table->quick_condition_rows= table->file->stats.records;
   if (s->type == JT_SYSTEM || s->type == JT_CONST)
   s->found_records=s->records=s->read_time=1; s->worst_seeks=1.0;

结论：单key的查询，join_tab的type=JT_CONST，索引record和read time都是1；最终得到的join->best_read=1.0;

实验2： 范围查询

explain select * from xpchild where id > 100;

step1：update_ref_and_keys: 一共得到两个possible index，

step2：test_quick_select

   

 1. 计算全表扫描的cost：

innodb全表扫描的io cost：

innodb的io cost： s->read_time=(ha_rows) s->table->file->scan_time();
innodb scan time：prebuilt->table->stat_clustered_index_size
等于innodb这张表的聚簇索引的page个数，本身innodb就是聚簇索引表，这里计算的io cost=16
(gdb) p s->read_time
$7 = 16

MySQL server的cpu cost：

scan_time= (double) records / TIME_FOR_COMPARE + 1;
(gdb) p scan_time
$16 = 1359.4000000000001

总的cost：read_time= (double) head->file->scan_time() + scan_time + 1.1;

(gdb) p read_time
$18 = 1376.5

 

继续函数栈：

根据possible index，生成sel_tree;
get_best_group_min_max: 这里没有使用到
get_key_scans_params：根据sel_tree找到更好的cost

 

2. 计算full index的cost

      find_shortest_key: 在覆盖索引中选择length最短的那个。
      get_index_only_read_time：这里如果有覆盖索引（covering index）那么就会计算此覆盖索引的cost。
      full index scan的计算方法：

1 2 3 4

uint keys_per_block= (param->table->file->stats.block_size/2/                       (param->table->key_info[keynr].key_length+                        param->table->file->ref_length) + 1); read_time=((double) (records+keys_per_block-1)/(double) keys_per_block);

     这里假设：一个块中，只使用了一半的空间写入数据，
     如果计算的key_read_time > read_time, 则read_time= key_read_time，从此不再使用全表扫描。

 

3. pk 索引计算的cost

进入get_key_scans_params函数：选择比传入的read_time小的cost的执行计划，生成一个TRP_RANGE对象返回。

 

step1: 评估范围扫描的记录数（check_quick_select）

check_quick_keys：根据key，min，max值来评估记录数，并把records记录到table->quick_rows[key]中，以便后续需要。

1 2	(gdb) p *min_key $82 = 100 'd'

ha_innobase::records_in_range: innodb引擎根据min和max值来评估记录数。
    计算方法：innodb对b_tree中范围确定的page的个数和record_per_page进行计算，当评估>all_record/2时，就取all_record/2。

1 2	(gdb) p records $112 = 3396

 

step 2: 计算cost

根据pk range估算的records=3396，调整table->quick_condition_rows从全表的6792到现在的3396。
计算cost：

1 2 3 4 5

cpu_cost= (double) found_records / TIME_FOR_COMPARE; cpu_cost= 679.20000000000005<br> io_cost = param->table->file->read_time(keynr,param->range_count,found_records); <br>found_read_time = cpu_cost + io_cost + 0.01 found_read_time = 683.74750000000006

这样，通过pk扫描的cost远小于前面第一阶段的全表扫描的代价。

4. 计算普通索引的cost

     因为都可以使用前导列进行查询，查询的效率的差别在于非主键索引需要回到聚簇索引中查询非索引列。
所以，innodb返回的found_rows=6556（包含了扫描索引xpchild_id_c1 和primary key的）, 所以最终计算的cost大于使用pk的cost。

      最终计算得到的cost=7868.21 远高于pk索引的cost。

 

结论： 最终选择了pk的索引进行range扫描