Join优化目的！！！减少（驱动表或者被驱动表）循环/扫描，磁盘io就会少！
mysql只支持一种join算法：
Nested-Loop Join（嵌套循环连接）
Nested-Loop Join有三种变种：
Simple Nested-Loop Join
Index Nested-Loop Join
Block Nested-Loop Join
join性能
性能从高到低
Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join
1.Simple Nested-Loop Join：
如下图，r为驱动表，s为匹配表。
可以看到从r中分别取出r1、r2、......、rn去匹配s表的左右列。
然后再合并数据，对s表进行了rn次访问，对数据库开销大。
2.Index Nested-Loop Join（索引嵌套）：
这个要求非驱动表（匹配表s）上有索引，可以通过索引来减少比较，加速查询。
在查询时，驱动表（r）会根据关联字段的索引进行查找。
当在索引上找到符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表查询。
如果非驱动表（s）的关联键是主键的话，性能会非常高。
如果不是主键，要进行多次回表查询，先关联索引，然后根据二级索引的主键ID进行回表操作，性能上比索引是主键要慢（Innodb聚簇索引）。
3.Block Nested-Loop Join：
如果join列没有索引，就会采用Block Nested-Loop Join。
可以看到中间有个join buffer缓冲区，是将驱动表的所有join相关的列都先缓存到join buffer中。
然后批量与匹配表进行匹配，将第一种多次比较合并为一次，降低了非驱动表（s）的访问频率。
默认情况下join_buffer_size=256K。
在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。
在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。
如何使用join？
如果可以使用Index Nested-Loop Join算法（用上被驱动表上的索引）其实没有问题
如果使用Block Nested-Loop Join算法，尽量不要对大表进行join，这样可能会导致扫描行数过多，占用大量系统资源
在join的时候尽量选择小表做驱动表
在判断哪个表是小表的时候应该是按照两个表各自的条件过滤，过滤完成以后，计算参与join的各个字段的总数据量，数据量小的那个就是小表
JOIN总结
Index Nested-Loop Join和Block Nested-Loop Join都是在Simple Nested-Loop Join
的算法的基础上减少嵌套的循环次数；
不同的是Index Nested-Loop Join是通过索引的机制减少内层表的循环次数；
Block Nested-Loop Join 是通过一次缓存多条数据批量匹配的方式来减少外层表的循环次数（减少磁盘IO）
JOIN优化
永远用小结果集驱动大结果集(其本质就是减少外层循环（驱动表）的数据数量)
为匹配的条件增加索引(减少内层表（非驱动表）的循环次数)
增大join buffer size的大小（一次缓存的数据越多，那么外层表循环的次数就越少）
减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多，外层表的循环次数就越少）
JOIN缺点
1.增加cpu运算量，高并发情况下，cpu可能会变瓶颈
2.耦合度太高（同一个数据库），不适用于后期业务拆分，无法做到高性能和扩展
3.单表查询对应的结果对象容易缓存。如果关联中的某个表发生了变化，那么就无法使用查询缓存了；
而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了
4.增加冗余记录查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，
而在数据库中做关联查询，则可能需要重复地访问一部分数据，当没有where条件限制驱动表数据
优化建议
优先保证被驱动表的连接字段建立索引，因为建立索引的查询方式是效率最高的。
left join或者right join这种外连接的情况(改变mysql优化器的恋爱脑)，要保证小表（小结果集）作为驱动表，大表(大结果集)作为被驱动表，这样性能更好。
在查询字段的话，要避免select *或者select 全部字段，而是按需，因为这些字段也会加入到join buffer中。
能够直接多表关联的尽量直接关联，不用子查询，因为子查询的效率更加低。
在sql的查询计划的extra中，尽量避免出现Using join buffer，有这个表示使用了块嵌套循环连接算法，尽量通过索引去解决。
尽量避免超过3张表以上的关联查询。
常识
mysql优化器默认是用小结果集驱动大结果集,它好恋爱脑，不管怎样都以这种方式。
如果非要改变驱动表和被驱动表位置，可以用left join 、STRAIGHT_JOIN
解疑
为什么有时候大结果集驱动小结果 效率会比小结果集驱动大结果集好呢？
原因:被驱动表的索引是主键索引！！！效率比普通索引要高，所以在两个表
数据相差不大的时候，主键索引的高效查询就发挥出来了。
SHOW VARIABLES LIKE '%join_%';
优化ORDER BY=优化GROUP BY=优化DISTINCT
ORDER BY 实现原理：
1，通过有序索引而直接取得有序的数据；
2，通过MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回；
优化方案
临时文件在磁盘，产生更少的临时文件==减少磁盘的io
1，加大max_length_for_sort_data 参数；（目的为了少走双路排序）
2，去掉不必要的返回字段；（目的为了少走双路排序）
3，增大sort_buffer_size 参数；（目的为了产生更少的临时文件）
区别
1）双路排序会读取两次表， 且第二次是随机读取的
2）单路排序的元组比双路排序要长，导致它需要多次向临时文件写入内容，增加IO操作，当需要返回的列的总长度很长时尤其明显。
MySQL根据max_length_for_sort_data变量来确定使用哪种算法，默认值是1024字节，如果需要返回的列的总长度大于max_length_for_sort_data，使用第一种算法，否则使用第二种算法。
查看：show variables like 'max_sort_length';
单路排序效率高，但是如果查询的字段太长（所有字段长度>max_length_for_sort_data[1024]）
，sort_buffe放不了那么多数据,会导致走双路排序。
查看用了单路还是双路的命令
SET optimizer_trace="enabled=on";
SELECT * from t_order_info WHERE user_id=13000000004 ORDER BY create_date DESC;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
为什么加了索引就优化了order by？
select * from tb_user where age =18 order by crate_time desc;
给age加索引，索引本身有序（b+树)，经过回表之后就已经排好顺序了，
就不用走order by 的排序算法了。
欢迎小伙伴的点赞转发。
