MySQL5.7新特性之select半连接优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL5.7新特性之select半连接优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3620字，纯文字阅读大概需要6分钟。
内容图文
MySQL的子查询一直以来都是性能差的著称，解决方法是以join代替。
MySQL5.5版本中该查询先把accessLog表中版本为2.2的数据线过滤出来，然后每个符合条件的数据都与内表进行一次select id from accessLog_01，因此性能低下。MySQL5.5采取的解决方法是将in重写为exists。
在MySQL5.6/5.7版本中，子查询执行计划是将in/exists重写为join，如下看执行计划：
点击(此处)折叠或打开
- mysql> select version();
- +------------+
- | version() |
- +------------+
- | 5.7.18-log |
- +------------+
点击(此处)折叠或打开
- 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 | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | NULL |
- | 1 | SIMPLE | ac | NULL | eq_ref | PRIMARY | PRIMARY | 8 | <subquery2>.id | 1 | 100.00 | NULL |
- | 2 | MATERIALIZED | accessLog_01 | NULL | ALL | NULL | NULL | NULL | NULL | 1305 | 100.00 | NULL |
- +----+--------------+--------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
点击(此处)折叠或打开
- 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 |
- +----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------+
点击(此处)折叠或打开
- 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 |
- +----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
办连接查询优化默认开启，通过show variables like 'optimizer_switch' \G查询：
点击(此处)折叠或打开
- 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)
但是半连接优化只是针对查询，对于DML操作，性能依旧很差。
内容总结
以上是互联网集市为您收集整理的MySQL5.7新特性之select半连接优化全部内容，希望文章能够帮你解决MySQL5.7新特性之select半连接优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。