Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the features of MySQL". In daily operation, I believe many people have doubts about the characteristics of MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what are the features of MySQL?" Next, please follow the editor to study!

ICP test

First, let's take a look at the performance difference between turning on ICP and closing ICP. Here is the test process:

Prepare the data:

Create table icp (id int, age int, name varchar (30), memo varchar (600)) engine=innodb; alter table icp add index aind (age, name, memo);-- let $I = 100000 while ($I) {--eval insert into icp values ($I, 1, 'astati, repeat)-- dec $I}

PS: MySQL has a thing called profile, which can be used to monitor the execution of SQL statements at various stages. We can use this tool to observe the execution of SQL statements at various stages. For more information on profile, please refer to the official documentation.

Open the performance test for ICP:

Set profiling=on;set optimizer_switch= "index_condition_pushdown=on"; (default enabled) mysql > select * from icp where age = 999 and name like'% 999%' +-+ | id | age | name | memo | +-+ | NULL | 999 | 999 | 999 | + 1 row in set (0.00 sec) mysql > explain select * from icp Where age = 999 and name like'% 999%' +-+-- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + -+ | 1 | SIMPLE | icp | ref | aind | aind | 5 | const | 1 | Using index condition | +-+ -+-+ 1 row in set (0.00 sec) mysql > show profiles +-+ | Query_ID | Duration | Query | | +-+ | 1 | 0.00043550 | select * from icp where age = 999 and name like'| % 999%'| | 2 | 0.00043250 | explain select * from icp where age = 999 and name like'% 999%'| +-+ 2 rows in set 1 warning (0.00 sec) mysql > show profile cpu,block io for query 2 +-- +-+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | | +-+ + | starting | 0.000084 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | Opening tables | 0.000064 | 0.000000 | 0.000000 | 0 | 0 | init | 0.000046 | 0.000000 | 0.000000 | 0 | 0 | System lock | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | optimizing | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | statistics | 0.000082 | 0.000000 | 0.000000 | 0 | 0 | preparing | 0.000022 | 0.000000 | 0.000000 | 0 | | 0 | | explaining | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | query end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | closing tables | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | freeing items | | 0.000031 | 0.000000 | 0.000000 | 0 | 0 | cleaning up | 0.000013 | 0.000000 | 0.000000 | 0 | 0 | +-- +-+-+ 13 rows in set 1 warning (0.00 sec) mysql > show session status like'% handler%' +-- +-+ | Variable_name | Value | +-+-+ | Handler_commit | 2 | | Handler_delete | 0 | Handler_discover | | 0 | | Handler_external_lock | 4 | Handler_mrr_init | 0 | | Handler_prepare | 0 | Handler_read_first | 0 | Handler_read_key | 1 | Handler_read_last | 0 | Handler_read_next | 1 | Handler_read_prev | 0 | | Handler | _ read_rnd | 0 | | Handler_read_rnd_next | 42 | | Handler_rollback | 0 | Handler_savepoint | 0 | Handler_savepoint_rollback | 0 | Handler_update | 0 | Handler_write | 39 | + -+ 18 rows in set (0.00 sec)

Turn off performance testing for ICP:

Mysql > set optimizer_switch= "index_condition_pushdown=off"; mysql > select * from icp where age = 1 and memo like'% 9999% suicide MySQL > select * from icp where age = 999 and name like'% 999%' +-+ | id | age | name | memo | +-+ | NULL | 999 | 999 | 999 | + 1 row in set (0.00 sec) mysql > explain select * from icp Where age = 999 and name like'% 999%' +-- + | id | select_type | table | type | possible_keys | key | key_len | ref | Rows | Extra | +-+ | 1 | SIMPLE | icp | ref | aind | Aind | 5 | const | 1 | Using where | +-+ 1 row in set (0.00 sec) mysql > show profiles +-+ | Query_ID | Duration | Query | | +-+ | 1 | 0.00043550 | select * from icp where age = 999 and name like'| % 999%'| | 2 | 0.00043250 | explain select * from icp where age = 999 and name like'% 999%'| | 3 | 0.00081350 | show session status like'% handler%' | | 4 | 0.00010350 | set optimizer_switch= "index_condition_pushdown=off" | | 5 | 0.00036525 | select * from icp where age = 999 and name like'% 999% | '| | 6 | 0.00032950 | explain select * from icp where age = 999 and name like'% 999%'| +-+ 6 rows in set 1 warning (0.00 sec) mysql > show profile cpu,block io for query 5 +-- +-+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | | +-+ + | starting | 0.000068 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | Opening tables | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | init | 0.000032 | 0.000000 | 0.000000 | 0 | 0 | System lock | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | optimizing | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | statistics | 0.000088 | 0.000000 | 0.000000 | 0 | 0 | preparing | 0.000017 | 0.000000 | 0.000000 | 0 | | 0 | | executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | Sending data | 0.000049 | 0.000000 | 0.000000 | 0 | 0 | end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | query end | | | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | closing tables | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | freeing items | 0.000024 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000014 | 0.000000 | 0.000000 | | | 0 | 0 | +-- +-+ 15 rows in set | 1 warning (0.00 sec) mysql > show session status like'% handler%' +-- +-+ | Variable_name | Value | +-+-+ | Handler_commit | 4 | | Handler_delete | 0 | Handler_discover | | 0 | | Handler_external_lock | 8 | Handler_mrr_init | 0 | | Handler_prepare | 0 | Handler_read_first | 0 | Handler_read_key | 2 | Handler_read_last | 0 | Handler_read_next | 2 | Handler_read_prev | 0 | | Handler | _ read_rnd | 0 | Handler_read_rnd_next | 58 | | Handler_rollback | 0 | Handler_savepoint | 0 | Handler_savepoint_rollback | 0 | Handler_update | 0 | Handler_write | 54 | + -+ 18 rows in set (0.00 sec)

Test conclusion: from the above test results, we can see that when the secondary index is a composite index and the previous condition filtering is low, opening ICP can effectively reduce the number of interactions between the server layer and the engine layer, thus effectively reducing the running time.

ICP principle

Before SQL, during the execution of the SQL statement, the server layer obtains the data through the api of the engine, and then carries on the where_cond judgment (the specific judgment logic is: evaluate_join_record). Each piece of data needs to be returned to the server layer from the server layer to make a judgment. If we review the above test that turned off ICP, we can see that the value of Handler_read_next increases sharply because the first field is not differentiated and the index cannot be used in the memo field, resulting in a situation similar to index scanning and poor performance.

After that, if the index-related condition in where_cond is found in the process of index scanning, it will be recorded in the handler interface. In the process of index scanning, only if the condition of the index and handler interface is met, will it be returned to the server layer for further processing. In the case of insufficient prefix index differentiation and high differentiation of other fields, the overhead between server and engine can be effectively reduced. Improve query performance.

ICP source code implementation

We mentioned in the previous section that the conditions used by index condition down are recorded in the handler interface, so let's analyze how the process of recording is implemented.

First of all, after calculating the cost, the optimizer will generate a left branch tree of JOIN_TAB. Each JOIN_TAB contains information such as the pointer of the related table, the reading method of the table, and the index contained in the access table. The optimizer will modify the access method of the table in JOIN_TAB in make_join_readinfo, and further record the conditions related to the index in where cond to the handle of table. The stack is as follows:

# 0 make_cond_for_index (cond=0x2b69680179e8, table=0x2b6968012100, keyno=0, other_tbls_ok=true) # 1 in push_index_cond (tab=0x2b696802aa48, keyno=0, other_tbls_ok=true, trace_obj=0x2b696413ec30) # 2 in make_join_readinfo (join=0x2b6968017db0, options=0, no_jbuf_after=4294967295) # 3 in JOIN::optimize (this=0x2b6968017db0) # 4 in mysql_execute_select (thd=0x3176760, select_lex=0x3179470, free_join=true)

Secondly, make_cond_for_index is a recursive process, which judges every condition in where_cond, reassembles the cond that meets the conditions into a new cond, and finally hangs the new cond under table- > file (table- > file refers to the interface function for operating the physical table, this variable is private under thd, not shared, shared is tab- > table- > s). Refer to the detailed implementation of make_cond_for_index. The stack is set as follows:

# 0 ha_innobase::idx_cond_push (this=0x2b696800e810, keyno=0, idx_cond=0x2b69680179e8) # 1 0x0000000000a60a55 in push_index_cond (tab=0x2b696802aa48, keyno=0, other_tbls_ok=true, trace_obj=0x2b696413ec30) # 2 0x0000000000a6362f in make_join_readinfo (join=0x2b6968017db0, options=0, no_jbuf_after=4294967295) # 3 0x0000000000d9b8bd in JOIN::optimize (this=0x2b6968017db0 # 4 0x0000000000a5b9ae in mysql_execute_select (thd=0x3176760, select_lex=0x3179470, free_join=true)

Thirdly, the server layer reads the contents of the engine layer according to the generated JOIN_TAB. When the engine reads, it will make a call to index_condition_pushdown, that is, a call to ICP. The stack is as follows:

# 0 Item_func_like::val_int (this=0x2b6978005a28) # 1 0x0000000001187b66 in innobase_index_cond (file=0x2b696800e810) # 2 0x0000000001393566 in row_search_idx_cond_check (mysql_rec=0x2b69680129f0, prebuilt=0x2b69680130f8, rec=0x2b692b56e4cf "\ 200", offsets=0x2b697008d450) # 3 0x0000000001397e2b in row_search_for_mysql (buf=0x2b69680129f0, mode=2, prebuilt=0x2b69680130f8, match_mode=1, direction=0) # 4 0x00000000011696b9 in ha_innobase::index_read (this=0x2b696800e810, buf=0x2b69680129f0, key_ptr=0x2b697800a660 ", key_len=5, find_flag=HA_READ_KEY_EXACT) # 5 0x00000000006ecc58 in handler::index_read_map (this=0x2b696800e810 Buf=0x2b69680129f0, key=0x2b697800a660 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT) # 6 0x00000000006d6bb4 in handler::ha_index_read_map (this=0x2b696800e810, buf=0x2b69680129f0, key=0x2b697800a660 ", keypart_map=1, find_flag=HA_READ_KEY_EXACT) # 7 0x00000000009a1870 in join_read_always_key (tab=0x2b697800a1b8) # 8 0x000000000099d480 in sub_select (join=0x2b6978005df0, join_tab=0x2b697800a1b8, end_of_records=false) # 9 0x000000000099c6c0 in do_select (join=0x2b6978005df0) # 10 0x00000000009980a4 in JOIN::exec (this=0x2b6978005df0) # 11 0x0000000000a5bac0 in mysql_execute_select (thd=0x32801a0, select_lex=0x3282eb0, free_join=true)

It can be seen that the judgment in ICP is to call the function of the relevant item, although it is the same function that calls the server layer, but no ICP call needs to find the record according to the main construction, and then match it. With ICP, you can omit the process of finding data by the primary key, thus improving efficiency.

Restrictions and problems in the use of ICP

Only select statements are supported

Only MyISAM and InnoDB engines are supported in

The optimization strategy of ICP can be used for data access methods of range, ref, eq_ref and ref_or_null types.

ICP with primary indexing is not supported

When SQL uses an overlay index but only retrieves part of the data, ICP cannot be used. For detailed analysis, please refer to Olav Sandst? in bug#68554. For the analysis of the code implementation, please refer to make_join_readinfo.

In the query, even if the first N fields of the index are used correctly (that is, following the principle of prefix index), ICP will still be used, and there are more ICP-related judgments for no reason. This should be a degenerate problem, for example:

Mysql > explain select * from icp where age = 999 and name like '999%' +-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | Extra | +-- + | 1 | SIMPLE | icp | range | aind | aind | | | 98 | NULL | 1 | Using index condition | +-+ 1 row in set (0.00 sec) |

PS: engine condition pushdown is used by NDB and is not supported by other engines.

Add:

Such as:

Root@read 02:28:07 > show status like 'Handler_read%'

+-+

| | Variable_name | Value |

+-+

| | Handler_read_first | 0 | |

| | Handler_read_key | 0 | |

| | Handler_read_next | 0 | |

| | Handler_read_prev | 0 | |

| | Handler_read_rnd | 0 | |

| | Handler_read_rnd_next | 61 | |

+-+

6 rows in set (0.41 sec)

Handler_read_first represents the number of times the index header is read, and if this value is high, there are a lot of full index scans.

Handler_read_key represents the number of times an index is used. If we add a new index, we can see if Handler_read_key has increased. If so, sql uses the index.

Handler_read_next stands for reading the following indexes, and range scan usually occurs.

Handler_read_prev stands for reading the above column of the index, which usually occurs in ORDER BY. DESC .

Handler_read_rnd stands for reading rows in a fixed position, and if this value is high, a large number of result sets are sorted, a full table scan is performed, and the appropriate KEY is not used in the associated query.

The Handler_read_rnd_next representative does a lot of table scans and the query performance is poor.

At this point, the study of "what are the features of MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.