MySQL查询原理

单表访问之索引合并

MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况，在这些特殊情况下也可能在一个查询中使用到多个二级索引，MySQL中这种使用到多个索引来完成一次查询的执行方法称之为：索引合并/index merge，具体的索引合并算法有下边三种。

Intersection合并

Intersection翻译过来的意思是交集。这里是说某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集，比方说下边这个查询

select  * from `order` where  order_Id = 1 and name = 'n1'

假设这个查询使用Intersection合并的方式执行的话，那这个过程就是这样的：

1. 从idx_name二级索引对应的B+树中取出name='n1'的相关记录。
2. 从idx_order_id二级索引对应的B+树中取出order_id =1的相关记录。

二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中id值的交集。

按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来，返回给用户。

为啥不直接使用idx_name或者idx_order_id只根据某个搜索条件去读取一个二级索引，然后回表后再过滤另外一个搜索条件呢？这里要分析一下两种查询执行方式之间需要的成本代价。

只读取一个二级索引的成本：

1. 按照某个搜索条件读取一个二级索引
2. 根据从该二级索引得到的主键值进行回表操作
3. 然后再过滤其他的搜索条件

读取多个二级索引之后取交集成本：

1. 按照不同的搜索条件分别读取不同的二级索引
2. 将从多个二级索引得到的主键值取交集
3. 最后根据主键值进行回表操作。

虽然读取多个二级索引比读取一个二级索引消耗性能，但是大部分情况下读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

MySQL在某些特定的情况下才可能会使用到Intersection索引合并

等值匹配

• 二级索引列必须是等值匹配的情况
• 对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

select  * from `order` where  order_id > 1 and name = 'n1'

select * from `order` where create_time > '1970-01-01 00:00:00'

第一个查询是因为对order_id进行了范围匹配

第二个查询是因为create_time所在的联合索引idx_no_logistics_time中的order_no和logistics_no

列并没有出现在搜索条件中，所以这两个查询不能进行Intersection索引合并。

主键列可以是范围匹配

比方说下边这个查询可能用到主键和idx_name进行Intersection索引合并的操作：

select  * from `order` where  order_id > 1 and name = 'n1';

因为主键的索引是有序的，按照有序的主键值去回表取记录有个专有名词，叫：Rowid Ordered Retrieval，简称 ROR。

而二级索引的用户记录是由索引列 + 主键构成的，所以根据范围匹配出来的主键就是乱序的，导致回表开销很大。

那为什么在二级索引列都是等值匹配的情况下也可能使用Intersection索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。

Intersection索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就很容易。

当然，上边说的两种情况只是发生Intersection索引合并的必要条件，不是充分条件。也就是说即使符合Intersection的条件，也不一定发生Intersection索引合并，这得看优化器判断。

优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。

Union合并

Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。与Intersection索引合并类似，MySQL在某些特定的情况下才可能会使用到Union索引合并。

select  * from `order` where  order_id = 1 or name = 'n1';

等值匹配

与intersection合并一致

主键列范围匹配

与intersection合并一致

使用Intersection索引合并的搜索条件

SELECT * FROM `order` WHERE order_no = 'a' and logistics_no = 'b' and create_time = '1970-01-01 00:00:00'
OR ( order_id = 1 and name = 'n1');

优化器可能采用这样的方式来执行这个查询:

1. 先按照搜索条件order_no = 'a' and logistics_no = 'b' and create_time = '1970-01-01 00:00:00'从联合索引idx_no_logistics_time的方式得到一个主键集合。
2. 再按照搜索条件order_id = 1 and name = 'n1'从idx_order_id和idx_name中使用intersection索引合并的方式得到一个主键集合。
3. 采用union索引合并的方式吧上述两个主键集合去并集，然后进行回表操作，将结果返回给用户。

Sort-Union合并

Union索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到Union索引合并：

select  * from `order` where  order_id < 1 or name > 'n1';

这是因为根据order_id<1从idx_order_id索引中获取的二级索引记录的主键值不是排好序的，

同时根据name>'n1'从idx_name索引中获取的二级索引记录的主键值也不是排好序的，但是order_id < 1 or name > 'n1'这两个条件又特别让我们动心，所以我们可以这样：

1、先根据order_id<1条件从idx_order_id二级索引中获取记录，并按照记录的主键值进行排序

2、再根据name>'n1'条件从idx_name二级索引中获取记录，并按照记录的主键值进行排序

3、因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了。

上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

当然，查询条件符合了这些情况也不一定就会采用Sort-Union索引合并，也得看优化器的选择。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Sort-Union索引合并后进行访问的代价比全表扫描更小时才会使用Sort-Union索引合并。

联合索引替代Intersection索引合并

select  * from `order` where  order_id = 1 and name = 'n1';

这个查询之所以可能使用Intersection索引合并的方式执行，还不是因为idx_order_id和idx_name是两个单独的B+树索引，要是把这两个列搞一个联合索引，那直接使用这个联合索引就把事情搞定了，何必用啥索引合并呢？

drop index idx_name on `order`;

drop index idx_order_id on `order`;

create index idx_order_id_name on `order` (order_id, name);

删除idx_order_id和idx_name相关索引，创建联合索引，这样就不会使用索引合并，直接使用联合索引即可。

连接查询

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户

所以我们把e1和e2两个表连接起来的过程如下图所示：

这个过程看起来就是把e1表的记录和e2的记录连起来组成新的更大的记录，所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为 笛卡尔积 。

驱动表与被驱动表

首先确定第一个需要查询的表，这个表称之为 驱动表 。 遍历驱动表结果，到被驱动表中查找匹配记录，驱动表只需要访问一次，被驱动表可能被访问多次。

外连接

左外连接：SELECT * FROM e1 LEFT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤条件];

此时e1为驱动表，e2为被驱动表。

右外连接：SELECT * FROM e1 RIGHT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤条件];

此时e2是驱动表，e1是被驱动表。

内连接

三种写法（效果都一样）

SELECT * FROM e1 JOIN e2;

SELECT * FROM e1 INNER JOIN e2;

SELECT * FROM e1 CROSS JOIN e2;

其中e1为驱动表

三种join算法

嵌套循环连接（Nested-LoopJoin）

简单来说嵌套循环连接算法就是一个多层for 循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果，这种算法是最简单的方案，性能也一般，对内循环没优化。

例子：

-- 连接用户表与订单表 连接条件是 u.id = o.user_id
select * from user t1 left join order t2 on t1.id = t2.user_id;
-- user表为驱动表,order表为被驱动表

转换成代码执行时的思路是这样的:

for(user表行 uRow : user表){
    for(Order表的行 oRow : order表){
        if(uRow.id = oRow.user_id){
            return uRow;
        }
    }
}

SNL 的特点

• 简单粗暴容易理解，就是通过双层循环比较数据来获得结果

• 查询效率会非常慢,假设 A 表有 N 行，B 表有 M 行。SNL 的开销如下：

  • A 表扫描 1 次。

  • B 表扫描 M 次。

  • 一共有 N 个内循环，每个内循环要 M 次，一共有内循环 N * M 次

索引嵌套循连接（Index Nested-Loop join）

如果访问被驱动表的方式都是全表扫描的话，那速度肯定会很慢很慢。可以利用索引来加快查询速度。

• Index Nested-Loop Join 其优化的思路: 主要是为了减少内层表数据的匹配次数 , 最大的区别在于，用来进行 join 的字段已经在被驱动表中建立了索引。

• 从原来的 匹配次数 = 外层表行数 * 内层表行数 , 变成了 匹配次数 = 外层表的行数 * 内层表索引的高度 ，极大的提升了 join的性能。

注意：使用Index Nested-Loop Join 算法的前提是被驱动表连接条件对应的字段必须建立了索引

块嵌套循环连接（Block Nested-Loop Join）

如果 join 的字段有索引，MySQL 会使用 INL 算法。如果没有的话，MySQL 会如何处理？

因为不存在索引了，所以被驱动表需要进行扫描。这里 MySQL 并不会简单粗暴的应用 SNL 算法，而是加入了join buffer 缓冲区，降低了内循环的个数，也就是被驱动表的扫描次数。

在企业开发中，表的记录数量可能非常大，甚至达到几亿条。由于内存容量的限制，无法一次性将所有记录加载到内存中。因此，在扫描表的过程中，需要将部分记录从内存中释放出来。

对于采用嵌套循环连接算法的表连接操作，如果被驱动表中的数据量很大且不能使用索引进行访问，那么每次访问被驱动表都需要从磁盘上读取数据，这将导致很大的I/O代价。为了减少这种代价，我们需要尽量减少访问被驱动表的次数。

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录都会被加载到内存中。在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中。这样反复进行，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。

为了减少这种重复加载的代价，MySQL提出了一个名为join buffer的概念。join buffer是执行连接查询前申请的一块固定大小的内存。首先，将若干条驱动表结果集中的记录加载到join buffer中。然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配。由于匹配过程都是在内存中完成的，因此可以显著减少被驱动表的I/O代价。

最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录。

这种加入了join buffer的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。

• 在外层循环扫描 user表中的所有记录。扫描的时候，会把需要进行 join 用到的列都缓存到 buffer 中。buffer 中的数据有一个特点，里面的记录不需要一条一条地取出来和 order 表进行比较，而是整个 buffer 和 order表进行批量比较。

• 如果我们把 buffer 的空间开得很大，可以容纳下 user 表的所有记录，那么 order 表也只需要访问一次。

• 如果有 n 个 join 操作，会生成 n-1 个 join buffer。

这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为262144字节（也就是256KB），最小可以设置为128字节。

需要注意的是，驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录。

-- 查看join buffer的大小
show variables like 'join_buffer_size';



-- 设置join buffer的大小
set session join_buffer_size=262144;

 

JOIN优化总结

1. 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)

2. 为匹配的条件增加索引(减少内层表的循环匹配次数)

3. 适当增大join buffer size的大小（一次缓存的数据越多，那么被驱动表去扫表驱动表的次数就越少）

4. 减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）