MySQL优化系列（二）--查找优化（2）（外连接、多表联合查询以及查询注意点）

继续这一系列，上篇的简单查询优化并没讲完，第二点还有查询注意点以及多表查询优化呢！！

文章结构：（1）多表查询步步优化；（2）查询编写的注意点。

/*
	补充说明（往后有博友反馈的话，会继续补充。）
*/

一、2017-7-19：
关于“超大型数据尽可能尽力不要写子查询，使用连接（JOIN）去替换它”。
关于这一类的总结，我们要认真去考量，这里并不是说一定。
1）因为在大型的数据处理中，子查询是非常常见的，特别是在查询出来的数据需要进一步处理的情况，
无论是可读性还是效率上，这时候的子查都是更优。
2）然而在一些特定的场景，可以直接从数据库读取就可以的，
比如一个表（A表 a，b，c字段，需要内部数据交集）join自己的效率必然比放一个子查在where中快得多。
可参见我给的例子中的拉黑表，好友表（双方互相喜欢才是好友的表），在查自己的好友列表的时候，或者拉黑列表中。

本系列：demo下载

（一）MySQL优化笔记（一）–库与表基本操作以及数据增删改

（二）MySQL优化笔记（二）–查找优化（1）（非索引设计）

（三）MySQL优化笔记（二）–查找优化（2）（外连接、多表联合查询以及查询注意点）

（四） MySQL优化笔记（三）–索引的使用、原理和设计优化

（五） MySQL优化笔记（四）–表的设计与优化（单表、多表）

（六）MySQL优化笔记（五）–数据库存储引擎

---

文章目录：

（1）多表查询步步优化

• 基本连接方法（内连接、外连接以及交叉连接）
  - 内连接：用比较运算符根据每个表共有的列的值匹配两个表中的行（=或>、<）
  - 外连接之左连接
  - 外连接之右连接
  - 外连接之全外连接
  - 交叉连接

• 超大型数据尽可能尽力不要写子查询，使用连接（JOIN）去替换它（基础讲完，讲优化）

• （3）使用联合(UNION)来代替手动创建的临时表

• 建立索引（下一篇将详讲）

（2）查询编写的注意点

大概有9点，详情见下文。

---

一、多表查询步步优化：（使用还是之前的数据库的表，商品分类表以及商品详情表）

给出的数据库有基本的数据框架，剩下的几个假数据我们就自己创建吧。注意此两表是有外键约束的。

（1）基本连接方法（内连接、外连接以及交叉连接）：

一）内连接：用比较运算符根据每个表共有的列的值匹配两个表中的行（=或>、<）

//意思是：检索商品分类表和商品表“分类描述”相同的行
select
        d.Good_ID ,
        d.Classify_ID,
        d.Good_Name
        from
        Commodity_list d
        inner join commodity_classification c
        on d.Classify_Description=c.Good_kinds_Name
             

得到的满足某一条件的是A，B内部的数据；正因为得到的是内部共有数据，所以连接方式称为内连接。

很容器看出是两者都满足才查出

二）外连接之左连接

//意思：查得商品分类表的所有数据，以及满足条件的商品详情表的数据
select
        *
        from
        commodity_classification c
        left join commodity_list d
        on d.Classify_Description=c.Good_kinds_Name

可以看到，首先是左表数据全部罗列，然后有满足条件的右表数据都会全部罗列出。若两条右表数据对左表一条数据，则会用对应好的左表数据补足作为一条记录。

左连接升级：

[left join 或者left outer join(等同于left join)] + [where B.column is null]

//就是只查分类表数据，但是减去跟商品详情表有联系的数据。
select
        *
        from
        commodity_classification c
        left join commodity_list d
        on d.Classify_Description=c.Good_kinds_Name
        where d.Classify_Description is null
       

三）外连接之右连接

//意思是查得商品详情表的所有数据以及在分类描述相同条件下的商品分类表数据
select
        *
        from
        commodity_classification c
        right join commodity_list d
        on d.Classify_Description=c.Good_kinds_Name

与左连恰恰相反，首先是右表数据全部罗列，然后有满足条件的左表数据都会全部罗列出。若两条左表数据对右表一条数据，则会用对应好的右表数据补足作为一条记录。

右连接升级：

//意思：查询商品详情表的所有数据，但是要减去和商品分类表有联系的数据
select
        *
        from
        commodity_classification c
        right join commodity_list d
        on d.Classify_Description=c.Good_kinds_Name
        where c.Good_kinds_Name is null  
       

四）外连接之全外连接：

full join （mysql不支持，但是可以用 left join union right join代替）

select
        *
        from
        commodity_classification c
        left join commodity_list d
        on d.Classify_Description=c.Good_kinds_Name
        union
select
        *
        from
        commodity_classification c
        right join commodity_list d
        on d.Classify_Description=c.Good_kinds_Name

这种场景下得到的是满足某一条件的公共记录，和独有的记录

全外连接升级：

select
        *
        from
        commodity_classification c
        left join commodity_list d
        on d.Classify_Description=c.Good_kinds_Name
        where d.Classify_Description is null
        union
select
        *
        from
        commodity_classification c
        right join commodity_list d
        on d.Classify_Description=c.Good_kinds_Name
         where c.Good_kinds_Name is null

这种场景下得到的是A，B中不满足某一条件的记录之和.

全部一起列出：消除重复项

五）交叉连接：

交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。 FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。

有两种情况，显式的和隐式的，不带ON子句，返回的是两表的乘积，也叫笛卡尔积。

1. 第一种方式（显式的交叉连接）：A，B表记录的排列组合，即笛卡儿积。

//可以看到
select
        *
        from
        commodity_classification c
       cross join commodity_list d 

补充：cross join可指定条件 （where）

select
        *
        from
        commodity_classification c
       cross join commodity_list d 
       where c.Good_kinds_Name=d.Classify_Description

相当于实现内连接功能了。

2. 第二种方式：（隐式的交叉连接，没有CROSS JOIN）

就跟正上面的效果一样的语句啦！

select
        *
        from
        commodity_classification c,
        commodity_list d 
       where c.Good_kinds_Name=d.Classify_Description

交叉连接存在的问题遗漏： 参考此博主此博文

mysql对sql语句的容错问题，即在sql语句不完全符合书写建议的情况，mysql会允许这种情况，尽可能解释它：

1)一般cross join后面加上where条件，但是用cross join+on也是被解释为cross join+where；
2)一般内连接都需要加上on限定条件，如上面场景一；如果不加会被解释为交叉连接；
3)如果连接表格使用的是逗号，会被解释为交叉连接；

注：sql标准中还有union join和natural inner join，mysql不支持，而且本身也没有多大意义，其结果可以用上面的几种连接方式得到。

---

（2）超大型数据尽可能尽力不要写子查询，使用连接（JOIN）去替换它（基础讲完，讲优化）：

子查询：

定义：

在一个表表达中可以调用另一个表表达式，这个被调用的表表达式叫做子查询（subquery），也称作子选择（subselect）或内嵌选择（inner select）。子查询的结果传递给调用它的表表达式继续处理。

//很简单的意思：就是根据商品id查商品详情表，然后用查出来的ID去查商品分类表。
select
       *
       from
       commodity_classification c  
       where Classify_ID  IN(select Classify_ID from commodity_list where Good_ID='tb10025584930')

使用JOIN进行优化：

select
       *
       from
       commodity_classification c  
       left join commodity_list d on d.Classify_ID=c.Classify_ID
       where d.Good_ID='tb10025584930'
       

（3）使用联合(UNION)来代替手动创建的临时表

UNION是会把结果排序的！！！

union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中（即把两次或多次查询结果合并起来。）。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。

要求：

两次查询的列数必须一致

推荐：

列的类型可以不一样，但推荐查询的每一列，想对应的类型以一样

可以来自多张表的数据：

多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准。

如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么union会将相同的行合并，最终只保留一行。也可以这样理解，union会去掉重复的行。

如果不想去掉重复的行，可以使用union all。

如果子句中有order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选。

select
       Classify_ID,Good_kinds_Name
       from
       commodity_classification 
       union
select 
		Classify_ID,Classify_Description
		from 
		commodity_list

//加条件
(select
       Classify_ID,Good_kinds_Name
       from
       commodity_classification 
       order by Classify_ID)
       union
(select 
		Classify_ID,Classify_Description
		from 
		commodity_list
		order by Good_ID)
 
//经常操作的含义：列出所有在中国和美国的不同的雇员名
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
       

注意：

1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名

2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同

UNION ALL的作用和语法：

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。

select
       Classify_ID,Good_kinds_Name
       from
       commodity_classification 
       union ALL
select 
		Classify_ID,Classify_Description
		from 
		commodity_list

不删除重复数据

---

（4）建立索引（下一篇将详讲）

---

二、查询编写的注意点：

（1）对查询进行优化，要尽量避免全表扫描

首先应考虑在 where 及 order by 涉及的列上建立索引。（索引的注意点在下篇将详讲）

（2）应尽量避免在 where 子句中对字段进行 null 值判断

否则将导致引擎放弃使用索引而进行全表扫描，如：

//最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
select id from t where num is null

备注、描述、评论之类的可以设置为 NULL，其他最好不要使用NULL。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num = 0

（3）in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

//用这个去替换
select num from a where exists(select 1 from b where num=a.num)

（4）下面的查询也将导致全表扫描：

select id from t where name like ‘%abc%’

若要提高效率，可以考虑全文检索。

（5）尽量使用数字型字段

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

（6）任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

（7）尽量使用表变量来代替临时表。

如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

（8）在Join表的时候使用相当类型的例，并将其索引

如果你的应用程序有很多 JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。

而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）

//在state中查找company
SELECT company_name FROM users
    LEFT JOIN companies ON (users.state = companies.state)
    WHERE users.id = $user_id"
    //两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集

（9）查询索引中的sql语句有很多讲究，在下篇文章我们将详细讨论。

---

源码下载：数据库文件下载

好了，MySQL优化系列（二）–查找优化（2）（外连接、多表联合查询以及查询注意点）讲完了

下一篇就是我们的索引详讲了，很难又很重要关键，性能的关键点，我会继续出这个系列文章，分享经验给大家。欢迎在下面指出错误，共同学习！！你的点赞是对我最好的支持！！

更多内容，可以访问JackFrost的博客