单表访问方法

对于我们这些MySQL使用者来说，MySQL其实就是一个软件，平时用的最多的就是查询功能.DBA是不是丢过来一些慢查询语句让优化，如果我们连查询的原理都不知道，就很难进行优化了。
MySQL Server有一个称为查询优化器的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，优化的结果就是生成一个所谓的执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。
本文主要关注的是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列建立的二级索引，而且该二级索引是唯一二级索引
• 为key3列建立的idx_key3二级索引
• 为key_part1、key_part2、key_part3列建立的idx_key_part二级索引，这也是一个联合索引

访问方法的概念（access method）

MySQl查询的执行方式大致有以下两种：

• 使用全表扫描进行查询：这个很好理解，把表的每一行记录都扫描一遍，把符合搜索条件的记录加入结果集就可以了，不管是什么查询都可以用这种方式来执行。
• 使用索引进行查询：由于全表扫描需要扫描的记录太多，代价太大，如果查询语句的搜索条件可以用到某个索引，那么直接使用索引来执行查询可能可以加快查询执行的时间，使用索引来执行查询的方式又有很多种：
  1.针对主键或唯一二级索引的等值查询
  2.针对普通二级索引的等值查询
  3.针对索引列的范围查询
  4.直接扫描整个索引

MySQL执行查询语句的方式叫做访问方法或者访问类型。同一个查询语句可能可以使用多种不同的访问方法来执行，虽然最后的查询结果是一样的，但是执行的时间可能天差地别。

const

有的时候我们可以通过主键列来定位一条记录：

SELECT * FROM single_table WHERE id = 1438;

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

对于single_table表的聚簇索引来说，展现的就是id列。我们想突出的重点是：B+树叶子节点中的记录是按照索引列排序的，对于的聚簇索引来说，它对应的B+树叶子节点中的记录就是按照id列排序的。B+树本来就是一个矮矮的大胖子，所以这样根据主键值定位一条记录的速度很快。
类似的，我们根据唯一二级索引列来定位一条记录的速度也是很快的：

SELECT * FROM single_table WHERE key2 = 3841;

可以看到这个查询的执行分为两步，第一步先从idx_key2对应的B+树索引中根据key2列与常数的等值比较条件定位到一条二级记录，然后再根据该记录的id的值到聚簇索引中获取到完整的用户记录。

设计者认为通过主键或者一二级索引列与常数的等职比较来定位一条记录是像坐火箭一样快的，所以他们把这种通过主键或者一二级索引列来定位一条记录的访问方法定义为：const，意思是常数级别的。
不过这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const访问方法才有效。
对于唯一二级索引来说，查询该列为NULL值的情况比较特殊：

SELECT * FROM single_table WHERE key2 IS NULL

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

ref

有时候我们对某个普通的二级索引列与常数进行等值比较：

SELECT * FROM single_table WHERE key1 = 'abc'

对于这个查询，我们当然可以全盘扫描（这个大概是通用的方法），也可以先使用二级索引找到对应记录的id值，然后再回表到聚簇索引中查找完整的用户记录。由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录的条数。如果匹配的记录较少，那么回表的代价还是很低的，在极限情况下，如果只有一个回表，那么就和const一样了。
因此MySQL可能选择使用索引而不是全表扫描的方式来执行查询。
设计者把这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为ref。
查询图示如下：

对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引中只能匹配到一条记录，所以这种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_table WHERE key1 = 'abc' OR key1 IS NULL;

当使用二级索引而不是全表扫描的方式执行该查询的时候，这种类型的查询使用的访问方法就成为ref_or_null，执行过程如下：

可以看到，上边的查询相当于先分别从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之间
  设计者把这种利用索引进行范围匹配的访问方法称为：range
  如果把这几个所谓的key2列的值需要满足的范围在数轴上体现出来的话：
  也就是从数学的角度看，每一个所谓的范围都是数轴上的一个区间，3个范围也就对应三个区间。
  我们可以把那种索引值列等值匹配的情况称之为单点区间，上面所说的范围1和范围2都可以被称为单点区间，像范围3这种的我们可以称之为连续范围区间。