MySQL7种JOIN连接

1. MySQL逻辑架构简介

MySQL的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离，这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.1. 连接层

最上层是一些客户端和连接服务，例如连接处理，身份验证，安全性等。

1.2. 服务层

MySQL核心部分，主要完成权限判断，查询缓存，SQL解析及优化等功能。

1.3. 引擎层

存储引擎层负责MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信；不同的存储引擎具有不同的功能特性。

1.4. 存储层

数据存储层主要是将数据存储在运行于裸系统的文件系统上，并完成与存储引擎的交互。

补充文章

2. SQL语句执行流程

3. MyISAM和InnoDB

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即操作一条记录就锁住整个表，不适合高并发的操作	行锁，即操作时只锁某一行，不对其他行有影响 适合高并发的操作
缓存	只缓存索引，不缓存真实数据(时间换空间)	不仅缓存索引还缓存真实数据，对内存要求较高，且内存大小对性能有影响(空间换时间)
表空间	小	大
关注点	性能	事务

4. SQL性能下降的原因

4.1 查询语句没写好

避免select *全表扫描

4.2 索引失效

建立好索引，且保证索引不失效，或为热点字段建立索引

• 单值索引：create index idx_user_name on user(name);
• 复合索引：create index idx_user_nameEmail on user(name,email);

4.3 关联查询太多join

设计缺陷或不得已的必要需求

4.4 服务器调优及参数设置

缓冲，线程数等。

5. Join连接

完整的SQL Join连接图示如下：

5.1 INNER JOIN(内连接)

• 内连接查询返回表A和表B中所有匹配行的结果，示例如下：

SELECT <select_list>
FROM Table_A a
INNER JOIN Table_B b
ON a.key = b.key

5.2 LEFT JOIN(左连接)

• 左连接查询返回所有表A中的记录，不管是否有匹配记录在表B中(匹配不到的标记为null)，示例如下：

SELECT <select_list>
FROM Table_A a
LEFT JOIN Table_B b
on a.key = b.key

5.3 RIGHT JOIN(右连接)

• 和左连接相反，右连接查询会返回所有表B中的记录，不管是否有匹配记录在表A中(无匹配标记null)，示例如下：

SELECT <select_list>
FROM Table_A a
RIGHT JOIN Table_B b
on a.key = b.key

5.4 LEFT Excluding JOIN

• 左排除右连接，它会返回表A中所有不在表B中的行(返回表A独有部分)，示例：

SELECT <select_list>
FROM Table_A a
LEFT JOIN Table_B b
on a.key = b.key
WHERE b.key IS NULL

5.5 RIGHT Excluding JOIN

• 右排除左连接，它会返回表B中所有不在表A中的行，示例：

SELECT <select_list>
FROM Table_A a
RIGHT JOIN Table_B b
on a.key = b.key
WHERE a.key IS NULL

5.6 OUTER Excluding JOIN

• 外连接排除内连接结果，它会返回所有表A和表B中没有匹配的行(返回各自独有部分)，示例：

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

5.7 FULL OUTER JOIN

• 全连接，返回表A和表B中的所有行(匹配不到标记null)，示例如下：

SELECT <select_list>
FROM Table_A a
FULL OUTER JOIN Table_B b
on a.key = b.key

注意：MySQL天生不支持全连接，你可以使用UNION函数连接两个查询(左连接+右连接)来实现全连接。

UNION函数用于合并两个或多个SELECT语句的结果集并去重。

参考文章

5.8 常规SQL查询语句模板

SELECT DISTINCT
    <select_list>
FROM
    <left_table> <join_type>
JOIN <right_table> on <join_condition>
WHERE
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>
LIMIT <limit number>