前言

小弟最近在开发一个项目时遇到了有点困扰我的问题，很有意思，而且也值得记录一下，希望对大家有用

场景：

我们有两个表，一个订单表表示t1，一个是订单的明细表t2，t2表中包含用户购买的各个产品，他们是根据订单编号关联的，当我用t1作为驱动表left join 连接t2表时没用到索引，但是用t2表连接t1表时，就用到了全文检索，很奇怪！因为按照我们通常的想法都是小表驱动大表

业务要求：

1.根据产品名称或产品型号或订单编号模糊查询这个订单的所有信息

2：不能用like，所以用的是全文检索

说明：

这篇博客最大的作用不是教大家怎么解决，而是分析为什么会这样！

文章目录

• 前言
• 准备
• 问题
• • t1 left join t2
  • t2 left join t1
  • 小结：
• 说明
• • 问题1：MySQL为什么推荐小表驱动大表
  • • NLJ
    • BNL
    • MRR
    • BKA
  • 问题2：为什么换了一下位置就用到了索引？
  • • t1 left join t2
    • • 没有where条件
      • 加上where条件
      • 分析
    • t2 left join t1
    • • 没有where条件
      • 加上where条件
      • 分析
• 结束语

准备

MySQL：8.0
Java：1.8

创建t1表和t2表

创建索引：

t1和t2表的关系：一对多

t1

order_number字段是一个唯一索引

status字段是普通索引

t2

除了主键id，其它字段全是全文检索

1、在已有的表中添加全文索引

• WITH PARSER ngram 为全文检索的解析器

create fulltext index 索引名称 on 表名(字段名) WITH PARSER ngram;

2、搜索

• IN BOOLEAN MODE 为查询中是否包含该语句

SELECT * FROM tab_name WHERE MATCH ('列名1,列名2...列名n') AGAINST('词1 词2 词3 ... 词m' IN BOOLEAN MODE);

问题

用t1作为驱动表left join 连接t2表时没用到索引，但是用t2表连接t1表时，就用到了全文检索，用没用到索引有多大的差距，不做多诉说，相信大家都知道

t1 left join t2

sql：

EXPLAIN 
SELECT t1.* FROM t1
	LEFT JOIN t2 on t1.order_number = t2.order_number where t1.`status` = "true" and 			MATCH(t2.`name`) AGAINST("华为" IN BOOLEAN MODE)

结果：

说明：

所用实际为100毫秒左右

t1和t2表的type都是ALL，证明全都是全表扫描，

possible_keys指预计会用到的索引

key为实际用到的索引

rows为11和15是预计扫描了11行（可能跟实际行数有差距，这里只是预计，所以只做参考数据）

结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。所以这个sql要优化了。

t2 left join t1

sql：

EXPLAIN 
SELECT t1.* FROM t2
	LEFT JOIN t1 on t1.order_number = t2.order_number where t1.`status` = "true" and 			MATCH(t2.`name`) AGAINST("华为" IN BOOLEAN MODE)

结果：

说明：

所用实际为50毫秒左右

比没用到索引快一倍，这个倍数会随着数据的增多二增加，现在只有十几条数据就差一倍，后续可想而知（大家在测试时如果速度一样，则增多数据，就会有很明显的差距）

t1和t2表的type一个为fulltext，一个为eq_ref，细表是用到了全文检索，主表的索引等级也是很高

possible_keys指预计会用到的索引

key为实际用到的索引

rows都为1（可能跟实际行数有差距，这里只是预计，所以只做参考数据）

小结：

不言而喻，我们肯定会选择第二种，但是又会产生一些问题：

1. 我们都在强调用小表驱动大表，我们现在选用第二种好像违背了这一点
2. 为什么换了一下位置就用到了索引
3. 索引的走向到底是怎么走的

说明

问题1：MySQL为什么推荐小表驱动大表

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。

例如：t1有10条数据，t2表有1000条数据，t1表每条数据关联t2表100条数据

sql：

SELECT * FROM t2 LEFT JOIN t1 on t1.order_number = t2.order_number

这样则需要用t2表循环1000次才能查询出来，而如果用t1表驱动user表则只需要循环20次就能查询出来。

小表驱动大表主要是减少连接次数，增加速度

因此这个语句的执行流程是：

1. 从表t2中读入一行数据
2. 从数据行中，取出order_number字段到表t1里去查找
3. 取出表t1中满足条件的行，跟t2的数据组成一行，作为结果集的一部分
4. 重复上面操作，直到t2的数据取完为止

NLJ

这个过程是先遍历表t2，然后根据表t1中取出的每行数据中的order_number值，去表t1中查询满足条件的记录。这就是"Index Nested-Loop Join"，简称NLJ。

因此建议内表走索引，也叫INLJ，但是如果内表是二级索引，效率也低，因为要回表查主键。

索引MySQL会判断使用索引和不使用的差别，如果检测全表扫描比使用索引还快，它就会选择ALL

所以一般情况下我们是建议都是小表驱动大表

BNL

这里我们把t2表的order_number字段的索引删除

sql:

SELECT * FROM t2 LEFT JOIN t1 on t1.order_number = t2.order_number

结果就是两个表都要全表扫描，这里我们看到，Extra显示的是（Using where; Using join buffer (Block Nested Loop)）

这个其实是MySQL对join不走索引全表扫描做了一个优化，简称BNL。

因此这个语句的执行流程是：

1. 把表t1的数据读入线程内存join_buffer中，这里我们是把整个表t1放入内存中。
2. 扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。
   

这里，我们两个表都是做的全表扫描，所以不管是哪个表做驱动表都是执行消耗都是一样的。

如果一个表的数据太大了，根本装不下所有数据的话，就采用分段放。也可以修改join_buffer_size。

MRR

MySQL5.6版本开始支持的Multi-Range Read（MRR）优化。MRR目的是为了减少磁盘的随机访问，并且将随机访问转换为较为顺序的数据访问，MRR可适用于range，ref，eq_ref类型的查询

MRR优化有以下几个好处：

1. MRR使数据访问变的较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找
2. 减少缓冲池中页被替换的次数
3. 批量处理对键值的查询操作

MRR的设计思路是因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

BKA

针对于有索引的被驱动表，MySQL5.6版本开始增加了Batched Key Access（BKA）的新特性

对于多表join语句，当MySQL使用索引访问第一个join表的时候，使用join_buffer来收集第一个操作对象生成的相关列的值。BKA构建好key之后，通过MRR接口提交给引擎做查询。

BKA步骤：

1. 将驱动表相关的列放入join_buffer中。
2. 批量的将Key（索引键值）发送到MRR接口。
3. MRR通过收到的key，根据其对应的ROWID进行排序，然后再进行数据的读取操作。

这里来看，BKA和BNL其实是差不多的，主要区别就是BKA是针对被驱动表是走索引的情况下，索引是非主键索引的时候，按照索引字段进行排序，因此减少了随机IO，提高性能。

问题2：为什么换了一下位置就用到了索引？

我开始在网上搜索的时候得到一种答案，说的是两个表的字符集不一样

我检查了我的两个表，字符集都是一致的

虽然不适用这篇文章，但是确实是一种情况，当字符集不一致，是会出现的，所以只要改成一样就能解决

那么：

为什么我们的一致还是会出现这种情况呢？

1. 最好的解释就是MySQL认为不走索引比走索引快
2. where条件的干扰

t1 left join t2

没有where条件

sql：

EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 on t1.order_number = t2.order_number

加上where条件

sql：

EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 on t1.order_number = t2.order_number  where t1.`status` = "true" and MATCH(t2.`name`) AGAINST("华为" IN BOOLEAN MODE)

分析

我们可以看到结果差不多的，那可以分析一下各种情况：

1. 没有where条件时，主表就是全表扫描，为啥？

   因为MySQL在此认为走ALL更快，所以没选，或者说，就应该全表扫描，（还有一些特殊情况会用到索引， 例如主表关联字段是多个，也就是多对一的情况下会用到索引，大家可以试试，然后可以想想为什么）

2. 没有where条件时：细表预计用到order_number_idx索引，但最后也没用到，为啥？

   因为主表是全表扫描，一样的道理，t1表需要全部查出来,然后拿查出来的order_number字段去t2表匹配，然后组成一行数据返回，可想而知；

   t2全部扫描用索引不是更快吗？为啥不用？

   因为t2表的order_number_idx是全文检索，全文检索是啥大家可以去自行了解一下，它跟es一样，是把字段内容分词处理，每个分词都是索引，索引联表时如果是全文检索，那比ALL更慢

   一旦把order_number字段换成别的索引，比如普通索引或者唯一索引，结果是有惊喜的，它S是会用到索引的

   

3. 有where条件时：主表预计用到status_idx索引，但实际还是没用到，为啥？

   首先要知道为什么会预计用到status_idx 索引；是因为where后面有t1.status = "true" 这个条件，但最后没用到的原因是：

   1. status_idx 是二级索引，也就是非聚集索引，所以它会造成一个回表操作
   2. 还有就是t1表中status这个字段的值都是ture，一但把一半的数据改成fasel，那么就会用到索引
   3. 当status这个字段的结果都是一样时，MySQL认为这个回表操作比ALL更慢，所以选择了ALL

4. 有where条件时：细表预计使用order_number_idx,但最后也没用到，为啥？

   我们先把order_number改成普通索引，这种情况下，没有where条件时是会用到order_number_idx索引的,但是当我们加上where条件后，结果还是跟上面一摸一样,为什么呢？而且我们where的条件还是name字段的索引，就连预计都不会使用name这个索引

   1. 我们假设用到了name_idx这个索引，当一条数据从t1表过来，MySQL需要先根据order_number_idx索引找到主键索引，然后根据这个主键再去主键索引也就是id去查找name字段，然后才能使用name_idx这个索引，看到这里，大家应该懂了为什么MySQL连预计都没有

   2. 至于最后为什么还是没用到order_number_idx索引，主要是where后面使用了name这个字段，需要像上面一样繁琐，一旦我们后面的条件改成 where t1.status= "true" and t2.order_number = "heusjd23eu"，那么结果将大大不一样

      

   为什么会这样呢？就连主表也用到了索引，大家可以自行想想，其实很有意思，哈哈

在有索引的情况下，MySQL会尝试去使用Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择Block Nested-LoopJoin。

t2 left join t1

没有where条件

sql：

EXPLAIN SELECT t1.* FROM t2 LEFT JOIN t1 on t1.order_number = t2.order_number

加上where条件

sql：

EXPLAIN SELECT t1.* FROM t2 LEFT JOIN t1 on t1.order_number = t2.order_number  where t1.`status` = "true" and MATCH(t2.`name`) AGAINST("华为" IN BOOLEAN MODE)

分析

可以看到结果的区别主要就是在t2表用到了索引，我们不去分析没有加where条件的情况，上面已经说得很清楚了，没看懂的同学可以仔细琢磨琢磨，下面我们将分析一下有条件的情况：

1. 其实当你理清楚上面用t1 left join t2的情况时，就很容易理解为什么会这样了

2. 主表也就是驱动表为啥使用了name_idx索引?

   其实主要是有whereand MATCH(t2.name) AGAINST("华为" IN BOOLEAN MODE)这个条件，我们可以想象一下，当一条数据进来时，MySQL可以根据name_idx这个索引快速定位到具体哪一行数据，所以连它的rows预计扫描行数都只有1条，然后根据这行数据的order_number字段去扫描t1表，而t1表正好也有order_number_idx这个索引，所以结果不言而喻

其实到了这一步，已经不需要分析太多，估计大家懂了，懂得都懂，不懂得可以再看看

结束语

相信看了这篇博客可以很好的帮助你设计索引，还有最后一句话：

任何执拗都会成为过往，只有时间会告诉你对错。