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

最新推荐文章于 2022-09-02 16:40:43 发布

后端从入门到精通

最新推荐文章于 2022-09-02 16:40:43 发布

阅读量359

点赞数

分类专栏： mysql进阶文章标签： mysql java

本文链接：https://blog.csdn.net/ke1ying/article/details/120253877

版权

mysql进阶专栏收录该内容

83 篇文章 12 订阅

订阅专栏

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

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

单表的访问方法

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

访问方法(access method)的概念

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

使用全表扫描进行查询：这种顾名思义，为了查询数据，吧整个表都遍历一遍，基本算是最笨的方法，但什么数据都能找到。
使用索引进行查询：因为使用全表扫描的方法代价太大，所以可以查询搜索语句中加入索引查询，使用索引查询又分为，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';