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;

single_table 表建立了1个聚簇索引和4个二级索引，分别是：

• 为id 列建立的聚簇索引。
• 为key1 列建立的idx_key1 二级索引。
• 为key2 列建立的idx_key2 二级索引，而且该索引是唯一二级索引。
• 为key3 列建立的idx_key3 二级索引。
• 为key_part1 、key_part2 、key_part3 列建立的idx_key_part 二级索引，这也是一个联合索引。

一、访问方法的概念

MySQL Server 有一个称为查询优化器的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，优化的结果就是生成一个所谓的执行计划。这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。

访问方法：就是MySQL执行查询语句的方式，也叫访问类型。

二、访问方法的方式

2.1 const

const：搜索条件为主键或者唯一二级索引与常量的等值比较来定位一条记录的访问方法。

SELECT * FROM single_table WHERE id = 1438;

需要注意，对于唯一二级索引，在查询列为null时，不可以使用const方法来执行。因为唯一二级索引列并不限制null值的数量。

2.2 ref

ref：搜索条件为二级索引列与常数的等值比较来定位一条记录的访问方法。

SELECT * FROM single_table WHERE key1 = 'abc';

注意，每获取到一条二级索引记录，是立即回表的，而不是获取到了所有二级索引记录才回表。因为，对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配1条记录，所以这种ref 访问方法比const 差一点。ref要求是等值比较，所以向联合索引这种，只要有一列不是连续的等值比较，就不能用这种方法了。

2.3 ref_or_null

ref_or_null：不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL 的记录也找出来，使用二级索引而不是全表扫描来定位一条记录。

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

2.4 range

range：使用索引执行查询计划，对应的扫描区间为若干个单点扫描区间或者范围扫描区间，这种利用索引进行范围匹配的访问方法。

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

注意，扫描区间为(-00,+00)的访问方法不能称为range访问方法。

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

2.5 index

index：扫描全部二级索引记录，而不需要直接扫描全部聚簇索引的访问方法，类似覆盖索引，不需要回表。

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

需要注意，当全表扫描对使用了InnoDB存储引擎的表执行查询时，如果添加了ORDER BY 主键的语句，那么在执行的时候，也会被人为的认为认定使用的是index访问方法。

2.6 all

使用全表扫描执行查询的方式称之为： all。

2.7. 索引合并

二级索引+回表 的方式来减少需要扫描的记录数量，一般是为单个索引生成扫描区间，然后在该区间再去判断其他条件。

当然存在特殊的情况，MySQL也可能为多个索引生成扫描区间，这种使用多个索引来完成一次查询的执行方法称为index merge（索引合并）。

具体分为：

2.7.1 Intersection索引合并

比如有这样一个查询：

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

可以有三种方案执行该查询：

——方案一、二：可以分别使用idex_key1或idx_key3来获取对应的扫描区间，在根据该扫描区间进行回表，然后结合另一个条件判断是否成立；

——方案三：同时使用idex_key1和idx_key3，获取到对应的扫描区间，然后找到id列值相同的记录，再根据这些共有的id值进行回表操作，这样就省去了部分仅在单个扫描区间包含的id值。

这里的方案三就是Intersection索引合并：取交集，指针对从不同索引中扫描到的记录的id值取交集，只为这些id值执行回表操作。

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

条件一：二级索引列必须是等值匹配，主键列可以是范围匹配。

条件二：使用到的索引都是二级索引的话，则要求从每个索引中获取到的二级索引记录都是按照主键值排序的。

2.7.2 Union索引合并

比如有这样一个查询：

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

与Intersection索引合并条件不同，这里是用OR连接两个查询条件。Union 是并集的意思，适用于使用不同索引的搜索条件之间使用OR 连接起来的情况。Union索引合并：指对从不同索引中扫描到的记录的id取并集，为并集后的id进行回表。

需要注意，Union索引合并，如果使用到的索引都是二级索引的话，则要求从每个索引中获取到的二级索引记录都是按照主键值排序的。

2.7.3 Sort-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 索引合并多了一步对二级索引记录的主键值排序的过程。