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

This article mainly shows you the "example analysis of select semi-connection optimization in MySQL5.7", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let me lead you to study and study the "sample analysis of select semi-connection optimization in MySQL5.7".

MySQL's subqueries have always been known for their poor performance, and the solution is to use join instead.

In the MySQL5.5 version, the query first filters out the data lines of version 2.2 in the accessLog table, and then each eligible data is select id from accessLog_01 with the inner table, so the performance is poor. The workaround taken by MySQL5.5 is to rewrite in as exists.

In the MySQL5.6/5.7 version, the subquery execution plan is to rewrite in/exists to join, as shown below:

Click (here) to collapse or open

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.7.18-log |

+-+

Click (here) to collapse or open

Mysql > explain select * from accessLog ac where ac.id in (select id from accessLog_01)

+- -+

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+- -+

| | 1 | SIMPLE | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | NULL |

| | 1 | SIMPLE | ac | NULL | eq_ref | PRIMARY | PRIMARY | 8 | .id | 1 | 100.00 | NULL |

| | 2 | MATERIALIZED | accessLog_01 | NULL | ALL | NULL | NULL | NULL | NULL | 1305 | 100.00 | NULL |

+- -+

Click (here) to collapse or open

Mysql > explain select * from accessLog ac where exists (select * from accessLog_01)

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | PRIMARY | ac | NULL | ALL | NULL | NULL | NULL | NULL | 586090 | 100.00 | NULL |

| | 2 | SUBQUERY | accessLog_01 | NULL | ALL | NULL | NULL | NULL | NULL | 1305 | 100.00 | NULL |

+-- +

Click (here) to collapse or open

Mysql > explain select ac.* from accessLog ac join accessLog_01 b on ac.id=b.id

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 1305 | 100.00 | NULL |

| | 1 | SIMPLE | ac | NULL | eq_ref | PRIMARY | PRIMARY | 8 | xinhost.b.id | 1 | 100.00 | NULL |

+-- +

Connection query optimization is enabled by default, via show variables like 'optimizer_switch'\ G query:

Click (here) to collapse or open

Mysql > show variables like 'optimizer_switch'\ G

* * 1. Row *

Variable_name: optimizer_switch

Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on

1 row in set (0.01 sec)

However, semi-join optimization is only for queries, and for DML operations, the performance is still poor.

The above is all the contents of the article "sample Analysis of select semi-join Optimization in MySQL5.7". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.