首页 / MYSQL / MySQL 通过semi join 优化子查询

MySQL 通过semi join 优化子查询

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 通过semi join 优化子查询，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5761字，纯文字阅读大概需要9分钟。

内容图文

desc class; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | class_num | int(11) | NO | PRI | NULL | | | class_name | varchar(20) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> desc roster; +-------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+-------+ | class_num | int(11) | YES | | NULL | | | student_num | int(11) | YES | | NULL | | +-------------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)

roster表中记录的是学生的学号以及对应的教室，多个学生可能在同一个教室，所以字段class_num有重复值

class表中记录的是教室及对应的班级名，字段class_num为唯一值

如果要查询存在学生的班级有哪些

mysql>  SELECT class.class_num, class.class_name FROM class INNER JOIN roster WHERE class.class_num = roster.class_num;       
+-----------+------------+
| class_num | class_name |
+-----------+------------+
|         2 | class 2    |
|         3 | class 3    |
|         3 | class 3    |
+-----------+------------+
3 rows in set (0.00 sec)

可以通过distinct去除重复值，但这样做影响性能，所以通过子查询来得出结果

mysql>  SELECT class_num, class_name FROM class WHERE class_num IN (SELECT class_num FROM roster);       
+-----------+------------+
| class_num | class_name |
+-----------+------------+
|         2 | class 2    |
|         3 | class 3    |
+-----------+------------+
2 rows in set (0.00 sec)

优化器实际上是将子查询改写为了半连接

mysql> explain SELECT class_num, class_name FROM class WHERE class_num IN (SELECT class_num FROM roster);
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                             |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------+
|  1 | SIMPLE      | roster | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Start temporary                                                   |
|  1 | SIMPLE      | class  | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    4 |    25.00 | Using where; End temporary; Using join buffer (Block Nested Loop) |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                      |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`class`.`class_num` AS `class_num`,`test`.`class`.`class_name` AS `class_name` from `test`.`class` semi join (`test`.`roster`) where (`test`.`class`.`class_num` = `test`.`roster`.`class_num`) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Start temporary 和 End temporary的使用表明使用了临时表来去除重复值
如果 select_type 的值为 MATERIALIZED 并且 字段 rows的输出是 <subqueryN> 则表明临时表用于了物化表

select_type value of MATERIALIZED and rows with a table value of <subqueryN>.

 

如果子查询符合准则（参考文献：http://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html#semi-joins），MySQL将其转化为semi-join并从以下策略中作出基于cost的选择

  • Convert the subquery to a join, or use table pullout and run the query as an inner join between subquery tables and outer tables. Table pullout pulls a table out from the subquery to the outer query.

  • Duplicate Weedout: Run the semi-join as if it was a join and remove duplicate records using a temporary table.

  • FirstMatch: When scanning the inner tables for row combinations and there are multiple instances of a given value group, choose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows.

  • LooseScan: Scan a subquery table using an index that enables a single value to be chosen from each subquery‘s value group.

  • Materialize the subquery into a temporary table with an index and use the temporary table to perform a join. The index is used to remove duplicates. The index might also be used later for lookups when joining the temporary table with the outer tables; if not, the table is scanned

     不确定的内容不敢随意翻译，摘出来原汁原味的文献内容

系统变量optimizer_switch中的semi join 标签控制着半连接是否可用，5.6默认是开启的

 

MySQL 通过semi join 优化子查询

标签：

本文系统来源：http://www.cnblogs.com/Bccd/p/5942827.html

内容总结

以上是互联网集市为您收集整理的MySQL 通过semi join 优化子查询全部内容，希望文章能够帮你解决MySQL 通过semi join 优化子查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/470789.html

来源：【匿名】