查询原理篇
上篇文章MySQL索引篇熟悉了MySQL的索引，这篇文章来熟悉查询相关的原理
连接原理
selecte * from t1,t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd'
先在驱动表t1上使用二级索引或者聚簇索引根据t1.m1 > 1寻找符合的记录（如图中2号位置），再根据这条记录 t1.m1 = 2 去匹配另一个查询条件 2 = t2.m2 ，然后再匹配其他查询条件再被驱动表t2上查找，找到还可能需要回表查询所有记录，然后再从驱动表上寻找下一条记录
访问一次驱动表，根据驱动表的结果集来访问多次被驱动表
因此驱动表结果集越小访问被驱动表的次数就越少，因此最好小表驱动大表
这种嵌套循环连接的方式时间复杂度是指数级别的，如果联多个表时间开销会指数级增长
由于访问多次被驱动表，在联表查询时可以适当的在被驱动表上建立索引来加快连接
联表查询中使用索引的情况下，可能索引列不满足查询列表，因此会回表，这种情况下回表会产生随机IO，innodb会通过MRR缓冲排序主键值再回表，以此来避免随机IO
在嵌套循环连接且无法使用索引的情况下，时间开销会很大，innodb使用基于块的嵌套循环连接来优化：申请一块join buffer，将驱动表记录放在join buffer中，再访问被驱动表时就能够用join buffer中的记录与被驱动表记录对比，join buffer够大的情况下，完全可以只访问一次被驱动表
join buffer中存储查询列表和搜索条件，因此查询列表不要用*，在无法使用索引的情况下也可以适当增大join buffer
查询优化
优化器会对SQL的查询条件进行优化，比如移除永远为true或false的表达式、移除一些不必要的括号等
查询排序
在使用order by、group by的情况下会对某个列进行排序，如果这个列是索引列，那么使用该索引查询时，它天然就是有序的；但如果这个列不是索引列，则可能先使用二级索引或者聚簇索引查出结果集，在将这些结果放在sort buffer中对要排序的列进行排序。
对于内存充足，查询量不大的情况下使用全字段排序，sort buffer中存在完整的记录，再对需要排序的列排序后直接返回（图中对name排序，先使用city二级索引查询再排序）
对于内存不足，查询量大的情况下使用rowid排序，sort buffer中只存需要排序的列和主键值，排序好后再通过回表的方式获取完整记录
外连接消除
外连接与内连接的区别就是内连接的驱动表与被驱动表可以由优化器来选择最优的结果，而外连接由编写SQL来指定
外连接使用on来针对驱动表记录没在被驱动表中匹配时将被驱动表中的值填充为NULL加入结果集，因此只要在where条件中隐式的指定被驱动表其他列不能为NULL，就不能加入结果集，这样就能够消除外连接与内连接区别，从而让优化器来选择最优的驱动表与被驱动表
in子查询半连接优化
in 子查询半连接优化：在一定条件下会将子查询物化成一个临时表（数据量小在内存中使用哈希索引；数据量大在磁盘中使用B+树索引），由临时表与外部查询的表进行内连接，并通过一些措施防止取重复值（唯一索引保证不重复、去重等）
总结
驱动表访问一次，被驱动表访问多次，适当为被驱动表建立索引；不使用索引加快的情况下，会使用join buffer，适当增大join buffer；不要在查询列表中使用*，可能导致不使用覆盖索引、占用join buffer空间等
查询中使用order by或group by时会排序，尽量为要排序的列建立索引，否则还要使用临时表sort buffer去排序；数据量小时使用全字段排序，数据量大的情况下使用rowid排序，排序完还要进行回表查询完整记录
联表查询使用外连接时，如果能够隐式的让被驱动表其他列不为null就可以触发外连接消除，由优化器来选择最优驱动表与被驱动表
in子查询使用半连接优化，一定条件下将子查询物化为临时表，根据防重复措施来与外部查询进行内连接
