count
count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值.
结论：count(*) > count(1) > count(字段)
单看这两个用法的差别的话，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
join
- BKA优化时MySQL内置支持的
- 如果能提前过滤出小数据，那么临时表方案是很好的
- hash join效果好于临时表
a 100、b 1000顺序对应，有a索引（树搜索）
判断能否使用join： explain 结果里面，Extra 字段里面没有出现“Block Nested Loop”字样->可用
NLJ (小表驱动 N + N2log2M) √
前提：可以使用被驱动表的索引
遍历表 t1，然后根据从表 t1 中取出的每行数据中的 a 值，去表 t2 中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引
驱动表：全表查询 被驱动表：树搜索
树搜索：普通索引一次、主键索引一次 查一行 2*log2M
SNL (硬算)
表b上没有可用索引 100*1000 = 10w行
顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，匹配成功则作为结果集的一部分返回。
BNL （内存硬算 确定sql一定会用到BNJ时，再优化 ）
表b上没有可用索引 100*1000 = 10w行
出现join_buffer（默认256k），将a表数据放内存再进行查询操作，所以相对变快
join_buffer作用是暂存驱动表的数据
- 在 join_buffer_size 足够大的时候，是一样的；
- 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。
- 扫描行数是 N+λNM；
- 内存判断 N*M 次。
BNL 算法对系统的影响主要包括三个方面：
- 可能会多次扫描被驱动表，占用磁盘 IO 资源；
- 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
- 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。
优化方式↓
被驱动表加索引->BNL变BKA
使用临时表的大致思路是：
- 把表 t2 中满足条件的数据放在临时表 tmp_t 中；
- 为了让 join 使用 BKA 算法，给临时表 tmp_t 的字段 b 加上索引；
- 让表 t1 和 tmp_t 做 join 操作
手动 hash join
比上一个方法快：
select * from t1;取得表 t1 的全部 1000 行数据，在业务端存入一个 hash 结构，比如 C++ 里的 set、PHP 的数组这样的数据结构。select * from t2 where b>=1 and b<=2000;获取表 t2 中满足条件的 2000 行数据。- 把这 2000 行数据，一行一行地取到业务端，到 hash 结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行。
BNL比SNL更好原因：
- 在对被驱动表做全表扫描的时候，如果数据没有在 Buffer Pool 中，就需要等待这部分数据从磁盘读入； 从磁盘读入数据到内存中，会影响正常业务的 Buffer Pool 命中率，而且这个算法天然会对被驱动表的数据做多次访问，更容易将这些数据页放到 Buffer Pool 的头部；
- 即使被驱动表数据都在内存中，每次查找“下一个记录的操作”，都是类似指针操作。而 join_buffer 中是数组，遍历的成本更低。
MRR （顺序读盘，需要先设置）
数量大，在索引a上做范围查询，id顺序
set optimizer_switch="mrr_cost_based=off"
↑使用后，用explain可以看到Using MRR
t1：1000行 a = 1001-id t2: 100w行
因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。
通过索引a的条件获得主键索引id放入mrr_cost_based后，先对主键索引进行排序。
BKA（join_buffer + 先设置）√
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
对NLJ算法的优化（NLJ:一次只传小表的一行，没有数据去做排序，MRR无法使用）： 引入join_buffer，将数据放入，排序
其他
低频SQL的判定（低频一般不推荐建立索引)
- 执行频率：每天只执行一次或更少
- 数据量：查询结果集大，但执行效率低
- 使用场景：如果重要，必须建
