【笔记】mysql 单表查询的执行方式本文介绍 mysql 单表扫描方式，以及索引合并方式；另有简单描述「最左匹配」原

全表扫描
使用索引查询
- 针对主键或唯一二级索引的等值查询 const
- 针对普通二级索引的等值查询 ref
- 针对索引列的范围查询 range
- 扫描整个索引 all

准备

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;

id 列建立聚簇索引
key1 列建立 idx_key1 二级索引
key2 列建立 idx_key2 唯一二级索引
key3 列建立 idx_key3 二级索引
key_part1, key_part2, key_part3 建立 idx_key_part 二级索引，也是联合索引

访问方式

const

SELECT * FROM single_table WHERE id = 13213;

直接使用主键索引，定位后可直接获取 id = 13213 记录的所有列信息

SELECT * FROM single_table WHERE key2 = 1234;

使用 idx_key2 的唯一二级索引，通过该索引找到其对应的 id，因为唯一索引的值具有唯一性，所以只会找到一个 id，定位后立即停止扫描；再通过主键索引查询所有列

以上两种访问方式被称为 const 方式，也是 mysql 中最快的查询方式
特殊情况，唯一索引并不会限制 NULL 值的数量，所以查询 NULL 值时并不能找到唯一的 id，此时就不能称作 const 方式

ref

SELECT * FROM single_table WHERE key1 = 'hello';

查询步骤：

从 idx_key1 索引中定位 key1 = 'hello' 的记录，这些记录是连续的，即从扫描到第一个符合条件的记录开始，到第一个不符合条件的记录结束
利用上一个步骤中获取到的 id 值集合，到主键索引中查询所有信息

以上方式称为 ref，即通过普通索引获取到少量主键集合，再通过主键索引查询所需信息；但当第一步中获取的 id 值集合太大，那第二步回表的步骤代价会成倍上升

两种特殊情况
- 二级索引列值为 NULL；因为二级索引中对所含 NULL 值的数量并不限制，所以查询 key is null 时最好的情况是采用 ref 方式，而不是 const
- 联合索引；只要最左边的连续索引列是等值查询，就可能采用 ref 方式
```
SELECT * FROM single_table WHERE key_part1 = 'like god';
SELECT * FROM single_table WHERE key_part1 = 'like god' AND key_part2 = 'like eval';
```

ref_or_null

SELECT * FROM single_table WHERE key1 = 'abc' AND key1 IS NULL;

利用二级索引时，等值查询的同时找出 NULL 值，即在 key1 的二级索引上找出两个连续范围
ref_or_null 是 ref 方式的一种变形

range

SELECT * FROM single_table WHERE key2 IN (1000, 1200) or (key2 >= 38 AND key2 <= 79);

该 where 字句匹配到三个区间：

key2 值为 1000
key2 值为 1200
key2 值在 [38, 79] 范围内

其中前两个范围被称作单点区间

适用范围：索引列与常数使用 =、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个所谓的区间

index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'like god';

不符合最左原则，不能使用 ref / range
只查询 key_part1, key_part2, key_part3 三个列的值，且三个列均属于联合索引 idx_key_part 范围内
搜索条件 key_part2 也在联合索引 idx_key_part 中

查询联合索引 idx_key_part 就可得到所需信息，无需回表，效率较高

all

也就是全表扫描，实在找不到好办法时的托底方案

索引合并

目的：所有的一切都是为了减少回表

intersection 交集

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

如果采用 Intersection 的方式查询，过程如下：

从 idx_key1 二级索引中获取符合条件 key1='a' 的所有 id 集合
从 idx_key3 二级索引中获取符合条件 key3='b' 的所有 id 集合
取前两个步骤中所得 id 集合的交集，再回主键索引查询所有信息（减少回表次数）

两种特定情况

二级索引和联合索引的 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';
SELECT * FROM single_table WHERE key1='a' AND key_part1 = 'a' AND key_part2='b' AND key_part3>'c';

主键列可以范围匹配

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

union 合并

与 intersection 类似，不过 intersection 适用于 AND 运算，而 UNION 适合 OR 运算

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

如果采用 union 的方式查询，过程如下：

从 idx_key1 二级索引中获取符合条件 key1='a' 的所有 id 集合
从 idx_key3 二级索引中获取符合条件 key3='b' 的所有 id 集合
取前两个步骤中所得 id 集合的并集，再回主键索引查询所有信息（减少回表次数）

三种特殊情况

二级索引和联合索引的 union；二级索引必须时等值匹配，而联合索引必须时所有列都是等值匹配，不能只有部分值匹配

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

该语句满足执行 intersection 的条件，而以下语句不满足

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

主键列可以时范围匹配
使用 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 中得到另一个主键集合
取前两个步骤所得主键集合的并集，再回表

sort-union 合并

union 合并方式的使用条件太过苛刻，必须满足所有二级索引都是等值匹配

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

以上语句则不能使用 union 合并方式，但确实命中了两个二级索引，为了让索引发挥作用，mysql 采用了如下思路：

根据 key1 < 'a' 获取所有二级索引 idx_key1 中满足条件的 id 集合，并对 id 集合进行排序
根据 key3 > 'z' 获取所有二级索引 idx_key3 中满足条件的 id 集合，并对 id 集合进行排序
合并前两个步骤中获取的 id 集合，再采用 union 的方式进行索引合并

NULL 值

mysql 有一条铁律，当 WHERE 字句中出现 IS NULL 判断时，无法使用相应列所属索引为什么呢？

mysql 设计索引有一条准则，即索引所在列要存在足够的辨识度，即同一列的取值，重复性越小，创建索引的收益越大；而 NULL 是一个特殊的值，有的人认为 NULL 是一个不确定的值，即每一个 NULL 都是一个不同的值；有的人认为 NULL 是一个确定的值，即每一个 NULL 都代表同样的含义；有的人认为 NULL 是无意义的，忽略即可

mysql 为了解决这个问题，设计了一个全局变量， innodb_stats_method，用来指定计算某个索引列不重复值的数量时如何对待 NULL 值，共有三个选项

nulls_equal，即认为所有 NULL 都是相等的，也是 mysql 的默认值；如果某个索引列中 NULL 值特别多，那么 mysql 就认为该索引列辨识度不够，不推荐使用该索引
nulls_unequal，即认为所有 NULL 都是不相等的；如果某个索引列中 NULL 值特别多，那么 mysql 就认为该索引辨识度极高，推荐使用该索引
nulls_ignored，即忽略所有 NULL 值

最左匹配和索引下推

最左匹配针对的是联合索引，如 idx_key_part 索引，当 WHERE 子句中存在 key_part1 等值查询，或 key_part1 / key_part2 等值查询，或 key_part1 / key_part2 / key_part3 等值查询时，可以命中 idx_key_part 索引；即最左匹配徐满足两个条件：

参与比较的索引列必须是某个联合索引的（从第一个列开始的）顺序子集
参与比较的列都必须是等值比较，如 = 或 IN

举例，以下语句满足最左匹配，命中 idx_key_part 索引

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

而以下语句就不满足最左匹配，因为 key_part2 是非等值查询

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

所以，执行该语句的计划可能如下：

命中 idx_key_part 索引（仅限于 key_part1 这一列），找到符合 key_part1 = 'a' 条件的 id
回表，如果某条记录满足 key_part2 > 'b' AND key_part3 = 'c' 条件，则加入结果集

可以看出，对于 idx_key_part 这个联合索引来说，这是一个非常浪费性能的计划；为了更好使用联合索引，mysql 自 5.6 版本推出 index push down 的思想，中文译作【索引下推】，个人觉得【索引前推】可能更准确一些，在该思想的指导下，上述语句的执行计划如下：

命中 idx_key_part 索引（仅限于 key_part1 这一列），找到符合 key_part1 = 'a' 条件的 id
判断该记录的 key_part2 和 key_part3 是否满足条件 key_part2 > 'b' AND key_part3 = 'c'
如不满足条件，则直接丢弃该 id，否则回表，找到该 id 对应的记录，并加入结果集