mysql学习笔记(4)之mysql索引类型与数据存储_mysql 的主键索引和非主键索引是如何存储的-CSDN博客

本文链接：https://blog.csdn.net/weixin_39775274/article/details/113868486

mysql索引类型与数据存储

主键索引与普通索引的区别
- myisam
- innodb
innodb回表查询
覆盖索引
hash索引
全文搜索索引

内容来源为六星教育，这里仅作为学习笔记

主键索引与普通索引的区别

myisam

myisam索引的结构也是btree索引的方式去实现，但是他的主键索引与普通索引的特点是与innodb是不同的，我们可以来看下面的图片

在这里插入图片描述

在图中分为主键索引与普通索引，主键索引中非叶子节点记录了索引的信息以及数据的行记录位置，而他的叶子节点是记录的实际的数据，普通索引也是一样的，非叶子节点记录了索引的信息以及行记录，叶子节点记录的实际的数据，是的myisam的主键索引与普通索引区别不大，唯一的区别也就是索引所存储的数据了。

innodb

在这里插入图片描述

在innodb中我们可以看到，主键索引与普通索引与myisam的结构是一样的，但是在叶子节点的所存储的数据却是不同，innodb的普通索引所记录的是实际的数据以及主键索引的记录，也就是说，innodb的普通索引查询会进行两次查询，当然这个两次查询也是有前提的，比如sql语句如下：select * from user where name = “ls”;
在这里插入图片描述

在这个语句中想要查询的字段是表中的全部字段，而索引idx_name（name）只是记录了name以及主键id的数据，其他字段的数据在该索引中查询不到，所以需要在通过主键索引去定位其他字段的数据，在返回结果。
而这一现象其实也是我们之后会去讲解的innodb表查询的回表问题。当然这里我们只是提下，并不做重点讲解，稍等我们来看这个问题。

总结：

myisam：myisam存储引擎的普通索引与主键索引在索引指向方面都是指定位实际的数据在磁盘中的位置
innodb：innodb存储引擎的普通索引与主键索引在索引指定当面是普通索引指定主键索引的数据以及索引相关字段数据在磁盘中的位置，主键索引指向的是数据在磁盘中实际的位置

innodb回表查询

回表问题：主要是普通索引引起的问题，是在innodb存储引擎下，这个是需要注意。一般是当sql语句查询的时候用到普通索引，获取的数据只要不存在于索引中的情况下就会出现回表问题

概念：根据普通索引查询到主键，又去主键索引去磁盘中检索数据。

比如：sql语句 select count(*) , name ,age from user where name =“ls”;
建立索引：alter table user add index idx_name(name);

这条sql语句在查询的时候会先在普通索引中去查询数据，然后发现没有age字段，在去获取到name = ‘ls’ 所对应的主键值，在根据主键值到主键索引中找到表中对应的数据，最后进行返回。

比如：SQL语句 select id,name from user where name=‘ls’;

这条SQL语句在查询的时候会现在普通索引中查询数据，发现可以直接取出数据就会直接返回，不需要再重新依靠主键索引去查询对应的数据。如下图所示：在这里插入图片描述

覆盖索引

主键索引：主要是主键
普通索引：普通索引是一个范围的定义，比如单列索引以及联合索引其实都是属于普通索引
联合索引建立语法：alter table user add index idx_name_age(name,age); --建立一个name与age相关的联合索引

再次来看我们上面的案例

select count(*) , name ,age from user where name =“ls”;

这个语句中有name，以及age，可以直接通过覆盖索引的方式来解决回表查询的问题

alter table user add index idx_name_age(name,age); --建立一个name与age相关的联合索引

这个是在去查询，他就会是普通索引也可以返回我们sql语句所需要的数据了，就不需要在进行回表查询。

hash索引

我们听说过得hash一般是有hash算法，还有hash加密等，比如：会有xxxx的代码使用了hash算法，hash加密，一般的话会有32位，64位的字符串。
hash索引概念：mysql会根据我们的字段进行hash计算生成一个字符串，这个字符串就是磁盘数据的地址。

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code)，哈希码是一个较小的值，大部分情况下不同的键值的行计算出来的哈希码是不同的，但是也会有例外，就是说不同列值计算出来的hash值一样的（即所谓的hash冲突），哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每一个数据行的指针，hash很适合做索引，为某一列或几列建立hash索引，就会利用这一列或几列的值通过一定的算法计算出一个hash值，
对应一行或几行数据。
在这里插入图片描述

针对上图的理解：
keys：代表创建索引的列值；
buckets：就是计算出来的hash值和对应的数据的物理位置组成的hash表；
entries：就是代表具体的数据行；

创建hash索引后，会为每个键值通过特定的算法计算出一个哈希码（hash code)，需要注意的是不同的键值计算出来的hash值可能是相同的，例上图上的 John Smith 和Sandra Dee算出来的hash值都152，然后找到hash值为152在hash表中的存储数据的物理位置，这个位置对应着两条数据也(就是John Smith 521-1234 和Sandra Dee 521-9655)，然后再次遍历这两条数据，找到需要的数据，这就解释了为啥hash冲突严重了，hash索引效率降低的原因。

innodb引擎有一个特殊的功能叫做自适应哈希索引，当innodb注意到某些索引值被使用的非常频繁时，它会在内存中基于btree索引之上再创建一个哈希索引，这样就让btree索引也具有哈希索引的一些优点，比如：