Java面试题031：一文深入了解MySQL（3）

Java面试题029：一文深入了解MySQL（1）

Java面试题030：一文深入了解MySQL（2）

1、MySQL多表查询

（1）内连接 inner join

        返回两个表中完全匹配的行，即只保留两个表连接字段值相等的行。

（2）外连接

        左外连接

        LEFT JOIN 或 LEFT OUTER JOIN 左外连接返回左表中的所有行，以及右表中满足连接条件的行（如果左表中的某行在右表中没有匹配的行，那么结果集中该行的右表列将包含 NULL 值）。

        右外连接

        右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN）：右外连接返回右表中的所有行，以及左表中满足连接条件的行（如果右表中的某行在左表中没有匹配的行，那么结果集中该行的左表列将包含 NULL 值）。

（3）UNION 与 UNION ALL

-- UNION 语法
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
-- 这里使用了 column1, column2将字段一一列出来，如果 table1和table2字段的顺序一致，可以直接写为 select * ，下方 UNION ALL 同理
select * from table1
UNION
SELECT * FROM table2;-- UNION ALL 语法
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;
或
select * from table1
UNION ALL
SELECT * FROM table2;

（1）列数一致：所有 SELECT 语句的列数必须相同；
（2）数据类型兼容：对应列的数据类型需兼容（如 VARCHAR 与 TEXT 兼容）；
（3）列名规则：最终结果集的列名以第一个 SELECT 的列名为准；

• 字段相同，顺序相同

user1:

user2:

使用UNION查询 会自动去重，合并后的结果就是7条数据：

UNION ALL 不会自动去重，而是将两张表的全部数据合并，一共十条数据：

• 字段相同，顺序不同

user1：

user4：

        此时不能直接使用 select * ，否则不管是 UNION，还是UNION ALL，最终的表字段顺序会以第一张表 user1 的字段顺序为准，就会产生一个问题， user4 表中，passwor 和 username 顺序是反着的，会导致查询结果中，user4 表用户的密码 password 被当作 username，用户的 username 被当作 password；造成数据错误。

​

此时就需要将字段一一对应。

​

• 字段数量不同

user3：与user1对比，user3 少了一个 age 年龄字段

​

        此时查询的时候就需要"补列字段" ，可以使用 "NULL AS age"对查询结果中 user3 的age字段进行填充，使其全部为 NULL（也可以使用具体数据）。

2、笛卡尔积

        假设有两个集合A和B。A的元素是{a1, a2, …}，B的元素是{b1, b2, …}。那么，A和B的笛卡尔积就是从A中取一个元素，和从B中取一个元素，形成一个有序对，这样的所有有序对构成的集合就是笛卡尔积。数学上表示为：A × B = {(a1, b1), (a1, b2), …, (a2, b1), (a2, b2), …}。

        在数据库中，当你进行表连接操作时，如果没有指定任何连接条件（如使用WHERE子句），就会产生两个表的笛卡尔积。这意味着第一个表中的每一行都会与第二个表中的每一行配对，产生巨大数量的数据行。

（1）产生条件

        两表关联查询语句中没有指定连接条件。

表Employees中有两条数据：

表Departments中有两条数据： 

 不加查询条件进行查询，会发现出现4条数据，而且两个表的字段全部进行了展示

（2）避免笛卡尔积

        为了避免笛卡尔积，我们应该使用适当的连接条件。

使用显式的连接类型

• INNER JOIN: 使用INNER JOIN并指定连接条件，可以确保只连接相关的行。
• LEFT/RIGHT OUTER JOIN: 这些连接类型允许连接两个表，并包括左表/右表中的所有行，即使它们在右表/左表中没有匹配项。
• FULL OUTER JOIN: 它结合了LEFT和RIGHT JOIN的特点，如果左表或右表中的行没有匹配项，它也会被包含在结果中。

使用WHERE子句
        添加过滤条件: 在WHERE子句中明确指定连接条件可以防止产生笛卡尔积，因为它会限制只返回满足特定条件的行。

使用子查询
        子查询作为连接条件: 在连接的ON子句或WHERE子句中使用子查询，可以精确控制要返回的行。

使用聚合函数和GROUP BY
        分组和聚合: 当需要根据某个字段进行分组时，使用GROUP BY子句可以避免笛卡尔积，尤其是在进行统计计算时。

使用DISTINCT关键字
        消除重复行: 如果查询产生了重复行（这在某些类型的笛卡尔积中可能发生），使用DISTINCT关键字可以移除重复的结果集。

使用LIMIT子句
        限制返回行数: 在进行初步测试和调试时，使用LIMIT子句可以限制查询结果的行数，从而避免大量的输出，尤其是在处理可能产生笛卡尔积的复杂查询时。

3、SQL 查询语句的执行顺序

（8）Select
（9）distinct 字段名1,字段名2，
（6）[fun(字段名)]  
（1）from 表1
（3）<join类型>join 表2 
（2）on <join条件> 
（4）where <where条件> 
（5）group by <字段> 
（7）having <having条件> 
（10）order by <排序字段> 
（11）limit <起始偏移量,行数>

1. FROM：对 FROM 子句中的表1和表2执行笛卡儿积，产生虚拟表VT1

2. ON：对虚拟表 VT1 应用 ON 筛选，只有那些符合join条件的行才被插入虚拟表 VT2 中

3. JOIN：如果指定了 OUTER JOIN（如 LEFT JOIN、RIGHT JOIN），那么保留表中未匹配的行

作为外部行添加到虚拟表 VT2 中，产生虚拟表 VT3。如果 FROM 子句包含两个以上表，则对上一个连接生成的结果表 VT3 和下一个表重复执行步骤 1～步骤 3，直到处理完所有的表为止

4. WHERE：对虚拟表 VT3 应用 WHERE 过滤条件，只有符合条件的记录才被插入虚拟表 VT4 中

5. GROUP BY：根据 GROUP BY 子句中的列，对 VT4 中的记录进行分组操作，产生 VT5

6. 聚合函数：对表 VT5 进行 CUBE 或 ROLLUP 操作，产生表 VT6

7. HAVING：对虚拟表 VT6 应用 HAVING 过滤器，只有符合条件的记录才被插入虚拟表 VT7中。

8. SELECT：第二次执行 SELECT 操作，选择指定的列，插入到虚拟表 VT8 中

9. DISTINCT：去除重复数据，产生虚拟表 VT9

10. ORDER BY：将虚拟表 VT9 中的记录按照排序字段进行排序操作，产生虚拟表 VT10。

11. LIMIT：取出指定行的记录，产生虚拟表 VT11，并返回给查询⽤用户

举例：

用户表user ：

订单表orders ：

目标：查询来自北京，并且订单数少于2的客户。

SELECT a.user_id,COUNT(b.order_id) as total_orders
FROM  user as a
LEFT JOIN orders as b
ON a.user_id = b.user_id
WHERE a.city = 'beijing'
GROUP BY a.user_id
HAVING COUNT(b.order_id) < 2
ORDER BY total_orders desc

（1）FROM语句对两个表执行笛卡尔积，会得到一个虚拟表，VT1（vitual table 1）

（2）执行ON过滤

根据ON中指定的条件，去掉那些不符合条件的数据,得到VT2如下：

select * from user as a inner JOIN orders as b ON a.user_id = b.user_id;

（3）执行left join子句：user表作为保留表，未匹配的记录添加到VT2中形成VT3

（4）执行where条件过滤：对添加了外部行的数据进行where条件过滤，执行WHERE a.city = 'beijing'  得到VT4如下：

（5）执行group by分组语句：执行GROUP BY a.user_id 得到VT5如下：

（6）执行having：HAVING子句主要和GROUP BY子句配合使用，对分组得到VT5的数据进行条件过滤，执行 HAVING COUNT(b.order_id) < 2，得到VT6如下：

（7）select列表：执行测试语句中的SELECT a.user_id,user_name,COUNT(b.order_id) as total_orders，从VT6中选择出我们需要的内容，得到VT7如下：

（8）执行distinct去重复数据：如果在查询中指定了DISTINCT子句，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘了）。这张临时表的表结构和上一步产生的虚拟表是一样的，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来除重复数据。

（9）执行order by字句：对虚拟表VT7中的内容按照指定的列进行排序，然后返回一个新的虚拟表

（10）执行limit字句：

LIMIT子句从上一步得到的虚拟表中选出从指定位置开始的指定行数据，常用来做分页；

MySQL数据库的LIMIT支持如下形式的选择：limit n,m

表示从第n条记录开始选择m条记录。对于小数据，使用LIMIT子句没有任何问题，当数据量非常大的时候，使用LIMIT n, m是非常低效的。因为LIMIT的机制是每次都是从头开始扫描，如果需要从第60万行开始，读取3条数据，就需要先扫描定位到60万行，然后再进行读取，而扫描的过程是一个非常低效的过程。

4、count(1)、count(*) 与 count(列名) 的区别

（1）count(1) and count(*)

        当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count()用时多了！
        从执行计划来看，count(1)和count()的效果是一样的。但是在表做过分析之后，count(1)会比count(*)的用时少些（1w以内数据量），不过差不了多少。

        如果count(1)是聚索引，id，那肯定是count(1)快，但是差的很小的。
        因为count()，自动会优化指定到那一个字段。所以没必要去count(1)，用count()，sql会帮你完成优化的，因此：count(1)和count(*)基本没有差别！

（2）count(1) and count(字段)

        count(1) 会统计表中的所有的记录数，包含字段为null 的记录。
        count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。

（3）执行效率：

• 列名为主键，count(列名)会比count(1)快。

• 列名不为主键，count(1)会比count(列名)快。

• 如果有主键，则 select count（主键）的执行效率是最优的。

5、 in 和 exists 的区别

（1）exists

        exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时（无论记录行是的多少，只要能返回），条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false。

select * from user where exists (select 1);

        对user表的记录逐条取出，由于子条件中的select 1永远能返回记录行，那么user表的所有记录都将被加入结果集，所以与select * from user;是一样的。

select * from user where exists (select * from user where user_id = 0);

        user表进行loop时，检查条件语句(select * from user where user_id = 0),由于user_id永远不为0，所以条件语句永远返回空集，条件永远为false，那么user表的所有记录都将被丢弃。

        如果A表有n条记录，那么exists查询就是将这n条记录逐条取出，然后判断n遍exists条件。

（2）in

        in查询就是先将子查询条件的记录全都查出来，假设结果集为B，共有m条记录，然后再将子查询条件的结果集分解成m个，再进行m次查询。

        in查询相当于多个or条件的叠加。

select * from user where user_id in (1, 2, 3);等效于select * from user where user_id = 1 or user_id = 2 or user_id = 3;

（3）性能对比

• IN查询在内部表和外部表上都可以使用到索引。

• Exists查询仅在内部表上可以使用到索引。

• 当子查询结果集很大，而外部表较小的时候，Exists的Block Nested Loop(Block 嵌套循环)的作用开始显现，并弥补外部表无法用到索引的缺陷，查询效率会优于IN。

• 当子查询结果集较小，而外部表很大的时候，Exists的Block嵌套循环优化效果不明显，IN 的外表索引优势占主要作用，此时IN的查询效率会优于Exists。

• 表的规模不是看内部表和外部表，而是外部表和子查询结果集。

欢迎大家关注我的专栏，该专栏会持续更新，从原理角度覆盖Java知识体系的方方面面。

一文吃透JAVA知识体系（面试题）https://blog.csdn.net/wuxinyan123/category_7521898.html?fromshare=blogcolumn&sharetype=blogcolumn&sharerId=7521898&sharerefer=PC&sharesource=wuxinyan123&sharefrom=from_link