多表连接子查询

表连接

• Left join： 以左表为准，去右表找数据，如果没有匹配的数据，则以null补空位，
• Inner join： 查询结果是左右连接的交集
• Right join：和left相反

嵌套循环关联

SELECT   *    FROM    tbl1 a    LEFT JOIN     tbl2 b    ON    a.col1 = b.col1  

Paste_Image.png

由图可见， 在MySQL内部是这样关联的， 所以耗时也是a.time * b.time

子查询

上面看到的表连接， 我们可以将多个表连在一起查询更丰富的内容。
子查询的含义是：当一个查询是另一个查询的条件时。 通过多表连接和子查询可以完成更为丰富的功能。

按照返回结果集分类

• 表子查询：返回的结果集是一个行的集合，N行N列（N>=1）。表子查询经常用于父查询的FROM子句中。
• 行子查询：返回的结果集是一个列的集合，一行N列（N>=1）。行子查询可以用于福查询的FROM子句和WHERE子句中。�列子查询：返回的结果集是一个行的集合，N行一列（N>=1）。
• 标量子查询：返回的结果集是一个标量集合，一行一列，也就是一个标量值。可以指定一个标量表达式的任何地方，都可以用一个标量子查询。

按照对返回结果的调用方法分类

• where型子查询：（把内层查询结果当作外层查询的比较条件）� （把内层查询结果当作外层查询的比较条件）
  • 不用order by 来查询最新的商品

 select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);

- 取出每个栏目下最新的产品(goods_id唯一)

select cat_id,goods_id,goods_name from goods where goods_id in (select max(goods_id) from goods group by cat_id);

• from型子查询：（把内层的查询结果供外层再次查询）�
  用子查询查出挂科两门及以上的同学的平均成绩

  • 先查出哪些同学挂科两门以上

select name,count(*) as gk from stu where score < 60 having gk >=2;

- 以上查询结果，我们只要名字就可以了，所以再取一次名字

select name from (select name,count(*) as gk from stu having gk >=2) as t;

- 找出这些同学了，那么再计算他们的平均分

select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;

• exists型子查询（把外层查询结果拿到内层，看内层的查询是否成立）
  • 查询哪些栏目下有商品，栏目表category,商品表goods

select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

• 关联子查询
  关联子查询是指一个包含对表的引用的子查询，该表也显示在外部查询中。通俗一点来讲，就是子查询引用到了主查询的数据数据。很明显，一般情况下关联子查询的效率是比较低下的，实际上本例中的关联子查询例子也仅是为了演示关联子查询的原理及用法。如果可以的话，关联子查询尽量使用 JOIN 或其他查询来代替。

多表连接子查询的应用

分页优化

limit 1000,20，此时mysql排序出前1020条记录后仅仅返回第1001到1020条记录，前1000条记录都会被抛弃，查询和排序的代价非常高。

select id,title from collect limit 1000,10; --很快；基本上0.01秒就OK
select id,title from collect limit 90000,10; --从9万条开始分页, 8-9秒完成
select id from collect order by id limit 90000,10;-- id是做过索引的， 所以它的排序很快0.04秒
SELECT id,title,content FROM collect WHERE id IN (SELECT id FROM collect ORDER BY id limit 90000, 10);; 通过子查询很快就可以查到了

分解关联查询提高性能

         select * from tag
         join tag_post on tag_post.tag_id = tag.id
         join post on tag_post.post_id = post.id
         where tag.tag = 'mysql';
 
         可以分解成下面这些查询来代替
 
         select * from tag where tag = 'mysql';
         select * from tag_post where tag_id = 1234;
         select * from post where post.id in (123,456,9890);

1） 让缓存的效率更高。
应用缓存；mysql查询缓存
2） 执行单个查询可以减少锁的竞争。
3） 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
4） 查询本身的效率也可能会有所提升。
使用 IN () 代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效
5） 可以减少冗余记录的查询。
在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。这样的重构还可能减少网络和内存的消耗。
6） 这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。