代码与优化（4）——MYSQL的连表与子查询

​ mysql连表理解

1. 最简单的情形 user用户表里面 字段 id 在另外一张关联表例如订单表关联过来了 user_id
   需要返回下单的用户昵称信息
   当后台需要列表，同时需要显示最新的用户信息的时候

   如果不使用联表的逻辑查询: 主要解决办法

   读取全部的订单信息表，根据每张订单信息表的user_id，去读取用户表。这个时候，造成的是噩梦般的foreach +mysql循环。显然当用户量和订单量增大的时候，系统查询压力超大。当你系统订单很小的时候，或者仅仅是demo练手，这种写法被常用。

   于是进行一步优化，我们读取订单列表，将当前需要查询的user_id 拼接成一个用户数组，使用in查询方法。然后将返回的数组拼接成由用户uid 为键值的数组，最后我们再和我们的订单表进行信息拼接，读取用户的相关信息。
   当系统很大的时候，或者需要优化的时候，使用这种方法，优势也很明显，比如订单表的查询不会受用户表的干扰，可能订单只有几十万条，但是会员已经上千万，而且我们需要将user进行分开管理和做逻辑处理操作。这种办法对于大数据的处理，有优势。 而且in查询是可以使用索引的，简单的id通过in同样使用。

   而当小型项目的时候，直接使用join，开发效率是最高的。直接使用订单表join系统的user表，如果俩者信息匹对上，直接显示出来，否则就不显示。

   这里说明下 mysql的连接类型:

   1. 内连接（INNER JOIN）：返回两个表中匹配的行。只有当两个表中都存在匹配的行时，才会返回结果。（我们最常用的情形，会员跟订单都匹配到了信息才显示出来）
   2. 左连接（LEFT JOIN）：返回左表中的所有行以及右表中与左表匹配的行。如果右表中没有匹配的行，则结果为 NULL（删除了的会员订单列表，我们也可以看到）
   3. 右连接（RIGHT JOIN）：返回右表中的所有行以及左表中与右表匹配的行。如果左表中没有匹配的行，则结果为 NULL（极少使用，如果使用，会将user表放到左边来查询）

   示例MYSQL的查询: 可以选择指定的显示字段 查询条件，当连表只有一个唯一字段的时候，不需要加上修饰表

   SELECT `user_id`,`name`,`prebooktime`,`pick_time`,m.name as order_sn,`u`.`nickname`,`u`.`mobile`,`u`.`avatar` FROM `tb_order` `m` INNER JOIN `tb_user` `u` ON `m`.`user_id`=`u`.`id` WHERE  `m`.`status` = '5'   ORDER BY `pick_time` ASC
   
   

   thinkphp里面的语法糖
   Db::name('order')
               ->alias('m')
               ->join('user u','m.user_id = u.id')
               ->field('user_id,name,prebooktime,pick_time,m.name as order_sn,u.nickname,u.mobile,u.avatar')
               ->where([
                   'm.status' => '5',
                   'm.pick_user_id' => ['>', 0],
                   'm.pick_time' => ['>', 0],
               ])
               ->order('pick_time', 'asc')
               ->select()
       //如果tp里面需要直接使用查询
       Db::query($sql)  // 然后写入拼接SQL即可，需要注意的是自己拼接的SQL 需要对SQL注入进行检测
   

   通过对比我们其实发现，排除第一种直观的逻辑方法，第二种方法，逻辑度比联表逻辑度复杂俩个等级，先读取订单表，根据订单表再读取用户表信息，根据用户表信息再回填订单表里面，而且没有办法只读取存在用户的订单情形。而join大幅度减轻了逻辑负担。所以适当使用join能大幅度提升开发效率。
   在最开始我比较排斥实用join连表，主要设计考虑点都是大量数据优化的卡顿问题，现在来看，对于绝大部分的中小系统，只要MYSQL能命中索引，基本不存在订单的性能问题，需要考虑联表降低开发逻辑的复杂度。

   2.子查询的概念。上述的查询里面因为只是需要将俩张表联合起来查询 订单和user关系表对应起来，逻辑简单。
   子查询是一个嵌套在另一个查询（主查询）内部的查询。子查询先被执行，其结果被用于主查询中。

   如果更复杂的需求，我们需要查出每个用户的订单数，然后根据当前的订单数量返回给前端 用户+订单数量，需要用到子查询，首先我们需要查询出每个用户user_id 和 订单数量total_orders 作为一张新的表，对user表进行连表查询

   SELECT u.name, t.total_orders
   FROM (
       SELECT user_id, COUNT(order_id) AS total_orders
       FROM orders
       GROUP BY user_id
   ) t
   JOIN users u ON t.user_id = u.user_id;
   

​ 如果不用子查询，我们需要先试用group 查询获取到user_id和count的用户的统计结果，然后我们在根据我们当前的user_id 去读取我们的user表的用户信息数据。我们可以理解为子查询，其实就是中转到了我们外面的处理逻辑。

子查询也是大幅度降低我们本身复杂度的操作之一。

3.更复杂的联表 （三张表或者以上）
从逻辑上讲，更复杂的联表能降低我们的逻辑复杂度，但是已经开始起反作用了，因为本身我们构造三张表以上的SQL逻辑，就已经很复杂了，如果进行调试，调试MYSQL的成本比调试代码高很大，而且改动也麻烦很多。
，所以不推荐使用多联表，而更大的问题在于，性能的不可控。一旦索引命中失败，而且使用了三张以上的join联表，每张表里面有个十来万的数据，一般服务器可能就开始卡了。而且多级联表带来的另外一个后果，逻辑单元全部封装到了数据库里面，直接导致卡顿的时候，维护成本极高。而且根据绝大部分的业务逻辑，单表解决70%，加上join一张表可解决98%的问题。
而且从并发角度而言，基本都是数据库层面的卡段，而服务器层面的卡顿，只要加机器分担数据库的运算即可。

SELECT * 
FROM table1 
INNER JOIN table2 ON table1.column = table2.column
INNER JOIN table3 ON table2.column = table3.column;