MySQL: a special problem

-

This is a question asked by the developer.

-

# 1. Problem description

If a table has a federated index (ajournal b):

The index records are as follows:

Id1 id2

1 10

1 10

1 10

2 20

2 20

3 30

3 30

It means that the number of data filtered through an is the same as that filtered through an and b. Is it efficient to access data select * from table where id1=1 and select * from table where id1=1 and id2=10 through the index?

# II. Simple analysis

First of all, for data lookup, the first step is to locate the data in the innodb layer (that is, where to output the data to the MySQL layer), and then access it sequentially down after the positioning is completed, because innodb represents an index organization table.

-for the equivalence condition, the Innodb layer will access the next piece of data. If it does not meet the requirements, it means it is over, and a DB_RECORD_NOT_FOUND tag will be returned to the MySQL layer to indicate the end.

-for ranges such as > and

< 操作如果使用了索引，那么row_search_mvcc ->

Btr_pcur_open_with_no_init_func

-> btr_cur_search_to_nth_level

-> page_cur_search_with_match_bytes

-> cmp_dtuple_rec_with_match_bytes

`

This section of cmp_dtuple_rec_with_match_bytes is annotated as follows:

`

/ * Match fields in a loop; stop if we run out of fields in dtuple

Or find an externally stored field * /

While (cur_field < n_cmp) {

`

N_cmp is the number that needs to be compared.

Debug record:

`

A record

829 ut_ad (n_cmp