MySQL运维实战教程（4）多表连接查询
一、MySQL 多表连接查询概述
多表连接查询是指在多个表中，存在一个或多个相同的字段（这些字段的值必须一致），通过将这些字段连接起来，就能将不同表的数据整合在一起，形成一张包含所有相关信息的大表。这样就可以通过一个查询语句同时获取多个表的数据。多表连接查询按照不同的角度可以分为等值连接和非等值连接、自连接和非自连接以及内连接和外连接。
在多表查询时，最外层的表称为驱动表，该表的查询会先被处理（通常 FROM 中第一个表是驱动表），内层循环的表称为被驱动表，会在驱动表之后进行处理。其工作过程可以看作是一个分步执行的操作，数据库会从第一张表开始，把符合条件的数据逐步“拼接”到最终结果中，整个过程如下：
逐行扫描驱动表：按顺序读取驱动表的每一行。
匹配被驱动表：根据连接条件，在被驱动表中查找所有匹配的行。
拼接结果：将匹配的数据拼接到结果集中。
重复直到完成：对驱动表的每一行重复上述过程，直到所有行都处理完。
在进行多表连接之前需要确定各个表之间的关系，即它们如何通过共同的列连接在一起，比如外键或者相同字段。以下是一个在不使用多表连接进行员工所在城市的查询过程：
通过EMPLOYEES表查询出了员工姓名和所属部门，这里的关键信息为department_id
通过DEPARTMENTS表查询department_id所在的区域，关键信息为location_id
通过LOCATIONS表的location_id 最终查询出员工所在城市
上面的查询过程使用SQL表示经历了3步，而使用多表连接仅需要一条SQL即可完成整个查询过程
#查询员工tanglu所在城市，在没有多表连接时查询过程如下，会产生多次交互才能获取最终数据 SELECT * FROM employees WHERE last_name = 'tanglu'; #通过员工表找到员工tanglu的department_id（部门id）为80 SELECT * FROM departments department_id = 80 ; #通过department_id=80这个条件在部门表中查询，得到location_id是2500 SELECT * FROM locations WHERE location_id = 2500 ; #通过location_id在地区表中最终找到城市 #使用多表连接时一条语句即可满足上面3条语句的需求 SELECT employee_id,last_name,department_name,city FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id;
多表连接关联条件
如果有N个表进行多表查询，那么关联条件至少需要N-1个，以下是一个三表关联查询示例
SELECT employee_id,last_name,department_name,city FROM employees e JOIN departments d #第一个关联 ON e.department_id = d.department_id JOIN locations l #第二个关联 ON d.location_id = l.location_id;
二、MySQL 多表连接语法
在 SQL99 语法里使用JOIN...ON...语句来进行多表连接查询，其中ON用来指定多表之间的连接条件（早期的 SQL92 语法中不需要使用关键词JOIN...ON...，但是MySQL对于SQL92语法支持性不够好，建议使用SQL99语法），在进行多表连接查询时可以先找出需要查询的字段以及所在的表，然后按照固定框架写条件
# SELECT 字段1,字段2,字段3 FROM 表1 # JOIN 表2 ON 关联条件 # JOIN 表3 ON 关联条件
· 内连接（INNER JOIN，简写为JOIN）
内连接是最常用的连接方式，查询结果只返回多表之间匹配上的数据。也就是说只有当两个表中有相同的值时，才会把这些行组合在一起并返回查询结果。假设有员工表（employees）和部门表（departments），employees 表包含字段有 id（员工ID）、name（员工名字）、dept_id（部门ID），departments 表包含字段有 dept_id（部门ID）、dept_name（部门名称）。如果想查询每个员工的姓名和所在的部门名称，可以使用下面语句进行查询，但是只有员工的dept_id和部门的dept_id匹配时，才会返回该行数据，而如果员工没有填写部门信息，将无法查出该名员工
SELECT employees.name, departments.dept_name FROM employees INNER JOIN departments ON employees.dept_id = departments.dept_id;
· 外连接（LEFT JOIN、RIGHT JOIN）
左连接除了返回两表之间相匹配的数据之外，还会返回左表的所有数据，这样就可以解决上面遇到的员工没有填写部门信息而无法查询出来的问题。通常一个查询需求如果是"查询所有xxxxx"这样的描述就要用到外连接。外连接又分左外连接（left join）和右外连接（right join），左外连接是指"两个表在连接过程中除了返回满足条件的行以外，还需要返回左表中的所有行（右外连接则相反）。在实际开发过程中一般都是把数据量多的表放前面，然后使用LEFT JOIN。
SELECT employees.name, departments.dept_name FROM employees LEFT JOIN departments ON employees.dept_id = departments.dept_id;
· 自然连接（NATURAL JOIN）
自然连接是一种基于表中相同列名的自动连接方式。SQL 会根据两个表中列名相同的字段自动进行连接，并且不会重复列出相同的列。如果明确两个表有同名的列，并且这些列是用来建立连接的，可以使用 NATURAL JOIN 来自动完成连接，简化代码书写
SELECT employees.name, departments.dept_name FROM employees NATURAL JOIN departments;
· USING 连接
由于 NATURAL JOIN 会自动使用所有同名列进行连接，不够灵活。而 USING 提供了更多的灵活性，当多个表中有同名列时，可以用 USING 来明确指定作为连接条件的列
SELECT employees.name, departments.dept_name FROM employees JOIN departments USING (dept_id);
· 自连接多表查询
自连接查询是指将同一张表进行关联，在逻辑上这一张表等同于两张表，所以在查询的时候用自己和自己做关联即可
#通过员工信息表进行自连接查询，查出每个员工以及他的上级姓名，这里用到的连接条件就是员工表manager_id与管理者表employee_id相关联（虽然这里分为了员工表和管理者表，但实际就是一张表） SELECT e.employee_id,e.first_name,m.first_name AS manager FROM employees e JON employees m ON e.manager_id = m,employee_id
· 复合连接查询
当存在无法用单一列准确标识某张表里的情况时，就需要组合不同的列来构成唯一行，这种就叫复合连接查询
SELECT * FROM order_items oi JOIN order_item_notes oin ON oi.order_id = oin.order_id AND oi.product_id = oin.product_id
三、MySQL 多表连接性能问题
多表连接的本质是对多表之间数据进行循环匹配，首先扫描驱动表获取到数据，然后用这些数据再和另外一张表进行比较匹配。在比较匹配算法上，MySQL支持INLJ、BNLJ、SNLJ 3种（从8.0.20开始废弃BNLJ，由HASH JOIN替代），在整体效率上INLJ >BNLJ>SNLJ。SNLJ就是把驱动表中符合条件的数据一条一条拿到被驱动表中进行匹配（笛卡尔积），而BNLJ则是把驱动表中的多条数据放到JOIN BUFFER中一起进行匹配，性能更高。
· 控制关联表数量
由于JOIN查询方式非常消耗资源，所以要控制关联表的数量，尽量不要超过3张表
· 小表驱动大表
因为外层循环决定了整个扫描的次数，所以用小表驱动大表可以有效减少循环次数，并且要为被驱动表的匹配条件增加索引，减少内层表循环的匹配次数。假设 users 表100行，orders表10000行。在下面的SQL中，如果 users 是驱动表，数据库会先从 users 表中取一行，然后到 orders 表中匹配所有相关数据，这样相当于进行了外层100次扫描和内层100次扫描。而如果 orders 是驱动表，数据库会先从 orders 表中取一行，再到 users 表中匹配数据。此时外层循环高达 10000 次，且内层也需要循环扫描10000 次。
举个生活中的例子。假设老师核对学生出勤情况，此时有出勤名单记录了当天的学生出勤情况（小表，10人），全校学生名单记录了全校学生的详细信息（大表，1000人）。如果想找出出勤学生的详细信息，最合理的方式是先从 出勤名单 里挑出 10 个学生的名字，然后在 全校学生名单 中匹配这 10 个名字，这样只需要匹配 10 次即可。但如果反过来从 全校学生名单 中逐行检查每个学生是否出现在出勤名单中，会导致 1000 次匹配操作，效率明显更低。
SELECT users.name, orders.order_id, orders.amount FROM users JOIN orders ON users.user_id = orders.user_id;
· 多表连接笛卡尔乘积问题
在使用多表连接查询的时候，需要注意笛卡尔积问题，如果使用了错误的查询方式、缺少连接条件的话，就会出现交叉连接，也叫做笛卡尔积（笛卡尔积是一个数学运算，假设有两个集合X和Y，X和Y的笛卡尔积就是X和Y的所有可能的组合），会把每个员工与每个部门进行匹配，这样会产生大量的冗余查询。为了避免笛卡尔积的错误出现，需要在WHERE子句后添加正确的连接条件，也就是说可以让两张表中形成组合关系的字段
SELECT employee_id,department_name FROM employees,departments #错误的查询方式 SELECT employee_id,department_name FROM employess JOIN departments ON employees.department_id = departments.department_id
如果查询语句中出现了多个表共有的字段，则必须指明字段所在的表。从SQL优化的角度，建议多表查询时，每个字段前都声明所属表名
#指明department_id字段所属表 SELECT employee_id,department_name,employees.department_id FROM employess JOIN departments ON employees.department_id = departments.department_id
评论