从根儿上理解MySQL ｜ 单表访问方法与连接的原理

单表访问方法

对于单个表的查询来说，查询的执行方式大致分为下边两种：

1. 使用全表扫描进行查询
2. 使用索引进行查询

所谓的访问方法或访问类型其实就是mysql执行查询语句的方式，而同一个查询语句可以使用不同的访问方法执行。

• const：通过主键或者唯一二级索引列进行等值查询（注：使用唯一二级索引进行null值查询，最多只能使用ref方法）；
• ref：通过普通二级索引（包括联合索引）进行等值查询；
• ref_or_null：查询普通二级索引为常数或者为null的记录；
• range：利用索引进行范围匹配
• index：遍历二级索引记录
• all：直接扫描聚簇索引

注意点：

1. 一般情况下执行一个查询只会用到单个二级索引；
2. 对于一些搜索条件无法使用索引，可以把该条件置为true，来判定单表访问方法。

索引合并：在某些特殊情况下，也可能使用到多个二级索引来完成一次查询，这种查询的执行方法为索引合并。

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

情况二：主键列可以是范围匹配。

• Intersection合并：从多个二级索引中查询到的结果取交集；

• Union合并：从多个二级索引中查询到的结果取并集。

• Sort-Union合并

索引合并注意事项：一般来说可以用联合索引来替代Intersection索引合并

连接的原理

首先介绍一些表连接的基础：

• 笛卡尔积
• 内连接：驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集。

SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];

两表执行内连接查询的大致过程主要分成两步：

1. 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询；
2. 对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

• 左外连接：选取左侧的表为驱动表

SELECT * FROM t1 LEFT JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

• 右外连接：选取右侧的表为驱动表

SELECT * FROM t1 RIGHT JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

补充：对于两表连接来说，驱动表只会被访问一遍，但被驱动表的访问次数取决于对驱动表执行单表查询后的结果集中的记录条数。

接下来才是真正的主题，mysql中采用了以下几种算法来进行表与表之间的连接：

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

驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式。（最笨拙 最简单的一种连接查询）

• 使用索引加快连接速度

在对被驱动表的查询时，使用索引可以加快连接速度。在连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式称之为：eq_ref

• 基于块的嵌套循环连接（Block Nested-Loop Join）

mysql中使用join buffer来减少对被驱动表的访问次数。join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。

最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。join buffer的大小可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为256KB，最小可以设置为128字节。一般情况下，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连接查询进行优化。

声明：本博客纯粹为读书笔记，如想详细了解MySQL相关知识请访问《MySQL是怎么运行的：从根儿上理解MySQL》原作者撰写资料