MySQL5.7中select半連接優化的示例分析

這篇文章主要為大家展示了“MySQL5.7中select半連接優化的示例分析”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“MySQL5.7中select半連接優化的示例分析”這篇文章吧。

 MySQL的子查詢一直以來都是性能差的著稱，解決方法是以join代替。
 MySQL5.5版本中該查詢先把accessLog表中版本為2.2的數據線過濾出來，然后每個符合條件的數據都與內表進行一次select id from accessLog_01，因此性能低下。MySQL5.5采取的解決方法是將in重寫為exists。
 在MySQL5.6/5.7版本中，子查詢執行計劃是將in/exists重寫為join，如下看執行計劃：

點擊(此處)折疊或打開

1. mysql> select version();
   

2. +------------+
   

3. | version() |
   

4. +------------+
   

5. | 5.7.18-log |
   

6. +------------+

點擊(此處)折疊或打開

1. mysql> explain select * from accessLog ac where ac.id in (select id from accessLog_01);
   

2. +----+--------------+--------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
   

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

4. +----+--------------+--------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
   

5. | 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | NULL |
   

6. | 1 | SIMPLE | ac | NULL | eq_ref | PRIMARY | PRIMARY | 8 | <subquery2>.id | 1 | 100.00 | NULL |
   

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

8. +----+--------------+--------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+

點擊(此處)折疊或打開

1. mysql> explain select * from accessLog ac where exists (select * from accessLog_01);
   

2. +----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------+
   

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

4. +----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------+
   

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

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

7. +----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------+

點擊(此處)折疊或打開

1. mysql> explain select ac.* from accessLog ac join accessLog_01 b on ac.id=b.id;
   

2. +----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
   

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

4. +----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
   

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

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

7. +----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+

  辦連接查詢優化默認開啟，通過show variables like 'optimizer_switch' \G查詢：

點擊(此處)折疊或打開

1. mysql> show variables like 'optimizer_switch' \G
   

2. *************************** 1. row ***************************
   

3. Variable_name: optimizer_switch
   

4.         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
   

5. 1 row in set (0.01 sec)

  但是半連接優化只是針對查詢，對于DML操作，性能依舊很差。

以上是“MySQL5.7中select半連接優化的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！