一、MySQL 的查询机制

MySQL是“边读边发”的。

获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
重复获取行，直到 net_buffer 写满，调用网络接口发出去。
如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

由此可知，查询结果是分段发给客户端的。

1.1 MySQL 的两个接口

mysql_store_result：

机制：对结果进行本地缓存。

优点：数据量小时不会阻塞服务端发送结果。

缺点：数据量过大时会耗费很多的本地缓存，影响本地机器性能。

mysql_use_result：

机制：对结果进行不缓存，读一个处理一个。

优点：不会影响本地缓存。

缺点：本地处理慢时会导致服务器端发送阻塞。

1.2 MySQL 语句的几种状态

使用 show processlist 查看对应语句状态。

语句的 State 值为 Sending to client，代表等待客户端接收结果。
语句的 State 值为 Sending data，代表语句正在执行。
如果语句的 State 的值一直处于 Sending to client，就表示服务器端的网络栈写满了。
如果语句的 State 的值一直处于 Sending data，就表示可能语句执行被阻塞了，比如锁等待。

1.3 小结

大查询不会把内存用光，但可能影响接受结果机器的缓存，堵塞住 MySQL 的查询过程。
MySQL 的 InnoDB 引擎内部有淘汰策略，保证大查询也不会让内存暴涨。
全表扫描还是比较耗费 IO 资源，在业务高峰期会影响 MySQL 执行速度。

二、索引为什么不生效

2.1 如何看索引是否生效

使用 explain 命令查看语句的执行计划。

id	SELECT查询的序列标识符
select_type	SELECT关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

2.2 explain 命令结果字段详解：

id：

SELECT 标识符，是查询中 SELECT 的序号，用来标识整个查询中 SELELCT 语句的顺序。

id 如果相同，从上往下依次执行。id 不同，id 值越大，执行优先级越高，如果行引用其他行的并集结果，则该值可以为 NULL。

select_type：

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：

SIMPLE：简单查询，不包含 UNION 或者子查询。
PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
SUBQUERY：子查询中的第一个 SELECT。
UNION：在 UNION 语句中，UNION 之后出现的 SELECT。
DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。
UNION RESULT：UNION 查询的结果。

table：

查询用到的表名，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值：

<unionM,N> : 本行引用了 id 为 M 和 N 的行的 UNION 结果。
<derivedN> : 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。<subqueryN> : 本行引用了 id 为 N 的表所产生的的物化子查询结果。

type（重要）：

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下：

system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
ALL：全表扫描。

possible_keys：

possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

key（重要）：

key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

key_len：

key_len 列表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。

rows：

rows 列表示根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好。

Extra（重要）：

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

2.3 索引不生效的几种场景

2.3.1 索引建立不当或使用未遵循最左匹配原则

最左匹配原则顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询（>、<、between、like）就会停止匹配。

注：MySQL 8.0 进行了优化，提供了索引条约扫描功能，如果联合索引中第一列的索引的唯一值较少时，没有用第一列索引也可以使用到索引。

2.3.2 匹配时在索引列上使用了计算函数

使用函数本身可以走索引，但如果使用函数计算后的值进行匹配就没有办法走索引。因为改变了在索引内原来的值，总而言之，只要影响到索引列原来的值，索引就失效。

注：MySQL 8.0 进行了优化，可以针对函数计算后的值建立索引。

2.3.3 匹配时在索引列上有计算操作

与上述一个原因，使用计算后的值进行匹配就没有办法走索引。

2.3.4 使用了左边或两边的模糊查询

使用了左模糊查询或两边模糊查询会导致查询范围过大，导致索引没有意义而失效。

2.3.5 错误使用 or 关键字

比如 or 前面是索引列，而后面不是索引列，就会导致索引失效。

使用 or 关键字时两边组合成一个联合索引，就可以走索引。

2.3.6 in、not in 关键字使用不当

in、not in 的取值范围过大（结果集大于 30%）时会导致索引失效。

2.3.7 order by 关键字使用不当

Mysql 认为直接全表扫面的速度比回表的速度快所以就直接走索引了，在 order by 的情况下走全表扫描反而是更好的选择。

2.4 小结

查询范围过大，导致索引没有意义。
用于匹配时更改字段在索引上的原值导致匹配不上。
字段使用不确定时，无法选中索引（or 关键字）。
MySQL 内部的优化选择。
联合索引不遵循最左匹配原则。

三、join 关键字应该如何使用

3.1 join 关键字的问题

join 本身的效率不高，一旦数据量很大（超过百万级），效率就很难保证。
join 写的 sql 语句维护起来成本比较大。
对于分布式的分表分库，join 的表现不良好。

3.2 join 的运行机制

join 的运行机制在被驱动表连表字段有可用索引和无可用索引的情况下会分别使用两套算法进行执行。

被驱动表连表字段有可用索引：Index Nested-Loop Join 算法

被驱动表连表字段无可用索引：Block Nested-Loop Join 算法

3.2.1 被驱动表连表字段有可用索引

Index Nested-Loop Join 算法执行流程：

从表 t1 中读入一行数据 R；
从数据行 R 中，取出 a 字段到表 t2 里去查找；
取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；
重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

在执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。

假设被驱动表的行数是 M。每次在被驱动表查一行数据，要先搜索索引 a，再搜索主键索引。每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 log2M，所以在被驱动表上查一行的时间复杂度是 2 * log2M。

假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次。

因此整个执行过程，近似复杂度是 N + N * 2 * log2M。

显然，N对扫描行数的影响更大，因此应该让小表来做驱动表。（小表驱动大表）

3.2.2 被驱动表连表字段无可用索引

Block Nested-Loop Join 算法执行流程：

把表 t1 的数据读入线程内存 join_buffer 中；
扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

在执行过程中，对表 t1 和 t2 都做了一次全表扫描。

由于 join_buffer 是以无序数组的方式组织的，t2 匹配 t1 要把 t1 的所有行都判断一次。

假设小表的行数是 N，大表的行数是 M，那么在这个算法里：

两个表都做一次全表扫描，所以总的扫描行数是 M + N；
内存中的判断次数是 M * N。

3.2.3 要是表 t1 是一个大表，join_buffer 放不下怎么办呢？

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表t1的所有数据话，策略很简单，就是分段放。

执行过程就变成了：

扫描表 t1，顺序读取数据行放入 join_buffer 中，放完 join_buffer 满了，继续第 2 步；
扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
清空 join_buffer；
继续扫描表 t1，顺序读取接下来的数据放入 join_buffer 中，继续执行第 2 步。

这个流程才体现出了这个算法名字中 “Block” 的由来，表示 “分块去 join”。

在这种情况下假设，驱动表的数据行数是 N，需要分 K 段才能完成算法流程，被驱动表的数据行数是 M。

注意，这里的 K 不是常数，N 越大 K 就会越大，因此把 K 表示为 λ * N，显然 λ 的取值范围是（0，1）。

所以，在这个算法的执行过程中：

扫描行数是 N + λ * N * M；
内存判断 N * M 次。

显然，内存判断次数是不受选择哪个表作为驱动表影响的。而考虑到扫描行数，在 M 和 N 大小确定的情况下，N 小一些，整个算式的结果会更小。所以应该让小表当驱动表。

在 N+λ * N * M 这个式子里，λ 才是影响扫描行数的关键因素，这个值越小越好。

参数 join_buffer_size 越大，一次可以放入的行越多，分成的段数也就越少，对被驱动表的全表扫描次数就越少，λ 值越小。

3.3 案例分析

场景：根据 MOS 的模板素材审核通过返回的 multimediaId 字段找到对应的模板表 id 以提交模板。

业务背景：日常程序运行中每一个模板可能会拥有 1~n 个素材，每一个素材对应一个素材审核记录。

表的对应关系 e-r 图：

根据小表驱动大表的原则，sql 如下：

select distinct t.id
from gsms_m_msg_user_material_audit a
left join gsms_m_msg_user_material m on m.id = a.user_material_id
left join gsms_m_msg_template_page p on p.src = m.resource_id
left join gsms_m_msg_template t on t.id = p.template_id
where a.multimedia_id = #{multimediaId} and t.provider_type = #{providerType} and audit_state = 5 and t.is_deleted = 0 and t.provider_tpl_id is null;

sql 分析（假设模板的数据量非常大的情况下分析）：

每一个模板素材审核记录的 multimediaId 都是由运营商处返回来的唯一值，所以 gsms_m_msg_user_material_audit 是整个 sql 中的小表无疑，所以应用来做驱动表，别的表作为被驱动表。
gsms_m_msg_user_material 表作为关联 gsms_m_msg_user_material_audit 表的被驱动表，使用主键 id 进行关联，使用 Index Nested-Loop Join 算法，使用合理，而且搜查出来数据量与 gsms_m_msg_user_material_audit 一致，适合做下一任的驱动表。
gsms_m_msg_template_page 表作为关联 gsms_m_msg_user_material 表的被驱动表，使用 src 字段进行关联，无索引，使用 Block Nested-Loop Join 算法，建议在该表数据量大的时候给 src 字段加上索引，执行会更快一些。
gsms_m_msg_template 表作为关联 gsms_m_msg_template_page 表的被驱动表，使用主键 id 进行关联，使用 Index Nested-Loop Join 算法，此时 gsms_m_msg_template_page 表已经过匹配过滤，数据量较小，使用合理。

案例分析总结：

sql 总体设计合理，建议在 gsms_m_msg_template_page 数据量上升后，给 src 字段加上索引，会使该语句执行速度更快。gsms_m_msg_user_material_audit 表的数据量上升后，也建议给 multimedia_id 字段加上索引。

3.4 小结

join 可以使用 Index Nested-Loop Join 算法时，也就是说可以用上被驱动表上的索引，其实是没问题的。
join 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。
Index Nested-Loop Join 算法，应该选择小表做驱动表。
Block Nested-Loop Join 算法，在 join_buffer_size 足够大的时候，是一样的。在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。
小表的定义：小表是需要带着 where 条件去判断的，where 条件过滤后数据量更小的表才是真正的小表。