为了便于分析执行过程中的性能问题，可以使用straight_join来让MySQL使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去join，比如下面这条语句：
select * from t1 straight_join t2 on (t1.a=t2.a);那么t1就是驱动表，t2就是被驱动表
其中t1有100行数据，t2有1000行数据，两个表中都是id,a,b三个字段，其中id和a字段均有索引。
以上面SQL为例，这个查询过程跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以称为"Index Nested-Loop Join"，简称NLJ。
这里就有两个问题：
1、可以使用join吗？
如果不使用join，那么就需要单表查询t1，然后遍历t1，再去查询t2，虽然跟直接join相比扫描的行数是一样，但是执行的语句比直接使用join多，而且客户端还要组装数据，那么显示是直接使用join更好。
2、怎么选择驱动表
在这个SQL中，通过explain可以发现，驱动表走的是全表扫描，被驱动表走的是树搜索。
假设被驱动表的行数是M，每次在被驱动表查一行数据，要先搜索索引a，再搜索主键索引。每次搜索一棵树近似复杂度是以2为底的M的底数，记为log2M，所以在被驱动表上查一行的时间复杂度是2*log2M。
假设驱动表的行数是N，执行过程就要扫描驱动表N行，然后对于每一行，到被驱动表上匹配一次。
因此整个执行过程中，近似复杂度是N+N*2*log2M。显然，N对扫描行数的影响更大，因此应该让小表来做驱动表。
结论：
1、使用join语句，性能比强行拆成多个单表执行SQL语句的性能更好
2、如果使用join语句的话，需要让小表做驱动表
但是，需要主要，这个结论的前提是可以使用被驱动表的索引。
这种类型的join就是被驱动表用不上索引。
比如，将上面的SQL改成这样：
select * from t1 straight_join t2 on (t1.a=t2.b);因为t2的字段b上没有索引，因此每次到t2去匹配的时候，都要做一次全表扫描。
显然，这个算法太笨重了，MySQL没有使用这个算法，而是使用了下面这种算法
算法流程如下：
1、把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存
2、扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回
在这个过程中，对表t1和t2都做了一次全表扫描，因此总的扫描行数是1100。由于join_buffer是以无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共需要在内存中做的判断次数是：100*1000=10万次。
这个扫描行数和上一个算法是一样的，但是，BNJ算法的这10万行次判断是内存操作，速度上会快很多，性能也更好。
在这种情况下，应该选择哪个表做驱动表呢？
假设小表的行数是N，大表的行数是M，那么在这个算法里：
1、两个表都做一次全表扫描，所以总的扫描行数是M+N
2、内存中的判断次数是M*N
可以发现，调换M和N的顺序没什么差别，所以这时候选择大表还是小表做驱动表，执行耗时是一样的。
但是要注意，这里的表t1才100行，如果t1是一个大表，join_buffer放不下怎么办呢？
Join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。如果放不下表t1的所有数据的话，策略很简单，就是分段放。这也是该算法名字中"Block"的由来，表示"分块去join"。
这种情况下驱动表该如何选择呢？这里说下结论：
还是应该让小表当驱动表
并且join_buffer_size越大，一次可以放入的行越多，分成的段数也就越少，对被驱动表的全表扫描次数就越少。
所以有的建议说，如果你的join语句很慢，就把join_buffer_size改大。
1、能不能使用join语句？
- 如果可以使用NLJ算法，也就是说可以用上被驱动表的索引，其实是没问题的
- 如果使用BNJ算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表多次，占用大量系统资源，所以这种join尽量不要用。
所以在判断要不要使用join语句时，就是看explain结果里，Extra字段里面有没有出现"Block Nested Loop"字样。
2、如果要使用join，应该选择大表做驱动表还是小表做驱动表？
- 如果是NLJ算法，选择小表做驱动表
- 如果是BNJ算法：
- 在
join_buffer_size足够大的时候，是一样的 - 在
join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表
- 在
所以，结论就是总是应该使用小表做驱动表。
这里需要说明下，什么叫做小表。
比如下面这两条语句：
select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;
select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;都没用上索引，但第二个语句，join_buffer只需要放入t2的前50行，所以这里，"t2的前50行"就是那个相对小的表，也就是"小表"。
再来看另外一个例子：
select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100;
select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;都是只有100行参加join，但是两条语句每次查询放入join_buffer中的数据是不一样的
- 表t1只查字段b，因此把t1放到join_buffer中，join_buffer只需放入b的值
- 表t2需要查所有的字段，因此如果把t2放到join_buffer的话，就需要放入三个字段id，a和b
这里应该选择t1作为驱动表，在这个例子里，"只需要一列参与join的表t1"是那个相对小的表。
所以，更准确的说，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是"小表"，应该作为驱动表。
即MRR，这个优化的主要目的是尽量使用顺序读盘。
还记得“回表”这个概念吗？即在普通索引上查到主键值后，再根据一个个主键的值到主键索引上去查整行数据的过程。
那回表过程是一行行地查数据，还是批量地查数据？
比如下面这条语句：
select * from t1 where a>=1 and a<=100;主键索引是一棵B+树，在这棵树上，每次只能根据一个主键ID查到一行数据。因此，回表肯定是一行行搜索主键索引的。
因为大多数数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。这也是MRR优化的思路。
MRR能够提升性能的核心在于，上面这条查询语句在索引a上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键ID。这样通过排序以后，再去主键索引查数据，才能体现出"顺序性"的优势。
BKA算法，其实就是对NLJ算法的优化。
NLJ算法执行的逻辑是：从驱动表t1，一行行地取出a的值，再到被驱动表t2去做join。也就是说，对于表t2来说，每次都是匹配一个值。这时，MRR的优势就用不上了。
那怎么才能一次性地多传些值给表t2呢？方法就是，从表t1里一次性地多拿些出来，一起传给表t2。
既然如此，我们就把表t1的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是join_buffer。
如果要使用BKA优化算法的话，需要在执行SQL语句之前，设置：
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';前两个参数的作用是启用MRR，这么做的原因是BKA算法要依赖于MRR。
前面我们说过BNL算法，它会对系统造成下面三个方面的影响：
1、可能会多次扫描被驱动表，占用磁盘IO资源
2、判断join条件需要执行M*N次对比（M、N分别是两张表的行数），如果是大表就会占用非常多的CPU资源
3、可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。
所以我们在执行语句之前，需要通过分析和查看explain结果的方式，确认是否要使用BNL算法，如果确认优化器会使用BNL算法，就需要做优化。优化的常见做法是，给被驱动表的join字段加上索引，把BNL算法转成BKA算法。
一些情况下，可以直接在被驱动表上建索引，这时就可以直接转成BKA了。
但是，如果是一个低频的SQL语句，那么再为这个语句创建索引就很浪费了。
那有没有两全其美的办法呢？
这时可以考虑使用临时表，比如下面这条SQL：
select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;t1表有1000条数据，t2表有100万数据。
思路是：
1、把表t2中满足条件的数据放在tmp_t中
2、为了让join使用BKA算法，给临时表tmp_t的字段b加上索引
3、让表t1和tmp_t做join操作
对应的SQL写法如下：
create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);总体来看，不论是在原表上加索引，还是用有索引的临时表，思路都是让join语句能够用上被驱动表的索引，来触发BKA算法，提升查询性能。
其实上面那个语句，会进行1000*100万=10亿次操作，如果join_buffer里面维护的不是一个无序数组，而是一个哈希表的话，那么就不是10亿次判断，而是100万次hash查找。但是MySQL不支持哈希join。但是我们可以在业务端实现：
1、select * from t1，取得表t1的全部1000行数据，在业务端存入hash结构
2、select * from t2 where b>=1 and b<=2000，获取表t2中满足条件的2000行数据
3、把这2000行数据，一行一行地取到业务端，到hash结构的数据表中寻找匹配的数据，满足匹配的条件的这行数据，就作为结果集的一行
理论上，这个过程会比临时表方案的执行速度要快一些。
NLJ的优化就是MRR算法，而BNL的优化就是BKA算法。
在这些优化方法中：
1、BKA优化是MySQL已经内置支持的，建议默认嗨哟个
2、BNL算法效率低，建议转成BKA算法。优化方向就是给被驱动表的关联字段加上索引
3、基于临时表的改进方案，对于能够提前过滤出小数据的join语句来说，效果还不错
4、由于MySQL还不支持hash join，所以可以配合应用端，理论上效果好于临时表方案。
临时表分为内存临时表和磁盘临时表，如果内存临时表放不下，就会使用磁盘临时表。
现在有一个表t1，id是主键，a是索引，还有一个字段b，id从1000开始逆序存储
执行下面这条语句：
(select 1000 as f) union (select id from t1 order by id desc limit 2);这条语句用到了union，它的语义是，取这两个子查询结果的并集。并集的意思就是这两个集合加起来，重复的行只保留一行。
这个语句的执行流程如下：
1、创建一个内存临时表，这个临时表只有一个整型字段f，并且f是主键字段
2、执行第一个子查询，得到1000这个值，并存入临时表中
3、执行第二个子查询
- 拿到第一行id=1000，试图插入临时表中。但由于1000已经存在，违反唯一性约束，所以插入失败，然后继续执行
- 取第二行id=999，插入临时表成功
4、从临时表中按行取出数据，返回结果，并删除临时表，结果表中包含两行数据分别是1000和999。
所以这里的临时表起到了暂存数据的作用，而且计算过程还用上了临时表主键ID的唯一性约束，实现了union的语义。
顺便提一下，如果把union改成union all，就没有了去重的语义。这样执行的时候，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端，也就不需要临时表了。
1、如果对group by语句的结果没有排序要求，要在语句后面加上order by null，不是直接去掉order by，而是要显示加上order by null，这是规定。
注：在mysql8.0开始，不需要显示添加order by null了，因为从这个版本开始，group by会进行隐式排序的问题已经被抛弃了。所以具体得看当前所使用的数据库版本
2、尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary和 Using filesort；
3、如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size参数，来避免用到磁盘临时表；
4、如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法 得到group by的结果。因为你如果一个group by语句中需要放到临时表上的数据量特别大，却还是要按照"先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表"，看上去就有点儿傻。
1、删除数据的时候尽量加limit，这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。具体见锁这章。
2、读提交隔离级别下，锁的范围更小，锁的时间更短，因为在执行过程加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放，不需要等到事务提交。
3、现在在MySQL中有一张表table，有四百多万数据。
执行下面这个语句：
select * from table limit 2000000,2000时3秒
而执行
select a.* from table a,(select id from table limit 2000000,2000) b where a.id=b.id只耗时了0.3秒，为什么呢？
这是因为第二个查询语句使用了子查询，而第一个查询语句没有。
在第一个查询语句中，MySQL必须检索整个表中的所有记录，然后跳过前2000000个记录，并返回接下来的2000个记录。这需要MySQL读取和处理所有记录，包括前2000000个记录，这是一个昂贵的操作，因此需要更长的时间。
而在第二个查询语句中，子查询先执行，它只返回所需的2000个id。MySQL只需要在主查询中查找与这些id匹配的记录，这个操作更加快速和有效，因为MySQL只需要读取和处理少量的记录。
因此，使用子查询可以使查询更加有效，特别是当要处理大型表时。