二十一、为什么还有kill不掉的语句？
在 MySQL 中有两个 kill 命令：一个是 kill query + 线程 id，表示终止这个线程中正在执行的语句；一个是 kill connection + 线程 id，这里 connection 可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。
使用了 kill 命令，却没能断开这个连接。再执行 show processlist 命令，看到这条语句的 Command 列显示的是Killed。
实现上，当用户执行 kill query thread_id 时，MySQL 里处理 kill 命令的线程做了两件事：
- 把 session B 的运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为THD::KILL_QUERY)；
- 给 session B 的执行线程发一个信号。。
信号的作用：
- 一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态，如果发现线程状态是 THD::KILL_QUERY，才开始进入语句终止逻辑；
- 如果处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到“埋点”处；
- 语句从开始进入终止逻辑，到终止逻辑完全完成，是有一个过程的。
kill connection本质上只是把客户端的sql连接断开，后面的执行流程还是要走kill query的。kill connection会在 是show processlist 显示“killed
二十二、查了太多数据，会不会把数据库内存打爆？
全表扫描对 server 层的影响
服务端在全表扫描的时候并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：
- 获取一行，写到
net_buffer中。这块内存的大小是由参数net_buffer_length定义的，默认是 16k。 - 重复获取行，直到
net_buffer写满，调用网络接口发出去。 - 如果发送成功，就清空
net_buffer，然后继续取下一行，并写入net_buffer。 - 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socketsend buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。
从这个流程中，可以看到：
- 一个查询在发送过程中，占用的 MySQL 内部的内存最大就是
net_buffer_length这么大，并不会达到 200G； socket send buffer也不可能达到 200G（默认定义/proc/sys/net/core/wmem_default），如果socket send buffer被写满，就会暂停读数据的流程。
全表扫描对 InnoDB 的影响
WAL 机制的作用是保存更新的结果，再配合 redo log，就避免了随机写盘。内存的数据页是在 Buffer Pool (BP) 中管理的，在 WAL 里 Buffer Pool 起到了加速更新的作用。而实际上，Buffer Pool 还有一个更重要的作用，就是加速查询。
由于有 WAL 机制，当事务提交的时候，磁盘上的数据页是旧的，那如果这时候马上有一个查询要来读这个数据页，是不是要马上把 redo log应用到数据页呢？答案是不需要。因为这时候内存数据页的结果是最新的，直接读内存页就可以了。
而 Buffer Pool 对查询的加速效果，依赖于一个重要的指标，即内存命中率。
show engine innodb status可以看到“Buffer pool hit rate”字样，显示的就是当前的命中率。
InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的，一般建议设置成可用物理内存的 60%~80%。
对于 InnoDB 引擎内部，由于有淘汰策略(LRU)，大查询也不会导致内存暴涨。并且，由于InnoDB 对 LRU 算法做了改进，冷数据的全表扫描，对 Buffer Pool 的影响也能做到可控。
二十三、到底可不可以使用join？
Index Nested-Loop Join
select * from t1 straight_join t2 on (t1.a=t2.a);
如果直接使用 join 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表，这样会影响我们分析 SQL 语句的执行过程。所以，为了便于分析执行过程中的性能问题，我改用straight_join 让 MySQL 使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去 join。在这个语句里，t1 是驱动表，t2 是被驱动表。
可以看到，在这条语句里，被驱动表 t2 的字段 a 上有索引，join 过程用上了这个索引，因此这个语句的执行流程是这样的：
- 从表 t1 中读入一行数据 R；
- 从数据行 R 中，取出 a 字段到表 t2 里去查找；
- 取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；
- 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。
这个过程是先遍历表 t1，然后根据从表 t1 中取出的每行数据中的 a 值，去表 t2 中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称 NLJ。
在这个流程里：
- 对驱动表 t1 做了全表扫描，这个过程需要扫描 100 行；
- 而对于每一行 R，根据 a 字段去表 t2 查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描 100 行；
- 所以，整个执行流程，总扫描行数是 200。
怎么选择驱动表？
在这个 join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。假设被驱动表的行数是 M。每次在被驱动表查一行数据，要先搜索索引 a，再搜索主键索引。每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 logM，所以在被驱动表上查一行的时间复杂度是 2*logM。假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次。因此整个执行过程，近似复杂度是 N + N*2*logM。
显然，N 对扫描行数的影响更大，因此应该让小表来做驱动表。
通过上面的分析得到了两个结论：
- 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；
- 如果使用 join 语句的话，需要让小表做驱动表
Simple Nested-Loop Join
select * from t1 straight_join t2 on (t1.a=t2.b);
由于表 t2 的字段 b 上没有索引，因此再用图 2 的执行流程时，每次到 t2 去匹配的时候，就要做一次全表扫描。这样算来，这个 SQL 请求就要扫描表 t2 多达 100 次，总共扫描 100*1000=10 万行。
Block Nested-Loop Join
Simple Nested-Loop Join 实际不会用，被驱动表上没有可用的索引，会用 Block Nested-Loop Join， 算法的流程是这样的：
- 把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；
- 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。
可以看到，在这个过程中，对表 t1 和 t2 都做了一次全表扫描，因此总的扫描行数是1100。由于 join_buffer 是以无序数组的方式组织的，因此对表 t2 中的每一行，都要做100 次判断， 总共需要在内存中做的判断次数是：100*1000=10 万次。
前面我们说过，如果使用 Simple Nested-Loop Join 算法进行查询，扫描行数也是 10 万行。因此，从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join算法的这 10 万次判断是内存操作，速度上会快很多，性能也更好。
如果 Join_buffer 满了放不下怎么办呢？
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1的所有数据话，就是分段放。
执行过程就变成了：
- 扫描表 t1，顺序读取数据行放入 join_buffer 中，放完第 88 行 join_buffer 满了，继续第 2 步；
- 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
- 清空 join_buffer；
- 继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步。
能不能使用 join 语句？
- 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
- 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。
所以在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。
如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？
- 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；
- 如果是 Block Nested-Loop Join 算法：
在join_buffer_size足够大的时候，是一样的；
在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表。
所以，这个问题的结论就是，总是应该使用小表做驱动表。
更准确地说，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”， 应该作为驱动表。
二十四、join语句怎么优化？
Multi-Range Read 优化
select * from t1 where a>=1 and a<=100;
主键索引是一棵 B+ 树，在这棵树上，每次只能根据一个主键 id 查到一行数据。因此，回表肯定是一行行搜索主键索引的。
如果随着 a 的值递增顺序查询的话，id 的值就变成随机的，那么就会出现随机访问，性能相对较差。
虽然“按行查”这个机制不能改，但是调整查询的顺序，还是能够加速的。因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。
这就是 MRR 优化的设计思路。此时，语句的执行流程变成了这样：
- 根据索引 a，定位到满足条件的记录，将 id 值放入
read_rnd_buffer中 ; - 将
read_rnd_buffer中的 id 进行递增排序； - 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。
这里，read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。如果步骤 1 中，read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。之后继续找索引 a 的下个记录，并继续循环。
从上图的 explain 结果中，我们可以看到 Extra 字段多了 Using MRR，表示的是用上了MRR 优化。而且，由于我们在
read_rnd_buffer 中按照 id 做了排序，所以最后得到的结果集也是按照主键 id 递增顺序的，也就是与图 1 结果集中行的顺序相反。
MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。
BKA 算法(Batched Key Access)
NLJ 算法执行的逻辑是：从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做join。也就是说，对于表 t2 来说，每次都是匹配一个值。这时，MRR 的优势就用不上了。
那怎么才能一次性地多传些值给表 t2 呢？方法就是，从表 t1 里一次性地多拿些行出来，一起传给表 t2。既然如此，我们就把表 t1 的数据取出来一部分，先放到一个临时内存，就是 join_buffer。
总结：
- BKA 优化是 MySQL 已经内置支持的，建议你默认使用；
- BNL 算法效率低，建议你都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引；
- 基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的；4. MySQL 目前的版本还不支持 hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。
