访问方法access method---单表访问方法（三十六)

上篇文章回忆了innodDB的独立表空间和系统表空间的结构，因为需要梳理的知识点太多，所以额外用一篇。

独立表空间&系统表空间总结---innoDB表空间（三十五)

单表的访问方法

对我们开发来说，mysql就是个软件，用sql查询我们需要的数据，当遇到性能差的sql，如果我们连怎么优化都不知道，岂不是很尴尬。我们前面说过mysqlSql Service 有一个查询优化器的模块，一条sql进行预发解析后会进行查询优化，生成一个执行计划，这个执行计表明有哪些索引进行查询，表之间的链接顺序是什么样的，最后调用执行计划的步骤来真正的查询，吧结果返回给用户。不过查询优化这里的信息有点大，我们这里先看看单表的访问方法（from 单表那种）。

访问方法(access method)的概念

举个例子，大家用地图找路线的时候 ，地图会给你好几种路线，甚至你可以选择吧中国绕一遍再去到目的地，但不论哪种路线，你都能去到目的地，只是耗费的时间不同。回到mysql，我们只要获取到自己的需要的数据，至于mysql优化器怎么优化成执行计划查出来，是mysql的事。对于单表查询，mysql大致分为两种：

1. 使用全表扫描进行查询：这种顾名思义，为了查询数据，吧整个表都遍历一遍，基本算是最笨的方法，但什么数据都能找到。
2. 使用索引进行查询：因为使用全表扫描的方法代价太大，所以可以查询搜索语句中加入索引查询，使用索引查询又分为，1）针对主键或者唯一二级索引的等值查询。2）针对普通二级索引的等值查询。3）针对索引列的范围查询。4）直接扫描整个索引。

Mysql吧这种查询语句的方式称为访问方法或者访问类型。同一个sql语句可能有多种不同的查询方法，但查询的结果都是一样的。下面介绍各自方法的具体内容。

先建立个表single_table，给他五个索引，id为主键索引，key1和key3为普通索引，key2为唯一索引，idx_key_part为联合索引。然后自己可以写个程序给他插入10000条数据。

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.10 sec)

Const

当用主键查询和用唯一二级索引查询的时候，查询的是const方式，这种方式表示速度非常快，性能消耗基本忽略不计，为什么呢？

SELECT * FROM single_table WHERE id = 1438;

SELECT * FROM single_table WHERE key2 = 3841;

我们前面说了主键查询是直接在聚簇索引的b+树叶子节点上查询的，聚簇索引叶子节点存放的是当前列的所有数据，所以只要直接查询就可以获取所有数据。

当我们用唯一二级索引查询的时候，最多也就回表查询一次，因为二级索引的叶子几点存放的是对应的id，再用id回表从聚簇索引b+树查询一次。

至于唯一的二级索引比较特殊，比如这样：

SELECT * FROM single_table WHERE key2 IS NULL;

因为唯一二级索引并不限制null值的数量，所以导致可能查询多条数据，这样sql优化机制就没办法就const方法来进行查询。

ref

上面的情况是唯一二级索引查询，那如果普通二级索引查询呢？

SELECT * FROM single_table WHERE key1 = 'abc';

这时候，速度就是ref，比const效率差一点点，因为普通二级索引没有唯一性，可能会查询来多条数据，所以并不能根主键查询和唯一二级索引那样效率更高，前面的如果是坐火箭的话，这里大概是坐高铁差不多。查询的数据主要还是要看在二级索引的叶子节点会查出多少条id，如果id太多，回表的频率高，就非常影响性能，如果回表次数少，那效率还是非常高的。

1、二级索引列为null的情况：这点唯一二级索引和普通二级索引都一样，因为null可能存在多条，这种情况最多效率就是ref。

2、对于那种联合索引的二级索引来说，必须是where语句后面跟着常树相等的参数，比如

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方法访问数据库，

如果是这种，则就不能达到ref方法访问数据库的效率。

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

ref_or_null

当我们查询索引加null值的sql：

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

当这种情况，我们通过查询null值，并且这个sql没走全表扫描的时候，大部分is null都会放弃索引进行全表扫描，因为索引里没有存储null，如果这里走的是索引查询，则是用ref_or_null访问数据库。

range

这种区间方法查询数据库，如下sql会发生：

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

当前查询的区间是，1438数据和6328数据，以及38~79之间，这些数据都是sql需要的，这个sql可以用全表查询，或者二级索引+回表查询，此时查询就不是对某一个常量来匹配了，而是对这是哪个区间进行匹配。

1438和6328为单点范围区间，而38~79为连续范围区间，这种在mysql里面是用range方法来查询数据库的。

index

接下来看这个sql:

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

这个sql特点是他们都属于联合索引，联合索引的特点是后面的如果查询使用到索引，必须最左边那个数据是相等的，简称最左原则，所以这里并不能用range和ref方法区查询数据库。

但这里他可以通过遍历二级联合索引，来查询我们所需要的值，并且这里前面需要查询的数据，都在二级叶子节点里，所以并不需要回表去聚簇索引叶子节点查询。

all

这种是性能最差的全表查询，大家在写sql时候尽量避免这种方法访问数据库。