mysql中单表查询方法

大家好。我们知道，mysql有一个查询优化器的模块。当我们用sql语句查询表中记录时，会对这条查询语句进行语法解析，然后就会交给查询优化器来进行优化，优化后生成一个执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。今天我们就来介绍一下mysql的单表访问方法。

为了方便讲解，在这里我们先建立一个single_table 表并创建了一个聚簇索引（id）和四个二级索引，其中key_part1、key_part2和key_part3为联合索引。

CREATE TABLE single_table ( 
   id INT NOT NULL AUTO_INCREMENT, 
   key1 VARCHAR(100), 
   key2 INT, 
   key3 VARCHAR(100), 
   key_part1 VARCHAR(100), 
   key_part2 VARCHAR(100), 
   key_part3 VARCHAR(100), 
   common_field VARCHAR(100), 
   PRIMARY KEY (id), 
   KEY idx_key1 (key1), 
   UNIQUE KEY idx_key2 (key2), 
   KEY idx_key3 (key3), 
   KEY idx_key_part(key_part1, key_part2, key_part3) 
) Engine=InnoDB CHARSET=utf8;

一、访问方法（access method）的概念

我们平时所写的那些查询语句本质上只是一种声明式的语法，只是告诉MySQL我们要获取的数据符合哪些规则，至于MySQL背地里是怎么把查询结果搞出来的那是MySQL 自己的事儿。对于单个表的查询来说，查询的执行方式大致分为下边两种：

使用全表扫描进行查询： 这种执行方式很好理解，就是把表的每一行记录都扫一遍，把符合搜索条件的记录加入到结果集。任何查询都可以使用这种方式执行。

使用索引进行查询： 如果查询语句中的搜索条件可以使用到某个索引，那直接使用索引来执行查询可能会加快查询执行的时间。使用索引来执行查询的方式又可以细分为许多种类：针对主键或唯一二级索引的等值查询、针对普通二级索引的等值查询、针对索引列的范围查询、直接扫描整个索引。

MySQL执行查询语句的方式称之为访问方法或者访问类型。同一个查询语句可能可以使用多种不同的访问方法来执行，虽然最后的查询结果都是一样的，但是执行的时间却各不相同。下边细细道来各种访问方法的具体内容。

二、const

当我们通过主键来查询一条数据时，例如下面这条sql:

SELECT * FROM single_table WHERE id = 1438;

MySQL会直接利用主键值在聚簇索引中定位对应的用户记录，就像这样：

在这里插入图片描述
当我们通过唯一二级索引来查询一条数据时，例如下面这条sql:

SELECT * FROM single_table WHERE key2 = 3841;

MySQL会先从key2对应的idx_key2索引中定位到这条记录，然后再取这条记录中的id（主键）在聚簇索引中定位到对应的用户记录，就像这样：

在这里插入图片描述
这种通过主键或者唯一二级索引列来快速高效的定位一条记录的访问方法定义为：const，意思是常数级别的，代价是可以忽略不计的。

注意：这种const 访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个 const 访问方法才有效（这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录）。

对于唯一二级索引来说，查询该列为NULL值的情况比较特殊，比如这样：

SELECT * FROM single_table WHERE key2 IS NULL;

因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，所以上边这个语句不可以使用const 访问方法来执行。

三、ref

当我们通过普通的二级索引来查询一条数据时，例如下面这条sql:

SELECT * FROM single_table WHERE key1 = 'abc';

MySQL会先从key1对应的idx_key1索引中定位到符合key1 = ‘abc’ 的记录，然后再取这些记录中的id（主键）在聚簇索引中定位到对应的用户记录，就像这样：
在这里插入图片描述
普通的二级索引在索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配1条记录。所以这种ref访问方法比const差了一点，但是在二级索引等值比较时匹配的记录数较少时的效率还是很高的。不过需要注意下边两种情况：

二级索引列值为NULL 的情况： 不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含NULL值的数量并不限制，所以当我们用key IS NULL 这种语句进行查询时最多只能使用ref的访问方法，而不是const 的访问方法。

对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用ref 的访问方法： 比如下边这几个查询：

SELECT * FROM single_table WHERE key_part1 = 'god like';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary'; 
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';

但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref了，比如这样：

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary'

四、ref_or_null

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来，就像下边这个查询：

SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为 ref_or_null ，这个 ref_or_null 访问方法的执行过程如下：
在这里插入图片描述

五、range

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

我们平时查询的时候不仅仅只拿索引列与某个常量进行比较，往往会有更复杂的查询条件。如上述sql，我们可以使用全表扫描的方式来执行这个查询，也可以使用二级索引 + 回表的方式执行，如果采用二级索引 + 回表的方式来执行的话，那么此时的搜索条件就不只是要求索引列与常数的等值匹配了，而是索引列需要匹配某个或某些范围的值。这种利用索引进行范围匹配的访问方法称之为：range。

上述sql中，key2列对应着3个范围也就对应着3个区间：

范围1：key2 = 1438
范围2：key2 = 6328
范围3：key2 ∈ [38, 79] ，注意这里是闭区间。

我们可以把那种索引列等值匹配的情况称之为单点区间，上边所说的范围1和范围2都可以被称为单点区间，像范围3这种的我们可以称为连续范围区间。

六、index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

上述sql中key_part2并不是联合索引 idx_key_part 最左索引列，所以我们无法使用 ref 或者 range 访问方法来执行这个语句。但是这个查询符合下边这两个条件：

查询列表只有3个列：key_part1 , key_part2 , key_part3 ，而索引 idx_key_part 又包含这三个列。

搜索条件中只有key_part2 列。这个列也包含在索引 idx_key_part 中。

我们可以直接通过遍历idx_key_part索引的叶子节点的记录来比较 key_part2 = ‘abc’ 这个条件是否成立，把匹配成功的二级索引记录的key_part1 , key_part2 , key_part3 列的值直接加到结果集中就行了。由于二级索引记录比聚簇索记录小的多，而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多，这种采用遍历二级索引记录的执行方式称之为：index。

七、all

最直接的查询执行方式就是全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引，这种使用全表扫描执行查询的方式称之为：all。

八、注意事项

1、二级索引 + 回表注意事项

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

查询优化器会识别到这个查询中的两个搜索条件：key1 = ‘abc’ key2 > 1000。优化器一般会根据single_table 表的统计数据来判断到底使用哪个条件到对应的二级索引中查询扫描的行数会更少，选择那个扫描行数较少的条件到对应的二级索引中查询。然后将从该二级索引中查询到的结果经过回表得到完整的用户记录后再根据其余的WHERE条件过滤记录。

一般来说，等值查找比范围查找需要扫描的行数更少，所以这里假设优化器决定使用 idx_key1 索引进行查询，那么整个查询过程可以分为两个步骤：

使用二级索引定位记录的阶段，也就是根据条件key1 = ‘abc’ 从 idx_key1 索引代表的 B+ 树中找到对应的二级索引记录。
回表阶段，根据步骤1中找到的记录的主键值进行回表操作，也就是到聚簇索引中找到对应的完整的用户记录，再根据条件key2 > 1000 到完整的用户记录继续过滤。将最终符合过滤条件的记录返回给用户。

注意：因为二级索引的节点中的记录只包含索引列和主键，所以在步骤1中使用 idx_key1 索引进行查询时只会用到与 key1 列有关的搜索条件，其余条件，比如 key2 > 1000 这个条件在步骤1 中是用不到的，只有在步骤2完成回表操作后才能继续针对完整的用户记录中继续过滤。

2、明确range访问方法使用的范围区间

对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL 、 IS NOT NULL 、 > 、<、>=、<=、BETWEEN 、 != （不等于也可以写成 <> ）或者 LIKE 操作符连接起来，就可以产生一个所谓的区间。

注意：LIKE操作符比较特殊，只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引。
IN操作符的效果和若干个等值匹配操作符=之间用OR连接起来是一样的，都会产生多个单点区间。

当我们想使用range 访问方法来执行一个查询语句时，重点就是找出该查询可用的索引以及这些索引对应的范围区间。

所有搜索条件都可以使用某个索引的情况

SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;

这个查询中的搜索条件都可以使用到key2，也就是说每个搜索条件都对应着一个idx_key2 的范围区间。这两个小的搜索条件使用AND连接起来，也就是要取两个范围区间的交集，在我们使用range访问方法执行查询时，使用的idx_key2 索引的范围区间就是(200, +∞) 。

我们再看一下使用OR将多个搜索条件连接在一起的情况：

SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;

OR 意味着需要取各个范围区间的并集，所以上边这个查询在我们使用range访问方法执行查询时，使用的 idx_key2 索引的范围区间就是 (100， +∞)。

有的搜索条件无法使用索引的情况

SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc'

这个查询语句中能利用的索引只有idx_key2 一个，common_field 这个字段是没有索引的，所以在使用二级索引 idx_key2 定位记录的阶段用不到 common_field = ‘abc’ 这个条件。我们在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换为TRUE就好了。例如：我们把上边的查询中用不到idx_key2 的搜索条件替换后就是这样：

SELECT * FROM single_table WHERE key2 > 100 AND TRUE;

简化之后就是这样：

SELECT * FROM single_table WHERE key2 > 100;

所以最上边那个查询使用idx_key2 的范围区间就是：(100, +∞) 。再来看一下使用OR的情况：

SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';

同理，我们把使用不到idx_key2 索引的搜索条件替换为 TRUE ：

SELECT * FROM single_table WHERE key2 > 100 OR TRUE;

接着化简：

SELECT * FROM single_table WHERE TRUE;

所以对应的范围区间就是(-∞, +∞) ，也就是需要将全部二级索引的记录进行回表，这个代价肯定比直接全表扫描大。也就是说一个使用到索引的搜索条件和没有使用该索引的搜索条件使用OR连接起来后是无法使用该索引的。

复杂搜索条件下找出范围匹配的区间

SELECT * FROM single_table WHERE  
       (key1 > 'xyz' AND key2 = 748 ) OR 
       (key1 < 'abc' AND key1 > 'lmn') OR 
       (key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc'));

首先我们先看WHERE 子句中的搜索条件都涉及到了哪些列，哪些列可能使用到索引。这个查询的搜索条件涉及到了key1、key2 、 common_field 这3个列，然后 key1列有普通的二级索引idx_key1 ， key2列有唯一二级索引idx_key2 。

然后我们再对于这些可能用到的索引，分析它们的范围区间。

假设我们使用idx_key1 执行查询我们把用不到该索引的搜索条件替换为TRUE。把这些搜索条件替换为 TRUE 之后的样子就是这样：

(key1 > 'xyz' AND TRUE ) OR 
(key1 < 'abc' AND key1 > 'lmn') OR 
(TRUE AND key1 > 'zzz' AND (TRUE OR TRUE))

化简一下上边的搜索条件就是下边这样：

(key1 > 'xyz') OR 
(key1 < 'abc' AND key1 > 'lmn') OR 
(key1 > 'zzz')

最终的结果化简的到的区间就是：key1 > xyz 。

也就是说：上边那个有一坨搜索条件的查询语句如果使用 idx_key1 索引执行查询的话，需要把满足 key1 > xyz 的二级索引记录都取出来，然后拿着这些记录的id再进行回表，得到完整的用户记录之后再使用其他的搜索条件进行过滤。如果要用idx_key2列执行查询，大家试着自己找一找最终的区间。

3、索引合并

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

Intersection合并

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

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

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

从idx_key1 二级索引对应的 B+ 树中取出 key1 = ‘a’ 的相关记录。
从idx_key3 二级索引对应的 B+ 树中取出 key3 = ‘b’ 的相关记录。
二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中id值的交集。
按照上一步生成的id值列表进行回表操作。

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

只读取一个二级索引的成本： 按照某个搜索条件读取一个二级索引根据从该二级索引得到的主键值进行回表操作，然后再过滤其他的搜索条件。
读取多个二级索引之后取交集成本： 按照不同的搜索条件分别读取不同的二级索引将从多个二级索引得到的主键值取交集，然后进行回表操作。

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

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

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

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
 SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'; 
 SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';

上述三条sql中，第一条是可以进行Intersection合并的，其余两条则无法进行Intersection合并。
2、主键列可以是范围匹配

 SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

这个就查询可能用到主键和idx_key1 进行 Intersection 索引合并的操作。对于InnoDB 的二级索引来说，记录先是按照索引列进行排序，如果该二级索引是一个联合索引，那么会按照联合索引中的各个列依次排序。而二级索引的用户记录是由索引列 + 主键构成的，二级索引列的值相同的记录又是按照主键的值进行排序的。所以之所以在二级索引列都是等值匹配的情况下才可能使用Intersection索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。

假设某个查询使用 Intersection 索引合并的方式从 idx_key1 和 idx_key2 这两个二级索引中获取到的主键值分别是：

从idx_key1 中获取到已经排好序的主键值：1、3、5
从idx_key2 中获取到已经排好序的主键值：2、3、4

那么求交集的过程就是这样：先取出这两个结果集中较小的主键值做比较，因为1 < 2，所以把idx_key1 的结果集的主键值1丢弃，取出后边的3来比较。因为3 > 2 ，所以把idx_key2 的结果集的主键值 2 丢弃，取出后边的 3 来比较。因为3 = 3 ，所以把 3 加入到最后的交集结果中，继续两个结果集后边的主键值来比较。后边的主键值也不相等，所以最后的交集结果中只包含主键值3。

另外，不仅是多个二级索引之间可以采用Intersection 索引合并，索引合并也可以有聚簇索引参加，比如：

SELECT * FROM single_table WHERE key1 = 'a' AND id > 100;

假设这个查询可以采用Intersection 索引合并，那么这个查询可以在从idx_key1 中获取到的主键值上直接运用条件 id > 100 过滤就行了。

注意：上边说的情况一和情况二只是发生Intersection 索引合并的必要条件，不是充分条件。也就是说即使情况一、情况二成立，也不一定发生Intersection 索引合并。

Union合并

我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来。比方说这样：

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

Intersection 是交集的意思，这适用于使用不同索引的搜索条件之间使用 AND 连接起来的情况；Union 是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。

与Intersection 索引合并类似， MySQL 在某些特定的情况下才可能会使用到Union 索引合并：

1、二级索引列是等值匹配的情况
对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。比方说下边这个查询可能用到idx_key1 和 idx_key_part 这两个二级索引进行 Union 索引合并的操作：

SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

而下边这两个查询就不能进行Union 索引合并：

SELECT * FROM single_table WHERE key1 > 'a' OR (key_part1 = 'a' AND key_part2 = 'b'AND key_part3 = 'c'); 
SELECT * FROM single_table WHERE key1 = 'a' OR key_part1 = 'a';

第一个查询是因为对key1 进行了范围匹配，第二个查询是因为联合索引idx_key_part 中的 key_part2 列并没有出现在搜索条件中，所以这两个查询不能进行Union索引合并。

2、主键列可以是范围匹配

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

也就是搜索条件的某些部分使用Intersection 索引合并的方式得到的主键集合和其他方式得到的主键集合取交集，比方说这个查询：

 SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');

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

先按照搜索条件key1 = ‘a’ AND key3 = ‘b’ 从索引idx_key1和 idx_key3 中使用 Intersection 索引合并的方式得到一个主键集合。
再按照搜索条件key_part1 = ‘a’ AND key_part2 = ‘b’ AND key_part3 = ‘c’ 从联合索引 idx_key_part 中得到另一个主键集合。
采用Union 索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。

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

Sort-Union合并

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

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

这是因为根据key1 < ‘a’ 从 idx_key1 索引中获取的二级索引记录的主键值不是排好序的，根据 key3 > ‘z’ 从 idx_key3 索引中获取的二级索引记录的主键值也不是排好序的，但是我们可以这样：

先根据key1 < ‘a’ 条件从 idx_key1 二级索引总获取记录，并按照记录的主键值进行排序，再根据key3 > ‘z’ 条件从 idx_key3 二级索引总获取记录，并按照记录的主键值进行排序。因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了。我们把上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort Union 索引合并。

很显然，这种 Sort-Union 索引合并比单纯的 Union 索引合并多了一步对二级索引记录的主键值排序的过程。

那么我们思考一下：为啥有Sort-Union索引合并，就没有Sort-Intersection索引合并吗？是的，的确没有Sort-Intersection索引合并， Sort-Union的适用场景是单独根据搜索条件从某个二级索引中获取的记录数比较少，这样即使对这些二级索引记录按照主键值进行排序的成本也不会太高，而Intersection索引合并的适用场景是单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，合并后可以明显降低回表开销，但是如果加入Sort-Intersection后，就需要为大量的二级索引记录按照主键值进行排序，这个成本可能比回表查询都高了，所以也就没有引入Sort-Intersection这个玩意儿。

到这里今天的内容就讲完了，欢迎大家在评论区进行讨论。最后依旧是请各位老板有钱的捧个人场，没钱的也捧个人场，谢谢各位老板！