MySQL连接查询中索引的重要性
在mysql中，我们要从多张表中读取数据时，往往需要用到连接查询。连接查询通过两张表中符合连接关系的字段来建立两张表的关联，通常包括内连接、左外连接、右外连接和全连接。内连接会保留两张表中共有的那部分记录，因此最后产生的连接表记录数最少；全连接会保留两张表中所有的记录，因此最后产生的连接表记录数最多；而左外连接会保留左表的全部记录，右外连接会保留右表的全部记录，因此最后产生的连接表记录数处于内连接和外连接之间。
下面来看一个例子：
两张表students和workmates，主键都为id，students有11行数据，workmates有3行数据。
左右连接时由于两个表的连接字段都为主键索引，所以左连接时右表rows=1，右连接时左表rows=1，内连接左表rows=1，我想应该是mysql优化了查询，来保证查询的rows最少，让数据量大的表作为内部表使用索引。
多查询一个b.level字段，由于该字段没有添加索引，左连接时右表的rows=3（右表的总行数），同时Using join buffer。
MySQL使用Join Buffer有以下要点:
1. join_buffer_size变量决定buffer大小。
2. 只有在join类型为all, index, range或者Index_merge的时候才可以使用join buffer。
3. 能够被buffer的每一个join都会分配一个buffer, 也就是说一个query最终可能会使用多个join buffer。
4. 第一个nonconst table不会分配join buffer, 即便其扫描类型是all或者index。
5. 在join之前就会分配join buffer, 在query执行完毕即释放。
6. join buffer中只会保存参与join的列, 并非整个数据行。
再说下两个join buffer算法：
NLJ (Nested Loop Join)算法:将驱动表/外部表的结果集作为循环基础数据，然后循环从该结果集每次一条获取数据作为下一个表的过滤条件查询数据，然后合并结果。如果有多表join，则将前面的表的结果集作为循环数据，取到每行再到联接的下一个表中循环匹配，获取结果集返回给客户端。
BNL (Block Nested-Loop Join)算法:将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。举例来说，外层循环的结果集是100行，使用NLJ 算法需要扫描内部表100次，如果使用BNL算法，先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer,然后在InnerLoop表(内部表)中直接匹配这10行数据，内存循环就可以一次与这10行进行比较, 这样只需要比较10次，对内部表的扫描减少了9/10。所以BNL算法就能够显著减少内层循环表扫描的次数。
BNL算法要比BLJ算法高效很多，优化器选择 join方式的时候默认选择BNL算法。如果要使用NLJ算法，将优化器管理参数optimizer_switch中的block_nested_loop修改为off。
总结：
1.查询字段和连接字段最好都加上索引！
2.数据库多表联查时，左连接保证右表字段索引，右连接保证左表字段索引，内连接保证任一表连接字段索引。