MySQL查询的本质——单表与多表的查询方法

单表查询

单表查询的语句实际上是一种声明式的语法，只是告诉MySQL要获取的数据符合哪些规则，至于具体的执行方式是MySQL自己来定。针对不同的场景，也有不同的执行计划， MySQL基于开销(cost)的优化器策略，哪种执行计划开销更低，就意味着性能更好，速度更快，就选择哪一种。以下是执行计划的类型：

const

通过主键或者唯一二级索引与常数的等值比较来定位一条记录，是最快的执行方式。主键定位直接返回，而唯一二级索引只需查询一次+回表一次即可返回

ref

较为常见的，在建立二级索引后直接使用其为等值查询条件。因为是等值的，索引会形成单点扫描区间。这种方法称为ref，规则是：搜索条件为二级索引列与常数进行等值比较，形成的扫描区间为单点扫描区间，采用二级索引来执行查询。这种方式的缺点就是每查到一次就回表一次，消耗大。

• 且当二级索引列允许存储null时，都是用此ref方式访问。
• ref允许最左匹配机制，即最左连续列匹配联合索引，就可以使用ref
• 当查询条件不为等值时不能用ref

（另外：在多表查询中，对被驱动表的主键或者不允许存储NULL值的唯一二级索引进行等值查找，使用的方法称为eq_ref）

ref_or_null

比ref多扫描了一些值为null的二级索引记录

range

使用索引执行查询时，对应的扫描区间为若干个单点扫描区间或者范围扫描区间。即包含多个单点扫描区间的查询条件，包含一个或全表扫描都不能称为range

index

直接扫描全部二级索引记录，且查询结果包含在索引列中，无需回表操作。即索引覆盖，因为二级索引比聚簇索引是要小很多的，就算全部扫描也不会消耗很多资源

all

全表扫描，适用于所有查询，但缺点就是慢

注意：

一般来说，具体使用哪种方法来执行，是MySQL中优化器的工作，它会访问表中少量数据获等方式，分析出多种执行计划中成本最小的来查询。

一般来说，使用二级索引查询时，当拿到匹配的二级记录后，就算还有其他匹配条件，也会先根据结果（主键）执行回表操作，再检测该记录是否满足其他条件，满足则发送给客户端，不满足则忽略。

index_merge

索引合并。使用多个索引来完成一次查询，有以下三种方式：

• Intersection索引合并

举个栗子：SELECT * FROM sing_table WHERE key1 = ‘a’ AND key3 = ‘b’;

一共有四种方式可以执行该查询：

1. 1. 首先，是全表就不谈了，慢。
   2. 其次，是使用key1的索引查询，则扫描区间为[‘a’,‘a’]，查询到之后立刻回表，再匹配key3。
   3. 然后，是按照key3的索引查询，同样的则扫描区间为[‘b’,‘b’]，查询到之后立刻回表，再匹配key1
   4. 最后，同时使用key1和key3的索引查询。在key1索引中扫描出匹配key1=a的记录，在key3索引中扫描出匹配key1=b的记录。由于在普通二级索引中，重复的索引值会按照主键来排序。所以在这个执行计划中，使用各自的索引扫描出来的记录也是主键排序的。又因为这里使用了 AND，需要查询两者条件都匹配的结果，所以找出两者查询的结果中主键相同的记录，再使用这些相同的主键一并执行回表，这样可能会省下很多回表带来的开销。这就是Intersection索引合并，它要求二级索引是排序的，也就是说我们的查询条件必须是等值的，只有等值才会排序。如果不使用等值条件，则不会使用Intersection索引合并（相当于各自使用ref，最后取重复集）

注意：如果条件中含有主键索引，则并不会扫描主键索引，而是和非主键索引一起形成等效的扫描区间。

• Union索引合并

再举个栗子：SELECT * FROM sing_table WHERE key1 = ‘a’ OR key3 = ‘b’;

和之前的例子差不多，只是AND换成了OR，但完全不同了，你肯定不能单独使用其中一个索引来查，否则会先查询二级索引所有记录，再全部回表，灾难性的回表代表巨大消耗！所以要么你全表查询，要么你就是用Union索引合并：

在key1索引中扫描出匹配key1=a的记录，在key3索引中扫描出匹配key1=b的记录。因为这里用的是 OR ，只要匹配一个条件即可返回，所以只要根据两个结果集进行去重，把重复的ID去掉，接着就可以拿着剩下的ID区一并回表了，其实和Intersection索引是类似的，只不过它要的是重复的记录，这里要的是去掉重复的记录。同样，只有等值比较才可以使用这种Union索引合并。（相当于各自ref，然后去重）

• Sort-Union索引合并

看这个名字其实就是比Union新增了一个Sort，那么它在什么地方加了排序呢？我们知道使用Union的条件是二级索引记录必须是按主键排序的，也就是说在SQL语句中，必须是等值的，类似上面的：key1 = ‘a’ OR key3 = ‘b’; 但如果是这种：key1 < ‘a’ OR key3 > ‘z’; 呢？使用Sort-Union排序可以先从key1索引中获得条件为“key1 < ‘a’ ”的二级索引记录，并对其根据主键进行排序；再从key3索引中获得条件为“key3 > ‘z’ ”的二级索引记录，也对其根据主键进行排序。如此，又变成Union索引合并了。又可以减少很多回表操作。这种方法比普通的Union多了一层各自索引的主键值的排序，也称Sort-Union索引合并。

注意：只有Sort-Union而没有Sort-Intersecion的原因作者也有解释，但具体没怎么看明白：如果加入Sort-Intersecion，就需要为大量的二级索引记录按照主键值进行排序，这个成本可能比使用单个二级索引执行查询的成本都要高，所以mysql没有引入。

在MySQL中执行计划使用EXPLAIN关键字查看，可以查看 SELECT,DELETE,INSERT,REPLACE,UPDATE语句的查询计划，查询出来的执行计划有两种查看方式（MySQL5.7+），：

• 表格格式：EXPLAIN SQL
• json格式：EXPLAIN FORMAT =JSON SQL

例：表格：

json:

多表查询

MySQL是关系型数据库，一个非常重要的概念就是Join 。表关联的实质其实就是把各个表的记录都取出来依此匹配，并把匹配后的组合发给客户端。如果不加条件限制，给到客户端的记录将是几个表的乘积，也称笛卡尔积，因为需要每个表的每一条记录都与另一个表的每条记录相互匹配。这个过程有点像大规模的回表操作，或者代码中的嵌套for循环。

因此，我们一般在连接查询时并不会全部匹配，而是会加上限制条件。这里的限制条件一般只有两种情况：

• 单表限制条件。如：key=3或key>1或key<4。只针对key所在的表的限制条件
• 多表限制条件。如：t1.m1=t2.m1、t1.n1< t2.n2。就针对了t1和t2两个表的限制条件。

在这里有必要提一下基本连接查询过程：假设有表t1和t2，t1作为驱动表，t2作为被驱动表。首先在t1表中，按照对应的限制条件，使用成本最小的单表查询方法去查询。每次在t1查询到一条记录，都需要根据这条记录去t2表查询匹配的记录。即驱动表只需要查询一次，而被驱动表可能需要访问多次。

这种方式有个缺点，如果驱动表的某条记录，没有在被驱动表中找到相应记录，那么就连驱动表的那条记录也不会查询出来，这样就导致查询出来的数据有缺失。为了解决该问题，引出了连接另一个概念：外连接。在外连接中，即时驱动表中的记录在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

连接查询也需要有过滤条件，单表查询中使用WHERE，连接查询中的WHERE可能是不够用的。因此在连接查询中，存在着两种过滤条件：

• WHERE：不分内外连接，只要不匹配WHERE子句的条件，就不加入结果集。

• ON：

• • 对于外连接的驱动表来说，如果在被驱动表中找不到匹配ON子句的过滤条件，驱动表的记录还是会加入结果集，被驱动表找不到记录没有关系，填NULL即可。
  • 对于内连接的驱动表来说，ON等效于WHERE。

内连接的语法：

select * from t1,t2; 等效于 select * from t1 join t2; 等效于 select * from t1 inner join t2; 等效于 select * from t1 cross join t2; 。

推荐INNER JOIN的写法，语义明确

外连接的语法：

select * from t1 left join t2 on 连接条件 [where 普通过滤条件];

left join 中左侧的表称为外表或者驱动表，右侧的表称为内表或被驱动表。外连接必须使用on来指出连接条件。

right join也是一样的，只是左右驱动表位置换了，不再赘述

连接查询的原理

嵌套循环连接

最基本的简单的连接查询算算法。驱动表只访问一次，但被驱动表却可能访问多次，且访问的次数取决于对驱动表执行单表查询后的结果集中有多少条记录（驱动表匹配到记录后立即去被驱动表查）

多次如果每条驱动表的记录在被驱动表中都有很多，那么单表查询被驱动表的次数就会非常多，效率自然会不高。如果被驱动表的限制条件带有索引，那么有可能会使用ref、ref_or_null或者range等方式来查找，效率相对会快一些。所以在查询时，尽量避免select *，而是把真正使用到的列作为查询条件，这是在人为可控的情况下，优化查询。一般常用的列都会有索引，索引覆盖下查询效率会更高。

基于块的嵌套循环连接

在表数据过于庞大的情况下，记录有可能是千万、亿级的规模，这种情况下的连接查询有可能会非常慢。基于块的嵌套循环连接是从减少被驱动表的访问次数入手，达到减少IO、提高效率的效果的。具体的做法就是在执行连接查询前申请一块固定大小的内存（也称Join Buffer），先将若干驱动表结果集里的记录装在这个内存中，然后开始扫描被驱动表，每条被驱动表的记录一次性地与Join Buffer中的多条驱动表记录进行匹配，由于匹配在内存中完成，无需IO，所以可以大大减少IO量。

Join Buffer的大小通过启动项或系统变量进行配置，默认为256KB。一般在不能使用索引且自己机器的内存较大的情况下，可以尝试调大Join Buffer来进行连接查询的优化。需要注意的是Join Buffer并不会放所有驱动表的列，只存放查询列表中的列和过滤条件的列。因此我们查询时少用select * ，这样Join Buffer可以存放更多的记录。