【Mysql】Mysql单表访问方法-Toy模板网

这篇具有很好参考价值的文章主要介绍了【Mysql】Mysql单表访问方法。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

前言

作为一个DBA或者运维人员，有时候发现数据库有慢查询语句需要优化，那么我们就需要先了解查询是怎么执行的。我们知道，MySQL Server 有一个查询优化器的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，优化的结果就是生成一个执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是怎样的，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。接下来我们先了解一下Mysql怎么执行单表查询，方便后续学习优化。

先创建一个测试表。

mysql> 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;
Query OK, 0 rows affected (0.15 sec)

我们为这个 single_table 表建立了1个聚簇索引和4个二级索引，分别是：
为 id 列建立的聚簇索引。
为 key1 列建立的 idx_key1 二级索引。
为 key2 列建立的 idx_key2 二级索引，而且是唯一二级索引。
为 key3 列建立的 idx_key3 二级索引，普通二级索引。
为 key_part1 、 key_part2 、 key_part3 列建立的 idx_key_part 二级索引，这也是一个联合索引。
然后我们需要为这个表插入10000行记录，除 id 列外其余的列都插入随机值。可以使用下面的脚步（亲测）：
以下是一个示例的脚本，用于向上述表中插入一万条行记录，其中 id 列从 1 到 10000 逐渐增长，key2 列每行的值都是唯一的，其他列都生成随机值插入：

-- 生成随机字符串函数
DELIMITER $$
CREATE FUNCTION random_string(length INT) RETURNS VARCHAR(100)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE result VARCHAR(100) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    
    WHILE i < length DO
        SET result = CONCAT(result, SUBSTRING(chars_str, FLOOR(1 + RAND() * 62), 1));
        SET i = i + 1;
    END WHILE;
    
    RETURN result;
END$$
DELIMITER ;

-- 插入一万条行记录
DELIMITER ;;
CREATE PROCEDURE insert_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= 10000 DO
        INSERT INTO single_table (id, key1, key2, key3, key_part1, key_part2, key_part3, common_field)
        VALUES (i, random_string(10), i, random_string(10), random_string(10), random_string(10), random_string(10), random_string(10));
        
        SET i = i + 1;
    END WHILE;
    
    COMMIT;
END;;
DELIMITER ;

-- 执行插入数据的脚本
CALL insert_data();

访问方法（access method）

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

使用全表扫描进行查询
这种执行方式很好理解，就是把表的每一行记录都扫一遍，把符合搜索条件的记录加入到结果集就行了。所有的查询都可以使用这种方式执行，当然，这种也是最笨的执行方式。
使用索引进行查询
因为直接使用全表扫描的方式执行查询要遍历很多记录，所以代价可能太大了。如果查询语句中的搜索条件可以使用到某个索引，那直接使用索引来执行查询可能会加快查询执行的时间。使用索引来执行查询的方式有多种，可以细分为以下几种：
- 针对主键或唯一二级索引的等值查询
- 针对普通二级索引的等值查询
- 针对索引列的范围查询
- 直接扫描整个索引
  我们把 MySQL 执行查询语句的方式称之为访问方法或者访问类型。同一个查询语句可能可以使用多种不同的访问方法来执行，虽然最后的查询结果都是一样的，但是执行的时间可能差别很大。下边详细说说各种访问方法。

const（常数）

有的时候我们可以通过主键列来定位一条记录，比方说这个查询：
SELECT * FROM single_table WHERE id = 1438;
MySQL 会直接利用主键值在聚簇索引中定位对应的用户记录，就像这样：
【Mysql】Mysql单表访问方法,mysql,mysql
如上是一个简略版的聚簇索引对应的复杂的 B+ 树，省略了页的结构，直接把所有的叶子节点的记录都放在一起展示，而且记录中只展示了需要关注的索引列，对于 single_table 表的聚簇索引来说，索引列就是 id 列。重点就是： B+ 树叶子节点中的记录是按照索引列排序的，对于的聚簇索引来说，它对应的 B+ 树叶子节点中的记录就是按照 id 列排序的，根据主键值定位一条记录的速度很快。类似的，我们根据唯一二级索引列来定位一条记录的速度也是很快的，如下边这个查询（key2也是唯一索引）：
SELECT * FROM single_table WHERE key2 = 3841;
这个查询的执行过程的示意图就如下：
【Mysql】Mysql单表访问方法,mysql,mysql
该查询的执行分两步：

先从 idx_key2 对应的 B+ 树索引中根据 key2 列与常数的等值比较条件定位到一条二级索引记录，获取到该记录的 id 值
在根据获取到的id到聚簇索引中获取到完整的用户记录。

在MySQL中认为通过主键或者唯一二级索引列与常数的等值比较来定位一条记录都是很快的，所以把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为： const ，意思是常数级别的，代价很小是可以忽略不计的。不过这种 const 访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const 访问方法才有效（这是因为只有该索引中全部列都采用等值比较才可以定位到唯一的一条记录）。
对于唯一二级索引来说，查询该列为 NULL 值的情况比较特殊，比如这样：
SELECT * FROM single_table WHERE key2 IS NULL;
因为唯一二级索引列并不限制NULL 值的数量，所以上述语句可能访问到多条记录，也就是说上边这个语句不能使用 const 访问方法来执行（NULL的访问方法我们下面说）。

ref（refs 复数，理解为多个？）

有时候我们对某个普通的二级索引列与常数进行等值比较，比如这样：
SELECT * FROM single_table WHERE key1 = ‘abc’;
对于这个查询，我们可以选择全表扫描来逐一对比搜索条件是否满足要求，我们也可以先使用二级索引找到对应记录的 id 值，然后再回表到聚簇索引中查找完整的用户记录。由于普通二级索引并不限制索引列值的唯一性，所以很可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引条数。如果匹配的记录较少，则回表的代价还是比较低的，所以 MySQL 可能选择使用索引而不是全表扫描的方式来执行查询。在MySQL中把这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为： ref 。采用 ref 访问方法执行的查询如下图所示：
【Mysql】Mysql单表访问方法,mysql,mysql
从上图可以看出，对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配到1条记录，所以一般这种 ref 访问方法比 const 访问方法效率会差一点，但是在二级索引等值比较时匹配的记录数较少时的效率还是很高的（但如果匹配的二级索引记录太多那么回表的成本就很大）。不过需要注意下边两种情况：

二级索引列值为 NULL 的情况
不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含 NULL 值的数量并不限制，所以我们采用key IS NULL 这种形式的搜索条件最多只能使用 ref 的访问方法，而不是 const 的访问方法。

对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用 ref的访问方法，比如下边这几个查询：
SELECT * FROM single_table WHERE key_part1 = ‘good’;
SELECT * FROM single_table WHERE key_part1 = ‘good’ AND key_part2 = ‘legendary’;
SELECT * FROM single_table WHERE key_part1 = ‘good’ AND key_part2 = ‘legendary’ AND key_part3 = ‘triple 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 访问方法的执行过程大致如下：
【Mysql】Mysql单表访问方法,mysql,mysql
可以看到，上边的查询相当于先分别从 idx_key1 索引对应的 B+ 树中找出 key1 IS NULL 和 key1 = ‘abc’ 的两个连续的记录范围，然后根据这些二级索引记录中的 id 值再回表查找完整的用户记录。

range

上面介绍的几种访问方法都是在对索引列与某一个常数进行等值比较的时候才可能使用到（ ref_or_null 比较特别，还包含了值为 NULL 的情况），但是有时候我们使用的搜索条件更复杂，比如下面这个查询：
SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);
我们当然可以使用全表扫描的方式来执行这个查询，不过也可以使用二级索引 + 回表的方式执行，如果采用二级索引 + 回表的方式来执行的话，那么此时的搜索条件就不只是要求索引列与常数的等值匹配了，而是索引列需要匹配某个或某些范围的值，在本查询中 key2 列的值只要匹配下列3个范围中的任何一个就算是匹配成功了：

key2 的值是 1438
key2 的值是 6328
key2 的值在 38 和 79 之间。
在MySQL中我们把这种利用索引进行范围匹配的访问方法称之为： range 。
注意：此处所说的使用索引进行范围匹配中的 索引 可以是聚簇索引，也可以是二级索引。
如果把这几个所谓的 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’;
由于 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 列的值直接加到结果集中就行了。由于二级索引记录比聚簇索记录小的多（聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放索引列和主键），而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多，在MySQL中就把这种采用遍历二级索引记录的执行方式称之为： index 。

all

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

注意事项

二级索引 + 回表

一般情况下只能选择使用一个二级索引执行查询，比方说下边的这个查询：
SELECT * FROM single_table WHERE key1 = ‘abc’ AND key2 > 1000;
注意我们是对key1和key2分别建立了索引的。查询优化器会识别到这个查询中的两个搜索条件：

key1 = ‘abc’
key2 > 1000

查询优化器一般会根据 single_table 表的统计数据来判断到底使用哪个条件到对应的二级索引中查询扫描的行数会更少，选择那个扫描行数较少的条件到对应的二级索引中查询。然后将从该二级索引中查询到的结果经过回表得到完整的用户记录后再根据其余的 WHERE 条件进行过滤。一般来说，等值查找比范围查找需要扫描的行数更少（ ref 的访问方法一般比 range 好，但这并不是一定的，也可能采用 ref 访问方法的那个索引列的值为特定值的行数特别多），所以这里假设优化器决定使用idx_key1 索引进行查询，那么整个查询过程可以分为两个步骤：

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

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

其次，一般情况下执行一个查询只会用到单个二级索引，不过还是有特殊情况的，后续详细说明。

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

对于 B+ 树索引来说，只要索引列和常数使用 = 、 <=> 、 IN 、 NOT IN 、 IS NULL 、 IS NOT NULL 、> 、 < 、 >= 、 <= 、 BETWEEN 、 != （不等于也可以写成 <> ）或者 LIKE 操作符连接起来，就会产生一个所谓的区间。
说明：LIKE操作符比较特殊，只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引。
IN操作符的效果和若干个等值匹配操作符=之间用OR连接起来是一样的，也就是说会产生多个单点区间，比如下边这两个语句的效果是一样的：
SELECT * FROM single_table WHERE key2 IN (1438, 6328);
SELECT * FROM single_table WHERE key2 = 1438 OR key2 = 6328;

在日常的工作中，一个查询的 WHERE 子句可能有很多个小的搜索条件，这些搜索条件使用 AND 或者 OR操作符连接起来，如下：
condition1 AND condition2 ：只有当 condition1 和 condition2 都为 TRUE 时整个表达式才为 TRUE 。
condition1 OR condition2 ：只要 condition1 或者 condition2 中有一个为 TRUE 整个表达式就为 TRUE 。
当我们想使用 range 访问方法来执行一个查询语句时，重点就是找出该查询可用的索引以及这些索引对应的范围区间。下面分两种情况看一下如何从由 AND 或 OR 组成的复杂搜索条件中正确提取出范围区间。

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

有时候每个搜索条件都可以使用到某个索引，比如下边这个查询语句:
SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;
这个查询中的搜索条件都是使用 key2 ，也就是说每个搜索条件都对应着一个 idx_key2 的范围区间。这两个小的搜索条件使用 AND连接起来，也就是要取两个范围区间的交集，在我们使用 range 访问方法执行查询时，使用的idx_key2 索引的范围区间的确定过程就如下图所示：
【Mysql】Mysql单表访问方法,mysql,mysql
key2 > 100 和 key2 > 200 交集就是 key2 > 200 了，也就是说上边这个查询使用 idx_key2 的范围区间就是 (200, +∞) 。我们再看一下使用 OR 将多个搜索条件连接在一起的情况:
SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;
OR 意味着需要取各个范围区间的并集，所以上边这个查询在我们使用 range 访问方法执行查询时，使用的idx_key2 索引的范围区间的确定过程就如下图所示:
【Mysql】Mysql单表访问方法,mysql,mysql
也就是说上边这个查询使用 idx_key2 的范围区间就是 (100， +∞) 。

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

比如下面这个查询:
SELECT * FROM single_table WHERE key2 > 100 AND common_field = ‘abc’;
注意，这个查询语句只能使用一个索引 idx_key2，但 idx_key2 这个二级索引的记录中不包含 common_field 这个字段，所以在使用二级索引 idx_key2 定位记录的阶段用不到 common_field = ‘abc’ 这个条件，这个条件是在回表获取了完整的用户记录后比较才使用的，而范围区间是为了到索引中取记录中提出的概念，所以在确定范围区间的时候不需要考虑common_field = ‘abc’ 这个条件，我们在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件用 TRUE暂时替换就可以了。

注意：把用不到索引的搜索条件替换为TRUE，是因为我们不打算使用这些条件进行在该索引上进行过滤，所以不管索引的记录满不满足这些条件，我们都会把它们选取出来，后续回表的时候再使用它们进行过滤。所以上面的查询中用不到 common_field 这个搜索条件，替换后如下：
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;
这也就说如果我们一定要使用 idx_key2 执行查询的话，对应的查询范围区间就是 (-∞, +∞) ，也就是需要将全部二级索引的记录进行回表，这个代价肯定比直接全表扫描都大。也就是说一个使用到索引的搜索条件和没有使用该索引的搜索条件使用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 就好了。上边的查询中除了有关 key2 和 common_field 列不能使用到 idx_key1 索引外， key1 LIKE ‘%suf’ 也使用不到索引（不是前缀索引），所以把这些搜索条件替换为 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’)
  替换掉永远为 TRUE 或 FALSE 的条件
  因为符合 key1 < ‘abc’ AND key1 > ‘lmn’ 永远为 FALSE ，所以上边的搜索条件继续简写如下：
  (key1 > ‘xyz’) OR (key1 > ‘zzz’)
  继续化简区间
  key1 > ‘xyz’ 和 key1 > ‘zzz’ 之间使用 OR 操作符连接起来的，意味着要取并集，所以最终的结果化简的到的区间就是： key1 > xyz 。也就是说：上边那个有很多索条件的查询语句如果使用idx_key1 索引执行查询的话，需要把满足 key1 > xyz 的二级索引记录都取出来，然后拿着这些记录的id再进行回表，得到完整的用户记录之后再使用其他的搜索条件进行过滤。
- 假设我们使用 idx_key2 执行查询
  我们需要把那些用不到该索引的搜索条件暂时使用 TRUE 条件替换掉，所以有关 key1 和common_field 的搜索条件都需要被替换掉，替换后结果如下：
  (TRUE AND key2 = 748 ) OR
  (TRUE AND TRUE) OR
  (TRUE AND TRUE AND (key2 < 8000 OR TRUE))
  而key2 < 8000 OR TRUE 的结果肯定是 TRUE ，也就是说化简之后的搜索条件成这样：
  key2 = 748 OR TRUE
  这个化简之后的结果就更简单了：
  TRUE
  这个结果也就说明如果我们要使用 idx_key2 索引执行查询语句的话，需要扫描 idx_key2 二级索引的所有记录，然后再回表，所以这种情况下不会使用 idx_key2 索引的。

索引合并

前边讲到MySQL在一般情况下执行一个查询时最多只会用到一个二级索引，不过在某些特殊情况下也可能在一个查询中使用到多个二级索引，我们把在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 值列表进行回表操作，也就是从聚簇索引中把指定 id 值的完整用户记录取出来，返回给用户。

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

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

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

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

按照不同的搜索条件分别读取不同的二级索引
将从多个二级索引得到的主键值取交集，然后进行回表操作

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

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

情况一：二级索引列是等值匹配的情况，并且如果是联合索引的话，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。
比方说下边这个查询可能用到 idx_key1 和 idx_key_part （三列）这两个二级索引进行 Intersection 索引合并的操作：
SELECT * FROM single_table WHERE key1 = ‘a’ AND key_part1 = ‘a’ AND key_part2 = ‘b’ AND key_part3 = ‘c’;
而下边这两个查询就不能进行 Intersection 索引合并：
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’;
第一个查询是因为对 key1 进行了范围匹配，第二个查询是因为联合索引 idx_key_part 中的 key_part2 、key_part3 列没有出现在搜索条件中，所以这两个查询不能进行 Intersection 索引合并。
情况二：主键列可以是范围匹配
比如下面这个查询可能用到主键和 idx_key1 进行 Intersection 索引合并的操作：
SELECT * FROM single_table WHERE id > 100 AND key1 = ‘a’;
原因如下：需要从InnoDB 的索引结构说起，对于 InnoDB 的二级索引来说，记录先是按照索引列进行排序，如果该二级索引是一个联合索引，那么会按照联合索引中的各个列依次排序。而二级索引的用户记录是由索引列 + 主键构成的，二级索引列（不唯一）的值相同的记录可能会有很多条，这些索引列的值相同的记录又是按照主键的值进行排序的。 所以重点来了，之所以在二级索引列都是等值匹配的情况下才可能使用 Intersection 索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。

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 。

这个过程其实很快，时间复杂度是 O(n) ，但是如果从各个二级索引中查询出的结果集并不是按照主键排序的话，那就需要先把结果集中的主键值排序完再进行上面的过程，而排序过程就比较耗时了。
说明：按照有序的主键值去回表取记录有个专有名词儿，叫：Rowid Ordered Retrieval，简称ROR。

此外，不仅是多个二级索引之间可以采用 Intersection 索引合并，聚簇索引也可以进行索引合并，如上面的情况二：在搜索条件中有主键的范围匹配的情况下也可以使用 Intersection 索引合并索引合并。比如下面这个查询：
SELECT * FROM single_table WHERE key1 = ‘a’ AND id > 100;
假设这个查询可以采用 Intersection 索引合并，常规想法我们会认为这个查询会分别按照 id > 100 这个条件从聚簇索引中获取一些记录，在通过 key1 = ‘a’ 这个条件从 idx_key1 二级索引中获取一些记录，然后再求交集。但其实没必要从聚簇索引中再获取一次记录。因为二级索引的记录中都带有主键值的，所以可以在从 idx_key1 中获取到的主键值上直接运用条件 id > 100 过滤就可以了。所以涉及主键的搜索条件只不过是为了从别的二级索引得到的结果集中过滤记录罢了，是不是等值匹配并不重要（因为在key1相同的情况下，是按id值进行排好序的）。

当然，上边说的情况一和情况二只是发生Intersection 索引合并的必要条件，不是充分条件。也就是说即使情况一、情况二成立，也不一定发生 Intersection 索引合并，这得看优化器的优化结果。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过 Intersection 索引合并后需要回表的记录数大大减少时才会使用 Intersection 索引合并。（回表数量多而通过intersection索引合并后需要回表的记录减少很多的时候就很可能进行索引合并）

Union合并

我们在写查询语句时经常想把符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，这些不同的搜索条件之间是 OR 关系。有时候 OR 关系的不同搜索条件会使用到不同的索引，比如：
SELECT * FROM single_table WHERE key1 = ‘a’ OR key3 = ‘b’
上面的Intersection 是交集的意思，这适用于使用不同索引的搜索条件之间使用 AND 连接起来的情况；而Union 是并集的意思，适用于使用不同索引的搜索条件之间使用 OR 连接起来的情况。与 Intersection 索引合并类似，MySQL 在某些特定的情况下才可能会使用到 Union 索引合并：

情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。
比方说下边这个查询可能用到 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、key_part3 列并没有出现在搜索条件中，所以这两个查询不能进行 Union 索引合并。

情况二：主键列可以是范围匹配
情况三：使用 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’ 和 key3 > ‘z’ 这两个条件又很有吸引力（都有索引），所以我们可以这样：

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

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

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

联合索引替代Intersection索引合并

SELECT * FROM single_table WHERE key1 = ‘a’ AND key3 = ‘b’;
这个查询之所以可能使用 Intersection 索引合并的方式执行，是因为 idx_key1 和 idx_key3 是两个单独的 B+ 树索引，要是建立一个关于两个列的联合索引，那直接使用这个联合索引就可以了。如下：
ALTER TABLE single_table drop index idx_key1, idx_key3, add index idx_key1_key3(key1, key3);

这样我们先删除 idx_key1 、 idx_key3 ，再添加一个联合索引 idx_key1_key3 ，使用这个联合索引进行查询简直是又快又好，既不用多读一棵 B+ 树，也不用合并结果。
注意：但实际使用中是否有单独对key1、key3列进行查询的业务场景，如果有还是需要把idx_key1 、 idx_key3这两个索引保留。

更多关于mysql的知识分享，请前往博客主页。编写过程中，难免出现差错，敬请指出文章来源地址https://www.toymoban.com/news/detail-715336.html

到了这里，关于【Mysql】Mysql单表访问方法的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！