使用InnoDB存储引擎的表进行单表访问的访问方法及含义

本文链接：https://blog.youkuaiyun.com/qq_39298872/article/details/122831126

完整的访问方法有：system,const,eq_ref,ref,fulltext,ref_or_null,index_merge,unique_subquery，index_subquery，range，index，ALL
本文就只介绍几个常见的方法及含义，所用表的储存引擎为InnoDb

system
表中只有一条记录切该表使用的储存引擎的统计数据是精确的，那么对该表的访问就是system，但是不同的是在innodb中是all
const
根据主键或者唯一二级索引与 常数进行等职匹配的时候，访问就是const
```
SELECT * FROM s1 WHERE id = 5;
```
ref
通过普通的二级索引与常量进行等职匹配的来查询某个表的时候就是 ref

range
如果使用索引获取某些范围区间的记录,那么就可能用到range的访问方法

SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c'); 
或者
SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

index
覆盖索引,但需要扫描全部的索引记录，该表的访问方法就是index
```
SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
```
上面的sql语句查询的表其中 key_part2,key_part3都是联合索引中的列，但是联合索引还包含的一个列是key_part1，key_part1为最左列，所以不能使用ref或者range来访问
- 查询的列在联合索引中，条件也被包含在联合索引中
也就是说可以直接遍历该联合索引生成的b+树的叶子节点来判断条件是否成立，成立就将结果加到结果集就行。
由于二级索引所存放的记录少于聚簇索引的,加上我们搜索的列就在二级索引的记录之中，不需要在回表去查询主键索引(聚簇索引)，这种方式就是 index
All
all方法就是全表扫描
关于索引创建的代价
- 空间上
  每一个索引都会建立一颗b+树，每个b+树的每一个节点都是一个数据页，一页默认16kb的储存空间
- 时间上
  每次对表的增，删，改操作都需要去维护各个的b+树索引，b+树每层节点都是按照索引列的值从小到大的顺序排序组成的双向链表，每层节点中的记录无论是目录项记录还是用户记录都是按照从小到大的顺序排列成的一个单项链表，如果对表进行曾，删，查的操作会产生页分裂，页面位移，页面回收啥的操作导致来维护节点和记录的排序。一旦索引过多，每个操作都会对相应的b+树进行维护操作，性能也就随之下降了

总结及小贴士
这些访问方法按照介绍顺序他们的性能是依次变差，
1、索引的创建在空间和时间上都是有代价的，所以索引别乱建
2、B+树索引适用于下边这些情况：

  全值匹配
  匹配左边的列
  匹配范围值
  精确匹配某一列并范围匹配另外一列
  用于排序
  用于分组

3、在使用索引时需要注意下边这些事项：

  只为用于搜索、排序或分组的列创建索引
  为列的基数大的列创建索引
  索引列的类型尽量小
  可以只对字符串值的前缀建立索引
  只有索引列在比较表达式中单独出现才可以适用索引
  为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
  定位并删除表中的重复和冗余索引
  尽量使用覆盖索引进行查询，避免回表带来的性能损耗。