join

为什么MySQL不推荐使用子查询和join

1.对于mysql，不推荐使用子查询和join是因为本身join的效率就是硬伤，一旦数据量很大效率就很难保证，强烈推荐分别根据索引单表取数据，然后在程序里面做join，merge数据。

2.子查询就更别用了，效率太差，执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。

3.如果是JOIN的话，它是走嵌套查询的。小表驱动大表，且通过索引字段进行关联。如果表记录比较少的话，还是OK的。大的话业务逻辑中可以控制处理。

4.数据库是最底层的，瓶颈往往是数据库。建议数据库只是作为数据store的工具，而不要添加业务上去。

一、应用层关联的优势：

让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。
将查询分解后，执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
查询本身效率也可能会有所提升。查询id集的时候，使用IN（）代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。
可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消艳。
更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。
二、应用层关联的使用场景：

当应用能够方便地缓存单个查询的结果的时候
当可以将数据分布到不同的MySQL服务器上的时候
当能够使用IN（）的方式代替关联查询的时候
并发场景多，DB查询频繁，需要分库分表
三、不推荐使用join的原因：

1.DB承担的业务压力大，能减少负担就减少。当表处于百万级别后，join导致性能下降；

2.分布式的分库分表。这种时候是不建议跨库join的。目前mysql的分布式中间件，跨库join表现不良。

3.修改表的schema，单表查询的修改比较容易，join写的sql语句要修改，不容易发现，成本比较大，当系统比较大时，不好维护。

四、不使用join的解决方法：

在业务层，单表查询出数据后，作为条件给下一个单表查询。也就是子查询。会担心子查询出来的结果集太多。mysql对in的数量没有限制，但是mysql限制整条sql语句的大小。通过调整参数max_allowed_packet ，可以修改一条sql的最大值。建议在业务上做好处理，限制一次查询出来的结果集是能接受的。

五、再来说说join查询的好处：

1.做分页查询：

关联查询的好处时候可以做分页，可以用副表的字段做查询条件，在查询的时候，将副表匹配到的字段作为结果集，用主表去in它，但是问题来了，如果匹配到的数据量太大就不行了，也会导致返回的分页记录跟实际的不一样，解决的方法可以交给前端，一次性查询，让前端分批显示就可以了，这种解决方案的前提是数据量不太，因为sql本身长度有限。

https://www.cnblogs.com/BeginMan/p/3754322.html

在使用left jion时，on和where条件的区别如下：
1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。 2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

join用法

左(外)链接：相当于以主表为主显示全部主表，副表有的主表没有不显示，主表有的副表没有则副表显示空，所以做链接会存在副表为空的情况所以查询有两种

select * from A as a left join B as b on b.bid=a.aid where b.bid is NULL;//注意不是=是is或者is not

右外链接：和左链接完全相反

全外连接：取AB表的并集，mysql是不支持全外连接的我们可以用union all来实现全链接；select * from A as a left join B as b on b.bid=a.aid union all select * from A as a right join B as b on a.aid=b.bid;

内链接：相当于取两张表的公共部分：取交集

交叉连接：笛卡尔链接或者叉乘如果A和B是两个数据分别是4和5条那么他的结果及时4x5=20条数据 select * from A as a cross join B as b;

A表： B表

+-----+---------+-------+   | bid | bname | bnum |
| aid | amum | aname |   +-----+---------+------+
+-----+---------+-------+   | 1 | 9876543 | 1 |
| 1 | 1234567 | a |   | 2 | 9876543 | 2 |
| 2 | 1234567 | b |   | 4 | 9876543 | 3 |
| 3 | 1234567 | c | | 5 | 9876543 | 5 |
| 4 | 1234567 | D | | 6 | 9876543 | 6 |
| 5 | 1234567 | E |   | 7 | 9876543 | 7 |
| 6 | 1234567 | F |   | 8 | 9876543 | 8 |
| 7 | 1234567 | g |   | 9 | 9876543 | 9 |
+-----+---------+-------+ +-----+---------+------+

右链接：select * from A as a right join B as b on a.aid=b.bid; 左链接：select * from A as a left join B as b on b.bid=a.aid; | select * from A as a left join B as b on b.bid=a.aid where b.bid is NULL;

+------+------------+----------+-----+-----------+--------+ +-----+---------+-------+------+---------+------+ | | 3 | 1234567 | c | NULL | NULL | NULL |
| aid | amum | aname | bid | bname | bnum |   | aid | amum | aname | bid | bname | bnum |
+------+------------+----------+-----+-----------+--------+ +-----+---------+-------+------+---------+------+
| 1 | 1234567 | a | 1 | 9876543 | 1 |   | 1 | 1234567 | a | 1 | 9876543 | 1 |
| 2 | 1234567 | b | 2 | 9876543 | 2 |   | 2 | 1234567 | b | 2 | 9876543 | 2 |
| 4 | 1234567 | D | 4 | 9876543 | 3 |   | 3 | 1234567 | c | NULL | NULL | NULL |
| 5 | 1234567 | E | 5 | 9876543 | 5 |   | 4 | 1234567 | D | 4 | 9876543 | 3 |
| 6 | 1234567 | F | 6 | 9876543 | 6 |   | 5 | 1234567 | E | 5 | 9876543 | 5 |
| 7 | 1234567 | g | 7 | 9876543 | 7 |   | 6 | 1234567 | F | 6 | 9876543 | 6 |
| NULL | NULL | NULL | 8 | 9876543 | 8 |   | 7 | 1234567 | g | 7 | 9876543 | 7 |
| NULL | NULL | NULL | 9 | 9876543 | 9 |   +-----+---------+-------+------+---------+------+
+------+-----------+----------+-----+------------+--------+

select * from A as a inner join B as b on a.aid=b.bid;

全链接：

全链接要注意想要在页面得到全部的信息左表和右表查了一样的信息是不行的因为相同字段的原因，后面的字段会覆盖前面的内容，如果起别名的话用union又合不到想要的表，解决办法：

左查询这边只查询左表的字段，右查询那边只查询右表的字段，然后用union all链接起来；

左查询：select a.user_name,a.var from user1 as a left join user2 as b on a.user_name=b.user_name；

右查询：select b.user_name,b.var from user1 as a right join user2 as b on a.user_name=b.user_name;

如：select a.user_name,a.var from user1 as a left join user2 as b on a.user_name=b.user_name union all select b.user_name,b.var from user1 as a right join user2 as b on a.user_name=b.user_name;

想要合并的就像上面的那样不要左右查询两边同时查询一样的字段，像id这种不想合并的那么可以加个别名两边同时查询就行

select * from A as a left join B as b on b.bid=a.aid union all select * from A as a right join B as b on a.aid=b.bid;

+------+---------+-------+------+---------+------+
| aid | amum | aname | bid | bname | bnum |
+------+---------+-------+------+---------+------+
| 1 | 1234567 | a | 1 | 9876543 | 1 |
| 2 | 1234567 | b | 2 | 9876543 | 2 |
| 3 | 1234567 | c | NULL | NULL | NULL |
| 4 | 1234567 | D | 4 | 9876543 | 3 |
| 5 | 1234567 | E | 5 | 9876543 | 5 |
| 6 | 1234567 | F | 6 | 9876543 | 6 |
| 7 | 1234567 | g | 7 | 9876543 | 7 |
| 1 | 1234567 | a | 1 | 9876543 | 1 |
| 2 | 1234567 | b | 2 | 9876543 | 2 |
| 4 | 1234567 | D | 4 | 9876543 | 3 |
| 5 | 1234567 | E | 5 | 9876543 | 5 |
| 6 | 1234567 | F | 6 | 9876543 | 6 |
| 7 | 1234567 | g | 7 | 9876543 | 7 |
| NULL | NULL | NULL | 8 | 9876543 | 8 |
| NULL | NULL | NULL | 9 | 9876543 | 9 |
+------+---------+-------+------+---------+------+

交叉连接： select * from A as a cross join B as b;

+-----+---------+-------+-----+---------+------+
| aid | amum | aname | bid | bname | bnum |
+-----+---------+-------+-----+---------+------+
| 1 | 1234567 | a | 1 | 9876543 | 1 |
| 2 | 1234567 | b | 1 | 9876543 | 1 |
| 3 | 1234567 | c | 1 | 9876543 | 1 |
| 4 | 1234567 | D | 1 | 9876543 | 1 |
| 5 | 1234567 | E | 1 | 9876543 | 1 |
| 6 | 1234567 | F | 1 | 9876543 | 1 |
| 7 | 1234567 | g | 1 | 9876543 | 1 |
| 1 | 1234567 | a | 2 | 9876543 | 2 |
| 2 | 1234567 | b | 2 | 9876543 | 2 |
| 3 | 1234567 | c | 2 | 9876543 | 2 |
| 4 | 1234567 | D | 2 | 9876543 | 2 |
| 5 | 1234567 | E | 2 | 9876543 | 2 |
| 6 | 1234567 | F | 2 | 9876543 | 2 |
| 7 | 1234567 | g | 2 | 9876543 | 2 |
| 1 | 1234567 | a | 4 | 9876543 | 3 |
| 2 | 1234567 | b | 4 | 9876543 | 3 |
| 3 | 1234567 | c | 4 | 9876543 | 3 |
| 4 | 1234567 | D | 4 | 9876543 | 3 |
| 5 | 1234567 | E | 4 | 9876543 | 3 |
| 6 | 1234567 | F | 4 | 9876543 | 3 |
| 7 | 1234567 | g | 4 | 9876543 | 3 |
| 1 | 1234567 | a | 5 | 9876543 | 5 |
| 2 | 1234567 | b | 5 | 9876543 | 5 |
| 3 | 1234567 | c | 5 | 9876543 | 5 |
| 4 | 1234567 | D | 5 | 9876543 | 5 |
| 5 | 1234567 | E | 5 | 9876543 | 5 |
| 6 | 1234567 | F | 5 | 9876543 | 5 |
| 7 | 1234567 | g | 5 | 9876543 | 5 |
| 1 | 1234567 | a | 6 | 9876543 | 6 |
| 2 | 1234567 | b | 6 | 9876543 | 6 |
| 3 | 1234567 | c | 6 | 9876543 | 6 |
| 4 | 1234567 | D | 6 | 9876543 | 6 |
| 5 | 1234567 | E | 6 | 9876543 | 6 |
| 6 | 1234567 | F | 6 | 9876543 | 6 |
| 7 | 1234567 | g | 6 | 9876543 | 6 |
| 1 | 1234567 | a | 7 | 9876543 | 7 |
| 2 | 1234567 | b | 7 | 9876543 | 7 |
| 3 | 1234567 | c | 7 | 9876543 | 7 |
| 4 | 1234567 | D | 7 | 9876543 | 7 |
| 5 | 1234567 | E | 7 | 9876543 | 7 |
| 6 | 1234567 | F | 7 | 9876543 | 7 |
| 7 | 1234567 | g | 7 | 9876543 | 7 |
| 1 | 1234567 | a | 8 | 9876543 | 8 |
| 2 | 1234567 | b | 8 | 9876543 | 8 |
| 3 | 1234567 | c | 8 | 9876543 | 8 |
| 4 | 1234567 | D | 8 | 9876543 | 8 |
| 5 | 1234567 | E | 8 | 9876543 | 8 |
| 6 | 1234567 | F | 8 | 9876543 | 8 |
| 7 | 1234567 | g | 8 | 9876543 | 8 |
| 1 | 1234567 | a | 9 | 9876543 | 9 |
| 2 | 1234567 | b | 9 | 9876543 | 9 |
| 3 | 1234567 | c | 9 | 9876543 | 9 |
| 4 | 1234567 | D | 9 | 9876543 | 9 |
| 5 | 1234567 | E | 9 | 9876543 | 9 |
| 6 | 1234567 | F | 9 | 9876543 | 9 |
| 7 | 1234567 | g | 9 | 9876543 | 9 |
+-----+---------+-------+-----+---------+------+

union和union all的用法：就是把多个select的结果集放到一起（一个结果集)

两个的区别：当使用 UNION 时，MySQL 会把结果集中重复的记录删掉，而使用 UNION ALL ，MySQL 会把所有的记录返回，且效率高于 UNION。

mysql不能操作字句本身那么如何更新使用过滤条件中包括自身的表？

如：update user1 set var="齐天大圣" where user1.user_name in (select b.user_name from user1 as a join user2 as b on a.user_name=b.user_name);