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

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

一、多表查询步步优化
（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

二）外连接之左连接

//意思：查得商品分类表的所有数据，以及满足条件的商品详情表的数据
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

三）外连接之右连接
与左连接相反
四）外连接之全外连接：
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限定条件，如上面场景一；如果不加会被解释为交叉连接；
（2）超大型数据尽可能尽力不要写子查询，使用连接（JOIN）去替换它
（3）使用联合(UNION)来代替手动创建的临时表
UNION是会把结果排序的！！！
union会去掉重复的行。
如果不想去掉重复的行，可以使用union all。
如果子句中有order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选
注意：
1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名
2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同
（4）建立索引
二、查询编写的注意点：
（1）对查询进行优化，要尽量避免全表扫描。
首先应考虑在where及order by涉及的列上建立索引
（2）应尽量避免在where子句中对字段进行null值判断
否则将导致引擎放弃使用索引而进行全表扫描
最好不要给数据库流null，尽可能 使用not null填充数据库
备注、描述、评论之类的可以设置为null,其他最好不要使用null
可以再num上设置默认值为0，确保表中num列没有null值，然后这样查询：
select id from t hwere num =0
(3)in和not in 也要慎用，否则会导致全表扫描
对于连续的数值，能用between就不要用in了
很多时候exists代替in是一个好的选择
（4）like ‘%abd%’ 的查询也会导致全表扫描。若想提高效率可以考虑全文检索
(5)尽量使用数字型字段
若只含数值信息的字段尽量不要涉及为字符型，这回降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
（6）任何地方都不要使用 select * from t，用具体的字段列表代替*，不要返回用不到的任何字段。
（7）尽量使用变量来代替临时表。如果表变量包含大量数据，清注意索引非常有限（只有主键索引）
（8）在join表的时候使用相当类型的列，并将其索引。
如果你的应用程序有很多join查询，你应该确认两个表中join的字段是被建过索引的。这样，mysql内部会启动为你优化join的sql语句的机制。
而且，这些被用来join的字段，应该是相同的类型。例如：你要把decimal字段和一个int字段join在一起，mysql就无法使用它们的索引。而对于那些string类型，还需要有相同的字符集才行（两个表的字符集可能不一样）
（9）查询索引中的sql语句有很多讲究