MYSQL5.6.7-RC index condition pushdown indexed condition Push code interpretation

MYSQL5.6.7-RC index condition pushdown indexed condition Push code interpretation

Http://jishu.zol.com.cn/4505.html

MYSQL5.6.7-RC Index Condition pushdown code interpretation a-a+2013-08-07 11:10|Share to:

Interested in the index condition Pushdown, and the tracking code has benefited you a lot, so come and follow the relevant code.

Look at the official Community edition of MYSQL5.6.7-RC.

First talk about my view of the study of MySQL source code:

Should everyone who uses MySQL database read the code? No, that means the MySQL database is used too high and almost unusable, but on the other hand, if you look at the MySQL code more people, it means that more people have a deeper understanding of the MySQL database. Can further promote the MySQL database extensive and local use, for users, relevant practitioners to create more profit opportunities and employment opportunities.

Let's get down to the chase.

1. single-field index

Mysql> Show CREATE TABLE Pushdown\g

1. Row ***************************

Table:pushdown

Create table:create Table ' pushdown ' (

' id ' int (one) not NULL auto_increment,

' Name ' varchar (+) DEFAULT NULL,

' Info ' varchar (+) DEFAULT NULL,

' Other ' int (one), DEFAULT NULL,

PRIMARY KEY (' id '),

KEY ' name ' (' name ')

) Engine=innodb auto_increment=718829 DEFAULT charset=latin1

1 row in Set (0.08 sec)

Mysql> Select COUNT (*) from pushdown;

COUNT (*)

524288

Mysql> Explain select * from Pushdown where name= ' name6011′;

Id	Select_type	Table	Type	Possible_keys	Key	Key_len	Ref	Rows	Extra
1	Simple	Pushdown	Ref	Name	Name	103	Const	128	Using Index Condition

Mysql> Explain select * from Pushdown where name= ' Name6011′and info like '%1′;

Id	Select_type	Table	Type	Possible_keys	Key	Key_len	Ref	Rows	Extra
1	Simple	Pushdown	Ref	Name	Name	103	Const	128	Using index condition; Using where

The Using where means that after the data is obtained from the storage engine, it should be matched again with the condition. This example is matched to the like '%1 '.

By secondary index name to find the primary key of Name= ' name6011 ';

The value of the primary key is then detected in cluster index * (all data);

Filter out these databases without satisfying the conditions using the like '%1 ';

If we were to compare the using where in version 5.1, we found that the using where would also appear when we thought it was not possible. That is, we think that the index has found the data that satisfies all the conditions, but we can still see the using where. This problem seems to have been solved in version 5.6.

2. Two Fields Federated index

mysql> ALTER TABLE pushdown drop key name;

Query OK, 0 rows affected (0.17 sec)

records:0 duplicates:0 warnings:0

Mysql> ALTER TABLE Pushdown add key Name_info (Name,info);

Query OK, 0 rows affected (1 min 1.80 sec)

records:0 duplicates:0 warnings:0

Mysql> Show CREATE TABLE Pushdown\g

1. Row ***************************

Table:pushdown

Create table:create Table ' pushdown ' (

' id ' int (one) not NULL auto_increment,

' Name ' varchar (+) DEFAULT NULL,

' Info ' varchar (+) DEFAULT NULL,

' Other ' int (one), DEFAULT NULL,

PRIMARY KEY (' id '),

KEY ' Name_info ' (' name ', ' info ')

) Engine=innodb auto_increment=718829 DEFAULT charset=latin1

1 row in Set (0.00 sec)

Explain select * from Pushdown where name= ' name6011′;

Id	Select_type	Table	Type	Possible_keys	Key	Key_len	Ref	Rows	Extra
1	Simple	Pushdown	Ref	Name_info	Name_info	103	Const	128	Using Index Condition

Query with name only, no change in execution plan

Explain select * from Pushdown where name= ' Name6011′and info like '%1′;

Id	Select_type	Table	Type	Possible_keys	Key	Key_len	Ref	Rows	Extra
1	Simple	Pushdown	Ref	Name_info	Name_info	103	Const	128	Using Index Condition

With two criteria, the using where is less than the index only on name. This means that when using secondary index name_info to find the index key satisfying name= ' name6011 ', the like '%1 ' was used, and the index name_info was searched, and the records of the unsatisfied condition were filtered out. Then use the index name_info to get the primary key to cluster index to check all the data.

This is based on the execution plan and previously seen in version 5.1 of the code to speculate.

Find out the truth with the code below.

Follow along and find the location that compares the Where condition. First write down the call stack for your reference:

> mysqld.exe! Arg_comparator::compare_string () line 1374 C + +

mysqld.exe! Arg_comparator::compare () lines + 0x1c bytes C + +

mysqld.exe! Item_func_eq::val_int () line 2198 + 0xe bytes C + +

mysqld.exe! Item::val_bool () line 203 + 0xf bytes C + +

mysqld.exe! Item_cond_and::val_int () line 5084 + 0xf bytes C + +

Mysqld.exe!innobase_index_cond (void * file=0x0ef9b678) line 16388 + 0x2e bytes C + +

Mysqld.exe!row_search_idx_cond_check (unsigned char * mysql_rec=0x0ef9d0a0, row_prebuilt_t * prebuilt=0x0efb11e0, Const unsigned char * rec=0x06d3eab7, const unsigned long * offsets=0x11c9cf74) line 3604 + 0xf bytes C + +

Mysqld.exe!row_search_for_mysql (unsigned char * buf=0x0ef9d0a0, unsigned long mode=2, row_prebuilt_t * prebuilt= 0X0EFB11E0, unsigned long match_mode=1, unsigned long direction=0) line 4678 + 0x18 bytes C + +

Mysqld.exe!ha_innobase::index_read (unsigned char * buf=0x0ef9d0a0, const unsigned char * key_ptr=0x0efb2560, unsigned int key_len=103, ha_rkey_function find_flag=ha_read_key_exact) row 7438 + 0x1d bytes C + +

Mysqld.exe!handler::index_read_map (unsigned char * buf=0x0ef9d0a0, const unsigned char * key=0x0efb2560, unsigned long ke Ypart_map=1, ha_rkey_function find_flag=ha_read_key_exact) line 2174 + 0x22 bytes C + +

Mysqld.exe!handler::ha_index_read_map (unsigned char * buf=0x0ef9d0a0, const unsigned char * key=0x0efb2560, unsigned Long keypart_map=1, ha_rkey_function find_flag=ha_read_key_exact) row 2669 + 0x70 bytes C + +

Mysqld.exe!join_read_always_key (st_join_table * tab=0x0efb2354) row 2185 + 0x32 bytes C + +

Mysqld.exe!sub_select (JOIN * join=0x0ef96d00, st_join_table * join_tab=0x0efb2354, BOOL End_of_records=false) line 1239 + 0xe bytes C + +

Mysqld.exe!do_select (JOIN * join=0x0ef96d00) line 932 + 0x17 bytes C + +

mysqld.exe! Join::exec () line 191 + 0x9 bytes C + +

Mysqld.exe!mysql_execute_select (THD * thd=0x057c1090, St_select_lex * Select_lex=0x057c2bd8, BOOL free_join=true) Line 1086 C + +

Mysqld.exe!mysql_select (THD * thd=0x057c1090, table_list * tables=0x0ef96260, unsigned int wild_num=1, list<item> & fields={}, Item * conds=0x0ef96ba0, sql_i_list<st_order> * order=0x057c2cd0, sql_i_list<st_order> * GROUP=0X057C2C6C, Item * having=0x00000000, unsigned __int64 select_options=2147748608, Select_result * result= 0X0EF96CE0, St_select_lex_unit * unit=0x057c2760, St_select_lex * Select_lex=0x057c2bd8) line 1204 + 0x23 bytes C + +

Mysqld.exe!handle_select (THD * thd=0x057c1090, Select_result * result=0x0ef96ce0, unsigned long setup_tables_done_ option=0) line + 0x78 byte C + +

Mysqld.exe!execute_sqlcom_select (THD * thd=0x057c1090, table_list * all_tables=0x0ef96260) line 4990 + 0xf bytes C + +

Mysqld.exe!mysql_execute_command (THD * thd=0x057c1090) line 2554 + 0xd bytes C + +

Mysqld.exe!mysql_parse (THD * thd=0x057c1090, char * rawbuf=0x0ef96080, unsigned int length=63, parser_state * parser_stat e=0x11c9f840) line 6094 + 0x9 bytes C + +

Mysqld.exe!dispatch_command (Enum_server_command command=com_query, THD * thd=0x057c1090, char * packet=0x0ef8dd91, unsigned int packet_length=63) line 1314 + 0x28 bytes C + +

Mysqld.exe!do_command (THD * thd=0x057c1090) line 1038 + 0x1b bytes C + +

Mysqld.exe!do_handle_one_connection (THD * thd_arg=0x057c1090) line 969 + 0x9 bytes C + +

Mysqld.exe!handle_one_connection (void * arg=0x057c1090) line 885 + 0x9 bytes C + +

Mysqld.exe!pfs_spawn_thread (void * arg=0x0edb4ba0) line 1853 + 0x9 bytes C + +

Mysqld.exe!pthread_start (void * p=0x0edbbd10) line + 0x9 byte C

Mysqld.exe!_callthreadstartex () line 348 + 0xf byte C

Mysqld.exe!_threadstartex (void * ptd=0x0ef91dd8) line 331 C

KERNEL32.DLL!75DDED6C ()

Next look at what is currently being compared:

　　

　　

Go back to the following loop and do a follow-up comparison:

Longlong Item_cond_and::val_int ()

{

Dbug_assert (fixed = = 1);

List_iterator_fast<item> Li (list);

Item *item;

Null_value= 0;

while ((item=li++))

{

if (!item->val_bool ())

{

if (Abort_on_null | |!) ( Null_value= item->null_value))

return 0; Return FALSE

}

}

Return null_value? 0:1;

}

Go all the way down, into like comparison:

Longlong Item_func_like::val_int ()

{

Dbug_assert (fixed = = 1);

string* res = ARGS[0]->VAL_STR (&cmp.value1);

if (Args[0]->null_value)

{

null_value=1;

return 0;

}

string* res2 = Args[1]->val_str (&cmp.value2);

if (Args[1]->null_value)

{

null_value=1;

return 0;

}

null_value=0;

if (CANDOTURBOBM)

Return Turbobm_matches (Res->ptr (), Res->length ())? 1:0;

Return my_wildcmp (Cmp.cmp_collation.collation,

Res->ptr (), Res->ptr () +res->length (),

Res2->ptr (), Res2->ptr () +res2->length (),

Escape,wild_one,wild_many)? 0:1;

}

　　

　　

As you can see, one is the value of the field and one is the value after the like. This step info value is info21885, does not satisfy like '%1 ', this record is filtered out.

When a matching record is found, the data is also read from the cluster index to see the code

/* Check If the record matches the index condition. */

Switch (Row_search_idx_cond_check (buf, prebuilt, REC, offsets)) {

Case Icp_no_match:

if (did_semi_consistent_read) {

Row_unlock_for_mysql (prebuilt, TRUE);

}

Goto Next_rec;

Case Icp_out_of_range:

err = Db_record_not_found;

Goto idx_cond_failed;

Case Icp_match:

Break

}

/* Get The clustered index record if needed.

Search using the clustered index. */

if (Index! = Clust_index && prebuilt->need_to_access_clustered) {

Requires_clust_rec:

Ut_ad (Index! = clust_index);

/* We use a ' goto ' to the preceding label if a consistent

Read of a secondary index record requires us to look up old

Versions of the associated clustered index record. */

Ut_ad (Rec_offs_validate (REC, index, offsets));

/* It was a non-clustered index and we must fetch also the

Clustered index Record */

Mtr_has_extra_clust_latch = TRUE;

/* The following call returns ' offsets ' associated with

' Clust_rec '. Note that ' Clust_rec ' can is an old version

Built for a consistent read. */

Err = Row_sel_get_clust_rec_for_mysql (prebuilt, index, REC,

THR, &clust_rec,

&offsets, &heap, &mtr);

The name of this function is very clear, get cluster record for MySQL

Function Description:

/*********************************************************************//**

Retrieves the clustered index record corresponding to a record in a

Non-clustered index. Does the necessary locking. Used in the MySQL

Interface.

@return db_success, Db_success_locked_rec, or error code */

The more conditions the ICP filters, the less records need to be read from cluster index. Especially when using SELECT *, especially with blob and text fields, the cost of reading data from cluster index is more significant.

Remember our optimization of like '%abc% '? Especially when there is a big paragraph in the table. This may be optimized for:

SELECT * from A, (select ID from a where C1 like '%abc% ') b where a.id=b.id;

If the actual SQL also requires an equal condition, such as c2= ' CCC '. Then it can be optimized like this:

Build index C2_C1 (C2,C1);

SELECT * from a where c2= ' CCC ' and c1 like '%abc% ';

SQL does not rewrite, and the ICP will use C2_C1 to filter out all the records that do not meet the conditions, only to meet the criteria to read the Cluster_index, which is the same as before we optimize the idea of derived table.

Please try and feedback the results to me, thank you. Note that this is a feature that is only 5.6.

3. Look back and see only the index on the name field

Mysql> Explain select * from Pushdown where name= ' Name6011′and info like '%1′;

Id	Select_type	Table	Type	Possible_keys	Key	Key_len	Ref	Rows	Extra
1	Simple	Pushdown	Ref	Name	Name	103	Const	128	Using index condition; Using where

There is a using where. What is the Using where? Check the code:

Static Enum_nested_loop_state

Evaluate_join_record (Join *join, Join_tab *join_tab)

{

BOOL not_used_in_distinct=join_tab->not_used_in_distinct;

Ha_rows found_records=join->found_records;

Item *condition= join_tab->condition ();

BOOL Found= TRUE;

Dbug_enter ("Evaluate_join_record");

Dbug_print ("Enter",

("Join:%p Join_tab index:%d table:%s cond:%p",

Join, Static_cast<int> (Join_tab-join_tab->join->join_tab),

Join_tab->table->alias, condition));

/* is here, and when condition has a value it is the using where.

In the above example, if there is no ICP support, the like '%1 ' will appear here.

Similarly, the like '%1 ' appears here when you change the index to only the name.

*/

if (condition)

{

found= Test (Condition->val_int ());//Here is the detection of where condition matches

if (join->thd->killed)

{

Join->thd->send_kill_message ();

Dbug_return (nested_loop_killed);

}

/* Check for errors evaluating the condition */

if (Join->thd->is_error ())

Dbug_return (Nested_loop_error);

}

　　

Two compared items, the former is the Info field, and the latter is '%1 '.

　　

See like condition '%1 ' in Str_value

4. In the group to exchange a bit, the students gave a lot of help, raised a very enlightening question:

Two fields fuzzy query, can I use ICP?

Can the function use the ICP in addition to the fuzzy query?

3 Field Federated Index, 1th and 3rd fields have a where condition, can I use ICP?

Mysql> SELECT * from pushdown where name= ' Name658′and info like '%6′;

+ ——-+ ——— + ———-+ ——-+

ID | name | info | Other |

+ ——-+ ——— + ———-+ ——-+

12266 | name658 | info12266 | NULL |

3056 | name658 | info3056 | NULL |

+ ——-+ ——— + ———-+ ——-+

2 rows in Set (0.01 sec)

Mysql> Explain select * from Pushdown where name= ' name658′and length (info) = 8;

+--+ ————-+ ———-+--+ ————— + ———-+ ——— + ——-+--+ ———————-+

ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+--+ ————-+ ———-+--+ ————— + ———-+ ——— + ——-+--+ ———————-+

1 | Simple | Pushdown | Ref | Name_info | Name_info | 53 | Const | 4 | Using Index Condition |

+--+ ————-+ ———-+--+ ————— + ———-+ ——— + ——-+--+ ———————-+

1 row in Set (0.02 sec)

Mysql> Show CREATE TABLE Pushdown\g

1. Row ***************************

Table:pushdown

Create table:create Table ' pushdown ' (

MYSQL5.6.7-RC Index condition pushdown indexed condition push code interpretation