我要评论有INNER JOIN，WHERE（等值连接），STRAIGHT_JOIN, JOIN(省略INNER)四种写法。
SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId=t_type.id;
SELECT * FROM t_blog,t_type WHERE t_blog.typeId=t_type.id;
SELECT * FROM t_blog STRAIGHT_JOIN t_type ON t_blog.typeId=t_type.id; --注意STRIGHT_JOIN有个下划线
SELECT * FROM t_blog JOIN t_type ON t_blog.typeId=t_type.id;
+----+-------+--------+----+------+
| id | title | typeId | id | name |
+----+-------+--------+----+------+
| 1 | aaa | 1 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 7 | ggg | 2 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 6 | fff | 3 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 5 | eee | 4 | 4 | C# |
+----+-------+--------+----+------+
内连接谁当驱动表：当内连接时，务必用小表驱动大表，小表驱动大表可以减小内循环的次数。例子：
EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.type=t2.type;
+----+-------+------+------+-------+----------------------------------------------------+
| id | table | type | key | rows | Extra |
+----+-------+------+------+-------+----------------------------------------------------+
| 1 | t1 | ALL | NULL | 10000 | NULL |
| 1 | t2 | ALL | NULL | 100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------+------+------+-------+----------------------------------------------------+
EXPLAIN SELECT * FROM t2 STRAIGHT_JOIN t1 ON t2.type=t1.type;
+----+-------+------+------+-------+----------------------------------------------------+
| id | table | type | key | rows | Extra |
+----+-------+------+------+-------+----------------------------------------------------+
| 1 | t2 | ALL | NULL | 100 | NULL |
| 1 | t1 | ALL | NULL | 10000 | Using where; Using join buffer (Block Nested Loop) |
+----+-------+------+------+-------+----------------------------------------------------+
对于第一条查询语句，t1是驱动表，其有10000条记录，内循环也就有10000次，这还得了？
对于第二条查询语句，t2是驱动表，其有100条记录，内循环100次，感觉不错!
这些SQL语句的执行时间也说明了，当内连接时，务必用小表驱动大表。
本文地址：https://blog.csdn.net/weixin_46162745/article/details/108571699
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论