mysql 范围索引 els_MySQL内核技术之“索引查询”

MySQL的索引查询内部使用的是quick range结构。先让我们看看调用流程：

join->optimize()-->

make_join_plan()-->

estimate_rowcount()-->

get_quick_record_count()-->

test_quick_select()-->

...

join_init_read_record()(这个函数设置要使用的读取操作为rr_quick())-->

read_record(实际上是QUICK_RANGE_SELECT::get_next())-->

ha_innobase::multi_range_read_next-->

DsMrr_impl::dsmrr_next-->

handler::multi_range_read_next-->

如果是第一次：mrr_funcs.next(mrr_iter, &mrr_cur_range)实际上是调用：quick_range_seq_next

如果不是：read_range_next

quick_range_seq_next最重要的作用是设置范围查询的上下边界：

uint quick_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)

{

QUICK_RANGE_SEQ_CTX *ctx= (QUICK_RANGE_SEQ_CTX*)rseq;

if (ctx->cur == ctx->last)

return 1; /* no more ranges */

QUICK_RANGE *cur= *(ctx->cur);

key_range *start_key= &range->start_key;

key_range *end_key= &range->end_key;

start_key->key= cur->min_key;

start_key->length= cur->min_length;

start_key->keypart_map= cur->min_keypart_map;

start_key->flag= ((cur->flag & NEAR_MIN) ? HA_READ_AFTER_KEY :

(cur->flag & EQ_RANGE) ?

HA_READ_KEY_EXACT : HA_READ_KEY_OR_NEXT);

end_key->key= cur->max_key;

end_key->length= cur->max_length;

end_key->keypart_map= cur->max_keypart_map;

/*

We use HA_READ_AFTER_KEY here because if we are reading on a key

prefix. We want to find all keys with this prefix.

*/

end_key->flag= (cur->flag & NEAR_MAX ? HA_READ_BEFORE_KEY :

HA_READ_AFTER_KEY);

range->range_flag= cur->flag;

ctx->cur++;

return 0;

}

上面函数中，设置上下边界的的代码：

start_key->key= cur->min_key; 为5

end_key->key= cur->max_key; 为7

start_key->flag=HA_READ_KEY_OR_NEXT；

end_key->flag=HA_READ_BEFORE_KEY；

执行完quick_range_seq_next，开始真正执行读取操作：

read_range_first

/** @brief

Read first row between two ranges.

Store ranges for future calls to read_range_next.

@param start_key Start key. Is 0 if no min range

@param end_key End key. Is 0 if no max range

@param eq_range_arg Set to 1 if start_key == end_key

@param sorted Set to 1 if result should be sorted per key

@note

Record is read into table->record[0]

@retval

0 Found row

@retval

HA_ERR_END_OF_FILE No rows in range

@retval

\# Error code

*/

int handler::read_range_first(const key_range *start_key,

const key_range *end_key,

bool eq_range_arg,

bool sorted /* ignored */)

{

int result;

DBUG_ENTER("handler::read_range_first");

eq_range= eq_range_arg;

set_end_range(end_key, RANGE_SCAN_ASC);

range_key_part= table->key_info[active_index].key_part;

if (!start_key) // Read first record

result= ha_index_first(table->record[0]);

else

result= ha_index_read_map(table->record[0],

start_key->key,

start_key->keypart_map,

start_key->flag);

if (result)

DBUG_RETURN((result == HA_ERR_KEY_NOT_FOUND)

? HA_ERR_END_OF_FILE

: result);

if (compare_key(end_range) <= 0)

{

DBUG_RETURN(0);

}

else

{

/*

The last read row does not fall in the range. So request

storage engine to release row lock if possible.

*/

unlock_row();

DBUG_RETURN(HA_ERR_END_OF_FILE);

}

}

这里会设置结束边界：set_end_range(end_key, RANGE_SCAN_ASC); end_key为7。接下来读取第一条记录：

if (!start_key) // Read first record

result= ha_index_first(table->record[0]);

else

result= ha_index_read_map(table->record[0],

start_key->key,

start_key->keypart_map,

start_key->flag);

接下来sub_select会执行read_range_next来读取下一条数据。无论在read_range_next还是read_range_first中，最后面都有下面这个判断：

if (compare_key(end_range) <= 0)

{

DBUG_RETURN(0);

}

else

{

/*

The last read row does not fall in the range. So request

storage engine to release row lock if possible.

*/

unlock_row();

DBUG_RETURN(HA_ERR_END_OF_FILE);

}

}

这个就是用来判断读取的记录是不是到达了上界，如果是则返回HA_ERR_END_OF_FILE结束。